经典常用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','字段名'

创建表

查询表结构【示例图】

--查询表结构

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;--结束

查询表结构

经典常用SQL语句大全

基本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 源表名（【复制表结构及数据】要求目标表不存在，因为在插入时会自动创建）

删除：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 [表名]

外连接

--外连接

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)，不消除重复行。

注：使用运算词的几个查询结果行必须是一致的。

经典常用SQL语句大全

常用查询

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'

\t\r\n等特殊字符

--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)

字符转换函数 ASCII()/CHAR()/LOWER()/UPPER()/STR()

--LTRIM() 把字符串头部的空格去掉。

SELECT LTRIM('  ABC')

--RTRIM() 把字符串尾部的空格去掉。

SELECT RTRIM('ABC  ')

去空格函数 LTRIM()/RTRIM()

--LEFT()  返回字符串左起*个字符。

SELECT LEFT('ABCDEFG',3)

--RIGHT()  返回字符串右起*个字符。

SELECT RIGHT('ABCDEFG',3)

--SUBSTRING()  返回从字符串左边第*个字符起*个字符的部分。

SELECT SUBSTRING('ABCDEFG',3,2)

取子串函数 LEFT()/RIGHT()/SUBSTRING()

--CHARINDEX()  返回字符串中某个指定的子串出现的开始位置。如果没有发现子串，则返回0 值。不能用于TEXT 和IMAGE 数据类型。

SELECT CHARINDEX('C','ABCDEFG')

--PATINDEX()  返回字符串中某个指定的子串出现的开始位置。可以使用通配符

SELECT PATINDEX('%B_D%','ABCDEFG')

字符串比较函数 CHARINDEX()/PATINDEX()

--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

字符串操作函数 QUOTENAME()/REPLICATE()/REVERSE()/REPLACE()/SPACE()/STUFF()

CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型

例：SELECT CAST('' AS int)

CONVERT() 函数是把日期转换为新数据类型的通用函数。可以用不同的格式显示日期/时间数据。

例：SELECT CONVERT(int, '')

例：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 时失败。

数据类型转换函数 CAST()/CONVERT()

AVG()      --返回的平均价值

COUNT()    --返回的总数

FIRST()    --返回第一个值

LAST()     --返回最后一个值

MAX()      --返回的最大值

MIN()      --返回最小值

TOTAL()    --返回总和

统计函数 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')

日期函数 DATEADD()/DATEDIFF()/DATEPART()

经典常用SQL语句大全

自定义函数

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+'',6)

END

--执行

SELECT dbo.ConvertHex10To36('')

10进制转36进制（可用于邀请码）

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 ('','')

查询两个日期之间的时间【年】

其他

CREATE UNIQUE NONCLUSTERED INDEX [索引名称] ON [dbo].[表名]([字段1],[字段2])

唯一非聚集索引

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 数值1 and 数值2

between 限制查询数据范围

select top 10 * from tablename order by newid()

随机取出10条数据

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

检测sql性能

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查询结果转Html table

--“因为数据库正在使用，所以无法获得对数据库的独占访问权”，

--终解决方案如下

--关键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

检测sql执行情况

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

TRY CATCH

BEGIN try

BEGIN TRANSACTION

  --语句正确

  UPDATE [Test] SET [MenuId]='' WHERE [Id]=''

  --MenuId为int类型，出错

  UPDATE [Test] SET [MenuId]='ABCD' WHERE [Id]=''

  --语句正确

  UPDATE [Test] SET [MenuId]='' WHERE [Id]=''

  SELECT ResponseNum = 1,Message = '保存成功'

  COMMIT TRANSACTION  --事务已经成功执行，提交事务。

END try

BEGIN catch

  SELECT ResponseNum = 0,Message = ERROR_MESSAGE()

  ROLLBACK TRANSACTION --数据处理过程中出错，回滚到没有处理之前的数据状态

END catch

事务

BACKUP DATABASE [数据库名] TO  DISK = N'C:\数据库名.bak' WITH NOFORMAT, INIT,  NAME = N'数据库名', SKIP, REWIND, NOUNLOAD,  STATS = 10

数据库备份

--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

分销

《《《持续更新中..........》》》

秒客网

经典常用SQL语句大全

相关文章