MySQL经典50道练习题(MySQL classic 50 exercises)

MySQL练习

50道经典SQL练习题

50道经典SQL练习题

数据表介绍

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

    SId 学编号

    Sname 学姓名

    Sage 出年

    Ssex 学性别

  • SId 学编号
  • Sname 学姓名
  • Sage 出年
  • Ssex 学性别
  • 2.课程表 Course(CId,Cname,TId)

    CId 课程编号

    Cname 课程名称

    TId 教师编号

  • CId 课程编号
  • Cname 课程名称
  • TId 教师编号
  • 3.教师表 Teacher(TId,Tname)

    TId 教师编号

    Tname 教师姓名

  • TId 教师编号
  • Tname 教师姓名
  • 4.成绩表 SC(SId,CId,score)

    SId 学编号

    CId 课程编号

    score 分数

  • SId 学编号
  • CId 课程编号
  • score 分数

建表语句

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

  • 课程表 Course
    create table Course(
    CId varchar(10),
    Cname nvarchar(10),
    TId varchar(10)
    );

  • 教师表 Teacher
    create table Teacher(
    TId varchar(10),
    Tname varchar(10)
    );

  • 成绩表 SC
    create table SC(
    SId varchar(10),
    CId varchar(10),
    score decimal(18,1)
    );

插入数据

  • 学表 Student
    — 学生表 Student
    insert into Student values(’01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
    insert into Student values(’02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
    insert into Student values(’03’ , ‘孙风’ , ‘1990-12-20’ , ‘男’);
    insert into Student values(’04’ , ‘李云’ , ‘1990-12-06’ , ‘男’);
    insert into Student values(’05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
    insert into Student values(’06’ , ‘吴兰’ , ‘1992-01-01’ , ‘女’);
    insert into Student values(’07’ , ‘郑竹’ , ‘1989-01-01’ , ‘女’);
    insert into Student values(’09’ , ‘张三’ , ‘2017-12-20’ , ‘女’);
    insert into Student values(’10’ , ‘李四’ , ‘2017-12-25’ , ‘女’);
    insert into Student values(’11’ , ‘李四’ , ‘2012-06-06’ , ‘女’);
    insert into Student values(’12’ , ‘赵六’ , ‘2013-06-13’ , ‘女’);
    insert into Student values(’13’ , ‘孙七’ , ‘2014-06-01’ , ‘女’);

  • 课程表 Course
    — 科表 Course
    insert into Course values(’01’ , ‘语文’ , ’02’);
    insert into Course values(’02’ , ‘数学’ , ’01’);
    insert into Course values(’03’ , ‘英语’ , ’03’);

  • 教师表 Teacher
    — 教师表 Teacher
    insert into Teacher values(’01’ , ‘张三’);
    insert into Teacher values(’02’ , ‘李四’);
    insert into Teacher values(’03’ , ‘王五’);

  • 成绩表 SC
    — 成绩表 SC
    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 “课程成绩的学的信息及课程分数

通过题目需求可知,需要用到两张表:学生表和成绩表
学生表:
+------+--------+---------------------+------+
| SId  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06   | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07   | 郑竹   | 1989-01-01 00:00:00 | 女   |
| 09   | 张三   | 2017-12-20 00:00:00 | 女   |
| 10   | 李四   | 2017-12-25 00:00:00 | 女   |
| 11   | 李四   | 2012-06-06 00:00:00 | 女   |
| 12   | 赵六   | 2013-06-13 00:00:00 | 女   |
| 13   | 孙七   | 2014-06-01 00:00:00 | 女   |
+------+--------+---------------------+------+
成绩表:
+------+------+-------+
| SId  | CId  | score |
+------+------+-------+
| 01   | 01   |  80.0 |
| 01   | 02   |  90.0 |
| 01   | 03   |  99.0 |
| 02   | 01   |  70.0 |
| 02   | 02   |  60.0 |
| 02   | 03   |  80.0 |
| 03   | 01   |  80.0 |
| 03   | 02   |  80.0 |
| 03   | 03   |  80.0 |
| 04   | 01   |  50.0 |
| 04   | 02   |  30.0 |
| 04   | 03   |  20.0 |
| 05   | 01   |  76.0 |
| 05   | 02   |  87.0 |
| 06   | 01   |  31.0 |
| 06   | 03   |  34.0 |
| 07   | 02   |  89.0 |
| 07   | 03   |  98.0 |
+------+------+-------+

查询" 01 “课程” 02 "课程成绩的学的信息及课程分数
分析:
	(1)先将所有学生”01“的成绩从成绩表SC中提取出来
	select *  from SC where CId = '01';
    +------+------+-------+
    | SId  | CId  | score |
    +------+------+-------+
    | 01   | 01   |  80.0 |
    | 02   | 01   |  70.0 |
    | 03   | 01   |  80.0 |
    | 04   | 01   |  50.0 |
    | 05   | 01   |  76.0 |
    | 06   | 01   |  31.0 |
    +------+------+-------+
    
	(2)再将所有”02“的成绩从成绩表SC中提取出来
	select *  from SC where CId = '02';
    +------+------+-------+
    | SId  | CId  | score |
    +------+------+-------+
    | 01   | 02   |  90.0 |
    | 02   | 02   |  60.0 |
    | 03   | 02   |  80.0 |
    | 04   | 02   |  30.0 |
    | 05   | 02   |  87.0 |
    | 07   | 02   |  89.0 |
    +------+------+-------+
    
	(3)关联:以”01“课程为准,作为左表,与”02“进行左连接
	(select *  from SC where CId = '01') as t1
	left join		     ------------------------左连接
	(select *  from SC where CId = '02') as t2
	on t1.SId = t2.SId   ------------------------关联条件 
	
	(4)查询显示关联后的结果(在最前面加select *  from)
	select *  from
	(select *  from SC where CId = '01') as t1
	left join		     
	(select *  from SC where CId = '02') as t2
	on t1.SId = t2.SId; 
    +------+------+-------+------+------+-------+
    | SId  | CId  | score | SId  | CId  | score |
    +------+------+-------+------+------+-------+
    | 01   | 01   |  80.0 | 01   | 02   |  90.0 |
    | 02   | 01   |  70.0 | 02   | 02   |  60.0 |
    | 03   | 01   |  80.0 | 03   | 02   |  80.0 |
    | 04   | 01   |  50.0 | 04   | 02   |  30.0 |
    | 05   | 01   |  76.0 | 05   | 02   |  87.0 |
    | 06   | 01   |  31.0 | NULL | NULL |  NULL |
    +------+------+-------+------+------+-------+
    
	(5)通过上述表格发现,有重复的列名,提取t1的的SId、CId、score和t2的score即可
	顺便给t1.score,t2.score起个别名(若不起别名,输出的结果列名一样,不容区分)
	内部也做一下修改,不建议使用*
	select t1.SId,t1.CId,t1.score as t1score,t2.score as t2score from
	(select SId,CId,score from SC where CId = '01') as t1
	left join		     
	(select SId,CId,score from SC where CId = '02') as t2
	on t1.SId = t2.SId; 
		+------+------+---------+---------+
		| SId  | CId  | t1score | t2score |
		+------+------+---------+---------+
		| 01   | 01   |    80.0 |    90.0 |
		| 02   | 01   |    70.0 |    60.0 |
		| 03   | 01   |    80.0 |    80.0 |
		| 04   | 01   |    50.0 |    30.0 |
		| 05   | 01   |    76.0 |    87.0 |
		| 06   | 01   |    31.0 |    NULL |
		+------+------+---------+---------+

	(6)最后,结尾加个where条件比较t1score和t2score的大小
	select t1.SId,t1.CId,t1.score as t1score,t2.score as t2score from
	(select SId,CId,score from SC where CId = '01') as t1
	left join		     
	(select SId,CId,score from SC where CId = '02') as t2
	on t1.SId = t2.SId 
	where  t1.score > t2.score;
	
		+------+------+---------+---------+
		| SId  | CId  | t1score | t2score |
		+------+------+---------+---------+
		| 02   | 01   |    70.0 |    60.0 |
		| 04   | 01   |    50.0 |    30.0 |
		+------+------+---------+---------+
	到这里," 01 “课程” 02 "课程成绩的学SId就出来了
	
	(7)想要知道学SId对应的学生信息和其他的课程成绩,
	需要将得出表作为一个整体再与学生表、成绩表进行关联...
	因为要和成绩表关联了,上个表中的CId,t1score,t2score就没什么用了,只保留SId
	外连接和内连接都可以:
	
最终代码:
	select tt1.SId,tt2.Sname,tt3.CId,tt3.score from
	(select t1.SId from
	(select SId,CId,score from SC where CId = '01') as t1
	left join		     
	(select SId,CId,score from SC where CId = '02') as t2
	on t1.SId = t2.SId 
	where  t1.scor
     e > t2.score) as tt1 
	join Student as tt2 on tt1.SId = tt2.SId
	join SC as tt3 on tt1.SId = tt3.SId;
	
执行结果:
    +------+--------+------+-------+
    | SId  | Sname  | CId  | score |
    +------+--------+------+-------+
    | 02   | 钱电   | 01   |  70.0 |
    | 02   | 钱电   | 02   |  60.0 |
    | 02   | 钱电   | 03   |  80.0 |
    | 04   | 李云   | 01   |  50.0 |
    | 04   | 李云   | 02   |  30.0 |
    | 04   | 李云   | 03   |  20.0 |
    +------+--------+------+-------+

2.查询同时存在” 01 “课程和” 02 “课程的情况

题目意思:查询既学习”01“课程,也学习了”02“课程的学生(只需要得到他们的编号即可)
分析:需要用到成绩表
	只有在成绩表中,通过看哪些学生考了哪个课程,才能知道哪些学生学了哪些课程
	(1)先将所有学生学习”01“的课程情况从成绩表SC中提取出来
	(目的是看哪些学生考了”01“课程,从而知道哪些学生学了”01“这门课)
	select *  from SC where CId = '01';
		+------+------+-------+
		| SId  | CId  | score |
		+------+------+-------+
		| 01   | 01   |  80.0 |
		| 02   | 01   |  70.0 |
		| 03   | 01   |  80.0 |
		| 04   | 01   |  50.0 |
		| 05   | 01   |  76.0 |
		| 06   | 01   |  31.0 |
		+------+------+-------+
    
	(2)再将所有学生学习”02“课程情况从成绩表SC中提取出来
	(目的是看哪些学生考了”02“课程,从而知道哪些学生学了”02“这门课)
	select *  from SC where CId = '02';
		+------+------+-------+
		| SId  | CId  | score |
		+------+------+-------+
		| 01   | 02   |  90.0 |
		| 02   | 02   |  60.0 |
		| 03   | 02   |  80.0 |
		| 04   | 02   |  30.0 |
		| 05   | 02   |  87.0 |
		| 07   | 02   |  89.0 |
		+------+------+-------+
	
	(3)将上面得出的两个表进行关联,可以得到哪些学生既学了”01“课程,也学习了”02“课程
	不需要用*查询全部,提取我们需要的即可(只需要得到他们的编号即可)

最终代码:
	select t1.SId  from
	(select SId  from SC where CId = '01') as t1
	join
	(select SId  from SC where CId = '02') as t2
	on t1.SId = t2.SId;
	
执行结果:
    +------+
    | SId  |
    +------+
    | 01   |
    | 02   |
    | 03   |
    | 04   |
    | 05   |
    +------+

3.查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )

分析:需要用到成绩表,以”01“为准,进行左连接关联
过程:
	(1)先将所有学生学习”01“的课程情况从成绩表SC中提取出来
	(目的是看哪些学生考了”01“课程,从而知道哪些学生学了”01“这门课)
	select *  from SC where CId = '01';
		+------+------+-------+
		| SId  | CId  | score |
		+------+------+-------+
		| 01   | 01   |  80.0 |
		| 02   | 01   |  70.0 |
		| 03   | 01   |  80.0 |
		| 04   | 01   |  50.0 |
		| 05   | 01   |  76.0 |
		| 06   | 01   |  31.0 |
		+------+------+-------+
    
	(2)再将所有学生学习”02“课程情况从成绩表SC中提取出来
	(目的是看哪些学生考了”02“课程,从而知道哪些学生学了”02“这门课)
	select *  from SC where CId = '02';
		+------+------+-------+
		| SId  | CId  | score |
		+------+------+-------+
		| 01   | 02   |  90.0 |
		| 02   | 02   |  60.0 |
		| 03   | 02   |  80.0 |
		| 04   | 02   |  30.0 |
		| 05   | 02   |  87.0 |
		| 07   | 02   |  89.0 |
		+------+------+-------+
    
    (3)以第一个表为准进行左关联,题目需求需要显示null,
    那么将左表的SId,CId,score,右表的CId,score提取出来
    给t2.CId,t2.score起个别名,利于观看

最终代码:
	select t1.SId,t1.CId,t1.score,  t2.CId as t2CId, t2.score as t2score from
	(select SId,CId,score from SC where CId = '01') as t1
	left join
	(select SId,CId,score from SC where CId = '02') as t2
	on t1.SId = t2.SId;

执行结果:
        +------+------+-------+-------+---------+
        | SId  | CId  | score | t2CId | t2score |
        +------+------+-------+-------+---------+
        | 01   | 01   |  80.0 | 02    |    90.0 |
        | 02   | 01   |  70.0 | 02    |    60.0 |
        | 03   | 01   |  80.0 | 02    |    80.0 |
        | 04   | 01   |  50.0 | 02    |    30.0 |
        | 05   | 01   |  76.0 | 02    |    87.0 |
        | 06   | 01   |  31.0 | NULL  |    NULL |
        +------+------+-------+-------+---------+

4.查询不存在” 01 “课程但存在” 02 “课程的情况

分析:与第3题相反,更换两处地方即可
	将首行的t1全部换成t2
	将left换成right
	
最终代码:
	select t2.SId,t2.CId,t2.score,  t1.CId as t1CId, t1.score as t1score from
	(select SId,CId,score from SC where CId = '01') as t1
	right join
	(select SId,CId,score from SC where CId = '02') as t2
	on t1.SId = t2.SId;

执行结果:
		+------+------+-------+-------+---------+
		| SId  | CId  | score | t1CId | t1score |
		+------+------+-------+-------+---------+
		| 01   | 02   |  90.0 | 01    |    80.0 |
		| 02   | 02   |  60.0 | 01    |    70.0 |
		| 03   | 02   |  80.0 | 01    |    80.0 |
		| 04   | 02   |  30.0 | 01    |    50.0 |
		| 05   | 02   |  87.0 | 01    |    76.0 |
		| 07   | 02   |  89.0 | NULL  |    NULL |
		+------+------+-------+-------+---------+

5.查询平均成绩于等于 60 分的同学的学编号和学姓名和平均成绩

分析:需要用到学生表、成绩表
过程:
	(1)利用avg(),先将平均成绩算出来(基于成绩表SC)
	右SC表可知,需要根据SId先分组,然后在组内求平均
	select SId,avg(score) as avg_score from SC group  by SId;
		+------+-----------+
		| SId  | avg_score |
		+------+-----------+
		| 01   |  89.66667 |
		| 02   |  70.00000 |
		| 03   |  80.00000 |
		| 04   |  33.33333 |
		| 05   |  81.50000 |
		| 06   |  32.50000 |
		| 07   |  93.50000 |
		+------+-----------+
    
    小数位太多,利用round()取两位小数
    select SId, round(avg(score),2) as avg_score from SC group  by SId;
		+------+-----------+
		| SId  | avg_score |
		+------+-----------+
		| 01   |     89.67 |
		| 02   |     70.00 |
		| 03   |     80.00 |
		| 04   |     33.33 |
		| 05   |     81.50 |
		| 06   |     32.50 |
		| 07   |     93.50 |
		+------+-----------+
    
    (2)平均分要大于等于60,
    方法一:以平均分为整体,在其后面加where条件(因为where执行顺序优先于group by)
    select * from
    (select SId, round(avg(score),2) as avg_score from SC group  by SId) t1
    where t1.vag_score >=60;
	方法二:利用having条件,直接加在末尾
	select SId, round(avg(score),2) as avg_score from SC group  by SId
	having avg_score >= 60;
		+------+-----------+
		| SId  | avg_score |
		+------+-----------+
		| 01   |     89.67 |
		| 02   |     70.00 |
		| 03   |     80.00 |
		| 05   |     81.50 |
		| 07   |     93.50 |
		+------+-----------+
	
	(3)题目需求中的学生编号和平均成绩都有了,还差一个学生姓名
		把上面得出的表当作子表,与学生表进行关联
最终代码:
		select  t1.SId,t1.avg_score,t2.SName  from
	(select SId, round(avg(score),2) as avg_score from SC group  by SId
	having avg_score >= 60) t1 
	join Student t2
	on t1.SId = t2.SId;
	
执行结果:
        +------+-----------+--------+
        | SId  | avg_score | SName  |
        +------+-----------+--------+
        | 01   |     89.67 | 赵雷   |
        | 02   |     70.00 | 钱电   |
        | 03   |     80.00 | 孙风   |
        | 05   |     81.50 | 周梅   |
        | 07   |     93.50 | 郑竹   |
        +------+-----------+--------+

6.查询在 SC 表存在成绩的学信息

分析:由学生表可知学生有13个;由成绩表可知,有成绩的学生只有7个
过程:
	(1)将有成绩的学生SId提取出来
		select sId from SC; 
			+------+
			| sId  |
			+------+
			| 01   |
			| 01   |
			| 01   |
			| 02   |
			| 02   |
			| 02   |
			| 03   |
			| 03   |
			| 03   |
			| 04   |
			| 04   |
			| 04   |
			| 05   |
			| 05   |
			| 06   |
			| 06   |
			| 07   |
			| 07   |
			+------+
        利用distinct去重
		select distinct sId from SC; 
			+------+
			| sId  |
			+------+
			| 01   |
			| 02   |
			| 03   |
			| 04   |
			| 05   |
			| 06   |
			| 07   |
			+------+

		(2)将上述表与学生表进行关联
最终代码:
		select t1.SId,t2.SName from
		(select distinct sId from SC) t1
		join Student t2
		on t1.SId = t2.SId;

执行结果:
	+------+--------+
        | SId  | SName  |
        +------+--------+
        | 01   | 赵雷   |
        | 02   | 钱电   |
        | 03   | 孙风   |
        | 04   | 李云   |
        | 05   | 周梅   |
        | 06   | 吴兰   |
        | 07   | 郑竹   |
        +------+--------+

7.查询所有同学的学编号、学姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

分析:基于成绩表,算一个总分,算一个课程总数
	将得出的表与学生表进行关联(学生表为准)
	
过程:
	(1)基于成绩表,先将学生的总分和课程总数提取出来
		先将SId分组,利用count()求课程总数,利用sum()求总分
		select SId, count(CId) as cnt, sum(score) as sum_score 
		from SC group by SId;
        +------+-----+-----------+
        | SId  | cnt | sum_score |
        +------+-----+-----------+
        | 01   |   3 |     269.0 |
        | 02   |   3 |     210.0 |
        | 03   |   3 |     240.0 |
        | 04   |   3 |     100.0 |
        | 05   |   2 |     163.0 |
        | 06   |   2 |      65.0 |
        | 07   |   2 |     187.0 |
        +------+-----+-----------+
		
		(2)将上面得出的表与学生表进行关联(学生表为主)
			学生表在前,就使用左关联;学生表在后就使用右关联
最终代码:
			select t2.SId,t2.SName,t1.cnt,t1.sum_score  from
		(select SId, count(CId) as cnt, sum(score) as sum_score 
		from SC group by SId) t1
		right join Student t2 
		on t1.SId = t2.SId;
		
执行结果:
	+------+--------+------+-----------+
        | SId  | SName  | cnt  | sum_score |
        +------+--------+------+-----------+
        | 01   | 赵雷   |    3 |     269.0 |
        | 02   | 钱电   |    3 |     210.0 |
        | 03   | 孙风   |    3 |     240.0 |
        | 04   | 李云   |    3 |     100.0 |
        | 05   | 周梅   |    2 |     163.0 |
        | 06   | 吴兰   |    2 |      65.0 |
        | 07   | 郑竹   |    2 |     187.0 |
        | 09   | 张三   | NULL |      NULL |
        | 10   | 李四   | NULL |      NULL |
        | 11   | 李四   | NULL |      NULL |
        | 12   | 赵六   | NULL |      NULL |
        | 13   | 孙七   | NULL |      NULL |
        +------+--------+------+-----------+

8.查询「李」姓师的数量

分析:利用到老师表,count(),where,like
代码:
	select count(Tname) from Teacher where Tname like '李%';
执行结果:
	+--------------+
        | count(Tname) |
        +--------------+
        |            1 |
        +--------------+

9.查询学过「张三」师授课的同学的信息

分析:
	根据老师的姓名,在教师表里找对应的老师编号
    根据老师的编号,在课程表里找该老师所教的科目
    根据所教的科目,在成绩表里找对应学生的SId
    根据学生的SId,在学生表里找对应的其他学生信息
过程:
	(1)根据”张三“这个名字Tname,去教师表里找该老师的编号TId
		select TId from Teacher where Tname =  '张三';
				+------+
				| TId  |
				+------+
				| 01   |
				+------+
	(2)根据老师的编号TId,在课程表里找该老师所教的科目CId
		因为一个老师只有一个编号(一条记录),所以此处可以用等于号
		select CId from Course where TId 
		= (select TId from Teacher where Tname =  '张三');
			+------+
			| CId  |
			+------+
			| 02   |
			+------+
注意:如果括号里的结果是多条记录,那么就需要使用 in 来替代等于号
例如:...where TId in (select TId from Teacher where Tname = '张三','李四');

	(3)根据所教的科目CId,在成绩表里找学该科目的学生的SId
		因为一个老师编号只对应一个科目,中间用等于号连接
		select SId from SC where CId
		= 
(select CId from Course where TId = (select TId from Teacher where Tname = '张三'));
			+------+
			| SId  |
			+------+
			| 01   |
			| 02   |
			| 03   |
			| 04   |
			| 05   |
			| 07   |
			+------+

	(4)根据学生的SId,与学生表关联,在学生表里找对应的其他学生信息
执行结果:
		select t2.SId,t2.SName from 
		(select SId from SC where CId
		= 
(select CId from Course where TId = (select TId from Teacher where Tname = '张三'))) t1 
		join Student t2 on t1.SId = t2.SId;
		
执行结果:
			+------+--------+
			| SId  | SName  |
			+------+--------+
			| 01   | 赵雷   |
			| 02   | 钱电   |
			| 03   | 孙风   |
			| 04   | 李云   |
			| 05   | 周梅   |
			| 07   | 郑竹   |
			+------+--------+

10.查询没有学全所有课程的同学的信息

————————

MySQL练习

50 classic SQL exercises

50 classic SQL exercises

Data sheet introduction

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

    SId 学编号

    Sname 学姓名

    Sage 出年

    Ssex 学性别

  • Sid number
  • Sname 学姓名
  • Sage 出年
  • Ssex 学性别
  • 2.课程表 Course(CId,Cname,TId)

    CId 课程编号

    Cname 课程名称

    TId 教师编号

  • CID course number
  • Cname 课程名称
  • TID teacher number
  • 3.教师表 Teacher(TId,Tname)

    TId 教师编号

    Tname 教师姓名

  • TID teacher number
  • Tname 教师姓名
  • 4.成绩表 SC(SId,CId,score)

    SId 学编号

    CId 课程编号

    score 分数

  • Sid number
  • CID course number
  • score 分数

Create table statement

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

  • 课程表 Course
    create table Course(
    CId varchar(10),
    Cname nvarchar(10),
    TId varchar(10)
    );

  • 教师表 Teacher
    create table Teacher(
    TId varchar(10),
    Tname varchar(10)
    );

  • 成绩表 SC
    create table SC(
    SId varchar(10),
    CId varchar(10),
    score decimal(18,1)
    );

insert data

  • 学表 Student
    — 学生表 Student
    insert into Student values(’01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
    insert into Student values(’02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
    insert into Student values(’03’ , ‘孙风’ , ‘1990-12-20’ , ‘男’);
    insert into Student values(’04’ , ‘李云’ , ‘1990-12-06’ , ‘男’);
    insert into Student values(’05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
    insert into Student values(’06’ , ‘吴兰’ , ‘1992-01-01’ , ‘女’);
    insert into Student values(’07’ , ‘郑竹’ , ‘1989-01-01’ , ‘女’);
    insert into Student values(’09’ , ‘张三’ , ‘2017-12-20’ , ‘女’);
    insert into Student values(’10’ , ‘李四’ , ‘2017-12-25’ , ‘女’);
    insert into Student values(’11’ , ‘李四’ , ‘2012-06-06’ , ‘女’);
    insert into Student values(’12’ , ‘赵六’ , ‘2013-06-13’ , ‘女’);
    insert into Student values(’13’ , ‘孙七’ , ‘2014-06-01’ , ‘女’);

  • 课程表 Course
    — 科表 Course
    insert into Course values(’01’ , ‘语文’ , ’02’);
    insert into Course values(’02’ , ‘数学’ , ’01’);
    insert into Course values(’03’ , ‘英语’ , ’03’);

  • 教师表 Teacher
    — 教师表 Teacher
    insert into Teacher values(’01’ , ‘张三’);
    insert into Teacher values(’02’ , ‘李四’);
    insert into Teacher values(’03’ , ‘王五’);

  • 成绩表 SC
    — 成绩表 SC
    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);

Exercise questions with answers

1. Query the academic information and course scores of “01” and “02” course scores

通过题目需求可知,需要用到两张表:学生表和成绩表
学生表:
+------+--------+---------------------+------+
| SId  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-12-20 00:00:00 | 男   |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06   | 吴兰   | 1992-01-01 00:00:00 | 女   |
| 07   | 郑竹   | 1989-01-01 00:00:00 | 女   |
| 09   | 张三   | 2017-12-20 00:00:00 | 女   |
| 10   | 李四   | 2017-12-25 00:00:00 | 女   |
| 11   | 李四   | 2012-06-06 00:00:00 | 女   |
| 12   | 赵六   | 2013-06-13 00:00:00 | 女   |
| 13   | 孙七   | 2014-06-01 00:00:00 | 女   |
+------+--------+---------------------+------+
成绩表:
+------+------+-------+
| SId  | CId  | score |
+------+------+-------+
| 01   | 01   |  80.0 |
| 01   | 02   |  90.0 |
| 01   | 03   |  99.0 |
| 02   | 01   |  70.0 |
| 02   | 02   |  60.0 |
| 02   | 03   |  80.0 |
| 03   | 01   |  80.0 |
| 03   | 02   |  80.0 |
| 03   | 03   |  80.0 |
| 04   | 01   |  50.0 |
| 04   | 02   |  30.0 |
| 04   | 03   |  20.0 |
| 05   | 01   |  76.0 |
| 05   | 02   |  87.0 |
| 06   | 01   |  31.0 |
| 06   | 03   |  34.0 |
| 07   | 02   |  89.0 |
| 07   | 03   |  98.0 |
+------+------+-------+

查询" 01 “课程” 02 "课程成绩的学的信息及课程分数
分析:
	(1)先将所有学生”01“的成绩从成绩表SC中提取出来
	select *  from SC where CId = '01';
    +------+------+-------+
    | SId  | CId  | score |
    +------+------+-------+
    | 01   | 01   |  80.0 |
    | 02   | 01   |  70.0 |
    | 03   | 01   |  80.0 |
    | 04   | 01   |  50.0 |
    | 05   | 01   |  76.0 |
    | 06   | 01   |  31.0 |
    +------+------+-------+
    
	(2)再将所有”02“的成绩从成绩表SC中提取出来
	select *  from SC where CId = '02';
    +------+------+-------+
    | SId  | CId  | score |
    +------+------+-------+
    | 01   | 02   |  90.0 |
    | 02   | 02   |  60.0 |
    | 03   | 02   |  80.0 |
    | 04   | 02   |  30.0 |
    | 05   | 02   |  87.0 |
    | 07   | 02   |  89.0 |
    +------+------+-------+
    
	(3)关联:以”01“课程为准,作为左表,与”02“进行左连接
	(select *  from SC where CId = '01') as t1
	left join		     ------------------------左连接
	(select *  from SC where CId = '02') as t2
	on t1.SId = t2.SId   ------------------------关联条件 
	
	(4)查询显示关联后的结果(在最前面加select *  from)
	select *  from
	(select *  from SC where CId = '01') as t1
	left join		     
	(select *  from SC where CId = '02') as t2
	on t1.SId = t2.SId; 
    +------+------+-------+------+------+-------+
    | SId  | CId  | score | SId  | CId  | score |
    +------+------+-------+------+------+-------+
    | 01   | 01   |  80.0 | 01   | 02   |  90.0 |
    | 02   | 01   |  70.0 | 02   | 02   |  60.0 |
    | 03   | 01   |  80.0 | 03   | 02   |  80.0 |
    | 04   | 01   |  50.0 | 04   | 02   |  30.0 |
    | 05   | 01   |  76.0 | 05   | 02   |  87.0 |
    | 06   | 01   |  31.0 | NULL | NULL |  NULL |
    +------+------+-------+------+------+-------+
    
	(5)通过上述表格发现,有重复的列名,提取t1的的SId、CId、score和t2的score即可
	顺便给t1.score,t2.score起个别名(若不起别名,输出的结果列名一样,不容区分)
	内部也做一下修改,不建议使用*
	select t1.SId,t1.CId,t1.score as t1score,t2.score as t2score from
	(select SId,CId,score from SC where CId = '01') as t1
	left join		     
	(select SId,CId,score from SC where CId = '02') as t2
	on t1.SId = t2.SId; 
		+------+------+---------+---------+
		| SId  | CId  | t1score | t2score |
		+------+------+---------+---------+
		| 01   | 01   |    80.0 |    90.0 |
		| 02   | 01   |    70.0 |    60.0 |
		| 03   | 01   |    80.0 |    80.0 |
		| 04   | 01   |    50.0 |    30.0 |
		| 05   | 01   |    76.0 |    87.0 |
		| 06   | 01   |    31.0 |    NULL |
		+------+------+---------+---------+

	(6)最后,结尾加个where条件比较t1score和t2score的大小
	select t1.SId,t1.CId,t1.score as t1score,t2.score as t2score from
	(select SId,CId,score from SC where CId = '01') as t1
	left join		     
	(select SId,CId,score from SC where CId = '02') as t2
	on t1.SId = t2.SId 
	where  t1.score > t2.score;
	
		+------+------+---------+---------+
		| SId  | CId  | t1score | t2score |
		+------+------+---------+---------+
		| 02   | 01   |    70.0 |    60.0 |
		| 04   | 01   |    50.0 |    30.0 |
		+------+------+---------+---------+
	到这里," 01 “课程” 02 "课程成绩的学SId就出来了
	
	(7)想要知道学SId对应的学生信息和其他的课程成绩,
	需要将得出表作为一个整体再与学生表、成绩表进行关联...
	因为要和成绩表关联了,上个表中的CId,t1score,t2score就没什么用了,只保留SId
	外连接和内连接都可以:
	
最终代码:
	select tt1.SId,tt2.Sname,tt3.CId,tt3.score from
	(select t1.SId from
	(select SId,CId,score from SC where CId = '01') as t1
	left join		     
	(select SId,CId,score from SC where CId = '02') as t2
	on t1.SId = t2.SId 
	where  t1.scor
     e > t2.score) as tt1 
	join Student as tt2 on tt1.SId = tt2.SId
	join SC as tt3 on tt1.SId = tt3.SId;
	
执行结果:
    +------+--------+------+-------+
    | SId  | Sname  | CId  | score |
    +------+--------+------+-------+
    | 02   | 钱电   | 01   |  70.0 |
    | 02   | 钱电   | 02   |  60.0 |
    | 02   | 钱电   | 03   |  80.0 |
    | 04   | 李云   | 01   |  50.0 |
    | 04   | 李云   | 02   |  30.0 |
    | 04   | 李云   | 03   |  20.0 |
    +------+--------+------+-------+

2. Query whether there are “01” and “02” courses at the same time

题目意思:查询既学习”01“课程,也学习了”02“课程的学生(只需要得到他们的编号即可)
分析:需要用到成绩表
	只有在成绩表中,通过看哪些学生考了哪个课程,才能知道哪些学生学了哪些课程
	(1)先将所有学生学习”01“的课程情况从成绩表SC中提取出来
	(目的是看哪些学生考了”01“课程,从而知道哪些学生学了”01“这门课)
	select *  from SC where CId = '01';
		+------+------+-------+
		| SId  | CId  | score |
		+------+------+-------+
		| 01   | 01   |  80.0 |
		| 02   | 01   |  70.0 |
		| 03   | 01   |  80.0 |
		| 04   | 01   |  50.0 |
		| 05   | 01   |  76.0 |
		| 06   | 01   |  31.0 |
		+------+------+-------+
    
	(2)再将所有学生学习”02“课程情况从成绩表SC中提取出来
	(目的是看哪些学生考了”02“课程,从而知道哪些学生学了”02“这门课)
	select *  from SC where CId = '02';
		+------+------+-------+
		| SId  | CId  | score |
		+------+------+-------+
		| 01   | 02   |  90.0 |
		| 02   | 02   |  60.0 |
		| 03   | 02   |  80.0 |
		| 04   | 02   |  30.0 |
		| 05   | 02   |  87.0 |
		| 07   | 02   |  89.0 |
		+------+------+-------+
	
	(3)将上面得出的两个表进行关联,可以得到哪些学生既学了”01“课程,也学习了”02“课程
	不需要用*查询全部,提取我们需要的即可(只需要得到他们的编号即可)

最终代码:
	select t1.SId  from
	(select SId  from SC where CId = '01') as t1
	join
	(select SId  from SC where CId = '02') as t2
	on t1.SId = t2.SId;
	
执行结果:
    +------+
    | SId  |
    +------+
    | 01   |
    | 02   |
    | 03   |
    | 04   |
    | 05   |
    +------+

3. Query the situation that “01” course exists but “02” course may not exist (null if it does not exist)

分析:需要用到成绩表,以”01“为准,进行左连接关联
过程:
	(1)先将所有学生学习”01“的课程情况从成绩表SC中提取出来
	(目的是看哪些学生考了”01“课程,从而知道哪些学生学了”01“这门课)
	select *  from SC where CId = '01';
		+------+------+-------+
		| SId  | CId  | score |
		+------+------+-------+
		| 01   | 01   |  80.0 |
		| 02   | 01   |  70.0 |
		| 03   | 01   |  80.0 |
		| 04   | 01   |  50.0 |
		| 05   | 01   |  76.0 |
		| 06   | 01   |  31.0 |
		+------+------+-------+
    
	(2)再将所有学生学习”02“课程情况从成绩表SC中提取出来
	(目的是看哪些学生考了”02“课程,从而知道哪些学生学了”02“这门课)
	select *  from SC where CId = '02';
		+------+------+-------+
		| SId  | CId  | score |
		+------+------+-------+
		| 01   | 02   |  90.0 |
		| 02   | 02   |  60.0 |
		| 03   | 02   |  80.0 |
		| 04   | 02   |  30.0 |
		| 05   | 02   |  87.0 |
		| 07   | 02   |  89.0 |
		+------+------+-------+
    
    (3)以第一个表为准进行左关联,题目需求需要显示null,
    那么将左表的SId,CId,score,右表的CId,score提取出来
    给t2.CId,t2.score起个别名,利于观看

最终代码:
	select t1.SId,t1.CId,t1.score,  t2.CId as t2CId, t2.score as t2score from
	(select SId,CId,score from SC where CId = '01') as t1
	left join
	(select SId,CId,score from SC where CId = '02') as t2
	on t1.SId = t2.SId;

执行结果:
        +------+------+-------+-------+---------+
        | SId  | CId  | score | t2CId | t2score |
        +------+------+-------+-------+---------+
        | 01   | 01   |  80.0 | 02    |    90.0 |
        | 02   | 01   |  70.0 | 02    |    60.0 |
        | 03   | 01   |  80.0 | 02    |    80.0 |
        | 04   | 01   |  50.0 | 02    |    30.0 |
        | 05   | 01   |  76.0 | 02    |    87.0 |
        | 06   | 01   |  31.0 | NULL  |    NULL |
        +------+------+-------+-------+---------+

4. Query if there is no “01” course but “02” course

分析:与第3题相反,更换两处地方即可
	将首行的t1全部换成t2
	将left换成right
	
最终代码:
	select t2.SId,t2.CId,t2.score,  t1.CId as t1CId, t1.score as t1score from
	(select SId,CId,score from SC where CId = '01') as t1
	right join
	(select SId,CId,score from SC where CId = '02') as t2
	on t1.SId = t2.SId;

执行结果:
		+------+------+-------+-------+---------+
		| SId  | CId  | score | t1CId | t1score |
		+------+------+-------+-------+---------+
		| 01   | 02   |  90.0 | 01    |    80.0 |
		| 02   | 02   |  60.0 | 01    |    70.0 |
		| 03   | 02   |  80.0 | 01    |    80.0 |
		| 04   | 02   |  30.0 | 01    |    50.0 |
		| 05   | 02   |  87.0 | 01    |    76.0 |
		| 07   | 02   |  89.0 | NULL  |    NULL |
		+------+------+-------+-------+---------+

5. Query the student number, name and average score of students whose average score is equal to 60

分析:需要用到学生表、成绩表
过程:
	(1)利用avg(),先将平均成绩算出来(基于成绩表SC)
	右SC表可知,需要根据SId先分组,然后在组内求平均
	select SId,avg(score) as avg_score from SC group  by SId;
		+------+-----------+
		| SId  | avg_score |
		+------+-----------+
		| 01   |  89.66667 |
		| 02   |  70.00000 |
		| 03   |  80.00000 |
		| 04   |  33.33333 |
		| 05   |  81.50000 |
		| 06   |  32.50000 |
		| 07   |  93.50000 |
		+------+-----------+
    
    小数位太多,利用round()取两位小数
    select SId, round(avg(score),2) as avg_score from SC group  by SId;
		+------+-----------+
		| SId  | avg_score |
		+------+-----------+
		| 01   |     89.67 |
		| 02   |     70.00 |
		| 03   |     80.00 |
		| 04   |     33.33 |
		| 05   |     81.50 |
		| 06   |     32.50 |
		| 07   |     93.50 |
		+------+-----------+
    
    (2)平均分要大于等于60,
    方法一:以平均分为整体,在其后面加where条件(因为where执行顺序优先于group by)
    select * from
    (select SId, round(avg(score),2) as avg_score from SC group  by SId) t1
    where t1.vag_score >=60;
	方法二:利用having条件,直接加在末尾
	select SId, round(avg(score),2) as avg_score from SC group  by SId
	having avg_score >= 60;
		+------+-----------+
		| SId  | avg_score |
		+------+-----------+
		| 01   |     89.67 |
		| 02   |     70.00 |
		| 03   |     80.00 |
		| 05   |     81.50 |
		| 07   |     93.50 |
		+------+-----------+
	
	(3)题目需求中的学生编号和平均成绩都有了,还差一个学生姓名
		把上面得出的表当作子表,与学生表进行关联
最终代码:
		select  t1.SId,t1.avg_score,t2.SName  from
	(select SId, round(avg(score),2) as avg_score from SC group  by SId
	having avg_score >= 60) t1 
	join Student t2
	on t1.SId = t2.SId;
	
执行结果:
        +------+-----------+--------+
        | SId  | avg_score | SName  |
        +------+-----------+--------+
        | 01   |     89.67 | 赵雷   |
        | 02   |     70.00 | 钱电   |
        | 03   |     80.00 | 孙风   |
        | 05   |     81.50 | 周梅   |
        | 07   |     93.50 | 郑竹   |
        +------+-----------+--------+

6. Query the academic information with scores in the SC table

分析:由学生表可知学生有13个;由成绩表可知,有成绩的学生只有7个
过程:
	(1)将有成绩的学生SId提取出来
		select sId from SC; 
			+------+
			| sId  |
			+------+
			| 01   |
			| 01   |
			| 01   |
			| 02   |
			| 02   |
			| 02   |
			| 03   |
			| 03   |
			| 03   |
			| 04   |
			| 04   |
			| 04   |
			| 05   |
			| 05   |
			| 06   |
			| 06   |
			| 07   |
			| 07   |
			+------+
        利用distinct去重
		select distinct sId from SC; 
			+------+
			| sId  |
			+------+
			| 01   |
			| 02   |
			| 03   |
			| 04   |
			| 05   |
			| 06   |
			| 07   |
			+------+

		(2)将上述表与学生表进行关联
最终代码:
		select t1.SId,t2.SName from
		(select distinct sId from SC) t1
		join Student t2
		on t1.SId = t2.SId;

执行结果:
	+------+--------+
        | SId  | SName  |
        +------+--------+
        | 01   | 赵雷   |
        | 02   | 钱电   |
        | 03   | 孙风   |
        | 04   | 李云   |
        | 05   | 周梅   |
        | 06   | 吴兰   |
        | 07   | 郑竹   |
        +------+--------+

7. Query the student number, student name, total number of courses selected and the total score of all courses of all students (null if there is no score)

分析:基于成绩表,算一个总分,算一个课程总数
	将得出的表与学生表进行关联(学生表为准)
	
过程:
	(1)基于成绩表,先将学生的总分和课程总数提取出来
		先将SId分组,利用count()求课程总数,利用sum()求总分
		select SId, count(CId) as cnt, sum(score) as sum_score 
		from SC group by SId;
        +------+-----+-----------+
        | SId  | cnt | sum_score |
        +------+-----+-----------+
        | 01   |   3 |     269.0 |
        | 02   |   3 |     210.0 |
        | 03   |   3 |     240.0 |
        | 04   |   3 |     100.0 |
        | 05   |   2 |     163.0 |
        | 06   |   2 |      65.0 |
        | 07   |   2 |     187.0 |
        +------+-----+-----------+
		
		(2)将上面得出的表与学生表进行关联(学生表为主)
			学生表在前,就使用左关联;学生表在后就使用右关联
最终代码:
			select t2.SId,t2.SName,t1.cnt,t1.sum_score  from
		(select SId, count(CId) as cnt, sum(score) as sum_score 
		from SC group by SId) t1
		right join Student t2 
		on t1.SId = t2.SId;
		
执行结果:
	+------+--------+------+-----------+
        | SId  | SName  | cnt  | sum_score |
        +------+--------+------+-----------+
        | 01   | 赵雷   |    3 |     269.0 |
        | 02   | 钱电   |    3 |     210.0 |
        | 03   | 孙风   |    3 |     240.0 |
        | 04   | 李云   |    3 |     100.0 |
        | 05   | 周梅   |    2 |     163.0 |
        | 06   | 吴兰   |    2 |      65.0 |
        | 07   | 郑竹   |    2 |     187.0 |
        | 09   | 张三   | NULL |      NULL |
        | 10   | 李四   | NULL |      NULL |
        | 11   | 李四   | NULL |      NULL |
        | 12   | 赵六   | NULL |      NULL |
        | 13   | 孙七   | NULL |      NULL |
        +------+--------+------+-----------+

8. Inquire about the number of teachers surnamed “Li”

分析:利用到老师表,count(),where,like
代码:
	select count(Tname) from Teacher where Tname like '李%';
执行结果:
	+--------------+
        | count(Tname) |
        +--------------+
        |            1 |
        +--------------+

9. Inquire about the information of the students who have studied “teacher Zhang San”

分析:
	根据老师的姓名,在教师表里找对应的老师编号
    根据老师的编号,在课程表里找该老师所教的科目
    根据所教的科目,在成绩表里找对应学生的SId
    根据学生的SId,在学生表里找对应的其他学生信息
过程:
	(1)根据”张三“这个名字Tname,去教师表里找该老师的编号TId
		select TId from Teacher where Tname =  '张三';
				+------+
				| TId  |
				+------+
				| 01   |
				+------+
	(2)根据老师的编号TId,在课程表里找该老师所教的科目CId
		因为一个老师只有一个编号(一条记录),所以此处可以用等于号
		select CId from Course where TId 
		= (select TId from Teacher where Tname =  '张三');
			+------+
			| CId  |
			+------+
			| 02   |
			+------+
注意:如果括号里的结果是多条记录,那么就需要使用 in 来替代等于号
例如:...where TId in (select TId from Teacher where Tname = '张三','李四');

	(3)根据所教的科目CId,在成绩表里找学该科目的学生的SId
		因为一个老师编号只对应一个科目,中间用等于号连接
		select SId from SC where CId
		= 
(select CId from Course where TId = (select TId from Teacher where Tname = '张三'));
			+------+
			| SId  |
			+------+
			| 01   |
			| 02   |
			| 03   |
			| 04   |
			| 05   |
			| 07   |
			+------+

	(4)根据学生的SId,与学生表关联,在学生表里找对应的其他学生信息
执行结果:
		select t2.SId,t2.SName from 
		(select SId from SC where CId
		= 
(select CId from Course where TId = (select TId from Teacher where Tname = '张三'))) t1 
		join Student t2 on t1.SId = t2.SId;
		
执行结果:
			+------+--------+
			| SId  | SName  |
			+------+--------+
			| 01   | 赵雷   |
			| 02   | 钱电   |
			| 03   | 孙风   |
			| 04   | 李云   |
			| 05   | 周梅   |
			| 07   | 郑竹   |
			+------+--------+

10. Query the information of students who have not studied all courses