SQL Server 数据库之常用命令

最新推荐文章于 2024-05-08 17:47:16 发布

菇毒

最新推荐文章于 2024-05-08 17:47:16 发布

阅读量5.8k

点赞数 2

分类专栏： SQL server 文章标签： sqlserver 常用命令

本文链接：https://blog.csdn.net/weixin_43960383/article/details/124443870

版权

SQL server 专栏收录该内容

50 篇文章 59 订阅

订阅专栏

1. DECLARE 定义命令

在批处理或过程的正文用 DECLARE 语句声明变量，并用 SET 或 SELECT 语句给其指派值；
游标变量也能通过该语句声明，并可用在其他与游标相关的语句中；所有变量在声明后均初始化为 NULL；

1.1 语法

DECLARE
    {{@local_variable data_type}
    | {@cursor_variable_name CURSOR}
    | {table_type_definition}
    } [,...n]

1.2 参数

@local_variable
@local_veriable 是变量的名称；变量名必须以 @符（@）开头；
局部变量必须符合标识符规则；
data_type
data_type 是任何系统提供或用户定义的数据类型；变量不能是 text、ntext 或 image 数据类型；
@cursor_variable_name
@cursor_variable_name 是游标变量的名称；游标变量名必须以 @ 开头遵从标识符规则；
CURSOR
指定变量时局部游标变量；
table_type_definition
定义数据类型；表明包括列定义、名称、数据类型和约束；允许的约束类型只包括 PRIMARY KEY、UNIQUE KEY、NULL 和 CHECK；

1.3 注释

变量常用在批处理或过程中，作为 WHILE、LOOP 或 IF...ELSE 块的计数器；变量只能用表达式中，不能代替对象名或关键字；
若要构造动态 SQL 语句，使用 EXECUTE；局部变量的作用域是在其中声明局部变量的批处理、存储过程或语句块；

2 PRINT 输出命令

当用户在 SQL Server 中运行了一段 T-SQL 语句代码后，可将结果返回，此时就需要用到输出命令；PRINT 输出命令功能将用户定义的消息返回客户端；

2.1. 语法

PRINT 'any ASCII text' | @local_veriable | @@FUNCTION | string_expr

2.2. 参数

'any ASCII text'
一个文本字符串；
@local_variable
是任何有效字符数据类型的变量，local_variable 必须是 char、nchar、nvarchar、varchar；
@@FUNCTION
@@FUCTION 是返回字符串结果的函数；@@FUNCTION 必须是 char 或 varchar，或者能够隐式转换为这些数据类型；
string_expr
string_expr 是返回祝福词的表达式；可包括串联的字面值和变量，消息字符串最长可达 8000 个字符，超过 8000个的任何字符均被截断；

2.3. 注释

若要打印用户定义的错误信息（该信息中包含可由 @@ERROR 返回的错误号），使用 RAISERROR 而不要使用 PRINT；

3. BACKUP 备份数据库

BACKUP 命令用于备份整个数据库、事务日志，或备份一个或多个文件或文件组；
SQL Server 支持的备份类型包括：

完整数据库备份，它备份包括事务日志的整个数据库；
在完整数据库备份之间执行差异数据库备份；
事务日志备份；日志备份序列提供了连续的事务信息链，可支持从数据库、差异或文件备份中快速恢复；
文件和文件组备份；当时间限制使得完整数据库备份不切实际时，可使用 BACKUP 备份数据库文件和文件组，而不是备份完整数据库；若要备份一个文件而不是整个数据库，合理安排步骤以确保数据库中所有的文件按规则备份，同时必须进行单独的事务日志备份；在恢复一个文件备份后，使用事务日志将文件内容前滚，使其与数据库其余部分一致；

3.1. 语法

3.1.1 备份特定的文件或文件组：

BADCKUP DATABASE { datebase_name | @database_name_var}
    <file_or_filegroup> [,...n]
To <backup_device> [,...n]
[ WITH
    [ BLOCKSIZE = { blocksize | @blocksize_variable }]
    [ [,] DESCRIPTION = {'text'| @text_variable }]
    [ [,] DIFFERENTIAL ]
    [ [,] EXPIREDATE = { date | @date_var }
        | RETAINDAYS = { days | @days_var }]
    [ [,] PASSWORD = {password | @password_variable}]
    [ [,] FROMAT | NOFORMAT]
    [ [,] {INIT | NOINIT}]
    [ [,] MEDIADESCRIPTION = {'text' | @text_variable}]
    [ [,] MEDIANAME = {media_name | @media_name_variable}]
    [ [,] MEDIAPASSWORD = {mediapassword | @mediapassword_variable}]
    [ [,] NAME = { backup_set_name | @backup_set_name_var }]
    [ [,] {NOSKIP | SKIP }]
    [ [,] {NOREWIND | REWIND }]
    [ [,] {NOREWIND | REWIND }]
    [ [,] RESTART ]
    [ [,] STATS [ = pecentage ]]
]

3.1.2. 备份一个事务日志：

BACKUP LOG {database_name | @database_name_var}
{
    TO <backup_device> [,..n]
    [ WITH
        [ BLOCKSIZE = {blocksize | @blocksize_variable}]
        [ [,] DESCRIPTION = {'text' | @text_variabel}]
        [ [,] EXPIREDATE = {date | @data_var}]
            | RETAINDAYS = {days | @days_var}
        [ [,] PASSWORD = {password | @password_variable}]
        [ [,] FORMAT | NOFORMAT]
        [ [,] {INIF | NOINIF}]
        [ [,] MEIADESCRIPTION = {'text' | @text_variable}]
        [ [,] MEDIANAME = {medir_name | @media_name_variable}]
        [ [,] MEDIAPASSWORD = {mediapawwrod | @mediapassword_variable}]
        [ [,]  NAME = {backup_set_name | @backup_set_name_var}]
        [ [,] NO_TRUNCATE]
        [ [,] {NORECOVERY | STANDBY = undo_file_name}]
        [ [,] {NOREWIND | REWIND}]
        [ [,] {NOSKIP | SKIP}]
        [ [,] {NOUNLOAD | UNLOAD}]
        [ [,] RESTART ]
        [ [,] STATS [ = percentage ]]
    ]
}

3.1.3. 截断事务日志：

BACKUP LOG {database_name | @database_naem_var}
{
    [ WITH
        { NO_LOG | TRUNCATE_CNLY}]
}

3.2. 参数

DATABASE
指定一个完整的数据库备份；假定制定了一个文件和文件组的列表，那么仅有这些被指定的文件和文件组被备份；
BLOCKSIZE = { blocksize | @blocksize_variable}
用字节数来指定物理块的大小；在 Windows NT 系统上，默认设置是设备的默认大小；
一般情况下，当 SQL Server 选择合适设备的块大小时不需要此参数，在基于 Windows 2000 的计算机上，设置是 65536（64KB，是SQL Server 支持的最大大小）；对于磁盘，BACKUP 自动决定磁盘设备合适的块大小；
注意： 若要将结果备份存储到 CD-ROM，再从 CD-ROM 中恢复，要将 BLOCKSIZE 设为 2048；
DESCRIPTION = {'text' | @text_variable}
指定描述备份集的自由格式文本，该字符最长能哟 255 个字符；
DIFFERENTIAL
指定数据库备份或文件备份应该与上一次完整备份后改变的数据库或文件部分保持一致；
差异备份一般比完整备份占用更少的空间，对于上一此完整备份时备份的全部单个日志，使用该选项能不必进行备份；
EXPIREDATE = {date | @date_var}
指定备份集到其和允许被重写的日期；若将该日期作为变量（@date_var）提供，则能将该日期指定为字符串常量（@date_var）、字符串数据类型变量（ntext、text 数据类型除外）、smalldatetime 或 datetime 变量，且该日期必须符合已配置的系统 datetime 格式；
RETAINDAYS = {days | @days_var}
指定必须经过多少天才能重写该备份媒体；若用变量（@days_var）指定，该变量必须为整型；
PASSWORD = {password | @password_variable}
为备份集设置密码，PASSWORD 是一个字符串，若为备份集定义了密码，必须提供这个密码才能对该备份集执行任何还原操作；
注意：备份集密码防止未经授权即通过 SQL Server 工具访问备份集的内容，但不能防止重写备份集

3.3.注释

能将数据库或日志备份追加到任何磁盘或磁带设备上，从而使得数据库和它的事务日志能存储在一个物理位置中；
当数据库正在使用时，SQL Server 使用一个联机备份过程来对数据库进行备份；
若在这些操作正在进行时启动备份，备份将被终止；若正在进行备份时，试图进行这些操作，则操作会失败；
只要操作系统支持数据库的排序规则，就能在不同的平台之间执行备份操作，即使这些平台使用不同的处理器类型；

3.4.权限

BACKUP DATABASE 和 BACKUP LOG 权限默认情况下授予 sysadmin 固定服务器角色和 db_owner 及 db_backupoperator 固定数据库角色的成员；

4. RESTORE 还原数据库

RESTORE 命令用于还原使用 BACKUP 命令所做的备份；
SQL Server 支持还原的类型如下所示：

还原整个数据库的完整数据库还原；
完整数据库还原和差异数据库还原，使用 RESTORE DATABASE 语句还原差异备份；
事务日志还原；
个别文件和文件组还原，文件和文件组能通过文件或文件组备份操作完成，也能通过完整数据库备份操作完成；在还原文件或文件组时，必须应用事务日志；文件差异备份能在完成完整文件还原后还原；
创建并维护热备用服务器或备用服务器；

4.1. 语法

4.1.1. 还原整个数据库：

RESTORE DATABASE {database_name | @database_name_var}
[ FROM <backup_device> [,...n ] ]
[ WITH
    [ RESTRICTED_USER]
    [ [,] FILE = {file_number | @file_number} ]
    [ [,] PASSWORD = {password | @pass2word_Variable} ]
    [ [,] MEDIANAME = {media_name | @media_name_variable} ]
    [ [,] MEDIAPASSWORD = {mediapassword | @mediapassword_variable} ]
    [ [,] MOVE 'logical_File+_name' TO 'operating_system_file_name' ]
        [,...n]
    [ [,] KEEP_REPLICATION ]
    [ [,] {NORECOVERY | RECOVERY | STANDBY = undo_file_name} ]
    [ [,] {NOREWIND | REWIND} ]
    [ [,] {NOUNLOAD | UNLOAD} ]
    [ [,] REPLACE ]
    [ [,] RESTART ]
    [ [,] STATS [ = percentage ] ]             
]

4.1.2. 还原数据库的部分内容

RESTORE DATABASE {database_name | @database_name_var}
    <file_or filegroup> [,...n]
[ FROM <backup_device> [,..n] ]
[ WITH
    { PARTIAL }
    [ [,] FILE = {file_number | @ file_number} ]
    [ [,] PASSWORD = {password | @password_varidable} ]
    [ [,] MEDIANAME = {media_name | @ media_name_variable} ]
    [ [,] MEDIPASSWORD = {mediapssword | @mediapassword_variable} ]
    [ [,] MOVE 'logical_File_name' TO 'operating_system_File_name']
        [,...n]
    [ [,] NORECOVERY ]
    [ [,] {NOREWIND | REWIND} ]
    [ [,] {NOUNLIAD | UNLOAD} ]
    [ [,] REPLACE ]
    [ [,] RESTRICTED_USER ]
    [ [,] RESTART ]
    [ [,] STATS [ = percntage] ]    
]

4.1.3. 还原特定的文件或文件组：

RESRORE DATABASE {database_name | @database_name_var}
    <file_or_filegroup> [,...n]
[ FROM <backup_device> [,..n] ]
[ WITH 
    [ RESTRICRED_USER ]
    [ [,] FILE = {file_number | @file_number} ]
    [ [,] PASSWORD = {password | @password_variable} ]
    [ [,] MEDIANAME = {media_name | @media_name_variable} ]
    [ [,] MEDIAPSSSWORD = {mediapassword | @mediapassword_variable} ]
    [ [,] MOVE 'logical_file_name' TO 'operating_system_file_name' ]
        [,...n]
    [ [,] NORECOVERY ]
    [ [,] {NOREWIND | REWIND} ]
    [ [,] {NOUNLOAD | UNLOAD} ]
    [ [,] REPLACE ]
    [ [,] RESTART ]
    [ [,] SRATS [ = percentage ] ]
]

4.1.4. 还原事务日志：

RESTORE LOG {database_name | @database_name_var}
[ FROM <backup_device> [,..n] ]
[ WITH
    [ RESTRICTED_USER ]
    [ [,] FILE = {file_number | @file_number} ]
    [ [,] PASSWORD = {password | password_variable} ]
    [ [,] MOVE 'logical_file_name' TO 'operatring_system_file_name' ]
        [,...n]
    [ [,] MEDIANAME = {media_name | @media_name_variable} ]
    [ [,] MEDIAPASSWORD = {mediapassword | @mediapassword_variable} ]
    [ [,] KEEP_REPLICATION ]
    [ [,] { NORECOVERY | RECOVERY | STANDBY = nudo_file_name} ]
    [ [,] { NOREWIND | REWIND } ]
    [ [,] { NOUNLOAD | UNLOAD } ]
    [ [,] RESTART ]
    [ [,] STATS { = percentage } ]
    [ [,] STOPAT = { date_time | @date_time_var } ]
        | [,] STOPATMARK = 'kark_name' [ AFTER datetime ]
        | [,] STOPBEFOREMARK = 'mark_name' [ AFTER datetime ]
]

4.2. 参数

DATABASE
指定从备份还原整个数据库；若指定了文件和文件组列表，就只还原那些文件和文件组；
{database_name | @database_name_var}
将日志或整个数据库还原到数据库；若将其作为变量(@database_name_var)提供，就能将该名称指定为字符串常量(@database_name_var=database name)或字符串数据类型(ntext、text 类型除外）的变量；
FROM
指定从还原备份的备份设备；若没有指定 FROM 子句，就不会发生备份还原，而是恢复数据库；
可用省略 FROM 子句的办法尝试恢复通过 NORECOVERY 选项还原的数据库，或切换到一台备用服务器上；若省略 FROM 子句，就必须指定 NORECOVERY、RECOVERY、STANDBY；
<backup_device>
指定还原操作要使用的逻辑或物理备份设备；
RESTRICTED_USER
限制只有 db_owner、dbcreator、sysadmin 角色的成员才能访问新进还原的数据库；
FILE = {file_number | @file_number}
标识要还原的数据集；
PASSWORD = {password | @password_Variable}
提供备份集的密码；PASSWORD 是一个字符串，若在创建爱你备份集时提供了密码，从备份集还原操作时就必须提供密码；

4.3. 注释

在还原过程中，指定的数据库必须不处于使用状态；指定数据库中的任何数据将由还原的数据替换；
只要操作系统支持数据库排序规则，就能跨平台执行还原操作，即使这些平台使用不同的处理器类型；

4.4. 权限

若不存在要还原的数据库，用户就必须有 CREATE DATABASE 权限才能执行 RESTORE 命令；
若存在该数据库，RESTORE 权限就默认授予 sysadmin 和 dbcreator 固定服务器角色成员以及该数据库的所有者（dbo）；
RESTORE 权限被授予哪些成员资格信息始终可由服务器使用的角色，因为只有在固定数据库能访问且没有损坏时（在执行 RESTORE 时并不会总是这样）才能检查固定数据库角色成员资格，所以 db_owne 固定数据库角色成员没有 RESTORE 权限；
用户能为媒体集、备份集或两者指定密码；若为媒体集指定了密码，则用户只是适当的固定服务器和数据库角色成员还不足以执行备份；
用户还必须体用媒体密码才能执行这些操作，同样，除非在还原命令正确的媒体集密码和备份集密码，否则不能执行还原操作；

5. SELECT 返回数据记录

从数据库中检索行，并允许一个或多个表中选择一个或多个行或列；
虽然 SELECT 语句的完整性语法比较复杂，但是其主要的子句能归纳如下：

SELECT select_list
[ INTO new_table ]
FROM table_soure
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_experssion [ ASC | DESC] ]

能在查询之前使用 UNION 运算符，以将查询的结果组合成单个结果集；

5.1. 语法

SELECT statement ::=
    <query_expression>
    [ ORDER BY {order_by_expression | column_position [ ASC | DESC ] } [,..n] ]
    [ COMPUTE 
        { { AVG | COUNT | MAX | MIN | SUM } (opression)} [,..n]
        [ BY expression [,..n] ]
    ]
    [ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT }
        [ , XMLDATA ]
        [ ,ELEMENTS ]
        [,BINARY base64 ]
     }
]
    [ OPTION ( <query_hint> [,...n] ) ]
    
 <query expression> ::=
     SELECT [ ALL | DISTINCF ]
         { <query specification> | ( <query expression> ) }
         [ UNION [ ALL ] <query specification | (query expression)> [,..n] ]
         
<query specification> ::=
    SELECT [ ALL | DISTINCT ]
        [ [ { TOP integer | TOP integer PERCENT } [ WITH TIES ] ]
        <select_list>
    [ INTO new_table ]
    [ FROM { <table_source> } [,..n] ]
    [ WHERE <search_condition> ]
    [ GROUT BY [ ALL | group_by_expression [,..n] 
        [ WITH { CUBE | ROLLUP} ] 
    ]
    [ HAVING <search_condition> ]

5.2. 注释

下面是 SELECT 相关子句：

ELECT 子句
NTO 子句
ROM 子句
WHERE 子句
GROUP BY 子句
HAVING 子句
UNION 运算符
ORDER BY 子句
COMPUTE 子句
FOR 子句
OPTION 子句

6. SET 设置命令

T-SQL 语言提供了一些 SET 语句，这些语句能更改特定信息的当前会话处理；
SET 语句的分类如下表：
20220403

6.1. SET 语句

SET 语句较多，下面是两个 SET 相关子句：

日期和时间语句：

SET DATEFIRST
SET DATEORMAT

锁定语句：

SET DEADLOCK_PRIORITY
SET LOCK_TIMEOUT

6.2. 使用 SET 语句时的注意事项

除 SET FIPS_FLAGGER、SET OFFSETS、SET PARSEONLY 和 SET QUOTED_IDENTIFIER 外，所有其他 SET 语句均在执行或运行时设置；SET FIPS_FLAGGER、SET OFFSETS、SET PARSEONLY 和 SET QUOTED_DIENTIFIER 语句分析时设置；
若在存储过程中设置 SET 语句，则从存储过程返回控制后将还原 SET 选项的值；所以，在动态 SET 中指定 SET 语句不影响动态 SQL 语句之后的语句；
存储过程与在执行时指定的 SET 设置一起执行，但 SET ANSI_NULLS 和 SET QUOTED_IDENTIFIER 除外；指定 SET ANSI_NULLS 或 SET QUOTED_IDENTIFIER 的存储过程使用在存储过程创建是指定的设置；在存储过程内使用任何 SET 设置，都将忽略该设置；
sp_configure 的 user options 设置允许服务器范围的设置，并能跨多个数据库进行；该设置的行为还类似显式 SET 语句，在登录时出现该设置的情况除外；
数据库设置（使用 sp_dboption 设置的）仅在数据库上有效，并且只有在未显示设置的情况下才生效；数据库设置代替服务器选项设置（使用 sp_configure 设置）；
对于任何带 ON 和 OFF 设置的 SET 语句，能多多个 SET 选项指定 ON 或 OFF 设置；
SET 语句设置将代替数据库选项设置（使用 sp_dboption 设置的）；若用户在连接到数据库时所基于的值是由于先前使用 sp_configure user options 设置而生效的，或所基于的值适用于所有 ODBC 和 OLE/DB 连接，则一些连接设置将自动设置为 ON；
当全局变量或快捷 SET 语句（如 SET ANSI_DEFAULTS）设置多个选项时，发出快捷 SET 语句将为所有受快捷 SET 语句影响的个别 SET 选项，则个别 SET 语句将替代相应的快捷设置；
当使用批处理时，数据库上下文由使用 USE 语句建立到的批处理决定；在存储过程的外部执行的以及批处理中特殊查询和所有其他语句，继承使用 USE 语句建立的数据库和连接的选项设置；
当从批处理或另一个存储过程执行某个存储过程时，该存储过程将根据所在的数据库中当前设置的选项值执行；
当 T-SQL 语句引用驻留在多个数据库中的对象时，当前数据库上下文和当前连接上下文（若位于批处理中，则是由 USE 语句定义的数据库；若位于存储过程中，则是包含该存储过程的数据库）将应用于该语句；
当在计算列或索引视图上创建和操作索引时，必须将 SET 选项 ARITHABORT、CONCAT_NULL_YIELDS_NULL、QUOTED_IDENTIFIER、ANSI_NULLS、ANSI_PADDINIG 和 ANSI_WARNINGS 设置为 ON，必须将选项 NUMERIC_ROUNDABORT 设置为 OFF；

若这些选项中任何一个没哟设置为所要求的值，则在索引视图上火计算列带索引的表上进行的 INSERT、UPDATE 和 DELETE 操作将失败；SQL Server 将发出一个错误，列出所有设置不正确的选项；同时，SQL Server 将在这些表或索引视图上处理 SELECT 语句，仿佛计算或视图上不存在索引一样；

7. SHUTDOWN 关闭数据库

当用户不需要使用 SQL Server 数据库及实例时，可选择关闭数据库；
T-SQL 语句为用户提供了关闭数据库命令 SHUTDOWN，其功能为立即停止 SQL Server；

7.1 语法

SHUTDOWN [ WITH NOWAIT ]

7.2 参数

WITH NOWAIT
立即关闭 SQL Server 而不在每个数据库内执行检查点；在尝试终止所有用户进程后退出 SQL Server，并对每个活动事务执行回滚操作；

7.3 注释

除非 sysadmin 固定服务器角色成员指定 WITH NOWAIT 选项，否则 SHUTDOWN 尝试关闭 SQL Server 时的顺序为：

禁用登录（sysadmin 固定服务器角色成员除外）；若要查看所有当前用户的列表，执行 sp_who;
等待当前正在执行的 T-SQL 语句或存储过程执行完毕；若要查看所哟活动进行和所得列表，执行 sp_lock 和 sp_who；
在每个数据库内执行检查点，当 sysadmin 固定服务器角色成员重新启动 SQL Server 时，使用 SHUTDOWN 语句能将需要做的自动回复工作减到最小；

还能使用以下工具和方法停止 SQL Sever，每个工具或方法都在所有数据库内执行检查点；
从数据高速缓存中刷新所有提交的数据后，通过下列公户和方法停止服务器：

使用 SQL Server 企业管理器；
在命令提示符下使用 net stop mssqlserver；
使用控制面板中的服务；
使用 SQL Server 服务管理器；

若是从命令提示符下启动的 sqlser.exe，按 Ctrl+C 键能关闭 SQL Server，但不执行检查点；
注意： 通过 SQL Server 管理器、控制面板和 SQL Server 服务管理器停止 SQL Server 的方法所生成的服务器控制信息与使用 SERVICE_CONTROL_STOP 停止 SQL Server 所生成的相同；

7.4. 权限

SHUTDOWN 权限默认授予 sysadmin 和 serveradmin 固定服务器角色的成员且不可转让；

8. USE 打开数据库

当 SQL Server 实例中包括多个数据库时，能通过 USE 语句将数据库是上下文更改指定数据库；

8.1. 语法

USE [ database ]

8.2. 参数

database 是用户上下文要切换到的数据库名称，数据库名称必须符合标识符的规则；

8.3. 注释

USE 在编辑和执行期间均可执行，且立即生效，所以，出现在批处理中 USE 语句之后的语句将在指定数据库中指行；
用户在登录到 SQL Server 时，通常被自动连接到 master 数据库，除非为每个用户的登录 ID 设置了各自的默认数据库，每个用户必须执行 USE 语句从 master 切换到另一个数据库；
若要将上下文更改为不同的数据库，则用户必须有那个数据库的安全账户；由数据库所有者提供此数据库的安全账户；

8.4 权限

USE 权限默认授予那些由执行 sp_adduser 的 dbo 和 sysadmin 固定服务器角色，或由执行 sp_grantdbaccess 的 sysadmin 固定服务器角色以及 db_accessadmin 和 db_owner 固定数据库角色指派了权限的用户；若目的的数据库中存在来宾用户，则在该数据库中没有安全账户的用户依然能范围；