【MySQL命令总结】---高阶

创建 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)

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;

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

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

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;

[begin_label:]LOOP
    statement_list
END LOOP [end_label]

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

LEAVE label

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

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;

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

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

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

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

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

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

#创建一个名为 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);

SHOW TRIGGERS;

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

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

DROP TRIGGER double_salary;

BEGIN;

或

START TRANSACTION;

COMMIT;

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)