Mandatory SQL statement (6) Data Query and SQL statement Data Query

Mandatory SQL statement (6) Data Query and SQL statement Data Query

1. Basic Query
1) rename a column
Select name as 'name' from Table name
 
2) define constant Columns
Select = 'is 'From table name
 
3) top usage: percent
-- In this way, you can obtain the first 20% fields.
Select top 20 percent * from Table Name
 
4) Remove duplicate Columns
Select distinct column name from Table Name

5) Aggregate functions
Max avg count min sum
-- Multiple aggregate results are in one result set.
Select
Max age = (select max (age) from table name ),
Minimum age = (select min (age) from table name)
 
6) between and
Select * from table where xx between 5 and 6

2. Union uses Union to aggregate two result sets.
-- Age wage
----------
-- 19$ 20000
-- 50 $20005
-- 30 $23000
-- Summary $63005

-- Query the salaries of all ages and display the total salaries. (Like the above table)
Select
-- Convert age to varchar type
Convert (varchar (10), [age]) as age
Sum ([salary]) as salary
From employee table
Group by age
-- Merge two result sets into one.
Union
Select
-- A summary is a constant column.
'Summary', sum (salary)
From employee table
When union is used to merge two result sets,
The number of columns in the two result sets must be consistent and the data type must be consistent.
This is why age is converted into varchar in the code.
 
3. Order
-- Order by is used to sort result sets,
-- Its Order can be followed by not only one field,
-- Can also be connected to an expression.
Select *
From table
Order by (age + salary)/2.0 desc