MySQL學習筆記

登入和退出MySQL伺服器

# 登入MySQL
$ mysql -uroot -proot

# 退出MySQL資料庫伺服器
exit;

基本文法

-- 顯示所有資料庫
show databases;

-- 建立資料庫
CREATE DATABASE test;

-- 切換資料庫
use test;

-- 顯示資料庫中的所有表
show tables;

-- 建立資料表
CREATE TABLE pet (
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    death DATE
);

-- 檢視資料表結構
-- describe pet;
desc pet;

-- 查詢表
SELECT * from pet;

-- 插入資料
INSERT INTO pet VALUES ('puffball', 'Diane', 'hamster', 'f', '1990-03-30', NULL);

-- 修改資料
UPDATE pet SET name = 'squirrel' where owner = 'Diane';

-- 删除資料
DELETE FROM pet where name = 'squirrel';

-- 删除表
DROP TABLE myorder;

建表限制

主鍵限制

-- 主鍵限制
-- 使某個字段不重複且不得為空，確定表内所有資料的唯一性。
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- 聯合主鍵
-- 聯合主鍵中的每個字段都不能為空，聯合主鍵隻要所有的字段都不是相同的情況下可以允許其中的字段重複。
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY KEY(id, name)
);

-- 自增限制
-- 自增限制的主鍵由系統自動遞增配置設定。
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

-- 添加主鍵限制
-- 如果忘記設定主鍵，還可以通過SQL語句設定（兩種方式）：
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;

-- 删除主鍵
ALTER TABLE user drop PRIMARY KEY;

唯一限制

-- 建表時建立唯一主鍵
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    UNIQUE(name)
);

-- 添加唯一主鍵
-- 如果建表時沒有設定唯一主鍵，還可以通過SQL語句設定（兩種方式）：
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

-- 删除唯一主鍵
ALTER TABLE user DROP INDEX name;

非空限制

-- 建表時添加非空限制
-- 限制某個字段不能為空
CREATE TABLE user (
    id INT,
    name VARCHAR(20) NOT NULL
);

-- 移除非空限制
ALTER TABLE user MODIFY name VARCHAR(20);

預設限制

-- 建表時添加預設限制
-- 限制某個字段的預設值
CREATE TABLE user2 (
    id INT,
    name VARCHAR(20),
    age INT DEFAULT 10
);

-- 移除非空限制
ALTER TABLE user MODIFY age INT;

外鍵限制

-- 班級
CREATE TABLE classes (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- 學生表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    -- 這裡的 class_id 要和 classes 中的 id 字段相關聯
    class_id INT,
    -- 表示 class_id 的值必須來自于 classes 中的 id 字段值
    FOREIGN KEY(class_id) REFERENCES classes(id)
);

-- 1. 主表（父表）classes 中沒有的資料值，在副表（子表）students 中，是不可以使用的；
-- 2. 主表中的記錄被副表引用時，主表不可以被删除。

資料庫的三大設計範式

1NF

隻要字段值還可以繼續拆分，就不滿足第一範式。

範式設計得越詳細，對某些實際操作可能會更好，但并非都有好處，需要對項目的實際情況進行設定。

2NF

在滿足第一範式的前提下，其他列都必須完全依賴于主鍵列。如果出現不完全依賴，隻可能發生在聯合主鍵的情況下：

-- 訂單表
CREATE TABLE myorder (
    product_id INT,
    customer_id INT,
    product_name VARCHAR(20),
    customer_name VARCHAR(20),
    PRIMARY KEY (product_id, customer_id)
);

實際上，在這張訂單表中，

product_name

隻依賴于

product_id

，

customer_name

隻依賴于

customer_id

。也就是說，

product_name

和

customer_id

是沒用關系的，

customer_name

和

product_id

也是沒有關系的。

這就不滿足第二範式：其他列都必須完全依賴于主鍵列！

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

拆分之後，

myorder

表中的

product_id

和

customer_id

完全依賴于

order_id

主鍵，而

product

和

customer

表中的其他字段又完全依賴于主鍵。滿足了第二範式的設計！

3NF

在滿足第二範式的前提下，除了主鍵列之外，其他列之間不能有傳遞依賴關系。

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    customer_phone VARCHAR(15)
);

表中的

customer_phone

有可能依賴于

order_id

、

customer_id

兩列，也就不滿足了第三範式的設計：其他列之間不能有傳遞依賴關系。

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    phone VARCHAR(15)
);

修改後就不存在其他列之間的傳遞依賴關系，其他列都隻依賴于主鍵列，滿足了第三範式的設計！

查詢練習

準備資料

-- 建立資料庫
CREATE DATABASE select_test;
-- 切換資料庫
USE select_test;

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

-- 建立教師表
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 -- 部門
);

-- 建立課程表
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) 
);

-- 成績表
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)
);

-- 檢視所有表
SHOW TABLES;

-- 添加學生表資料
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');

-- 檢視表結構
SELECT * FROM course;
SELECT * FROM score;
SELECT * FROM student;
SELECT * FROM teacher;

1 到 10

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

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

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

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

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

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

-- 以 class 降序的方式查詢 student 表的所有行
-- DESC: 降序，從高到低
-- ASC（預設）: 升序，從低到高
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" 班的學生人數
-- COUNT: 統計
SELECT COUNT(*) FROM student WHERE class = '95031';

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

分組計算平均成績

查詢每門課的平均成績。

-- 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 名學生選修，并以 3 開頭的課程的平均分數。

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) 通過分組查詢出來
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 名學生選修的課程
-- HAVING: 表示持有
HAVING COUNT(c_no) >= 2

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

-- 把前面的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 |
+-------+-------------+----------+

多表查詢 - 1

查詢所有學生的

name

，以及該學生在

score

表中對應的

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

通過分析可以發現，隻要把

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

多表查詢 - 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 |
+------+-----------------+--------+

三表關聯查詢

查詢所有學生的

name

、課程名 (

course

表中的

name

) 和

degree

。

隻有

score

表中關聯學生的學号和課堂号，我們隻要圍繞着

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

表中對應的

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 | 計算機導論      |
| 3-245 | 作業系統        |
| 6-166 | 數字電路        |
| 9-888 | 高等數學        |
+-------+-----------------+

-- 由于字段名存在重複，使用 "表名.字段名 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;

子查詢加分組求平均分

查詢

班學生每門課程的平均成績。

在

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

這時隻要将

c_no

分組一下就能得出

班學生每門課的平均成績：

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

查詢在

3-105

課程中，所有成績高于

号同學的記錄。

首先篩選出課堂号為

3-105

，在找出所有成績高于

号同學的的行。

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

子查詢 - 2

查詢所有成績高于

号同學的

3-105

課程成績記錄。

-- 不限制課程号，隻要成績大于109号同學的3-105課程成績就可以。
SELECT * FROM score
WHERE degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');

YEAR 函數與帶 IN 關鍵字查詢

查詢所有和

、

号學生同年出生的

no

、

name

、

birthday

列。

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

多層嵌套子查詢

查詢

'張旭'

教師任課的學生成績表。

首先找到教師編号：

SELECT NO FROM teacher WHERE NAME = '張旭'

通過

sourse

表找到該教師課程号：

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

通過篩選出的課程号查詢成績表：

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

多表查詢

查詢某選修課程多于5個同學的教師姓名。

首先在

teacher

表中，根據

no

字段來判斷該教師的同一門課程是否有至少5名學員選修：

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

SELECT name FROM teacher WHERE no IN (
    -- 在這裡找到對應的條件
);

檢視和教師編号有有關的表的資訊：

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

我們已經找到和教師編号有關的字段就在

course

表中，但是還無法知道哪門課程至少有5名學生選修，是以還需要根據

score

表來查詢：

-- 在此之前向 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 |
+------+-------+--------+

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

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

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

在

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

子查詢 - 3

查詢 “計算機系” 課程的成績表。

思路是，先找出

course

表中所有

計算機系

課程的編号，然後根據這個編号查詢

score

表。

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

-- 根據篩選出來的課程号查詢成績表
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 |
+------+-------+--------+

UNION 和 NOTIN 的使用

查詢

計算機系

與

電子工程系

中的不同職稱的教師。

-- NOT: 代表邏輯非
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 = '計算機系'
);

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

查詢課程

3-105

且成績至少高于

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 成績中，隻要有一個大于從 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 |
+------+-------+--------+

表示所有的 ALL

查詢課程

3-105

且成績高于

3-245

的

score

表。

-- 隻需對上一道題稍作修改。
-- ALL: 符合SQL語句中的所有條件。
-- 也就是說，在 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 |
+------+-------+--------+

複制表的資料作為條件查詢

查詢某課程成績比該課程平均成績低的

score

表。

-- 查詢平均分
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 |
+-------+-------------+

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

-- 将表 b 作用于表 a 中查詢資料
-- score a (b): 将表聲明為 a (b)，
-- 如此就能用 a.c_no = b.c_no 作為條件執行查詢了。
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      |
+--------+-----------------+
| 李誠   | 計算機系        |
| 王萍   | 計算機系        |
| 劉冰   | 電子工程系      |
| 張旭   | 電子工程系      |
+--------+-----------------+

條件加組篩選

查詢

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

NOTLIKE 模糊查詢取反

查詢

student

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

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

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

MAX 與 MIN 函數

查詢

student

表中最大和最小的

birthday

值。

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

多段排序

以

class

和

birthday

從大到小的順序查詢

student

表（DESC代表降序，預設是ASC升序）。

SELECT * FROM student ORDER BY class DESC, birthday ASC;
+-----+-----------+-----+------------+-------+
| 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 |
+-----+-----------+-----+------------+-------+

子查詢 - 5

查詢 “男” 教師及其所上的課程。

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

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

子查詢 - 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 |
+-----------+-----+
| 曾華      | 男  |
| 匡明      | 男  |
| 李軍      | 男  |
| 陸軍      | 男  |
| 王尼瑪    | 男  |
| 張全蛋    | 男  |
| 趙鐵柱    | 男  |
| 張飛      | 男  |
+-----------+-----+

子查詢 - 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 |
+-----------+-----+-------+

子查詢 - 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 |
+------+-------+--------+

按等級查詢

建立一個

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

查詢所有學生的

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

連接配接查詢

SQL的四種連接配接查詢：

内連接配接：

inner join 或者 join；

外連接配接：

左連接配接：left join 或者 left outer join；

右連接配接：right join 或者 left outer join；

//完全外連接配接：full join 或者 full outer join；

準備用于測試連接配接查詢的資料：

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, '農行卡'), (4, '工商卡'), (5, '郵政卡');
SELECT * FROM card;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | 飯卡      |
|    2 | 建行卡    |
|    3 | 農行卡    |
|    4 | 工商卡    |
|    5 | 郵政卡    |
+------+-----------+

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

分析兩張表發現，

person

表并沒有為

cardId

字段設定一個在

card

表中對應的

id

外鍵。如果設定了的話，

person

中

cardId

字段值為

的行就插不進去，因為該

cardId

值在

card

表中并沒有。

内連接配接

要查詢這兩張表中有關系的資料，可以使用

INNER JOIN

( 内連接配接 ) 将它們連接配接在一起。

-- INNER JOIN: 表示為内連接配接，将兩張表拼接在一起。
-- on: 表示要執行某個條件。
SELECT * FROM person INNER JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 張三   |      1 |    1 | 飯卡      |
|    2 | 李四   |      3 |    3 | 農行卡    |
+------+--------+--------+------+-----------+

-- 内聯查詢，其實就是将兩張表中的資料，通過某個相等字段，查詢出相關記錄資料。

-- 将 INNER 關鍵字省略掉，結果也是一樣的。
-- SELECT * FROM person JOIN card on person.cardId = card.id;

注意： card 的整張表被連接配接到了右邊。

左外連接配接

完整顯示左邊的表 (

person

) ，右邊的表如果符合條件就顯示，不符合則補

NULL

。

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

右外連接配接

完整顯示右邊的表 (

card

) ，左邊的表如果符合條件就顯示，不符合則補

NULL

。

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

全外連接配接

完整顯示兩張表的全部資料。

-- MySQL 不支援這種文法的全外連接配接
-- SELECT * FROM person FULL JOIN card on person.cardId = card.id;
-- 出現錯誤：
-- 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 | 農行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 郵政卡    |
+------+--------+--------+------+-----------+

事務

在 MySQL 中，事務其實是一個最小的不可分割的工作單元。事務能夠保證一個業務的完整性。

比如我們的銀行轉賬：

-- a -> -100
UPDATE user set money = money - 100 WHERE name = 'a';

-- b -> +100
UPDATE user set money = money + 100 WHERE name = 'b';

在實際項目中，假設隻有一條 SQL 語句執行成功，而另外一條執行失敗了，就會出現資料前後不一緻。

是以，在執行多條有關聯 SQL 語句時，事務可能會要求這些 SQL 語句要麼同時執行成功，要麼都執行失敗。

如何控制事務 - COMMIT / ROLLBACK

在 MySQL 中，事務的自動送出狀态預設是開啟的。

-- 查詢事務的自動送出狀态
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+

自動送出的作用：當我們執行一條 SQL 語句的時候，其産生的效果就會立即展現出來，且不能復原。

什麼是復原？舉個例子：

CREATE DATABASE bank;

USE bank;

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    money INT
);

INSERT INTO user VALUES (1, 'a', 1000);

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

可以看到，在執行插入語句後資料立刻生效，原因是 MySQL 中的事務自動将它送出到了資料庫中。那麼所謂復原的意思就是，撤銷執行過的所有 SQL 語句，使其復原到最後一次送出資料時的狀态。

在 MySQL 中使用

ROLLBACK

執行復原：

-- 復原到最後一次送出
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

由于所有執行過的 SQL 語句都已經被送出過了，是以資料并沒有發生復原。那如何讓資料可以發生復原？

-- 關閉自動送出
SET AUTOCOMMIT = 0;

-- 查詢自動送出狀态
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+

将自動送出關閉後，測試資料復原：

INSERT INTO user VALUES (2, 'b', 1000);

-- 關閉 AUTOCOMMIT 後，資料的變化是在一張虛拟的臨時資料表中展示，
-- 發生變化的資料并沒有真正插入到資料表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

-- 資料表中的真實資料其實還是：
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

-- 由于資料還沒有真正送出，可以使用復原
ROLLBACK;

-- 再次查詢
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

那如何将虛拟的資料真正送出到資料庫中？使用

COMMIT

INSERT INTO user VALUES (2, 'b', 1000);
-- 手動送出資料（持久性），
-- 将資料真正送出到資料庫中，執行後不能再復原送出過的資料。
COMMIT;

-- 送出後測試復原
ROLLBACK;

-- 再次查詢（復原無效了）
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

總結

自動送出

檢視自動送出狀态： SELECT @@AUTOCOMMIT ；

設定自動送出狀态： SET AUTOCOMMIT = 0 。

手動送出 @@AUTOCOMMIT = 0 時，使用 COMMIT 指令送出事務。

事務復原 @@AUTOCOMMIT = 0 時，使用 ROLLBACK 指令復原事務。

事務的實際應用，讓我們再回到銀行轉賬項目：

-- 轉賬
UPDATE user set money = money - 100 WHERE name = 'a';

-- 到賬
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

這時假設在轉賬時發生了意外，就可以使用

ROLLBACK

復原到最後一次送出的狀态：

-- 假設轉賬發生了意外，需要復原。
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

這時我們又回到了發生意外之前的狀态，也就是說，事務給我們提供了一個可以反悔的機會。假設資料沒有發生意外，這時可以手動将資料真正送出到資料表中：

COMMIT

。

手動開啟事務 - BEGIN / START TRANSACTION

事務的預設送出被開啟 (

@@AUTOCOMMIT = 1

) 後，此時就不能使用事務復原了。但是我們還可以手動開啟一個事務處理事件，使其可以發生復原：

-- 使用 BEGIN 或者 START TRANSACTION 手動開啟一個事務
START TRANSACTION;
-- BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

-- 由于手動開啟的事務沒有開啟自動送出，
-- 此時發生變化的資料仍然是被儲存在一張臨時表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 測試復原
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

仍然使用

COMMIT

送出資料，送出後無法再發生本次事務的復原。

START TRANSACTION;
-- BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 送出資料
COMMIT;

-- 測試復原（無效，因為表的資料已經被送出）
ROLLBACK;

事務的 ACID 特征與使用

事務的四大特征：

**（1）原子性（Atomicity）**：原子性是指事務是一個不可分割的工作機關，事務中的操作要麼都發生，要麼都不發生；
**（2）一緻性（Consistency）**：事務前後資料的完整性必須保持一緻；
**（3）隔離性（Isolation）**：事務的隔離性是多個使用者并發通路資料庫時，資料庫為每一個使用者開啟的事務，不能被其他事務的操作資料所幹擾，多個并發事務之間要互相隔離；
**（4）持久性（Durability）**：持久性是指一個事務一旦被送出，它對資料庫中資料的改變就是永久性的，接下來即使資料庫發生故障也不應該對其有任何影響。

事務的隔離性

事務的隔離性可分為四種 ( 性能從低到高 ) ：

READ UNCOMMITTED ( 讀取未送出 )

如果有多個事務，那麼任意事務都可以看見其他事務的未送出資料；
READ COMMITTED ( 讀取已送出 )

隻能讀取到其他事務已經送出的資料；
REPEATABLE READ ( 可被重複讀 )

如果有多個連接配接都開啟了事務，那麼事務之間不能共享資料記錄，否則隻能共享已送出的記錄；
SERIALIZABLE ( 串行化 )

所有的事務都會按照固定順序執行，執行完一個事務後再繼續執行下一個事務的寫入操作。

檢視目前資料庫的預設隔離級别：

-- MySQL 8.x, GLOBAL 表示系統級别，不加表示會話級别。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                | -- MySQL的預設隔離級别，可以重複讀。
+--------------------------------+

-- MySQL 5.x
SELECT @@GLOBAL.TX_ISOLATION;
SELECT @@TX_ISOLATION;

修改隔離級别：

-- 設定系統隔離級别，LEVEL 後面表示要設定的隔離級别 (READ UNCOMMITTED)。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 查詢系統隔離級别，發現已經被修改。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+

髒讀

測試 READ UNCOMMITTED ( 讀取未送出 ) 的隔離性：

INSERT INTO user VALUES (3, '小明', 1000);
INSERT INTO user VALUES (4, '淘寶店', 1000);

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘寶店    |  1000 |
+----+-----------+-------+

-- 開啟一個事務操作資料
-- 假設小明在淘寶店買了一雙800塊錢的鞋子：
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘寶店';

-- 然後淘寶店在另一方查詢結果，發現錢已到賬。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘寶店    |  1800 |
+----+-----------+-------+

由于小明的轉賬是在新開啟的事務上進行操作的，而該操作的結果是可以被其他事務（另一方的淘寶店）看見的，是以淘寶店的查詢結果是正确的，淘寶店确認到賬。但就在這時，如果小明在它所處的事務上又執行了

ROLLBACK

指令，會發生什麼？

-- 小明所處的事務
ROLLBACK;

-- 此時無論對方是誰，如果再去查詢結果就會發現：
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘寶店    |  1000 |
+----+-----------+-------+

這就是所謂的髒讀，一個事務讀取到另外一個事務還未送出的資料。這在實際開發中是不允許出現的。

讀取已送出

把隔離級别設定為 READ COMMITTED ：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+

這樣，再有新的事務連接配接進來時，它們就隻能查詢到已經送出過的事務資料了。但是對于目前事務來說，它們看到的還是未送出的資料，例如：

-- 正在操作資料事務（目前事務）
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘寶店';

-- 雖然隔離級别被設定為了 READ COMMITTED，但在目前事務中，
-- 它看到的仍然是資料表中臨時改變資料，而不是真正送出過的資料。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘寶店    |  1800 |
+----+-----------+-------+


-- 假設此時在遠端開啟了一個新事務，連接配接到資料庫。
$ mysql -u root -p12345612

-- 此時遠端連接配接查詢到的資料隻能是已經送出過的
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘寶店    |  1000 |
+----+-----------+-------+

但是這樣還有問題，那就是假設一個事務在操作資料時，其他事務幹擾了這個事務的資料。例如：

-- 小張在查詢資料的時候發現：
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘寶店    |  1800 |
+----+-----------+-------+

-- 在小張求表的 money 平均值之前，小王做了一個操作：
START TRANSACTION;
INSERT INTO user VALUES (5, 'c', 100);
COMMIT;

-- 此時表的真實資料是：
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘寶店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+

-- 這時小張再求平均值的時候，就會出現計算不相符合的情況：
SELECT AVG(money) FROM user;
+------------+
| AVG(money) |
+------------+
|  820.0000  |
+------------+

雖然 READ COMMITTED 讓我們隻能讀取到其他事務已經送出的資料，但還是會出現問題，就是在讀取同一個表的資料時，可能會發生前後不一緻的情況。這被稱為不可重複讀現象 ( READ COMMITTED ) 。

幻讀

将隔離級别設定為 REPEATABLE READ ( 可被重複讀取 ) :

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+

測試 REPEATABLE READ ，假設在兩個不同的連接配接上分别執行

START TRANSACTION

-- 小張 - 成都
START TRANSACTION;
INSERT INTO user VALUES (6, 'd', 1000);

-- 小王 - 北京
START TRANSACTION;

-- 小張 - 成都
COMMIT;

目前事務開啟後，沒送出之前，查詢不到，送出後可以被查詢到。但是，在送出之前其他事務被開啟了，那麼在這條事務線上，就不會查詢到目前有操作事務的連接配接。相當于開辟出一條單獨的線程。

無論小張是否執行過

COMMIT

，在小王這邊，都不會查詢到小張的事務記錄，而是隻會查詢到自己所處事務的記錄：

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘寶店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+

這是因為小王在此之前開啟了一個新的事務 (

START TRANSACTION

) ，那麼在他的這條新事務的線上，跟其他事務是沒有聯系的，也就是說，此時如果其他事務正在操作資料，它是不知道的。

然而事實是，在真實的資料表中，小張已經插入了一條資料。但是小王此時并不知道，也插入了同一條資料，會發生什麼呢？

INSERT INTO user VALUES (6, 'd', 1000);
-- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'

報錯了，操作被告知已存在主鍵為

的字段。這種現象也被稱為幻讀，一個事務送出的資料，不能被其他事務讀取到。

串行化

顧名思義，就是所有事務的寫入操作全都是串行化的。什麼意思？把隔離級别修改成 SERIALIZABLE :

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+

還是拿小張和小王來舉例：

-- 小張 - 成都
START TRANSACTION;

-- 小王 - 北京
START TRANSACTION;

-- 開啟事務之前先查詢表，準備操作資料。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘寶店    |  1000 |
|  5 | c         |   100 |
|  6 | d         |  1000 |
+----+-----------+-------+

-- 發現沒有 7 号王小花，于是插入一條資料：
INSERT INTO user VALUES (7, '王小花', 1000);

此時會發生什麼呢？由于現在的隔離級别是 SERIALIZABLE ( 串行化 ) ，串行化的意思就是：假設把所有的事務都放在一個串行的隊列中，那麼所有的事務都會按照固定順序執行，執行完一個事務後再繼續執行下一個事務的寫入操作 ( 這意味着隊列中同時隻能執行一個事務的寫入操作 ) 。

根據這個解釋，小王在插入資料時，會出現等待狀态，直到小張執行

COMMIT

結束它所處的事務，或者出現等待逾時。

MySQL學習筆記MySQL學習筆記

MySQL學習筆記

登入和退出MySQL伺服器

基本文法

建表限制

主鍵限制

唯一限制

非空限制

預設限制

外鍵限制

資料庫的三大設計範式

1NF

2NF

3NF

查詢練習

準備資料

1 到 10

分組計算平均成績

分組條件與模糊查詢

多表查詢 - 1

多表查詢 - 2

三表關聯查詢

子查詢加分組求平均分

子查詢 - 1

子查詢 - 2

YEAR 函數與帶 IN 關鍵字查詢

多層嵌套子查詢

多表查詢

子查詢 - 3

UNION 和 NOTIN 的使用

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

表示所有的 ALL

複制表的資料作為條件查詢

子查詢 - 4

條件加組篩選

NOTLIKE 模糊查詢取反

YEAR 與 NOW 函數

MAX 與 MIN 函數

多段排序

子查詢 - 5

MAX 函數與子查詢

子查詢 - 6

子查詢 - 7

子查詢 - 8

按等級查詢

連接配接查詢

内連接配接

左外連接配接

右外連接配接

全外連接配接

事務

如何控制事務 - COMMIT / ROLLBACK

手動開啟事務 - BEGIN / START TRANSACTION

事務的 ACID 特征與使用

事務的隔離性

髒讀

讀取已送出

幻讀

串行化

繼續閱讀