1. 多表查詢
1.從多張表進(jìn)行查詢操作课梳。
- 練習(xí)使用的sql語(yǔ)句:
-- 建表語(yǔ)句 開(kāi)始
# 創(chuàng)建部門(mén)表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('開(kāi)發(fā)部'),('市場(chǎng)部'),('財(cái)務(wù)部');
# 創(chuàng)建員工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性別
salary DOUBLE, -- 工資
join_date DATE, -- 入職日期
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外鍵仓洼，關(guān)聯(lián)部門(mén)表(部門(mén)表的主鍵)
);
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);
-- 建表語(yǔ)句 結(jié)束
笛卡爾積 ： 有兩個(gè)集合A宦言、B，取這兩個(gè)集合的所有組成情況换可。
要完成多表查詢需要消除無(wú)用的數(shù)據(jù)椎椰。
1.1 多表查詢的分類
1.1.1 內(nèi)連接查詢
隱式的內(nèi)連接 ：使用where 條件消除無(wú)用的數(shù)據(jù)。
顯式內(nèi)連接：
- 語(yǔ)法 ： select 字段列表 from 表名1 [inner] join 表名2 on 查詢條件
- 使用內(nèi)連接查詢需要注意的事項(xiàng):
- 從哪些表中查詢數(shù)據(jù) ?
- 查詢條件是什么?
- 需要查詢哪些字段?
- 內(nèi)連接查詢案例 ：
-- 內(nèi)連接查詢 開(kāi)始
-- 隱式內(nèi)連接查詢 開(kāi)始
SELECT * FROM emp , dept WHERE emp.`dept_id` = dept.`id`;
-- 查詢員工表的名稱 锦担、 性別 俭识。部門(mén)表的名稱
SELECT emp.`name` , emp.`gender` , dept.`name` FROM emp ,dept WHERE emp.`dept_id` = dept.`id`;
-- 使用起別名的方式查詢
SELECT e.`name` , e.gender , d.`name`
FROM emp e , dept d
WHERE e.`dept_id` = d.`id`;
-- 隱式內(nèi)連接查詢 結(jié)束
-- 顯式內(nèi)連接查詢 開(kāi)始
SELECT
e.name , e.`gender` , d.`name`
FROM emp e
INNER JOIN dept d
ON e.`dept_id` = d.`id`;
-- 其中inner 為可選關(guān)鍵字 可以不用寫(xiě)
SELECT
e.`name` , e.gender , d.`name`
FROM emp e
JOIN dept d
ON e.`dept_id` = d.`id`;
-- 顯式內(nèi)連接查詢 結(jié)束
-- 內(nèi)連接查詢 結(jié)束
1.1.2 外連接查詢
- 左外連接： 查詢的是左表所有數(shù)據(jù)以及其交集部分。
語(yǔ)法 ： select 字段列表 from 表1 left join 表2 on 條件 ;
- 右外連接：
語(yǔ)法 ： select 字段列表 from 表1 right join 表2 on 條件 ;
- 外鏈接查詢案例 ：
-- 外連接 開(kāi)始
-- 左外鏈接 開(kāi)始 左連接查詢是查詢左表中所有數(shù)據(jù) 以及 右表與左表的交集部分
-- 查詢所有員工信息洞渔，如果員工有部門(mén)套媚，則查詢部門(mén)名稱·，沒(méi)有部門(mén)則不顯示部門(mén)名稱
SELECT
e.* , d.`name`
FROM emp e
LEFT JOIN dept d
ON e.`dept_id` = d.`id`;
-- 左外連接 結(jié)束
-- 右外連接 開(kāi)始 右外連接 查詢是查詢右表所有數(shù)據(jù) 以及 右表 與左表的交集部分
SELECT
e.* , d.`name`
FROM emp e
RIGHT JOIN dept d
ON e.`dept_id` = d.`id`;
-- 右外連接 結(jié)束
-- 外連接 結(jié)束
1.1.3 子查詢
概念 ： 查詢中嵌套查詢 磁椒。稱嵌套的查詢?yōu)樽硬樵儭?/p>
子查詢基本案例 :
-- 子查詢 開(kāi)始
-- 查詢工資最高的員工信息
SELECT * FROM emp WHERE emp.salary = (
SELECT MAX(salary) FROM emp
);
-- 子查詢 結(jié)束
- 子查詢的結(jié)果是單行單列的 : 使用的運(yùn)算符是 > < = 等
-- 員工工資小于平均工資的人
SELECT * FROM emp WHERE emp.`salary` < (
SELECT AVG(salary) FROM emp
)
- 子查詢的結(jié)果是多行單列的: 可以使用運(yùn)算符 in 進(jìn)行判斷
-- 查詢財(cái)務(wù)部所有員工信息 --> 多行單列
SELECT *FROM emp WHERE emp.`dept_id` IN (
SELECT id FROM dept WHERE dept.`name` = '財(cái)務(wù)部' OR dept.`name` = '市場(chǎng)部'
);
- 子查詢的結(jié)果是多行多列的:如果查詢的結(jié)果是多行多列的堤瘤，子查詢可以作為一張?zhí)摂M表進(jìn)行連接查詢。
-- 查詢員工入職日期是 2011 - 11 - 11 日之后額員工信息和部門(mén)信息
SELECT d.`name` ,e.* FROM dept d , (SELECT * FROM emp WHERE join_date > '2011-11-11') e WHERE e.dept_id = d.`id`;
-- 使用普通內(nèi)聯(lián)接查詢更加方便理解
SELECT e.* , d.`name`
FROM emp e , dept d
WHERE join_date > '2011-11-11' AND e.`dept_id` = d.id;
1.2 多表練習(xí)
-- 多表查詢練習(xí) 開(kāi)始
CREATE DATABASE db3 CHARACTER SET utf8;
DROP TABLE dept;
DROP TABLE emp;
-- 練習(xí)建表 語(yǔ)句 開(kāi)始
-- 部門(mén)表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- 部門(mén)id
dname VARCHAR(50), -- 部門(mén)名稱
loc VARCHAR(50) -- 部門(mén)所在地
);
-- 添加4個(gè)部門(mén)
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'學(xué)工部','上海'),
(30,'銷售部','廣州'),
(40,'財(cái)務(wù)部','深圳');
-- 職務(wù)表浆熔，職務(wù)名稱本辐，職務(wù)描述
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
-- 添加4個(gè)職務(wù)
INSERT INTO job (id, jname, description) VALUES
(1, '董事長(zhǎng)', '管理整個(gè)公司，接單'),
(2, '經(jīng)理', '管理部門(mén)員工'),
(3, '銷售員', '向客人推銷產(chǎn)品'),
(4, '文員', '使用辦公軟件');
-- 員工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 員工id
ename VARCHAR(50), -- 員工姓名
job_id INT, -- 職務(wù)id
mgr INT , -- 上級(jí)領(lǐng)導(dǎo)
joindate DATE, -- 入職日期
salary DECIMAL(7,2), -- 工資
bonus DECIMAL(7,2), -- 獎(jiǎng)金
dept_id INT, -- 所在部門(mén)編號(hào)
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,'關(guān)羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 工資等級(jí)表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, -- 級(jí)別
losalary INT, -- 最低工資
hisalary INT -- 最高工資
);
-- 添加5個(gè)工資等級(jí)
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
-- 需求：
-- 1.查詢所有員工信息。查詢員工編號(hào)慎皱，員工姓名老虫，工資，職務(wù)名稱茫多，職務(wù)描述
SELECT e.`id`,e.`ename`,e.`salary`,j.`jname`,j.`description`
FROM emp e , job j
WHERE e.`job_id` = j.`id`;
-- 2.查詢員工編號(hào)祈匙，員工姓名，工資天揖，職務(wù)名稱夺欲，職務(wù)描述，部門(mén)名稱今膊，部門(mén)位置
SELECT e.`ename` , e.`salary` , j.`jname` , j.`description` , d.`dname` , d.`loc`
FROM emp e , job j , dept d
WHERE e.`dept_id` = d.`id` AND e.`job_id` = j.`id`;
-- 3.查詢員工姓名些阅，工資，工資等級(jí)
SELECT e.`ename` , e.`salary` , s.`grade`
FROM emp e , salarygrade s
WHERE e.`salary` BETWEEN s.`losalary` AND s.`hisalary`;
-- 4.查詢員工姓名斑唬，工資市埋，職務(wù)名稱，職務(wù)描述恕刘，部門(mén)名稱腰素，部門(mén)位置，工資等級(jí)
SELECT e.ename , e.`salary` , j.`jname` , j.`description` , d.`dname` , d.`loc` , s.`grade`
FROM emp e , job j , dept d , salarygrade s
WHERE e.`dept_id` = d.`id` AND e.`job_id` = j.`id` AND e.`salary` BETWEEN s.`losalary` AND s.`hisalary`;
-- 5.查詢出部門(mén)編號(hào)雪营、部門(mén)名稱、部門(mén)位置衡便、部門(mén)人數(shù)
/*
使用分組查詢献起，按照 emp.dept_id 完成分組查詢
*/
SELECT d.`id` , d.`dname` , d.`loc` , e.total
FROM dept d , (
SELECT dept_id , COUNT(id) total
FROM emp
GROUP BY dept_id
) e
WHERE d.`id` = e.dept_id ;
-- 6.查詢所有員工的姓名及其直接上級(jí)的姓名,沒(méi)有領(lǐng)導(dǎo)的員工也需要查詢
-- 使用自關(guān)聯(lián)映射進(jìn)行查詢
-- 沒(méi)有領(lǐng)導(dǎo)的也需要查詢 ： 查詢左表的數(shù)據(jù) ，需要查詢左表的所有數(shù)據(jù)镣陕， 以及左右表的交集部分谴餐。需要使用左外連接查詢。
SELECT e.`ename` , m.`ename` ,e.`mgr` , m.`id`
FROM emp e LEFT JOIN emp m
ON e.`mgr` = m.`id`;
-- 練習(xí)建表 語(yǔ)句 結(jié)束
2. 事務(wù)
2.1 事務(wù)的基本介紹
概念 ： 什么是事務(wù) 呆抑？ 如果一個(gè)包含多個(gè)步驟的業(yè)務(wù)操作被事務(wù)管理岂嗓，那么這些操作要么同時(shí)成功要么同時(shí)失敗。
案例 ： 張三給李四轉(zhuǎn)錢(qián) 鹊碍。 第一步是查詢張三賬戶余額是否大于 500 厌殉。 第二步張三賬戶減 500 挤渐。 第三步李四賬戶加500适肠。如果當(dāng)執(zhí)行到第二步的時(shí)候出現(xiàn)異常，下面的步驟將不會(huì)再執(zhí)行容贝。如果該操作已經(jīng)被事務(wù)管理耀销，這三步操作楼眷，要么同時(shí)成功要么就同時(shí)失敗。如果中間出現(xiàn)異常，將出現(xiàn)回滾操作罐柳。如果沒(méi)有出現(xiàn)異常將提交事務(wù)掌腰。
2.1.1 事務(wù)的操作步驟
- 開(kāi)啟事務(wù)
start transaction
- 回滾事務(wù)
rollback
- 提交事務(wù)
commit
2.1.2 演示
- 創(chuàng)建一張賬戶表
CREATE DATABASE transactionDb CHARACTER SET utf8;
USE transactionDb;
-- 創(chuàng)建數(shù)據(jù)表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加數(shù)據(jù)
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
- 事務(wù)的基本操作 :
-- 開(kāi)啟事務(wù)
START TRANSACTION ;
-- 張三 給 李四 轉(zhuǎn)賬 500 元
-- 張三賬戶 -500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 出現(xiàn)了問(wèn)題 ...
-- 李四賬戶 +500
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
-- 提交事務(wù)
COMMIT;
-- 回滾事務(wù)
ROLLBACK;
SELECT * FROM account;
-- 數(shù)據(jù)庫(kù)事務(wù) 結(jié)束
2.1.3 事務(wù)默認(rèn)自動(dòng)提交與手動(dòng)提交
mysql數(shù)據(jù)庫(kù)中事務(wù)默認(rèn)自動(dòng)提交 。 一條DML （增刪改）語(yǔ)句會(huì)自動(dòng)提交一次事務(wù)张吉。
使用 start transaction 開(kāi)啟事務(wù)之后 齿梁，提交方式將變?yōu)槭謩?dòng)提交。 事務(wù)將不會(huì)被提交芦拿，事務(wù)將會(huì)被回滾士飒。
事務(wù)提交的兩種方式：手動(dòng)提交和自動(dòng)提交。手動(dòng)提交需要先開(kāi)啟事務(wù)再提交蔗崎。自動(dòng)提交酵幕，在mysql中就是自動(dòng)提交事務(wù)的。
修改事務(wù)的默認(rèn)提交方式 ：
- 查詢事務(wù)的默認(rèn)提交方式 : select @@autocommit; 1 代表自動(dòng)提交缓苛； 0 代表手動(dòng)提交 芳撒；
- 修改默認(rèn)的提交方式 set @@autocommit = 0;
2.2 事務(wù)的四大特征
2.2.1 原子性
- 事務(wù)是原子性的，原子是不可再分割的最小單位未桥。要么同時(shí)成功要同時(shí)失敗笔刹。是不可分割的最小操作單位。
2.2.2 持久性
- 當(dāng)事務(wù)提交或者回滾之后冬耿，數(shù)據(jù)庫(kù)會(huì)持久化的保存數(shù)據(jù)舌菜。
2.2.3 隔離性
- 多個(gè)事務(wù)之間相互獨(dú)立。
2.2.4 一致性
- 事務(wù)操作前后數(shù)據(jù)總量不變 亦镶。
2.3 事務(wù)的隔離級(jí)別 （了解）
- 概念 ： 多個(gè)事務(wù)之間是相互獨(dú)立的日月，但是如果多個(gè)事務(wù)操作同一批數(shù)據(jù)則會(huì)引發(fā)一些問(wèn)題。設(shè)置不同的隔離級(jí)別就可以解決這些問(wèn)題缤骨。
- 會(huì)引發(fā)的問(wèn)題 ： 臟讀 爱咬、 不可重復(fù)讀 、 幻讀
臟讀 : 一個(gè)事務(wù) 绊起，讀取到另一個(gè)事務(wù)中沒(méi)有提交的數(shù)據(jù) 精拟。
不可重復(fù)讀 （虛讀）： 在同一個(gè)事務(wù)中兩次讀取到的數(shù)據(jù)不一樣。
幻讀 ： 一個(gè)事務(wù)去操作（DML增刪改）數(shù)據(jù)表中所有數(shù)據(jù) 虱歪， 另一個(gè)事務(wù)添加了一條數(shù)據(jù)蜂绎，則第一個(gè)事務(wù)查詢不到自己的修改。
2.3.1 隔離級(jí)別
- read uncommitted ： 讀未提交 实蔽。會(huì)產(chǎn)生的問(wèn)題 是 臟讀 荡碾、 不可重復(fù)度 、幻讀 局装。
- read committed（Oracle默認(rèn)的隔離級(jí)別） ： 讀已提交 坛吁。 會(huì)產(chǎn)生的問(wèn)題 是 不可重復(fù)讀 劳殖、 幻讀。
- repeatable read（Mysql默認(rèn)） ：可重復(fù)讀拨脉。 會(huì)產(chǎn)生的問(wèn)題是 幻讀哆姻。
- serializable ： 串行化 。 可以解決所有的問(wèn)題玫膀。
- 注意 ： 隔離級(jí)別從小到大安全性越來(lái)越高矛缨，但是效率將會(huì)越來(lái)越低。
2.3.2 查詢并設(shè)置數(shù)據(jù)庫(kù)的隔離級(jí)別
- 查詢數(shù)據(jù)庫(kù)的隔離級(jí)別
select @@tx_isolation;
- 設(shè)置數(shù)據(jù)庫(kù)的隔離級(jí)別
set global transaction isolation level 級(jí)別字符串;
-- 設(shè)置數(shù)據(jù)庫(kù)的隔離級(jí)別
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED ;
2.3.2 演示設(shè)置不同的隔離級(jí)別會(huì)產(chǎn)生的問(wèn)題
需要使用兩個(gè)不同的 cmd 窗口進(jìn)行操作 帖旨。
設(shè)置隔離級(jí)別 為未提交 ：
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
- 開(kāi)啟事務(wù) ：
在開(kāi)啟的兩個(gè) cmd 窗口中都 開(kāi)啟事務(wù) 制造 臟讀 的問(wèn)題 箕昭。 讀取事務(wù)中未提交的數(shù)據(jù) 。
start transaction ;
- 轉(zhuǎn)賬操作 ：
- zhangsan 轉(zhuǎn)賬 500 給李四
update account set balance = balance - 500 where name = 'zhangsan';
-- 李四賬戶 加 500
update account set balance = balance + 500 where name = 'lisi';
-- zhangsan 進(jìn)行回滾操作
rollback;
- 設(shè)置事務(wù)的隔離級(jí)別為 讀已提交 :
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED ;
-- 重新開(kāi)啟 cmd 窗口查看事務(wù)的隔離級(jí)別
select @@tx_isolation;
- 執(zhí)行轉(zhuǎn)賬操作 :
-- 在兩個(gè)窗口中開(kāi)啟事務(wù)
start transaction;
-- zhangsan 想lisi 轉(zhuǎn)賬 500
update account set balance = balance - 500 where name = 'zhangsan';
-- lisi 賬戶 增加 500
update account set balance = balance + 500 where name = 'lisi';
-- 提交 事務(wù)
commit ;
但是在讀已提交的隔離級(jí)別的時(shí)候出現(xiàn)了一個(gè)問(wèn)題 ： 在同一個(gè)事務(wù)中兩次查詢到的數(shù)據(jù)出現(xiàn)了不一致的情況解阅。
設(shè)置事務(wù)的隔離級(jí)別為 repeatable read :
SET GLOBAL TRANSACTION ISOLATION LEVEL repeatable read ;
-- 重新啟動(dòng)一個(gè) cmd 窗口查詢當(dāng)前數(shù)據(jù)庫(kù)的隔離級(jí)別
select @@tx_isolation;
- 執(zhí)行轉(zhuǎn)賬操作 :
-- 在兩個(gè) cmd 窗口中 開(kāi)啟事務(wù)
start transaction;
update account set balance = balance - 500 where name = 'zhangsan';
update account set balance = balance + 500 where name = 'lisi';
-- 在其中一個(gè)窗口提交事務(wù)
commit;
- 設(shè)置事務(wù)的隔離級(jí)別為 serializable 串行化
SET GLOBAL TRANSACTION ISOLATION LEVEL serializable ;
-- 重新啟動(dòng)窗口查詢當(dāng)前事務(wù)的隔離級(jí)別
select @@tx_isolation;
- 執(zhí)行轉(zhuǎn)賬操作 :
-- 在兩個(gè)窗口中開(kāi)啟事務(wù)
start transaction
-- zhangsan向lisi轉(zhuǎn)賬
update account set balance = balance - 500 where name = 'zhangsan';
update account set balance = balance + 500 where name = 'lisi';
-- 提交事務(wù)
commit;
