12 记录相关操作之多表查询

756次阅读
没有评论

共计 15574 个字符,预计需要花费 39 分钟才能阅读完成。

引入

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);

12 记录相关操作之多表查询

二. 多表连接查询

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;

12 记录相关操作之多表查询

上面发现 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;

12 记录相关操作之多表查询

4. 外连接之右连接

  • 优先显示右表全部记录, 如果右表中的某行在左表中没有匹配的行, 那么在显示的结果中, 左表未匹配的字段为 NULL
  • 使用语法 : 左连接使用 right join 连接两表, 后面使用 on 子句设置连接条件
select emp.id,emp.name,dep.name 
    from emp right join dep
    on emp.dep_id=dep.id;

12 记录相关操作之多表查询

5. 全外连接

  • 显示左右两表全部记录, 在内连接的基础上增加两边没有的结果
  • 注意 : 全连接关键字 full join, mysql 不支持全外连接, 可以使用 union 合并左连接和右连接结果产生与全连接相同的效果
  • unionunion 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;

12 记录相关操作之多表查询

三. 符合条件查询

设置条件, 符合条件的才显示结果

1. 示例 1

  • 内连接方式查询出年龄大于 25 的员工及员工名所在的部门名
select emp.name,dep.name 
    from emp inner join dep 
    on emp.dep_id=dep.id 
    where emp.age>25;

12 记录相关操作之多表查询

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)

12 记录相关操作之多表查询

四. 子查询

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);

12 记录相关操作之多表查询

  • 查看 CFE 岗位所有员工的姓名
select name from emp
    where dep_id in
        (select id from dep where name="CFT");

12 记录相关操作之多表查询

  • 查看部门人数不足 3 人的部门名
select name from dep
    where id in
        (select dep_id from emp 
             where dep_id
             group by dep_id 
             having count(id)<3);

12 记录相关操作之多表查询

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);

12 记录相关操作之多表查询

  • 求只要能大于任意一个部门平均年龄的员工
select * from emp where age > any
    (select avg(age) from emp 
        group by dep_id);

12 记录相关操作之多表查询

  • 求大于所有部门平均年龄的员工
select * from emp where age > all
    (select avg(age) from emp
        group by dep_id);

12 记录相关操作之多表查询

3. 带比较运算符的子查询

  • 查询大于所有员工平均年龄的员工名与年龄
select name,age from emp
    where age>(select avg(age) from emp);

12 记录相关操作之多表查询

  • 查询大于部门平均年龄的员工名与年龄
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;

12 记录相关操作之多表查询

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");

12 记录相关操作之多表查询

# dep 表中存在 "IO" 这个部门 : False
select * from emp
    where exists
        (select name from dep where name="IO");

12 记录相关操作之多表查询

五. 练习

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;

12 记录相关操作之多表查询

  • 使用子查询
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);

12 记录相关操作之多表查询

六. 综合练习

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;

12 记录相关操作之多表查询

  • 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  # 这里填的是文件路径

12 记录相关操作之多表查询

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---

正文完
 
shawn
版权声明:本站原创文章,由 shawn 2023-06-16发表,共计15574字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
评论(没有评论)