1.show create table 表名
可以得到建表语句
2.desc 表名
可以得到表的列相关信息
- mysql> help DESCRIBE
-
Name: 'DESCRIBE'
-
Description:
-
Syntax:
-
{DESCRIBE | DESC} tbl_name [col_name | wild]
-
-
DESCRIBE provides information about the columns in a table. It is a
-
shortcut for SHOW COLUMNS FROM. These statements also display
-
information for views. (See [HELP SHOW COLUMNS].)
-
-
col_name can be a column name, or a string containing the SQL "%" and
-
"_" wildcard characters to obtain output only for the columns with
-
names matching the string. There is no need to enclose the string
-
within quotes unless it contains spaces or other special characters.
-
-
mysql> DESCRIBE City;
-
+------------+----------+------+-----+---------+----------------+
-
| Field | Type | Null | Key | Default | Extra |
-
+------------+----------+------+-----+---------+----------------+
-
| Id | int(11) | NO | PRI | NULL | auto_increment |
-
| Name | char(35) | NO | | | |
-
| Country | char(3) | NO | UNI | | |
-
| District | char(20) | YES | MUL | | |
-
| Population | int(11) | NO | | 0 | |
-
+------------+----------+------+-----+---------+----------------+
-
5 rows in set (0.00 sec)
-
-
The description for SHOW COLUMNS provides more information about the
-
output columns (see [HELP SHOW COLUMNS]).
-
-
URL: http://dev.mysql.com/doc/refman/5.1/en/describe.html
3.SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
与desc不同,该命令可以显示跟多的字段信息
4.SHOW INDEX FROM tbl_name [FROM db_name]
可以得到表的索引
5.SHOW TABLES [FROM db_name] [like_or_where]
可以得到数据库中所有的表名
6.建表语句中的KEY是用来建立索引的,mysql扩展。
- CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
-
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
-
-
create_definition:
-
col_name column_definition
-
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
-
[index_option] ...
-
| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
-
[index_option] ...
-
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
-
[index_name] [index_type] (index_col_name,...)
-
[index_option] ...
-
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
-
[index_option] ...
-
| [CONSTRAINT [symbol]] FOREIGN KEY
-
[index_name] (index_col_name,...) reference_definition
-
| CHECK (expr)
7.START TRANSACTION
- START TRANSACTION
-
Name: 'START TRANSACTION'
-
Description:
-
Syntax:
-
START TRANSACTION [WITH CONSISTENT SNAPSHOT] | BEGIN [WORK]
-
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
-
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
-
SET autocommit = {0 | 1}
-
-
The START TRANSACTION or BEGIN statement begins a new transaction.
-
COMMIT commits the current transaction, making its changes permanent.
-
ROLLBACK rolls back the current transaction, canceling its changes. The
-
SET autocommit statement disables or enables the default autocommit
-
mode for the current session.
奇怪说START TRANSACTION或者BEGIN都可以开始一个新事务。但是我用的时候
怎么都不管用。接着看下面的:
- By default, MySQL runs with autocommit mode enabled. This means that as
-
soon as you execute a statement that updates (modifies) a table, MySQL
-
stores the update on disk to make it permanent. To disable autocommit
-
mode, use the following statement:
-
-
SET autocommit=0;
现在知道了吧,事务是自动提交的。要把事务自动提交给关掉。在C API中使用
my_bool mysql_autocommit(MYSQL *mysql, my_bool mode);
来操作是否自动提交事务
my_bool mysql_commit(MYSQL *mysql);
用来提交事务
my_bool mysql_rollback(MYSQL *mysql);
用来回滚事务
我没有找到Begin的对应函数,没有找到设置回滚点的函数,不知道怎么实现了。
mysql源代码目录下有一个client目录,下面有mysql命令的C++语言文件为mysql.cc。还没找到怎么实现。
以下是从mysql的官方手册上摘录下来的(
http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#savepoints)
SAVEPOINT identifier
ROLLBACK [WORK] TO SAVEPOINT identifier
RELEASE SAVEPOINT identifier
InnoDB支持SQL语句SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT和自选的用于ROLLBACK的WORK关键词。
LOCK TABLES和UNLOCK TABLES,SET TRANSACTION,XA事务都是事务相关的内容
8.CASE OPERATOR和CASE STATEMENT
CASE OPERATOR是在select,insert,delete,update中使用的,
CASE STATEMENT是在存储过程或者触发器中使用的。
两者的区别在于:
CASE OPERATOR的THEN后面不能跟sql语句
CASE STATEMENT的THEN后面可以跟sql语句
例如:
- mysql> SELECT CASE 1 WHEN 1 THEN 'one'
-
-> WHEN 2 THEN 'two' ELSE 'more' END;
-
-> 'one'
-
-----------------------------------------------------------------
-
CREATE PROCEDURE p()
-
BEGIN
-
DECLARE v INT DEFAULT 1;
-
-
CASE v
-
WHEN 2 THEN SELECT v;
-
WHEN 3 THEN SELECT 0;
-
ELSE
-
BEGIN
-
END;
-
END CASE;
-
END;
这样在写sql的时候就不能这么写:
update a set a.b=1,case 1 when 1 then a.c=1 end where a.d = 5;
就是选择性的更新某一列的值在mysql中是不能做的。
9.SET和SHOW VARIABLES
set设置自定义或者系统变量。
- SET variable_assignment [, variable_assignment] ...
-
-
variable_assignment:
-
user_var_name = expr
-
| [GLOBAL | SESSION] system_var_name = expr
-
| @@[global. | session.]system_var_name = expr
SHOW VARIABLES显示系统变量,另外显示系统变量还可以通过使用@@[global.|local.]var_name语法和SELECT来得到值:
- SELECT @@max_join_size, @@global.max_join_size;
当您使用SELECT @@var_name(即您不指定全局、会话或本地)来恢复一个变量时,则MySQL会返回SESSION值(如果存在)或者GLOBAL值。
还有一些非标准语法的变量,或描述在系统变量清单中(
见5.3.3节,“服务器系统变量”。)中没有描述的变量。这些变量没有被SHOW VARIABLES显示,但是您可以使用SELECT来获得它们的值(例外情况是,使用CHARACTER SET和SET NAMES)。例如:
- mysql> SELECT @@AUTOCOMMIT;
阅读(2505) | 评论(1) | 转发(2) |