SQL Server命令大全

07-16 610阅读

文章目录

1. 数据库管理
2. 表操作
3. 查询数据
4. 连接查询
5. 存储过程与函数
6. 视图
7. 索引
8. 其他常用命令
9. 用户与权限管理
10. 数据备份与恢复
11. 系统函数与信息查询
12. 其他高级特性
13. 分区表
14. 高级查询操作
15. 复制与同步
16. 异步处理与作业调度
17. 查询执行计划
18. 引用外部数据
19. 动态SQL
20. 自增序列与标识符
21. 数据类型转换
22. CASE表达式和IIF函数

23. 数据库快照

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. 高级查询操作

联合查询（UNION、UNION ALL）

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. 复制与同步

使用SQL Server Replication进行数据复制

这涉及到一系列复杂的配置步骤，包括发布设置、订阅设置、代理设置等。

16. 异步处理与作业调度

创建SQL Server Agent作业

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. 引用外部数据

OPENROWSET函数读取文件

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

构建并执行动态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函数

CASE表达式

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';
```
    python推荐学习汇总连接：
    50个开发必备的Python经典脚本(1-10)
    
    50个开发必备的Python经典脚本(11-20)
    
    50个开发必备的Python经典脚本(21-30)
    
    50个开发必备的Python经典脚本(31-40)
    
    50个开发必备的Python经典脚本(41-50)
    ————————————————
    
    最后我们放松一下眼睛

VPS购买请点击我