MySQL练习题 （练习表+题目+答案）「建议收藏」CSDN博客

mysql练习题 （表+题目+答案）

一、创建所需要练习的表

CREATE TABLE J_TEACHER
tno int NOT NULL PRIMARY KEY,
tname varchar20) NOT NULL
);

INSERT INTO J_TEACHERtno,tname)VALUES1,‘张老师’);
INSERT INTO J_TEACHERtno,tname)VALUES2,‘王老师’);
INSERT INTO J_TEACHERtno,tname)VALUES3,‘李老师’);
INSERT INTO J_TEACHERtno,tname)VALUES4,‘赵老师’);
INSERT INTO J_TEACHERtno,tname)VALUES5,‘刘老师’);
INSERT INTO J_TEACHERtno,tname)VALUES6,‘向老师’);
INSERT INTO J_TEACHERtno,tname)VALUES7,‘李文静’);
INSERT INTO J_TEACHERtno,tname)VALUES8,‘叶平’);

CREATE TABLE J_STUDENT
sno int NOT NULL PRIMARY KEY,
sname varchar20) NOT NULL,
sage datetime NOT NULL,
ssex char2) NOT NULL
);

INSERT INTO J_STUDENTsno,sname,sage,ssex) VALUES1,‘张三’,‘1980-1-23’,‘男’);
INSERT INTO J_STUDENTsno,sname,sage,ssex) VALUES2,‘李四’,‘1982-12-12’,‘男’);
INSERT INTO J_STUDENTsno,sname,sage,ssex) VALUES3,‘张飒’,‘1981-9-9’,‘男’);
INSERT INTO J_STUDENTsno,sname,sage,ssex) VALUES4,‘莉莉’,‘1983-3-23’,‘女’);
INSERT INTO J_STUDENTsno,sname,sage,ssex) VALUES5,‘王弼’,‘1982-6-21’,‘男’);
INSERT INTO J_STUDENTsno,sname,sage,ssex) VALUES6,‘王丽’,‘1984-10-10’,‘女’);
INSERT INTO J_STUDENTsno,sname,sage,ssex) VALUES7,‘刘香’,‘1980-12-22’,‘女’);

CREATE TABLE J_COURSE
cno int NOT NULL PRIMARY KEY,
cname varchar20) NOT NULL,
tno int NOT NULL
);

insert into J_COURSEcno,cname,tno) values1,‘企业管理’,3);
insert into J_COURSEcno,cname,tno) values2,‘马克思’,1);
insert into J_COURSEcno,cname,tno) values3,‘UML’,2);
insert into J_COURSEcno,cname,tno) values4,‘数据库’,5);
insert into J_COURSEcno,cname,tno) values5,‘物理’,8);

CREATE TABLE J_SCORE
sno int NOT NULL,
cno int NOT NULL,
score int NOT NULL
);

ALTER TABLE J_SCORE ADD CONSTRAINT FK_SCORE_course FOREIGN KEYcno)
REFERENCES J_COURSE cno);
ALTER TABLE J_SCORE ADD CONSTRAINT FK_score_student FOREIGN KEYsno)
REFERENCES J_STUDENT sno);

INSERT INTO J_SCOREsno,cno,score)VALUES1,1,80);
INSERT INTO J_SCOREsno,cno,score)VALUES1,2,86);
INSERT INTO J_SCOREsno,cno,score)VALUES1,3,83);
INSERT INTO J_SCOREsno,cno,score)VALUES1,4,89);

INSERT INTO J_SCOREsno,cno,score)VALUES2,1,50);
INSERT INTO J_SCOREsno,cno,score)VALUES2,2,36);
INSERT INTO J_SCOREsno,cno,score)VALUES2,3,43);
INSERT INTO J_SCOREsno,cno,score)VALUES2,4,59);

INSERT INTO J_SCOREsno,cno,score)VALUES3,1,50);
INSERT INTO J_SCOREsno,cno,score)VALUES3,2,96);
INSERT INTO J_SCOREsno,cno,score)VALUES3,3,73);
INSERT INTO J_SCOREsno,cno,score)VALUES3,4,69);

INSERT INTO J_SCOREsno,cno,score)VALUES4,1,90);
INSERT INTO J_SCOREsno,cno,score)VALUES4,2,36);
INSERT INTO J_SCOREsno,cno,score)VALUES4,3,88);
INSERT INTO J_SCOREsno,cno,score)VALUES4,4,99);

INSERT INTO J_SCOREsno,cno,score)VALUES5,1,90);
INSERT INTO J_SCOREsno,cno,score)VALUES5,2,96);
INSERT INTO J_SCOREsno,cno,score)VALUES5,3,98);
INSERT INTO J_SCOREsno,cno,score)VALUES5,4,99);

INSERT INTO J_SCOREsno,cno,score)VALUES6,1,70);
INSERT INTO J_SCOREsno,cno,score)VALUES6,2,66);
INSERT INTO J_SCOREsno,cno,score)VALUES6,3,58);
INSERT INTO J_SCOREsno,cno,score)VALUES6,4,79);

INSERT INTO J_SCOREsno,cno,score)VALUES7,1,80);
INSERT INTO J_SCOREsno,cno,score)VALUES7,2,76);
INSERT INTO J_SCOREsno,cno,score)VALUES7,3,68);
INSERT INTO J_SCOREsno,cno,score)VALUES7,4,59);
INSERT INTO J_SCOREsno,cno,score)VALUES7,5,89);

创建后结果（作者用的mysql）

J_TEACHER
MySQL练习题 （练习表+题目+答案）「建议收藏」MySQL练习题 （练习表+题目+答案）「建议收藏」

J_STUDENT
在这里插入图片描述在这里插入图片描述
J_COURSE
在这里插入图片描述在这里插入图片描述
J_SCORE
在这里插入图片描述在这里插入图片描述

二、题目和过程答案

作者用的mysql 不同数据库有些语法可能会不同，请注意。

1、查询课程1的成绩比课程2的成绩 高 的所有学生的学号。

select a.sno
from j_score as a JOIN j_score as b 
on a.sno=b.sno
where a.cno=1 and b.cno=2 and a.score>b.score]

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

SELECT a.sno,AVGa.score)as '平均成绩'
from j_score as a
group by a.sno
having avga.score)>60

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

select a.sno,a.sname,countb.cno)as'选课数',sumb.score)as'总成绩'
from j_student as a join j_score as b 
on a.sno=b.sno
group by a.sno

4、查询姓“李”的学生的个数。

select COUNTa.sname) as '个数'
from j_student as a
where a.sname like '李%'

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

SELECT a.sno,a.sname
from j_student as a
where a.sno not in 	
	select s.sno
	from j_score as s,j_course as c,j_teacher as t
	where s.cno=c.cno and c.tno=t.tno and t.tname='叶平')

6、查询同时学过课程1和课程2的同学的学号、姓名。

SELECT a.sno,a.sname
from j_student as a
where a.sno in 
	select b.sno
	from j_score as b JOIN j_score as c
	on b.sno=c.sno
	where b.cno=1 and c.cno=2)

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

select a.sno,a.sname
from j_student as a
where a.sno in 
	SELECT b.sno 
	from j_score as b 
  where b.cno in 
		SELECT c.cno
		from j_course as c
		where c.tno in 
			select d.tno
			from j_teacher as d
			where d.tname='叶平')))

8、查询 课程编号1的成绩 比 课程编号2的成绩 高的所有同学的学号、姓名。

select a.sno,a.sname
from j_student as a
where a.sno in 
	select b.sno
	from j_score as b join j_score as c
	on b.sno=c.sno
	where b.cno=1 and c.cno=2 and b.score>c.score)

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

select a.sno,a.sname
from j_student as a
where a.sno in 
	select b.sno
	from j_score as b
	group by b.sno
	having maxb.score)<60)

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

select a.sno,a.sname
from j_student as a
where a.sno in 
	select b.sno
	from j_score as b
	group by b.sno
	having minb.score)>60)

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

select a.sno,a.sname
from j_student as a,
		select b.sno,b.cno,countb.cno)as 'yixuan' from j_score as b group by b.sno)c,
		SELECT d.cno,countd.cno)as 'total' from j_course as d)e
where a.sno=c.sno and c.cno=e.cno and c.yixuan<e.total

12、查询至少有一门课程 与 学号为1的同学所学课程 相同的同学的学号和姓名

select a.sno ,a.sname
from j_student a ,j_score b
where a.sno=b.sno and a.sno<>1 and b.cno in 
	select c.cno from j_score c where c.sno =1)
group by a.sno```

13、查询和2号同学学习的课程完全相同的其他同学学号和姓名。

select a.sno ,a.sname
from j_student a ,j_score b
where a.sno=b.sno and a.sno<>2 and b.sno not in 
	select c.sno from j_score c where c.cno not in 
		select d.cno from j_score d where d.sno=2)) 
group by a.sno
having counta.sno)=select countd.cno) from j_score d where d.sno=2)

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

select a.cno,maxa.score) as '最高分', mina.score) as '最低分'
from j_score a
group by a.cno

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

select a.cname,countb.sno)
from j_course a,j_score b
where b.score is not NULL and a.cno=b.cno
group by a.cno

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

select a.sno,a.sname
from j_student a ,j_score b
where a.sno=b.sno and b.score is not null 
group by a.sno
having countb.cno)=1

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

select t.sname ,t.ssex ,countt.sname) 
from  select a.sno,a.sname,a.sage,a.ssex
		from j_student a join j_student b
		on a.sno=b.sno
		where a.sname=b.sname and a.ssex=b.ssex
		group by a.sno)t
group by t.sname ,t.ssex 
having countt.sname) > 1

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

select a.sname,maxb.score)
from j_student a,j_score b
where a.sno=b.sno and b.cno = 
	select c.cno
	from j_course c
	where c.tno = 
		select d.tno
		from j_teacher d
		where d.tname='叶平'))

19、查询不同课程成绩相同的学生的学号、课程号、学生成绩。

select a.sno,a.cno,a.score
from j_score a join j_score b
where a.score=b.score and a.cno<>b.cno

20、查询每门课程成绩最好的前两名的学生ID

select a.sno,a.cno,a.score
from j_score a 
where 
	select count*)
	from j_score b
	where a.cno=b.cno and a.score<=b.score)<=2

21、检索至少选修了5门课程的学生学号。

select a.sno
from j_score a
GROUP BY a.sno
having count*)>=5

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

select a.sname
from j_student a
where a.sno  not in 
	select b.sno
	from j_score b
	where b.cno not in 
		select c.cno
		from j_course c
		where c.tno not in 
			select d.tno
			from j_teacher d
			where d.tname='叶平')))

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

select a.sno,roundavga.score),3) as'平均成绩'
from j_score a 
where a.score<60 
group by sno 
having count*)>2

24、查询最受欢迎的老师选修学生最多的老师)。

select a.cno,c.tname,counta.cno) as '选修人数'
from j_course a,j_score b,j_teacher c
where a.cno=b.cno and a.tno=c.tno 
group by a.cno
having counta.cno) = 

	select d.max from  
		select countcno) max
		from j_score 
		group by cno
		order by countcno) desc 
		limit 0,1)d
)
order by counta.cno) desc

Published by

风君子

独自遨游何稽首 揭天掀地慰生平

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注