Shulou(Shulou.com)06/01 Report--

This paper gives an example of MySQL Union merging query data and the usage of table aliases and field aliases. Share with you for your reference, the details are as follows:

Union keyword

SELECT s_id, f_name, f_priceFROM fruitsWHERE f_price < 9.0UNION ALLSELECT s_id, f_name, f_priceFROM fruitsWHERE s_id IN (101103)

Alias the table

SELECT * from orders AS oWHERE o.o_num = 30001bot select c.c_id, o.o_numFROM customers AS c LEFT OUTER JOIN orders AS oON c.c_id = o.c_id

Alias the field

SELECT f1.f_name AS fruit_name, f1.f_price AS fruit_priceFROM fruits AS f1WHERE f1.f_price < 8 position select CONCAT (RTRIM (s_name),'(', RTRIM (s_city),')) FROM suppliersORDER BY signorance select CONCAT (RTRIM (s_name),'(', RTRIM (s_city),')') as suppliers_titleFROM suppliersORDER BY s_name

[example. 62] query the list of all fruits whose prices are less than 9, query all kinds of fruits whose s_id equals 101,103, and use UNION to connect the query results.

SELECT s_id, f_name, f_priceFROM fruitsWHERE f_price < 9.0UNION ALLSELECT s_id, f_name, f_priceFROM fruitsWHERE s_id IN (101103)

[example. 63] query the list of all fruits whose prices are less than 9, query all kinds of fruits whose s_id equals 101,103, and use UNION ALL to connect the query results, the SQL statement is as follows

SELECT s_id, f_name, f_priceFROM fruitsWHERE f_price < 9.0UNION ALLSELECT s_id, f_name, f_priceFROM fruitsWHERE s_id IN (101103)

[example .64] alias o for the orders table to query the date of the order for order 30001

SELECT * from orders AS oWHERE o.o_num = 30001

[example .65] alias the customers and orders tables respectively, and query the join

SELECT c.c_id, o.o_numFROM customers AS c LEFT OUTER JOIN orders AS oON c.c_id = o.c_id

[example. 66] query fruits table, alias fruit_name,f_price for f_name, alias fruit_price for fruits table, alias F1 for fruits table, and query the names of fruits with f_price < 8 in the table.

SELECT f1.f_name AS fruit_name, f1.f_price AS fruit_priceFROM fruits AS f1WHERE f1.f_price < 8

[example .67] query the fields s_name and s_city in the suppliers table, use the CONCAT function to concatenate these two field values, and alias the column as suppliers_title

If you do not alias the connected value, it will not be intuitive enough to display the column name. Enter the following SQL

SELECT CONCAT (RTRIM (s_name),'(', RTRIM (s_city),') FROM suppliersORDER BY s_name

More readers who are interested in MySQL-related content can check out this site's special topics: "A Summary of MySQL Common functions", "A Collection of MySQL Log Operation skills", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

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.