SQL语句2

数据分组

按照年龄进行分组统计各个年龄段的人数
select age,count(*) from employee group by age
group by子句必须放到where语句之后
没有出现在group by子句中的列是不能放到select语句后的列名列表中的(聚合函数中除外)
错误:select age,salary from employee group by age
正确:select age,AVG(salary) from employee group by age

Having语句

在where中不能使用聚合函数,必须使用Having,Having要位于group by之后
select age,count(*) as 人数 from employee
group by age
having count(*)>1
注意having中不能使用未参数分组的列
having不能替代where,作用不一样,having是对分组后信息的过滤 能用的列和select能用的列一样 

限制结果集行数

select top 5 * from employee order by salary Desc
检索按照工资从高到低排序检索从第六名开始一共三个人的信息
select top 3 * from employee where number not in(select top 5 number from employee order by salary Desc)order by salary Desc
SQLServer2005后增加了Row_Number函数简化实现

去掉数据重复

select distinct department from employee
distinct是对整个结果集进行重复处理的,而不是针对每一个列
select distinct department,subcompany from employee 不会对department去重复 2个列都重复才去除

联合结果集

简单的结果集联合
select number,name,age from employee union select idcardnumber,name,age from tempemployee
基本的原则 每个结果集必须有相同的列数&每个结果集必须类型相容
select number,name,age,department from employee union select idcardnumber,name,age,'临时工，无部门'from tempemployee
select name,age from employee union select name,age from tempemployee
union合并2个查询结果集并将其中完全重复的数据行合并为一条
select name,age from employee union all select name,age from tempemployee
union因为要进行重复值扫描所以效率低,因此如果不需要合并重复行用union all

查询员工最低年龄和最高年龄区分临时工和正式员工

select '正式员工最高年龄',MAX(age) from employee
union all
select '正式员工最低年龄',MIN(age) from employee
union all
select '临时员工最高年龄',MAX(age) from tempemployee
union all
select '临时员工最高年龄',MIN(age) from tempemployee

查询每位员工的信息(工号工资)并在最后一行加上所有员工的工资合计

select number,salary from employee
union

select '工资合计',SUM(salary) from employee

函数

数字函数

ABS()
ceiling()天花板 向上取变大 -3.6-->-3
floor()地板 向下去变小 -3.4-->-4
round()半径 四舍五入 round(3.1415926,3)-->(3.14200000)
len()计算字符串长度
lower()upper()转小写大写
ltrim()rtrim()去左侧右侧空格
substring(string,start_position,length)
string主字符串start_position起始位置length最大长度
substring('abcdef111',2,3)

日期函数

getdate()取得当前日期时间
dateadd(datepart,number,date)计算增加以后的日期 date(month,-8,date)
datediff(datepart,startdate,enddate)计算两个日期的差额
datepart(datepart,date)返回一个日期的特定部分
统计不同工龄的员工的个数
select datediff(year,indate,getdate()),count(*) from employee
group by datediff(year,indate,getdate())
统计员工的入职年份个数
select datepart(year,indate),count(*)
from employee
group by datepart(year,indate)

类型转换函数

cast(express as data_type)
convert(data_type,expression)
select idnumber,
right(idnumber,3) as 后三位,
cast(right(idnumber,3) as integer) as 后三位的整数形式，
cast(right(idnumber,3) as integer)+1 as 后三位的整数形式，
convert(integer,right(idnumber,3))/2 as 后三位除以2

from person

控制处理函数

isnull(expression,value):如果expression不为空则返回value

select isnull(name,'佚名') as 姓名 from employee

case函数

单值判断,相当于switch case

case expression

when value1 then returnvalue1

when value2 then returnvalue2

when value3 then returnvalue3

else default returnvalue

end

例子

select name,

(case level when 1 then '普通客户'

when 2 then '会员'

when 3 then 'VIP'

else '客户类型错误'

end) as 客户类型

from customer

条件判断

value值可为condition

相当于if...else...else...