sql语句练习题五十道详解

网上很流行的sql练习五十题，作为小白，很开心的拿过来练手，但难度还是有点大，磕磕绊绊的做完了一些，后续还会继续练习和更新。

一. 表名和字段

1. 学生表
   Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
   
2. 课程表
   Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
   
3. 教师表
   Teacher(t_id,t_name) –教师编号,教师姓名
   
4. 成绩表
   Score(s_id,c_id,s_score) –学生编号,课程编号,分数
   

二. 建表语句

1. 学生表
CREATE TABLE `Student`(
    `s_id` VARCHAR(20),
    `s_name` VARCHAR(20) NOT NULL DEFAULT '',
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(`s_id`)
);
2. 课程表
CREATE TABLE `Course`(
    `c_id`  VARCHAR(20),
    `c_name` VARCHAR(20) NOT NULL DEFAULT '',
    `t_id` VARCHAR(20) NOT NULL,
    PRIMARY KEY(`c_id`)
);
3. 教师表
CREATE TABLE `Teacher`(
    `t_id` VARCHAR(20),
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(`t_id`)
);
4. 成绩表
CREATE TABLE `Score`(
    `s_id` VARCHAR(20),
    `c_id`  VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY(`s_id`,`c_id`)
);
5. 插入学生表测试数据
insert into Student values('01' , 'zhaolei' , '1990-01-01' , 'man');
insert into Student values('02' , 'qiandian' , '1990-12-21' , 'man');
insert into Student values('03' , 'sunfeng' , '1990-05-20' , 'man');
insert into Student values('04' , 'liyun' , '1990-08-06' , 'man');
insert into Student values('05' , 'zhoumei' , '1991-12-01' , 'women');
insert into Student values('06' , 'wuzhu' , '1992-03-01' , 'women');
insert into Student values('07' , 'zhengzhu' , '1989-07-01' , 'women');
insert into Student values('08' , 'wangju' , '1990-01-20' , 'women');
6. 插入课程表测试数据
insert into Course values('01' , 'chinese' , '02');
insert into Course values('02' , 'math' , '01');
insert into Course values('03' , 'english' , '03');

7. 插入教师表测试数据
insert into Teacher values('01' , 'zhangsan');
insert into Teacher values('02' , 'lisi');
insert into Teacher values('03' , 'wangwu');

8. 插入成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

三. 练习题及sql语句详解

– 1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
（1）用连接查询结果不正确 原因不明

select a.*,b.s_score as 01_score,c.s_score as 02_score from student a
join score b on a.s_id=b.s_id and b.c_id='01'
left join score c on c.c_id=a.s_id and c.s_id='02' or c.c_id=null where b.s_score>c.s_score;

（2）用子查询
思路：由于score表的01成绩和02成绩全部在一起，所以用select子查询将01成绩和02成绩的信息分别查询出来并进行命名，然后通过where条件筛选（s_id相同，01成绩比02成绩高），将整个结果r再进行select选出需要信息，最后左连接并入student表。

select * from 
  (
   select t1.s_id,01_score,02_score
   from 
        (select s_id,s_score as 01_score from score where c_id='01') as t1,
        (select s_id,s_score as 02_score from score where c_id='02') as t2
   where t1.s_id=t2.s_id and t1.01_score>t2.02_score
   ) as r
left join student
on r.s_id=student.s_id
group by student.s_id;

– 2.查询"01"课程比"02"课程成绩低的学生的信息及课程分数 (做法同第一题)
– 3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
（1）用子查询
思路：将score里数据用group by分组后进行s_score求平均值，用having avg筛选条件，最后将结果和student表进行连接 (联合查询)，选出需求数据。
注：用group by 可以直接分组+求平均值，直接写出即可，不用考虑其他

select  a.s_id,a.s_name,平均成绩 from student a,
 (
  select s_id,avg(s_score) as 平均成绩 from score
  group by s_id
  having avg(s_score)>60
 )r
where a.s_id=r.s_id;

（2）连接查询
思路：直接先将student和score表连接在一起，然后通过group by分组进行求平均值。
注：round（x,1）round会把数值字段舍入为指定的小数位数，x为要舍入的数值，1是位数。

select a.s_id,a.s_name,round(avg(s_score),2) as 平均成绩 from student a
join score b on a.s_id=b.s_id
group by a.s_id 
having round(avg(b.s_score),2)>60;

– 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)
– 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
（1）连接查询

select a.s_id,a.s_name,count(b.c_id) as 课程总数,sum(b.s_score) as 总成绩 from student a
left join score b
on a.s_id=b.s_id
group by a.s_id;

（2）子查询 （很啰嗦不建议用）
– 6、查询"李"姓老师的数量

select count(t_name) as t_name from teacher where t_name like'li%';
select count(t_name) as t_name from teacher where left(t_name,2)='li';

– 7、查询学过"张三"老师授课的同学的信息
（1）左连接

select a.* from  student a
left join score b
on a.s_id=b.s_id
left join course d
on d.c_id=b.c_id
left join teacher e
on d.t_id=e.t_id
where t_name='zhangsan'
group by a.s_id;

（2）多表联合查询

select student.* from student,score,teacher,course
where student.s_id=score.s_id
 and score.c_id=course.c_id
 and course.t_id=teacher.t_id
 and t_name='zhangsan';

– 8、查询没学过"张三"老师授课的同学的信息

select * from student
where student.s_id not in(
select score.s_id from score,teacher,course
where  score.c_id=course.c_id
and course.t_id=teacher.t_id
and t_name='zhangsan'

– 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
（1）连接查询
思路：如果单纯的把student表和score表连接起来，如下

同一个学生学过01课程和02课程的信息不再一起，无法判断，故将student表和score表连接两次，一张student表和两张score表放在一起，如下

在同一个学生信息中出现了01和02课程，故可以选出

select a.* from 
student a,score b,score c 
where a.s_id = b.s_id  and a.s_id = c.s_id and b.c_id='01' and c.c_id='02';

（2）子查询
思路：分别从score选出 c_id为01课程和02课程的s_id，然后把它们当成条件和student的s_id作对比，选出所要信息。

select a.* from student a
where a.s_id in (select s_id from score where c_id='01') 
and a.s_id in (select s_id from score where c_id='02');

– 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
思路：分别从score选出 c_id为01课程和02课程的s_id，然后把它们当成条件和student的s_id作对比，选出所要信息。

select a.* from student a
where a.s_id in (select s_id from score where c_id='01') 
and a.s_id not in (select s_id from score where c_id='02');

– 11.未完待续，即将更新。