Mysql练习题
创建数据表:
CREATE TABLE student1(
sno VARCHAR(20) PRIMARY KEY,
sname VARCHAR(20) NOT NULL,
ssex VARCHAR(20) NOT NULL ,
sbirthday DATETIME,
class VARCHAR(20)
)
CREATE TABLE course(
cno VARCHAR(20) NOT NULL,
cname VARCHAR(20) NOT NULL,
tno VARCHAR(20) NOT NULL
)
CREATE TABLE score1(
sno VARCHAR(20) NOT NULL,
cno VARCHAR(20) NOT NULL,
degree DECIMAL(4,1)
)
CREATE TABLE teacher(
tno VARCHAR(20) PRIMARY KEY,
tname VARCHAR(20) NOT NULL,
tsex VARCHAR(20) NOT NULL,
tbirthday DATETIME,
prof VARCHAR(20),
depart VARCHAR(20) NOT NULL
)
INSERT INTO student1 VALUES('108','曾华','男','1977-09-01','95033')
INSERT INTO student1 VALUES('105','匡明','男','1975-10-02','95031')
INSERT INTO student1 VALUES('107','王丽','女','1976-01-23','95033')
INSERT INTO student1 VALUES('101','李军','男','1976-02-20','95033')
INSERT INTO student1 VALUES('109','王芳','女','1975-02-10','95031')
INSERT INTO student1 VALUES('103','陆君','男','1974-06-03','95031')
SELECT * FROM student1
INSERT INTO course VALUES('3-105','计算机导论','825')
INSERT INTO course VALUES('3-245','操作系统','804')
INSERT INTO course VALUES('6-166','数字电路','856')
INSERT INTO course VALUES('9-888','高等数学','831')
SELECT * FROM course
INSERT INTO score1 VALUES('103','3-245','86')
INSERT INTO score1 VALUES('105','3-245','75')
INSERT INTO score1 VALUES('109','3-245','68')
INSERT INTO score1 VALUES('103','3-105','92')
INSERT INTO score1 VALUES('105','3-105','88')
INSERT INTO score1 VALUES('109','3-105','76')
INSERT INTO score1 VALUES('101','3-105','64')
INSERT INTO score1 VALUES('107','3-105','91')
INSERT INTO score1 VALUES('108','3-105','78')
INSERT INTO score1 VALUES('101','6-166','85')
INSERT INTO score1 VALUES('107','6-166','79')
INSERT INTO score1 VALUES('108','6-166','81')
DELETE FROM score1 WHERE sno='109'
SELECT * FROM score1
INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系')
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系')
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系')
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系')
SELECT * FROM teacher
-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT sname,ssex,class FROM student1
-- 2、 查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT(depart) FROM teacher;
-- 3、 查询Student表的所有记录。
SELECT * FROM student1
-- 4、 查询Score表中成绩在60到80之间的所有记录。
SELECT * FROM score1 WHERE degree>=60 AND degree<=80
-- 5、 查询Score表中成绩为85，86或88的记录。
SELECT * FROM score1 WHERE degree=85 OR degree=86 OR degree=88
-- 6、 查询Student表中“95031”班或性别为“女”的同学记录。
SELECT * FROM student1 WHERE class='95031'OR ssex='女'
-- 7、以Class降序查询Student表的所有记录。
SELECT * FROM student1 ORDER BY class
-- 8、以Cno升序、Degree降序查询Score表的所有记录。
SELECT * FROM score1 ORDER BY cno ,degree DESC
-- 9、查询“95031”班的学生人数。
SELECT COUNT(*) FROM student1 WHERE class='95031';
-- 10、查询每门课的平均成绩。
SELECT cno,AVG(degree) FROM score1 GROUP BY cno;
-- 11、查询Score表中至少有5名学生选修 并以3开头的课程的平均分数。
SELECT cno,AVG(degree) FROM score1 WHERE Cno LIKE '3%' GROUP BY Cno HAVING COUNT(cno)>5
-- 12、查询分数大于70，小于90的Sno列。
SELECT sno FROM score1 WHERE degree BETWEEN 70 AND 90
-- 13、查询所有学生的Sname、Cno和Degree列。
SELECT sname,cno,degree FROM student1 JOIN score1 ON student1.`sno`=score1.`sno`
-- 14、查询所有学生的Sno、Cname和Degree列。
SELECT sno,cname,degree FROM Score1 JOIN Course ON score1.`cno`=course.`cno`
-- 15、查询所有学生的Sname、Cname和Degree列。
SELECT sname,cname,degree FROM Score1 JOIN Course ON score1.`cno`=course.`cno` JOIN student1 ON student1.`sno`=score1.`sno`
-- 16、查询“95033”班学生的平均分。
SELECT AVG(degree) FROM student1 JOIN score1 ON student1.`sno`= score1.`sno` WHERE class = '95033';
-- 17、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT Sno,Sname,Sbirthday FROM student1 WHERE YEAR(student1.`sbirthday`)IN(SELECT YEAR(sbirthday) FROM student1 WHERE sno='108')
-- 18、查询“张旭“教师任课的学生成绩（姓名）。
SELECT sname,degree FROM student1 JOIN score1 ON student1.`sno`=score1.`sno` JOIN course ON course.`cno`=score1.`cno` JOIN teacher ON teacher.`tno`=course.`tno` WHERE tname='张旭'
-- 19、查询考计算机导论的学生成绩
SELECT degree FROM score1 JOIN course ON score1.`cno`=course.`cno` WHERE cname = '计算机导论'
-- 20、查询李诚老师教的课程名称
SELECT cname FROM teacher JOIN course ON teacher.`tno`=course.`tno` WHERE tname ='李诚'
-- 21、教高等数学的老师是哪个系的
SELECT depart FROM teacher JOIN course ON teacher.`tno`=course.`tno` WHERE cname='高等数学'
-- 22、查询选修某课程的同学人数多于5人的教师姓名。
SELECT tname FROM student1 JOIN score1 ON student1.`sno`=score1.`sno` JOIN course ON course.`cno`=score1.`cno` JOIN teacher ON teacher.`tno`=course.`tno` GROUP BY tname HAVING COUNT(*)>5;
-- 23、查询95033班和95031班全体学生的记录。
SELECT * FROM student1 JOIN score1 ON student1.`sno`=score1.`sno` JOIN course ON course.`cno`=score1.`cno` JOIN teacher ON teacher.`tno`=course.`tno` WHERE class='95033'OR class='95031'
-- 24、查询存在有85分以上成绩的课程Cno.
SELECT DISTINCT cno FROM score1 WHERE Degree>85
-- 25、查询出“计算机系“教师所教课程的成绩表。
SELECT *FROM teacher JOIN course ON teacher.`tno`=course.`tno` JOIN score1 ON course.`cno`=score1.`cno` WHERE depart ='计算机系'
-- 26、 查询所有教师和同学的name、sex和birthday
SELECT DISTINCT sname ,ssex ,sbirthday FROM student1 UNION SELECT DISTINCT tname,tsex,tbirthday FROM teacher
-- 27、查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT DISTINCT sname ,ssex ,sbirthday FROM student1 WHERE ssex='女' UNION SELECT DISTINCT tname,tsex,tbirthday FROM teacher WHERE tsex='女'
-- 28、 查询所有任课教师的Tname和Depart.
SELECT tname,depart FROM teacher
-- 29、查询所有未讲课的教师的Tname和Depart.
SELECT tname,depart FROM teacher WHERE tno NOT IN(SELECT tno FROM course WHERE cno IN(SELECT cno FROM score1))
-- 30、查询至少有2名男生的班号。
SELECT class FROM student1 WHERE ssex ='男' GROUP BY class HAVING COUNT(*)>=2
-- 31、查询Student表中不姓“王”的同学记录。
SELECT * FROM student1 WHERE sname NOT LIKE'王%'
-- 32、查询Student表中每个学生的姓名和年龄。
SELECT sname,age(2020-sbirthday) FROM student1
-- 33、查询Student表中最大和最小的Sbirthday日期值。
SELECT MAX(Sbirthday) ,MIN(Sbirthday) FROM student1
-- 34、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT * FROM student1 ORDER BY class DESC,sbirthday DESC;
-- 35、查询“男”教师及其所上的课程。
SELECT cname FROM teacher JOIN course ON teacher.`tno`=course.`tno` WHERE tsex = '男'
-- 36、查询最高分同学的Sno、Cno和Degree列。
SELECT sno,cno,degree FROM score1 WHERE degree=(SELECT MAX(degree)FROM score1)
-- 37、查询和“李军”同性别的所有同学的Sname.
SELECT sname FROM student1 WHERE ssex=(SELECT ssex FROM student1 WHERE sname='李军')
-- 38、查询和“李军”同性别并同班的同学Sname.
SELECT Sname FROM Student1 WHERE Ssex = (SELECT Ssex FROM Student1 WHERE Sname='李军') AND Class=(SELECT Class FROM Student1 WHERE Sname = '李军')
-- 39、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT degree FROM student1 JOIN score1 ON student1.`sno`=score1.`sno` JOIN course ON course.`cno`=score1.`cno` JOIN teacher ON teacher.`tno`=course.`tno` WHERE cname ='计算机导论' AND ssex ='男'
当然方法还有很多,欢迎各位大神指点和评论
