mysql经典例题50道52

初学mysql写了50道例题

建表：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-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
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);



五十道sql练习题：

1. 查询" 01 "课程⽐" 02 "课程成绩⾼的学⽣的信息及课程分数
先分别查看01、 02课程学生成绩：
select sid,score from sc where cid = '01';
select sid,score from sc where cid = '02';

比较上述的两个表，发现有些sid不对应；
因此对两个表进行join联结，条件是两个表的sid相等；
同时，第一个表的成绩比第二个表的成绩高。

select s1.sid,s1.score 
from (select sid,score from sc where cid = '01') as s1 
join (select sid,score from sc where cid = '02') as s2 
on s1.sid = s2.sid where s1.score > s2.score;
+------+-------+
| sid  | score |
+------+-------+
| 02   |  70.0 |
| 04   |  50.0 |
+------+-------+
2 rows in set (0.00 sec)


2. 查询同时存在" 01 "课程和" 02 "课程的情况
select s1.* 
from (select sid, score from sc where cid = '01') as s1 
join (select sid,score from sc where cid = '02') as s2 
on s1.sid = s2.sid;
+------+-------+
| sid  | score |
+------+-------+
| 01   |  80.0 |
| 02   |  70.0 |
| 03   |  80.0 |
| 04   |  50.0 |
| 05   |  76.0 |
+------+-------+
5 rows in set (0.00 sec)


3. 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select s1.* 
from (select sid, score from sc where cid = '01') as s1 
left join (select sid,score from sc where cid = '02') as s2 
on s1.sid = s2.sid;
+------+-------+
| sid  | score |
+------+-------+
| 01   |  80.0 |
| 02   |  70.0 |
| 03   |  80.0 |
| 04   |  50.0 |
| 05   |  76.0 |
| 06   |  31.0 |
+------+-------+
6 rows in set (0.00 sec)


4. 查询不存在" 01 "课程但存在" 02 "课程的情况
select sid ,score 
from (select sid ,score from sc where cid = '02') as s1 
where s1.sid not in (select sid from sc where cid = '01');
+------+-------+
| sid  | score |
+------+-------+
| 07   |  89.0 |
+------+-------+
1 row in set (0.00 sec)

换种方案：
select * 
from sc 
where sid not in (select sid from sc where cid = '01') and cid = '02';
+------+------+-------+
| SId  | CId  | score |
+------+------+-------+
| 07   | 02   |  89.0 |
+------+------+-------+
1 row in set (0.00 sec)


5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩
select stu.sid ,stu.sname, s1.score 
from student as stu 
join (select sid, avg(score) as score from sc group by sid) as s1 
on  stu.sid = s1.sid where s1.score >=60;
+------+--------+----------+
| sid  | sname  | score    |
+------+--------+----------+
| 01   | 赵雷   | 89.66667 |
| 02   | 钱电   | 70.00000 |
| 03   | 孙⻛   | 80.00000 |
| 05   | 周梅   | 81.50000 |
| 07   | 郑⽵   | 93.50000 |
+------+--------+----------+
5 rows in set (0.00 sec)


6.查询在 SC 表存在成绩的学⽣信息
select * 
from student 
where sid in (select sid from sc group by sid);
+------+--------+---------------------+------+
| 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 | ⼥   |
+------+--------+---------------------+------+
7 rows in set (0.00 sec)


7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select stu.sid, stu.sname,s1.count,s1.sum 
from student as stu 
left join (select sid, count(cid) as count, sum(score) as sum from sc group by sid) as s1 
on stu.sid = s1.sid;
+------+--------+-------+-------+
| sid  | sname  | count | sum   |
+------+--------+-------+-------+
| 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 |
|