100条牛批的SqlServer Sql语句排行榜

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;

1.4 分页查询

SELECT * FROM YourTable ORDER BY YourColumn OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY;

二、数据操作语句

2.1 插入数据并返回自动生成的ID

INSERT INTO YourTable (Column1, Column2) OUTPUT INSERTED.ID VALUES (Value1, Value2);

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;

三、索引和约束

3.1 创建索引

CREATE INDEX IX_YourTable_YourColumn ON YourTable (YourColumn);

3.2 创建唯一约束

ALTER TABLE YourTable ADD CONSTRAINT UC_YourTable_YourColumn UNIQUE (YourColumn);

四、连接查询

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());

二十三、字符串函数

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;

二十四、数学函数

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;

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数据库。