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

This article will explain in detail the case of aliasing tables and fields for MySQL query functions. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

(1) give the table an alias

When the table name is long or 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 use this alias instead of the original name. The basic syntax format for aliasing a table is:

Table name [as] Table alias

Table name is the name of the data table stored in the database, table alias is the new name of the table specified in the query, and the as keyword is optional.

[example 1] give the alias o to the orders table and query the order date of the 30001 order. The SQL statement is as follows

Mysql > select * from orders as o-> where o.o_num = 30001 +-+ | o_num | o_date | c_id | +-+ | 30001 | 2008-09-01 00:00:00 | 10001 | +-- -+-+ 1 row in set (0.00 sec)

[example 2] aliases the customers table and the orders table, and query the join. The SQL statement is as follows:

Mysql > select c. CSecretidPowero.oroomnum-> from customers as c left join orders as o-> on c.c_id = o.cprincipidtoscope copyright painting + | c_id | o_num | +-+-+ | 10001 | 30001 | | 10003 | 30002 | 10004 | 30003 | 10001 | 30005 | 10002 | NULL | +-+-+ 5 rows in set (0.05sec)

As you can see from the results, MySQL can alias multiple tables at the same time, and table aliases can be placed in different locations, such as where clause, select list, on clause, and order by clause.

Self-join is a special inner join. The two tables in the join query are the same table, and the query statement is as follows:

Mysql > select f1.fprincipidreignedf1.ftreasname-> from fruits as f1gradefruits as f2-> where f1.s_id = f2.s_id and f2.f_id = 'a1' +-+-+ | f_id | f_name | +-+-+ | A1 | apple | | b1 | blackberry | | c0 | cherry | +-+-+ 3 rows in set (0.00 sec) (2). Alias the field.

When using select statements to display query results, MySQL displays the output columns specified after each select. In some cases, the names of the columns displayed can be long or not intuitive enough, and MySQL can specify column aliases, replace fields, or expressions. The basic syntax format for aliasing a field is:

Column name [as] column alias

Column name is the name of the field defined in the table, column alias is the new name of the field, and the as keyword is an optional parameter.

[example 1] query fruits table, alias fruit_name for f_name, alias fruit_price for f_price, alias F1 for fruits table, query table f_price select f1.f_name as fruits_name,f1.f_price as fruit_price-> from fruits as F1-> where f1.f_price select concat (trim (s_name),'(', trim (s_city),')-> from suppliers-> order by s_name +-- + | concat (trim (s_name),'(', trim (s_city)) ')') | +-- + | ACME (Shanghai) | | DK Inc (Zhengzhou) | | FastFruit Inc. (Tianjin) | | FNK Inc. (Zhongshan) | Good Set (Taiyuan) | | Just Eat Ours (Beijing) | | LT Supplies (Chongqing) | +-+ 7 rows in set (0.00 sec)

As you can see from the result, the column name that displays the result is called the calculated field after the select clause. In fact, the calculated column has no name. This result is not easy to understand. If you give the field an alias, it will make the result clearer. The SQL statement is as follows:

Mysql > select concat (trim (s_name),'(', trim (s_city),')-> as suppliers_title-> from suppliers-> order by s_name +-+ | suppliers_title | +-+ | ACME (Shanghai) | | DK Inc (Zhengzhou) | | FastFruit Inc. (Tianjin) | | FNK Inc. (Zhongshan) | | Good Set (Taiyuan) | | Just Eat Ours (Beijing) | | LT Supplies (Chongqing) | + -+ 7 rows in set (0.00 sec)

As you can see, select adds as suppliers_title, which instructs MySQL to create an alias suppliers_title for the calculated field, displaying the result as the specified column alias, thus enhancing the readability of the query results.

Note: the table alias is only used when executing the query and is not displayed in the returned results, but after the column alias is defined, it will be returned to the client for display, and the displayed result field is the alias of the field column.

On the "MySQL query function aliases for tables and fields of the case" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

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.