回顧

1. DQL單表高級查詢條件 where比較運算邏輯運算符in關鍵字between關鍵字if null關鍵字like關鍵字% 多個任意字符_ 單個任意字符排序 order byascdesc聚合函數count(*) 統計行，包括null值maxminavgsum分組 group by 分組字段 having 分組后條件過濾分頁 limit 開始索引，每頁顯示個數公式：索引=（當前頁-1）× 每頁的個數 2. 數據庫約束主鍵約束create table 表名(id int primary key auto_increment,....);唯一 unique非空 not null默認值 default3. 表關系一對多主表從表（外鍵字段 指向 主表主鍵）多對多二個主表創建第三張作為從表（二個外鍵字段 分別指向 各自主表的主鍵），由多個一對多組成一對一外鍵是唯一主鍵是外鍵外鍵約束保證多表有關的數據進行限定，保證他的正確性，有效性和完整性[constraint] [約束性] foreign key(外鍵字段) references 主表（主鍵）4. 數據庫備份與還原

MySQL多表查詢與事務

今日目標

1. DQL多表高級查詢語法笛卡爾積內連接外連接子查詢2. 多表綜合案例（四張表），學習多表查詢的規律3. 事務安全（TCL）轉賬案例引入它，解決場景4. 表的范式（數據庫的設計規則）

一 多表查詢【重點】

同時查詢多張表獲取到需要的數據組成完整的信息返回給用戶。

準備數據

-- 多表查詢 create database web03_1; use web03_1; -- 創建部門表（主表） CREATE TABLE dept (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) );INSERT INTO dept (NAME) VALUES ('開發部'),('市場部'),('財務部'),('銷售部');-- 創建員工表（從表） CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),gender CHAR(1), -- 性別（sex）salary DOUBLE, -- 工資join_date DATE, -- 入職日期dept_id INT -- 外鍵字段 );INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孫悟空','男',7200,'2013-02-24',1); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('豬八戒','男',3600,'2010-12-02',2); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1); INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('沙僧','男',6666,'2017-03-04',null);

1.1 笛卡爾積

* 功能二張表的記錄所有字段進行了組合，這種現象稱為笛卡爾積，（又稱為交叉連接） * 語法SELECT ... FROM 左表,右表;

1.2 內連接

* 功能拿左表的記錄去匹配右表的記錄，若匹配上就顯示，匹配不上就不顯示（二張表的交集） * 語法1.隱式內連接【了解】SELECT ... FROM 左表,右表 where 條件過濾;2.顯示內連接【推薦】 select ... from 左表 [inner] join 右表 on 條件過濾; -- 隱式內連接 SELECT * FROM emp e,dept d WHERE e.dept_id = d.id; -- 顯示內連接 SELECT * FROM emp e INNER JOIN dept d ON e.dept_id = d.id;# 內連接 -- 查詢唐僧的 id，姓名，性別，工資和所在部門名稱 SELECT e.id,e.name,e.gender,e.salary,d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.name = '唐僧';

1.3 外連接

1. 左外連接【掌握】功能展示左表的全部記錄，去匹配右表的記錄，若匹配上顯示數據，若沒匹配上顯示NULL語法select ... from 左表 left [outer] join 右表 on 條件過濾;2. 右外連接【了解】功能展示右表的全部記錄，去匹配左表的記錄，若匹配上顯示數據，若沒匹配上顯示NULL語法select ... from 左表 right [outer] join 右表 on 條件過濾; # 左外連接 -- 查詢所有員工信息（左邊）及對應的部門名稱（右邊） SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id; -- 查詢所有部門（左邊）及對應的員工信息（右邊） SELECT * FROM dept d LEFT OUTER JOIN emp e ON e.dept_id = d.id;# 右外連接 -- 查詢所有部門（右邊）及對應的員工信息（左邊） SELECT * FROM emp e RIGHT OUTER JOIN dept d ON d.id = e.dept_id;

1.4 子查詢

* 功能一條select語句執行結果，作為另一條select 語法 的一部分，使用需要添加括號 * 語法1. 單值SELECT MAX(salary) FROM emp;2. 單列多行SELECT NAME FROM emp;3. 多列多行SELECT * FROM emp;* 小結子查詢結果為單列，作為條件在where后使用select ... from 表名 where 字段 = or in(子查詢)；子查詢結果為多列，作為虛擬表在form后使用select ... from (子查詢) as 表別名.... # 子查詢# 子查詢結果為單值 -- 1 查詢工資最高的員工是誰？ SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp); -- 2 查詢工資小于平均工資的員工有哪些？ -- 2.1 先求出平均工資 SELECT AVG(salary) FROM emp; -- 2.2 再查詢小于平均工資的員工 SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);# 子查詢結果為單列多行 -- 1 查詢工資大于5000的員工，來自于哪些部門的名字 -- 1.1 查詢工資大于5000的員工 SELECT dept_id FROM emp WHERE salary > 5000; -- 1.2 來自于哪些部門的名字 SELECT * FROM dept WHERE id IN(SELECT dept_id FROM emp WHERE salary > 5000);-- 2 查詢開發部與財務部所有的員工信息 -- 2.1 查詢開發部與財務部 SELECT id FROM dept WHERE NAME IN('開發部','財務部'); -- 2.2 對應的的員工信息 SELECT * FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE NAME IN('開發部','財務部'));# 子查詢結果為多列多行 -- 1 查詢出2011年以后入職的員工信息，包括部門名稱 -- 1.1 查詢出2011年以后入職的員工信息 SELECT * FROM emp WHERE join_date > '2011-1-1'; -- 1.2 作為虛擬表連接部門表 SELECT * FROM (SELECT * FROM emp WHERE join_date > '2011-1-1') AS e LEFT JOIN dept d ON e.dept_id = d.id;-- 擴展 SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id WHERE e.join_date > '2011-1-1';

二 多表案例【四張表】

我們在企業開發時，根據不同的業務需求往往需要通過2張及以上的表中去查詢需要的數據。其實不管是幾張表的查詢，都是有規律可循的。

準備數據

-- 多表案例 create database web03_2; use web03_2; -- 部門表 CREATE TABLE dept (id INT PRIMARY KEY auto_increment, -- 部門iddname VARCHAR(50), -- 部門名稱loc VARCHAR(50) -- 部門位置 );-- 添加4個部門 INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京'), (20,'學工部','上海'), (30,'銷售部','廣州'), (40,'財務部','深圳');-- 職務表 CREATE TABLE job (id INT PRIMARY KEY,jname VARCHAR(20), -- 職務名稱description VARCHAR(50) -- 職務描述 );-- 添加4個職務 INSERT INTO job (id, jname, description) VALUES (1, '董事長', '管理整個公司，接單'), (2, '經理', '管理部門員工'), (3, '銷售員', '向客人推銷產品'), (4, '文員', '使用辦公軟件');-- 員工表 CREATE TABLE emp (id INT PRIMARY KEY, -- 員工idename VARCHAR(50), -- 員工姓名job_id INT, -- 職務id 外鍵mgr INT , -- 上級領導joindate DATE, -- 入職日期salary DECIMAL(7,2), -- 工資 99999.99bonus DECIMAL(7,2), -- 獎金 99999.99dept_id INT, -- 所在部門編號 外鍵CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id) );-- 添加員工 INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1001,'孫悟空',4,1004,'2000-12-17','8000.00',NULL,20), (1002,'盧俊義',3,1006,'2001-02-20','16000.00','3000.00',30), (1003,'林沖',3,1006,'2001-02-22','12500.00','5000.00',30), (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20), (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30), (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30), (1007,'劉備',2,1009,'2001-09-01','24500.00',NULL,10), (1008,'豬八戒',4,1004,'2007-04-19','30000.00',NULL,20), (1009,'羅貫中',1,NULL,'2001-11-17','50000.00',NULL,10), (1010,'吳用',3,1006,'2001-09-08','15000.00','0.00',30), (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20), (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30), (1013,'小白龍',4,1004,'2001-12-03','30000.00',NULL,20), (1014,'關羽',4,1007,'2002-01-23','13000.00',NULL,10);-- 工資等級表 CREATE TABLE salarygrade (grade INT PRIMARY KEY, -- 等級losalary INT, -- 最低工資hisalary INT -- 最高工資 );-- 添加5個工資等級 INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (1,7000,12000), (2,12010,14000), (3,14010,20000), (4,20010,30000), (5,30010,99990);

練習

-- 1 查詢所有員工信息。顯示員工編號，員工姓名，工資，職務名稱，職務描述 -- 1.1 確定查詢哪些表？【員工 職務】 SELECT * FROM emp e INNER JOIN job j ; -- 1.2 確定連接條件？【員工(job_id) 指向 職務(id)】 SELECT * FROM emp e INNER JOIN job j ON e.job_id = j.id; -- 1.3 確定顯示字段？【員工編號，員工姓名，工資，職務名稱，職務描述】 SELECT e.id,e.ename, e.salary,j.jname,j.descriptionFROM emp e INNER JOIN job j ON e.job_id = j.id; -- 2 查詢所有員工信息。顯示員工編號，員工姓名，工資，職務名稱，職務描述，部門名稱，部門位置 -- 2.1 確定查詢的表？【員工、職務、部門】 SELECT * FROM emp e INNER JOIN job j INNER JOIN dept d; -- 2.2 確定連接條件？【員工(job_id) 指向 職務(id)、員工(dept_id) 指向 部門(id)】 SELECT * FROM emp e INNER JOIN job j INNER JOIN dept dON e.job_id = j.id AND e.dept_id = d.id; -- 了解SELECT * FROM emp e INNER JOIN job j ON e.job_id = j.idINNER JOIN dept d ON e.dept_id = d.id; -- 推薦 -- 2.3 確定顯示字段？【顯示員工編號，員工姓名，工資，職務名稱，職務描述，部門名稱，部門位置】 SELECT e.id,e.ename, e.salary,j.jname,j.description,d.dname,d.locFROM emp e INNER JOIN job j ON e.job_id = j.idINNER JOIN dept d ON e.dept_id = d.id; -- 推薦 -- 3 查詢所有員工信息。顯示員工姓名，工資，職務名稱，職務描述，部門名稱，部門位置，工資等級 -- 3.1 確定查詢表？【員工、職務、部門、工資等級】 SELECT * FROM emp eINNER JOIN job jINNER JOIN dept dINNER JOIN salarygrade sg; -- 3.2 確定連接條件？【員工(job_id) 指向 職務(id)、員工(dept_id) 指向 部門(id)、員工(salary) 范圍查找 對應工資等級】 SELECT * FROM emp eINNER JOIN job j ON e.job_id = j.idINNER JOIN dept d ON e.dept_id = d.idINNER JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalary; -- 3.3 確定顯示字段？【顯示員工姓名，工資，職務名稱，職務描述，部門名稱，部門位置，工資等級】 SELECT e.id,e.ename, e.salary,j.jname,j.description,d.dname,d.loc,sg.gradeFROM emp eINNER JOIN job j ON e.job_id = j.idINNER JOIN dept d ON e.dept_id = d.idINNER JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalary;

小結

1. 多表查詢會出現笛卡爾積2. 消除笛卡爾積：2張表1個條件、3張表2個條件【條件=n-1】3. 步驟3.1 確定查詢幾張表？3.2 確定連接條件？3.3 確定顯示字段？3.4 確定業務條件？ -- 4 查詢經理的信息。顯示員工姓名，工資，職務名稱，職務描述，部門名稱，部門位置，工資等級 -- 4.1 確定查詢幾張表 SELECT * FROM emp eINNER JOIN job jINNER JOIN dept dINNER JOIN salarygrade sg; -- 4.2 確定連接條件 SELECT * FROM emp eINNER JOIN job j ON e.job_id = j.idINNER JOIN dept d ON e.dept_id = d.idINNER JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalary; -- 4.3 確定顯示字段 SELECT e.id,e.ename, e.salary,j.jname,j.description,d.dname,d.loc,sg.gradeFROM emp eINNER JOIN job j ON e.job_id = j.idINNER JOIN dept d ON e.dept_id = d.idINNER JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalary; -- 4.4 確定業務條件【經理】 SELECT e.id,e.ename, e.salary,j.jname,j.description,d.dname,d.loc,sg.gradeFROM emp eINNER JOIN job j ON e.job_id = j.idINNER JOIN dept d ON e.dept_id = d.idINNER JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalaryWHERE j.jname = '經理'; -- 5 查詢出部門編號、部門名稱、部門位置、部門人數 -- 5.1 先查詢部門人數 SELECT dept_id,COUNT(*) AS total FROM emp GROUP BY dept_id;-- 5.2 查詢結果作為虛擬表 連接 部門表 SELECT * FROM (SELECT dept_id,COUNT(*) AS total FROM emp GROUP BY dept_id) AS e INNER JOIN dept d ON e.dept_id = d.id;-- 內連接不關注書寫的順序，誰先誰后都可以 SELECT d.id,d.dname,d.loc,e.total FROM dept d INNER JOIN (SELECT dept_id,COUNT(*) AS total FROM emp GROUP BY dept_id) AS e ON d.id = e.dept_id;

三 事務安全 TCL

3.1 概述

如果一個包含多個步驟的業務操作，被事務管理，那么這些操作要么同時成功，要么同時失敗。

準備數據

-- 創建數據表 CREATE TABLE account ( -- 賬戶表id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(32),money DOUBLE );-- 添加數據 INSERT INTO account (NAME, money) VALUES ('片王', 1000), ('剛剛', 1000);

模擬轉賬

-- 轉賬 -- 片王扣錢 -100 UPDATE account SET money=money-100 WHERE id = 1; -- 機器故障 -- 剛剛收錢 +100 UPDATE account SET money=money+100 WHERE id = 2;

3.2 操作

3.2.1 手動提交事務

1. 開啟事務start transaction;2. 提交事務commit;3. 回滾事務rollback;

轉賬成功

-- 1.開啟事務 start transaction; -- 2.片王扣錢 UPDATE account SET money=money-100 WHERE id = 1; -- 3.剛剛加錢 UPDATE account SET money=money+100 WHERE id = 2; -- 4.提交事務 commit;

轉賬失敗

-- 1.開啟事務 start transaction; -- 2.片王扣錢 UPDATE account SET money=money-100 WHERE id = 1; -- 機器故障 -- 3.回滾事務 rollback;

3.2.2 自動提交事務

* MySQL數據庫默認情況一條DML語句就是一個獨立的事務。* 查看MySQL是否開啟自動提交show variables like 'autocommit';* 臨時關閉自動提交set autocommit=OFF;

測試還會不會自動提交

-- 片王扣錢 UPDATE account SET money=money-100 WHERE id = 1; -- 手動提交事務 commit;

3.3 事務原理【理解】

注意：代碼報錯之后必須進行rollback處理，如果沒有執行rollback 會影響到同一個事務中下一次提交數據。

3.4 回滾點【了解】

* 功能當事務開啟后，一部分sql執行成功，添加一個回滾點，后續操作報錯了，直接到回滾點，保證之前的操作可以成功提交* 語法：1.添加保存點savepoint 保存點名稱;2.回滾到保存點rollback to 保存點名稱;

模擬，插入四條記錄，在前二條插入成功后，設置保存點，后續操作報錯了，直接到保存點，保證前二條記錄可成功提交。

-- 1.開啟事務 start transaction; -- 2.插入 小路 insert into account values(null,'小路',1000); -- 3.插入 PGONE insert into account values(null,'pgone',1000); -- 4.設置保存點 savepoint ok; -- 5.插入 薛之謙 insert into account values(null,'謙謙',1000); -- 機器故障 -- 6.回滾到保存點 rollback to ok; -- 7.提交事務 commit;

3.5 事務特性【ACID】【面試題】

1. 原子性：A atomicity如果一個包含多個步驟的業務操作，被事務管理，那么這些操作要么同時成功，要么同時失敗。2. 一致性：C consistency數據在事務的執行前后，保證它的完整一致3. 隔離性：I isolation多個事務之間，相互獨立互不干擾4. 持久性：D durability事務一旦成功提交，同步到數據庫磁盤文件，不可逆。

3.6 事務隔離性

* 功能多個事務之間隔離的，相互獨立的。但是如果多個事務操作同一批數據，則會引發一些問題，設置不同的隔離級別就可以解決這些問題。1. 臟讀：一個事務中，讀取到另一個事務未提交的數據2. 不可重復讀：一個事務中，二次讀取的內容不一致（update）3. 幻讀：一個事務中，二次讀取的數量不一致（insert、delete）

MySQL數據庫隔離級別

| 級別 | 名字 | 隔離級別 | 臟讀 | 不可重復讀 | 幻讀 | 數據庫默認隔離級別 | | ---- | -------- | ---------------- | ---- | ---------- | ---- | ------------------ | | 1 | 讀未提交 | read uncommitted | 是 | 是 | 是 | | | 2 | 讀已提交 | read committed | 否 | 是 | 是 | Oracle和SQL Server | | 3 | 可重復讀 | repeatable read | 否 | 否 | 是 | MySQL | | 4 | 串行化 | serializable | 否 | 否 | 否 | |

數據庫性能：1>2>3>4

數據庫安全：4>3>2>1

綜合考量：2、3

演示不同隔離級別產生的問題

開啟二個事務：A窗口 B窗口

1. 查看當前數據庫隔離級別show variables like '%isolation%'; 2. 臨時修改隔離級別set session transaction isolation level 級別字符串;update account set money=900 where id = 1; 3. 演示3.1 臟讀 設置隔離級別為 read uncommitted;set session transaction isolation level read uncommitted;3.2 解決臟讀 設置隔離級別為 read committed;set session transaction isolation level read committed;3.3 解決不可重復讀 設置隔離級別為 repeatable read;set session transaction isolation level repeatable read;4.3 串行化 設置隔離級別 serializable;set session transaction isolation level serializable;

四 三范式【了解】

4.1 概述

* 范式(Normal Form)設計數據庫表的規則，好的數據庫設計對數據的存儲性能和后期的程序開發，都會產生重要的影響。建立科學的，規范的數據庫就需要滿足一些規則來優化數據的設計和存儲 。* 分類目前關系數據庫有六種范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）、第五范式（5NF，又稱完美范式）。滿足最低要求的范式是第一范式（1NF）。在第一范式的基礎上進一步滿足更多規范要求的稱為第二范式（2NF），其余范式以次類推。一般說來，數據庫只需滿足第三范式(3NF）就行了。

4.2 三范式

看圖

1. 第一范式（1NF）要求：每一列不能再拆分 2. 第二范式（2NF）要求：1.一張表只描述一件事情2.表中的每一個普通列都依賴于主鍵 3. 第三范式（3NF）要求：從表的外鍵必須使用主表的主鍵

總結

以上是生活随笔為你收集整理的asp多表查询并显示_MySQL多表查询与事务的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。