# MySQL Cheat Sheet

## 基础命令

**登录**：`mysql -uroot -p[密码]`

**退出**：`exit`、`quit`

**查看数据库列表**：`show databases;`

**使用某个数据库**：`use [数据库名];`

**查看当前使用的数据库名**：`select database();`

**创建数据库**：`create database [数据库名];`

**删除数据库：**`drop database [数据库名];`

**查看数据库表名**：`show tables;`

**执行SQL脚本**：use 某个数据库时`source [不带中文的路径]`，Windows可能要用两个反斜杠

**查看表所有数据**：`select * from [表名];`

**查看表结构**：`desc [表名];`

**查看 MySQL 版本**：`select version();`

> **Windows中打开服务**：`net start mysql80`
>
> **Windows中关闭服务**：`net stop mysql80`
>
> 注意这两个命令是在cmd里面输入的，mysql80是安装MySQL在windows服务界面配置的服务名，默认mysql80
>
> 除了命令行，还可以通过Windows的`服务`手动打开和关闭

## 其他命令

* `SHOW STATUS;` 显示广泛服务器状态信息
* `SHOW GRANTS;` 显示授权用户的安全权限
* `SHOW ERRORS;`服务器错误信息
* `SHOW WARNINGS;`服务器警告信息
* `show engines \G`查看存储引擎
* `show create table [表名];`查看创建语句：

**导出数据（退出MySQL到命令行）：**

```bash
mysqldump [数据库名] > [路径] -uroot -p[密码];
```

**例子：**

```bash
mysqldump bj > ~/Desktop/bj.sql -uroot -p[密码]
```

```bash
mysqldump --single-transaction -u［用户名］ -p［密码］［数据库名］ >［导出文件名］.sql
```

\--single-transaction选项： 该选项适用于 InnoDB 引擎的表，可以在导出时启动一个事务，从而避免锁表。使用该选项，mysqldump 会在导出过程中保持数据的一致性，而不需要锁定表，这样其他进程仍然可以对数据库进行读写操作

在目标服务器上，使用 mysql 客户端来导入数据库。首先需要创建数据库（如果它还不存在），然后导入

```bash
mysql -u username -p -e "CREATE DATABASE IF NOT EXISTS database_name"
mysql -u username -p database_name < database_dump.sql
```

## Select

### 简单查询

**查询多个字段**：`select [字段名],[字段名]... from [表名];`

**查询所有字段**：`select * from [表名];`

**列起别名**：

`select [字段名] as [别名] from [表名];`

`select [字段名] [别名] from [表名];`

`select [字段名] ‘[别名]’ from [表名];`（别名是中文或带空格的方案）

**字段可以带有数学表达式**，例如：`select ENAME, SAL*12 'YEAR SALE' from emp;`

### 条件查询

**格式**：

```sql
select
	[字段名1], [字段名2], [字段名3]...
from
	[表名]
where
	[条件];
```

**例子**：

```sql
 select empno, ename from emp where sal = 800;
 
 select ename, sal, deptno from emp where sal > 2000 and (deptno = 10 or deptno = 20);
 
 select ename, job from emp where job in ('manager', 'salesman');
 
 select ename from emp where ename like '%o%';
```

**条件**：

* 大小关系：`=, !=, <, >, <>`
* 两个值之间（包含）：`between [值1] and [值2]`
* 判断为空：`is null`和`is not null`
* 并且：`and`
* 或：`or` （and 优先级比 or 高，加括号改变优先级）
* 是否包含：`in ([值1], [值2]...)`和`not in ([值1], [值2]...)`
* 模糊查询：`like [值]`（% 代表 0 到任意多个字符， \_ 代表任意一个字符，注意 \ 转义）

### 多情况讨论

**格式：**

```sql
select
	[字段名1], [字段名2], [字段名3]...
	
case [目标字段名]
    when
        [值1]
    then 
        [对应处理]
    when
        [值2]
    then 
        [对应处理]
    else
        [对应处理]
end

from
	[表名];
```

**例子：**

```sql
select ename, job, sal oldsal, (case job when 'manager' then sal * 1.1 when 'salesman' then sal * 1.3 else sal end) as newsal from emp;
```

### 子查询

**语法：**

在 `select`, `from`, `where` 子句中嵌套查询

**例子：**

```sql
select ename, sal 
from emp 
where sal > (
    select min(sal) 
    from emp
);
```

```sql
select t.job, t.avgsal, s.grade
from (select job, avg(sal) avgsal from emp group by job) t
join salgrade s
on t.avgsal between s.losal and s.hisal;
```

```sql
select e.ename, (select d.dname from dept d where e.deptno = d.deptno) dname from emp e;
```

### 正则表达式

```sql
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)';
```

```
+----------------+
| prod_name      |
+----------------+
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
```

* \\\ 代表转义
* \[0-9] 匹配 0～9 的任意一个字符
* ? 代表其前一个字符出现1次或0次

```sql
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]';
```

```
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
```

* 此处 ^ 代表匹配串的开始

### union

**语法：**

```sql
[查询语句]
union
[查询语句]
```

*注：合理运用 union, 查询次数可以优于 join （例如多表连接）*

*注：要求结果的列数和数据类型相同*

**例子：**

```sql
select ename , job from emp where job = 'manager'
union
select ename , job from emp where job = 'salesman';
```

* UNION 自动去除重复的行，使用 UNION ALL 包含重复的行
* UNION 中若要排序，只允许出现一次，放在最后的 SELECT 语句后

### 单行处理函数

* 转大小写：`lower(字段名)`和`upper(字段名)`
* 提取子串：`substr(字段名, 起始下标, 截取长度)` *注：起始下标从 1 开始*
* 拼接成字符串：`concat(字段名1, 字段名2)`
* 长度：`length(字段名)`
* 去空格：`trim(字段名)`
* 四舍五入：`round(字段名, 小数位)`
* 生成随机数：`rand()`
* 处理 NULL：`ifnull(字段名, 被当做的值)`
* 条件表达式：`if(条件，为真的值，为假的值)`
* `ABS()`: 返回一个数的绝对值。
* `CEILING()`: 返回大于或等于给定数值的最小整数。
* `FLOOR()`: 返回小于或等于给定数值的最大整数。
* `ROUND()`: 四舍五入到指定的小数位数。
* `MOD()`: 返回除法操作的余数。
* `FORMAT()`: 格式化数字为格式化的字符串，通常用于货币显示。

**例子：**

```sql
select lower(ename) as ename from emp;

select concat(upper(substr(ename, 1, 1)), lower(substr(ename, 2, length(ename) - 1))) as name from emp;

select ename from emp where ename = trim('  KING ');

select round(rand() * 1000, -1) as num from emp;

select ename, (sal + ifnull(comm, 0)) * 12 yearsal from emp;
```

### 限制结果条数

**语法：**

```sql
[其他查询语句]
limit [起始下标], [长度];
```

\*\*例子：\*\*偏移量是从 0 开始的，即第一条记录的偏移量是 0；如果想从第 3 条记录开始，偏移量就应该是 2。

```sql
SELECT * FROM table_name LIMIT 2, 8;
```

## 排序

**格式**：

```sql
select
	[字段名1], [字段名2], [字段名3]...
from
	[表名]
order by
	[规则];
```

**例子：**

```sql
select ename, sal from emp order by sal asc, ename asc;

select ename, sal from emp order by 2;

select ename, sal from emp where sal between 1000 and 2000 order by sal;
```

**规则：**

* 默认升序：`[字段名]`
* 指定降序：`[字段名] desc`
* 指定升序：`[字段名] asc`
* 多个字段排序：`[字段名1] [排序方式], [字段名2] [排序方式]...`（靠前的规则优先）
* 根据查询结果的字段位置排序：`[数字]`

## 分组

**格式：**

```sql
select
	[分组字段名1], [分组字段名2] ... [分组函数1], [分组函数2]...
from
	[表名]
where
	[不带分组函数的条件]
group by
	[分组字段名1], [分组字段名2]...
having
	[带分组函数的条件]
order by
	[规则];
```

**例子：**

```sql
select job, sum(sal) from emp group by job;

select job, deptno, max(sal) from emp group by job, deptno;

select deptno, max(sal) from emp group by deptno having max(sal) > 2000 order by max(sal);

select deptno, count(distinct job) from emp group by deptno;
```

**分组函数（多行处理函数）：**

* 最大最小：`max(字段名)`和`min(字段名)`
* 求和：`sum(字段名)`，可以加入判断例如 `sum(a.id<10)`
* 平均：`avg(字段名)`
* 数量：`count(字段名)` 计算不为 null 的数量，`count(*)` 包括 null 的数量

> 对于判断，例如 `a.id<10`，返回的是0或1，对于 count，因为不为 null 都计数，所以`count(a.id<10)`没有意义，无论0或者1都计数，对于sum函数可以累加返回1的个数

**关键字补充**

* 去重：`distinct`（放在所有字段前，后面字段联合去重）
* WITH ROLLUP 在分组中使用，获得汇总值

```sql
SELECT vend_id, COUNT(*) AS num 
FROM products 
GROUP BY vend_id WITH ROLLUP;
```

```
+---------+-----+
| vend_id | num |
+---------+-----+
|    1001 |   3 |
|    1002 |   2 |
|    1003 |   7 |
|    1005 |   2 |
|    NULL |  14 |
+---------+-----+
```

## join

### 内连接

**SQL99语法：**

```sql
select
	[字段名1], [字段名2]...
from
	[表名A]
inner join
	[表名B]
on
	[表连接条件];
```

*注：A B 两表没有主次关系，只看表连接条件，inner 可省略*

**例子：**

```sql
select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;

select e.ename, d.dname from emp e inner join dept d on e.deptno = d.deptno;
 
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

select a.ename employee, b.ename employer from emp a join emp b on a.mgr = b.empno;
```

### 外连接

**语法：**

```sql
select
	[字段名1], [字段名2]...
from
	[表名A]
right outer join  -- left outer join
	[表名B]
on
	[表连接条件];
```

*注：right 意思是表 B 为主表，left 意思是表 A 为主表，outer 可省略*

**例子：**

```sql
select e.ename, d.dname from emp e right join dept d on e.deptno = d.deptno;

select a.ename employee, b.ename employer from emp a left join emp b on a.mgr = b.empno;
```

### 自联结

**例：寻找与 prod\_id = 'DTNTR' 产品相同供应商的产品**

```sql
SELECT p1.prod_id, p1.prod_name
FROM products p1
join products p2
on p2.prod_id = 'DTNTR' AND p1.vend_id = p2.vend_id; 
```

### 自然连接

不重要的概念，指联结后不出现重复的列

```sql
SELECT c.cust_id, c.cust_name, o.order_num, oi.prod_id
FROM customers AS c
JOIN orders AS o
ON c.cust_id = o.cust_id
JOIN orderitems AS oi
ON oi.order_num = o.order_num AND oi.prod_id = 'FB';
```

```
+---------+-------------+-----------+---------+
| cust_id | cust_name   | order_num | prod_id |
+---------+-------------+-----------+---------+
|   10001 | Coyote Inc. |     20005 | FB      |
|   10001 | Coyote Inc. |     20009 | FB      |
+---------+-------------+-----------+---------+
```

## DQL 语句总结

**格式：**

```sql
select
	[分组字段名1], [分组字段名2] ... [分组函数1], [分组函数2]...
from
	[表名]
where
	[不带分组函数的条件]
group by
	[分组字段名1], [分组字段名2]...
having
	[带分组函数的条件]
order by
	[规则]
limit 
	[起始下标], [长度];
```

**执行顺序：**

1. from
2. where
3. group by
4. having
5. select
6. order by
7. limit

**例子：每个部门工资最多的人**

```sql
select e.ename, t.*
from emp e
join (select max(sal) maxsal, deptno from emp group by deptno) t
on e.sal = t.maxsal and e.deptno = t.deptno;
```

```
+-------+---------+--------+
| ename | maxsal  | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+
```

## 修改表结构

创建表

```sql
create table [表名](
  [字段名1] [数据类型] default [默认值],
  [字段名2] [数据类型] ...,
  [字段名3] [数据类型] ...,
  ......
);
```

*注：default \[默认值] 可省略*

删表

```sql
drop table [表名];

drop table if exists [表名];
```

**数据类型：**

* varchar 可变长度字符串（最长255）
* char 定长字符串（最长255）
* int
* bigint 长整型
* float
* double 短日期
* datetime 长日期，年月日时分秒
* date 短日期，年月日
* clob 字符大对象，可存 4G 字符串
* blob 二进制大对象，例如媒体数据

**例子：**

```sql
create table t_student(
	nob int,
  name varchar(32),
  sex char(1) default 'm',
  age int(4),
  email varchar(255)
);

create table emp2 as select ename, job from emp where job = 'manager';
```

**添加列：**

```sql
ALTER TABLE vendors
ADD vend_phone CHAR(20);
```

**删除列：**

```sql
ALTER TABLE vendors
DROP COLUMN vend_phone;
```

**添加约束：**

```sql
# 来自 create.sql

ALTER TABLE orderitems 
ADD CONSTRAINT fk_orderitems_orders 
FOREIGN KEY (order_num) REFERENCES orders (order_num);
```

**重命名表**

```sql
RENAME TABLE [旧表] TO [新表名];
```

### 约束

* 非空约束 ：`not null`
* 唯一性约束（NULL不算）： `unique`
* 联合唯一：`unique([字段名1],[字段名2]...)`
* 主键约束（非空，唯一）：`primary key`
* 复合主键（不建议使用）：`primary key([字段名1],[字段名2]...)`
* 设置自然主键：`auto_increment`
* 外键约束：`foreign key([子表字段名]) references [父表名]([父表unique字段名])`

**例子：**

```sql
create table t_vip (
	id int primary key auto_increment,
	name varchar(32) not null,
	email varchar(255),f
	unique(name, email)
);
```

```sql
create table t_class (
	classno int primary key,
  classname varchar(255)
);

create table t_student(
	id int primary key auto_increment,
  name varchar(255),
  classno int,
  foreign key(classno) references t_class(classno)
);

insert into t_class values (100, 'class one'), (101, 'class two');

insert into t_student(name, classno) 
values ('jk', 100), ('jjkk', 101), ('ab', 100), ('cd', 101), ('ps', 100), ('ed', 101);

select s.*, t.classname from t_student s join t_class t where s.classno = t.classno;
```

### 隔离

**查看隔离级别：**

```sql
select @@transaction_isolation;
```

**改变隔离级别：**

```sql
set global transaction isolation level [隔离级别];
```

**隔离级别：**

* `read uncommitted`：可以查到未提交的数据
* `read committed`：查已提交的数据
* `repeatable read`：只要另一方开启事务就不会查到对应数据
* `serializable`：非并行，排队

### 索引

**查看索引：**

```sql
explain [查询语句]
```

**添加索引：**

```sql
create index [索引名] on [表名]([字段名], [字段名]...);
```

*注：主键与 unique 自动添加索引*

**删除索引：**

```sql
drop index [索引名] on [表名];
```

*注：索引可以缩短查询时间，但模糊查询，类型转换，or条件，索引字段参与运算或函数等等情况会使其失效*

## insert 插入数据

**格式：**

```sql
insert into 
	[表名]([字段名1], [字段名2], [字段名3]...) 
values 
	([数据], [数据], [数据]...),
	([数据], [数据], [数据]...),
	([数据], [数据], [数据]...)
	...;
```

*注：(\[字段名1], \[字段名2], \[字段名3]...) 可省略，values 要填写全部信息*

**例子：**

```sql
insert into t_student(nob, name, sex, age, email) 
values (1, 'jk', 'f', 18, 'jk@666.com');

insert into t_student(name, nob) values ('Abe', 2);

insert into t_student values (3, 'jjkk', 'f', 19, 'jjkk@666.com');

insert into t_student(nob, name) values (4, 'a'), (5, 'b'), (6, 'c');

insert into emp2 select ename, job from emp where job = 'salesman';
```

## 日期操作

**字符串转date：**

```sql
str_to_date([字符串日期], [日期格式])
```

*注：如果日期格式为'%Y-%m-%d'，不用函数也能将字符串转date*

**date转字符串：**

```sql
date_format([日期], [格式])
```

*注：默认日期格式为'%Y-%m-%d'* *注：如果是datetime，默认日期格式为'%Y-%m-%d %h:%i:%s'*

**获取当前时间：**

```sql
now()
```

| %Y | %m | %d | %h | %i | %s |
| -- | -- | -- | -- | -- | -- |
| 年  | 月  | 日  | 时  | 分  | 秒  |

**例子：**

```sql
create table t_user(
	id int(11),
  name varchar(32),
  birth date,
  create_time datetime
);

insert into t_user(id, name, birth)
values (1, 'jk',str_to_date('2000-12-03', '%Y-%m-%d'),now());

insert into t_user values (2, 'jjkk','2002-09-12','2022-07-28 13:13:13');

select name, date_format(birth, '%d\%m\%Y') birth from t_user;
```

| CurDate() | CurTime() | Now()     |
| --------- | --------- | --------- |
| 返回当前日期    | 返回当前时间    | 返回当前日期和时间 |

| Date() | Time() | Year() | Month() | Day() | Hour() | Minute() | Second() |
| ------ | ------ | ------ | ------- | ----- | ------ | -------- | -------- |
| 返回日期部分 | 返回时间部分 | 返回年部分  | 类比      | 类比    | 类比     | 类比       | 类比       |

**例子：**

```sql
SELECT order_num, order_date
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

SELECT order_num, order_date
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
```

## update 修改数据

**格式：**

```sql
update 
	[表名]
set
	[字段名1] = [数据],
	[字段名2] = [数据],
	[字段名3] = [数据],
	......
where
	[条件];
```

**例子：**

```sql
update t_user set name = 'Abe' where id = 2;
```

## 删除表中数据

**格式：**

```sql
delete from [表名] where [条件]; 
```

```sql
truncate table [表名];
```

*注：delete支持回滚，速度慢。truncate 物理删除，速度快*

**例子：**

```sql
delete from t_user where id = 2; 

truncate table t_user;
```

| 区别   | delete               | truncate        | drop                      |
| ---- | -------------------- | --------------- | ------------------------- |
| 类型   | 属于 DML               | 属于 DDL          | 属于 DDL                    |
| 回滚   | 可回滚                  | 不可回滚            | 不可回滚                      |
| 删除内容 | 表结构还在，删除表的全部或者一部分数据行 | 表结构还在，删除表中的所有数据 | 从数据库中删除表，所有数据行，索引和权限也会被删除 |
| 删除速度 | 删除速度慢，需要逐行删除         | 删除速度快           | 删除速度最快                    |

因此，在不再需要一张表的时候，用 drop；在想删除部分数据行时候，用 delete；在保留表而删除所有数据的时候用 truncate

## 视图

**创建视图：**

```sql
create view [视图名] as [查询语句];
```

**删除视图：**

```sql
drop view [视图名]
```

*注：对视图的增删改查格式不变，对视图的改变会影响原表*

**例子：**

```sql
create view emp_dept_view as
select e.ename, e.sal, d.dname
from emp e
join dept d
on e.deptno = d.deptno;
```

## 全文本搜索

开启全文本搜索支持

```sql
# 来自 create.sql

CREATE TABLE productnotes
(
  note_id    int           NOT NULL AUTO_INCREMENT,
  prod_id    char(10)      NOT NULL,
  note_date datetime       NOT NULL,
  note_text  text          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MyISAM;
```

全文本搜索为每一条数据给出一个匹配值，最匹配d的记录优先

```sql
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
```

使用查询扩展，即找出与搜索结果再相关的其他行

```sql
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
```

布尔文本搜索不需要建立 FULLTEXT 索引，更慢

**例子:**

```sql
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
```

* `-`代表排除后面这个词
* `*`代表词尾通配符

```sql
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);
```

* `""`将双引号内部的词视为一整个短语

```sql
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
```

* `+`代表一定有这个词
* `()`包含一个子表达式
* `<`降低这个词的等级

## 存储过程

* 存储过程可以封装一系列复杂的操作
* 有输入输出
* 形式类似于函数

创建存储过程

**例子1：**

```sql
DELIMITER //

CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END //

DELIMITER ;
```

* `DELIMITER`重新定义语句分隔符
* `BEGIN`, `END` 包含存储过程体
* 存储过程名为 `productpricing()`，这个例子没有参数

**例子2:**

```sql
DELIMITER //

CREATE PROCEDURE productpricing2 (
    OUT pl DECIMAL(8, 2),
    OUT ph DECIMAL(8, 2),
    OUT pa DECIMAL(8, 2)
)
BEGIN
    SELECT Min(prod_price)
    INTO pl
    FROM products;
    SELECT Max(prod_price)
    INTO ph
    FROM products;
    SELECT Avg(prod_price)
    INTO pa
    FROM products;
END //

DELIMITER ;
```

* 这个存储过程有三个函数
* `OUT`代表可以从存储过程传出
* `pl`等等为形参名
* `DECIMAL`是一种数据类型，精度可变的浮点值

**例子3:**

```sql
DELIMITER //

CREATE PROCEDURE ordertotal(
    IN  onumber INT,
    OUT ototal DECIMAL(8, 2)
)
BEGIN
    SELECT Sum(item_price * quantity)
    FROM orderitems
    WHERE onumber = order_num
    INTO ototal;
END //

DELIMITER ;
```

* `IN` 代表传入参数

**例子4:**

```sql
DELIMITER //

-- Name : ordertotal2
-- Parameters : onumber = order number
--              taxable = 0 if not taxable, 1 if taxable
--              ototal = order total if variable

CREATE PROCEDURE ordertotal2(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8, 2)
) COMMENT 'Obtain order total, optionally add tax'
BEGIN
    
    DECLARE total DECIMAL(8, 2);
    DECLARE taxrate INT DEFAULT 6;

    SELECT Sum(item_price * quantity)
    FROM orderitems
    WHERE onumber = order_num
    INTO total;
    
    IF taxable THEN
        SELECT total + (total / 100 * taxrate) INTO total;
    END IF;
    
    SELECT total INTO ototal;
    
END //

DELIMITER ;
```

* `--`表示注释
* `DECLARE`声明变量
* `IF`,`END IF`包含分支语句
* `COMMENT`内容非必要，将在`SHOW PROCEDURE STATUS`展示

使用存储过程

**例子1：**

```sql
CALL productpricing();
```

```
+--------------+
| priceaverage |
+--------------+
|    16.133571 |
+--------------+
```

**例子2:**

```sql
CALL productpricing2(
    @pricelow,
    @pricehigh,
    @priceaverage
);

SELECT @pricelow, @pricehigh, @priceaverage;
```

```
+-----------+------------+---------------+
| @pricelow | @pricehigh | @priceaverage |
+-----------+------------+---------------+
|      2.50 |      55.00 |         16.13 |
+-----------+------------+---------------+
```

* MySQL 变量名由`@`开始

**例子3:**

```sql
CALL ordertotal(20005, @total);

SELECT @total;
```

```
+--------+
| @total |
+--------+
| 149.87 |
+--------+
```

**例子4:**

```sql
CALL ordertotal2(20005, 1, @total);

SELECT @total;
```

```
+--------+
| @total |
+--------+
| 158.86 |
+--------+
```

删除存储过程

```sql
DROP PROCEDURE productpricing;
```

检查存储过程

```sql
SHOW CREATE PROCEDURE ordertotal;

SHOW PROCEDURE STATUS;

SHOW PROCEDURE STATUS LIKE 'ordertotal2';
```

## 游标

***

* 通常在存储过程中使用
* 表示一个`SELECT`语句，一次取一条数据，可用于遍历

```sql
DELIMITER //

CREATE PROCEDURE processorders()
BEGIN
    
    DECLARE done BOOLEAN DEFAULT 0; -- 遍历结束条件
    DECLARE o INT;
    DECLARE t DECIMAL(8, 2);
    
    -- 定义游标
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    -- 遍历结束条件,出现SQLSTATE为'02000'时,即未找到,done = 1
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    
    DROP TABLE IF EXISTS ordertotals;
    -- 存结果的表
    CREATE TABLE IF NOT EXISTS ordertotals(
        order_num INT,
        total DECIMAL(8, 2)
    );
    
    -- 打开游标才能使用
    OPEN ordernumbers;
    
    -- 循环体，开始
    REPEAT
        FETCH ordernumbers INTO o;-- 取一条记录
        CALL ordertotal2(o, 1, t);
        INSERT INTO ordertotals(order_num, total) VALUES(o, t);
    UNTIL done END REPEAT;
    -- 循环体，结束
    
    -- 关闭游标，释放资源
    CLOSE ordernumbers;
    
END //

DELIMITER ;
```

```sql
CALL processorders();
SELECT * FROM ordertotals;
```

```
+-----------+---------+
| order_num | total   |
+-----------+---------+
|     20005 |  158.86 |
|     20009 |   40.78 |
|     20006 |   58.30 |
|     20007 | 1060.00 |
|     20008 |  132.50 |
|     20008 |  132.50 |
+-----------+---------+
```

## 触发器

***

* 在对表内容修改前或后自动触发一些操作
* 支持语句`INSERT`,`DELETE`,`UPDATE`
* 使用`BEFORE`,`AFTER`来表示修改后或修改前
* `BEFORE`可以用于验证数据
* 如果触发器或语句出错，后续语句和触发器不会执行
* 合理运用触发器，可以保证数据一致性，追踪保留更改

INSERT 触发器

```sql
# 书上例子
# 执行报错：ERROR 1415 (0A000): Not allowed to return a result set from a trigger
# 新版本不支持这种写法。触发器不允许出现SELECT *的形式
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

# 修改版
# 结果放进变量
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num INTO @ordernumber;
```

* 创建了名为`neworder`的触发器
* 在 `INSERT orders`之后触发
* `FOR EACH ROW`代表每一行都触发
* `NEW`是一个虚拟表，可以获取新插入的内容

```sql
INSERT INTO orders(order_date, cust_id)
VALUES (Now(), 10001);

SELECT @ordernumber;
```

```
+--------------+
| @ordernumber |
+--------------+
|        20010 |
+--------------+
```

DELETE 触发器

```sql
CREATE TABLE archive_orders # 存档表
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;


DELIMITER //

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO archive_orders(order_num, order_date, cust_id)
    VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END //

DELIMITER ;
```

* `OLD`是一个虚拟表，可以访问要删除的信息
* `OLD`只读

```sql
DELETE FROM orders WHERE order_num = 20010;

SELECT * FROM archive_orders;
```

```
+-----------+---------------------+---------+
| order_num | order_date          | cust_id |
+-----------+---------------------+---------+
|     20010 | 2022-08-03 15:32:25 |   10001 |
+-----------+---------------------+---------+
```

UPDATE 触发器

```sql
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
```

* `NEW`虚拟表可以修改，也修改即将更新的内容
* `OLD`只读

删除触发器

```sql
DROP TRIGGER updatevendor;
```

## 事务

**关闭自动提交机制：**`start transaction;`

**回滚：**`rollback;`

**提交：**`commit;`

保留点：

* 保留点在实务中设置
* 如果不显式释放保留点，则在事务完成后自动释放

**创建保留点**

```sql
SAVEPOINT delete1;
```

**退回到保留点**

```sql
ROLLBACK TO delete1;
```

**释放保留点**

```sql
RELEASE SAVEPOINT delete1;
```

**不自动提交更改**

```sql
SET autocommit = 0; # 针对链接而不是服务器
```

## 用户管理

创建用户

```sql
CREATE USER ben IDENTIFIED BY 'p@$$word';
```

更改口令

```sql
SET PASSWORD FOR ben = 'n3w p@$$word';

SET PASSWORD = 'n3w p@$$word'; # 更改自己口令
```

查看用户

```sql
USE mysql;
SELECT user FROM user;
```

```
+------------------+
| user             |
+------------------+
| ben              |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
```

重命名用户

```sql
RENAME USER ben TO QMS;
```

删除用户

```sql
DROP USER QMS;
```

查看权限

```sql
SHOW GRANTS FOR ben;
```

```
+---------------------------------+
| Grants for ben@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `ben`@`%` |
+---------------------------------+
```

* `USAGE`代表没有权限

授予权限

```sql
GRANT [权限] ON [数据库名].[表名] TO [用户名];
```

* 具体到权限表见书

**例子：**

```sql
GRANT SELECT ON cc.* TO ben; # cc 就是 crashcourse
```

```
mysql> SHOW GRANTS FOR ben;
+-------------------------------------+
| Grants for ben@%                    |
+-------------------------------------+
| GRANT USAGE ON *.* TO `ben`@`%`     |
| GRANT SELECT ON `cc`.* TO `ben`@`%` |
+-------------------------------------+
```

撤销权限

```sql
REVOKE SELECT ON cc.* FROM ben;
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://qmmms.gitbook.io/note/dev_tools/qms_07_mysql1.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.