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到命令行）：

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

例子：

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

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

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

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

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;

条件查询

格式：

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

例子：

 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 到任意多个字符， _ 代表任意一个字符，注意 \ 转义）

多情况讨论

格式：

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

from
	[表名];

例子：

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 子句中嵌套查询

例子：

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

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;

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

正则表达式

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次

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

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

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

union

语法：

[查询语句]
union
[查询语句]

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

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

例子：

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(): 格式化数字为格式化的字符串，通常用于货币显示。

例子：

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;

限制结果条数

语法：

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

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

SELECT * FROM table_name LIMIT 2, 8;

排序

格式：

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

例子：

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] [排序方式]...（靠前的规则优先）

• 根据查询结果的字段位置排序：[数字]

分组

格式：

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

例子：

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 在分组中使用，获得汇总值

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语法：

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

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

例子：

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;

外连接

语法：

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

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

例子：

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' 产品相同供应商的产品

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; 

自然连接

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

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 语句总结

格式：

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

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

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 |
+-------+---------+--------+

修改表结构

创建表

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

注：default [默认值] 可省略

删表

drop table [表名];

drop table if exists [表名];

数据类型：

• varchar 可变长度字符串（最长255）

• char 定长字符串（最长255）

• int

• bigint 长整型

• float

• double 短日期

• datetime 长日期，年月日时分秒

• date 短日期，年月日

• clob 字符大对象，可存 4G 字符串

• blob 二进制大对象，例如媒体数据

例子：

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';

添加列：

ALTER TABLE vendors
ADD vend_phone CHAR(20);

删除列：

ALTER TABLE vendors
DROP COLUMN vend_phone;

添加约束：

# 来自 create.sql

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

重命名表

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

约束

• 非空约束 ：not null

• 唯一性约束（NULL不算）： unique

• 联合唯一：unique([字段名1],[字段名2]...)

• 主键约束（非空，唯一）：primary key

• 复合主键（不建议使用）：primary key([字段名1],[字段名2]...)

• 设置自然主键：auto_increment

• 外键约束：foreign key([子表字段名]) references [父表名]([父表unique字段名])

例子：

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

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;

隔离

查看隔离级别：

select @@transaction_isolation;

改变隔离级别：

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

隔离级别：

• read uncommitted：可以查到未提交的数据

• read committed：查已提交的数据

• repeatable read：只要另一方开启事务就不会查到对应数据

• serializable：非并行，排队

索引

查看索引：

explain [查询语句]

添加索引：

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

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

删除索引：

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

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

insert 插入数据

格式：

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

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

例子：

insert into t_student(nob, name, sex, age, email) 
values (1, 'jk', 'f', 18, '[e-mail]');

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

insert into t_student values (3, 'jjkk', 'f', 19, '[e-mail]');

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：

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

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

date转字符串：

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

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

获取当前时间：

now()

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

例子：

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()
返回日期部分	返回时间部分	返回年部分	类比	类比	类比	类比	类比

例子：

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 修改数据

格式：

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

例子：

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

删除表中数据

格式：

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

truncate table [表名];

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

例子：

delete from t_user where id = 2; 

truncate table t_user;

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

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

视图

创建视图：

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

删除视图：

drop view [视图名]

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

例子：

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

全文本搜索

开启全文本搜索支持

# 来自 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的记录优先

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

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

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

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

例子:

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

• -代表排除后面这个词

• *代表词尾通配符

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

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

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

• +代表一定有这个词

• ()包含一个子表达式

• <降低这个词的等级

存储过程

• 存储过程可以封装一系列复杂的操作

• 有输入输出

• 形式类似于函数

创建存储过程

例子1：

DELIMITER //

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

DELIMITER ;

• DELIMITER重新定义语句分隔符

• BEGIN, END 包含存储过程体

• 存储过程名为 productpricing()，这个例子没有参数

例子2:

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:

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:

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：

CALL productpricing();

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

例子2:

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

SELECT @pricelow, @pricehigh, @priceaverage;

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

• MySQL 变量名由@开始

例子3:

CALL ordertotal(20005, @total);

SELECT @total;

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

例子4:

CALL ordertotal2(20005, 1, @total);

SELECT @total;

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

删除存储过程

DROP PROCEDURE productpricing;

检查存储过程

SHOW CREATE PROCEDURE ordertotal;

SHOW PROCEDURE STATUS;

SHOW PROCEDURE STATUS LIKE 'ordertotal2';

游标

---

• 通常在存储过程中使用

• 表示一个SELECT语句，一次取一条数据，可用于遍历

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 ;

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 触发器

# 书上例子
# 执行报错：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是一个虚拟表，可以获取新插入的内容

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

SELECT @ordernumber;

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

DELETE 触发器

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只读

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 触发器

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

• NEW虚拟表可以修改，也修改即将更新的内容

• OLD只读

删除触发器

DROP TRIGGER updatevendor;

事务

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

回滚：rollback;

提交：commit;

保留点：

• 保留点在实务中设置

• 如果不显式释放保留点，则在事务完成后自动释放

创建保留点

SAVEPOINT delete1;

退回到保留点

ROLLBACK TO delete1;

释放保留点

RELEASE SAVEPOINT delete1;

不自动提交更改

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

用户管理

创建用户

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

更改口令

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

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

查看用户

USE mysql;
SELECT user FROM user;

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

重命名用户

RENAME USER ben TO QMS;

删除用户

DROP USER QMS;

查看权限

SHOW GRANTS FOR ben;

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

• USAGE代表没有权限

授予权限

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

• 具体到权限表见书

例子：

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`@`%` |
+-------------------------------------+

撤销权限

REVOKE SELECT ON cc.* FROM ben;