1. MySQL是一個關(guān)系型數(shù)據(jù)庫, 關(guān)系型數(shù)據(jù)庫描述的就是實體與實體之間的關(guān)系.聊到實體與實體之間的關(guān)系(表與表之間的關(guān)系), 就勢必要聊到約束:
約束的作用:
保證數(shù)據(jù)的完整性.
單表約束:
主鍵約束: primary key //一般后邊還會寫上auto_increment
唯一約束: unique
非空約束: not null
多表約束:
外鍵約束: foreign key
案例:
創(chuàng)建一個部門表(dept) 和 員工信息表(employee),
正常的邏輯應(yīng)該是, 先刪除員工, 再清除部門,
而如果這兩張表之間沒有主外鍵約束的話, 就可以直接刪除部門, 而沒有先清除該部門的員工, 這樣做是不符合實際生活中的邏輯的.
- 可視化界面的使用
SQLyog
3. 多表的分析和設(shè)計
在實際開發(fā)中, 經(jīng)常會向數(shù)據(jù)庫中創(chuàng)建多個表, 而且多個表之間都是有關(guān)系的.
關(guān)系分類:
一對多的關(guān)系:
一個部門可以有多個員工, 一個員工只能屬于某一個部門.
一個分類下有多個商品, 一個商品只能屬于某一個分類.
一個用戶產(chǎn)生多個訂單, 一個訂單只能屬于某一個用戶.
多對多的關(guān)系:
一個學(xué)生可以選擇多門課程, 一個課程也可以被多個學(xué)生選擇.
一個訂單包含多個商品, 一個商品也可以在多個訂單中.
一對一的關(guān)系:
一個公司只能對應(yīng)一個注冊地址, 一個注冊地址只能對應(yīng)一個公司.
4. 多表建表原則:
一對多的建表原則:
在"多"的一方創(chuàng)建一個字段, 作為外鍵, 指向"一"的一方的主鍵.
多對多的建表原則:
需要創(chuàng)建第三張表(中間表), 在中間表中至少需要有兩個字段, 分別作為外鍵指向多對多雙方的主鍵.
一對一的建表原則:
唯一外鍵對應(yīng):
示例:
公司表
cid int primary key auto_increment
cname varchar(20)
地址表:
aid int primary key auto_increment
aname varchar(20)
//再新建一列: cno int unique
主鍵對應(yīng):
兩張表的主鍵保證一一對應(yīng).
5. 多表查詢
連接查詢:
交叉連接(了解即可):
select * from A,B; //最終結(jié)果獲取到的是兩個表的笛卡爾積, 一般不用這種做法.
內(nèi)連接:(inner join)
顯示內(nèi)連接:
select * from A inner join B on 條件;
隱式內(nèi)連接:
select * from A,B where 條件;
外連接:(outer join)
左外連接:
select * from A left outer join B on 條件;
右外連接:
select * from A right outer join B on 條件;
內(nèi)連接和外連接的區(qū)別:
內(nèi)連接: 查到的是 兩個表的交集.
左外連接: 左邊表的全部 和 兩個表的交集.
右外連接: 右邊表的全部 和 兩個表的交集.
子查詢:
概述:
指的是一條語句的查詢條件需要依賴另一條語句的查詢結(jié)果.
in: 只要滿足其中一個就行.
any: 只要比給定的所有值中的任意一個大(小)就行.
all: 必須比給定的所有值都要大(小).
需求:
查詢收入在5000元及其以上的員工的所在部門;
SELECT * FROM dept WHERE did IN (SELECT dno FROM employee WHERE salary >= 5000);
查詢部門編號比"收入在5000元及其以上的員工所在部門編號任意一個" 大的 所有部門的名稱及部門編號
select * from dept where did > any(select dno from employee where salary >=5000);
查詢部門編號比"收入在5000元及其以上的員工所在部門編號任意一個" 都大的 所有部門的名稱及部門編號
select * from dept where did > all(select dno from employee where salary >=5000);
6. 事務(wù)
概述:
事務(wù)指的是邏輯上的一組操作, 組成這組操作的各個邏輯單元要么全部都成功, 要么全失敗.
//MySQL中默認情況下,一條語句是一個事務(wù), 事務(wù)會自動提交.
//Oracle中默認事務(wù)是不提交的, 需要手動提交事務(wù).
MySQL事務(wù)管理的命令:
start transaction; //開啟事務(wù)
SQL語句1; //要執(zhí)行的SQL語句
SQL語句2;
commit //提交事務(wù)
rollback; //回滾事務(wù)
show variables like '%commit%';
事務(wù)的特性:
1. 原子性.
強調(diào)事務(wù)的多個操作不可分割.
2. 一致性.
強調(diào)事務(wù)執(zhí)行的前后, 數(shù)據(jù)完整性保持一致.
3. 隔離性
強調(diào)事務(wù)執(zhí)行過程中, 不應(yīng)該受到其他事務(wù)的干擾.
4. 持久性
強調(diào)事務(wù)一旦結(jié)束, 數(shù)據(jù)就持久到數(shù)據(jù)庫中.
如果不考慮事務(wù)的隔離性, 有可能會引發(fā)一些安全性的問題.
讀的方面:
臟讀: 指的是一個事務(wù)讀到另一個事務(wù)未提交的數(shù)據(jù).
不可重復(fù)讀: 指的是一個事務(wù)讀到了另一個事務(wù)已經(jīng)提交的 update的數(shù)據(jù),
導(dǎo)致多次查詢結(jié)果不一致.
虛讀/幻讀: 指的是一個事務(wù)讀到了另一個事務(wù)已經(jīng)提交的 insert的數(shù)據(jù),
導(dǎo)致多次查詢結(jié)果不一致.
寫的方面: (了解就行, 不用重點掌握)
丟失更新.
解決事務(wù)的讀問題:
設(shè)置事務(wù)的隔離級別:
read uncommitted: //未提交讀
臟讀, 不可重復(fù)讀, 虛讀都有可能發(fā)生.
read committed: //已提交數(shù)據(jù)庫
避免臟讀, 但是不可重復(fù)讀和虛讀是有可能發(fā)生的.
repeatable read: //可重復(fù)讀
避免臟讀, 不可重復(fù)讀. 但是虛讀是有可能發(fā)生的.
serializable: 串行化的
避免臟讀, 不可重復(fù)讀, 虛讀
安全性:
read uncommitted < read committed < repeatable read< Serializable
效率:
read uncommitted > read committed > repeatable read > Serializable
一般數(shù)據(jù)庫設(shè)置: read committed 和 repeatable read
MySQL默認: repeatable read
Oracle默認: read committed
7.演示:
演示"臟讀"的產(chǎn)生:
1. 開啟兩個cmd窗口A, B.
2. 查看當(dāng)前的事務(wù)的隔離級別:
select @@tx_isolation
//默認為: repeatable read
3. 將A窗口的隔離級別修改為: read uncommitted;
set session transaction isolation level read uncommitted;
4. 再次查看下兩個窗口的的隔離級別:
select @@tx_isolation;
5. 在A, B兩個窗口中分別開啟事務(wù):
6. 在B窗口中完成轉(zhuǎn)賬.
//注意: 先不要提交事務(wù).
7. 在A窗口中進行查詢.
//發(fā)現(xiàn)轉(zhuǎn)賬已經(jīng)成功, 說明臟讀已經(jīng)發(fā)生了.
//臟讀: 一個事物讀取到另一個事務(wù)未提交的數(shù)據(jù).
演示"不可重復(fù)讀"的產(chǎn)生:
1. 開啟兩個cmd窗口A, B.
2. 查看當(dāng)前的事務(wù)的隔離級別:
select @@tx_isolation
//默認為: repeatable read
3. 將A窗口的隔離級別修改為: read committed;
set session transaction isolation level read committed;
4. 再次查看下兩個窗口的的隔離級別:
select @@tx_isolation;
5. 在A, B兩個窗口中分別開啟事務(wù):
6. 在B窗口中完成轉(zhuǎn)賬.
//注意: 先不要提交事務(wù).
7. 在A窗口中進行查詢.
//發(fā)現(xiàn)A窗口中的數(shù)據(jù)沒有變化, 說明臟讀沒有發(fā)生.
//臟讀: 一個事物讀取到另一個事務(wù)未提交的數(shù)據(jù).
8. 在B窗口中提交事務(wù).
//發(fā)現(xiàn)A窗口中的數(shù)據(jù)已經(jīng)變化了, 說明"不可重復(fù)讀"已經(jīng)產(chǎn)生了,
//不可重復(fù)讀: 指的是一個事務(wù)讀到了另一個事務(wù)已經(jīng)提交的 update的數(shù)據(jù),
//導(dǎo)致多次查詢結(jié)果不一致.
避免演示"不可重復(fù)讀"的產(chǎn)生:
1. 開啟兩個cmd窗口A, B.
2. 查看當(dāng)前的事務(wù)的隔離級別:
select @@tx_isolation
//默認為: repeatable read
3. 將A窗口的隔離級別修改為: repeatable read;
set session transaction isolation level repeatable read;
4. 再次查看下兩個窗口的的隔離級別:
select @@tx_isolation;
5. 在A, B兩個窗口中分別開啟事務(wù):
6. 在B窗口中完成轉(zhuǎn)賬.
//主要: 先不要提交事務(wù).
7. 在A窗口中進行查詢.
//發(fā)現(xiàn)A窗口中的數(shù)據(jù)沒有變化, 說明臟讀沒有發(fā)生.
//臟讀: 一個事物讀取到另一個事務(wù)未提交的數(shù)據(jù).
8. 在B窗口中提交事務(wù).
//發(fā)現(xiàn)A窗口中的數(shù)據(jù)已經(jīng)變化了, 說明"不可重復(fù)讀"已經(jīng)產(chǎn)生了,
//不可重復(fù)讀: 指的是一個事務(wù)讀到了另一個事務(wù)已經(jīng)提交的 update的數(shù)據(jù),
//導(dǎo)致多次查詢結(jié)果不一致.
演示: Serializable 串行化的
8. truncate table 和 delete from 區(qū)別?
//truncate(重置), delete(刪除表數(shù)據(jù)), drop(刪除數(shù)據(jù)庫, 數(shù)據(jù)表)
truncate table:
可以清除數(shù)據(jù), 而且會重置Id, 相當(dāng)于是: 把表摧毀了, 然后創(chuàng)建一張和該表一模一樣的表.
屬于DDL語句, 不可以結(jié)合 事務(wù)使用.
delete from:
可以清除數(shù)據(jù), 但是不會重置id.
屬于DML語句, 可以結(jié)合 事務(wù) 使用.
