数据库——MySQL - 酸梨子-专注技术分享

名称	描述	命令
数据定义语言（DDL ）	数据库、数据表的创建、修改和删除	CREATE、ALTER、DROP
数据操作语言（DML ）	数据的增加、修改和删除	INSERT、UPDATE、DELETE
数据查询语言（ DQL ）	数据的查询	SELECT
数据控制语言（ DCL ）	用户授权、事务的提交和回滚	GRANT、COMMIT、 ROLLBACK

2.2 数据库操作

2.2.1创建数据库的语法

CREATE DATABASE [IF NOT EXISTS] 数据库名称 DEFAULT CHARACTER SET 字符集 COLLATE 排序规则;

示例：创建数据库lesson，并指定字符集为 GBK ，排序规则为 GBK_CHINESE_CI
CREATE DATABASE IF NOT EXISTS lesson DEFAULT CHARACTER SET GBK COLLATE GBK_CHINESE_CI;

2.2.2 修改数据库的语法

ALTER DATABASE 数据库名称 CHARACTER SET 字符集 COLLATE 排序规则;
示例：修改数据库lesson的字符集为 UTF8 ，排序规则为 UTF8_GENERAL_CI
ALTER DATABASE lesson CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

2.2.3删除数据库的语法

DROP DATABASE [IF EXISTS] 数据库名称;
示例：删除数据库lesson
DROP DATABASE IF EXISTS lesson;

2.2.4 查看数据库语法

SHOW DATABASES;

2.2.5 使用数据库的语法

USE 数据库名称;
示例：使用数据库lesson
USE lesson;

3. 列类型

在 MySQL 中，常用列类型主要分为数值类型、日期时间类型、字符串类型。

3.1 数值类型

类型	说明	取值范围	存储需求
tinyint	非常小的数据	有符号值： -2^7 ~ 2^7 -1 无符号值：0 ~ 2^8 -1	1字节
smallint	较小的数据	有符号值： -2^15 ~ 2^15 -1 无符号值： 0 ~ 2^16 -1	2字节
mediumint	中等大小的数据	有符号值： -2^23 ~ 2^23 -1 无符号值： 0 ~ 2^24 -1	3字节
int	标准整数	有符号值： -2^31 ~ 2^31 -1 无符号值：0 ~ 2^32 -1	4字节
bigint	较大的整数	有符号值： -2^63 ~2^63 -1无符号值：0 ~2^64 -1	8字节
float	单精度浮点数	无符号值：1.1754351 * 10^-38 ~3.402823466 *10^38	4字节
double	双精度浮点数	无符号值：2.22507385 * 10^-308 ~ 1.79769313* 10^308	8字节
decimal	字符串形式的浮点数	decimal(m, d)	m个字节

3.2 日期时间类型

类型	说明	取值范围
DATE	YYYY-MM-dd ，日期格式	1000-01-01 ~ 9999-12-31
TIME	HH:mm:ss ，时间格式	-838:59:59.000000~838:59:59.000000
DATETIME	YY-MM-dd HH:mm:ss	1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999
TIMESTAMP	YYYY-MM-dd HH:mm:ss 格式表示的时间戳	1970-01-01 00:00:01.000000 ~ 2038-01-1903:14:07.999999
YEAR	YYYY 格式的年份值	1901~2155

3.3 字符串类型

类型	说明	最大长度
char [(M)]	固定长字符串，检索快但费空间， 0 <= M <= 255	M字符
varchar [(M)]	可变字符串0 <= M <= 65535	变长度
text	文本串	2^16 –1字节

3.4列类型修饰属性

属性名	说明	示例
UNSIGNED	无符号，只能修来修饰数值类型，表名该列数据不能出现负数	INT(4) UNSIGNED，表示只能为4位大于等于0的整数
ZEROFILL	不足的位数使用0来填充	INT(4) ZEROFILL ，如果给定的值为10，此时只有2位，而该列需要4位，不足的2位由0来填充，最终值为0010
NOT NULL	表示该列类型的值不能为空	VARCHAR (20) NOT NULL，表示该列数据不能为空值
DEFAULT	表示设置默认值	INT(4) DEFAULT 0，表示该列不赋值时默认为0
AUTO_INCREMENT	表示自增长,只能应用于数值列类型,该列类型必须为键,且不能为空	INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY。第一次为该列中插入值时为1，第二次为2

4.数据表操作

4.1 数据表类型

MySQL 中的数据表类型有许多，如 MyISAM 、 InnoDB 、 HEAP 、 BOB 、 CSV 等。其中最常用的就是MyISAM 和 InnoDB

4.2 MyISAM 与InnoDB 的区别

名称	MyISAM	InnoDB
事务处理	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大,约2倍

事务：涉及的所有操作是一个整体，要么都执行，要么都不执行。
数据行锁定：一行数据，当一个用户在修改该数据时，可以直接将该条数据锁定。

如何选择数据表的类型？

当涉及的业务操作以查询居多，修改和删除较少时，可以使用MyISAM。当涉及的业务操作经常会有修改和删除操作时，使用InnoDB。

4.3 创建数据表

CREATE TABLE [IF NOT EXISTS] 数据表名称(
 字段名1 列类型(长度) [修饰属性] [键/索引] [注释],
 字段名2 列类型(长度) [修饰属性] [键/索引] [注释],
 字段名3 列类型(长度) [修饰属性] [键/索引] [注释],
 ......
 字段名n 列类型(长度) [修饰属性] [键/索引] [注释]
) [ENGINE = 数据表类型][CHARSET=字符集编码] [COMMENT=注释];
示例：创建学生表，表中有字段学号、姓名、性别、年龄和成绩
CREATE TABLE IF NOT EXISTS student(
    `number` VARCHAR(30) NOT NULL PRIMARY KEY COMMENT '学号，主键',
    name VARCHAR(30) NOT NULL COMMENT '姓名',
    sex TINYINT(1) UNSIGNED DEFAULT 0 COMMENT '性别：0-男 1-女 2-其他',
    age TINYINT(3) UNSIGNED DEFAULT 0 COMMENT '年龄',
    score DOUBLE(5, 2) UNSIGNED COMMENT '成绩'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='学生表';

4.4 修改数据表

修改表名：
ALTER TABLE 表名 RENAME AS 新表名;
示例：将student表名称修改为 stu
ALTER TABLE student RENAME AS stu;


增加字段：
ALTER TABLE 表名 ADD 字段名 列类型(长度) [修饰属性] [键/索引] [注释];
示例：在 stu 表中添加字段联系电话(phone)，类型为字符串，长度为11，非空
ALTER TABLE stu ADD phone VARCHAR(11) NOT NULL COMMENT '联系电话';


查看表结构：
DESC 表名; -- 查看表结构
 

修改字段：
-- MODIFY 只能修改字段的修饰属性
ALTER TABLE 表名 MODIFY 字段名 列类型(长度) [修饰属性] [键/索引] [注释];
-- CHANGE 可以修改字段的名字以及修饰属性
ALTER TABLE 表名 CHANGE 字段名 新字段名 列类型(长度) [修饰属性] [键/索引] [注释];
示例： 将 stu 表中的 sex 字段的类型设置为 VARCHAR ，长度为2，默认值为'男'，注释为 "性别，男，女，其他"
ALTER TABLE stu MODIFY sex VARCHAR(2) DEFAULT '男' COMMENT '性别：男，女，其他';
示例：将 stu 表中 phone 字段修改为 mobile ，属性保持不变
ALTER TABLE stu CHANGE phone mobile VARCHAR(11) NOT NULL COMMENT '联系电话';


删除字段：
ALTER TABLE 表名 DROP 字段名;
示例：将 stu 表中的 mobile 字段删除
ALTER TABLE stu DROP mobile;

4.5 删除数据表

DROP TABLE [IF EXISTS] 表名;
示例：删除数据表 stu
DROP TABLE IF EXISTS stu;

5.运用

1. 在数据库exercise中创建课程表stu_course，包含字段课程编号(number)，类型为整数，长度为11，是主键，自增长，非空、课程名称(name)，类型为字符串，长度为20，非空、学分(score)，类型为浮点数，小数点后面保留2位有效数字， 长度为5，非空

-- 如果数据库不存在 就创建数据库
CREATE DATABASE IF NOT EXISTS exercise DEFAULT CHARACTER SET UTF8 COLLATE
UTF8_GENERAL_CI;
-- 使用数据库
USE exercise;
-- 在数据库中创建数据表stu_course
CREATE TABLE IF NOT EXISTS stu_course(
`number` INT(11) AUTO_INCREMENT PRIMARY KEY NOT NULL COMMENT '课程编号',
name VARCHAR(20) NOT NULL COMMENT '课程名称',
score DOUBLE(5, 2) NOT NULL COMMENT '学分'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT '课程表';

—————————————————————————————————————————————————————————————————————————————————————————

2. 将课程表重命名为course
ALTER TABLE stu_course RENAME AS course;

—————————————————————————————————————————————————————————————————————————————————————————

3. 在课程表中添加字段学时(time)，类型为整数，长度为3，非空
ALTER TABLE course ADD `time` INT(3) NOT NULL COMMENT '学时';

—————————————————————————————————————————————————————————————————————————————————————————

4. 修改课程表学分类型为浮点数，小数点后面保留1位有效数字，长度为3，非空
ALTER TABLE course MODIFY score DOUBLE(3, 1) NOT NULL COMMENT '学分';

—————————————————————————————————————————————————————————————————————————————————————————

5. 删除课程表
DROP TABLE IF EXISTS course;

—————————————————————————————————————————————————————————————————————————————————————————

6. 删除数据库exercise
DROP DATABASE IF EXISTS exercise;

二. MySQL数据的增删改查

1.DML语句

1.1什么是DML

DML全称为Data Manipulation Language，表示数据操作语言。主要体现于对表数据的增删改操作。因此DML仅包括INSERT、UPDATE和DELEETE语句。

1.2 INSERT语句

-- 需要注意，VALUES后的字段值必须与表名后的字段名一一对应
INSERT INTO 表名(字段名1, 字段名2, ..., 字段名n) VALUES(字段值1, 字段值2, ..., 字段值n);
-- 需要注意，VALUES后的字段值必须与创建表时的字段顺序保持一一对应
INSERT INTO 表名 VALUES(字段值1, 字段值2, ..., 字段值n);
-- 一次性插入多条数据
INSERT INTO 表名(字段名1, 字段名2, ..., 字段名n) VALUES(字段值1, 字段值2, ..., 字段值n),(字段值1, 字段值2, ..., 字段值n), ... , (字段值1, 字段值2, ..., 字段值n);
INSERT INTO 表名 VALUES(字段值1, 字段值2, ..., 字段值n), (字段值1, 字段值2, ..., 字段值n), ..., (字段值1, 字段值2, ..., 字段值n);

示例：向课程表中插入数据

INSERT INTO course(`number`, name, score, `time`) VALUES (1, 'Java基础', 4, 40);
INSERT INTO course VALUES (2, '数据库', 3, 20);
INSERT INTO course(`number`, score, name, `time`) VALUES (3, 5, 'Jsp', 40);
INSERT INTO course(`number`, name, score, `time`) VALUES (4, 'Spring', 4, 5),(5,'Spring Mvc', 2, 5);
INSERT INTO course VALUES (6, 'SSM', 2, 3), (7, 'Spring Boot', 2, 2);

1.3 UPDATE语句

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2, ..., 字段名n=字段值n] [WHERE 修改条件]

WHERE条件子句
WHERE time > 20 && time < 40;  <=>  WHERE time > 20 and time <40;

UPDATE语句
示例：将数据库的学分更改为4，学时更改为15
UPDATE course SET score=4, `time`=15 WHERE name='数据库';

1.4 DELETE语句

DELETE FROM 表名 [WHERE 删除条件];
示例：删除课程表中课程编号为1的数据
DELETE FROM course WHERE `number`=1;

1.5 TRUNCATE语句

-- 清空表中数据
TRUNCATE [TABLE] 表名;
示例：清空课程表数据
TRUNCATE course;

1.6 DELETE与TRUNCATE区别

DELETE语句根据条件删除表中数据，而TRUNCATE语句则是将表中数据全部清空；如果DELETE语句要删除表中所有数据，那么在效率上要低于TRUNCATE语句。
如果表中有自增长列，TRUNCATE语句会重置自增长的计数器，但DELETE语句不会。
TRUNCATE语句执行后，数据无法恢复，而DELETE语句执行后，可以使用事务回滚进行恢复。

2.DQL语句

2.1 什么是DQL

DQL全称是Data Query Language，表示数据查询语言。体现在数据的查询操作上，因此，DQL仅包括SELECT语句。

2.2 SELECT语句

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n] FROM 表名 WHERE 查询条件
解释说明：
ALL表示查询所有满足条件的记录，可以省略；DISTINCT表示去掉查询结果中重复的记录
AS可以给数据列、数据表取一个别名

示例： 从课程表中查询课程编号小于5的课程名称
SELECT name FROM course WHERE `number`<5;
从课程表中查询课程名称为"Java基础"的学分和学时
SELECT score, `time` FROM course WHERE name='Java基础';

2.3 比较操作符

操作符	语法	说明
IS NULL	字段名 IS NULL	如果字段的值为NULL，则条件满足
IS NOT NULL	字段名 IS NOT NULL	如果字段的值不为NULL，则条件满足
BETWEEN...AND	字段名 BETWEEN 最小值 AND 最大值	如果字段的值在最小值与最大值之间（能够取到最小值和最大值），则条件满足
LIKE	字段名 LIKE '%匹配内容%'	如果字段值包含有匹配内容，则条件满足
IN	字段名 IN(值1，值 2，...，值n)	如果字段值在值1,值2, ...，值n中，则条件满足

示例：从课程表查询课程名为NULL的课程信息
SELECT * FROM course WHERE name IS NULL;

示例：从课程表查询课程名不为NULL的课程信息
SELECT * FROM course WHERE name IS NOT NULL;

示例：从课程表查询学分在2~4之间的课程信息
SELECT * FROM course WHERE score BETWEEN 2 AND 4;

示例：从课程表查询课程名包含"V"的课程信息
SELECT * FROM course WHERE name LIKE '%v%';

示例：从课程表查询课程名以"J"开头的课程信息
SELECT * FROM course WHERE name LIKE 'J%';

示例：从课程表查询课程名以"p"结尾的课程信息
SELECT * FROM course WHERE name LIKE '%p';

示例：从课程表查询课程编号为1,3,5的课程信息
SELECT * FROM course WHERE `number` IN (1, 3, 5);

2.5 分组

数据表准备：新建学生表student，包含字段学号（no），类型为长整数，长度为20，是主键，自增长，非空；姓名（name），类型为字符串，长度为20，非空；性别（sex），类型为字符串，长度为2，默认值为"男"；年龄（age），类型为整数，长度为3，默认值为0；成绩（score），类型为浮点数，长度为5，小数点后面保留2位有效数字

DROP TABLE IF EXISTS student;
CREATE TABLE student(
         no BIGINT(20) AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '学号，主键',
         name VARCHAR(20) NOT NULL COMMENT '姓名',
         sex VARCHAR(2) DEFAULT '男' COMMENT '性别',
         age INT(3) DEFAULT 0 COMMENT '年龄',
         score DOUBLE(5, 2) COMMENT '成绩'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='学生表';

插入测试数据：

INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '张三', '男', 20,59);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '李四', '女', 19,62);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '王五', '其他',21, 62);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '龙华', '男', 22,75);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '金凤', '女', 18,80);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '张华', '其他',27, 88);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '李刚', '男', 30,88);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '潘玉明', '女',28, 81);
INSERT INTO student(no, name, sex, age, score) VALUES (DEFAULT, '凤飞飞', '其他',32, 90);

分组查询：

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n]FROM 表名 WHERE 查询条件 GROUP BY 字段名1，字段名2,..., 字段名n
分组查询所得的结果只是该组中的第一条数据。

示例：从学生表查询成绩在80分以上的学生信息并按性别分组
SELECT * FROM student WHERE score>80 GROUP BY sex;

示例：从学生表查询成绩在60~80之间的学生信息并按性别和年龄分组
SELECT * FROM student WHERE score BETWEEN 60 AND 80 GROUP BY sex, age;

聚合函数：

COUNT() ：统计满足条件的数据总条数
示例：从学生表查询成绩在80分以上的学生人数
SELECT COUNT(*) total FROM student WHERE score>80;

SUM()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的总和
示例：从学生表查询不及格的学生人数和总成绩
SELECT COUNT(*) totalCount, SUM(score) totalScore FROM student WHERE score<60;

AVG()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的平均值
示例：从学生表查询男生、女生、其他类型的学生的平均成绩
SELECT sex, AVG(score) avgScore FROM student GROUP BY sex;

MAX()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的最大值
示例：从学生表查询学生的最大年龄
SELECT MAX(age) FROM student;

MIN()：只能用于数值类型的字段或者表达式，计算该满足条件的字段值的最小值
示例：从学生表查询学生的最低分
SELECT MIN(score) FROM student;

分组查询结果筛选：

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n] FROM 表名 WHERE 查询条件 GROUP BY 字段名1，字段名2,..., 字段名n HAVING 筛选条件

分组后如果还需要满足其他条件，则需要使用HAVING子句来完成。

示例：从学生表查询年龄在20~30之间的学生信息并按性别分组，找出组内平均分在74分以上的组
SELECT * FROM student WHERE age BETWEEN 20 AND 30 GROUP BY sex HAVING avg(score)>74;

2.6 排序

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n] FROM 表名 WHERE 查询条件 ORDER BY 字段名1 ASC|DESC，字段名2 ASC|DESC,..., 字段名n ASC|DESC

ORDER BY 必须位于WHERE 条件之后。

示例：从学生表查询年龄在18~30岁之间的学生信息并按成绩从高到低排列，如果成绩相同，则按年龄从小到大排列
SELECT * FROM student WHERE age BETWEEN 18 AND 30 ORDER BY score DESC, age ASC;

2.7分页

SELECT ALL/DISTINCT * | 字段名1 AS 别名1[,字段名1 AS 别名1, ..., 字段名n AS 别名n] FROM 表名 WHERE 查询条件 LIMIT 偏移量, 查询条数

LIMIT的第一个参数表示偏移量，也就是跳过的行数。
LIMIT的第二个参数表示查询返回的最大行数，可能没有给定的数量那么多行。

示例：从学生表分页查询成绩及格的学生信息，每页显示3条，查询第2页学生信息
SELECT * FROM student WHERE score>=60 LIMIT 3, 3;

注意：如果一个查询中包含分组、排序和分页，那么它们之间必须按照分组->排序->分页的先后顺序排列。

3.运用

现有员工表 emp ，包含字段员工编号（no），类型为整数，长度为20，是主键，自增长，非空；姓名（name），类型为字符串，长度为20，非空；性别（sex），类型为字符串，长度为2，默认值为"男"；年龄（age），类型为整数，长度为3，非空；所属部门（dept），类型为字符串，长度为20，非空；薪资（salary），类型为浮点数，长度为10，小数点后面保留2位有效数字，非空。

CREATE TABLE IF NOT EXISTS emp(
`no` BIGINT(20) AUTO_INCREMENT PRIMARY KEY NOT NULL COMMENT '员工编号',
name VARCHAR(20) NOT NULL COMMENT '姓名',
sex VARCHAR(2) DEFAULT '男' COMMENT '性别',
age TINYINT(3) UNSIGNED NOT NULL COMMENT '年龄',
dept VARCHAR(20) NOT NULL COMMENT '所属部门',
salary DOUBLE(10, 2) NOT NULL COMMENT '薪资'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT '员工表';

1. 向员工表插入如下数据：

INSERT INTO emp(`no`, name ,sex, age, dept, salary) VALUES(DEFAULT, '张三', '男',22, '研发部', 13000);
INSERT INTO emp(name ,sex, age, dept, salary) VALUES('李刚', '男', 24, '研发部',14000);
INSERT INTO emp VALUES(DEFAULT, '金凤', '女', 23, '财务部', 8000);
INSERT INTO emp(name ,sex, age, dept, salary) VALUES('肖青', '女', 26, '财务部',9000), ('张华', '男', 28, '研发部', 15000),('董钰', '女', 24, '研发部', 12000);
INSERT INTO emp VALUES(DEFAULT, '吴梅', '女', 24, '测试部', 9000),(DEFAULT, '王玲','女', 26, '测试部', 9500);

2. 吴梅因工作出色而被提升为测试主管，薪资调整为11000

UPDATE emp SET salary = 11000 WHERE name='吴梅';

3. 研发部金凤离职

DELETE FROM emp WHERE name='金凤';

4. 从员工表中查询出平均年龄小于25的部门

SELECT dept FROM emp GROUP BY dept HAVING AVG(age)<25;

5. 从员工表中统计研发部的最高薪资、最低薪资、平均薪资和总薪资

SELECT MAX(salary), MIN(salary), AVG(salary),SUM(salary) FROM emp WHERE dept='研发部';

6. 从员工表中统计各个部门的员工数量

SELECT dept, COUNT(*) FROM emp GROUP BY dept;

7. 从员工表中查询薪资在10000以上的员工信息并按薪资从高到低排列

SELECT * FROM emp WHERE salary > 10000 ORDER BY salary DESC;

8. 从员工表中分页查询员工信息，每页显示5条员工信息，按薪资从高到低排列，查询第2页员工信息

SELECT * FROM emp ORDER BY salary DESC LIMIT 5, 5;

三. MySQL常用函数

1.常用数学函数

2.常用字符串函数

查询计科和软工各有多少人
SELECT LEFT(class, 2), COUNT(*) FROM stu GROUP BY LEFT(class, 2);

查询名字有4个字的学生信息
SELECT * FROM stu WHERE CHAR_LENGTH(`name`)=4;

查询成绩能够被10整除的考试信息
SELECT * FROM score WHERE MOD(score, 10)=0;

3.日期和时间函数

查询年龄在20岁以上的学生信息
SELECT * FROM stu WHERE TIMESTAMPDIFF(YEAR, birthday, NOW()) > 20;

查询今天过生日的学生信息
SELECT * FROM stu WHERE MONTH(birthday)=MONTH(NOW()) AND DAYOFMONTH(birthday)=DAYOFMONTH(NOW());

查询本周过生日的学生信息
SELECT * FROM stu WHERE RIGHT(birthday, 5) > RIGHT(DATE_FORMAT(ADDDATE(NOW(), -DAYOFWEEK(NOW())), '%Y-%m-%d'), 5) AND RIGHT(birthday, 5) <= RIGHT(DATE_FORMAT(ADDDATE(NOW(),7-DAYOFMONTH(NOW())), '%Y-%m-%d'), 5);

4.条件判断函数

01.IF函数

01.01 IF(条件, 表达式1, 表达式2)

如果条件满足，则使用表达式1,否则使用表达式2

将学生成绩展示为及格和不及格
SELECT id,stu_name,course, IF(score>=60, '及格','不及格') score FROM score;

01.02 IFNULL(字段, 表达式)

如果字段值为空，则使用表达式，否则，使用字段值

将未参加考试的学生成绩展示为缺考
SELECT id,stu_name,course, IFNULL(score, '缺考') score FROM score;

02. CASE...WHEN 语句

02.01 CASE WHEN

 CASE WHEN 条件1 THEN 表达式1 [WHEN 条件2 THEN 表达式2 ...] ELSE 表达式n END
如果条件1满足，则使用表达式1；【如果条件2满足，则使用表达式2， ... 】否则，使用表达式n。相当于Javah和c++中的多重if..else语句。
SELECT (CASE WHEN (course = 'Java') THEN score ELSE 0 END) Java FROM score;

查询每位学生的各课程成绩
SELECT
    stu_name,
    course,
    MAX(CASE WHEN (course = 'Java') THEN score ELSE 0 END) Java,
    MAX(CASE WHEN (course = 'Html') THEN score ELSE 0 END) Html,
    MAX(CASE WHEN (course = 'Jsp') THEN score ELSE 0 END) Jsp,
    MAX(CASE WHEN (course = 'Spring') THEN score ELSE 0 END) Spring
FROM score
GROUP BY stu_name;

02.02练习

查询各班级性别人数，查询结果格式如图

SELECT
    class,
    SUM(CASE sex WHEN 0 THEN 1 ELSE 0 END) '男',
    SUM(CASE sex WHEN 1 THEN 1 ELSE 0 END) '女',
    SUM(CASE sex WHEN 2 THEN 1 ELSE 0 END) '其他'
FROM stu
GROUP BY class

5. 其他函数

01. 数字格式化函数

FORMAT(X,D)，将数字X格式化，将X保留到小数点后D位，截断时要进行四舍五入。

SELECT FORMAT(1.2353,2);

02. 系统信息函数

6.运用

1. 求字符串的字符数
SELECT CHAR_LENGTH('ABC');

2. 将字符串"超用心"和"在线教育"拼接成新的字符串
SELECT CONCAT("超用心", "在线教育");

3. 求"2019-05-04"到现在一共有多少天
SELECT TIMESTAMPDIFF(DAY, '2019-05-04', NOW());

4. 如果字段score的值大于90，则展示为优秀，否则展示为良好
SELECT IF(score>90, '优秀', '良好');

四.多表查询

1.表与表之间的关系

数据表是用来描述实体信息的，比如可以使用数据表来描述学生信息，也可以用数据表来描述班级信息，这样就会存在学生表和班级表。而学生和班级显然存在着一种关系：

这种关系在数据库中体现就称之为表与表之间的关系。数据库通过主外键关联关系来体现表与表之间的关联关系。

2.主外键关联关系

如图所示，此时学生表和班级表并没有任何关系，然而实际上学生和班级是存在归属关系。可以在学生表中添加一个字段，表名该学生所属班级，该字段值使用的是班级表中的主键，在学生表中称之为外键。这样学生表中的所属班级（外键）与班级表中的编号（主键）就产生关联关系，这种关联关系称为主外键关联关系。

3. 主外键关联关系的定义

DROP TABLE IF EXISTS cls;
CREATE TABLE cls(
    number INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '班级编号，主键',
    name VARCHAR(20) NOT NULL COMMENT '班级名称',
    grade VARCHAR(20) NOT NULL COMMENT '年级'
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='班级表';
DROP TABLE IF EXISTS student;
CREATE TABLE student(
    number BIGINT(20) AUTO_INCREMENT NOT NULL COMMENT '学号，主键',
    name VARCHAR(20) NOT NULL COMMENT '姓名',
    sex VARCHAR(2) DEFAULT '男' COMMENT '性别',
    age TINYINT(3) DEFAULT 0 COMMENT '年龄',
    cls_number INT(11) NOT NULL COMMENT '所属班级',
    PRIMARY KEY(number),
    -- 字段cls_number与cls表中的number字段相关联
    FOREIGN KEY(cls_number) REFERENCES cls(number)
)ENGINE=InnoDB CHARSET=UTF8 COMMENT='学生表';

4. 约束

4.1主键约束

-- 添加主键约束：保证数据的唯一性
ALTER TABLE 表名 ADD PRIMARY KEY(字段名1,字段名2, ..., 字段名n)
-- 删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;

4.2 外键约束

-- 添加外键约束
ALTER TABLE 表名1 ADD CONSTRAINT 外键名称 FOREIGN KEY(表名1的字段名) REFERENCES 表名2(表名2的字段名);
-- 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

4.3 唯一约束

-- 为字段添加唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名称 UNIQUE(字段名1, 字段名2, ..., 字段名n);
-- 删除字段的唯一约束
ALTER TABLE 表名 DROP KEY 约束名称;

4.4 非空约束

-- 为字段添加非空约束
ALTER TABLE 表名 MODIFY 字段名 列类型 NOT NULL;
-- 删除字段非空约束
ALTER TABLE 表名 MODIFY 字段名 列类型 NULL;

4.5 默认值约束

-- 为字段添加默认值
ALTER TABLE 表名 ALTER 字段名 SET DEFAULT 默认值;
-- 删除字段的默认值
ALTER TABLE 表名 ALTER 字段名 DROP DEFAULT;

4.6 自增约束

-- 为字段添加自增约束
ALTER TABLE 表名 MODIFY 字段名 列类型 AUTO_INCREMENT;
-- 为字段删除自增约束
ALTER TABLE 表名 MODIFY 字段名 列类型;

5. 索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是表中一列或多列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

5.1 索引的作用

保证数据的准确性
提高检索速度
提高系统性能

5.2 索引的类型

唯一索引（UNIQUE）：不可以出现相同的值，可以有NULL值
普通索引（INDEX）：允许出现相同的索引内容
主键索引（PRIMARY KEY）：不允许出现相同的值
全文索引（FULLTEXT INDEX）：可以针对值中的某个单词，但效率确实不敢恭维
组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

5.3 索引的创建、查看、删除

-- 创建索引
ALTER TABLE 表名 ADD INDEX 索引名称 (字段名1, 字段名2, ..., 字段名n);
-- 创建全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名称 (字段名1, 字段名2, ..., 字段名n);
-- 查看索引
SHOW INDEX FROM 表名;
-- 删除索引
ALTER TABLE 表名 DROP INDEX 索引名称;

5.4 使用索引的注意事项

虽然索引大大提高了查询速度，但也会降低更新表的速度，比如对表进行INSERT,UPDATE和DELETE操作，此时，数据库不仅要保存数据，还要保存一下索引文件

建立索引会占用磁盘空间的索引文件。如果索引创建过多（尤其是在字段多、数据量大的表上创建索引），就会导致索引文件过大，这样反而会降低数据库性能。因此，索引要建立在经常进行查询操作的字段上

不要在列上进行运算（包括函数运算），这会忽略索引的使用

不建议使用like操作，如果非使用不可，注意正确的使用方式。like '%查询内容%'不会使用索引，而like '查询内容%'可以使用索引

避免使用IS NULL、NOT IN、<>、!=、OR操作，这些操作都会忽略索引而进行全表扫描

6. 多表查询

6.1笛卡尔积

笛卡尔积又称为笛卡尔乘积，由笛卡尔提出，表示两个集合相乘的结果。

笛卡尔积与多表查询有什么关系呢？每一张表可以看做是一个数据的集合，多表关联串时，这些表中的数据就会形成笛卡尔积。

6.2 内连接

内连接相当于在笛卡尔积的基础上加上了连接条件。当没有连接条件时，内连接上升为笛卡尔积。

SELECT 字段名1, 字段名2, ..., 字段名n FROM 表1 [INNER] JOIN 表2 [ON 连接条件];
SELECT 字段名1, 字段名2, ..., 字段名n FROM 表1, 表2 [WHERE 关联条件 AND 查询条件];
示例：
SELECT COUNT(*) FROM stu INNER JOIN score ON stu.id=score.stu_id;
SELECT COUNT(*) FROM stu, score WHERE stu.id=score.stu_id;

6.3外连接

外连接涉及到两张表：主表和从表，要查询的信息主要来自于哪张表，哪张表就是主表。
外连接查询的结果为主表中所有的记录。如果从表中有和它匹配的，则显示匹配的值，这部分相当于内连接查询出来的结果；如果从表中没有和它匹配的，则显示null。
外连接查询的结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录
外连接分为左外连接和右外连接两种。左外连接使用LEFT JOIN关键字，LEFT JOIN左边的是主表；右外连接使用RIGHT JOIN关键字，RIGHT JOIN右边的是主表。

左外连接：SELECT 字段名1, 字段名2, ..., 字段名n FROM 主表 LEFT JOIN 从表 [ON 连接条件];
示例：SELECT * FROM stu a LEFT JOIN score b ON a.id=b.stu_id WHERE score IS NULL;

右外连接：SELECT 字段名1, 字段名2, ..., 字段名n FROM 从表 RIGHT JOIN 主表 [ON 连接条件];
示例：SELECT * FROM stu a RIGHT JOIN score b ON a.id=b.stu_id;

7. 子查询

子查询就是嵌套在其他查询中的查询。因此，子查询出现的位置只有3种情况：在SELECT... FROM 之间、在FROM...WHERE之间、在WHERE之后。

7.1 SELECT ... FROM之间

执行时机是在查询结果出来之后
查询stu表所有学生信息，并将性别按男、女、其他展示

SELECT
    id,
    `name`,
    (SELECT text FROM dict WHERE type='sex' AND value=sex) sex,
    birthday,
    class
FROM
    stu;

7.2 FROM ... WHERE 之间

执行时机是一开始就执行
查询年龄与Java成绩都与汤辰宇的年龄与Java成绩相同的学生信息

SELECT c.*, d.* FROM stu c
INNER JOIN
    score d ON c.id=d.stu_id
INNER JOIN
  (SELECT
       TIMESTAMPDIFF(YEAR, a.birthday,NOW()) age,
       b.score
  FROM stu a INNER JOIN score b ON a.id=b.stu_id
  WHERE a.name='汤辰宇'
  AND b.course='Java') e
ON TIMESTAMPDIFF(YEAR, c.birthday,NOW())=e.age AND d.score=e.score
WHERE d.course='Java';

7.3 WHERE 之后

执行时机是筛选数据时执行

查询Java成绩最高的学生信息
SELECT a.*, b.* FROM stu a INNER JOIN score b ON a.id=b.stu_id
WHERE b.score=(SELECT MAX(score) FROM score WHERE course='Java') AND b.course='Java';

五.存储过程、函数、触发器和视图

1.变量

在 MySQL 中，变量分为四种类型，即局部变量、用户变量、会话变量和全局变量。其中局部变量和用户变量在实际应用中使用较多，会话变量和全局变量使用较少，因此作为了解即可

1.1全局变量

MySQL 全局变量会影响服务器整体操作，当服务启动时，它将所有全局变量初始化为默认值。要想更改全局变量，必须具有管理员权限。其作用域为服务器的整个生命周期。

-- 显示所有的全局变量
SHOW GLOBAL VARIABLES;
-- 设置全局变量的值的两种方式
SET GLOBAL sql_warnings = ON;     -- GLOBAL不能省略
SET @@GLOBAL.sql_warnings = OFF;
-- 查询全局变量的值的两种方式
SELECT @@GLOBAL.sql_warnings;
SHOW GLOBAL VARIABLES LIKE '%sql_warnings%';

1.2 会话变量

MySQL 会话变量是服务器为每个连接的客户端维护的一系列变量。其作用域仅限于当前连接，因此，会话变量是独立的。

-- 显示所有的会话变量
SHOW SESSION VARIABLES;
-- 设置会话变量的值的三种方式
SET SESSION auto_increment_increment = 1;
SET @@SESSION.auto_increment_increment = 2;
-- 当省略SESSION关键字时，默认缺省为SESSION，即设置会话变量的值
SET auto_increment_increment = 3;
-- 查询会话变量的值的三种方式
SELECT @@auto_increment_increment;
SELECT @@SESSION.auto_increment_increment;
-- SESSION关键字可省略,也可用关键字LOCAL替代
SHOW SESSION VARIABLES LIKE '%auto_increment_increment%';
SET @@LOCAL.auto_increment_increment = 1;
SELECT @@LOCAL.auto_increment_increment;

1.3 用户变量

MySQL 用户变量， MySQL 中用户变量不用提前申明，在用的时候直接用“@变量名”使用就可以了。其作用域为当前连接。

-- 第一种用法，使用SET时可以用“=”或“:=”两种赋值符号赋值
SET @age = 19;
-- 第二种用法，使用SELECT时必须用“:=”赋值符号赋值
SELECT @age := 22;
SELECT @age := age FROM stu WHERE `name` = '张华';
-- 第三种用法，使用SELECT...INTO语句赋值
SELECT age INTO @age FROM stu WHERE `name` = '张华';
SELECT @age;

1.4 局部变量

MySQL 局部变量，只能用在BEGIN/END语句块中，比如存储过程中的BEGIN/END语句块。

-- 定义局部变量
DECLARE age INT(3) DEFAULT 0;
-- 为局部变量赋值
SET age = 10;
SELECT age := 10;
SELECT 10 INTO age;
SELECT age;

2. 存储过程

在大型数据库系统中，存储过程是一组为了完成特定功能而存储在数据库中的 SQL 语句集，一次编译后永久有效

2.1为什么要使用存储过程

运行速度快：
在存储过程创建的时候，数据库已经对其进行了一次解析和优化。存储过程一旦执行，在内存中就会保留一份这个存储过程，下次再执行同样的存储过程时，可以从内存中直接调用，所以执行速度会比普通 SQL 快。
减少网络传输：
存储过程直接就在数据库服务器上跑，所有的数据访问都在数据库服务器内部进行，不需要传输数据到其它服务器，所以会减少一定的网络传输。
增强安全性：
提高代码安全，防止 SQL 被截获、篡改。

2.2如何使用存储过程

语法

-- 声明分隔符
[DELIMITER $$]
CREATE PROCEDURE 存储过程名称 ([IN | OUT | INOUT] 参数名1 数据类型, [[IN | OUT | INOUT] 参数名2 数据类型, ..., [IN | OUT | INOUT] 参数名n 数据类型])
-- 语句块开始
BEGIN
-- SQL语句集
END[$$]
-- 还原分隔符
[DELIMITER ; ]
-- 调用存储过程
CALL 存储过程名(参数1,参数2,…);

示例：使用存储过程完成银行转账业务

-- 创建存储过程
CREATE PROCEDURE transfer(IN transferFrom BIGINT, IN transferTo BIGINT, IN transferMoney BIGINT(20))
BEGIN
   UPDATE account SET balance = balance - transferMoney WHERE account =
transferFrom;
   UPDATE account SET balance = balance + transferMoney WHERE account =
transferTo;
END
-- 调用存储过程
CALL transfer(123456, 123457, 2000);

如果转账账户余额不足，上面的 SQL 代码依然可以正常执行，只是执行完后，转账账户的余额变为了负数。这显然不符合常理。因此需要修正。

DROP PROCEDURE IF EXISTS transfer;
CREATE PROCEDURE transfer(IN transferFrom BIGINT, IN transferTo BIGINT, IN
transferMoney BIGINT(20))
BEGIN
  -- 定义变量表示执行结果：0-失败，1-成功
  DECLARE result TINYINT(1) DEFAULT 0;
  -- 转账账户必须保证余额大于等于转账金额
  UPDATE account SET balance = balance - transferMoney WHERE account =
transferFrom AND balance >= transferMoney;
  -- 检测受影响的行数是否为1，为1表示更新成功
  IF ROW_COUNT() = 1 THEN
   -- 目标账号余额增加
   UPDATE account SET balance = balance + transferMoney WHERE account = transferTo;
   IF ROW_COUNT() = 1 THEN
    -- 更新结果为1
    SET result = 1;
END IF;
  END IF;
  -- 查询结果
  SELECT result;
END

如果转账账户已经将钱转出去，而在执行目标账户增加余额的时候出现了异常或者目标账户输入错误，此时应该怎么办呢？
MySQL 对数据的操作提供了事务的支持，用来保证数据的一致性,可以有效的解决此类问题。

3. 事务

事务是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

3.1 事务的特性

原子性
事务的各元素是不可分的（原子的）,它们是一个整体。要么都执行，要么都不执行。
一致性
当事务完成时，必须保证所有数据保持一致状态。当转账操作完成时，所有账户的总金额应该保持不变，此时数据处于一致性状态；如果总金额发生了改变，说明数据处于非一致性状态。
隔离性
对数据操作的多个并发事务彼此独立，互不影响。比如张三和李四同时都在进行转账操作，但彼此都不影响对方。
持久性
对于已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障

3.2 事务解决银行转账问题

DROP PROCEDURE IF EXISTS transfer;
CREATE PROCEDURE transfer(IN transferFrom BIGINT, IN transferTo BIGINT, IN transferMoney BIGINT(20))
BEGIN
    -- 定义变量表示执行结果，默认为1
    DECLARE result TINYINT(1) DEFAULT 1;
    -- 声明SQLEXCEPTION处理器，当有SQLEXCEPTION发生时，错误标识符的值设为0
    -- 发生SQLEXCEPTION时的处理方式：CONTINUE，EXIT
    -- CONTINUE表示即使有异常发生，也会执行后面的语句
    -- EXIT表示，有异常发生时，直接退出当前存储过程
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result = 0;
    -- 开启事务
    START TRANSACTION;
    UPDATE account SET balance = balance - transferMoney WHERE account = transferFrom AND balance >= transferMoney;
    -- 设置result的值为SQL执行后受影响的行数
    SET result = ROW_COUNT();
    IF result = 1 THEN
      UPDATE account SET balance = balance + transferMoney WHERE account = transferTo;
      SET result = ROW_COUNT();
    END IF;
    -- 如果result的值为1，表示所有操作都成功，提交事务
    IF result = 1 THEN COMMIT;
    -- 否则，表示操作存在失败的情况，事务回滚，数据恢复到更改之前的状态
    ELSE ROLLBACK;
    END IF;
    SELECT result;
END

4. 存储过程输出

DROP PROCEDURE IF EXISTS transfer;
CREATE PROCEDURE transfer(IN transferFrom BIGINT, IN transferTo BIGINT, IN transferMoney BIGINT(20), OUT result TINYINT(1))
BEGIN
    -- 为SQL异常声明一个持续处理的处理器，一旦出现异常，则将result的值更改为0
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result = 0;
    -- 开启事务
    START TRANSACTION;
    UPDATE account SET balance = balance - transferMoney WHERE account = transferFrom AND balance >= transferMoney;
    -- 设置result的值为SQL执行后受影响的行数
    SET result = ROW_COUNT();
    IF result = 1 THEN
      UPDATE account SET balance = balance + transferMoney WHERE account = transferTo;
      SET result = ROW_COUNT();
    END IF;
    -- 如果result的值为1，表示所有操作都成功，提交事务
    IF result = 1 THEN COMMIT;
    -- 否则，表示操作存在失败的情况，事务回滚，数据恢复到更改之前的状态
    ELSE ROLLBACK;
    END IF;
END
-- 调用存储过程
CALL transfer(123456, 123457, 2000, @rs);
SELECT @rs;

5. 自定义函数

5.1 如何使用自定义函数

语法：

CREATE FUNCTION 函数名称 (参数名1 数据类型, 参数名2 数据类型, ..., 参数名n 数据类型])
RETURNS 数据类型
-- 函数特征：
-- DETERMINISTIC: 不确定的
-- NO SQL：没有SQL语句，当然也不会修改数据
-- READS SQL DATA： 只是读取数据，不会修改数据
-- MODIFIES SQL DATA：要修改数据
-- CONTAINS SQL：包含了SQL语句
DETERMINISTIC | NO SQL | READS SQL DATA | MODIFIES SQL DATA | CONTAINS SQL
-- 语句块开始
BEGIN
    -- SQL语句集
    RETURN 结果;
-- 语句块结束
END

示例：使用函数实现求score表中的成绩最大差值

CREATE FUNCTION getMaxDiff()
RETURNS DOUBLE(5, 2)
DETERMINISTIC
BEGIN
  RETURN (SELECT MAX(score) - MIN(score) FROM score);
END
-- 调用函数
SELECT getMaxDiff();

5.2 循环结构

WHILE 循环条件 DO
 -- SQL语句集
END WHILE；
REPEAT
  -- SQL语句集      
UNTIL 循环终止条件 END REPEAT;
标号: LOOP
   -- SQL语句集
    IF 循环终止条件 THEN LEAVE 标号;
    END IF;
END LOOP;

示例：使用函数实现求0~给定的任意整数的累加和

DROP FUNCTION IF EXISTS getTotal;
CREATE FUNCTION getTotal(maxNum INT(11))
RETURNS INT(11)
NO SQL
BEGIN
    DECLARE i INT(11) DEFAULT 0;
    DECLARE total INT(11) DEFAULT 0;
    WHILE i <= maxNum DO
        SET total = total + i;
        SET i = i + 1;
    END WHILE;
 RETURN total;
END
-- 调用函数
SELECT getTotal(100);

6. 触发器

触发器是用来保证数据完整性的一种方法，由事件来触发，比如当对一个表进行增删改操作时就会被激活执行。经常用于加强数据的完整性约束和业务规则

6.1 如何定义触发器

DROP TRIGGER [IF EXISTS] 触发器名称;
-- 创建触发器
-- 触发时机为BEFORE或者AFTER
-- 触发事件，为INSERT 、 UPDATE或者DELETE
CREATE TRIGGER 触发器名称 {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 FOR EACH ROW
BEGIN
-- 执行的SQL操作
END

6.2 触发器类型

触发器类型	NEW和OLD的使用
INSERT触发器	NEW表示将要或者已经新增的数据
UPDATE触发器	OLD表示将要或者已经修改的数据，NEW表示将要修改的数据
DELETE触发器	OLD表示将要或者已经删除的数据

6.3 触发器使用场景

场景一：现有商品表goods和订单表order，每一个订单的生成都意味着商品数量的减少，请使用触发器完成这一过程。

-- 如果存在增加订单的触发器，就将其删除掉
DROP TRIGGER IF EXISTS addOrder;
-- 创建触发器
CREATE TRIGGER addOrder AFTER INSERT ON `order` FOR EACH ROW
BEGIN
UPDATE goods SET number = number - NEW.sale_count WHERE id=NEW.goods_id;
END
-- 测试代码
INSERT INTO `order` (`goods_id`, `sales_id`, `sale_count`, `created_time`, `state`) VALUES (1, 1, 6, '2021-08-16', 1);

场景二：现有商品表goods和订单order，每一个订单的取消都意味着商品数量的增加，请使用触发器完成这一过程

-- 如果存在增加订单的触发器，就将其删除掉
DROP TRIGGER IF EXISTS deleteOrder;
-- 创建触发器
CREATE TRIGGER deleteOrder AFTER DELETE ON `order` FOR EACH ROW
BEGIN
    UPDATE goods SET number = number + OLD.sale_count WHERE id=OLD.goods_id;
END
-- 测试代码
DELETE FROM `order` WHERE id=350001;

场景三：现有商品表goods和订单表order，每一个订单购买数量的更新都意味着商品数量的变动，请使用触发器完成这一过程。

-- 如果存在增加订单的触发器，就将其删除掉
DROP TRIGGER IF EXISTS updateOrder;
-- 创建触发器
CREATE TRIGGER updateOrder AFTER UPDATE ON `order` FOR EACH ROW
BEGIN
    DECLARE changeNum INT(11) DEFAULT 0;
    SET changeNum = NEW.sale_count - OLD.sale_count;
    UPDATE goods SET number = number - changeNum WHERE id=OLD.goods_id;
END
-- 测试代码
UPDATE `order` SET sale_count = sale_count + 2 WHERE id=20;
UPDATE `order` SET sale_count = sale_count - 4 WHERE id=20;

7. 视图

视图是一张虚拟表，本身并不存储数据，当 SQL 操作视图时所有数据都是从其他表中查出来

7.1如何使用视图

-- 创建视图
CREATE VIEW 视图名称 AS SELECT 列1[,列2,…] FROM 表名 WHERE 条件;
-- 更新视图
CREATE OR REPLACE VIEW 视图名称 AS SELECT 列1[,列2,…] FROM 表名 WHERE 条件;
-- 删除视图
DROP VIEW IF EXISTS 视图名称;

7.2 为什么使用视图

定制用户数据，聚焦特定的数据。例如：如果频繁获取销售人员编号、姓名和代理商名称，可以创建视图

-- 删除视图
DROP VIEW IF EXISTS salesInfo;
-- 创建或者更新视图
CREATE OR REPLACE VIEW salesInfo AS
SELECT
    a.id,
    a.`name` saleName,
    b.`name` agentName
FROM
    sales a,
    agent b
WHERE
    a.agent_id = b.id;
-- 测试代码
SELECT id, saleName FROM salesInfo;

简化数据操作。例如：进行关联查询时，涉及到的表可能会很多，这时写的 SQL 语句可能会很长，如果这个动作频繁发生的话，可以创建视图

DROP VIEW IF EXISTS searchOrderDetail;
CREATE OR REPLACE VIEW searchOrderDetail AS
SELECT
    a.id regionId,
    a.`name` regionName,
    b.id agentId,
    b.`name` agentName,
    c.id saleId,
    c.`name` saleName,
    d.sale_count saleCount,
    d.created_time createdTime,
    e.`name` goodsName
FROM
    region a,
    agent b,
    sales c,
    `order` d,
    goods e
WHERE
    a.id = b.region_id
AND b.id = c.agent_id
AND c.id = d.sales_id
AND d.goods_id = e.id;
-- 测试代码
SELECT * FROM searchOrderDetail;

提高安全性能。例如：用户密码属于隐私数据，用户不能直接查看密码。可以使用视图过滤掉这一字段

DROP VIEW IF EXISTS userInfo;
CREATE OR REPLACE VIEW userInfo AS
SELECT
    username,
    salt,
    failure_times,
    last_log_time
FROM
    `user`;
SELECT username, salt FROM userInfo;

视图并不能提升查询速度，只是方便了业务开发，但同时也加大了数据库服务器的压力，因此，需要合理的使用视图