MySQL 从快速入门到精通

MySQL

---

数据库的核心功能就是处理数据，其中提供空间供数据存储又是其主要功能，当然数据库一般也不直接面向数据存储，存储的活是交给表／索引这类对象完成的，本篇文章将带您快速入门mysql！

MySQL产品的优点

• 成本低：开放源代码，一般可以免费试用
• 性能高：执行很快
• 简单：很容易安装和使用

启动MySQL服务器

以windows为例，需要以管理员身份运行
net start MySQL服务名
net stop MySQL服务名

客户端登录

• MySQL自带客户端
• 命令行: mysql -h 主机名 -P 端口号 -u 用户名 -p密码

注意:
（1）-p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格
mysql -hlocalhost -P3306 -uroot -proot
（2）密码建议在一行输入
mysql -h localhost -P 3306 -u root -p
Enter password:****
（3）如果是连本机：-hlocalhost就可以省略，如果端口号没有修改：-P3306也可以省略
简写成：mysql -u root -p
Enter password:****

• 可视化工具：Navicat Preminum，SQLyogEnt， Navicat for MySQL

MySQL的四个默认库：

• information schema ：记录用户、表、视图等元数据信息，提供类似 Oracle 数据字典的功能，类似于 Oracle 数据库中的 SYSTEM 表空间。值得关注的是，这个库是个特例，它是虚拟出来的库，是由 MySQL 实例构建和维护的，其对象都保存在内存中，也就是说在磁盘上找不到对应的物理存在，因为它是虚拟的 那么，用户也无法在该库下创建对象，甚至是 root 身份用户也不行，对于该库，用户唯能做的事情就是查询，而且该库中的对象在用户权限上面也非常特别。
• mysql ：记录用户权限、帮助、日志等信息，提供类似 Oracle 数据字典的功能，类似于 Oracle 数据库中的 SYSTEM SYSAUX 表空间
• performance_schema: MySQL 服务性能指标库，提供类似 Oracle 数据库中 v$类视图和数据字典的功能，这个库是从 MySQL5.5 版本才开始引入的
• test ：顾名思义，测试库，它最不重要，因此也留到最后才说它，其实也没啥可说的，直接删除都行，你们信不信？我反正是信了， 一会儿就把它
  删掉

SQL语句

SQL语法要求

• SQL语句可以单行或多行书写，以分号结尾；
• 可以用空格和缩进来来增强语句的可读性；
• 关键字不区别大小写，建议使用大写；

分类

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等（数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。）
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

在讲述SQL语句之前，特此申明help关键词
基本语法：help create database;
在下面的学习中，你将会深刻体会到help关键字的作用 😐

数据定义语言

查看当前存在的数据库：show databases;

SHOW DATABASES 命令实际显示的是，当前连接的用户拥有访问权限的数据库，登录数据库时默认使用拥有最高权限的用户，因此能够查看到所有存在的数据库，不过对于那些只拥有一定权限的用户，就只会看到它拥有访问权限的库和对象。所有此命令查看的是“当前”存在的数据库，因为它可能因为用户的不同，显示的数据库不同！

删除当前存在的数据库：DROP TABLE

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

DROP TABLE用于取消一个或多个表。您必须有每个表的DROP权限。所有的表数据和表定义会被取消，所以使用本语句要小心！

RESTRICT和CASCADE可以使分区更容易。目前，RESTRICT和CASCADE不起作用。
注释：除非您使用TEMPORARY关键词，DROP TABLE会自动提交当前的有效的事务。

TEMPORARY关键词具有以下作用：
· 语句只取消TEMPORARY表。
· 语句不会终止正在进行中的事务。
· 不会查验存取权。（TEMPORARY表仅对于创建该表的客户端是可见的，所以查验是不必要的。）

使用TEMPORARY是确保您不会意外取消一个非TEMPORARY表的良好方法。

\

删除表：drop [database | schema] [if exists] db_name

关键字	含义
DROP	DROP 关键字，固定语法
database | schema	schema 是 database的同义词
if exists	判断当前数据库是否存在，不存在会出现警告语句，但是如果没有此语言，而去删除一个不存在的数据库，则会报错

修改当前存在的数据库：ALTER DATABASE

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification [, alter_specification] ...

alter_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

ALTER DATABASE用于更改数据库的全局特性。这些特性储存在数据库目录中的db.opt文件中。要使用ALTER DATABASE，您需要获得数据库ALTER权限。

CHARACTER SET子句用于更改默认的数据库字符集。COLLATE子句用于更改默认的数据库整序。

数据库名称可以忽略，此时，语句对应于默认数据库。也可以使用ALTER SCHEMA。

修改表：ALTER TABLE

ALTER TABLE语法
ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
  | ADD [COLUMN] (column_definition,...)
  | ADD INDEX [index_name] [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        PRIMARY KEY [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [index_name] [index_type] (index_col_name,...)
  | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        [reference_definition]
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | table_options
  | partition_options
  | ADD PARTITION partition_definition
  | DROP PARTITION partition_names
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | ANALYZE PARTITION partition_names
  | CHECK PARTITION partition_names
  | OPTIMIZE PARTITION partition_names
  | REBUILD PARTITION partition_names
  | REPAIR PARTITION partition_names

ALTER TABLE用于更改原有表的结构。例如，您可以增加或删减列，创建或取消索引，更改原有列的类型，或重新命名列或表。您还可以更改表的评注和表的类型。

创建数据库：CREATE DATABASE

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]
 
create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name
CREATE DATABASE用于创建数据库，并进行命名。如果要使用CREATE DATABASE，您需要获得数据库CREATE权限。

创建索引：CREATE INDEX

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name (index_col_name,...)
 
index_col_name:
    col_name [(length)] [ASC | DESC]

CREATE INDEX被映射到一个ALTER TABLE语句上，用于创建索引。

创建表：CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options] [select_statement]
或：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(] LIKE old_tbl_name [)];
 
create_definition:
    column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
  | KEY [index_name] [index_type] (index_col_name,...)
  | INDEX [index_name] [index_type] (index_col_name,...)
  | [CONSTRAINT [symbol]] UNIQUE [INDEX]
        [index_name] [index_type] (index_col_name,...)
  | [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)
  | [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name,...) [reference_definition]
  | CHECK (expr)
 
column_definition:
    col_name type [NOT NULL | NULL] [DEFAULT default_value]
        [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
        [COMMENT 'string'] [reference_definition]
 
type:
    TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | CHAR(length) [BINARY | ASCII | UNICODE]
  | VARCHAR(length) [BINARY]
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
  | TEXT [BINARY]
  | MEDIUMTEXT [BINARY]
  | LONGTEXT [BINARY]
  | ENUM(value1,value2,value3,...)
  | SET(value1,value2,value3,...)
  | spatial_type
 
index_col_name:
    col_name [(length)] [ASC | DESC]
 
reference_definition:
    REFERENCES tbl_name [(index_col_name,...)]
               [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
               [ON DELETE reference_option]
               [ON UPDATE reference_option]
 
reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION
 
table_options: table_option [table_option] ...
 
table_option:
    {ENGINE|TYPE} = engine_name
  | AUTO_INCREMENT = value
  | AVG_ROW_LENGTH = value
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
  | CHECKSUM = {0 | 1}
  | COMMENT = 'string'
  | CONNECTION = 'connect_string'
  | MAX_ROWS = value
  | MIN_ROWS = value
  | PACK_KEYS = {0 | 1 | DEFAULT}
  | PASSWORD = 'string'
  | DELAY_KEY_WRITE = {0 | 1}
  | ROW_FORMAT = {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | UNION = (tbl_name[,tbl_name]...)
  | INSERT_METHOD = { NO | FIRST | LAST }
  | DATA DIRECTORY = 'absolute path to directory'
  | INDEX DIRECTORY = 'absolute path to directory'
 
partition_options:
    PARTITION BY
           [LINEAR] HASH(expr)
        |  [LINEAR] KEY(column_list)
        |  RANGE(expr)
        |  LIST(column_list)
    [PARTITIONS num]
    [  SUBPARTITION BY
           [LINEAR] HASH(expr)
         | [LINEAR] KEY(column_list)
      [SUBPARTITIONS(num)]  
    ]
    [(partition_definition), [(partition_definition)], ...]
 
partition_definition:
    PARTITION partition_name
        [VALUES { 
                  LESS THAN (expr) | MAXVALUE 
                | IN (value_list) }]
        [[STORAGE] ENGINE [=] engine-name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] (tablespace_name)]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition), [(subpartition_definition)], ...]
 
subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine-name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] (tablespace_name)]
        [NODEGROUP [=] node_group_id]
 
select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...   (Some legal select statement)

一句SQL，好几页的参数，就问大家慌不慌！

在此，可见help关键字的作用，在我看来，记住最基本的语句，那就够了，不知道的，一个help语句就够了！

help create index;

更改表名：RENAME TABLE

RENAME TABLE tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...

本语句用于对一个或多个表进行重命名。

数据操作语句

DELETE语法

单表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]
多表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*] ...]
    FROM table_references
    [WHERE where_definition]
或：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*] ...]
    USING table_references
    [WHERE where_definition]

tbl_name中有些行满足由where_definition给定的条件。DELETE用于删除这些行，并返回被删除的记录的数目

DO语法

DO expr [, expr] ...

DO用于执行表达式，但是不返回任何结果。DO是SELECT expr的简化表达方式。DO有一个优势，就是如果您不太关心结果的话，DO的速度稍快。
DO主要用于执行有副作用的函数，比如RELEASE_LOCK()。

HANDLER语法

HANDLER tbl_name OPEN [ AS alias ]
HANDLER tbl_name READ index_name { = | >= | <= | < } (value1,value2,...)
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name CLOSE

HANDLER语句提供通往表存储引擎接口的直接通道。HANDLER可以用于MyISAM和InnoDB表。

INSERT语法

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或：

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

INSERT用于向一个已有的表中插入新行。INSERT…VALUES和INSERT…SET形式的语句根据明确指定的值插入行。INSERT…SELECT形式的语句插入从其它表中选出的行。

INSERT ... SELECT语法

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
使用INSERT...SELECT，您可以快速地从一个或多个表中向一个表中插入多个行。

INSERT DELAYED语法

INSERT DELAYED ...
用于INSERT语句的DELAYED选项是MySQL相对于标准SQL的扩展。如果您的客户端不能等待INSERT完成，则这个选项是非常有用的。当您使用MySQL进行日志编写时，这是非常常见的问题。您也可以定期运行SELECT和UPDATE语句，这些语句花费的时间较长。

示例：

INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

LOAD DATA INFILE语法

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char' ]
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]

LOAD DATA INFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为一个文字字符串。

REPLACE语法

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
或：

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
或：

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...

REPLACE的运行与INSERT很相像。只有一点除外，如果表中的一个旧记录与一个用于PRIMARY KEY或一个UNIQUE索引的新记录具有相同的值，则在新记录被插入之前，旧记录被删除。

SELECT语法

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr, ...
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name']
    [FROM table_references
    [WHERE where_definition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_definition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC] , ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT用于恢复从一个或多个表中选择的行，并可以加入UNION语句和子查询。

TRUNCATE语法

TRUNCATE [TABLE] tbl_name

TRUNCATE TABLE用于完全清空一个表。从逻辑上说，该语句与用于删除所有行的DELETE语句等同，但是在有些情况下，两者在使用上有所不同。

对于InnoDB表，如果有需要引用表的外键限制，则TRUNCATE TABLE被映射到DELETE上；否则使用快速删减（取消和重新创建表）。使用TRUNCATE TABLE重新设置AUTO_INCREMENT计数器，设置时不考虑是否有外键限制。

对于其它存储引擎，在MySQL 5.1中，TRUNCATE TABLE与DELETE FROM有以下几处不同：
· 删减操作会取消并重新创建表，这比一行一行的删除行要快很多。
· 删减操作不能保证对事务是安全的；在进行事务处理和表锁定的过程中尝试进行删减，会发生错误。
· 被删除的行的数目没有被返回。
· 只要表定义文件tbl_name.frm是合法的，则可以使用TRUNCATE TABLE把表重新创建为一个空表，即使数据或索引文件已经被破坏。
· 表管理程序不记得最后被使用的AUTO_INCREMENT值，但是会从头开始计数。即使对于MyISAM和InnoDB也是如此。MyISAM和InnoDB通常不再次使用序列值。
· 当被用于带分区的表时，TRUNCATE TABLE会保留分区；即，数据和索引文件被取消并重新创建，同时分区定义（.par）文件不受影响。

TRUNCATE TABLE是在MySQL中采用的一个Oracle SQL扩展。

UPDATE语法

Single-table语法：

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]
Multiple-table语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

UPDATE语法可以用新值更新原有表行中的各列。SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行。如果指定了ORDER BY子句，则按照被指定的顺序对行进行更新。LIMIT子句用于给定一个限值，限制可以被更新的行的数目。

MySQL事务处理和锁定语句

MySQL通过SET AUTOCOMMIT, START TRANSACTION, COMMIT和ROLLBACK等语句支持本地事务（在给定的客户端连接中）。

START TRANSACTION, COMMIT和ROLLBACK语法

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

START TRANSACTION或BEGIN语句可以开始一项新的事务。COMMIT可以提交当前事务，是变更成为永久变更。ROLLBACK可以 回滚当前事务，取消其变更。SET AUTOCOMMIT语句可以禁用或启用默认的autocommit模式，用于当前连接。

默认情况下，MySQL采用autocommit模式运行。这意味着，当您执行一个用于更新（修改）表的语句之后，MySQL立刻把更新存储到磁盘中。

如果您正在使用一个事务安全型的存储引擎（如InnoDB, BDB或NDB簇），则您可以使用以下语句禁用autocommit模式：

SET AUTOCOMMIT=0;

通过把AUTOCOMMIT变量设置为零，禁用autocommit模式之后，您必须使用COMMIT把变更存储到磁盘中，或着如果您想要忽略从事务开始进行以来做出的变更，使用ROLLBACK。

如果您想要对于一个单一系列的语句禁用autocommit模式，则您可以使用START TRANSACTION语句：

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
使用START TRANSACTION，autocommit仍然被禁用，直到您使用COMMIT或ROLLBACK结束事务为止。然后autocommit模式恢复到原来的状态。

BEGIN和BEGIN WORK被作为START TRANSACTION的别名受到支持，用于对事务进行初始化。

您也可以按照如下方法开始一项事务：

START TRANSACTION WITH CONSISTENT SNAPSHOT;

SAVEPOINT和ROLLBACK TO SAVEPOINT语法

SAVEPOINT identifier
ROLLBACK [WORK] TO SAVEPOINT identifier
RELEASE SAVEPOINT identifier
InnoDB支持SQL语句SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT和自选的用于ROLLBACK的WORK关键词。

SAVEPOINT语句用于设置一个事务保存点，带一个标识符名称。如果当前事务有一个同样名称的保存点，则旧的保存点被删除，新的保存点被设置。

ROLLBACK TO SAVEPOINT语句会向以命名的保存点回滚一个事务。如果在保存点被设置后，当前事务对行进行了更改，则这些更改会在 回滚中被撤销。但是，InnoDB不会释放被存储在保存点之后的存储器中的行锁定。（注意，对于新插入的行，锁定信息被存储在行中的事务ID承载；锁定没有被分开存储在存储器中。在这种情况下，行锁定在撤销中被释放。）在被命名的保存点之后设置的保存点被删除。
如果语句返回以下错误，则意味着不存在带有指定名称的保存点：
ERROR 1181: Got error 153 during ROLLBACK
RELEASE SAVEPOINT语句会从当前事务的一组保存点中删除已命名的保存点。不出现提交或 回滚。如果保存点不存在，会出现错误。
如果您执行COMMIT或执行不能命名保存点的ROLLBACK，则当前事务的所有保存点被删除。

LOCK TABLES和UNLOCK TABLES语法

LOCK TABLES
    tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
    [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES

LOCK TABLES可以锁定用于当前线程的表。如果表被其它线程锁定，则造成堵塞，直到可以获取所有锁定为止。UNLOCK TABLES可以释放被当前线程保持的任何锁定。当线程发布另一个LOCK TABLES时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁。

表锁定只用于防止其它客户端进行不正当地读取和写入。保持锁定（即使是读取锁定）的客户端可以进行表层级的操作，比如DROP TABLE。

注意，下面是对事务表使用LOCK TABLES的说明：
在尝试锁定表之前，LOCK TABLES不是事务安全型的，会隐含地提交所有活性事务。同时，开始一项事务（例如，使用START TRANSACTION），会隐含地执行UNLOCK TABLES。（

SET TRANSACTION语法

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

本语句用于设置事务隔离等级，用于下一个事务，或者用于当前会话。

在默认情况下，SET TRANSACTION会为下一个事务（还未开始）设置隔离等级。如果您使用GLOBAL关键词，则语句会设置全局性的默认事务等级，用于从该点以后创建的所有新连接。原有的连接不受影响。要进行此操作，您需要SUPER权限。使用SESSION关键测可以设置默认事务等级，用于对当前连接执行的所有将来事务。

存储程序和授权表

授权表

创建存储子程序需要CREATE ROUTINE权限。
提醒或移除存储子程序需要ALTER ROUTINE权限，这个权限自动授予子程序的创建者。
执行子程序需要EXECUTE权限，这个权限自动授予子程序的创建者。

存储程序的语法

存储程序和函数是用CREATE PROCEDURE和CREATE FUNCTION语句创建的子程序 使用CALL语句来调用程序，程序只能用输出变量传回值。

创建存储程序

CREATE PROCEDURE和CREATE FUNCTION

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
   [characteristic ...] routine_body

CREATE FUNCTION sp_name ([func_parameter[,...]])
   RETURNS type
   [characteristic ...] routine_body
   
   proc_parameter:
   [ IN | OUT | INOUT ] param_name type
   
   func_parameter:
   param_name type

type:
   Any valid MySQL data type

characteristic:
   LANGUAGE SQL
 | [NOT] DETERMINISTIC
 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }
 | COMMENT 'string'

routine_body:
   Valid SQL procedure statement or statements

默认地，子程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.sp_name。
在上面语句出现的参数，我们将会在下面一一学习

如果子程序名和内建的SQL函数名一样，定义子程序时，你需要在这个名字和随后括号中间插入一个空格，否则发生语法错误。当你随后调用子程序的时候也要插入。

由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数默认都是一个IN参数。要指定为其它参数，可在参数名之前使用关键词 OUT或INOUT

注意: 指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。（FUNCTION参数总是被认为是IN参数）

MySQL允许子程序包含DDL语句，如CREATE和DROP。MySQL也允许存储程序（但不是存储函数）包含SQL 交互语句，如COMMIT。存储函数不可以包含那些做明确的和绝对的提交或者做回滚的语。

下面是一个使用OUT参数的简单的存储程序的例子。例子为，在程序被定义的时候，用mysql客户端delimiter命令来把语句定界符从 ;变为//。这就允许用在程序体中的;定界符被传递到服务器而不是被mysql自己来解释。
mysql> delimiter //
 
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;
 
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

当使用delimiter命令时，你应该避免使用反斜杠(‘\’)字符，因为那是MySQL的转义字符。 

下列是一个例子，一个采用参数的函数使用一个SQL函数执行一个操作，并返回结果： 

mysql> delimiter //
 
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
    -> RETURN CONCAT('Hello, ',s,'!');
    -> //
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;
 
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

如果在存储函数中的RETURN语句返回一个类型不同于在函数的RETURNS子句中指定类型的值，返回值被强制为恰当的类型。比如，如果一个函数返回一个ENUM或SET值，但是RETURN语句返回一个整数，对于SET成员集的相应的ENUM成员，从函数返回的值是字符串。

修改存储程序

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
 
characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

这个语句可以被用来改变一个存储程序或函数的特征

删除存储程序

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
这个语句被用来移除一个存储程序或函数。即，从服务器移除一个制定的子程序。在MySQL 5.1中，你必须有ALTER ROUTINE权限才可用此子程序。这个权限被自动授予子程序的创建者。

IF EXISTS 子句是一个MySQL的扩展。在前面的内容中，也学习过，在此再次复习一下。如果程序或函数不存储，它防止发生错误，产生一个可以用SHOW WARNINGS查看的警告。

查看创建存储程序语句

这个语句是一个MySQL的扩展。类似于SHOW CREATE TABLE，它返回一个可用来重新创建已命名子程序的确切字符串。
SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION

mysql> SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
       Function: hello
       sql_mode:
Create Function: CREATE FUNCTION `test`.`hello`(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')

查看存储程序的所有信息

SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS
这个语句是一个MySQL的扩展。它返回子程序的特征，如数据库，名字，类型，创建者及创建和修改日期。如果没有指定样式，根据你使用的语句，所有存储程序和所有存储函数的信息都被列出。

mysql> SHOW FUNCTION STATUS LIKE 'hello'\G
*************************** 1. row ***************************
           Db: test
         Name: hello
         Type: FUNCTION
      Definer: testuser@localhost
     Modified: 2004-08-03 15:29:37
      Created: 2004-08-03 15:29:37
Security_type: DEFINER
      Comment:

你可以从INFORMATION_SCHEMA中的ROUTINES表获得有关存储子程序的信息。

CALL语句

CALL语句调用一个先前用CREATE PROCEDURE创建的程序。

基础语法：CALL sp_name([parameter[,...]])

CALL语句可以用声明为OUT或的INOUT参数的参数给它的调用者传回值。它也“返回”受影响的行数，客户端程序可以在SQL级别通过调用ROW_COUNT()函数获得这个数，从C中是调用the mysql_affected_rows() C API函数来获得。

BEGIN … END复合语句

[begin_label:] BEGIN
    [statement_list]
END [end_label]

存储子程序可以使用BEGIN … END复合语句来包含多个语句。statement_list 代表一个或多个语句的列表。statement_list之内每个语句都必须用分号（；）来结尾。

复合语句可以被标记。除非begin_label存在,否则end_label不能被给出,并且如果二者都存在,他们必须是同样的。

DECLARE语句

DECLARE语句被用来把不同项目局域到一个子程序：局部变量，条件和处理程序及光标

DECLARE仅被用在BEGIN … END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。
光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。

存储程序中的变量:

1. DECLARE局部变量
DECLARE var_name[,...] type [DEFAULT value]

2. 变量SET语句
SET var_name = expr [, var_name = expr] ...

3. SELECT ... INTO语句
SELECT col_name[,...] INTO var_name[,...] table_expr

条件和处理程序
1. DECLARE条件
DECLARE condition_name CONDITION FOR condition_value
 
condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code
这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在DECLARE HANDLER语句中。
2. DECLARE处理程序
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
 
handler_type:
    CONTINUE
  | EXIT
  | UNDO
 
condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

光标

简单光标在存储程序和函数内被支持。语法如同在嵌入的SQL中。光标当前是不敏感的，只读的及不滚动的。不敏感意为服务器可以活不可以复制它的结果表。

光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
  OPEN cur1;
  OPEN cur2;
 
  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;
 
  CLOSE cur1;
  CLOSE cur2;
END

• 声明光标

DECLARE cursor_name CURSOR FOR select_statement
这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。

SELECT语句不能有INTO子句。 

• 光标OPEN语句

OPEN cursor_name
这个语句打开先前声明的光标。

• 光标FETCH语句

FETCH cursor_name INTO var_name [, var_name] ...
这个语句用指定的打开光标读取下一行（如果有下一行的话），并且前进光标指针。

• 光标CLOSE语句

CLOSE cursor_name
这个语句关闭先前打开的光标。 

如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。

流程控制构造

IF语句
CASE语句
LOOP语句
LEAVE语句
ITERATE语句
REPEAT语句
WHILE语句

IF, CASE, LOOP, WHILE, ITERATE, 及 LEAVE 构造被完全实现。

这些构造可能每个包含要么一个单独语句，要么是使用BEGIN … END复合语句的一块语句

IF语句

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

IF实现了一个基本的条件构造。如果search_condition求值为真，相应的SQL语句列表被执行。如果没有search_condition匹配，在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。

请注意，也有一个IF() 函数，它不同于这里描述的IF语句。

CASE语句

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
Or: 

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

存储程序的CASE语句实现一个复杂的条件构造。如果search_condition 求值为真，相应的SQL被执行。如果没有搜索条件匹配，在ELSE子句里的语句被执行。

注意：这里介绍的用在存储程序里的CASE语句“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句，并且用END CASE替代END来终止。

LOOP语句

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

LOOP允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直循环被退出，退出通常伴随着一个LEAVE 语句。

LOOP语句可以被标注。除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，它们必须是同样的。

LEAVE语句

LEAVE label

这个语句被用来退出任何被标注的流程控制构造。它和BEGIN … END或循环一起被使用。

ITERATE语句

ITERATE label

ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为：“再次循环。”

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE label1; END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END

REPEAT语句

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

REPEAT语句内的语句或语句群被重复，直至search_condition 为真。

REPEAT 语句可以被标注。 除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的。

mysql> delimiter //
 
mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
 
mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @x//
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

WHILE语句

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

WHILE语句内的语句或语句群被重复，直至search_condition 为真。

WHILE语句可以被标注。 除非begin_label也存在，end_label才能被用，如果两者都存在，它们必须是一样的。

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;
 
  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END