日志查询
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 aLEFT JOIN PrintSql AS b ON b.p_name = a.SqlNameWHERE 1 = 1AND 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')
创建表
--删除表--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--增加账户（职员）表_zjBEGIN 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='保存失败' ENDELSE BEGIN SELECT ResponseNum=0,Message='名称重复' ENDEND TRYBEGIN CATCH SELECT ResponseNum=0,Message=ERROR_MESSAGE()END CATCH --删除账户（职员）表_zj--delete_System_Userinfo_StaffBEGIN TRY delete [System_Userinfo_Staff] where Id = {Id} IF((SELECT @@ROWCOUNT)=1) SELECT ResponseNum=1,Message='删除成功' ELSE SELECT ResponseNum=0,Message='删除失败'END TRYBEGIN CATCH SELECT ResponseNum=0,Message=ERROR_MESSAGE()END CATCH --修改账户（职员）表_zj--update_System_Userinfo_StaffBEGIN 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='修改失败' ENDELSE BEGIN SELECT ResponseNum=0,Message='账户重复' ENDEND TRYBEGIN 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 --判断存储过程是否存在，存在则删除然后重建。gocreate proc page_System_Userinfo_Staff --创建存储过程 @pagesize INT, @pageindex INT, @Name nvarchar (50), @RoleId nvarchar (50), @IsEnable nvarchar (50)ASBEGIN --开始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_Staffselect * from System_Userinfo_Staff
触发器
--查看当前库中所有的触发器和与之相对应的表：SELECT tb2.name AS '对应的表', tb1.name AS '触发器', tb1.crdate AS '创建时间'FROM Sysobjects tb1JOIN Sysobjects tb2 ON tb1.parent_obj = tb2.idWHERE tb1.type = 'TR';--显示触发器的定义:EXEC sp_helptext 'UPDATE_Personnel_Staff_Changes';
--Inserted表有数据，Deleted表无数据 CREATE trigger INSERT_PrintSqlON PrintSql FOR INSERTAsINSERT 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
--Inserted表无数据，Deleted表有数据 CREATE trigger DELETE_PrintSqlON PrintSql FOR DELETEAsINSERT 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
--Inserted表有数据（新数据），Deleted表有数据（旧数据）CREATE trigger UPDATE_PrintSqlON PrintSql FOR UPDATEAsINSERT 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
查询表详情
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 aleft join systypes b on a.xusertype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'left join syscomments e on a.cdefault=e.idleft join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_idleft 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 = 1SET @iCount = (SELECT COUNT(*) FROM [dbo].[Personnel_Staff] WHERE DepartmentId = 2)--循环WHILE @i<= @iCountBEGIN--查询符合的职员信息加入临时表 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 tryBEGIN 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 tryBEGIN 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 trybegin catch SELECT ResponseNum=0,Message=ERROR_MESSAGE() if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务 rollback tran ---数据处理过程中出错，回滚到没有处理之前的数据状态end catchif(@@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_helpuserselect * from #helpuser--删除临时表drop Table #helpuser
其他
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 bWHERE a.is_ms_shipped = 0AND a.object_id = b.object_idAND a.[type] IN ('P', 'V', 'AF')ORDER BY a.[name] ASC SELECT @@IDENTITY --得到的是跨域最新idSELECT IDENT_CURRENT('table_name')--得到的是指定表的最新idSELECT SCOPE_IDENTITY() --得到的是当前数据库中的最后插入表的id例子：数据说明：database1_table:table3database2_table:table1/table2以此在所属数据库下执行以下语句：insert table1(cname) values('ceshi');--假设结果 1insert table2(lcid) values('qwe');--假设结果 3insert table3(departmentmark) values('测试');--假设结果 10然后回到database2下：select @@IDENTITY，跨域，所以得到10select ident_current('table1')，不跨域，返回指定表的最后的id，插入得到结果1select SCOPE_IDENTITY() 不跨域，但是未指定表，所以得到结果本数据库的最后插入的id，3 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-13SELECT CONVERT(varchar(50) , GETDATE(), 23 ) AS 'TIME'--2018-11-13 18:46:14SELECT CONVERT(varchar(50) , GETDATE(), 20 ) AS 'TIME'--2019-01-14 17:15:59.920select 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
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_StaffSELECTCASEWHEN 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.Ida.Id ORDER BY aa.Id) AS 下一篇idFROM [dbo].[System_Code_OrderSource] AS aWHERE Id = 5
BEGIN TRY SELECT 5 / 0END TRYBEGIN CATCH SELECT ERROR_MESSAGE() AS '错误描述', ERROR_NUMBER() AS '错误号', ERROR_SEVERITY() AS '严重级别', ERROR_STATE() AS '错误状态号', ERROR_LINE() AS '出错的行号', ERROR_PROCEDURE() AS '发生错误的存储过程名或触发器名'END CATCH
SELECT host_id() AS '主机端口号', host_name() AS '主机名', @@SERVERNAME AS '服务器的名称', client_net_address '客户端IP', local_net_address '服务器的IP'FROM sys.dm_exec_connectionsWHERE Session_id =@@spid
--20190306094000select convert(varchar(8),GETDATE(),112)+replace(convert(varchar(8),GETDATE(),114),':','')--20190306094332437select 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 onselect * from dbo.Productset statistics time off--查看查询对I/0的操作情况set statistics io onselect * from dbo.Productset 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 UPDATE [表名] SET [字段] = replace([字段], '替换前内容', '替换后内容');
DROP TABLE 表名称 --删除表TRUNCATE TABLE 表名称 --清空表中的数据DELETE FROM 表名称 WHERE 列名称 = 值 --删除表中的数据
--查看被锁表：SELECTrequest_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableNameFROMsys.dm_tran_locksWHEREresource_type = 'OBJECT' ORDER BY request_session_id ASC-- 解锁：DECLARE @spid INTSET @spid = 52 --锁表进程DECLARE @SQL VARCHAR (1000)SET @SQL = 'kill ' + CAST (@spid AS VARCHAR) EXEC (@SQL)
--Union可以对两个或多个结果集进行连接，形成“并集”。子结果集所有的记录组合在一起形成新的结果集。SELECT Name FROM Person_1UNIONSELECT Name FROM Person_2--Except可以对两个或多个结果集进行连接，形成“差集”。返回左边结果集合中已经有的记录，而右边结果集中没有的记录。SELECT Name FROM Person_1EXCEPTSELECT Name FROM Person_2--InterSect可以对两个或多个结果集进行连接，形成“交集”。返回左边结果集和右边结果集中都有的记录。SELECT Name FROM Person_1INTERSECTSELECT Name FROM Person_2
“因为数据库正在使用，所以无法获得对数据库的独占访问权”，终解决方案如下
关键SQL语句：
ALTER DATABASE [datebase] SET OFFLINE WITH ROLLBACK IMMEDIATE用完之后再
ALTER database [ datebase] set online