45道mysql语句题

HY_000

于 2024-03-28 16:53:34 发布

阅读量746

点赞数 14

文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/HY_000/article/details/137117402

版权

创建表

CREATE TABLE students(
sno VARCHAR(3) NOT NULL,
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATE NOT NULL,
class VARCHAR(5)
);
CREATE TABLE courses(
cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(10) NOT NULL
);
CREATE TABLE scores (
sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10, 1) NOT NULL
);
CREATE TABLE teachers (
tno VARCHAR(3) NOT NULL,
tname VARCHAR(4) NOT NULL,
tsex VARCHAR(2) NOT NULL,
tbirthday DATETIME NOT NULL,
prof VARCHAR(6) NOT NULL,
depart VARCHAR(10) NOT NULL
);
select * from STUDENTS;
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);
select * from COURSES;
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);
select * from SCORES;
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
select * from teachers;
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

1、查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT sname,ssex,class from students;

2、查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT depart from teachers;

3、查询Student表的所有记录。
SELECT *FROM teachers;

4、查询Score表中成绩在60到80之间的所有记录
select * from scores where degree>60 and degree<80;

5、查询Score表中成绩为85，86或88的记录。
SELECT * from scores WHERE degree=85 or degree=86 or degree=88;

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

7、以Class降序查询Student表的所有记录。
SELECT * from students ORDER BY class desc;

8、以Cno升序、Degree降序查询Score表的所有记录。
SELECT * from scores ORDER BY degree desc,cno asc;

9、查询“95031”班的学生人数。
SELECT class,count(*)from students group by class HAVING class='95031';

10、查询Score表中的最高分的学生学号和课程号。
SELECT sno,cno from scores WHERE(SELECT max(degree)from scores);

11、查询‘3-105’号课程的平均分。
SELECT avg(degree)from scores where cno='3-105';

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT avg(degree) from scores GROUP BY cno having count(*)>=5 and cno like '3%';

13、查询最低分大于70，最高分小于90的Sno列。
SELECT sno,degree from scores where degree>70 and degree<90;

14、查询所有学生的Sname、Cno和Degree列。
SELECT sname,cno,degree from students INNER JOIN scores on students.sno =scores.sno;

15、查询所有学生的Sno、Cname和Degree列。
SELECT sno,cname,degree from scores INNER JOIN courses on scores.cno =courses.cno;

16、查询所有学生的Sname、Cname和Degree列。
SELECT sname,cname,degree from students join scores on students.sno =scores.sno join courses on scores.cno=courses.cno;

17、查询“95033”班所的课程的平均分。
SELECT avg(degree) from students INNER JOIN scores on students.sno =scores.sno where class='95033';

19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT * from students join scores on students.sno =scores.sno join courses on scores.cno=courses.cno where courses.cno='3-105' and scores.degree>(SELECT max(degree) FROM scores where sno='109');

*20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
1/SELECT sno, min(degree) as 非最高分 from scores GROUP BY sno having count(*)>=2;

2/SELECT s1.* from scores s1 where s1.sno in (SELECT sno FROM scores GROUP BY sno having count
(cno)>1)and s1.degree<(SELECT max(degree) FROM scores s2 WHERE s2.sno=s1.sno);

21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT * from students join scores on students.sno =scores.sno join courses on scores.cno=courses.cno where scores.degree>(select degree FROM scores where sno='109'and cno='3-105');

22、查询和学号为107的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
1/select sno,sname,sbirthday from students where year(sbirthday)=(SELECT year(sbirthday)
from students where sno='107');

2/select sno,sname,sbirthday from students where sbirthday like '1976%';

23、查询“张旭“教师任课的学生成绩。
SELECT degree from teachers join courses on teachers.tno =courses.tno join scores on scores.cno=courses.cno where tname='张旭';

24、查询选修某课程的同学人数多于5人的教师姓名。
1./SELECT tname from teachers join courses on teachers.tno =courses.tno join scores on scores.cno=courses.cno where courses.cno in (SELECT cno from scores GROUP BY cno having count(cno)>5);

2./SELECT teachers.*,courses.* from teachers,courses where teachers.tno=courses.tno and courses.cno=(SELECT cno from scores GROUP BY cno having count(cno)>5)

25、查询95033班和95031班全体学生的记录。
SELECT * from students join scores on students.sno =scores.sno join courses on scores.cno=courses.cno
where students.class='95033'or students.class='95031';

26、查询存在有85分以上成绩的课程Cno.
select cno from scores where degree>85;

27、查询出“计算机系“教师所教课程的成绩表。
SELECT * from teachers join courses on teachers.tno =courses.tno join scores on scores.cno=courses.cno where depart='计算机系';

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
1/SELECT DISTINCT prof from teachers where depart='计算机系' or depart='电子工程系';
2/SELECT tname, prof from teachers where depart='计算机系' or depart='电子工程系';

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
SELECT cno,sno,degree from scores where cno='3-105'and degree>(SELECT min(degree) from scores where cno='3-245') ORDER BY degree desc;

30、查询选修编号为“3-105”且成绩高于任意选修编号为“3-245”课程的同学的Cno、Sno和Degree.
SELECT cno,sno,degree from scores where cno='3-105'and degree>(SELECT max(degree) from scores where cno='3-245');

31、查询所有教师和同学的name、sex和birthday.
1/SELECT sname,tname,tsex,ssex,sbirthday,tbirthday from students join scores on students.sno =scores.sno join courses on scores.cno=courses.cno join teachers on courses.tno=teachers.tno;

2/select teachers.tname,teachers.tsex,teachers.tbirthday from teachers UNION
SELECT students.sname,students.ssex,students.sbirthday FROM students;

32、查询所有“女”教师和“女”同学的name、sex和birthday.
/*SELECT sname,tname,tsex,ssex,sbirthday,tbirthday from students join scores on students.sno =scores.sno join courses on scores.cno=courses.cno join teachers on courses.tno=teachers.tno where tsex='女'or ssex='女';*/

select teachers.tname,teachers.tsex,teachers.tbirthday from teachers where tsex='女' UNION
SELECT students.sname,students.ssex,students.sbirthday FROM students where ssex='女';

*33、查询成绩比该课程平均成绩低的同学的成绩表。
select * from scores x where degree<(SELECT avg(degree) from scores y where x.cno=y.cno)

34、查询所有任课教师的Tname和Depart.
SELECT teachers.tname,teachers.depart from teachers,courses where teachers.tno=courses.tno

35 查询所有未讲课的教师的Tname和Depart.
SELECT tname,depart from teachers left join courses on teachers.tno=courses.tno WHERE courses.tno is null;

36、查询至少有2名男生的班号。
1/SELECT class from students where ssex='男'GROUP BY class having count(ssex)>=2;
2/SELECT class from students where ssex='男'LIMIT 2;

37、查询Student表中不姓“王”的同学记录。
SELECT * from students where sname not like '王%';

38、查询Student表中每个学生的姓名和年龄。
select YEAR(CURRENT_DATE())-YEAR(sbirthday) as age,sname from students;

39、查询Student表中最大和最小的Sbirthday日期值。
SELECT max(sbirthday)as min,min(sbirthday)as max from students;

40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT * from students ORDER BY class desc , YEAR(CURRENT_DATE())-YEAR(sbirthday) DESC;

41、查询“男”教师及其所上的课程。
SELECT cno,tname from teachers join courses on teachers.tno=courses.tno where tsex='男';

42、查询最高分同学的Sno、Cno和Degree列。
SELECT sno,cno,degree from scores where degree=(SELECT max(degree)FROM scores);

43、查询和“李军”同性别的所有同学的Sname.
SELECT sname from students WHERE ssex=(SELECT ssex from students where sname='李军');

44、查询和“李军”同性别并同班的同学Sname.
SELECT sname from students WHERE ssex=(SELECT ssex from students where sname='李军') and class=(SELECT class from students where sname='李军');

45、查询所有选修“计算机导论”课程的“男”同学的成绩表
SELECT degree from students join scores on students.sno =scores.sno join courses on scores.cno=courses.cno where cname='计算机导论' and ssex='男';