select的使用（一）

 

单表操作

select Name,Major,InDate from T_Employee

select 12*12

select 12*12 as 计算结果

select Name as 姓名,Major,InDate 
from T_Employee

select * from T_Employee

select distinct Nationality 
from T_Employee--消除重复列

select * from T_Employee 
where GenderId='403A0BD5-A304-4981-9535-ADA7AF2BFB51'

select * from T_Employee 
where GenderId='403A0BD5-A304-4981-9535-ADA7AF2BFB51'
and DepartmentId='0DA51809-7C40-493E-8AB5-CF18EAA279DF'

select * from T_Employee 
where GenderId='403A0BD5-A304-4981-9535-ADA7AF2BFB51'
or DepartmentId='0DA51809-7C40-493E-8AB5-CF18EAA279DF'
and BirthDay like '1990%'

/*排序*/
select * from T_Employee 
where GenderId='403A0BD5-A304-4981-9535-ADA7AF2BFB51'
or DepartmentId='0DA51809-7C40-493E-8AB5-CF18EAA279DF'
and BirthDay like '1990%'
order by BirthDay asc--默认是升序排序

select * from T_Employee 
where GenderId='403A0BD5-A304-4981-9535-ADA7AF2BFB51'
or DepartmentId='0DA51809-7C40-493E-8AB5-CF18EAA279DF'
and BirthDay like '1990%'
order by BirthDay desc--降序排序

select * from T_Employee 
where GenderId='403A0BD5-A304-4981-9535-ADA7AF2BFB51'
or DepartmentId='0DA51809-7C40-493E-8AB5-CF18EAA279DF'
and BirthDay like '1990%'
order by BirthDay asc,ContractStartDay--默认是升序排序

/*分组*/
select DepartmentId ,AVG(BaseSalary )
from T_Employee 
group by DepartmentId--平均的数必须是可平均的数,要select出作为分组的依据的列

select DepartmentId ,AVG(BaseSalary )
from T_Employee 
group by rollup( DepartmentId)--对所有部门又进行平均值

select  EducationId,DepartmentId ,AVG(BaseSalary )
from T_Employee 
group by rollup(EducationId, DepartmentId)--先对教育状况来分组平均值,再对总的结果平均值

select  EducationId,DepartmentId ,AVG(BaseSalary )
from T_Employee 
group by cube(EducationId, DepartmentId)--先对部门来分组平均值,再对总的结果平均值,再对教育状况再平均值
 

select MAX(indate)
from T_Employee 
where DepartmentId='ADE0F75A-5581-46C2-B3A5-F92B1D1E603B'

select MIN(indate) 
from T_Employee 
where DepartmentId='ADE0F75A-5581-46C2-B3A5-F92B1D1E603B'

select avg(BaseSalary) 
from T_Employee 
where DepartmentId='ADE0F75A-5581-46C2-B3A5-F92B1D1E603B'

select sum(BaseSalary) 
from T_Employee 
where DepartmentId='ADE0F75A-5581-46C2-B3A5-F92B1D1E603B'

select top 3 Name 
from T_Employee 
where DepartmentId='ADE0F75A-5581-46C2-B3A5-F92B1D1E603B'
order by InDate desc

select top 3 percent Name 
from T_Employee 
where DepartmentId='ADE0F75A-5581-46C2-B3A5-F92B1D1E603B'
order by InDate desc

/*having字句*/
select  GenderId,DepartmentId,MAX(indate)--select的字段必须出现在group by里或者为聚合函数
from T_Employee 
group by GenderId,DepartmentId
having GenderId='34E1FD3A-EA46-4B80-9612-4014345C4CD2'--筛选条件必须从select 里选
order by DepartmentId--,order by 的字段也是出现在group by 里

select  GenderId,DepartmentId,MAX(indate)--select的字段必须出现在group by里或者为聚合函数
from T_Employee 
group by GenderId,DepartmentId
having MAX(indate)>='2014-03-31 18:28:36.427'--筛选条件必须从select 里选
order by DepartmentId--,order by 的字段也是出现在group by 里

/*compute子句*/
select *
from T_Employee 
where DepartmentId='ADE0F75A-5581-46C2-B3A5-F92B1D1E603B'
compute MAX(indate),min(indate),sum(basesalary),avg(basesalary)


select *
from T_Employee 
where DepartmentId='ADE0F75A-5581-46C2-B3A5-F92B1D1E603B'
order by InDate 
compute MAX(indate),min(indate),sum(basesalary),avg(basesalary)by indate--和order by 排序对应

/*where子句*/
select * from T_Employee
where InDate between '2014-03-30 18:19:14.503' and '2014-04-09 00:00:00.000'

select * from T_Employee
where BaseSalary%10=0


select * from T_Employee
where Name in ('个','个地方','必须','古典风格')


select * from T_Employee
where Name not in ('个','个地方','必须','古典风格')

select * from T_Employee