一、準備數據
- 1、創建數據庫
- 2、創建學生表
- 3、創建教師表
- 4、創建課程表
- 5、創建成績表
- 6、添加數據
二、查詢練習
- 1、查詢 student 表的所有行
- 2、查詢 student 表中的 name、sex 和 class 字段的所有行
- 3、查詢 teacher 表中不重復的 department 列
- 4、查詢 score 表中成績在60-80之間的所有行（區間查詢和運算符查詢）
- 5、查詢 score 表中成績為 85, 86 或 88 的行
- 6、查詢 student 表中 '95031' 班或性別為 '女' 的所有行
- 7、以 class 降序的方式查詢 student 表的所有行
- 8、以 c_no 升序、degree 降序查詢 score 表的所有行
- 9、查詢 "95031" 班的學生人數
- 10、查詢 score 表中的最高分的學生學號和課程編號（子查詢或排序查詢）
- 11、計算分組平均值
- 12、分組條件與模糊查詢
- 13、多表查詢-1
- 14、多表查詢-2
- 15、三表關聯查詢
- 16、子查詢加分組求平均分
- 17、子查詢 - 1
- 18、子查詢 - 2
- 19、YEAR 函數與帶 IN 關鍵字查詢
- 20、多層嵌套子查詢
- 21、多表查詢
- 22、子查詢 - 3
- 23、UNION 和 NOTIN 的使用
- 24、ANY 表示至少一個 - DESC ( 降序 )
- 25、表示所有的 ALL
- 26、復制表的數據作為條件查詢
- 27、子查詢 - 4
- 28、條件加組篩選
- 29、NOTLIKE 模糊查詢取反
- 30、YEAR 與 NOW 函數
- 31、MAX 與 MIN 函數
- 32、多段排序
- 33、子查詢 - 5
- 34、MAX 函數與子查詢
- 35、子查詢 - 6
- 36、子查詢 - 7
- 37、子查詢 - 8
- 38、按等級查詢
參考

一、準備數據

創建數據庫-》創建學生表-》創建教師表-》創建課程表-》創建成績表；

1、創建數據庫

-- 創建數據庫
CREATE DATABASE select_test;
-- 切換數據庫
USE select_test;

2、創建學生表

由學號、姓名、性別、出生年月日、所在年級組成。學號為主鍵。

-- 創建學生表
CREATE TABLE student (no VARCHAR(20) PRIMARY KEY,name VARCHAR(20) NOT NULL,sex VARCHAR(10) NOT NULL,birthday DATE, -- 生日class VARCHAR(20) -- 所在班級
);

3、創建教師表

由教師編號、姓名、性別、出生年月日、職稱、所在部門組成，教師編號為主鍵。

-- 創建教師表
CREATE TABLE teacher (no VARCHAR(20) PRIMARY KEY,name VARCHAR(20) NOT NULL,sex VARCHAR(10) NOT NULL,birthday DATE,profession VARCHAR(20) NOT NULL, -- 職稱department VARCHAR(20) NOT NULL -- 部門
);

4、創建課程表

由課程號、課程名稱、教師編號組成，課程號為主鍵，教師編號為外鍵。

-- 創建課程表
CREATE TABLE course (no VARCHAR(20) PRIMARY KEY,name VARCHAR(20) NOT NULL,t_no VARCHAR(20) NOT NULL, -- 教師編號-- 表示該 tno 來自于 teacher 表中的 no 字段值FOREIGN KEY(t_no) REFERENCES teacher(no) 
);

5、創建成績表

由學生學號、課程號、成績組成。
學生學號、課程號均為外鍵。兩者聯合為主鍵。
因為一個學生要修多門課程，每個課程都有一個成績。如果只設學號or課程號為主鍵，都會存在無法區分問題。

-- 成績表
CREATE TABLE score (s_no VARCHAR(20) NOT NULL, -- 學生編號c_no VARCHAR(20) NOT NULL, -- 課程號degree DECIMAL,	-- 成績-- 表示該 s_no, c_no 分別來自于 student, course 表中的 no 字段值FOREIGN KEY(s_no) REFERENCES student(no),	FOREIGN KEY(c_no) REFERENCES course(no),-- 設置 s_no, c_no 為聯合主鍵PRIMARY KEY(s_no, c_no)
);

6、添加數據

添加學生表數據、添加教師表數據、添加課程表數據、添加添加成績表數據。

-- 添加學生表數據
INSERT INTO student VALUES('101', '曾華', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王麗', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李軍', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陸軍', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王尼瑪', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '張全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '趙鐵柱', '男', '1974-06-03', '95031');-- 添加教師表數據
INSERT INTO teacher VALUES('804', '李誠', '男', '1958-12-02', '副教授', '計算機系');
INSERT INTO teacher VALUES('856', '張旭', '男', '1969-03-12', '講師', '電子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '計算機系');
INSERT INTO teacher VALUES('831', '劉冰', '女', '1977-08-14', '助教', '電子工程系');-- 添加課程表數據
INSERT INTO course VALUES('3-105', '計算機導論', '825');
INSERT INTO course VALUES('3-245', '操作系統', '804');
INSERT INTO course VALUES('6-166', '數字電路', '856');
INSERT INTO course VALUES('9-888', '高等數學', '831');-- 添加添加成績表數據
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');

二、查詢練習

1、查詢 student 表的所有行

SELECT * FROM student;

在這里插入圖片描述

2、查詢 student 表中的 name、sex 和 class 字段的所有行

SELECT name, sex, class FROM student;

在這里插入圖片描述

3、查詢 teacher 表中不重復的 department 列

如果單純使用：

SELECT department FROM teacher;

會發現有重復
在這里插入圖片描述

-- 查詢 teacher 表中不重復的 department 列
-- department: 去重查詢
SELECT DISTINCT department FROM teacher;

在這里插入圖片描述

4、查詢 score 表中成績在60-80之間的所有行（區間查詢和運算符查詢）

-- 查詢 score 表中成績在60-80之間的所有行（區間查詢和運算符查詢）
-- BETWEEN xx AND xx: 查詢區間, AND 表示 "并且"
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;

在這里插入圖片描述

5、查詢 score 表中成績為 85, 86 或 88 的行

-- 查詢 score 表中成績為 85, 86 或 88 的行
-- IN: 查詢規定中的多個值
SELECT * FROM score WHERE degree IN (85, 86, 88);

在這里插入圖片描述

6、查詢 student 表中 ‘95031’ 班或性別為 ‘女’ 的所有行

-- 查詢 student 表中 '95031' 班或性別為 '女' 的所有行
-- or: 表示或者關系
SELECT * FROM student WHERE class = '95031' or sex = '女';

在這里插入圖片描述

7、以 class 降序的方式查詢 student 表的所有行

-- 以 class 降序的方式查詢 student 表的所有行
-- DESC: 降序，從高到低
-- ASC（默認）: 升序，從低到高
SELECT * FROM student ORDER BY class DESC;
SELECT * FROM student ORDER BY class ASC;

在這里插入圖片描述

8、以 c_no 升序、degree 降序查詢 score 表的所有行

-- 以 c_no 升序、degree 降序查詢 score 表的所有行
SELECT * FROM score ORDER BY c_no ASC, degree DESC;

在這里插入圖片描述

9、查詢 “95031” 班的學生人數

-- 查詢 "95031" 班的學生人數
-- COUNT: 統計
SELECT COUNT(*) FROM student WHERE class = '95031';

在這里插入圖片描述

10、查詢 score 表中的最高分的學生學號和課程編號（子查詢或排序查詢）

首先找到最高分在哪兒，然后把他選擇出來

-- (SELECT MAX(degree) FROM score): 子查詢，算出最高分
SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);

降序排序，然后選擇第一條數據

--  排序查詢
-- LIMIT r, n: 表示從第r行開始，查詢n條數據
SELECT s_no, c_no, degree FROM score ORDER BY degree DESC LIMIT 0, 1;

11、計算分組平均值

查詢每門課的平均成績

-- AVG: 平均值
SELECT AVG(degree) FROM score WHERE c_no = '3-105';
SELECT AVG(degree) FROM score WHERE c_no = '3-245';
SELECT AVG(degree) FROM score WHERE c_no = '6-166';-- GROUP BY: 分組查詢		這樣就不需要一行一行地打了
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;

12、分組條件與模糊查詢

查詢 score 表中至少有 2 名學生選修，并以 3 開頭的課程的平均分數。
首先查詢score表。

SELECT * FROM score;
-- c_no 課程編號
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

分析表發現，至少有 2 名學生選修的課程是 3-105 、3-245 、6-166 ，以 3 開頭的課程是 3-105 、3-245 。也就是說，我們要查詢所有 3-105 和 3-245 的 degree 平均分。

首先把 c_no, AVG(degree) 通過分組查詢出來

-- 首先把 c_no, AVG(degree) 通過分組查詢出來
SELECT c_no, AVG(degree) FROM score GROUP BY c_no
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

再查詢出至少有 2 名學生選修的課程

-- 再查詢出至少有 2 名學生選修的課程
-- HAVING: 表示持有
HAVING COUNT(c_no) >= 2

并且是以 3 開頭的課程

-- 并且是以 3 開頭的課程
-- LIKE 表示模糊查詢，"%" 是一個通配符，匹配 "3" 后面的任意字符。
AND c_no LIKE '3%';

把前面的SQL語句拼接起來，后面加上一個 COUNT(*)，表示將每個分組的個數也查詢出來

-- 把前面的SQL語句拼接起來，
-- 后面加上一個 COUNT(*)，表示將每個分組的個數也查詢出來。
SELECT c_no, AVG(degree), COUNT(*) FROM score GROUP BY c_no
HAVING COUNT(c_no) >= 2 AND c_no LIKE '3%';
+-------+-------------+----------+
| c_no  | AVG(degree) | COUNT(*) |
+-------+-------------+----------+
| 3-105 |     85.3333 |        3 |
| 3-245 |     76.3333 |        3 |
+-------+-------------+----------+

13、多表查詢-1

查詢所有學生的 name，以及該學生在 score 表中對應的 c_no 和 degree 。

從student表中選出學號和姓名；
從score表中選出學號、班級號、和分數。

SELECT no, name FROM student;
+-----+-----------+
| no  | name      |
+-----+-----------+
| 101 | 曾華      |
| 102 | 匡明      |
| 103 | 王麗      |
| 104 | 李軍      |
| 105 | 王芳      |
| 106 | 陸軍      |
| 107 | 王尼瑪    |
| 108 | 張全蛋    |
| 109 | 趙鐵柱    |
+-----+-----------+SELECT s_no, c_no, degree FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

通過分析可以發現，只要把 score 表中的 s_no 字段值替換成 student 表中對應的 name 字段值就可以了。

-- FROM...: 表示從 student, score 表中查詢
-- WHERE 的條件表示為，只有在 student.no 和 score.s_no 相等時才顯示出來。
SELECT name, c_no, degree FROM student, score 
WHERE student.no = score.s_no;
+-----------+-------+--------+
| name      | c_no  | degree |
+-----------+-------+--------+
| 王麗      | 3-105 |     92 |
| 王麗      | 3-245 |     86 |
| 王麗      | 6-166 |     85 |
| 王芳      | 3-105 |     88 |
| 王芳      | 3-245 |     75 |
| 王芳      | 6-166 |     79 |
| 趙鐵柱    | 3-105 |     76 |
| 趙鐵柱    | 3-245 |     68 |
| 趙鐵柱    | 6-166 |     81 |
+-----------+-------+--------+

14、多表查詢-2

查詢所有學生的 no 、課程名稱 ( course 表中的 name ) 和成績 ( score 表中的 degree ) 列。
只有 score 關聯學生的 no ，因此只要查詢 score 表，就能找出所有和學生相關的 no 和 degree

SELECT s_no, c_no, degree FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

然后觀察 course 表：

+-------+-----------------+
| no    | name            |
+-------+-----------------+
| 3-105 | 計算機導論      |
| 3-245 | 操作系統        |
| 6-166 | 數字電路        |
| 9-888 | 高等數學        |
+-------+-----------------+

只要把 score 表中的 c_no 替換成 course 表中對應的 name 字段值就可以了。

-- 增加一個查詢字段 name，分別從 score、course 這兩個表中查詢。
-- as 表示取一個該字段的別名。
SELECT s_no, name as c_name, degree FROM score, course
WHERE score.c_no = course.no;
+------+-----------------+--------+
| s_no | c_name          | degree |
+------+-----------------+--------+
| 103  | 計算機導論      |     92 |
| 105  | 計算機導論      |     88 |
| 109  | 計算機導論      |     76 |
| 103  | 操作系統        |     86 |
| 105  | 操作系統        |     75 |
| 109  | 操作系統        |     68 |
| 103  | 數字電路        |     85 |
| 105  | 數字電路        |     79 |
| 109  | 數字電路        |     81 |
+------+-----------------+--------+

15、三表關聯查詢

查詢所有學生的 name 、課程名 ( course 表中的 name ) 和 degree 。
sname來自student表
cname來自course表
degree來自score表
只有 score 表中關聯學生的學號和課堂號，我們只要圍繞著 score 這張表查詢就好了。

-- 由于字段名存在重復，使用 "表名.字段名 as 別名" 代替。
SELECT student.name as s_name, course.name as c_name, degree 
FROM student, score, course
WHERE student.NO = score.s_no
AND score.c_no = course.no;

在這里插入圖片描述

16、子查詢加分組求平均分

查詢 95031 班學生每門課程的平均成績。
分為三個步驟：
1、找到95031班的學生

SELECT no FROM student WHERE class = '95031'

2、以上面的學生號為查找對象，在分數表中將學生號、課程號、分數打印出來
在 score 表中根據 student 表的學生編號篩選出學生的課堂號和成績：

-- IN (..): 將篩選出的學生號當做 s_no 的條件查詢
SELECT s_no, c_no, degree FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031');
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

3、以課程號分組，算平均成績

SELECT c_no, AVG(degree) FROM score
WHERE s_no IN (SELECT no FROM student WHERE class = '95031')
GROUP BY c_no;
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+-------+-------------+

17、子查詢 - 1

查詢在 3-105 課程中，所有成績高于 109 號同學的記錄。
where的條件有兩個：1、課程號為3-105 2、在本門課程中分數高于109號同學的分數

SELECT * FROM score 
WHERE c_no = '3-105'
AND degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');

18、子查詢 - 2

查詢所有成績高于 109 號同學的 3-105 課程成績記錄。
不限制課程號，只要成績大于109號同學的3-105課程成績就可以

SELECT * FROM score
WHERE degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');

19、YEAR 函數與帶 IN 關鍵字查詢

查詢所有和 101 、108 號學生同年出生的 no 、name 、birthday 列。

-- YEAR(..): 取出日期中的年份
SELECT no, name, birthday FROM student
WHERE YEAR(birthday) IN (SELECT YEAR(birthday) FROM student WHERE no IN (101, 108));

在這里插入圖片描述

20、多層嵌套子查詢

查詢 ‘張旭’ 教師任課的學生成績表。
1、首先找到教師編號：

SELECT NO FROM teacher WHERE NAME = '張旭'

2、通過 course 表找到該教師課程號：

SELECT NO FROM course WHERE t_no = ( SELECT NO FROM teacher WHERE NAME = '張旭' );

3、通過篩選出的課程號查詢成績表：

SELECT * FROM score WHERE c_no = (SELECT no FROM course WHERE t_no = ( SELECT no FROM teacher WHERE NAME = '張旭' )
);

反復套娃。。。

21、多表查詢

查詢某選修課程多于5個同學的教師姓名。
1、首先在 teacher 表中，根據 no 字段來判斷該教師的同一門課程是否有至少5名學員選修：

-- 查詢 teacher 表
SELECT no, name FROM teacher;
+-----+--------+
| no  | name   |
+-----+--------+
| 804 | 李誠   |
| 825 | 王萍   |
| 831 | 劉冰   |
| 856 | 張旭   |
+-----+--------+
SELECT name FROM teacher WHERE no IN (-- 在這里找到對應的條件
);

2、查看和教師編號有有關的表的信息：

SELECT * FROM course;
-- t_no: 教師編號
+-------+-----------------+------+
| no    | name            | t_no |
+-------+-----------------+------+
| 3-105 | 計算機導論      | 825  |
| 3-245 | 操作系統        | 804  |
| 6-166 | 數字電路        | 856  |
| 9-888 | 高等數學        | 831  |
+-------+-----------------+------+

3、在 score 表中將 c_no 作為分組，并且限制 c_no 持有至少 5 條數據

-- 在此之前向 score 插入一些數據，以便豐富查詢條件。
INSERT INTO score VALUES ('101', '3-105', '90');
INSERT INTO score VALUES ('102', '3-105', '91');
INSERT INTO score VALUES ('104', '3-105', '89');
-- 查詢 score 表
SELECT * FROM score;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5;
+-------+
| c_no  |
+-------+
| 3-105 |
+-------+

4、根據篩選出來的課程號，找出在某課程中，擁有至少5名學員的教師編號：

SELECT t_no FROM course WHERE no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5
);
+------+
| t_no |
+------+
| 825  |
+------+

5、在 teacher 表中，根據篩選出來的教師編號找到教師姓名：

SELECT name FROM teacher WHERE no IN (-- 最終條件SELECT t_no FROM course WHERE no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5)
);

22、子查詢 - 3

查詢 “計算機系” 課程的成績表。
1、先找出 course 表中所有 計算機系 課程的編號

-- 通過 teacher 表查詢所有 `計算機系` 的教師編號
SELECT no, name, department FROM teacher WHERE department = '計算機系'
+-----+--------+--------------+
| no  | name   | department   |
+-----+--------+--------------+
| 804 | 李誠   | 計算機系     |
| 825 | 王萍   | 計算機系     |
+-----+--------+--------------+-- 通過 course 表查詢該教師的課程編號
SELECT no FROM course WHERE t_no IN (SELECT no FROM teacher WHERE department = '計算機系'
);
+-------+
| no    |
+-------+
| 3-245 |
| 3-105 |
+-------+

2、根據這個編號查詢 score 表

-- 根據篩選出來的課程號查詢成績表
SELECT * FROM score WHERE c_no IN (SELECT no FROM course WHERE t_no IN (SELECT no FROM teacher WHERE department = '計算機系')
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+

23、UNION 和 NOTIN 的使用

查詢 計算機系 與 電子工程系 中的不同職稱的教師。
首先觀察所有教師，發現題目中要求我們找到的是804 and 856.

在這里插入圖片描述
1、首先找計算機系里面的講師，這個講師的職稱不在電子工程系里面。

-- NOT: 代表邏輯非
SELECT * FROM teacher WHERE department = '計算機系' AND profession NOT IN (SELECT profession FROM teacher WHERE department = '電子工程系'
)

在這里插入圖片描述
2、另外一個反過來

SELECT * FROM teacher WHERE department = '電子工程系' AND profession NOT IN (SELECT profession FROM teacher WHERE department = '計算機系'
);

在這里插入圖片描述
3、使用union把兩個集合拼接起來（注意第一句話不要有分號）

SELECT * FROM teacher WHERE department = '計算機系' AND profession NOT IN (SELECT profession FROM teacher WHERE department = '電子工程系'
)
-- 合并兩個集
UNION
SELECT * FROM teacher WHERE department = '電子工程系' AND profession NOT IN (SELECT profession FROM teacher WHERE department = '計算機系'
);

在這里插入圖片描述

24、ANY 表示至少一個 - DESC ( 降序 )

查詢課程 3-105 且成績至少高于 3-245 的 score 表，并且按照degree從高到低排序
1、首先把課程為3-105 和3-245 的成績表選出來看看

SELECT * FROM score WHERE c_no = '3-105';
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+SELECT * FROM score WHERE c_no = '3-245';
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-245 |     86 |
| 105  | 3-245 |     75 |
| 109  | 3-245 |     68 |
+------+-------+--------+

2、在 3-105 成績中，只要有一個大于從 3-245 篩選出來的任意行就符合條件。最后根據降序查詢結果。
這里其實意思就是大于3-245成績最小值即可

SELECT * FROM score WHERE c_no = '3-105' AND degree > ANY(SELECT degree FROM score WHERE c_no = '3-245'
) ORDER BY degree DESC;
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 102  | 3-105 |     91 |
| 101  | 3-105 |     90 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
| 109  | 3-105 |     76 |
+------+-------+--------+

25、表示所有的 ALL

查詢課程 3-105 且成績高于 3-245 的 score 表。
也就是說，在 3-105 每一行成績中，都要大于從 3-245 篩選出來全部行才算符合條件。

SELECT * FROM score WHERE c_no = '3-105' AND degree > ALL(SELECT degree FROM score WHERE c_no = '3-245'
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 103  | 3-105 |     92 |
| 104  | 3-105 |     89 |
| 105  | 3-105 |     88 |
+------+-------+--------+

26、復制表的數據作為條件查詢

查詢某課程成績比該課程平均成績低的 score 表。
1、首先查詢每個課程的平均分

-- 查詢平均分
SELECT c_no, AVG(degree) FROM score GROUP BY c_no;
+-------+-------------+
| c_no  | AVG(degree) |
+-------+-------------+
| 3-105 |     87.6667 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

2、查詢 score 表

SELECT degree FROM score;
+--------+
| degree |
+--------+
|     90 |
|     91 |
|     92 |
|     86 |
|     85 |
|     89 |
|     88 |
|     75 |
|     79 |
|     76 |
|     68 |
|     81 |
+--------+

3、將表 b 作用于表 a 中查詢數據。ab兩表均為score的別名

SELECT * FROM score a WHERE degree < ((SELECT AVG(degree) FROM score b WHERE a.c_no = b.c_no)
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

27、子查詢 - 4

查詢所有任課 ( 在 course 表里有課程 ) 教師的 name 和 department 。

SELECT name, department FROM teacher WHERE no IN (SELECT t_no FROM course);
+--------+-----------------+
| name   | department      |
+--------+-----------------+
| 李誠   | 計算機系        |
| 王萍   | 計算機系        |
| 劉冰   | 電子工程系      |
| 張旭   | 電子工程系      |
+--------+-----------------+

28、條件加組篩選

查詢 student 表中至少有 2 名男生的 class 。

-- 查看學生表信息
SELECT * FROM student;
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 101 | 曾華      | 男  | 1977-09-01 | 95033 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
| 103 | 王麗      | 女  | 1976-01-23 | 95033 |
| 104 | 李軍      | 男  | 1976-02-20 | 95033 |
| 105 | 王芳      | 女  | 1975-02-10 | 95031 |
| 106 | 陸軍      | 男  | 1974-06-03 | 95031 |
| 107 | 王尼瑪    | 男  | 1976-02-20 | 95033 |
| 108 | 張全蛋    | 男  | 1975-02-10 | 95031 |
| 109 | 趙鐵柱    | 男  | 1974-06-03 | 95031 |
| 110 | 張飛      | 男  | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+-- 只查詢性別為男，然后按 class 分組，并限制 class 行大于 1。
SELECT class FROM student WHERE sex = '男' GROUP BY class HAVING COUNT(*) > 1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+

29、NOTLIKE 模糊查詢取反

查詢 student 表中不姓 “王” 的同學記錄。

-- NOT: 取反
-- LIKE: 模糊查詢
SELECT * FROM student WHERE name NOT LIKE '王%';
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 101 | 曾華      | 男  | 1977-09-01 | 95033 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
| 104 | 李軍      | 男  | 1976-02-20 | 95033 |
| 106 | 陸軍      | 男  | 1974-06-03 | 95031 |
| 108 | 張全蛋    | 男  | 1975-02-10 | 95031 |
| 109 | 趙鐵柱    | 男  | 1974-06-03 | 95031 |
| 110 | 張飛      | 男  | 1974-06-03 | 95038 |
+-----+-----------+-----+------------+-------+

30、YEAR 與 NOW 函數

查詢 student 表中每個學生的姓名和年齡。
使用函數 YEAR(NOW()) 計算出當前年份，減去出生年份后得出年齡。

SELECT name, YEAR(NOW()) - YEAR(birthday) as age FROM student;
+-----------+------+
| name      | age  |
+-----------+------+
| 曾華      |   42 |
| 匡明      |   44 |
| 王麗      |   43 |
| 李軍      |   43 |
| 王芳      |   44 |
| 陸軍      |   45 |
| 王尼瑪    |   43 |
| 張全蛋    |   44 |
| 趙鐵柱    |   45 |
| 張飛      |   45 |
+-----------+------+

31、MAX 與 MIN 函數

查詢 student 表中最大和最小的 birthday 值。

SELECT MAX(birthday), MIN(birthday) FROM student;
+---------------+---------------+
| MAX(birthday) | MIN(birthday) |
+---------------+---------------+
| 1977-09-01    | 1974-06-03    |
+---------------+---------------+

32、多段排序

以 class 和 birthday 從大到小的順序查詢 student 表。

SELECT * FROM student ORDER BY class DESC, birthday;
+-----+-----------+-----+------------+-------+
| no  | name      | sex | birthday   | class |
+-----+-----------+-----+------------+-------+
| 110 | 張飛      | 男  | 1974-06-03 | 95038 |
| 103 | 王麗      | 女  | 1976-01-23 | 95033 |
| 104 | 李軍      | 男  | 1976-02-20 | 95033 |
| 107 | 王尼瑪    | 男  | 1976-02-20 | 95033 |
| 101 | 曾華      | 男  | 1977-09-01 | 95033 |
| 106 | 陸軍      | 男  | 1974-06-03 | 95031 |
| 109 | 趙鐵柱    | 男  | 1974-06-03 | 95031 |
| 105 | 王芳      | 女  | 1975-02-10 | 95031 |
| 108 | 張全蛋    | 男  | 1975-02-10 | 95031 |
| 102 | 匡明      | 男  | 1975-10-02 | 95031 |
+-----+-----------+-----+------------+-------+

33、子查詢 - 5

查詢 “男” 教師及其所上的課程。
先從教師表中找教師再把查詢結果作為課程表的查詢依據

SELECT * FROM course WHERE t_no in (SELECT no FROM teacher WHERE sex = '男');
+-------+--------------+------+
| no    | name         | t_no |
+-------+--------------+------+
| 3-245 | 操作系統     | 804  |
| 6-166 | 數字電路     | 856  |
+-------+--------------+------+

34、MAX 函數與子查詢

查詢最高分同學的 score 表。

-- 找出最高成績（該查詢只能有一個結果）
SELECT MAX(degree) FROM score;
-- 根據上面的條件篩選出所有最高成績表，
-- 該查詢可能有多個結果，假設 degree 值多次符合條件。
SELECT * FROM score WHERE degree = (SELECT MAX(degree) FROM score);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
+------+-------+--------+

35、子查詢 - 6

查詢和 “李軍” 同性別的所有同學 name 。

-- 首先將李軍的性別作為條件取出來
SELECT sex FROM student WHERE name = '李軍';
+-----+
| sex |
+-----+
| 男  |
+-----+
-- 根據性別查詢 name 和 sex
SELECT name, sex FROM student WHERE sex = (SELECT sex FROM student WHERE name = '李軍'
);
+-----------+-----+
| name      | sex |
+-----------+-----+
| 曾華      | 男  |
| 匡明      | 男  |
| 李軍      | 男  |
| 陸軍      | 男  |
| 王尼瑪    | 男  |
| 張全蛋    | 男  |
| 趙鐵柱    | 男  |
| 張飛      | 男  |
+-----------+-----+

36、子查詢 - 7

查詢和 "李軍" 同性別且同班的同學 name 。

SELECT name, sex, class FROM student WHERE sex = (SELECT sex FROM student WHERE name = '李軍'
) AND class = (SELECT class FROM student WHERE name = '李軍'
);
+-----------+-----+-------+
| name      | sex | class |
+-----------+-----+-------+
| 曾華      | 男  | 95033 |
| 李軍      | 男  | 95033 |
| 王尼瑪    | 男  | 95033 |
+-----------+-----+-------+

37、子查詢 - 8

查詢所有選修 “計算機導論” 課程的 “男” 同學成績表。
需要的 “計算機導論” 和性別為 “男” 的編號可以在 course 和 student 表中找到。

SELECT * FROM score WHERE c_no = (SELECT no FROM course WHERE name = '計算機導論'
) AND s_no IN (SELECT no FROM student WHERE sex = '男'
);
+------+-------+--------+
| s_no | c_no  | degree |
+------+-------+--------+
| 101  | 3-105 |     90 |
| 102  | 3-105 |     91 |
| 104  | 3-105 |     89 |
| 109  | 3-105 |     76 |
+------+-------+--------+

38、按等級查詢

建立一個 grade 表代表學生的成績等級，并插入數據：

CREATE TABLE grade (low INT(3),upp INT(3),grade 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');SELECT * FROM grade;
+------+------+-------+
| low  | upp  | grade |
+------+------+-------+
|   90 |  100 | A     |
|   80 |   89 | B     |
|   70 |   79 | C     |
|   60 |   69 | D     |
|    0 |   59 | E     |
+------+------+-------+

2、查詢所有學生的 s_no 、c_no 和 grade 列。
思路是，使用區間 ( BETWEEN ) 查詢，判斷學生的成績 ( degree ) 在 grade 表的 low 和 upp 之間。

SELECT s_no, c_no, grade FROM score, grade 
WHERE degree BETWEEN low AND upp;
+------+-------+-------+
| s_no | c_no  | grade |
+------+-------+-------+
| 101  | 3-105 | A     |
| 102  | 3-105 | A     |
| 103  | 3-105 | A     |
| 103  | 3-245 | B     |
| 103  | 6-166 | B     |
| 104  | 3-105 | B     |
| 105  | 3-105 | B     |
| 105  | 3-245 | C     |
| 105  | 6-166 | C     |
| 109  | 3-105 | C     |
| 109  | 3-245 | D     |
| 109  | 6-166 | B     |
+------+-------+-------+