经典常用SQL语句大全
创建表
--删除表 --DROP TABLE [dbo].[Test] --创建表 CREATE TABLE [dbo].[Test] ( [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ----自增主键 [Name] nvarchar(50) NULL DEFAULT '默认值', ----可设置默认值 [Remarks] nvarchar(MAX) NULL, --------------------字符串类型 [TypeId] int NULL,-------------------------------整型，取值范围[-231~231) [ServicevalueExcludingVat] MONEY NULL, ----------货币型 [ServicevalueEncludingVat] decimal(12,4) NULL, --精确数值型 共12位，小数点右4位 [VatPercentage] float NULL, ---------------------近似数值型 [AddDate] date NULL , ------------------------------日期 [AddTime] datetime NULL ,---------------------------时间 ) --添加表说明 EXECUTE sp_addextendedproperty N'MS_Description',N'测试表',N'user',N'dbo',N'table',N'Test',NULL,NULL --删除表说明 --EXEC sp_dropextendedproperty N'MS_Description','user','dbo','table', '表名', NULL,NULL --添加字段说明 EXECUTE sp_addextendedproperty N'MS_Description',N'名称',N'user',N'dbo',N'table',N'Test',N'column',N'Name' EXECUTE sp_addextendedproperty N'MS_Description',N'备注',N'user',N'dbo',N'table',N'Test',N'column',N'Remarks' EXECUTE sp_addextendedproperty N'MS_Description',N'类型',N'user',N'dbo',N'table',N'Test',N'column',N'TypeId' EXECUTE sp_addextendedproperty N'MS_Description',N'服务价值，不包括增值税',N'user',N'dbo',N'table',N'Test',N'column',N'ServicevalueExcludingVat' EXECUTE sp_addextendedproperty N'MS_Description',N'服务价值，包括增值税',N'user',N'dbo',N'table',N'Test',N'column',N'ServicevalueEncludingVat' EXECUTE sp_addextendedproperty N'MS_Description',N'增值税百分比',N'user',N'dbo',N'table',N'Test',N'column',N'VatPercentage' EXECUTE sp_addextendedproperty N'MS_Description',N'创建日期',N'user',N'dbo',N'table',N'Test',N'column',N'AddDate' EXECUTE sp_addextendedproperty N'MS_Description',N'创建时间',N'user',N'dbo',N'table',N'Test',N'column',N'AddTime' --删除字段说明 --EXEC sp_dropextendedproperty N'MS_Description', 'user','dbo', 'table', '表名', 'column','字段名'
DECLARE @TAB TABLE ( [Name] nvarchar(50) NULL, ----可设置默认值 [Remarks] nvarchar(MAX) NULL, --------------------字符串类型 [TypeId] int NULL,-------------------------------整型，取值范围[-231~231) [ServicevalueExcludingVat] MONEY NULL, ----------货币型 [ServicevalueEncludingVat] decimal(12,4) NULL, --精确数值型 共12位，小数点右4位 [VatPercentage] float NULL, ---------------------近似数值型 [AddDate] date NULL , ------------------------------日期 [AddTime] datetime NULL ---------------------------时间 ) DECLARE @i int SET @i = 1 WHILE @i<= 100000 BEGIN INSERT INTO @TAB VALUES( ABS(CHECKSUM(newid())%900000)+100000,NEWID(), CONVERT(int,RAND()*10)+1, CONVERT(MONEY,RAND()*1000), CONVERT(decimal(12,4),RAND()*1000), CONVERT(decimal(12,2),RAND()*100), DATEADD(dd, CONVERT(int,RAND()*1000), '2015-01-01'), DATEADD(dd, CONVERT(int,RAND()*1000), '2015-01-01')) SET @i = @i + 1 END INSERT INTO [Test]([Name],[Remarks],[TypeId],[ServicevalueExcludingVat],[ServicevalueEncludingVat],[VatPercentage],[AddDate],[AddTime]) SELECT [Name],[Remarks],[TypeId],[ServicevalueExcludingVat],[ServicevalueEncludingVat],[VatPercentage],[AddDate],[AddTime] FROM @TAB
--单列索引(非聚集索引) CREATE INDEX [索引名称] ON [表名] ([字段1]) --唯一索引(非聚集索引) CREATE UNIQUE INDEX [索引名称] ON [表名] ([字段1]) --复合索引(非聚集索引) CREATE UNIQUE NONCLUSTERED INDEX [索引名称] ON [表名]([字段1],[字段2]) --删除索引 DROP INDEX [索引名称] ON [表名] --查询索引 SELECT CASE WHEN t.[type] = 'U' THEN '表' WHEN t.[type] = 'V' THEN '视图' END AS '类型', SCHEMA_NAME( t.schema_id ) + '.' + t.[name] AS '(表/视图)名称', i.[name] AS 索引名称, SUBSTRING ( column_names, 1, LEN( column_names ) - 1 ) AS '列名', CASE WHEN i.[type] = 1 THEN '聚集索引' WHEN i.[type] = 2 THEN '非聚集索引' WHEN i.[type] = 3 THEN 'XML索引' WHEN i.[type] = 4 THEN '空间索引' WHEN i.[type] = 5 THEN '聚簇列存储索引' WHEN i.[type] = 6 THEN '非聚集列存储索引' WHEN i.[type] = 7 THEN '非聚集哈希索引' END AS '索引类型', CASE WHEN i.is_unique = 1 THEN'唯一' ELSE '不唯一' END AS '索引是否唯一' FROM sys.objects AS t INNER JOIN sys.indexes AS i ON t.object_id = i.object_id CROSS APPLY ( SELECT col.[name] + ', ' FROM sys.index_columns AS ic INNER JOIN sys.columns AS col ON ic.object_id = col.object_id AND ic.column_id = col.column_id WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id ORDER BY col.column_id FOR XML PATH ( '' ) ) D ( column_names ) WHERE t.is_ms_shipped <> 1 AND index_id > 0 ORDER BY i.[name];
查询表结构【示例图】
--查询表结构 CREATE PROC [dbo].[aaa_select_table] --创建存储过程 @name nvarchar(50) as begin --开始 declare @condition nvarchar(2000); set @condition=' where 1=1 '; if(@name<>'') set @condition=@condition+' and d.name like ''%'+@name+'%'''; exec(' SELECT 表名=case when a.colorder=1 then d.name else '''' end, 表说明=case when a.colorder=1 then isnull(f.value,'''') else '''' end, 字段序号=a.colorder, 字段名=a.name, 字段说明=isnull(g.[value],''''), 类型=b.name, 长度=COLUMNPROPERTY(a.id,a.name,''PRECISION''), 标识=case when COLUMNPROPERTY( a.id,a.name,''IsIdentity'')=1 then ''√''else '''' end, 主键=case when exists(SELECT 1 FROM sysobjects where xtype=''PK'' and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ))) then ''√'' else '''' end, 允许空=case when a.isnullable=1 then ''√''else '''' end, 默认值=isnull(e.text,'''') FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties'' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 '+@condition+' order by a.id,a.colorder '); end;--结束
CREATE PROC [dbo].[zsgc_GenerateSQL] --创建存储过程 @TableName nvarchar(50) as -- set nocount on; --不返回计数，提高应用程序性能 begin --开始 exec(' DECLARE @TableNotes nvarchar (50) SET @TableNotes = (SELECT CONVERT(varchar(50) ,ISNULL(f.value,'''')) FROM sysobjects AS d LEFT JOIN sys.extended_properties f ON d.id= f.major_id AND f.minor_id= 0 WHERE d.name = '''+@TableName+''') create table #helpuser ( p_Name nvarchar(200), p_sql nvarchar(4000), p_remark nvarchar(200) ) insert into #helpuser(p_Name,p_sql,p_remark) SELECT ''INSERT_''+'''+@TableName+''',Add1 +char(10)+ Add2,''增加''+@TableNotes FROM( SELECT ''INSERT INTO [''+'''+@TableName+'''+''] (''+ STUFF(( SELECT '','' + a.name +'''' FROM syscolumns a LEFT JOIN systypes AS b ON a.xusertype = b.xusertype LEFT JOIN sysobjects AS d ON a.id = d.id AND a.isnullable = 1 AND d.xtype= ''U'' AND d.name<> ''dtproperties'' WHERE d.name = '''+@TableName+''' FOR XML PATH ('''')),1,1,'''') + '') '' AS Add1, '' VALUES (''+STUFF(( SELECT '',@'' + a.name +'''' FROM syscolumns a LEFT JOIN systypes AS b ON a.xusertype = b.xusertype LEFT JOIN sysobjects AS d ON a.id = d.id AND a.isnullable = 1 AND d.xtype= ''U'' AND d.name<> ''dtproperties'' WHERE d.name = '''+@TableName+''' FOR XML PATH ('''')),1,1,'''') + '') '' AS Add2) AS a insert into #helpuser(p_Name,p_sql,p_remark) SELECT ''DELETE_''+'''+@TableName+''',''DELETE FROM [''+'''+@TableName+'''+''] WHERE Id = @Id'',''删除''+@TableNotes insert into #helpuser(p_Name,p_sql,p_remark) SELECT ''UPDATE_''+'''+@TableName+''',edit1 + LEFT(edit2,LEN(edit2)-2) + edit3,''修改''+@TableNotes FROM ( SELECT ''UPDATE [''+'''+@TableName+'''+'']''+ char(10)+ ''SET ''+ char(10) AS edit1, STUFF(( SELECT a.name +'' = @'' + a.name +'',''+ char(10) FROM syscolumns a LEFT JOIN systypes AS b ON a.xusertype = b.xusertype LEFT JOIN sysobjects AS d ON a.id = d.id AND a.isnullable = 1 AND d.xtype= ''U'' AND d.name<> ''dtproperties'' WHERE d.name = '''+@TableName+''' FOR XML PATH ('''')),1,0,'''') AS edit2, char(10)+''WHERE Id = @Id'' AS edit3) AS a insert into #helpuser(p_Name,p_sql,p_remark) SELECT ''SELECT_''+'''+@TableName+''',''SELECT ''+ ( SELECT STUFF(( SELECT CASE WHEN b.name = ''nvarchar'' THEN '',ISNULL('' + a.name +'', '''''''') AS '' + a.name WHEN b.name = ''datetime'' THEN '',ISNULL(CONVERT(varchar(50),'' + a.name +'',23), '''''''') AS '' + a.name ELSE '','' + a.name +'''' END FROM syscolumns a LEFT JOIN systypes AS b ON a.xusertype = b.xusertype LEFT JOIN sysobjects AS d ON a.id = d.id AND d.xtype= ''U'' AND d.name<> ''dtproperties'' WHERE d.name = '''+@TableName+''' FOR XML PATH ('''')),1,1,'''')) + '' FROM [''+'''+@TableName+'''+''] WHERE Id = @Id'',''查询''+@TableNotes insert into #helpuser(p_Name,p_sql,p_remark) SELECT ''PAGE_''+'''+@TableName+''',''PAGE_''+'''+@TableName+' @pageindex,@pagesize,@Name'',''分页''+@TableNotes insert into #helpuser(p_Name,p_sql,p_remark) values(''---------'',''--------分页存储过程--------'',''----------'') insert into #helpuser(p_sql) values (''CREATE PROCEDURE PAGE_'+@TableName+''') insert into #helpuser(p_sql) values (''@pageindex [int],'') insert into #helpuser(p_sql) values (''@pagesize [int],'') insert into #helpuser(p_sql) values (''@Account [nvarchar](50),'') insert into #helpuser(p_sql) values (''@StaffName [nvarchar](50)'') insert into #helpuser(p_sql) values (''WITH EXECUTE AS CALLER'') insert into #helpuser(p_sql) values (''AS'') insert into #helpuser(p_sql) values (''BEGIN'') insert into #helpuser(p_sql) values (''DECLARE @pagebefore INT ;'') insert into #helpuser(p_sql) values (''DECLARE @pagerear INT ;'') insert into #helpuser(p_sql) values (''DECLARE @condition nvarchar (2000) ;'') insert into #helpuser(p_sql) values (''SET @pagebefore = @pagesize * @pageindex ;'') insert into #helpuser(p_sql) values (''SET @pagerear = @pagebefore + @pagesize ;'') insert into #helpuser(p_sql) values (''SET @condition = '''' where 1=1 '''' ;'') insert into #helpuser(p_sql) values (''IF (@Account <> '''''''')'') insert into #helpuser(p_sql) values (''BEGIN'') insert into #helpuser(p_sql) values (''SET @condition =@condition + '''' and a.Account like ''''''''%'''' +@Account + ''''%'''''''''''' ;'') insert into #helpuser(p_sql) values (''END'') insert into #helpuser(p_sql) values (''IF (@StaffName <> '''''''')'') insert into #helpuser(p_sql) values (''BEGIN'') insert into #helpuser(p_sql) values (''SET @condition =@condition + '''' and a.StaffName = '''''''''''' +@StaffName + '''''''''''''''' ;'') insert into #helpuser(p_sql) values (''END'') insert into #helpuser(p_sql) values (''EXEC ('''''') insert into #helpuser(p_sql) values (''SELECT row_number()over(order by a.Id DESC) as iid,* FROM ('') insert into #helpuser(p_sql) values (''SELECT'') insert into #helpuser(p_sql) SELECT CASE WHEN b.name = ''nvarchar'' THEN ''ISNULL(a.'' + a.name +'', '''''''') AS '' + a.name + '','' WHEN b.name = ''datetime'' THEN ''ISNULL(CONVERT(varchar(50),a.'' + a.name +'',23), '''''''') AS '' + a.name+'','' ELSE ''a.'' + a.name +'','' END FROM syscolumns a LEFT JOIN systypes AS b ON a.xusertype = b.xusertype LEFT JOIN sysobjects AS d ON a.id = d.id AND d.xtype= ''U'' AND d.name<> ''dtproperties'' WHERE d.name = '''+@TableName+''' insert into #helpuser(p_sql) values (''FROM '+@TableName+' AS a '''' +@condition + '''' )AS a where a.iid>'''' +@pagebefore + '''' and a.iid<='''' +@pagerear + '''''') insert into #helpuser(p_sql) values (''select count(*) as rows FROM '+@TableName+' '''' +@condition + '''''''') ;'') insert into #helpuser(p_sql) values (''END '') select * from #helpuser drop Table #helpuser '); end;--结束
基本sql语句
插入：INSERT INTO [表名] VALUES('value1','value2') INSERT INTO [表名]([字段1],[字段2]) VALUES('value1','value2') INSERT INTO [表名]([字段1],[字段2]) SELECT [字段1],[字段2] FROM [表名] 复制：SELECT * INTO 目标表名 FROM 源表名 WHERE 1=2 (【复制表结构】即:让WHERE条件不成立) SELECT * INTO 目标表名 FROM 源表名（【复制表结构及数据】要求目标表不存在，因为在插入时会自动创建） 删除表：DROP TABLE [表名] 删除数据：DELETE FROM [表名] WHERE 范围 清空：TRUNCATE TABLE [表名] 更新：UPDATE [表名] SET [字段]='value1' WHERE 范围 替换：UPDATE [表名] SET [字段] = REPLACE([字段], '替换前内容', '替换后内容'); 选择：SELECT * FROM [表名] WHERE 范围 查找：SELECT * FROM [表名] WHERE [字段] LIKE '%value1%' 排序：SELECT * FROM [表名] ORDER BY [字段1] ASC,[字段2] DESC 总数：SELECT COUNT(*) AS TotalCount FROM [表名] 求和：SELECT SUM([字段]) AS SumValue FROM [表名] 平均：SELECT AVG([字段]) AS AvgValue FROM [表名] 最大：SELECT MAX([字段]) AS MaxValue FROM [表名] 最小：SELECT MIN([字段]) AS MinValue FROM [表名] --添加主键 ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY(字段); --修改字段名 (注意: 更改对象名的任一部分都可能会破坏脚本和存储过程) EXEC SP_RENAME '表名.字段名','新字段名' --修改字段类型： ALTER TABLE 表名 ALTER COLUMN 字段名 INT NOT NULL --增加字段 ALTER TABLE 表名 ADD 字段名 INT NOT NULL --删除字段 ALTER TABLE 表名 DROP COLUMN 字段名;
外连接
--外连接 A、left join： 左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。 B：right join: 右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。 C：join： 全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
高级查询运算词
A： UNION 运算符 UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 B： EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。 C： INTERSECT 运算符 INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。 注：使用运算词的几个查询结果行必须是一致的。
常用查询
1：查询重复数据
select * from table1 a where field1 in (select field1 from table1 group by field1 having count(*) > 1)
2：自增序号
SELECT row_number () OVER (ORDER BY Id) AS '序号',* FROM table1
3：星期几
set language N'Simplified Chinese' select datename(weekday, getdate()) select datename(dw, getdate())
常用函数
参考链接：https://www.w3cschool.cn/wqf_database/
--char(9) 水平制表符 print 'A'+char(9)+'B' --char(10)换行键 print 'A'+char(10)+'B' --char(13)回车键 print 'A'+char(13)+'B'
--ASCII() 返回字符表达式最左端字符的ASCII 码值。在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。 SELECT ASCII('A') --CHAR() 将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值，CHAR（） 返回NULL 。 SELECT CHAR(65) --LOWER() 将字符串全部转为小写 SELECT LOWER('ABC') --UPPER() 将字符串全部转为大写。 SELECT UPPER('abc') --STR() 将数字转换为字符串的快捷函数 , 函数有3个参数：数值、总长度和小数位数 SELECT STR(123.4, 8, 4)
--LTRIM() 把字符串头部的空格去掉。 SELECT LTRIM(' ABC') --RTRIM() 把字符串尾部的空格去掉。 SELECT RTRIM('ABC ')
--LEFT() 返回字符串左起*个字符。 SELECT LEFT('ABCDEFG',3) --RIGHT() 返回字符串右起*个字符。 SELECT RIGHT('ABCDEFG',3) --SUBSTRING() 返回从字符串左边第*个字符起*个字符的部分。 SELECT SUBSTRING('ABCDEFG',3,2)
--CHARINDEX() 返回字符串中某个指定的子串出现的开始位置。如果没有发现子串，则返回0 值。不能用于TEXT 和IMAGE 数据类型。 SELECT CHARINDEX('C','ABCDEFG') --PATINDEX() 返回字符串中某个指定的子串出现的开始位置。可以使用通配符 SELECT PATINDEX('%B_D%','ABCDEFG')
--QUOTENAME() 返回被特定字符括起来的字符串。给输入的字符串加一对方括号，并返回新形成的字符串 SELECT QUOTENAME('ABCDEFG','[]') --输出结果：[ABCDEFG] --REPLICATE() 返回一个重复指定次数的字符串。 SELECT REPLICATE('ABC|',3) --输出结果：ABC|ABC|ABC| --REVERSE() 将指定的字符串的字符排列顺序颠倒。 SELECT REVERSE('ABCDEFG') --输出结果：GFEDCBA --REPLACE() 返回被替换了指定子串的字符串。 SELECT REPLACE('ABCDEFG','ABC','XXXXX') --输出结果：XXXXXDEFG --SPACE() 返回一个有指定长度的空白字符串。 SELECT 'A' + SPACE(5) + 'B' --输出结果：A B --STUFF() 将字符串插入到另一个字符串中。它会删除开始位置第一个字符串中的指定长度的字符，然后将第二个字符串插入到开始位置的第一个字符串中。 --STUFF（<character_expression>，<开始>，<长度>，<character_expression>） SELECT STUFF('ABCD', 2, 1, '|EFG|') --输出结果：A|EFG|CD
CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型 例：SELECT CAST('123' AS int) CONVERT() 函数是把日期转换为新数据类型的通用函数。可以用不同的格式显示日期/时间数据。 例：SELECT CONVERT(int, '123') 例：SELECT CONVERT(varchar(50) , GETDATE(), 23 ) --CAST()函数和CONVERT()函数都不能执行四舍五入或截断操作 SELECT CAST('123.4' AS int) --在将 varchar 值 '123.4' 转换成数据类型 int 时失败。 SELECT CONVERT(int, '123.4') --在将 varchar 值 '123.4' 转换成数据类型 int 时失败。
AVG() --返回的平均价值 COUNT() --返回的总数 FIRST() --返回第一个值 LAST() --返回最后一个值 MAX() --返回的最大值 MIN() --返回最小值 TOTAL() --返回总和
DATEADD()函数用于在日期/时间值上加上日期单位间隔 例：SELECT DATEADD(yy, 1, '2019-01-01') 例：SELECT DATEADD(mm, 2, '2019-01-01') 例：SELECT DATEADD(dd, 30, '2019-01-01') DATEDIFF() 函数返回两个日期之间的时间 例：SELECT DATEDIFF(yy,'2009-01-01','2019-01-01') 例：SELECT DATEDIFF(mm,'2019-01-01','2019-12-12') 例：SELECT DATEDIFF(dd,'2019-01-01','2019-01-30') DATEPART() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。 例：SELECT DATEPART(yy,'2019-01-01') 例：SELECT DATEPART(mm,'2019-01-01') 例：SELECT DATEPART(dd,'2019-01-01')
自定义函数
CREATE FUNCTION [dbo].[Intercept] ( @String VARCHAR(MAX), @Delimiter VARCHAR(MAX) ) RETURNS @temptable TABLE ( [value] VARCHAR(MAX) ) AS BEGIN DECLARE @idx INT=1 DECLARE @slice VARCHAR(MAX) IF LEN(@String) < 1 OR LEN(ISNULL(@String,'')) = 0 RETURN WHILE @idx != 0 BEGIN SET @idx = CHARINDEX(@Delimiter,@String) IF @idx != 0 SET @slice = LEFT(@String,@idx - 1) ELSE SET @slice = @String IF LEN(@slice) > 0 INSERT INTO @temptable([value]) VALUES(@slice) SET @String = RIGHT (@String, LEN(@String) - @idx) IF LEN(@String) = 0 BREAK END RETURN END --执行 SELECT * FROM Intercept ('1,2,3,4,5,6,7,8,9',',')
CREATE FUNCTION [dbo].[Intercept2] ( @SplitString varchar(max), @Separator char(1) ) RETURNS @SplitStringsTable TABLE ( [id] int identity(1,1), [value] nvarchar(max) ) AS BEGIN DECLARE @CurrentIndex int; DECLARE @NextIndex int; DECLARE @ReturnText nvarchar(max); SELECT @CurrentIndex=1; WHILE(@CurrentIndex<=len(@SplitString)) BEGIN SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex); IF(@NextIndex=0 OR @NextIndex IS NULL) SELECT @NextIndex=len(@SplitString)+1; SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex); INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText); SELECT @CurrentIndex=@NextIndex+1; END RETURN; END --执行 SELECT * FROM Intercept2 ('1,2,3,4,5,6,7,8,9',',')
CREATE FUNCTION [dbo].[ConvertHex10To36] (@Hex10 INT) RETURNS VARCHAR(100) AS BEGIN declare @reminder int declare @decimalNum int= @Hex10+100000 declare @system int=36 declare @hexStr varchar(50)='' declare @baseStr varchar(36)='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' while @decimalNum>=@system begin set @reminder=@decimalNum%@system set @decimalNum=@decimalNum/@system set @hexStr=SUBSTRING(@baseStr,@reminder+1,1)+@hexStr end set @hexStr=SUBSTRING(@baseStr,@decimalNum+1,1)+@hexStr RETURN LEFT(@hexStr+'000000',6) END --执行 SELECT dbo.ConvertHex10To36('123456')
CREATE FUNCTION [dbo].[GetDayTime] ( @beginTime nvarchar(100), @endTime nvarchar(100) ) RETURNS @returntable TABLE ( DayTime nvarchar(100) ) AS BEGIN while(@beginTime<=@endTime) begin INSERT INTO @returntable (DayTime) SELECT @beginTime SELECT @beginTime = CONVERT(varchar(50),dateadd(day,1,@beginTime), 23 ) end; RETURN END --执行 SELECT * FROM GetDayTime ('2019-08-01','2019-08-31')
CREATE FUNCTION [dbo].[GetMonthTime] ( @beginTime nvarchar(100), @endTime nvarchar(100) ) RETURNS @returntable TABLE ( MonthTime nvarchar(100) ) AS BEGIN SET @beginTime = @beginTime + '-01'; SET @endTime = @endTime + '-01'; while(@beginTime<=@endTime) begin INSERT INTO @returntable (MonthTime) SELECT CONVERT(varchar(50),LEFT (CONVERT (VARCHAR ,@beginTime, 21),7)) SELECT @beginTime = CONVERT(varchar(50),LEFT (CONVERT (VARCHAR ,dateadd(mm, 1 ,@beginTime), 21),7))+ '-01' end; RETURN END --执行 SELECT * FROM GetMonthTime ('2019-01','2019-08')
CREATE FUNCTION [dbo].[GetYearTime] ( @beginTime nvarchar(100), @endTime nvarchar(100) ) RETURNS @returntable TABLE ( YearTime nvarchar(100) ) AS BEGIN SET @beginTime = @beginTime + '-01-01'; SET @endTime = @endTime + '-01-01'; while(@beginTime<=@endTime) begin INSERT INTO @returntable (YearTime) SELECT CONVERT (VARCHAR(4),YEAR(@beginTime)) SELECT @beginTime = CONVERT (VARCHAR(4),YEAR(dateadd(yy,1,@beginTime))) + '-01-01' end; RETURN END --执行 SELECT * FROM GetYearTime ('2015','2019')
其他
select * from table1 where time between time1 and time2 select a,b,c, from table1 where a not between 数值1 and 数值2
select top 10 * from tablename order by newid()
SELECT TOP 10 st.text AS [父级完整语句], SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) as [统计对应的部分语句], CAST( ((qs.total_elapsed_time / 1000000.0)/qs.execution_count) AS DECIMAL(28,2) ) AS [平均消耗秒数], CAST(qs.last_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最后完成消耗秒数], qs.last_execution_time AS [最后执行时间], CAST(qs.min_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最小消耗秒数], CAST(qs.max_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [最大消耗秒数], CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [总消耗秒数], (qs.execution_count) AS [总执行次数], creation_time AS [编译计划的时间], CAST(qs.last_worker_time / 1000000.0 AS DECIMAL(28, 2)) AS [最后完成占用CPU秒数] from sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qs.last_execution_time>DATEADD(n,-30,GETDATE()) ORDER BY qs.last_worker_time DESC
USE [master] GO ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIMPLE WITH NO_WAIT GO ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIMPLE --简单模式 GO USE 要清理的数据库名称 GO DBCC SHRINKFILE (N'要清理的数据库名称_log' , 2, TRUNCATEONLY) --设置压缩后的日志大小为2M，可以自行指定 GO USE [master] GO ALTER DATABASE 要清理的数据库名称 SET RECOVERY FULL WITH NO_WAIT GO ALTER DATABASE 要清理的数据库名称 SET RECOVERY FULL --还原为完全模式 GO
-- 第一步：启用 sql server 邮件的功能 exec sp_configure 'show advanced options', 1 go reconfigure with override go exec sp_configure 'Database Mail XPs', 1 go reconfigure with override go -- 第二步：查看数据库邮件功能是否开启，value 值为1表示已开启，0为未开启 select name,value,description,is_dynamic,is_advanced from sys.configurations where name like '%mail%' --第三步:创建邮件账户 if exists(SELECT * FROM msdb..sysmail_account WHERE NAME='Mail') --判断邮件账户名为 test 的账户是否存在 begin EXEC msdb..sysmail_delete_account_sp @account_name='Mail' -- 删除邮件账户名为 test 的账户 end exec msdb..sysmail_add_account_sp --创建邮件账户 @account_name = 'Mail' -- 邮件帐户名称 ,@email_address = '123456789@qq.com' -- 发件人邮件地址 ,@display_name = '系统' -- 发件人姓名 ,@replyto_address = null -- 回复地址 ,@description = null -- 邮件账户描述 ,@mailserver_name = 'smtp.qq.com' -- 邮件服务器地址 ,@mailserver_type = 'SMTP' -- 邮件协议 ,@port = 25 -- 邮件服务器端口 ,@username = '123456789@qq.com' -- 用户名 ,@password = '*********' -- QQ邮箱POP3/SMTP服务 授权码 ,@use_default_credentials = 0 -- 是否使用默认凭证，0为否，1为是 ,@enable_ssl = 1 -- 是否启用 ssl 加密，0为否，1为是 PS：如果使用的是QQ邮箱，记得要把参数 @enable_ssl 的值设置为 1 ,@account_id = null -- 输出参数，返回创建的邮件账户的ID --第四步:创建邮件配置文件 if exists(SELECT * FROM msdb..sysmail_profile where NAME = N'SendEmailProfile') --判断名为 SendEmailProfile 的邮件配置文件是否存在 begin exec msdb..sysmail_delete_profile_sp @profile_name = 'SendEmailProfile' --删除名为 SendEmailProfile 的邮件配置文件 end exec msdb..sysmail_add_profile_sp -- 添加邮件配置文件 @profile_name = 'SendEmailProfile', -- 配置文件名称 @description = '数据库发送邮件配置文件', -- 配置文件描述 @profile_id = NULL -- 输出参数，返回创建的邮件配置文件的ID --第五步:邮件账户和邮件配置文件相关联 exec msdb..sysmail_add_profileaccount_sp @profile_name = 'SendEmailProfile', -- 邮件配置文件名称 @account_name = 'Mail', -- 邮件账户名称 @sequence_number = 1 -- account 在 profile 中的顺序，一个配置文件可以有多个不同的邮件账户 --第六步:发送测试邮件 EXEC msdb.dbo.sp_send_dbmail @profile_name=N'SendEmailProfile', --配置文件，就是前面配置好的 @recipients='123456789@qq.com', --收件箱（多个用;隔开） @subject ='同步失败预警', -- 消息的主题 @body_format='HTML', --指定消息的格式，一般文本直接去掉即可，发送html格式的内容需加上 @body='', --消息主体 @query='select * FROM [Test].[dbo].[Course]', --查询 @attach_query_result_as_file=1, --是否以附件发送 @query_attachment_filename='test.csv'; --附件文件名 --第七步:查询邮件发送状态 SELECT recipients, --收件人的电子邮件地址 subject, -- 消息的主题 body, --消息的正文。 body_format, --消息正文的格式。 可为 TEXT 和 HTML。 query, --邮件程序所执行的查询 sent_date, --发送消息的日期和时间 sent_status --邮件的状态 【sent】邮件已发送。 --【unsent】数据库邮件仍在尝试发送消息。 --【retrying】数据库邮件无法发送消息，但正在尝试再次发送。 --【failed】数据库邮件无法发送消息。 FROM msdb.dbo.sysmail_allitems ORDER BY mailitem_id DESC
declare @tableHTML nvarchar(max) set @tableHTML = N'<H1>test for xml result</H1>' + N'<table border="1">' + N'<tr>' + N'<th> id </th>' + N'<th>Name</th>' + N'<th>IsDelete</th></tr>' + CAST(( select td=id,'', td=name,'', td=IsDelete,'' from ActivityType for xml path('tr'))as nvarchar(max)) +'</table>' SELECT @tableHTML
--“因为数据库正在使用，所以无法获得对数据库的独占访问权”， --终解决方案如下 --关键SQL语句： ALTER DATABASE [datebase] SET OFFLINE WITH ROLLBACK IMMEDIATE --用完之后再 ALTER database [datebase] set online
--查看被锁表： SELECT request_session_id spid, OBJECT_NAME(resource_associated_entity_id) tableName FROM sys.dm_tran_locks WHERE resource_type = 'OBJECT' ORDER BY request_session_id ASC -- 解锁： DECLARE @spid INT SET @spid = 52 --锁表进程 DECLARE @SQL VARCHAR (1000) SET @SQL = 'kill ' + CAST (@spid AS VARCHAR) EXEC (@SQL)
--查看执行时间和cpu占用时间 set statistics time on select * from dbo.Product set statistics time off --查看查询对I/0的操作情况 set statistics io on select * from dbo.Product set statistics io off
SELECT a.name, a.[type],--P = SQL 存储过程 V = 视图 AF = 聚合函数 (CLR) b.[definition], a.create_date, --创建日期 a.modify_date --修改日期 FROM sys.all_objects a, sys.sql_modules b WHERE a.is_ms_shipped = 0 AND a.object_id = b.object_id AND a.[type] IN ('P', 'V', 'AF') ORDER BY a.[name] ASC
--查看当前库中所有的触发器和与之相对应的表： SELECT tb2.name AS '对应的表', tb1.name AS '触发器', tb1.crdate AS '创建时间' FROM Sysobjects tb1 JOIN Sysobjects tb2 ON tb1.parent_obj = tb2.id WHERE tb1.type = 'TR'; --显示触发器的定义: EXEC sp_helptext 'UPDATE_Personnel_Staff_Changes';
BEGIN TRY SELECT 5 / 0 END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS '错误描述', ERROR_NUMBER() AS '错误号', ERROR_SEVERITY() AS '严重级别', ERROR_STATE() AS '错误状态号', ERROR_LINE() AS '出错的行号', ERROR_PROCEDURE() AS '发生错误的存储过程名或触发器名' END CATCH
BEGIN try BEGIN TRANSACTION --语句正确 UPDATE [Test] SET [MenuId]='11' WHERE [Id]='1' --MenuId为int类型，出错 UPDATE [Test] SET [MenuId]='ABCD' WHERE [Id]='1' --语句正确 UPDATE [Test] SET [MenuId]='13' WHERE [Id]='1' SELECT ResponseNum = 1,Message = '保存成功' COMMIT TRANSACTION --事务已经成功执行，提交事务。 END try BEGIN catch SELECT ResponseNum = 0,Message = ERROR_MESSAGE() ROLLBACK TRANSACTION --数据处理过程中出错，回滚到没有处理之前的数据状态 END catch
--DROP TABLE [dbo].[UserTree] CREATE TABLE [dbo].[UserTree] ( [Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY, [Name] nvarchar(50) NULL, [ParentId] INT NULL, --父Id [Tree] nvarchar(MAX) NULL, --层级关系 [Level] INT NULL --层级 ) INSERT INTO [UserTree] VALUES (N'A', 0, N',', 1); INSERT INTO [UserTree] VALUES (N'B', 1, N',1,', 2); INSERT INTO [UserTree] VALUES (N'C', 2, N',1,2,', 3); INSERT INTO [UserTree] VALUES (N'D', 3, N',1,2,3', 4); INSERT INTO [UserTree] VALUES (N'E', 4, N',1,2,3,4,', 5); INSERT INTO [UserTree] VALUES (N'F', 5, N',1,2,3,4,5,', 6); INSERT INTO [UserTree] VALUES (N'G', 6, N',1,2,3,4,5,6,', 7); INSERT INTO [UserTree] VALUES (N'H', 7, N',1,2,3,4,5,6,7,', 8); INSERT INTO [UserTree] VALUES (N'I', 8, N',1,2,3,4,5,6,7,8,', 9); INSERT INTO [UserTree] VALUES (N'J', 9, N',1,2,3,4,5,6,7,8,9,', 10); INSERT INTO [UserTree] VALUES (N'AA', 0, N',', 1); INSERT INTO [UserTree] VALUES (N'BB', 11, N',11,', 2); INSERT INTO [UserTree] VALUES (N'CC', 12, N',11,12,', 3); INSERT INTO [UserTree] VALUES (N'DD', 13, N',11,12,13,', 4); INSERT INTO [UserTree] VALUES (N'EE', 14, N',11,12,13,14,', 5); INSERT INTO [UserTree] VALUES (N'FF', 15, N',11,12,13,14,15,', 6); INSERT INTO [UserTree] VALUES (N'GG', 16, N',11,12,13,14,15,16,', 7); INSERT INTO [UserTree] VALUES (N'HH', 17, N',11,12,13,14,15,16,17,', 8); INSERT INTO [UserTree] VALUES (N'II', 18, N',11,12,13,14,15,16,17,18,', 9); INSERT INTO [UserTree] VALUES (N'JJ', 19, N',11,12,13,14,15,16,17,18,19,', 10); SELECT * FROM UserTree DECLARE @Id int DECLARE @Tree nvarchar(MAX) DECLARE @UpLevel int DECLARE @DownLevel int SELECT @Id = id,@Tree = Tree,@UpLevel = [Level]-2,@DownLevel = [Level]+2 FROM UserTree WHERE Id = 14 --查找出A的下级用户（所有） --SELECT * FROM UserTree WHERE patindex('%,'+CONVERT(varchar(50),@Id)+',%',Tree) >= 1 --查找出A的下级用户（所有 方法二） WITH TreeLevel(Id,Name,ParentId) AS( SELECT Id,Name,ParentId from UserTree where Id = 1 UNION ALL SELECT a.Id,a.Name,a.ParentId FROM UserTree AS a,TreeLevel WHERE a.ParentId = TreeLevel.Id ) SELECT * FROM TreeLevel --查找出A的下级用户(三级) SELECT * FROM UserTree WHERE patindex('%,'+CONVERT(varchar(50),@Id)+',%',Tree) >= 1 AND Level BETWEEN [Level] AND @DownLevel --查找出A的上级用户（所有） --SELECT * FROM UserTree WHERE patindex('%,'+CONVERT(varchar(50),Id)+',%',@Tree) >= 1 --查找出A的上级用户(三级) SELECT * FROM UserTree WHERE patindex('%,'+CONVERT(varchar(50),Id)+',%',@Tree) >= 1 AND [Level] BETWEEN @UpLevel AND Level
CREATE TABLE [dbo].[PhoneBook] ( [Id] int IDENTITY(1,1) NOT NULL, [Phone] nvarchar(100) NULL, --联系电话 [CommunicationTime] datetime NULL --通讯时间 ) INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'13112345678', N'2020-06-02 01:00:00.000') INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'13112345678', N'2020-06-02 02:00:00.000') INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'15186452378', N'2020-06-02 03:00:00.000') INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'15186452378', N'2020-06-02 04:00:00.000') INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'13112345678', N'2020-06-02 05:00:00.000') INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'15186452378', N'2020-06-02 06:00:00.000') INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'13112345678', N'2020-06-02 07:00:00.000') INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'18125634588', N'2020-06-02 08:00:00.000') INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'18125634588', N'2020-06-02 09:00:00.000') INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'13112345678', N'2020-06-02 10:00:00.000') INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'18125634588', N'2020-06-02 11:00:00.000') INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'13112345678', N'2020-06-02 12:00:00.000') INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'15186452378', N'2020-06-02 13:00:00.000') INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'13112345678', N'2020-06-02 14:00:00.000') INSERT INTO [dbo].[PhoneBook] ([Phone],[CommunicationTime]) VALUES ( N'13112345678', N'2020-06-02 15:00:00.000') SELECT * FROM PhoneBook SELECT CASE WHEN AA.num=1 THEN AA.Phone ELSE AA.Phone+'('+CASt(AA.num AS VARCHAR(4))+')' END AS '通讯记录',AA.num AS '通讯次数',AA.CommunicationTime AS '最后联系时间' FROM ( SELECT a.Phone ,COUNT(*) AS num,MAX(a.CommunicationTime) AS CommunicationTime FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Phone ORDER BY CommunicationTime,Phone) AS num1,ROW_NUMBER() OVER (ORDER BY CommunicationTime,Phone) AS num2,* FROM PhoneBook ) a GROUP BY a.num2-a.num1,a.Phone,CONVERT(VARCHAR(10),a.CommunicationTime,120) ) AS AA ORDER BY AA.CommunicationTime
--SqlServer数据库备份 数据库名=Test --增量备份 BACKUP DATABASE [Test] TO DISK = N'F:\Backup\DB_完整数据库备份.bak' WITH NOFORMAT, INIT, NAME = N'DB_完整数据库备份', SKIP, REWIND, NOUNLOAD,STATS = 10 ; --每日备份 declare @name varchar(250) set @name='F:\Backup\BackupList\DB_'+ convert(varchar(50),getdate(),112)+'.bak' BACKUP DATABASE [Test] TO DISK = @name WITH NOFORMAT, NOINIT, NAME = N'DB_每日数据库备份', SKIP, NOREWIND, NOUNLOAD,STATS = 10 ; --删除7日前的备份 declare @d Nvarchar(64); set @d = convert(varchar,dateadd(day,-7,getdate()),120); EXECUTE master.dbo.xp_delete_file 0,N'F:\Backup\BackupList',N'bak',@d;
《转发 推荐 点赞 收藏》
《《《 持续更新中..........》》》
