🍖记录相关操作之多表查询(+综合练习)
引入
MySQL是关系型数据库, 表与表之间是可以建立联系的, 相关联的两张表或多张表的查询就需要使用一些方法, 下面介绍 MySQL 的多表查询 :
- 多表连接查询
- 交叉连接
- 内连接
- 外连接
- 符合条件连接查询
- 子查询
一.创建两张表(准备工作)
五个部门, 九个员工(这个随意), 为了实验方便, 设置一个部门没有员工(FI), 设置一个员工不属于任何部门(Lina)
create table dep(
id int primary key auto_increment,
name varchar(16)
);
insert dep(name) value("TE"),("CFT"),("ST"),("HR"),("FI");
create table emp(
id int primary key auto_increment,
name varchar(16),
sex enum("male","female") not null default "male",
age int,
dep_id int
);
insert emp(name,sex,age,dep_id) value
("shawn","male",22,1),
('Chair','female',48,1),
('jack','male',18,2),
('Anni','male',28,2),
('Rub','male',18,2),
('Summer','female',18,3),
('Tom','male',18,4),
('Bob','male',18,4),
('Lina','female',18,6);
二.多表连接查询
0.外连接语法与笛卡尔积是什么
-
外连接语法
select [字段列表]
from [表1] inner|left|right join [表2] # inner|left|right 这三个就对应的内|左|右连接
on [表1].[字段] = [表2].[字段]; # on 子句后面接的是连接条件
-
笛卡尔积
🥒笛卡尔积(Cartesian product),指两个集合的乘积
x = {1,2} # 集合 x
y = {4,5,6} # 集合 y
x * y = {(1,4),(1,5),(1,6),(2,4),(2,5),(2,6)} # x 乘 y 的结果
y * x = {(4,1),(4,2),(5,1),(5,2),(6,1),(6,2)} # y 乘 x 的结果
1.交叉连接
- 交叉连接不适用与任何匹配查询, 只能可以生成笛卡尔积
mysql> select * from emp,dep;
+----+--------+--------+------+--------+----+------+
| id | name | sex | age | dep_id | id | name |
+----+--------+--------+------+--------+----+------+
| 1 | shawn | male | 22 | 1 | 1 | TE |
| 1 | shawn | male | 22 | 1 | 2 | CFT |
| 1 | shawn | male | 22 | 1 | 3 | ST |
| 1 | shawn | male | 22 | 1 | 4 | HR |
| 1 | shawn | male | 22 | 1 | 5 | FI |
| 2 | Chair | female | 48 | 1 | 1 | TE |
| 2 | Chair | female | 48 | 1 | 2 | CFT |
| 2 | Chair | female | 48 | 1 | 3 | ST |
| 2 | Chair | female | 48 | 1 | 4 | HR |
| 2 | Chair | female | 48 | 1 | 5 | FI |
| 3 | jack | male | 18 | 2 | 1 | TE |
| 3 | jack | male | 18 | 2 | 2 | CFT |
| 3 | jack | male | 18 | 2 | 3 | ST |
| 3 | jack | male | 18 | 2 | 4 | HR |
| 3 | jack | male | 18 | 2 | 5 | FI |
| 4 | Anni | male | 28 | 2 | 1 | TE |
| 4 | Anni | male | 28 | 2 | 2 | CFT |
| 4 | Anni | male | 28 | 2 | 3 | ST |
| 4 | Anni | male | 28 | 2 | 4 | HR |
| 4 | Anni | male | 28 | 2 | 5 | FI |
| 5 | Rub | male | 18 | 2 | 1 | TE |
| 5 | Rub | male | 18 | 2 | 2 | CFT |
| 5 | Rub | male | 18 | 2 | 3 | ST |
| 5 | Rub | male | 18 | 2 | 4 | HR |
| 5 | Rub | male | 18 | 2 | 5 | FI |
| 6 | Summer | female | 18 | 3 | 1 | TE |
| 6 | Summer | female | 18 | 3 | 2 | CFT |
| 6 | Summer | female | 18 | 3 | 3 | ST |
| 6 | Summer | female | 18 | 3 | 4 | HR |
| 6 | Summer | female | 18 | 3 | 5 | FI |
| 7 | Tom | male | 18 | 4 | 1 | TE |
| 7 | Tom | male | 18 | 4 | 2 | CFT |
| 7 | Tom | male | 18 | 4 | 3 | ST |
| 7 | Tom | male | 18 | 4 | 4 | HR |
| 7 | Tom | male | 18 | 4 | 5 | FI |
| 8 | Bob | male | 18 | 4 | 1 | TE |
| 8 | Bob | male | 18 | 4 | 2 | CFT |
| 8 | Bob | male | 18 | 4 | 3 | ST |
| 8 | Bob | male | 18 | 4 | 4 | HR |
| 8 | Bob | male | 18 | 4 | 5 | FI |
| 9 | Lina | female | 18 | 6 | 1 | TE |
| 9 | Lina | female | 18 | 6 | 2 | CFT |
| 9 | Lina | female | 18 | 6 | 3 | ST |
| 9 | Lina | female | 18 | 6 | 4 | HR |
| 9 | Lina | female | 18 | 6 | 5 | FI |
+----+--------+--------+------+--------+----+------+
45 rows in set (0.00 sec)
2.内连接
- 只连接两张表匹配的行(两张表共有的部分), 相当于是从笛卡尔积中筛选出正确的结果
- 语句使用 : 内连接使用
inner join关键字连接, 并使用on子句设置连接条件 - 注意 : 当对多个表进行查询时，要在 select 语句后面指定字段是来源自哪一张表
语法为表名.列名，如果表名较长，可以给表设置别名，这样就可以直接在 select 后写表的别名.列名
select emp.id,emp.name,emp.age,emp.sex,dep.name
from emp inner join dep
on emp.dep_id=dep.id;
# 还可以下面这种写法
select emp.id,emp.name,emp.sex,emp.age,dep.name
from emp,dep
where emp.dep_id=dep.id;
上面发现 FI 部门没有任何员工与之匹配, 所以就没有显示该条记录
员工 Lina 也没有与任何部门匹配, 所以也没有显示该条记录
3.外连接之左连接
- 优先显示左表全部记录, 如果左表中的某行在右表中没有匹配的行, 那么在显示的结果中, 右表未匹配的字段为 NULL
- 使用语法 : 左连接使用
left join连接两表, 后面使用on子句设置连接条件
select emp.id,emp.name,dep.name
from emp left join dep
on emp.dep_id=dep.id;
4.外连接之右连接
- 优先显示右表全部记录, 如果右表中的某行在左表中没有匹配的行, 那么在显示的结果中, 左表未匹配的字段为 NULL
- 使用语法 : 左连接使用
right join连接两表, 后面使用on子句设置连接条件
select emp.id,emp.name,dep.name
from emp right join dep
on emp.dep_id=dep.id;
5.全外连接
- 显示左右两表全部记录, 在内连接的基础上增加两边没有的结果
- 注意 : 全连接关键字
full join, mysql 不支持全外连接, 可以使用union合并左连接和右连接结果产生与全连接相同的效果 union与union all的区别 :union会去掉相同的记录
select emp.id,emp.name,dep.name
from emp left join dep
on emp.dep_id=dep.id
union
select emp.id,emp.name,dep.name
from emp right join dep
on emp.dep_id=dep.id;
三.符合条件查询
设置条件, 符合条件的才显示结果
1.示例1
- 内连接方式查询出年龄大于 25 的员工及员工名所在的部门名
select emp.name,dep.name
from emp inner join dep
on emp.dep_id=dep.id
where emp.age>25;
2.示例2
- 内连接方式查询出年龄大于 25 的员工名及部门名, 并且以 age 字段升序
select emp.name,dep.name
from emp inner join dep
on emp.dep_id=dep.id
where emp.age>25
order by emp.age; # 默认升序(asc), 降序(dEsc)
四.子查询
0.子语句介绍
- 子查询时将一个查询语句嵌套在另一个查询语句中
- 内层查询语句的查询结果, 可以为外层查询语句提供查询条件
- 子查询中包含的关键字 : IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS 等关键字
- 还可以包含比较运算符 : =、 != 、 >、 <、等
1.帶 IN 关键字的子查询
注意 : 特别注意not in结果集中不能有null,not in的结果集中出现null则查询结果为null
- 查询个部门员工平均年龄在 25 以上的部门名
select id,name from dep
where id in
(select dep_id from emp group by dep_id having avg(age)>18);
- 查看 CFE 岗位所有员工的姓名
select name from emp
where dep_id in
(select id from dep where name="CFT");
- 查看部门人数不足3人的部门名
select name from dep
where id in
(select dep_id from emp
where dep_id
group by dep_id
having count(id)<3);
2.带ANY、ALL关键字的子查询
- **=any **与 in 效果相等 (查看部门人数不足三人的部门名)
select name from dep
where did =any
(select dep_id from emp
group by dep_id
having count(id)<3);
- 求只要能大于任意一个部门平均年龄的员工
select * from emp where age > any
(select avg(age) from emp
group by dep_id);
- 求大于所有部门平均年龄的员工
select * from emp where age > all
(select avg(age) from emp
group by dep_id);
3.带比较运算符的子查询
- 查询大于所有员工平均年龄的员工名与年龄
select name,age from emp
where age>(select avg(age) from emp);
- 查询大于部门平均年龄的员工名与年龄
select emp.name,emp.age
from emp inner join (select dep_id,avg(age) as avg_age from emp group by dep_id) as id_avg
on emp.dep_id=id_avg.dep_id
where emp.age>id_avg.avg_age;
4. 带EXISTS关键字的子查询
- EXISTS关字键字表示存在
- 在使用EXISTS关键字时, 内层查询语句不返回查询的记录, 而是返回一个真假值, True或False
- 当返回True时，外层查询语句将进行查询; 当返回值为False时, 外层查询语句不进行查询
- 相当于一个循环嵌套,先外层循环一次,内层再完完整整的循环一遍(上面的in,any,all..等等都是先拿到内层的一个结果集)
# dep 表中存在 "FI" 这个部门 : TRUE
select * from emp
where exists
(select name from dep where name="FI");
# dep 表中存在 "IO" 这个部门 : False
select * from emp
where exists
(select name from dep where name="IO");
五.练习
1.查询每个部门最新入职的员工信息
- 准备表,并插入数据
create database emp_dep;
use emp_dep
create table emp(
id int primary key auto_increment,
emp_name varchar(16) not null,
sex enum("male","female") default "male",
age int unsigned not null,
entry_date date not null,
post varchar(20),
post_des varchar(100) default "暂无说明...",
salary float(10,2),
office int,
dep_id int
);
insert emp(emp_name,sex,age,entry_date,post,salary,office,dep_id) value
("shawn","male",23,'20190804','TE',5100.5,324,1), # 测试工程师
('start','male',18,'20170301','TE',7300.33,401,1),
('Ann','male',78,'20150302','TE',1000000.31,401,1),
('Bella','male',81,'20130305','TE',8300,401,1),
('tony','male',73,'20140701','TE',3500,401,1),
('Alice','male',28,'20121101','TE',2100,401,1),
('jack','female',18,'20110211','ST',9000,401,2), # 系统工程师
('Cara','male',18,'19000301','ST',30000,401,2),
('Hedy','male',48,'20101111','ST',10000,401,2),
('Dora','female',48,'20150311','ST',3000.13,402,2),
('Sam','female',38,'20101101','ST',2000.35,402,2),
('Otis','female',18,'20110312','CFT',1000.37,402,3), # 程式测试工程师
('Ray','female',18,'20160513','CFT',3000.29,402,3),
('Chris','female',28,'20170127','CFT',4000.33,402,3),
('Summer','male',28,'20160311','CFT',10000.13,403,3),
('Rub','male',18,'19970312','CFT',20000,403,3),
('Luck','female',18,'20130311','HR',19000,403,4), # 人力资源
('Bob','male',18,'20150411','HR',18000,403,4),
('Tom','female',18,'20140512','HR',17000,403,4);
- 使用链表查询
select * from emp
inner join
(select id,post,max(entry_date) from emp
group by post
having max(entry_date))as t2
on emp.id=t2.id;
- 使用子查询
select * from emp as t3
where id in
(select
(select id from emp as t2
where t2.post=t1.post
order by entry_date desc
limit 1) # 显示一条记录(如果两个员工同时入职就不准确)
from emp as t1
group by post);
六.综合练习
1.题目
1、查询所有的课程的名称以及对应的任课老师姓名
2、查询学生表中男女生各有多少人
3、查询物理成绩等于100的学生的姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
5、查询所有学生的学号，姓名，选课数，总成绩
6、查询姓李老师的个数
7、查询没有报李平老师课的学生姓名
8、查询物理课程比生物课程高的学生的学号
9、查询没有同时选修物理课程和体育课程的学生姓名
10、查询挂科超过两门(包括两门)的学生姓名和班级
11、查询选修了所有课程的学生姓名
12、查询李平老师教的课程的所有成绩记录
13、查询全部学生都选修了的课程号和课程名
14、查询每门课程被选修的次数
15、查询之选修了一门课程的学生姓名和学号
16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
17、查询平均成绩大于85的学生姓名和平均成绩
18、查询生物成绩不及格的学生姓名和对应生物分数
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
20、查询每门课程成绩最好的前两名学生姓名
21、查询没学过“李平”老师课程的学生姓名以及选修的课程名称
2.创建表
- 将下面数据复制到一个文件中, 以"sql"为后缀
/*
数据导入：
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
- 从 class_tea_stu_sou.sql 中直接导入数据
create database cla_stu_tea;
use cla_stu_tea
source J:\MySql\mysql-5.6.48-winx64\data\sql_file\class_tea_stu_sou.sql # 这里填的是文件路径
3.题解
- 1.查询所有的课程的名称以及对应的任课老师姓名
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
- 2.查询学生表中男女生各有多少人
SELECT
gender,
count( sid )
FROM
student
GROUP BY
gender;
- 3.查询物理成绩等于100的学生的姓名
SELECT
sname
FROM
student
WHERE
sid IN ( SELECT student_id FROM score WHERE course_id = ( SELECT cid FROM course WHERE cname = "物理" ) AND num = 100 );
- 4.查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
sname,
t1.avg_num
FROM
student
INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY student_id HAVING avg( num )> 80 ) AS t1 ON student.sid = t1.student_id;
- 5.查询所有学生的学号，姓名，选课数，总成绩
SELECT
student.sid,
sname,
count( course_id ),
sum( num )
FROM
student
LEFT JOIN score ON student.sid = score.student_id
GROUP BY
student.sid;
- 6.查询姓李老师的个数
SELECT
count( tid )
FROM
teacher
WHERE
tname LIKE "李%";
- 7.查询没有报李平老师课的学生姓名
SELECT
sname
FROM
student
WHERE
student.sid NOT IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
teacher_id IN ( SELECT tid FROM teacher WHERE tname = "李平老师" )));
- 8.查询物理课程分比生物课程分高的学生的学号
SELECT
t2.student_id
FROM
(
SELECT
student_id,
course_id,
num
FROM
score
WHERE
course_id = ( SELECT cid FROM course WHERE cname = "生物" )) AS t1
INNER JOIN (
SELECT
student_id,
course_id,
num
FROM
score
WHERE
course_id = ( SELECT cid FROM course WHERE cname = "物理" )) AS t2 ON t1.student_id = t2.student_id
AND t2.num > t1.num;
- 9.查询没有同时选修物理课程和体育课程的学生姓名
SELECT
sname
FROM
student
WHERE
student.sid NOT IN (
SELECT
t1.student_id
FROM
(
SELECT
*
FROM
score
WHERE
course_id = ( SELECT cid FROM course WHERE cname = "物理" )) AS t1
INNER JOIN (
SELECT
*
FROM
score
WHERE
course_id = ( SELECT cid FROM course WHERE cname = "体育" )) AS t2 ON t1.student_id = t2.student_id
);
- 10.查询挂科超过两门(包括两门)的学生姓名和班级
SELECT sname,( SELECT caption FROM class WHERE class.cid = student.class_id )
FROM
student
WHERE
student.sid = (
SELECT
t1.student_id
FROM
( SELECT * FROM score WHERE num < 60 ) AS t1
GROUP BY
t1.student_id
HAVING
count( t1.course_id )>= 2
);
- 11.查询选修了所有课程的学生姓名
SELECT
sname
FROM
student
WHERE
student.sid IN ( SELECT student_id FROM score GROUP BY student_id HAVING count( course_id )= 4 );
- 12.查询李平老师教的课程的所有成绩记录
SELECT
*
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
teacher_id IN ( SELECT tid FROM teacher WHERE tname = "李平老师" ));
- 13.查询全部学生都选修了的课程号和课程名
SELECT
cid,
cname
FROM
course
WHERE
cid IN (
SELECT
course_id
FROM
score
GROUP BY
course_id
HAVING
count( student_id )> ( SELECT count( student.sid ) FROM student ));
# 这里查询为空, 因为没有一个课程是所有学生都选了的
- 14.查询每门课程被选修的次数
SELECT
t1.course_id,
course.cname,
t1.count
FROM
( SELECT course_id, count( student_id ) AS count FROM score GROUP BY course_id ) AS t1
INNER JOIN course ON t1.course_id = course.cid;
- 15.查询之选修了一门课程的学生姓名和学号
SELECT
sid,
sname
FROM
student
WHERE
sid IN ( SELECT student_id FROM score GROUP BY student_id HAVING count( course_id )= 1 );
- 16.查询所有学生考出的成绩并按从高到低排序（成绩去重）
SELECT DISTINCT
num
FROM
score
ORDER BY
num DESC;
- 17.查询平均成绩大于85的学生姓名和平均成绩
SELECT
sname,
t1.avg
FROM
student
INNER JOIN ( SELECT student_id, avg( num ) AS avg FROM score GROUP BY student_id HAVING avg( num )> 85 ) AS t1 ON sid = t1.student_id;
- 18.查询生物成绩不及格的学生姓名和对应生物分数
SELECT
sname,
t1.num
FROM
student
INNER JOIN ( SELECT student_id, num FROM score WHERE course_id = ( SELECT cid FROM course WHERE cname = "生物" ) AND num < 60 ) AS t1 ON sid = t1.student_id;
- 19.查询在有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
SELECT
sname
FROM
student
INNER JOIN (
SELECT
t3.student_id,
max( _avg ) AS max_avg
FROM
(
SELECT
t2.student_id,
avg( num ) AS _avg
FROM
(
SELECT
student_id,
num
FROM
score
INNER JOIN (
SELECT
cid
FROM
course
WHERE
teacher_id IN ( SELECT tid FROM teacher WHERE tname = "李平老师" )) AS t1 ON t1.cid = score.course_id
) AS t2
GROUP BY
t2.student_id
) AS t3
HAVING
max( _avg )) AS t4 ON t4.student_id = student.sid;
- 20.查询每门课程成绩最好的前两名学生姓名
SELECT
sname,
t5.*
FROM
student
INNER JOIN (
SELECT
score.student_id,
t4.course_id,
t4.first_num,
t4.second_num
FROM
score
INNER JOIN (
SELECT
t3.course_id,
t3.first_num,
t2.second_num
FROM
( SELECT course_id, max( num ) AS first_num FROM score GROUP BY course_id ) AS t3
INNER JOIN (
SELECT
score.course_id,
max( num ) AS second_num
FROM
score
INNER JOIN ( SELECT course_id, max( num ) AS first_num FROM score GROUP BY course_id ) AS t1 ON score.course_id = t1.course_id
WHERE
score.num < t1.first_num
GROUP BY
course_id
) AS t2 ON t3.course_id = t2.course_id
) AS t4 ON score.course_id = t4.course_id
WHERE
score.num >= t4.second_num
AND score.num <= t4.first_num
ORDER BY
course_id
) AS t5 ON sid = t5.student_id;
- 21.查询没学过“李平”老师课程的学生姓名以及选修的课程名称
SELECT
t2.sname,
course.cname
FROM
course
INNER JOIN (
SELECT
t1.sid,
t1.sname,
course_id
FROM
score
INNER JOIN (
SELECT
sid,
sname
FROM
student
WHERE
student.sid NOT IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
WHERE
teacher_id IN ( SELECT tid FROM teacher WHERE tname = "李平老师" )))) AS t1 ON score.student_id = t1.sid
) AS t2 ON t2.course_id = course.cid;
---end---
