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

# 字符处理

--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

# 条件

-- 时间
DateDiff(mm,fjsj,getdate())=0  --本月

 GETDATE() between [KaiShi] and [JieShu] --这两个时间内,注意结束时间是0点

1
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

# SQL优化

# 查看当前连接

select * from master.dbo.sysprocesses order by Blocked desc, waittime  desc

# 当前正在执行的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

# 查看耗时长

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

# 重建索引


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

上次更新: 2022/04/26, 16:00:33

← C#基础 olap发布到IIS→