【SqlServer】SqlServer的常规操作, 函数

SELECT INTO FROM 语句

创建一张新表，不负责任何数据（该表不会有原来表的主键、索引等等）

select * into NewTable from OldTable where 1<>1;

 

创建一张新表，并且复制旧表的数据（不会复制原来表的主键，索引等等）

select * into tablenew from tableold

 也可以指定复制那些字段：

SELECT vale1, value2 into Table2 from Table1

 

INSERT INTO SELECT语句

Insert into Table2(field1,field2,...) select value1,value2,... from Table1

这种查询后直接转表的形式，比先用select将数据查询出来后再一条条的插入到新表中效率要高。

 

在插入数据之前先判断是否已经有相同的数据，若有则不添加，若无则添加

if not exists(select * from Table1 where orderId=@orderid) insert into Table1(info,orderId)values(@info,@orderid);

上面的语句就是先判断Table1中是否已经有@orderid的订单，如果没有则加入该订单的信息，若有则不做任何操作。

上面的过程是如果是没有订单就加入，下面语句对上面的语句进行了扩充，如果有订单的话，则更新。

if not exists(select * from Table1 where orderId=@orderid)
    insert into Table1(info,orderId)values(@info,@orderid)
else
    update Table1 set info=@info  where orderId=@orderid;

 

SqlServer撤销所有表

EXEC sp_MSforeachtable 'DROP TABLE ?'

 

sys.sp_addextendedproperty 添加描述信息

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{描述信息}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{表名称}', @level2type=N'COLUMN',@level2name=N'{字段名称}'

 

OUTPUT(OUTPUT INTO) 输出新插入的，删除前的，修改前的 数据

-- TestTable的Id为自动生成的值

-- 输出新插入数据的值
Insert Into TestTable(columnA, columnB)
Output inserted.Id
Values('valueA','valueB')
-- 输出新插入数据的值 到 变量中
DECLARE @table table (id nvarchar(200));
insert into BusinessTable_Expense(bg,application_number)
output inserted.Id into @table
values('test2','test100')
select * from @table;

-- 获取update覆盖了的数据
Update TestTable Set columnA = ''
Output deleted.ColumnA
Where Id = @Id;
-- 输出update覆盖的数据 到 变量中
DECLARE @table (@ColumnA nvarchar(200)) ;
Update TestTable Set columnA = ''
Output deleted.ColumnA into @table
Where Id = @Id;

-- 获取删除了的数据值
Delete TestTable 
Output  deleted.*
Where Id = @Id;
-- 输出删除了的数据值 到变量中
DECLARE @table table (id nvarchar(200),columnA nvarchar(200), columnB nvarchar(200));
Delete TestTable
Output delete.* into @table
Where Id = @Id
select * from @table;

如果要直接输出影响的值，那么使用OUTPUT就可以了。但如果要将影响的值保存到变量中，那么应该使用OUTPUT INTO语句。OUTPUT INTO是原子性操作，要么全部成功，要么全部失败。

 

ALTER语句

在修改Sql Server表结构时，常用到Alter语句，把一些常用的alter语句列举如下。

 

-- 向表中添加字段
Alter table [表名] add [列名] 类型

-- 删除字段
Alter table [表名]  drop column [列名]

-- 修改表中字段类型 （可以修改列的类型，是否为空）
Alter table [表名] alter column [列名] 类型

-- 添加主键
Alter table [表名] add constraint [ 约束名] primary key( [列名])

-- 添加唯一约束
Alter table [表名] add constraint [ 约束名] unique([列名])

-- 添加表中某列的默认值
Alter table [表名] add constraint [约束名] default(默认值) for [列名]

-- 添加约束
Alter table [表名] add constraint [约束名] check (内容)

-- 添加外键约束
 Alter table [表名] add constraint [约束名]  foreign key(列名) referencese 另一表名(列名)

-- 删除约束
Alter table [表名] drop constraint [约束名] 

-- 重命名表
exec sp_rename '[原表名]','[新表名]'

-- 重命名列名
exec sp_rename '[表名].[列名]','[表名].[新列名]'

 -- 删除主键，以及主键上的索引
alter table table_name drop constraint clusteredName

 

字符串函数

SubString()：用于截取指定字符串的方法。该方法有三个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定要截取的字符串的起始位置，起始值为 1 。
参数3：用于指定要截取的长度。

select substring('abcdef',1,3)    -- 返回 abc
select substring('123456321',0,2)    -- 返回 1，即第一位，最好不要这样做

Left()：用于返回指定字符串中指定长度的左侧部分。该方法有两个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定要返回的子字符串的长度。

select LEFT('abc123',3)        -- 返回 abc
select LEFT('左侧部分右侧部分',4)    -- 返回 左侧部分

Right()：用于返回指定字符串中指定长度的右侧部分。该方法有两个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定要返回的子字符串的长度。

select RIGHT('abc123',3)        -- 返回 123
select RIGHT('左侧部分右侧部分',4)    -- 返回 右侧部分

CharIndex()：用于返回指定字符串中指定子字符串出现的起始位置。如果未找到就返回 0。该方法有两个参数：
参数1：用于指定要进行查找的字符串。
参数2：用于指定用作检索的字符串。

select charindex('a','123a123')        -- 返回 4
select charindex('abc','123a123')    -- 返回 0
select charindex('abc','123abc123')    -- 返回 4

Stuff()：用于删除指定长度的字符，并在删除的位置插入新的字符/值。该方法有四个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定要删除字符的起始位置。
参数3：用于指定要删除字符的长度。
参数4：用于指定在删除的位置插入的新的字符串/值。

select stuff('123abc456',4,3,'ABC')        -- 返回 123ABC456
select stuff('123abc456',1,3,'')    -- 返回 abc456，用空字符串替代

Len()：用于返回指定文本的值的长度。前导空格计算在内，尾随空格不计算在内。该方法有一个参数：
参数1：用于指定要操作的文本或字符串。

select len('123')    -- 返回 3
select len('字符串')    -- 返回 3

Difference()：用于返回一个整数值，指示两个字符表达式的 SOUNDEX 值之间的差异。（即两个字符串的相似度）那么什么是 SOUNDEX 值呢？先记着，下面轮到它了。
返回的值从 0 到 4 不等：0 表示几乎不同或完全不同，4 表示几乎相同或完全相同。该方法有两个参数：
参数1：用于指定要进行比对的第一个字符串 SOUNDEX 值 。
参数2：用于指定要进行比对的第二个字符串 SOUNDEX 值。

select difference('action','demo')        -- 返回 2
select difference('123456','整数')        -- 返回 4

Soundex()：用于返回指定字符串的 SOUNDEX 值。SOUNDEX是一种语音算法，利用英文字的读音计算近似值。SOUNDEX函数基于第一个字母和随后三个除了H、Y、W之外的辅音进行计算。
值由四个字符构成，第一个字符为英文字母，后三个为数字。在拼音文字中有时会有会念但不能拼出正确字的情形，可用 Soundex 做类似模糊匹配的效果。这里的模糊匹配跟 LIKE 不同

如果字符串中存在拥有相同对应数字的2个或以上的字母在一起（例如 j 和 k），则删除其他的，只保留1个。去除对应值为 0 的字符，只返回前4个字节，不够用 0 填充。

select soundex('string')    -- 返回 S215
select soundex('str')    -- 返回 S210
select soundex('123')    -- 返回 0000
select soundex('字符串')    -- 返回 0000

PS：除英文字符以外的字符都将返回 0000，所以上面的方法 Difference() 的第二个示例会返回4（表示完全相同）。

Lower()：用于返回指定英文字符串的小写形式的字符串。如果不为英文字符串，则返回原值。该方法有一个参数：
参数1：用于指定要转换为小写形式的字符串。

select lower('ABC')        -- 返回 abc
select lower('123')    -- 返回 123

Upper()：用于返回指定英文字符串的大写形式的字符串。如果不为英文字符串，则返回原值。该方法有一个参数：
参数1：用于指定要转换为大写形式的字符串。

select upper('abc')        -- 返回 ABC
select upper('123')    -- 返回 123

Ltrim()：用于返回删除前导空格之后的字符串。该方法有一个参数：
参数1：用于指定要进行删除前导空格操作的字符串。

select ltrim('        123')        -- 返回 123
select ltrim('        好多空格')    -- 返回 好多空格
select len('        123')    -- 返回 11
select len(ltrim('        123'))  -- 返回 3

Rtrim()：用于返回截断尾随空格之后的字符串。该方法有一个参数：
参数1：用于指定要进行截断尾随空格操作的字符串。

select rtrim('123         ')        -- 返回 123
select len(rtrim('123         '))  -- 返回 3

Replace()：用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。该方法有三个参数：
参数1：用于指定要操作的字符串，即被匹配的字符串。
参数2：用于指定要进行匹配的字符串。
参数3：用于指定用作替换存在的匹配项的字符串。

--把 abc 替换为 xxx
select replace('123abc456','abc','xxx')        -- 返回 123xxx456

-- 用空字符串替换匹配项
select replace('123abc456','abc','')    -- 返回 123456

 Unicode()：根据 Unicode 标准返回指定字符或字符串的第一个字符的整数值。该方法有一个参数：
参数1：用于指定要操作的字符或字符串。

select unicode('a')        -- 返回 97
select unicode('abc')    -- 返回 97，只返回第一个字符

NChar()：根据 Unicode 标准返回指定整数值代码的 Unicode 字符。该方法有一个参数：
参数1：指定一个 Unicode 标准的整数值代码。

select nchar(97)    -- 返回 a
select nchar(65)    -- 返回 A

Char()：将指定的 int 类型的值转换为 ASCII 代码。该方法有一个参数：
参数1：指定一个 int 类型的数值，值范围为 0 至 255。返回 null 表示整数表达式不是在此范围内。

select char(99)    -- 返回 c
select char(9)    -- Tab符
select char(10)    -- 换行符
select char(13)    -- 回车符

ASCII()：返回指定字符或字符串的第一个字符的 ASCII 代码值。该方法有一个参数：
参数1：用于指定要操作的字符或字符串。

select ascii('A')    -- 返回 65
select ascii('a')    -- 返回 97

PS：ASCII() 用于操作单字节，双字节请使用 Unicode()。全角字符为双字节、中文字符为双字节。
示例：

select unicode('字符串')    -- 返回 23383
select nchar('23383')    -- 返回 字
select char('23383')    -- 返回 NULL
select ascii('字符串')    -- 返回 215

select nchar('215')    -- 返回 ×
select char('215')    -- 返回 NULL
select ascii('Kan')    -- 返回 75
select nchar('75')    -- 返回 K
select char('75')    -- 返回 K

Str()：返回一个指定数值的等效字符串。该方法有三个参数：
参数1：指定要操作的数值。
参数2：指定要返回字符串的长度。默认值为10，即不指定默认长度为10，不足用空字符填充。
参数3：要返回的小数位数。默认值为0，即不指定就不保留小数，不足指定位数，用0补充。

select str(123.10)    -- 返回        123
select len(str(123.10))    -- 返回 10
select str(123.10,5)    -- 返回   123
select len(str(123.10,5))    -- 返回 5
select str(123.10,20,5)        -- 返回            123.10000
select len(str(123.10,20,5))    -- 返回 20

Space()：用于返回指定数值长度的空格字符串。该方法有一个参数：
参数1：指定返回空格字符串的长度。

select '1'+space(3)+'2'        -- 返回 1   2
select len('1'+space(3)+'2')    -- 返回 5

Reverse()：用于对指定字符串进行反转，按照单个字符进行反转。该方法有一个参数：
参数1：指定需要执行反转操作的字符串。

select reverse('123')    -- 返回 321
select reverse('abc')    -- 返回 cba

Replicate()：用于返回一个对指定字符串重复指定次数后的字符串。该方法有两个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定字符串重复的次数。如果为0，返回空字符串；如果为负数，则返回 null。

select replicate('123',3)    -- 返回 123123123
select replicate('a',3)        -- 返回 aaa

Quotename()：返回一个 Unicode 字符串，根据指定分隔符，返回一个有效的字符串标识符。该方法有两个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定分隔符，可以是单引号（'）、左括号（[）、右括号（]）、左右括号（[]）或双引号（"）。如果未指定，默认使用左右括号。

select quotename('123','[]')    -- 返回 [123]
select quotename('123','''')    -- 返回 '123'
select quotename('123','"')        -- 返回 "123"
select quotename('12[]3','[]')    -- 返回 [12[]]3],右括号加倍表示转义字符。

Patindex()：返回指定字符串表达式中指定模式第一次出现的起始位置，未找到则返回0。该方法有两个参数：
参数1：指定要匹配的字符串及匹配模式。
参数2：指定要被匹配的字符串表达式。

select Patindex('abc','123abc456abc789')    -- 返回 0
select Patindex('abc','abc')    -- 返回 1
select Patindex('abc%','123abc456abc789')    -- 返回 0
select Patindex('%abc%','123abc456abc789')    -- 返回 4
select Patindex('%[abc]%','123abc456abc789')    -- 匹配字符串 abc 第一次出现的位置，返回 4
select Patindex('%[^abc]%','123abc456abc789')    -- 匹配不等于字符 a、b、c 的其他字符第一次出现的位置，返回 1
select Patindex('%[^abc]%','b123abc456abc789')    -- 匹配不等于字符 a、b、c 的其他字符第一次出现的位置，返回 2

Parsename()：返回对象名称的指定部分。 可检索的对象部分包括对象名称、所有者名称、数据库名称和服务器名称。其实也可以用于截取字符串。该方法有两个参数：
参数1：要检索其指定部分的对象的名称。此名称可包含四部分：服务器名称、数据库名称、所有者名称以及对象名称。
参数2：要返回的对象部分。只能为1至4的数值。1 = 对象名称；2 = 架构名称；3 = 数据库名称；4 = 服务器名称

select parsename('server.dbo.dbTest.table',1) tablename,
       parsename('server.dbo.dbTest.table',2) dbname,
       parsename('server.dbo.dbTest.table',3) schemaname,
       parsename('server.dbo.dbTest.table',4) servername

这种截取方式类似于字符串分割，不过这里是倒着截取的，所以在一定程度上可以用于字符串的截取。不过只支持小数点（.）的分割，而且只支持四位。

select parsename('192.168.1.1',4) col1,
       parsename('192.168.1.1',3) col2,
       parsename('192.168.1.1',2) col3,
       parsename('192.168.1.1',1) col4

STRING_AGG(): 从SQL Server 2017起，添加了STRING_AGG()函数，STRING_AGG()是一个聚合函数，它将由指定的分隔符分隔将字符串行连接成一个字符串。 它不会在结果字符串的末尾添加分隔符。

语法结构：

STRING_AGG ( input_string, separator ) [ order_clause ]

第一个参数： input_string 代表要合并的字段或表达式

第二个参数： separator 是结果字符串的分隔符

后面的那个 [order_clause]  是可选的，用于排序的。

考虑如下Sql 语句：

--建表
create table t
(
name varchar(10),
age varchar(20)
)
 
insert into t
select 'a','100' union all
select 'a','20' union all
select 'b','3' union all
select 'b','2' union all
select 'c','5'

--按照name分组，每组的所有age用","分割符拼接成字符串
select name, string_agg(age,',') as age
from t
group by name
/*
name    age
a    100,20
b    3,2
c    5
*/

--和上面一样，只不过每组里的age按照升序排序
select name, string_agg(age,',') within group(order by cast(age as int)) as age
from t
group by name
/*
name    age
a    20,100
b    2,3
c    5
*/

 

日期函数

Getdate()：获取当前日期时间。

select getdate()

Year()：获取指定日期表达式的年。该方法有一个参数：
参数1：指定要操作的日期表达式。

select year('2016-01-01')    -- 返回 2016
select year('2017-01-01 12:30:30')    -- 返回 2017
select year(getdate())    -- 返回 2017

Month()：获取指定日期表达式的月。该方法有一个参数：

参数1：指定要操作的日期表达式。

select month('2016-01-01')    -- 返回 1
select month('2017-10-01 12:30:30')    -- 返回 10
select month(getdate())    -- 返回 5

Day()：获取指定日期表达式的日。该方法有一个参数：
参数1：指定要操作的日期表达式。

select day('2016-01-01')    -- 返回 1
select day('2017-10-03 12:30:30')    -- 返回 3
select day(getdate())    -- 返回 11

Isdate()：用于判断指定的字符串表达式是否为日期。返回 1，表示指定字符串表达式是日期。返回 0，表示指定字符串表达式不是日期。该方法有一个参数：
参数1：用于指定要操作的字符串表达式。

select isdate('10/30/2017')        -- 月/日/年        返回 1
select isdate('30/10/2017')        -- 日/月/年        返回 0
select isdate('2017/10/30')        -- 年/月/日        返回 1
select isdate('02/29/2017')        --用于判断闰年,由于2017不是闰年,所以没有29     返回 0
select isdate('2017-01-01')        -- 返回 1
select isdate('2017-01-01 10:10:10')    -- 返回 1
select isdate('01-01-2017')        -- 返回 1
select isdate('01-01-2017 10:10:10')    -- 返回 1

Datename()：返回指定日期的指定日期的部分的字符串。该方法有两个参数：
参数1：指定返回指定日期的指定部分的字符串。可以为下图中的日期部分或者缩写。
参数2：指定要操作的日期字符串。

select datename(year,'2017-01-01 10:10:10')    -- 返回 2017
select datename(yyyy,'2017-01-01 10:10:10')    -- 返回 2017

select datename(month,'2017-01-01 10:10:10')    -- 返回 01
select datename(mm,'2017-01-01 10:10:10')    -- 返回 01
    
select datename(quarter,'2017-05-02 10:10:10')        -- 表示一年的第几个季度        返回 2
select datename(weekday,'2017-02-02 10:10:10')        -- 表示一周的星期几        返回 星期四
select datename(week,'2017-02-02 10:10:10')        -- 表示一年的第几周        返回 5
select datename(dayofyear,'2017-02-02 10:10:10')        -- 表示一年的第几天        返回 33

select datename(hh,'2017-01-01 10:30:25')    -- 返回 10

Datepart()：返回指定日期的指定日期的部分的整数。Datepart() 方法和 Datename() 的方法使用方式类似，只不过 Datename() 方法返回的是一个字符串，而 Datepart() 方法返回的是一个整数数值。该方法有两个参数：
参数1：指定返回指定日期的指定部分的字符串。可以为上图中的日期部分或者缩写。
参数2：指定要操作的日期字符串。

select datepart(year,'2017-01-01 10:10:10')    -- 返回 2017
select datepart(yyyy,'2017-01-01 10:10:10')    -- 返回 2017

select datepart(month,'2017-01-01 10:10:10')    -- 返回 1
select datepart(mm,'2017-01-01 10:10:10')    -- 返回 1
    
select datepart(quarter,'2017-05-02 10:10:10')        -- 表示一年的第几个季度        返回 2
select datepart(weekday,'2017-02-02 10:10:10')        -- 表示一周的星期几,即一周的第几天,星期日为一周的第一天    返回 5
select datepart(week,'2017-02-02 10:10:10')        -- 表示一年的第几周        返回 5
select datepart(dayofyear,'2017-02-02 10:10:10')        -- 表示一年的第几天        返回 33

select datepart(hh,'2017-01-01 10:30:25')    -- 返回 10

其他函数：

Coalesce()：返回所有参数中的第一个非空（not null）表达式。该方法最多可以有 n 个参数，但是最少要有两个参数。

SELECT COALESCE('abc','')    -- 返回 abc
SELECT COALESCE(NULL,'')    -- 返回 空字符串
SELECT COALESCE(null,null,'123','abc')    -- 返回 123

PS：这个函数是返回第一个非空的值，所以参数里面必须最少有一个非空的值。

ISNull()：判断指定的表达式一是否为空（null），如果为空则返回表达式二的值，否则返回表达式一的值，类似于C#中的三元运算符。该方法有两个参数：
参数1：指定要操作的表达式一。
参数2：指定要操作的表达式二。

select isnull('','123')        -- 返回 空字符串
select isnull(null,'123')        -- 返回 123
select isnull('123','abc')        -- 返回 123
select isnull(null,null)    -- 返回 null

CAST() 进行类型转化

select CAST(12 as nvarchar);--将int转化为nvarchar
select CAST('12' as bigint);--将nvarchar转化为bigint