2 MYSQL SQL练习题

一、小试牛刀

库表结构

库名：practice，表名：info

建表语句
create table info(id int(3),name varchar(10),age int(3),birth date,sex varchar(5),memo varchar(10));

插入语句
insert into info values(1,"徐洪国","37","1979-03-23","男",'高中'),(2,"王芳芳","26","1988-02-06","女",'本科'),(3,"徐晓盛","24","1990-04-02","男",'硕士'),(4,"陈晓","30","1984-09-12","女",'博士'),(5,"郑凯","27","1987-12-30","男",'大专');

1）请编写sql语句对年龄进行升序排列

2）请编写sql语句查询对“徐”姓开头的人员名单

3）请编写sql语句修改“陈晓”的年龄为“45”

4）请编写sql删除王芳芳这表数据记录。

二、炉火纯青

表结构

学生表
create table Student(s_no int(3),sname varchar(10),sage int(3),sex varchar(5));
insert into Student values(1,'杨过',25,'男'),(2,'小龙女',23,'女'),(3,'乔峰',38,'男'),(4,'令狐冲',27,'男'),(5,'段誉',30,'男'),(6,'王语嫣',22,'女'),(7,'郭靖',40,'男'),(8,'天山童姥',80,'女');

课程表
create table Course(c_no int(3),cname varchar(10),t_no int(3));
insert into Course values(1,"北冥神功",2),(2,"独孤九剑",3),(3,"易筋经",1),(4,"太极拳",4);

成绩表

create table Sc(s_no int(3),c_no int(3),score int(10));
insert into Sc values(1,2,66);
insert into Sc values(1,3,63);
insert into Sc values(2,2,75);
insert into Sc values(3,1,80);
insert into Sc values(3,3,78);
insert into Sc values(3,4,65);
insert into Sc values(3,2,74);
insert into Sc values(4,2,90);
insert into Sc values(4,3,85);
insert into Sc values(5,1,77);
insert into Sc values(5,4,68);
insert into Sc values(6,1,95);
insert into Sc values(7,3,84);
insert into Sc values(7,4,87);
insert into Sc values(8,1,90);

教师表
create table Teacher(t_no int(3),tname varchar(10));
insert into Teacher values(1,'达摩祖师'),(2,'逍遥子'),(3,'独孤求败'),(4,'张三丰');

E-R图

例题：

查询“001”课程比“002”课程成绩高的所有学生的学号
select a.s_no 学号 from Sc a,Sc b where a.s_no=b.s_no and a.c_no=1 and b.c_no=2 and a.score>b.score;

join语法方式：

查询平均成绩大于80分的同学的学号和平均成绩
select s_no 学号,avg(score) 平均成绩 from Sc group by s_no having avg(score)>80;

查询所有同学的学号、姓名、选课数、总成绩；
select Student.s_no,sname,count(c_no),sum(score) from Student,Sc where Student.s_no=Sc.s_no group by s_no,sname;

查询姓张的老师的个数
select count(*) 人数 from Teacher where tname like '张%';

查询没学过“三丰”老师课的同学的学号、姓名
方法一：select s_no,sname from Student where s_no not in (select s_no from Sc,Course c,Teacher t where Sc.c_no=c.c_no and c.t_no=t.t_no and tname like "%三丰");

查询学过“002”并且也学过编号“003”课程的同学的学号、姓名
select s.s_no,s.sname from Student s,Sc sc1,Sc sc2 where sc1.s_no=s.s_no and sc2.s_no=s.s_no and sc1.c_no=2 and sc2.c_no=3;

查询所有课程成绩小于80分的同学的学号、姓名
select s_no,sname from Student where s_no not in (select distinct(s_no) from Sc where score>=80);

查询没有学全所有课的同学的学号、姓名
子查询方法
select s_no,sname from Student where s_no not in (select s_no from Sc group by s_no having count(s_no)=(select count(c_no) from Course));

先连表的方法
select s.s_no 学号,sname 姓名,count(Sc.c_no) 科目数 from Student s,Sc,Course c where s.s_no=Sc.s_no and Sc.c_no=c.c_no group by Sc.s_no,sname having not count(Sc.c_no)=(select count(c_no) from Course);

查询学过学号为“001”同学所有门课的其他同学学号和姓名
select distinct(Sc.s_no) 学号,sname 姓名 from Student,Sc where Sc.s_no=Student.s_no and c_no in (select c_no from Sc where s_no=1) and not Sc.s_no=1;

查询和“001”号同学学习的课程完全相同的其他同学学号和姓名

select s_no,sname from Student where s_no in (select s_no from Sc where c_no in (select c_no from Sc where s_no=1) group by s_no,sname having count(*)=(select count(*) from Sc where s_no=1) and s_no !=1);

把“Sc”表中“张三丰”老师教的课的成绩都更改为此课程的平均成绩
select Sc.s_no 学号,Sc.c_no 课程号,new_score 成绩 FROM Sc inner join ( select c_no,avg(score) new_score from Sc where c_no in (select c.c_no from Course c,Teacher t where c.t_no=t.t_no and t.tname like '%三丰') group by c_no) a where Sc.c_no=a.c_no;

删除学习“达摩祖师”老师课的sc表记录

查询没有上过编号“003”课程的同学学号和姓名
select s_no,sname from Student where s_no not in (select s_no from Sc where c_no=3);

错误：只要有学其他课程的人，就会被加入到没学的行列
select s_no from Sc where s_no not in (select s_no from Sc where c_no=3) group by s_no;

查询各科成绩最高和最低的分：以如下形式显示：课程ID，课程名，最高分，最低分 

select Sc.c_no,cname 课程,max(score) 最高分,min(score) 最低分 from Sc,Course where Sc.c_no=Course.c_no group by c_no;

查询不同老师所教不同课程平均分从高到低显示
select Sc.c_no,cname,avg(score) from Sc,Course where Sc.c_no=Course.c_no group by c_no,cname order by avg(score) desc;

统计各科成绩，各分数段人数：课程ID，课程名称，【100-85】，【85-70】，【70-60】，【<60】
select Sc.c_no 课程号,cname 课程名称,sum(case when score between 85 and 100 then 1 else 0 end) "85-100",sum(case when score between 70 and 85 then 1 else 0 end) "70-85",sum(case when score between 60 and 70 then 1 else 0 end) "60-70",sum(case when score<60 then 1 else 0 end) "<60" from Sc,Course where Sc.c_no=Course.c_no group by Sc.c_no,cname;

查询每门课程被选修的学生数
select Sc.c_no 课程号,cname 课程名,count(*) 选修人数 from Sc,Course where Sc.c_no=Course.c_no group by Sc.c_no,cname;

查询出只选修了一门课程的全部学生的学号和姓名
select Student.s_no,sname,count(c_no) 选课数 from Sc,Student where Student.s_no=Sc.s_no group by s_no,sname having count(c_no)=1;

查询男生、女生人数
select sex 性别,count(*)人数 from Student group by sex;

查询姓“杨”的学生名单
select * from Student where sname like "杨%";

查询同名同性学生名单，并统计同名人数
insert into Student values(9,'乔峰',88,'男');

select sname,count(*) 人数 from Student group by sname having 人数>1;

查询1994年出生的学生名单（注：student表中sage列的类型是datatime）

select * from Student where sage like "1994%";

查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select c_no 课程号,avg(score) 平均成绩 from Sc group by c_no order by avg(score) asc,c_no desc;

查询平均成绩大于85的所有学生的学号，姓名和平均成绩
select Student.s_no 学号,sname 姓名,avg(score) 平均成绩 from Student,Sc where Student.s_no=Sc.s_no group by Sc.s_no,sname having avg(score)>85;

查询课程名称为“独孤九剑”且分数低于80的学生姓名和分数
select sname 姓名,score 独孤九剑成绩 from Student,Sc where Student.s_no=Sc.s_no and c_no=(select c_no from Course where cname="独孤九剑") and score<80;

查询所有学生的选课情况
select s.s_no 学号,sname 姓名,sum(if(Sc.c_no=(select c_no from Course where cname="北冥神功"),1,0)) 北冥神功,sum(if(Sc.c_no=(select c_no from Course where cname="独孤九剑"),1,0)) 独孤九剑,sum(if(Sc.c_no=(select c_no from Course where cname="易筋经"),1,0)) 易筋经,sum(if(Sc.c_no=(select c_no from Course where cname="太极拳"),1,0)) 太极拳 from Student s,Sc,Course c where s.s_no=Sc.s_no and Sc.c_no=c.c_no group by s.s_no,sname;

知识点补充
if(值,t,f)
ifnull(值1，值2)
case when [值1] then [值2] … else[默认值] end

查询平均分数小于80的课程，并按课程号从大到小排序
select c_no 课程号,avg(score) 平均成绩 from Sc group by c_no having avg(score)<80 order by c_no desc;

查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
连接查询
select Student.s_no 学号,sname 姓名,score 成绩 from Student,Sc where Student.s_no=Sc.s_no and c_no=3 and score>80;

子查询
select s_no,sname from Student where s_no in (select s_no from Sc where c_no=3 and Score>80);

求选修了课程的学生人数
select count(distinct s_no) from Sc;

查询选修了“逍老师”所授课程的学生中，成绩最高的学生姓名及其成绩。
select sname 姓名,score 成绩 from Student s,Sc,Course c,Teacher t where s.s_no=Sc.s_no and Sc.c_no=c.c_no and c.t_no=t.t_no and tname like "逍%" order by score desc limit 1;

查询各个课程及相应的选修人数
select Sc.c_no,cname 课程名,count(Sc.c_no) 选课人数 from Sc,Course where Sc.c_no=Course.c_no group by c_no,cname;

查询每门课程最好的前两名
核心板

完整版
select r1.c_no,cname 课程名,s.s_no 学号,sname 姓名,score 成绩 from Student s,Sc r1,Course c where s.s_no=r1.s_no and r1.c_no=c.c_no and (select count(1) from Sc r2 where r2.c_no=r1.c_no and r2.score >= r1.score)<=2 order by c_no,score desc;

查询每门课程的学生选修人数（超过3人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
select c_no 课程号,count(*) 选课人数 from Sc group by c_no having count(*)>3 order by count(*) desc,c_no asc;

检索至少选修三门课程的学生学号
select Student.s_no 学号,count(Sc.s_no) 选修课程数 from Student,Sc where Student.s_no=Sc.s_no group by Student.s_no having count(Sc.s_no)>=3;

查询全部学生都选修的课程的课程号和课程名
插入数据便于查询
insert into Sc values(5,2,81),(6,2,83),(7,2,78),(8,2,58);
都选独孤九剑
连表查询
select c.c_no,cname from Course c,Sc where c.c_no=Sc.c_no and c.c_no=Sc.c_no group by c.c_no,cname having count(Sc.c_no)=(select count(s_no) from Student);
子查询
select c_no,cname from Course where c_no in (select c_no from Sc group by c_no having count(c_no)=(select count(s_no) from Student));

查询两门以上小于80分课程的同学的学号及其平均成绩
select s.s_no,sname,avg(score) from Student s,Sc,(select s_no from Sc where score<80 group by s_no having count(s_no)>=2) A where s.s_no=A.s_no and Sc.s_no=A.s_no group by s.s_no,s.sname;