MySQL拾遗

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;