mysql 常用查询

2020-04-13 本文已影响0人 Canes

题目来源: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 = '男';

mysql 常用查询

猜你喜欢

热点阅读