1.5 mysql练习题37道，做完这些mysql练习题，立马让你进阶。(附答案)

一、现有数据库casemanage中表结构如下图

TABLENAME:afinfo

Id	name	age	birth	sex	memo
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删除王芳芳这表数据记录。

二、现有以下几个表

学生信息表（student）

1）查询出所有学生信息，SQL怎么编写？

2）新学生小明，学号为005，需要将信息写入学生信息表，SQL语句怎么编写？

3）李四语文成绩被登记错误，成绩实际为85分，更新到考试信息表中，SQL语句怎么编写？

4）查询出各科成绩的平均成绩，显示字段为：学科、平均分，SQL怎么编写？

5）查询出所有学生各科成绩，显示字段为：姓名、学号、学科、成绩，并以学号与学科排序，没有成绩的学生也需要列出，SQL怎么编写？

6）查询出单科成绩最高的，显示字段为：姓名、学号、学科、成绩，SQL怎么编写？

三、根据要求写出SQL语句。

Student（s_no ,sname,sage,sex）学生表
Teacher（t_no,tname）教师表
Course（c_no,cname,t_no）课程表 （t_no关联的是教师表的t_no）
Sc(s_no,c_no,score)成绩表 (c_no关联的是课程表的c_no)

数据根据下面的题目进行添加

1、查询“001”课程比“002”课程成绩高的所有学生的学号。

2、查询平均成绩大于60分的同学的学号和平均成绩。

3、查询所有同学的学号、姓名、选课数、总成绩。

4、查询姓李的老师的个数。

5、查询没学过“叶平”老师课的同学的学号、姓名

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名。

7、查询所有课程成绩小于60分的同学的学号、姓名。

8、查询没有学全所有课的同学的学号、姓名。

10、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名。

11、把“sc”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩。

12、查询和“1002”号同学学习的课程完全相同的其他同学学号和姓名。

13、删除学习“叶平”老师课的sc表记录。

14、向sc表中插入一些记录，这些记录要求符合一下条件：没有上过编号“003”课程的

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

16、查询不同老师所教不同课程平均分从高到低显示。

17、统计各科成绩，各分数段人数：课程ID，课程名称，【100-85】，【85-70】，【70-60】，【<60】

18、查询每门课程被选修的学生数

19、查询出只选修了一门课程的全部学生的学号和姓名

20、查询男生、女生人数

21、查询姓“张”的学生名单

22、查询同名同性学生名单，并统计同名人数。

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

24、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列。

25、查询平均成绩都大于85的所有学生的学号，姓名和平均成绩

26、查询课程名称为“数据库”且分数低于60的学生姓名和分数

27、查询所有学生的选课情况

28、查询不及格的课程，并按课程号从大到小排序。

29、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名。

30、求选修了课程的学生人数。

31、查询选修了“冯老师”所授课程的学生中，成绩最高的学生姓名及其成绩。

32、查询各个课程及相应的选修人数。

33、查询每门课程最好的前两名。

34、查询每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列。

35、检索至少选修两门课程的学生学号。

36、查询全部学生都选修的课程的课程号和课程名。

37、查询两门以上不及格课程的同学的学号及其平均成绩。

自己写的查询句子答案（可能并不是最佳）

一、

create database casemanage
default charset utf8;

drop table if exists afinfo;
create table afinfo(
id int(10) primary key auto_increment,
`name` varchar(20) not null,
age int(10) not null,
birth date not null,
sex char(2) not null,
memo varchar(20)
)

insert afinfo 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','男','大专')
-- 排序 order by
//1
select * from afinfo order by age asc;
//2
select * from afinfo where name like '徐%';
//3
update afinfo set age = 45 where name = '陈晓'; 
//4
delete from afinfo where name = '王芳芳';

二、

create table student (
name varchar(20) not NULL,
code int(10) not null
)

create table exam(
code int(10) not null comment '学号',
subject varchar(20),
score int(10)
)

insert student values('张三',001),('李四',002),('王五',003),('甲六',004)
insert exam values(001,'数学',80),
(002,'数学',75),
(001,'语文',90),
(002,'语文',80),
(001,'英语',90),
(002,'英语',85),
(003,'英语',80),
(004,'英语',70)

//1
select s.name 姓名,s.code 学号,e.subject 学科,e.score 成绩 from student s left join exam e on s.code = e.code;
select * from exam;
//2
insert student value('小明',005)
//3
update exam set score = 85 where code = (select code from student where name = '李四') and subject = '语文';
//4
select avg(score) 平均分,subject 学科 from exam group by subject ;
//5
select * from student  
left join exam  on s.code = e.code order by e.code asc,subject asc;
//6
-- 当两个表聚合而且没有聚合可以想通的 列的时候 可以对列进行存在查询 （两个查询）
select s.code,s.name from student s join exam e on s.code = e.code where (e.`subject`,e.score) in 
(
select subject,max(score) from exam group by subject
) 

三、
(部分表名做了更改)

create table Student1(
s_no int(10) primary key auto_increment,
sname varchar(25),
anage varchar(20),
sex varchar(20)
)

create table course(
c_no int(10) comment '课程号',
cname VARCHAR(10),
t_no int(10) comment '教师号'
)
drop table if exists sc
create table sc(
s_no int(10) comment '学号',
c_no int(10) comment '课程号',
score int(10)
)
drop table if exists sc1
create table sc1(
s_no int(10),
c_no int(10),
score int(10)
)

INSERT INTO `sc1` VALUES (1001, 002, 85);
INSERT INTO `sc1` VALUES (1001, 003, 87);
INSERT INTO `sc1` VALUES (1001, 004, 83);
INSERT INTO `sc1` VALUES (1002, 001, 50);
INSERT INTO `sc1` VALUES (1002, 002, 45);
INSERT INTO `sc1` VALUES (1002, 004, 55);
INSERT INTO `sc1` VALUES (1003, 001, 91);
INSERT INTO `sc1` VALUES (1003, 002, 89);
INSERT INTO `sc1` VALUES (1003, 003, 92);
INSERT INTO `sc1` VALUES (1003, 004, 96);
INSERT INTO `sc1` VALUES (1004, 001, 55);
INSERT INTO `sc1` VALUES (1004, 002, 44);
INSERT INTO `sc1` VALUES (1004, 004, 36);
INSERT INTO `sc1` VALUES (1005, 001, 25);
INSERT INTO `sc1` VALUES (1005, 002, 21);
INSERT INTO `sc1` VALUES (1005, 003, 58);
INSERT INTO `sc1` VALUES (1005, 004, 21);



create table teacher(
t_no int(10),
tname VARCHAR(10)
)
-- 1
insert course values(001,'语文',1),(002,'数学',2),(003,'英语',3)
insert sc values(2001,002,100),(2001,001,70),(2002,001,88),(2002,002,78)
1 -- 查询“001”课程比“002”课程成绩高的所有学生的学号。 查出最大值 然后按是课程号排序 加入条件
-- select max(score) 最高分,s_no 学号,c_no 课程号 from sc GROUP BY c_no HAVING c_no = 1
-- select s_no from sc1 where score = (select max(score) 最高分 from sc1 GROUP BY c_no HAVING c_no = 1)
-- select max(score) 最高分,c_no 课程号 from sc1 GROUP BY c_no HAVING c_no = 1

-- 0查询“001”课程比“002”课程成绩高的所有学生的学号。 查出最大值

select sc1.s_no from sc1 sc1 JOIN sc1 sc2 ON sc1.s_no=sc2.s_no
WHERE sc1.c_no='001' AND sc2.c_no='002' AND sc1.score>sc2.score


select sc2.s_no from sc1 sc2 join sc1 sc3 on sc2.s_no = sc3.s_no
where sc2.c_no = '001' and sc3.c_no = '002' and sc2.score > sc3.score 

select * from sc1

2 -- 查询平均成绩大于60的学号和平均成绩
select s_no,avg(score) from sc1 group by s_no having avg(score) > 60;

3 -- 查询所有同学的学号、姓名、选课数、总成绩。
insert Student1 values(1002,'李四','20','男');
insert Student1 values(1003,'王四','20','女');
insert Student1 values(1004,'赵六','18','女');
insert Student1 values(1005,'杨七','17','男');

select t.s_no 学号,sname 姓名,count(c_no) 课程数,sum(score) 总分 
from Student1 t left join sc1 s on t.s_no = s.s_no group by
t.s_no

-- 4  查询姓李的老师的个数。
insert teacher values(4,'叶平'),(2,'李春雷'),(3,'王铁柱'),(4,'叶平')


select count(tname) 个数 FROM teacher where tname like '李%'


-- 5  查询没学过“叶平”老师课的同学的学号、姓名
select * from teacher
select * from student1
select * from course
select * from sc1
insert course values(1,'java','4')
update course set c_no = 4 where t_no = 4
         -- 查出 选叶平的老师的学生
select t_no from teacher where tname = '叶平'
				-- 查课程号 c_no
select c_no from course c 
join teacher t
on c.t_no = t.t_no 
where t.tname = '叶平'
       -- 通过查出来的c_no查s_no
select s_no from sc1 sc
join (
select c_no from course c 
join teacher t
on c.t_no = t.t_no 
where t.tname = '叶平'
) c
on  sc.c_no = c.c_no


select s_no from sc1 where s_no not in  
		(
		select s_no from  sc1
		where c_no in (
				(select DISTINCT c_no from course c 
				join teacher t
				on c.t_no = t.t_no 
				where t.tname = '叶平')
		)
)


      

-- 6  查询学过“001”并且也学过编号“002”课程的同学的学号、姓名。
select s_no from sc1 where c_no = 1 or c_no = 2

select sc.s_no from (select s_no from sc1
where c_no = 1) n 
join sc1 sc 
on sc.s_no = n.s_no
join student1 st1
on sc.s_no = st1.s_no
where sc.c_no = 2

-- 7 查询课程成绩小于60分的的学号、姓名。所有同学 
select st1.s_no,sname,m.c_no from student1 st1
join 
(select s_no,max(score),c_no from sc1 group by c_no having max(score) < 60) m
on st1.s_no = m.s_no


-- 当查询的中有聚合函数的时候，这个表不可以做子查询 不符合逻辑
-- 	select c.s_no 学号,max(score) 学分 from
--  (select s_no,max(score) from sc1 group by sc1.c_no )  c
-- 	where c.max(score) > 60;



-- 8 查询没有学全所有课的同学的学号、姓名。
select * from sc1 where s_no != 1001

--    select s_no,sname from student1 where s_no not in (
-- 		select sc01.s_no from sc1 sc01 join sc1 sc02 on sc01.s_no = sc02.s_no join sc1 sc03 
-- 		on sc01.s_no = sc03.s_no join sc1 sc04 on sc01.s_no = sc04.s_no
-- 		where sc01.c_no = 1 and sc02.c_no = 2 and sc03.c_no = 3 and sc04.c_no = 4
-- 		)


select count(*) from course

select count(*) from student1 sd join sc1 sc on sd.s_no = sc.s_no
join course e on e.c_no = sc.c_no
group by sd.s_no 

select d.s_no from student1 d join sc1 sc on d.s_no = sc.s_no
where (
select sd.s_no,count(*) from student1 sd join sc1 sc on sd.s_no = sc.s_no
join course e on e.c_no = sc.c_no
group by sd.s_no
) < (select count(*) from course)


-- 10查询至少学过学号为“1001”同学所有一门课的其他同学学号和姓名。
select c_no from sc1 where s_no = 1001

select s_no from sc1 where c_no in (select c_no from sc1 where s_no = 1001)

-- 11 把“sc”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩。
-- 课程号
select c_no from course c join teacher t on c.t_no = t.t_no where tname = '叶平'
-- 平均成绩
select avg(score) from sc1 where c_no in
(select c_no from course c join teacher t on c.t_no = t.t_no where tname = '叶平')
group by c_no

select * from course

update sc1 set score = (
select bb.co from 
(
	select avg(score) co from sc1 sc where c_no in
	(select c_no  from course c join teacher t on c.t_no = t.t_no where tname = '叶平')
	group by c_no
) bb
)
where c_no in (
select c_no from course c join teacher t on c.t_no = t.t_no where tname = '叶平'
)


-- 12 查询和“1002”号同学学习的课程完全相同的其他同学学号和姓名。
select s.c_no from sc1 s join course s1 
on s.c_no = s1.c_no
join student1 d
on s.s_no = d.s_no
where d.s_no = 1002

select s_no from sc1 where c_no not in (
select s.c_no from sc1 s join course s1 
on s.c_no = s1.c_no
join student1 d
on s.s_no = d.s_no
where d.s_no = 1002
)



-- 13 删除学习“叶平”老师课的sc表记录。

select * from sc1

delete from sc1 where c_no = 
(select c_no from course c 
join teacher t 
on c.t_no = t.t_no 
where t.tname = '叶平')


-- 14 向sc表中插入一些记录，这些记录要求符合一下条件：没有上过编号“003”课程的

？？？

-- 15 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分。
select max(score) as 最高分,min(score) 最低分,c.c_no 课程ID from sc1 s 
join course c
on c.c_no = s.c_no 
group by cname;


-- 16查询不同老师所教不同课程平均分从高到低显示
 
select c_no from course

select c.cname,avg(score) as a from sc1 s 
join course c on s.c_no = c.c_no 
group by cname order by a desc;


-- 17 统计各科成绩，各分数段人数：课程ID，课程名称，【100-85】，【85-70】，【70-60】，
select * from sc1
-- 总选课人数
select count(*),c.c_no,c.cname from sc1 s 
join course c on s.c_no = c.c_no 
group by c.cname

select * from sc1 
-- 算出区间人数
select count(*) from course c
join sc1 s on s.c_no = c.c_no 
where s.score < 100  and s.score > 85 group by cname

select * from sc1


select count(case when s.score < 100  and s.score > 85 then '1' end)
`[100-85]`,
 count(case when s.score < 85  and s.score > 70 then '1' end)
`[85-70]`,
 count(case when s.score < 70  and s.score > 60 then '1' end)
`[70-60]`,
 count(case when s.score < 60 then '1' else null end)
`[0-60]`, 
c.c_no,c.cname from sc1 s 
join course c on s.c_no = c.c_no 
group by c.cname


-- 18、查询每门课程被选修的学生数


select count(s.s_no) 选修数,cname 课程 from sc1 s 
join course c
on s.c_no = c.c_no group by c.c_no



-- 19 查询出只选修了一门课程的全部学生的学号和姓名


select sc.s_no,count(sc.c_no) c from student1 t 
join sc1 sc on sc.s_no = t.s_no group by sc.s_no
having c = 1


-- 20 查询男生、女生人数
select count(
case 
when sex = '男' then 1
end) 男,
count(case
when sex = '女' then 1
end) 女
from student1



-- 21、查询姓“张”的学生名单

select * from student1 where sname like '张%'

-- 22、查询同名同性学生名单，并统计同名人数。


select sname,s_no,count(*) from student1 where sname like '___' and sex like '_'



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

select * from student1
update student1 set anage = '1994-01-10 10:01:10'
alter table student1 change anage anage datetime default '1994-01-10 10:01:10'


select * from student1 where anage > '1994-01-01 00:00:00' and anage < '1995-01-01 00:00:00'

-- 24、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列。

select c_no,avg(score) co from sc1 group by c_no order by co asc,c_no desc





-- 25、查询平均成绩都大于85的所有学生的学号，姓名和平均成绩

select t.s_no,sname co from sc1 s,student1 t where s.s_no = t.s_no group by s_no having avg(score) > 85


-- 26、查询课程名称为“数据库”且分数低于60的学生姓名和分数
select s.s_no,sname from sc1 s,student1 t,course c 
where s.s_no = t.s_no and s.c_no = c.c_no and s.score < 60 and cname = '数据库'

-- 27、查询所有学生的选课情况
select * from student1 t,sc1 s,course c 
where s.s_no = t.s_no and s.c_no = c.c_no

-- 28、查询不及格的课程，并按课程号从大到小排序。

select DISTINCT(t.c_no),cname from course t join sc1 s on s.c_no = t.c_no  
where score < 60 order by t.c_no desc

-- 29、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名。
select s.s_no,sname from sc1 s,student1 t,course c where s.s_no = t.s_no and s.c_no = c.c_no and s.score > 80 and c.c_no = 3


-- 30、求选修了课程的学生人数。
-- 各选修的课程数
select s.s_no,count(c.c_no) z from sc1 s,course c where s.c_no = c.c_no 
group by s.s_no having z > 0

select count(*) from (
select s.s_no s,count(c.c_no) z from sc1 s,course c where s.c_no = c.c_no 
group by s.s_no having z > 0
)b


-- 31、查询选修了“冯老师”所授课程的学生中，成绩最高的学生姓名及其成绩。

select max(score),sname from student1 t,sc1 s,
course c,teacher e
 where t.s_no = s.s_no and s.c_no = c.c_no and c.t_no = e.t_no and tname = '冯老师'



-- 32、查询各个课程及相应的选修人数。
select count(*),cname from course join sc1 on sc1.c_no =course.c_no  group by cname



-- 33、查询每门课程最好的前两名。


-- 34、查询每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列。

select count(*) c,cname from course join sc1 on sc1.c_no =course.c_no  
group by cname having c > 10 order by c desc
-- 35、检索至少选修两门课程的学生学号。
select sc.s_no,count(sc.c_no) c from student1 t 
join sc1 sc on sc.s_no = t.s_no group by sc.s_no
having c > 1

-- 36、查询全部学生都选修的课程的课程号和课程名。
select count(*) from course 

select DISTINCT(count(sc1.c_no)) s,sc1.c_no,cname from course 
join sc1 
on sc1.c_no = course.c_no group by sc1.s_no 
having s = (select count(*) from course )

-- 37、查询两门以上不及格课程的同学的学号及其平均成绩。


select s.s_no,count(s.score) 不及格科目数,avg(score) 平均成绩 from sc1 s,student1 t,course c 
where s.s_no = t.s_no and s.c_no = c.c_no 
and s.score < 60 group by s.s_no having count(s.score) >2