单表操作
select Name,Major,InDate from T_Employeeselect 12*12select 12*12 as 计算结果select Name as 姓名,Major,InDate from T_Employeeselect * from T_Employeeselect 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 descselect 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,DepartmentIdhaving 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,DepartmentIdhaving 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_Employeewhere InDate between '2014-03-30 18:19:14.503' and '2014-04-09 00:00:00.000'select * from T_Employeewhere BaseSalary%10=0select * from T_Employeewhere Name in ('个','个地方','必须','古典风格')select * from T_Employeewhere Name not in ('个','个地方','必须','古典风格')select * from T_Employee