在本教程中，您將學(xué)習(xí)如何使用MySQLSELECT語(yǔ)句從表或視圖查詢數(shù)據(jù)。
使用SELECT語(yǔ)句從表或視圖獲取數(shù)據(jù)。表由行和列組成，如電子表格。 通常，我們只希望看到子集行，列的子集或兩者的組合。SELECT語(yǔ)句的結(jié)果稱為結(jié)果集，它是行列表，每行由相同數(shù)量的列組成。
請(qǐng)參閱示例數(shù)據(jù)庫(kù)(yiibaidb)中的以下employees表的結(jié)構(gòu)。它有8列：?jiǎn)T工人數(shù)，姓氏，名字，分機(jī)，電子郵件，辦公室代碼，報(bào)告，職位等。
SELECT語(yǔ)句控制要查看哪些列和行。例如，如果只對(duì)所有員工的名字，姓氏和職位感興趣，或者您只想查看其職位是銷售代表的每位員工的信息，則SELECT語(yǔ)句可幫助您執(zhí)行這些操作。
我們來(lái)看一下SELECT語(yǔ)句的語(yǔ)法：
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語(yǔ)句由以下列表中所述的幾個(gè)子句組成：
SELECT之后是逗號(hào)分隔列或星號(hào)(*)的列表，表示要返回所有列。FROM指定要查詢數(shù)據(jù)的表或視圖。JOIN根據(jù)某些連接條件從其他表中獲取數(shù)據(jù)。WHERE過(guò)濾結(jié)果集中的行。GROUP BY將一組行組合成小分組，并對(duì)每個(gè)小分組應(yīng)用聚合函數(shù)。HAVING過(guò)濾器基于GROUP BY子句定義的小分組。ORDER BY指定用于排序的列的列表。LIMIT限制返回行的數(shù)量。語(yǔ)句中的SELECT和FROM語(yǔ)句是必須的，其他部分是可選的。
在隨后的教程中將更詳細(xì)地了解每個(gè)子句。在本教程中，我們將重點(diǎn)介紹
SELECT語(yǔ)句的簡(jiǎn)單形式用法。
SELECT語(yǔ)句允許通過(guò)在SELECT子句中指定逗號(hào)分隔列的列表來(lái)查詢表的部分?jǐn)?shù)據(jù)。 例如，如果要僅查看員工的名字，姓氏和職位，請(qǐng)使用以下查詢：
SELECT
lastname, firstname, jobtitle
FROM
employees;
即使員工表中有很多列，SELECT語(yǔ)句只返回表中所有行的三列數(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
注意比較以下兩個(gè)語(yǔ)句返回列有什么區(qū)別 -
語(yǔ)句-1
SELECT lastname, firstname, jobtitle FROM employees;
語(yǔ)句-2
SELECT * FROM employees;
如果要獲取employees表中所有列的數(shù)據(jù)，可以列出SELECT子句中的所有列名，或者只需使用星號(hào)(*)表示您想要從表的所有列獲取數(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表中的所有列和行。應(yīng)該使用星號(hào)(*)進(jìn)行測(cè)試。建議顯式獲取數(shù)據(jù)的列，原因如下：
*)可能會(huì)返回不使用的列的數(shù)據(jù)。 它在MySQL數(shù)據(jù)庫(kù)服務(wù)器和應(yīng)用程序之間產(chǎn)生不必要的I/O磁盤和網(wǎng)絡(luò)流量。*)并且有人通過(guò)添加更多列來(lái)更改表格數(shù)據(jù)時(shí)，將會(huì)得到一個(gè)與預(yù)期不同的結(jié)果集。*)可能會(huì)將敏感信息暴露給未經(jīng)授權(quán)的用戶。在本教程中，您已經(jīng)了解并熟悉了MySQL SELECT語(yǔ)句的用法，并通過(guò)SELECT語(yǔ)句從MySQL表中查詢數(shù)據(jù)。
掃碼關(guān)注鍍金池微信號(hào)
手機(jī)訂課，優(yōu)惠多多