mysql模拟题三_mysql经典45题（3刷）

Mysql 经典练习题

我使用的Mysql版本是5.7.27-log，答案可能会因版本会有少许出入。

一、数据源准备及说明：

1.1、数据字段说明:

1.学生表 Student(SId,Sname,Sage,Ssex)

SId ：学生编号

Sname：学生姓名

Sage ：出生年月

Ssex：学生性别

2.课程表 Course(CId,Cname,TId)

CId ：课程编号

Cname ：课程名称

TId ：教师编号

3.教师表 Teacher(TId,Tname)

TId ：教师编号

Tname ：教师姓名

4.成绩表 SC(SId,CId,score)

SId ：学生编号

CId ：课程编号

score： 分数

1.2、将数据导入数据库：

导入数据方法：

1、将以下 mysql 语句，完整复制到 workbench 语句窗口(或者是mysql 的黑窗口或者Navicat窗口也可以)

2、然后运行即可导入，不需要另外创建表，下面表的操作一样。

注释：这些语句第一条是创建表(create table)，后面都是插入数据到表中(insert into table )：

学生表 Student：

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

insert into Student values('09' , '张三' , '2017-12-20' , '女');

insert into Student values('10' , '李四' , '2017-12-25' , '女');

insert into Student values('11' , '李四' , '2017-12-30' , '女');

insert into Student values('12' , '赵六' , '2017-01-01' , '女');

insert into Student values('13' , '孙七' , '2018-01-01' , '女');

科目表 Course:

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

教师表 Teacher:

create table Teacher(TId varchar(10),Tname varchar(10));

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五');

成绩表 SC:

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));

insert into SC values('01' , '01' , 80);

insert into SC values('01' , '02' , 90);

insert into SC values('01' , '03' , 99);

insert into SC values('02' , '01' , 70);

insert into SC values('02' , '02' , 60);

insert into SC values('02' , '03' , 80);

insert into SC values('03' , '01' , 80);

insert into SC values('03' , '02' , 80);

insert into SC values('03' , '03' , 80);

insert into SC values('04' , '01' , 50);

insert into SC values('04' , '02' , 30);

insert into SC values('04' , '03' , 20);

insert into SC values('05' , '01' , 76);

insert into SC values('05' , '02' , 87);

insert into SC values('06' , '01' , 31);

insert into SC values('06' , '03' , 34);

insert into SC values('07' , '02' , 89);

insert into SC values('07' , '03' , 98);

二、练习题目：

1、查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

分析：题意就是想查询所有学生中01课程分数比02课程分数高的学生有哪些？

思路：分别查出01和02的sid和分数，然后与student表内连接查询学生姓名，输出样式：Sid,Sname,score01,score02，然后用where条件a.score > b.score过滤一下即可实现查询结果。

SELECT a.sid

,st.Sname

,a.score

,b.score

FROM(SELECT SId

,score

FROM sc

WHERE CId = 01) AS a # 查询01课程的分数

INNER JOIN (SELECT SId

,score

FROM sc

WHERE CId = 02) AS b ON a.sid = b.sid # 查询02课程的分数

INNER JOIN student AS st ON st.SId = a.SId # 2个内连接查交集

WHERE a.score > b.score;

1.1、查询同时学过" 01 "课程和" 02 "课程的学生信息

思路：使用子查询，先分别查询出01课程和02课程的sid，然后将这两个表内连接，即是同时学01和02课程的sid，此时再与studen表中的sid匹配查到学生详细信息。

解法1：标量子查询

SELECT student.*

FROM student

WHERE student.SId IN (SELECT a.SId

FROM (SELECT sid

FROM sc

WHERE CId = "01") AS a

INNER JOIN (SELECT *

FROM sc

WHERE CId = "02") AS b ON a.SId = b.SId);

但这个查询结果仅仅只能看到学生信息，看不到学生各个课程的分数，输出样式不是最优，所以我们希望输出的样式是这样的：sid,sname,score01,score02,那么就可以如题1一样，使用多表连接对三个表做内连接，然后直接取需要的字段即可：

SELECT a.sid

,st.Sname

,a.score

,b.score

FROM(SELECT SId

,score

FROM sc

WHERE CId = 01) AS a

INNER JOIN (SELECT SId

,score

FROM sc

WHERE CId = 02) AS b ON a.sid = b.sid

INNER JOIN student AS st ON st.SId = a.SId;

1.2、查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

分析：所谓"情况"，无非就是查这样一些人：必须学过01课课程，不一定学过02课程的学生是哪些，那既然知道了学生id，那很有可能想知道这些学生的信息，所以我们希望查询结果以这样的格式进行输出：sid,sname,score01,score02.

思路：多表连接的时候使用左连接即可，分别查询出学过01和02课程的sid和分数，然后将两个查询结果进行左连接，确保01课程的所有人都在，然后与student表左连接，这样就得到了想要查询结果了。

SELECT a.sid

,st.Sname

,a.score

,b.score

FROM(SELECT SId

,score

FROM sc

WHERE CId = 01) AS a

LEFT JOIN (SELECT SId

,score

FROM sc

WHERE CId = 02) AS b ON a.sid = b.sid

LEFT JOIN student AS st ON st.SId = a.SId;

1.3、查询不存在" 01 "课程但存在" 02 "课程的情况

思路：过滤掉cid = 01 课程的所有sid，再选取cid = 02的学生

解法1：使用子查询过滤掉学过01课程的学生，然后再选出那些学了02课程的学生，用AND将两个约束条件进行连接就可以得到最终的结果集了。

SELECT a.sid

,b.s