MySQL—子查询（五）

一、数据准备

-- 数据准备
-- 1 创建教师表
CREATE TABLE teacher(
tea_num VARCHAR(10) PRIMARY KEY COMMENT "教师编号",-- 主键
name VARCHAR(30) COMMENT "姓名",
gender CHAR(2) COMMENT "性别",
age INT COMMENT "年龄",
department VARCHAR(30) COMMENT "部门",
institute VARCHAR(50) COMMENT "学院",
salary DECIMAL(10,2) COMMENT "薪水",
hire_date date COMMENT "入职时间"
);

-- 1.1 插入数据
-- 教师号为100001，姓名为旗木卡卡西，性别男，年龄30，科室为综合科，就职学院为火影学院，薪水2000，入职日期为1988-12-21
INSERT INTO teacher
VALUES("100001","旗木卡卡西","男",30,"综合科","火影学院",2000.56,"1988-12-21");
-- 教师号为100002，姓名为迈特凯，性别男，年龄31，科室为体术科，就职学院为火影学院，薪水2100，入职日期为1987-10-21
INSERT INTO teacher
VALUES("100002","迈特凯","男",31,"体术科","火影学院",2100.58,"1987-10-21");
-- 教师号为100003，姓名为孙悟空，性别男，年龄100，科室为猴科，就职学院为西游学院，薪水1500，入职日期为1966-11-01
INSERT INTO teacher
VALUES("100003","孙悟空","男",100,"猴科","西游学院",1500.123,"1966-11-01");
-- 教师号为100004，姓名为马基，性别男，年龄42，科室为风科，就职学院为风影学院，薪水3000，入职日期为1975-04-21
INSERT INTO teacher
VALUES("100004","马基","男",42,"风科","风影学院",3000.222,"1975-04-21");

-- 2 创建学生表
CREATE TABLE student(
stu_num VARCHAR(10) PRIMARY KEY COMMENT "学号", -- 主键
name VARCHAR(30) COMMENT "姓名",
gender CHAR(2) COMMENT "性别",
age INT COMMENT "年龄",
birthday DATE COMMENT "出生日期",
major VARCHAR(30) COMMENT "专业",
institute VARCHAR(50) COMMENT "学院",
fee DECIMAL(8,2) COMMENT "学费",
tea_num VARCHAR(10) COMMENT "教师编号",
create_time datetime COMMENT "创建时间",
FOREIGN KEY (tea_num) REFERENCES teacher(tea_num) -- 外键
);

-- 2.1 插入数据
INSERT INTO student
VALUES("2023000001","鸣人","男",12, '2001-01-02',"影分身","火影学院",20000.23,"100001",NOW());
INSERT INTO student
VALUES("2023000000","宇智波佐助","男",13, '2001-07-12',"写轮眼","火影学院",456123.21,"100001",NOW());
INSERT INTO student
VALUES('2023000002','小樱', '女', 14, '2001-05-18', '治疗', '火影学院', 12345.21,"100001",'2023-01-12 20:04:11');
INSERT INTO student
VALUES("2023000003","我爱罗","男",16, "1999-12-31","砂之术","风影学院",40000,"100004",NOW());
INSERT INTO student(stu_num,name,gender,age,birthday,major,institute,fee,tea_num,create_time)
VALUES("2023000004","小李","男",NULL, NULL,"体术","火影学院",10000.88,"100002",NOW());
INSERT INTO student
VALUES('2023000005','手鞠', '女', 18, '1995-01-25', '三星扇', '风影学院',5652.12,"100004",NOW());

二、实操练习

-- 实操练习
-- 1 单行子查询
-- 1.1 执行单行子查询
-- 1.1.1 查询学费比鸣人高的学生姓名及对应的学费
SELECT name,fee
FROM student
WHERE fee > (SELECT fee
						 FROM student
						 WHERE name = "鸣人");
-- 1.2 子查询使用组函数
-- 1.2.1 查询学费最多的学生的学号、姓名和学费
SELECT stu_num,name,fee
from student
WHERE fee = (SELECT MAX(fee)
						 FROM student);
-- 1.3 子查询中的HAVING子句
-- 1.3.1 查询学费大于火影学院最低学费的学生信息
SELECT *
FROM student
where fee > (SELECT MIN(fee)
						 FROM student
						 GROUP BY institute
						 HAVING institute = "火影学院");
-- 1.4 子查询中的空值问题
-- 1.4.1 查询学费大于水影学院最高学费的学生信息
SELECT *
FROM student
where fee > (SELECT MAX(fee)
						 FROM student
						 GROUP BY institute
						 HAVING institute = "水影学院");

-- 2 多行子查询
-- 2.1 使用IN操作符
-- 2.1.1 查询在综合科和风科部门工作的老师所教授的学生
SELECT *
FROM student
WHERE tea_num IN (SELECT tea_num
									FROM teacher
									WHERE department IN ("综合科","风科"));

-- 2.2 使用ALL操作符
-- 2.2.1 查询学费大于风影学院学生的学生信息
SELECT *
FROM student 
WHERE fee > ALL (SELECT fee
								 FROM student
								 WHERE institute = "风影学院");


-- 等同于
SELECT *
FROM student 
WHERE fee > ALL (SELECT MAX(fee)
								 FROM student
								 WHERE institute = "风影学院");