给查询结果指定别名
使用AS给查询结果中的列名指定别名是AS关键字最常见的用法。例如,下面的SQL语句查询了cust_order表中所有客户的姓名和订单总金额,并将列名重命名为’Customer Name’和’Total Order Amount’:
SELECT cust_name AS `Customer Name`, SUM(order_amount) AS `Total Order Amount` FROM cust_order GROUP BY cust_name;
给表名指定别名
AS关键字还可以用于给表名指定别名,这在需要引用同一个表两遍或者需要引用一个长表名时非常有用。例如:
SELECT o.order_id, o.order_date, c.cust_name FROM orders AS o INNER JOIN customers AS c ON o.cust_id = c.cust_id;
给子查询指定别名
AS还可以用于给子查询指定别名,使得子查询更容易理解。下面的SQL语句查询了所有客户的姓名、订单总数和订单平均金额:
SELECT c.cust_name, o.total_orders, o.avg_order_amount FROM customers AS c INNER JOIN ( SELECT cust_id, COUNT(*) AS total_orders, AVG(order_amount) AS avg_order_amount FROM orders GROUP BY cust_id) AS o ON c.cust_id = o.cust_id;
以上就是MySQL中AS关键字的用法和作用,使用AS可以帮助大家使SQL语句更加易于理解和维护。