mysql22道简单练习题，附带表数据，本人亲自测过，放心参考

一、数据准备

1. student表
drop table if exists student;  
create table student (  
  studentNo varchar(10),  
  studentName varchar(10),
  studentAge varchar(3),
  studentSex varchar(1),
  primary key(studentNo)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; 

INSERT INTO student VALUES ('s001', '刘一', '23', '男');
INSERT INTO student VALUES ('s002', '陈二', '23', '男');
INSERT INTO student VALUES ('s003', '张三', '25', '男');
INSERT INTO student VALUES ('s004', '李四', '20', '男');
INSERT INTO student VALUES ('s005', '王五', '20', '男');
INSERT INTO student VALUES ('s006', '赵六', '21', '男');
INSERT INTO student VALUES ('s007', '孙七', '21', '男');
INSERT INTO student VALUES ('s008', '周八', '21', '女');
INSERT INTO student VALUES ('s009', '吴九', '23', '女');
INSERT INTO student VALUES ('s010', '郑十', '22', '女');

2. course表
drop table if exists course;
create table course(
	courseNo varchar(10),
	courseName varchar(10),
	teacherNo varchar(10),
	primary key (courseNo),
	foreign key(teacherNo) references teacher(teacherNo)
);
INSERT INTO course VALUES ('c001', 'mysql', 't1');
INSERT INTO course VALUES ('c002', 'oracle', 't2');
INSERT INTO course VALUES ('c003', 'sql2005', 't3');
INSERT INTO course VALUES ('c004', 'db2', 't4');

3. score表
drop table if exists grade;
create table grade(
studentNo varchar(10),
courseNo varchar(10),
score float(4,2),
constraint pk_grade primary key (studentNo,courseNo),
constraint fk_studentNo foreign key (studentNo) references student (studentNo) on delete no action on update no action,
constraint fk_courseNo foreign key (courseNo) references course (courseNo) on delete no action on update no action
);
insert into grade values ('s001','c001',50);
insert into grade values ('s002','c001',55);
insert into grade values ('s003','c001',50);
insert into grade values ('s004','c001',59);
insert into grade values ('s001','c002',82.9);
insert into grade values ('s002','c002',72.9);
insert into grade values ('s003','c002',81.9);
insert into grade values ('s004','c002',59);
insert into grade values ('s001','c003',90);
insert into grade values ('s002','c003',72.9);
insert into grade values ('s003','c003',81.9);
insert into grade values ('s004','c003',59);
insert into grade values ('s001','c004',55);
insert into grade values ('s002','c004',72.9);

4. teacher表
drop table if exists teacher;
create table teacher(
teacherNo varchar(10),
teacherName varchar(10),
primary key(teacherNo)
);
INSERT INTO teacher VALUES ('t1', '孔子');
INSERT INTO teacher VALUES ('t2', '孟子');
INSERT INTO teacher VALUES ('t3', '老子');
INSERT INTO teacher VALUES ('t4', '墨子');
INSERT INTO teacher VALUES ('t5', '儿子');
INSERT INTO teacher VALUES ('t6', '孔乙己');

二、
1、查询“c002”课程比“c001”课程成绩高的学生信息，显示：学号

select a.studentNo
from
(
	select studentNo,courseNo,score
	from grade 
	where courseNo ='c001'
) a,
(
	select studentNo,courseNo,score
	from grade
	where courseNo ='c002'
) b
where b.score > a.score and a.studentNo = b.studentNo;

2、查询平均成绩大于60分的学生，显示：学号、姓名和平均成绩

select s.studentNo,avg(score) as 平均分
from grade g
inner join student s on g.studentNo=s.studentNo
group by s.studentNo having avg(score) > 60;

3、查询所有学生的选课信息，显示：学号、姓名、选课数、总成绩

select s.studentNo,s.studentName,count(1) 选课数,sum(g.score) 总成绩
from student s
inner join grade g on s.studentNo = g.studentNo
group by s.studentNo,s.studentName;

4、查询姓“孔”的老师的人数，显示：姓孔老师的人数

select count(1) as 姓孔老师的人数
from teacher
where teacherName like '孔%';

5、查询选修了“老子”的课的学生信息，显示：学号、姓名以及性别

select studentNo,studentName,studentSex
from student
where studentNo in
(
	select g.studentNo
	from course c 
	inner join teacher t on c.teacherNo = t.teacherNo
	inner join grade g on c.courseNo = g.courseNo
	where t.teacherName = '老子'
);

6、查询没有选修“老子”的课的学生信息，显示：学号、姓名以及性别

select studentNo,studentName,studentSex
from student
where studentNo not in
(
	select g.studentNo
	from course c 
	inner join teacher t on c.teacherNo = t.teacherNo
	inner join grade g on c.courseNo = g.courseNo
	where t.teacherName = '老子'
);

7、查询选修了“c001”并且也选修“c002”课程的学生信息，显示学号、姓名

select s.studentNo,s.studentName
from grade g
inner join student s on g.studentNo = s.studentNo
where g.courseNo ='c001' and s.studentNo in
(
	select studentNo
	from grade
	where courseNo ='c002'
);

8、查询“c002”课程比“c001”课程成绩高的学生信息，显示学号、姓名、性别

select t.studentNo,t.studentName,t.studentSex
from student t
inner join 
(
	select studentNo,score
	from grade 
	where courseNo ='c001'
) a on t.studentNo =a.studentNo,
(
	select studentNo,score
	from grade
	where courseNo ='c002'
) b
where b.score > a.score and a.studentNo = b.studentNo;

9、查询所有课程成绩大于等于60的学生信息，显示：学号、姓名

select distinct t.studentNo,t.studentName
from student t
inner join grade g on t.studentNo = g.studentNo
where t.studentNo not in
(
	select studentNo
	from grade
	where score < 60
);

10、查询没有全选所有课的学生信息，显示：学号、姓名

select t.studentNo,t.studentName
from student t
inner join grade g on t.studentNo = g.studentNo
group by t.studentNo,t.studentName having count(1) < 
(
	select count(1) from course
);

11、查询至少选一门课的学生信息，显示：学号、姓名

select t.studentNo,t.studentName
from student t
inner join grade g on t.studentNo = g.studentNo
group by t.studentNo,t.studentName having count(1) > 1 

12、查询各个课程的授课老师以及相应的选修人数，显示：课程号，课程名以及选修人数

select c.courseNo,c.courseName,t.teacherName,count(1) as 选修人数
from grade g
inner join course c on g.courseNo = c.courseNo
inner join teacher t on c.teacherNo = t.teacherNo
group by c.courseNo,c.courseName;

13、查询不同课程成绩相同的数量,显示：学号、课程以及成绩

select distinct a.studentNo,a.courseNo,a.score
from grade a
inner join grade b on a.score = b.score
where a.courseNo <> b.courseNo;

14、查询每门课程成绩最好的前两名，显示：课程号以及成绩

select a.courseNo, a.score 
from grade a
where
(
	select count(distinct score)
	from grade b
	where a.courseNo = b.courseNo and a.score >= b.score
) <= 2
order by a.courseNo asc, a.score desc;

15、统计每门课程的学生选修人数(超过2人的课程才统计)，查询结果按人数降序排序，若人数相同，按课程号升序排序，显示：课程号、选修人数以及授课教师

select c.courseNo,count(1) as 选修人数,t.teacherName
from grade g
inner join course c on g.courseNo = c.courseNo
inner join teacher t on c.teacherNo = t.teacherNo
group by courseNo having count(1) >2 order by 选修人数 desc,courseNo asc;

16、查询至少选修三门课程的学生信息，显示学号，姓名

select s.studentNo,s.studentName
from grade g
inner join student s on g.studentNo = s.studentNo
group by studentNo having count(1) >=3;

17、查询出所有学生的选课信息，显示课程号，课程名以及授课教师

select courseNo,courseName,teacherName
from course
inner join teacher on course.teacherNo=teacher.teacherNo
where courseNo in
(
	select courseNo from grade
);

18、查询没有选修“孔子”老师讲授的任一门课程的学生信息，显示：学号，姓名

select studentNo,studentName
from student
where studentNo in
(
	select g.studentNo
	from course c
	inner join grade g on c.courseNo = g.courseNo
	inner join teacher_t t on c.teacherNo = t.teacherNo
	where t.teacherName = '孔子'
);

19、查询两门以上不及格课程的学生的信息，显示：学号，姓名及平均成绩

select s.studentNo,s.studentName,avg(score) as 平均成绩
from grade g
inner join student s on g.studentNo = s.studentNo 
where score < 60
group by studentNo having count(1) >=2;

20、查询“c001”课程分数小于60的学生信息，按分数降序排列，显示：学号、姓名、分数

select s.studentNo,s.studentName,g.score
from grade g
inner join student s on g.studentNo = s.studentNo 
where courseNo='c001' and score < 60 order by score desc;

21、删除“s001”学生的“c002”课程的成绩

delete
from grade
where studentNo = 's001' and courseNo='c002';

22、把成绩表中“孔子”老师教的课的成绩都更改为此课程的平均成绩

update grade 
set score=
(
    select a.pj from 
 	(	
		select avg(score) as pj from grade
	) a
)
where courseNo in
(	
	select c.courseNo
	from teacher_t t
	inner join course_t c on t.teacherNo = c.teacherNo
	where t.teacherName = '孔子'
);