作者 | 遠(yuǎn)辰

本文為「數(shù)據(jù)不吹?！雇陡?/p>

今天給大家分享的是我學(xué)習(xí)MySQL記錄的詳細(xì)筆記，有基礎(chǔ)知識(shí)，也有實(shí)戰(zhàn)案例，文章較長(zhǎng)，建議收藏~

基本語法

-- 顯示所有數(shù)據(jù)庫
show databases;

-- 創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE test;

-- 切換數(shù)據(jù)庫
use test;

-- 顯示數(shù)據(jù)庫中的所有表
show tables;

-- 創(chuàng)建數(shù)據(jù)表
CREATE TABLE pet (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);

-- 查看數(shù)據(jù)表結(jié)構(gòu)
-- describe pet;
desc pet;

-- 查詢表
SELECT * from pet;

-- 插入數(shù)據(jù)
INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', );

-- 修改數(shù)據(jù)
UPDATE pet SET name = 'squirrel' where owner = 'Diane';

-- 刪除數(shù)據(jù)
DELETE FROM pet where name = 'squirrel';

-- 刪除表
DROP TABLE myorder;

數(shù)據(jù)庫大三大設(shè)計(jì)范式

• 1NF

不可分割性，只要字段值還可以繼續(xù)拆分，就不滿足第一范式。

不可分割的意思就按字面理解就是最小單位，不能再分成更小單位了。

字段只能是一個(gè)值，不能被拆分成多個(gè)字段，否則的話，它就是可分割的，就不符合一范式。

• 2NF

第二范式就是要有主鍵，要求其他字段都依賴于主鍵。

為什么要有主鍵？沒有主鍵就沒有唯一性，沒有唯一性在集合中就定位不到這行記錄，所以要主鍵。

在滿足第一范式的前提下，主鍵外的每一列都必須完全依賴于主鍵。如果出現(xiàn)不完全依賴，只可能發(fā)生在聯(lián)合主鍵的情況下。

• 3NF

在滿足第二范式的前提下，除了主鍵列之外，其他列之間不能有傳遞依賴關(guān)系，即“消除冗余”。

消除冗余，就是各種信息只在一個(gè)地方存儲(chǔ)，不出現(xiàn)在多張表中。

范式總結(jié)：范式，其實(shí)是用來學(xué)習(xí)參考的，設(shè)計(jì)的時(shí)候根據(jù)情況，未必一定要遵守，要靈活結(jié)合業(yè)務(wù)實(shí)際情況決定。

查詢練習(xí)

• 準(zhǔn)備數(shù)據(jù)

-- 創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE select_test;
-- 切換數(shù)據(jù)庫
USE select_test;

-- 創(chuàng)建學(xué)生表
CREATE TABLE student (
no VARCHAR(20) PRIMARY KEY,
name VARCHAR(20) NOT ,
sex VARCHAR(10) NOT ,
birthday DATE, -- 生日
class VARCHAR(20) -- 所在班級(jí)
);

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

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

-- 成績(jī)表
CREATE TABLE score (
s_no VARCHAR(20) NOT , -- 學(xué)生編號(hào)
c_no VARCHAR(20) NOT , -- 課程號(hào)
degree DECIMAL, -- 成績(jī)
-- 表示該 s_no, c_no 分別來自于 student, course 表中的 no 字段值
FOREIGN KEY(s_no) REFERENCES student(no),
FOREIGN KEY(c_no) REFERENCES course(no),
-- 設(shè)置 s_no, c_no 為聯(lián)合主鍵
PRIMARY KEY(s_no, c_no)
);

-- 查看所有表
SHOW TABLES;

-- 添加學(xué)生表數(shù)據(jù)
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');

-- 添加教師表數(shù)據(jù)
INSERT INTO teacher VALUES('804', '李誠(chéng)', '男', '1958-12-02', '副教授', '計(jì)算機(jī)系');
INSERT INTO teacher VALUES('856', '張旭', '男', '1969-03-12', '講師', '電子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '計(jì)算機(jī)系');
INSERT INTO teacher VALUES('831', '劉冰', '女', '1977-08-14', '助教', '電子工程系');

-- 添加課程表數(shù)據(jù)
INSERT INTO course VALUES('3-105', '計(jì)算機(jī)導(dǎo)論', '825');
INSERT INTO course VALUES('3-245', '操作系統(tǒng)', '804');
INSERT INTO course VALUES('6-166', '數(shù)字電路', '856');
INSERT INTO course VALUES('9-888', '高等數(shù)學(xué)', '831');

-- 添加添加成績(jī)表數(shù)據(jù)
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');

-- 查看表結(jié)構(gòu)
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;

• 基礎(chǔ)查詢

-- 查詢 student 表的所有行
SELECT * FROM student;

-- 查詢 student 表中的 name、sex 和 class 字段的所有行
SELECT name, sex, class FROM student;

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

-- 查詢 score 表中成績(jī)?cè)?0-80之間的所有行（區(qū)間查詢和運(yùn)算符查詢）
-- BETWEEN xx AND xx: 查詢區(qū)間, AND 表示 "并且"
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;

-- 查詢 score 表中成績(jī)?yōu)?85, 86 或 88 的行
-- IN: 查詢規(guī)定中的多個(gè)值
SELECT * FROM score WHERE degree IN (85, 86, 88);

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

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

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

-- 查詢 "95031" 班的學(xué)生人數(shù)
-- COUNT: 統(tǒng)計(jì)
SELECT COUNT(*) FROM student WHERE class = '95031';

-- 查詢 score 表中的最高分的學(xué)生學(xué)號(hào)和課程編號(hào)（子查詢或排序查詢）。
-- (SELECT MAX(degree) FROM score): 子查詢，算出最高分
SELECT s_no, c_no FROM score WHERE degree = (SELECT MAX(degree) FROM score);

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

• 分組計(jì)算平均成績(jī)

查詢每門課的平均成績(jī)

-- 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;

• 分組條件與模糊查詢

查詢score表中至少有 2 名學(xué)生選修，并以 3 開頭的課程的平均分?jǐn)?shù)

分析表發(fā)現(xiàn)，至少有 2 名學(xué)生選修的課程是3-105、3-245、6-166，以 3 開頭的課程是3-105、3-245。也就是說，我們要查詢所有3-105和3-245的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 名學(xué)生選修的課程
-- HAVING: 表示持有
HAVING COUNT(c_no) >= 2

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

-- 把前面的SQL語句拼接起來，
-- 后面加上一個(gè) COUNT(*)，表示將每個(gè)分組的個(gè)數(shù)也查詢出來。
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 |
+-------+-------------+----------+

• 多表查詢 - 1

查詢所有學(xué)生的name，以及該學(xué)生在score表中對(duì)應(yīng)的c_no和degree

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 |
+------+-------+--------+

通過分析可以發(fā)現(xiàn)，只要把score表中的s_no字段值替換成student表中對(duì)應(yīng)的name字段值就可以了，如何做呢？

-- FROM...: 表示從 student, score 表中查詢
-- WHERE 的條件表示為，只有在 student.no 和 score.s_no 相等時(shí)才顯示出來。
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 |
+-----------+-------+--------+

• 多表查詢 - 2

查詢所有學(xué)生的 no 、課程名稱 ( course 表中的 name ) 和成績(jī) ( score 表中的degree ) 列。

只有score關(guān)聯(lián)學(xué)生的no，因此只要查詢score表，就能找出所有和學(xué)生相關(guān)的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表：

SELECT no, name FROM course;
+-------+-----------------+
| no | name |
+-------+-----------------+
| 3-105 | 計(jì)算機(jī)導(dǎo)論 |
| 3-245 | 操作系統(tǒng) |
| 6-166 | 數(shù)字電路 |
| 9-888 | 高等數(shù)學(xué) |
+-------+-----------------+

只要把score表中的c_no替換成course表中對(duì)應(yīng)的name字段值就可以了。

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

• 三表關(guān)聯(lián)查詢

查詢所有學(xué)生的 name 、課程名 ( course 表中的 name ) 和 degree 。

只有score表中關(guān)聯(lián)學(xué)生的學(xué)號(hào)和課堂號(hào)，我們只要圍繞著score這張表查詢就好了。

SELECT * 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 |
+------+-------+--------+

只要把 s_no 和 c_no 替換成 student 和 course 表中對(duì)應(yīng)的 name 字段值就好了。

首先把 s_no 替換成 student 表中的 name 字段：

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 |
+-----------+-------+--------+

再把c_no替換成course表中的name字段：

-- 課程表
SELECT no, name FROM course;
+-------+-----------------+
| no | name |
+-------+-----------------+
| 3-105 | 計(jì)算機(jī)導(dǎo)論 |
| 3-245 | 操作系統(tǒng) |
| 6-166 | 數(shù)字電路 |
| 9-888 | 高等數(shù)學(xué) |
+-------+-----------------+

-- 由于字段名存在重復(fù)，使用 "表名.字段名 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;

• 子查詢加分組求平均分

查詢95031班學(xué)生每門課程的平均成績(jī)。

在score表中根據(jù)student表的學(xué)生編號(hào)篩選出學(xué)生的課堂號(hào)和成績(jī)：

-- IN (..): 將篩選出的學(xué)生號(hào)當(dāng)做 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 |
+------+-------+--------+

這時(shí)只要將c_no分組一下就能得出95031班學(xué)生每門課的平均成績(jī)：

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 |
+-------+-------------+

• 子查詢 - 1

查詢?cè)?3-105 課程中，所有成績(jī)高于 109 號(hào)同學(xué)的記錄。

先用子查詢查找出109同學(xué)在3-105中的成績(jī)

select * from score
where c_no ='3-105' and s_no='109'

然后再以課程3-105為條件，查找成績(jī)大76的記錄

select * from score
where c_no = '3-105'
and degree>
(select degree from score
where c_no = '3-105' and s_no='109');

• 子查詢 - 2

查詢所有成績(jī)高于 109 號(hào)同學(xué)的 3-105 課程成績(jī)記錄。

-- 不限制課程號(hào)，只要成績(jī)大于109號(hào)同學(xué)的3-105課程成績(jī)就可以。
SELECT * FROM score
WHERE degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');

• YEAR 函數(shù)與帶 IN 關(guān)鍵字查詢

查詢所有和 101 、108 號(hào)學(xué)生同年出生的 no 、name 、birthday 列。

select no, name, birthday from student
where year(birthday) in (
select year(birthday) from student
where no in (101,108));

• 多層嵌套子查詢

查詢 '張旭' 教師任課的學(xué)生成績(jī)表。

用的三張表teacher、course、score，首先找到教師編號(hào)：

SELECT NO FROM teacher WHERE NAME = '張旭'

通過sourse表找到該教師課程號(hào)：

select no from course
where t_no = (SELECT NO FROM teacher WHERE NAME = '張旭')

通過篩選出的課程號(hào)查詢成績(jī)表：

select * from score where c_no = (
select no from course where t_no = (
select no from teacher
where name = '張旭'));

• 多表查詢

查詢某選修課程多于5個(gè)同學(xué)的教師姓名。

首先在teacher表中，根據(jù)no字段來判斷該教師的同一門課程是否有至少5名學(xué)員選修：

-- 查詢 teacher 表
SELECT no, name FROM teacher;
+-----+--------+
| no | name |
+-----+--------+
| 804 | 李誠(chéng) |
| 825 | 王萍 |
| 831 | 劉冰 |
| 856 | 張旭 |
+-----+--------+

SELECT name FROM teacher WHERE no IN (
-- 在這里找到對(duì)應(yīng)的條件
);

查看和教師編號(hào)有有關(guān)的表的信息：

SELECT * FROM course;
-- t_no: 教師編號(hào)
+-------+-----------------+------+
| no | name | t_no |
+-------+-----------------+------+
| 3-105 | 計(jì)算機(jī)導(dǎo)論 | 825 |
| 3-245 | 操作系統(tǒng) | 804 |
| 6-166 | 數(shù)字電路 | 856 |
| 9-888 | 高等數(shù)學(xué) | 831 |
+-------+-----------------+------+

我們已經(jīng)找到和教師編號(hào)有關(guān)的字段就在course表中，但是還無法知道哪門課程至少有5名學(xué)生選修，所以還需要根據(jù)score表來查詢：

-- 在此之前向 score 插入一些數(shù)據(jù)，以便豐富查詢條件。
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 |
+------+-------+--------+

-- 在 score 表中將 c_no 作為分組，并且限制 c_no 持有至少 5 條數(shù)據(jù)。
SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5;
+-------+
| c_no |
+-------+
| 3-105 |
+-------+

根據(jù)篩選出來的課程號(hào)，找出在某課程中，擁有至少5名學(xué)員的教師編號(hào)：

SELECT t_no FROM course WHERE no IN (
SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*) > 5
);
+------+
| t_no |
+------+
| 825 |
+------+在teacher表中，根據(jù)篩選出來的教師編號(hào)找到教師姓名：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
)
);

• 子查詢 - 3

查詢 “計(jì)算機(jī)系” 課程的成績(jī)表。

思路是，先找出teacher表中所有計(jì)算機(jī)系課程的編號(hào)，根據(jù)這個(gè)編號(hào)查詢course表中的課程編號(hào)，再用課程編號(hào)查找score表

-- 通過 teacher 表查詢所有 `計(jì)算機(jī)系` 的教師編號(hào)
SELECT no, name, department FROM teacher WHERE department = '計(jì)算機(jī)系'
+-----+--------+--------------+
| no | name | department |
+-----+--------+--------------+
| 804 | 李誠(chéng) | 計(jì)算機(jī)系 |
| 825 | 王萍 | 計(jì)算機(jī)系 |
+-----+--------+--------------+

-- 通過 course 表查詢?cè)摻處煹恼n程編號(hào)
SELECT no FROM course WHERE t_no IN (
SELECT no FROM teacher WHERE department = '計(jì)算機(jī)系'
);
+-------+
| no |
+-------+
| 3-245 |
| 3-105 |
+-------+

-- 根據(jù)篩選出來的課程號(hào)查詢成績(jī)表
SELECT * FROM score WHERE c_no IN (
SELECT no FROM course WHERE t_no IN (
SELECT no FROM teacher WHERE department = '計(jì)算機(jī)系'
)
);
+------+-------+--------+
| 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 |
+------+-------+--------+

• UNION 和 NOT IN 的使用

查詢計(jì)算機(jī)系與電子工程系中的不同職稱的教師。

+-----+------+-----+------------+------------+------------+
| no | name | sex | birthday | profession | department |
+-----+------+-----+------------+------------+------------+
| 804 | 李誠(chéng) | 男 | 1958-12-02 | 副教授 | 計(jì)算機(jī)系 |
| 825 | 王萍 | 女 | 1972-05-05 | 助教 | 計(jì)算機(jī)系 |
| 831 | 劉冰 | 女 | 1977-08-14 | 助教 | 電子工程系 |
| 856 | 張旭 | 男 | 1969-03-12 | 講師 | 電子工程系 |
+-----+------+-----+------------+------------+------------+

-- NOT: 代表邏輯非
SELECT * FROM teacher WHERE department = '計(jì)算機(jī)系' AND profession NOT IN (
SELECT profession FROM teacher WHERE department = '電子工程系'
)
-- 合并兩個(gè)集
UNION
SELECT * FROM teacher WHERE department = '電子工程系' AND profession NOT IN (
SELECT profession FROM teacher WHERE department = '計(jì)算機(jī)系'
);

• ANY 表示至少一個(gè) - DESC ( 降序 )

查詢課程 3-105 且成績(jī) 至少 高于 3-245 的 score 表。

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 |
+------+-------+--------+

-- ANY: 符合SQL語句中的任意條件。
-- 也就是說，在 3-105 成績(jī)中，只要有一個(gè)大于從 3-245 篩選出來的任意行就符合條件，
-- 最后根據(jù)降序查詢結(jié)果。
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 |
+------+-------+--------+

• 表示所有的 ALL

查詢課程 3-105 且成績(jī)高于 3-245 的 score 表。

-- 只需對(duì)上一道題稍作修改。
-- ALL: 符合SQL語句中的所有條件。
-- 也就是說，在 3-105 每一行成績(jī)中，都要大于從 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 |
+------+-------+--------+

• 復(fù)制表的數(shù)據(jù)作為條件查詢

查詢某課程成績(jī)比該課程平均成績(jī)低的score表。

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

-- 查詢 score 表
select * from score;
a表
+------+-------+--------+
| 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 |
+------+-------+--------+

-- 將表 b 作用于表 a 中查詢數(shù)據(jù)
-- score a (b): 將表聲明為 a (b)，
-- 如此就能用 a.c_no = b.c_no 作為條件執(zhí)行查詢了。
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 |
+------+-------+--------+

• 子查詢 - 4

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

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

• 條件加組篩選

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

-- 查看學(xué)生表信息
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 * from student where sex='男'
group by class
having count(class)>=2；
+-----+------+-----+------------+-------+
| no | name | sex | birthday | class |
+-----+------+-----+------------+-------+
| 101 | 曾華 | 男 | 1977-09-01 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 | 95031 |
+-----+------+-----+------------+-------+

• NOT LIKE 模糊查詢?nèi)》?/strong>

-- NOT: 取反
-- LIKE: 模糊查詢
mysql> 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 |
+-----+-----------+-----+------------+-------+

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

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

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

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

-- 找出最高成績(jī)（該查詢只能有一個(gè)結(jié)果）
SELECT MAX(degree) FROM score;

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

select name from student where sex = (
SELECT sex FROM student where name='李軍'
);
+--------+
| 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 |
+-----------+-----+-------+

SELECT * FROM score WHERE c_no = (
SELECT no FROM course WHERE name = '計(jì)算機(jī)導(dǎo)論'
) 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 |
+------+-------+--------+

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 |
+------+------+-------+

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 |
+------+-------+-------+

CREATE DATABASE testJoin;

CREATE TABLE person (
id INT,
name VARCHAR(20),
cardId INT
);

CREATE TABLE card (
id INT,
name VARCHAR(20)
);

INSERT INTO card VALUES (1, '飯卡'), (2, '建行卡'), (3, '農(nóng)行卡'), (4, '工商卡'), (5, '郵政卡');
SELECT * FROM card;
+------+-----------+
| id | name |
+------+-----------+
| 1 | 飯卡 |
| 2 | 建行卡 |
| 3 | 農(nóng)行卡 |
| 4 | 工商卡 |
| 5 | 郵政卡 |
+------+-----------+

INSERT INTO person VALUES (1, '張三', 1), (2, '李四', 3), (3, '王五', 6);
SELECT * FROM person;
+------+--------+--------+
| id | name | cardId |
+------+--------+--------+
| 1 | 張三 | 1 |
| 2 | 李四 | 3 |
| 3 | 王五 | 6 |
+------+--------+--------+

-- INNER JOIN: 表示為內(nèi)連接，將兩張表拼接在一起。
-- on: 表示要執(zhí)行某個(gè)條件。
SELECT * FROM person INNER JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 張三 | 1 | 1 | 飯卡 |
| 2 | 李四 | 3 | 3 | 農(nóng)行卡 |
+------+--------+--------+------+-----------+

-- 將 INNER 關(guān)鍵字省略掉，結(jié)果也是一樣的。
-- SELECT * FROM person JOIN card on person.cardId = card.id;注意：card 的整張表被連接到了右邊。

-- LEFT JOIN 也叫做 LEFT OUTER JOIN，用這兩種方式的查詢結(jié)果是一樣的。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 張三 | 1 | 1 | 飯卡 |
| 2 | 李四 | 3 | 3 | 農(nóng)行卡 |
| 3 | 王五 | 6 | | |
+------+--------+--------+------+-----------+

SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 張三 | 1 | 1 | 飯卡 |
| 2 | 李四 | 3 | 3 | 農(nóng)行卡 |
| | | | 2 | 建行卡 |
| | | | 4 | 工商卡 |
| | | | 5 | 郵政卡 |
+------+--------+--------+------+-----------+

-- MySQL 不支持這種語法的全外連接
-- SELECT * FROM person FULL JOIN card on person.cardId = card.id;
-- 出現(xiàn)錯(cuò)誤：
-- ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'

-- MySQL全連接語法，使用 UNION 將兩張表合并在一起。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id
UNION
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id | name | cardId | id | name |
+------+--------+--------+------+-----------+
| 1 | 張三 | 1 | 1 | 飯卡 |
| 2 | 李四 | 3 | 3 | 農(nóng)行卡 |
| 3 | 王五 | 6 | | |
| | | | 2 | 建行卡 |
| | | | 4 | 工商卡 |
| | | | 5 | 郵政卡 |
+------+--------+--------+------+-----------+