常用SQL

日志查询

SELECT
    TOP 100 a.CreateTime AS '运行时间',
    --b.p_id,
    --a.Login_UserId AS '操作人id',
    a.Login_UserName AS '操作人姓名',
    b.p_remark AS 'sql方法',
    a.SqlName AS 'SQL名',
    a.SqlContent AS '运行SQL内容',
    a.ErrorPrompt AS '错误提示'
    --b.p_sql AS 'SQL',
    --a.Source AS '来源',
    --a.CreatePeople AS '主机名'
FROM
    System_Log AS a
LEFT JOIN PrintSql AS b ON b.p_name = a.SqlName
WHERE
    1 = 1
AND b.p_remark NOT IN ('未同步数据','查询session_zj')
AND DateDiff(dd, a.CreateTime, getdate()) = 0
--AND b.p_name LIKE '%insert_Order_OrderInfo_OneAndMany%'
ORDER BY
    a.CreateTime DESC;

SELECT COUNT(*) FROM System_Log WHERE DateDiff(dd, CreateTime, getdate()) = 0;

日志查询

UPDATE [dbo].[PrintSql] SET [p_sql]=N' ' WHERE ([p_id]='999')

修改sql

创建表

--删除表
--DROP TABLE [dbo].[System_Code_InstallationPeriod]


--创建表
CREATE TABLE [dbo].[System_Code_InstallationPeriod] (
[Id] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Name] nvarchar(50) NULL  DEFAULT '默认值', 
[TypeId] INT NULL,
[Money] money NULL,
[CreatePeople] nvarchar(50) NULL ,
[CreateTime] datetime NULL ,
[ModifyPeople] nvarchar(50) NULL ,
[ModifyTime] datetime NULL 
)

--添加表说明
EXECUTE sp_addextendedproperty   N'MS_Description',N'安装时段表',N'user',N'dbo',N'table',N'System_Code_InstallationPeriod',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'System_Code_InstallationPeriod',N'column',N'Name'


--添加字段说明
EXECUTE   sp_addextendedproperty   N'MS_Description',N'【创建人】',N'user',N'dbo',N'table',N'System_Code_InstallationPeriod',N'column',N'CreatePeople'
EXECUTE   sp_addextendedproperty   N'MS_Description',N'【创建时间】',N'user',N'dbo',N'table',N'System_Code_InstallationPeriod',N'column',N'CreateTime'
EXECUTE   sp_addextendedproperty   N'MS_Description',N'【修改人】',N'user',N'dbo',N'table',N'System_Code_InstallationPeriod',N'column',N'ModifyPeople'
EXECUTE   sp_addextendedproperty   N'MS_Description',N'【修改时间】',N'user',N'dbo',N'table',N'System_Code_InstallationPeriod',N'column',N'ModifyTime'

--删除字段说明
--EXEC sp_dropextendedproperty N'MS_Description', 'user','dbo', 'table', '表名', 'column','字段名'

创建表

创建表

alter table Personnel_Position ADD ProjectId INT NULL;
alter table Personnel_Position ADD ProjectName nvarchar(50) NULL;

--添加字段说明
EXECUTE   sp_addextendedproperty   N'MS_Description',N'【项目名称】',N'user',N'dbo',N'table',N'Personnel_Position',N'column',N'ProjectId';
EXECUTE   sp_addextendedproperty   N'MS_Description',N'【项目名称】',N'user',N'dbo',N'table',N'Personnel_Position',N'column',N'ProjectName';

aaa_select_table 'Personnel_Position';

追加字段

ALTER TABLE [表名] DROP COLUMN [字段名]

删除字段

sql 增删改查分页语句

--insert_System_Userinfo_Staff
--增加账户（职员）表_zj


BEGIN TRY 
IF((SELECT COUNT(*) FROM [dbo].[System_Userinfo_Staff] WHERE Name = '{Name}')=0)
 BEGIN
  insert [System_Userinfo_Staff] values('{Name}','{CreatePeople}',GETDATE(),'','')
  IF((SELECT @@ROWCOUNT)=1)
     SELECT ResponseNum=1,Message='保存成功'
  ELSE 
     SELECT ResponseNum=0,Message='保存失败'
 END
ELSE
 BEGIN
  SELECT ResponseNum=0,Message='名称重复'
 END
END TRY
BEGIN CATCH 
  SELECT ResponseNum=0,Message=ERROR_MESSAGE()
END CATCH

增加

--删除账户（职员）表_zj
--delete_System_Userinfo_Staff

BEGIN TRY 
 delete [System_Userinfo_Staff] where Id = {Id}
 IF((SELECT @@ROWCOUNT)=1)
    SELECT ResponseNum=1,Message='删除成功'
 ELSE 
    SELECT ResponseNum=0,Message='删除失败'
END TRY
BEGIN CATCH 
  SELECT ResponseNum=0,Message=ERROR_MESSAGE()
END CATCH

删除

--修改账户（职员）表_zj
--update_System_Userinfo_Staff


BEGIN TRY 
IF((SELECT COUNT(*) FROM[dbo].[System_Userinfo_Staff] WHERE UserName = '{UserName}' AND Id != {Id})=0)
 BEGIN
   update [System_Userinfo_Staff] set UserName = '{UserName}',ModifyPeople = '{ModifyPeople}',ModifyTime = GETDATE() where Id = {Id}
   IF((SELECT @@ROWCOUNT)=1)
     SELECT ResponseNum=1,Message='修改成功'
   ELSE 
     SELECT ResponseNum=0,Message='修改失败'
 END
ELSE
 BEGIN
   SELECT ResponseNum=0,Message='账户重复'
 END
END TRY
BEGIN CATCH 
  SELECT ResponseNum=0,Message=ERROR_MESSAGE()
END CATCH

修改

--page_System_Userinfo_Staff
--查询账户（职员）表(分页)

if (exists (select * from sys.objects where name = 'page_System_Userinfo_Staff')) drop proc page_System_Userinfo_Staff   --判断存储过程是否存在，存在则删除然后重建。
go


create proc page_System_Userinfo_Staff  --创建存储过程 
 @pagesize INT,
 @pageindex INT,
 @Name nvarchar (50),
 @RoleId nvarchar (50),
 @IsEnable nvarchar (50)
AS
BEGIN
    --开始
DECLARE @pagebefore INT ;
DECLARE @pagerear INT ;
DECLARE @condition nvarchar (2000) ;
SET @pagebefore =@pagesize *@pageindex ;
SET @pagerear =@pagebefore +@pagesize ;
SET @condition = ' where 1=1 ' ;
IF (@Name <> '')
SET @condition =@condition + ' and UserName like ''%' +@Name + '%''  OR [StaffName] LIKE ''%' +@Name + '%''' ;
IF (@RoleId <> '')
SET @condition =@condition + ' and RoleId = ' +@RoleId + '';
IF (@IsEnable <> '')
SET @condition =@condition + ' and IsEnable = ' +@IsEnable + '';
EXEC (
    '
    declare @table table(
    iid int identity,
  Id INT,
  UserName nvarchar(50),
  UserPassword nvarchar(50),
  StaffId INT,
  StaffName nvarchar(50),
  RoleId INT,
  RoleName nvarchar(50),
  CityId INT,
  CityName nvarchar(50),
  IsEnable  INT,
  CreatePeople nvarchar(50),
  CreateTime datetime,
  ModifyPeople nvarchar(50),
  ModifyTime datetime
    )
    insert @table
    select * from System_Userinfo_Staff ' +@condition + ' and RoleId <> 1 order by Id desc
    select * from @table where iid>' +@pagebefore + ' and iid<=' +@pagerear + '
    select count(*) as rows from @table;'
) ;
END ; --结束

分页

--查询账户（职员）表_zj
--select_System_Userinfo_Staff

select * from System_Userinfo_Staff

查询

触发器

--查看当前库中所有的触发器和与之相对应的表：
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';

查看当前库中所有的触发器和与之相对应的表

--Inserted表有数据，Deleted表无数据 

CREATE trigger INSERT_PrintSql
ON PrintSql 
FOR INSERT
As
INSERT into PrintSql_Log SELECT inserted.p_name, inserted.p_sql,inserted.p_remark,'增加',(SELECT client_net_address FROM sys.dm_exec_connections WHERE Session_id =@@spid),GETDATE() from inserted

插入操作（Insert）

--Inserted表无数据，Deleted表有数据 


CREATE trigger DELETE_PrintSql
ON PrintSql 
FOR DELETE
As
INSERT into PrintSql_Log SELECT Deleted.p_name, Deleted.p_sql,Deleted.p_remark,'删除',(SELECT client_net_address FROM sys.dm_exec_connections WHERE Session_id =@@spid),GETDATE() from Deleted

删除操作（Delete）

--Inserted表有数据（新数据），Deleted表有数据（旧数据）

CREATE trigger UPDATE_PrintSql
ON PrintSql 
FOR UPDATE
As
INSERT into PrintSql_Log SELECT inserted.p_name, inserted.p_sql,inserted.p_remark,'修改',(SELECT client_net_address FROM sys.dm_exec_connections WHERE Session_id =@@spid),GETDATE() from inserted

更新操作（Update）

查询表详情

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,
标识=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,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case   when   a.isnullable=1   then   '√'else   ''   end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
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
--where   d.name='Exam_Options'         --如果只查询指定表,加上此条件
order   by   a.id,a.colorder

查询表详情

循环插入

--声明变量
DECLARE @i int   
DECLARE @iCount int   
--设置变量
SET @i = 1
SET @iCount = (SELECT COUNT(*) FROM [dbo].[Personnel_Staff] WHERE  DepartmentId = 2)
--循环
WHILE @i<= @iCount
BEGIN
--查询符合的职员信息加入临时表
  SELECT row_number () OVER (ORDER BY Id) AS NumberId,* INTO #Temp FROM [dbo].[Personnel_Staff] WHERE  DepartmentId = 3 ORDER BY Id ASC 
--声明变量
  DECLARE @StaffId INT
  DECLARE @StaffName nvarchar (50)
--设置变量
  SET @StaffId = (SELECT Id FROM #Temp WHERE NumberId = @i)
  SET @StaffName = (SELECT Name FROM #Temp WHERE NumberId = @i)
--删除临时表
  DROP TABLE #Temp
--数据操作
  INSERT [System_Message] VALUES('1','1111',@StaffId,@StaffName,'PC端','1','【消息标题】','【消息内容】',0,'测试',GETDATE(),NULL,NULL)
--循环变量增加1
  SET @i = @i + 1 
END

循环插入

事务

BEGIN try
BEGIN TRANSACTION 
  --修改订单状态
  UPDATE [Order_OrderInfo] SET OrderStatusId = '13',    /*【订单状态Id】*/ OrderStatusName = '异常-转异常',    /*【订单状态名称】*/WHERE Id = {Id}
  --插入订单操作记录
  INSERT into Order_Operating_Record SELECT *,'13','异常-转异常','{Login_UserId}','{Login_UserName}',GETDATE(),{Id},'{OperationRemarks}' from Order_OrderInfo WHERE Id = {Id}
  SELECT ResponseNum = 1,Message = '保存成功' 
  COMMIT TRANSACTION  --事务已经成功执行，提交事务。
END try
BEGIN catch 
  SELECT ResponseNum = 0,Message = ERROR_MESSAGE() 
  ROLLBACK TRANSACTION --数据处理过程中出错，回滚到没有处理之前的数据状态
END catch


/******************************************************************************************/
/******************************************************************************************/
/******************************************************************************************/

--开启事务
begin tran
--错误扑捉机制，看好啦，这里也有的。并且可以嵌套。
begin try  
   --语句正确
     UPDATE [erpnew].[dbo].[System_MenuJurisdiction] SET [MenuId]='11' WHERE ([Id]='1')
   --MenuId为int类型，出错
     UPDATE [erpnew].[dbo].[System_MenuJurisdiction] SET [MenuId]='1w' WHERE ([Id]='1')
   --语句正确
     UPDATE [erpnew].[dbo].[System_MenuJurisdiction] SET [MenuId]='13' WHERE ([Id]='1')
end try
begin catch
   SELECT ResponseNum=0,Message=ERROR_MESSAGE()
   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
      rollback tran  ---数据处理过程中出错，回滚到没有处理之前的数据状态
end catch
if(@@trancount>0)
commit tran  --事务已经成功执行，数据已经处理妥当。
SELECT ResponseNum=1,Message='保存成功'

事务

保存从存储过程返回的结果集

create table #helpuser
(
UserName nvarchar(128),
RoleName nvarchar(128),
LoginName nvarchar(128),
DefDBName nvarchar(128),
DefSchemaName nvarchar(128),
UserID smallint,
SID smallint
)

insert into #helpuser exec sp_helpuser

select * from #helpuser
--删除临时表
drop Table #helpuser

保存从存储过程返回的结果集

其他

SELECT IDENTITY(INT,1,1) AS Id,items AS 'NavigationId' INTO #temp1 FROM Intercept ('1,2,3,4,',',')

SELECT IDENTITY(INT,1,1) AS Id,items AS 'JurisdictionId' INTO #temp2 FROM Intercept ('1,2,3,4,&1,2,7,&1,3,&1,7,','&')

SELECT a.NavigationId,b.JurisdictionId INTO #Temp3 FROM #temp1 AS a LEFT JOIN #temp2 AS b ON a.Id = b.Id;


WITH testtb2 AS 
(
SELECT A.NavigationId,A.JurisdictionId,CHARINDEX(',', A.JurisdictionId) AS STA, CHARINDEX(',', A.JurisdictionId)-1 AS LENS
FROM #Temp3 AS A 
UNION ALL
SELECT NavigationId,JurisdictionId, CHARINDEX(',', JurisdictionId, STA + 1) AS STA, CHARINDEX(',',JurisdictionId,STA+1)-STA-1 AS LENS
FROM testtb2 AS testtb2_2
WHERE (STA <> 0)
)
SELECT TOP (100) PERCENT SUBSTRING(JurisdictionId, STA - LENS, LENS) AS OrderRequest,NavigationId
FROM testtb2 AS testtb2_1
WHERE (STA <> 0)
ORDER BY JurisdictionId


DROP TABLE #Temp1
DROP TABLE #Temp2
DROP TABLE #Temp3

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 @@IDENTITY --得到的是跨域最新id

SELECT IDENT_CURRENT('table_name')--得到的是指定表的最新id

SELECT SCOPE_IDENTITY() --得到的是当前数据库中的最后插入表的id




例子：

数据说明：

database1_table:table3

database2_table:table1/table2

以此在所属数据库下执行以下语句：

insert table1(cname) values('ceshi');--假设结果 1
insert table2(lcid) values('qwe');--假设结果 3

insert table3(departmentmark) values('测试');--假设结果 10

然后回到database2下：

select  @@IDENTITY，跨域，所以得到10
select ident_current('table1')，不跨域，返回指定表的最后的id，插入得到结果1
select SCOPE_IDENTITY() 不跨域，但是未指定表，所以得到结果本数据库的最后插入的id，3

获取SQL增加的id

INSERT INTO 表名称 VALUES (值1, 值2,....)

INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....)

INSERT INTO 表名称 (列1, 列2,...) SELECT 值1, 值2,.... FROM  表名称

增加数据

--创建序号插入临时表
select row_number () OVER (ORDER BY Id) AS NumberId,* INTO #Temp FROM [dbo].[Personnel_Staff]  ORDER BY Id ASC 
--查询第二条数据
select * from #Temp where NumberId =2
--删除临时表
drop Table #Temp

查询第几条

SELECT ltrim(floor(rand()*10000))

随机数

SELECT row_number () OVER (ORDER BY Id) AS '序号' ,* FROM [dbo].[System_Code_OrderSource]

序号

--2018-11-13
SELECT CONVERT(varchar(50) , GETDATE(), 23 ) AS 'TIME'

--2018-11-13 18:46:14
SELECT CONVERT(varchar(50) , GETDATE(), 20 ) AS 'TIME'

--2019-01-14 17:15:59.920
select convert(varchar(50),GETDATE(),121)

时间转字符串

SELECT
    CONVERT (VARCHAR(4),YEAR(GETDATE())) AS '年',
    CONVERT (VARCHAR(4),MONTH(GETDATE())) AS '月',
    CONVERT (VARCHAR(4),DAY(GETDATE())) AS '日'

年月日

SELECT insert_id =@@IDENTITY

查询插入的id

select * from System_Code_OrderSource a where a.id=(select max(id) from System_Code_OrderSource)

查询表最新的一条数据

SELECT 
CASE Sex 
WHEN 0 THEN '女'
WHEN 1 THEN '男'
ELSE ''
END AS '性别'
FROM
    System_Userinfo_Staff


SELECT
CASE
WHEN w_grade >= 90 THEN'A'
WHEN w_grade >= 80 AND w_grade <= 89 THEN 'B'
WHEN w_grade >= 70 AND w_grade <= 79 THEN 'C'
WHEN w_grade >= 60 AND w_grade <= 69 THEN 'D'
ELSE 'E'
END AS '成绩评定'
FROM
    tb_gradego

条件判断

select * from xi a where (a.username) in  (select username from xi group by username  having count(*) > 1)

查询重复数据

SELECT
    *,
(select TOP 1 aa.Id from System_Code_OrderSource aa where aa.Id<a.Id ORDER BY aa.Id DESC) AS 上一篇id,
(select TOP 1 aa.Id from System_Code_OrderSource aa where aa.Id>a.Id ORDER BY aa.Id) AS 下一篇id
FROM
    [dbo].[System_Code_OrderSource] AS a
WHERE
    Id = 5

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

SELECT
    host_id() AS '主机端口号',
    host_name() AS '主机名',
  @@SERVERNAME AS '服务器的名称',
    client_net_address '客户端IP',
    local_net_address '服务器的IP'
FROM
    sys.dm_exec_connections
WHERE
    Session_id =@@spid

主机端口号主机名服务器的名称客户端IP 服务器的IP

--20190306094000
select convert(varchar(8),GETDATE(),112)+replace(convert(varchar(8),GETDATE(),114),':','')

--20190306094332437
select convert(varchar(8),GETDATE(),112)+replace(convert(varchar(12),GETDATE(),14),':','')

--当月天数
select day(dateadd(ms,-3,DATEADD(m, DATEDIFF(m,0,getdate())+1,0)))

---当月第一天
select dateadd(d,-day(getdate())+1,getdate())

---当月最后一天
select dateadd(d,-day(getdate()),dateadd(m,1,getdate()))

--明年上月的最后一天此时
select dateadd(d,-day(getdate()),dateadd(m,12,getdate()))

--当月第一个星期一
SELECT DATEADD(wk, DATEDIFF(wk, '', DATEADD(dd, 6 - DAY(getdate()), getdate())), '')

--今天的所有数据
select * from System_Log where DateDiff(dd,CreateTime,getdate())=0

--昨天的所有数据
select * from System_Log where DateDiff(dd,CreateTime,getdate())=1

--7天内的所有数据
select * from System_Log where DateDiff(dd,CreateTime,getdate())<=7

--30天内的所有数据
select * from System_Log where DateDiff(dd,CreateTime,getdate())<=30

--本月的所有数据：
select * from System_Log where DateDiff(mm,CreateTime,getdate())=0

--本年的所有数据
select * from System_Log where DateDiff(yy,CreateTime,getdate())=0

--查询今天是今年的第几天
select datepart(dayofyear,getDate())

--查询今天是本月的第几天
select datepart(dd, getDate())  
select day(getDate())

--查询本周的星期一日期是多少 （注意：指定日期不能是周日，如果是周日会计算到下周一去。所以如果是周日要减一天）
SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),0)

--查询昨天日期
select convert(char,dateadd(DD,-1,getdate()),121) 

--查询本月第一天日期
Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) as firstday

--查询本月最后一天日期
Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) as lastday 

--本月有多少天
select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast((cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' ) as datetime ))))

--求两个时间段相差几天
select datediff(day,'2016/5/17','2016/8/20') as daysum

--在指定的日期上±N天
select convert(char,dateadd(dd,1,getdate()),111) as riqi    

--在指定的日期上±N分钟：//查询当前时间15分钟之前的日期
select dateadd(mi,-15,getdate())

时间相关

SELECT CONVERT (VARCHAR(50), dateadd(dd,1,getdate()), 23)

查询明天

--查看执行时间和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

优化查询

set language N'Simplified Chinese'
select datename(weekday, getdate())
select datename(dw, getdate())

星期几

SELECT STUFF((SELECT',' + CONVERT (VARCHAR, aa.Id)FROM[dbo].[System_Code_City] AS aa FOR XML PATH ('')),1,1,'') AS Id 

declare @ids varchar(1000)
set @ids='1,2,3,4'
select * from System_Code_City where charindex(','+ltrim(ProjectId)+',',','+@ids+',')>0

1,2,3

UPDATE [表名] SET [字段] = replace([字段], '替换前内容', '替换后内容');

替换

DROP TABLE 表名称  --删除表
TRUNCATE TABLE 表名称  --清空表中的数据
DELETE FROM 表名称  WHERE 列名称 = 值  --删除表中的数据

删除/清空表数据

复制表结构及数据到新表

select * into 目标表名 from 源表名（要求目标表不存在，因为在插入时会自动创建）

只复制表结构到新表

select * into 目标表名 from 源表名 WHERE 1=2 即:让WHERE条件不成立.

复制表

--查看被锁表：
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)

查看被锁表/解锁

--Union可以对两个或多个结果集进行连接，形成“并集”。子结果集所有的记录组合在一起形成新的结果集。

SELECT Name FROM Person_1
UNION
SELECT Name FROM Person_2


--Except可以对两个或多个结果集进行连接，形成“差集”。返回左边结果集合中已经有的记录，而右边结果集中没有的记录。

SELECT Name FROM Person_1
EXCEPT
SELECT Name FROM Person_2


--InterSect可以对两个或多个结果集进行连接，形成“交集”。返回左边结果集和右边结果集中都有的记录。

SELECT Name FROM Person_1
INTERSECT
SELECT Name FROM Person_2

SQL 交/并/差

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

sql 发送邮件

“因为数据库正在使用，所以无法获得对数据库的独占访问权”，终解决方案如下

关键SQL语句：
ALTER DATABASE [datebase] SET OFFLINE WITH ROLLBACK IMMEDIATE

用完之后再
ALTER database [ datebase] set online

常用SQL

推荐阅读