目录
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 NewId
2.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 LogTable
2.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
END
23.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
GO
23.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 132
37.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