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