mysql数据库教程-MySQL别名

跨零代码为大家提供高品质的解决方案,请大家多多来访,跨零不胜感激,在此谢过。

在本教程中,您将学习如何使用MySQL别名来提高查询的可读性。

MySQL支持两种别名,称为列别名和表别名。下面来详细看看和学习MySQL中的别名。

MySQL列的别名

有时,列的名称是一些表达式,使查询的输出很难理解。要给列一个描述性名称,可以使用列别名。

以下语句说明了如何使用列别名:

SELECT   [column_1 | expression] AS descriptive_name FROM table_name; 

要给列添加别名,可以使用AS关键词后跟别名。 如果别名包含空格,则必须引用以下内容:

SELECT   [column_1 | expression] AS `descriptive name` FROM table_name; 

因为AS关键字是可选的,可以在语句中省略它。 请注意,还可以在表达式上使用别名。

我们来看看示例数据库(yiibaidb)中的employees表,其表结构如下所示 –

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函数用于连接名字和姓氏。

SELECT      CONCAT_WS(', ', lastName, firstname) FROM     employees; 

执行上面代码,得到以下结果 –

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; 

执行上面代码,得到以下结果 –

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 BYGROUP BYHAVING子句中的列别名来引用该列。

以下查询使用ORDER BY子句中的列别名按字母顺序排列员工的全名:

SELECT  CONCAT_WS(' ', lastName, firstname) `Full name` FROM  employees ORDER BY  `Full name`; 

执行上面代码,得到以下结果 –

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 BYHAVING子句中使用列别名。

SELECT  orderNumber `Order no.`,  SUM(priceEach * quantityOrdered) total FROM  orderdetails GROUP BY  `Order no.` HAVING  total > 60000; 

执行上面查询语句,得到以下结果 –

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 

MySQL表的别名

可以使用别名为表添加不同的名称。使用AS关键字在表名称分配别名,如下查询语句语法:

table_name AS table_alias 

该表的别名称为表别名。像列别名一样,AS关键字是可选的,所以完全可以省略它。

一般在包含INNER JOINLEFT JOINself join子句和子查询的语句中使用表别名。

下面来看看客户(customers)和订单(orders)表,它们的ER图如下所示 –

MySQL别名

两个表都具有相同的列名称:customerNumber。如果不使用表别名来指定是哪个表中的customerNumber列,则执行查询时将收到类似以下错误消息:

Error Code: 1052. Column 'customerNumber' in on clause is ambiguous 

为避免此错误,应该使用表别名来限定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; 

执行上面查询语句,得到以下结果 –

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)表中选择客户名称和订单数量。 它使用c作为customers表的表别名,o作为orders表的表别名。customersorders表中的列通过表别名(co)引用。

如果您不在上述查询中使用别名,则必须使用表名称来引用其列,这样的会使得查询冗长且可读性较低,如下 –

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别名,使查询更易于阅读和更易于理解。

  

从零到一,创造未来!跨零综合IT问题解决服务站,欢迎你的到来。Oracle数据库教程 mysql数据库教程/a> 只为你绽放。

本文固定链接: http://kua0.com/2019/04/22/mysql数据库教程-mysql别名/

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注