MSSQL管理常用语句使用指南-猿码集

一、数据库连接

在使用MSSQL Server 进行管理之前，首先必须建立与数据库的连接。以下是建立连接的基本方式之一：


USE master;
GO
EXEC sp_addlinkedserver 
   @server = '服务器名', -- 替换为实际的服务器名
   @srvproduct= '',
   @provider = 'SQLNCLI',
   @datasrc = '目标服务器名'; -- 替换为实际的目标服务器名
GO

使用sp_addlinkedserver存储过程可以连接到其他的数据库服务器上，也可以连接到同一台服务器上的其他实例上。

1. 认证方式选择

连接数据库时需要指定认证方式，默认为Windows身份验证（即Windows Authentication），也可以选择SQL Server验证（即SQL Server Authentication）。

Windows身份验证是以当前操作系统登录的用户身份来验证访问MSSQL Server的权限，适用于仅需要本地访问的情况；

SQL Server身份验证则是指使用自定义的用户名和密码来访问MSSQL Server的权限。它适用于在本地或远程系统上使用应用程序的情况，也适合多个用户使用相同的身份验证方式进行远程访问的情况。

在使用SQL Server验证方式进行数据库连接时需要指定用户名和密码。


USE master;
GO
EXEC sp_addlinkedsrvlogin 
   @rmtsrvname = '服务器名', -- 替换为实际的服务器名
   @useself = 'false',
   @locallogin = NULL,
   @rmtuser = '用户名', -- 替换为实际的用户名
   @rmtpassword = '密码'; -- 替换为实际的密码
GO

使用sp_addlinkedsrvlogin存储过程可以添加与其他服务器的连接的登录信息。

2. 连接多个数据库

除了连接到单个数据库之外，还可以连接多个数据库。在使用多个数据库时，需要先连接到master数据库，然后再连接到其他数据库。


USE master;
GO
EXEC sp_addlinkedserver 
   @server = '服务器1', -- 替换为实际的服务器名
   @srvproduct= '',
   @provider = 'SQLNCLI',
   @datasrc = '目标服务器1'; -- 替换为实际的目标服务器名
EXEC sp_addlinkedserver 
   @server = '服务器2', -- 替换为实际的服务器名
   @srvproduct= '',
   @provider = 'SQLNCLI',
   @datasrc = '目标服务器2'; -- 替换为实际的目标服务器名
GO

为了实现跨服务器的查询，可以使用以下语句：


SELECT *
FROM [服务器1].[database1].[dbo].[table1] t1
JOIN [服务器2].[database2].[dbo].[table2] t2
ON t1.id = t2.id;
GO

使用两个方括号括住服务器名、数据库名和表名是连接多个数据库的一种方式。

二、数据库备份与还原

1. 备份数据库

MSSQL Server 提供了多种备份方式，可以进行完整备份、差异备份或日志备份。

以下是进行完整备份的基本语法：


BACKUP DATABASE dbname
TO DISK = '备份路径\dbname_full.bak'
WITH INIT;

使用BACKUP DATABASE语句可以备份数据库，TO DISK选项可以指定备份文件路径和名称。

使用WITH INIT选项可以覆盖以前的备份文件，如果没有这个选项，则备份文件将追加到当前备份集中。

2. 还原数据库

以下是还原MSSQL Server中数据库的基本语法：

RESTORE DATABASE dbname FROM DISK = '备份路径\dbname_full.bak' WITH REPLACE;

使用RESTORE DATABASE语句可以从备份文件中还原数据库，FROM DISK选项可以指定备份文件路径和名称。

使用WITH REPLACE选项可以覆盖当前数据库，如果没有这个选项，则还原操作将被取消。

三、数据库维护

1. 压缩数据库

MSSQL Server中的压缩操作有助于减少磁盘空间的使用，并且可以提高查询性能。

以下是对数据库表进行压缩的基本语法：


ALTER TABLE <tablename> REBUILD WITH (DATA_COMPRESSION = ROW);

使用ALTER TABLE语句可以对指定的数据库表进行重建操作，并且可以通过DATA_COMPRESSION选项指定压缩类型。

2. 检查数据库的一致性

在MSSQL Server中，可以使用DBCC CHECKDB命令来检查数据库的一致性。

以下是检查数据库一致性的基本语法：


DBCC CHECKDB (dbname) WITH NO_INFOMSGS;

使用DBCC CHECKDB命令可以检查指定的数据库，并且可以使用WITH NO_INFOMSGS选项禁止输出信息。

另外，还可以通过DBCC CHECKALLOC、DBCC CHECKCATALOG、DBCC CHECKFILEGROUP等命令来检查数据库的其他方面。

四、数据库查询

1. 查询数据表

以下是查询MSSQL Server中数据表的基本语法：


SELECT * FROM tablename;

使用SELECT语句可以查询指定的数据表（tablename），并且可以使用通配符*来返回所有列。

2. SQL查询优化

频繁的查询操作可能会导致性能下降，因此建议尽可能使用索引来提高查询性能。

以下是创建索引的基本语法：


CREATE INDEX idx_tablename_colname
ON tablename (colname);

使用CREATE INDEX语句可以创建索引，其中的idx_tablename_colname为索引名称，tablename为数据表名称，colname为要索引的列名。

3. 存储过程

存储过程是一组预编译的SQL查询或命令，可以在MSSQL Server中进行存储、创建、维护和管理，它具有更高的安全性和更高的性能。

以下是创建存储过程的基本语法：


CREATE PROC [dbo].[sp_test]
AS
BEGIN


SELECT * FROM tablename
END

存储过程可以带参数，以下是带有参数的存储过程：


CREATE PROC [dbo].[sp_test1]
@id INT
AS
BEGIN


SELECT * FROM tablename WHERE id = @id
END

使用CREATE PROC语句可以创建存储过程，其中[dbo]为数据库架构，sp_test为存储过程名。

五、用户与角色

1. 创建用户

MSSQL Server中可以创建本地或远程用户，并进行身份验证和授权操作。

以下是创建本地用户的基本语法：


CREATE LOGIN username 
WITH PASSWORD = 'password', 
CHECK_POLICY=OFF;

使用WITH PASSWORD选项可以指定用户密码，CHECK_POLICY选项可以关闭密码复杂度检查。

2. 创建角色

MSSQL Server中可以创建角色，在角色中为用户或其他角色分配权限。

以下是创建角色的基本语法：


CREATE ROLE rolename;

使用CREATE ROLE语句可以创建角色，其中rolename为角色名称。

3. 授权给用户或角色

在MSSQL Server中，可以将权限赋予已存在的用户或角色。

以下是赋予权限的基本语法：


GRANT SELECT ON tablename TO username;

使用GRANT语句可以将表或其他对象的权限授予其他用户或角色。

六、日志管理

1. 查看日志

在MSSQL Server中，可以查看数据库的日志以了解先前发生的事件。

以下是查看日志的基本语法：


SELECT * FROM sys.fn_dblog(NULL,NULL);

使用sys.fn_dblog函数可以通过查看日志来了解数据库上的先前事件。

2. 清空日志

定期清空日志可以帮助维护数据库性能。

以下是清空日志的基本语法：

BACKUP LOG dbname WITH TRUNCATE_ONLY

使用BACKUP LOG语句可以备份事务日志，并使用WITH TRUNCATE_ONLY选项清空日志。

七、性能监测

1. 监测SQL运行性能

在MSSQL Server中，可以使用系统监测工具来监测SQL的运行性能。

以下是使用系统监测工具监测SQL运行性能的基本语法：


SELECT 


TotalElapsedTime/execution_count 'AvgElapsedTime', 


execution_count, 


TotalElapsedTime, 


TotalLogicalReads, 


TotalLogicalReads/execution_count 'AvgLogicalReads', 


TotalLogicalWrites, 


TotalLogicalWrites/execution_count 'AvgLogicalWrites', 


sql_handle, 


statement_start_offset, 


statement_end_offset, 


plan_handle, 


query_plan 
FROM 


sys.dm_exec_query_stats sqs 
CROSS APPLY 


sys.dm_exec_sql_text(sqs.sql_handle) st 
CROSS APPLY


sys.dm_exec_query_plan(sqs.plan_handle) qp
ORDER BY TotalElapsedTime DESC;

使用sys.dm_exec_query_stats和相关的函数和视图可以监控SQL的运行性能。

2. 监测系统性能

在MSSQL Server中，可以使用系统监视器来监测系统性能。

以下是使用系统监视器监测系统性能的基本语法：


SELECT * FROM sys.dm_os_performance_counters;

使用sys.dm_os_performance_counters视图可以监测系统性能。

八、总结

本文简要介绍了MSSQL Server管理中常用的语句和操作。通过正确地使用这些语句，可以更好地管理和维护MSSQL Server，提高数据库性能和安全性。希望通过本文的介绍，读者能够学习到一些有用的技能和知识。

MSSQL管理常用语句使用指南