MSSQL
# 数据库SQL
# SQL语句
# 基础查询
select * from table ;
select count(distinct name) from table;
--不重复
select name from table group by name;
--2014及以后的分页
-- overall_count:总行数 、 从0开始总返回10条、 order by 语句必须存在
select *, overall_count = COUNT(1) OVER() from table order by name offset 0 rows fetch next 10 rows only;
--得到自增长ID
select @@IDENTITY
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 字符处理
--SQL 小数保留2位
Convert(decimal(18,2),num)
--判断
case when (sum([kaohejine])>0) then 0 else 1 end
--时间格式
CONVERT(varchar(12) , getdate(), 112 ) --20200606
CONVERT(varchar(12) , getdate(), 23 ) --yyyy-mm-dd
YEAR(GETDATE())--年
MONTH(GETDATE())--月
DAY(GETDATE())--日
DATEADD(MONTH, -1, GETDATE()) --增加-1月
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# 条件
-- 时间
DateDiff(mm,fjsj,getdate())=0 --本月
GETDATE() between [KaiShi] and [JieShu] --这两个时间内,注意结束时间是0点
1
2
3
4
5
2
3
4
5
# SQL操作
--修改
UPDATE [NBWanS_XueXiJiLu] SET [Company] = '' WHERE id=3;
DELETE FROM [NBWanS_XueXiJiLu] WHERE id=3;
--修改字段名
alter table 表名 rename column A to B
--修改字段类型
alter table 表名 alter column 字段名 type -- not null
--增加字段
alter table 表名 add 字段名 type -- not null default 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
# SQL优化
# 查看当前连接
select * from master.dbo.sysprocesses order by Blocked desc, waittime desc
1
# 当前正在执行的SQL
SELECT spid,
blocked,
DB_NAME(sp.dbid) AS DBName,
program_name,
waitresource,
lastwaittype,
sp.loginame,
sp.hostname,
a.[Text] AS [TextData],
SUBSTRING(A.text, sp.stmt_start / 2,
(CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text) ELSE sp.stmt_end
END - sp.stmt_start) / 2) AS [current_cmd]
FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A
WHERE spid > 50
ORDER BY blocked DESC, DB_NAME(sp.dbid) ASC, a.[text];
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 查看耗时长
SELECT TOP 10[session_id],[request_id],
[start_time] AS '开始时间',[status] AS '状态',[command] AS '命令',
dest.[text] AS 'sql语句',
DB_NAME([database_id]) AS '数据库名',[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',[wait_time] AS '等待时间',[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',[writes] AS '写次数',[logical_reads] AS '逻辑读次数',[row_count] AS '返回结果行数'FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]>50 ORDER BY [cpu_time] DESC
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# 重建索引
DBCC showcontig('xsfhdmx')
--如果结果中扫描密度小于70%时,或者逻辑扫描碎片大于30%时,即有必要去重建索引了。当然70%和30%只是参考值
--闲时执行
EXEC Sp_msforeachtable
@command1="print '?' DBCC DBREINDEX ('?', '', 90)"
go
EXEC Sp_updatestats
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
上次更新: 2022/04/26, 16:00:33