SQL 基础知识

                  **SQL 基础知识**

一、变量
1、变量分类：局部变量，全局变量
2、局部变量：局部变量以@开头，作用范围仅在程序内部。数据类型除（text,ntext,image）类型外的所有系统数据类型和用户自定义类型
如：decalre @testname char(10)
3、局部变量赋值：select 语句 ，set 语句
如：select @testname=1 或select @testname=b from a
set @testname=‘3’;
4、全部变量： 全部变量以@@开头，SQL Server 系统内部事先定义好的变量，不需用户参与定义，作用范围任何程序均可随时调用。通常存储一些SQL Server 的配置设定值和效能统计数据。
5、常用全局变量
@@CONNECTIONS:记录自最后一次服务器启动以来，所有针对这台服务器进行的连接次数，包括没有连接成功的尝试。
@@CPU_BUSY:记录自上次启动的工作时间，无论连接成功还是失败，以ms为单位的CPU工作时间。
@@CURSOR_ROWS:返回在本次服务器连接中，打开游标取出数据行的行数。
@@DBTS:返回当前数据库中timestamp数据类型的当前值
@@ERROR：返回执行上一条SQL语句所返回的错误代码。（0，成功，其他错误代码）在每一条语句执行后被清除并重置。
@@FETCH_STATUS:返回上一次使用游标FETCH操作所返回的状态值，且返回值为整型（0，成功，-1，失败或此行不在结果集中，-2，被提取的行不存在）
@@IDENTITY:返回最近一次插入的identity列的数值，返回值为numeric
@@IDLE:返回以ms为单位计算SQL Server 服务器自最近一次启动以来停顿状态的时间
@@IO_BUSY:返回以ms为单位计算SQL Server 服务器自最近一次启动以来输入和输出使用的时间
@@LOCK_TIMEOUT:当前对数据锁定的超时设置
@@PACK_RECEIVED:SQL Server服务器自最近一次启动以来从网络接收数据分组的总数
@@PACK_SENT:SQL Server服务器自最近一次启动以来从网络发送数据分组的总数
@@PROCID：当前存储过程的ID标识
@@REMSERVER：在登录记录中记载远程的SQL Server服务器名
@@ROWCOUNT ：上一条SQL语句影响数据行的行数。所有不影响数据库数据的SQL语句，返回0.
@@SPID:当前服务器进行的ID标识
@@TOTAL_ERRORS:自服务器启动以来，遇到的读写错误的总数
@@TOTAL_READ:自服务器启动以来，读磁盘的次数
@@TOTAL_WRITE:自服务器启动以来，写磁盘的次数
@@TRANCOUNT:当前连接中，处于活动状态事务的总数
@@VERSION:当前SQL Server服务器安装日期，版本以及处理器的类型
二、运算符：
1、比较运算符：
!=(不等于)，!<(不小于),!>(不大于)不是标准的ASNI运算符。
比较运算符的结果为布尔数据类型，值为：FALSE,TRUE,UNKNOWN.
不能将布尔数据类型指定为表列或变量的数据类型，也不能在结果集中返回布尔数据类型
2、逻辑运算符：
ALL:如果一个比较集中全部是TRUE，则值为TRUE(注意子查询必须返回一列值，以便进行逐一比较)
ANY:如果一个比较集中任何一个为TRUE，则值为TRUE(注意子查询必须返回一列值，以便进行逐一比较)
SOME:如果一个比较集中某些为TRUE，则值为TRUE.(注意子查询必须返回一列值，以便进行逐一比较)
NOT,AND ,OR 同时出现在一个表达式中，优先级NOT ,AND, OR
3、通配符(LIKE)
%：包含零个或更多字符的任意字符。
-（下划线）：任意单个字符 如：k_ 可表示ka,kb,kc
[]:指定范围[a~f]或集合[abcdf]中的任意单个字符
[^]:不属于指定范围[a~f]或集合[abcdf]中的任意单个字符
三、流程控制
1、BEGIN …END :将多个SQL语句组合为一个逻辑块，必须成对使用，可以嵌套使用
2、IF IF ……ELSE:选择判断语句 ，程序块使用begin ……end包含（单条SQL，可以不加）。
3、CASE:实现多重选择的情况，
case express
when express then result
else result
END
或
case
when bool then result
else result
END
4、WHILE:条件为true时，循环执行SQL语句， 为FALSE,结束循环。如果一组SQL，使用begin……end，
5、CONTINUE,跳过CONTINUE命令之后的语句，回到循环开始。
6、BREAK，完全跳出循环，结束整体循环
7、RETURN:从查询或过程中无条件退出，位于RETURN之后的语句不会被执行
8、GOTO: 改变程序执行的流程，使程序跳到标识符指定的程序继续执行
goto 标识符
标识符需要在名字后加“：”
如： goto test
test: djdjlajfkdal
9、waitfor: 指定触发器，存储过程或事务执行的时间、时间间隔或事件；还可用来暂时停止程序的执行，直到锁设定的时间已过才继续往下执行。
waitfor delay|time 时间
delay：设定等待的时间，最多可大24小时
time ：等待结束的时间
时间 ：必须是datetime类型的数据，如"11:23:12",不能包含日期）
四、常用命令
1、DBCC:用于验证数据库完整性，查找错误和分析系统使用情况等
DBCC CHECKALLOC: 检查指定数据库的硬盘空间分配结构的一致性
如：dbcc checkalloc (‘db_test’)
DBCC SHOWCONTIG:显示指定表的数据和索引的碎片信息
如： decalre @id int ,@idindex int
set @Id=object_id(‘tb_course’)
select @idindex=index_id
from sys.indexes
where object_id=@id
and name=‘pk_tb_course’
DBCC SHOWCONTIG(@id,@idindex)
2、CHECKPOINT:检查当前工作的数据库中被更改过的数据页或日志页，并将这些数据从数据缓冲器中强制写入硬盘。
use db_test checkpoint 时间整数单位为秒（可省略，则自动调整检查持续事件）
3、DECLARE:声明一个或多个局部变量，游标变量，表变量
4、PRINT:向客户端返回一个用户自定义的信息，即下是一个字符串，局部变量，或全部变量的内容 PRINT @TEST|@@ERROE|‘FDJAKD’
5、RAISERROR：是一个用于生成错误消息的函数，它可以用来在存储过程、触发器或任何 T-SQL 脚本中引发自定义错误。这对于向调用者提供有关错误的详细信息或执行流程中的特定条件非常有用。
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,…n ] ] )
[ WITH option [ ,…n ] ]
msg_id：是存储在 sys.messages 中的用户定义错误消息的 ID。
msg_str：是用户定义的错误消息字符串。
severity：指定错误的严重性级别。
state：提供有关错误的额外信息，通常用于调试。
argument：替换 msg_str 或 sys.messages 消息中的占位符的参数。
option：控制消息的行为的选项，如 WITH NOWAIT、WITH SETERROR 等。

如：RAISERROR (‘Error occurred!’, – Message text.
16, – Severity.
1 – State.
) WITH NOWAIT, LOG（立即显示错误消息（不等待任何挂起的 I/O 操作完成），并将消息记录到 SQL Server 错误日志中）;

如：假设你已经在 sys.messages 中添加了一个错误消息，其 ID 为 50001，消息文本为 ‘Error %d occurred in procedure %s.’。：
RAISERROR (50001, 16, 1, 123, ‘MyProcedure’)
生成错误消息 ‘Error 123 occurred in procedure MyProcedure.’

6、READTEXT:读取text,ntext,image 列中的值，从指定的位置读取指定的字符数
READTEXT { TABLE.COLUMN TEXT_PRT OFFSET SIZE }[HOLDLOCK]
如：
DECLARE @text_ptr varbinary(16)
SELECT @text_ptr = TEXTPTR(Resume) FROM Employees WHERE EmployeeID = 1

DECLARE @resume varchar(max)
SET @resume = (SELECT CONVERT(varchar(max), READTEXT(Employees.Resume, @text_ptr, 0, NULL)))
PRINT @resume

7、BACKUP:将数据库内容或其事务处理日志备份到存储介质上。
backup database databsename
to back_drive
[next-mirror-to]
[with differential]

• 完整备份：使用BACKUP DATABASE命令，并指定数据库名称和备份目标位置。例如，BACKUP DATABASE AdventureWorks TO DISK='C:\AdventureWorks.BAK'。
• 差异备份：在完整备份之后，使用BACKUP DATABASE命令并添加WITH DIFFERENTIAL选项来捕获自上次完整备份以来所做的更改。例如，BACKUP DATABASE AdventureWorks TO DISK='C:\AdventureWorks.DIF' WITH DIFFERENTIAL。
• 文件备份和文件组备份：使用BACKUP DATABASE命令的FILE或FILEGROUP选项来备份数据库中的特定文件或文件组。BACKUP DATABASE YourDatabaseName
  FILE = ‘YourFileLogicalName’
  TO DISK = ‘D:\Backups\YourDatabaseName_YourFileLogicalName.bak’
  WITH NOFORMAT, NOINIT, NAME = ‘YourDatabaseName_YourFileLogicalName_FullBackup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

BACKUP DATABASE YourDatabaseName
FILEGROUP = ‘YourFileGroupName’
TO DISK = ‘D:\Backups\YourDatabaseName_YourFileGroupName.bak’
WITH NOFORMAT, NOINIT, NAME = ‘YourDatabaseName_YourFileGroupName_FullBackup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

8、RESTORE:将数据库或其事务处理日志备份文件由存储介质还原到SQL SERVER系统

• 基本格式：

RESTORE DATABASE [数据库名]  
FROM DISK = '[备份文件路径]'  
[WITH 选项[, ...]]  

• 常见选项：
  • WITH MOVE：指定数据文件和日志文件的物理路径，以便在恢复时将它们移动到新的位置。
  • WITH REPLACE：如果目标数据库已存在，则替换它。
  • WITH NORECOVERY：使数据库处于无法访问的状态，以便可以进一步应用事务日志备份。
  • WITH STANDBY：在恢复过程中创建一个备用数据库，以便在恢复过程中仍然可以访问原始数据库。
  • WITH STATS：显示恢复操作的进度信息。

9、shutdown 立即停止SQL server执行
shutdown with nowait
有nowait参数时，立即终止所有的用户进程。并对每一个现行的事务发生一个回滚后，退出SQL
没有nowait:终止任何用户登录SQL SERVER ,等待未完成的SQL命令或存储过程执行功能完毕，在每个数据库中执行checkpoint命令，停止SQL server 执行

10、WRITETEXT：将一段长文本或二进制数据写入到指定的轻量级二进制形式的数据类型中，如text、ntext或image字段
DECLARE @TextPtr VARBINARY(16)
SELECT @TextPtr = TEXTPTR(MyTextColumn) FROM MyTable WHERE … – 获取有效的文本指针
WRITETEXT MyTable.MyTextColumn @TextPtr ‘这是要写入的数据’ – 写入数据