SQL中的内置函数

来源：互联网发布：便宜的淘宝cosplay店铺编辑：程序博客网时间：2024/04/30 09:42

--------------------------------------

----- 数据汇总-聚合函数 ---------

--------------------------------------

--查询T_Employee表中数据条数

select COUNT(*) from T_Employee

--查询工资最高的人

select MAX(FSalary) as Top1 from T_Employee

--查询工资最低的人

select Min(FSalary) as Bottom1 from T_Employee

--查询工资的平均水平

select Avg(FSalary) as 平均水平 from T_Employee

--所有工资的和

select SUM(FSalary) as 总工资 from T_Employee

--查询工资大于5K的员工总数

select COUNT(*) as total from T_Employee

where FSalary>5000

------------------------------

----- 数据排序 -------

------------------------------

--按年龄排序升序,默认是升序

select * from T_Employee

order by FAge ASC

--多个条件排序,先什么,后什么,在前一个条件相同的情况下,根据后一个条件进行排列

--where在order by之前

select * from T_Employee

order by FAge ASC, FSalary DESC

------------------------------

----- 模糊匹配 -------

------------------------------

--通配符查询

--1.单字符通配符_

--2.多字符通配符%

--以DEV开头的任意个字符串

select * from T_Employee

where FNumber like 'DEV%'

--以一个字符开头,om结尾的字符串

select * from T_Employee

where FName like '_om'

--检索姓名中包含m的字符

select * from T_Employee

where FName like '%m%'

------------------------------

----- 空值处理 -------

------------------------------

--null表示不知道,不是没有值

--null和其他值计算结果是null

select null+1

--查询名字是null的数据

select * from T_Employee

where FName is null

--查询名字不为空null的数据

select * from T_Employee

where FName is not null

--年龄是23,25,28中的员工

select * from T_Employee

where FAge=23 or FAge=25 or FAge=28

--或者用in 集合查询

--年龄是23,25,28中的员工

select * from T_Employee

where FAge in (23,25,28)

--年龄在20到25之间的员工信息

select * from T_Employee

where FAge>20 and FAge<25

--年龄在20到25之间的员工信息,包含25

select * from T_Employee

where FAge between 20 and 25

------------------------------

----- 数据分组 -------

------------------------------

Select FAge,COUNT(*) from T_Employee

group by FAge

--1.根据年龄进行分组

--2.再取出分组后的年龄的个数

--注意:没有出现在group by 子句中的字段,不能出现在select语句后的列名列表中(聚合函数除外)

--group by 必须出现在where后面

Select FAge,AVG(FSalary),COUNT(*) from T_Employee

group by FAge

--错误用法

Select FAge,FName,COUNT(*) from T_Employee

group by FAge

--加上where的group by 子句

--group by 必须出现在where后面

Select FAge,AVG(FSalary),COUNT(*) from T_Employee

where FAge>=25

group by FAge

--Having不能包含查不到的字段,只能包含聚合函数和本次查询有关的字段

select FAge,COUNT(*) from T_Employee

group by FAge

Having COUNT(*)>1

select FAge,COUNT(*) from T_Employee

where FSalary>2500

group by FAge

--HAVING 子句中的列 'T_Employee.FSalary' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中

--Having是对分组后信息的过滤，能用的列和select中能有的列是一样的。

--因此，having不能代替where

select FAge,COUNT(*) from T_Employee

group by FAge

Having FSalary>2500

------------------------------

----- 确定结果集行数 -------

------------------------------

--取出所有员工的信息,根据工资降序排列

select * from T_Employee

order by FSalary DESC

--取出前三名员工的信息,根据工资降序排列

select top 3 * from T_Employee

order by FSalary DESC

--根据工资取出排名在6-8的员工信息,按工资降排列

select top 3 * from T_Employee

where FNumber not in

(select top 5 FNumber from T_Employee order by FSalary DESC)

order by FSalary DESC

---修改数据表，添加字段，更新字段的值等操作。

alter table T_Employee add FSubCompany varchar(20)

alter table T_Employee add FDepartment varchar(20)

update T_Employee set FSubCompany='Beijing',FDepartment='Development'

where FNumber='DEV001';

update T_Employee set FSubCompany='ShenZhen',FDepartment='Development'

where FNumber='DEV002';

update T_Employee set FSubCompany='Beijing',FDepartment='HumanResource'

where FNumber='HR001';

update T_Employee set FSubCompany='Beijing',FDepartment='HumanResource'

where FNumber='HR002';

update T_Employee set FSubCompany='Beijing',FDepartment='InfoTech'

where FNumber='IT001';

update T_Employee set FSubCompany='ShenZhen',FDepartment='InfoTech'

where FNumber='IT002'

update T_Employee set FSubCompany='Beijing',FDepartment='Sales'

where FNumber='SALES001';

update T_Employee set FSubCompany='Beijing',FDepartment='Sales'

where FNumber='SALES002';

update T_Employee set FSubCompany='ShenZhen',FDepartment='Sales'

where FNumber='SALES003';

select * from T_Employee

------------------------------

------ 去掉重复数据 ------

------------------------------

--所有员工的部门信息

select Distinct FDepartment from T_Employee;

select FDepartment,FSubCompany

from T_Employee

--以上两个例子结合起来比较，Distinct针对的是整行进行比较的

select Distinct FDepartment,FSubCompany

from T_Employee

------------------------------

----- 联合结果集Union --------

------------------------------

--创建一个测试表T_TempEmployee，并插入数据

Create Table T_TempEmployee(FIdCardNumber varchar(20),FName varchar(20),FAge int,Primarykey(FIdCardNumber));

insert into T_TempEmployee(FIdCardNumber,FName,FAge) values('1234567890121','Sarani',33);

insert into T_TempEmployee(FIdCardNumber,FName,FAge) values('1234567890122','Tom',26);

insert into T_TempEmployee(FIdCardNumber,FName,FAge) values('1234567890123','Yamaha',38);

insert into T_TempEmployee(FIdCardNumber,FName,FAge) values('1234567890124','Tina',36);

insert into T_TempEmployee(FIdCardNumber,FName,FAge) values('1234567890125','Konkaya',29);

insert into T_TempEmployee(FIdCardNumber,FName,FAge) values('1234567890126','Foortia',29);

select * from T_TempEmployee

--Union关键字，联合2个结果

--把2个查询结果结合为1个查询结果

--要求:上下2个查询语句的字段(个数,名字,类型相容)必须一致

select FName,Fage from T_TempEmployee

union

select FName,Fage from T_Employee

select FNumber, FName,Fage,FDepartment from T_Employee

union

select FIdCardNumber,FName,Fage,'临时工，无部门' from T_TempEmployee

---Union All:不合并重复数据

--Union：合并重复数据

select FName,FAge from T_Employee

union all

select FName,FAge from T_TempEmployee

select FAge from T_Employee

union

select FAge from T_TempEmployee

--注意:Union因为要进行重复值扫描,所以效率低,因此如果不是确定要合并重复,那么就用Union all

--例子:报名

select '正式员工最高年龄',MAX(FAge) from T_Employee

union all

select '正式员工最低年龄',MIN(FAge) from T_Employee

union all

select '临时工最高年龄',MAX(FAge) from T_TempEmployee

union all

select '临时工最低年龄',MIN(FAge) from T_TempEmployee

--查询每位正式员工的信息,包括工号,工资,并且在最后一行加上员工工资额合计

select FNumber,FSalary from T_Employee

union all

select '工资额合计',SUM(FSalary) from T_Employee

------------------------------

----- SQL其他内置函数 ------

------------------------------

--1.数学函数

--ABS():求绝对值

--CEILING():舍入到最大整数

--FLOOR():舍入到最小整数

--ROUND():四舍五入

select ABS(-3)

select CEILING(3.33)

select CEILING(-3.61)

select FLOOR(2.98)

select FLOOR(-3.61)

select ROUND(-3.61,1)--第二个参数是精度，小数点后的位数

select ROUND(-3.61,0)

select ROUND(3.1415926,3)

--2.字符串函数

--LEN():计算字符串长度

--LOWER(),UPPER():转大小写

--LTRIM():去掉字符串左侧的空格

--RTRIM():去掉字符串右侧的空格

--SUBSTRING(string,start_positoin,length):

--索引从1开始

select SUBSTRING('abc111',2,3)--结果是bc1

select FName, SUBSTRING(FName,2,2) from T_Employee

select LEN('abc') --结果是3

select FName, LEN(FName) from T_Employee

--没有可以同时既去掉左边空格、又去掉右边空格的TRIM()内置函数，所以先左后右的进行TRim，当然，你也可以先右后左

select LTRIM(' abc '),RTRIM(' abc '),LEN(LTRIM(RTRIM(' abc ')))

--3.日期函数

--GETDATE():获取当前日期时间

--DATEADD(datepart,numbre,date):计算增加以后的日期，

--参数date为待计算的日期；参数number为增量；参数datepart为计量单位,时间间隔单位；

--DATEDIFF(datepart,startdate,enddate):计算2个日期之间的差额

--DATEPART(datepart,date):返回一个日期的特定部分,比如年月日,时分秒等.

值缩写（Sql Server） (Access 和 ASP) 说明

Year Yy yyyy 年 1753 ~ 9999

Quarter Qq q 季 1 ~ 4

Month Mm m 月 1 ~ 12

Day of year Dy y 一年的日数,一年中的第几日 1-366

Day Dd d 日， 1-31

Weekday Dw w 一周的日数，一周中的第几日 1-7

Week Wk ww 周，一年中的第几周 0 ~ 51

Hour Hh h 时0 ~ 23

Minute Mi n 分钟0 ~ 59

Second Ss s 秒 0 ~ 59

Millisecond Ms - 毫秒 0 ~ 999

select DATEADD(DAY,3,getdate())

select DATEADD(MONTH,-3,getdate())

select DATEADD(HOUR,8,getdate())

select DATEDIFF(YEAR,'1989-05-01',GETDATE())

select DATEDIFF(HH,GETDATE(),DATEADD(DAY,-3,GETDATE()))

--查询员工的工龄,年为单位

select FName,FInDate,DATEDIFF(year,FInDate,getdate()) as 工龄 from T_Employee

--取出每一年入职员工的个数V1

select DATEDIFF(year,FInDate,getdate()),COUNT(*)

from T_Employee

group by DATEDIFF(year,FInDate,getdate())

--取出每一年入职员工的个数V2

select DATEPART(YEAR,FInDate), COUNT(*)

from T_Employee

group by DATEPART(YEAR,FInDate)

select DATEPART(YEAR,GETDATE())

select DATEPART(MONTH,GETDATE())

select DATEPART(DAY,GETDATE())

select DATEPART(HH,GETDATE())

select DATEPART(MINUTE,GETDATE())

select DATEPART(SECOND,GETDATE())

--4.类型转换函数

--CAST(expression as data_type)

--CONVERT(data_type,expression)

select CAST('123' as int),CAST('2010-09-08' as datetime),

CONVERT(datetime,'2010-09-08'),CONVERT(varchar(20),123)

--5.空值处理函数isNull

--ISNULL(expression,value)

select ISNULL(FName,'佚名') as 姓名 from T_Employee

--6.CASE函数用法：

--1.单值判断：相当于switch.case

--CASE expression

--WHEN value1 then returnvalue1

--WHEN value2 then returnvalue2

--WHEN value3 then returnvalue3

--ELSE default_return_value

--END

--判断客户类型

select FName,

(

case FLevel

when 1 then '普通客户'

when 2 then '会员'

when 3 then 'VIP'

else '未知客户类型'

End

) as 客户类型

from T_Customer

--收入水平查询

select FName,

(

case

when FSalary < 2000 then '低收入'

when FSalary >= 2000 and FSalary <=5000 then '中等收入'

else '高收入'

end

)as 收入水平

from T_Employee

--这里有一道关于CASE用法的面试题

--表T中有ABC三列，用SQL语句实现:当A列大于B列时选择A列，否则选择B列；

--当B列大于C列时选择B列，否则选择C列。

select

(

case

when a > b then a else b

end

(

case

when b>c then b else c

end

)

from T

---------------------------------------

select FNumber,

(

case

when FAmount>0 then FAmount

else 0

end

) as 收入,

(

case

when FAmount<0 then ABS(FAmount)

else 0

end

) as 支出

from T

-----------------------------------------

--球队比赛那个题

--有一张表T_Scroes，记录比赛成绩：

--Date Name Scroe

--2008-8-8 拜仁胜

--2008-8-9 奇才胜

--2008-8-8 湖人胜

--2008-8-10 拜仁负

--2008-8-8 拜仁负

--2008-8-12 奇才胜

--要求输出下面格式：

--Name 胜负

--拜仁 1 2

--湖人 1 0

--奇才 2 0

--注意：在中文字符串前加 N，比如 N'胜'

create table T_Scores(

[Date] datetime null collate

[Name] nvarchar(50)

)

CREATE TABLE [T_Scores]( [Date] [datetime] NULL,

[Name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

[Score] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL

);

INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF200000000 AS DateTime),N'拜仁', N'胜');

INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF300000000 AS DateTime),N'奇才', N'胜');

INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF300000000 AS DateTime),N'湖人', N'胜');

INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF400000000 AS DateTime),N'拜仁', N'负');

INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF200000000 AS DateTime),N'拜仁', N'负');

INSERT [T_Scores] ([Date], [Name], [Score]) VALUES (CAST(0x00009AF600000000 AS DateTime),N'奇才', N'胜');

select * from T_Scores

--列出第一个表格

--统计每支队伍的胜负情况

select Name,

(

case Score

when N'胜' then 1

else 0

end

) as 胜,

(

case Score

when N'负' then 1

else 0

end

) as 负

from T_Scores

select Name,

sum

(

case Score

when N'胜' then 1

else 0

end

) as 胜,

sum

(

case Score

when N'负' then 1

else 0

end

) as 负

from T_Scores

group by Name

--根据每个队的胜负判断出胜负的场数

--题5) 创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号，对方号码，通话开始时间，通话结束时间，。

--创建一张表T_Callers，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。

--要求：

-- 1) 输出所有数据中通话时间最长的5条记录。

-- 2) 输出所有数据中拨打长途号码（对方号码以0开头）的总时长。

-- 3) 输出本月通话总时长最多的前三个呼叫员的编号。

-- 4) 输出本月拨打电话次数最多的前三个呼叫员的编号。

-- 5) 输出所有数据的拨号流水，并且在最后一行添加总呼叫时长。

-- 记录呼叫员编号、对方号码、通话时长

-- ......

-- 汇总[市内号码总时长][长途号码总时长]

--Id CallerNumber TellNumber StartDateTimeEndDateTime

--1 001 02088888888 2010-7-10 10:01 2010-7-1010:05

--2 001 02088888888 2010-7-11 13:41 2010-7-1113:52

--3 001 89898989 2010-7-11 14:42 2010-7-11 14:49

--4 002 02188368981 2010-7-13 21:04 2010-7-1321:18

--5 002 76767676 2010-6-29 20:15 2010-6-29 20:30

--6 001 02288878243 2010-7-15 13:40 2010-7-1513:56

--7 003 67254686 2010-7-13 11:06 2010-7-13 11:19

--8 003 86231445 2010-6-19 19:19 2010-6-19 19:25

--9 001 87422368 2010-6-19 19:25 2010-6-19 19:36

--10 004 40045862245 2010-6-19 19:50 2010-6-1919:59

-- 创建表

create table T_CallRecords(

id int not null,

CallerNumber varchar(3),

TellNumber varchar(13),

StartDateTIme datetime,

EndDateTime datetime,

Primary key(Id)

);

--插入数据

insert into T_CallRecords(Id,CallerNumber,TellNumber,StartDateTime,EndDateTIme)

values(1,'001','02088888888','2010-7-1010:01','2010-7-10 10:05');