SQL Server編程必知必會 -- （58-79 點總結）

標籤：customers

------------------------建立進階連接-----------------------
-- 58.使用表/列別名
-- 58.1. 對錶使用別名
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 =‘TNT2‘

--結果：
cust_name cust_contact
Coyote Inc.                                        Y Lee                                            
Yosemite Place                                     Y Sam                                            

-- 58.2. 對列使用別名
SELECT cust_name AS c_name,cust_contact AS c_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 =‘TNT2‘

--結果：
c_name c_contact
Coyote Inc.                                        Y Lee                                            
Yosemite Place                                     Y Sam                                            

-- 58.3. 正常的表示：
SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE  customers.cust_id = orders.cust_id
AND orderitems.order_num= orders.order_num
AND prod_id = ‘TNT2‘

-- 59.使用不同類型的連接

-- 59.1. 自連接： 找到生產ID為DTNTR物品的供應商，然後找出這個供應商生產的其他物品； 自連接有時候要比子查詢要快；
SELECT p1.prod_id,p1.prod_name,p1.vend_id
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = ‘DTNTR‘

-- 59.2. 子查詢： 找到生產ID為DTNTR物品的供應商，然後找出這個供應商生產的其他物品
SELECT prod_id, prod_name,vend_id
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id = ‘DTNTR‘)
-- 60. 自然連接：排除返回的資料有多次出現的情況，使得每個列只返回一次； 標準的自連接中相同的列可能多次出現；
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=‘FB‘

-- 61. 外部連接： 將一個表中的行與另一個表中的行相關聯，返回包含沒有關聯線的那些行；

--對每個客戶下了多少訂單進行計數，包括那些至今尚未下訂單的客戶
SELECT customers.cust_name,customers.cust_id,orders.order_num
--RIGHT OUTER JOIN從FROM子句右邊的表(orders表)中選擇所有行
--LEFT OUTER JOIN從FROM子句左邊的表(customers表)中選擇所有行
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id

--以前版本的簡化使用：
SELECT customers.cust_name,customers.cust_id,orders.order_num
FROM customers ,orders
WHERE customers.cust_id *= orders.cust_id

--完全外連接，從每個表中檢索不相關的行（這些行對另一個表的非選擇列具有NULL值）
SELECT customers.cust_name,customers.cust_id,orders.order_num
FROM customers FULL OUTER JOIN orders
ON customers.cust_id = orders.cust_id

--檢索所有客戶及其訂單
SELECT customers.cust_name,customers.cust_id,orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id

--列出所有產品以及訂購數量，包括沒有人訂購的產品

--計算平均銷售規模，包括那些至今尚未下訂單的客戶

-- 62. 使用帶聚集合函式的連接：

-- 檢索所有客戶及每個客戶所下的訂單數
SELECT customers.cust_name,
       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_name,
         customers.cust_id

-- 檢索所有客戶及每個客戶所下的訂單數,包含那些沒有下任何訂單的客戶        
SELECT customers.cust_name,
       customers.cust_id,
       COUNT(orders.order_num) as num_ord
FROM customers LEFT OUTER JOIN orders
ON   customers.cust_id = orders.cust_id
GROUP BY customers.cust_name,
         customers.cust_id
        
-- 63. 使用連接的注意事項

-- 一般使用內部連接，但是有外部連接也是有效。
-- 保證使用正確的連接條件，否則將返回不正確的資料。
-- 應該總是提供連接條件，否則會得出笛卡兒積。
-- 在一個連接中可以包含多個表，甚至對於每個連接可以採用不同的連接類型。

------------------------組合查詢-----------------------

--64. 有兩種基本情況，需要使用組合查詢：
--1. 在單個查詢中從不同的表返回類似結構的資料
--2. 對單個表執行多個查詢，按單個查詢返回資料

--找出價格小於等於5的所有物品的一個列表
SELECT vend_id,prod_id, prod_price
FROM products
WHERE prod_price <= 5

--找出價格小於等於5的所有物品的一個列表，而且還想包括供應商1001和1002生產的所有物品。
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <=5
UNION
SELECT vend_id, prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002)

--等價於
SELECT vend_id,prod_id, prod_price
FROM products
WHERE prod_price <=5
OR  vend_id IN (1001,1002)

--65. UNION使用注意事項
-- 必須由兩條或兩條以上的SELECT語句組成，語句之間用關鍵字UNION分隔
-- UNION中的每個查詢必須包含相同的列，運算式或聚集合函式，而且每個列必須以相同次序列出
-- 列資料類型必須相容：類型不必完全相同，但必須是SQL Server可以隱含地轉換的類型
-- UNION會自動取消重複的行

--66. UNION ALL 用來返回所有匹配行，包括重複的行
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <=5
UNION ALL
SELECT vend_id, prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002)

--67. 對組合查詢結果排序
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <=5
UNION
SELECT vend_id, prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002)
ORDER BY vend_id,prod_price

------------------------全文本搜尋-----------------------

-- 全文本搜尋，SQL Server不需要分別查看每個行，不需要分別分析和處理每個詞。
-- SQL Server建立指定列中各詞的一個索引，搜尋可以針對這些詞進行。這樣，SQL Server可以快速有效地覺得哪些詞匹配（哪些行包含它們），哪些詞不匹配；

-- 設定全文本搜尋需求：
-- 必須對相應的資料庫啟用全文本搜尋的支援
-- 必須定義一個目錄
-- 必須對要索引的表和列建立全文本索引

--68. 啟用全文本搜尋支援
EXEC sp_fulltext_database ‘enable‘

--69. 建立一個目錄
CREATE FULLTEXT CATALOG catalog_crashcourse

--70. 建立全文本索引，索引列note_text列。唯一標識各行的鍵，用KEY INDEX提供表的主鍵名pk_productnotes，ON 子句用來儲存全文本資料的目錄。
CREATE FULLTEXT INDEX ON productnotes(note_text)
KEY INDEX pk_productnotes
ON catalog_crashcourse

-- 不要在匯入資料時使用全文本索引

-- 管理目錄和索引

--71. 刪除和重建目錄索引，有效地進行一個完全的重新索引。
ALTER FULLTEXT CATALOG catalog_crashcourse REBUILD

--72. 使用FREETEXT進行搜尋

-- 進行全文本搜尋

-- FRETEXT進行簡單的搜尋，按意思進行匹配；
-- CONTAINS進行詞或短語的搜尋，包括近似詞、派生詞；
-- FRETEXT，CONTAINS都可用於SELECT 語句的WHERE子句；

SELECT note_id, note_text
FROM productnotes
WHERE FREETEXT(note_text,‘rabbit food‘)

--查詢列中包含短語rabbit food的行，沒有結果輸出，因為該短語沒有出現在任一行中。
SELECT note_id, note_text
FROM productnotes
WHERE note_text LIKE ‘%rabbit food%‘

--73. 使用CONTAINS進行搜尋，表示在列note_text中找出詞handsaw;
SELECT note_id, note_text
FROM productnotes
WHERE CONTAINS(note_text,‘handsaw‘)

--74. 使用CONTAINS進行搜尋，支援萬用字元，表示在列note_text中找出詞含有“anvil,後面任意匹配的”;
SELECT note_id, note_text
FROM productnotes
WHERE CONTAINS(note_text,‘"anvil*"‘)

--75. 使用CONTAINS進行搜尋，支援布爾操作符AND,OR和NOT;只匹配包含safe和handsaw的行
SELECT note_id, note_text
FROM productnotes
WHERE CONTAINS(note_text,‘safe AND handsaw‘)

--76. 使用CONTAINS進行搜尋，支援布爾操作符AND,OR和NOT;只匹配包含詞rabbit和不包含詞food的行
SELECT note_id, note_text
FROM productnotes
WHERE CONTAINS(note_text,‘rabbit  AND NOT food‘)

--77. 使用CONTAINS進行搜尋，只匹配包含相互靠近的詞detonate和quickly的行
SELECT note_id, note_text
FROM productnotes
WHERE CONTAINS(note_text,‘detonate NEAR quickly‘)

--78. 使用CONTAINS進行搜尋，匹配與詞vary有相同詞幹成分的詞，如varies
SELECT note_id, note_text
FROM productnotes
WHERE CONTAINS(note_text,‘FORMSOF(INFLECTIONAL,vary)‘)

--79. 排序搜尋的結果
-- 使用FREETEXT類型的搜尋，使用FREETEXTTABLE()函數來提供一個搜尋模式，指示全文本引擎匹配包含意思為rabbit和food的詞的行
-- FREETEXTTABLE()返回別名為f的一個表，這個表包含名為key的一個列，它匹配被索引的表的主鍵，和一個名為rank的列，它是被賦予的等級值。
-- 第一行的等級為256，表示一個較好的匹配，第二行等級為45，表示一個較差的匹配。

SELECT  f.rank, note_id, note_text
FROM productnotes,
     FREETEXTTABLE (productnotes,note_text,‘rabbit food‘) f
WHERE productnotes.note_id = f.[key]
ORDER BY RANK DESC

本文出自 “Ricky's Blog” 部落格，請務必保留此出處http://57388.blog.51cto.com/47388/1703603

SQL Server編程必知必會 -- （58-79 點總結）