SQL text (UNION)

"2014/10/14 0:20"

The database software expires, but you want to remember the use of Union ~order by.

connecting to a database

Start mysql-u root (no password set)

View databases in a database

show databases;

Select Test Database

Use test;

Create a database (this is a good thing to read)

String sql= "CREATE TABLE Milk (" + "ID INT not NULL auto_increment PRIMARY KEY," + "NAME VARCHAR (a) not null, ' + ' number in T default 0, "+" Price DOUBLE (6,2) of default 0.00, "+" Id_shop INT not NULL "+") ";

Show data table structure

Describe milk

To insert data into the database:

Insert into milk values (1, ' Mengniu ', 20,3.00,12); Insert into milk values (2, ' Yili ', 30,30.1,12), insert into milk values (3, ' Sanyuan ', 20,1.22,13)

displaying data in a database

select * from milk;

Querying using the Union statement

Select id from milk where number=20 union select ID from milk where number=30

Sort by using ORDER by

Error statement

Select id from milk where number=20 the ORDER by ID DESC Union SELECT ID from milk where number=30

Tip Information:

Correct statement:

Select id from milk where number=20  union select ID from milk where number=30 order by id DESC

1. only the order by is used in the last subquery of the Union, and the order by IS for the result set after the entire Union.

2. Each subquery in the Union must have the same number of columns, and the corresponding location column should have the same data type, but the column name can be different.

The following examples draw from other places:

II use the column ordinal instead of the actual column name, the sequence number starts at 1, and if the specified ordinal is not within the allowable range, an exception is reported. This can also be done using ORDER by Supplier_name

Select supplier_id, supplier_name from   suppliers   UNION  Select company_id, Company_Name   from Companies   ORDER by 2;  

III Use the same column name for each subquery of Unoin

Select supplier_id as ID, supplier_name as name from  suppliers   UNION  Select company_id as ID, company_name as Name from  companies   ORDER by name;  

SQL text (UNION)