MySQL查詢資料


在本教學中,您將學習如何使用MySQLSELECT語句從表或檢視查詢資料。

MySQL SELECT語句簡介

使用SELECT語句從表或檢視獲取資料。表由行和列組成,如電子試算表。 通常,我們只希望看到子集行,列的子集或兩者的組合。SELECT語句的結果稱為結果集,它是行列表,每行由相同數量的列組成。

請參閱範例資料庫(yiibaidb)中的以下employees表的結構。它有8列:員工人數,姓氏,名字,分機,電子郵件,辦公室程式碼,報告,職位等。

SELECT語句控制要檢視哪些列和行。例如,如果只對所有員工的名字,姓氏和職位感興趣,或者您只想檢視其職位是銷售代表的每位員工的資訊,則SELECT語句可幫助您執行這些操作。

我們來看一下SELECT語句的語法:

SELECT 
    column_1, column_2, ...
FROM
    table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
    conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;

SELECT語句由以下列表中所述的幾個子句組成:

  • SELECT之後是逗號分隔列或星號(*)的列表,表示要返回所有列。
  • FROM指定要查詢資料的表或檢視。
  • JOIN根據某些連線條件從其他表中獲取資料。
  • WHERE過濾結果集中的行。
  • GROUP BY將一組行組合成小分組,並對每個小分組應用聚合函式。
  • HAVING過濾器基於GROUP BY子句定義的小分組。
  • ORDER BY指定用於排序的列的列表。
  • LIMIT限制返回行的數量。

語句中的SELECTFROM語句是必須的,其他部分是可選的。

在隨後的教學中將更詳細地了解每個子句。在本教學中,我們將重點介紹SELECT語句的簡單形式用法。

MySQL SELECT語句範例

SELECT語句允許通過在SELECT子句中指定逗號分隔列的列表來查詢表的部分資料。 例如,如果要僅檢視員工的名字,姓氏和職位,請使用以下查詢:

SELECT 
    lastname, firstname, jobtitle
FROM
    employees;

即使員工表中有很多列,SELECT語句只返回表中所有行的三列資料,如下圖所示:

mysql> SELECT lastname, firstname, jobtitle FROM employees;
+-----------+-----------+----------------------+
| lastname  | firstname | jobtitle             |
+-----------+-----------+----------------------+
| Murphy    | Diane     | President            |
| Patterson | Mary      | VP Sales             |
| Firrelli  | Jeff      | VP Marketing         |
| Patterson | William   | Sales Manager (APAC) |
| Bondur    | Gerard    | Sale Manager (EMEA)  |
| Bow       | Anthony   | Sales Manager (NA)   |
| Jennings  | Leslie    | Sales Rep            |
| Thompson  | Leslie    | Sales Rep            |
| Firrelli  | Julie     | Sales Rep            |
| Patterson | Steve     | Sales Rep            |
| Tseng     | Foon Yue  | Sales Rep            |
| Vanauf    | George    | Sales Rep            |
| Bondur    | Loui      | Sales Rep            |
| Hernandez | Gerard    | Sales Rep            |
| Castillo  | Pamela    | Sales Rep            |
| Bott      | Larry     | Sales Rep            |
| Jones     | Barry     | Sales Rep            |
| Fixter    | Andy      | Sales Rep            |
| Marsh     | Peter     | Sales Rep            |
| King      | Tom       | Sales Rep            |
| Nishi     | Mami      | Sales Rep            |
| Kato      | Yoshimi   | Sales Rep            |
| Gerard    | Martin    | Sales Rep            |
+-----------+-----------+----------------------+
23 rows in set

注意比較以下兩個語句返回列有什麼區別 -

語句-1

SELECT lastname, firstname, jobtitle FROM employees;

語句-2

SELECT * FROM employees;

如果要獲取employees表中所有列的資料,可以列出SELECT子句中的所有列名,或者只需使用星號(*)表示您想要從表的所有列獲取資料,如下查詢:

mysql> SELECT * FROM employees;
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
| employeeNumber | lastName  | firstName | extension | email                 | officeCode | reportsTo | jobTitle             |
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
|           1002 | Murphy    | Diane     | x5800     | [email protected]    | 1          | NULL      | President            |
|           1056 | Patterson | Mary      | x4611     | [email protected]  | 1          |      1002 | VP Sales             |
|           1076 | Firrelli  | Jeff      | x9273     | [email protected]  | 1          |      1002 | VP Marketing         |
|           1088 | Patterson | William   | x4871     | [email protected] | 6          |      1056 | Sales Manager (APAC) |
|           1102 | Bondur    | Gerard    | x5408     | [email protected]     | 4          |      1056 | Sale Manager (EMEA)  |
|           1143 | Bow       | Anthony   | x5428     | [email protected]        | 1          |      1056 | Sales Manager (NA)   |
|           1165 | Jennings  | Leslie    | x3291     | [email protected]  | 1          |      1143 | Sales Rep            |
|           1166 | Thompson  | Leslie    | x4065     | [email protected]  | 1          |      1143 | Sales Rep            |
|           1188 | Firrelli  | Julie     | x2173     | [email protected]  | 2          |      1143 | Sales Rep            |
|           1216 | Patterson | Steve     | x4334     | [email protected] | 2          |      1143 | Sales Rep            |
|           1286 | Tseng     | Foon Yue  | x2248     | [email protected]     | 3          |      1143 | Sales Rep            |
|           1323 | Vanauf    | George    | x4102     | [email protected]    | 3          |      1143 | Sales Rep            |
|           1337 | Bondur    | Loui      | x6493     | [email protected]    | 4          |      1102 | Sales Rep            |
|           1370 | Hernandez | Gerard    | x2028     | [email protected]   | 4          |      1102 | Sales Rep            |
|           1401 | Castillo  | Pamela    | x2759     | [email protected]   | 4          |      1102 | Sales Rep            |
|           1501 | Bott      | Larry     | x2311     | [email protected]      | 7          |      1102 | Sales Rep            |
|           1504 | Jones     | Barry     | x102      | [email protected]      | 7          |      1102 | Sales Rep            |
|           1611 | Fixter    | Andy      | x101      | [email protected]    | 6          |      1088 | Sales Rep            |
|           1612 | Marsh     | Peter     | x102      | [email protected]     | 6          |      1088 | Sales Rep            |
|           1619 | King      | Tom       | x103      | [email protected]       | 6          |      1088 | Sales Rep            |
|           1621 | Nishi     | Mami      | x101      | [email protected]      | 5          |      1056 | Sales Rep            |
|           1625 | Kato      | Yoshimi   | x102      | [email protected]       | 5          |      1621 | Sales Rep            |
|           1702 | Gerard    | Martin    | x2312     | [email protected]     | 4          |      1102 | Sales Rep            |
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
23 rows in set

它返回employees表中的所有列和行。應該使用星號(*)進行測試。建議顯式獲取資料的列,原因如下:

  • 使用星號(*)可能會返回不使用的列的資料。 它在MySQL資料庫伺服器和應用程式之間產生不必要的I/O磁碟和網路流量。
  • 如果明確指定列,則結果集更可預測並且更易於管理。 想象一下,當您使用星號(*)並且有人通過新增更多列來更改表格資料時,將會得到一個與預期不同的結果集。
  • 使用星號(*)可能會將敏感資訊暴露給未經授權的使用者。

在本教學中,您已經了解並熟悉了MySQL SELECT語句的用法,並通過SELECT語句從MySQL表中查詢資料。