在本教程中,您將學習如何使用MySQLSELECT
語句從表或視圖查詢數(shù)據(jù)。
使用SELECT
語句從表或視圖獲取數(shù)據(jù)。表由行和列組成,如電子表格。 通常,我們只希望看到子集行,列的子集或兩者的組合。SELECT
語句的結(jié)果稱為結(jié)果集,它是行列表,每行由相同數(shù)量的列組成。
請參閱示例數(shù)據(jù)庫(yiibaidb
)中的以下employees
表的結(jié)構(gòu)。它有8
列:員工人數(shù),姓氏,名字,分機,電子郵件,辦公室代碼,報告,職位等。
SELECT
語句控制要查看哪些列和行。例如,如果只對所有員工的名字,姓氏和職位感興趣,或者您只想查看其職位是銷售代表的每位員工的信息,則SELECT
語句可幫助您執(zhí)行這些操作。
我們來看一下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
指定要查詢數(shù)據(jù)的表或視圖。JOIN
根據(jù)某些連接條件從其他表中獲取數(shù)據(jù)。WHERE
過濾結(jié)果集中的行。GROUP BY
將一組行組合成小分組,并對每個小分組應用聚合函數(shù)。HAVING
過濾器基于GROUP BY
子句定義的小分組。ORDER BY
指定用于排序的列的列表。LIMIT
限制返回行的數(shù)量。語句中的SELECT
和FROM
語句是必須的,其他部分是可選的。
在隨后的教程中將更詳細地了解每個子句。在本教程中,我們將重點介紹
SELECT
語句的簡單形式用法。
SELECT
語句允許通過在SELECT
子句中指定逗號分隔列的列表來查詢表的部分數(shù)據(jù)。 例如,如果要僅查看員工的名字,姓氏和職位,請使用以下查詢:
SELECT
lastname, firstname, jobtitle
FROM
employees;
即使員工表中有很多列,SELECT
語句只返回表中所有行的三列數(shù)據(jù),如下圖所示:
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
注意比較以下兩個語句返回列有什么區(qū)別 -
語句-1
SELECT lastname, firstname, jobtitle FROM employees;
語句-2
SELECT * FROM employees;
如果要獲取employees
表中所有列的數(shù)據(jù),可以列出SELECT
子句中的所有列名,或者只需使用星號(*
)表示您想要從表的所有列獲取數(shù)據(jù),如下查詢:
mysql> SELECT * FROM employees;
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
| employeeNumber | lastName | firstName | extension | email | officeCode | reportsTo | jobTitle |
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
| 1002 | Murphy | Diane | x5800 | dmurphy@yiibai.com | 1 | NULL | President |
| 1056 | Patterson | Mary | x4611 | mpatterso@yiibai.com | 1 | 1002 | VP Sales |
| 1076 | Firrelli | Jeff | x9273 | jfirrelli@yiibai.com | 1 | 1002 | VP Marketing |
| 1088 | Patterson | William | x4871 | wpatterson@yiibai.com | 6 | 1056 | Sales Manager (APAC) |
| 1102 | Bondur | Gerard | x5408 | gbondur@gmail.com | 4 | 1056 | Sale Manager (EMEA) |
| 1143 | Bow | Anthony | x5428 | abow@gmail.com | 1 | 1056 | Sales Manager (NA) |
| 1165 | Jennings | Leslie | x3291 | ljennings@yiibai.com | 1 | 1143 | Sales Rep |
| 1166 | Thompson | Leslie | x4065 | lthompson@yiibai.com | 1 | 1143 | Sales Rep |
| 1188 | Firrelli | Julie | x2173 | jfirrelli@yiibai.com | 2 | 1143 | Sales Rep |
| 1216 | Patterson | Steve | x4334 | spatterson@yiibai.com | 2 | 1143 | Sales Rep |
| 1286 | Tseng | Foon Yue | x2248 | ftseng@yiibai.com | 3 | 1143 | Sales Rep |
| 1323 | Vanauf | George | x4102 | gvanauf@yiibai.com | 3 | 1143 | Sales Rep |
| 1337 | Bondur | Loui | x6493 | lbondur@yiibai.com | 4 | 1102 | Sales Rep |
| 1370 | Hernandez | Gerard | x2028 | ghernande@gmail.com | 4 | 1102 | Sales Rep |
| 1401 | Castillo | Pamela | x2759 | pcastillo@gmail.com | 4 | 1102 | Sales Rep |
| 1501 | Bott | Larry | x2311 | lbott@yiibai.com | 7 | 1102 | Sales Rep |
| 1504 | Jones | Barry | x102 | bjones@gmail.com | 7 | 1102 | Sales Rep |
| 1611 | Fixter | Andy | x101 | afixter@yiibai.com | 6 | 1088 | Sales Rep |
| 1612 | Marsh | Peter | x102 | pmarsh@yiibai.com | 6 | 1088 | Sales Rep |
| 1619 | King | Tom | x103 | tking@gmail.com | 6 | 1088 | Sales Rep |
| 1621 | Nishi | Mami | x101 | mnishi@gmail.com | 5 | 1056 | Sales Rep |
| 1625 | Kato | Yoshimi | x102 | ykato@gmail.com | 5 | 1621 | Sales Rep |
| 1702 | Gerard | Martin | x2312 | mgerard@gmail.com | 4 | 1102 | Sales Rep |
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
23 rows in set
它返回employees
表中的所有列和行。應該使用星號(*
)進行測試。建議顯式獲取數(shù)據(jù)的列,原因如下:
*
)可能會返回不使用的列的數(shù)據(jù)。 它在MySQL數(shù)據(jù)庫服務器和應用程序之間產(chǎn)生不必要的I/O磁盤和網(wǎng)絡流量。*
)并且有人通過添加更多列來更改表格數(shù)據(jù)時,將會得到一個與預期不同的結(jié)果集。*
)可能會將敏感信息暴露給未經(jīng)授權(quán)的用戶。在本教程中,您已經(jīng)了解并熟悉了MySQL SELECT
語句的用法,并通過SELECT
語句從MySQL表中查詢數(shù)據(jù)。