mysql 常用查询

题目来源:https://blog.csdn.net/mrbcy/article/details/68965271

• 数据表及数据准备

CREATE TABLE IF NOT EXISTS students (sno VARCHAR (3) NOT NULL,sname VARCHAR (4) NOT NULL,ssex VARCHAR (2) NOT NULL,sbirthday DATETIME,class VARCHAR (5)); 
CREATE TABLE IF NOT EXISTS courses (cno VARCHAR (5) NOT NULL,cname VARCHAR (10) NOT NULL,tno VARCHAR (10) NOT NULL); 
CREATE TABLE IF NOT EXISTS scores (sno VARCHAR (3) NOT NULL,cno VARCHAR (5) NOT NULL,degree NUMERIC (10,1) NOT NULL); 
CREATE TABLE IF NOT EXISTS teachers (tno VARCHAR (3) NOT NULL,tname VARCHAR (4) NOT NULL,tsex VARCHAR (2) NOT NULL,tbirthday DATETIME NOT NULL,prof VARCHAR (6),depart VARCHAR (10) NOT NULL); 
CREATE TABLE grade (`low` DECIMAL (3,0),`upp` DECIMAL (3),`rank` CHAR (1)); 

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); 
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); 
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); 
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','助教','电子工程系'); 
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');

• 题目
  1、 查询Student表中的所有记录的Sname、Ssex和Class列。
  2、 查询教师所有的单位即不重复的Depart列。
  3、 查询Student表的所有记录。
  4、 查询Score表中成绩在60到80之间的所有记录。
  5、 查询Score表中成绩为85，86或88的记录。
  6、 查询Student表中“95031”班或性别为“女”的同学记录。
  7、 以Class降序查询Student表的所有记录。
  8、 以Cno升序、Degree降序查询Score表的所有记录。
  9、 查询“95031”班的学生人数。
  10、查询Score表中的最高分的学生学号和课程号。
  11、查询‘3-105’号课程的平均分。
  12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
  13、查询socre表中最低分大于70，最高分小于90的Sno列。
  14、查询所有学生的Sname、Cno和Degree列。
  15、查询所有学生的Sno、Cname和Degree列。
  16、查询所有学生的Sname、Cname和Degree列。
  17、查询“95033”班所选课程的平均分。
  18、建立grade表，现查询所有同学的Sno、Cno和rank列。
  19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
  20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
  21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
  22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
  23、查询“张旭“教师任课的学生成绩。
  24、查询选修某课程的同学人数多于5人的教师姓名。
  25、查询95033班和95031班全体学生的记录。
  26、查询存在有85分以上成绩的课程Cno.
  27、查询出“计算机系“教师所教课程的成绩表。
  28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
  29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
  30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
  31、查询所有教师和同学的name、sex和birthday.
  32、查询所有“女”教师和“女”同学的name、sex和birthday.
  33、查询成绩比该课程平均成绩低的同学的成绩表。
  34、查询所有任课教师的Tname和Depart.
  35 查询所有未讲课的教师的Tname和Depart.
  36、查询至少有2名男生的班号。
  37、查询Student表中不姓“王”的同学记录。
  38、查询Student表中每个学生的姓名和年龄。
  39、查询Student表中最大和最小的Sbirthday日期值。
  40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
  41、查询“男”教师及其所上的课程。
  42、查询最高分同学的Sno、Cno和Degree列。
  43、查询和“李军”同性别的所有同学的Sname.
  44、查询和“李军”同性别并同班的同学Sname.
  45、查询所有选修“计算机导论”课程的“男”同学的成绩表

• 答案

1. 查询Student表中的所有记录的Sname、Ssex和Class列
   SELECT Sname,Ssex,Class FROM students;
2. 查询教师所有的单位即不重复的Depart列
   SELECT DISTINCT(depart) from teachers;
3. 查询Student表的所有记录
   SELECT * FROM students;
4. 查询Score表中成绩在60到80之间的所有记录
   SELECT * from scores WHERE degree BETWEEN 60 and 80;
5. 查询Score表中成绩为85，86或88的记录
   SELECT * from scores WHERE degree in (85,86,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 Cno, degree desc;
9. 查询“95031”班的学生人数
   SELECT count(*) from students WHERE class = '95031';
10. 查询Score表中的最高分的学生学号和课程号。
    SELECT sno, cno from scores WHERE degree = (select max(degree) from scores);
11. 查询‘3-105’号课程的平均分。
    SELECT cno,avg(degree) from scores where cno = '3-105';
12. 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
    SELECT cno,avg(degree) from scores where cno like '3%' GROUP BY cno having count(*) >= 5;
13. 查询socre表中最低分大于70，最高分小于90的Sno列。
    select sno from scores GROUP BY sno having min(degree) > 70 and max(degree) < 90;
14. 查询所有学生的Sname、Cno和Degree列。
    select s1.sname,s2.cno,s2.degree from students s1 INNER JOIN scores as s2 on s1.sno = s2.sno;
15. 查询所有学生的Sno、Cname和Degree列。
    select s.sno,c.cno,s.degree from scores s INNER JOIN courses as c on s.cno = c.cno;
16. 查询所有学生的Sname、Cname和Degree列。
    SELECT scores.sno, courses.cname, scores.degree from scores INNER JOIN courses on (scores.cno = courses.cno) INNER JOIN students on (students.sno = scores.sno);
17. 查询“95033”班所选课程的平均分。
    select students.class,avg(degree) from students INNER JOIN scores on students.sno = scores.sno where students.class = '95033';
18. 建立grade表，现查询所有同学的Sno、Cno和rank列。

• select s.sno,s.cno,g.rank from scores s, grade g where s.degree > g.low and s.degree < g.upp;
• select s.sno,s.cno,g.rank from scores s JOIN grade g on s.degree > g.low and s.degree < g.upp;

19. 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

• select * from scores where degree >(select degree from scores WHERE scores.sno = '109' and cno = '3-105') and cno='3-105';
• SELECT s1.sno,s1.cno,s1.degree from scores s1 INNER JOIN scores s2 on (s1.cno = s2.cno and s1.degree > s2.degree )where s1.cno = '3-105' and s2.sno = '109';

20. 查询score中选学一门以上课程的同学中分数为非最高分成绩的记录

select s1.sno,s1.cno,s1.degree from (select cno, max(degree) as degree from scores GROUP BY cno ) as s2, #每科最高分
scores as s1 WHERE s1.cno = s2.cno AND
s1.degree != s2.degree and s1.sno in   #科目相同但分数不为每科最高分
(select sno from scores GROUP BY sno having count(*) > 1); #每人选择大于1科

21. 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录

• select * from scores where degree >(select degree from scores where sno='109' and cno = '3-105');
• select s1.sno,s1.cno,s1.degree from scores s1 JOIN scores s2 on (s1.degree>s2.degree) WHERE s2.sno = '109' and s2.cno = '3-105';

22. 查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列

• select s1.sno, s1.sname,s1.sbirthday from students s1 INNER JOIN students s2 on (DATE_FORMAT(s1.sbirthday,'%Y') = DATE_FORMAT(s2.sbirthday,'%Y') ) WHERE s2.sno = '101';
• SELECT s1.Sno,s1.Sname,s1.Sbirthday FROM Students AS s1 INNER JOIN Students AS s2 ON (YEAR (s1.Sbirthday)=YEAR (s2.Sbirthday)) WHERE s2.Sno='101';

23. 查询“张旭“教师任课的学生成绩
    select s.cno, s.degree from scores s INNER JOIN courses c on s.cno = c.cno INNER JOIN teachers t on c.tno = t.tno where t.tname = '张旭';
24. 查询选修某课程的同学人数多于5人的教师姓名

 select t.tname,c.cname from teachers t INNER JOIN courses c on (t.tno = c.tno) INNER JOIN scores s on (c.cno = s.cno) GROUP BY s.cno having (count(*))>5;

25. 查询95033班和95031班全体学生的记录
    select * from students where class in ('95033','95031');
26. 查询存在有85分以上成绩的课程Cno
    select DISTINCT cno from scores WHERE degree > 85;
27. 查询出“计算机系“教师所教课程的成绩表。

SELECT scores.sno,scores.cno,scores.degree FROM teachers 
INNER JOIN courses ON (courses.tno=teachers.tno) 
INNER JOIN scores ON (scores.cno=courses.cno) 
WHERE teachers.depart='计算机系';

28. 查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof

SELECT *FROM teachers
WHERE depart IN ('计算机系', '电子工程系')
AND prof NOT IN (
    SELECT t1.prof
    FROM teachers as t1
    INNER JOIN teachers as t2
    ON (t1.prof = t2.prof)
    WHERE t1.depart = '计算机系'
    AND t2.depart = '电子工程系'  #查找出有相同的职称
)

29. 查询选修编号为“3-105“课程且成绩至少高于任意选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序

select * from scores 
WHERE cno='3-105' 
and degree > (select min(degree) from scores WHERE cno = '3-245') 
ORDER BY degree desc;

SELECT Cno,Sno,Degree FROM Scores
WHERE Cno='3-105' AND Degree > ANY(
    SELECT Degree
    FROM Scores
    WHERE Cno='3-245')
ORDER BY Degree DESC;

select DISTINCT t1.cno, t1.sno, t1.degree from scores t1 
INNER JOIN scores t2 on (t1.degree > t2.degree) 
WHERE t1.cno ='3-105' and t2.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 > ALL(
    SELECT Degree
    FROM Scores
    WHERE Cno='3-245')
ORDER BY Degree DESC;

31. 查询所有教师和同学的name、sex和birthday

select t.tname as name, t.tsex as sex, t.tbirthday as birthday from teachers t 
UNION
select s.sname as name, s.ssex as sex, s.sbirthday as birthday from students s;

32. 查询所有“女”教师和“女”同学的name、sex和birthday

select t.tname as name, t.tsex as sex, t.tbirthday as birthday from teachers t  WHERE t.tsex = '女' 
UNION
select s.sname as name, s.ssex as sex, s.sbirthday as birthday from students s WHERE  s.ssex = '女';

33. 查询成绩比该课程平均成绩低的同学的成绩表

SELECT s.sno,s.cno,s.degree from scores  as s 
INNER JOIN 
(SELECT avg(degree) as avgg,cno as cno from scores GROUP BY cno) ss on s.cno = ss.cno 
WHERE s.degree < ss.avgg;

SELECT s1.*
FROM Scores AS s1 INNER JOIN (
    SELECT Cno,AVG(Degree) AS aDegree
    FROM Scores
    GROUP BY Cno) s2
ON(s1.Cno=s2.Cno AND s1.Degree<s2.aDegree); 

34. 查询所有任课教师的Tname和Depart

SELECT t.tname, t.depart from teachers as t INNER JOIN courses c on t.tno = c.tno;

35. 查询所有未讲课的教师的Tname和Depart
    SELECT tname, depart from teachers WHERE tno not in (SELECT tno from courses);
36. 查询至少有2名男生的班号
    SELECT class from students WHERE ssex = '男' GROUP BY class having count(*)>=2;
37. 查询Student表中不姓“王”的同学记录
    select * FROM students WHERE sname not like '王%';
38. 查询Student表中每个学生的姓名和年龄
    select sname, (YEAR(now()) - YEAR(sbirthday)) as age from students;
39. 查询Student表中最大和最小的Sbirthday日期值
    select min(sbirthday) as min_bir,max(sbirthday) as max_bir from students;
40. 以班号和年龄从大到小的顺序查询Student表中的全部记录
    select * from students ORDER BY class desc, sbirthday;
41. 查询“男”教师及其所上的课程
    select t.tname,c.cname from teachers t INNER JOIN courses c on t.tno = c.tno WHERE t.tsex = '男';
42. 查询各科最高分同学的Sno、Cno和Degree列
    select sno,cno,degree from scores GROUP BY cno having degree = max(degree);
43. 查询和“李军”同性别的所有同学的Sname

• select sname from students WHERE ssex = (select ssex from students WHERE sname = '李军');
• select s1.sname from students s1 INNER JOIN students s2 on s1.ssex = s2.ssex where s2.sname = '李军';

44. 查询和“李军”同性别并同班的同学Sname

select s1.sname from students s1 
INNER JOIN students s2 
on s1.ssex = s2.ssex and s1.class = s2.class and  s1.sno != s2.sno 
where s2.sname = '李军' ;

45. 查询所有选修“计算机导论”课程的“男”同学的成绩表

SELECT c.cno,ss.sname,ss.ssex,s.degree from courses c 
INNER JOIN scores s on c.cno = s.cno 
INNER JOIN students ss on ss.sno = s.sno 
WHERE c.cname = '计算机导论' and ss.ssex = '男';