SQL Server命令大全

文章目录

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

1. 数据库管理

• 创建数据库

CREATEDATABASE MyDatabase;

• 删除数据库

DROPDATABASE MyDatabase;

• 选择/切换当前数据库

USE MyDatabase;

2. 表操作

• 创建表

CREATETABLE Employees (
    EmployeeID intPRIMARYKEY,
    FirstName nvarchar(50),
    LastName nvarchar(50),
    HireDate datetime);

• 插入数据

INSERTINTO Employees (EmployeeID, FirstName, LastName, HireDate)VALUES(1,'John','Doe','2023-01-01');

• 更新数据

UPDATE Employees
SET FirstName ='Jane'WHERE EmployeeID =1;

• 删除数据

DELETEFROM Employees
WHERE EmployeeID =1;

• 修改表结构

ALTERTABLE Employees
ADD DepartmentID intFOREIGNKEYREFERENCES Departments(DepartmentID);

3. 查询数据

• 基本查询

SELECT*FROM Employees;

• 条件查询

SELECT*FROM Employees WHERE DepartmentID =2;

• 排序查询

SELECT*FROM Employees ORDERBY HireDate DESC;

• 聚合函数查询

SELECTCOUNT(*)FROM Employees;

• 分组查询

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

4. 连接查询

• 内连接

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

5. 存储过程与函数

• 创建存储过程

CREATEPROCEDURE GetEmployeesByDepartment @deptIdINTASBEGINSELECT*FROM Employees WHERE DepartmentID =@deptId;END

• 调用存储过程

EXEC GetEmployeesByDepartment 2;

• 创建用户定义函数

CREATEFUNCTION dbo.GetTotalEmployees(@deptIdINT)RETURNSINTASBEGINRETURN(SELECTCOUNT(*)FROM Employees WHERE DepartmentID =@deptId);END

• 使用函数

SELECT dbo.GetTotalEmployees(2)AS TotalEmpInDept2;

6. 视图

• 创建视图

CREATEVIEW EmployeeNames ASSELECT FirstName, LastName FROM Employees;

• 查询视图

SELECT*FROM EmployeeNames;

7. 索引

• 创建索引

CREATEINDEX IX_Employees_DepartmentID ON Employees (DepartmentID);

• 删除索引

DROPINDEX IX_Employees_DepartmentID ON Employees;

8. 其他常用命令

• 事务控制

BEGINTRANSACTION;-- 执行一系列操作...COMMITTRANSACTION;

• 备份还原

BACKUPDATABASE MyDatabase TODISK='C:\backup\MyDatabase.bak';RESTOREDATABASE MyDatabase FROMDISK='C:\backup\MyDatabase.bak';

9. 用户与权限管理

• 创建登录账户

CREATE LOGIN NewUser WITH PASSWORD ='StrongPassword!';

• 创建数据库用户并映射到登录名

CREATEUSER UserForDB FOR LOGIN NewUser;ALTER ROLE db_datareader ADD MEMBER UserForDB;-- 给予读权限ALTER ROLE db_datawriter ADD MEMBER UserForDB;-- 给予写权限

• 撤销用户对数据库的访问

DROPUSER UserForDB;

• 授予、拒绝或撤销权限

GRANTSELECTON Employees TO UserForDB;DENYUPDATEON Employees TO UserForDB;REVOKEDELETEON Employees FROM UserForDB;

10. 数据备份与恢复

• 完整数据库备份

BACKUPDATABASE MyDatabase
TODISK='C:\Backup\MyDatabase.bak'WITH FORMAT, MEDIANAME ='MyDatabase_Full', NAME ='Full Backup';

• 差异备份

BACKUPDATABASE MyDatabase
TODISK='C:\Backup\MyDatabase_diff.bak'WITH DIFFERENTIAL, FORMAT, MEDIANAME ='MyDatabase_Diff', NAME ='Differential Backup';

• 事务日志备份

BACKUP LOG MyDatabase
TODISK='C:\Backup\MyDatabase_log.trn'WITH NOFORMAT, NOINIT, NAME = N'MyDatabase_LogBackup', SKIP, NOREWIND, NOUNLOAD, STATS =10;

• 还原数据库

RESTOREDATABASE MyDatabase
FROMDISK='C:\Backup\MyDatabase.bak'WITHFILE=1, NOUNLOAD,REPLACE, STATS =5;

11. 系统函数与信息查询

• 查询当前数据库版本

SELECT @@VERSION;

• 查询表结构信息

sp_help 'Employees';

• 获取当前时间

SELECT GETDATE()AS CurrentDateTime;

12. 其他高级特性

• 窗口函数

SELECT 
    EmployeeID, 
    FirstName, 
    Salary,AVG(Salary)OVER(PARTITIONBY DepartmentID)AS AvgSalaryInDept
FROM Employees;

• CTE（公用表表达式）

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

13. 分区表

• 创建分区函数

CREATEPARTITIONFUNCTION pf_EmployeesRange (int)AS RANGE RIGHTFORVALUES(2000,2005,2010,2015);

• 创建分区方案

CREATEPARTITION SCHEME ps_Employees 
ASPARTITION pf_EmployeesRange 
TO([PrimaryFileGroup],[SecondaryFileGroup1],[SecondaryFileGroup2],[SecondaryFileGroup3]);

• 创建分区表

CREATETABLE PartitionedEmployees (
    EmployeeID intPRIMARYKEY,
    HireDate intNOTNULL)ON ps_Employees(HireDate);

14. 高级查询操作

• 联合查询（UNION、UNION ALL）

SELECT FirstName, LastName FROM Employees WHERE DepartmentID =1UNIONALLSELECT FirstName, LastName FROM Employees WHERE DepartmentID =2;

• INTERSECT和EXCEPT操作

SELECT EmployeeID FROM Employees WHERE DepartmentID =1INTERSECTSELECT EmployeeID FROM Employees WHERE DepartmentID =2;SELECT EmployeeID FROM Employees WHERE DepartmentID =1EXCEPTSELECT 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 ONSET 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*FROMOPENROWSET('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@DepartmentIDINT=1;DECLARE@SQL NVARCHAR(MAX)= N'SELECT * FROM Employees WHERE DepartmentID = '+ CAST(@DepartmentIDAS NVARCHAR(10));EXEC sp_executesql @SQL;

20. 自增序列与标识符

• 创建带有自增列的表

CREATETABLE Orders (
    OrderID INTIDENTITY(1,1),
    CustomerID INT,
    OrderDate DATE,PRIMARYKEY(OrderID));

21. 数据类型转换

• 显式转换

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

22. CASE表达式和IIF函数

• CASE表达式

SELECT EmployeeID, FirstName, LastName,CASEWHEN Salary >50000THEN'High'WHEN Salary >30000THEN'Medium'ELSE'Low'ENDAS 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. 数据库快照

• 创建数据库快照

CREATEDATABASE MyDatabase_snapshot ON(NAME = MyDatabase, FILENAME ='C:\Snapshots\MyDatabase_snapshot.ss')ASSNAPSHOTOF MyDatabase;

• 从快照恢复数据

RESTOREDATABASE 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)
————————————————

​最后我们放松一下眼睛