Mysql经典45题练习

忆丶长安

于 2022-07-28 09:52:07 发布

阅读量101

点赞数 1

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/vn666/article/details/126028237

版权

#向教师信息teacher表添加数据
INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart)
VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart)
VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart)
VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher(tno,tname,tsex,tbirthday,prof,depart)
VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

#向课程course表添加数据
INSERT INTO course(cno,cname,tno)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO course(cno,cname,tno)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO course(cno,cname,tno)VALUES ('6-166' ,'数字电路' ,856);
INSERT INTO course(cno,cname,tno)VALUES ('9-888' ,'高等数学' ,831);

#向成绩score表添加数据
INSERT INTO score(sno,cno,degree)VALUES (103,'3-245',86);
INSERT INTO score(sno,cno,degree)VALUES (105,'3-245',75);
INSERT INTO score(sno,cno,degree)VALUES (109,'3-245',68);
INSERT INTO score(sno,cno,degree)VALUES (103,'3-105',92);
INSERT INTO score(sno,cno,degree)VALUES (105,'3-105',88);
INSERT INTO score(sno,cno,degree)VALUES (109,'3-105',76);
INSERT INTO score(sno,cno,degree)VALUES (101,'3-105',64);
INSERT INTO score(sno,cno,degree)VALUES (107,'3-105',91);
INSERT INTO score(sno,cno,degree)VALUES (108,'3-105',78);
INSERT INTO score(sno,cno,degree)VALUES (101,'6-166',85);
INSERT INTO score(sno,cno,degree)VALUES (107,'6-166',79);
INSERT INTO score(sno,cno,degree)VALUES (108,'6-166',81);
#------------------------------------------------------------------------------------------------

#1、查询student表中的所有记录的sname、ssex和class列。
SELECT sname,ssex,class FROM student

#2、查询教师所有的单位即不重复的depart列。
SELECT DISTINCT depart FROM teacher

#3、查询student表的所有记录。
SELECT * FROM student

#4、查询score表中成绩在60到80之间的所有记录。
SELECT *FROM score WHERE degree BETWEEN 60 AND 80

#5、查询score表中成绩为85，86或88的记录。
SELECT * FROM score WHERE degree in(85,86,88)

#6、查询student表中“95031”班或性别为“女”的同学记录。
SELECT * FROM student WHERE class=95031 or ssex='女'

#7、以class降序查询student表的所有记录。
SELECT * FROM student ORDER BY class DESC

#8、以cno升序、degree降序查询score表的所有记录。
SELECT * FROM score ORDER BY cno,degree DESC

#9、查询“95031”班的学生人数。
SELECT COUNT(class) '95031班学生人数' FROM student WHERE class=95031

#10、查询score表中的最高分的学生学号和课程号。（子查询或者排序）
SELECT sno,cno FROM score WHERE degree=(SELECT MAX(degree) FROM score )

#11、查询每门课的平均成绩。
SELECT cno,AVG(degree) FROM score GROUP BY cno

#12、查询score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT cno,AVG(degree) FROM score WHERE cno like '3%' GROUP BY cno HAVING COUNT(1)>5

#13、查询分数大于70，小于90的sno列。
SELECT sno FROM score WHERE degree BETWEEN 70 AND 90

#14、查询所有学生的sname、cno和degree列。
SELECT e1.sname,e2.cno,e2.degree
FROM student e1,score e2

#15、查询所有学生的sno、cname和degree列。
SELECT e3.sno,e2.cname,e3.degree
FROM course e2,score e3

#16、查询所有学生的sname、cname和degree列
SELECT e1.sname,e2.cname,e3.degree
FROM student e1,course e2,score e3

#17、查询“95033”班学生的平均分。
SELECT AVG(degree) '9503班学生的平均分' FROM score WHERE sno in (SELECT sno FROM student WHERE class=95033)

#18、假设使用如下命令建立了一个grade表：
CREATE TABLE IF NOT EXISTS `grade` (
`low` INT (3) COMMENT '底限',
`upp` INT (3) COMMENT '上限',
`rank` CHAR(1) COMMENT '等级'
) ;

#向grade表中添加数据
INSERT INTO grade VALUES(90,100,'A');
INSERT INTO grade VALUES(80,89,'B');
INSERT INTO grade VALUES(70,79,'C');
INSERT INTO grade VALUES(60,69,'D');
INSERT INTO grade VALUES(0,59,'E');

#现查询所有同学的sno、cno和rank列。
SELECT e1.sno,e1.cno,e2.rank
FROM score e1,grade e2
WHERE e1.degree BETWEEN low AND upp

#19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT * FROM score WHERE cno='3-105' AND degree>(SELECT degree FROM score WHERE sno=109 AND cno='3-105')

#20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
SELECT * FROM score WHERE degree NOT in (SELECT MAX(degree) FROM score GROUP BY sno HAVING COUNT(sno)>1)

#21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT * FROM score WHERE cno='3-105' AND degree>(SELECT degree FROM score WHERE sno=109 AND cno='3-105')

#22、查询和学号为107的同学同年出生的所有学生的sno、sname和sbirthday列。
SELECT sno,sname,sbrithday
FROM student
where year(sbrithday) in
(select year(sbrithday) from student where sno=108 or sno=101)
having sno != 108 and sno != 101;

#23、查询“张旭“教师任课的学生成绩。
SELECT degree FROM teacher t,course c,score s WHERE t.tname='张旭' AND t.tno=c.tno AND c.cno=s.cno

#24、查询选修某课程的同学人数多于5人的教师姓名。
SELECT DISTINCT tname FROM teacher t,course c,score s WHERE t.tno=c.tno AND c.cno = (SELECT cno FROM score GROUP BY cno HAVING COUNT(cno)>5 )

#25、查询95033班和95031班全体学生的记录。
SELECT * FROM student NATURAL JOIN score

#26、查询存在有85分以上成绩的课程cno.
SELECT cno FROM course WHERE cno in (SELECT cno FROM score WHERE degree>85)

#27、查询出“计算机系“教师所教课程的成绩表。
SELECT *FROM course WHERE tno in (SELECT tno FROM teacher WHERE depart='计算机系')

#28、查询“计算机系”与“电子工程系“不同职称的教师的tname和prof。
SELECT tname,prof FROM teacher WHERE prof =(SELECT prof FROM teacher WHERE depart='计算机系' or depart='电子工程系' GROUP BY prof HAVING COUNT(1)>1);

#29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的cno、sno和degree,并按degree从高到低次序排序。
SELECT * FROM score WHERE cno='3-105' AND degree>(SELECT min(degree) FROM score WHERE cno='3-245')

#30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的cno、sno和degree.
SELECT * FROM score WHERE cno='3-105' AND degree>(SELECT MAX(degree) FROM score WHERE cno='3-245')

#31、查询所有教师和同学的name、sex和birthday.
SELECT tname name,tsex sex,tbrithday brithday FROM teacher
UNION
SELECT sname name,ssex sex,sbrithday brithday FROM student

#32、查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT tname name,tsex sex,tbrithday brithday FROM teacher WHERE tsex='女'
UNION
SELECT sname name,ssex sex,sbrithday brithday FROM student WHERE ssex='女'

#33、查询成绩比该课程平均成绩低的同学的成绩表。
SELECT * FROM score a WHERE degree <(SELECT AVG(degree) FROM score b WHERE a.cno=b.cno)

#34、查询所有任课教师的tname和depart.
SELECT tname,depart FROM teacher,course WHERE course.cno in (SELECT cno FROM score) AND course.tno=teacher.tno

#35 、查询所有未讲课的教师的tname和depart.
SELECT tname,depart FROM teacher,course WHERE course.cno not in (SELECT cno FROM score) AND course.tno=teacher.tno

#36、查询至少有2名男生的班号。
SELECT class FROM student WHERE ssex='男' GROUP BY class HAVING COUNT(1)>1

#37、查询student表中不姓“王”的同学记录。
SELECT * FROM student WHERE sname not LIKE '王%'

#38、查询student表中每个学生的姓名和年龄。
SELECT sname,YEAR(now())-YEAR(sbrithday) age FROM student

#39、查询student表中最大和最小的sbirthday日期值。
SELECT MAX(sbrithday),min(sbrithday) FROM student

#40、以班号和年龄从大到小的顺序查询student表中的全部记录。
SELECT * FROM student ORDER BY class DESC,sbrithday DESC

#41、查询“男”教师及其所上的课程。
SELECT tname,cname FROM teacher,course WHERE teacher.tsex='男' AND teacher.tno=course.tno

#42、查询最高分同学的sno、cno和degree列。
SELECT * FROM score WHERE degree=(SELECT MAX(degree)FROM score)

#43、查询和“李军”同性别的所有同学的sname.
SELECT sname FROM student WHERE ssex=(SELECT ssex FROM student WHERE sname='李军')

#44、查询和“李军”同性别并同班的同学sname.
SELECT sname FROM student WHERE ssex=(SELECT ssex FROM student WHERE sname='李军') and class=(SELECT class FROM student WHERE sname='李军')

#45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
#SELECT cno FROM course WHERE cname='计算机导论'
SELECT degree
FROM score,student
WHERE cno=(SELECT cno FROM course WHERE cname='计算机导论') AND student.ssex='男' AND student.sno=score.sno