MYSQL练习

WPhoenixes

于 2021-08-03 15:02:12 发布

阅读量231

点赞数

分类专栏： mysql 文章标签： mysql

原文链接：https://www.cnblogs.com/pythonxiaohu/p/5749864.html

版权

mysql 专栏收录该内容

5 篇文章 3 订阅

订阅专栏

mysql练习

https://www.cnblogs.com/pythonxiaohu/p/5749864.html
案例表图片

1、创建测试表和数据

create table class (
cidint not null auto_increment primary key, 
caption varchar(50)
);

insert into class values (0, "三年二班"),(0, "一年三班"),(0, "三年一班");

create table student (
sid int not null auto_increment primary key,
sname varchar(20),gender varchar(2),
class_id int not null references class(cid)
);

insert into student values (0,"钢弹","女",1),(0,"铁锤","女",1),(0,"山炮","男",2);

create table teacher( 
tid int not null auto_increment primary key,
tname varchar(20)
);

insert into teacher values (0,"波多"),(0,"苍空"),(0,"饭岛");

create table course (
cid int not null auto_increment primary key,
cname varchar(10),
teacher_id int not null references teacher(tid)
);

insert into course values (0,"生物",1),(0,"体育",1),(0,"物理",2);

create table score (
sid int not null auto_increment primary key,
student_id int not null references student(sid), 
course_id int not null references course(cid), 
number int not null default 0,
);

insert into score values (0,1,1,60),(0,1,2,59),(0,2,2,100);

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；

select A.student_id from(

	select student_id,number from score 
		left join course 
		on score .course_id = course.cid 
		where course.cname="生物") 
	
	as A 
	inner join
	
	(select student_id,number from score 
		left join course 
		on score .course_id = course.cid 
		where course.cname="物理") 
	
	as B 
	on A.student_id  = B.student_id 
	where A.number > B.number;

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

select student_id,avg(number) as avg_num 
from score group by student_id having avg_num >60;

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

select score.student_id,student.sname,count(score.course_id),sum(score.number) 
from score 
left join student 
on score.student_id=student.sid 
group by score.student_id;

5、查询姓“李”的老师的个数；

select count(*) from teacher where tname like "李%";

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

select sid,sname from student 
where sid not in 
(select student_id from score 
where course_id in 
(select cid from course where teacher_id =(select tid from teacher where tname="叶平")) 
group by student_id);

or

select sid,sname from student 
where sid not in 
(select distinct student_id from score 
where course_id in
(select cid from course where teacher_id =(select tid from teacher where tname="叶平"))
);

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

select A.student_id,student.sname from 
(select student_id from score where course_id=1 or course_id=2)  as A 
left join student 
on student.sid=A.student_id 
group by student_id having count(*)>1;

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

select student.sid,sname from student 
left join score on student.sid=score.student_id 
where score.course_id in 
(select cid from course left join teacher on teacher.tid = course.teacher_id where tname="叶平") 
group by student_id;

9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

select sid, sname from student 
where sid in
(select A.student_id from 
(select student_id,number from score where course_id=1) as A 
inner join 
(select student_id,number from score where course_id=2) as B 
on A.student_id = B.student_id
where A.number > B.number);

10、查询有课程成绩小于60分的同学的学号、姓名；

select sid,sname from student 
where sid in 
(select distinct student_id from score where number < 60);

11、查询没有学全所有课的同学的学号、姓名；

select sid,sname from student
where sid not in 
(select student_id from score 
group by student_id 
having count(*) = (select count(*) from course));

12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

select student.sid,sname from student 
where sid in 
(select distinct student_id from score where course_id in 
(select course_id from score where student_id = 1) and student_id != 1);

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

select student_id,sname,count(course_id) from score 
left join student on score.student_id = student.sid
where student_id != 1 
and course_id in 
(select course_id from score where student_id = 1)
group by student_id having count(course_id) =
(select count(course_id) from score where student_id = 1);

14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

select student_id,sname from score 
left join student on score.student_id = student.sid 

where 
student_id in 
(select student_id from score  where student_id != 2 
group by student_id 
having count(course_id) = 
(select count(*) from score where student_id = 2)) 
and 
course_id in 
(select course_id from score where student_id = 2) 

group by student_id 
having count(course_id) = 
(select count(*) from score where student_id = 2);

15、删除学习“叶平”老师课的SC表记录；

delete from score 
where course_id in
(select cid from course where teacher_id = 
(select tid from teacher where tname="叶平"));

16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号2课程的同学学号；②插入2号课程的平均成绩；

insert into score(student_id,course_id, number) 
select sid,2,(select avg(number) from score where course_id =2) 
from student
where sid not in 
(select  student_id from score where course_id = 2);

17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示：学生ID,语文,数学,英语,有效课程数,有效平均分；

select sc.student_id as sid,
       (select number from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sw,
       (select number from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl,
       (select number from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty,
       count(sc.course_id) as count,
       avg(sc.number) as avg 
from score as sc
group by student_id 
order by avg(sc.number) asc;

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
```
select course_id as cid,
max(number) as max,
min(number) as min  
from score
group by course_id;
```

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

select course_id, avg(number) as avgnum,
sum(case when score.number > 60 then 1 else 0 END)/count(1)*100 as percent 
from score group by course_id order by avgnum asc,percent desc;

20、课程平均分从高到低显示（现实任课老师）；
21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
22、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
```
select course_id, avg(if(isnull(number), 0, number)) as avg from score
group by course_id
order by avg asc ,course_id desc;
```

23、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；

select student.sid,sname,avg(if(isnull(number), 0, number)) as avg 
from score 
left join student 
on student.sid=score.student_id 
group by student_id
having avg>85;

24、求选了课程的学生人数

select count(*) from 
(select student_id,count(*) from score 
group by student_id) as A;

25、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

select sname,max(number) as max_number from score 
inner join student 
on student.sid = score.student_id 
where course_id in 
(select cid from course where teacher_id= 
(select tid from teacher where tname="叶平") );

26、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

select distinct s1.student_id,s1.course_id,s2.course_id,s1.number as number  
from score as s1, score as s2 
where s1.number= s2.number and s1.course_id != s2.course_id;

27、查询每门课程成绩最好的前两名；

select course_id,student_id,number from score as sc where

(select count(*) from score where 
course_id=sc.course_id and number > sc.number)<3  

order by sc.course_id asc, number desc;

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

select course.cid,cname from course 
left join score 
on course.cid=score.course_id 
group by course_id 
having count(*)=(select count(*) from student);

29、查询没学过“叶平”老师讲授的任一门课程的学生姓名；

select sname from student where sid not in 
(select student_id from score where course_id in  
(select cid from course where teacher_id = 
(select tid from teacher where tname="叶平"))
group by student_id);

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

select student_id,avg(number) as avg
from score where student_id in 
(select student_id from score 
where number<60
group by student_id
having count(*)>=2);