大家好，又见面了，我是你们的朋友全栈君。
mysql练习题 （表+题目+答案）
一、创建所需要练习的表CREATE TABLE J_TEACHER (
tno int NOT NULL PRIMARY KEY,
tname varchar(20) NOT NULL
);
INSERT INTO J_TEACHER(tno,tname)VALUES(1,‘张老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(2,‘王老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(3,‘李老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(4,‘赵老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(5,‘刘老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(6,‘向老师’);
INSERT INTO J_TEACHER(tno,tname)VALUES(7,‘李文静’);
INSERT INTO J_TEACHER(tno,tname)VALUES(8,‘叶平’);
CREATE TABLE J_STUDENT(
sno int NOT NULL PRIMARY KEY,
sname varchar(20) NOT NULL,
sage datetime NOT NULL,
ssex char(2) NOT NULL
);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(1,‘张三’,‘1980-1-23’,‘男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(2,‘李四’,‘1982-12-12’,‘男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(3,‘张飒’,‘1981-9-9’,‘男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(4,‘莉莉’,‘1983-3-23’,‘女’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(5,‘王弼’,‘1982-6-21’,‘男’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(6,‘王丽’,‘1984-10-10’,‘女’);
INSERT INTO J_STUDENT(sno,sname,sage,ssex) VALUES(7,‘刘香’,‘1980-12-22’,‘女’);
CREATE TABLE J_COURSE(
cno int NOT NULL PRIMARY KEY,
cname varchar(20) NOT NULL,
tno int NOT NULL
);
insert into J_COURSE(cno,cname,tno) values(1,‘企业管理’,3);
insert into J_COURSE(cno,cname,tno) values(2,‘马克思’,1);
insert into J_COURSE(cno,cname,tno) values(3,‘UML’,2);
insert into J_COURSE(cno,cname,tno) values(4,‘数据库’,5);
insert into J_COURSE(cno,cname,tno) values(5,‘物理’,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 KEY(cno)
REFERENCES J_COURSE (cno);
ALTER TABLE J_SCORE ADD CONSTRAINT FK_score_student FOREIGN KEY(sno)
REFERENCES J_STUDENT (sno);
INSERT INTO J_SCORE(sno,cno,score)VALUES(1,1,80);
INSERT INTO J_SCORE(sno,cno,score)VALUES(1,2,86);
INSERT INTO J_SCORE(sno,cno,score)VALUES(1,3,83);
INSERT INTO J_SCORE(sno,cno,score)VALUES(1,4,89);
INSERT INTO J_SCORE(sno,cno,score)VALUES(2,1,50);
INSERT INTO J_SCORE(sno,cno,score)VALUES(2,2,36);
INSERT INTO J_SCORE(sno,cno,score)VALUES(2,3,43);
INSERT INTO J_SCORE(sno,cno,score)VALUES(2,4,59);
INSERT INTO J_SCORE(sno,cno,score)VALUES(3,1,50);
INSERT INTO J_SCORE(sno,cno,score)VALUES(3,2,96);
INSERT INTO J_SCORE(sno,cno,score)VALUES(3,3,73);
INSERT INTO J_SCORE(sno,cno,score)VALUES(3,4,69);
INSERT INTO J_SCORE(sno,cno,score)VALUES(4,1,90);
INSERT INTO J_SCORE(sno,cno,score)VALUES(4,2,36);
INSERT INTO J_SCORE(sno,cno,score)VALUES(4,3,88);
INSERT INTO J_SCORE(sno,cno,score)VALUES(4,4,99);
INSERT INTO J_SCORE(sno,cno,score)VALUES(5,1,90);
INSERT INTO J_SCORE(sno,cno,score)VALUES(5,2,96);
INSERT INTO J_SCORE(sno,cno,score)VALUES(5,3,98);
INSERT INTO J_SCORE(sno,cno,score)VALUES(5,4,99);
INSERT INTO J_SCORE(sno,cno,score)VALUES(6,1,70);
INSERT INTO J_SCORE(sno,cno,score)VALUES(6,2,66);
INSERT INTO J_SCORE(sno,cno,score)VALUES(6,3,58);
INSERT INTO J_SCORE(sno,cno,score)VALUES(6,4,79);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,1,80);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,2,76);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,3,68);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,4,59);
INSERT INTO J_SCORE(sno,cno,score)VALUES(7,5,89);
创建后结果（作者用的mysql）J_TEACHER
J_STUDENT
J_COURSE
J_SCORE
二、题目和过程答案作者用的mysql 不同数据库有些语法可能会不同，请注意。
1、查询课程1的成绩比课程2的成绩 高 的所有学生的学号。
代码语言：javascript复制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分的同学的学号和平均成绩。
代码语言：javascript复制SELECT a.sno,AVG(a.score)as '平均成绩'
from j_score as a
group by a.sno
having avg(a.score)>603、查询所有同学的学号、姓名、选课数、总成绩。
代码语言：javascript复制select a.sno,a.sname,count(b.cno)as'选课数',sum(b.score)as'总成绩'
from j_student as a join j_score as b
on a.sno=b.sno
group by a.sno 4、查询姓“李”的学生的个数。
代码语言：javascript复制select COUNT(a.sname) as '个数'
from j_student as a
where a.sname like '李%'5、查询没学过“叶平”老师课的同学的学号、姓名。
代码语言：javascript复制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的同学的学号、姓名。
代码语言：javascript复制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、查询学过“叶平”老师所教所有课程的所有同学的学号、姓名。
代码语言：javascript复制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的成绩 高的所有同学的学号、姓名。
代码语言：javascript复制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分的同学的学号、姓名。
代码语言：javascript复制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 max(b.score)<60)10、查询所有课程成绩大于60分的同学的学号、姓名。
代码语言：javascript复制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 min(b.score)>60)11、查询没有学全所有课的同学的学号、姓名
代码语言：javascript复制select a.sno,a.sname
from j_student as a,
(select b.sno,b.cno,count(b.cno)as 'yixuan' from j_score as b group by b.sno)c,
(SELECT d.cno,count(d.cno)as 'total' from j_course as d)e
where a.sno=c.sno and c.cno=e.cno and c.yixuan 代码语言：javascript复制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号同学学习的课程完全相同的其他同学学号和姓名。 代码语言：javascript复制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 count(a.sno)=(select count(d.cno) from j_score d where d.sno=2)14、查询各科成绩最高分和最低分。 以如下形式显示：课程号，最高分，最低分 代码语言：javascript复制select a.cno,max(a.score) as '最高分', min(a.score) as '最低分' from j_score a group by a.cno15、查询每门课程被选修的学生数。 代码语言：javascript复制select a.cname,count(b.sno) from j_course a,j_score b where b.score is not NULL and a.cno=b.cno group by a.cno16、查询出只选修了一门课程的全部学生的学号和姓名。 代码语言：javascript复制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 count(b.cno)=117、查询同名同性学生名单，并统计同名人数。 代码语言：javascript复制select t.sname ,t.ssex ,count(t.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 count(t.sname) > 1 18、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩。 代码语言：javascript复制select a.sname,max(b.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、查询不同课程成绩相同的学生的学号、课程号、学生成绩。 代码语言：javascript复制select a.sno,a.cno,a.score from j_score a join j_score b where a.score=b.score and a.cno<>b.cno20、查询每门课程成绩最好的前两名的学生ID 代码语言：javascript复制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)<=221、检索至少选修了5门课程的学生学号。 代码语言：javascript复制select a.sno from j_score a GROUP BY a.sno having count(*)>=522、查询没学过“叶平”老师讲授的任一门课程的学生姓名。 代码语言：javascript复制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、查询两门以上不及格课程的同学的学号及其平均成绩。 代码语言：javascript复制select a.sno,round(avg(a.score),3) as'平均成绩' from j_score a where a.score<60 group by sno having count(*)>224、查询最受欢迎的老师(选修学生最多的老师)。 代码语言：javascript复制select a.cno,c.tname,count(a.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 count(a.cno) = ( select d.max from ( select count(cno) max from j_score group by cno order by count(cno) desc limit 0,1)d ) order by count(a.cno) desc版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。 发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/192150.html原文链接：https://javaforall.cn