理解查詢

服務(wù)器執(zhí)行命令，在原始數(shù)據(jù)表中查找符合條件的數(shù)據(jù)，產(chǎn)生一個(gè)虛擬表。
虛擬表是數(shù)據(jù)組合后的重新展示浴鸿，而不是原始的物理數(shù)據(jù)。

查詢基本語法構(gòu)成

select <列名>
from <表名>
[where <查詢條件表達(dá)式>]
[order by <排序的列名> ASC或DESC]

查詢?nèi)啃泻土?/h4>

select * from Students

查詢部分行

select StudentName,Gender,出生日期
from Students where Gender='男' and Age>20

使用“AS”或使用“=”重新命名字段

select StudentName as 姓名,Gender as 性別,出生日期=birthday
from Students where Gender='男'

加號的使用

select 姓名=StudentName,地址和電話=StudentAddress+'【'+PhoneNumber+'】' 
from Students where Gender='男'
select 總成績=CSharp+SQLServerDB from ScoreList

注意：
1.+連接的數(shù)據(jù)類型必須兼容
2.如果使用+連接字符型數(shù)據(jù)弦追，結(jié)果為字符串?dāng)?shù)據(jù)的連接
3.如果使用+連接數(shù)值型數(shù)據(jù)岳链，結(jié)果為數(shù)值的和

查詢空列

select * from ScoreList where SQLServerDB is null

使用常量列

select StudentName as 姓名,Gender as 性別,出生日期=birthday,所在學(xué)校='北京大學(xué)'
from Students where Gender='男'

限制固定行數(shù)

select top 5 StudentName,Gender,Birthday from Students

返回百分之多少行

select top 20 percent StudentName,Gender,Birthday from Students

按多列排序

select top 3 StudentId,CSharp as C#,DB=SQLServerDB
from ScoreList  
where StudentId not in(select top 6 StudentId from ScoreList order by SQLServerDB DESC,CSharp DESC )
order by SQLServerDB DESC,CSharp DESC

模糊查詢-like

select StudentName,StudentAddress from Students where StudentAddress like '天津%'
select StudentName,StudentAddress from Students where StudentName like '%小%'

模糊查詢-between

select * from ScoreList where CSharp between 80 and 90
select StudentName,StudentAddress,Birthday from Students where Birthday between '1987-01-01' and '1988-01-01'

模糊查詢-in

select StudentName,StudentAddress,age from Students where Age in(21,22,23)
select StudentName,StudentAddress,age from Students where StudentName in('王小虎','賀小張')

查詢函數(shù)的使用

聚合函數(shù)（求和、統(tǒng)計(jì)骗卜、求最大值、最小值左胞、平均值）

select SUM(CSharp) as C#總成績 from ScoreList
select 總?cè)藬?shù)=COUNT(*) from Students
select MAX(Csharp) as C#最高分 ,MIN(CSharp) as C#最低分,AVG(CSharp) as C#平均分 from ScoreList

多表之間的數(shù)據(jù)查詢

內(nèi)連接（inner join...on...）查詢

select Students.StudentId,C#成績=CSharp,StudentName,ClassName
from ScoreList
inner join Students on Students.StudentId=ScoreList.StudentId
inner join StudentClass on Students.ClassId=StudentClass.ClassId
where CSharp >80

左外連接（left outer join...on...）

select Students.StudentId,StudentName,Gender ,C#成績=CSharp from Students
left outer join ScoreList on Students.StudentId=ScoreList.StudentId
where Gender='男'

右外連接（right outer join...on...）

分組查詢寇仓、統(tǒng)計(jì)及統(tǒng)計(jì)篩選（group by...having...）

select 班級=StudentClass.ClassName,人數(shù)=COUNT(*),C#最高分=Max(CSharp),DB最高分=MAX(SQLServerDB),
AVG(CSharp) as C#平均分,AVG(SQLServerDB) as DB平均分
from Students
inner Join StudentClass on Students.ClassId =StudentClass.ClassId
inner join ScoreList on ScoreList.StudentId=Students.StudentId
group by ClassName
having AVG(CSharp)>=70 and AVG(SQLServerDB)>=70

查詢重復(fù)

--查詢所有重復(fù)的記錄
select * from ScoreList
where StudentId in(select StudentId from ScoreList group by StudentId  having COUNT(*)>1)
order by StudentId
--其它方法
select * from ScoreList
where (select COUNT(*) from ScoreList s where s.StudentId=ScoreList.StudentId)>1
order by StudentId

分組查詢對比

• where字句：從數(shù)據(jù)源中去掉不符合其搜索條件的數(shù)據(jù)。
• group by字句：搜集數(shù)據(jù)行到各個(gè)組中烤宙，統(tǒng)計(jì)函數(shù)為各個(gè)組計(jì)算統(tǒng)計(jì)值遍烦。
• having字句：在分組結(jié)果中，去掉不符合其組搜索條件的各組數(shù)據(jù)行躺枕。