mysql> desc employees; +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | employeeNumber | int(11) | NO | PRI | NULL | | | lastName | varchar(50) | NO | | NULL | | | firstName | varchar(50) | NO | | NULL | | | extension | varchar(10) | NO | | NULL | | | email | varchar(100) | NO | | NULL | | | officeCode | varchar(10) | NO | MUL | NULL | | | reportsTo | int(11) | YES | MUL | NULL | | | jobTitle | varchar(50) | NO | | NULL | | +----------------+--------------+------+-----+---------+-------+ 8 rows in set 以下查詢選擇員工的名字和姓氏，并將其組合起來生成全名。 CONCAT_WS函數(shù)用于連接名字和姓氏。
SELECT CONCAT_WS(', ', lastName, firstname) FROM employees; 執(zhí)行上面代碼，得到以下結(jié)果 -
mysql> SELECT CONCAT_WS(', ', lastName, firstname) FROM employees; +--------------------------------------+ | CONCAT_WS(', ', lastName, firstname) | +--------------------------------------+ | Murphy, Diane | | Patterson, Mary | | Firrelli, Jeff | | Patterson, William | | Bondur, Gerard | | Bow, Anthony | | Jennings, Leslie | | Thompson, Leslie | | Firrelli, Julie | | Patterson, Steve | | Tseng, Foon Yue | | Vanauf, George | | Bondur, Loui | | Hernandez, Gerard | | Castillo, Pamela | | Bott, Larry | | Jones, Barry | | Fixter, Andy | | Marsh, Peter | | King, Tom | | Nishi, Mami | | Kato, Yoshimi | | Gerard, Martin | +--------------------------------------+ 23 rows in set 在上面示例中，列標題很難閱讀理解。可以為輸出的標題分配一個有意義的列別名，以使其更可讀，如以下查詢：
SELECT CONCAT_WS(', ', lastName, firstname) AS `Full name` FROM employees; 執(zhí)行上面代碼，得到以下結(jié)果 -
mysql> SELECT CONCAT_WS(', ', lastName, firstname) AS `Full name` FROM employees; +--------------------+ | Full name | +--------------------+ | Murphy, Diane | | Patterson, Mary | | Firrelli, Jeff | ... ... | King, Tom | | Nishi, Mami | | Kato, Yoshimi | | Gerard, Martin | +--------------------+ 23 rows in set 在MySQL中，可以使用ORDER BY，GROUP BY和HAVING子句中的列別名來引用該列。
以下查詢使用ORDER BY子句中的列別名按字母順序排列員工的全名：
SELECT CONCAT_WS(' ', lastName, firstname) `Full name` FROM employees ORDER BY `Full name`; 執(zhí)行上面代碼，得到以下結(jié)果 -
mysql> SELECT CONCAT_WS(' ', lastName, firstname) `Full name` FROM employees ORDER BY `Full name`; +-------------------+ | Full name | +-------------------+ | Bondur Gerard | | Bondur Loui | | Bott Larry | | Bow Anthony | | Castillo Pamela | | Firrelli Jeff | | Firrelli Julie | | Fixter Andy | | Gerard Martin | | Hernandez Gerard | | Jennings Leslie | | Jones Barry | | Kato Yoshimi | | King Tom | | Marsh Peter | | Murphy Diane | | Nishi Mami | | Patterson Mary | | Patterson Steve | | Patterson William | | Thompson Leslie | | Tseng Foon Yue | | Vanauf George | +-------------------+ 23 rows in set 以下語句查詢總金額大于60000的訂單。它在GROUP BY和HAVING子句中使用列別名。
SELECT orderNumber `Order no.`, SUM(priceEach * quantityOrdered) total FROM orderdetails GROUP BY `Order no.` HAVING total > 60000; 執(zhí)行上面查詢語句，得到以下結(jié)果 -
mysql> SELECT orderNumber `Order no.`, SUM(priceEach * quantityOrdered) total FROM orderdetails GROUP BY `Order no.` HAVING total > 60000; +-----------+----------+ | Order no. | total | +-----------+----------+ | 10165 | 67392.85 | | 10287 | 61402.00 | | 10310 | 61234.67 | +-----------+----------+ 3 rows in set 請注意，不能在WHERE子句中使用列別名。原因是當MySQL評估求值WHERE子句時，SELECT子句中指定的列的值可能尚未確定。
Error Code: 1052. Column 'customerNumber' in on clause is ambiguous 為避免此錯誤，應(yīng)該使用表別名來限定customerNumber列：
SELECT customerName, COUNT(o.orderNumber) total FROM customers c INNER JOIN orders o ON c.customerNumber = o.customerNumber GROUP BY customerName HAVING total >=5 ORDER BY total DESC; 執(zhí)行上面查詢語句，得到以下結(jié)果 -
mysql> SELECT customerName, COUNT(o.orderNumber) total FROM customers c INNER JOIN orders o ON c.customerNumber = o.customerNumber GROUP BY customerName HAVING total >=5 ORDER BY total DESC; +------------------------------+-------+ | customerName | total | +------------------------------+-------+ | Euro+ Shopping Channel | 26 | | Mini Gifts Distributors Ltd. | 17 | | Reims Collectables | 5 | | Down Under Souveniers, Inc | 5 | | Danish Wholesale Imports | 5 | | Australian Collectors, Co. | 5 | | Dragon Souveniers, Ltd. | 5 | +------------------------------+-------+ 7 rows in set 上面的查詢從客戶(customers)和訂單(orders)表中選擇客戶名稱和訂單數(shù)量。 它使用c作為customers表的表別名，o作為orders表的表別名。customers和orders表中的列通過表別名(c和o)引用。
SELECT customers.customerName, COUNT(orders.orderNumber) total FROM customers INNER JOIN orders ON customers.customerNumber = orders.customerNumber GROUP BY customerName ORDER BY total DESC 到此，相信大家對“mysql中as怎么用”有了更深的了解，不妨來實際操作一番吧！
