MySQL query function to alias tables and fields

Directory:

(1) Give the table an alias
(2) Give the field an alias

(recommended for free learning : mysql video tutorial)

---

(1). Give the table an alias

When the table name is very long or some special queries are executed, for convenience of operation or when the same table needs to be used multiple times, you can specify an alias for the table and use this alias to replace the original name. The basic syntax format for aliasing a table is:

表名 [as] 表别名

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

[Example 1] Take the alias o for the orders table and query the order date of 30001 orders. 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] Give the aliases for the customers and orders tables respectively. , and perform a connection query. The SQL statement is as follows:

mysql> select c.c_id,o.o_num    -> from customers as c left join orders as o    -> on c.c_id = o.c_id;+-------+-------+| c_id  | o_num |+-------+-------+| 10001 | 30001 || 10003 | 30002 || 10004 | 30003 || 10001 | 30005 || 10002 |  NULL |+-------+-------+5 rows in set (0.05 sec)

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 clauses and select lists. , on clause and order by clause, etc.

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

mysql> select f1.f_id,f1.f_name    -> from fruits as f1,fruits 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 the select statement to display query results, MySQL will display the output columns specified after each select. In some cases, the names of the displayed columns will be very long or the names are not intuitive enough. MySQL can specify column aliases. Replace fields or expressions. The basic syntax format for aliasing a field is:

列名 [as] 列别名

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

[Example 1] Query the fruits table, give f_name the alias fruit_name, f_price the alias fruit_price, and then give the fruits table the alias f1, query the fruit names of f_price

mysql> select f1.f_name as fruits_name,f1.f_price as fruit_price    -> from fruits as f1    -> where f1.f_price <8;+-------------+-------------+| fruits_name | fruit_price |+-------------+-------------+| lemon       |        6.40 || apple       |        5.20 || apricot     |        2.20 || berry       |        7.60 || xxxx        |        3.60 || cherry      |        3.20 || xbabay      |        2.60 || grape       |        5.30 || xbabay      |        3.60 |+-------------+-------------+9 rows in set (0.00 sec)

[Example 2] Query the fields s_name and s_city in the suppliers table, use the concat function to connect the two field values, and take the column alias as suppliers_title.

mysql> 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 results, the column name of the displayed result is the calculated field after the select clause. In fact, the calculated column has no name. This result is very difficult to understand. If Taking an alias for the field will make the results 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, and the display result is the specified Column aliases, which enhances the readability of query results.

Note: Table aliases are only used when executing queries and are not displayed in the returned results. After the column alias is defined, it will be returned to the client for display. The displayed result fields are the aliases of the field columns. .

Related free learning recommendations: mysql database(Video)

The above is the detailed content of MySQL query function to alias tables and fields. For more information, please follow other related articles on the PHP Chinese website!