SQL learning notes 4 Aggregate functions and sorting methods are very useful in data calling.
SQL learning notes 4 Aggregate functions and sorting methods are very useful in data calling.
Aggregate functions count, max, min, avg, sum...
Select count (*) from T_Employee
Select Max (FSalary) from T_Employee
Sort ASC ascending DESC descending
Select * from T_Employee order by Fage
Sort by age in descending order. Sort by salary in ascending order if the age is the same
Select * from T_Employee order by FAge DESC, FSalary ASC
Order by must be placed after the where clause
Wildcard Filter
Use like for wildcard Filtering
Single-character wildcard '_'
Multi-character wildcard '%'
Select * from T_Employee where FName like '_ erry'
NULL is unknown, rather than not
To query NULL data using SQL statements, use = or <> instead of is NULL or is not NULL.
Select * from T_Employee where FName is NULL
In () matches two values at the same time. Equivalent to 23 or 25
Between 20 and 30 match the number between 20 and 30
Group by group
Select FAge, count (*) from T_Employee
Group by Fage
Divide the same Fage into one group, and then count the number of each group.
The group by clause must be placed after the where clause. If you want to obtain the number of people in a certain age group greater than 1, you cannot use where count (*)> 1, because the aggregate function cannot be placed behind the where clause. Use having clause
Having is used to filter the grouped columns. The columns that can be used are the same as those in select. In the following example, having Fsalary> 2000 can only be used for where Fsalary> 2000.
Select FAge, count (*) from T_Employee
Group by FAge
Having count (*)> 1;
Limit the range of result sets
Select Top 3 * from T_Employee
Order by FSalary DESC
You can use the Row_Number function after selecting 3. 2005 from the sixth name.
Select Top 3 * from T_Employee
Where FNumber not in (select TOP 5 FNumber from T_Employee order by FSalary DESC)
Order by FSalary DESC
