SQL 語法 SELECT (初階1)

關於 Linux 下的 Mysql (MariaDB) 設定請參考 http://benjr.tw/12461

測試環境為 Ubuntu 16.04 x86_64 ,常用的 SQL SELECT 基礎查詢.

先建立一個資料庫 (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 , DISTINCT , WHERE , ORDER BY , LIMIT 等組合來查詢資料庫內容.

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)

DISTINCT

Select 特定欄位時可以選擇 顯示全部 All(*) 或是 DISTINCT 不重覆的資料.

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

WHERE

SELECT "欄位" FROM "表格" 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個到多個任何字元) 或是 _ (任何一個字元).

    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)
    

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)

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)

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *