目录
37.1 sqlserver varchar类型的字段，如果没有数据的时候，存null好，还是空字符串好？
SQL Server 强大而灵活，这里是100条实用且“牛逼”的 SQL 语句。它们覆盖了查询、数据操作、性能优化和数据库管理等多个方面，旨在帮助数据库管理员和开发人员高效地管理和操作 SQL Server 数据库。
一、基本查询语句
1.1 查询所有数据库名称
SELECT name FROM sys.databases;1.2 查询所有表名
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';1.3 查询表中的前10行
SELECT TOP 10 * FROM YourTable;
--Top数量做为入参传入
SELECT TOP (@num) * FROM YourTable;1.4 分页查询
SELECT * FROM YourTable
ORDER BY YourColumn
OFFSET 50 ROWS
FETCH NEXT 50 ROWS ONLY;二、数据操作语句
2.1 插入数据并返回自动生成的ID
INSERT INTO YourTable (Column1, Column2)
VALUES (Value1, Value2);
SELECT @@identity AS NewId2.2 批量插入数据
INSERT INTO YourTable (Column1, Column2)
SELECT Column1, Column2 FROM AnotherTable;2.3 更新数据
UPDATE YourTable SET Column1 = NewValue WHERE Column2 = ConditionValue;2.4 删除数据
DELETE FROM YourTable WHERE Column1 = ConditionValue;
删除表全部数据：
TRUNCATE TABLE LogTable2.5 循环删除指定条件下表的数据
循环删除指定条件下表的数据示例：
while exists
(select top 1 1 from dbo.ErrorInfo with(nolock) where Throwtime >'2009-12-03 15:25:00')
begin
waitfor delay '00:00:02'
delete top (1000) ErrorInfo with(rowlock) from ErrorInfo
with(index=IX_ErrorInfo) where Throwtime >'2009-12-03 15:25:00'
option(maxdop 1)
end
重建索引：
ALTER INDEX ix_name ON DBO.tablename REBUILD WITH(ONLINE=ON,MAXDOP=1,data_compression=page)
--MAXDOP=1是指定用几个CPU,data_compression=page可选项。三、索引和约束
3.1 创建索引
CREATE INDEX IX_YourTable_YourColumn ON YourTable (YourColumn);3.2 创建唯一约束
ALTER TABLE YourTable
ADD CONSTRAINT UC_YourTable_YourColumn
UNIQUE (YourColumn);3.3 创建主键
--创建keyColumnName为新主键
ALTER TABLE YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY (keyColumnName);3.4 删除主键
--删除原来的主键
ALTER TABLE YourTable DROP CONSTRAINT PK_YourTable;四、连接查询
4.1 左连接查询
SELECT a.*, b.* FROM TableA a LEFT JOIN TableB b ON a.ID = b.ID;4.2 右连接查询
SELECT a.*, b.* FROM TableA a RIGHT JOIN TableB b ON a.ID = b.ID;4.3 内连接查询
SELECT a.*, b.* FROM TableA a INNER JOIN TableB b ON a.ID = b.ID;4.4 全连接查询
SELECT a.*, b.* FROM TableA a FULL OUTER JOIN TableB b ON a.ID = b.ID;4.5 交叉连接查询
SELECT a.*, b.* FROM TableA a CROSS JOIN TableB b;4.6 自连接查询
SELECT a.*, b.* FROM YourTable a
INNER JOIN YourTable b ON a.ID = b.ParentID;五、子查询和CTE
5.1 子查询
SELECT * FROM YourTable WHERE Column1 IN (SELECT Column1 FROM AnotherTable);5.2 CTE（公用表表达式）使用
WITH CTE AS (SELECT Column1, Column2 FROM YourTable WHERE Condition)
SELECT * FROM CTE;六、分组和聚合
6.1 分组查询
SELECT Column1, COUNT(*) FROM YourTable GROUP BY Column1;6.2 HAVING子句
SELECT Column1, COUNT(*) FROM YourTable GROUP BY Column1 HAVING COUNT(*) > 1;6.3 计算列和聚合函数
SELECT COUNT(*), AVG(Column1), SUM(Column2) FROM YourTable WHERE Condition;七、窗口函数
7.1 窗口函数
SELECT Column1,
ROW_NUMBER() OVER (PARTITION BY Column2 ORDER BY Column3) AS RowNum
FROM YourTable;
八、动态SQL
8.1 动态SQL
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'SELECT * FROM ' + @YourTableName;
EXEC sp_executesql @SQL;九、存储过程和函数
9.1 执行存储过程
EXEC YourStoredProcedure @Param1 = Value1, @Param2 = Value2;9.2 创建存储过程
CREATE PROCEDURE YourStoredProcedure
@Param1 INT,
@Param2 NVARCHAR(50)
AS
BEGIN
SELECT * FROM YourTable WHERE Column1 = @Param1 AND Column2 = @Param2;
END;9.3 创建函数
CREATE FUNCTION YourFunction (@Param1 INT)
RETURNS INT
AS
BEGIN
RETURN @Param1 * 2;
END;9.4 使用函数
SELECT dbo.YourFunction(Column1) FROM YourTable;十、视图
10.1 创建视图
CREATE VIEW YourView
AS
SELECT Column1, Column2 FROM YourTable WHERE Condition;10.2 更新视图
ALTER VIEW YourView
AS
SELECT Column1, Column2, Column3 FROM YourTable WHERE Condition;10.3 删除视图
DROP VIEW YourView;十一、触发器
11.1 创建触发器
CREATE TRIGGER YourTrigger ON YourTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
-- Your trigger logic
END;11.2 删除触发器
DROP TRIGGER YourTrigger;十二、临时表和表变量
12.1 临时表使用
CREATE TABLE #TempTable (Column1 INT, Column2 NVARCHAR(50));
INSERT INTO #TempTable (Column1, Column2) VALUES (1, 'Value1');
SELECT * FROM #TempTable;
DROP TABLE #TempTable;12.1 表变量使用
DECLARE @TempTable TABLE (Column1 INT, Column2 NVARCHAR(50));
INSERT INTO @TempTable (Column1, Column2) VALUES (1, 'Value1');
SELECT * FROM @TempTable;十三、游标
13.1 游标使用
DECLARE @Column1 INT;
DECLARE CursorName CURSOR FOR SELECT Column1 FROM YourTable;
OPEN CursorName;
FETCH NEXT FROM CursorName INTO @Column1;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Your logic
FETCH NEXT FROM CursorName INTO @Column1;
END;
CLOSE CursorName;
DEALLOCATE CursorName;十四、MERGE语句
14.1 合并（MERGE）语句
MERGE INTO TargetTable AS target
USING SourceTable AS source ON target.ID = source.ID
WHEN MATCHED THEN UPDATE SET target.Column1 = source.Column1
WHEN NOT MATCHED THEN
INSERT (Column1, Column2) VALUES (source.Column1, source.Column2)
WHEN NOT MATCHED BY SOURCE THEN DELETE;十五、高级查询
15.1 WITH ROLLUP
SELECT Column1, Column2, SUM(Column3)
FROM YourTable
GROUP BY Column1, Column2
WITH ROLLUP;15.2 WITH CUBE
SELECT Column1, Column2, SUM(Column3)
FROM YourTable
GROUP BY Column1, Column2 WITH CUBE;15.3 PIVOT查询
SELECT * FROM YourTable
PIVOT (SUM(Column1) FOR Column2
IN ([Value1], [Value2], [Value3])) AS PivotTable;15.4 UNPIVOT查询
SELECT * FROM YourTable
UNPIVOT (Column1 FOR Column2 IN ([Value1], [Value2], [Value3]))
AS UnpivotTable;15.5 数据分片
SELECT Column1, NTILE(4) OVER (ORDER BY Column2) AS Quartile FROM YourTable;十六、性能优化和管理
16.1 获取数据库大小
EXEC sp_spaceused;16.2 获取表大小
EXEC sp_spaceused 'YourTable';16.3 性能优化查询计划
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM YourTable;16.4 查看查询执行计划
SET SHOWPLAN_ALL ON;
GO
SELECT * FROM YourTable;
GO
SET SHOWPLAN_ALL OFF;16.5 处理NULL值
SELECT ISNULL(Column1, 'DefaultValue') FROM YourTable;16.6 处理空字符串
SELECT COALESCE(Column1, 'DefaultValue') FROM YourTable;十七、备份和恢复
17.1 数据库备份
BACKUP DATABASE YourDatabase TO DISK = 'C:\Backup\YourDatabase.bak';17.2 数据库恢复
RESTORE DATABASE YourDatabase
FROM DISK = 'C:\Backup\YourDatabase.bak';十八、日志管理
18.1 截断日志
BACKUP LOG YourDatabase WITH TRUNCATE_ONLY;18.2 收缩日志文件
DBCC SHRINKFILE (YourDatabase_Log, 1);十九、安全管理
19.1 创建登录
CREATE LOGIN YourLogin WITH PASSWORD = 'YourPassword';19.2 删除登录
DROP LOGIN YourLogin;19.3 创建用户
CREATE USER YourUser FOR LOGIN YourLogin;19.4 删除用户
DROP USER YourUser;19.5 授予权限
GRANT SELECT ON YourTable TO YourUser;19.6 撤销权限
REVOKE SELECT ON YourTable FROM YourUser;二十、作业管理
20.1 创建作业
USE msdb;
EXEC sp_add_job @job_name = 'YourJob';
EXEC sp_add_jobstep @job_name = 'YourJob',
@step_name = 'Step1', @subsystem = 'TSQL', @command = 'SELECT 1';
EXEC sp_add_schedule @job_name = 'YourJob', @name = 'DailySchedule',
@freq_type = 4, @freq_interval = 1, @active_start_time = 233000;
EXEC sp_attach_schedule @job_name = 'YourJob',
@schedule_name = 'DailySchedule';
EXEC sp_add_jobserver @job_name = 'YourJob';20.2 删除作业
EXEC sp_delete_job @job_name = 'YourJob';二十一、数据类型转换
21.1 CAST 和 CONVERT
SELECT CAST(Column1 AS NVARCHAR(50)) FROM YourTable;
SELECT CONVERT(DATETIME, Column1, 101) FROM YourTable;二十二、日期和时间函数
22.1 获取当前日期和时间
SELECT GETDATE();22.2 获取当前UTC日期和时间
SELECT GETUTCDATE();22.3 日期加减
SELECT DATEADD(day, 7, GETDATE());
SELECT DATEDIFF(day, '2023-01-01', GETDATE());22.4 其他日期方法
--当日小时数
declare @currentHH int=DATEPART(HH,getdate())
--当前年
declare @currentYear int=DATEPART(YYYY,getdate())
--当前月
declare @currentMonth int=DATEPART(MONTH,getdate())
--查8点的数据
datename(HOUR,rd.SignTime))=8
--（2）周计算、计算上一个工作日
declare @ComputeDate nvarchar(10)
set @ComputeDate=dateadd(dd,-1,getdate())
if(DATEPART (weekday,getdate())=1)
begin
--周日取周五，减2天
set @ComputeDate= convert(nvarchar(10),dateadd(dd,-2,getdate()),23)
end
if(DATEPART (weekday,getdate())=7)
begin
--周六取周五，减1天
set @ComputeDate= convert(nvarchar(10),dateadd(dd,-1,getdate()),23)
end
if(DATEPART (weekday,getdate())=2)
begin
--周一取周五，减3天
set @ComputeDate= convert(nvarchar(10),dateadd(dd,-3,getdate()),23)
end
select @ComputeDate
（3）根据日期查询星期
SELECT SYSDATETIME();
select datepart(weekday,getdate());
SET DATEFIRST 1
select '星期'+case when cast(datepart(dw,getdate()) as char(1))= 7 then '天' else cast(datepart(dw,getdate()) as char(1)) end
select @@datefirst;
datefirst 一般美国采用星期天作为一周的第一天（默认）。
--按照中国的习惯，我们可以设置周一的为一周的开始。 set datefirst 1
二十三、字符串函数
23.1 字符串连接
SELECT CONCAT(Column1, ' ', Column2) FROM YourTable;23.2 字符串截取
SELECT SUBSTRING(Column1, 1, 10) FROM YourTable;23.3 字符串长度
SELECT LEN(Column1) FROM YourTable;23.4 字符串替换
SELECT REPLACE(Column1, 'old', 'new') FROM YourTable;23.5 自定义函数
23.5.1 分隔符字符串转化为字符串数组列
ALTER FUNCTION [dbo].[SplitString]
(
@String NVARCHAR(MAX),
@Delimiter CHAR(1)
)
RETURNS @Output TABLE (
Id varchar(100)
)
AS
BEGIN
DECLARE @Start INT, @End INT
SET @Start = 1
SET @End = CHARINDEX(@Delimiter, @String)
WHILE @Start <= LEN(@String)
BEGIN
IF @End = 0
SET @End = LEN(@String) + 1
INSERT INTO @Output (Id)
VALUES (SUBSTRING(@String, @Start, @End - @Start))
SET @Start = @End + 1
SET @End = CHARINDEX(@Delimiter, @String, @Start)
END
RETURN
END23.5.2 分隔符字符串转化为整数数组列
CREATE FUNCTION dbo.SplitStringToIntArray
(
@String NVARCHAR(MAX),
@Delimiter CHAR(1)
)
RETURNS @Output TABLE (Id INT)
AS
BEGIN
DECLARE @Start INT, @End INT
SET @Start = 1
SET @End = CHARINDEX(@Delimiter, @String)
WHILE @Start <= LEN(@String)
BEGIN
IF @End = 0
SET @End = LEN(@String) + 1
INSERT INTO @Output (Id)
VALUES (CAST(SUBSTRING(@String, @Start, @End - @Start) AS INT))
SET @Start = @End + 1
SET @End = CHARINDEX(@Delimiter, @String, @Start)
END
RETURN
END
GO23.5.3 声明数组类型
DECLARE @Ids TABLE (Id INT)
INSERT INTO @Ids (Id)
SELECT Id FROM dbo.SplitStringToIntArray('469,470', ',')
select *from @Ids二十四、数学函数
24.1 四舍五入
SELECT ROUND(Column1, 2) FROM YourTable;24.2 取整
SELECT FLOOR(Column1) FROM YourTable;
SELECT CEILING(Column1) FROM YourTable;24.3 随机数生成
SELECT RAND();二十五、排序和去重
25.1 排序
SELECT * FROM YourTable ORDER BY Column1 ASC, Column2 DESC;25.2 去重
SELECT DISTINCT Column1 FROM YourTable;二十六、条件语句
26.1 CASE 语句
SELECT Column1,
CASE
WHEN Column2 = 'Value1' THEN 'Result1'
WHEN Column2 = 'Value2' THEN 'Result2'
ELSE 'OtherResult' END AS NewColumn
FROM YourTable;二十七、元数据查询
27.1 查询表的列
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTable';27.2 查询主键
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'YourTable'
AND CONSTRAINT_NAME LIKE 'PK%';27.3 查询外键
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'YourTable' AND CONSTRAINT_NAME LIKE 'FK%';二十八、事务管理
28.1 开始事务
BEGIN TRANSACTION;28.2 提交事务
COMMIT TRANSACTION;28.3 回滚事务
ROLLBACK TRANSACTION;二十九、锁管理
29.1 查询锁信息
SELECT * FROM sys.dm_tran_locks;29.2 设置锁超时
SET LOCK_TIMEOUT 5000;三十、分区
30.1 创建分区函数
CREATE PARTITION FUNCTION YourPartitionFunction (INT)
AS
RANGE LEFT FOR VALUES (1, 100, 1000);30.2 创建分区方案
CREATE PARTITION SCHEME YourPartitionScheme
AS
PARTITION YourPartitionFunction TO (FileGroup1, FileGroup2,
FileGroup3, FileGroup4);三十一、全文搜索
31.1 创建全文索引
CREATE FULLTEXT INDEX ON YourTable (YourColumn) KEY INDEX PK_YourTable;31.2 全文搜索查询
SELECT * FROM YourTable WHERE CONTAINS(YourColumn, 'YourSearchTerm');三十二、模式管理
32.1 创建模式
CREATE SCHEMA YourSchema;32.2 删除模式
DROP SCHEMA YourSchema;三十三、表管理
33.1 重命名表
EXEC sp_rename 'OldTableName', 'NewTableName';33.2 添加列
ALTER TABLE YourTable ADD NewColumn INT;
alter table 表名 add 日期字段名 datetime default getdate() not null;
alter table 表名 add 整型字段名 int default 0 not null;
alter table 表名 add 字符串字段 nvarchar(500) default N'' null;
alter table 表名 add 字符串字段 varchar(20) default N'' not null;
alter table 表名 add 整型字段 tinyint default 0 not null;
alter table 表名 add 整型字段 bit default 0 not null;
alter table 表名 add 金额类型字段 decimal(18,2) default 0 not null;
--通过下面的存储过程可以给你的字段增加备注
EXEC sp_addextendedproperty
'MS_Description', N'你的备注内容',
'SCHEMA', N'dbo',
'TABLE', N'你的表名',
'COLUMN', N'你的列名';
--通过下面的存储过程可以给你的字段修改备注
EXEC sp_updateextendedproperty
@name = N'MS_Description',
@value = N'新的备注内容',
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = N'你的表名',
@level2type = N'COLUMN', @level2name = N'你的列名';
--通过下面的存储过程可以给你的字段删除备注
EXEC sp_dropextendedproperty
@name = N'MS_Description',
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = N'你的表名',
@level2type = N'COLUMN', @level2name = N'你的列名';
--查询列备注
SELECT objname AS ObjectName, value AS Description
FROM
fn_listextendedproperty ('MS_Description', 'schema', 'dbo', 'table', 'Users', 'column', 'FirstName');33.3 删除列
ALTER TABLE YourTable DROP COLUMN ColumnName;33.4 修改列
ALTER TABLE YourTable ALTER COLUMN ColumnName NVARCHAR(50);三十四、异常处理
34.1 TRY...CATCH
BEGIN TRY
-- Your SQL code
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;三十五、查询性能优化
35.1 索引重建
ALTER INDEX ALL ON YourTable REBUILD;35.2 索引重组
ALTER INDEX ALL ON YourTable REORGANIZE;35.3 更新统计信息
UPDATE STATISTICS YourTable;35.4 查看索引使用情况
SELECT * FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('YourDatabase');三十六、其他高级功能
36.1 文件组管理
ALTER DATABASE YourDatabase ADD FILEGROUP YourFileGroup;36.2 添加文件到文件组
ALTER DATABASE YourDatabase
ADD FILE (NAME = 'YourFile', FILENAME = 'C:\YourFile.ndf')
TO FILEGROUP YourFileGroup;36.3 分区表创建
CREATE TABLE YourPartitionedTable
(Column1 INT, Column2 NVARCHAR(50)) ON YourPartitionScheme (Column1);36.4 分区视图创建
CREATE VIEW YourPartitionedView
AS
SELECT * FROM Partition1Table
UNION ALL
SELECT * FROM Partition2Table以上100条SQL Server语句涵盖了从基础操作到高级功能的多个方面，能够帮助您更高效地管理和优化SQL Server数据库。
三十七、一些疑问
37.1 sqlserver varchar类型的字段，如果没有数据的时候，存null好，还是空字符串好？
在SQL Server中，对于varchar类型的字段，当没有数据时，存储NULL还是空字符串取决于具体的应用场景和需求。以下是存储NULL和空字符串各自的优缺点及建议：
存储NULL
优点
- 语义清晰：
NULL表示数据缺失或不可用，语义上更加清晰。 - 条件查询：在查询时，可以明确区分数据缺失与空字符串，通过
IS NULL进行检查。 - 索引和统计信息：SQL Server会针对
NULL值进行单独统计，可能会对查询性能有一定的帮助。
缺点
- 处理复杂：在编写查询和逻辑时，需要额外处理
NULL值，增加了代码复杂性。 - 函数运算：某些函数在遇到
NULL值时需要特别处理，如字符串连接、聚合函数等。
示例
SELECT * FROM table WHERE column IS NULL;存储空字符串
优点
- 一致性：在应用中，所有字段都保持一致，即使没有数据也有一个默认的空字符串值。
- 简化处理：不需要额外处理
NULL值，简化了代码逻辑。
缺点
- 语义模糊：空字符串和数据缺失在语义上可能会混淆，不容易区分。
- 索引和统计信息：空字符串和有值的记录混在一起，可能会对统计信息和索引性能有一定影响。
示例
SELECT * FROM table WHERE column = '';建议
- 语义明确：如果需要明确表示数据缺失，建议使用
NULL。 - 应用需求：根据应用需求决定。如果业务逻辑中需要处理大量的空字符串，而
NULL处理复杂，可以考虑使用空字符串。 - 一致性和规范性：在项目初期就确定好统一的规范，保证整个项目中对缺失数据处理的一致性。
参考示例
假设有一个用户表，需要记录用户的昵称：
存储NULL
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Nickname VARCHAR(50) NULL
);
-- 插入数据时存储NULL
INSERT INTO Users (UserID, Nickname) VALUES (1, NULL);
-- 查询时检查NULL
SELECT * FROM Users WHERE Nickname IS NULL;存储空字符串
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Nickname VARCHAR(50) NOT NULL DEFAULT ''
);
-- 插入数据时存储空字符串
INSERT INTO Users (UserID, Nickname) VALUES (1, '');
-- 查询时检查空字符串
SELECT * FROM Users WHERE Nickname = '';总结来说，存储NULL和空字符串各有优缺点，具体选择应根据项目需求和团队规范来决定。如果需要明确表示数据缺失，建议使用NULL。
37.2 查看死锁 & 杀死死锁进程
注意：要反复查询多次，一直存在的Pid才可以删除，因为一直存在的才可能是死锁进程。
/*查看死锁进程和表名*/
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks
where resource_type='OBJECT'
--杀掉死锁进程
kill 13237.3 隐藏身份证号
--身份证号隐藏11到14位
REPLACE(B.SFZH,SUBSTRING(B.SFZH,11,4),'****') AS SFZH
--手机号隐藏4到7位
REPLACE(B.LXDH,SUBSTRING(B.LXDH,4,4),'****') AS LXDH
37.4 收缩数据库
DBCC SHRINKDATABASE(N’DBName’, 10 )
37.5 分析历史Sql语句执行效率
Select TOP 111
ST.text AS '执行的SQL语句',
QS.execution_count AS '执行次数',
QS.total_elapsed_time AS '耗时',
QS.total_logical_reads AS '逻辑读取次数',
QS.total_logical_writes AS '逻辑写入次数',
QS.total_physical_reads AS '物理读取次数',
QS.creation_time AS '执行时间' ,
QS.*
FROM sys.dm_exec_query_stats QS
CROSS APPLY
sys.dm_exec_sql_text(QS.sql_handle) ST
Where QS.creation_time BETWEEN '2023-10-21 10:00:00' AND '2024-10-21 11:00:00'
--and ST.text like '%简历地区%'
-- Where
ORDER BY QS.total_elapsed_time DESC
144
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
