In mysql, the "as" keyword is used to specify aliases for data tables and fields. Syntax: 1. "SELECT field name AS alias FROM data table;", you can specify aliases for fields; 2. " SELECT field name FROM data table AS alias;", you can specify an alias for the table.
The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.
For the convenience of query, MySQL provides the AS keyword to specify aliases for tables and fields. This section mainly explains how to specify an alias for tables and fields.
When using MySQL query, when the table name is very long or when performing some special queries, for convenience of operation or when the same table needs to be used multiple times, you can specify an alias for the table, use this alias Replace the original name of the table.
Sometimes, the names of columns are expressions that make the output of the query difficult to understand. To give a column a descriptive name, use a column alias.
The following statements illustrate how to use column aliases:
SELECT 字段名 AS 别名 FROM 数据表;
To add an alias to a field, you can use the AS
keyword followed by the alias. If the alias contains spaces, it must be quoted as follows:
SELECT 字段名 AS `别名` FROM 数据表;
Because the AS
keyword is optional, it can be omitted from the statement. Note that you can also use aliases on expressions.
Let’s take a look at the employees
table in the sample database. Its table structure is as follows-
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
The following query selects the first and last names of employees and combines them Get up to generate the full name. CONCAT_WS
Function is used to concatenate first name and last name.
SELECT CONCAT_WS(', ', lastName, firstname) FROM employees;
Execute the above code and get the following results -
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
In the above example, the column headers are difficult to read and understand. You can assign a meaningful column alias to the title of the output to make it more readable, such as the following query:
SELECT CONCAT_WS(', ', lastName, firstname) AS `Full name` FROM employees;
Execute the above code and get the following results-
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
In MySQL, The column can be referenced using column aliases in the ORDER BY
, GROUP BY
, and HAVING
clauses.
The following query sorts the full names of employees in alphabetical order using the column alias in the ORDER BY
clause:
SELECT CONCAT_WS(' ', lastName, firstname) `Full name` FROM employees ORDER BY `Full name`;
Execute the above code and get the following results-
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
The following statement queries orders with a total amount greater than 60000
. It uses column aliases in the GROUP BY
and HAVING
clauses.
SELECT orderNumber `Order no.`, SUM(priceEach * quantityOrdered) total FROM orderdetails GROUP BY `Order no.` HAVING total > 60000;
Execute the above query statement and get the following results -
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
Please note that column aliases cannot be used in the
WHERE
clause. The reason is that when MySQL evaluates theWHERE
clause, the value of the column specified in theSELECT
clause may not have been determined yet.
You can use aliases to add different names to the table. Use the AS
keyword to assign an alias to the table name, as shown in the following query statement syntax:
SELECT 字段名 FROM 数据表 AS 别名;
The alias name of the table is the table alias. Like column aliases, the AS
keyword is optional, so it can be completely omitted.
Generally use table aliases in statements containing INNER JOIN
, LEFT JOIN
, self join
clauses and subqueries.
Let’s take a look at the customer (customers
) and order (orders
) tables. Their ER diagram is as follows-
Both tables have the same column name: customerNumber
. If you do not use a table alias to specify which table the customerNumber
column is from, you will receive an error message similar to the following when executing the query:
Error Code: 1052. Column 'customerNumber' in on clause is ambiguous
To avoid this error, you should use a table alias. Limit the customerNumber
column:
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;
Execute the above query statement and get the following results-
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
The above query starts from customers (customers
) and orders ( orders
) select the customer name and order quantity from the table. It uses c
as the table alias for the customers
table and o
as the table alias for the orders
table. Columns in the customers
and orders
tables are referenced through table aliases (c
and o
).
If you do not use aliases in the above query, you must use the table name to refer to its columns, which will make the query lengthy and less readable, as follows-
SELECT customers.customerName, COUNT(orders.orderNumber) total FROM customers INNER JOIN orders ON customers.customerNumber = orders.customerNumber GROUP BY customerName ORDER BY total DESC
[Related recommendations :mysql video tutorial】
The above is the detailed content of What is the usage of as in mysql. For more information, please follow other related articles on the PHP Chinese website!