MySQL常用命令

春日阳光

¶1 操作数据库

¶1.1 创建数据库

CREATE DATABASE mydb1;

CREATE DATABASE mydb2 CHARACTER SET utf8; # 定义编码集

CREATE DATABASE mydb3 CHARACTER SET utf8 COLLATE utf8_bin; # 定义校对规则

¶1.2 查看数据库

1
2
3

SHOW DATABASES # 查看当前数据库服务器中所有数据库

SHOW CREATE DATABASE mydb1; # 查看前面创建的mydb1数据库的定义信息

¶1.3 修改数据库

1
2
3

ALTER DATABASE mydb1 CHARACTER SET utf8; # 修改编码集

ALTER DATABASE mydb1 COLLATE utf8_bin; # 修改校对规则

¶1.4 删除数据库

1	DROP DATABASE mydb1; # 删除

¶1.5 选择数据库

1
2
3

use mydb1;  # 进入，没有退出操作

SELECT DATABASE(); # 查看当前所选的数据库

¶2 表

¶2.1 增加表

CREATE TABLE t1
(
    id INT,
    name VARCHAR(20),
    gender CHAR(1),
    worktime DATE,
    money DOUBLE
) CHARACTER SET utf8 COLLATE utf8_bin;

¶2.2 查找表

SHOW TABLES; # 查看表

SHOW CREATE TABLE mytable； # 查看表的建表语句

DESC mytable;  # 查看表结构

¶2.3 修改表

ALTER TABLE mytable1 ADD image BLOB; # 新增列

ALTER TABLE mytable1 MODIFY name VARCHAR(25); # 修改列的类型

ALTER TABLE mytable1 DROP gender; # 删除某列

ALTER TABLE mytable1 CHARACTER SET utf8; # 修改表的字符集

ALTER TABLE mytable1 CHANGE name username VARCHAR(20); # 修改列名

RENAME TABLE mytable1 TO mytable2; # 修改表名 mytable1 -> mytable2

¶2.4 删除表

1	DROP TABLE mytable1;

¶2.5 各种约束

CREATE TABLE t1
(
    id INT PRIMARY KEY AUTO_INCREMENT, # 主键约束,自动增长
    name VARCHAR(20) UNIQUE,  # 唯一约束   
    gender CHAR(1) NOT NULL,  # 非空约束
    worktime DATE,
    money DOUBLE DEFAULT 10000.0  # 默认值约束
) CHARACTER SET utf8 COLLATE utf8_bin;

FOREIGN KEY // 外键约束

¶2.6 索引

¶2.6.1 创建索引

#/////////////////////////
#// 方式一 ： 建表时候创建索引
#/////////////////////////
# 创建普通索引
CREATE TABLE t1
(
    id INT, 
    INDEX bieming(id) 
)；

# 创建唯一索引,升序
CREATE TABLE t2
(
    id INT, 
    UNIQUE INDEX bieming(id, ASC) 
)；

# 创建全文索引
CREATE TABLE t3
(
    name VARCHAR(20), 
    FULLTEXT INDEX bieming(name) 
)ENGINE=MyISAM; # 必须使用 myisam引擎

# 创建多列索引
CREATE TABLE t4
(
    id INT, 
    name VARCHAR(20),
    INDEX bieming(id,name(20)) 
)；

# 创建空间索引
CREATE TABLE t5
(
    a1 GEOMETRY,
    a2 POINT,
    a3 LINESTRING,
    a4 POLYGON
    SPATIAL INDEX bieming(a1) 
)ENGINE=MyISAM; # 必须使用 myisam引擎


#/////////////////////////
#// 方式二： 在已经存在的表上创建索引
#/////////////////////////


CREATE TABLE t1
(
    id INT, // 主键约束,自动增长
    name VARCHAR(20), // 唯一约束   
    gender CHAR(1),  // 非空约束
    worktime DATE,
    money DOUBLE  // 默认值约束
);

CREATE INDEX bieming on t1(id);          	# 创建普通索引
CREATE UNIQUE INDEX bieming on t1(id, ASC); # 创建唯一索引
CREATE FULLTEXT INDEX bieming on t1(id); 	# 创建全文索引
CREATE INDEX bieming on t1(id, gender(1));  # 创建多列索引
CREATE SPATIAL INDEX bieming on t1(id);  	# 创建空间索引

¶2.6.2 删除索引

1
2
3

ALTER TABLE t1 DROP INDEX bieming;

DROP INDEX beiming ON t1;

¶3 操作表记录

mysql> desc test2;
#+--------+----------+------+-----+---------+-------+
#| Field  | Type     | Null | Key | Default | Extra |
#+--------+----------+------+-----+---------+-------+
#| id     | int      | YES  |     | NULL    |       |
#| name   | char(50) | YES  |     | NULL    |       |
#| gender | char(1)  | YES  |     | NULL    |       |
#| salary | double   | YES  |     | NULL    |       |
#+--------+----------+------+-----+---------+-------+

¶4.1 insert

INSERT INTO test1(id, name, gender) VALUES(10, 'JACK', 'M'); # 可以缺省

INSERT INTO test1 VALUES(11, 'MAC', 'M', 123.245); # 省去field 必须满足列数相同
												   # 且可以一次性添加多条
INSERT INTO test1 VALUES(11, 'MAC', 'M', 123.245), (12, 'MAcC', 'M', 123.5245);

¶4.2 update

UPDATE test1 SET salary = 5000; # 表内所有人工资变成 5000

UPDATE test1 SET salary = 3000 WHERE name = 'A'; # 表内名为 A 的工资更新为 3000

UPDATE test1 SET salary = 4000, gender = 'f' WHERE = 'B'; # B 改为4000， 女

UPDATE test1 SET salary = salary + 1000 WHERE name = 'C'; # 支持表达式运算

¶4.3 delete，truncate

DELETE FROM test1 WHERE name = 'C'; # 删除 C 的数据

DELETE FROM test1;  # 删除表中所有数据

TRUNCATE test1;     # 删除表中所有数据 -> 摧毁表并创建新的表格

¶4 单表查询

#+----+-------+--------+--------+-----------+
#| id | name  | gender | salary | allowance |
#+----+-------+--------+--------+-----------+
#|  1 | Alice | M      |   5000 |       500 |
#|  2 | Bob   | M      |   4500 |       400 |
#|  3 | Cc    | F      |   6000 |       600 |
#+----+-------+--------+--------+-----------+

¶4.1 简单查询

SELECT * FROM test2;  # 查询所有内容

SELECT id, name FROM test2;  # 查询 id 和 name , 注意 逗号

SELECT DISTINCT gender FROM test2;  # 过滤表中重复的数据

SELECT name, salary + 1200 FROM test2;  # 查询税前薪水（+1200） 数据库底层数据没有改变

SELECT name, salary + allowance FROM test2;  # 统计薪水 + 津贴

SELECT name AS 姓名, salary + allowance AS 总工资 FROM test2;  # 使用别名
SELECT name 序号, salary + allowance 总工资 FROM test2; 		  # as 用空格代替

¶4.2 带有过滤的查询

SELECT * FROM test2 WHERE name = 'A';  # 查询 A 的所有数据

SELECT * FROM test2 WHERE salary > 5000;  # 查询 薪水大于 5000 的用户

SELECT * FROM test2 WHERE salary + allowance > 4000;  # 查询薪水 + 津贴大于 4000 的用户 

SELECT * FROM test2 WHERE salary BETWEEN 3500 AND 6500;  # 查询薪水在 3500- 6500 之间

SELECT * FROM test2 WHERE salary IN (4000, 6000);  # 查询薪水在 4000 和 6000 

SELECT * FROM test2 WHERE name like 'A%';   # 模糊查找名字有A （% -> 0-n 个， _ ->0-1个）
SELECT * FROM test2 WHERE name like 'A____';  #/ _ 数量对应

SELECT * FROM test2 WHERE salary > 4000 and allowance > 500;

¶4.3 聚合函数

count 聚合函数，用来统计行数

1
2
3

SELECT COUNT(*) FROM test2;

SELECT COUNT(*) FROM test2 WHERE salary + allowance > 4000; # 查薪水津贴大于4000的用户数

sum 聚合函数，求符合条件的某列的和值

SELECT SUM(salary) FROM test1;  # 所有人的总薪水

SELECT SUM(salary + allowance) FROM test1;  # 所有人的总收入（薪水+津贴）

SELECT SUM(salary + allowance) / COUNT(*) FROM test1;  # 收入的平均水平

avg 聚合函数，求符合条件的列的平均值

1	SELECT AVG(salary) FROM test1; # 求薪水的平均值

max / min ，求符合条件的列的最大最小值

1 2	SELECT MAX(salary) FROM test1; # 最大值 SELECT MIN(salary) FROM test1; # 最小值

¶4.4 排序查询

SELECT name, salary FROM test1 ORDER BY salary;  # 按照薪水大小排序 升序
SELECT name, salary FROM test1 ORDER BY salary DESC; # 降序

SELECT name 姓名, salary + allowance 总收入 FROM test1 ORDER BY 总收入 DESC;  # 按照总收入降序排序

SELECT name 姓名, salary + allowance 总收入 FROM test1 WHERE id < 3 ORDER BY 总收入 DESC;  # 按照id<3 总收入降序排序（使用别名）

¶4.5 分组查询

CREATE TABLE ORDERS(
	id int,
    product varchar(20),
    price float
);

SELECT *FROM orders GROUP BY product;  # 按照商品分组查找

SELECT product, SUM(price) FROM orders GROUP BY product;  # 分组合并价格总数

# 查询总价大于100的商品名称，含组合

# WHERE 在分组之前过滤 不满足条件 且不允许使用聚合语句
SELECT product, SUM(price) FROM orders WHERE price > 100 GROUP BY product;  √ 一半
SELECT product, SUM(price) FROM orders WHERE SUM(price) > 100 GROUP BY product;  x

# HAVING 分组之后
SELECT product, SUM(price) FROM orders GROUP BY product HAVING SUM(price) > 100;

# 总价大于100，单价小于100
SELECT product, SUM(price) FROM orders WHERE price < 100 GROUP BY product HAVING sum(price) > 100;

¶4.6 limit

1 2	SELECT * FROM cast LIMIT 5; # 从第1条开始取5条 SELECT * FROM cast LIMIT 5, 5; # 从第6条开始取5条

¶4.7 别名

SELECT * FROM tableName AS otherName;  # 给表明取别名  AS 可以省略

SELECT * FROM student AS s WHERE s.gender = 'F';

SELECT name AS 姓名 FROM tablename;  # 字段别名

¶5 多表查询

¶5.1 添加外键约束

¶5.1.1 声明表的时候添加外键

FOREIGN KEY(dept_id) REFERENCES dept(id) 

CREATE TABLE dept (
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);
INSERT INTO dept VALUES(NULL,'财务部'),(NULL,'人事部'),(NULL,'行政部'),(NULL,'科技部');
CREATE TABLE emp (
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY(dept_id) REFERENCES dept(id)  # 创建表的时候添加外键约束
);
INSERT INTO emp VALUES(NULL,'奥巴马',1),(NULL,'拜登',3),(NULL,'林肯',4),(NULL,'特朗普',2);

¶5.5.2 表已经存在

1
2
3

ALTER TALBE 表名 ADD CONSTRAINT fk_id FOREIGN KEY (外键字段名) REFERENCES 外表表名（主键字段名）

ALTER TABLE emp ADD CONSTRAINT aaa_fk FOREIGN KEY (dept_id) REFERENCES dept(id);

¶5.5.3 删除外键

1	ALTER TABLE emp DROP FOREIGN KEY aaa_fk; # 删除外键名

¶5.2 操作关联表

¶5.2.1 添加数据

¶5.2.2 删除数据

¶5.3 连接查询

#+----+--------+
#| id | name   |
#+----+--------+
#|  1 | 财务部 |
#|  2 | 人事部 |
#|  3 | 行政部 |
#|  4 | 科技部 |
#|  5 | 商务部 |
#+----+--------+
#+----+--------+---------+
#| id | name   | dept_id |
#+----+--------+---------+
#|  1 | 奥巴马 |       1 |
#|  2 | 拜登   |       3 |
#|  3 | 林肯   |       4 |
#|  4 | 特朗普 |       4 |
#|  5 | 克林顿 |       6 |
#+----+--------+---------+

# 笛卡尔积查询  -> 两张表相乘的结果
SELECT * FROM dept, emp;   

# 内连接查询    -> 查询两张表都有的记录
SELECT * FROM dept, emp WHERE dept.id = emp.dept_id;
SELECT * FROM dept INNER JOIN emp ON dept.id = emp.dept_id;

# 左外连接查询  -> 在内连接的基础上增加左有右无的记录
SELECT * FROM dept LEFT JOIN emp ON dept.id = emp.dept_id;

# 左外连接查询  -> 在内连接的基础上增加右有左无的记录
SELECT * FROM dept RIGHT JOIN emp ON dept.id = emp.dept_id;

# 全外连接查询   mysql #不支持#
SELECT * FROM dept FULL JOIN emp ON dept.id = emp.dept_id;

# 全外连接查询  mysql 方式 利用 union 操作
SELECT * FROM dept LEFT JOIN emp ON dept.id = emp.dept_id
union
SELECT * FROM dept RIGHT JOIN emp ON dept.id = emp.dept_id;

# 查询 dept_id = 4 的部门名称和其中的员工的姓名（包含id）
SELECT * FROM dept INNER JOIN emp ON dept.id = emp.dept_id WHERE dept.id = 4;

# 查询 dept_id = 4 的部门名称和其中的员工的姓名（不包含id）
SELECT dept.name AS 部门, emp.name AS 姓名 FROM dept INNER JOIN emp ON dept.id = emp.dept_id WHERE dept.id = 4;

¶5.4 子查询

¶5.4.1 带 in 关键字的子查询

使用 in 关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列中的值将供外层查询语句进行比较操作。

# 建立表格
CREATE TABLE department (
	did INT,
    dname VARCHAR(50)
);
INSERT INTO department VALUES(1,'网络部'),(2,'媒体部'),(3,'研发部'),(5,'人事部');

#+------+--------+
#| did  | dname  |
#+------+--------+
#|    1 | 网络部 |
#|    2 | 媒体部 |
#|    3 | 研发部 |
#|    5 | 人事部 |
#+------+--------+

CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    did INT
);
INSERT INTO employee VALUES(NULL, '张三', 20, 1),(NULL, '李四', 22, 1),(NULL, '王五', 20, 2),(NULL, '刘六', 20, 4);

#+----+------+------+------+
#| id | name | age  | did  |
#+----+------+------+------+
#|  1 | 张三 |   20 |    1 |
#|  2 | 李四 |   22 |    1 |
#|  3 | 王五 |   20 |    2 |
#|  4 | 刘六 |   20 |    4 |
#+----+------+------+------+


# 查询存在年龄为20的员工的部门

# 1. 查询年龄为20的员工信息
SELECT did FROM employee WHERE age = 20;
# 2. 查询他们的部门,将 1 作为嵌套语句
SELECT dname FROM department WHERE did IN (SELECT did FROM employee WHERE age = 20);

¶5.4.2 带 not in 关键字的子查询

与 in 关键词相反

# 查询存在年龄不是20的员工的部门

# 1. 查询年龄为20的员工信息
SELECT did FROM employee WHERE age = 20;
# 2. 查询他们的部门,将 1 作为嵌套语句
SELECT dname FROM department WHERE did NOT IN (SELECT did FROM employee WHERE age = 20);

¶5.4.3 带 exists 关键字的子查询

关键词后面的参数可以时任意一个子查询，这个子查询的作用相当于测试，他不产生任何数据，只返回 TRUE , FALSE。

# 查询employee中是否有年龄大于20的员工，如果存在，则查询department表中的所有记录

# 1. 查询年龄大于20的员工
SELECT * FROM employee WHERE age > 20;
# 2. 嵌套
SELECT * FROM department WHERE EXISTS (SELECT * FROM employee WHERE age > 20);

¶5.4.4 带 any 关键字的子查询

any 关键字表示满足其中任意一个条件，它允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任意一个比较条件，就返回一个结果作为查询条件。

# 查询 department.did > employee.did 的部门名称
# 1. 查询 employee 所有 did
SELECT did FROM employee;
# 2. 嵌套比较
SELECT * FROM department WHERE did > ANY(SELECT did FROM employee);

¶5.4.5 带 all 关键字的子查询

相较于 any 需要满足所有条件

# 查询 department.did > 所有 employee.did 的部门名称
# 1. 查询 employee 所有 did
SELECT did FROM employee;
# 2. 嵌套比较
SELECT * FROM department WHERE did > ALL(SELECT did FROM employee);

¶5.4.6 带比较运算符的子查询

# 查询 王五 是哪个部门
# 1. 查询王五的did
SELECT did FROM employee WHERE name = '王五';
# 2. 嵌套比较
SELECT dname FROM department WHERE did = (SELECT did FROM employee WHERE name = '王五');

¶6 事务

¶6.1 事务控制

¶6.1.1 基本概念

事务：逻辑上的一组操作，要么同时完成要么同时不完成

mysql 默认就自带事务，但是mysql自带的事务是一条语句独占一个事务

我们也可以自己来通知事务：

START TRANSACTION;  #开启事务
...
...
COMMIT;   # 提交事务
ROLLBACK; # 回滚事务，也就是取消上述 (放在事务内部...后面)

CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    money DOUBLE
);
INSERT INTO account VALUES(NULL,'A',1000),(NULL,'B',1200);

## 转账 ##
# 方式一
UPDATE account SET money = money - 100 WHERE name = 'A';
# 如果不在事务里面，第一条运行，第二条失败会导致转账出现问题
UPDATE account SET money = money + 100 WHERE name = 'B'; 

# 方式二

START TRANSACTION;  #开启事务
UPDATE account SET money = money - 100 WHERE name = 'A';
UPDATE account SET money = money + 100 WHERE name = 'B'; 
COMMIT;   # 提交事务

¶6.1.2 事务的特性 ACID

原子性：(Atomicity)

事务是一组不可分割的单位，要么同时成功，要么同时失败。

一致性：(Consistency)

事务前后的数据完整性应该保持一致。（数据库的完整性：如果事务在某个时间点下，所有的数据都符合所有的约束，则称数据库为符合完整性的状态）也就是说数据库中的数据并不会因为执行了一半而导致不同。

隔离性：(Isolation)

多个用户并发访问数据库时，一个用户的事务不能被其他用户的事物所干扰，多个并发事务之间数据要相互隔离。

持久性：(Durability)

一个事务一旦被提交，他对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。（也就是 commit 后不能 rollback）

¶6.1.3 事务的隔离级别

隔离性：本质就是多个线程操作同一个资源早成的多线程并发安全问题。加锁可以完美保证隔离性，但是影响性能。

两个事务并发的修改：必须隔离

两个事务并发的查询：完全不用隔离

一个事务修改，一个事务查询：？

（脏读：一个事务读取到另一个事务未提交的数据）

（不可重复读：一个事务多次读取同一条记录，读取的结果不相同）

（虚读，幻读：一个事务多次查询整表的数据，由于其他事务新增（删除）记录造成多次查询出的记录条数不同）

四大隔离级别：

READ UNCOMMITTED;   # 不做任何隔离问题

READ COMMITTED;		# 可以防止脏读

REPEATABLE READ;    # 可以防止 脏读 不可重复读   mysql 默认

SERIALIZABLE;       # 数据库运行在串行化为实现 解决所有问题，单性能低


SET [SESSION / GLOBAL] TRANSACTION ISOLATION LEVEL ...; # 修改隔离级别
SELECT @@TRANSACTION_ISOLATION;  # 查询当前数据库的隔离级别

¶6.2 存储过程的创建

存储过程类似于一个函数。

创建存储过程

# 创建表
CREATE DATABASE chapter06；
USE chapter06;
CREATE TABLE student(
	id INT(3) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    grade FLOAT,
    gender CHAR(2)
);
INSERT INTO student(name, grade, gender) VALUES('tom', 60, '男'),('jack', 70, '男'),('rose', 90, '女'),('lucy', 100, '女');

#+----+------+-------+--------+
#| id | name | grade | gender |
#+----+------+-------+--------+
#|  1 | tom  |    60 | 男     |
#|  2 | jack |    70 | 男     |
#|  3 | rose |    90 | 女     |
#|  4 | lucy |   100 | 女     |
#+----+------+-------+--------+

# 创建查看student表的存储过程

# 将 mysql 结束语句改为 //
DELIMITER // 
CREATE PROCEDURE Proc()
BEGIN
	SELECT * FROM student;
END //
# 存储过程结束之后将分号改回来
DELIMITER ;

CALL Proc; # 使用

DROP PROCEDURE Proc; # 删除

¶6.3 存储过程的使用

¶6.3.1 定义一个变量

# 注意 变量的声明一定要在存储过程中，且作用域为存储过程
DECLARE var_name[, varname]...date_type[DEFAULT value];

# 定义一个名称为 myvariable 的变量，类型为 INT 类型，默认值为 100
DECLARE my_name INT DEFAULT 100;  # 没有在存储过程中

DELIMITER //
CREATE PROCEDURE proc2() 
BEGIN
	DECLARE myvariable INT DEFAULT 100;
	SELECT myvariable;
END //
DELIMITER ;

¶6.3.2 修改一个变量的值

# 方式一：
SET var_name = expr[, var_name = expr]...;

DELIMITER //
CREATE PROCEDURE proc2() 
BEGIN
	DECLARE myvariable INT DEFAULT 100;
	SET myvariable = myvariavle + 20;
	SELECT myvariable;
END //
DELIMITER ;

# 方式二：
SELECT col_name[...] INTO var_name[...] table_expr;

# 定义变量，保存学生表中查询到的班级信息和性别信息
DELIMITER //
CREATE PROCEDURE proc2() 
BEGIN
	DECLARE s_grade FLOAT;
	DECLARE s_gender CHAR(2);
	SELECT grade, gender INTO s_grade, s_gender FROM student WHERE id = 3;
	SELECT s_grade, s_gender;
END //
DELIMITER ;

¶6.3.3 定义条件和处理程序

# 定义一个 ERROR1148(42000)错误，名称为 command_not_allowed

DECLARE command_not_allowed CONDITION FOR SQLSTATE 42000;

DECLARE command_not_allowed CONDITION FOR 1148;

# 定义处理程序
DECLARE handler_type HANDLER FRO condition_value[,...] sp_statement
# handler_type:
CONTINUE, EXIT, UNDO
#condition_value:
	SQLSTATE[VALUE]
	condition_name
	SQLWARNING   #匹配所有01开头的SQLSTATE错误代码
	NOT FOUND    #匹配所有02开头的SQLSTATE错误代码
	SQLEXCEPTION #其他
	mysql_error_code

¶6.3.4 光标（游标）的使用

## 光标的声明 ##
DECLARE cursor_name CURSOR FOR select_statement
# 声明一个名为 cursor_student 的光标
DECLARE cursor_student CURSOR FOR SELECT a_name, a_gender FROM student;

## 光标的使用 ##
# 先打开光标
	 OPEN cursor_name;
	 FETCH cursor_name INTO var_name[...]... # 这里的变量要在游标之前定义好。

# 使用名称为 cursor_student 的光标。将查询出来的信息存入 s_name, s_grade中
FETCH cursor_student INTO s_name, s_grade;

## 光标的关闭 ##
CLOSE cursor_name;
# 关闭名称为 cursor_student 的光标。
CLOSE cursor_student;

¶6.3.5 流程控制的使用

¶6.3.5.1 if 语句

# 1. if 语句
IF expr_condition THEN statemen_list
	[ELSEIF expr_condition THEN statement_list]..
	[ELSE statement_list]
END IF

# 创建一个存储过程判断变量 v 是否是一个 null,输出对应的提示信息

DELIMITER //
CREATE PROCEDURE proc4()
BEGIN
	DECLARE v VARCHAR(2);
	SET v = 'a';
	IF V IS NULL THEN SELECT 'IS NULL';
	ELSE SELECT 'NOT NULL';
	END IF;
END //
DELIMITER ;

¶6.3.5.2 case 语句

# 2. case 语句
CASE case_expr
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statemen_list]...
	[ELSE statement_list]
END CASE;

# 创建存储过程
DELIMITER //
CREATE PROCEDURE proc5()
BEGIN
	DECLARE v INT;
	SET v = 1;
	CASE v
		WHEN 1 THEN SELECT 'VALUE IS 1';
		WHEN 2 THEN SELECT 'VALUE IS 1';
		ELSE SELECT 'IS NOT 1 OR 2';
	END CASE;
END //
DELIMITER ;

¶6.3.5.3 loop 语句

# 3. loop 语句
[loop_lable:] LOOP STATEMENT_LIST END [loop_lable]

DELIMITER //
CREATE PROCEDURE proc6()
BEGIN
	DECLARE id INT DEFAULT 0;
	add_loop : LOOP
		SET id = id + 1;
		IF id >= 10 THEN LEAVE add_loop; // 跳出循环
		END IF;
		SELECT id;
	END LOOP add_loop;
END //
DELIMITER ;

LEAVE # 跳出循环  break
ITERATE # 再次循环 contionue

# 案例
DELIMITER //
CREATE PROCEDURE doiterate()
BEGIN
	DECLARE p1 INT DEFAULT 0;
	my_loop : LOOP
		SET p1 = p1 + 1;
		IF p1 < 10 THEN ITERATE my_loop;
		ELSE IF p1 > 20 THEN LEAVE my_loop;
		END IF;
		SELECT 'p1 is between 10 and 20';
	END LOOP my_loop;
END //
DELIMITER ;

¶6.3.5.4 repeat 语句 while 语句

# 案例 repeat
DELIMITER //
CREATE PROCEDURE proc7()
BEGIN
	DECLARE p1 INT DEFAULT 0;
	REPEAT
		SET p1 = p1 + 1;
		UNTIL p1 >= 10;
	END REPEAT;
END //
DELIMITER ;


# 案例 while
DELIMITER //
CREATE PROCEDURE proc8()
BEGIN
	DECLARE p1 INT DEFAULT 0;
	WHILE P1 < 10 DO
		SET p1 = p1 + 1;
		SELECT p1;
	END WHILE;
END //
DELIMITER ;

¶6.4 操作存储过程

¶6.4.1 执行存储过程

CALL SP_NAME(parameter...);

# 案例
DELIMITER //
CREATE PROCEDURE proc01(IN s_gender VARCHAR(50), OUT num INT)
BEGIN
	SELECT COUNT(*) INTO num FROM student WHERE gender = s_gender;
END //
DELIMITER ;

# 执行存储过程
CALL proc01("f",@num);
# 查看 num
SELECT @num

¶6.4.2 查看存储过程

SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE '%2';
SHOW CREATE PROCEDURE PROC4;
# 查询 information_schema.routines 表中查看存储过程的信息
SELECT * FROM information_schema.Routines；

¶6.5 修改存储过程

1 2	# 可以修改存储过程的特性，但是不能修改存储过程的内容 # 如果要修改内容，先删除在重新创建

¶6.6 删除存储过程

1	DROP PROCEDURE sp_name;

¶7 视图

¶7.1 创建视图

¶7.1.1 在单表上创建视图

# 创建单表 in chapter07 数据库

CREATE TABLE student(
	s_id INT(3),
    name VARCHAR(20),
    math FLOAT,
    chinese FLOAT
);
INSERT INTO student VALUES(1, 'TOM', 80, 78),(2, 'JACK', 70, 80),(3, 'LUCY', 97, 95);
#+------+------+------+---------+
#| s_id | name | math | chinese |
#+------+------+------+---------+
#|    1 | TOM  |   80 |      78 |
#|    2 | JACK |   70 |      80 |
#|    3 | LUCY |   97 |      95 |
#+------+------+------+---------+

# 创建 student 表的视图，保存数学成绩，语文成绩，总成绩
CREATE VIEW view_stu AS SELECT math, chinese FROM student;
# 可替代
CREATE (OR REPLEASE) VIEW view_stu AS SELECT math, chinese, math + chinese FROM student;
SELECT * FORM view_stu;

# 添加别名 (可选列名)
CREATE VIEW view_stu2 (数学,语文,总分) AS SELECT math, chinese, math + chinese FROM student;
SELECT * FroM view_stu2;

7.1.2 在多表上创建视图

# 在创建一张表格
CREATE TABLE stu_info(
	s_id INT(3),
    class VARCHAR(50),
	addr VARCHAR(50)
);
INSERT INTO stu_info VALUES(1, '#2', '202'),(2, '#3', '203'),(3, '#1', '201');

#+------+-------+------+
#| s_id | class | addr |
#+------+-------+------+
#|    1 | #2    | 202  |
#|    2 | #3    | 203  |
#|    3 | #1    | 201  |
#+------+-------+------+

# 创建视图包含 学生的编号 学生的姓名 班级名称

CREATE VIEW stu_class (编号, 学生, 班级) 
AS 
SELECT student.s_id,student.name,stu_info.class FROM student,stu_info WHERE student.s_id = stu_info.s_id;

¶7.2 查看视图

# 使用 describe   --- 查看视图结构
DESCRIBE stu_class;
DESC stu_class;

# 使用 SHOW TABLE STATUS LIKE '视图名'  --- 查看视图信息
SHOW TABLE STATUS LIKE 'stu_class';

# 查看视图的创建语句    --- 查看视图的创建语句
SHOW CREATE VIEW stu_class;

¶7.3 修改视图

# 1. OR REPLACE 覆盖
# 修改 view_stu
CREATE OR REPLACE VIEW view_stu AS SELECT * FROM student;

# 2. 使用alter语句
ALTER VIEW view_stu AS SELECT Chinese FROM student;

¶7.4 更新视图

# 更新视图会导致基本表中的数据也会修改
# 使用 update
UPDATE view_stu SET chinese = 100;

# 变化基本表中的数据同样也会改变视图中的数据
# 使用 insert 语句
INSERT INTO student VALUES(4, 'LILY', 98, 99);

# 使用 delete
DELETE FROM view_stu WHERE chinese =  99;

¶7.5 删除视图

1	DROP VIEW view_name;

¶8 数据库的高级操作

¶8.1 数据备份与还原

¶8.1.1 数据的备份

1 2	# 命令行内 mysqldump -u root -p mydb1 > d:\1.sql

¶8.1.2 数据的还原

# 方法一   --- 命令行内
mysql -u root -p mydb1 < d:\1.sql  # 前提是先创建空数据库 mydb1

# 方法二   --- mysql内
CREATE DATABASE mydb1;
USE mydb1;
SOURCE D:\1.sql  # 载入数据

¶8.2 用户管理

¶8.2.1 user 表

¶8.2.1 创建普通用户

# grant 语句
# 对于 localhost 主机，创建用户 USER01，选择 mydb1内的所有所有数据，密码为123456
GRANT SELECT ON mydb1.* TO 'USER01'@localhost IDENTIFIED BY 'abc123';

# create 语句
CREATE USER 'USER02'@localhost IDENTIFIED BY 'abc123';

# insert 语句
INSERT INTO mysql.user(Host, User, Password, ssl_cipher, x509issuer, x509_subject)
VALUES(local, 'USER03', PASSWORD('abc123'),'','','');
FLUSH PRIVILEGES;  # 刷新权限

¶8.2.1 删除普通用户

# drop 语句
DROP USER 'USER02'@localhost;

# delete 语句
DELETE FROM mysql.user WHERE Host = 'localhost' AND User = 'USER02';
FLUSH PRIVILEGES;  # 刷新权限

¶8.2.1 修改用户密码

# 1. 修改root用户密码
# 命令行
mysqladmin -u root -p password rootroot;

# update 语句
UPDATE mysql.user SET PASSWORD = PASSWORD('rootroot')
WHERE User = 'username' AND Host = 'hostname';
FLUSH PRIVILEGES;

# SET 语句 必须 root 用户本身
SET PASSWORD = PASSWORD('rootroot');

# 2. root 用户修改普通用户密码
# grant 语句
# update 语句
# set 语句

#3. 普通用户修改自己密码
# set 语句
SET PASSWORD = PASSWORD('password');

¶8.3 权限管理

¶8.3.1 Mysql 的权限

¶8.3.2 授予权限

1 2	# 赋予 insert select 权限给 user04 ，密码为 123 GRANT INSERT,SELECT ON . TO 'USER04'@localhost IDENTIFIED BY '123' WITH GRANT OPTION;

¶8.3.3 查看权限

1	SHOW GRANTS FOR 'root'@localhost;

¶8.3.4 收回权限

1 2	REVOKE insert on . FROM 'USER04'@localhost; # 回收 user04 的 insert 权限 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USER04'@localhost; # 回收 user04 的所有权限