Transact-SQL函数

一、时间日期函数

unit：YEAR/MONTH/DAY 增加日期类型 年/月/日

n：增加的数字

date：对应的日期

part：YEAR/MONTH/DAY 日期类型

1、DATEADD(unit,n,date) 在date的基础上添加n后的日期

2、DATEDIFF(unit,date,GETDATE()) 以unit为单位计算日期之间的差值

3、DATENAME(part,date) 返回指定日期的指定部分 返回字符串

4、DATEPART(part,date) 返回指定日期的指定部分 返回整数

二、转换函数

CAST(表达式 AS 类型)

CONVERT(varchar(100), GETDATE(), 102): 2006.05.16

CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49

CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47

CONVERT(varchar(100), GETDATE(), 23): 2006-05-16

CONVERT(varchar(100), GETDATE(), 24): 10:57:47

101表示mm/dd/yy日期格式

isnull(字段,0)//字段为Null赋值0

三、CASE语句 判断所有字段并赋值

select city,

case city

when 'Boston' then '美国'

when 'Washington' then '纽约'

end as '国家'

from [pubs].[dbo].[publishers]

四、生成新表 INTO

INTO子句创建新表 字段根据查询的数据生成

select * into st_score from st_student

查询学生表 生成一个新表score

五、联合查询UNION

select * from [pubs].[dbo].[sales] where YEAR(ord_date) = 1993 or MONTH(ord_date) = 05

相当于

select * from [pubs].[dbo].[sales] where YEAR(ord_date) = 1993

union

select * from [pubs].[dbo].[sales] where MONTH(ord_date) = 05

union与union all 后者检索有重复数据

六、数据关键字

1、UNIQUE 创建表时创建唯一标识，等同于主键的效果，可以有多个

2、FOREIGN KEY外键约束，防止非法数据插入外键列

3、CHECK 约束

3.1、创建单个字段的约束

Id_P int NOT NULL CHECK (Id_P>0),

3.2、创建多个字段的约束

CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')

3.3、修改字段的约束

ALTER TABLE Persons ADD CHECK (Id_P>0) 单个字段

ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes') 多个字段

3.4、撤销 CHECK 约束

ALTER TABLE Persons DROP CONSTRAINT chk_Person

4、DEFAULT 约束用于向列中插入默认值

5、修改表自增值

ALTER TABLE Persons AUTO_INCREMENT=100

6、MID 函数用于从文本字段中提取字符

SELECT MID(City,1,3) as SmallCity FROM Persons

7、ROUND 函数用于把数值字段舍入为指定的小数位数

ROUND(column_name,decimals)要舍入的字段/规定要返回的小数位数

8、FORMAT 函数用于对字段的显示进行格式化

FORMAT(Now(),'YYYY-MM-DD')

9、COMPUTE对结果集 进行进一步操作 位于查询语句后面

AVG/COUNT/MAX/MIN/SUM聚合函数，生成结果作为附加的汇总出现在结果集的最后

有compute by时必须有order by，将结果集排序，然后输出聚合函数值

select * from 表名 order by 字段B compute max(字段A) by 字段B

10、UNION 拼接结果集

11、GROUP BY、HAVING联用 用来处理“课程多于2门 ”这类问题

group by 课程 having sum(*)>2作为条件

12、EXISTS是否存在查询，匹配到第一条数据就结束，返回ture

select * from 表名 exists (查询语句)

exists （sql 返回结果集为真） 

not exists (sql 不返回结果集为真）

下面详细描述not exists的过程：

如下：

表A

ID NAME 

1   A1

2   A2

3   A3

表B

ID AID NAME

1   1     B1

2   2     B2 

3   2     B3

表A和表B是１对多的关系 A.ID => B.AID

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)

执行结果为

1 A1

2 A2

原因可以按照如下分析

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)

--->SELECT * FROM B WHERE B.AID=１有值返回真所以有数据

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)

--->SELECT * FROM B WHERE B.AID=２有值返回真所以有数据

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)

--->SELECT * FROM B WHERE B.AID=３无值返回真所以没有数据

NOT EXISTS 就是反过来

SELECT ID,NAME FROM A WHERE　NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)

执行结果为

3 A3