工具: HeidiSQL

小知識區

count(...)

CRUD:

• Create
• Read
• Update
• Delete

ws: select權限
wi: insert, update, delete權限

Select 基礎查詢

查詢資料前的基本概念

1. 表格(table), 記錄(row, record), 欄位(column)
2. 在設計 table的時候，通常會指定一個欄位為「主索引鍵(primary key)」
3. 資料型態: 數值, 字串, 日期(預設格式「年-月-日」), NULL(表示「不確定」、「未知」或「沒有」)

查詢敘述

1. 語法使用順序，使用順序不對會造成錯誤
   1. SELECT
   2. FROM
   3. WHERE
   4. GROUP BY
   5. HAVING
   6. ORDER BY
   7. LIMIT
2. 指定使用中的資料庫: USE 資料庫名稱
3. SELECT: 指定需要的欄位
   1. *: 全部
   2. 複數個欄位: 欄位1, 欄位2, 欄位3, ...
4. FROM: 指定 column, table
   1. 不同資料庫時，可以使用 資料庫2.表格名稱
5. 數學運算: % 餘數, MOD 餘數, * 乘, / 除, DIV 除整數, + 加, - 減
6. 別名 (alias name)
   1. 幫一般欄位取一個欄位別名是比較沒有必要的，如果是運算式的話，通常就要幫它取一個欄位別名來取代原來一大串的運算式。
   2. 別名中，別用 保留字與 空格
   3. 範例: 從 資料庫 cmdev 的 資料表 emp，拿資料並 取別名MonthSalary, !AnnualSalary, !AnnualFullSalary

   SELECT ename, salary AS "MonthSalary",
          salary * 12 AS "AnnualSalary",
          (salary * 12 ) + (salary DIV 2) "AnnualFullSalary"
   FROM cmdev.emp
   

條件查詢 where

1. 查詢條件: 順序的差異，每次找出最少的資料，以此準則安排條件的順序，對效能會有幫助。
2. 比較運算子: =, !=, >, >=, <, <=, 其中<=>與=的差異在於NULL的判斷
3. 邏輯運算子: 1. [NOT非], 2. [&&, AND而且], 3. [||, OR或, XOR互斥]
   1. 程式執行順序: ()先算, 由左到右, 1 > 2 > 3 (NOT較少用)
   2. BETWEEN 指定較小的值 AND 指定較大的值: 範圍比較
   3. IN(...): 成員比較
   4. IS: 是
   5. IS NOT: 不是
   6. LIKE: 像
4. NULL 值判斷
   1. 「NULL」值的判斷，不可以使用比較運算子(<=>除外)，判斷一般資料的條件設定，要用邏輯運算子判斷，以下兩種判斷法範例:

   SELECT Name, LifeExpectancy
   FROM country
   WHERE LifeExpectancy IS NULL
   
   SELECT Name, LifeExpectancy
   FROM country
   WHERE LifeExpectancy <=> NULL
   

5. 字串樣式
   1. %: 0到多個任何字元
   2. _: 一個任何字元

   SELECT
   FROM
   WHERE {欄位|資料|運算式} LIKE '樣版'
   

6. ORDER BY {欄位||運算式||位置編號} [ASC(預設)|DESC] [,...]

限制查詢

1. LIMIT: 指定回傳數量
   1. LIMIT 5: 後面一個數字，從最前面拿5筆
   2. LIMIT 5, 5: 後面兩個數字，起始點, 數量; 從第六筆開始拿(不包含第五筆)，往後拿五筆
   3. 傳回月薪最高前三名，範例

   SELECT empno, ename, salary
   FROM cmdev.emp
   ORDER BY salary DESC
   LIMIT 3;
   

2. 排除重複記錄
   1. DISTINCT: 資料庫會特別執行回傳紀錄是否重複的檢查

問題記錄

• 語法: 大、小寫 對搜尋結果有差異嗎?
  • A: 沒有，搜尋字填大、小寫都得到相同的結果

運算式與函式

資料型態: 數值, 字串, 日期/時間, 空值(使用NULL), 布林值

1. 數值: 精確值, 近似值( 科學表示法5E3 = 5000)
2. 字串
3. 日期/時間: INTERVAL 語法 日期or時間 +/- INTERVAL 數字 單位
4. NULL 值: 表是未知的記錄

函式

1. 字串函式:
   1. LOWER(字串): [字串]轉為小寫
   2. UPPER(字串): [字串]轉為大寫
   3. LPAD(字串1, 長度, 字串2), RPAD(字串1, 長度, 字串2): 如果[字串1]的長度小於指定的[長度]，就在[字串1]左邊/右邊使用[字串2]補滿
      1. 在處理報表資料的時候，很常用來控制報表內容的格式
   4. LTRIM(字串), RTRIM(字串): 移除[字串]左邊/右邊的空白
   5. TRIM(字串): 移除[字串]左、右的空白
   6. REPEAT(字串, 個數): 重複[字串]指定的[個數]
   7. REPLACE(字串1, 字串2, 字串3): 將[字串1]中的[字串2]替換為[字串3]
2. 截取字串內容的函式：
   1. LEFT(字串, 長度), RIGHT(字串, 長度)： 傳回[字串]左邊/右邊指定[長度]的內容
   2. SUBSTRING(字串, 位置)：
   3. SUBSTRING(字串, 位置, 長度)：
3. 連接字串的函式：
   1. CONCAT(參數 [,…])：傳回所有參數連接起來的字串
   2. CONCAT_WS(分隔字串, 參數 [,…])：傳回所有參數連接起來的字串，參數之間插入指定的[分隔字串]
4. 取得字串資訊的函式：
   1. LENGTH(字串)：傳回[字串]的長度(bytes)
   2. CHAR_LENGTH(字串)：傳回[字串]的長度(字元個數)
   3. LOCATE(字串1, 字串2)：傳回[字串1]在[字串2]中的位置，如果[字串2]中沒有[字串1]指定的內容就傳回0
5. 數學函式

日期時間函式

1. 計算日期與時間的函式：
   1. ADDDATE(日期, 天數)：傳回[日期]在指定[天數] 以後的日期
   2. ADDDATE(日期, INTERVAL 數字 單位)：傳回[日期]在指定[數字]的[單位]以後的日期
   3. ADDTIME(日期時間, INTERVAL數字 單位)：傳回[日期時間]在指定[數字]的[單位]以後的日期時間
   4. SUBDATE(日期, 天數)：傳回[日期]在指定[天數] 以前的日期
   5. SUBDATE(日期, INTERVAL 數字 單位)：傳回[日期]在指定[數字]的[單位]以前的日期
   6. SUBTIME(日期時間, INTERVAL數字 單位)：傳回[日期時間]在指定[數字]的[單位]以前的日期時間
   7. DATEDIFF(日期1, 日期2)：計算兩個日期差異的天數
   8. 範例:

   SELECT ename, hiredate
   ADDDATE(hiredate, 30),
   ADDDATE(hiredate, INTERVAL 30 DAY)
   FROM cmdev.emp e;
   

2. 流程控制函式: IF, CASE...WHEN ...THEN

群組查詢

查詢並計算資料的統計分析資訊

1. 群組函式, 也可以用在日期資料

   SELECT
   SUM(Population) PopSum,
   AVG(Population) PopAvg,
   MAX(Population) PopMax,
   MIN(Population) PopMin,
   COUNT(*) Amount
   FROM country
   

2. GROUP_CONCAT函式

   SELECT FROUP_CONCAT(DISTINCT Region)
   FROM country
   WHERE Continent = 'Europe'; /* 排除重複的資料後，紙回傳6個區域名稱連接在一起的紀錄 */
   

JOIN 與 UNION 查詢

使用多個表格

Inner Join

通稱內部結合，分成寫在 WHERE後面與 FROM後面

1. 使用結合條件

   SELECT country.Code, country.Capital, city.Name
   FROM country, city
   WHERE country.Capital = city.ID
   

2. 指定表格名稱
3. 表格別名

   SELECT a.name, a.Population
   FROM country a
   

4. 使用「INNER JOIN」

   SELECT country.Code, country.Capital, city.Name
   FROM country JOIN city ON Capital = city.ID
   

Outer Join (外部結合)

「內部結合」的查詢，一定要符合「結合條件」的資料才會出現
「內部結合」: 包含部門名稱的員工資料，可是沒有分派部門的員工就不用出現了
「外部結合」: 包含部門名稱的員工資料，沒有分派部門的員工也要出現
1. LEFT OUTER JOIN: 以左邊的表格為主
1. RIGHT OUTER JOIN: 以右邊的表格為主
1. 語法範例:
sql SELECT empno, ename, e.deptno, d.dname FROM cmdev.emp e LEFT/RIGHT OUTER JOIN cemdev.dept d ON e.deptno = d.deptno

合併查詢

SELECT 與 SELECT 結合
1.語法:
sql SELECT Region, Name, Papulation FROM country WHERE Region = 'Southeast Asia' AND Population < 2000 UNION SELECT Region, Name, Population FROM country WHERE Region = 'Eastern Asia' AND Population < 1000

1. 規則:
   1. 回傳結果的欄位名稱，使用第一個查詢的欄位名稱
   2. 所有查詢敘述的欄位數量一定要一樣