SQL SERVER 常用 SQL 函数

最新推荐文章于 2024-09-29 08:44:52 发布

weixin_34178244

最新推荐文章于 2024-09-29 08:44:52 发布

阅读量69

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/1179666/blog/729816

版权

2019独角兽企业重金招聘Python工程师标准>>>

时间格式化

select convert(date,'2016.08.09',23) ;  -- 格式化结果：2016-08-09
convert(varchar(100),CONVERT(date, '2016-5-16', 23),111)
select convert(varchar(100),CONVERT(date, '2016-5-16', 23),111);  -- 格式化结果：2016/05/16
select convert(date,'',23) ;  -- 格式化结果：1900-01-01
select convert(varchar(100),CONVERT(date, null, 23),111)； -- 格式化结果： ''
select convert(varchar(100),GETDATE(),20)；-- 格式化结果: '2016-08-17 13:30:27'
select dateadd(YYYY,1,getdate()); -- 格式化结果：加一年
select DateAdd(dd,-1,getdate());  -- 格式化结果：减一天

时间相减

select DATEDIFF(day,'2016-08-22 22:02:20.237',getDate()), 
DATEDIFF(day,'2016-08-22','2016-08-25') 当天为'2016-08-25'，结果为：3,3 #按天相减
select DATEDIFF(MINUTE, '2016-12-23 14:37:08', '2016-12-23 14:39:09'); 结果：3 #按分钟相减

四舍五入

select cast(round(12.5,2)   as  numeric(5,2));  -- 12.50
select cast(round(12.555,2)   as  numeric(5,2));  -- 12.56

查看数据库连接

sp_who:可以看到连接用户(hostname),登录名(loginname),数据库(dbname)
Select * from sys.dm_exec_connections; -- 可以查看当前有多少人连接（client_net_address：连接用户IP）
Select * from sys.dm_exec_connections where client_net_address = '192.168.0.151' -- 查看具体IP连接
Select * from sys.dm_exec_sessionswhere host_name = '计算机名' 
-- 可以看到有多少会话，一个连接可以有多个会话

使用指定值替换空

isnull(area_max,9999) #当为空时使用9999替换

查询和关闭锁表进程

select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName   
    from   sys.dm_tran_locks where resource_type='OBJECT'  -- 查看锁表进程
-- 关闭锁表进程
declare @spid  int 
    Set @spid  = 92 --锁表进程
    declare @sql varchar(1000)
    set @sql='kill '+cast(@spid  as varchar)
    exec(@sql)

查询当前执行SQL

SELECT  [Spid] = session_id ,  
            ecid ,  
            [Database] = DB_NAME(sp.dbid) ,  
            [User] = nt_username ,  
            [Status] = er.status ,  
            [Wait] = wait_type ,  
            [Individual Query] = SUBSTRING(qt.text,  
                                           er.statement_start_offset / 2,  
                                           ( CASE WHEN er.statement_end_offset = -1  
                                                  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))  
                                                       * 2  
                                                  ELSE er.statement_end_offset  
                                             END - er.statement_start_offset )  
                                           / 2) ,  
            [Parent Query] = qt.text ,  
            Program = program_name ,  
            hostname ,  
            nt_domain ,  
            start_time  
    FROM    sys.dm_exec_requests er  
            INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid  
            CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt  
    WHERE   session_id > 50 -- Ignore system spids.  
            AND session_id NOT IN ( @@SPID ) -- Ignore this current statement.  
ORDER BY    1,2

替换特殊字符

#替换回车符
REPLACE(detail, CHAR(13) , '<br>') 
#替换换行符
REPLACE(detail, CHAR(10), '<br>') 
#替换回车换行符
REPLACE(detail, CHAR(13) + CHAR(10), '<br>')

查询最大连接数

SELECT @@MAX_CONNECTIONS

查询每个数据的连接数

SELECT name ,count(*) FROM (
SELECT b.name,a.* FROM [Master].[dbo].[SYSPROCESSES] a 
INNER JOIN [Master].[dbo].[SYSDATABASES] b 
ON a.dbid=b.dbid
)t 
GROUP BY t.name

查询线程来源

select * from master..sysprocesses
spid--SQL Server进程的ID。
kpid--Microsoft Windows线程的ID。
dbid--当前正由进程使用的数据库ID。
dbcc inputbuffer(spid)  -- 查看正在执行的SQL
sp_who spid; --查看表名

找出执行时间最长的SQL

-- 所有SQL
SELECT top 10  
    (total_elapsed_time / execution_count)/1000 N'平均时间ms'  
    ,total_elapsed_time/1000 N'总花费时间ms'  
    ,total_worker_time/1000 N'所用的CPU总时间ms'  
    ,total_physical_reads N'物理读取总次数'  
    ,total_logical_reads/execution_count N'每次逻辑读次数'  
    ,total_logical_reads N'逻辑读取总次数'  
    ,total_logical_writes N'逻辑写入总次数'  
    ,execution_count N'执行次数'  
    ,creation_time N'语句编译时间'  
    ,last_execution_time N'上次执行时间'  
    ,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  
    ) N'执行语句'  
    ,qp.query_plan  
FROM  sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp  
WHERE  
    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  
    )   not like '%fetch%'  
ORDER BY  total_elapsed_time / execution_count DESC;  
-- 指定包含 表名的SQL
SELECT top 10  
    (total_elapsed_time / execution_count)/1000 N'平均时间ms'  
    ,total_elapsed_time/1000 N'总花费时间ms'  
    ,total_worker_time/1000 N'所用的CPU总时间ms'  
    ,total_physical_reads N'物理读取总次数'  
    ,total_logical_reads/execution_count N'每次逻辑读次数'  
    ,total_logical_reads N'逻辑读取总次数'  
    ,total_logical_writes N'逻辑写入总次数'  
    ,execution_count N'执行次数'  
    ,creation_time N'语句编译时间'  
    ,last_execution_time N'上次执行时间'  
    ,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  
    ) N'执行语句'  
    ,qp.query_plan  
FROM  sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp  
WHERE  
    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  
    )   like '%se_user%'
ORDER BY  total_elapsed_time / execution_count DESC;

转载于:https://my.oschina.net/u/1179666/blog/729816