![cover](https://img-blog.csdnimg.cn/direct/01d72f8d29cb4e74a548d01a6daf0e5a.png)
SQL Server命令大全
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. 高级查询操作
- 联合查询(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经典脚本(41-50)
————————————————
最后我们放松一下眼睛
![Logo](https://devpress.csdnimg.cn/79de2bf0b7994defa4242ef90d5513fa.jpg)
开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!
更多推荐
所有评论(0)