数据库系统---T_SQL
注释符与运算符
--单行注释
/**/ 多行注释
算术：+ - * / %
赋值：= 通常用于set 和select语句中
比较：> = < >= <= <>
逻辑： not and or
字符串连接：+
变量
全局变量的声明
全局变量不是由用户定义，而是由服务器级定义，用户只能使用，引用全局变量，以标记符@@开头
例：输出当前版本信息
select @@version as '版本',@@servername as '服务器名称'
局部变量的声明
局部变量被引用时要在其名称前加上标志“@”，而且必须先用DECLARE命令定义后才可以使用。其说明形式如下：
DECLARE @变量名 变量类型[，@变量名 变量类型…]
局部变量的赋值
在Transact-SQL中不能像在一般的程序语言中一样使用“变量=变量值”来给变量赋值，必须使用SELECT 或SET命令来设定变量的值。其语法如下：
SELECT @局部变量1=变量值1
SET @局部变量=变量值
例 声明一个长度为4个字符的变量sno,并赋值
declare @sno char(4)--存放学号
select @sno = ‘S003’--赋值
局部变量的输出
Print ：一次只能输出一个变量的值
Select :一次可以输出多个变量的值
变量的默认值和作用域
变量的作用域（可用范围和生命周期）为当前批处理新声明的变量默认值为NULL,必须初始化
use sss
declare @test1 int,
@test2 varchar(25);
select @test1,@test2;
set @test1=1;
set @test2='a value';
select @test1,@test2;
go
定义两个变量，将sc表中的最高分和最低分分别赋值给这两个变量
use test1
declare @max1 int,@min1 int
set @max1 =(select max(grade) from SC )--select max(grade) from SC（子查询）
set @min1 =(select min(grade) from SC )
select @max1 max,@min1 min--@变量 别名
go
条件控制语句
IF…ELSE
IF…ELSE语句是条件判断语句。IF…ELSE语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。其语法如下：
IF <条件表达式>
<命令行或程序块>
[ELSE <条件表达式>
<命令行或程序块>]
其中，<条件表达式>可以是各种表达式的组合，但表达式的值必须是逻辑值“真”或“假”；ELSE子句是可选的，最简单的IF语句没有ELSE子句部分。
IF…ELSE可以进行嵌套，在Transact-SQL中最多可嵌套32级
例：从SC（选修表）中求出学号为“201215121”的同学的平均成绩，如果此平均成绩大于或等于90分，则输出“优秀”字样。
IF (select avg(grade) from SC
where sno='201215121'
group by sno)>=90
print'优秀';
else
print '一般';
例：判断张三同学的年龄，如果年龄在[18,22]之间，显示‘合适’，否则显示‘不合适’
declare @age int;
select @age=sage from student where sname='张三'
if @age between 18 and 22
print '合适'
else
print '不合适'
CASE:多分支选择情况（多于2种分支）
CASE 命令有两种语句格式：
（1）
CASE<运算式>
WHEN <运算式> THEN <运算式>
…
WHEN <运算式> THEN <运算式>
[ELSE <运算式>]
END
该语句的执行过程是：
将CASE后面表达式的值与各WHEN子句中的表达式的值进行比较，如果二者相等，则返回THEN后的表达式的值，然后跳出CASE语句，否则返回ELSE子句中的表达式的值。
ELSE子句是可选项。当CASE语句中不包含ELSE子句时，如果所有比较失败时，CASE语句将返回NULL。
例 从学生表中，选取Sno ，Ssex ，如果Ssex为“男”则显示“Male”，如果为“女”，显示“Female”。
SELECT Sno 学号,
CASE Ssex
WHEN '男' THEN 'Male'
WHEN '女' THEN 'Female'
END AS '性别'
FROM Student
（2）
CASE
WHEN <条件表达式> THEN <运算式>
…
WHEN <条件表达式> THEN <运算式>
[ELSE <运算式>]
END
Case后面没有条件表达式
该语句的执行过程是：
首先测试WHEN后的表达式的值。如果其值为真，则返回THEN后面的表达式的值，否则测试下一个WHEN子句中的表达式的值。如果所有WHEN子句后的表达式的值都为假，则返回ELSE后的表达式的值，此时，如果在CASE语句中没有ELSE子句，则CASE表达式返回NULL。
例：从SC表中查询所有同学选课成绩情况，将百分制转换为等级制；凡成绩为空者显示“未考”、小于60分显示“不及格”、60分至70分显示“及格”、70分至80分显示“中等”、80分至90分显示“良好”、大于或等于90分时显示“优秀”。
SELECT Sno,Cno,
CASE
WHEN grade IS NULL THEN '未考'
WHEN grade < 60 THEN '不及格'
WHEN grade >= 60 AND grade <70 THEN '及格'
WHEN grade >= 70 AND grade <80 THEN '中等'
WHEN grade >= 80 AND grade <90 THEN '良好'
WHEN grade >= 90 THEN '优秀'
END AS '等级'
FROM SC
If Exists()
将SQL语句是否返回了行作为条件，速度快，不需要确定返回的总行数。
declare @name varchar(20)
set @name='雷吉平'
if exists(select COUNT(*) from student,sc where student.sno=sc.sno and sname=@name)
print'该同学已选课！'
else
print'该同学未选课！'
循环控制语句
While…continue…break
While 逻辑判断语句
SQL语句
Break\contine
Continue:可以让程序跳过continue命令之后的所有语句，回到while循环第一行,继续进行下一次循环
Break:使程序跳出循环，结束while语句的执行
While
在条件为真的情况下不断执行代码。
declare @temp int
set @temp=0
while @temp<3
begin
print 'tested condition' +Str(@temp);
set @temp=@temp+1;
end
BEGIN…END
BEGIN…END语句能够将多个Transact-SQL语句组合成一个语句块，并将在BEGIN…END内的所有程序视为一个单元处理。其语法形式为：
BEGIN
<命令行或程序块>
END
在BEGIN…END中可嵌套另外的BEGIN…END来定义另一程序块。
延时语句
Waitfor语句
又称延迟语句，用于指定触发器、存储过程或事务执行的时间或时间间隔；还可以暂停程序的运行，直到所设定的等待时间已过或所设定的时间已到才继续往下执行。
Waitfor{
delay<‘时间’>|time<‘时间’>}
delay:指定延迟的时间，最长为24小时
Time:指定程序执行的时间点，只能用24小时制的时间值。
declare @name varchar(50)
set @name='admin'
begin
waitfor delay '00:00:10'
print @name
end
延迟10秒输出结果，延迟时间和时刻的格式为“HH:MM:SS”，时间长度不能超过24小时
Goto
只能够跳转到同一个批处理或过程中的标签处，除用于跳转到批处理或过程末尾的错误处理程序外，很少使用。
要创建标签，只需指定标签名并在后面加上冒号。
LabelName:;
例：
goto ErrorHandler
print 'more code'
ErrorHandler:
print 'Logging the error'
Return [integer_expression]
用于使程序从一个查询、存储过程或者批
处理中无条件地返回，其后面的语句不再执行。
如果在存储过程中使用return语句，那么此
语句可以用来指定返回给调用应用程序、批处
理或过程的整数。
0:程序执行成功
-1：找不到对象
-2：数据类型错误
-3：死锁
错误捕获语句
@@error
用于捕捉上一条T-SQL语句的错误号，在每一条语句执行后被清除并重置。没有错误返回0，否则返回错误代号。
Try catch
类似于C++中的异常处理，当执行try语法块中的代码出现错误时，系统会把控制传递到catch语法去处理。
begin try
select 1/0
end try
begin catch
select
ERROR_NUMBER() as errornumber,--错误号
ERROR_SEVERITY () as errorseverity,--严重性
ERROR_STATE() as errorstate,--错误状态号
ERROR_LINE () as errorline,--行号
ERROR_MESSAGE () as errormessage;--错误文本
end catch
数据库的创建与管理
使用T—SQL创建数据库
CREATE DATABASE 数据库名
[ON [PRIMARY]
{
(NAME=数据文件的逻辑名称，
‘数据文件的路径和文件名’, (要包含文件扩展名)
SIZE=数据文件的初始容量，(不能小于Model数据库的初始文件大小)
MAXSIZE=数据文件的最大容量，
数据文件的增长量)
}[,…n]
LOG ON
{
(NAME=事务日志文件的逻辑名称， '事务日志文件的物理名称'，
SIZE=事务日志文件的初始容量，
MAXSIZE=事务日志文件的最大容量，
事务日志文件的增长量)
}[,…n]]
例：
CREATE DATABASE [dd] on primary
(
NAME=‘dd_db’, -- 创建数据库文件
FILENAME='c:\SQL Server 2017\dd_db.MDF' , -- 要确保文件夹存在
SIZE=10, --初始大小
MAXSIZE=100, --最大容量
FILEGROWTH=10 --增长方案
)
LOG ON -- LOG ON 主要是针对日志文件的编写
(
NAME='dd_log',
FILENAME='c:\SQL Server 2017\dd_log.LDF' , -- 要确保文件夹存在
SIZE=10, --初始大小
MAXSIZE=100, --最大容量
FILEGROWTH=10 --增长方案
)
修改数据库
ALTER DATABASE 数据库名
｛[add file 文件描述] [to 文件组名] 新增数据文件
｜[add log file 文件描述] 新增日志文件
｜[remove file 逻辑文件名 ] 删除数据库文件
｜[add 文件组名] 新增文件组
｜[remove 文件组名] 删除文件组
｜[modify file 文件修改内容描述] 修改数据文件属性｝
查看数据库文件属性
EXEC sp_helpfile
系统存储过程，用于查看数据库有哪些文件及文件属性。
删除数据库
Drop database 数据库名称
存储过程
是一种高效、安全地访问数据库的方法
主要用于提高数据库检索速度，也经常被用来访问数据或管理被修改的数据
存储过程（stored Procedure)是一组完成特定功能的SQL语句的集合，经编译后存储在数据库中，用户通过指定存储过程的名称和参数来执行存储过程
存储过程就像数据库中运行的方法
常用系统存储过程
创建存储过程
CREATE PROCEDURE 存储过程名
@参数1 数据类型 = 默认值 OUTPUT,
@参数n 数据类型 = 默认值 OUTPUT
AS
SQL语句
参数说明：
参数可选
参数分为输入参数、输出参数
输入参数允许有默认值
执行存储过程
调用
Exec[ute]<过程名>[[@<参数名>=]<参数>…[<版本号>]
删除
Drop procedure <存储过程名组>
创建简单的存储过程(不带参数）
例 创建一个存储过程，从学生选课数据库中查询学号,姓名,课程名和成绩
use test1
go --分批次（go以前的代码运行）
create procedure proc_st
as
select student.sno,sname,cname,grade
from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno;
go --分批次（go以前的代码运行）
exec proc_st
创建带输入参数的存储过程
例：创建一个存储过程，完成向课程表（Course）中插入一条记录，记录值为（’c07’,’java语言’,2），记录值通过输入参数传递到存储过程中
IF EXISTS (SELECT * FROM SYSobjects WHERE name='proc_Insert_C' AND type='p') --如果在系统表SYSobjects中存在存储过程proc_Insert_C则删除它
DROP PROCEDURE proc_Insert_C
GO
CREATE PROCEDURE proc_Insert_C
(@Cno char(7),
@Cname char(10),
@Ccredit smallint)
as
insert into Course(Cno,Cname,Ccredit)
values(@Cno,@Cname,@Ccredit)
Go
exec proc_Insert_c @Cno='c01',@Cname='java语言',@Ccredit=2
创建带返回参数的存储过程
在SQL Server 中，从存储过程中返回数据主要有以下3种形式：
输出参数，即OUTPUT参数。OUTPUT参数用来表明参数是返回参数，该参数值可以返回给EXECUTE。
返回代码，即RETURN 语句，它始终是整型值。
SELECT语句的结果集。
例：创建一个存储过程，该存储过程接受外部传入的sno，在SC表中查找该sno的选课记录，然后输出该学生所选课程的总成绩。
USE test1;
go
IF EXISTS(SELECT * FROM SYSobjects WHERE name='proc_Search_sumgrade' AND type='p')
DROP PROCEDURE proc_Search_sumgrade
GO--以上为例行检查操作
CREATE PROCEDURE proc_Search_sumgrade
（@sid char(10),
@sumgrade int output）--输出变量
as
select @sumgrade=sum(sc.Grade)
from SC
where sc.Sno=@sid;
go
例：创建一个存储过程，输入系名，统计该系的学生人数
use test1 ;
go
create procedure proc_counts1
（@sdep char(2),
@studentcount int output）
as
select @studentcount=count(student.sno)
from Student
where student.sdept=@sdep;
go
创建加密存储过程
使用with encryption对用户隐藏存储过程文本
create procedure p_encrypt
with encryption
as
SQL语句
go
如果执行：
exec sp_helptext p_encrypt
触发器 (Trigger)
触发器是一种特殊的存储过程，其特殊性在于它并不需要由用户直接调用，当对表进行插入、删除、修改等操作时自动执行。
触发器可以用来实施复杂的完整性约束，以防止对数据的不正确修改。
触发器不允许带参数、也不允许被调用。
触发器不能返回任何结果。
触发器组成
(1)事件:指对数据库进行的插入、删除、修改等操作。触发器可以响应这些事件,在适合的条件及恰当的时间内执行指定的动作。
(2)条件:触发器测试给定的条件,若条件成立,则执行相应的动作,否则什么也不执行。
(3)动作:动作是一系列的操作,这些操作可以撤消触发器发生的事件,可以是与事件相关的操作,也可以是与事件无关的其他操作。
触发器的类型
AFTER触发器
又称后触发器（After Trigger）
这种类型的触发器将在数据变动（UPDATE、INSERT和DELETE操作）完成后才被触发。
指定 AFTER 与指定 FOR 相同。
AFTER触发器只能在表上定义，不能在视图上定义。
在同一个数据表中可以创建多个AFTER触发器。
Server默认的为AFTER触发器。
INSTEAD OF触发器
又称前触发器（Inserted Of Trigger）
INSTEAD OF触发器在数据变动以前被触发，并取代变动数据的操作（UPDATE、INSERT和DELETE操作），而去执行触发器定义的操作。
INSTEAD OF触发器可以在表或视图上定义。
在表或视图上，每个UPDATE、INSERT和DELETE语句最多可以定义一个INSTEAD OF触发器。
触发器中使用的特殊表
执行触发器时，系统创建了两个特殊的临时表：inserted和deleted
只读，不允许修改；触发器执行完成后，自动删除。
触发器分类
1、登陆触发器
作用在LOGIN事件上，是一种AFTER触发器，可以控制用户会话的创建过程及限制用户名和会话的次数
2、DML触发器
针对INSERT,UPDATE,DELETE操作的触发器。
3、DDL触发器
针对CREATE, ALTER, DROP，GRANT , DENY, REVOKE操作的触发器。
触发器基本语法
CREATE TRIGGER〈触发器名〉
ON 表图/视图名
With encryption ‘对SQL语句加密 ’
{FOR|AFTER| before/instead of }{delete/update/insert}
AS ……SQL语句
登陆触发器基本语法
CREATE TRIGGER〈触发器名〉
ON all server
{FOR|AFTER| logon}
AS 动作
例：创建一个登陆触发器，仅允许白名单主机名连接SQL SERVER服务器
create trigger myhostsonly
on all server
for logon
as
begin
if(
HOST_NAME () not in ('prodbox','qabox','devbox'))
begin
raiserror('you are not allowed to login from this hostname',16,1);
rollback;
end
end
DML触发器
定义触发器的基本语法:
CREATE TRIGGER〈触发器名〉
ON {〈表名〉 | 〈视图名〉}
{FOR|AFTER|INSTEAD OF}
[INSERT][,][UPDATE][,][DELETE]
AS
〈 SQL 语句〉
例：在学生表Ｓ上创建触发器，当向该表中插入记录时，触发器向客户端发出提示信息
use test1;
go
create trigger insert_student
on student
after insert
as
begin
create table stu_sum(number int);
declare @stunumber int;
select @stunumber =count(*) from student;
if not exists(select * from stu_sum )
insert into stu_sum values(0);
update stu_sum set number =@stunumber ;
end
go