您现在的位置是：首页 > 数据库

当前栏目

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

mysql 建议收藏答案练习题目练习题

2023-06-13 09:13:40 时间

大家好，又见面了，我是你们的朋友全栈君。

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的成绩高的所有学生的学号。

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,AVG(a.score)as '平均成绩'
from j_score as a
group by a.sno
having avg(a.score)>60

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

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、查询姓“李”的学生的个数。

select COUNT(a.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 max(b.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 min(b.score)>60)

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

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<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 count(a.sno)=(select count(d.cno) from j_score d where d.sno=2)

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

select a.cno,max(a.score) as '最高分', min(a.score) as '最低分'
from j_score a
group by a.cno

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

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.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 count(b.cno)=1

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

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、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩。

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、查询不同课程成绩相同的学生的学号、课程号、学生成绩。

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,round(avg(a.score),3) as'平均成绩'
from j_score a 
where a.score<60 
group by sno 
having count(*)>2

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

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

猜你喜欢

我要输入中文使用Linux系统拼音输入中文（linuxpinyin）
jQuery基本选择器（4个）
bt3使用_手机怎么下载视频到u盘上
Linux whereis命令用法详解
Java面向对象的基本特征有哪些？三大基本特征「建议收藏」
区块链洪流之下，各国监管机构立场不一，你站谁？丨G20峰会
从MSSQL数据库里植入绿色未来：绿化方法研询（绿化mssql方法）
构建MSSQL数据库连接：提升存储空间可用性（连接mssql数据库空间）
如何正确卸载并关闭Redis服务（卸载redis关闭）
MySQL测试脚本之旅（mysql测试脚本）
哪些属于网页抓取算法_网页排序算法有哪些
Oracle中列转行函数实现你想要的一切（列转行函数oracle）
MySQL Error number: 3644; Symbol: ER_CANNOT_ALTER_SRID_DUE_TO_INDEX; SQLSTATE: HY000 报错故障修复远程处理
Redis集群中的锁定保护数据安全（redis集群的锁）
Oracle删除实例的步骤与命令（oracle删除实例命令）

zl程序教程

当前栏目

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

一、创建所需要练习的表

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

二、题目和过程答案

相关文章

当前栏目

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

一、创建所需要练习的表

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

二、题目和过程答案

相关文章

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