SQL必知必會（第3版）學習筆記【11-15章】

第11章 使用子查詢

子查詢

• 子查詢（subquery）：嵌套在其他查詢中的查詢

利用子查詢進行過濾

• 可以把一條SELECT語句傳回的結果用于另一條SELECT語句的WHERE子句
• 也可以使用子查詢來把3個查詢組合成一條語句

SELECT cust_id 
FROM Orders 
WHERE order_num IN (SELECT order_num 
                    FROM OrderItems 
                    WHERE prod_id = 'RGAN01');
           

作為計算字段使用子查詢

• 使用子查詢的另一方法是建立計算字段

第12章 聯結表

• 各表通過某些常用的值（即關系設計中的關系（relational））互相關聯
• 分解資料為多個表能更有效地存儲，更友善地處理，并且具有更強大的可伸縮性

使用聯結

• 通過聯結來用單條SELECT語句檢索出資料
• 聯結是一種機制，用來在一條SELECT語句中關聯表
• 聯結在實際的資料庫表中不存在，它不是實體實體

建立聯結

SELECT vend_name, prod_name, prod_price 
FROM Vendors, Products 
WHERE Vendors.vend_id = Products.vend_id;
           

• 單條SELECT語句傳回兩個（多個）不同表中的資料

WHERE子句的重要性

• 在一條SELECT語句中關聯幾個表時，相應的關系是在運作中構造的
• 在聯結兩個表時，實際上做的是将第一個表中的每一行與第二個表中的每一行配對
• 應該保證所有聯結都有WHERE子句，否則将傳回很多不正确的資料

笛卡爾積

• 由沒有聯結條件的表關系傳回的結果為笛卡爾積，檢索出的行的數目将是第一個表中的行數乘以第二個表中的行數
• 也稱為叉聯結（cross join）

内部聯結（等值聯結）

SELECT vend_name, prod_name, prod_price 
FROM Vendors INNER JOIN Products 
ON Vendors.vend_id = Products.vend_id;
           

聯結多個表

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 = ;
           

• 不要聯結不必要的表，以免造成性能下降

第13章 建立進階聯結

使用表别名

• 縮短SQL語句
• 允許在單條SELECT語句中多次使用相同的表

SELECT cust_name, cust_contact 
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';
           

使用不同類型的聯結

• 内部聯結（等值聯結）
• 自聯結
• 自然聯結
• 外部聯結

自聯結

• 自聯結通常作為外部語句來代替從相同表中檢索資料的使用子查詢語句
• 許多DBMS處理聯結遠比處理子查詢快得多

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';
           

自然聯結

• 自然聯結排除多次出現的列，每個列隻傳回一次
• 隻能選擇那些唯一的列
• 很可能我們永遠都不會用到不是自然聯結的内部聯結

外部聯結

• 聯結包含了那些在相關表中沒有關聯行的行，這稱為外部聯結

SELECT Customers.cust_id, Orders.order_num 
FROM Customers LEFT OUTER JOIN Orders 
    ON Customers.cust_id = Orders.cust_id;
           

• 使用關鍵字OUTER JOIN來指定聯結的類型
• 在使用OUTER JOIN文法時，必須使用RIGHT或LEFT關鍵字指定包括其所有行的表（LEFT指出的是OUTER JOIN左邊的表

簡化的外部聯結文法

SELECT Customers.cust_id, Orders.order_num 
FROM Customers, Orders 
WHERE Customers.cust_id *= Orders.cust_id;
           

• *=為左外部聯結操作符，它從左邊表中檢索所有行
• =*為右外部聯結操作符，它從右邊表中檢索所有行

全外部聯結

SELECT Customers.cust_id, Orders.order_num 
FROM Customers FULL OUTER JOIN Orders 
    ON Customers.cust_id = Orders.cust_id;
           

使用帶聚集函數的聯結

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;
           

使用聯結和聯結條件

• 注意所使用的聯結類型，一般使用内部聯結
• 注意相應的DBMS支援何種文法
• 應該總是提供聯結條件，否則會得出笛卡爾積

第14章 組合查詢

• UNION操作符，将多條SELECT語句組合成一個結果集

組合查詢

• 執行多條SELECT語句，并将結果作為單個查詢結果集傳回
• 這些組合查詢通常稱為并（union）或複合查詢（compound query）
• 有兩種基本情況需要使用組合查詢：
  • 在單個查詢中從不同的表類似傳回結構資料
  • 對單個表執行多個查詢，按單個查詢傳回資料

建立組合查詢

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';
           

UNION規則

• UNION必須由兩條或兩條以上的SELECT語句組成，語句之間用關鍵字UNION分隔
• UNION中的每個查詢必須包含相同的列、表達式或聚集函數（不過各個列不需要以相同的次序列出）
• 列資料類型必須相容：類型不必完全相同，但必須是DBMS可以隐含的轉換的類型（例如，不同的數值類型或不同的日期類型）

包含或取消重複的行

• UNION從查詢結果集中自動去除了重複的行
• 如果要包含重複的行，可使用UNION ALL（重複的結果）

對組合查詢結果排序

• 隻能使用一條ORDER BY子句，位于最後一條SELECT語句之後
• 會排序所有傳回的結果

第15章 插入資料

• INSERT語句
  • 插入完整的行
  • 插入行的一部分
  • 插入某些查詢的結果

插入完整的行

INSERT INTO Customers
VALUES('1000000006',
    'Toy Land',
    '123 Any Street',
    'New York',
    'NY',
    '11111',
    'USA',
    NULL,
    NULL);
           

• 雖然這種文法簡單，但并不安全，應該盡量避免使用
• 編寫依賴于特定列次序的SQL語句是很不安全的

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

• 這樣就不擔心會跟表中實際列的順序不一緻了

插入部分行

• 省略一些沒有值的列

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',
           

插入檢索出的資料

• INSERT SELECT
• 将一條SELECT語句的結果插入表中

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

從一個表複制到另一個表

• SELECT INTO
• 将複制資料到一個新表

SELECT *
INTO CustCopy
FROM Customers;
           

• 任何SELECT選項和子句都可以使用，包括WHERE和GROUP BY
• 可利用聯結從多個表插入資料
• 不管從多少個表中檢索資料，資料都隻能插入到單個表中