Notepad++

行前行尾添加字符:

  1. 按Ctrl + H 打开替换窗口
  2. 查找模式选择正则表达式
  3. 输入 ^ 行前添加字符串
  4. 输入 $ 行尾添加字符串

删除包含某字符串的行:

  1. 按Ctrl + H 打开替换窗口
  2. 查找模式选择正则表达式
  3. 查找目标输入.*DROP.*,DROP是想替换的关键词
  4. 全部替换

删除空白行:

  1. 按Ctrl + H 打开替换窗口
  2. 查找模式选择正则表达式
  3. 查找目标输入^\s
  4. 全部替换

下划线转驼峰:

  1. 按Ctrl + H 打开替换窗口
  2. 查找模式选择正则表达式
  3. 查找目标输入_([a-z])
  4. 替换为\U\1\E
  5. 全部替换

SQL

操作前的判断

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
#创建表前的判断
USE [db_test]
GO

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE type = 'U' AND name = 'table_name')
BEGIN
DROP TABLE [dbo].[table_name]
END

#创建存储过程前的判断
USE [db_test]
GO

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp_name]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE [dbo].[sp_name]
END
GO

#添加表字段前的判断
USE [db_test]
GO

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[table_name]') AND name = 'column_name')
BEGIN
ALTER TABLE dbo.table_name ADD column_name char(2) NULL
END
GO

#创建触发器前的判断
USE [db_test]
GO

IF OBJECT_ID ('dbo.trigger_name', 'TR') IS NOT NULL
BEGIN
DROP TRIGGER [dbo].[trigger_name]
END
GO

#创建索引前的判断
USE [db_test]
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE id = OBJECT_ID('table_name') AND name = 'index_name')
BEGIN
DROP INDEX table_name.index_name
END
GO

索引的操作

1
2
3
4
5
6
7
8
9
10
11
# 删除主键
alter table db_test.table_name drop constraint index_name

# 删除普通索引
DROP index index_name on db_test.table_name

# 新建唯一索引
CREATE UNIQUE INDEX index_name ON db_test.table_name (seq)

# 新建主键
alter table db_test.table_name add constraint index_name PRIMARY key(seq)

列的操作

1
2
3
4
5
6
7
8
9
10
11
# 重命名列名,索引中的列名会自动更改
EXEC sp_rename 'db_test.table_name.id_type','id_type_before','COLUMN'

# 更改列的类型
ALTER TABLE db_test.table_name ALTER COLUMN id_type_before char(24) NULL

# 新增列
ALTER TABLE db_test.table_name ADD id_type_before varchar(10) NULL

# 删除列
ALTER TABLE db_test.table_name DROP COLUMN id_type_before

获取下一个sequence

1
2
3
4
5
6
7
#sql server
declare @tm_new_detail_id numeric(20, 0)
SELECT @tm_new_detail_id = CONVERT(numeric(20, 0), NEXT VALUE FOR db_test.seq_name)

#SYBASE
declare @new_cust_detail_id unsigned bigint
select @new_cust_detail_id = convert(unsigned bigint, reserve_identity('seq_name',1))

查看表锁级别

select lockscheme('db_test..table_name')

查包含某字符串的存储过程

select distinct object_name(id) from syscomments where id in(select id from sysobjects where type ='P') and text like '%find_string%'

查看不包含数字

select * from customer a where PATINDEX('%[0-9]%', a.name) = 0

查询SQL执行时CPU的占用情况

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH AggregatedCPU AS (SELECT q.query_hash, SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec, SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions) AS avg_cpu_millisec, MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec, MIN(rs.min_cpu_time / 1000.00) AS min_cpu_millisec,MAX(max_logical_io_reads) max_logical_reads, COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans, COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids, SUM(CASE WHEN rs.execution_type_desc='Aborted' THEN count_executions ELSE 0 END) AS Aborted_Execution_Count, SUM(CASE WHEN rs.execution_type_desc='Regular' THEN count_executions ELSE 0 END) AS Regular_Execution_Count, SUM(CASE WHEN rs.execution_type_desc='Exception' THEN count_executions ELSE 0 END) AS Exception_Execution_Count, SUM(count_executions) AS total_executions, MIN(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=rs.runtime_stats_interval_id
WHERE rs.execution_type_desc IN ('Regular', 'Aborted', 'Exception')AND rsi.start_time>='2022-05-09 06:00:00' --AND rsi.start_time<='2021-10-28 05:00:00'
GROUP BY q.query_hash), OrderedCPU AS (SELECT query_hash, total_cpu_millisec, avg_cpu_millisec, max_cpu_millisec, min_cpu_millisec,max_logical_reads, number_of_distinct_plans, number_of_distinct_query_ids, total_executions, Aborted_Execution_Count, Regular_Execution_Count, Exception_Execution_Count, sampled_query_text, ROW_NUMBER() OVER (ORDER BY total_cpu_millisec DESC, query_hash ASC) AS RN
FROM AggregatedCPU)
SELECT OD.query_hash, OD.total_cpu_millisec, OD.avg_cpu_millisec, OD.max_cpu_millisec, OD.min_cpu_millisec,OD.max_logical_reads, OD.number_of_distinct_plans, OD.number_of_distinct_query_ids, OD.total_executions, OD.Aborted_Execution_Count, OD.Regular_Execution_Count, OD.Exception_Execution_Count, OD.sampled_query_text, OD.RN
FROM OrderedCPU AS OD
WHERE OD.RN<=100 -- and sampled_query_text like '%VS9251B%'
ORDER BY total_cpu_millisec DESC;

触发器记录数据的IUD操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
#创建日志表
USE [db_test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF exists (select * from sysobjects where type = 'U' and name = 'table_name')
BEGIN
DROP TABLE [dbo].[table_name]
END


CREATE TABLE [dbo].[table_name_log]
(
[file_content] [char](150) NOT NULL,
[spid] int NULL,
[prog_name] [varchar](100) NULL,
[user_name] [varchar](100) NULL,
[hostname] varchar(100) NULL,
[ip_address] varchar(100) NULL,
[action_type] [varchar](40) NULL,
[update_time] [datetime] NULL
)
GO

#创建触发器
USE [db_test]
GO

IF OBJECT_ID ('dbo.trigger_name', 'TR') IS NOT NULL
BEGIN
DROP TRIGGER [dbo].[trigger_name]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[trigger_name]
ON [dbo].[table_name] FOR UPDATE,INSERT,DELETE
AS
BEGIN

declare @op varchar(10)
select @op=case when exists(select 1 from inserted) and exists(select 1 from deleted)
then 'Update'
when exists(select 1 from inserted) and not exists(select 1 from deleted)
then 'Insert'
when not exists(select 1 from inserted) and exists(select 1 from deleted)
then 'Delete' end

if @op = 'Update'
begin
insert into [dbo].[table_name_log]
(file_content,spid,prog_name,user_name,hostname,ip_address,action_type,update_time)
select file_content,
@@spid,
(select program_name as prog_name from sys.dm_exec_sessions where session_id=@@spid),
(select login_name as user_name from sys.dm_exec_sessions where session_id=@@spid),
(select hostname as hostname from sys.sysprocesses where spid=@@spid),
(select client_net_address as ip_address from sys.dm_exec_connections where session_id=@@spid),
'Before Update',
getdate()
from deleted

insert into [dbo].[table_name_log]
(file_content,spid,prog_name,user_name,hostname,ip_address,action_type,update_time)
select file_content,
@@spid,
(select program_name as prog_name from sys.dm_exec_sessions where session_id=@@spid),
(select login_name as user_name from sys.dm_exec_sessions where session_id=@@spid),
(select hostname as hostname from sys.sysprocesses where spid=@@spid),
(select client_net_address as ip_address from sys.dm_exec_connections where session_id=@@spid),
'After Update',
getdate()
from inserted
end
else if @op = 'Delete'
begin
insert into [dbo].[table_name_log]
(file_content,spid,prog_name,user_name,hostname,ip_address,action_type,update_time)
select file_content,
@@spid,
(select program_name as prog_name from sys.dm_exec_sessions where session_id=@@spid),
(select login_name as user_name from sys.dm_exec_sessions where session_id=@@spid),
(select hostname as hostname from sys.sysprocesses where spid=@@spid),
(select client_net_address as ip_address from sys.dm_exec_connections where session_id=@@spid),
@op,
getdate()
from deleted
end
else
begin
insert into [dbo].[table_name_log]
(file_content,spid,prog_name,user_name,hostname,ip_address,action_type,update_time)
select file_content,
@@spid,
(select program_name as prog_name from sys.dm_exec_sessions where session_id=@@spid),
(select login_name as user_name from sys.dm_exec_sessions where session_id=@@spid),
(select hostname as hostname from sys.sysprocesses where spid=@@spid),
(select client_net_address as ip_address from sys.dm_exec_connections where session_id=@@spid),
@op,
getdate()
from inserted
end
END
GO

查看数据库指定schema下所有表是否有主键约束和唯一索引(SQL Server)

1
2
3
4
5
6
SELECT s.name + '.' + t.name AS table_name,
(case when (select count(1) from sys.indexes i where t.object_id = i.object_id and is_unique = 1) >=1 then '1' else '0' end) as has_unique,
(case when (select count(1) from sys.indexes i where t.object_id = i.object_id and is_primary_key = 1) >=1 then '1' else '0' end) as has_primary
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.type = 'U' and s.name = 'dbo'

ISNULL与NULLIF函数区别(SQL Server)

ISNULL(check_expression, replacement_value)

  • 如果 check_expression 为 NULL,则返回 replacement_value
  • 如果 check_expression 不为 NULL,则返回 check_expression

NULLIF(expression, expression)

  • 如果两个 expression 相等,则返回 NULL,该 NULL 为第一个 expression 的数据类型
  • 如果两个 expression 不相等,则返回第一个 expression

存储过程的两个参数(QUOTED_IDENTIFIER 和 ANSI_NULLS)

QUOTED_IDENTIFIER

  • SET QUOTED_IDENTIFIER ON:与系统关键字重复的,允许使用双引号括起来,就能正常使用。注意是双引号
  • SET QUOTED_IDENTIFIER OFF:与系统关键字重复的,不允许使用

ANSI_NULLS

  • SET ANSI_NULLS ON:只能使用IS NULL来判断值是否为NULL,而不能使用=或<>来与NULL做比较,任何值包括NULL值与NULL值做=或<>运算都得到FALSE
  • SET ANSI_NULLS OFF:可以使用=和<>来与NULL做BOOL运算

Excel

Excel中$是什么意思,$在Excel中是对数据区域的绝对引用,表示固定单元格中的行列,没有$则表示相对引用
跨sheet操作只需要光标在参数前,点击对应sheet tab就行,同个参数只需要一次这样的操作

生成SQL:

  1. 这一列的文本类型选 general
  2. 公式文本框输入="'"&A1&"',"

快速填充整列:

  1. 左上角输入 D2:D414716,按住ctrl+enter,光标移到公式后,继续按住ctrl+enter

判断两列内容是否相同:

  1. 这一列的文本类型选 general
  2. =IF((A1=B1),TRUE,FALSE)

判断C列中的内容有没有在B列中出现过:

  1. 这一列的文本类型选 general
  2. =COUNTIF(B:B,C2)
  3. 指定范围并且跨sheet判断则是:=COUNTIF(Sheet1!$B$1:Sheet1!$B$7,C1)
  4. 结果为0的,就是B列中没有出现过的,而结果不为0,则是在B列中出现过

快速生成字母开头的连续序号:

  1. 这一列的文本类型选 general
  2. ="T"&TEXT(0+ROW(A1),"0000")
  3. 第一行就是T0001,下一行就是T0002

快速生成sql的in条件(2000个参数):

  1. 这一列的文本类型选 general
  2. =IF(EXACT(MOD(ROW()-2,2000),0),=TEXTJOIN("' ,' ",TRUE,A2:A2001),"")
  3. 结果:1’,’2’,’3….’,’2000

快速根据key获取key所在行的所有数据:

  1. 在sheet2中根据A列的值获取sheet1中A列对应值所在行的所有数据
  2. 将sheet2的B列的文本类型选 general
  3. =VLOOKUP($A1,Sheet1!$A$2:$AL$1001,COLUMN(Sheet1!B1),0)
  4. $A1是要基于其返回整行的查找值, Sheet1!$A$2:$AL$1001是要查找的数据范围, COLUMN(Sheet1!B1)表示数据范围内的第二列号,0表示精确查找
  5. 数据量太大导致卡死,可参考Power Query进行数据合并

单元测试

JMockit中文网