【如何成为SQL高手】第九关：高级复杂查询

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程，B站及腾讯课堂讲师，直播量破10W
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

SQL对于现在的互联网公司产研岗位几乎是一个必备技能，但仅会SQL的话，应该是什么都做不了。
1.如果你是数据分析师，你需要熟练地把自己脑子里的数据和指标需求翻译成SQL逻辑去查询数据，进而完成自己的数据分析报告等，你的产出是分析报告，而不是SQL代码；
2.如果你是数仓工程师（偏应用层），你需要根据业务逻辑去设计模型，编写调度任务去产出数据，以供业务人员使用，你的产出是数据模型和表；
3.如果你是算法工程师，你可能需要用SQL来实现用户标签、特征工程等工作，但是这些是为你的模型训练评估做基础准备工作，你的产出是可以提升某些指标的算法模型。

所以，SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。
IT邦德老师带你成为SQL高手，那我们开始吧~

文章目录

环境准备，通过以下建表

-- 创建数据库
CREATE DATABASE mes_db charset utf8mb4;
-- 切换数据库
USE mes_db;
-- 创建学生表
CREATE TABLE student (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE, -- 生日
class VARCHAR(20) -- 所在班级
);
-- 创建教师表
CREATE TABLE teacher (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE,
profession VARCHAR(20) NOT NULL, -- 职称
department VARCHAR(20) NOT NULL -- 部门
);
-- 创建课程表
CREATE TABLE course (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
t_no VARCHAR(20) NOT NULL, -- 教师编号
-- 表示该 t_no 来自于 teacher 表中的 no 字段值
FOREIGN KEY(t_no) REFERENCES teacher(no)
);
-- 成绩表
CREATE TABLE score (
s_no VARCHAR(20) NOT NULL, -- 学生编号
c_no VARCHAR(20) NOT NULL, -- 课程号
degree DECIMAL, -- 成绩
-- 表示该 s_no, c_no 分别来自于 student, course 表中的 no 字段值
FOREIGN KEY(s_no) REFERENCES student(no),
FOREIGN KEY(c_no) REFERENCES course(no),
-- 设置 s_no, c_no 为联合主键
PRIMARY KEY(s_no, c_no)
);
-- 查看所有表
SHOW TABLES;
-- 添加学生表数据
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王飘飘', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');

-- 添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');

-- 添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');

-- 添加添加成绩表数据
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');

⛳️1.ANY用法

查询课程 3-105 且成绩至少高 3-245 的 score 表，DESC ( 降序 )

#ANY: 符合 SQL 语句中的任意条件
#也就是说，3-105成绩中，只要有一个大于从3-245筛选出来的任意行就符合条件
#最后根据降序查询结果
SELECT * FROM score WHERE c_no = ‘3-105’ AND degree > ANY(
SELECT degree FROM score WHERE c_no = ‘3-245’
) ORDER BY degree DESC;

⛳️2.ALL的用法

查询课程 3-105 且成绩高于 3-245 的 score 表

#只需对上一道题稍作修改。
#ALL: 符合 SQL 语句中的所有条件。
#也就是说，在3-105成绩中，都要大于从3-245筛选出来全部行才算符合条件
SELECT * FROM score WHERE c_no = ‘3-105’ AND degree > ALL(
SELECT degree FROM score WHERE c_no = ‘3-245’);

⛳️3. 自连接

查询某课程成绩比该课程平均成绩低的 score 表
#将表 b 作用于表 a 中查询数据
#score 别名a,b 如此就能用 a.c_no=b.c_no 作为条件执行查询
SELECT * FROM score a WHERE degree < (
(SELECT AVG(degree) FROM score b WHERE a.c_no = b.c_no));

⛳️4. 排序

😆 4.1 多字段排序

以 class 和 birthday 从大到小的顺序查询 student 表
SELECT * FROM student ORDER BY class DESC, birthday;

😆 4.2 等级处理

建立一个 grade 表代表学生的成绩等级，并插入数据：
CREATE TABLE grade (
low INT(3),
upp INT(3),
grade char(1)
);
INSERT INTO grade VALUES (90, 100, ‘A’);
INSERT INTO grade VALUES (80, 89, ‘B’);
INSERT INTO grade VALUES (70, 79, ‘C’);
INSERT INTO grade VALUES (60, 69, ‘D’);
INSERT INTO grade VALUES (0, 59, ‘E’);
SELECT * FROM grade;
–查询所有学生的 s_no 、c_no 和 grade 列
SELECT s_no, c_no, grade FROM score, grade
WHERE degree BETWEEN low AND upp;

😆 4.3 排名

create table scores_tb (
id int auto_increment primary key,
xuehao int not null,
score int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into scores_tb (xuehao,score)
values (1001,89),
(1002,99),
(1003,96),
(1004,96),
(1005,92),
1006,90),
(1007,90),
(1008,94);
select * from scores_tb;

MySQL8.0 中可以利用 ROW_NUMBER()，DENSE_RANK()，RANK()
三个窗口函数实现上述三种排名

普通排名：
按分数高低直接排名，从 1 开始，往下排，类似于 row number
select xuehao,score,
ROW_NUMBER() OVER(order by score desc)
as row_r from scores_tb;

分数相同，名次相同，排名无间隔
select xuehao,score,
DENSE_RANK() OVER(order by score desc) as dense_r
from scores_tb;

并列排名，排名有间隔
select xuehao,score,
RANK() over(order by score desc) as r
from scores_tb;

大家点赞、收藏、关注、评论啦 👇🏻👇🏻👇🏻微信公众号👇🏻👇🏻👇🏻