MySQL基礎(chǔ) — 多表查詢以及事務(wù)管理
一、多表查詢
1.1 對(duì)應(yīng)關(guān)系
- 一對(duì)一
? 多用于單表拆分，將一張表的基礎(chǔ)字段放在一張表中，其他詳情字段放在另一張表中，以提升操作效率
? 在任意一方加入外鍵，關(guān)聯(lián)另外一方的主鍵，并且設(shè)置外鍵為唯一的(UNIQUE)
? 因?yàn)槭且粚?duì)一，所以需要設(shè)置唯一約束
- 一對(duì)多
? 在多的一方建立外鍵，指向一的一方的主鍵
- 多對(duì)多
? 建立第三張中間表，中間表至少包含兩個(gè)外鍵，分別關(guān)聯(lián)兩方主鍵
1.2 準(zhǔn)備數(shù)據(jù)
學(xué)生表
create table student(
id int auto_increment primary key comment '主鍵ID',
name varchar(10) comment '姓名',
no varchar(10) comment '學(xué)號(hào)'
) comment '學(xué)生表';
insert into student values (null, '黛綺絲', '2000100101'),(null, '謝遜','2000100102'),
(null, '殷天正', '2000100103'),(null, '韋一笑', '2000100104');
課程表
create table course(
id int auto_increment primary key comment '主鍵ID',
name varchar(10) comment '課程名稱'
) comment '課程表';
insert into course values (null, 'Java'), (null, 'PHP'),
(null , 'MySQL') ,(null, 'Hadoop');
學(xué)生課表關(guān)聯(lián)表
create table student_course(
id int auto_increment comment '主鍵' primary key,
studentid int not null comment '學(xué)生ID',
courseid int not null comment '課程ID',
constraint fk_courseid foreign key (courseid) references course (id),
constraint fk_studentid foreign key (studentid) references student (id)
)comment '學(xué)生課程中間表';
insert into student_course values (null,1,1),(null,1,2),(null,1,3),
(null,2,2),(null,2,3),(null,3,4);
dept表
-- 創(chuàng)建dept表，并插入數(shù)據(jù)
create table dept(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部門名稱'
)comment '部門表';
INSERT INTO dept (id, name) VALUES (1, '研發(fā)部'), (2, '市場(chǎng)部'),(3, '財(cái)務(wù)部'),
(4,'銷售部'), (5, '總經(jīng)辦'), (6, '人事部');
創(chuàng)建emp表
-- 創(chuàng)建emp表，并插入數(shù)據(jù)
create table emp(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年齡',
job varchar(20) comment '職位',
salary int comment '薪資',
entrydate date comment '入職時(shí)間',
managerid int comment '直屬領(lǐng)導(dǎo)ID',
dept_id int comment '部門ID'
)comment '員工表';
-- 添加外鍵
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
INSERT INTO emp (id, name, age, job,salary, entrydate, managerid, dept_id)
VALUES
(1, '金庸', 66, '總裁',20000, '2000-01-01', null,5),
(2, '張無忌', 20, '項(xiàng)目經(jīng)理',12500, '2005-12-05', 1,1),
(3, '楊逍', 33, '開發(fā)', 8400,'2000-11-03', 2,1),
(4, '韋一笑', 48, '開發(fā)',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '開發(fā)',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '程序員鼓勵(lì)師',6600, '2004-10-12', 2,1),
(7, '滅絕', 60, '財(cái)務(wù)總監(jiān)',8500, '2002-09-12', 1,3),
(8, '周芷若', 19, '會(huì)計(jì)',48000, '2006-06-02', 7,3),
(9, '丁敏君', 23, '出納',5250, '2009-05-13', 7,3),
(10, '趙敏', 20, '市場(chǎng)部總監(jiān)',12500, '2004-10-12', 1,2),
(11, '鹿杖客', 56, '職員',3750, '2006-10-03', 10,2),
(12, '鶴筆翁', 19, '職員',3750, '2007-05-09', 10,2),
(13, '方東白', 19, '職員',5500, '2009-02-12', 10,2),
(14, '張三豐', 88, '銷售總監(jiān)',14000, '2004-10-12', 1,4),
(15, '俞蓮舟', 38, '銷售',4600, '2004-10-12', 14,4),
(16, '宋遠(yuǎn)橋', 40, '銷售',4600, '2004-10-12', 14,4),
(17, '陳友諒', 42, null,2000, '2011-10-12', 1,null);
1.3 概述
問：select * from emp , dept 最終結(jié)果為什么是102條？
? emp 記錄17條，dept 記錄6條，17*6=102
? 其實(shí)就是員工表emp所有的記錄(17) 與 部門表dept所有記錄(6) 的所有組合情況，這種現(xiàn)象稱為笛卡爾積，在這些組合情況中有許多記錄是無用的，我們需要消除笛卡爾積
笛卡爾積
左邊集合與右邊集合所有情況
怎么消除笛卡爾積？
? 多表連接時(shí)添加條件即可
select * from emp , dept where emp.dept_id = dept.id;
1.4 內(nèi)連接
內(nèi)連接查詢的是兩張表交集部分的數(shù)據(jù)。(也就是綠色部分的數(shù)據(jù))
內(nèi)連接分為兩種：隱式內(nèi)連接、顯式內(nèi)連接
- 隱式內(nèi)連接
SELECT 字段列表
FROM 表1 , 表2
WHERE 條件 ... ;
-
顯式內(nèi)連接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 連接條件 ... ;
案例
- 查詢每一個(gè)員工的姓名 , 及關(guān)聯(lián)的部門的名稱 (顯式內(nèi)連接實(shí)現(xiàn))
SELECT emp.name '員工姓名', dept.name '部門名稱'
FROM emp
join dept on emp.dept_id = dept.id
- 查詢每一個(gè)員工的姓名 , 及關(guān)聯(lián)的部門的名稱 (隱式內(nèi)連接實(shí)現(xiàn))
select emp.name , dept.name
from emp , dept
where emp.dept_id = dept.id ;
1.5 外連接
外連接分為兩種，分別是：左外連接 和 右外連接。
- 左外連接
SELECT 字段列表
FROM 表1
LEFT [ OUTER ] JOIN 表2 ON 條件 ... ;
左外連接相當(dāng)于查詢表1(左表)的所有數(shù)據(jù)，當(dāng)然也包含表1和表2交集部分的數(shù)據(jù)。
- 右外連接
SELECT 字段列表
FROM 表1
RIGHT [ OUTER ] JOIN 表2 ON 條件 ... ;
? 右外連接相當(dāng)于查詢表2(右表)的所有數(shù)據(jù)，當(dāng)然也包含表1和表2交集部分的數(shù)據(jù)
案例
- 查詢emp表的所有數(shù)據(jù), 和對(duì)應(yīng)的部門信息
select e.*, d.name
from emp e
left outer join dept d on e.dept_id = d.id;
- 查詢dept表的所有數(shù)據(jù), 和對(duì)應(yīng)的員工信息(右外連接)
select d.*, e.*
from emp e
right outer join dept d on e.dept_id = d.id;
注意事項(xiàng)
? 左外連接和右外連接是可以相互替換的，只需要調(diào)整在連接查詢時(shí)SQL中，表結(jié)構(gòu)的先后順序就可以了。而我們?cè)谌粘ｉ_發(fā)使用時(shí)，更偏向于左外連接
1.6 自連接
? 是把一張表連接查詢多次。
SELECT 字段列表
FROM 表A 別名A
JOIN 表A 別名B ON 條件 ... ;
? 注意事項(xiàng)
? 在自連接查詢中，必須要為表起別名，要不然我們不清楚所指定的條件、返回的字段，到底是哪一張表的字段。
案例
- 查詢員工 及其 所屬領(lǐng)導(dǎo)的名字
select a.name , b.name
from emp a , emp b
where a.managerid = b.id;
- 查詢所有員工 emp 及其領(lǐng)導(dǎo)的名字 emp , 如果員工沒有領(lǐng)導(dǎo), 也需要查詢出來
select a.name '員工', b.name '領(lǐng)導(dǎo)'
from emp a
left join emp b on a.managerid =b.id;
1.7 聯(lián)合查詢 union
? 對(duì)于union查詢，就是把多次查詢的結(jié)果合并起來，形成一個(gè)新的查詢結(jié)果集。
- 對(duì)于聯(lián)合查詢的多張表的列數(shù)必須保持一致，字段類型也需要保持一致。
- union all 會(huì)將全部的數(shù)據(jù)直接合并在一起，union 會(huì)對(duì)合并之后的數(shù)據(jù)去重。
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;
案例
- 將薪資低于 5000 的員工 , 和 年齡大于 50 歲的員工全部查詢出來
? 使用union，因?yàn)閡nion不僅合并，還會(huì)對(duì)數(shù)據(jù)進(jìn)行去重，可能會(huì)存在一些人薪資低于5000并且年齡大于50歲
select * from emp where salary < 5000
union
select * from emp where age > 50;
1.8 子查詢
? SQL語句中嵌套SELECT語句，稱為嵌套查詢，又稱子查詢。
語法
SELECT *
FROM t1
WHERE column1 = ( SELECT column1 FROM t2 );
子查詢外部的語句可以是INSERT / UPDATE / DELETE / SELECT 的任何一個(gè)。
分類
- 標(biāo)量子查詢（子查詢結(jié)果為單個(gè)值）
- 列子查詢 (子查詢結(jié)果為一列)
- 行子查詢 (子查詢結(jié)果為一行)
- 表子查詢 (子查詢結(jié)果為多行多列)
1.8.1 標(biāo)量子查詢
? 子查詢返回的結(jié)果是單個(gè)值（數(shù)字、字符串、日期等），最簡(jiǎn)單的形式，這種子查詢稱為標(biāo)量子查詢。
常用的操作符： = <> > >= < <=
- 查詢 “銷售部” 的所有員工信息
select *
from emp
where dept_id = (
select id
from dept
where name = '銷售部'
);
- 查詢?cè)?“方東白” 入職之后的員工信息
select *
from emp
where entrydate > (
select entrydate
from emp
where name = '方東白'
);
1.8.2 列子查詢
? 子查詢返回的結(jié)果是一列（可以是多行），這種子查詢稱為列子查詢。
常見操作符
| 操作符 | 描述 |
|---|---|
| IN | 在指定的集合范圍之內(nèi)，多選一 |
| NOT IN | 不在指定的集合范圍之內(nèi) |
| ANY | 子查詢返回列表中，有任意一個(gè)滿足即可 |
| SOME | 與ANY等同，使用SOME的地方都可以使用ANY |
| ALL | 子查詢返回列表的所有值都必須滿足 |
案例
- 查詢 “銷售部” 和 “市場(chǎng)部” 的所有員工信息
select *
from emp
where dept_id in (
SELECT id
from dept
where name = '銷售部' or name = '市場(chǎng)部'
)
- 查詢比 財(cái)務(wù)部 所有人工資都高的員工信息
select *
from emp
where salary > (
select MAX(salary)
from emp
where dept_id = (
select id
from dept
where name ='財(cái)務(wù)部'
)
)
或者
select *
from emp
where salary > all (
select salary
from emp
where dept_id =(
select id
from dept
where name = '財(cái)務(wù)部'
)
);
- 查詢比研發(fā)部其中任意一人工資高的員工信息
select *
from emp
where salary > any (
select salary
from emp
where dept_id = '1'
);
1.8.3 行子查詢
子查詢返回的結(jié)果是一行（可以是多列），這種子查詢稱為行子查詢
常見操作符：= 、<> 、IN 、NOT IN
- 查詢與 “張無忌” 的薪資及直屬領(lǐng)導(dǎo)相同的員工信息 ;
SELECT *
from emp
where (salary , managerid) = (
select salary , managerid
from emp
where name = '張無忌'
)
1.8.4 表子查詢
子查詢返回的結(jié)果是多行多列，這種子查詢稱為表子查詢。
常用操作符：IN
- 查詢與 “鹿杖客” , “宋遠(yuǎn)橋” 的職位和薪資相同的員工信息
select *
from emp
where (salary ,managerid) in (
select salary ,managerid
from emp
where name ='鹿杖客' or name = '宋遠(yuǎn)橋'
)
二、事務(wù)
2.1 簡(jiǎn)介
? 事務(wù) 是一組操作的集合，它是一個(gè)不可分割的工作單位，事務(wù)會(huì)把所有的操作作為一個(gè)整體一起向系統(tǒng)提交或撤銷操作請(qǐng)求，即這些操作要么同時(shí)成功，要么同時(shí)失敗
? 在業(yè)務(wù)邏輯執(zhí)行之前開啟事務(wù)，執(zhí)行完畢后提交事務(wù)。如果執(zhí)行過程中報(bào)錯(cuò)，則回滾事務(wù)，把數(shù)據(jù)恢復(fù)到事務(wù)開始之前的狀態(tài)。
注意：
? 默認(rèn)MySQL的事務(wù)是自動(dòng)提交的，也就是說，當(dāng)執(zhí)行完一條DML語句時(shí)，MySQL會(huì)立即隱式的提交事務(wù)。
2.2 操作演示
數(shù)據(jù)準(zhǔn)備
drop table if exists account;
create table account(
id int primary key AUTO_INCREMENT comment 'ID',
name varchar(10) comment '姓名',
money double(10,2) comment '余額'
) comment '賬戶表';
insert into account(name, money) VALUES ('張三',2000), ('李四',2000);
正常情況
-- 1. 查詢張三余額
select * from account where name = '張三';
-- 2. 張三的余額減少1000
update account set money = money - 1000 where name = '張三';
-- 3. 李四的余額增加1000
update account set money = money + 1000 where name = '李四';
? 但是假設(shè)我們?cè)诘诙街蟪鲥e(cuò)，張三余額減少了1000，但是由于拋異常了，沒有執(zhí)行完第三步或者沒有執(zhí)行第三步，所以憑空的少了1000
就目前來說，三條sql語句就是三條事務(wù)，他們會(huì)自動(dòng)提交
為了避免這種情況，我們需要把這些操作控制在一個(gè)事務(wù)范圍內(nèi)
2.3 控制事務(wù)
2.3.1 控制事務(wù)一
- 查看/設(shè)置事務(wù)提交方式
如果為‘1’就是自動(dòng)提交，如果為‘0’就是手動(dòng)提交
SELECT @@autocommit ;
SET @@autocommit = 0 ;
- 提交事務(wù)
COMMIT;
- 回滾事務(wù)
ROLLBACK;
注意：
? 上述的這種方式，我們是修改了事務(wù)的自動(dòng)提交行為, 把默認(rèn)的自動(dòng)提交修改為了手動(dòng)提交, 此時(shí)我們執(zhí)行的DML語句都不會(huì)提交, 需要手動(dòng)的執(zhí)行commit進(jìn)行提交。
2.3.2 控制事務(wù)二
不會(huì)修改事務(wù)的提交方式，直接開啟事務(wù)
- 開啟事務(wù)
START TRANSACTION 或 BEGIN ;
- 提交事務(wù)
COMMIT;
- 回滾事務(wù)
ROLLBACK;
-- 開啟事務(wù)
start transaction;
-- 1. 查詢張三余額
SELECT * from account where name ='張三';
-- 2. 張三的余額減少1000
update account set money = (money - 1000) where name = '張三';
-- 3. 李四的余額增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常執(zhí)行完畢, 則提交事務(wù)
commit;
-- 如果執(zhí)行過程中報(bào)錯(cuò), 則回滾事務(wù)
-- rollback;
2.4 事務(wù)四大特性 ACID
-
原子性（Atomicity）：事務(wù)是不可分割的最小操作單元，要么全部成功，要么全部失敗。
-
一致性（Consistency）：事務(wù)完成時(shí)，必須使所有的數(shù)據(jù)都保持一致狀態(tài)
? 比如張三和李四轉(zhuǎn)賬，無論是轉(zhuǎn)賬成功還是轉(zhuǎn)賬失敗，兩個(gè)人金額加起來是恒定不變的
- 隔離性（Isolation）：數(shù)據(jù)庫系統(tǒng)提供的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的獨(dú)立環(huán)境下運(yùn)行。
? 比如A事務(wù)和B事務(wù)同時(shí)操作數(shù)據(jù)庫，A事務(wù)在操作的時(shí)候不會(huì)影響并發(fā)的B事務(wù)的執(zhí)行，同理B事務(wù)的執(zhí)行不會(huì)影響A事務(wù)的執(zhí)行
- 持久性（Durability）：事務(wù)一旦提交或回滾，它對(duì)數(shù)據(jù)庫中的數(shù)據(jù)的改變就是永久的
2.5 并發(fā)事務(wù)問題
- 臟讀
? 一個(gè)事務(wù)讀到另一個(gè)事務(wù)還沒有提交的數(shù)據(jù)
? 事務(wù)A對(duì)ID為1的數(shù)據(jù)進(jìn)行更改，但是并沒有提交，此時(shí)恰好事務(wù)B也讀取ID為1的數(shù)據(jù)，好巧不巧的讀取到了事務(wù)A修改之后的數(shù)據(jù)
? 很顯然這是不正確的，因?yàn)槭聞?wù)A并沒有提交數(shù)據(jù)，事務(wù)B讀取到的內(nèi)容應(yīng)該還是修改之前的內(nèi)容
- 不可重復(fù)度
? 一個(gè)事務(wù)先后讀取同一條記錄，但兩次讀取的數(shù)據(jù)不同，稱之為不可重復(fù)讀
? 如下所示，事務(wù)A讀取ID為1的數(shù)據(jù)，讀取完成后事務(wù)B對(duì)ID為1的數(shù)據(jù)進(jìn)行更改，更改完成之后事務(wù)A又對(duì)ID為1的數(shù)據(jù)進(jìn)行讀取，發(fā)現(xiàn)兩次讀取的數(shù)據(jù)內(nèi)容不一樣
- 幻讀
? 一個(gè)事務(wù)按照條件查詢數(shù)據(jù)時(shí)，沒有對(duì)應(yīng)的數(shù)據(jù)行，但是在插入數(shù)據(jù)時(shí)，又發(fā)現(xiàn)這行數(shù)據(jù)已經(jīng)存在，好像出現(xiàn)了“幻讀”
? 事務(wù)A首先向數(shù)據(jù)庫中查詢ID為1的數(shù)據(jù)，發(fā)現(xiàn)沒有
? 此時(shí)事務(wù)B向數(shù)據(jù)庫插入了一條ID為1的數(shù)據(jù)
? 事務(wù)A向數(shù)據(jù)庫插入ID為1的數(shù)據(jù)報(bào)錯(cuò)，因?yàn)镮D為1，不能再次插入
? 事務(wù)A向數(shù)據(jù)庫讀取ID為1的數(shù)據(jù)，發(fā)現(xiàn)找不到（讀不到事務(wù)B提交的數(shù)據(jù)，因?yàn)槲覀兘鉀Q了“不可重復(fù)讀”的問題）數(shù)據(jù)。
? 執(zhí)行了插入明明有了，但是找不到，這就叫出現(xiàn)了幻覺，所以我們叫幻讀
2.6 事務(wù)隔離級(jí)別
為了解決并發(fā)事務(wù)所引發(fā)的問題
事務(wù)隔離級(jí)別越高，數(shù)據(jù)越安全，但是性能越低。從上到下，事務(wù)隔離級(jí)別遞增
| 隔離級(jí)別 | 臟讀 | 不可重復(fù)讀 | 幻讀 |
|---|---|---|---|
| Read uncommitted 讀未提交 | √ | √ | √ |
| **Read committed ** 讀已提交 | × | √ | √ |
| Repeatable Read(默認(rèn)) 可重復(fù)讀 | × | × | √ |
| Serializable 串行化 | × | × | × |
Oracle默認(rèn)隔離級(jí)別是 Read committed 讀已提交
- 查看事務(wù)隔離級(jí)別
SELECT @@TRANSACTION_ISOLATION;
- 設(shè)置事務(wù)隔離級(jí)別
-
- SESSION 設(shè)置會(huì)話級(jí)別，僅代表針對(duì)當(dāng)前客戶端窗口有效
- GLOBAL 針對(duì)所有客戶端會(huì)話窗口有效
SET [ SESSION | GLOBAL ]
TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
ISOLATION 為隔離的意思文章來源地址http://www.zghlxwxcb.cn/news/detail-464650.html
- 設(shè)置隔離級(jí)別為“可重復(fù)讀”
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
| 隔離級(jí)別 | 臟讀 | 不可重復(fù)讀 | 幻讀 |
|---|---|---|---|
| Read uncommitted 讀未提交 | √ | √ | √ |
| **Read committed ** 讀已提交 | × | √ | √ |
| Repeatable Read(默認(rèn)) 可重復(fù)讀 | × | × | √ |
| Serializable 串行化 | × | × | × |
Oracle默認(rèn)隔離級(jí)別是 Read committed 讀已提交文章來源：http://www.zghlxwxcb.cn/news/detail-464650.html
- 查看事務(wù)隔離級(jí)別
SELECT @@TRANSACTION_ISOLATION;
- 設(shè)置事務(wù)隔離級(jí)別
-
- SESSION 設(shè)置會(huì)話級(jí)別，僅代表針對(duì)當(dāng)前客戶端窗口有效
- GLOBAL 針對(duì)所有客戶端會(huì)話窗口有效
SET [ SESSION | GLOBAL ]
TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
ISOLATION 為隔離的意思
- 設(shè)置隔離級(jí)別為“可重復(fù)讀”
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
到了這里，關(guān)于MySQL基礎(chǔ) — 多表查詢以及事務(wù)管理的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
