Mysql初学者sql语句练习题（45道）带模拟数据和答案+对数据库中的All和Any的区别

亦凉 2022-05-22 09:43 300阅读 0赞

\--这些表有外键约束,必须先建立没有外键的表,先建一四或者先不管外键，建好表后添加外键

1.  CREATE TABLE STUDENT
2.  (SNO VARCHAR(3) NOT NULL,
3.  SNAME VARCHAR(4) NOT NULL,
4.  SSEX VARCHAR(2) NOT NULL,
5.  SBIRTHDAY DATE,
6.  SCLASS VARCHAR(5));
7.   
8.  CREATE TABLE TEACHER
9.  (TNO VARCHAR(3) NOT NULL,
10. TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,
11. TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),
12. DEPART VARCHAR(10) NOT NULL);
13.  
14. CREATE TABLE COURSE
15. (CNO VARCHAR(5) NOT NULL,
16. CNAME VARCHAR(10) NOT NULL,
17. TNO VARCHAR(10) NOT NULL);
18.  
19. CREATE TABLE SCORE
20. (SNO VARCHAR(3) NOT NULL,
21. CNO VARCHAR(5) NOT NULL,
22. DEGREE NUMERIC(10, 0) NOT NULL);
23.  
24. ALTER TABLE student
25. ADD CONSTRAINT primary key (sno);
26. ALTER TABLE course
27. ADD constraint primary key (cno);
28. ALTER TABLE score
29. ADD constraint primary key (sno, cno);
30. ALTER TABLE teacher
31. ADD constraint primary key (tno);
32. ALTER TABLE course
33. ADD constraint foreign key (tno) references teacher (tno);
34. ALTER TABLE score
35. ADD constraint foreign key (cno) references course (cno);
36. INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,SCLASS) VALUES (108 ,'曾华'
37. ,'男' ,'1977-09-01',95033);
38. INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,SCLASS) VALUES (105 ,'匡明'
39. ,'男' ,'1975-10-02',95031);
40. INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,SCLASS) VALUES (107 ,'王丽'
41. ,'女' ,'1976-01-23',95033);
42. INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,SCLASS) VALUES (101 ,'李军'
43. ,'男' ,'1976-02-20',95033);
44. INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,SCLASS) VALUES (109 ,'王芳'
45. ,'女' ,'1975-02-10',95031);
46. INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,SCLASS) VALUES (103 ,'陆君'
47. ,'男' ,'1974-06-03',95031);
48. INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
49. VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
50. INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
51. VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
52. INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
53. VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
54. INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
55. VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');
56. INSERT INTO COURSE(CNO,CNAME,TNO) VALUES ('3-105','计算机导论',825);
57. INSERT INTO COURSE(CNO,CNAME,TNO) VALUES ('3-245','操作系统',804);
58. INSERT INTO COURSE(CNO,CNAME,TNO) VALUES ('6-166','数据电路',856);
59. INSERT INTO COURSE(CNO,CNAME,TNO) VALUES ('9-888','高等数学',831);
60. INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (103,'3-245',86);
61. INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (105,'3-245',75);
62. INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (109,'3-245',68);
63. INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (103,'3-105',92);
64. INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (105,'3-105',88);
65. INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (109,'3-105',76);
66. INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (101,'3-105',64);
67. INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (107,'3-105',91);
68. INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (108,'3-105',78);
69. INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (101,'6-166',85);
70. INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (107,'6-166',79);
71. INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (108,'6-166',81);

\-- 1、 查询Student表中的所有记录的Sname、Ssex和Class列。  
\-- SELECT s.sname,s.ssex,s.class from student s;  
\-- 2、 查询教师所有的单位即不重复的Depart列。  
\-- SELECT DISTINCT t.depart from teacher t;  
\-- 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 s where s.DEGREE in (85,86,88);  
\-- 6、 查询Student表中“95031”班或性别为“女”的同学记录。  
\-- SELECT \* from student s where s.CLASS=95031 or s.SSEX='女';  
\-- 7、 以Class降序查询Student表的所有记录。  
\-- SELECT \* from student order by class desc;  
\-- 8、 以Cno升序、Degree降序查询Score表的所有记录。  
\-- select sno,cno,degree from score order by cno asc,degree desc;  
\-- select \* from score order by cno,degree desc;  
\-- 9、 查询“95031”班的学生人数。  
\-- SELECT COUNT(\*) from student where class=95031;  
\-- 10、查询Score表中的最高分的学生学号和课程号。  
\-- SELECT sno,cno from score where degree=(SELECT MAX(degree) from score);  
\-- SELECT sno,cno from score order by degree desc limit 1;  
  
  
\-- 11、查询‘3-105’号课程的平均分。  
\-- select AVG(degree) from score where cno='3-105';  
\-- 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。  
\-- SELECT AVG(degree),SNO from score where cno like '3%' GROUP BY cno HAVING count(cno) >= 5;  
\-- 13、查询最低分大于70，最高分小于90的Sno列。  
\-- SELECT sno,DEGREE from score GROUP BY sno HAVING min(degree)>70 and max(degree)<90;  
\-- SELECT \* from score where sno in(105,108);  
\-- 14、查询所有学生的Sname、Cno和Degree列。  
\-- SELECT s.sname,c.cno,c.degree from student s left join score c on s.SNO=c.SNO;  
\-- 15、查询所有学生的Sno、Cname和Degree列。  
\-- select s.SNO,c.CNAME,s.DEGREE from score s join (course c) USING(cno);  
\-- 16、查询所有学生的Sname、Cname和Degree列。  
\-- SELECT A.SNAME,B.CNAME,C.DEGREE FROM STUDENT A JOIN (COURSE B,SCORE C) ON A.SNO=C.SNO AND B.CNO =C.CNO;  
\-- SELECT s.SNAME,c.CNAME,sc.DEGREE from  score sc join (course c,student s) USING(cno,sno);  
\-- 17、查询“95033”班所选课程的平均分。  
\-- select AVG(s.degree) from score s left join student t on s.SNO=t.SNO where t.CLASS='95033' GROUP BY s.CNO;  
\-- SELECT avg(s.DEGREE),st.SNAME from score s join student st USING(sno) where st.class='95033' GROUP BY s.CNO;  
\-- 18、假设使用如下命令建立了一个grade表：  
\-- create table grade(low  NUMERIC(3,0),upp   NUMERIC(3),rank   char(1));  
\-- 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 s.sno,s.cno,g.rank from score s ,grade g where s.DEGREE BETWEEN g.low and g.upp;  
\-- SELECT s.sno,st.SNAME,s.CNO,c.CNAME,g.rank from score s join (grade g,student st,course c) USING(sno,CNO) where s.DEGREE BETWEEN g.low and g.upp ORDER BY g.rank;  
\-- SELECT A.SNO,A.CNO,B.RANK FROM SCORE A,GRADE B WHERE A.DEGREE BETWEEN B.LOW AND B.UPP ORDER BY RANK;  
  
  
  
  
\-- 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。  
\-- SELECT a.\* from score a join score b where a.cno ='3-105' and a.DEGREE>b.DEGREE and b.SNO='109' and b.CNO='3-105';  
\-- 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。  
\-- select \* from score where DEGREE <(SELECT MAX(DEGREE) FROM score) GROUP BY sno HAVING COUNT(SNO)>1 ORDER BY DEGREE;  
\-- 21、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。  
\-- SELECT SNO,SNAME,SBIRTHDAY from student where YEAR(SBIRTHDAY)=(SELECT YEAR(SBIRTHDAY) from student where sno=105)  
\-- 22、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。  
\-- SELECT \* from student s,score c where s.SNO=c.SNO and c.DEGREE >(SELECT DEGREE from score where cno='3-105' and sno='109');  
\-- 23、查询“张旭“教师任课的学生成绩。  
\-- select st.SNAME,s.DEGREE from score s,course c,teacher t,student st where t.TNAME='张旭' AND s.CNO=c.CNO and t.TNO=c.TNO and s.SNO=st.SNO;  
\-- 24、查询选修某课程的同学人数多于5人的教师姓名。  
\-- SELECT t.TNAME from teacher t join (course b,score c) on (t.TNO=b.TNO and b.CNO=c.CNO) GROUP BY c.CNO HAVING count(c.CNO)>5;  
\-- 25、查询95033班和95031班全体学生的记录。  
\-- select \* from student where CLASS='95033' or CLASS='95031';  
\-- 26、查询存在有85分以上成绩的课程Cno.  
\-- select DISTINCT CNO from score where DEGREE>='85';  
\-- 27、查询出“计算机系“教师所教课程的成绩表。  
\-- select t.TNAME,t.DEPART from teacher t where t.DEPART='计算机系';  
\-- SELECT degree from score where cno=(select cno from course where cname='计算机导论');  
\-- 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。  
\-- SELECT tname,prof from teacher a where PROF not in (SELECT PROF from teacher b where a.DEPART!=b.depart);  
\-- 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。  
\-- SELECT \* from score where CNO='3-105' and DEGREE> ANY (SELECT DEGREE from score where cno='3-245') ORDER BY DEGREE DESC;  
\-- 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.  
\-- SELECT \* from score where cno='3-105' and DEGREE >ALL (SELECT DEGREE from score where CNO='3-245');  
\-- 31、查询所有教师和同学的name、sex和birthday.

select DISTINCT t.TNAME as name,t.TSEX as sex,t.TBIRTHDAY as birthday from teacher t UNION SELECT DISTINCT s.SNAME as name,s.SSEX as sex,s.SBIRTHDAY as birthday from student s;

\-- 32、查询所有“女”教师和“女”同学的name、sex和birthday.  
SELECT DISTINCT t.TNAME as name, t.TSEX as sex,t.TBIRTHDAY as birthday from teacher t where t.TSEX='女'   
union   
SELECT DISTINCT s.SNAME as name,s.SSEX as sex,s.SBIRTHDAY as birthday from student s where  s.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 where Tname in (select distinct Tname from Teacher,Course,Score where Teacher.Tno=Course.Tno and Course.Cno=Score.Cno)  
\-- 35  查询所有未讲课的教师的Tname和Depart.   
SELECT tname,depart from teacher where TNAME not in (select DISTINCT TNAME from teacher t,course c,score s where t.tno=c.tno AND c.cno=s.cno );  
\-- 36、查询至少有2名男生的班号。  
\-- SELECT a.class from student a where a.SSEX='男' GROUP BY class HAVING COUNT(a.SSEX)>1;  
\-- 37、查询Student表中不姓“王”的同学记录。  
\-- SELECT \* from student where sname not like '王%';  
\-- 38、查询Student表中每个学生的姓名和年龄。  
\-- select sname,(YEAR(NOW())-YEAR(SBIRTHDAY)) as age from student;  
\-- 39、查询Student表中最大和最小的Sbirthday日期值。  
\-- SELECT SNAME,sbirthday from student where SBIRTHDAY=(SELECT min(SBIRTHDAY) FROM student)   
\-- UNION  
\-- SELECT sname,sbirthday from student where SBIRTHDAY=(SELECT MAX(SBIRTHDAY) from student);  
\-- 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。  
\-- SELECT class,(YEAR(NOW())-YEAR(SBIRTHDAY)) as age FROM student ORDER BY class,age DESC;  
\-- 41、查询“男”教师及其所上的课程。  
\-- SELECT t.TNAME,c.CNAME from teacher t JOIN course c USING(TNO) where t.TSEX='男';

\-- 42、查询最高分同学的Sno、Cno和Degree列。  
\-- SELECT a.\* FROM score a where a.DEGREE=(SELECT MAX(degree) from score);

![70][]  
\-- 43、查询和“李军”同性别的所有同学的Sname.  
\-- SELECT sname from student where SSEX=(SELECT SSEX from student where sname='李军');

![70 1][]  
\-- 44、查询和“李军”同性别并同班的同学Sname.  
\-- SELECT sname from student where SSEX=(SELECT SSEX from student where SNAME='李军') and   
\-- CLASS=(SELECT CLASS from student where SNAME='李军');

![70 2][]  
\-- 45、查询所有选修“计算机导论”课程的“男”同学的成绩表

SELECT a.\* from score a join (student b,course c) USING(sno,cno) where b.SSEX='男' AND c.CNAME='计算机导论';

![70 3][]

数据库any和All区别：

any表示至少大于一个值，即大于最小值；

All表示大于每一个值，即大于最大值；

相同点：都是关键字，可用于多行子查询语句

[70]: /images/20220522/df561395153846cba5040fb40183f255.png
[70 1]: /images/20220522/c4779415df7a4e2a8daaec32b374986d.png
[70 2]: /images/20220522/940db945251d470c827496758750049b.png
[70 3]: /images/20220522/c197f0d8858e4a1a9e9d1b2a32720e0b.png