SQL 語法 進階 SELECT

測試環境為 Ubuntu 16.04 x86_64 ,下面針對 SQL SELECT 的 WHERE , GROUP BY , HAVING , ORDER BY , LIMIT 與 sub-query 使用方式做說明, SELECT 查詢需注意資料庫字元編碼 Character Sets 與 文字排序 Collations ,請參考資料庫編碼 – http://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');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO employee VALUES ('Afa','Power','Engineer','48000');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO employee VALUES ('Boss','Testing','Manager','75000');
Query OK, 1 row affected (0.00 sec)

MariaDB [testdb]> INSERT INTO employee VALUES ('Cars','Testing','Senior Engineer','42000');
Query OK, 1 row affected (0.00 sec)

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.00 sec)
MariaDB [testdb]> SELECT Name,Salary FROM employee;
+------+--------+
| Name | Salary |
+------+--------+
| Ben  |  45000 |
| Afa  |  48000 |
| Boss |  75000 |
| Cars |  42000 |
+------+--------+
4 rows in set (0.00 sec)

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

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

DISTINCT 只顯示不重覆的內容.

MariaDB [testdb]> SELECT DISTINCT JobTitle FROM employee;
+-----------------+
| JobTitle        |
+-----------------+
| Engineer        |
| Manager         |
| Senior Engineer |
+-----------------+
3 rows in set (0.01 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.01 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.01 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.00 sec)
    
  2. NOT , != (非,不等於)
    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 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 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)
    
  4. > , >= (大於,大於等於)
    MariaDB [testdb]> SELECT * FROM employee WHERE Salary >= 50000;
    +------+---------+----------+--------+
    | Name | Dept    | JobTitle | Salary |
    +------+---------+----------+--------+
    | Boss | Testing | Manager  |  75000 |
    +------+---------+----------+--------+
    1 row in set (0.00 sec)
    
  5. && , AND (而且)
    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)
    
  6. || , OR (或)
    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)
    
  7. XOR (互斥)
    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)
    
  8. 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)
    
  9. IN (…) (多條件比較)
    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.00 sec)
    
  10. IS (是),IS NOT (不是)
    如果是要找出資料內含有 NULL 的,需要使用 IS , IS NOT .
  11. 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)
    
  12. 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.00 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.01 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.00 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.01 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 'Memeber#' FROM employee GROUP BY Dept;
    +---------+----------+
    | Dept    | Memeber# |
    +---------+----------+
    | Power   |        1 |
    | Testing |        3 |
    +---------+----------+
    2 rows in set (0.00 sec)
    

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

    MariaDB [testdb]> SELECT Dept,COUNT(*) AS 'Memeber#' FROM employee GROUP BY Dept HAVING COUNT(*) > 1;
    +---------+----------+
    | Dept    | Memeber# |
    +---------+----------+
    | Testing |        3 |
    +---------+----------+
    1 row in set (0.01 sec)
    
  3. ORDER BY
    ORDER BY 預設使用 ASC (Ascending) 從小到大,也可以設定從大到小 DESC (Descending).

    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)
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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