SQL语句相关概念及练习之基础篇(1)(3)

三、SQL中的内置函数

-------------------------------------- 
-----数据汇总-聚合函数--------- 
-------------------------------------- 
 
--查询T_Employee表中数据条数 
selectCOUNT(*)fromT_Employee 
 
--查询工资最高的人 
selectMAX(FSalary)asTop1fromT_Employee 
 
--查询工资最低的人 
selectMin(FSalary)asBottom1fromT_Employee 
 
--查询工资的平均水平 
selectAvg(FSalary)as平均水平fromT_Employee 
 
--所有工资的和 
selectSUM(FSalary)as总工资fromT_Employee 
 
--查询工资大于5K的员工总数 
selectCOUNT(*)astotalfromT_Employee 
whereFSalary>5000 
 
------------------------------ 
-----数据排序------- 
------------------------------ 
 
--按年龄排序升序,默认是升序 
select*fromT_Employee 
orderbyFAgeASC 
 
--多个条件排序,先什么,后什么,在前一个条件相同的情况下,根据后一个条件进行排列 
 
--where在orderby之前 
select*fromT_Employee 
orderbyFAgeASC,FSalaryDESC 
 
------------------------------ 
-----模糊匹配------- 
------------------------------ 
 
--通配符查询 
--1.单字符通配符_ 
--2.多字符通配符% 
--以DEV开头的任意个字符串 
select*fromT_Employee 
whereFNumberlike'DEV%' 
 
--以一个字符开头,om结尾的字符串 
select*fromT_Employee 
whereFNamelike'_om' 
 
--检索姓名中包含m的字符 
select*fromT_Employee 
whereFNamelike'%m%' 
 
------------------------------ 
-----空值处理------- 
------------------------------ 
--null表示不知道,不是没有值 
--null和其他值计算结果是null 
selectnull+1 
 
--查询名字是null的数据 
select*fromT_Employee 
whereFNameisnull 
 
--查询名字不为空null的数据 
select*fromT_Employee 
whereFNameisnotnull 
 
--年龄是23,25,28中的员工 
select*fromT_Employee 
whereFAge=23orFAge=25orFAge=28 
 
--或者用in集合查询 
--年龄是23,25,28中的员工 
select*fromT_Employee 
whereFAgein(23,25,28) 
 
--年龄在20到25之间的员工信息 
select*fromT_Employee 
whereFAge>20andFAge<25 
 
--年龄在20到25之间的员工信息,包含25 
select*fromT_Employee 
whereFAgebetween20and25 
 
------------------------------ 
-----数据分组------- 
------------------------------ 
SelectFAge,COUNT(*)fromT_Employee 
groupbyFAge 
 
--1.根据年龄进行分组 
--2.再取出分组后的年龄的个数 
--注意:没有出现在groupby子句中的字段,不能出现在select语句后的列名列表中(聚合函数除外) 
--groupby必须出现在where后面 
SelectFAge,AVG(FSalary),COUNT(*)fromT_Employee 
groupbyFAge 
 
--错误用法 
SelectFAge,FName,COUNT(*)fromT_Employee 
groupbyFAge 
 
--加上where的groupby子句 
--groupby必须出现在where后面 
SelectFAge,AVG(FSalary),COUNT(*)fromT_Employee 
whereFAge>=25 
groupbyFAge 
 
--Having不能包含查不到的字段,只能包含聚合函数和本次查询有关的字段 
selectFAge,COUNT(*)fromT_Employee 
groupbyFAge 
HavingCOUNT(*)>1 
selectFAge,COUNT(*)fromT_Employee 
whereFSalary>2500 
groupbyFAge 
 
--HAVING子句中的列'T_Employee.FSalary'无效，因为该列没有包含在聚合函数或GROUPBY子句中 
--Having是对分组后信息的过滤，能用的列和select中能有的列是一样的。 
--因此，having不能代替where 
selectFAge,COUNT(*)fromT_Employee 
groupbyFAge 
HavingFSalary>2500 
 
------------------------------ 
-----确定结果集行数------- 
------------------------------ 
--取出所有员工的信息,根据工资降序排列 
select*fromT_Employee 
orderbyFSalaryDESC 
 
--取出前三名员工的信息,根据工资降序排列 
selecttop3*fromT_Employee 
orderbyFSalaryDESC 
 
--根据工资取出排名在6-8的员工信息,按工资降排列 
selecttop3*fromT_Employee 
whereFNumbernotin 
(selecttop5FNumberfromT_EmployeeorderbyFSalaryDESC) 
orderbyFSalaryDESC 
 
---修改数据表，添加字段，更新字段的值等操作。 
altertableT_EmployeeaddFSubCompanyvarchar(20) 
altertableT_EmployeeaddFDepartmentvarchar(20) 
updateT_EmployeesetFSubCompany='Beijing',FDepartment='Development' 
whereFNumber='DEV001'; 
updateT_EmployeesetFSubCompany='ShenZhen',FDepartment='Development' 
whereFNumber='DEV002'; 
updateT_EmployeesetFSubCompany='Beijing',FDepartment='HumanResource' 
whereFNumber='HR001'; 
updateT_EmployeesetFSubCompany='Beijing',FDepartment='HumanResource' 
whereFNumber='HR002'; 
updateT_EmployeesetFSubCompany='Beijing',FDepartment='InfoTech' 
whereFNumber='IT001'; 
updateT_EmployeesetFSubCompany='ShenZhen',FDepartment='InfoTech' 
whereFNumber='IT002' 
updateT_EmployeesetFSubCompany='Beijing',FDepartment='Sales' 
whereFNumber='SALES001'; 
updateT_EmployeesetFSubCompany='Beijing',FDepartment='Sales' 
whereFNumber='SALES002'; 
updateT_EmployeesetFSubCompany='ShenZhen',FDepartment='Sales' 
whereFNumber='SALES003'; 
select*fromT_Employee 
 
------------------------------ 
------去掉重复数据------ 
------------------------------ 
--所有员工的部门信息 
selectDistinctFDepartmentfromT_Employee; 
selectFDepartment,FSubCompany 
fromT_Employee 
--以上两个例子结合起来比较，Distinct针对的是整行进行比较的 
selectDistinctFDepartment,FSubCompany 
fromT_Employee 
 
----------------------------- 
-----联合结果集Union-------- 
------------------------------ 
--创建一个测试表T_TempEmployee，并插入数据 
CreateTableT_TempEmployee(FIdCardNumbervarchar(20),FNamevarchar(20),FAgeint,Primarykey(FIdCardNumber)); 
insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890121','Sarani',33); 
insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890122','Tom',26); 
insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890123','Yamaha',38); 
insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890124','Tina',36); 
insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890125','Konkaya',29); 
insertintoT_TempEmployee(FIdCardNumber,FName,FAge)values('1234567890126','Foortia',29); 
select*fromT_TempEmployee 
 
--Union关键字，联合2个结果 
--把2个查询结果结合为1个查询结果 
--要求:上下2个查询语句的字段(个数,名字,类型相容)必须一致 
selectFName,FagefromT_TempEmployee 
union 
selectFName,FagefromT_Employee 
selectFNumber,FName,Fage,FDepartmentfromT_Employee 
union 
selectFIdCardNumber,FName,Fage,'临时工，无部门'fromT_TempEmployee 
 
---UnionAll:不合并重复数据 
--Union：合并重复数据 
selectFName,FAgefromT_Employee 
unionall 
selectFName,FAgefromT_TempEmployee 
selectFAgefromT_Employee 
union 
selectFAgefromT_TempEmployee 
 
--注意:Union因为要进行重复值扫描,所以效率低,因此如果不是确定要合并重复,那么就用Unionall 
--例子:报名 
select'正式员工最高年龄',MAX(FAge)fromT_Employee 
unionall 
select'正式员工最低年龄',MIN(FAge)fromT_Employee 
unionall 
select'临时工最高年龄',MAX(FAge)fromT_TempEmployee 
unionall 
select'临时工最低年龄',MIN(FAge)fromT_TempEmployee 
 
--查询每位正式员工的信息,包括工号,工资,并且在最后一行加上员工工资额合计 
selectFNumber,FSalaryfromT_Employee 
unionall 
select'工资额合计',SUM(FSalary)fromT_Employee