SQL Server数据库命令整理大全

SQL Server 中包含了大量的命令用于数据库的管理、查询和操作。以下是一些主要命令分类及其简要示例：

1. 数据库管理

CREATE DATABASE MyDatabase; 删除数据库

DROP DATABASE MyDatabase; 选择/切换当前数据库

USE MyDatabase;

2. 表操作

CREATE TABLE Employees ( EmployeeID int PRIMARY KEY, FirstName nvarchar(50), LastName nvarchar(50), HireDate datetime ); 插入数据

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate) VALUES (1, 'John', 'Doe', '2023-01-01'); 更新数据

UPDATE Employees SET FirstName = 'Jane' WHERE EmployeeID = 1; 删除数据

DELETE FROM Employees WHERE EmployeeID = 1; 修改表结构

ALTER TABLE Employees ADD DepartmentID int FOREIGN KEY REFERENCES Departments(DepartmentID);

3. 查询数据

SELECT * FROM Employees; 条件查询

SELECT * FROM Employees WHERE DepartmentID = 2; 排序查询

SELECT * FROM Employees ORDER BY HireDate DESC; 聚合函数查询

SELECT COUNT(*) FROM Employees; 分组查询

SELECT DepartmentID, COUNT(*) AS CountOfEmployees FROM Employees GROUP BY DepartmentID;

4. 连接查询

SELECT E.FirstName, D.DepartmentName FROM Employees E INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID;

5. 存储过程与函数

CREATE PROCEDURE GetEmployeesByDepartment @deptId INT AS BEGIN SELECT * FROM Employees WHERE DepartmentID = @deptId; END 调用存储过程

EXEC GetEmployeesByDepartment 2; 创建用户定义函数

CREATE FUNCTION dbo.GetTotalEmployees(@deptId INT) RETURNS INT AS BEGIN RETURN (SELECT COUNT(*) FROM Employees WHERE DepartmentID = @deptId); END 使用函数

SELECT dbo.GetTotalEmployees(2) AS TotalEmpInDept2;

6. 视图

CREATE VIEW EmployeeNames AS SELECT FirstName, LastName FROM Employees; 查询视图

SELECT * FROM EmployeeNames;

7. 索引

CREATE INDEX IX_Employees_DepartmentID ON Employees (DepartmentID); 删除索引

DROP INDEX IX_Employees_DepartmentID ON Employees;

8. 其他常用命令

BEGIN TRANSACTION; -- 执行一系列操作... COMMIT TRANSACTION; 备份还原

BACKUP DATABASE MyDatabase TO DISK = 'C:\backup\MyDatabase.bak'; RESTORE DATABASE MyDatabase FROM DISK = 'C:\backup\MyDatabase.bak';

9. 用户与权限管理

CREATE LOGIN NewUser WITH PASSWORD = 'StrongPassword!'; 创建数据库用户并映射到登录名

CREATE USER UserForDB FOR LOGIN NewUser; ALTER ROLE db_datareader ADD MEMBER UserForDB; -- 给予读权限 ALTER ROLE db_datawriter ADD MEMBER UserForDB; -- 给予写权限 撤销用户对数据库的访问

DROP USER UserForDB; 授予、拒绝或撤销权限

GRANT SELECT ON Employees TO UserForDB; DENY UPDATE ON Employees TO UserForDB; REVOKE DELETE ON Employees FROM UserForDB;

10. 数据备份与恢复

BACKUP DATABASE MyDatabase TO DISK = 'C:\Backup\MyDatabase.bak' WITH FORMAT, MEDIANAME = 'MyDatabase_Full', NAME = 'Full Backup'; 差异备份

BACKUP DATABASE MyDatabase TO DISK = 'C:\Backup\MyDatabase_diff.bak' WITH DIFFERENTIAL, FORMAT, MEDIANAME = 'MyDatabase_Diff', NAME = 'Differential Backup'; 事务日志备份

BACKUP LOG MyDatabase TO DISK = 'C:\Backup\MyDatabase_log.trn' WITH NOFORMAT, NOINIT, NAME = N'MyDatabase_LogBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10; 还原数据库

RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backup\MyDatabase.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5;

11. 系统函数与信息查询

SELECT @@VERSION; 查询表结构信息

sp_help 'Employees'; 获取当前时间

SELECT GETDATE() AS CurrentDateTime;

12. 其他高级特性

SELECT EmployeeID, FirstName, Salary, AVG(Salary) OVER (PARTITION BY DepartmentID) AS AvgSalaryInDept FROM Employees; CTE（公用表表达式）

WITH EmpSalaries AS ( SELECT EmployeeID, Salary FROM Employees ) SELECT * FROM EmpSalaries WHERE Salary > (SELECT AVG(Salary) FROM EmpSalaries);

13. 分区表

CREATE PARTITION FUNCTION pf_EmployeesRange (int) AS RANGE RIGHT FOR VALUES (2000, 2005, 2010, 2015); 创建分区方案

CREATE PARTITION SCHEME ps_Employees AS PARTITION pf_EmployeesRange TO ( [PrimaryFileGroup], [SecondaryFileGroup1], [SecondaryFileGroup2], [SecondaryFileGroup3] ); 创建分区表

CREATE TABLE PartitionedEmployees ( EmployeeID int PRIMARY KEY, HireDate int NOT NULL ) ON ps_Employees(HireDate);

14. 高级查询操作

SELECT FirstName, LastName FROM Employees WHERE DepartmentID = 1 UNION ALL SELECT FirstName, LastName FROM Employees WHERE DepartmentID = 2; INTERSECT和EXCEPT操作

SELECT EmployeeID FROM Employees WHERE DepartmentID = 1 INTERSECT SELECT EmployeeID FROM Employees WHERE DepartmentID = 2; SELECT EmployeeID FROM Employees WHERE DepartmentID = 1 EXCEPT SELECT EmployeeID FROM Employees WHERE DepartmentID = 2;

15. 复制与同步

16. 异步处理与作业调度

USE msdb; GO EXEC sp_add_job @job_name=N'MyBackupJob', @enabled=1, @description='Daily backup job'; GO -- 添加作业步骤 EXEC sp_add_jobstep @job_name=N'MyBackupJob', @step_name=N'Backup Database', @subsystem=N'TSQL', @command=N'BACKUP DATABASE MyDatabase TO DISK = ''C:\Backup\MyDatabase.bak'';', @retry_attempts=5, @retry_interval=5; GO -- 启用作业调度 EXEC dbo.sp_add_schedule @schedule_name = N'DailyAtMidnight', @freq_type = 4, -- 每日 @freq_interval = 1, -- 每天运行一次 @active_start_time = 000000; -- 在午夜开始 -- 将作业与调度关联 EXEC sp_attach_schedule @job_name = N'MyBackupJob', @schedule_name = N'DailyAtMidnight'; GO

17. 查询执行计划

-- 在查询语句前添加EXPLAIN 或者 SET SHOWPLAN_ALL ON SET SHOWPLAN_ALL ON; SELECT * FROM Employees WHERE DepartmentID = 1; SET SHOWPLAN_ALL OFF; -- 或使用图形化方式查看 -- 在SQL Server Management Studio中，运行查询后右键选择"包括实际执行计划" SELECT * FROM Employees WHERE DepartmentID = 1;

18. 引用外部数据

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Text;Database=C:\Data;HDR=YES;FMT=Delimited', 'SELECT * FROM [Employees.txt]'); 链接服务器

-- 创建链接服务器 EXEC sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'OtherDB', @provider=N'SQLNCLI', @datasrc=N'ServerName\InstanceName'; -- 使用链接服务器查询数据 SELECT * FROM MyLinkedServer.RemoteDB.dbo.Employees;

19. 动态SQL

DECLARE @DepartmentID INT = 1; DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM Employees WHERE DepartmentID = ' + CAST(@DepartmentID AS NVARCHAR(10)); EXEC sp_executesql @SQL;

20. 自增序列与标识符

CREATE TABLE Orders ( OrderID INT IDENTITY(1,1), CustomerID INT, OrderDate DATE, PRIMARY KEY (OrderID) );

21. 数据类型转换

SELECT CAST('1234' AS INT), CONVERT(INT, '1234');

22. CASE表达式和IIF函数

SELECT EmployeeID, FirstName, LastName, CASE WHEN Salary > 50000 THEN 'High' WHEN Salary > 30000 THEN 'Medium' ELSE 'Low' END AS SalaryLevel FROM Employees; IIF函数（SQL Server 2012及以上版本）

SELECT EmployeeID, FirstName, LastName, IIF(Salary > 50000, 'High', IIF(Salary > 30000, 'Medium', 'Low')) AS SalaryLevel FROM Employees;

23. 数据库快照

CREATE DATABASE MyDatabase_snapshot ON (NAME = MyDatabase, FILENAME = 'C:\Snapshots\MyDatabase_snapshot.ss') AS SNAPSHOT OF MyDatabase; 从快照恢复数据

RESTORE DATABASE MyDatabase FROM DATABASE_SNAPSHOT = 'MyDatabase_snapshot';

总结 

到此这篇关于SQL Server数据库命令整理大全的文章就介绍到这了,