MySQL列的别名
有时，列的名称是一些表达式，使查询的输出很难理解。要给列一个描述性名称，可以使用列别名。
以下语句说明了如何使用列别名：
SELECT
[column_1 | expression] AS descriptive_name
FROM table_name;
SELECT
[column_1 | expression] AS descriptive_name
FROM table_name;
SQL
要给列添加别名，可以使用AS关键词后跟别名。 如果别名包含空格，则必须引用以下内容：
SELECT
[column_1 | expression] AS `descriptive name`
FROM table_name;
SELECT
[column_1 | expression] AS `descriptive name`
FROM table_name;
SQL
因为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
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
SQL
以下查询选择员工的名字和姓氏，并将其组合起来生成全名。 CONCAT_WS函数用于连接名字和姓氏。
SELECT
CONCAT_WS(', ', lastName, firstname)
FROM
employees;
SELECT
CONCAT_WS(', ', lastName, firstname)
FROM
employees;
SQL
执行上面代码，得到以下结果 -
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
Shell
在上面示例中，列标题很难阅读理解。可以为输出的标题分配一个有意义的列别名，以使其更可读，如以下查询：
SELECT
CONCAT_WS(', ', lastName, firstname) AS `Full name`
FROM
employees;
SELECT
CONCAT_WS(', ', lastName, firstname) AS `Full name`
FROM
employees;
SQL
执行上面代码，得到以下结果 -
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
Shell
在MySQL中，可以使用ORDER BY，GROUP BY和HAVING子句中的列别名来引用该列。
以下查询使用ORDER BY子句中的列别名按字母顺序排列员工的全名：
SELECT
CONCAT_WS(' ', lastName, firstname) `Full name`
FROM
employees
ORDER BY
`Full name`;
SELECT
CONCAT_WS(' ', lastName, firstname) `Full name`
FROM
employees
ORDER BY
`Full name`;
SQL
执行上面代码，得到以下结果 -
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
Shell
以下语句查询总金额大于60000的订单。它在GROUP BY和HAVING子句中使用列别名。
SELECT
orderNumber `Order no.`,
SUM(priceEach * quantityOrdered) total
FROM
orderdetails
GROUP BY
`Order no.`
HAVING
total > 60000;
SELECT
orderNumber `Order no.`,
SUM(priceEach * quantityOrdered) total
FROM
orderdetails
GROUP BY
`Order no.`
HAVING
total > 60000;
SQL
执行上面查询语句，得到以下结果 -
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> 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
SQL
请注意，不能在WHERE子句中使用列别名。原因是当MySQL评估求值WHERE子句时，SELECT子句中指定的列的值可能尚未确定。
MySQL表的别名
可以使用别名为表添加不同的名称。使用AS关键字在表名称分配别名，如下查询语句语法：
table_name AS table_alias
table_name AS table_alias
SQL
该表的别名称为表别名。像列别名一样，AS关键字是可选的，所以完全可以省略它。
一般在包含INNER JOIN，LEFT JOIN，self join子句和子查询的语句中使用表别名。
下面来看看客户(customers)和订单(orders)表，它们的ER图如下所示 -
两个表都具有相同的列名称：customerNumber。如果不使用表别名来指定是哪个表中的customerNumber列，则执行查询时将收到类似以下错误消息：
Error Code: 1052. Column 'customerNumber' in on clause is ambiguous
Shell
为避免此错误，应该使用表别名来限定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;
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;
SQL
执行上面查询语句，得到以下结果 -
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
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
SQL
上面的查询从客户(customers)和订单(orders)表中选择客户名称和订单数量。 它使用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
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 还没有启动，请注意 MySQL 的 root 账户默认密码为空。启动 MySQL 后会发现已经有了一个 shiyanlou 的数据库，访问的用户名为 shiyanlou，密码为 shiyanlou，shiyanlou 数据库中包含三个表：
user：1000 名实验楼用户数据，包含两列，用户 ID 和用户名
course：10 门实验楼课程数据，包含两列，课程 ID 和课程名
usercourse：100 条用户课程学习记录，包含四列，ID，用户 ID，课程 ID 和学习时间（分钟）
目标
查询并将查询结果创建一个新表，任务完成后满足以下要求：
MySQL 服务处于运行状态
新的表名称为 favorite，包含四列：id（主键），user_name（用户名），course_name（课程名），study_time（学习时间）
favorite 表中存储的是所有在 usercourse 表中有学习记录的用户学习时间最长的课程，如果有多门课程学习时间相同，则都存入该表
解题过程
create table favorite ( id int(11) primary key not null auto_increment, user_name varchar(20) , course_name varchar(50), study_time int(11))insert into favorite(user_name, course_name, study_time)select a.name user_name, b.name course_name, c.time study_timefrom user a,course b,(select user_id, course_id, max(study_time) timefrom usercourse group by user_id )cwhere a.id=c.user_id and b.id=c.course_id;
总结
注意建favorite表时要参考user表、course表以及usercourse表的结构
