SQLServer基本查询

条件查询

--1.比较运算符

select * from dbo.DepartMent where dID>=3

--2.确定集合谓词

select * from dbo.DepartMent where dID between 2 and 4

--3.确定范围谓词

select * from dbo.DepartMent where dID in( 2 , 4)

--4.字符匹配谓词

select * from dbo.DepartMent where dName like 'C%'

select * from dbo.DepartMent where dName not like '_#%'

--5.空值谓词

select * from dbo.DepartMent where dName IS null

select * from dbo.DepartMent where dName IS not null

--6.多重条件运算符

select * from dbo.DepartMent where dID =  2 or dID = 4

select * from dbo.DepartMent where dID =  2 and dID = 4

聚集函数介绍

--1.avg()

select avg(dID),dName from DepartMent group by dName

--2.count()

select count(dID),dName from DepartMent group by dName

--3.max()

select count(dID),dName from DepartMent group by dName

--4.min()

select min(dID),dName from DepartMent group by dName

--5.sum()

select sum(dID),dName from DepartMent group by dName

带top和distinct关键字的查询

Select  top 3 * from DepartMent

Select  distinct dName from DepartMent

附加子句查询

--Order by 字句

select * from dbo.DepartMent

Order by dName desc

--Group by 字句

select dName from dbo.DepartMent

Group by dName

--Having 字句

select COUNT(dID),dName from dbo.DepartMent

Group by dName

Having COUNT(dID)>2

--Into 字句

select COUNT(dID) count,dName into #TmpTable

from dbo.DepartMent

Group by dName

Having COUNT(dID)>2

--临时表(本次链接才有效)和基本表的区别

select * from  #TmpTable