SQL 語法 進階 SELECT

adminLinux, MariaDB, Mysql, Ubuntu

Loading

測試環境為 Ubuntu 16.04 x86_64 ,下面針對 SQL SELECT 的 WHERE , GROUP BY , HAVING , ORDER BY , LIMIT 與 sub-query 使用方式做說明, SELECT 查詢需注意資料庫字元編碼 Character Sets 與 文字排序 Collations ,請參考資料庫編碼 – https://benjr.tw/102156 ).

先建立一個資料庫 (testdb) , 與 tables (employee) 格式為 Name char(20), Dept char(20), jobTitle char(20) 各 20 個字元以及 Salary int(11).

root@ubuntu:~# mysql -u root -p
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE testdb;
Database changed

MariaDB [testdb]> CREATE TABLE employee (Name char(20),Dept char(20),JobTitle char(20),Salary int(11));
Query OK, 0 rows affected (0.10 sec)

MariaDB [testdb]> DESCRIBE employee; 
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| Name     | char(20) | YES  |     | NULL    |       |
| Dept     | char(20) | YES  |     | NULL    |       |
| JobTitle | char(20) | YES  |     | NULL    |       |
| Salary   | int(11)  | YES  |     | NULL    |       |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)

MariaDB [testdb]> SHOW TABLES;
+------------------+
| Tables_in_sbtest |
+------------------+
| employee         |
+------------------+
1 row in set (0.00 sec)

透過 INSERT 新增加了四筆資料.

MariaDB [testdb]> INSERT INTO employee VALUES ('Ben','Testing','Engineer','45000') , ('Afa','Power','Engineer','48000') , ('Boss','Testing','Manager','75000') , ('Cars','Testing','Senior Engineer','42000');
Query OK, 4 rows affected (0.002 sec)
Records: 4  Duplicates: 0  Warnings: 0

SELECT FROM [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT]

SELECT 可以透過 [WHERE] [GROUP BY] [HAVING] [ORDER BY] [LIMIT] 來對查詢後的資料內容加以來篩選,分類,限制.

SELECT FROM

SELECT : 需指定欄位 , * 表示顯示資料表所有的資料, 當指定欄位時可以搭配 All (所有資料) 與 DISTINCT (只顯示不重覆的內.
FROM : 資料庫名稱.

MariaDB [testdb]> SELECT * FROM employee;
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Ben  | Testing | Engineer        |  45000 |
| Afa  | Power   | Engineer        |  48000 |
| Boss | Testing | Manager         |  75000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
4 rows in set (0.001 sec)

MariaDB [testdb]> SELECT Name,Salary FROM employee;
+------+--------+
| Name | Salary |
+------+--------+
| Ben  |  45000 |
| Afa  |  48000 |
| Boss |  75000 |
| Cars |  42000 |
+------+--------+
4 rows in set (0.001 sec)

All (預設值) 顯示所有資料.

MariaDB [testdb]> SELECT All JobTitle FROM employee;
+-----------------+
| JobTitle        |
+-----------------+
| Engineer        |
| Engineer        |
| Manager         |
| Senior Engineer |
+-----------------+
4 rows in set (0.001 sec)

DISTINCT 只顯示不重覆的內容.

MariaDB [testdb]> SELECT DISTINCT JobTitle FROM employee;
+-----------------+
| JobTitle        |
+-----------------+
| Engineer        |
| Manager         |
| Senior Engineer |
+-----------------+
3 rows in set (0.001 sec)

MariaDB [testdb]> SELECT DISTINCT Dept , JobTitle FROM employee;
+---------+-----------------+
| Dept    | JobTitle        |
+---------+-----------------+
| Testing | Engineer        |
| Power   | Engineer        |
| Testing | Manager         |
| Testing | Senior Engineer |
+---------+-----------------+
4 rows in set (0.001 sec)

CONCAT
透過 CONCAT 可以打兩個欄位顯示在一起.

MariaDB [testdb]> SELECT NAME, CONCAT(Dept, ' ', JobTitle) AS JOB FROM employee;
+------+-------------------------+
| NAME | JOB                     |
+------+-------------------------+
| Ben  | Testing Engineer        |
| Afa  | Power Engineer          |
| Boss | Testing Manager         |
| Cars | Testing Senior Engineer |
+------+-------------------------+
4 rows in set (0.001 sec)

WHERE

WHERE 可以針對特定欄位來搜尋.

  1. = , <=> (等於) 
    MariaDB [testdb]> SELECT * FROM employee WHERE JobTitle='Engineer';
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Ben  | Testing | Engineer |  45000 |
| Afa  | Power   | Engineer |  48000 |
+------+---------+----------+--------+
2 rows in set (0.001 sec)

    MariaDB [testdb]> SELECT * FROM employee WHERE JobTitle <=> 'Engineer';
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Ben  | Testing | Engineer |  45000 |
| Afa  | Power   | Engineer |  48000 |
+------+---------+----------+--------+
2 rows in set (0.001 sec)
  2. NOT (非) 
    MariaDB [testdb]> SELECT * FROM employee WHERE NOT jobTitle = 'Engineer';
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Boss | Testing | Manager         |  75000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
2 rows in set (0.00 sec)
  3. <> , != (不等於) 
    MariaDB [testdb]> SELECT * FROM employee WHERE JobTitle != 'Engineer';
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Boss | Testing | Manager         |  75000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
2 rows in set (0.00 sec)

    MariaDB [testdb]> SELECT * FROM employee WHERE JobTitle <> 'Engineer';
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Boss | Testing | Manager         |  75000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
2 rows in set (0.001 sec)
  4. < , <= (小於,小於等於) 
    MariaDB [testdb]> SELECT * FROM employee WHERE Salary <= 50000;
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Ben  | Testing | Engineer        |  45000 |
| Afa  | Power   | Engineer        |  48000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
3 rows in set (0.01 sec)
  5. > , >＝ (大於,大於等於) 
    MariaDB [testdb]> SELECT * FROM employee WHERE Salary >= 50000;
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Boss | Testing | Manager  |  75000 |
+------+---------+----------+--------+
1 row in set (0.00 sec)
  6. && , AND (而且) , || , OR (或) , XOR (互斥)
    && , AND 必需符合所有條件的資料.

    MariaDB [testdb]> SELECT * FROM employee WHERE jobTitle='Engineer' AND Dept='Testing';
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Ben  | Testing | Engineer |  45000 |
+------+---------+----------+--------+
1 row in set (0.01 sec)

    MariaDB [testdb]> SELECT * FROM employee WHERE jobTitle='Engineer' && Dept='Testing';
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Ben  | Testing | Engineer |  45000 |
+------+---------+----------+--------+
1 row in set (0.00 sec)

    || , OR 任一符合條件的資料.

    MariaDB [testdb]> SELECT * FROM employee WHERE jobTitle='Engineer' OR Dept='Testing';
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Ben  | Testing | Engineer        |  45000 |
| Afa  | Power   | Engineer        |  48000 |
| Boss | Testing | Manager         |  75000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
4 rows in set (0.00 sec)

    MariaDB [testdb]> SELECT * FROM employee WHERE jobTitle='Engineer' || Dept='Testing';
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Ben  | Testing | Engineer        |  45000 |
| Afa  | Power   | Engineer        |  48000 |
| Boss | Testing | Manager         |  75000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
4 rows in set (0.01 sec)

    AND OR 一起使用.

    MariaDB [testdb]>  SELECT * FROM employee WHERE Dept='Testing' AND (JobTitle='Senior Engineer' OR JobTitle='Engineer');
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Ben  | Testing | Engineer        |  45000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
2 rows in set (0.001 sec)

MariaDB [testdb]>  SELECT * FROM employee WHERE (Dept='Testing' AND JobTitle='Senior Engineer') OR JobTitle='Engineer';
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Ben  | Testing | Engineer        |  45000 |
| Afa  | Power   | Engineer        |  48000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
3 rows in set (0.001 sec)

    XOR 兩者中有一個成立的資料即為符合篩選,兩個條件皆成立或是皆不成立的資料不符合篩選.

    MariaDB [testdb]> SELECT * FROM employee WHERE jobTitle='Engineer' XOR Dept='Testing';
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Afa  | Power   | Engineer        |  48000 |
| Boss | Testing | Manager         |  75000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
3 rows in set (0.01 sec)
  7. BETWEEN … AND … , NOT BETWEEN … AND … (區間比較) 
    MariaDB [testdb]> SELECT * FROM employee WHERE Salary BETWEEN 45000 AND 50000;
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Ben  | Testing | Engineer |  45000 |
| Afa  | Power   | Engineer |  48000 |
+------+---------+----------+--------+
2 rows in set (0.00 sec)

    MariaDB [testdb]> SELECT * FROM employee WHERE Salary NOT BETWEEN 45000 AND 50000;
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Boss | Testing | Manager         |  75000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
2 rows in set (0.000 sec)
  8. IN (…) , NOT IN(…)(多條件比較)
    IN 裡面的多條件,只要其中一個成立的資料.而 NOT IN 就是相反.

    MariaDB [testdb]> SELECT * FROM employee WHERE Name IN('Ben','Afa');
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Ben  | Testing | Engineer |  45000 |
| Afa  | Power   | Engineer |  48000 |
+------+---------+----------+--------+
2 rows in set (0.001 sec)

MariaDB [testdb]> SELECT * FROM employee WHERE Name NOT IN('Ben','Afa');
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Boss | Testing | Manager         |  75000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
3 rows in set (0.001 sec)
  9. EXISTS, NOT EXISTS
    通常 EXISTS, NOT EXISTS 會搭配 Sub-query 一起使用, EXISTS 回傳 True 或是 False,用來決定該敘述是否要執行.

    MariaDB [testdb]> SELECT * FROM employee WHERE EXISTS(SELECT Salary FROM employee WHERE Salary > 100000);
Empty set (0.00 sec)

MariaDB [testdb]> SELECT * FROM employee WHERE EXISTS(SELECT Salary FROM employee WHERE Salary > 50000);
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Ben  | Testing | Engineer        |  45000 |
| Afa  | Power   | Engineer        |  48000 |
| Boss | Testing | Manager         |  75000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
4 rows in set (0.001 sec)
  10. IS (是),IS NOT (不是)
    TBD
  11. IS NULL (是 NULL),IS NOT NULL(不是 NULL)
    如果是要找出資料內含有 NULL 的,需要使用 IS NULL, IS NOT NULL.
    我們資料中沒有 NULL 的資料,先新增一筆含有 NULL 的資料.

    MariaDB [testdb]> INSERT INTO employee (Name) VALUES ('Judy');
Query OK, 1 row affected (0.00 sec)

    MariaDB [testdb]> SELECT * FROM employee WHERE Salary IS NULL;
+------+------+----------+--------+
| Name | Dept | JobTitle | Salary |
+------+------+----------+--------+
| Judy | NULL | NULL     |   NULL |
+------+------+----------+--------+
1 row in set (0.00 sec)

MariaDB [testdb]> SELECT * FROM employee WHERE Salary IS NOT NULL;
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Ben  | Testing | Engineer        |  45000 |
| Afa  | Power   | Engineer        |  48000 |
| Boss | Testing | Manager         |  75000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
4 rows in set (0.001 sec)

    移除這一筆含有 NULL 的資料.

    MariaDB [testdb]> DELETE FROM employee WHERE Name LIKE 'Judy';
Query OK, 1 row affected (0.002 sec)
  12. LIKE (像)
    LIKE 通常會搭配 % ( 0個到多個任何字元) 或是 _ (任何一個字元).
    搜尋 b 開頭的字串.

    MariaDB [testdb]> SELECT * FROM employee WHERE Name like 'b%';
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Ben  | Testing | Engineer |  45000 |
| Boss | Testing | Manager  |  75000 |
+------+---------+----------+--------+
2 rows in set (0.00 sec)

    搜尋 er 結尾的字串.

    MariaDB [testdb]> SELECT * FROM employee WHERE JobTitle like '%er';
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Ben  | Testing | Engineer        |  45000 |
| Afa  | Power   | Engineer        |  48000 |
| Boss | Testing | Manager         |  75000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
4 rows in set (0.00 sec)

    前後可同時使用 % .

    MariaDB [testdb]> SELECT * FROM employee WHERE JobTitle LIKE '%Engineer%';
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Ben  | Testing | Engineer        |  45000 |
| Afa  | Power   | Engineer        |  48000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
3 rows in set (0.00 sec)

    搜尋任何三個字元的字串.

    MariaDB [testdb]> SELECT * FROM employee WHERE Name like '___';
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Ben  | Testing | Engineer |  45000 |
| Afa  | Power   | Engineer |  48000 |
+------+---------+----------+--------+
2 rows in set (0.00 sec)
  13. NOT LIKE (不像)
    LIKE 的反向搜尋 NOT LIKE ,一樣可以通搭配 % ( 0個到多個任何字元) 或是 _ (任何一個字元).
    搜尋 非 Engineer 的字串.

    MariaDB [testdb]> SELECT * FROM employee WHERE JobTitle NOT LIKE '%Engineer%';
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Boss | Testing | Manager  |  75000 |
+------+---------+----------+--------+
1 row in set (0.00 sec)

GROUP BY , HAVING , ORDER BY 以及 LIMIT

透過 SELECT 搜尋到的資料還可以加上運算子 GROUP BY , HAVING , ORDER BY 以及 LIMIT 來對查詢後的資料內容加以來篩選,分類,限制.

  1. GROUP BY
    GROUP BY 可以依據指定欄位將資料將資料整合在一起,數字欄位可以透過 SUM 函數把數字相加.下面範例依據不同單位做分類,再以單位統計各單位的薪水.

    MariaDB [testdb]> SELECT Dept, SUM(salary) AS Total FROM employee GROUP BY Dept;
+---------+--------+
| Dept    | Total  |
+---------+--------+
| Power   |  48000 |
| Testing | 162000 |
+---------+--------+
2 rows in set (0.001 sec)

    WITH ROLLUP 可以把分類的資料做個總結(數字部分).

    MariaDB [testdb]> SELECT Dept, SUM(salary) AS Total FROM employee GROUP BY Dept WITH ROLLUP;
+---------+--------+
| Dept    | Total  |
+---------+--------+
| Power   |  48000 |
| Testing | 162000 |
| NULL    | 210000 |
+---------+--------+
3 rows in set (0.001 sec)

    透過函數 IFNULL 來把總合 ROLLUP 欄位顯示為 NULL 取代成我們想要的字串.

    MariaDB [testdb]> SELECT IFNULL(Dept,'Total') AS DEPT, SUM(Salary) FROM employee GROUP BY Dept WITH ROLLUP;
+---------+-------------+
| DEPT    | SUM(Salary) |
+---------+-------------+
| Power   |       48000 |
| Testing |      162000 |
| Total   |      210000 |
+---------+-------------+
3 rows in set, 1 warning (0.001 sec)

    函數說明:

    • IFNULL : 用來判斷 Expression 字串(第一個參數)是否為 NULL ,是就以 ALTER_Value (第二個參數)來取代.

    有辨法能直接多一的欄位來顯示該單位的薪水總數嗎? 可以透過 OVER 的方式來處理.

    MariaDB [testdb]> SELECT Name , Dept , JobTitle , Salary, SUM(Salary) OVER (PARTITION BY Dept) AS Dept_Total FROM employee;
+------+---------+-----------------+--------+------------+
| Name | Dept    | JobTitle        | Salary | Dept_Total |
+------+---------+-----------------+--------+------------+
| Afa  | Power   | Engineer        |  48000 |      48000 |
| Ben  | Testing | Engineer        |  45000 |     162000 |
| Cars | Testing | Senior Engineer |  42000 |     162000 |
| Boss | Testing | Manager         |  75000 |     162000 |
+------+---------+-----------------+--------+------------+
4 rows in set (0.000 sec)

    來看一下 OVER PARTITION BY 的使用方式,詳細使用方式請參考 – https://benjr.tw/102501 . 

    SUM(Salary) OVER (PARTITION BY Dept)
    • PARTITION BY Dept – 會依據 Dept 欄位來分類.
    • SUM(Salary) OVER – 會依據 PARTITION BY Dept 分類的結果來加總 (SUM).
    • AS Dept_Total – 欄位就會顯示依據各單位 Dept 欄位的加總

    GROUP BY 之後其他欄位資料只會顯示一筆資料,如果是數字欄位就可以透過 MAX(最大) 或是 MIN(最小) 函數來顯示.

    MariaDB [testdb]> SELECT Dept, MAX(Salary) AS "Top Salary", SUM(salary) AS Total FROM employee GROUP BY Dept;
+---------+------------+--------+
| Dept    | Top Salary | Total  |
+---------+------------+--------+
| Power   |      48000 |  48000 |
| Testing |      75000 | 162000 |
+---------+------------+--------+
2 rows in set (0.001 sec)

    透過 COUNT 函數,可以統計資料有幾筆.

    MariaDB [testdb]> SELECT Dept, COUNT(*) AS 'Member #' FROM employee GROUP BY Dept;
+---------+----------+
| Dept    | Member # |
+---------+----------+
| Power   |        1 |
| Testing |        3 |
+---------+----------+
2 rows in set (0.001 sec)

    透過 GROUP_CONCAT 函數,可以把文字整合在一起.

    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept;
+---------+---------------+
| Dept    | Member Name   |
+---------+---------------+
| Power   | Afa           |
| Testing | Ben,Boss,Cars |
+---------+---------------+
2 rows in set (0.001 sec)

    透過 GROUP_CONCAT 與 CONCAT 函數,可以把多個欄位文字整合在一起.

    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(CONCAT(Name,':',Salary) SEPARATOR ',') AS "Member Name: Salary" FROM employee GROUP BY Dept;
+---------+---------------------------------+
| Dept    | Member Name: Salary             |
+---------+---------------------------------+
| Power   | Afa:48000                       |
| Testing | Ben:45000,Boss:75000,Cars:42000 |
+---------+---------------------------------+
2 rows in set (0.00 sec)
  2. HAVING
    剛剛的 GROUP BY 可以將資料分類.

    MariaDB [testdb]>  SELECT Dept,COUNT(*) AS NUM FROM employee GROUP BY Dept;
+---------+-----+
| Dept    | NUM |
+---------+-----+
| Power   |   1 |
| Testing |   3 |
+---------+-----+
2 rows in set (0.000 sec)

    我們還可以針對 GROUP BY 後的資料做篩選,下面範例把部門人數超過 1人的才列出來.

    MariaDB [testdb]>  SELECT Dept,COUNT(*) AS NUM FROM employee GROUP BY Dept HAVING NUM > 1;
+---------+-----+
| Dept    | NUM |
+---------+-----+
| Testing |   3 |
+---------+-----+
1 row in set (0.000 sec)
  3. ORDER BY
    ORDER BY 預設使用 ASC (Ascending) 從小到大,也可以設定從大到小 DESC (Descending), 當有多個欄位需要排序時皆可個別指定 (DESC 或是預設的 ASC).

    MariaDB [testdb]> SELECT * FROM employee ORDER BY Salary;
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Cars | Testing | Senior Engineer |  42000 |
| Ben  | Testing | Engineer        |  45000 |
| Afa  | Power   | Engineer        |  48000 |
| Boss | Testing | Manager         |  75000 |
+------+---------+-----------------+--------+
4 rows in set (0.01 sec)

    MariaDB [testdb]> SELECT * FROM employee ORDER BY Salary DESC;
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Boss | Testing | Manager         |  75000 |
| Afa  | Power   | Engineer        |  48000 |
| Ben  | Testing | Engineer        |  45000 |
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
4 rows in set (0.01 sec)
  4. LIMIT
    LIMIT 可以限制查詢回應的行數,使用方式有兩種 N (只需要前面 N 行的資料) 或是 N,M (只顯示從 N+1 到 M 行的資料).

    MariaDB [testdb]> SELECT * FROM employee ORDER BY Salary ASC limit 1; 
+------+---------+-----------------+--------+
| Name | Dept    | JobTitle        | Salary |
+------+---------+-----------------+--------+
| Cars | Testing | Senior Engineer |  42000 |
+------+---------+-----------------+--------+
1 row in set (0.01 sec)

    MariaDB [testdb]> SELECT * FROM employee ORDER BY Salary DESC limit 0,3; 
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Boss | Testing | Manager  |  75000 |
| Afa  | Power   | Engineer |  48000 |
| Ben  | Testing | Engineer |  45000 |
+------+---------+----------+--------+
3 rows in set (0.00 sec)

子查詢 (sub-query)

子查詢 (sub-query) 為 一段 SQL 語法中內還包含一段 SELECT ( SELECT 敘述需置於 左右刮號 中).

這一段查詢 Dept=’testing’ 以及 Salary>50000 的方式,可以透過下下面的語法來完成.

MariaDB [testdb]> SELECT * FROM employee WHERE Dept='testing' AND Salary>50000;
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Boss | Testing | Manager  |  75000 |
+------+---------+----------+--------+
1 row in set (0.00 sec)

我們先 查詢 Dept=’testing’ ,再從這資料中查詢 Salary>50000 的欄位 (子查詢 不只可以放在 FROM ,也可以接著 SELECT , WHERE 後面).

MariaDB [testdb]> SELECT * FROM (SELECT * FROM employee WHERE Dept='testing') testing WHERE Salary>50000;
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Boss | Testing | Manager  |  75000 |
+------+---------+----------+--------+
1 row in set (0.00 sec)

常見錯誤

MariaDB [testdb]> SELECT * FROM (SELECT * FROM employee WHERE Dept='testing') WHERE Salary>50000;
Error 1248 (42000): Every derived table must have its own alias

FROM 後面接 (SELECT) 需接 alias 名稱,改成如下即可.

MariaDB [testdb]> SELECT * FROM (SELECT * FROM employee WHERE Dept='testing') testing WHERE Salary>50000;
+------+---------+----------+--------+
| Name | Dept    | JobTitle | Salary |
+------+---------+----------+--------+
| Boss | Testing | Manager  |  75000 |
+------+---------+----------+--------+
1 row in set (0.00 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料。