环境准备，通过以下建表
-- 创建数据库 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;
