一、基本概念

記錄 -- 行

字段 -- 列

主鍵： 唯一

外鍵

完整性

• 數(shù)據(jù)完整性
  • 實(shí)體完整性
    表中每一記錄是唯一實(shí)體
  • 域完整性
    • 表中列須滿足某種特定數(shù)據(jù)類型的約束
      • 取值范圍
      • 精度
  • 參照完整性
    • 兩表的主鍵和外鍵數(shù)據(jù)應(yīng)一致
  • 用戶定義的完整性
• 完整性約束類型
  • 與表有關(guān)的約束
    • 列約束
      • not null
    • 表約束
      • PRIMARY KEY
      • UNIQUE
      • foreign key
      • CHECK約束
    • 特定域（數(shù)據(jù)類型）的約束
  • 斷言

數(shù)據(jù)庫類型

• 層次...
• 網(wǎng)狀...
• 關(guān)系...

二、關(guān)系運(yùn)算

集合運(yùn)算

• 并
  • 定義

    •   抽取兩張表中所有行的運(yùn)算
      

    •   須去重
      

  • 符號：U
• 交
  • 定義

    •   抽取既在表1夺欲，又在表2中的行
      

• 差
  • 定義

    •   抽取其中一張表中獨(dú)有行的運(yùn)算
      

  • 符號： -
• 笛卡爾積
  • 定義

    •   將兩張表的所有行進(jìn)行排列組合
      

  • 符號：X

關(guān)系運(yùn)算

• 投影
  • 定義

    •   抽取列的運(yùn)算
      

• 選擇
  • 定義

    •   抽取行的運(yùn)算
      

• 連接
  • 定義

    •   如表中某字段是其他表中的主鍵
      

    •   通過連接運(yùn)算跪帝，可以將兩張表連起來
      

• 除
  • 定義

    •   從“被除表格”中調(diào)取“除表格”中包含的所有行
      

    •   去掉“除表格”中所有列的運(yùn)算
      

  • 總結(jié)

    •   笛卡爾積的逆運(yùn)算
      

  • 實(shí)例

    
    
    
    
    
    

三、E-R模型 & 規(guī)范化

E-R模型關(guān)系
表格規(guī)范化

• 類別
  • 非范式
    沒有除去數(shù)據(jù)重復(fù)的表
  • 第一范式
    • 定義
      • 表的每一列均是不可分割的基本數(shù)據(jù)項(xiàng)
      • 同一列中不能有多個(gè)值
    • 反例
      • 表里的電話字段些阅，既有手機(jī)號伞剑，又有座機(jī)號
    • 總結(jié)
      • 原子性約束
      • 字段不能再分
  • 第二范式
    • 定義
      • 滿足第一范式
      • 表中每一行可以被唯一的區(qū)分
      • 其他列完全依賴于主鍵
    • 總結(jié)
      • 唯一性約束
      • 每一行記錄都可以通過主鍵被唯一標(biāo)識
  • 第三范式
    • 定義
      • 滿足第二范式
      • 不能存在其他數(shù)據(jù)表中的非主鍵字段
      • 主鍵以外的字段依賴主鍵，且不能依賴表中其他字段
      • 每一列數(shù)據(jù)和主鍵直接相關(guān)市埋，而非間接相關(guān)
    • 總結(jié)
      • 冗余性約束
      • 直接依賴黎泣，而非傳遞依賴
  • BCNF
  • 第四范式
• 設(shè)計(jì)目標(biāo)
  • 規(guī)范化數(shù)據(jù)庫：將冗余降到最低
    • 需執(zhí)行開銷很大的連接操作
  • 非規(guī)范化數(shù)據(jù)庫：優(yōu)化讀取時(shí)間
    • 通常用于構(gòu)建高可擴(kuò)展性系統(tǒng)

四、SQL基本操作

select

• like模糊搜索
  • %： 表示任意字符串
  • _： 表示一個(gè)字符
• 檢索條件
  • where
  • between ... and ...
  • is null
    • select * from product where price is null
  • order by
• 實(shí)例
  • select * from table
  • select * from product where name like '%果' or name like '% 莓' order by price
• 計(jì)算函數(shù)
  • count()
    • count(*)
      求行數(shù)
    • count(列名)
      求非空值行數(shù)
    • count(distinct 列名)
      求排除空值及重復(fù)行的行數(shù)
    • select count as ... from ...
  • avg()
  • sum()
    • select sum(field) as ... from ...
  • min()
  • max()
• 分組：group by
  • select place, avg(price) from product group by 地域 having avg（price） >= 200
• 子查詢檢索
  • select * from where ... in（select * from ... where ...）
• 連接

  • 內(nèi)部連接

    • 選擇數(shù)值相同的行進(jìn)行連接的連接
    • 與一般的where語句等價(jià)
    • 類別
      • 相等連接
      • 自然連接
        • 兩表中相同列只出現(xiàn)一次
      • 交叉連接
        • 即笛卡爾積連接

  • 外部連接

    • 定義
      • 保留其中一表格的所有行
      • 將另一方中沒有的行設(shè)置為空值
    • 類別
      • 左外連接
        • 左表為主表缤谎，右邊表為副表
      • 右外連接
        • 右表為主表抒倚，左表為副表
      • 全連接
        • 左右外連接的并集

  • 實(shí)例

  • 相關(guān)參考

    • 連接--》維基

create

• 實(shí)例

create table product
（
id number(3, 0)，
name char(20), 
price number(10, 0), 
primary key(id) 
）
        
create database DBName

insert

• 實(shí)例
  insert into product (id, name, price) values(101, '香瓜'坷澡， 800)

update

• 實(shí)例
  update product set name = "甜瓜" where name = “香瓜”

delete

• 實(shí)例
  delete from product where name = "蘋果"

drop

• 實(shí)例
  drop database DBName
  drop table TableName

高級檢索

• 復(fù)制表
  • 既復(fù)制表結(jié)構(gòu)托呕，也復(fù)制內(nèi)容
    • select * into B from A
  • 只復(fù)制表結(jié)構(gòu)
    • select * into B from A where 1 = 2
    • select top 0 * into B from A
  • 只復(fù)制內(nèi)容
    • insert into B select * from A
• 分頁查詢
  • 倒序top
    select top 3 userid from ( select top 7 userid from ... order by userid) order by userid desc
  • 排除top

select top 5 * from UserInfo where UserId not in
(select top (n-1)*5 UserID from UserInfo order by UserID asc) order by UserID asc

SELECT TOP 頁大小 * FROM table WHERE 主鍵 NOT IN
(
SELECT TOP (頁碼-1)*頁大小 主鍵 FROM table WHERE 查詢條件 ORDER BY 排序條件
)

• ORDER BY 排序條件
  • not in
    • 優(yōu)點(diǎn)：通用性強(qiáng)。
    • 缺點(diǎn)：當(dāng)數(shù)據(jù)量較大時(shí)向后翻頁洋访，NOT IN中的數(shù)據(jù)過大會(huì)影響性能镣陕。
    • 適用：數(shù)據(jù)量不大
  • 直接限制返回區(qū)間
    • SELECT * FROM table WHERE 查詢條件 ORDER BY 排序條件 LIMIT ((頁碼-1)*頁大小),頁大小;
    • 優(yōu)劣
      • 優(yōu)點(diǎn)：寫法簡單。
      • 缺點(diǎn)：當(dāng)頁碼和頁大小過大時(shí)姻政，性能明顯下降呆抑。
      • 適用：數(shù)據(jù)量不大
  • MAX

SELECT TOP 頁大小 * FROM table WHERE 查詢條件 AND id >
(
SELECT ISNULL(MAX(id),0) FROM 
(
SELECT TOP ((頁碼-1)*頁大小) id FROM table WHERE 查詢條件 ORDER BY id 
) AS tempTable
) 

• ORDER BY id

  • 優(yōu)劣

    • 優(yōu)點(diǎn)：速度快，特別是當(dāng)id為主鍵時(shí)汁展。
    • 缺點(diǎn)：適用面窄鹊碍，要求排序條件單一且可比較。
    • 適用：簡單排序（特殊情況也可嘗試轉(zhuǎn)換成類似可比較值處理）

  • ROW_NUMBER()

SELECT TOP 頁大小 * FROM 
(
SELECT TOP (頁碼*頁大小) ROW_NUMBER() OVER (ORDER BY 排序條件) AS RowNum, * FROM table WHERE 查詢條件
) AS tempTable
WHERE RowNum BETWEEN (頁碼-1)*頁大小+1 AND 頁碼*頁大小

• ORDER BY RowNum

  • 優(yōu)劣
    • 優(yōu)點(diǎn)：在數(shù)據(jù)量較大時(shí)相比NOT IN有優(yōu)勢食绿。
    • 缺點(diǎn)：小數(shù)據(jù)量時(shí)不如NOT IN侈咕。
    • 適用：大部分分頁查詢需求。

• 查詢表中某列最小數(shù)

  • select top 1 col from A order by col
  • select * from A where col = ( select min(col) from A )

• 建立臨時(shí)表

create table #Tmp(
...
)

• 操作表中重復(fù)記錄

查找表中重復(fù)記錄

• 單個(gè)字段

select * from people where peopleId in 
(
select   peopleId  from   people  
group  by   peopleId  having  count(peopleId) > 1
)

• 多個(gè)字段

select * from vitae a where (a.peopleId,a.seq) in   
(
select peopleId,seq from vitae 
group by peopleId,seq  having count(*) > 1
)

• 完全重復(fù)
  • select distinct * from tableName

刪除表中重復(fù)記錄

• 單個(gè)字段

delete from people where peopleId  in 
(
select   peopleId  from people  
group  by   peopleId   having  count(peopleId) > 1
)
    delete from people where peopleId  in 
(
select   peopleId  from people  
group  by   peopleId   having  count(peopleId) > 1
)  and rowid not in 
(
select min(rowid) from   people  group by peopleId  having count(peopleId )>1
)

• 多個(gè)字段

delete from vitae a where (a.peopleId,a.seq) in   
(
select peopleId,seq from vitae 
group by peopleId,seq having count(*) > 1
)
    delete from vitae a where (a.peopleId,a.seq) in   
(
select peopleId,seq from vitae 
group by peopleId,seq having count(*) > 1
)and rowid not in 
(
select min(rowid) from vitae group by peopleId,seq having count(*)>1
)

• 重復(fù)記錄保留1條

    select distinct * into #Tmp from tableName
    drop table tableName
    select * into tableName from #Tmp
    drop table #Tmp

五器紧、數(shù)據(jù)庫的應(yīng)用

事務(wù)

• 定義
  • 作為單個(gè)邏輯工作單元執(zhí)行的一系列操作
• A
  • 原子性
    • 事務(wù)中的所有操作耀销，要么全部完成，要么全部不完成
    • 要么提交铲汪，要么回滾
    • 沒有中間狀態(tài)
• C
  • 一致性
    • 事務(wù)開始前與結(jié)束后熊尉，數(shù)據(jù)庫的完整性約束沒有被破壞
    • 數(shù)據(jù)庫機(jī)制層面
      • 唯一約束
      • 外鍵約束
      • check約束
      • 觸發(fā)器
    • 業(yè)務(wù)層面
      • 保持業(yè)務(wù)的一致性
• I
  • 隔離性
    • 事務(wù)執(zhí)行互不干擾
    • 利用鎖和阻塞來保證事務(wù)之間的隔離性
    • 并發(fā)事務(wù)中
      • 不同事務(wù)同時(shí)操作相同數(shù)據(jù)
• D
  • 持久性
    • 事務(wù)一旦完成，所做修改便持久地保存在數(shù)據(jù)庫中

鎖

• 定義
  • 進(jìn)程間由于爭奪資源而處于無限期的等待狀態(tài)
• 類別
  • 共享鎖
    • 又稱讀鎖
    • 某事務(wù)對數(shù)據(jù)加共享鎖
      • 其他事務(wù)也只能加共享鎖
      • 其他事務(wù)不能加互斥鎖
      • 有事務(wù)讀數(shù)據(jù)時(shí)掌腰，其他事務(wù)
• 不能執(zhí)行除讀以外的操作
  • 獨(dú)占鎖
    • 用于數(shù)據(jù)修改操作
    • 某事物為數(shù)據(jù)加互斥鎖狰住，則其他事務(wù)不能再加任何鎖

六、概念總結(jié)

存儲(chǔ)過程 vs 函數(shù)

• 存
  • 用戶定義的一系列sql語句的集合
  • 涉及特定表或?qū)ο蟮娜蝿?wù)
• 函
  • 數(shù)據(jù)庫中已定義的方法
  • 不涉及特定表

游標(biāo)

• 作用
  • 定位結(jié)果集的行
  • 從結(jié)果集中每次提取一條記錄的機(jī)制
• 判斷游標(biāo)是否已到了最后
  • 判斷全局變量@@FETCH_STATUS
  • 不為0表示到了最后或出錯(cuò)

• 盡量用存儲(chǔ)過程執(zhí)行查詢的原因
  • 封裝
    • 盡可能使各層的功能齿梁、職責(zé)隔離催植，不相互影響
  • 安全
    • 有效防防sql注入攻擊
  • 優(yōu)化
    • sql語句執(zhí)行需先編譯肮蛹，再執(zhí)行
    • 存儲(chǔ)過程可高效優(yōu)化

sql注入攻擊

• 定義
  • 將sql命令插入到...中
    • web表單輸入域
    • 頁面請求的查詢字符串
  • 欺騙服務(wù)器執(zhí)行惡意的sql命令
• 防范
  • 替換單引號
  • 限制權(quán)限
  • 刪除用戶輸入內(nèi)容中的所有連字符
  • 盡可能使用存儲(chǔ)過程
  • 檢查數(shù)據(jù)的合法性

存儲(chǔ)過程格式與調(diào)用