本文實例總結了MySQL必備的常見知識點。分享給大家供大家參考，具體如下：

最近在整理 sql 的時候發現一份優秀的筆記，是原作者學習 sql 所做的筆記，分享這份總結給大家，對大家對 sql 的可以來一次全方位的檢漏和排查，感謝原作者 hjzCy 的付出，原文鏈接放在文章最下方，如果出現錯誤，希望大家共同指出！

登錄和退出 MySQL 服務器

# 登錄MySQL$ mysql -u root -p12345612# 退出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_noHAVING 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, scoreWHERE 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, courseWHERE 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 和 srouse 表中對應的 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, degreeFROM student, score, courseWHERE student.NO = score.s_noAND score.c_no = course.no;子查詢加分組求平均分

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

在 score 表中根據 student 表的學生編號篩選出學生的課堂號和成績：

-- IN (..): 將篩選出的學生號當做 s_no 的條件查詢SELECT s_no, c_no, degree FROM scoreWHERE 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 分組一下就能得出 95031 班學生每門課的平均成績：

SELECT c_no, AVG(degree) FROM scoreWHERE 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 課程中，所有成績高于 109 號同學的記錄。

首先篩選出課堂號為 3-105 ，在找出所有成績高于 109 號同學的的行。

SELECT * FROM scoreWHERE c_no = ’3-105’AND degree > (SELECT degree FROM score WHERE s_no = ’109’ AND c_no = ’3-105’);子查詢 - 2

查詢所有成績高于 109 號同學的 3-105 課程成績記錄。

-- 不限制課程號，只要成績大于109號同學的3-105課程成績就可以。SELECT * FROM scoreWHERE degree > (SELECT degree FROM score WHERE s_no = ’109’ AND c_no = ’3-105’);YEAR 函數與帶 IN 關鍵字查詢

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

-- YEAR(..): 取出日期中的年份SELECT no, name, birthday FROM studentWHERE 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 = ( 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 = ’電子工程系’)-- 合并兩個集UNIONSELECT * FROM teacher WHERE department = ’電子工程系’ AND profession NOT IN ( SELECT profession FROM teacher WHERE department = ’計算機系’);ANY 表示至少一個 - DESC ( 降序 )

查詢課程 3-105 且成績 <u>至少</u> 高于 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 表。

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 |+-----+-----------+-----+------------+-------+子查詢 - 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 和 sexSELECT 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, gradeWHERE 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, ’農行卡’), (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 字段值為 6 的行就插不進去，因為該 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.idUNIONSELECT * 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 -> -100UPDATE user set money = money - 100 WHERE name = ’a’;-- b -> +100UPDATE 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 提交數據，提交后無法再發生本次事務的回滾。

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 特征與使用

事務的四大特征：

A 原子性：事務是最小的單位，不可以再分割； C 一致性：要求同一事務中的 SQL 語句，必須保證同時成功或者失敗； I 隔離性：事務 1 和 事務 2 之間是具有隔離性的； D 持久性：事務一旦結束 ( COMMIT ) ，就不可以再返回了 ( ROLLBACK ) 。事務的隔離性

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

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.xSELECT @@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’

報錯了，操作被告知已存在主鍵為 6 的字段。這種現象也被稱為幻讀，一個事務提交的數據，不能被其他事務讀取到。

串行化

顧名思義，就是所有事務的寫入操作全都是串行化的。什么意思？把隔離級別修改成 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日志操作技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數據庫鎖相關技巧匯總》

希望本文所述對大家MySQL數據庫計有所幫助。