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
限制返回行的數量。
語句中的SELECT
和FROM
語句是必須的,其他部分是可選的。
在隨後的教程中將更詳細地瞭解每個子句。在本教程中,我們將重點介紹
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表中查詢數據。