What is the usage of as in mysql

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.

Specify aliases for fields

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 theASkeyword followed by the alias. If the alias contains spaces, it must be quoted as follows:

SELECT 字段名 AS `别名` FROM 数据表;

Because theASkeyword is optional, it can be omitted from the statement. Note that you can also use aliases on expressions.

Let’s take a look at theemployeestable 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_WSFunction 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 theORDER BY,GROUP BY, andHAVINGclauses.

The following query sorts the full names of employees in alphabetical order using the column alias in theORDER BYclause:

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 than60000. It uses column aliases in theGROUP BYandHAVINGclauses.

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 theWHEREclause. The reason is that when MySQL evaluates theWHEREclause, the value of the column specified in theSELECTclause may not have been determined yet.

Specify an alias for the table

You can use aliases to add different names to the table. Use theASkeyword 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, theASkeyword is optional, so it can be completely omitted.

Generally use table aliases in statements containingINNER JOIN,LEFT JOIN,self joinclauses 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 thecustomerNumbercolumn 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 thecustomerNumbercolumn:

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 usescas the table alias for thecustomerstable andoas the table alias for theorderstable. Columns in thecustomersandorderstables are referenced through table aliases (cando).

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!