What is the usage of as in mysql-Mysql Tutorial-php.cn

What is the usage of as in mysql

青灯夜游

Release： 2022-01-06 16:02:38

Original

29100 people have browsed it

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.

What is the usage of as in mysql

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 数据表;

Copy after login

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 数据表;

Copy after login

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

Copy after login

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;

Copy after login

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

Copy after login

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;

Copy after login

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

Copy after login

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`;

Copy after login

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

Copy after login

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;

Copy after login

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

Copy after login

Please note that column aliases cannot be used in the WHERE clause. The reason is that when MySQL evaluates the WHERE clause, the value of the column specified in the SELECT clause may not have been determined yet.

Specify an alias for the table

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 别名;

Copy after login

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-

What is the usage of as in mysql

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

Copy after login

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;

Copy after login

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

Copy after login

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

Copy after login

[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!