Daily Support

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

操作前的判断

#创建表前的判断
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

索引的操作

# 删除主键
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)

列的操作

# 重命名列名，索引中的列名会自动更改
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

#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的占用情况

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

#创建日志表
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)

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中文网