单表操作
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