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、查询所有选修“计算机导论”课程的“男”同学的成绩表 -
答案
- 查询Student表中的所有记录的Sname、Ssex和Class列
SELECT Sname,Ssex,Class FROM students;
- 查询教师所有的单位即不重复的Depart列
SELECT DISTINCT(depart) from teachers;
- 查询Student表的所有记录
SELECT * FROM students;
- 查询Score表中成绩在60到80之间的所有记录
SELECT * from scores WHERE degree BETWEEN 60 and 80;
- 查询Score表中成绩为85,86或88的记录
SELECT * from scores WHERE degree in (85,86,88);
- 查询Student表中“95031”班或性别为“女”的同学记录
SELECT * from students WHERE class = '95031' or ssex = '女';
- 以Class降序查询Student表的所有记录
SELECT * from students ORDER BY class desc;
- 以Cno升序、Degree降序查询Score表的所有记录
select * from scores ORDER BY Cno, degree desc;
- 查询“95031”班的学生人数
SELECT count(*) from students WHERE class = '95031';
- 查询Score表中的最高分的学生学号和课程号。
SELECT sno, cno from scores WHERE degree = (select max(degree) from scores);
- 查询‘3-105’号课程的平均分。
SELECT cno,avg(degree) from scores where cno = '3-105';
- 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT cno,avg(degree) from scores where cno like '3%' GROUP BY cno having count(*) >= 5;
- 查询socre表中最低分大于70,最高分小于90的Sno列。
select sno from scores GROUP BY sno having min(degree) > 70 and max(degree) < 90;
- 查询所有学生的Sname、Cno和Degree列。
select s1.sname,s2.cno,s2.degree from students s1 INNER JOIN scores as s2 on s1.sno = s2.sno;
- 查询所有学生的Sno、Cname和Degree列。
select s.sno,c.cno,s.degree from scores s INNER JOIN courses as c on s.cno = c.cno;
- 查询所有学生的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);
- 查询“95033”班所选课程的平均分。
select students.class,avg(degree) from students INNER JOIN scores on students.sno = scores.sno where students.class = '95033';
- 建立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;
- 查询选修“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';
- 查询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科
- 查询成绩高于学号为“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';
- 查询和学号为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';
- 查询“张旭“教师任课的学生成绩
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 = '张旭';
- 查询选修某课程的同学人数多于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;
- 查询95033班和95031班全体学生的记录
select * from students where class in ('95033','95031');
- 查询存在有85分以上成绩的课程Cno
select DISTINCT cno from scores WHERE degree > 85;
- 查询出“计算机系“教师所教课程的成绩表。
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='计算机系';
- 查询“计算机系”与“电子工程系“不同职称的教师的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 = '电子工程系' #查找出有相同的职称
)
- 查询选修编号为“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; #只能查找存在有
- 查询选修编号为“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;
- 查询所有教师和同学的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;
- 查询所有“女”教师和“女”同学的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 = '女';
- 查询成绩比该课程平均成绩低的同学的成绩表
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);
- 查询所有任课教师的Tname和Depart
SELECT t.tname, t.depart from teachers as t INNER JOIN courses c on t.tno = c.tno;
- 查询所有未讲课的教师的Tname和Depart
SELECT tname, depart from teachers WHERE tno not in (SELECT tno from courses);
- 查询至少有2名男生的班号
SELECT class from students WHERE ssex = '男' GROUP BY class having count(*)>=2;
- 查询Student表中不姓“王”的同学记录
select * FROM students WHERE sname not like '王%';
- 查询Student表中每个学生的姓名和年龄
select sname, (YEAR(now()) - YEAR(sbirthday)) as age from students;
- 查询Student表中最大和最小的Sbirthday日期值
select min(sbirthday) as min_bir,max(sbirthday) as max_bir from students;
- 以班号和年龄从大到小的顺序查询Student表中的全部记录
select * from students ORDER BY class desc, sbirthday;
- 查询“男”教师及其所上的课程
select t.tname,c.cname from teachers t INNER JOIN courses c on t.tno = c.tno WHERE t.tsex = '男';
- 查询各科最高分同学的Sno、Cno和Degree列
select sno,cno,degree from scores GROUP BY cno having degree = max(degree);
- 查询和“李军”同性别的所有同学的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 = '李军';
- 查询和“李军”同性别并同班的同学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 = '李军' ;
- 查询所有选修“计算机导论”课程的“男”同学的成绩表
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 = '男';