SQL初階教學2

Posted on | In ,

延續SQL初階教學教學文件

在之前的教學文件中介紹了基本資料的選取、篩選、函數、分組的使用

這篇主要在表格連接的介紹

表格連接 JOIN

當要把多個表格資料連接在一起的時候要使用到JOIN的概念

假設我們要根據CUSTOMER資料表來看每個客戶他有哪些ORDERS

有以下幾種JOIN的方式

  • INNER JOIN 內部連接
  • LEFT (OUTER) JOIN 左外部連接
  • RIGHT (OUTER) JOIN 右外部連接
  • FULL (OUTER) JOIN 全部外部連接
  • CROSS JOIN 交叉連接 (少用)
  • NATURAL JOIN 自然連接 (少用)

INNER JOIN

INNER JOIN (也可以只寫JOIN)查詢結果返回符合連接條件的資料，兩邊資料都有對應到才回取得

SELECT * FROM CUSTOMER C
INNER JOIN ORDERS O
ON C.ACCOUNT = O.CUSTOMER_ACCOUNT

SELECT * FROM CUSTOMER C, ORDERS O
WHERE C.ACCOUNT = O.CUSTOMER_ACCOUNT

結果應該會看到14筆資料，ORDERS裡面有14筆資料可以對應到CUSTOMER資料

LEFT (OUTER) JOIN

LEFT JOIN 可以用來建立左外部連接，查詢的 SQL 敘述句 LEFT JOIN 左側資料表 (table_name1) 的所有記錄都會加入到查詢結果中，即使右側資料表 (table_name2) 中的連接欄位沒有符合的值也一樣。

SELECT * FROM CUSTOMER C
LEFT JOIN ORDERS O
ON C.ACCOUNT = O.CUSTOMER_ACCOUNT

Oracle可以在WHERE語句中加上(+)，代表這個表格不管有沒有資料都要

SELECT * FROM CUSTOMER C, ORDERS O
WHERE C.ACCOUNT = O.CUSTOMER_ACCOUNT(+)

結果16筆資料，有2筆客戶是沒有ORDERS資料的

RIGHT (OUTER) JOIN

相對於 LEFT JOIN，RIGHT JOIN 可以用來建立右外部連接，查詢的 SQL 敘述句 RIGHT JOIN 右側資料表 (table_name2) 的所有記錄都會加入到查詢結果中，即使左側資料表 (table_name2) 中的連接欄位沒有符合的值也一樣。

SELECT * FROM CUSTOMER C
RIGHT JOIN ORDERS O
ON C.ACCOUNT = O.CUSTOMER_ACCOUNT

Oracle可以在WHERE語句中加上(+)，代表這個表格不管有沒有資料都要

SELECT * FROM CUSTOMER C, ORDERS O
WHERE C.ACCOUNT(+) = O.CUSTOMER_ACCOUNT

結果15筆資料，有1筆ORDERS的客戶ACCOUNT不存在於CUSTOMER表格

FULL JOIN

FULL JOIN 即為 LEFT JOIN 與 RIGHT JOIN 的聯集，它會返回左右資料表中所有的紀錄，不論是否符合連接條件。

SELECT * FROM CUSTOMER C
FULL JOIN ORDERS O
ON C.ACCOUNT = O.CUSTOMER_ACCOUNT

結果17筆資料

CROSS JOIN

交叉連接為兩個資料表間的笛卡兒乘積 (Cartesian product)，兩個資料表在結合時，不指定任何條件，即將兩個資料表中所有的可能排列組合出來，以下例而言 CROSS JOIN 出來的結果資料列數為 3×5=15 筆，因此，當有 WHERE、ON、USING 條件時不建議使用。

SELECT table_column1, table_column2…

FROM table_name1

CROSS JOIN table_name2;

下面簡單範例用CUSTOMER和PRODUCT的NAME做交叉乘開所有的組合

SELECT C.NAME,P.NAME 
FROM CUSTOMER C
CROSS JOIN PRODUCT P

結果會是CUSTOMER 10筆 * PRODUCT 6筆 = 60筆

NATURAL JOIN

自然連接有 NATURAL JOIN、NATURAL LEFT JOIN、NATURAL RIGHT JOIN，兩個表格在進行 JOIN 時，加上 NATURAL 這個關鍵字之後，兩資料表之間同名的欄位會被自動結合在一起。

SELECT table_column1, table_column2…

FROM table_name1

NATURAL JOIN table_name2;

用不到 不示範了

資料處理

CONCATENATE

有的時候，我們有需要將由不同欄位獲得的資料串連在一起。每一種資料庫都有提供方法來達到這個目的：

  • MySQL: CONCAT( )
  • Oracle: CONCAT( ), ||
  • SQL Server: +

把CUSTOMER的ACCOUNT和NAME欄位串起來成一個欄位

SELECT C.ACCOUNT||C.NAME 
FROM CUSTOMER C

SUBSTRING

SQL 中的 SUBSTRING 函數是用來抓出一個欄位資料中的其中一部分。這個函數的名稱在不同的資料庫中不完全一樣：

  • MySQL: SUBSTR( ), SUBSTRING( )
  • Oracle: SUBSTR( )
  • SQL Server: SUBSTRING( )

SUBSTR (str, pos)

以上語法的意思是，由 中，選出所有從第 位置開始的字元。請注意，這個語法不適用於SQL Server上。

SUBSTR (str, pos, len)

以上語法的意思是，由 中的第 位置開始，選出接下去的 個字元。

擷取字串時如果取不到會回傳NULL

CUSTOMER的NAME只取前兩個字

SELECT SUBSTR(C.NAME,1,2) 
FROM CUSTOMER C

CUSTOMER的NAME取第2個字後的所有字(包含2)

SELECT SUBSTR(C.NAME,2) 
FROM CUSTOMER C

TRIM

SQL 中的 TRIM 函數是用來移除掉一個字串中的字頭或字尾。最常見的用途是移除字首或字尾的空白。這個函數在不同的資料庫中有不同的名稱：

  • MySQL: TRIM( ), RTRIM( ), LTRIM( )
  • Oracle: RTRIM( ), LTRIM( )
  • SQL Server: RTRIM( ), LTRIM( )

TRIM ( [ [位置] [要移除的字串] FROM ] 字串): [位置] 的可能值為 LEADING (起頭), TRAILING (結尾), or BOTH (起頭及結尾)。 這個函數將把 [要移除的字串] 從字串的起頭、結尾，或是起頭及結尾移除。如果我們沒有列出 [要移除的字串] 是什麼的話，那空白就會被移除。

LTRIM (字串): 將所有字串起頭的空白移除。

RTRIM (字串): 將所有字串結尾的空白移除。

LENGTH

在 SQL 中，長度函數是用來找出一個字串的長度。這個函數的名稱在不同的資料庫中不完全一樣：

  • MySQL: LENGTH( )
  • Oracle: LENGTH( )
  • SQL Server: LEN( )

REPLACE

在 SQL 中，Replace函數是用來改變一個字串的內容。這個函數在 MySQL、Oracle、及 SQL Server 上都是 Replace( )。這個函數的語法如下：

Replace (str1, str2, str3)

以上語法的意思是，在字串 str1 中，當 str2 出現時，將其以 str3 替代。