選項(xiàng)
Oracle：甲骨文公司收費(fèi)
Microsoft SQL Server：微軟公司收費(fèi)
MySQL 與 PostgreSQL的對比
1、開源許可
MySQL是GPL許可胖缤，使用者對代碼所做的修改也必須開源
PostgreSQL是BSD許可李茫，允許使用者對代碼所做的修改進(jìn)行保密
2、開發(fā)背景
MySQL的背后是一個成熟的商業(yè)公司Sun公司
PostgreSQL的背后是一個龐大的志愿開發(fā)組
3、并發(fā)模式
MySQL使用的是線程模式淤刃，線程模式對資源消耗比較少，支持更多連接
PostgreSQL使用的是進(jìn)程模式吱型，進(jìn)程模式對多CPU利用率比較高
關(guān)系模型的完整性約束：實(shí)體完整性逸贾、參照完整性、用戶定義的完整性
完整性：數(shù)據(jù)正確性（對象自身符合現(xiàn)實(shí)）和相容性（對象之間符合邏輯）
實(shí)體完整性：主鍵唯一且不能為空
參照完整性：外鍵要么為空津滞，要么是另一張表存在的主鍵值
用戶定義的完整性：例如非主鍵的非空性和唯一性
集合運(yùn)算：并 U铝侵，交 n ，差 -触徐，笛卡爾積 X
笛卡爾積：A表的行 和 B表的行咪鲜，兩兩組合 成目標(biāo)表
關(guān)系運(yùn)算：選擇（行過濾）、投影（列過濾）撞鹉、連接（等值連接疟丙、自然連接颖侄、外連接）、除
等值連接：在A表和B表的笛卡爾積中享郊，過濾出 A表指定屬性 = B表指定屬性 的行览祖，組成目標(biāo)表
自然連接：在A表和B表的笛卡爾積中，過濾出 A表和B表所有的同名屬性都相等 的行炊琉，組成T表展蒂，T表去除重復(fù)屬性，得到目標(biāo)表
懸浮組：等值連接中温自，AB兩表中被舍棄的行
外連接：AB表的等值連接T + AB表的懸浮組D玄货，組成外鏈接。D加入T時(shí)悼泌，沒有的屬性松捉，值取null
左外連接：外連接中，只保留A表懸浮組
右外連接：外連接中馆里，只保留B表懸浮組
除：從A表中提取A表有而B表沒有的列隘世，去重作為T表；T表中的一些行與B表的笛卡爾積為A的子集鸠踪，這些行組成目標(biāo)表
SQL（Structured Query Language）：結(jié)構(gòu)化查詢語言
數(shù)據(jù)字典：保存關(guān)于表丙者、視圖、索引营密、用戶的設(shè)置
SQL操作對象：模式（表的命名空間）械媒、表、視圖评汰、索引纷捞、斷言、觸發(fā)器
create schema wang authorization wang -- 創(chuàng)建模式被去，指定授權(quán)用戶
drop schema wang cascade -- 級聯(lián)刪除模式主儡，另有 restrict，只允許刪除空模式
create table wang.Student -- 創(chuàng)建表惨缆，指定模式
(Sno char(9) primary key, -- 主鍵
Sname char(20) unique, -- 唯一性約束
Spno char(4) not null, -- 非空約束
Sgrade int check(Sgrade>0), -- 條件約束糜值，可以是where字句能用到的所有條件
foreign key (Spno) references Course(Cno)) -- 外鍵
alter table student -- 修改表
add column Sname char(20) unique -- 添加新列
add unique(Sname), -- 添加完整性約束
alter column Ssex int -- 修改列數(shù)據(jù)類型
drop column Ssex cascade -- 級聯(lián)刪除列，另有restrict坯墨，只允許刪除未被其他對象（表寂汇，視圖）引用的列
drop table student cascade -- 級聯(lián)刪除表，另有restrict捣染，只允許刪除未被其他對象（表健无，視圖）引用的表
數(shù)據(jù)類型：定長字符串char(n)，不定長字符串varchar(n)液斜，2字節(jié)整數(shù)smallint累贤，4字節(jié)整數(shù)int叠穆，8字節(jié)整數(shù)bigint，布爾值boolean臼膏，日期date硼被，時(shí)間time，時(shí)間戳timestamp渗磅，精度為n的浮點(diǎn)數(shù)float(n)
搜索路徑
創(chuàng)建表時(shí)嚷硫，若不指定模式，則默認(rèn)采用搜索路徑里設(shè)置的模式
查看：show search_path
設(shè)置 set search_path to 模式1,模式2
索引
加快查詢始鱼、分組仔掸、排序、連接速度医清，但需要額外占用存儲空間起暮，增刪改表時(shí)需要額外時(shí)間更新索引
create index Sno on SC(Sno ASC, Cno DESC) -- 給SC表創(chuàng)建索引Sno，索引屬性為Sno和Cno会烙。ASC為升序负懦，DESC為降序
create unique index Sno on SC(Sno) -- 唯一索引，性能更高柏腻，插入值需要唯一纸厉，但允許為空
alter index Sno rename to Cno -- 重命名索引
drop index Sno -- 刪除索引
索引設(shè)計(jì)原則
1、一個頻繁更新的表五嫂，索引數(shù) 和 索引列數(shù) 盡可能少
2颗品、行數(shù)少表、重復(fù)值多的列 不要建索引
3沃缘、在unique列上建立unique索引
4躯枢、一個頻繁排序和分組的表，對應(yīng)列建立索引
數(shù)據(jù)查詢
/** 列查詢 **/
select distinct Sno,lower(Sname),2018-Sage birth -- 選擇列孩灯，可以進(jìn)行表達(dá)式運(yùn)算闺金，可以取別名逾滥，distinct表示去重
from Student -- 來源表
where Sno=1 -- 查詢條件
order by Sage,(case when Sage=null then 0 else 100 end) desc -- 結(jié)果排序峰档，這里實(shí)現(xiàn)了三元表達(dá)式運(yùn)算，desc表示降序
/** 統(tǒng)計(jì)查詢 **/
select count(distinct Sno) --distinct表示去重
from Student
order by ( case when store_id is null then 0 when store_id=25 then 1 else 2 end) -- 自定義排序
/** 組查詢 **/
select Cno,Cname, avg(Sno) -- 這里得到一個組列表寨昙，Cno是一個組共有的讥巡，Cname不是一個組公有的，會得到一個次序在前的值舔哪，avg是對一個組進(jìn)行統(tǒng)計(jì)
from Student
group by Cno
having count(*) > 3 -- 組過濾
查詢條件
比較：=欢顷、>、< 等
范圍：between and捉蚤，not between and
集合：in抬驴，not in
字符匹配：like炼七，not like
空值：is null，is not null
多重條件：and布持，or豌拙，not
聚集函數(shù)
count(*)、count(distinct 列) 题暖、count(列)按傅，max(列)、min(列)胧卤、sum(列)唯绍、avg(列)、group_concat(列)
連接查詢
/** 等值連接 **/
select student.*,sc.* -- 取所有列
from student,sc
where student.Sno = sc.Sno -- 等值連接
/** 非等值連接 **/
select student.*,sc.*
from student,(select ... from ...) as sc -- sc為派生表
where student.Sno > sc.Sno -- 在笛卡爾積中過濾出符合條件的行枝誊，組成目標(biāo)表
/** 自然連接 **/
select student.Sno,Sname,Ssex -- 指定具體列况芒，去除重復(fù)。兩表中不重復(fù)的列侧啼，可以不指明表名
from student,sc
where student.Sno = sc.Sno
/** 自連接 **/
select first.Sno,second.Spno -- 指定具體列牛柒，去除重復(fù)。兩表中不重復(fù)的列痊乾，可以不指明表名
from student first,student second -- 給表取別名皮壁，用于區(qū)分
where first.Sno = second.Spno
/** 外連接 **/
select student.Sno,Sname,Ssex
from student outer join sc on(student.Sno = sc.Sno)
/** 左外連接 **/
select student.Sno,Sname,Ssex
from student left outer join sc on(student.Sno = sc.Sno)
/** 右外連接 **/
select student.Sno,Sname,Ssex
from student right outer join sc on(student.Sno = sc.Sno)
/** 多表連接 **/
select student.Sno,Sname,Ssex
from student,sc,course
嵌套查詢
/** 不相關(guān)嵌套查詢 **/
-- 先處理子查詢，再處理父查詢
select Sname
from student
where Sdept in -- 子查詢的結(jié)果為單列集合哪审，那么謂詞為 in蛾魄、>any、>all湿滓、=any滴须、!=all等，some與any同義
(select Sdept from ...); -- 內(nèi)層查詢(子查詢)
/** 相關(guān)嵌套查詢 **/
-- 1叽奥、忽略子查詢扔水，先得到一個結(jié)果T；2朝氓、遍歷T魔市，計(jì)算出子查詢，過濾出符合條件的行
select Sname
from student x
where Sno >= -- 子查詢的結(jié)果為單列單行赵哲，那么謂詞為>待德、<、=等
(select avg(grade) from student y where y.sno = x.sno); -- 子查詢用到父查詢中的表
select Sname
from student x
where exists -- 子查詢的結(jié)果多列集合枫夺，那么謂詞為exists将宪、not exists
(select * from student y where y.sno = x.sno)
select student.*,sc.*
from student,(select * from student) sc --子查詢在from子句，作為一個表
where student.Sno = sc.Sno
SELECT * from
(SELECT *, MAX(age) AS max_age FROM student GROUP BY gender) student -- 子查詢結(jié)果表的別名
ORDER BY max_age -- 組函數(shù)不能被order by引用，可以改成這樣的嵌套查詢
select age+(SELECT MAX(score) AS maxScore FROM score) from student -- 子查詢在select子句
集合查詢
select * from student
union -- 并集较坛，另外交集intersect印蔗，差集except
select * from student;
數(shù)據(jù)更新
insert -- 插入行
into student(Sno,Sname) -- 未指定值的列，默認(rèn)為null
values('2000','hogen'), ('300','alina');
insert
into student(Sno,Sname)
子查詢; -- 以子查詢?yōu)檩斎?
update student -- 更新行
set Sage=Sage+2
where Sno='3333' -- 可以是子查詢
delete -- 刪除行
from student
where Sno='33333' -- 可以是子查詢
視圖
一個或幾個表導(dǎo)出的虛表丑勤，數(shù)據(jù)庫只保存視圖的定義喻鳄，不保存視圖對應(yīng)的數(shù)據(jù)
視圖消解：查詢視圖時(shí)，結(jié)合用戶查詢和視圖定義确封，最終形成對表的查詢
視圖的作用：簡化SQL語句除呵，隱匿原表數(shù)據(jù)；不能提升查詢性能
/** 單表視圖 **/
create view student_view(Sno,Sname,Sbirth) -- 保留主鍵的視圖稱為行列子集視圖
as
select Sno,Sname,2018-Sage -- Sbirth為派生屬性（虛擬屬性爪喘，表達(dá)式屬性）颜曾，表只保存最簡屬性，視圖提供派生屬性
where Sage > 30
with check option -- 視圖消解時(shí)秉剑，遇到不符合 where 字句的操作泛豪，拒絕執(zhí)行
/** 多表視圖 **/
create view student_view(Sno,Sname)
多表查詢
drop view student_view cascade -- 級聯(lián)刪除
權(quán)限
/** 授權(quán) **/
grant update(Sno),select -- 操作另有insert、delete侦鹏、references诡曙、all privileges，可以指定列也可以不指定
on table student
to user1 -- 另有public略水，表示所有用戶
with grant option; -- 允許轉(zhuǎn)授權(quán)（傳播權(quán)限）
/** 回收權(quán)限 **/
revoke select
on table student
from user1;
/** 創(chuàng)建用戶价卤，指定系統(tǒng)角色 **/
create user hogen with dba; -- 沒有指定系統(tǒng)角色的話，默認(rèn)為connect
/** 自定義角色 **/
create role role1; -- 創(chuàng)建角色
grant select
on table student -- 另有 view
to role1; -- 給角色授權(quán)
grant role1
to role2,hogen -- 把角色的權(quán)限授予用戶或其他角色
with admin option; -- 允許轉(zhuǎn)授權(quán)給其他角色
revoke select
on table student
from role1; -- 回收角色權(quán)限
系統(tǒng)角色
dba：超級管理員渊涝，擁有包括創(chuàng)建用戶慎璧、創(chuàng)建模式的所有權(quán)限
resource：有創(chuàng)建表、操作表的權(quán)限
connect：只有操作表的權(quán)限
斷言
create assertion ass -- 創(chuàng)建斷言
check (60>=( -- 拒絕插入第61條跨释，即拒絕將導(dǎo)致斷言為否的操作
select count(*) from Student)
);
drop assertion ass; -- 刪除斷言
觸發(fā)器
create trigger trigger1 -- 觸發(fā)器的命名空間也是模式
after update on table1 -- 觸發(fā)器只能定義在表上胸私。after 另有 before，update 另有delete鳖谈、insert
referencing
oldrow as oldStudent -- 行數(shù)據(jù)別名
newrow as newStudent
for each row -- 行級觸發(fā)器岁疼，另有語句級觸發(fā)器 for each statement，執(zhí)行一條語句只觸發(fā)一次
when(newStudent.Sage > oldStudent.Sage) -- 觸發(fā)條件
begin
insert into ... ; -- 觸發(fā)動作
end
drop trigger trigger1 on table1; -- 刪除觸發(fā)器
關(guān)系規(guī)范化
碼：由一個或多個屬性組成
超碼：能唯一確定一行的碼
候選碼：最小范圍的超碼缆娃，即只保留超碼中的必要屬性
主碼：從多個候選碼中選一個為主碼
主屬性：任一候選碼包含的屬性
非主屬性：非碼屬性捷绒，即主屬性以外的屬性
外碼：另一個關(guān)系的候選碼
完全函數(shù)依賴：非主屬性 完全函數(shù)依賴于 候選碼，即確定的候選碼 能確定 所有非主屬性
部分函數(shù)依賴：非主屬性 部分函數(shù)依賴于 超碼龄恋，即部分確定的候選碼 就能確定 所有非主屬性
第一范式 1NF：一個屬性不可拆分疙驾，多個屬性之間不能重復(fù)
第二范式 2NF：任一非主屬性完全函數(shù)依賴于任一候選碼
第三范式 3NF：任一非主屬性只函數(shù)依賴于候選碼
第三范式修正BCNF：任一主屬性 完全函數(shù)依賴且只函數(shù)依賴于 其他候選碼
E-R圖（Entity Relationship Diagram凶伙，實(shí)體-聯(lián)系圖）
矩形框：實(shí)體
橢圓框：屬性
菱形框：聯(lián)系郭毕，與實(shí)體連接，標(biāo)明（1:1函荣，1:m显押，n:m）
事務(wù)
ACID特性
原子性（Atomicity）：事務(wù)不可拆分
一致性（Consistency）：事務(wù)執(zhí)行前后扳肛，數(shù)據(jù)庫保持一致性（正確性）狀態(tài)
隔離性（Isolation）：事務(wù)與事務(wù)之間互相隔離
持續(xù)性（Durability）：永久性，事務(wù)一旦提交乘碑，永久改變
begin transaction; -- 開始定義事務(wù)
sql語句
commit; -- 提交事務(wù)
rollback; -- 某種情況下挖息，顯式撤銷事務(wù)
封鎖
排他鎖（X鎖）：寫鎖，獨(dú)占鎖兽肤，獨(dú)享鎖套腹，互斥鎖
共享鎖（S鎖）：讀鎖
一級封鎖協(xié)議：事務(wù)在修改數(shù)據(jù)之前加X鎖，事務(wù)結(jié)束時(shí)釋放鎖
二級封鎖協(xié)議：在一級封鎖協(xié)議的基礎(chǔ)上资铡，事務(wù)在讀取數(shù)據(jù)之前加S鎖电禀，讀取結(jié)束時(shí)釋放鎖
三級封鎖協(xié)議：在一級封鎖協(xié)議的基礎(chǔ)上，事務(wù)在讀取數(shù)據(jù)之前加S鎖笤休，事務(wù)結(jié)束時(shí)釋放鎖
活鎖：在沒有事務(wù)排隊(duì)機(jī)制的系統(tǒng)上尖飞，一個事務(wù)請求加鎖，可能長時(shí)間被插隊(duì)店雅，導(dǎo)致這個事務(wù)沒法完成
死鎖：兩個事務(wù)互相依賴于被對方加鎖的數(shù)據(jù)政基，導(dǎo)致兩個事務(wù)都無法完成
預(yù)防死鎖
一次封鎖法：一次性把所需數(shù)據(jù)全部加鎖，用完釋放；缺點(diǎn)：降低了系統(tǒng)并發(fā)能力
順序封鎖法：預(yù)先規(guī)定一個加鎖順序，所有事務(wù)都遵循該順序绒净；缺點(diǎn)：順序難以維護(hù)剩愧，不能處理動態(tài)選擇的封鎖對象
死鎖診斷
超時(shí)判定：事務(wù)等待超過時(shí)限，判定事務(wù)遇到死鎖诈泼；缺點(diǎn)：容易誤判
等待圖分析：數(shù)據(jù)庫管理系統(tǒng)能獲取到事務(wù)等待圖，判斷有沒有循環(huán)等待
死鎖解除
數(shù)據(jù)庫管理系統(tǒng)撤銷一個事務(wù)，釋放其持有的鎖
事務(wù)加鎖規(guī)則
可串行性：給定一個初始狀態(tài)摧扇；1、多個事務(wù)依次啟動挚歧，并行執(zhí)行扛稽；2、同樣的幾個事務(wù)依次串行執(zhí)行滑负；如果12兩種方式的結(jié)果無差異在张，則稱這幾個事務(wù)可串行化（具有可串行性）
兩段鎖協(xié)議（2PL）：第一階段（擴(kuò)展階段），讀寫數(shù)據(jù)前加鎖矮慕，不解鎖帮匾；第二階段（收縮階段），只解鎖痴鳄，不加鎖
所有事務(wù)都遵循兩段鎖協(xié)議 是 這些事務(wù)可串行化 的充分條件
顯式封鎖：本對象被加鎖
隱式封鎖：本對象的父對象被加鎖
封鎖沖突：一個對象只能有一個顯式鎖 或者 一個隱式鎖瘟斜，因此給對象加鎖時(shí)系統(tǒng)需要檢查它是否已經(jīng)有顯式鎖、隱式鎖，還要檢查子對象有沒有顯式鎖
意向鎖：給一個對象加鎖時(shí)螺句，同時(shí)給他的所有父對象加意向鎖虽惭，使得其他事物給父對象加鎖時(shí)，不用向下檢查
意向共享鎖：IS鎖
意向排他鎖：IX鎖
數(shù)據(jù)庫優(yōu)化
1蛇尚、建立合適的索引
2芽唇、sql 語句優(yōu)化
3、創(chuàng)建冗余字段取劫，用來存儲（查詢匆笤、排序、分組用到的）需要大量計(jì)算得來的數(shù)值谱邪，用觸發(fā)器更新該字段
4疚膊、去除多余字段，多余關(guān)聯(lián)虾标，杜絕一個接口服務(wù)多個場景導(dǎo)致的字段多余
5寓盗、利用elastic search 實(shí)現(xiàn)模糊搜索
SQL案例
1、每組取 最小計(jì)算值 所在的那條記錄
難點(diǎn)：計(jì)算值可以被order by璧函，但是group by時(shí) 所取的行傀蚌，不是order by的第一行
select o.*, abs(score - 50) as minscore from student o, # abs(score - 50) 即為本例針對的計(jì)算值
(SELECT name, min(abs(score - 50)) as minscore FROM student group by name) t # 找到name和最小計(jì)算值的對應(yīng)關(guān)系，作為臨時(shí)表
where o.name=t.name and abs(score - 50)=t.minscore # 等值連接
2蘸吓、不存在才插入
INSERT INTO student(name) SELECT '張三' FROM DUAL -- DUAL為mysql的一個輔助表
WHERE NOT EXISTS(SELECT id FROM student WHERE name='張三') -- 此語句用于防止重復(fù)插入
3善炫、更新 或 插入
REPLACE INTO student(id, num, name) VALUES(3, 5, '張山') -- 主建或 唯一鍵 存在則更新，不存在則插入
