【极客时间】MySQL 必知必会-20230901_geekhour的mysql

03 | 表：怎么创建和修改数据表？

新增数据表

CREATE DATABASE demo;

CREATE TABLE goodsmaster (barcode TEXT,goodsname TEXT,price DOUBLE, itemnumber INT PRIMARY KEY AUTO_INCREMENT);

INSERT INTO demo.goodsmaster (barcode, goodsname,price) VALUES ('002','笔',0.44);

SELECT * from demo.goodsmaster;

SELECT SUM(price)
FROM demo.goodsmaster;
1
2
3
4
5
6
7
8
9
10

修改数据表

ALTER TABLE demo.goodsmaster
MODIFY COLUMN price DECIMAL(5,2);


CREATE TABLE demo.inporthead
(listnumber INT,supplierid INT,stocknumber INT,importtype INT DEFAULT 1,quantity DECIMAL(10,3),
importvalue DECIMAL(10,2),record INT,recordingdate DATETIME);

INSERT INTO demo.inporthead
(listnumber,supplierid,stocknumber,quantity,importvalue,recorder,recordingdate)
VALUES(3456,1,1,10,100,1,'2020-12-10');

INSERT INTO demo.goodsmaster
(itemnumber,barcode,goodsname,specification,unit,price)
VALUES(100,'0003','测试1','','个',10);

INSERT INTO demo.goodsmaster
(barcode,goodsname,specification,unit,price)
VALUES('0003','测试1','','个',10);

CREATE TABLE demo.importheadhist
LIKE demo.importhead;

ALTER TABLE demo.importheadhist  ADD confirmer INT;
ALTER TABLE importheadhist ADD confirmdate DATETIME;

DESCRIBE demo.importheadhist;

ALTER TABLE demo.importheadhist CHANGE quantity importquantity DOUBLE;

ALTER TABLE demo.importheadhist MODIFY importquantity DECIMAL(10,3);

ALTER TABLE demo.importheadhist ADD suppliername TEXT AFTER supplierid;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

总结

CREATE TABLE
(
字段名 字段类型 PRIMARY KEY
);
CREATE TABLE
(
字段名 字段类型 NOT NULL
);
CREATE TABLE
(
字段名 字段类型 UNIQUE
);
CREATE TABLE
(
字段名 字段类型 DEFAULT 值
);
-- 这里要注意自增类型的条件，字段类型必须是整数类型。
CREATE TABLE
(
字段名 字段类型 AUTO_INCREMENT
);
-- 在一个已经存在的表基础上，创建一个新表
CREATE TABLE demo.importheadhist LIKE demo.importhead;
-- 修改表的相关语句
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;
ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 FIRST|AFTER 字段名;
ALTER TABLE 表名 MODIFY 字段名 字段类型 FIRST|AFTER 字段名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

04 | 增删改查：如何操作表中的数据？

删

DELETE
FROM demo.goodsmaster
WHERE itemnumber=5;
1
2
3

增

INSERT INTO 表名 （字段名）
SELECT 字段名或值
FROM 表名
WHERE 条件

INSERT INTO importhead_copy (listnumber,supplierid,stocknumber,importtype,quantity,importvalue,recorder,recordingdate)
SELECT listnumber,supplierid,stocknumber,importtype,quantity,importvalue,recorder,recordingdate
FROM importhead
WHERE recordingdate = '2023-03-03 00:00:00';
DESCRIBE demo.importhead_copy;
1
2
3
4
5

DELETE FROM 表名WHERE 条件

DELETE FROM demo.goodsmaster;

DELETE FROM demo.goodsmaster
WHERE itemnumber > 1;
1
2
3
4

改

UPDATE 表名
SET 字段名=值
WHERE 条件

update importhead_copy 
SET listnumber=100
WHERE supplierid = 1;
1
2
3

查

SELECT *|字段列表
FROM 数据源
WHERE 条件
GROUP BY 字段
HAVING 条件
ORDER BY 字段
LIMIT 起始点，行数

SELECT * FROM demo.goodsmaster
ORDER BY barcode ASC,price DESC;
1
2

SELECT * FROM demo.goodsmaster
ORDER BY barcode ASC,price DESC
LIMIT 1,2;

INSERT INTO demo.goodsmaster SELECT *FROM demo.goodsmaster1 as aON DUPLICATE KEY UPDATE barcode = a.barcode,goodsname=a.goodsname;

复习

INSERT INTO 表名 [(字段名 [,字段名] ...)] VALUES (值的列表);
 
INSERT INTO 表名 （字段名）
SELECT 字段名或值
FROM 表名
WHERE 条件
 
DELETE FROM 表名
WHERE 条件
 
UPDATE 表名
SET 字段名=值
WHERE 条件

SELECT *|字段列表
FROM 数据源
WHERE 条件
GROUP BY 字段
HAVING 条件
ORDER BY 字段
LIMIT 起始点，行数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

05 | 主键：如何正确设置主键？

设定主键

CREATE TABLE demo.membermaster
(
cardno CHAR(8) PRIMARY KEY,
membername TEXT,
memberphone TEXT,
memberid TEXT,
memberaddress TEXT,
sex TEXT,
birthday DATETIME
);


insert into demo.membermaster
(cardno,membername,memberphone,memberid,memberaddress,sex,birthday)
VALUES('10000001','张三','13620888888','110123200001017890','北京','男','2000-01-01');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

关联查询

SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate
FROM demo.trans AS a
JOIN demo.membermaster AS b
JOIN demo.goodsmaster AS c
ON (a.cardno = b.cardno AND a.itemnumber = c.itemnumber);

1
2
3
4
5
6

更新表格信息

UPDATE demo.membermaster
SET membername = '王五',
memberphone = '13698765432',
memberid = '475145197001012356',
memberaddress='天津',
sex='女',
birthday= '1970-01-01'
WHERE cardno = '10000001'

1
2
3
4
5
6
7
8
9

修改主键

ALTER TABLE demo.membermaster
DROP PRIMARY KEY

ALTER TABLE demo.membermaster
ADD id INT PRIMARY KEY AUTO_INCREMENT;

ALTER TABLE demo.trans
ADD memberid INT;

UPDATE demo.trans AS a,demo.membermaster AS b
SET a.memberid=b.id
WHERE a.transactionno > 0 
AND a.cardno = b.cardno;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

关联查询2

SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate
FROM demo.trans AS a
JOIN demo.membermaster AS b
JOIN demo.goodsmaster AS c
ON (a.memberid = b.id AND a.itemnumber = c.itemnumber);
1
2
3
4
5

06 | 外键和连接：如何做关联查询？

复习

-- 定义外键约束：
CREATE TABLE 从表名
(
字段 字段类型
....
CONSTRAINT 外键约束名称
FOREIGN KEY (字段名) REFERENCES 主表名 (字段名称)
);
ALTER TABLE 从表名 ADD CONSTRAINT 约束名 FOREIGN KEY 字段名 REFERENCES 主表名 （字段名）;

-- 连接查询
SELECT 字段名
FROM 表名 AS a
JOIN 表名 AS b
ON (a.字段名称=b.字段名称);
 
SELECT 字段名
FROM 表名 AS a
LEFT JOIN 表名 AS b
ON (a.字段名称=b.字段名称);
 
SELECT 字段名
FROM 表名 AS a
RIGHT JOIN 表名 AS b
ON (a.字段名称=b.字段名称);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

建立主从表

CREATE TABLE demo.importhead (
    listnumber INT PRIMARY KEY,
    supplierid INT,
    stocknumber INT,
    importtype INT,
    importquantity DECIMAL(10 , 3 ),
    importvalue DECIMAL(10 , 2 ),
    recorder INT,
    recordingdate DATETIME
);


CREATE TABLE demo.importdetails
(
  listnumber INT,
  itemnumber INT,
  quantity DECIMAL(10,3),
  importprice DECIMAL(10,2),
  importvalue DECIMAL(10,2),
  -- 定义外键约束，指出外键字段和参照的主表字段
  CONSTRAINT fk_importdetails_importhead
  FOREIGN KEY (listnumber) REFERENCES importhead (listnumber)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

内连接

SELECT a.transactionno,a.itemnumber,a.quantity,a.price,a.transdate,b.membername
FROM demo.trans AS a
JOIN  demo.membermaster AS b ON (a.cardno = b.cardno);
1
2
3

左链接

SELECT 
    a.transactionno,
    a.itemnumber,
    a.quantity,
    a.price,
    a.transdate,
    b.membername
FROM demo.trans AS a
LEFT JOIN demo.membermaster AS b -- LEFT JOIN，以demo.transaction为主
ON (a.cardno = b.cardno);
1
2
3
4
5
6
7
8
9
10

右链接

SELECT 
    a.transactionno,
    a.itemnumber,
    a.quantity,
    a.price,
    a.transdate,
    b.membername
FROM
    demo.membermaster AS b
        RIGHT JOIN
    demo.trans AS a ON (a.cardno = b.cardno); -- RIGHT JOIN，顺序颠倒了，还是以demo.trans为主
1
2
3
4
5
6
7
8
9
10
11

我的实践

• 左链接查询在职人员的passlist通过情况

SELECT 
    a.emplid,
    a.name_a,
    a.deptid,
    b.FUNCTION_NAME,
    b.PASS_ALL
FROM
    demo.season_people AS a
LEFT JOIN
    demo.season_passlist AS b ON (a.emplid = b.MPLOYEE_ID);
1
2
3
4
5
6
7
8
9
10

• 内链接查询工号一致的通过情况

SELECT 
    a.emplid,
    a.deptid,
    b.FUNCTION_NAME,
    b.PASS_ALL
FROM
    demo.season_people AS a
JOIN
    demo.season_passlist AS b ON (a.emplid = b.MPLOYEE_ID);
1
2
3
4
5
6
7
8
9

• 左链接查询三表

SELECT 
    a.emplid,
    a.deptid,
    b.FUNCTION_NAME,
    b.PASS_ALL,
    c.course,
    c.present,
    c.score
FROM
    demo.season_people AS a
LEFT JOIN
    demo.season_passlist AS b ON (a.emplid = b.MPLOYEE_ID)
LEFT JOIN
	 demo.season_training AS c ON (a.emplid = c.employid)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

• 增加where条件

SELECT 
    a.emplid,
    a.deptid,
    b.FUNCTION_NAME,
    b.PASS_ALL,
    c.course,
    c.present,
    c.score
FROM
    demo.season_people AS a
LEFT JOIN
    demo.season_passlist AS b ON (a.emplid = b.MPLOYEE_ID)
LEFT JOIN
	 demo.season_training AS c ON (a.emplid = c.employid)
WHERE b.PASS_ALL = 'Y'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 增加group by条件，看起来会丢失明细数据，需要匹配聚合信息。

SELECT 
    a.*,
    COUNT(a.emplid)
FROM
    demo.season_people AS a
GROUP BY a.deptid
1
2
3
4
5
6

07 | 条件语句：WHERE 与 HAVING有什么不同?

WHERE

SELECT DISTINCT b.goodsname
FROM demo.transactiondetails AS a
JOIN demo.goodsmaster AS b
ON (a.itemnumber = b.itemnumber)
WHERE a.salesvalue > 50;

GROUP BY

• 多表交叉

SELECT 
    a.transdate,
    b.operatorname,
    d.goodsname,
    c.quantity,
    c.price,
    c.salesvalue
FROM
    demo.transactionhead AS a
JOIN
    demo.operator AS b ON (a.operatorid = b.operatorid)
JOIN
	 demo.transactiondetails AS c ON (a.transactionid = c.transactionid)

JOIN
	 demo.goodsmaster AS d ON (d.itemnumber = c.itemnumber);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• 如果我想看看每天的销售数量和销售金额，可以按照一个字段“transdate”对数据进行分组和统计：

SELECT 
    a.transdate,
    sum(b.quantity),
    sum(b.salesvalue)
FROM
    demo.transactionhead AS a
JOIN
	 demo.transactiondetails AS b ON (a.transactionid = b.transactionid)
GROUP BY a.transdate
1
2
3
4
5
6
7
8
9

• 如果我想看每天、每个收银员的销售数量和销售金额，就可以按 2 个字段进行分组和统计，分别是“transdate”和“operatorname”：

SELECT 
    a.transdate,
    c.operatorname,
    sum(b.quantity),
    sum(b.salesvalue)
FROM
    demo.transactionhead AS a
JOIN
	 demo.transactiondetails AS b ON (a.transactionid = b.transactionid)
JOIN
	 demo.operator AS c ON (a.operatorid = c.operatorid)
GROUP BY a.transdate
1
2
3
4
5
6
7
8
9
10
11
12

HAVING

SELECT DISTINCT b.goodsname
FROM demo.transactiondetails AS a
JOIN demo.goodsmaster AS b
ON (a.itemnumber = b.itemnumber)
GROUP BY b.goodsname
HAVING MAX(a.salesvalue)>50;
1
2
3
4
5
6

区别

第一个区别是，如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。
第二个区别是，WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

假如超市经营者提出，要查询一下是哪个收银员、在哪天卖了 2 单商品。这种必须先分组才能筛选的查询，用 WHERE 语句实现就比较难，我们可能要分好几步，通过把中间结果存储起来，才能搞定。但是用 HAVING，则很轻松，代码如下：

SELECT a.transdate,count(b.itemnumber),c.operatorname
FROM demo.transactionhead AS a
JOIN demo.transactiondetails AS b
ON (a.transactionid = b.transactionid)
JOIN demo.operator AS c
ON (a.operatorid = c.operatorid)
GROUP BY a.transdate
HAVING COUNT(b.itemnumber)=2;
1
2
3
4
5
6
7
8

WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。举个例子，假设现在我们有一组销售数据，包括交易时间、收银员、商品名称、销售数量、价格和销售金额等信息，超市的经营者要查询“2020-12-10”和“2020-12-11”这两天收银金额超过 100 元的销售日期、收银员名称、销售数量和销售金额。

SELECT a.transdate,c.operatorname,d.goodsname,b.quantity,b.price,b.salesvalue
FROM demo.transactionhead AS a
JOIN demo.transactiondetails AS b
ON (a.transactionid = b.transactionid)
JOIN demo.operator AS c
ON (a.operatorid = c.operatorid)
JOIN demo.goodsmaster AS d
ON (b.itemnumber = d.itemnumber)
WHERE a.transdate IN ('2020-12-10','2020-12-11')
GROUP BY a.transdate,c.operatorname
HAVING SUM(b.salesvalue)>100;

1
2
3
4
5
6
7
8
9
10
11
12

我的试验

查询PASS_ALL 为Y，比部门代码来分组。

SELECT a.deptid,COUNT(b.MPLOYEE_ID)
FROM demo.season_people AS a
JOIN demo.season_passlist AS b
ON (a.emplid = b.MPLOYEE_ID)


WHERE a.deptid IN ('MZH710','MZH720','MZH740') AND b.PASS_ALL IN ('Y')
GROUP BY a.deptid
HAVING COUNT(b.MPLOYEE_ID)=1;
1
2
3
4
5
6
7
8
9

08 | 聚合函数：怎么高效地进行分组统计？

聚合函数

• SUM()、AVG()、MAX()、MIN() 、 COUNT()、LEFT()、ORDER BY()
  MySQL 中有 5 种聚合函数较为常用，分别是求和函数 SUM()、求平均函数 AVG()、最大值函数 MAX()、最小值函数 MIN() 和计数函数 COUNT()。

SELECT LEFT(a.transdate,10),c.goodsname,sum(b.quantity),sum(b.salesvalue)
FROM demo.transactionhead AS a
JOIN demo.transactiondetails AS b ON (a.transactionid = b.transactionid)
JOIN demo.goodsmaster AS c ON (b.itemnumber = c.itemnumber)
GROUP BY LEFT(a.transdate,10),c.goodsname
order BY LEFT(a.transdate,10),c.goodsname
1
2
3
4
5
6

LEFT

LEFT(str，n)：表示返回字符串 str 最左边的 n 个字符。我们这里的 LEFT（a.transdate,10），表示返回交易时间字符串最左边的 10 个字符。在 MySQL 中，DATETIME 类型的默认格式是：YYYY-MM-DD，也就是说，年份 4 个字符，之后是“-”，然后是月份 2 个字符，之后又是“-”，然后是日 2 个字符，所以完整的年月日是 10 个字符。用户要求按照日期统计，所以，我们需要从日期时间数据中，把年月日的部分截取出来。

COUNT

要计算记录数，就要用到 COUNT() 函数了。这个函数有两种情况。COUNT（*）：统计一共有多少条记录；COUNT（字段）：统计有多少个不为空的字段值。

SELECT COUNT(*) FROM demo.operator
1

SELECT COUNT(operatorname) FROM demo.operator
1

思考题

如果用户想要查询一下，在商品信息表中，到底是哪种商品的商品名称有重复，分别重复了几次，该如何查询呢？

SELECT c.goodsname,COUNT(*)
FROM demo.transactionhead AS a
JOIN demo.transactiondetails AS b ON (a.transactionid = b.transactionid)
JOIN demo.goodsmaster AS c ON (b.itemnumber = c.itemnumber)
GROUP BY c.goodsname
HAVING COUNT(c.salesprice)>1
1
2
3
4
5
6

09 | 时间函数：时间类数据，MySQL是怎么处理的？

EXTRACT（）

SELECT EXTRACT(hour FROM b.transdate) AS 时段,SUM(a.quantity),SUM(a.salesvalue)
FROM demo.transactiondetails AS a
JOIN demo.transactionhead AS b ON(a.transactionid = b.transactionid)
GROUP BY EXTRACT(HOUR FROM b.transdate)
ORDER BY EXTRACT(HOUR FROM b.transdate)
1
2
3
4
5

HOUR（）

YEAR（date）：获取 date 中的年。
MONTH（date）：获取 date 中的月。
DAY（date）：获取 date 中的日。
HOUR（date）：获取 date 中的小时。
MINUTE（date）：获取 date 中的分。
SECOND（date）：获取 date 中的秒。

SELECT EXTRACT(hour FROM b.transdate) AS 时段,SUM(a.quantity),SUM(a.salesvalue)
FROM demo.transactiondetails AS a
JOIN demo.transactionhead AS b ON(a.transactionid = b.transactionid)
GROUP BY hour(b.transdate)
ORDER BY hour(b.transdate)
1
2
3
4
5

DATE_ADD() 和 LAST_DAY()

# 2019/12/10
#SELECT DATE_ADD('2020-12-10',INTERVAL - 1 YEAR);

# 2019/11/10
#SELECT date_add(DATE_ADD('2020-12-10',INTERVAL - 1 YEAR),INTERVAL - 1 MONTH);

# 2019/11/30
#SELECT last_day(date_add(DATE_ADD('2020-12-10',INTERVAL - 1 YEAR),INTERVAL - 1 MONTH));


# 2019/12/01
#SELECT date_add(last_day(date_add(DATE_ADD('2020-12-10',INTERVAL - 1 YEAR),INTERVAL - 1 MONTH)),INTERVAL 1 DAY);

# 2019/12/31
SELECT last_day(date_add(last_day(date_add(DATE_ADD('2020-12-10',INTERVAL - 1 YEAR),INTERVAL - 1 MONTH)),INTERVAL 1 DAY));

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

除了 DATE_ADD()，ADDDATE()、DATE_SUB() 和 SUBDATE() 也能达到同样的效果。ADDDATE()：跟 DATE_ADD() 用法一致；DATE_SUB()，SUBDATE()：与 DATE_ADD() 用法类似，方向相反，执行日期的减操作。

CURDATE（）和 DAYOFWEEK（date）

CURDATE（）：获取当前的日期。日期格式为“YYYY-MM-DD”，也就是年月日的格式。DAYOFWEEK（date）：获取日期“date”是周几。1 表示周日，2 表示周一，以此类推，直到 7 表示周六。

SELECT dayofweek(CURDATE())
1

DATE_FORMAT()

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

SELECT DATE_FORMAT('2021-02-01 13:25:50',"%T")
1

SELECT DATE_FORMAT('2021-02-01 13:25:50',"%r")
1

DATEDIFF（date1,date2）

SELECT DATEDIFF('2021-02-01','2020-12-01')
1

CASE 函数（难）

CASE 表达式 WHEN 值1 THEN 表达式1 [ WHEN 值2 THEN 表达式2] ELSE 表达式m END
1

假设今天是 2021 年 02 月 06 日，通过下面的代码，我们就可以查到今天商品的全部折后价格了：

SELECT CURDATE() AS 日期,
case DAYOFWEEK(CURDATE())-1 when 0 then 7 else DAYOFWEEK(CURDATE())-1 end AS 周几,
a.goodsname AS 商品名称,
a.salesprice AS 价格,
IFNULL(b.discountrate,1) AS 折扣率,
a.salesprice*IFNULL(b.discountrate,1) AS 折后价格
FROM demo.goodsmaster AS a
left JOIN demo.discountrule AS b ON(a.itemnumber = b.itemnumber)
AND CASE DAYOFWEEK(CURDATE()) - 1 WHEN 0 THEN 7 ELSE DAYOFWEEK(CURDATE()) - 1 END = b.weekday;
1
2
3
4
5
6
7
8
9

IFNULL()

IFNULL(b.discountrate, 1)取不到值就默认为1

复习

10 | 如何进行数学计算、字符串处理和条件判断？

数学函数

向上取整 CEIL(X) 和 CEILING(X)：返回大于等于 X 的最小 INT 型整数。
向下取整 FLOOR(X)：返回小于等于 X 的最大 INT 型整数。
舍入函数 ROUND(X,D)：X 表示要处理的数，D 表示保留的小数位数，处理的方式是四舍五入。ROUND(X) 表示保留 0 位小数。

积分的规则也很简单，就是消费一元积一分，不满一元不积分，那我们就需要对销售金额的数值进行取整。

首先，我们要通过关联查询，获得会员消费的相关信息：

SELECT c.membername AS '会员',
a.transactionid AS '单号',
b.transdate AS '交易时间',
d.goodsname AS '商品名称',
a.salesvalue  AS '交易金额'
FROM demo.transactiondetails AS a
JOIN demo.transactionhead AS b ON (a.transactionid = b.transactionid)
JOIN demo.membermaster AS c ON (b.memberid = c.memberid)
JOIN demo.goodsmaster AS d ON (a.itemnumber = d.itemnumber)
1
2
3
4
5
6
7
8
9

FLOOR() , CEIL()

接着，我们用 FLOOR（a.salesvalue），对销售金额向下取整，获取会员积分值，代码如下：

SELECT c.membername AS '会员',
a.transactionid AS '单号',
b.transdate AS '交易时间',
d.goodsname AS '商品名称',
a.salesvalue  AS '交易金额',
floor(a.salesvalue) AS '积分'
FROM demo.transactiondetails AS a
JOIN demo.transactionhead AS b ON (a.transactionid = b.transactionid)
JOIN demo.membermaster AS c ON (b.memberid = c.memberid)
JOIN demo.goodsmaster AS d ON (a.itemnumber = d.itemnumber)
1
2
3
4
5
6
7
8
9
10

如果用户的积分规则改为“不满一元积一分”，其实就是对金额数值向上取整，这个时候，我们就可以用 CEIL() 函数。操作方法和前面是一样的，我就不具体解释了。

ROUND()

如果要精确到角，可以设置保留 1 位小数：

SELECT ROUND(salesvalue,1)
FROM demo.transactiondetails
WHERE transactionid = 1
1
2
3

比如说，ROUND（X）是对 X 小数部分四舍五入，那么在“五入”的时候，返回的值是不是一定比 X 大呢？其实不一定，因为当 X 为负数时，五入的值会更小。

ABS() , MOD()

MySQL 还支持绝对值函数 ABS（）和求余函数 MOD（），ABS（X）表示获取 X 的绝对值；MOD（X，Y）表示获取 X 被 Y 除后的余数。

字符串函数

CONCAT（s1,s2,…）：表示把字符串 s1、s2……拼接起来，组成一个字符串。
CAST（表达式 AS CHAR）：表示将表达式的值转换成字符串。
CHAR_LENGTH（字符串）：表示获取字符串的长度。
SPACE（n）：表示获取一个由 n 个空格组成的字符串。

SELECT
CONCAT(CAST(quantity AS CHAR),
SPACE(7 - CHAR_LENGTH(CAST(quantity AS CHAR)))) AS 数量
FROM demo.transactiondetails
WHERE transactionid = 1;
1
2
3
4
5

MySQL 还支持 SUBSTR（）、MID（）、TRIM（）、LTRIM（）、RTRIM（）。

条件判断函数

IFNULL（V1，V2）：表示如果 V1 的值不为空值，则返回 V1，否则返回 V2。
IF（表达式，V1，V2）：如果表达式为真（TRUE），则返回 V1，否则返回 V2。

IFNULL

我们希望规格是空的商品，拼接商品信息字符串的时候，规格不要是空。这个问题，可以通过 IFNULL(specification, ‘’) 函数来解决。具体点说就是，对字段“specification”是否为空进行判断，如果为空，就返回空字符串，否则就返回商品规格 specification 的值。

SELECT goodsname,specification,
CONCAT(goodsname,'(',IFNULL(specification,''),')')
FROM demo.goodsmaster
1
2
3

IF

商品名称后面的那个空括号“()”会让客人觉得奇怪，能不能去掉呢？

SELECT goodsname,specification,
IF(ISNULL(specification),goodsname,CONCAT(goodsname,'(',specification,')'))
FROM demo.goodsmaster
1
2
3

总结

11 | 索引：怎么提高查询的速度？

单字段索引

如何创建单字段索引？

直接给数据表创建索引

CREATE INDEX 索引名 ON TABLE 表名 (字段);
1

CREATE INDEX index_trans ON demo.trans (transdate)

SELECT quantity,price,transdate
FROM demo.trans
where transdate > '2023-12-11'
AND transdate < '2023-12-14'
1
2
3
4
5
6

创建表的同时创建索引

CREATE TABLE 表名
(
字段 数据类型,
….
{ INDEX | KEY } 索引名(字段)
)
1
2
3
4
5
6

修改表时创建索引

ALTER TABLE 表名 ADD { INDEX | KEY } 索引名 (字段);
1

EXPLAIN

EXPLAIN 关键字能够查看 SQL 语句的执行细节，包括表的加载顺序，表是如何连接的，以及索引使用情况等。

如何选择索引字段？

我建议你在选择索引字段的时候，要选择那些经常被用做筛选条件的字段。

组合索引

如果有多个索引，而这些索引的字段同时作为筛选字段出现在查询中的时候，MySQL 会选择使用最优的索引来执行查询操作。

如何创建组合索引？

跟创建单索引类似。
具体做法是，我们给销售流水表创建一个由 3 个字段 branchnumber、cashiernumber、itemnumber 组成的组合索引，如下所示：

CREATE INDEX Index_branchnumber_cashiernumber_itemnumber ON demo.trans (branchnumber,cashiernumber,itemnumber);
1

组合索引的原理

组合索引的多个字段是有序的，遵循左对齐的原则。比如我们创建的组合索引，排序的方式是 branchnumber、cashiernumber 和 itemnumber。因此，筛选的条件也要遵循从左向右的原则，如果中断，那么，断点后面的条件就没有办法利用索引了。
比如说我们刚才的条件，branchnumber = 11 AND cashiernumber = 1 AND itemnumber = 100，包含了从左到右的所有字段，所以可以最大限度使用全部组合索引。
假如把条件换成“cashiernumber = 1 AND itemnumber = 100”，由于我们的组合索引是按照 branchnumber、cashiernumber 和 itemnumber 的顺序建立的，最左边的字段 branchnumber 没有包含到条件当中，中断了，所以这个条件完全不能使用组合索引。
类似的，如果筛选的是一个范围，如果没有办法无法精确定位，也相当于中断。比如“branchnumber > 10 AND cashiernumber = 1 AND itemnumber = 100”这个条件，只能用到组合索引中 branchnumber>10 的部分，后面的索引就都用不上了。

删除索引

如果你要删除索引，就可以用：DROP INDEX 索引名 ON 表名;
当然， 有的索引不能用这种方法删除，比如主键索引，你就必须通过修改表来删除索引。语法如下：ALTER TABLE 表名 DROP PRIMARY KEY；

12 | 事务：怎么确保关联操作正确执行？

什么是事务？

带有ROLLBACK理论上就可以回退

START TRANSACTION 或者 BEGIN （开始事务）
一组DML语句
COMMIT（提交事务）
ROLLBACK（事务回滚）



START TRANSACTION;
INSERT INTO demo.mytrans VALUES(1,1,'abc');
UPDATE demo.inventory SET invquantity = invquantity - 5 ;
COMMIT;
rollback;

1
2
3
4
5
6
7
8
9
10
11
12
13

START TRANSACTION 和 BEGIN：表示开始事务，意思是通知 MySQL，后面的 DML 操作都是当前事务的一部分。
COMMIT：表示提交事务，意思是执行当前事务的全部操作，让数据更改永久有效。
ROLLBACK：表示回滚当前事务的操作，取消对数据的更改。

事务有 4 个主要特征

原子性：表示事务中的操作要么全部执行，要么全部不执行，像一个整体，不能从中间打断。
一致性：表示数据的完整性不会因为事务的执行而受到破坏。
隔离性：表示多个事务同时执行的时候，不互相干扰。不同的隔离级别，相互独立的程度不同。
持久性：表示事务对数据的修改是永久有效的，不会因为系统故障而失效。

原子性（需加强学习）

在这个存储过程中，我使用了“DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;”这个语句，来监控 SQL 语句的执行结果，一旦发发生错误，就自动回滚并退出。通过这个机制，我们就实现了对事务中的 SQL 操作进行监控，如果发现事务中的任何 SQL 操作发生错误，就自动回滚。

#DELIMITER ;
#CREATE PROCEDURE demo.test()
#BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
INSERT INTO demo.mytrans VALUES(1,1,5);
UPDATE demo.inventory SET invquantity = invquantity - 5 WHERE itemnumber = 1;
COMMIT;
end
1
2
3
4
5
6
7
8
9
10

我们可以通过 MySQL 的函数 ROW_COUNT() 的返回，来判断一个 DML 操作是否失败，-1 表示操作失败，否则就表示影响的记录数。

如何用好事务的隔离性？

MySQL 支持 4 种事务隔离等级。
READ UNCOMMITTED：可以读取事务中还未提交的被更改的数据。
READ COMMITTED：只能读取事务中已经提交的被更改的数据。
REPEATABLE READ：表示一个事务中，对一个数据读取的值，永远跟第一次读取的值一致，不受其他事务中数据操作的影响。这也是 MySQL 的默认选项。
SERIALIZABLE：表示任何一个事务，一旦对某一个数据进行了任何操作，那么，一直到这个事务结束，MySQL 都会把这个数据锁住，禁止其他事务对这个数据进行任何操作。
一般来讲，使用 MySQL 默认的隔离等级 REPEATABLE READ，就已经够了。不过，也不排除需要对一些关键的数据操作，使用最高的隔离等级 SERIALIZABLE。

13 | 临时表：复杂查询，如何保存中间结果？

临时表是什么？

临时表是一种特殊的表，用来存储查询的中间结果，并且会随着当前连接的结束而自动删除。
MySQL 中有 2 种临时表，分别是内部临时表和外部临时表：内部临时表主要用于性能优化，由系统自动产生，我们无法看到；外部临时表通过 SQL 语句创建，我们可以使用。

CREATE TEMPORARY TABLE 表名
(
字段名 字段类型,
...
);
1
2
3
4
5

跟普通表相比，临时表有 3 个不同的特征：临时表的创建语法需要用到关键字 TEMPORARY；临时表创建完成之后，只有当前连接可见，其他连接是看不到的，具有连接隔离性；临时表在当前连接结束之后，会被自动删除。

如何用临时表简化复杂查询？

1.查询出每个单品的销售数量和销售金额，并存入临时表：

CREATE TEMPORARY TABLE demo.mysales
SELECT itemnumber,
SUM(quantity) AS QUANTITY,
SUM(salesvalue) AS salesvalue
FROM demo.transactiondetails
GROUP BY itemnumber;

SELECT * FROM demo.mysales;
1
2
3
4
5
6
7
8

2.SQL 语句计算进货数据，并且保存在临时表里面：

CREATE TEMPORARY TABLE demo.myimport
SELECT b.itemnumber,SUM(b.quantity) AS quantity,SUM(b.importvalue) AS importvalue
FROM demo.importhead AS a JOIN demo.importdetails AS b ON (a.listnumber = b.listnumber)
GROUP BY b.itemnumber;

SELECT * FROM demo.myimport;
1
2
3
4
5
6

3.下面的 SQL 语句计算返厂信息，并且保存到临时表中。

CREATE TEMPORARY TABLE demo.myreturn
SELECT b.itemnumber,SUM(b.quantity) AS quantity,SUM(b.importvalue) AS importvalue
FROM demo.returnhead AS a JOIN demo.returndetails AS b ON (a.listnumber = b.listnumber)
GROUP BY b.itemnumber;

SELECT * FROM demo.myreturn;
1
2
3
4
5
6

4.把单品的销售信息、进货信息和返厂信息汇总到一起了。

CREATE TEMPORARY TABLE demo.mysales
SELECT itemnumber,
SUM(quantity) AS QUANTITY,
SUM(salesvalue) AS salesvalue
FROM demo.transactiondetails
GROUP BY itemnumber;

CREATE TEMPORARY TABLE demo.myimport
SELECT b.itemnumber,SUM(b.quantity) AS quantity,SUM(b.importvalue) AS importvalue
FROM demo.importhead AS a JOIN demo.importdetails AS b ON (a.listnumber = b.listnumber)
GROUP BY b.itemnumber;

CREATE TEMPORARY TABLE demo.myreturn
SELECT b.itemnumber,SUM(b.quantity) AS quantity,SUM(b.importvalue) AS importvalue
FROM demo.returnhead AS a JOIN demo.returndetails AS b ON (a.listnumber = b.listnumber)
GROUP BY b.itemnumber;


SELECT
a.itemnumber,
a.goodsname,
ifnull(b.quantity,0) as salesquantity,    -- 如果没有销售记录，销售数量设置为0
ifnull(c.quantity,0) as importquantity,   -- 如果没有进货，进货数量设为0
ifnull(d.quantity,0) as returnquantity    -- 如果没有返厂，返厂数量设为0
FROM demo.goodsmaster a               -- 商品信息表放在左边进行左连接，确保所有的商品都包含在结果集中
LEFT JOIN demo.mysales b ON (a.itemnumber=b.itemnumber)
LEFT JOIN demo.myimport c ON (a.itemnumber=c.itemnumber)
LEFT JOIN demo.myreturn d ON (a.itemnumber=d.itemnumber)
HAVING salesquantity>0 OR importquantity>0 OR returnquantity>0; -- 在结果集中剔除没有销售，没有进货，也没有返厂的商品
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

内存临时表和磁盘临时表

创建一个内存中的临时表：

CREATE TEMPORARY TABLE demo.mysales
ENGINE = MEMORY
SELECT itemnumber,
SUM(quantity) AS QUANTITY,
SUM(salesvalue) AS salesvalue
FROM demo.transactiondetails
GROUP BY itemnumber;
1
2
3
4
5
6
7

在磁盘上创建临时表（默认）

速度比较

我们向刚刚的两张表里都插入同样数量的记录，然后再分别做一个查询：

14 | 视图：如何简化查询？

视图的创建及其好处

视图的创建

CREATE VIEW demo.trans_goodmaster AS 
SELECT a.transdate,a.itemnumber,b.goodsname,SUM(a.salesquantity) AS quantity,SUM(a.salesvalue) AS salesvalue
FROM demo.trans AS a
JOIN demo.goodsmaster AS b ON (a.itemnumber = b.itemnumber)
GROUP BY a.transdate,a.itemnumber;
1
2
3
4
5

视图与其他表联合查询

SELECT a.transdate,a.itemnumber,a.goodsname,a.quantity,b.invquantity
FROM demo.trans_goodmaster AS a
JOIN demo.inventoryhist AS b ON (a.transdate = b.invdate and a.itemnumber = b.itemnumber)
1
2
3

如何操作视图？

如何操作视图中的数据？

1. 在视图中插入数据

只有视图中的字段跟实际数据表中的字段完全一样，MySQL 才允许通过视图插入数据。

insert into demo.trans_goodsmaster
VALUES(5,'0005','测试',100)
1
2

2. 删除视图中的数据

DELETE from demo.trans_goodsmaster
WHERE itemnumber = 5
1
2

3. 修改视图中的数据

update demo.trans_goodsmaster
SET salesprice = 100.11
WHERE itemnumber = 1

1
2
3
4

视图的优点

15 | 存储过程：如何提高程序的性能和安全性？

如何创建存储过程？

CREATE PROCEDURE 存储过程名 （[ IN | OUT | INOUT] 参数名称 类型）程序体
1

DELIMITER // -- 设置分割符为//
CREATE PROCEDURE demo.dailyoperation(transdate TEXT)
BEGIN -- 开始程序体
DECLARE startdate,enddate DATETIME; -- 定义变量
SET startdate = date_format(transdate,'%Y-%m-%d'); -- 给起始时间赋值
SET enddate = date_add(startdate,INTERVAL 1 DAY); -- 截止时间赋值为1天以后
-- 删除原有数据
DELETE FROM demo.dailystatistics
WHERE
salesdate = startdate;
-- 插入新计算的数据
INSERT into dailystatistics
(
salesdate,
itemnumber,
quantity,
actualvalue,
cost,
profit,
profitratio
)
SELECT
LEFT(b.transdate,10),
a.itemnumber,
SUM(a.quantity), -- 数量总计
SUM(a.salesvalue), -- 金额总计
SUM(a.quantity*c.avgimportprice), -- 计算成本
SUM(a.salesvalue-a.quantity*c.avgimportprice), -- 计算毛利
CASE sum(a.salesvalue) WHEN 0 THEN 0
ELSE round(sum(a.salesvalue-a.quantity*c.avgimportprice)/sum(a.salesvalue),4) END -- 计算毛利率
FROM
demo.transactiondetails AS a
JOIN
demo.transactionhead AS b
ON (a.transactionid = b.transactionid)
JOIN
demo.goodsmaster AS c
ON (a.itemnumber=c.itemnumber)
WHERE
b.transdate>startdate AND b.transdate<enddate
GROUP BY
LEFT(b.transdate,10),a.itemnumber
ORDER BY
LEFT(b.transdate,10),a.itemnumber;
END
//
DELIMITER ; -- 恢复分隔符为；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

存储过程的参数定义（难）

参数有 3 种，分别是 IN、OUT 和 INOUT。
IN 表示输入的参数，存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN，表示输入参数。
OUT 表示输出的参数，存储过程在执行的过程中，把某个计算结果值赋给这个参数，执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
INOUT 表示这个参数既可以作为输入参数，又可以作为输出参数使用。

如何查看存储过程？

SHOW CREATE PROCEDURE demo.dailyoperation;
1

如何调用存储过程？

CALL demo.dailyoperation('2020-12-01');
1

如何修改和删除存储过程？

删除存储过程

DROP PROCEDURE 存储过程名称;
1

思考题

请写一个简单的存储过程，要求是定义 2 个参数，一个输入参数 a，数据类型是 INT；另一个输出参数是 b，类型是 INT。程序体完成的操作是：b = a + 1。
1.定义存储过程

DELIMITER //
CREATE PROCEDURE demo.test(IN a INT,OUT b INT) 
BEGIN
	SET b = a + 1;
END
//
DELIMITER ;
1
2
3
4
5
6
7

2.调用存储过程

CALL demo.test(100,@b);
1

3.查看导出参数

SELECT @b
1

参数报错参考链接https://blog.csdn.net/qq_41490938/article/details/115585853

16 | 游标：对于数据集中的记录，该怎么逐条处理？（难）

游标

存储过程中使用游标的 4 个步骤，分别是定义游标、打开游标、读取游标数据和关闭游标。

DECLARE 游标名 CURSOR FOR 查询语句

OPEN 游标名称；

FETCH 游标名 INTO 变量列表；

CLOSE 游标名；
1
2
3
4
5
6
7

与游标结合使用的流程控制语句

与游标结合使用的流程控制语句，包括循环语句 LOOP、WHILE 和 REPEAT；条件判断语句 IF 和 CASE；还有跳转语句 LEAVE 和 ITERATE。

条件处理语句

DECLARE 处理方式 HANDLER FOR 问题 操作；
1

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
1

流程控制语句-跳转语句

ITERATE 语句：只能用在循环语句内，表示重新开始循环。
LEAVE 语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。

流程控制语句-循环语句

流程控制语句-条件判断语句

案例

要验收进货单，我们就需要对每一个进货商品进行两个操作：在现有库存数量的基础上，加上本次进货的数量；根据本次进货的价格、数量，现有商品的平均进价和库存，计算新的平均进价：（本次进货价格 * 本次进货数量 + 现有商品平均进价 * 现有商品库存）/（本次进货数量 + 现有库存数量）。

mysql> DELIMITER //
mysql> CREATE PROCEDURE demo.mytest(mylistnumber INT)
-> BEGIN
-> DECLARE mystockid INT;
-> DECLARE myitemnumber INT;
-> DECLARE myquantity DECIMAL(10,3);
-> DECLARE myprice DECIMAL(10,2);
-> DECLARE done INT DEFAULT FALSE; -- 用来控制循环结束
-> DECLARE cursor_importdata CURSOR FOR -- 定义游标
-> SELECT b.stockid,a.itemnumber,a.quantity,a.importprice
-> FROM demo.importdetails AS a
-> JOIN demo.importhead AS b
-> ON (a.listnumber=b.listnumber)
-> WHERE a.listnumber = mylistnumber;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 条件处理语句
->
-> OPEN cursor_importdata; -- 打开游标
-> FETCH cursor_importdata INTO mystockid,myitemnumber,myquantity,myprice; -- 读入第一条记录
-> REPEAT
-> -- 更新进价
-> UPDATE demo.goodsmaster AS a,demo.inventory AS b
-> SET a.avgimportprice = (a.avgimportprice*b.invquantity+myprice*myquantity)/(b.invquantity+myquantity)
-> WHERE a.itemnumber=b.itemnumber AND b.stockid=mystockid AND a.itemnumber=myitemnumber;
-> -- 更新库存
-> UPDATE demo.inventory
-> SET invquantity = invquantity + myquantity
-> WHERE stockid = mystockid AND itemnumber=myitemnumber;
-> -- 获取下一条记录
-> FETCH cursor_importdata INTO mystockid,myitemnumber,myquantity,myprice;
-> UNTIL done END REPEAT;
-> CLOSE cursor_importdata;
-> END
-> //
Query OK, 0 rows affected (0.02 sec)
-> DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

把 MySQL 的分隔符改成“//”。开始程序体之后，我定义了 4 个变量，分别是 mystockid、myitemnumber、myquantity 和 myprice，这几个变量的作用是，存储游标中读取的仓库编号、商品编号、进货数量和进货价格数据。定义游标。这里我指定了游标的名称，以及游标可以处理的数据集（mylistnumber 指定的进货单的全部进货商品明细数据）。定义条件处理语句“DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;”。打开游标，读入第一条记录，然后开始执行数据操作。关闭游标，结束程序。

思考题

你能自己写一个简单的存储过程，用游标来逐一处理一个数据表中的数据吗？要求：编号为偶数的记录，myquant=myquant+1；编号是奇数的记录，myquant=myquant+2。

DELIMITER //
CREATE PROCEDURE demo.myproc()
BEGIN
	DECLARE myid INT;
    DECLARE myq INT;
    DECLARE done INT DEFAULT FALSE;
	DECLARE cursor_test CURSOR FOR 
    SELECT * FROM demo.test;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
	OPEN cursor_test;
    FETCH cursor_test INTO myid,myq;
    REPEAT
		IF (myid MOD 2 = 0) THEN    -- 如果是偶数，加1
            UPDATE demo.test SET myquant = myquant + 1 WHERE id = myid;
		ELSE                                       -- 奇数加2
			UPDATE demo.test SET myquant = myquant + 2 WHERE id = myid;
		END IF;
        FETCH cursor_test INTO myid,myq;
	UNTIL done END REPEAT;
    CLOSE cursor_test;
END
//
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

17 | 触发器：如何让数据修改自动触发关联操作，确保数据一致性？

触发器的优缺点

首先，触发器可以确保数据的完整性。
其次，触发器可以帮助我们记录操作日志。
另外，触发器还可以用在操作数据前，对数据进行合法性检查。
触发器最大的一个问题就是可读性差。ERROR 1054 (42S22): Unknown column 'aa' in 'field list'

如何操作触发器

创建触发器的语法结构是
CREATE TRIGGER 触发器名称 {BEFORE|AFTER} {INSERT|UPDATE|DELETE}
ON 表名 FOR EACH ROW 表达式；

查看触发器的语句是：
SHOW TRIGGERS\G;

删除触发器的语法结构是：
DROP TRIGGER 触发器名称;
1
2
3
4
5
6
7
8
9

案例

会员信息表（demo.membermaster）

会员储值历史表（demo.deposithist）

定义trigger

DELIMITER //
CREATE TRIGGER demo.upd_membermaster BEFORE UPDATE  -- 在更新前触发
ON demo.membermaster
FOR EACH ROW                              -- 表示每更新一条记录，触发一次
BEGIN                                     -- 开始程序体
IF (new.memberdeposit <> old.memberdeposit)  -- 如果储值金额有变化
THEN
INSERT INTO demo.deposithist
(
memberid,
transdate,
oldvalue,
newvalue,
changedvalue
)
SELECT
NEW.memberid,
NOW(),
OLD.memberdeposit,                  -- 更新前的储值金额
NEW.memberdeposit,                  -- 更新后的储值金额
NEW.memberdeposit-OLD.memberdeposit; -- 储值金额变化值
END IF;
END
//
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

触发trigger

UPDATE demo.membermaster
SET memberdeposit = memberdeposit + 10
WHERE memberid = 1
1
2
3

18 | 权限管理：如何控制数据库访问，消除安全隐患？

1.操作权限

创建角色

	CREATE ROLE 角色名;
	CREATE ROLE 'manager'@'localhost';
		不写主机名 默认是通配符% 可以从任何主机登录
1
2
3

角色授权

	GRANT 权限 ON 表名 TO 角色名;
	GRANT SELECT,INSERT,DELETE,UPDATE ON demo.user TO 'manager';
1
2

查看角色权限

	SHOW GRANTS FOR 'manager';
1

删除角色

	DROP ROLE 角色名称;
1

2.操作用户

创建用户

	CREATE USER 用户名 [IDENTIFIED BY 密码];
	create user bb identified by 123;
1
2

给用户授权

	直接授权
		GRANT 角色名称 TO 用户名称;
	通过角色授权
		GRANT 权限 ON 表名 TO 用户名;
1
2
3
4

查看用户权限

	SHOW GRANTS FOR 用户名;
1

删除用户

	DROP USER 用户名;
1

3.注意点

mysql创建角色后 默认是没有激活的，需要激活（目前无法激活用户）。mysql8开始才支持角色

SET global activate_all_roles_on_login=ON;

但是可能面临激活失败，就可以直接将table授权给用户，而不通过角色。

直接将table授权给用户，而不通过角色

可以直接将table授权给用户，而不通过角色
GRANT SELECT,INSERT,DELETE,UPDATE ON demo.membermaster TO ‘season’;

19 | 日志（上）：系统出现问题，如何及时发现？

通用查询日志

1.开启通用查询日志

SET GLOBAL general_log = 'ON';
SET @@global.general_log_file = 'H:\mytest.log';
1
2

2.查看通用查询日志
去H:\mytest.log这里查看

3.删除通用查询日志

ET GLOBAL general_log = 'OFF';
1

慢查询日志

慢查询日志用来记录执行时间超过指定时长的查询。
1.MySQL 的配置文件“my.ini”

slow-query-log=1 -- 表示开启慢查询日志，系统将会对慢查询进行记录。
 
slow_query_log_file="GJTECH-PC-slow.log"  -- 表示慢查询日志的名称是"GJTECH-PC-slow.log"。这里没有指定文件夹，默认就是数据目录："C:\ProgramData\MySQL\MySQL Server 8.0\Data"。
 
long_query_time=10  -- 表示慢查询的标准是查询执行时间超过10秒
1
2
3
4
5

错误日志

错误日志记录了 MySQL 服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。

# Error Logging.
log-error="GJTECH-PC.err"
1
2

InnoDB: using atomic writes.
2023-09-01  8:42:13 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2023-09-01  8:42:13 0 [Note] InnoDB: Uses event mutexes
2023-09-01  8:42:13 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2023-09-01  8:42:13 0 [Note] InnoDB: Number of pools: 1
2023-09-01  8:42:13 0 [Note] InnoDB: Using SSE2 crc32 instructions
2023-09-01  8:42:13 0 [Note] InnoDB: Initializing buffer pool, total size = 1073741824, chunk size = 134217728
2023-09-01  8:42:13 0 [Note] InnoDB: Completed initialization of buffer pool
2023-09-01  8:42:13 0 [Note] InnoDB: 128 rollback segments are active.
2023-09-01  8:42:13 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2023-09-01  8:42:13 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2023-09-01  8:42:13 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2023-09-01  8:42:13 0 [Note] InnoDB: 10.5.1 started; log sequence number 60142; transaction id 21
2023-09-01  8:42:13 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-09-01  8:42:13 0 [Note] InnoDB: Loading buffer pool(s) from C:\Program Files\MariaDB 10.5\data\ib_buffer_pool
2023-09-01  8:42:13 0 [Note] InnoDB: Buffer pool(s) load completed at 230901  8:42:13
2023-09-01  8:42:13 0 [Note] Server socket created on IP: '::'.
2023-09-01  8:42:13 0 [Note] Reading of all Master_info entries succeeded
2023-09-01  8:42:13 0 [Note] Added new Master_info '' to hash table
2023-09-01  8:42:13 0 [Note] C:\Program Files\MariaDB 10.5\bin\mysqld.exe: ready for connections.
Version: '10.5.1-MariaDB'  socket: ''  port: 3306  mariadb.org binary distribution
2023-09-01  8:49:49 4 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
2023-09-01 10:27:06 9 [Warning] Aborted connection 9 to db: 'demo' user: 'root' host: 'localhost' (Got an error reading communication packets)
2023-09-01 10:35:10 10 [Warning] Aborted connection 10 to db: 'demo' user: 'root' host: 'localhost' (Got an error reading communication packets)
2023-09-01 11:19:31 11 [Warning] Aborted connection 11 to db: 'demo' user: 'root' host: 'localhost' (Got an error reading communication packets)
2023-09-01 14:45:46 33 [Warning] Access denied for user 'season'@'localhost' (using password: YES)
2023-09-01 15:13:45 36 [ERROR] Could not use C:Users13073219Desktopseasonmytest.log for logging (error 22). Turning logging off for the whole duration of the MariaDB server process. To turn it on again: fix the cause, shutdown the MariaDB server and restart it.
2023-09-01 15:13:56 36 [ERROR] Could not use C:Users13073219Desktopseasonmytest.log for logging (error 22). Turning logging off for the whole duration of the MariaDB server process. To turn it on again: fix the cause, shutdown the MariaDB server and restart it.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

20 | 日志（下）：系统故障，如何恢复数据？

二进制日志（binary log）

二进制日志主要记录数据库的更新事件，比如创建数据表、更新表中的数据、数据更新所花费的时长等信息。通过这些信息，我们可以再现数据更新操作的全过程。而且，由于日志的延续性和时效性，我们还可以利用日志，完成无损失的数据恢复和主从服务器之间的数据同步。

中继日志（relay log）

中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。

回滚日志（undo log）

回滚日志的作用是进行事务回滚。当事务执行的时候，回滚日志中记录了事务中每次数据更新前的状态。当事务需要回滚的时候，可以通过读取回滚日志，恢复到指定的位置。另一方面，回滚日志也可以让其他的事务读取到这个事务对数据更改之前的值，从而确保了其他事务可以不受这个事务修改数据的影响。

重做日志（redo log）

重做日志是存储在磁盘上的一种日志文件，主要有 2 个作用。
1.在系统遇到故障的恢复过程中，可以修复被未完成的事务修改的数据。
2.MySQL 为了提高数据存取的效率，减少磁盘操作的频率，对数据的更新操作不会立即写到磁盘上，而是把数据更新先保存在内存中，积累到一定程度，再集中进行磁盘读写操作。这样就存在一个问题：一旦出现宕机或者停电等异常情况，内存中保存的数据更新操作可能会丢失。这个时候就可以通过读取重做日志中记录的数据更新操作，把没来得及写到磁盘上的数据更新写到磁盘上，确保数据的完整性。

21 | 数据备份：异常情况下，如何确保数据安全？

如何进行数据备份？

首先，我们来学习下用于数据备份的工具 mysqldump。它总共有三种模式：备份数据库中的表；备份整个数据库；备份整个数据库服务器。

备份数据库中的表

H:\>mysqldump -u root -p demo goodsmaster membermaster > test.sql
Enter password: *****
1
2

备份整个数据库

mysqldump -h 服务器 -u 用户 -p 密码 --databases 数据库名称 … > 备份文件名

mysqldump -u root -p --databases demo demo1 > test1.sql
1
2
3

备份整个数据库服务器

这个指令表示，备份本机上运行的 MySQL 数据库服务器的全部内容，包含系统数据库和用户创建的数据库中的库结构信息、表结构信息和表里的数据。这种备份方式会把系统数据库也全部备份出来，而且消耗的资源也比较多，一般来说没有必要，我就不展开细说了。

mysqldump -h 服务器 -u 用户 -p 密码 --all-databases > 备份文件名

mysqldump -u root -p --all-databases > test2.sql
1
2
3

如何进行数据恢复？

数据恢复的方法主要有 2 种：使用“mysql”命令行客户端工具进行数据恢复；使用“SOURCE”语句进行数据恢复。

使用“mysql”命令行

mysql -u root -p demo < test.sql
1

使用“SOURCE”语句

SOURCE 备份文件名
1

如何导出和导入表里的数据？

SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/goodsmaster.txt'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM demo.goodsmaster;
1
2
3
4

使用“LOAD DATA”语句导入数据

LOAD DATA INFILE 文件名
INTO TABLE 表名
FIELDS TERMINATED BY 字符
LINES TERMINATED BY 字符;
1
2
3
4

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/goodsmaster.txt'
INTO TABLE demo.goodsmaster
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
1
2
3
4

22 | 范式：如何消除冗余，实现高效存取？

MySQL 数据库规范化设计的三个范式。

（简单来说，就是用itemid来引用别的表的数据，实现拆表）
第一范式：数据表中所有字段都是不可拆分的基本数据项。
第二范式：在满足第一范式的基础上，数据表中所有非主键字段，必须完全依赖全部主键字段，不能存在部分依赖主键字段的字段。
第三范式：在满足第二范式的基础上，数据表中不能存在可以被其他非主键字段派生出来的字段，或者说，不能存在依赖于非主键字段的字段。遵循范式的要求，可以减少冗余，结合外键约束，可以防止添加、删除、修改数据时产生数据的不一致问题。

遵循业务优先的原则，首先满足业务需求，在这个前提下，再尽量减少冗余。

23 | ER模型：如何理清数据库设计思路？

ER 模型包括哪些要素？

在 ER 模型里面，有三个要素，分别是实体、属性和关系。
实体，可以看做是数据对象，往往对应于现实生活中的真实存在的个体。比如，这个连锁超市就可以看做一个实体。在 ER 模型中，用矩形来表示。实体分为两类，分别是强实体和弱实体。强实体是指不依赖于其他实体的实体；弱实体是指对另一个实体有很强的依赖关系的实体。
属性，则是指实体的特性。比如超市的地址、联系电话、员工数等。在 ER 模型中用椭圆形来表示。
关系，则是指实体之间的联系。比如超市把商品卖给顾客，就是一种超市与顾客之间的联系。在 ER 模型中用菱形来表示。

如何把 ER 模型图转换成数据表？

1. 一个实体通常转换成一个数据表；
2. 一个多对多的关系，通常也转换成一个数据表；
3. 一个 1 对 1，或者 1 对多的关系，往往通过表的外键来表达，而不是设计一个新的数据表；
4. 属性转换成表的字段。

供货商实体转换成供货商表（demo.supplier）的代码如下所示：

mysql> CREATE TABLE demo.supplier
-> (
-> -- 我们给它添加一个与业务无关的字段“supplierid”为主键，并且设置自增约束。
-> supplierid INT PRIMARY KEY AUTO_INCREMENT,
-> suppliername TEXT,
-> address TEXT,
-> phone TEXT
-> );
Query OK, 0 rows affected (0.06 sec)
1
2
3
4
5
6
7
8
9

一个多对多的关系转换成一个数据表

CREATE TABLE demo.transactionhead(transactionid INT PRIMARY KEY, -- 添加与业务无关的字段为主键transactionno TEXT NOT NULL,cashierid INT NOT NULL, -- 收款机表的主键，反映了参与零售关系的收款机信息memberid INT, -- 会员表的主键，反映了参与零售关系的会员的信息operatorid INT NOT NULL, -- 员工表的主键，反映了参与零售关系的员工信息transdate DATETIME NOT NULL,CONSTRAINT fk_transactionhead_cashier FOREIGN KEY (cashierid) REFERENCES cashier (cashierid),CONSTRAINT fk_transactionhead_member FOREIGN KEY (memberid) REFERENCES member (memberid),CONSTRAINT fk_transactionhead_operator FOREIGN KEY (operatorid) REFERENCES operator (operatorid));
1

24 | 查询有点慢，语句该如何写？

查询分析语句

{ EXPLAIN | DESCRIBE | DESC }查询语句;
1

mysql> EXPLAIN SELECT itemnumber,quantity,price,transdate -- 分析查询执行情况
-> FROM demo.trans
-> WHERE itemnumber=1                  -- 通过商品编号筛选
-> AND transdate>'2020-06-18 09:00:00' -- 通过交易时间筛选
-> AND transdate<'2020-06-18 12:00:00';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key |key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | trans | NULL | ALL | NULL | NULL | NULL | NULL | 4157166 | 1.11 | Using where |      -- 没有索引，扫描4157166条记录
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

id：是一个查询序列号。
table：表示与查询结果相关的表的名称。
partition：表示查询访问的分区。
key：表示优化器最终决定使用的索引是什么。
key_len：表示优化器选择的索引字段按字节计算的长度。如果没有使用索引，这个值就是空。
ref：表示哪个字段或者常量被用来与索引字段比对，以读取表中的记录。如果这个值是“func”，就表示用函数的值与索引字段进行比对。
rows：表示为了得到查询结果，必须扫描多少行记录。
filtered：表示查询筛选出的记录占全部表记录数的百分比。
possible_key：表示 MySQL 可以通过哪些索引找到查询的结果记录。如果这里的值是空，就说明没有合适的索引可用。你可以通过查看 WHERE 条件语句中使用的字段，来决定是否可以通过创建索引提高查询的效率
Extra：表示 MySQL 执行查询中的附加信息。你可以点击这个链接查询详细信息。
type：表示表是如何连接的。至于具体的内容，你可以参考下查询分析语句输出内容说明。
除了刚刚这些字段，还有 1 个比较重要，那就是 select_type。它表示查询的类型，主要有 4 种取值。SIMPLE：表示简单查询，不包含子查询和联合查询。PRIMARY：表示是最外层的查询。UNION：表示联合查询中的第二个或者之后的查询。DEPENDENTUNION：表示联合查询中的第二个或者之后的查询，而且这个查询受外查询的影响。

特别提醒

特别需要提醒你注意的是，在使用“LIKE”关键字的条件语句中，通配符“%”在前面的筛选条件不能使用索引，通配符“%”在后面的筛选条件可以使用索引。在使用“OR”关键字的条件语句中，只有关键字“OR”前后的表达式中的字段都创建了索引，条件语句才能使用索引。

25 | 表太大了，如何设计才能提高性能？

从设计角度提升查询性能的方法

修改数据类型以节省存储空间；在利大于弊的情况下增加冗余字段；把大表中查询频率高的字段和查询频率低的字段拆分成不同的表；尽量使用非空约束。

修改数据类型以节省存储空间

一种是针对整数类型数据，尽量使用小的整数类型来定义；另外一种是，如果字段既可以用文本类型，也可以用整数类型，尽量使用整数类型。

在利大于弊的情况下增加冗余字段

修改完表的结构之后，我们把商品名称数据填入新加的字段中，这样一来，流水表中就有了商品名称信息，不用再通过与商品信息表进行连接来获取了。

把大表中查询频率高的字段和查询频率低的字段拆分成不同的表

如果把这个大表拆分开，把使用频率高的字段放在一起形成一个表，把剩下的使用频率低的字段放在一起形成一个表，这样查询操作每次读取的记录比较小，查询效率自然也就提高了。

尽量使用非空约束

在设计字段的时候，如果业务允许，我建议你尽量使用非空约束。这样做的好处是，可以省去判断是否为空的开销，提高存储效率。而且，非空字段也容易创建索引。使用非空约束，甚至可以节省存储空间（每个字段 1 个比特）。

mysql> DESCRIBE demo.goodsmaster;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| itemnumber    | int           | NO   | PRI | NULL    |       |
| barcode       | text          | NO   |     | NULL    |       |
| goodsname     | text          | NO   |     | NULL    |       |
| specification | text          | NO   |     | NULL    |       |
| unit          | text          | NO   |     | NULL    |       |
| salesprice    | decimal(10,2) | NO   | UNI | 0.00    |       |
+---------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12

26 | 如何充分利用系统资源？

我们学习了通过系统参数来配置资源、提高查询效率的方法。

1. 系统参数 InnoDB_flush_log_at_trx_commit 适用于 InnoDB 存储引擎。默认的值是 1，意思是每次提交事务的时候，都把数据写入日志，并把日志写入磁盘。0 表示每隔 1 秒将数据写入日志，并将日志写入磁盘。2 表示每次事务提交的时候，将数据写入日志，但是日志每隔 1 秒写入磁盘。
2. 系统参数 InnoDB_buffer_pool_size 表示 InnoDB 存储引擎使用多少缓存来存储索引和数据。这个值越大，可以加载到缓存区的索引和数据量就越多，需要的磁盘读写就越少。
3. 系统参数 InnoDB_buffer_pool_instances 的意思是，将 InnoDB 的缓存区分成几个部分，可以提高系统的并行处理能力。

怎么通过使用 Performance Schema 来监控服务器运行中的事件。

系统数据库 performance_schema 中的表 setup_instruments 和 setup_consumers 中的数据，是启用监控的关键。
setup_instruments 保存的数据，表示哪些对象发生的事件可以被系统捕获。
setup_consumers 保存的数据用来控制保存哪些事件的信息。

27 | 手把手带你设计一个完整的连锁超市信息系统数据库（上）

如何设计数据结构？

盘点模块的 ER 模型

ER 模型转换成数据表

首先，我们把强实体转换成独立的数据表。

商户表（demo.enterprice）：

商品信息表（demo.goodsmaster）：

接着，我们把弱实体转换成独立的数据表。

门店表（demo.branch）：

员工表（demo.employee）：

仓库表（demo.stockmaster）：

第三步，把多对多的关系转换成独立的数据表。

强实体和弱实体转换成独立的数据表，多对多的关系转换成独立的数据表，1 对多的关系转换成外键约束。

库存表（demo.inventory）：

盘点关系可以转换成 2 个表，分别是盘点单头表和盘点单明细表，这样做是为了满足第三范式的要求，防止冗余。
盘点单头表（demo.invcounthead）：

盘点单明细表（demo.invcountdetails）：

如何进行分库分表？

所谓的分库分表，其实就是把大的数据库拆成小数据库，把大表拆成小表，让单一数据库、单一数据表的数据量变小。这样每次查询时，需要扫描的数据量减少了，也就达到了提升查询执行效率的目的。分库分表又可以分成垂直分表、垂直分库、水平分库和水平分表。

垂直分表

所谓垂直分表，就是把一个有很多字段的表，按照使用频率的不同，拆分成 2 个或多个表。
商品常用信息表：

商品不常用信息表：

垂直分库

垂直分库的意思是，把不同模块的数据表分别存放到不同的数据库中。这样做的好处是，每个数据库只保存特定模块的数据，系统只有用到特定模块的数据时，才会访问这个数据库。这样就减少了数据库访问的次数，就相当于是把数据访问的流量分散了。

水平分库

水平分表的意思是，把数据表的内容，按照一定的规则拆分出去。
第一步，我们把盘点单头表和盘点单明细表水平拆分：把验收处理过的盘点单头表和盘点单明细表拆分到盘点单头历史表和盘点单明细历史表。

盘点单头表历史表：

盘点单明细历史表：

第二步，我们把组号大于 500、小于 1000 的商户数据，拆分到另外的数据表里进行保存。这里的数字是我们根据对入驻平台商户的数据量进行评估之后得出的，在实际工作中，你可以根据实际情况来决定。原则是：确保单个数据表中的数据量适中，不会成为操作的瓶颈。

水平分表

水平分库的意思与水平分表类似，就是按照一定的规则，把数据库中的数据拆分出去，保存在新的数据库当中。
新的数据库可以在相同的服务器上，也可以在不同的服务器上。比如，我们可以把组号大于 500、小于 1000 的用户数据拆分出来，保存到新的服务器的数据库中。不过，保存到新的服务器，也就意味着增加系统的开销。

28 | 手把手带你设计一个完整的连锁超市信息系统数据库（下）

如何创建数据库和数据表？

创建营运数据库和库存数据库

mysql> CREATE DATABASE operation;
Query OK, 1 row affected (0.03 sec)
 
mysql> CREATE DATABASE inventory;
Query OK, 1 row affected (0.02 sec)
1
2
3
4
5

以商户表（operation.enterprice）为例：

mysql> CREATE TABLE operation.enterprice
-> (
-> groupnumber SMALLINT PRIMARY KEY,  -- 组号
-> groupname VARCHAR(100) NOT NULL,   -- 名称
-> address TEXT NOT NULL,             -- 地址
-> phone VARCHAR(20) NOT NULL,        -- 电话
-> contactor VARCHAR(50) NOT NULL     -- 联系人
-> );
Query OK, 0 rows affected (0.05 sec)
1
2
3
4
5
6
7
8
9

如何创建索引？

索引对提升数据查询的效率作用最大，没有之一。我们创建索引的策略是：所有的数据表都必须创建索引；只要是有可能成为查询筛选条件的字段，都必须创建索引。

mysql> CREATE INDEX index_enterprice_groupname ON operation.enterprice (groupname);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
1
2
3

如何创建视图？

我们为盘点单创建视图

mysql> CREATE VIEW view_invcount
    -> AS
    -> SELECT a.*,b.itemnumber,b.accquant,b.invquant,b.plquant
    -> FROM inventory.invcounthead AS a
    -> JOIN
    -> inventory.invcountdetails AS b
    -> ON (a.listnumber=b.listnumber);
Query OK, 0 rows affected (0.04 sec)
1
2
3
4
5
6
7
8

如何创建存储过程？

下面我具体介绍存储过程的入口参数和数据处理逻辑。
存储过程的入口参数是单号和验收人的员工编号。
存储过程的数据处理逻辑是：先用盈亏数量调整库存，计算方式是新库存 = 老库存 + 盈亏数量；然后把盘点单数据移到盘点单历史中去。把盘点单明细表中的数据插入到盘点单明细历史表中；把盘点单头表中的数据，插入到盘点单头历史表中；删除盘点单明细表中的数据；删除盘点单头表中的数据。

CREATE DEFINER=`root`@`localhost` PROCEDURE `invcountconfirm`(mylistnumber INT,myconfirmer SMALLINT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE mybranchid INT;
DECLARE myitemnumber INT;
DECLARE myplquant DECIMAL(10,3);
DECLARE cursor_invcount CURSOR FOR 
SELECT branchid,itemnumber,plquant
FROM inventory.invcountdetails
WHERE listnumber = mylistnumber;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; 

START TRANSACTION;         
 OPEN cursor_invcount; -- 打开游标
 FETCH cursor_invcount INTO mybranchid,myitemnumber,myplquant; -- 读入第一条记录
REPEAT
  UPDATE inventory.inventory
  SET itemquantity = itemquantity + myplquant    -- 更新库存
        WHERE itemnumber = myitemnumber
        AND branchid = mybranchid; 
  FETCH cursor_invcount INTO mybranchid,myitemnumber,myplquant; -- 读入下一条记录
 UNTIL done END REPEAT;
    
 CLOSE cursor_invcount;
    
  INSERT INTO inventory.invcountdetailshist 
(listnumber,groupnumber,branchid,stockid,itemnumber,accquant,invquant,plquant) 
  SELECT listnumber,groupnumber,branchid,stockid,itemnumber,accquant,invquant,plquant
  FROM inventory.invcountdetails 
  WHERE listnumber=mylistnumber;  -- 把这一单的盘点单明细插入历史表

  INSERT INTO inventory.invcountheadhist  
(listnumber,groupnumber,branchid,stockid,recorder,recordingdate,confirmer,confirmationdate) 
  SELECT listnumber,groupnumber,branchid,stockid,recorder,recordingdate,myconfirmer,now()
  FROM inventory.invcounthead
  WHERE listnumber=mylistnumber;  -- 把这一单的盘点单头插入历史 
  DELETE FROM inventory.invcountdetails WHERE listnumber = mylistnumber; -- 删除这一单的盘点单明细表数据
 DELETE FROM inventory.invcounthead WHERE listnumber = mylistnumber; -- 删除这一单的盘点单头表数据
COMMIT;
END
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

如何创建触发器？

由于我们根据分库分表的策略，把商品信息表拆分成了商品常用信息表和商品不常用信息表，这样就很容易产生数据不一致的情况。为了确保商品常用信息表和商品不常用信息表中的数据保持一致，我们可以创建触发器，保证这 2 个表中删除其中一个表的一条记录的操作，自动触发删除另外一个表中对应的记录的操作。这样一来，就防止了一个表中的记录在另外一个表中不存在的情况，也就确保了数据的一致性。

DELIMITER //
CREATE TRIGGER operation.del_goodso BEFORE DELETE  -- 在删除前触发
ON operation.goods_o
FOR EACH ROW                              -- 表示每删除一条记录，触发一次
BEGIN                                     -- 开始程序体
 DELETE FROM operation.goods_f
    WHERE groupnumber=OLD.groupnumber
    AND itemnumber=OLD.itemnumber;
END
//
DELIMITER ;

DELIMITER //
CREATE TRIGGER operation.del_goodsf BEFORE DELETE  -- 在删除前触发
ON operation.goods_f
FOR EACH ROW                              -- 表示每删除一条记录，触发一次
BEGIN                                     -- 开始程序体
 DELETE FROM operation.goods_o
    WHERE groupnumber=OLD.groupnumber
    AND itemnumber=OLD.itemnumber;
END
//
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

如何制定容灾和备份策略？(难)

如何搭建主从服务器？

第一步，确保从服务器可以访问主服务器（在同一网段），例如，可以把主服务器的 IP 地址设置为：主服务器IP：192.168.1.100
我们把从服务器的 IP 地址设置为：从服务器IP： 192.168.1.110

第二步，修改主从服务器的系统配置文件 my.ini，使主从服务器有不同的 ID 编号，并且指定需要同步的数据库。
在主服务器的配置文件中，我们把主服务器的编号修改为：server-id = 1。

# ***** Group Replication Related *****
# Specifies the base name to use for binary log files. With binary logging
# enabled, the server logs all statements that change data to the binary
# log, which is used for backup and replication.
log-bin=mysql-bin          -- 二进制日志名称
binlog-do-db = operation   -- 需要同步的数据库：营运数据库
binlog-do-db = inventory   -- 需要同步的数据库：库存数据库

# ***** Group Replication Related *****
# Specifies the server ID. For servers that are used in a replication topology,
# you must specify a unique server ID for each replication server, in the
# range from 1 to 2^32 − 1. “Unique” means that each ID must be different
# from every other ID in use by any other source or replica.
server-id=1          -- 主服务器的ID设为1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

然后，我们来修改从服务器的配置文件 my.ini，把从服务器的编号设置为 server-id = 2。

# ***** Group Replication Related *****
# Specifies the base name to use for binary log files. With binary logging
# enabled, the server logs all statements that change data to the binary
# log, which is used for backup and replication.
log-bin=mysql-bin              -- 二进制日志名称
replicate_do_db = operation    -- 需要同步过来的数据库：营运数据库
replicate_do_db = inventory    -- 需要同步过来的数据库：库存数据库

# ***** Group Replication Related *****
# Specifies the server ID. For servers that are used in a replication topology,
# you must specify a unique server ID for each replication server, in the
# range from 1 to 2^32 − 1. “Unique” means that each ID must be different
# from every other ID in use by any other source or replica.
server-id=2                       -- 从服务器的编号为2
1
2
3
4
5
6
7
8
9
10
11
12
13
14

第三步，在主从服务器上都保存配置文件，然后分别重启主从服务器上的 MySQL 服务器。
第四步，为了使从服务器可以访问主服务器，在主服务器上创建数据同步用户，并赋予所有权限。这样，从服务器就可以实时读取主服务器的数据了。

mysql> CREATE USER 'myreplica'@'%' IDENTIFIED BY 'mysql';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL ON *.* TO 'myreplica'@'%';
Query OK, 0 rows affected (0.99 sec)
1
2
3
4
5

第五步，在从服务器上启动数据同步，开始从主服务器中同步数据。

mysql>change master to master_host='192.168.1.100',master_port=3306,master_user='myreplica',master_password='mysql’,master_log_file='mysql-bin.000001',master_log_pos=535;
Query OK, 0 rows affected (0.02 sec)
1
2

启动同步的时候，你需要注意的是，必须指明主服务器上二进制日志中的位置 master_log_pos。也就是说，你准备从主服务器的二进制日志的哪个位置开始同步数据。你可以通过在主服务器上，用 SQL 语句“SHOW BINLOG EVENTS IN 二进制日志名” 获取这个值。下面的代码可以启动同步：

mysql>start slave;
Query OK, 0 rows affected (0.02 sec)
1
2

如何制定数据备份策略？

我们可以在应用层面调用类似下面的命令进行备份：

H:\>mysqldump -u root -p --databases
inventory operation > H:\backup\Monday\mybackup.sql
1
2

特别放送（三）| MySQL 8 都有哪些新特征？（难）

这节课，我们学习了 MySQL 8 的 2 个重要新功能：窗口函数和公用表表达式。当然，除了今天学习的窗口函数和公用表表达式，MySQL 8 还有许多其他的新特征，比如，完善了对空间位置信息的处理；支持对表的 DDL 操作（创建、修改和删除表）的原子性，使得CREATE TABLE …SELECT语句能够成为一个原子操作，提高了数据安全性，等等。

窗口函数

窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用。

公用表表达式

公用表表达式的作用是可以替代子查询，而且可以被多次引用。递归公用表表达式对查询有一个共同根节点的树形结构数据非常高效，可以轻松搞定其他查询方式难以处理的查询。

【极客时间】2 小时搞定 MySQL 核心应用

MySQL 核心应用开发规范

MySQL 性能优化

MySQL 性能优化总结和架构设计详解