【sqlServer】常用语句整理
---------------------------------------------------------
--1:查询数据库中包含某字段的所有表名
--objName表名 根据xtpe='u'这个是查询表名
use BigData_ODS
GO
select object_name(id) objName,Name as 列名
from syscolumns
where (name like'字段名') and id in(select id from sysobjects where xtype='u')
order by objname
------------------------------------------------------------
--2:查看所有表对应的数据量
SELECT a.name AS 表名, MAX(b.rows) AS 记录条数
FROM sys.sysobjects AS a INNER JOIN
sys.sysindexes AS b ON a.id = b.id
WHERE (a.xtype = 'u')
GROUP BY a.name
ORDER BY 记录条数 DESC
------------------------------------------------------------
--3:查看数据库的总数据量
SELECT SUM(记录条数) AS 总记录数
FROM (SELECT TOP (10000) a.name AS 表名, MAX(b.rows) AS 记录条数
FROM sys.sysobjects AS a INNER JOIN
sys.sysindexes AS b ON a.id = b.id
WHERE (a.xtype = 'u')
GROUP BY a.name
ORDER BY 记录条数 DESC) AS t1
------------------------------------------------------------
--xtype可以是下列对象类型中的一种:
--C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 L = 日志 FN = 标量函数
--IF = 内嵌表函数 P = 存储过程 PK = PRIMARY KEY 约束(类型是 K) RF = 复制筛选存储过程
-- S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 UQ = UNIQUE 约束(类型是 K)
--V = 视图 X = 扩展存储过程
--type可以是下列值之一:
-- C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束
-- FN = 标量函数 IF = 内嵌表函数 K = PRIMARY KEY 或 UNIQUE 约束
-- L = 日志 P = 存储过程 R = 规则 RF = 复制筛选存储过程
--S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 V = 视图 X = 扩展存储过程
------------------------------------------------------------
--4:时间查询--
--获取今天的时间 0点0时0分
select DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))
--本月第一天 返回格式 yyyy-MM-dd
select Convert(char(10),dateadd(dd,-day(getdate())+1,getdate()) ,126)
--当前时间两小时前
--这个小时不支持0.5这种格式 可以用SELECT DATEADD(MINUTE,-30,GETDATE()) 替代
SELECT DATEADD(hour,-1, getdate())
--a. 本月的第一天 返回格式yyyy-MM-dd HH:mm:ss
select dateadd(mm, datediff(mm,0,getdate()), 0) AS 本月的第一天
--b. 本月的最后一天
select dateadd(ms,-3,dateadd(mm, datediff(mm,0,getdate())+1, 0)) AS 本月的最后一天
--c. 上个月的最后一天
select dateadd(ms,-3,dateadd(mm, datediff(mm,0,getdate()), 0)) AS 上个月的最后一天
--d. 本周的星期一
select dateadd(wk, datediff(wk,0,getdate()), 0) AS 本周的星期一
--e. 本季度的第一天
select dateadd(qq, datediff(qq,0,getdate()), 0) AS 本季度的第一天
--f. 本季度的最后一天
select DATEADD(ms,-3,dateadd(qq, datediff(qq,0,getdate())+1, 0)) AS 本季度的最后一天
--g. 本月的第一个星期一
select dateadd(wk, datediff(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0) AS 本月的第一个星期一
--h. 本年的第一天
select DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0) AS 本年的第一天
SELECT DATEDIFF(yy,0,GETDATE())
--i. 本年的最后一天
select dateadd(ms,-3,dateadd(yy, datediff(yy,0,getdate())+1, 0)) AS 本年的最后一天
--f. 去年的最后一天
select dateadd(ms,-3,dateadd(yy, datediff(yy,0,getdate()), 0)) AS 去年的最后一天
--计算哪一天是本周的星期一
Select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
--5:查询数据库的连接情况:(数据库SQL可直接执行) SELECT [dec].client_net_address, [des].[program_name], [des].[host_name], Count([dec].session_id) AS connection_count FROM sys.dm_exec_sessions AS [des] INNER JOIN sys.dm_exec_connections AS [dec] ON [des].session_id = [dec].session_id GROUP BY [dec].client_net_address, [des].[program_name], [des].[host_name] ORDER BY [des].[program_name], [dec].[client_net_address]
-:6:查看锁活动情况 select * from sys.dm_tran_locks
--8查看事务活动情况--要有权限 dbcc opentran
-:7查看事务当前隔离级别
DBCC USEROPTIONS
9:事务与锁
https://www.cnblogs.com/knowledgesea/p/3714417.html
以下数据来源地址:
https://mp.weixin.qq.com/s/4KxLdDw3phWvRJimBGpTZg
10:查看数据库缓存的SQL
use master
declare @dbid int
Select @dbid = dbid from sysdatabases
where name = 'SQL_ROAD'--修改成数据库的名称
select
dbid,UseCounts ,RefCounts,CacheObjtype,ObjType,
DB_Name(dbid) as DatabaseName,SQL
from syscacheobjects
where dbid=@dbid
order by dbid,useCounts desc,objtype
11:常用系统检测脚本
--查看内存状态 dbcc memorystatus --查看哪个引起的阻塞,blk EXEC sp_who active --查看锁住了那个资源id,objid EXEC sp_lock
还有如何查看查询分析器的SPID,可以在查询分析器的状态栏看到,比如sa(57),这就表示当前查询分析器SPID为57,这样在使用profile的时候就可以指定当前窗体进行监控。状态栏在查询窗口的右下角。
12:获取脚本执行时间
declare @timediff datetime select @timediff=getdate() select * from Suppliers(查询语句) print '耗时:'+ convert(varchar(10),datediff(ms,@timediff,getdate()))
13:连接远程数据库
select * from openrowset( 'SQLOLEDB', 'server=192.168.0.1;uid=sa;pwd=password', 'SELECT * FROM dbo.test')
--方法2: select * from openrowset( 'SQLOLEDB', '192.168.0.1'; 'sa'; 'password', 'SELECT * FROM dbo.test')
14:查询一个表内相同记录 HAVING
表数据结构
一个Id可以区分的时候
用title可以区分时:
select * from HR.Employees where title in ( select title from HR.Employees group by title having count(1)>1)
多个id才可以区分时:
用title和titleofcourtesy才能区分相同记录
select * from HR.Employees where title+titleofcourtesy in (select title+titleofcourtesy from HR.Employees group by title,titleofcourtesy having count(1)>1)
15:事务
有四个命令用于控制事务:
-
COMMIT:提交更改;
-
ROLLBACK:回滚更改;
-
SAVEPOINT:在事务内部创建一系列可以 ROLLBACK 的还原点;
-
SET TRANSACTION:命名事务
SAVEPOINT 是事务中的一个状态点,使得我们可以将事务回滚至特定的点,而不是将整个事务都撤销。
SAVEPOINT 命令的记录如下所示:
SAVEPOINT SAVEPOINT_NAME;
回滚至某一保存点的语法如下所示:
ROLLBACK TO SAVEPOINT_NAME;