Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to use as in mysql". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn how to use as in mysql.

In mysql, the "as" keyword is used to specify aliases for data tables and fields. Syntax: 1, "SELECT field names AS aliases FROM data tables;", you can specify aliases for fields; 2, "SELECT field names FROM data table aliases;", you can specify aliases for tables.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

For ease of query, MySQL provides the AS keyword to specify aliases for tables and fields. This section focuses on how to specify an alias for tables and fields.

When using MySQL queries, when the table name is very long or when some special queries are executed, for convenience or when you need to use the same table multiple times, you can specify an alias for the table and replace the original table name with this alias.

Specify an alias for the field

Sometimes the names of the 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 statement shows how to use column aliases:

SELECT field name AS alias FROM data sheet

To add an alias to a field, use the AS keyword followed by an alias. If the alias contains spaces, you must refer to the following:

SELECT Field name AS `Alias `FROM data Table

Because the AS keyword is optional, you can omit it in the statement. Note that aliases can also be used on expressions.

Let's take a look at the employees table in the sample database, which has the following table structure-

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 | 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 name of the employee and combines them to generate the full name. The CONCAT_WS function is used to concatenate the first and last names.

SELECT CONCAT_WS (',', lastName, firstname) FROM employees

Execute the above code to 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, column headings are difficult to read and understand. You can assign a meaningful column alias to the output title to make it more readable, such as the following query:

SELECT CONCAT_WS (',', lastName, firstname) AS `Full name `from employees

Execute the above code to 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, you can use column aliases in the ORDER BY,GROUP BY and HAVING clauses to refer to the column.

The following query sorts the full names of employees alphabetically using column aliases in the ORDER BY clause:

SELECT CONCAT_WS ('', lastName, firstname) `Full name`from employeesORDER BY `Full name`

Execute the above code to get the following results-

Mysql > SELECT CONCAT_WS ('', lastName, firstname) `Full name`from employeesORDER 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 for 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) totalFROM orderdetailsGROUP BY `Order no.`Having total > 60000

Execute the above query and get the following results-

Mysql > SELECT orderNumber `Order no.`, SUM (priceEach * quantityOrdered) totalFROM orderdetailsGROUP BY `Order no.`HAVING total > 60000 Order no. | | total | +-+ | 10165 | 67392.85 | | 10287 | 61402.00 | | 10310 | 61234.67 | +-+-+ 3 rows in set |

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.

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 query statement syntax:

SELECT field name FROM datasheet AS alias

The alias for the table is called the table alias. Like column aliases, the AS keyword is optional, so it can be omitted altogether.

Table aliases are commonly used in statements that contain INNER JOIN,LEFT JOIN,self join clauses and subqueries.

Let's take a look at the customer (customers) and order (orders) tables, and their ER diagrams are as follows-

Both tables have the same column name: customerNumber. If you do not use a table alias to specify the customerNumber column in which table, 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 qualify the customerNumber column with a table alias:

SELECT customerName, COUNT (o.orderNumber) totalFROM customers cINNER JOIN orders o ON c.customerNumber = o.customerNumberGROUP BY customerNameHAVING total > = 5ORDER BY total DESC

Execute the above query and get the following results-

Mysql > SELECT customerName, COUNT (o.orderNumber) totalFROM customers cINNER JOIN orders o ON c.customerNumber = o.customerNumberGROUP BY customerNameHAVING total > = 5ORDER 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 selects the customer name and order quantity from the customer (customers) and order (orders) tables. It uses c as the table alias for customers table and o as the table alias for orders table. Columns in the customers and orders tables are referenced by table aliases (c and o).

If you do not use aliases in the above query, you must use the table name to reference its columns, which makes the query lengthy and less readable, as follows-

SELECT customers.customerName, COUNT (orders.orderNumber) totalFROM customersINNER JOIN orders ON customers.customerNumber = orders.customerNumberGROUP BY customerNameORDER BY total DESC so far, I believe you have a deeper understanding of "how to use as in mysql". You might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.