目錄
- 主鍵約束
- 唯一主鍵
- 非空約束
- 默認(rèn)約束
- 外鍵約束
- 1NF
- 2NF
- 3NF
- 準(zhǔn)備數(shù)據(jù)
- 1 到 10
- 分組計算平均成績
- 分組條件與模糊查詢
- 多表查詢 - 1
- 多表查詢 - 2
- 三表關(guān)聯(lián)查詢
- 子查詢加分組求平均分
- 子查詢 - 1
- 子查詢 - 2
- YEAR 函數(shù)與帶 IN 關(guān)鍵字查詢
- 多層嵌套子查詢
- 多表查詢
- 子查詢 - 3
- UNION 和 NOTIN 的使用
- ANY 表示至少一個 - DESC ( 降序 )
- 表示所有的 ALL
- 復(fù)制表的數(shù)據(jù)作為條件查詢
- 子查詢 - 4
- 條件加組篩選
- NOTLIKE 模糊查詢?nèi)》?/li>
- YEAR 與 NOW 函數(shù)
- MAX 與 MIN 函數(shù)
- 多段排序
- 子查詢 - 5
- MAX 函數(shù)與子查詢
- 子查詢 - 6
- 子查詢 - 7
- 子查詢 - 8
- 按等級查詢
- 連接查詢
- 如何控制事務(wù) - COMMIT / ROLLBACK
- 手動開啟事務(wù) - BEGIN / START TRANSACTION
- 事務(wù)的 ACID 特征與使用
- 事務(wù)的隔離性
本文實例總結(jié)了MySQL必備的常見知識點。分享給大家供大家參考,具體如下:
最近在整理 sql 的時候發(fā)現(xiàn)一份優(yōu)秀的筆記,是原作者學(xué)習(xí) sql 所做的筆記,分享這份總結(jié)給大家,對大家對 sql 的可以來一次全方位的檢漏和排查,感謝原作者 hjzCy 的付出,原文鏈接放在文章最下方,如果出現(xiàn)錯誤,希望大家共同指出!
登錄和退出 MySQL 服務(wù)器
# 登錄MySQL
$ mysql -u root -p12345612
# 退出MySQL數(shù)據(jù)庫服務(wù)器
exit;
基本語法
-- 顯示所有數(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', NULL);
-- 修改數(shù)據(jù)
UPDATE pet SET name = 'squirrel' where owner = 'Diane';
-- 刪除數(shù)據(jù)
DELETE FROM pet where name = 'squirrel';
-- 刪除表
DROP TABLE myorder;
建表約束
主鍵約束
-- 主鍵約束
-- 使某個字段不重復(fù)且不得為空,確保表內(nèi)所有數(shù)據(jù)的唯一性。
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(20)
);
-- 聯(lián)合主鍵
-- 聯(lián)合主鍵中的每個字段都不能為空,并且加起來不能和已設(shè)置的聯(lián)合主鍵重復(fù)。
CREATE TABLE user (
id INT,
name VARCHAR(20),
password VARCHAR(20),
PRIMARY KEY(id, name)
);
-- 自增約束
-- 自增約束的主鍵由系統(tǒng)自動遞增分配。
CREATE TABLE user (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
-- 添加主鍵約束
-- 如果忘記設(shè)置主鍵,還可以通過SQL語句設(shè)置(兩種方式):
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;
-- 刪除主鍵
ALTER TABLE user drop PRIMARY KEY;
唯一主鍵
-- 建表時創(chuàng)建唯一主鍵
CREATE TABLE user (
id INT,
name VARCHAR(20),
UNIQUE(name)
);
-- 添加唯一主鍵
-- 如果建表時沒有設(shè)置唯一建,還可以通過SQL語句設(shè)置(兩種方式):
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);
默認(rèn)約束
-- 建表時添加默認(rèn)約束
-- 約束某個字段的默認(rèn)值
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)
);
-- 學(xué)生表
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(20),
-- 這里的 class_id 要和 classes 中的 id 字段相關(guān)聯(lián)
class_id INT,
-- 表示 class_id 的值必須來自于 classes 中的 id 字段值
FOREIGN KEY(class_id) REFERENCES classes(id)
);
-- 1. 主表(父表)classes 中沒有的數(shù)據(jù)值,在副表(子表)students 中,是不可以使用的;
-- 2. 主表中的記錄被副表引用時,主表不可以被刪除。
數(shù)據(jù)庫的三大設(shè)計范式
1NF
只要字段值還可以繼續(xù)拆分,就不滿足第一范式。
范式設(shè)計得越詳細(xì),對某些實際操作可能會更好,但并非都有好處,需要對項目的實際情況進(jìn)行設(shè)定。
2NF
在滿足第一范式的前提下,其他列都必須完全依賴于主鍵列。如果出現(xiàn)不完全依賴,只可能發(fā)生在聯(lián)合主鍵的情況下:
-- 訂單表
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
是沒用關(guān)系的,customer_name
和 product_id
也是沒有關(guān)系的。
這就不滿足第二范式:其他列都必須完全依賴于主鍵列!
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
表中的其他字段又完全依賴于主鍵。滿足了第二范式的設(shè)計!
3NF
在滿足第二范式的前提下,除了主鍵列之外,其他列之間不能有傳遞依賴關(guān)系。
CREATE TABLE myorder (
order_id INT PRIMARY KEY,
product_id INT,
customer_id INT,
customer_phone VARCHAR(15)
);
表中的 customer_phone
有可能依賴于 order_id
、 customer_id
兩列,也就不滿足了第三范式的設(shè)計:其他列之間不能有傳遞依賴關(guān)系。
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)
);
修改后就不存在其他列之間的傳遞依賴關(guān)系,其他列都只依賴于主鍵列,滿足了第三范式的設(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 NULL,
sex VARCHAR(10) NOT NULL,
birthday DATE, -- 生日
class VARCHAR(20) -- 所在班級
);
-- 創(chuàng)建教師表
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 -- 部門
);
-- 創(chuàng)建課程表
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, -- 學(xué)生編號
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),
-- 設(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', '李誠', '男', '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', '助教', '電子工程系');
-- 添加課程表數(shù)據(jù)
INSERT INTO course VALUES('3-105', '計算機導(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');
-- 添加添加成績表數(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;
1 到 10
-- 查詢 student 表的所有行
SELECT * FROM student;
-- 查詢 student 表中的 name、sex 和 class 字段的所有行
SELECT name, sex, class FROM student;
-- 查詢 teacher 表中不重復(fù)的 department 列
-- department: 去重查詢
SELECT DISTINCT department FROM teacher;
-- 查詢 score 表中成績在60-80之間的所有行(區(qū)間查詢和運算符查詢)
-- 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 表中成績?yōu)?85, 86 或 88 的行
-- IN: 查詢規(guī)定中的多個值
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)計
SELECT COUNT(*) FROM student WHERE class = '95031';
-- 查詢 score 表中的最高分的學(xué)生學(xué)號和課程編號(子查詢或排序查詢)。
-- (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;
分組計算平均成績
查詢每門課的平均成績。
-- 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ù)。
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 |
+------+-------+--------+
分析表發(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 表示模糊查詢,"%" 是一個通配符,匹配 "3" 后面的任意字符。
AND c_no LIKE '3%';
-- 把前面的SQL語句拼接起來,
-- 后面加上一個 COUNT(*),表示將每個分組的個數(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
表中對應(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
表中對應(yīng)的 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
查詢所有學(xué)生的 no
、課程名稱 ( course
表中的 name
) 和成績 ( 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
表:
+-------+-----------------+
| no | name |
+-------+-----------------+
| 3-105 | 計算機導(dǎo)論 |
| 3-245 | 操作系統(tǒng) |
| 6-166 | 數(shù)字電路 |
| 9-888 | 高等數(shù)學(xué) |
+-------+-----------------+
只要把 score
表中的 c_no
替換成 course
表中對應(yīng)的 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 | 計算機導(dǎo)論 | 92 |
| 105 | 計算機導(dǎo)論 | 88 |
| 109 | 計算機導(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é)號和課堂號,我們只要圍繞著 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
表中對應(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 | 計算機導(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é)生每門課程的平均成績。
在 score
表中根據(jù) student
表的學(xué)生編號篩選出學(xué)生的課堂號和成績:
-- IN (..): 將篩選出的學(xué)生號當(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 |
+------+-------+--------+
這時只要將 c_no
分組一下就能得出 95031
班學(xué)生每門課的平均成績:
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
課程中,所有成績高于 109
號同學(xué)的記錄。
首先篩選出課堂號為 3-105
,在找出所有成績高于 109
號同學(xué)的的行。
SELECT * FROM score
WHERE c_no = '3-105'
AND degree > (SELECT degree FROM score WHERE s_no = '109' AND c_no = '3-105');
子查詢 - 2
查詢所有成績高于 109
號同學(xué)的 3-105
課程成績記錄。
-- 不限制課程號,只要成績大于109號同學(xué)的3-105課程成績就可以。
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
號學(xué)生同年出生的 no
、name
、birthday
列。
-- YEAR(..): 取出日期中的年份
SELECT no, name, birthday FROM student
WHERE YEAR(birthday) IN (SELECT YEAR(birthday) FROM student WHERE no IN (101, 108));
多層嵌套子查詢
查詢 '張旭'
教師任課的學(xué)生成績表。
首先找到教師編號:
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 個同學(xué)的教師姓名。
首先在 teacher
表中,根據(jù) no
字段來判斷該教師的同一門課程是否有至少 5 名學(xué)員選修:
-- 查詢 teacher 表
SELECT no, name FROM teacher;
+-----+--------+
| no | name |
+-----+--------+
| 804 | 李誠 |
| 825 | 王萍 |
| 831 | 劉冰 |
| 856 | 張旭 |
+-----+--------+
SELECT name FROM teacher WHERE no IN (
-- 在這里找到對應(yīng)的條件
);
查看和教師編號有有關(guān)的表的信息:
SELECT * FROM course;
-- t_no: 教師編號
+-------+-----------------+------+
| no | name | t_no |
+-------+-----------------+------+
| 3-105 | 計算機導(dǎo)論 | 825 |
| 3-245 | 操作系統(tǒng) | 804 |
| 6-166 | 數(shù)字電路 | 856 |
| 9-888 | 高等數(shù)學(xué) | 831 |
+-------+-----------------+------+
我們已經(jīng)找到和教師編號有關(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ù)篩選出來的課程號,找出在某課程中,擁有至少 5 名學(xué)員的教師編號:
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ù)篩選出來的教師編號找到教師姓名:
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
表中所有 計算機系
課程的編號,然后根據(jù)這個編號查詢 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 |
+-------+
-- 根據(jù)篩選出來的課程號查詢成績表
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
且成績 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 篩選出來的任意行就符合條件,
-- 最后根據(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
且成績高于 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 |
+------+-------+--------+
復(fù)制表的數(shù)據(jù)作為條件查詢
查詢某課程成績比該課程平均成績低的 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 中查詢數(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 |
+--------+-----------------+
| 李誠 | 計算機系 |
| 王萍 | 計算機系 |
| 劉冰 | 電子工程系 |
| 張旭 | 電子工程系 |
+--------+-----------------+
條件加組篩選
查詢 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 class FROM student WHERE sex = '男' GROUP BY class HAVING COUNT(*) > 1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+
NOTLIKE 模糊查詢?nèi)》?/h2>
查詢 student
表中不姓 "王" 的同學(xué)記錄。
-- 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 函數(shù)
查詢 student
表中每個學(xué)生的姓名和年齡。
-- 使用函數(shù) YEAR(NOW()) 計算出當(dāng)前年份,減去出生年份后得出年齡。
SELECT name, YEAR(NOW()) - YEAR(birthday) as age FROM student;
+-----------+------+
| name | age |
+-----------+------+
| 曾華 | 42 |
| 匡明 | 44 |
| 王麗 | 43 |
| 李軍 | 43 |
| 王芳 | 44 |
| 陸軍 | 45 |
| 王尼瑪 | 43 |
| 張全蛋 | 44 |
| 趙鐵柱 | 45 |
| 張飛 | 45 |
+-----------+------+
MAX 與 MIN 函數(shù)
查詢 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 | 操作系統(tǒng) | 804 |
| 6-166 | 數(shù)字電路 | 856 |
+-------+--------------+------+
MAX 函數(shù)與子查詢
查詢最高分同學(xué)的 score
表。
-- 找出最高成績(該查詢只能有一個結(jié)果)
SELECT MAX(degree) FROM score;
-- 根據(jù)上面的條件篩選出所有最高成績表,
-- 該查詢可能有多個結(jié)果,假設(shè) degree 值多次符合條件。
SELECT * FROM score WHERE degree = (SELECT MAX(degree) FROM score);
+------+-------+--------+
| s_no | c_no | degree |
+------+-------+--------+
| 103 | 3-105 | 92 |
+------+-------+--------+
子查詢 - 6
查詢和 "李軍" 同性別的所有同學(xué) name
。
-- 首先將李軍的性別作為條件取出來
SELECT sex FROM student WHERE name = '李軍';
+-----+
| sex |
+-----+
| 男 |
+-----+
-- 根據(jù)性別查詢 name 和 sex
SELECT name, sex FROM student WHERE sex = (
SELECT sex FROM student WHERE name = '李軍'
);
+-----------+-----+
| name | sex |
+-----------+-----+
| 曾華 | 男 |
| 匡明 | 男 |
| 李軍 | 男 |
| 陸軍 | 男 |
| 王尼瑪 | 男 |
| 張全蛋 | 男 |
| 趙鐵柱 | 男 |
| 張飛 | 男 |
+-----------+-----+
子查詢 - 7
查詢和 "李軍" 同性別且同班的同學(xué) 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
查詢所有選修 "計算機導(dǎo)論" 課程的 "男" 同學(xué)成績表。
需要的 "計算機導(dǎo)論" 和性別為 "男" 的編號可以在 course
和 student
表中找到。
SELECT * FROM score WHERE c_no = (
SELECT no FROM course WHERE name = '計算機導(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 |
+------+-------+--------+
按等級查詢
建立一個 grade
表代表學(xué)生的成績等級,并插入數(shù)據(jù):
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 |
+------+------+-------+
查詢所有學(xué)生的 s_no
、c_no
和 grade
列。
思路是,使用區(qū)間 ( BETWEEN
) 查詢,判斷學(xué)生的成績 ( 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 |
+------+-------+-------+
連接查詢
準(zhǔn)備用于測試連接查詢的數(shù)據(jù):
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 |
+------+--------+--------+
分析兩張表發(fā)現(xiàn),person
表并沒有為 cardId
字段設(shè)置一個在 card
表中對應(yīng)的 id
外鍵。如果設(shè)置了的話,person
中 cardId
字段值為 6
的行就插不進(jìn)去,因為該 cardId
值在 card
表中并沒有。
內(nèi)連接
要查詢這兩張表中有關(guān)系的數(shù)據(jù),可以使用 INNER JOIN
( 內(nèi)連接 ) 將它們連接在一起。
-- INNER JOIN: 表示為內(nèi)連接,將兩張表拼接在一起。
-- on: 表示要執(zhí)行某個條件。
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
的整張表被連接到了右邊。
左外連接
完整顯示左邊的表 ( person
) ,右邊的表如果符合條件就顯示,不符合則補 NULL
。
-- 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 | 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 | 農(nóng)行卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 郵政卡 |
+------+--------+--------+------+-----------+
全外鏈接
完整顯示兩張表的全部數(shù)據(jù)。
-- MySQL 不支持這種語法的全外連接
-- SELECT * FROM person FULL JOIN card on person.cardId = card.id;
-- 出現(xiàn)錯誤:
-- 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 | NULL | NULL |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 郵政卡 |
+------+--------+--------+------+-----------+
事務(wù)
在 MySQL 中,事務(wù)其實是一個最小的不可分割的工作單元。事務(wù)能夠保證一個業(yè)務(wù)的完整性。
比如我們的銀行轉(zhuǎn)賬:
-- a -> -100
UPDATE user set money = money - 100 WHERE name = 'a';
-- b -> +100
UPDATE user set money = money + 100 WHERE name = 'b';
在實際項目中,假設(shè)只有一條 SQL 語句執(zhí)行成功,而另外一條執(zhí)行失敗了,就會出現(xiàn)數(shù)據(jù)前后不一致。
因此,在執(zhí)行多條有關(guān)聯(lián) SQL 語句時,事務(wù)可能會要求這些 SQL 語句要么同時執(zhí)行成功,要么就都執(zhí)行失敗。
如何控制事務(wù) - COMMIT / ROLLBACK
在 MySQL 中,事務(wù)的自動提交狀態(tài)默認(rèn)是開啟的。
-- 查詢事務(wù)的自動提交狀態(tài)
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 1 |
+--------------+
自動提交的作用:當(dāng)我們執(zhí)行一條 SQL 語句的時候,其產(chǎn)生的效果就會立即體現(xiàn)出來,且不能回滾。
什么是回滾?舉個例子:
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 |
+----+------+-------+
可以看到,在執(zhí)行插入語句后數(shù)據(jù)立刻生效,原因是 MySQL 中的事務(wù)自動將它提交到了數(shù)據(jù)庫中。那么所謂回滾的意思就是,撤銷執(zhí)行過的所有 SQL 語句,使其回滾到最后一次提交數(shù)據(jù)時的狀態(tài)。
在 MySQL 中使用 ROLLBACK
執(zhí)行回滾:
-- 回滾到最后一次提交
ROLLBACK;
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
由于所有執(zhí)行過的 SQL 語句都已經(jīng)被提交過了,所以數(shù)據(jù)并沒有發(fā)生回滾。那如何讓數(shù)據(jù)可以發(fā)生回滾?
-- 關(guān)閉自動提交
SET AUTOCOMMIT = 0;
-- 查詢自動提交狀態(tài)
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 0 |
+--------------+
將自動提交關(guān)閉后,測試數(shù)據(jù)回滾:
INSERT INTO user VALUES (2, 'b', 1000);
-- 關(guān)閉 AUTOCOMMIT 后,數(shù)據(jù)的變化是在一張?zhí)摂M的臨時數(shù)據(jù)表中展示,
-- 發(fā)生變化的數(shù)據(jù)并沒有真正插入到數(shù)據(jù)表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
-- 數(shù)據(jù)表中的真實數(shù)據(jù)其實還是:
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
-- 由于數(shù)據(jù)還沒有真正提交,可以使用回滾
ROLLBACK;
-- 再次查詢
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
那如何將虛擬的數(shù)據(jù)真正提交到數(shù)據(jù)庫中?使用 COMMIT
:
INSERT INTO user VALUES (2, 'b', 1000);
-- 手動提交數(shù)據(jù)(持久性),
-- 將數(shù)據(jù)真正提交到數(shù)據(jù)庫中,執(zhí)行后不能再回滾提交過的數(shù)據(jù)。
COMMIT;
-- 提交后測試回滾
ROLLBACK;
-- 再次查詢(回滾無效了)
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
總結(jié)
-
自動提交
- 查看自動提交狀態(tài):
SELECT @@AUTOCOMMIT
;
- 設(shè)置自動提交狀態(tài):
SET AUTOCOMMIT = 0
。
- 手動提交
@@AUTOCOMMIT = 0
時,使用 COMMIT
命令提交事務(wù)。
- 事務(wù)回滾
@@AUTOCOMMIT = 0
時,使用 ROLLBACK
命令回滾事務(wù)。
事務(wù)的實際應(yīng)用,讓我們再回到銀行轉(zhuǎn)賬項目:
-- 轉(zhuǎn)賬
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 |
+----+------+-------+
這時假設(shè)在轉(zhuǎn)賬時發(fā)生了意外,就可以使用 ROLLBACK
回滾到最后一次提交的狀態(tài):
-- 假設(shè)轉(zhuǎn)賬發(fā)生了意外,需要回滾。
ROLLBACK;
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
這時我們又回到了發(fā)生意外之前的狀態(tài),也就是說,事務(wù)給我們提供了一個可以反悔的機會。假設(shè)數(shù)據(jù)沒有發(fā)生意外,這時可以手動將數(shù)據(jù)真正提交到數(shù)據(jù)表中:COMMIT
。
手動開啟事務(wù) - BEGIN / START TRANSACTION
事務(wù)的默認(rèn)提交被開啟 ( @@AUTOCOMMIT = 1
) 后,此時就不能使用事務(wù)回滾了。但是我們還可以手動開啟一個事務(wù)處理事件,使其可以發(fā)生回滾:
-- 使用 BEGIN 或者 START TRANSACTION 手動開啟一個事務(wù)
-- START TRANSACTION;
BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';
-- 由于手動開啟的事務(wù)沒有開啟自動提交,
-- 此時發(fā)生變化的數(shù)據(jù)仍然是被保存在一張臨時表中。
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
提交數(shù)據(jù),提交后無法再發(fā)生本次事務(wù)的回滾。
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 |
+----+------+-------+
-- 提交數(shù)據(jù)
COMMIT;
-- 測試回滾(無效,因為表的數(shù)據(jù)已經(jīng)被提交)
ROLLBACK;
事務(wù)的 ACID 特征與使用
事務(wù)的四大特征:
- A 原子性:事務(wù)是最小的單位,不可以再分割;
- C 一致性:要求同一事務(wù)中的 SQL 語句,必須保證同時成功或者失??;
- I 隔離性:事務(wù) 1 和 事務(wù) 2 之間是具有隔離性的;
- D 持久性:事務(wù)一旦結(jié)束 (
COMMIT
) ,就不可以再返回了 ( ROLLBACK
) 。
事務(wù)的隔離性
事務(wù)的隔離性可分為四種 ( 性能從低到高 ) :
- READ UNCOMMITTED ( 讀取未提交 )
如果有多個事務(wù),那么任意事務(wù)都可以看見其他事務(wù)的未提交數(shù)據(jù)。
- READ COMMITTED ( 讀取已提交 )
只能讀取到其他事務(wù)已經(jīng)提交的數(shù)據(jù)。
- REPEATABLE READ ( 可被重復(fù)讀 )
如果有多個連接都開啟了事務(wù),那么事務(wù)之間不能共享數(shù)據(jù)記錄,否則只能共享已提交的記錄。
- SERIALIZABLE ( 串行化 )
所有的事務(wù)都會按照固定順序執(zhí)行,執(zhí)行完一個事務(wù)后再繼續(xù)執(zhí)行下一個事務(wù)的寫入操作。
查看當(dāng)前數(shù)據(jù)庫的默認(rèn)隔離級別:
-- MySQL 8.x, GLOBAL 表示系統(tǒng)級別,不加表示會話級別。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ | -- MySQL的默認(rèn)隔離級別,可以重復(fù)讀。
+--------------------------------+
-- MySQL 5.x
SELECT @@GLOBAL.TX_ISOLATION;
SELECT @@TX_ISOLATION;
修改隔離級別:
-- 設(shè)置系統(tǒng)隔離級別,LEVEL 后面表示要設(shè)置的隔離級別 (READ UNCOMMITTED)。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 查詢系統(tǒng)隔離級別,發(fā)現(xiàn)已經(jīng)被修改。
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 |
+----+-----------+-------+
-- 開啟一個事務(wù)操作數(shù)據(jù)
-- 假設(shè)小明在淘寶店買了一雙800塊錢的鞋子:
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘寶店';
-- 然后淘寶店在另一方查詢結(jié)果,發(fā)現(xiàn)錢已到賬。
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 200 |
| 4 | 淘寶店 | 1800 |
+----+-----------+-------+
由于小明的轉(zhuǎn)賬是在新開啟的事務(wù)上進(jìn)行操作的,而該操作的結(jié)果是可以被其他事務(wù)(另一方的淘寶店)看見的,因此淘寶店的查詢結(jié)果是正確的,淘寶店確認(rèn)到賬。但就在這時,如果小明在它所處的事務(wù)上又執(zhí)行了 ROLLBACK
命令,會發(fā)生什么?
-- 小明所處的事務(wù)
ROLLBACK;
-- 此時無論對方是誰,如果再去查詢結(jié)果就會發(fā)現(xiàn):
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘寶店 | 1000 |
+----+-----------+-------+
這就是所謂的臟讀,一個事務(wù)讀取到另外一個事務(wù)還未提交的數(shù)據(jù)。這在實際開發(fā)中是不允許出現(xiàn)的。
讀取已提交
把隔離級別設(shè)置為 READ COMMITTED :
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-COMMITTED |
+--------------------------------+
這樣,再有新的事務(wù)連接進(jìn)來時,它們就只能查詢到已經(jīng)提交過的事務(wù)數(shù)據(jù)了。但是對于當(dāng)前事務(wù)來說,它們看到的還是未提交的數(shù)據(jù),例如:
-- 正在操作數(shù)據(jù)事務(wù)(當(dāng)前事務(wù))
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘寶店';
-- 雖然隔離級別被設(shè)置為了 READ COMMITTED,但在當(dāng)前事務(wù)中,
-- 它看到的仍然是數(shù)據(jù)表中臨時改變數(shù)據(jù),而不是真正提交過的數(shù)據(jù)。
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 200 |
| 4 | 淘寶店 | 1800 |
+----+-----------+-------+
-- 假設(shè)此時在遠(yuǎn)程開啟了一個新事務(wù),連接到數(shù)據(jù)庫。
$ mysql -u root -p12345612
-- 此時遠(yuǎn)程連接查詢到的數(shù)據(jù)只能是已經(jīng)提交過的
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘寶店 | 1000 |
+----+-----------+-------+
但是這樣還有問題,那就是假設(shè)一個事務(wù)在操作數(shù)據(jù)時,其他事務(wù)干擾了這個事務(wù)的數(shù)據(jù)。例如:
-- 小張在查詢數(shù)據(jù)的時候發(fā)現(xiàn):
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;
-- 此時表的真實數(shù)據(jù)是:
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘寶店 | 1000 |
| 5 | c | 100 |
+----+-----------+-------+
-- 這時小張再求平均值的時候,就會出現(xiàn)計算不相符合的情況:
SELECT AVG(money) FROM user;
+------------+
| AVG(money) |
+------------+
| 820.0000 |
+------------+
雖然 READ COMMITTED 讓我們只能讀取到其他事務(wù)已經(jīng)提交的數(shù)據(jù),但還是會出現(xiàn)問題,就是在讀取同一個表的數(shù)據(jù)時,可能會發(fā)生前后不一致的情況。這被稱為不可重復(fù)讀現(xiàn)象 ( READ COMMITTED ) 。
幻讀
將隔離級別設(shè)置為 REPEATABLE READ ( 可被重復(fù)讀取 ) :
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ |
+--------------------------------+
測試 REPEATABLE READ ,假設(shè)在兩個不同的連接上分別執(zhí)行 START TRANSACTION
:
-- 小張 - 成都
START TRANSACTION;
INSERT INTO user VALUES (6, 'd', 1000);
-- 小王 - 北京
START TRANSACTION;
-- 小張 - 成都
COMMIT;
當(dāng)前事務(wù)開啟后,沒提交之前,查詢不到,提交后可以被查詢到。但是,在提交之前其他事務(wù)被開啟了,那么在這條事務(wù)線上,就不會查詢到當(dāng)前有操作事務(wù)的連接。相當(dāng)于開辟出一條單獨的線程。
無論小張是否執(zhí)行過 COMMIT
,在小王這邊,都不會查詢到小張的事務(wù)記錄,而是只會查詢到自己所處事務(wù)的記錄:
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘寶店 | 1000 |
| 5 | c | 100 |
+----+-----------+-------+
這是因為小王在此之前開啟了一個新的事務(wù) ( START TRANSACTION
) ,那么在他的這條新事務(wù)的線上,跟其他事務(wù)是沒有聯(lián)系的,也就是說,此時如果其他事務(wù)正在操作數(shù)據(jù),它是不知道的。
然而事實是,在真實的數(shù)據(jù)表中,小張已經(jīng)插入了一條數(shù)據(jù)。但是小王此時并不知道,也插入了同一條數(shù)據(jù),會發(fā)生什么呢?
INSERT INTO user VALUES (6, 'd', 1000);
-- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
報錯了,操作被告知已存在主鍵為 6
的字段。這種現(xiàn)象也被稱為幻讀,一個事務(wù)提交的數(shù)據(jù),不能被其他事務(wù)讀取到。
串行化
顧名思義,就是所有事務(wù)的寫入操作全都是串行化的。什么意思?把隔離級別修改成 SERIALIZABLE :
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| SERIALIZABLE |
+--------------------------------+
還是拿小張和小王來舉例:
-- 小張 - 成都
START TRANSACTION;
-- 小王 - 北京
START TRANSACTION;
-- 開啟事務(wù)之前先查詢表,準(zhǔn)備操作數(shù)據(jù)。
SELECT * FROM user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘寶店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
+----+-----------+-------+
-- 發(fā)現(xiàn)沒有 7 號王小花,于是插入一條數(shù)據(jù):
INSERT INTO user VALUES (7, '王小花', 1000);
此時會發(fā)生什么呢?由于現(xiàn)在的隔離級別是 SERIALIZABLE ( 串行化 ) ,串行化的意思就是:假設(shè)把所有的事務(wù)都放在一個串行的隊列中,那么所有的事務(wù)都會按照固定順序執(zhí)行,執(zhí)行完一個事務(wù)后再繼續(xù)執(zhí)行下一個事務(wù)的寫入操作 ( 這意味著隊列中同時只能執(zhí)行一個事務(wù)的寫入操作 ) 。
根據(jù)這個解釋,小王在插入數(shù)據(jù)時,會出現(xiàn)等待狀態(tài),直到小張執(zhí)行 COMMIT
結(jié)束它所處的事務(wù),或者出現(xiàn)等待超時。
更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題:《MySQL查詢技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》
希望本文所述對大家MySQL數(shù)據(jù)庫計有所幫助。
您可能感興趣的文章:- php中關(guān)于mysqli和mysql區(qū)別的一些知識點分析
- Mysql基礎(chǔ)知識點匯總
- MySQL架構(gòu)體系知識點總結(jié)
- Mysql連接join查詢原理知識點
- 計算機二級考試MySQL知識點 常用MYSQL命令
- MySQL ALTER命令知識點匯總
- mysql數(shù)據(jù)庫基礎(chǔ)知識點與操作小結(jié)
- MySQL優(yōu)化中B樹索引知識點總結(jié)
- MySql 知識點之事務(wù)、索引、鎖原理與用法解析