【MySQL命令总结】-- 高阶

最新推荐文章于 2024-05-27 11:31:40 发布

CSDN_mayan

最新推荐文章于 2024-05-27 11:31:40 发布

阅读量675

点赞数

分类专栏：数据库文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/CSDN_mayan/article/details/126995362

版权

数据库专栏收录该内容

4 篇文章

订阅专栏

一、MySQL约束

在 MySQL 中，主要支持6 种约束：主键约束、外键约束、唯一约束、检查约束、非空约束、默认值约束。

1.主键约束

1.1 在创建表时设置主键约束

既可以为表中的一个字段设置主键，也可以为表中多个字段设置联合主键。但是不论使用哪种方法，在一个表中主键只能有一个

1）设置单字段主键

CREATE TABLE 表名 <字段名> <数据类型> PRIMARY KEY [默认值];
#或者是在定义完所有字段之后指定主键，语法格式如下：
[CONSTRAINT <约束名>] PRIMARY KEY [字段名];

CREATE TABLE tb_emp3
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
CREATE TABLE tb_emp3
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT pk_emp3 PRIMARY KEY (id)
);
CREATE TABLE tb_emp3
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id)
);

2）在创建表时设置联合主键

PRIMARY KEY [字段1，字段2，…,字段n]

注意：当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。

mysql> CREATE TABLE tb_emp5
    -> (
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> PRIMARY KEY(name,deptId)
    -> );

1.2 在修改表时添加主键约束

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

mysql> ALTER TABLE tb_emp2
    -> ADD PRIMARY KEY(id);

1.3 删除主键约束

ALTER TABLE <数据表名> DROP PRIMARY KEY;

1.4 主键自增长

通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长。语法格式如下：

字段名 数据类型 AUTO_INCREMENT

默认情况下，AUTO_INCREMENT 的初始值是 1，每新增一条记录，字段值自动加 1。
一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。
AUTO_INCREMENT 约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等）。

AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限，AUTO_INCREMENT 就会失效。

mysql> CREATE TABLE tb_student(
    -> id INT(4) PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(25) NOT NULL
    -> );

指定自增字段初始值

mysql> CREATE TABLE tb_student2 (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL,
    -> PRIMARY KEY(ID)
    -> )AUTO_INCREMENT=100;

2.外键约束

2.1在创建表时设置外键约束

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

mysql> CREATE TABLE tb_emp6
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_dept1
    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );

2.2 在修改表时添加外键约束

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

mysql> ALTER TABLE tb_emp2
    -> ADD CONSTRAINT fk_tb_dept1
    -> FOREIGN KEY(deptId)
    -> REFERENCES tb_dept1(id);

2.3 删除外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

mysql> ALTER TABLE tb_emp2
    -> DROP FOREIGN KEY fk_tb_dept1;

3.唯一约束

3.1 在创建表时设置唯一约束

在定义完列之后直接使用 UNIQUE 关键字指定唯一约束，语法格式如下：

<字段名> <数据类型> UNIQUE

mysql> CREATE TABLE tb_dept2
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) UNIQUE,
    -> location VARCHAR(50)
    -> );

3.2 在修改表时添加唯一约束

ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

mysql> ALTER TABLE tb_dept1
    -> ADD CONSTRAINT unique_name UNIQUE(name);

3.3 删除唯一约束

ALTER TABLE <表名> DROP INDEX <唯一约束名>;

mysql> ALTER TABLE tb_dept1
    -> DROP INDEX unique_name;

4. 检查约束

4.1 选取设置检查约束的字段

CHECK <表达式>

“表达式”指的就是 SQL 表达式，用于指定需要检查的限定条件。

4.2 在创建表时设置检查约束

CHECK(<检查约束>)

mysql> CREATE TABLE tb_emp7
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CHECK(salary>0 AND salary<100),
    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );

4.3 在修改表时添加检查约束

ALTER TABLE tb_emp7 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

mysql> ALTER TABLE tb_emp7
    -> ADD CONSTRAINT check_id
    -> CHECK(id>0);

4.4 删除检查约束

ALTER TABLE <数据表名> DROP CONSTRAINT <检查约束名>;

mysql> ALTER TABLE tb_emp7
    -> DROP CONSTRAINT check_id;

5. 默认值约束

5.1在创建表时设置默认值约束

<字段名> <数据类型> DEFAULT <默认值>;

mysql> CREATE TABLE tb_dept3
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22),
    -> location VARCHAR(50) DEFAULT 'Beijing'
    -> );

5.2 在修改表时添加默认值约束

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;

mysql> ALTER TABLE tb_dept3
    -> CHANGE COLUMN location
    -> location VARCHAR(50) DEFAULT 'Shanghai';

5.3 删除默认值约束

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL;

mysql> ALTER TABLE tb_dept3
    -> CHANGE COLUMN location
    -> location VARCHAR(50) DEFAULT NULL;

6. 非空约束

6.1 在创建表时设置非空约束

<字段名> <数据类型> NOT NULL;

mysql> CREATE TABLE tb_dept4
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) NOT NULL,
    -> location VARCHAR(50)
    -> );

6.2 在修改表时添加非空约束

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> NOT NULL;

mysql> ALTER TABLE tb_dept4
    -> CHANGE COLUMN location
    -> location VARCHAR(50) NOT NULL;

6.3 删除非空约束

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;

mysql> ALTER TABLE tb_dept4
    -> CHANGE COLUMN location
    -> location VARCHAR(50) NULL;

7.查看数据表中的约束

SHOW CREATE TABLE <数据表名>;

二、 REGEXP：正则表达式

正则表达式主要用来查询和替换符合某个模式（规则）的文本内容

属性名 REGEXP '匹配方式'

其中，“属性名”表示需要查询的字段名称；“匹配方式”表示以哪种方式来匹配查询。

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	'^b' 匹配以字母 b 开头的字符串	book、big、banana、bike
$	匹配文本的结束字符	'st$' 匹配以 st 结尾的字符串	test、resist、persist
.	匹配任何单个字符	'b.t' 匹配任何 b 和 t 之间有一个字符	bit、bat、but、bite
*	匹配零个或多个在它前面的字符	'f*n' 匹配字符 n 前面有任意个字符 f	fn、fan、faan、abcn
+	匹配前面的字符 1 次或多次	'ba+' 匹配以 b 开头，后面至少紧跟一个 a	ba、bay、bare、battle
<字符串>	匹配包含指定字符的文本	'fa' 匹配包含‘fa’的文本	fan、afa、faad
[字符集合]	匹配字符集合中的任何一个字符	'[xz]' 匹配 x 或者 z	dizzy、zebra、x-ray、extra
[^]	匹配不在括号中的任何字符	'[^abc]' 匹配任何不包含 a、b 或 c 的字符串	desk、fox、f8ke
字符串{n,}	匹配前面的字符串至少 n 次	'b{2}' 匹配 2 个或更多的 b	bbb、bbbb、bbbbbbb
字符串 {n,m}	匹配前面的字符串至少 n 次，至多 m 次	'b{2,4}' 匹配最少 2 个，最多 4 个 b	bbb、bbbb

#在 tb_students_info 表中，查询 name 字段以“J”开头的记录
mysql> SELECT * FROM tb_students_info 
    -> WHERE name REGEXP '^J';
#在 tb_students_info 表中，查询 name 字段以“y”结尾的记录
mysql> SELECT * FROM tb_students_info
    -> WHERE name REGEXP 'y$';
#在 tb_students_info 表中，查询 name 字段值包含“a”和“y”，且两个字母之间只有一个字母的记录
mysql> SELECT * FROM tb_students_info
    -> WHERE name REGEXP 'a.y';
#在 tb_students_info 表中，查询 name 字段值包含字母“T”，且“T”后面出现字母“h”的记录
mysql> SELECT * FROM tb_students_info
    -> WHERE name REGEXP '^Th*';
#在 tb_students_info 表中，查询 name 字段值包含字母“T”，且“T”后面至少出现“h”一次的记录
mysql> SELECT * FROM tb_students_info
    -> WHERE name REGEXP '^Th+';
#在 tb_students_info 表中，查询 name 字段值包含字母“i”或“o”的记录
mysql> SELECT * FROM tb_students_info
    -> WHERE name REGEXP '[io]';
#在 tb_students_info 表中，查询 name 字段值包含字母 a~t 以外的字符的记录
mysql> SELECT * FROM tb_students_info
    -> WHERE name REGEXP '[^a-t]' ;
#在 tb_students_info 表中，查询 name 字段值出现字母‘e’ 至少 2 次的记录
mysql> SELECT * FROM tb_students_info WHERE name REGEXP 'e{2,}';
#在 tb_students_info 表中，查询 name 字段值出现字符串“i” 最少 1 次，最多 3 次的记录
ysql> SELECT * FROM tb_students_info WHERE name REGEXP 'i{1,3}';

三、视图

1.创建视图

CREATE VIEW <视图名> AS <SELECT语句>

#创建基于单表的视图
mysql> CREATE VIEW view_students_info
    -> AS SELECT * FROM tb_students_info;
mysql> CREATE VIEW v_students_info
    -> (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
    -> AS SELECT id,name,dept_id,age,sex,height,login_date
    -> FROM tb_students_info;
#创建基于多表的视图
mysql> CREATE VIEW v_students_info
    -> (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
    -> AS SELECT id,name,dept_id,age,sex,height,login_date
    -> FROM tb_students_info;

2. 查看视图的字段信息

DESCRIBE 视图名；
DESC 视图名;

mysql> DESCRIBE v_students_info;

3.查看视图的详细信息

SHOW CREATE VIEW 视图名;

mysql>  SHOW CREATE VIEW v_studentinfo;

4.修改视图

ALTER VIEW <视图名> AS <SELECT语句>

mysql> ALTER VIEW view_students_info
    -> AS SELECT id,name,age
    -> FROM tb_students_info;
#使用 UPDATE 语句更新视图 view_students_info
mysql> UPDATE view_students_info
    -> SET age=25 WHERE id=1;

修改视图的名称可以先将视图删除，然后按照相同的定义语句进行视图的创建，并命名为新的视图名称。

5.删除视图

DROP VIEW <视图名1> [ , <视图名2> …]

mysql> DROP VIEW IF EXISTS v_students_info;

四、索引

1.创建索引

三种创建索引的方法：

#1)使用 CREATE INDEX 语句
CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

#2) 使用 CREATE TABLE 语句
#在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的主键。
CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)
#在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的索引。
KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)
#在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的唯一性索引。
UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)
#在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的外键。
FOREIGN KEY <索引名> <列名>

#3) 使用 ALTER TABLE 语句
#在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加索引。
ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)
#在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加主键。
ADD PRIMARY KEY [<索引类型>] (<列名>,…)
#在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加唯一性索引。
ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)
#在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加外键。
ADD FOREIGN KEY [<索引名>] (<列名>,…)

2.创建普通索引

创建普通索引时，通常使用 INDEX 关键字。

mysql> CREATE TABLE tb_stu_info
    -> (
    -> id INT NOT NULL,
    -> name CHAR(45) DEFAULT NULL,
    -> dept_id INT DEFAULT NULL,
    -> age INT DEFAULT NULL,
    -> height INT DEFAULT NULL,
    -> INDEX(height)
    -> );

3.创建唯一索引

创建唯一索引，通常使用 UNIQUE 参数。

mysql> CREATE TABLE tb_stu_info2
    -> (
    -> id INT NOT NULL,
    -> name CHAR(45) DEFAULT NULL,
    -> dept_id INT DEFAULT NULL,
    -> age INT DEFAULT NULL,
    -> height INT DEFAULT NULL,
    -> UNIQUE INDEX(height)
    -> );

4.查看索引

SHOW INDEX FROM <表名> [ FROM <数据库名>]

5.删除索引

DROP INDEX <索引名> ON <表名>

根据 ALTER TABLE 语句的语法可知，该语句也可以用于删除索引。具体使用方法是将 ALTER TABLE 语句的语法中部分指定为以下子句中的某一项。

DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
DROP INDEX index_name：表示删除名称为 index_name 的索引。
DROP FOREIGN KEY fk_symbol：表示删除外键。

mysql> DROP INDEX height
    -> ON tb_stu_info;
mysql> ALTER TABLE tb_stu_info2
    -> DROP INDEX height;

五、存储过程

1.创建存储过程

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>

1) 过程名

存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。

2) 过程参数

MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。

3) 过程体

存储过程的主体部分，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。

在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。

为解决以上问题，通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下：

DELIMITER $$

$$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符。

在 MySQL 命令行客户端输入如下 SQL 语句。

mysql > DELIMITER ??

成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个问号“??”了。

#创建名称为 ShowStuScore 的存储过程，存储过程的作用是从学生成绩信息表中查询学生的成绩信息
mysql> DELIMITER //
mysql> CREATE PROCEDURE ShowStuScore()
    -> BEGIN
    -> SELECT * FROM tb_students_score;
    -> END //
#创建名称为 GetScoreByStu 的存储过程，输入参数是学生姓名。存储过程的作用是通过输入的学生姓名从学生成绩信息表中查询指定学生的成绩信息
mysql> DELIMITER //
mysql> CREATE PROCEDURE GetScoreByStu
    -> (IN name VARCHAR(30))
    -> BEGIN
    -> SELECT student_score FROM tb_students_score
    -> WHERE student_name=name;
    -> END //

2.查看存储过程的状态

SHOW PROCEDURE STATUS LIKE 存储过程名;

mysql> SHOW PROCEDURE STATUS LIKE 'showstuscore' \G

3.查看存储过程的定义

SHOW CREATE PROCEDURE 存储过程名;

4.查看存储过程的信息

存储过程的信息都存储在 information_schema 数据库下的 Routines 表中，可以通过查询该表的记录来查询存储过程的信息，SQL 语句如下：

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=存储过程名;

5.修改存储过程

ALTER PROCEDURE 存储过程名 [ 特征 ... ]

特征指定了存储过程的特性，可能的取值有：

CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
NO SQL 表示子程序中不包含 SQL 语句。
READS SQL DATA 表示子程序中包含读数据的语句。
MODIFIES SQL DATA 表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
DEFINER 表示只有定义者自己才能够执行。
INVOKER 表示调用者可以执行。
COMMENT 'string' 表示注释信息。

#下面修改存储过程 showstuscore 的定义，将读写权限改为 MODIFIES SQL DATA，并指明调用者可以执行，安全类型改为INVOKE。
mysql> ALTER PROCEDURE showstuscore MODIFIES SQL DATA SQL SECURITY INVOKER;

6.删除存储过程

DROP PROCEDURE [ IF EXISTS ] <过程名>

7.创建存储函数

CREATE FUNCTION sp_name ([func_parameter[...]])
RETURNS type
[characteristic ...] routine_body

sp_name 参数：表示存储函数的名称；
func_parameter：表示存储函数的参数列表；
RETURNS type：指定返回值的类型；
characteristic 参数：指定存储函数的特性，该参数的取值与存储过程是一样的；
routine_body 参数：表示 SQL 代码的内容，可以用 BEGIN...END 来标示 SQL 代码的开始和结束。

func_parameter 可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：

[IN | OUT | INOUT] param_name type;

#使用 CREATE FUNCTION 创建查询 tb_student 表中某个学生姓名的函数
mysql> DELIMITER //
mysql> CREATE FUNCTION func_student(id INT(11))
    -> RETURNS VARCHAR(20)
    -> COMMENT '查询某个学生的姓名'
    -> BEGIN
    -> RETURN(SELECT name FROM tb_student WHERE tb_student.id = id);
    -> END //

8.调用存储过程

CALL sp_name([parameter[...]]);

mysql> DELIMITER ;
mysql> CALL ShowStuScore();

9.调用存储函数

mysql> SELECT func_student(3);

六、游标

一般通过游标定位到结果集的某一行进行数据修改。

MySQL 游标只能用于存储过程和函数。

1. 声明游标

DECLARE cursor_name CURSOR FOR select_statement;

#下面声明一个名为 nameCursor 的游标，代码如下：
mysql> DELIMITER //
mysql> CREATE PROCEDURE processnames()
    -> BEGIN
    -> DECLARE nameCursor CURSOR
    -> FOR
    -> SELECT name FROM tb_student;
    -> END//

2. 打开游标

声明游标之后，要想从游标中提取数据，必须首先打开游标

OPEN cursor_name;

其中，cursor_name 表示所要打开游标的名称。需要注意的是，打开一个游标时，游标并不指向第一条记录，而是指向第一条记录的前边。

在程序中，一个游标可以打开多次。用户打开游标后，其他用户或程序可能正在更新数据表，所以有时会导致用户每次打开游标后，显示的结果都不同。

3. 使用游标

FETCH cursor_name INTO var_name [,var_name]...

上述语句中，将游标 cursor_name 中 SELECT 语句的执行结果保存到变量参数 var_name 中。变量参数 var_name 必须在游标使用之前定义。使用游标类似高级语言中的数组遍历，当第一次使用游标时，此时游标指向结果集的第一条记录。

MySQL 的游标是只读的，也就是说，你只能顺序地从开始往后读取结果集，不能从后往前，也不能直接跳到中间的记录。

4. 关闭游标

CLOSE cursor_name;

创建 users 数据表，并插入数据，SQL 语句和运行结果如下：
mysql> CREATE TABLE `users`
    ->  (
    ->  `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->  `user_name` VARCHAR(60),
    ->  `user_pass` VARCHAR(64),
    ->  PRIMARY KEY (`ID`)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO users VALUES(null,'sheng','sheng123'),
    -> (null,'yu','yu123'),
    -> (null,'ling','ling123');
Query OK, 3 rows affected (0.01 sec)
创建存储过程 test_cursor，并创建游标 cur_test，查询 users 数据表中的第 3 条记录，SQL 语句和执行过程如下：
mysql> DELIMITER //
mysql> CREATE PROCEDURE test_cursor (in param INT(10),out result VARCHAR(90))
    -> BEGIN
    -> DECLARE name VARCHAR(20);
    -> DECLARE pass VARCHAR(20);
    -> DECLARE done INT;
    -> DECLARE cur_test CURSOR FOR SELECT user_name,user_pass FROM users;
    -> DECLARE continue handler FOR SQLSTATE '02000' SET done = 1;
    -> IF param THEN INTO result FROM users WHERE id = param;
    -> ELSE
    -> OPEN cur_test;
    -> repeat
    -> FETCH cur_test into name,pass;
    -> SELECT concat_ws(',',result,name,pass) INTO result;
    -> until done
    -> END repeat;
    -> CLOSE cur_test;
    -> END IF;
    -> END //
Query OK, 0 rows affected (0.10 sec)

mysql> call test_cursor(3,@test)//
Query OK, 1 row affected (0.03 sec)

mysql> select @test//
+-----------+
| @test     |
+-----------+
| ling,ling123 |
+-----------+
1 row in set (0.00 sec)

七、流程控制语句

在存储过程和自定义函数中可以使用流程控制语句来控制程序的流程。MySQL 中流程控制语句有：IF 语句、CASE 语句、LOOP 语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句等。

1. IF语句

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

IF age>20 THEN SET @count1=@count1+1;
    ELSEIF age=20 THEN @count2=@count2+1;
    ELSE @count3=@count3+1;
END lF;

2. CASE语句

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

case_value 参数表示条件判断的变量，决定了哪一个 WHEN 子句会被执行；
when_value 参数表示变量的取值，如果某个 when_value 表达式与 case_value 变量的值相同，则执行对应的 THEN 关键字后的 statement_list 中的语句；
statement_list 参数表示 when_value 值没有与 case_value 相同值时的执行语句。
CASE 语句都要使用 END CASE 结束。

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

其中，search_condition 参数表示条件判断语句；statement_list 参数表示不同条件的执行语句。与上述语句不同的是，该语句中的 WHEN 语句将被逐个执行，直到某个 search_condition 表达式为真，则执行对应 THEN 关键字后面的 statement_list 语句。如果没有条件匹配，ELSE 子句里的语句被执行。

CASE age
    WHEN 20 THEN SET @count1=@count1+1;
    ELSE SET @count2=@count2+1;
END CASE;
CASE
    WHEN age=20 THEN SET @count1=@count1+1;
    ELSE SET @count2=@count2+1;
END CASE;

3. LOOP 语句

LOOP 语句可以使某些特定的语句重复执行。

LOOP 语句本身没有停止循环的语句，必须使用 LEAVE 语句等才能停止循环，跳出循环过程

[begin_label:]LOOP
    statement_list
END LOOP [end_label]

begin_label 参数和 end_label 参数分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；statement_list 参数表示需要循环执行的语句。

add_num:LOOP
    SET @count=@count+1;
END LOOP add_num;

4. LEAVE 语句

LEAVE 语句主要用于跳出循环控制。其语法形式如下：

LEAVE label

add_num:LOOP
    SET @count=@count+1;
    IF @count=100 THEN
        LEAVE add_num;
END LOOP add num;

5. ITERATE 语句

ITERATE 是“再次循环”的意思，用来跳出本次循环，直接进入下一次循环。

ITERATE label

#该示例循环执行 count 加 1 的操作，count 值为 100 时结束循环。如果 count 的值能够整除 3，则跳出本次循环，不再执行下面的 SELECT 语句。
add_num:LOOP
    SET @count=@count+1;
    IF @count=100 THEN
        LEAVE add_num;
    ELSE IF MOD(@count,3)=0 THEN
        ITERATE add_num;
    SELECT * FROM employee;
END LOOP add_num;

6. REPEAT 语句

REPEAT 语句是有条件控制的循环语句，每次语句执行完毕后，会对条件表达式进行判断，如果表达式返回值为 TRUE，则循环结束，否则重复执行循环中的语句。

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

其中：

begin_label 为 REPEAT 语句的标注名称，该参数可以省略；
REPEAT 语句内的语句被重复，直至 search_condition 返回值为 TRUE。
statement_list 参数表示循环的执行语句；
search_condition 参数表示结束循环的条件，满足该条件时循环结束。
REPEAT 循环都用 END REPEAT 结束。

REPEAT
    SET @count=@count+1;
    UNTIL @count=100
END REPEAT;

7. WHILE 语句

[begin_label:] WHILE search_condition DO
    statement list
END WHILE [end label]

WHILE @count<100 DO
    SET @count=@count+1;
END WHILE;

八.触发器

1.创建触发器

CREATE TRIGGER <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>

2.创建 BEFORE 类型触发器

#创建一个名为 SumOfSalary 的触发器，触发的条件是向数据表 tb_emp8 中插入数据之前，对新插入的 salary 字段值进行求和计算
mysql> CREATE TRIGGER SumOfSalary
    -> BEFORE INSERT ON tb_emp8
    -> FOR EACH ROW
    -> SET @sum=@sum+NEW.salary;

3.创建 AFTER 类型触发器

#创建一个名为 double_salary 的触发器，触发的条件是向数据表 tb_emp6 中插入数据之后，再向数据表 tb_emp7 中插入相同的数据，并且 salary 为 tb_emp6 中新插入的 salary 字段值的2倍
mysql> CREATE TRIGGER double_salary
    -> AFTER INSERT ON tb_emp6
    -> FOR EACH ROW
    -> INSERT INTO tb_emp7
    -> VALUES (NEW.id,NEW.name,deptId,2*NEW.salary);

4.查看触发器信息

SHOW TRIGGERS;

5.在triggers表中查看触发器信息

在 MySQL 中，所有触发器的信息都存在 information_schema 数据库的 triggers 表中，可以通过查询命令 SELECT 来查看

SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';
#查看所有触发器
SELECT * FROM information_schema.triggers \G

6.删除触发器

DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>

DROP TRIGGER double_salary;

九.事务

1. 开始事务

BEGIN;

或

START TRANSACTION;

2.提交事务

COMMIT;

3.回滚（撤销）事务

ROLLBACK;

#下面模拟在张三的账户减少 500 元后，李四的账户还未增加 500 时，有其他会话访问数据表的场景。由于代码需要在两个窗口中执行，为了方便阅读，这里我们称为 A 窗口和 B 窗口。

#在 A 窗口中开启一个事务，并更新 mybank 数据库中 bank 表的数据
mysql> USE mybank;。
Database changed
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE bank SET currentMoney = currentMoney-500
    -> WHERE customerName='张三';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#在 B 窗口中查询 bank 数据表中的数据
mysql> SELECT * FROM mybank.bank;
+--------------+--------------+
| customerName | currentMoney |
+--------------+--------------+
| 张三         |      1000.00 |
| 李四         |         1.00 |
+--------------+--------------+
2 rows in set (0.00 sec)

# 在 A 窗口中继续执行事务并提交事务
mysql> UPDATE bank SET currentMoney = currentMoney+500
    -> WHERE customerName='李四';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.07 sec)

# 在 B 窗口中再次查询 bank 数据表的数据
mysql> SELECT * FROM mybank.bank;
+--------------+--------------+
| customerName | currentMoney |
+--------------+--------------+
| 张三         |       500.00 |
| 李四         |       501.00 |
+--------------+--------------+
2 rows in set (0.00 sec)

在 A 窗口中执行 COMMIT 提交事务后，对数据所做的更新将一起提交，其他会话读取到的是更新后的数据。从结果可以看出张三和李四的总账户余额和转账前保持一致，这样数据从一个一致性状态更新到另一个一致性状态。