sql必知必會讀書筆記

第三課： 排序檢索數(shù)據(jù)

  1. distinct關鍵字：distinct 列名1，列名2，列名3
    1. DISTINCT 關鍵字會作用于每一列，然后取最大的那一個返回；
  2. order by使用方法
    1. order by子句只能出現(xiàn)在select語句中最后一條子句，否則報錯；
    2. 多個列排序時：order by 列名1，列名2，僅在多行具有相同的列名1時，才會按照列名2進行排序，如果每個列名1都是唯一的，則不會按列名2進行排序；
    3. select 列名1，列名2，列名3 from table order by 2,3：先按照列名2進行排序，再按照列名3進行排序（列名2的相對位置是2，列名3的相對位置是3）
    4. desc和asc用法：desc/asc只能作用于一列，如果想在多個列上進行排序，，需要對每一列指定desc/asc關鍵字。

第四課：使用where子句

4.2 where子句操作符

操作符 說明 舉例
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
!< 不小于
> 大于
>= 大于等于
!> 不大于
BETWEEN...AND... 在指定的兩個值之間 WHERE price BETWEEN 5 AND 10;
IS NULL 為null值 WHERE name IS NULL;//name為空的記錄
IS NOT NULL 不為null值 WHERE name IS NOT NULL //name不為空的記錄

第五課 高級數(shù)據(jù)過濾

  1. and
  2. or：當一個where子句中有多個and和or時，一定要加括號，因為and的優(yōu)先級比or高；
  3. in：指定操作范圍，相當于多個or；in的優(yōu)點：
    1. in的語法更加清楚，直觀；
    2. 在與其他and和or操作符組合使用in時，求值順序更容易管理；
    3. in的效率高于多個or的組合；
    4. 最大優(yōu)點：可以包含其他select語句，可以動態(tài)建立where子句。
  4. not ：
    1. 功能：否定其后所跟的任何條件；
    2. 語法：not從不單獨使用，總是和其他操作符一起使用；not關鍵字可以用在要過濾的列前，而不僅是在其后；
    3. 如何使用：****先寫出條件表達式，然后在條件表達式前面(where后面)加not**** 
      //這兩條語句等價
SELECT * FROM t_project WHERE NOT Project_ID  IN (1,2,3,4,5,6);

SELECT * FROM t_project WHERE Project_ID NOT IN (1,2,3,4,5,6);
    4. not 比!=的功能更加強大，尤其是在復雜的子句中，如上述和in的聯(lián)合使用中。

第6課 用通配符進行過濾

通配符搜索只能作用于文本字段，非文本字段不能用通配符進行搜索；

6.1 "%"通配符

  1. %表示任何字符（0個，1個，多個字符）出現(xiàn)任意次數(shù)（0次，1次，多次）；
  2. %可以在任何地方使用任何次；
  3. %不能匹配NULL；

6.2 "_"通配符

  1. _ 只匹配一個字符，因此有幾個字符需要匹配就需要多個_；

6.3 "[]"通配符

  1. []通配符用來指定一個字符集，它必須匹配指定位置（通配符的位置）的一個字符，但是并不是所有的DBMS都支持此通配符；
//找出所有名字以J或M起頭的聯(lián)系人
WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;
//找出所有名字不以J或M起頭的聯(lián)系人
WHERE cust_contact LIKE '[^JM]%' ORDER BY cust_contact;

6.4 通配符的缺點及使用注意事項

  1. 缺點：通配符搜索比較耗時，性能低下；
  2. 注意事項：
    1. 不要過度使用通配符：如果其他操作符能達到相同的目的，應該使用其他操作符；
    2. 把通配符置于開始處，搜索起來是最慢的，在確實需要使用通配符時，也盡量不要把它們用在搜索模式的開始處；
    3. 仔細注意通配符的位置。如果放錯地方，可能不會返回想要的數(shù)據(jù)；

第7課 創(chuàng)建計算字段

  1. 計算字段并不是數(shù)據(jù)庫中真實存在的字段，而是運行時，在select語句內(nèi)創(chuàng)建的，用來接收數(shù)據(jù)庫中多個字段計算后的結(jié)果。

7.2 拼接字段

將多個字段拼接成一個字段

從數(shù)據(jù)庫中查詢返回的字段可以來源于多張表，也可以是自己創(chuàng)建的計算字段。

SELECT CONCAT(Project_Caption,'(',Project_ID,")") 
AS project_title 
FROM t_project 
ORDER BY t_project.Project_ID;
  1. 不同的數(shù)據(jù)庫采用不一樣的方式來拼接多個字段

|數(shù)據(jù)庫類型|拼接方式|舉例|
|---|:----:|----|---|
|Access、SQL Server|+|SELECT vend_name + ' (' + vend_country + ')'+FROM VendorsORDER BY vend_name;|
|DB2、Oracle、PostgreSQL、SQLite、Open Office Base||||SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'AS vend_title FROM Vendors ORDER BY vend_name;|
|MySQL、MariaDB|concat(str...)|SELECT Concat(vend_name, ' (', vend_country, ')') AS vend_title FROM Vendors ORDER BY vend_name;|

  1. 去掉空格
名稱 作用 舉例
RTRIM 切割字符串右邊的空格 RTRIM(vend_country)
LTRIM 切割字符串左邊的空格 LTRIM(vend_country)
TRIM 切割字符串左右兩邊的空格 TRIM(vend_country)
  1. 使用別名（AS）

給計算字段取一個名字，方便以后的操作；

7.3 執(zhí)行算術計算

對字段做加減乘除運算

SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

7.4 測試計算

select 去掉from子句后就可以用來測試

SELECT 3 * 2;將返回6，SELECT Trim(' abc ');將返回abc

第8課 使用數(shù)據(jù)處理函數(shù)

8.1 函數(shù)以及帶來的問題

  • 函數(shù)的作用：高效，方便。
  • 問題：每一個DBMS都有特定的函數(shù)，而且名稱和語法可能不同；

8.2 使用函數(shù)

  1. 大多數(shù)SQL實現(xiàn)支持以下類型的函數(shù)。
    1. 處理文本字符串（如刪除或填充值，轉(zhuǎn)換值為大寫或小寫）的文本函數(shù)。
      2.數(shù)值數(shù)據(jù)上進行 算術操作（如返回絕對值，進行代數(shù)運算）的數(shù)值函數(shù)。
    2. 日期和時間值并從這些值中提取特定成分（如返回兩個日期之差，檢查日期有效性）的日期和時間函數(shù)。
    3. 返回DBMS正使用的特殊信息（如返回用戶登錄信息）的系統(tǒng)函數(shù)。
  2. 常用的文本處理函數(shù)
    函 數(shù) 說 明
    LEFT()（或使用子字符串函數(shù)） 返回字符串左邊的字符
    LENGTH()（也使用DATALENGTH()或LEN()） 返回字符串的長度
    LOWER()（Access使用LCASE()） 將字符串轉(zhuǎn)換為小寫
    LTRIM() 去掉字符串左邊的空格
    RIGHT()（或使用子字符串函數(shù)） 返回字符串右邊的字符
    LTRIM() 去掉字符串右邊的空格
    SOUNDEX() 返回字符串的SOUNDEX值
    UPPER()（Access使用UCASE()） 將字符串轉(zhuǎn)換為大寫
  3. 日期和時間處理函數(shù)
  4. 數(shù)值處理函數(shù)
    函 數(shù) 說 明
    ABS() 返回一個數(shù)的絕對值
    COS() 返回一個角度的余弦
    EXP() 返回一個數(shù)的指數(shù)值
    PI() 返回圓周率
    SIN() 返回一個角度的正弦
    SQRT() 返回一個數(shù)的平方根
    TAN() 返回一個角度的正切

第9課 匯總數(shù)據(jù)

9.1 聚集函數(shù)

函 數(shù) 作用 說明
AVG() 返回某列的平均值
COUNT() 返回某列的行數(shù)
MAX() 返回某列的最大值 返回最大的日期，數(shù)值，排序后列的最后一行
MIN() 返回某列的最小值 返回最小的日期，數(shù)值，排序后列的第一行
SUM() 返回某列值之和

1. AVG()

  1. 特別說明

    1. 只能用于單個列；
    2. 而且必須給出列名；
    3. 多個列就必須使用多次avg()；
    4. AVG()函數(shù)忽略列值為NULL的行；

  2. 舉例說明

    SELECT
    AVG(
        DISTINCT t_project.Project_code
    ) AS project_code
FROM
    t_project; 

    SELECT
    AVG(
        DISTINCT t_project.Project_code
    ) AS project_code,
    AVG(
        DISTINCT t_project.Project_ID
    ) AS project_Id
FROM
    t_project;

2. COUNT()

  1. 特別說明
    1. 使用COUNT(*)對表中行的數(shù)目進行計數(shù)，不管表列中包含的是空值（NULL）還是非空值，但是不能用DISTINCT修飾，即COUNT(DISTINCT *)是違法的。
    2. 使用COUNT(column)對特定列中具有值的行進行計數(shù)，忽略NULL值，可以用DISTINCT修飾，即COUNT(DISTINCT column)是合法的。

3. MAX()

  1. 特別說明
    1. 必須指定列名；
    2. 忽略列值為null的行
    3. 雖然MAX()一般用來找出最大的數(shù)值或日期值，但許多（并非所有）DBMS允許將它用來返回任意列中的最大值，包括返回文本列中的最值。在用于文本數(shù)據(jù)時，MAX()返回按該列排序后的最后一行。 
SELECT MAX(prod_price) AS max_price
FROM Products;

4. MIN()

  1. 特別說明
    1. 必須指定列名；
    2. 忽略列值為null的行
    3. 雖然MIN()一般用來找出最小的數(shù)值或日期值，但許多（并非所有）DBMS允許將它用來返回任意列中的最小值，包括返回文本列中的最值。在用于文本數(shù)據(jù)時，MAX()返回按該列排序后的第一行。

5. SUM()

  1. SUM()函數(shù)忽略列值為NULL的行
SELECT
    SUM(3 * quantity) AS total_price
FROM
    OrderItems
WHERE
    order_num = 20005;

9.2 組合聚集函數(shù)

SELECT
    COUNT(*) AS num_items,
    MIN(prod_price) AS price_min,
    MAX(prod_price) AS price_max,
    AVG(prod_price) AS price_avg
FROM
    Products;

第10課 分組數(shù)據(jù)

10.1 創(chuàng)建分組（GROUP BY）

  1. GROUP BY子句可以包含多個列名，可以對分組進行嵌套，更細致的進行數(shù)據(jù)分組；
  2. 如果在GROUP BY子句中嵌套了分組，數(shù)據(jù)將在最后指定的分組上進行匯總。換句話說，在建立分組時，指定的所有列都一起計算（所以
    不能從個別的列取回數(shù)據(jù)）。
  3. GROUP BY子句中列出的每一列都必須是檢索列或有效的表達式（但不能是聚集函數(shù)）。如果在SELECT中使用表達式，則必須在GROUP BY子句中指定相同的表達式。不能使用別名。
  4. 大多數(shù)SQL實現(xiàn)不允許GROUP BY列帶有長度可變的數(shù)據(jù)類型（如文本或備注型字段）。
  5. 除聚集計算語句外，SELECT語句中的每一列都必須在GROUP BY子句中給出。
  6. 如果分組列中包含具有NULL值的行，則NULL將作為一個分組返回。如果列中有多行NULL值，它們將分為一組。
  7. GROUP BY子句必須出現(xiàn)在WHERE子句之后，ORDER BY子句之前。
-- 先按照EPS_ID進行分組，然后在按照Status進行分組
SELECT
    t_project.EPS_ID,
    t_project.`Status`,
    COUNT(*)
FROM
    t_project
GROUP BY
    t_project.EPS_ID,
    t_project.`Status`;

10.2 過濾分組（HAVING）

  1. GROUP BY 創(chuàng)建分組，HAVING過濾分組，包括哪些分組，排除哪些分組。
  2. GROUP BY和WHERE唯一的差別：
    1. WHERE過濾行，而HAVING過濾分組。
    2. 學過的有關WHERE的所有技術和選項都適用于HAVING。它們的句法是相同的，只是關鍵字有差別。

HAVING和WHERE的差別另一種理解方式：

WHERE在數(shù)據(jù)分組前進行過濾，HAVING在數(shù)據(jù)分組后進行過濾。這是一個重要的區(qū)別，WHERE排除的行不包括在分組中。這可能會改變計算值，從而影響HAVING子句中基于這些值過濾掉的分組。

SELECT
    cust_id,
    COUNT(*) AS orders
FROM
    Orders
GROUP BY
    cust_id
HAVING
    COUNT(*) >= 2;

10.3 分組和排序

名稱 ORDERBY GROUP BY
作用 對產(chǎn)生的輸出排序 對行分組，但輸出可能不是分組的順序
使用范圍 任意列都可以使用（甚至非選擇的列也可以使用） 只可能使用選擇列或表達式列，而且必須使用每個選擇列表達式
是否需要 不一定需要 如果與聚集函數(shù)一起使用列（或表達式），則必須使用

一般在使用GROUP BY子句時，應該也給出ORDER BY子句。這是保證數(shù)據(jù)正確排序的唯一方法。

SELECT
    order_num,
    COUNT(*) AS items
FROM
    OrderItems
GROUP BY
    order_num
HAVING
    COUNT(*) >= 3
ORDER BY
    items,
    order_num;

10.4 SELECT子句順序

子 句 說 明 是否必須使用
SELECT 要返回的列或表達式 是
FROM 從中檢索數(shù)據(jù)的表 僅在從表選擇數(shù)據(jù)時使用
WHERE 行級過濾 否
GROUP BY 分組說明 僅在按組計算聚集時使用
HAVING 組級過濾 否
ORDER BY 輸出排序順序 否

第11課 使用子查詢

  1. 就是在select里面嵌套select，嵌套的select就是子查詢;
  2. 查詢執(zhí)行的順序：由內(nèi)而外；
  3. 使用子查詢效率低下；
  4. 使用場景：
    1. 子查詢常用于WHERE子句的IN操作符中；
    2. 用來填充計算列。

11.1 利用子查詢進行過濾

SELECT
    customers.cust_name,
    customers.cust_contact
FROM
    customers
WHERE
    cust_id IN (
        SELECT
            orders.cust_id
        FROM
            orders
        WHERE
            order_num IN (
                SELECT
                    orderitems.order_num
                FROM
                    orderitems
                WHERE
                    orderitems.prod_id = "RGAN01"
            );
);
  1. 作為子查詢的SELECT語句只能查詢單個列。企圖檢索多個列將返回錯誤。
  2. 使用子查詢并不總是執(zhí)行這類數(shù)據(jù)檢索的最有效方法，效率低下。

11.2 作為計算字段使用子查詢

SELECT
    customers.cust_name,
    customers.cust_state,
    (
        SELECT
            COUNT(*)
        FROM
            orders
        WHERE
            orders.cust_id = customers.cust_id
    ) AS orders
FROM
    customers
ORDER BY
    cust_name;

必須完全限定類名

第12課 聯(lián)結(jié)表

12.1 聯(lián)結(jié)

  1. 關系式數(shù)據(jù)庫設計，關系表
    1. 方便維護；
    2. 防止冗余數(shù)據(jù)；

12.2 創(chuàng)建聯(lián)結(jié)

  1. 如何創(chuàng)建鏈接（首先列出表，然后定義表之間的關系）
    1. 指定要鏈接的表；
    2. 指定這些表之間的關聯(lián)方式；
SELECT
    vend_name,
    prod_name,
    prod_price
FROM
    vendors,
    products
WHERE
    vendors.vend_id = products.vend_id;
WHERE子句的重要性
  1. 在一條SELECT語句中聯(lián)結(jié)幾個表時，相應的關系是在運行中構(gòu)造的；
  2. 在數(shù)據(jù)庫表的定義中沒有指示DBMS如何對表進行聯(lián)結(jié)的內(nèi)容，你必須自己做這件事情；
  3. 在聯(lián)結(jié)兩個表時，實際要做的是將第一個表中的每一行與第二個表中的每一行配對；
  4. WHERE子句作為過濾條件，只包含那些匹配給定條件（這里是聯(lián)結(jié)條件）的行。沒有WHERE子句，第一個表中的每一行將與第二個表中的每一行配對，而不管它們邏輯上是否能配在一起。

笛卡兒積（cartesian product）

由沒有聯(lián)結(jié)條件的表關系返回的結(jié)果為笛卡兒積。檢索出的行的數(shù)目將是第一個表中的行數(shù)乘以第二個表中的行數(shù)。

提示：叉聯(lián)結(jié)

有時，返回笛卡兒積的聯(lián)結(jié)，也稱叉聯(lián)結(jié)（cross join）

12.2.2 內(nèi)聯(lián)結(jié)

--上述where的等價SQL語句
SELECT
    vend_name,
    prod_name,
    prod_price
FROM
    vendors
INNER JOIN products ON vendors.vend_id = products.vend_id;

12.2.3 聯(lián)結(jié)多個表

SELECT
    prod_name,
    vend_name,
    prod_price,
    quantity
FROM
    OrderItems,
    Products,
    Vendors
WHERE
    Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;

警告：性能考慮

DBMS在運行時關聯(lián)指定的每個表，以處理聯(lián)結(jié)。這種處理可能非常耗費資源，因此應該注意，不要聯(lián)結(jié)不必要的表。聯(lián)結(jié)的表越多，性能下降越厲害。

第13課 創(chuàng)建高級聯(lián)結(jié)

13.1 使用表別名

  1. SQL可以給列名，計算字段，表名起別名；
  2. 使用別名的好處：
    1. 縮短SQL語句；
    2. 允許在一條SELECT語句中多次使用相同的表。
SELECT
    cust_name,
    cust_contact
FROM
    customers AS C,
    orders AS O,
    orderitems AS OI
WHERE
    O.cust_id = C.cust_id
AND O.order_num = OI.order_num AND prod_id = 'RGAN01';
  1. 表別名可以在任何子句中使用（如WHERE子句，SELECT的列表、ORDER BY子句等）；
  2. 表別名只在查詢執(zhí)行中使用。與列別名不一樣，表別名不返回到客戶端；

13.2 使用不同類型的聯(lián)結(jié)

聯(lián)接的類型

  • 等值聯(lián)接/內(nèi)聯(lián)接（inner join）
  • 自聯(lián)接（self-join）
  • 自然聯(lián)接（natural join）
  • 外聯(lián)接（outer join）

13.2.1 自聯(lián)結(jié)

SELECT
    cust_id,
    cust_name,
    cust_contact
FROM
    customers
WHERE
    cust_name = (
        SELECT
            cust_name
        FROM
            customers
        WHERE
            cust_contact = 'Jim Jones'
    );

使用自聯(lián)接實現(xiàn)

SELECT
    C1.cust_id,
    C1.cust_name,
    C1.cust_contact
FROM
    customers AS C1,
    customers AS C2
WHERE
    C1.cust_name = C2.cust_name
AND C2.cust_contact = 'Jim Jones';
  • 分析

  1. 此查詢中需要的兩個表實際上是相同的表，因此Customers表在FROM子句中出現(xiàn)了兩次。雖然這是完全合法的，但對Customers的引用具有歧義性，因為DBMS不知道你引用的是哪個Customers表。

  2. 解決此問題，需要使用表別名。Customers第一次出現(xiàn)用了別名C1，第二次出現(xiàn)用了別名C2。現(xiàn)在可以將這些別名用作表名。例如，SELECT語句使用C1前綴明確給出所需列的全名。如果不這樣，DBMS將返回錯誤，因為名為cust_id、cust_name、cust_contact的列各有兩個。DBMS不知道想要的是哪一列（即使它們其實是同一列）。WHERE首先聯(lián)結(jié)兩個表，然后按第二個表中的cust_contact過濾數(shù)據(jù)，返回所需的數(shù)據(jù)。

提示：用自聯(lián)結(jié)而不用子查詢

自聯(lián)結(jié)通常作為外部語句，用來替代從相同表中檢索數(shù)據(jù)的使用子查詢語句。雖然最終的結(jié)果是相同的，但許多DBMS處理聯(lián)結(jié)遠比處理子查詢快得多。應該試一下兩種方法，以確定哪一種的性能更好。

13.2.2 自然聯(lián)結(jié)

  1. 自然聯(lián)接的作用：
    • 標準的聯(lián)結(jié)（前一課中介紹的內(nèi)聯(lián)結(jié)）返回所有數(shù)據(jù)，相同的列甚至多次出現(xiàn)。自然聯(lián)結(jié)排除多次出現(xiàn)，使每一列只返回一次。
  2. 如何實現(xiàn)自然聯(lián)接：
    • 自然聯(lián)結(jié)要求你只能選擇那些唯一的列，一般通過對一個表使用通配符（SELECT *），而對其他表的列使用明確的子集來完成。
SELECT
    C.*, O.order_num,
    O.order_date,
    OI.prod_id,
    OI.quantity,
    OI.item_price
FROM
    Customers AS C,
    Orders AS O,
    OrderItems AS OI
WHERE
    C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';

實際上，我們建立的大多數(shù)內(nèi)聯(lián)接都是自然聯(lián)接，可能用不到不是自然聯(lián)接的內(nèi)聯(lián)接。

13.2.3 外聯(lián)結(jié)（使用OUTER JOIN來指定聯(lián)結(jié)類型）

  1. 定義：聯(lián)結(jié)包含了那些在相關表中沒有關聯(lián)行的行。
  2. 分類
    1. 左外聯(lián)接(LEFT OUTER JOIN)
    2. 右外聯(lián)接(RIGHT OUTER JOIN)
    3. 全外聯(lián)接(FULL OUTER JOIN)
-- 輸出Customers表的所有行
SELECT
    Customers.cust_id,
    Orders.order_num
FROM
    Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
  1. 在使用OUTER JOIN語法時，必須使用RIGHT或LEFT關鍵字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右邊的表，而LEFT指出的是OUTER JOIN左邊的表）。
  2. 左外聯(lián)結(jié)和右外聯(lián)結(jié)的區(qū)別：唯一的區(qū)別是所關聯(lián)的表的順序。
  3. 全外聯(lián)接：
SELECT
    Customers.cust_id,
    Orders.order_num
FROM
    Orders
FULL OUTER JOIN Customers ON Orders.cust_id = Customers.cust_id;

全外鏈接檢索兩個表中的所有行并關聯(lián)那些可以關聯(lián)的行；

13.3 使用帶聚集函數(shù)的聯(lián)結(jié)

SELECT
    Customers.cust_id,
    COUNT(Orders.order_num) AS num_ord
FROM
    Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY
    Customers.cust_id;

13.4 使用聯(lián)結(jié)和聯(lián)結(jié)條件的總結(jié)

  1. 注意所使用的聯(lián)結(jié)類型
    • 一般我們使用內(nèi)聯(lián)結(jié)，但使用外聯(lián)結(jié)也有效
  2. 關于確切的聯(lián)結(jié)語法，應該查看具體的文檔，看相應的DBMS支持何種語法
  3. 保證使用正確的聯(lián)結(jié)條件（不管采用哪種語法），否則會返回不正確的數(shù)據(jù)。
  4. 應該總是提供聯(lián)結(jié)條件，否則會得出笛卡兒積。
  5. 在一個聯(lián)結(jié)中可以包含多個表，甚至可以對每個聯(lián)結(jié)采用不同的聯(lián)結(jié)類型。雖然這樣做是合法的，一般也很有用，但應該在一起測試它們前分別測試每個聯(lián)結(jié)。這會使故障排除更為簡單。

第14課 組合查詢

14.1 組合查詢/復合查詢（compound query）/并（union）

  1. 定義：執(zhí)行多條SELECT語句，并將結(jié)果作為一個查詢結(jié)果返回;
  2. 使用場景：
    1. 在一個查詢中從不同的表返回結(jié)構(gòu)數(shù)據(jù)；
    2. 對一個表執(zhí)行多個查詢，按一個查詢返回數(shù)據(jù)；
  3. 組合查詢和多個where條件
    • 多數(shù)情況下，組合相同表的兩個查詢所完成的工作與具有多個WHERE子句條件的一個查詢所完成的工作相同。換句話說，任何具有多個WHERE子句的SELECT語句都可以作為一個組合查詢。
    • 利用UNION，可以把多條查詢的結(jié)果作為一條組合查詢返回，不管結(jié)果中有無重復。使用UNION可極大地簡化復雜的WHERE子句，簡化從多個表中檢索數(shù)據(jù)的工作。

14.2 創(chuàng)建組合查詢

只需要使用UNION操作符把多個select語句連接起來即可。

SELECT
    cust_name,
    cust_contact,
    cust_email
FROM
    Customers
WHERE
    cust_state IN ('IL', 'IN', 'MI')
UNION
    SELECT
        cust_name,
        cust_contact,
        cust_email
    FROM
        Customers
    WHERE
        cust_name = 'Fun4All';
ORDER BY cust_name, cust_contact;

等價的where語句

SELECT
    cust_name,
    cust_contact,
    cust_email
FROM
    customers
WHERE
    cust_state IN ('IL', 'IN', 'MI')
OR cust_name = 'Fun4All';
ORDER BY cust_name, cust_contact;

14.2.2 UNION使用規(guī)則

  1. 性能和多個where沒有多大差別，具體需要測試；
  2. UNION從查詢結(jié)果集中自動去除了重復的行，如果想返回所有匹配的行，使用UNION ALL；
  3. 在用UNION組合查詢時，只能使用一條ORDER BY子句，它必須位于最后一條SELECT語句之后；
    • 對于結(jié)果集，不存在用一種方式排序一部分，而又用另一種方式排序另一部分的情況，因此不允許使用多條ORDER BY子句;
  4. 使用規(guī)則
    1. UNION必須由兩條或兩條以上的SELECT語句組成，語句之間用關鍵字UNION分隔；
    2. UNION中的每個查詢必須包含相同的列、表達式或聚集函數(shù)（各個列次序可不同）；
    3. 列數(shù)據(jù)類型必須兼容：類型不必完全相同，但必須是DBMS可以隱含轉(zhuǎn)換的類型；

第15課 插入數(shù)據(jù)

15.1.1插入部分行

INSERT INTO customers (
    cust_id,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
)
VALUES
    (
        '1000000006',
        'Toy Land',
        '123 Any Street',
        'New York',
        'NY',
        '11111',
        'USA'
    );

說明：

  1. 不需要列出所有的列；
  2. 如果表的定義允許，則可以在INSERT操作中省略某些列。省略的列必須滿足以下某個條件。
    1. 該列定義為允許NULL值（無值或空值）；
    2. 在表定義中給出默認值。這表示如果不給出值，將使用默認值；
    3. 如果對表中不允許NULL值且沒有默認值的列不給出值，DBMS將產(chǎn)生錯誤消息，并且相應的行插入不成功；

15.1.3 插入檢索出的數(shù)據(jù)

INSERT INTO Customers (
    cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
) SELECT
    cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
FROM
    CustNew
WHERE XXX;

說明：

  1. INSERT INTO() SELECT() FROM table WHERE
  2. INSERT SELECT中的列名：
    • 為簡單起見，這個例子在INSERT和SELECT語句中使用了相同的列名。但是，不一定要求列名匹配。事實上，DBMS一點兒也不關
      心SELECT返回的列名。它使用的是列的位置，因此SELECT中的第一列（不管其列名）將用來填充表列中指定的第一列，第二列將用來填充表列中指定的第二列；
  3. INSERT通常只插入一行。要插入多行，必須執(zhí)行多個INSERT語句。INSERT SELECT是個例外，它可以用一條INSERT插入多行，不管SELECT語句返回多少行，都將被INSERT插入。

15.2 從一個表復制到另一個表（SELECT INTO）

SELECT
    * INTO CustCopy
FROM
    Customers;

說明：

  1. INSERT SELECT與SELECT INTO的區(qū)別
    • INSERT SELECT導出數(shù)據(jù)；
    • SELECT INTO導入數(shù)據(jù)；
  2. 要想只復制部分的列，可以明確給出列名，而不是使用*通配符；
  3. 任何SELECT選項和子句都可以使用，包括WHERE和GROUP BY；
  4. 可利用聯(lián)結(jié)從多個表插入數(shù)據(jù)；
  5. 不管從多少個表中檢索數(shù)據(jù)，數(shù)據(jù)都只能插入到一個表中。
-- 這個不懂
CREATE TABLE CustCopy AS SELECT * FROM Customers;

第16課 更新和刪除數(shù)據(jù)

16.1 更新數(shù)據(jù)

  • 更新的語法
UPDATE table 
SET columnName1 ='xxxx',
    columnName2 ='XXX',
    columnName3 ='XXX'
WHERE columnName4 ='' AND　columnName5 ='';
  • 舉例
UPDATE Customers
SET cust_contact = 'Sam Roberts',
    cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';
  • 在UPDATE語句中使用子查詢
    • UPDATE語句中可以使用子查詢，使得能用SELECT語句檢索出的數(shù)據(jù)更新列數(shù)據(jù)。

16.2 刪除數(shù)據(jù)

  • delete是刪除一整行數(shù)據(jù)，而不是一行的某幾列數(shù)據(jù)，即刪除的是表的內(nèi)容，而不是表本身，刪除表本身使用drop;
  • 刪除的語法
DELETE FROM table
WHERE columnName1 = '' AND columnName2 ='';
  • 舉例
DELETE FROM Customers
WHERE cust_id = '1000000006';
  • 更快的刪除行
    如果想從表中刪除所有行，不要使用DELETE。可使用TRUNCATE TABLE語句，它完成相同的工作，而速度更快（因為不記錄數(shù)據(jù)的變
    動）
  • 友好的外鍵(不懂)

第12課介紹了聯(lián)結(jié)，簡單聯(lián)結(jié)兩個表只需要這兩個表中的常用字段。也可以讓DBMS通過使用外鍵來嚴格實施關系。存在外鍵時，DBMS使用它們實施引用完整性。例如要向Products表中插入一個新產(chǎn)品，DBMS不允許通過未知的供應商id插入它，因為vend_id列是作為外鍵連接到Vendors表的。那么，這與DELETE有什么關系呢？使用外鍵確保引用完整性的一個好處是，DBMS通常可以防止刪除某個關系需要用到的行。例如，要從Products表中刪除一個產(chǎn)品，而這個產(chǎn)品用在OrderItems的已有訂單中，那么DELETE語句將拋出錯誤并中止。這是總要定義外鍵的另一個理由。

16.3 更新和刪除的指導原則

  1. 除非確實打算更新和刪除每一行，否則絕對不要使用不帶WHERE子句的UPDATE或DELETE語句。
  2. 保證每個表都有主鍵，盡可能像WHERE子句那樣使用它（可以指定各主鍵、多個值或值的范圍）。
  3. 在UPDATE或DELETE語句使用WHERE子句前，應該先用SELECT進行測試，保證它過濾的是正確的記錄，以防編寫的WHERE子句不正確。
  4. 使用強制實施引用完整性的數(shù)據(jù)庫，這樣DBMS將不允許刪除其數(shù)據(jù)與其他表相關聯(lián)的行。
  5. 有的DBMS允許數(shù)據(jù)庫管理員施加約束，防止執(zhí)行不帶WHERE子句的UPDATE或DELETE語句。如果所采用的DBMS支持這個特性，應該使用它。

第17課 創(chuàng)建和操縱表

17.1 創(chuàng)建表

-- 使用default 給某一列指定默認值
-- primary key 指定主鍵
-- autoincrement 自增長
CREATE TABLE OrderItems IF NOT EXISTS
(
    order_num INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    order_item INTEGER NOT NULL,
    prod_id CHAR(10) NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    item_price DECIMAL(8,2) NULL
);

說明：

  1. 不允許NULL值的列不接受沒有列值的行，否則會報錯。
  2. 在不指定NOT NULL時，多數(shù)DBMS認為指定的是NULL，但是最好加上NULL；
  3. 不要把NULL值與空字符串相混淆。NULL值是沒有值，不是空字符串。如果指定''（兩個單引號，其間沒有字符），這在NOT NULL列中是允許的。空字符串是一個有效的值，它不是無值。NULL值用關鍵字NULL而不是空字符串指定；

17.2 更新表(ALERT TABLE)

  1. 使用alert table時要考慮的問題
    1. 理想情況下，不要在表中包含數(shù)據(jù)時對其進行更新。應該在表的設計過程中充分考慮未來可能的需求，避免今后對表的結(jié)構(gòu)做大改動。
    2. 所有的DBMS都允許給現(xiàn)有的表增加列，不過對所增加列的數(shù)據(jù)類型（以及NULL和DEFAULT的使用）有所限制。
    3. 許多DBMS不允許刪除或更改表中的列。
    4. 多數(shù)DBMS允許重新命名表中的列。
    5. 許多DBMS限制對已經(jīng)填有數(shù)據(jù)的列進行更改，對未填有數(shù)據(jù)的列幾乎沒有限制。
  2. 使用ALTER TABLE更改表結(jié)構(gòu)，必須給出下面的信息：
    1. 在ALTER TABLE之后給出要更改的表名（該表必須存在，否則將出錯）；
    2. 列出要做哪些更改。
  3. 增加、更改、刪除列、增加約束、增加鍵的做法都是類似的
ALTER TABLE Vendors
ADD vend_phone CHAR(20);

ALTER TABLE Vendors
DROP COLUMN vend_phone;
  1. 復雜的表結(jié)構(gòu)更改一般需要手動刪除過程，它涉及以下步驟（不懂）
    1. 用新的列布局創(chuàng)建一個新表；
    2. 使用INSERT SELECT語句，從舊表復制數(shù)據(jù)到新表。有必要的話，可以使用轉(zhuǎn)換函數(shù)和計算字段；
    3. 檢驗包含所需數(shù)據(jù)的新表；
    4. 重命名舊表（如果確定，可以刪除它）；
    5. 用舊表原來的名字重命名新表；
    6. 根據(jù)需要，重新創(chuàng)建觸發(fā)器、存儲過程、索引和外鍵。
  2. 使用ALTER TABLE要極為小心，應該在進行改動前做完整的備份（模式和數(shù)據(jù)的備份）,數(shù)據(jù)庫的操作大多數(shù)都不可逆。

17.3 刪除表

-- 就是這么簡單
DROP TABLE CustCopy;

提示：使用關系規(guī)則防止意外刪除

許多DBMS允許強制實施有關規(guī)則，防止刪除與其他表相關聯(lián)的表。在實施這些規(guī)則時，如果對某個表發(fā)布一條DROP TABLE語句，且該表
是某個關系的組成部分，則DBMS將阻止這條語句執(zhí)行，直到該關系被刪除為止。如果允許，應該啟用這些選項，它能防止意外刪除有用的
表。

17.4 重命名表

不同的DBMS對重命名完全不一樣。參照具體的文檔。

第18課 使用視圖

18.1 視圖

  1. 定義：視圖是虛擬的表，包含的不是數(shù)據(jù)而是根據(jù)需要檢索數(shù)據(jù)的查詢，即將select語句封裝起來。
  2. 作用：
    1. 簡化數(shù)據(jù)處理，使用表的一部分而不是整個表；
    2. 提高sql語句的復用性，簡化復雜的sql操作；
    3. 重新格式化數(shù)據(jù)，視圖可返回與底層表的表示和格式不同的數(shù)據(jù)；
    4. 保護基礎數(shù)據(jù)，可以授予用戶訪問表的特定部分的權(quán)限，而不是整個表的訪問權(quán)限；
  3. 試圖創(chuàng)建好之后，可以像正常的表一樣操作他們，可以對視圖執(zhí)行SELECT操作，過濾和排序數(shù)據(jù)，將視圖聯(lián)結(jié)到其他視圖或表，甚至添加和更新數(shù)據(jù)。
  4. 試圖的規(guī)則和限制
    1. 視圖的名字必須唯一（與其他視圖和表名都不能重復）；
    2. 可以創(chuàng)建無數(shù)多個視圖；
    3. 創(chuàng)建視圖，必須具有足夠的訪問權(quán)限。這些權(quán)限通常由數(shù)據(jù)庫管理人員授予。
    4. 視圖可以嵌套，即視圖里面含有視圖，但是性能下降會很厲害，使用時需要測試；
    5. 許多DBMS禁止在視圖查詢中使用ORDER BY子句;
    6. 有些DBMS要求對返回的所有列進行命名，如果列是計算字段，則需要使用別名;
    7. 視圖不能索引，也不能有關聯(lián)的觸發(fā)器或默認值;
    8. 有些DBMS把視圖作為只讀的查詢，這表示可以從視圖檢索數(shù)據(jù)，但不能將數(shù)據(jù)寫回底層表;
    9. 有些DBMS允許創(chuàng)建這樣的視圖，它不能進行導致行不再屬于視圖的插入或更新;(例如有一個視圖，只檢索帶有電子郵件地址的顧客。如果更新某個顧客，刪除他的電子郵件地址，將使該顧客不再屬于視圖。這是默認行為，而且是允許的，但有的DBMS可能會防止這種情況發(fā)生。)

18.2 創(chuàng)建視圖

-- 創(chuàng)建視圖，只能用來創(chuàng)建不存在的視圖
CREATE VIEW view_name AS SELECT
-- 刪除視圖
DROP VIEW view_name;
  1. 更新視圖，必須先刪除它，然后重新創(chuàng)建；
  2. 視圖的創(chuàng)建與使用舉例 
    -- 創(chuàng)建視圖
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

--使用視圖，當DBMS處理此查詢時，
-- 它將指定的WHERE子句添加到視圖查詢中
-- 已有的WHERE子句中，以便正確過濾數(shù)據(jù)。
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';
  3. 如何創(chuàng)建視圖
    1. 先把select語句創(chuàng)建寫出來；
    2. 然后把創(chuàng)建視圖的語句加上；
    3. select語句有什么功能，視圖就有什么功能，比如在視圖中過濾不想要的數(shù)據(jù)，在試圖中創(chuàng)建計算字段；
    -- 用視圖過濾不想要的數(shù)據(jù)
CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers  
WHERE cust_email IS NOT NULL;

-- 使用視圖與計算字段
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems;

第19課 使用存儲過程

存儲過程就是為以后使用而保存的一條或多條SQL語句。可將其視為批文件，但它們的作用不僅限于批處理。

19.2 為什么要使用存儲過程

1. 存儲過程的好處：簡單、安全、高性能。

  1. 通過把處理封裝在一個易用的單元中，可以簡化復雜的操作；
  2. 由于不要求反復建立一系列處理步驟，因而保證了數(shù)據(jù)的一致性（防止錯誤）；
  3. 簡化對變動的管理。如果表名、列名或業(yè)務邏輯（或別的內(nèi)容）有變化，那么只需要更改存儲過程的代碼（保證安全性）；
  4. 因為存儲過程通常以編譯過的形式存儲，所以DBMS處理命令的工作較少，提高了性能；
  5. 存在一些只能用在單個請求中的SQL元素和特性，存儲過程可以使用它們來編寫功能更強更靈活的代碼；

2. 存儲過程的不足

  1. 不同DBMS中的存儲過程語法有所不同，代碼不具有可可移植性;
  2. 編寫存儲過程比編寫基本SQL語句復雜，需要更高的技能，更豐富的經(jīng)驗;

19.3 執(zhí)行存儲過程

-- orcale版本的存儲過程
-- 語法：EXECUTE <存儲過程名稱> <參數(shù)>
-- 利用存儲過程插入一條數(shù)據(jù)
EXECUTE AddNewProduct( 'JTS01',
    'Stuffed Eiffel Tower',
    6.49,
    'Plush stuffed toy with the text La
    tour Eiffel in red white and blue' );

19.4 創(chuàng)建存儲過程

-- orcale 版本
-- 功能：對郵件發(fā)送清單中具有郵件地址的顧客進行計數(shù)
CREATE PROCEDURE MailingListCount (
    ListCount OUT INTEGER
) 
IS v_rows INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_rows
    FROM Customers
    WHERE NOT cust_email IS NULL;
    ListCount := v_rows;
END;

分析:

  1. 這個存儲過程有一個名為ListCount的參數(shù)。此參數(shù)從存儲過程返回一個值而不是傳遞一個值給存儲過程。關鍵字OUT用來指示這種行為。
  2. 存儲過程的代碼括在BEGIN和END語句中，這里執(zhí)行一條簡單的SELECT語句，它檢索具有郵件地址的顧客。然后用檢索出的行數(shù)設置ListCount（要傳遞的輸出參數(shù)）

Oracle支持

  1. IN（傳遞值給存儲過程）;
  2. OUT（從存儲過程返回值）;
  3. INOUT（既傳遞值給存儲過程也從存儲過程傳回值）類型的參數(shù);

調(diào)用存儲過程

-- 這段代碼聲明了一個變量來保存存儲過程返回的任何
-- 值，然后執(zhí)行存儲過程，再使用SELECT語句顯示返回的值
var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;

第20課 管理事務處理

20.1 事務處理

1. 使用事務的目的

使用事務處理（transaction processing），通過確保成批的SQL操作要么完全執(zhí)行，要么完全不執(zhí)行，來維護數(shù)據(jù)庫的完整性。

2. 什么是事務處理

事務處理是一種機制，用來管理必須成批執(zhí)行的SQL操作，保證數(shù)據(jù)庫不包含不完整的操作結(jié)果。利用事務處理，可以保證一組操作不會中途停止，它們要么完全執(zhí)行，要么完全不執(zhí)行（除非明確指示）。如果沒有錯誤發(fā)生，整組語句提交給（寫到）數(shù)據(jù)庫表；如果發(fā)生錯誤，則進行回退（撤銷），將數(shù)據(jù)庫恢復到某個已知且安全的狀態(tài)。

3. 幾個概念

  1. 事務（transaction）：指一組SQL語句；
  2. 回退（rollback）：指撤銷指定SQL語句的過程；
  3. 提交（commit）：指將未存儲的SQL語句結(jié)果寫入數(shù)據(jù)庫表；
  4. 保留點（savepoint）：指事務處理中設置的臨時占位符（placeholder），可以對它發(fā)布回退（與回退整個事務處理不同）

4. 不能回退的語句

  1. 事務處理用來管理INSERT、UPDATE和DELETE語句；
  2. 不能回退SELECT,DROP,CREATE語句；

20.2 控制事務處理

管理事務的關鍵在于將SQL語句組分解為邏輯塊，并明確規(guī)定數(shù)據(jù)何時應該回退，何時不應該回退

-- BEGIN TRANSACTION和COMMIT TRANSACTION語句之間的SQL
-- 必須完全執(zhí)行或者完全不執(zhí)行
-- 不同的DBMS不一樣
BEGIN TRANSACTION
...
COMMIT TRANSACTION

１．使用ROLLBACK

--執(zhí)行DELETE操作，然后用ROLLBACK語句撤銷
DELETE FROM Orders;
ROLLBACK;

２．使用COMMIT

一般的SQL語句都是針對數(shù)據(jù)庫表直接執(zhí)行和編寫的。這就是所謂的隱式提交（implicit　commit），即提交（寫或保存、刪除）操作是自動進行的。在事務處理塊中，提交不會隱式進行。不過，不同DBMS的做法有所不同。有的DBMS按隱式提交處理事務端，有的則不這樣。

--　SQL Server使用commit進行（明確）提交
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION

--　Oracle使用commit
SET TRANSACTION
DELETE OrderItems WHERE order_num = 12345;
DELETE Orders WHERE order_num = 12345;
COMMIT;

3．使用保留點

１．使用保留點的原因

  • 使用簡單的ROLLBACK和COMMIT語句，就可以寫入或撤銷整個事務。但是，只對簡單的事務才能這樣做，復雜的事務可能需要部分提交或回退;
  • 要支持回退部分事務，必須在事務處理塊中的合適位置放置占位符。這樣，如果需要回退，可以回退到某個占位符;在SQL中，這些占位符稱為保留點。
  1. 每個保留點都要取能夠標識它的唯一名字，以便在回退時，DBMS知道回退到何處
-- MariaDB、MySQL和Oracle
SAVEPOINT delete1;

ROLLBACK TO delete1;

-- SQL Server
SAVE TRANSACTION delete1;

ROLLBACK TRANSACTION delete1;

-- 一個完整的SQL Server例子
BEGIN TRANSACTION
INSERT INTO Customers(cust_id, cust_name)
VALUES('1000000010', 'Toys Emporium');
-- 這里設置了一個保留點，如果后面的任何一個INSERT操作失敗，
-- 事務處理最近回退到這里
SAVE TRANSACTION StartOrder;  
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20100,'2001/12/1','1000000010');
-- 在SQL Server中，可檢查一個名為@@ERROR的變量，看操作是否成功
-- 如果@@ERROR返回一個非0的值，表示有錯誤發(fā)生，
-- 事務處理回退到保留點
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 1, 'BR01', 100, 5.49);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 2, 'BR03', 100, 10.99);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION

第21課 使用游標

21.1 游標

  1. 結(jié)果集：SQL查詢所檢索出的結(jié)果
  • SELECT操作返回一組稱為結(jié)果集的行，這組返回的行都是與SQL語句相匹配的行（零行或多行）。
  1. 游標
    游標（cursor）是一個存儲在DBMS服務器上的數(shù)據(jù)庫查詢，它不是一條SELECT語句，而是被該語句檢索出來的結(jié)果集；可以在檢索出來的行中前進或后退一行或多行。

21.2 使用游標

使用步驟

  1. 在使用游標前，必須聲明（定義）它。這個過程實際上沒有檢索數(shù)據(jù)，它只是定義要使用的SELECT語句和游標選項。
  2. 一旦聲明，就必須打開游標以供使用。這個過程用前面定義的SELECT語句把數(shù)據(jù)實際檢索出來。
  3. 對于填有數(shù)據(jù)的游標，根據(jù)需要取出（檢索）各行。
  4. 在結(jié)束游標使用時，必須關閉游標，可能的話，釋放游標（有賴于具體的DBMS）。

1、創(chuàng)建游標

使用DECLARE語句創(chuàng)建游標，DECLARE命名游標，并定義相應的SELECT語句，根據(jù)需要帶WHERE和其他子句。

-- DB2、MariaDB、MySQL和SQL Server
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL

-- Oracle和PostgreSQL
DECLARE CURSOR CustCursor
IS
SELECT * FROM Customers
WHERE cust_email IS NULL

2、使用游標

-- 打開游標
OPEN CURSOR CustCursor

-- oracle
-- FETCH用來檢索當前行（自動從第一行開始），
-- 放到聲明的變量CustRecord中。
-- 對于檢索出來的數(shù)據(jù)不做任何處理
DECLARE TYPE CustCursor IS REF CURSOR
    RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
    OPEN CustCursor;
    FETCH CustCursor INTO CustRecord;
    CLOSE CustCursor;
END;

-- 使用FETCH檢索當前行，放到一個名為CustRecord的變量中
DECLARE TYPE CustCursor IS REF CURSOR
    RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
    OPEN CustCursor;
    LOOP
    -- 這里的FETCH位于LOOP內(nèi)，因此它反復執(zhí)行。
    -- 代碼EXIT WHEN  CustCursor%NOTFOUND
    -- 使在取不出更多的行
    -- 時終止處理（退出循環(huán)）
    FETCH CustCursor INTO CustRecord;
    EXIT WHEN CustCursor%NOTFOUND;
    ...
    END LOOP;
    -- 關閉游標
    CLOSE CustCursor;
END;

第22課 高級SQL特性

22.1 約束

約束（constraint）： 管理如何插入或處理數(shù)據(jù)庫數(shù)據(jù)的規(guī)則。

1.1 主鍵

  1. 主鍵是一種特殊的約束，用來保證一列（或一組列）中的值是唯一的，而且永不改動。
  2. 表中任意列只要滿足以下條件，都可以用于主鍵：
    1. 任意兩行的主鍵值都不相同。
    2. 每行都具有一個主鍵值（即列中不允許NULL值）。
    3. 包含主鍵值的列從不修改或更新。（大多數(shù)DBMS不允許這么做，但如果你使用的DBMS允許這樣做，好吧，千萬別?。?/li>
    4. 主鍵值不能重用。如果從表中刪除某一行，其主鍵值不分配給新行
CREATE TABLE Vendors
( 
    --通過primary key來指定主鍵
    vend_id CHAR(10) NOT NULL PRIMARY KEY,  
    vend_name CHAR(50) NOT NULL,
    vend_address CHAR(50) NULL,
    vend_city CHAR(50) NULL,
    vend_state CHAR(5) NULL,
    vend_zip CHAR(10) NULL,
    vend_country CHAR(50) NULL
);

-- 這里定義相同的列為主鍵，但使用的是CONSTRAINT語法
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

1.2 外鍵

CREATE TABLE Orders
(
    order_num INTEGER NOT NULL PRIMARY KEY,
    order_date DATETIME NOT NULL,
    -- 添加外鍵
    -- 使用了REFERENCES關鍵字，它表示cust_id中的
    -- 任何值都必須是Customers表的cust_id中的值
    cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id)
);


ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

提示：外鍵有助防止意外刪除

外鍵的作用

  1. 保證引用完整性；
  2. 在定義外鍵后，DBMS不允許刪除在另一個表中具有關聯(lián)行的行；

例如，不能刪除關聯(lián)訂單的顧客。刪除該顧客的唯一方法是首先刪除相關的訂單（這表示還要刪除相關的訂單項）。由于需要一系列的刪除，
因而利用外鍵可以防止意外刪除數(shù)據(jù)。

有的DBMS支持稱為級聯(lián)刪除（cascading delete）的特性。如果啟用，該特性在從一個表中刪除行時刪除所有相關的數(shù)據(jù)。例如，如果啟用級聯(lián)刪除并且從Customers表中刪除某個顧客，則任何關聯(lián)的訂單行也會被自動刪除。

1.3 唯一約束

  1. 唯一約束用來保證一列（或一組列）中的數(shù)據(jù)是唯一的。它們類似于主鍵，但存在以下重要區(qū)別。

    • 表可包含多個唯一約束，但每個表只允許一個主鍵。
    • 唯一約束列可包含NULL值。
    • 唯一約束列可修改或更新。
    • 唯一約束列的值可重復使用。
    • 與主鍵不一樣，唯一約束不能用來定義外鍵

  2. 語法：唯一約束既可以用UNIQUE關鍵字在表定義中定義，也可以用單獨的CONSTRAINT定義

employees表中每個雇員都有唯一的社會安全號，但我們并不想用它作主鍵，因為它太長（而且我們也不想使該信息容易利用）。因此，每個雇員除了其社會安全號外還有唯一的雇員ID（主鍵）。

雇員ID是主鍵，可以確定它是唯一的。你可能還想使DBMS保證每個社會安全號也是唯一的（保證輸入錯誤不會導致使用他人號碼）?？梢酝ㄟ^在社會安全號列上定義UNIQUE約束做到。

1.4 檢查約束

  1. 檢查約束用來保證一列（或一組列）中的數(shù)據(jù)滿足一組指定的條件。檢查約束的常見用途有以下幾點。
    1. 檢查最小或最大值。例如，防止0個物品的訂單（即使0是合法的數(shù)）。
    2. 指定范圍。例如，保證發(fā)貨日期大于等于今天的日期，但不超過今天起一年后的日期。
    3. 只允許特定的值。例如，在性別字段中只允許M或F。
CREATE TABLE OrderItems
(
    order_num INTEGER NOT NULL,
    order_item INTEGER NOT NULL,
    prod_id CHAR(10) NOT NULL,
    -- 利用這個約束，任何插入（或更新）的行都會被檢查，保證quantity大于0。
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    item_price MONEY NOT NULL
);

-- 檢查名為gender的列只包含M或F，可編寫如下的ALTER TABLE語句
ADD CONSTRAINT CHECK (gender LIKE '[MF]');

22.2 索引

  1. 索引用來排序數(shù)據(jù)以加快搜索和排序操作的速度，類似于書的目錄;
  2. 可以在一個或多個列上定義索引，使DBMS保存其內(nèi)容的一個排過序的列表。
  3. 關于索引的幾點認識
    1. 索引改善檢索操作的性能，但降低了數(shù)據(jù)插入、修改和刪除的性能。在執(zhí)行這些操作時，DBMS必須動態(tài)地更新索引。
    2. 索引數(shù)據(jù)可能要占用大量的存儲空間。
    3. 并非所有數(shù)據(jù)都適合做索引。取值不多的數(shù)據(jù)（如州）不如具有更多可能值的數(shù)據(jù)（如姓或名），能通過索引得到那么多的好處。
    4. 索引用于數(shù)據(jù)過濾和數(shù)據(jù)排序。如果你經(jīng)常以某種特定的順序排序數(shù)據(jù)，則該數(shù)據(jù)可能適合做索引。
    5. 可以在索引中定義多個列（例如，州加上城市）。這樣的索引僅在以州加城市的順序排序時有用。如果想按城市排序，則這種索引沒有用處。
-- 在Products表的產(chǎn)品名列上創(chuàng)建一個簡單的索引
-- 索引必須唯一命名
CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);
  1. 索引的效率隨表數(shù)據(jù)的增加或改變而變化，定期檢查索引，并根據(jù)需要對索引進行調(diào)整；

22.3 觸發(fā)器

  1. 觸發(fā)器是特殊的存儲過程，它在特定的數(shù)據(jù)庫活動發(fā)生時自動執(zhí)行；
  2. 觸發(fā)器可以與特定表上的INSERT、UPDATE和DELETE操作（或組合）相關聯(lián)。
  3. 觸發(fā)器與單個的表相關聯(lián)（存儲過程可以關聯(lián)多張表，存儲過程只是簡單的存儲SQL語句）。與Orders表上的INSERT操作相關聯(lián)的觸發(fā)器只在Orders表中插入行時執(zhí)行；
  4. 觸發(fā)器內(nèi)的代碼具有的權(quán)限
    1. INSERT操作中的所有新數(shù)據(jù)；
    2. UPDATE操作中的所有新數(shù)據(jù)和舊數(shù)據(jù)；
    3. DELETE操作中刪除的數(shù)據(jù)。
  5. 觸發(fā)器的一些常見用途
    1. 保證數(shù)據(jù)一致。例如，在INSERT或UPDATE操作中將所有州名轉(zhuǎn)換為大寫。
    2. 基于某個表的變動在其他表上執(zhí)行活動。例如，每當更新或刪除一行時將審計跟蹤記錄寫入某個日志表。
    3. 進行額外的驗證并根據(jù)需要回退數(shù)據(jù)。例如，保證某個顧客的可用資金不超限定，如果已經(jīng)超出，則阻塞插入。
    4. 計算計算列的值或更新時間戳。
-- 對所有INSERT和UPDATE操作，將Customers表中的cust_state列轉(zhuǎn)換為大寫
-- SQL Server
CREATE TRIGGER customer_state
ON Customers
FOR INSERT, UPDATE
AS
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id;


-- Oracle和PostgreSQL
CREATE TRIGGER customer_state
AFTER INSERT OR UPDATE
FOR EACH ROW
BEGIN
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = :OLD.cust_id
END;
  1. 約束的處理比觸發(fā)器快，因此在可能的時候，應該盡量使用約束。

22.4 數(shù)據(jù)庫安全

  1. 使用用戶名用戶密碼來保證系統(tǒng)安全。
  2. 一般說來，需要保護的操作有：
    1. 對數(shù)據(jù)庫管理功能（創(chuàng)建表、更改或刪除已存在的表等）的訪問；
    2. 對特定數(shù)據(jù)庫或表的訪問；
    3. 訪問的類型（只讀、對特定列的訪問等）；
    4. 僅通過視圖或存儲過程對表進行訪問；
    5. 創(chuàng)建多層次的安全措施，從而允許多種基于登錄的訪問和控制；
    6. 限制管理用戶賬號的能力。
  3. 安全性使用SQL的GRANT和REVOKE語句來管理，交互式管理程序在內(nèi)部使用GRANT和REVOKE語句；
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內(nèi)容

  • 表 存儲在表中的數(shù)據(jù)是同一種類型的數(shù)據(jù)或清單。 數(shù)據(jù)庫中的表有為一個名字來標識自己。 表具有一些特性，這些特性定義...
    蛐蛐囍閱讀 1,455評論 0贊 7
  • 數(shù)據(jù)庫入門 數(shù)據(jù)庫： 保存有組織的數(shù)據(jù)的容器(通常是一個文件或一組文件).數(shù)據(jù)庫軟件應該稱為 DBMS(DataB...
    Mjericho閱讀 570評論 0贊 0
  • 前言 讀《sql必知必會 第四版》隨手做的筆記，寫的比較亂，可讀性并不好，讀的是中文版，翻譯過來的感覺有點怪怪的。...
    _老徐_閱讀 761評論 0贊 0
  • 今天的天還是不好，鄭州開始沙塵暴，當北京沙塵暴的時候我們暗暗竊喜，其實，很快又到了我們這里。感恩跟老公孩子一起過周...
    黃偉vi閱讀 200評論 0贊 0
  • 比爾.蓋茨曾說：“如果我要完成一件事情，我得立即動手去做，空談無濟于事。 那這件事是重要的嗎？...
    冷小熱閱讀 378評論 0贊 1

友情鏈接更多精彩內(nèi)容