SQL學(xué)習(xí)筆記四 聚合函數(shù)、排序方法，在數(shù)據(jù)調(diào)用中非常實用。
聚合函數(shù) count,max,min,avg,sum…
select count (*) from T_Employee
select Max(FSalary) from T_Employee
排序 ASC升序 DESC降序
select * from T_Employee order by Fage
先按年齡降序排列。如果年齡相同，則按薪水升序排列
select * from T_Employee order by FAge DESC,FSalary ASC
order by 要放在 where 子句之后
通配符過濾
通配符過濾用like
單字符通配符‘_’
多字符通配符‘%’
select * from T_Employee where FName like ‘_erry’
NULL 是不知道的意思，而不是沒有
用SQL語句查詢NULL的數(shù)據(jù)不能用=或 而用is NULL或者is not NULL
select * from T_Employee where FName is NULL
in(23,25)同時匹配兩個值。相當(dāng)于 23 or 25
between 20 and 30 匹配介于20到30之間的數(shù)
group by分組
select FAge, count(*) from T_Employee
Group by Fage
先把相同的Fage分一組，再統(tǒng)計每一組的個數(shù)
group by子句要放在where子句之后。如果想取某個年齡段人數(shù)大于1的，不能用where count（*） > 1 ，因為聚合函數(shù)不能放在where子句之后。要用having子句
Having是對分組后的列進(jìn)行過濾，能用的列和select中的一樣。如下例中則不能用having Fsalary>2000 只能用where Fsalary>2000
select FAge, count(*) from T_Employee
Group by FAge
having count(*) > 1;
限制結(jié)果集的范圍
select Top 3 * from T_Employee
order by FSalary DESC
從第六名開始選3個.2005后可以用Row_Number函數(shù)
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
