一、基本概念

記錄 -- 行

字段 -- 列

主鍵： 唯一

外鍵

完整性

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

數據庫類型

• 層次...
• 網狀...
• 關系...

二、關系運算

集合運算

• 并
  • 定義

    •   抽取兩張表中所有行的運算
      

    •   須去重
      

  • 符號：U
• 交
  • 定義

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

• 差
  • 定義

    •   抽取其中一張表中獨有行的運算
      

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

    •   將兩張表的所有行進行排列組合
      

  • 符號：X

關系運算

• 投影
  • 定義

    •   抽取列的運算
      

• 選擇
  • 定義

    •   抽取行的運算
      

• 連接
  • 定義

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

    •   通過連接運算，可以將兩張表連起來
      

• 除
  • 定義

    •   從“被除表格”中調取“除表格”中包含的所有行
      

    •   去掉“除表格”中所有列的運算
      

  • 總結

    •   笛卡爾積的逆運算
      

  • 實例

    
    
    
    
    
    

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

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

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

四、SQL基本操作

select

• like模糊搜索
  • %： 表示任意字符串
  • _： 表示一個字符
• 檢索條件
  • where
  • between ... and ...
  • is null
    • select * from product where price is null
  • order by
• 實例
  • select * from table
  • select * from product where name like '%果' or name like '% 莓' order by price
• 計算函數
  • count()
    • count(*)
      求行數
    • count(列名)
      求非空值行數
    • count(distinct 列名)
      求排除空值及重復行的行數
    • 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 ...）
• 連接

  • 內部連接

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

  • 外部連接

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

  • 實例

  • 相關參考

    • 連接--》維基

create

• 實例

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

insert

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

update

• 實例
  update product set name = "甜瓜" where name = “香瓜”

delete

• 實例
  delete from product where name = "蘋果"

drop

• 實例
  drop database DBName
  drop table TableName

高級檢索

• 復制表
  • 既復制表結構，也復制內容
    • select * into B from A
  • 只復制表結構
    • select * into B from A where 1 = 2
    • select top 0 * into B from A
  • 只復制內容
    • 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)點：通用性強。
    • 缺點：當數據量較大時向后翻頁，NOT IN中的數據過大會影響性能。
    • 適用：數據量不大
  • 直接限制返回區(qū)間
    • SELECT * FROM table WHERE 查詢條件 ORDER BY 排序條件 LIMIT ((頁碼-1)*頁大小),頁大小;
    • 優(yōu)劣
      • 優(yōu)點：寫法簡單。
      • 缺點：當頁碼和頁大小過大時，性能明顯下降。
      • 適用：數據量不大
  • 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)點：速度快，特別是當id為主鍵時。
    • 缺點：適用面窄，要求排序條件單一且可比較。
    • 適用：簡單排序（特殊情況也可嘗試轉換成類似可比較值處理）

  • 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)點：在數據量較大時相比NOT IN有優(yōu)勢。
    • 缺點：小數據量時不如NOT IN。
    • 適用：大部分分頁查詢需求。

• 查詢表中某列最小數

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

• 建立臨時表

create table #Tmp(
...
)

• 操作表中重復記錄

查找表中重復記錄

• 單個字段

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

• 多個字段

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

• 完全重復
  • select distinct * from tableName

刪除表中重復記錄

• 單個字段

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
)

• 多個字段

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
)

• 重復記錄保留1條

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

五、數據庫的應用

事務

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

鎖

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

六、概念總結

存儲過程 vs 函數

• 存
  • 用戶定義的一系列sql語句的集合
  • 涉及特定表或對象的任務
• 函
  • 數據庫中已定義的方法
  • 不涉及特定表

游標

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

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

sql注入攻擊

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

存儲過程格式與調用