MySQL 约束,视图,索引及常见函数

​​​​​​

​​​​​​​

2-MySQL 约束,视图,索引及常见函

1 SQL约束

SQL 约束用于规定表中的数据规则。实际上就是表中数据的限制条件。是为了保证数据的完整性而实现的一套机制。

MySQL的约束种类如下：

• 非空约束：NOT NULL

NOT NULL约束强制该字段不接受 NULL 值。

• 唯一约束：UNIQUE

UNIQUE 约束唯一标识数据库表中的每条记录。 即该字段的值不能重复，但null除外。

• 主键约束：PRIMARY KEY

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

• 主键、自动增长约束：PRIMARY KEY AUTO_INCREMENT

在每次插入新记录时，自动地创建主键字段的值。开始值是 1，每条新记录递增 1。

• 外键约束：FOREIGN KEY

FOREIGN KEY约束保证一个表中的数据匹配另一个表中的值的参照完整性。

• 默认约束：DEFAULT

DEFAULT约束用于保证该字段有默认值。

1.1 主键约束#

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

主键必须 唯一、非空。

每个表都应该有一个主键，并且每个表只能有一个主键。

作用:

​ 1）保证实体的完整性;

​ 2）加快数据库的操作速度

​ 3） 在表中添加新记录时，DBMS会自动检查新记录的主键值，不允许该值与其他记录的主键值重复。

​ 4) DBMS自动按主键值的顺序显示表中的记录。如果没有定义主键，则按输入记录的顺序显示表中的记录。

案例演示：

-- 创建学生表，将id设置为主键
CREATE TABLE student (
    s_id INT PRIMARY KEY,
    s_name VARCHAR(30),
    s_age INT
);

-- 显示student表的结构
DESC student;

-- 插入数据
INSERT INTO student(s_id, s_name, s_age) 
VALUES 
(1, "Tom", 23), 
(2, "Jerry", 24);

-- 查询所有数据
SELECT * FROM student;

-- 尝试在主键列插入重复值：报错
INSERT INTO student(s_id, s_name, s_age) 
VALUES 
(2, "Jhon", 25);

-- 如需撤销 PRIMARY KEY 约束
-- ALTER TABLE 表名 DROP PRIMARY KEY;
ALTER TABLE student DROP PRIMARY KEY;

-- 如果在建表后需要添加主键
-- ALTER TABLE 表名 ADD PRIMARY KEY(主键列名);
ALTER TABLE student ADD PRIMARY KEY (s_id);

1.2 主键自增约束#

在每次插入新记录时，自动地创建主键字段的值。

默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。

案例演示：

-- 创建教师表
CREATE TABLE teacher (
    t_id INT PRIMARY KEY AUTO_INCREMENT, -- 设置主键自增
    t_name VARCHAR(30)
);

-- 显示teacher表的结构
DESC teacher;

-- 插入数据: 主键列为空，插入数据时自动生成一个自增的数字
INSERT INTO teacher
VALUES
(NULL, "吴亦凡"),
(NULL, "王力宏");

SELECT * FROM teacher;

-- 如果需要改变自增的起始值
ALTER TABLE teacher AUTO_INCREMENT = 100;
-- 再次插入数据查看
INSERT INTO teacher
VALUES
(NULL, "玉田"),
(NULL, "王麻子");

-- 如需删除自增约束
-- ALTER TABLE 表名 MODIFY 列名 数据类型;
ALTER TABLE teacher MODIFY t_id INT;

-- 如果在建表后添加自增约束
-- ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;
ALTER TABLE teacher MODIFY t_id INT AUTO_INCREMENT;

-- 再次插入数据测试
INSERT INTO teacher
VALUES
(NULL, "小甜甜");

1.3 非空约束#

NOT NULL 约束强制列不接受 NULL 值。

NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

案例演示：

-- 创建员工表employee
CREATE TABLE employee (
    e_id INT PRIMARY KEY AUTO_INCREMENT,    -- 设置主键自增
    e_name VARCHAR(100) NOT NULL        -- 姓名列不能为空
);

DESC employee;

-- 插入数据
INSERT INTO employee
VALUES 
(NULL, "八戒"),
(NULL, "悟空");

SELECT * FROM employee;

-- 尝试插入一条空数据：报错，此时添加数据，标记 非空的字段，必须给值，否则约束不通过。
INSERT INTO employee
VALUES 
(NULL, NULL);

-- 如需删除非空约束
ALTER TABLE employee MODIFY e_name VARCHAR(100) NULL;

-- 如需在创建表后，添加非空约束
ALTER TABLE employee MODIFY e_name VARCHAR(100) NOT NULL;

1.4 默认约束#

default：当设置约束后，插入数据时如果不想给值，可以设置默认值。

当插入时，发现你没给值，可以使用默认值填充。

案例演示：

-- 创建学生信息表
CREATE TABLE student_info (
    s_id INT PRIMARY KEY AUTO_INCREMENT,    -- 设置主键自增
    s_name VARCHAR(100) NOT NULL,       -- 姓名列不能为空
  s_modify_time datetime default now()  -- 当添加数据时，如果没有给该列赋值，默认去当前时间
);

insert into student_info(s_id, s_name) values (null, 'tom');

select * from student_info;

1.5 唯一约束#

unique：字段数据必须唯一不重复、字段可以为NULL。

案例演示：

-- 创建用户表：规定用户名唯一
CREATE TABLE tb_user (
    u_id INT PRIMARY KEY AUTO_INCREMENT, -- 设置主键自增
    u_username VARCHAR(100) UNIQUE -- 用户名唯一
);

DESC tb_user;

-- 插入数据
INSERT INTO tb_user
VALUES
(NULL, "zhangsan"),
(NULL, "lisi");

SELECT * FROM tb_user;

-- 尝试插入重复值：报错
INSERT INTO tb_user
VALUES
(NULL, "zhangsan");

-- 如需删除唯一约束
-- ALTER TABLE 表名 DROP INDEX 列名
ALTER TABLE tb_user DROP INDEX u_username;

-- 如果在创建表后，添加唯一约束
-- ALTER TABLE 表名 ADD UNIQUE (列名)
ALTER TABLE tb_user ADD UNIQUE (u_username);
-- 注意：表中该列如果已经存在重复值，不能添加唯一约束。先删除重复值

1.6 外键约束#

一个表中的 FOREIGN KEY （外键）指向另一个表中的 UNIQUE KEY(唯一约束的键)。

我们通过一个实例来解释外键：

订单表（orderlist）：

id	number	uid
1	hn001	1
2	hn002	1
3	hn003	2
4	hn004	2
5	hn005	3
6	hn006	3

用户表（user）：

id	name
1	张三
2	李四
3	王五

订单表中的uid列指向user表中的id列，也就是说添加订单时，订单所属的用户必须是真实存在的；同理，如果要删除用户表中的数据，也必须保证订单表中已经没有该用户的订单。

外键约束能防止非法数据插入外键列，用于预防破坏表之间连接的行为。

示例：

-- 外键约束语法：CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)

-- 创建用户表（主表）
CREATE TABLE USER (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) NOT NULL
);

-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');

SELECT * FROM USER;

-- 创建订单表
CREATE TABLE orderlist (
    id INT PRIMARY KEY AUTO_INCREMENT, -- id
    number VARCHAR(20) NOT NULL, -- 订单编号
    uid INT, -- 外键列
    CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) -- 外键列uid指向user表中的主键id列
);

-- 订单表添加数据：外键列的值必须是主表user中存在的主键值
INSERT INTO orderlist 
VALUES
(NULL, "hn001", 1),
(NULL, "hn002", 1),
(NULL, "hn003", 2);
-- 如果不存在，添加失败
INSERT INTO orderlist VALUES (NULL,'hn005',3);

-- 删除李四用户，删除失败
DELETE FROM USER WHERE NAME='李四';

DESC orderlist;

-- 如需删除外键
-- 标准语法：ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;

-- 在创建表后，添加外键约束
-- 标准语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);

-- 当添加数据时，如果想取消外键约束的检查
SET FOREIGN_KEY_CHECKS = 0;

-- 恢复外键约束检查
SET FOREIGN_KEY_CHECKS = 1;

1.7 外键级联操作#

级联更新：

​ 当我们想把user用户表中的某个用户id修改，我们希望订单表中该用户所属的订单用户编号也随之修改。

级联删除：

​ 当我们想把user用户表中的某个用户删掉，我们希望该用户所有的订单也随之被删除。

-- 添加外键约束，同时添加级联更新  标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
ON UPDATE CASCADE;

-- 添加外键约束，同时添加级联删除  标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
ON DELETE CASCADE;

-- 添加外键约束，同时添加级联更新和级联删除  标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
ON UPDATE CASCADE ON DELETE CASCADE;

示例：

-- 继续使用上一节中的数据

-- 删除外键约束
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;

-- 添加外键约束，同时添加级联更新和级联删除
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE;

-- 将王五用户的id修改为5    订单表中的uid也随之被修改
UPDATE USER SET id=5 WHERE id=3;

-- 将王五用户删除     订单表中该用户所有订单也随之删除
DELETE FROM USER WHERE id=5;

2 多表查询

2.1 表与表的关系#

通过之前ER图的讲解，我们知道实体与实体的关系有三种，实体对应表，所以表与表的关系也有三种。

2.1.1 一对一#

一对一关系是建立在两张表之间的关系。一个表中的一条数据可以对应另一个表中的一条数据。

例如：一个人对应一张身份证，一张身份证对应一个人。

实现方式：

​ 在任意一个表建立外键，去关联另外一个表的主键。

示例：

-- 创建person表
CREATE TABLE person(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    NAME VARCHAR(20)                        -- 姓名
);
-- 添加数据
INSERT INTO person VALUES (NULL,'张三'),(NULL,'李四');

-- 创建card表
CREATE TABLE card(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    number VARCHAR(20) UNIQUE NOT NULL, -- 身份证号
    pid INT UNIQUE,                         -- 外键列
    CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id)
);
-- 添加数据
INSERT INTO card VALUES (NULL,'12345',1),(NULL,'56789',2);

注：一对一关系使用场景较少，因为在该案例的场景中，我们可以把身份证号码作为person表的一个字段。

2.1.2 一对多#

一对多关系是建立在两张表之间的关系。一个表中的一条数据可以对应另一个表中的多条数据，但另一个表中的一条数据只能对应第一张表的一条数据。

例如：

​ 一个班级拥有多个学生，一个学生只能够属于某个班级。

​ 一个用户可以有多个订单，一个订单只能属于某个用户。

实现方式：

​ 在多的一方，建立外键约束，来关联一的一方主键。注意：外键永远在多方。外键允许重复，允许含有空值。

示例：

-- 上一节讲解外键约束时，采用的用户和订单表即为一对多的关系

-- 班级和学生示例
-- 创建班级表
CREATE TABLE class (
    c_id INT PRIMARY KEY AUTO_INCREMENT,    -- 主键id
    c_name VARCHAR(100) NOT NULL,       -- 班级名称
    c_capacity INT                      -- 班级容量
);

-- 班级表插入数据
INSERT INTO class 
VALUES
(NULL, "大数据01", 80),
(NULL, "大数据02", 80),
(NULL, "大数据03", 60);

SELECT * FROM class;

-- 创建学生表
CREATE TABLE student (
    s_id INT PRIMARY KEY AUTO_INCREMENT,    -- 主键id
    s_name VARCHAR(100) NOT NULL,       -- 学生姓名
    cid INT,                            -- 外键列 学生所属班级
    CONSTRAINT sc_fk1 FOREIGN KEY (cid) REFERENCES class (c_id) -- 外键约束
);

DESC student;

-- 学生表插入数据（如果外键列所表示的班级id在班级表不存在，则数据插入失败）
INSERT INTO student
VALUES
(NULL, "张三", 1),
(NULL, "李四", 1),
(NULL, "王五", 2);

SELECT * FROM student;

2.1.3 多对多#

多对多关系是关系数据库中两个表之间的一种关系， 该关系中第一个表中的一个行可以与第二个表中的一个或多个行相关。第二个表中的一个行也可以与第一个表中的一个或多个行相关。

例如：

​ 学生表和课程表：一个学生可以选修多门课程，一个课程可以被多个学生选修。

​ 产品表和订单表：一个订单中可以包含多个产品，一个产品可能出现在多个订单中。

实现方式：

​ 创建第三个表，该表通常称为联接表，它将多对多关系划分为两个一对多关系。在该表中建立两个列，每个列作为外键参照各自的表的主键。

示例：

-- 创建student表
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    NAME VARCHAR(20)            -- 学生姓名
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四');

-- 创建course表
CREATE TABLE course(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    NAME VARCHAR(10)            -- 课程名称
);
-- 添加数据
INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');

-- 创建中间表
CREATE TABLE stu_course(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    sid INT,  -- 用于和student表中的id进行外键关联
    cid INT,  -- 用于和course表中的id进行外键关联
    CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 添加外键约束
    CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)   -- 添加外键约束
);
-- 添加数据
INSERT INTO stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);
-- 上面的关系表示id为1的学生选择了id为1和2的课程，而id为1的课程也被id为1和2的两个学生选择，多对多的关系

-- 练习 订单表和商品表
-- 创建订单表
CREATE TABLE t_order (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    order_number VARCHAR(20) NOT NULL,  -- 订单编号
    order_time TIMESTAMP                -- 下单时间
);

-- 创建商品表
CREATE TABLE t_product (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    product_name VARCHAR(100),      -- 商品名称
    product_price DOUBLE            -- 商品价格
);

-- 创建中间表（连接表表名一般情况下，取两张表的名字用下划线连接，方便管理）
CREATE TABLE t_order_product (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键id
    order_id INT,           -- 引用订单表id
    product_id INT,         -- 引用商品表id
    CONSTRAINT order_product_fk1 FOREIGN KEY (order_id) REFERENCES t_order (id),
    CONSTRAINT order_product_fk2 FOREIGN KEY (product_id) REFERENCES t_product (id)
);

-- 插入数据
INSERT INTO t_product
VALUES (NULL, "西游记", 88), (NULL, "三国演义", 77), (NULL, "水浒传", 99);

SELECT * FROM t_product;

INSERT INTO t_order
VALUES (NULL, "hn001", NULL), (NULL, "hn002", NULL), (NULL, "hn003", NULL);

SELECT * FROM t_order;

-- 插入中间表数据
INSERT INTO t_order_product
VALUES
(NULL, 1, 1),(NULL, 1, 3),(NULL, 2, 2),(NULL, 2, 3);
-- 表示：id为1的订单买了西游记和水浒传两本书；id为2的订单买了三国演义和水浒传两本书

SELECT * FROM t_order_product;

注意：在多对多关系中，插入数据时，先两侧再中间表；而删除数据时，要先中间后两侧。

2.2 多表查询#

2.2.1 准备数据#

重新给商品表和商品类别表初始化数据。

-- 创建category分类表
CREATE TABLE category (
  cid int comment '主键id',
  cname varchar(50) DEFAULT NULL comment '分类名称',
  PRIMARY KEY (cid)
);

-- 创建product 表，并声明 category 表的cid字段作为外键
CREATE TABLE product (
  pid int(11) NOT NULL AUTO_INCREMENT comment '主键id',
  pname varchar(500) DEFAULT NULL comment '产品名称',
  price DECIMAL(10,2) DEFAULT NULL comment '产品价格',
  cid int(11) DEFAULT NULL comment '产品所属类别',
  PRIMARY KEY (pid),
  CONSTRAINT `produce_cid_fk` FOREIGN KEY (`cid`) REFERENCES `category` (`cid`)
);
-- 向商品类别表中添加数据
insert into category (cid,cname) values(1,'家用电器/电脑'), (2,'男装/女装/童装/内衣'),(3,'女鞋/箱包/珠宝/钟表'),(4,'食品/酒类/生鲜/特产'),(5,'美妆/个护清洁/宠物');

-- 向商品表中添加数据
INSERT INTO product VALUES(null,' 联想（Lenovo）威6 14英寸商务轻薄笔记本电脑(i7-8550U 8G 256G PCIe SSD FHD MX150 Win10 两年上门)鲨鱼灰',5999,1);
INSERT INTO product VALUES(null,'联想(Lenovo)拯救者Y7000 15.6英寸游戏笔记本电脑(英特尔八代酷睿i5-8300H 8G 512G GTX1050 黑)',5999,1);
INSERT INTO product VALUES(null,'三洋（SANYO）9公斤智能变频滚筒洗衣机 臭氧除菌 空气洗 WiFi智能 中途添衣 Magic9魔力净',2499,1);
INSERT INTO product VALUES(null,'海尔（Haier) 滚筒洗衣机全自动 10公斤变频 99%防霉抗菌窗垫EG10014B39GU1',2499,1);
INSERT INTO product VALUES(null,'雷神(ThundeRobot)911SE炫彩版 15.6英寸游戏笔记本电脑（I7-8750H 8G 128SSD+1T GTX1050Ti Win10 RGB IPS）',6599,1);
INSERT INTO product VALUES(null,'七匹狼休闲裤男2018秋装新款纯棉男士直筒商务休闲长裤子男装 2775 黑色 32/80A',299,2);
INSERT INTO product VALUES(null,'真维斯JEANSWEST t恤男 纯棉圆领男士净色修身青年打底衫长袖体恤上衣 浅花灰 M',35,2);
INSERT INTO product VALUES(null,'PLAYBOY/花花公子休闲裤男弹力修身 秋季适中款商务男士直筒休闲长裤 黑色适中款 31(2.4尺)',128,2);
INSERT INTO product VALUES(null,'劲霸男装K-Boxing 短版茄克男士2018新款休闲舒适棒球领拼接青年夹克|FKDY3114 黑色 185',362,2); 
INSERT INTO product VALUES(null,'Chanel 香奈儿 女包 2018全球购 新款蓝色鳄鱼皮小牛皮单肩斜挎包A 蓝色',306830,3);
INSERT INTO product VALUES(null,'皮尔卡丹(pierre cardin)钱包真皮新款横竖款男士短款头层牛皮钱夹欧美商务潮礼盒 黑色横款（款式一）',269,3);
INSERT INTO product VALUES(null,'PRADA 普拉达 女士黑色皮质单肩斜挎包 1BD094 PEO V SCH F0OK0',28512,3);
INSERT INTO product VALUES(null,'好想你 干果零食 新疆特产 阿克苏灰枣 免洗红枣子 玛瑙红500g/袋',21.9,4);
INSERT INTO product VALUES(null,'三只松鼠坚果大礼包1588g每日坚果礼盒干果组合送礼火红A网红零食坚果礼盒8袋装',128,4);
INSERT INTO product VALUES(null,'三只松鼠坚果炒货零食特产每日坚果开心果100g/袋',32.8,4);
INSERT INTO product VALUES(null,'洽洽坚果炒货孕妇坚果零食恰恰送礼每日坚果礼盒（26g*30包） 780g/盒（新老包装随机发货)',149,4);
INSERT INTO product VALUES(null,'今之逸品【拍3免1】今之逸品双眼皮贴双面胶美目舒适隐形立显大眼男女通用 中号160贴',9.9,5);
INSERT INTO product VALUES(null,'自然共和国 原自然乐园 芦荟舒缓保湿凝胶300ml*2（约600g）进口补水保湿舒缓晒后修复面膜',72,5);

2.2.2 连接查询#

2.2.2.1 笛卡尔积查询#

笛卡尔积在SQL中的实现方式是交叉连接(Cross Join)。笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合，得到的结果集记录数是两个表记录数的乘积，这样的结果集也称笛卡尔积。

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

笛卡尔积是很可怕的，比如 A，B两表 个10000条数据，得到的笛卡尔积就有 10000 0000条。

示例：

-- 商品类别 5条数据
select count(*) from category;
-- 商品 18条数据
select count(*) from product;
-- 交叉查询 90条数据
select count(*) from product,category;
-- 详细数据
select * from product,category;

2.2.2.2 内连接查询#

内链接是查询两个表的交集的部分。

显示内连接#

标准语法：

SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;

示例：

-- 查看所有的商品信息，并且展示其所属分类信息
SELECT * FROM product p INNER JOIN category c ON p.cid = c.cid;

-- 加条件，查询名称中包含 坚果 信息的商品及所属分类信息
SELECT * FROM product p INNER JOIN category c ON p.cid = c.cid WHERE p.pname like '%坚果%';

隐式内连接#

标准语法：

SELECT 列名 FROM 表名1,表名2 where 关联条件;

示例：

-- 查看所有的商品信息，并且展示其所属分类信息
select * from product p, category c where p.cid = c.cid;

-- 加条件
select * from product p, category c where p.cid = c.cid and p.pname like '%坚果%';

推荐使用显示内连接。

内链接的问题：

向商品类别表插入一条数据

INSERT into category values (6,'手机/运营商/数码');

但是当我关联查询的时候

select DISTINCT c.* from category c inner join product p on c.cid = p.cid;

只能查询出5个类别

商品表只有这5个类别的数据。如何解决？ 用外连接。

2.2.2.3 外连接查询#

外连接可以把关联查询的两张表的一张表作为主表，另外一张作为从表，而外链接始终保证主表的数据完整。

外连接分三类：左外连接（left outer join）、右外连接（right outer join）和全外连接（full outer join），可以省略outer。

左外连接（left join）#

左外连接查询从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

语法：

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;
-- 注：其中的OUTER关键字可以省略

案例演示：

-- 查询所有的商品分类信息，并将该分类下所有的商品展示
SELECT * FROM category c LEFT OUTER JOIN product p on c.cid = p.cid;
-- 可以看到即使商品表中没有该分类的商品，也会展示分类表中的信息

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

右外连接（right join）#

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

语法：

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

案例演示：

-- 先给商品表插入一条数据
INSERT INTO product VALUES(null,'樱桃键盘',72,7);
-- 用右连接实现上面的查询结果
select * from product p right join category c on c.cid = p.cid;

2.2.2.4 多表查询综合案例#

1） 查询价格在一万以内名字中包含 '想' 的商品分类是什么

隐式外连接

-- 查询价格在一万以内名字中包含 '想' 的商品分类是什么
select distinct c.cname from product p,category c where p.cid=c.cid and p.price <10000 and p.pname like '%想%'

显式外连接

-- 查询价格在一万以内名字中包含 '想' 的商品分类是什么
select distinct c.cname from product p left join  category c on p.cid=c.cid where p.price<10000 and p.pname like '%想%';

2）查询所有分类商品的个数

左外连接 :

-- 先通过连接条件生成临时结果，然后再通过group by 汇总出最终结果
select c.cname,count(p.cid) num from category c
left join product p on c.cid = p.cid 
group by c.cname;

2.2.3 子查询#

有时一次查询查不出结果，需要将一次查询完的结果作为条件再进行查询。

子查询是指在查询语句中嵌套另一个查询，子查询可以支持多层嵌套。

对于一个普通的查询语句，子查询可以出现在两个位置。

1）出现在 from 语句后当成数据表#

语法：

SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];

示例：

-- 通过商品表统计商品共有多少分类
select count(*) from (select DISTINCT cid from product) t1;
select count(*) from (select cid from product group by cid) t1;

2）出现在where 条件后作为过滤条件的值#

子查询的结果是单行单列的

语法：

SELECT 列名 FROM 表名 WHERE 列名=(SELECT 列名 FROM 表名 [WHERE 条件]);

示例：

-- 类别为 家用电器/电脑的商品名称和价格
select pname, price from product 
where cid=(select cid from category where cname='家用电器/电脑');

子查询的结果是多行单列的

语法：

 SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); 

示例：

-- 查询商品价格大于5000的类别
select * from category where cid in (select cid from product where price>5000);

-- 获取所有商品中,平均价格大于1000的分类的全部商品
select * from product where cid in (select p.cid FROM product p group by p.cid HAVING avg(price) > 1000);

使用子查询要注意如下要点：

1）子查询要用括号括起来。

2）把子查询当成数据表时（出现在from 之后），可以为该子查询起别名，尤其是作为前缀来限定数据列时，必须给子查询起别名。

3）把子查询当成过滤条件时，将子查询放在比较运算符的右边，这样可以增强查询的可读性。

2.2.4 综合练习#

创建表：

--创建部门表
create table dept (deptid int primary key ,deptname varchar(20),address varchar(20)); 
insert into dept values(1,'研发部','北京'),(2,'人事部','上海'),(3,'销售部','深圳'),(4,'公关部','东莞');  
--创建员工表
create table emp(empid int primary key ,empname varchar(20),salary DECIMAL ,rizhidate date,mgr int,deptid int); 
insert into emp values(1001,'tom',18000,'2013-10-11',1005,1) ;
insert into emp values(1002,'jerry',13000,'2021-11-11',1005,1); 
insert into emp values(1003,'jack',10000,'2020-09-11',1001,1) ;
insert into emp values(1004,'rose',5000,'2020-10-11',1001,2) ;
insert into emp values(1005,'bob',20000,'2018-08-11',null,2);

需求

--查出部门的员工数和部门的名称 
select count(d.deptname),d.deptname from dept d join emp e on d.deptid=e.deptid group by deptname 
-- 公司最高的工资是多少，以及这个人是谁 
select empname,salary from emp where salary = (select max(salary)  from emp) 
-- 每个组最高的工资，以及是谁 
select emp.empname,emp.salary from (select max(salary) max,deptid from emp group by deptid) t join emp on t.deptid=emp.deptid and t.max=emp.salary 
--平均工资大于公司总平均工资的部门是哪个部门
select t1.deptid,t1.avg from (select avg(salary) avg,deptid from emp  group by deptid)  t1 join  (select avg(salary) avg from emp) t2 on t1.avg>t2.avg 
-- 求出哪个员工的工资大于本部门的平均工资 
select t2.empname,t2.salary,t2.deptid,t1.avg from (select avg(salary) avg,deptid from emp  group by deptid) t1 join emp t2 on t2.salary >t1.avg  and t1.deptid=t2.deptid

3 视图

什么是视图

​ 视图是一张虚拟表

​ 表示一张表的部分数据或多张表的综合数据

​ 其结构和数据是建立在对表的查询基础上

​ 视图中不存放数据

​ 数据存放在视图所引用的原始表中

​ 一个原始表，根据不同用户的不同需求，可以创建不同的视图

为什么使用视图

• 重用SQL语句
• 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
• 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。

规定及限制

• 因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时需要的所有检索
• 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字） 名称_view
• 对于可以创建的视图数目没有限制

视图语法

-- 创建视图
create view view_name as
<select 语句>;

-- 查询视图数据
select 字段1,字段2, ... from view_name

-- 删除视图
drop view view_name;

示例：

-- 查询 类别为 家用电器/电脑 且 商品价格>5000的商品信息
select pname, price from product 
where cid=(select cid from category where cname='家用电器/电脑') and  price > 5000;

-- 查询 类别为 家用电器/电脑 且 商品价格<5000的商品信息
select pname, price from product 
where cid=(select cid from category where cname='家用电器/电脑') and  price < 5000;

发现上面的两个查询有公共的部分，那我们就可以把公共部分创建视图，基于这个视图在来查询相应价格的数据

-- 创建视图 （查询 类别为 家用电器/电脑 的 商品信息）
create view cp1_view as 
select pname, price from product 
where cid=(select cid from category where cname='家用电器/电脑');

-- 在此基础上，用视图直接筛选价格>5000的商品
select * from cp1_view where price > 5000;

-- 在此基础上，用视图直接筛选价格<5000的商品
select * from cp1_view where price < 5000;

删除视图

drop view cp1_view;

4 索引

4.1 索引概述#

为什么要学索引

​ 如果新华字典没有汉语拼音、偏旁部首目录，你如何查找某个汉字？

​ 一页一页翻找，效率低

​ 如果带着汉语拼音、偏旁部首目录，你如何查找？

​ 先看汉语拼音目录，找到汉字对应的页数，直接找对应页码即可。利用索引检索，效率高

索引是什么

​ Mysql官方对索引的定义是：索引（Index）是帮助Mysql高效获取数据的数据结构。

​ 提取句子主干就是：索引是数据结构。

索引的目的

​ 索引的目的在于提高查询或检索效率。（拿空间换时间）

索引的优势

​ 提高数据检索效率，降低数据库IO成本。

​ 降低数据排序的成本，降低CPU的消耗。

索引的劣势

​ 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用磁盘和内存空间

​ 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息 。

4.2 MySQL的索引存储结构#

MySQL默认使用的是B+树存储结构，如下图所示：

其中：

​ 非叶子节点只存储键值。

​ 只有叶子节点才会存储数据，数据存储的是非主键的数据。

​ 叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

优势：

​ 范围查询特别方便，只需要在叶子节点利用有序链表查找范围即可。

4.3 MySQL索引分类#

• 普通索引： 最基本的索引，它没有任何限制。
• 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。
• 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。
• 组合索引：顾名思义，就是将单列索引进行组合。
• 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
• 全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。

4.4 MySQL 索引使用#

索引常见语法

-- 创建索引
create table 表名(字段名 字段类型... index  索引名 (类名))
或者
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型]  -- 默认是B+TREE
ON 表名(列名...);
或者
ALTER TABLE 表名 ADD INDEX 索引名称(列名);

-- 查看索引
SHOW INDEX FROM 表名;

-- 删除索引
DROP INDEX 索引名称 ON 表名;

案例演示：

--创建普通索引 
create table index1(id int,name varchar(20) , gender varchar(20),index (id)) ; 
insert into index1 values(1,'zs','female') 
--执行计划 
explain select * from index1 where name='zs'; 
explain select id from index1 where id =10; 
--创建唯一索引 
create table index2(id int unique,name varchar(20) ,gender varchar(20), unique index un_index (id)) ; 
insert into index2 values(1,'zs','female') 
--执行计划 
explain select * from index2 where name='zs'; 
explain select id from index2 where id =1; 
--创建联合索引 
create table index3(id int unique,name varchar(20) , gender varchar(20), index n_g_index (name,gender)) ; 
insert into index3 values(1,'zs','female') 
--执行计划 
explain select * from index3 where name='zs'; 
explain select id from index3 where gender ='female'; 
explain select id from index3 where name='zs'and gender ='female';

2）查看某个表的索引

show index from product;

部分结果说明

Table：创建索引的表

Non_unique：索引是否非唯一

Key_name：索引的名称

Column_name：定义索引的列字段

Seq_in_index：该列在索引中的位置

Null：该列是否能为空值

Index_type：索引类型

3）删除索引

DROP INDEX index_product_price on product;
show create table

4.5 MySql创建索引的使用技巧#

创建索引的指导原则

(一) 按照下列标准选择建立索引的列

1. 频繁搜索的列
2. 经常用作查询选择的列
3. 经常排序、分组的列
4. 经常用作连接的列（主键/外键）

(二) 使用索引时注意事项

1. 查询时减少使用*返回全部列，不要返回不需要的列
2. 索引应该尽量小，在字节数小的列上建立索引
3. WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前
4. 避免在ORDER BY子句中使用表达式

4.6 索引失效的场景#

1）当在查询条件中出现 <>、NOT、in、not exists 时，查询时更倾向于全表扫描。

2）在查询条件中有or 也不走索引，尽量不使用。

3）查询条件使用LIKE通配符

​ SQL语句中，使用后置通配符会走索引，例如查询姓张的学生（select from student where name LIKE 'hai%'），而前置通配符(select from student where name LIKE '%niu')会导致索引失效而进行全表扫描。

4）在索引列上使用函数 或 计算。

5）索引列数据类型不匹配。比如：字段类型是string， 但条件值不是string。

案例演示：

--索引失效场景 
explain select * from  index1 where id <> 1 
explain select * from index1  where id in (1,2,3,4,5); 
explain select * from index1 where id = 1 or id = 2 or id = 3 
explain select * from index1 where id like "%1234%" 
explain select * from index1 where id+1 = 2 
explain select * from index1 where id = '123' 

5 关键字

5.1UNION 操作符#

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

语法：

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

注意：

UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

UNION 会自动去除多个结果中的重复结果，如果允许重复的值，使用 UNION ALL。

示例：

-- 查询分类为 '家用电器/电脑' 的 价格大于3000的 商品信息
SELECT p.pid, p.pname, p.price, c.cid, c.cname
FROM product p join category c
on p.cid = c.cid
WHERE c.cname = '家用电器/电脑' and p.price > 200

-- 查询分类为 '男装/女装/童装/内衣' 的 价格大于200的 商品信息； 
SELECT p.pid, p.pname, p.price, c.cid, c.cname
    FROM product p join category c
    on p.cid = c.cid
    WHERE c.cname = '男装/女装/童装/内衣' and p.price > 200

-- 将上面两条语句的查询结果合并
SELECT p.pid, p.pname, p.price, c.cid, c.cname
FROM product p join category c
ON p.cid = c.cid
WHERE c.cname = '家用电器/电脑' and   p.price > 200
UNION
SELECT p.pid, p.pname, p.price, c.cid, c.cname
FROM product p join category c
on p.cid = c.cid
WHERE c.cname = '男装/女装/童装/内衣' and p.price > 200

-- 如果有重复数据，UNION ALL会保留重复

5.2 distinct去重#

--查询商品表汇总有多少种不同的价格
select distinct price from product 

5.3 not与exists#

exists判断在数据集中是否存在

--在商品表中查找在category表中存在分类的product数据 
select * from product where cid not in (select cid from category) 
select * from product p where not exists (select * from category c where p.cid = c.cid)

6 常用函数

6.1 字符串相关函数#

1. CONCAT(s1,s2...sn) 链接字符串

   select concat('hello','world');

2. CONCAT_WS(x, s1,s2...sn)间隔符链接

   select concat_ws('*','a','b','c') 

3. LOWER(s)转换小写

   select LOWER("HELLO") 

4. UPPER转换为大写

   select UPPER("hello") 

5. trim去除字符串的左右部分的空格

   select trim(" hello ") 

6. SUBSTRING(s, start, length) /SUBSTR(s,start,length) 截取字符串

   select substring('1234567',2,4) 

7. REPLACE(s,s1,s2)替换字符串字段

   select replace('12121abc123122123','abc','xxxxx') 

8. REVERSE翻转

   select reverse('123') 

6.2 数字类型函数#

1. max

2. min

3. avg

4. sum

5. rand()随机数

   select rand()

6. mod()余数

   select mod(12,9)

7. floor()向下取整

   select floor(3.14)

8. ceil向上取整

   select ceil(3.14)

9. 数字格式化，保留指定位数的小数

   select format(2.2252222,2) 

6.3 日期函数#

1. CURRENT_DATE() 当前日期

2. CURRENT_TIME() 当前时间

3. CURRENT_TIMESTAMP()当前时间 相当于上面两个的总体

4. datediff(dat1,dat2)时间间隔天数

   select datediff('2022-10-11','2022-10-01')

5. DATE_ADD 计算起始日期 d 加上一个时间段后的日期

   SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);   -> 2017-06-25 
   SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE); -> 2017-06-15 09:49:21 
   SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR); ->2017-06-15 06:34:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR); ->2017-04-15 

6. DATE_FORMAT(d,f) 格式化日期

   select date_format(CURRENT_TIMESTAMP(),'%Y-%m-%d ')

6.4 高级函数#

1. case when

   语法：

   CASE 
   WHEN  条件值1 THEN result1  
   WHEN 条件值2 THEN result2   
   ...    
   WHEN 条件值n THEN resultN    
   ELSE result END as 字段名
   

   -- 给入职3年的员工薪资+10000
   select ename, workyears, 
   case when workyears > 3 then salary+10000 
   else salary end as salary 
   from (select ename,salary,floor(datediff(current_date(),hiredate)/365) as workyears from t_emp) t

2. if

   语法：IF(expr,v1,v2)

   select ename, workyears, if(workyears>3,salary+10000,salary) as salary 
   from (select ename,salary,floor(datediff(current_date(),hiredate)/365) as workyears from t_emp)t

7 行列转换

7.1 行转列#

即将原本同一列下多行的不同内容作为多个字段，输出对应内容。

准备数据：

-- 创建成绩表
CREATE TABLE row_to_column_score (
    id INT(11) NOT NULL auto_increment,
    userid VARCHAR(20) NOT NULL COMMENT '用户id',
    subject VARCHAR(20) COMMENT '科目',
    score DOUBLE COMMENT '成绩',
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

-- 添加数据
INSERT INTO row_to_column_score(userid,subject,score) VALUES ('001','语文',90);
INSERT INTO row_to_column_score(userid,subject,score) VALUES ('001','数学',92);
INSERT INTO row_to_column_score(userid,subject,score) VALUES ('001','英语',80);
INSERT INTO row_to_column_score(userid,subject,score) VALUES ('002','语文',88);
INSERT INTO row_to_column_score(userid,subject,score) VALUES ('002','数学',90);
INSERT INTO row_to_column_score(userid,subject,score) VALUES ('002','英语',75.5);
INSERT INTO row_to_column_score(userid,subject,score) VALUES ('003','语文',70);
INSERT INTO row_to_column_score(userid,subject,score) VALUES ('003','数学',85);
INSERT INTO row_to_column_score(userid,subject,score) VALUES ('003','英语',90);
INSERT INTO row_to_column_score(userid,subject,score) VALUES ('003','政治',82);

-- 查看全部
select * from row_to_column_score;
+----+--------+---------+-------+
| id | userid | subject | score |
+----+--------+---------+-------+
|  1 | 001    | 语文    |    90 |
|  2 | 001    | 数学    |    92 |
|  3 | 001    | 英语    |    80 |
|  4 | 002    | 语文    |    88 |
|  5 | 002    | 数学    |    90 |
|  6 | 002    | 英语    |  75.5 |
|  7 | 003    | 语文    |    70 |
|  8 | 003    | 数学    |    85 |
|  9 | 003    | 英语    |    90 |
| 10 | 003    | 政治    |    82 |
+----+--------+---------+-------+
10 rows in set (0.00 sec)

先来查看转换后的结果：

+--------+--------+--------+--------+--------+
| userid | 语文   | 数学   | 英语   | 政治   |
+--------+--------+--------+--------+--------+
| 001    |     90 |     92 |     80 |      0 |
| 002    |     88 |     90 |   75.5 |      0 |
| 003    |     70 |     85 |     90 |     82 |
+--------+--------+--------+--------+--------+

可以看出，这里行转列是将原来的subject字段的多行内容选出来，作为结果集中的不同列，并根据userid进行分组显示对应的score。

1、使用case...when....then 进行行转列

select userid,
sum(case  when subject='语文' then score else 0 end) as '语文',
sum(case  when subject='数学' then score else 0 end) as '数学',
sum(case  when subject='英语' then score else 0 end) as '英语',
sum(case  when subject='政治' then score else 0 end) as '政治'
from row_to_column_score
group by userid;

2、使用IF() 进行行转列：

select userid,
sum(if(subject = '语文',score,0)) as '语文',
sum(if(subject = '数学',score,0)) as '数学',
sum(if(subject = '英语',score,0)) as '英语',
sum(if(subject = '政治',score,0)) as '政治'
from row_to_column_score
group by userid;

注意：

SUM() 是为了能够使用GROUP BY根据userid进行分组，因为每一个userid对应的subject="语文"的记录只有一条，所以SUM() 的值就等于对应那一条记录的score的值。

7.2 列转行#

准备数据：

-- 创建成绩表
CREATE TABLE column_to_row_score (
    id INT(11) NOT NULL auto_increment,
    userid VARCHAR(20) NOT NULL COMMENT '用户id',
    cn_score DOUBLE COMMENT '语文成绩',
    math_score DOUBLE COMMENT '数学成绩',
    en_score DOUBLE COMMENT '英语成绩',
    po_score DOUBLE COMMENT '政治成绩',
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

-- 插入数据
INSERT INTO column_to_row_score(userid,cn_score,math_score,en_score,po_score) VALUES ('001',90,92,80,0);
INSERT INTO column_to_row_score(userid,cn_score,math_score,en_score,po_score) VALUES ('002',88,90,75.5,0);
INSERT INTO column_to_row_score(userid,cn_score,math_score,en_score,po_score) VALUES ('003',70,85,90,82);

-- 查看全部
select * from column_to_row_score;
+----+--------+----------+------------+----------+----------+
| id | userid | cn_score | math_score | en_score | po_score |
+----+--------+----------+------------+----------+----------+
|  1 | 001    |       90 |         92 |       80 |        0 |
|  2 | 002    |       88 |         90 |     75.5 |        0 |
|  3 | 003    |       70 |         85 |       90 |       82 |
+----+--------+----------+------------+----------+----------+
3 rows in set (0.00 sec)

先查看转换后的结果：

+--------+--------+-------+
| userid | course | score |
+--------+--------+-------+
| 001    | 数学   |    92 |
| 001    | 语文   |    90 |
| 001    | 政治   |     0 |
| 001    | 英语   |    80 |
| 002    | 政治   |     0 |
| 002    | 英语   |  75.5 |
| 002    | 数学   |    90 |
| 002    | 语文   |    88 |
| 003    | 政治   |    82 |
| 003    | 英语   |    90 |
| 003    | 数学   |    85 |
| 003    | 语文   |    70 |
+--------+--------+-------+

看以看出，转换是将userid的每个科目分数分散成一条记录显示出来。

实现：

select userid, '语文' as course, cn_score as score from column_to_row_score
union all
select userid, '数学' as course, math_score as score from column_to_row_score
union all
select userid, '英语' as course, en_score as score from column_to_row_score
union all
select userid, '政治' as course, po_score as score from column_to_row_score
order by userid;

这里其实是将每个userid对应的多个科目的成绩查出来，通过UNION将结果合并起来，达到上面的效果。

8 分组后的topN

准备数据：

-- 创建学生成绩表
CREATE TABLE `stu_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,     -- id
  `name` varchar(20) DEFAULT NULL,          -- 姓名
  `course` varchar(20) DEFAULT NULL,        -- 课程名称
  `score` int(11) DEFAULT NULL,             -- 成绩
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- 插入数据
insert into stu_score(name,course,score) values
('张三','语文',80),
('李四','语文',90),
('王五','语文',93),
('张三','数学',77),
('李四','数学',68),
('王五','数学',99),
('张三','英语',90),
('李四','英语',50),
('王五','英语',89);

需求1：查询每门课程分数最高的学生以及成绩

-- 使用自关联查询
select a.* 
from stu_score a 
join (select course, max(score) max_score from stu_score group by course) b 
on a.course = b.course and a.score = max_score;
/*
分析：
    上面的查询，是首先按照课程分组，将每门课程的最高分先查询出来，虚拟化成一张表
    然后使用原成绩表与该表关联，条件是两张表的课程和成绩相同
    即查询出每门课程最高成绩的学生信息
*/

-- 使用子查询
select a.* from stu_score a where score = (select max(score) from stu_score b where a.course = b.course);
/*
分析：
    课程相同的条件下，查询最高的成绩
    再查询成绩为最高成绩的学生信息
*/

+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
| 12 | 王五   | 语文   |    93 |
| 15 | 王五   | 数学   |    99 |
| 16 | 张三   | 英语   |    90 |
+----+--------+--------+-------+

需求2：查询每门课程前两名的学生以及成绩

-- 使用自关联查询
select a.*
from stu_score a 
left join stu_score b
on a.course = b.course and a.score < b.score
group by a.id
having count(b.id) < 2
order by a.course, a.score desc;

/*
解释：
    左表a + 右表b
    首先查询所有课程相同，但是a的成绩小于b的成绩的数据；
    然后按照a表中的数据分组；
    分组后统计每条数据（即每条学生成绩信息），在b表中对应的数据 < 2 (如果有一个比当前学生成绩高的，就出现1条，所以要求前2名，比当前学生成绩高的应该小于2，这样就取到了top2)
    最后按照课程和成绩排序，使结果好看   
*/

-- 使用子查询
select a.*
from stu_score a
where (select count(*) from stu_score b where a.course = b.course and a.score < b.score) < 2
order by a.course, a.score desc;
/*
分析：
    课程相同的条件下，查询a表成绩比b表小的
    判断条件 这样的数据少于2条，那么a表中该记录就是top2
*/

+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
| 15 | 王五   | 数学   |    99 |
| 13 | 张三   | 数学   |    77 |
| 16 | 张三   | 英语   |    90 |
| 18 | 王五   | 英语   |    89 |
| 12 | 王五   | 语文   |    93 |
| 11 | 李四   | 语文   |    90 |
+----+--------+--------+-------+