SQL SERVER常用命令维护汇总

SQL Server (Microsoft SQL Server) 是微软开发的关系型数据库，其核心查询语言是 T-SQL (Transact-SQL)。与 MySQL 和 Oracle 相比，它在窗口函数、CTE (公用表表达式) 以及图形化管理工具 (SSMS) 方面表现卓越，但在命令行操作上依赖 sqlcmd。

以下是 SQL Server 从环境连接、基础配置、核心 T-SQL 语法到运维监控的全流程指南。

第一阶段：连接与环境 (Windows/Linux)#

SQL Server 没有像 MySQL 那样通用的“命令行客户端”作为日常首选，绝大多数 DBA 和开发者使用 SSMS (SQL Server Management Studio) (Windows) 或 Azure Data Studio (跨平台)。但在脚本和 Linux 服务器上，我们需要用 sqlcmd。

1. 使用 `sqlcmd` 连接 (命令行)#

# Windows 集成认证 (信任连接)
sqlcmd -S localhost -E

# 用户名密码认证
sqlcmd -S localhost -U sa -P 'YourStrongPassword123'

# 指定实例名 (SQL Server 支持多实例，默认实例是 MssqlServer)
sqlcmd -S localhost\SQLEXPRESS -U sa -P 'Password'

# 执行单条查询并退出
sqlcmd -S localhost -U sa -P 'Password' -Q "SELECT @@VERSION"

# 执行脚本文件
sqlcmd -S localhost -U sa -P 'Password' -i C:\scripts\backup.sql

2. 进入交互式模式#

输入 sqlcmd 登录后，提示符变为 1>。

输入 SQL 语句，每行结束不需要分号（虽然推荐加），最后输入 GO 并回车来执行批次。
退出：输入 EXIT 或 QUIT。

第二阶段：数据库与用户管理 (T-SQL)#

SQL Server 的逻辑结构：实例 (Instance) -> 数据库 (Database) -> 架构 (Schema) -> 表 (Table)。 注意：默认架构通常是 dbo。

1. 数据库操作#

-- 查看所有数据库
SELECT name, database_id, create_date FROM sys.databases;

-- 创建数据库 (指定文件和路径)
CREATE DATABASE MyDB
ON PRIMARY (
    NAME = 'MyDB_Data',
    FILENAME = 'C:\SQLData\MyDB.mdf',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
)
LOG ON (
    NAME = 'MyDB_Log',
    FILENAME = 'C:\SQLLog\MyDB.ldf',
    SIZE = 5MB,
    MAXSIZE = 1GB,
    FILEGROWTH = 1MB
);

-- 切换数据库
USE MyDB;
GO

-- 删除数据库
DROP DATABASE MyDB;

-- 修改数据库为只读
ALTER DATABASE MyDB SET READ_ONLY;
ALTER DATABASE MyDB SET READ_WRITE;

SQL Server 有两层安全：Login (服务器级，能登录) -> User (数据库级，能操作)。

-- 1. 创建服务器登录名 (Login)
CREATE LOGIN MyAppUser WITH PASSWORD = 'StrongPass123!';

-- 2. 在特定数据库中创建用户 (User) 并映射到 Login
USE MyDB;
GO
CREATE USER MyAppUser FOR LOGIN MyAppUser;

-- 3. 授权 (推荐的角色)
-- db_owner: 完全控制
-- db_datareader: 只能读
-- db_datawriter: 只能增删改
ALTER ROLE db_datareader ADD MEMBER MyAppUser;
ALTER ROLE db_datawriter ADD MEMBER MyAppUser;

-- 4. 授予特定对象权限
GRANT SELECT, INSERT ON dbo.Employees TO MyAppUser;
DENY DELETE ON dbo.Employees TO MyAppUser; -- 显式禁止删除

-- 5. 删除用户和登录名
DROP USER MyAppUser;
DROP LOGIN MyAppUser;

第三阶段：核心 T-SQL 操作 (增删改查)#

T-SQL 功能非常强大，尤其在分析函数和递归查询上。

1. 表操作 (DDL)#

-- 创建表 (IDENTITY 是自增列)
CREATE TABLE dbo.Employees (
    EmpID INT IDENTITY(1,1) PRIMARY KEY, -- 从1开始，每次加1
    FirstName NVARCHAR(50) NOT NULL,     -- NVARCHAR 支持 Unicode (中文)
    LastName NVARCHAR(50),
    Salary DECIMAL(10, 2),
    HireDate DATETIME2 DEFAULT SYSDATETIME(), -- 高精度时间
    IsActive BIT DEFAULT 1               -- BIT 类型 (0/1)
);

-- 查看表结构 (系统存储过程)
EXEC sp_help 'dbo.Employees';
-- 或查看建表脚本
EXEC sp_helptext 'dbo.Employees'; -- 仅适用于视图/存储过程，表需用 SSMS

-- 修改表
ALTER TABLE dbo.Employees ADD PhoneNumber NVARCHAR(20);
ALTER TABLE dbo.Employees DROP COLUMN PhoneNumber;
EXEC sp_rename 'dbo.Employees.LastName', 'Surname', 'COLUMN'; -- 重命名

-- 截断表 (清空数据，重置自增)
TRUNCATE TABLE dbo.Employees;

2. 增删改 (DML)#

SQL Server 默认自动提交，但在存储过程或显式事务中需控制。

-- 增 (INSERT)
INSERT INTO dbo.Employees (FirstName, LastName, Salary)
VALUES ('San', 'Zhang', 5000.00);

-- 批量插入
INSERT INTO dbo.Employees (FirstName, LastName, Salary)
SELECT FirstName, LastName, Salary FROM dbo.OldEmployees WHERE Dept = 'IT';

-- 改 (UPDATE) - 支持 TOP 限制
UPDATE TOP (100) dbo.Employees 
SET Salary = Salary * 1.1 
WHERE IsActive = 1;

-- 删 (DELETE) - 支持 TOP 限制
DELETE TOP (50) FROM dbo.Employees WHERE IsActive = 0;

-- T-SQL 特有：OUTPUT 子句 (返回被修改的数据)
DELETE FROM dbo.Employees 
OUTPUT DELETED.EmpID, DELETED.FirstName 
WHERE EmpID = 10;

3. 查 (SELECT) - T-SQL 的强项#

A. 分页 (Offset Fetch) SQL Server 2012+ 支持标准语法：

SELECT EmpID, FirstName, Salary
FROM dbo.Employees
ORDER BY Salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; -- 跳过10行，取10行

旧版本 (2008及以下) 需用 ROW_NUMBER() 窗口函数嵌套。

B. 顶级查询 (Top N)

-- 取前 10 名高薪员工
SELECT TOP 10 * FROM dbo.Employees ORDER BY Salary DESC;

-- 取前 10% 的员工
SELECT TOP 10 PERCENT * FROM dbo.Employees;

C. 常用函数差异

功能	MySQL / Oracle	SQL Server (T-SQL)
当前时间	`NOW()`, `SYSDATE`	`GETDATE()` (datetime), `SYSDATETIME()` (高精度)
空值处理	`IFNULL`, `NVL`	`ISNULL(col, default)` 或 `COALESCE(col, default)`
字符串拼接	`
字符串长度	`LENGTH` / `CHAR_LENGTH`	`LEN()` (去尾空格), `DATALENGTH()` (不去空格)
子串	`SUBSTR`	`SUBSTRING(str, start, length)`
类型转换	`CAST`, `CONVERT`	`CAST()`, `CONVERT(type, value, style)` (style 用于日期格式化)
自增列	`AUTO_INC`	`IDENTITY(1,1)`
伪表	`DUAL`	不需要 (直接 `SELECT GETDATE()`)

D. 高级查询 (CTE 与窗口函数) T-SQL 的 CTE (公用表表达式) 写法非常优雅：

-- 使用 CTE 计算部门平均工资，再筛选
WITH DeptAvg AS (
    SELECT DeptID, AVG(Salary) as AvgSal
    FROM dbo.Employees
    GROUP BY DeptID
)
SELECT e.FirstName, e.Salary, d.AvgSal
FROM dbo.Employees e
JOIN DeptAvg d ON e.DeptID = d.DeptID
WHERE e.Salary > d.AvgSal;

-- 窗口函数 (计算排名)
SELECT 
    FirstName, 
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) as RowNum,
    RANK() OVER (ORDER BY Salary DESC) as RankNum,
    DENSE_RANK() OVER (ORDER BY Salary DESC) as DenseRank
FROM dbo.Employees;

第四阶段：日常维护与监控 (DBA)#

1. 监控进程与锁 (sp_who2)#

-- 查看当前活动进程 (经典命令)
EXEC sp_who2 'active'; 

-- 查看更详细的进程信息
SELECT 
    session_id, login_name, host_name, program_name, status, command, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests 
WHERE status != 'background';

-- 查看阻塞 (谁锁住了谁)
SELECT 
    t1.resource_type,
    t1.resource_database_id,
    t1.request_session_id AS BlockedSessionID,
    t2.blocking_session_id AS BlockingSessionID
FROM sys.dm_tran_locks t1
JOIN sys.dm_exec_requests t2 ON t1.request_session_id = t2.session_id
WHERE t2.blocking_session_id IS NOT NULL;

-- 杀掉进程 (KILL)
KILL 54; -- 杀掉 session_id 为 54 的进程

2. 查看数据库大小与信息#

-- 查看数据库文件大小
EXEC sp_spaceused; -- 当前库

-- 查看所有数据库大小
SELECT 
    DB_NAME(database_id) as DBName,
    SUM(size * 8.0 / 1024) AS SizeMB
FROM sys.master_files
GROUP BY database_id;

3. 备份与还原 (T-SQL 方式)#

虽然 SSMS 图形化更常用，但脚本更可靠。

-- 完整备份
BACKUP DATABASE MyDB
TO DISK = 'D:\Backups\MyDB_Full.bak'
WITH FORMAT, INIT, NAME = 'MyDB-Full Backup', COMPRESSION; -- COMPRESSION 需企业版或 2016+ 标准版

-- 差异备份 (基于最近一次全备)
BACKUP DATABASE MyDB
TO DISK = 'D:\Backups\MyDB_Diff.bak'
WITH DIFFERENTIAL;

-- 事务日志备份 (需恢复模式为 FULL)
BACKUP LOG MyDB
TO DISK = 'D:\Backups\MyDB_Log.trn';

-- 还原数据库 (需先杀掉占用进程)
USE master;
GO
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE MyDB
FROM DISK = 'D:\Backups\MyDB_Full.bak'
WITH REPLACE, RECOVERY; -- RECOVERY 表示还原后可用
GO
ALTER DATABASE MyDB SET MULTI_USER;

4. 性能调优#

-- 查看执行计划 (图形化在 SSMS 中按 Ctrl+M 开启)
SET SHOWPLAN_TEXT ON; -- 仅显示计划不执行
-- 执行 SQL...
SET SHOWPLAN_TEXT OFF;

-- 清除缓存 (测试用，生产慎用)
DBCC DROPCLEANBUFFERS; -- 清除数据缓存
DBCC FREEPROCCACHE;    -- 清除执行计划缓存

-- 查看索引碎片
SELECT 
    OBJECT_NAME(object_id) as TableName,
    index_id,
    avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 30;

-- 重建索引 (解决碎片)
ALTER INDEX ALL ON dbo.Employees REBUILD;

5. 常用系统存储过程#

SQL Server 有大量的 sp_ 开头的系统存储过程，非常实用：

sp_helpdb: 查看数据库概况。
sp_who2: 查看进程。
sp_lock: 查看锁。
sp_rename: 重命名对象。
sp_depends: 查看对象依赖关系 (谁用了这张表)。
sp_refreshview: 刷新视图元数据。

💡 SQL Server 特色与避坑指南#

GO 关键字：
- GO 不是 T-SQL 语句，而是 SSMS/sqlcmd 的批处理结束符。
- 变量作用域限制在 GO 之间。
- 创建存储过程、视图、函数前通常必须加 GO 分隔。
方括号 []：
- 如果表名或列名包含空格、特殊字符或关键字，必须用方括号包裹。
- 例如：SELECT [First Name], [Order] FROM [User Details]。
- SSMS 通常会自动生成方括号，建议养成习惯。
事务日志爆炸：
- 如果恢复模式是 FULL 且不清理日志，.ldf 文件会无限增长直到占满磁盘。
- 解决：定期备份事务日志，或者如果不是必须点对点恢复，可改为 SIMPLE 模式：
```
ALTER DATABASE MyDB SET RECOVERY SIMPLE;
```
临时表：
- 本地临时表：#TempTable (仅当前会话可见，会话结束自动删除)。
- 全局临时表：##TempTable (所有会话可见，直到最后一个引用它的会话结束)。
- 表变量：@TempTable (存在于内存中，适合小数据量)。
NULL 排序：
- SQL Server 中，ORDER BY 时 NULL 默认被视为最小值 (排在 ASC 的最前面)。
日期格式：
- T-SQL 对日期字符串格式敏感。推荐始终使用 ISO 格式 YYYYMMDD 或 YYYY-MM-DDTHH:MM:SS，避免受服务器语言设置影响。
- 转换函数：CONVERT(VARCHAR, GETDATE(), 120) 输出 yyyy-mm-dd hh:mi:ss。

🚀 快速上手命令清单 (复制即用)#

-- 1. 检查版本
SELECT @@VERSION;

-- 2. 列出所有表
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';

-- 3. 查看表的前 100 条数据
SELECT TOP 100 * FROM dbo.YourTableName;

-- 4. 查找包含特定文本的存储过程
SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%YourSearchText%';

-- 5. 谁正在占用 CPU 最高？
SELECT TOP 10 
    session_id, 
    cpu_time, 
    total_elapsed_time, 
    status, 
    command, 
    text 
FROM sys.dm_exec_requests 
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY cpu_time DESC;

掌握这些 T-SQL 命令和 SSMS 的使用，你就能高效地管理 SQL Server 数据库。