SQL基础知识总结
代表本机：（ . , 计算机名 , Localhost , (Local) , 127.0.0.1）
- SQL Server Management Studio ⟶ \longrightarrow ⟶ 启动程序
- 5种状态（选择）1. 启动2. 停止3. 暂停4. 恢复5. 重启
- 3种验证方式1. win验证2. SQL验证3. 混合验证
- 5个表表名PRIMARY KEY（主键约束）主从表studentsno主表coursecno主表scsno,cno从表deptdeptno主表empempno从表其中sc表中，通过sno,cno主外键关联与student表，course表产生联系 ⟶ \longrightarrow ⟶三表之间主表不可以随便更改
一、服务（名字，状态）、登陆方式、服务器
- SQL Server(SQLEXPRESS) ⟶ \longrightarrow ⟶服务器名称(**.**\SQLEXPRESS)
- SQL Server(MSSQLSERVER) ⟶ \longrightarrow ⟶服务器名称(.)
二、SQL(结构化查询语言)
- DDL：数据定义 ⟶ \longrightarrow ⟶create,alter,drop ⟶ \longrightarrow ⟶结构上
- DML：数据操纵 ⟶ \longrightarrow ⟶insert,update,delete
- DQL：数据查询 ⟶ \longrightarrow ⟶select
- DCL：数据控制 ⟶ \longrightarrow ⟶grant(授权),revoke(回收授权),deny(拒绝)
三、增删改
- insert into ⟶ \longrightarrow ⟶表(列，列，列 ⋯ \cdots ⋯) ⟶ \longrightarrow ⟶values(值，值，值 ⋯ \cdots ⋯) ⟹ \Longrightarrow ⟹行操作- ※※用null来代替不知道的值- 或指定表的列
- update ⟶ \longrightarrow ⟶表set ⟶ \longrightarrow ⟶列=新值，列=新值，列=新值 ⋯ \cdots ⋯where ⟶ \longrightarrow ⟶行（行的过滤） ⟹ \Longrightarrow ⟹为空时判定用is
- delete from ⟶ \longrightarrow ⟶表where ⟶ \longrightarrow ⟶行（行的过滤）drop table ⟶ \longrightarrow ⟶表名
四、数据查询 select
select
⟶
\longrightarrow
⟶**列**（列，列的别名，列的表达式，列的函数）
from
⟶
\longrightarrow
⟶**数据集**（表，视图，子查询）
where
⟶
\longrightarrow
⟶**行的选择**（<,=,>,is,between
⋯
\cdots
⋯and
⋯
\cdots
⋯,in,or,like）
group by
⟶
\longrightarrow
⟶**分组**
having
⟶
\longrightarrow
⟶**组的选择和过滤**
order by
⟶
\longrightarrow
⟶**排序**，asc升序，desc降序
s
e
l
e
c
t
{
d
i
s
t
i
n
c
t
⟶
消
除
重
复
键
a
v
g
求
平
均
，
s
u
m
求
和
,
m
a
x
求
最
大
值
,
m
i
n
求
最
小
值
,
c
o
u
n
t
(
∗
)
查
有
几
行
⋯
⟶
聚
簇
函
数
聚
簇
函
数
会
从
第
一
条
数
据
查
到
最
后
一
行
才
输
出
结
果
a
s
′
′
⟶
起
别
名
select \begin{cases} distinct\longrightarrow消除重复键\\ avg求平均，sum求和,max求最大值,min求最小值,count(*)查有几行\cdots\longrightarrow聚簇函数\\\qquad聚簇函数会从第一条数据查到最后一行才输出结果\\ as\quad' \quad '\longrightarrow起别名 \end{cases}
select⎩⎪⎪⎪⎨⎪⎪⎪⎧distinct⟶消除重复键avg求平均，sum求和,max求最大值,min求最小值,count(∗)查有几行⋯⟶聚簇函数聚簇函数会从第一条数据查到最后一行才输出结果as′′⟶起别名
1.简单查询
- between ⋯ \cdots ⋯and ⋯ \cdots ⋯ ⟶ \longrightarrow ⟶连续的闭区间
- and ⟶ \longrightarrow ⟶与
- or ⟶ \longrightarrow ⟶或
- in/not in ⟶ \longrightarrow ⟶是否在集合内（可进行离散值的查询）
- where ⟶ \longrightarrow ⟶选择行
- is/is not null ⟶ \longrightarrow ⟶空不空使用
- like ‘% %’，ike ‘_ _ ‘ ⟶ \longrightarrow ⟶模糊查询, ‘**%’ 表示任意数量字符，’_**’ 表示仅有一个字符
2.分组查询
- Order by ⟶ \longrightarrow ⟶只能用在最后一行，且只能出现一次
- select中除聚簇函数外其他属性列必须放在group by 中-
select deptno,avg(sal)groupby deptno
3.非相关子查询 in
select sname from student where sno in(select sno from sc where cno='c1')
4.相关子查询 exist
5.连接查询
//自然连接where student.sno=sc.sno
外
连
接
(
信
息
是
全
的
)
{
l
e
f
t
j
o
i
n
⟶
从
左
表
返
回
所
有
的
行
，
即
使
右
表
中
没
有
匹
配
。
如
果
右
表
中
没
有
匹
配
，
则
结
果
为
N
U
L
L
。
r
i
g
h
t
j
o
i
n
⟶
从
右
表
返
回
所
有
的
行
，
即
使
左
表
中
没
有
匹
配
。
如
果
左
表
中
没
有
匹
配
，
则
结
果
为
N
U
L
L
。
f
u
l
l
j
o
i
n
⟶
在
其
中
一
个
表
中
存
在
匹
配
项
时
返
回
行
外连接(信息是全的) \begin{cases} left\ join\longrightarrow从左表返回所有的行，即使右表中没有匹配。如果右表中没有匹配，则结果为 NULL。\\ right\ join\longrightarrow从右表返回所有的行，即使左表中没有匹配。如果左表中没有匹配，则结果为 NULL。\\ full\ join\longrightarrow在其中一个表中存在匹配项时返回行 \end{cases}
外连接(信息是全的)⎩⎪⎨⎪⎧left join⟶从左表返回所有的行，即使右表中没有匹配。如果右表中没有匹配，则结果为NULL。right join⟶从右表返回所有的行，即使左表中没有匹配。如果左表中没有匹配，则结果为NULL。full join⟶在其中一个表中存在匹配项时返回行
- INNER JOIN -当两个表中都有匹配项时返回行。
- LEFT JOIN -返回左侧表中的所有行，即使右表中没有匹配项。
- RIGHT JOIN -返回右表中的所有行，即使左表中没有匹配项。
- FULL JOIN -在其中一个表中存在匹配项时返回行。
- SELF JOIN -这用于将表连接到自身，就像该表是两个表，临时重命名MS SQL Server语句中的至少一个表。
- CARTESIAN JOIN -返回两个或多个联接表中的记录集的笛卡尔乘积。
T-SQL基础（SQL高级编程语言）
一、数据类型、常量、变量
1.自定义数据类型：exec(调用执行 )
sp_
⟶
\longrightarrow
⟶不是真正的数据类型，不需要约束条件
e
x
e
c
{
s
p
_
a
d
d
t
y
p
e
′
s
_
s
n
o
′
,
′
c
h
a
r
(
8
)
′
,
′
n
o
t
n
u
l
l
′
⟶
定
义
自
定
义
类
型
s
p
_
d
r
o
p
t
y
p
e
′
s
_
s
n
o
′
⟶
删
除
自
定
义
类
型
exec \begin{cases} sp\_addtype\quad's\_sno','char(8)','not\quad null'\longrightarrow定义自定义类型\\sp\_droptype\quad's\_sno'\longrightarrow删除自定义类型\end{cases}
exec{sp_addtype′s_sno′,′char(8)′,′notnull′⟶定义自定义类型sp_droptype′s_sno′⟶删除自定义类型
2.常量
- 又称为字面值或标量值,程序运行过程中值不变
- ’O’'Bbaar’，如果单引号中的字符串包含引号，可以使用两个单引号表示嵌入的单引号。
$常量
\begin{cases}
字符串常量\数值型常量\日期型常量\longrightarrow单引号包起来，中间不能有汉字\begin{cases}‘April\ 20,2000’\rightarrow字母日期格式\‘4/15/1998’\rightarrow数字日期格式\‘20011207’\rightarrow未分隔的字符串格式\end{cases}\Money(货币)常量\rightarrow前缀为$\end{cases}$
3.变量
变量名不能与系统变量相同
※※※※TSQL中无string类型
⟶
\longrightarrow
⟶$
\begin{cases}char\varchar\nchar\cdots
\end{cases}$
变
量
{
@
a
⟶
(
可
)
自
定
义
，
局
部
变
量
@
@
⟶
系
统
定
义
（
不
可
操
控
）
，
全
局
变
量
,
作
为
函
数
引
用
变量 \begin{cases} @a\longrightarrow(可)自定义，局部变量\\@@\longrightarrow系统定义（不可操控），全局变量,作为函数引用\end{cases}
变量{@a⟶(可)自定义，局部变量@@⟶系统定义（不可操控），全局变量,作为函数引用
变量的声明赋值与使用
declare @i int
⟶
\longrightarrow
⟶声明变量
set @i=10
⟶
\longrightarrow
⟶变量赋值，同时给多个变量赋值时使用**select**
print @a
⟶
\longrightarrow
⟶变量打印输出，同时输出多个变量时使用**select**
- print ‘和为’+str(@c) ⟶ \longrightarrow ⟶str把变量转型成字符串
- ltrim ⟶ \longrightarrow ⟶去掉左边空格同时把变量转型成字符串
- rtrim ⟶ \longrightarrow ⟶去掉右边空格同时把变量转型成字符串
- @a+@b ⟶ \longrightarrow ⟶’+'连接作用 { @ a , @ b 都 为 数 值 型 ⟶ 加 法 @ a , @ b 都 为 字 符 串 ⟶ 连 接 @ a 为 数 值 型 , @ b 为 字 符 型 ⟶ l t r i m ( ) , s t r ( ) , r t r i m ( ) \begin{cases}@a,@b都为数值型\longrightarrow加法\@a,@b都为字符串\longrightarrow连接\@a为数值型,@b为字符型\longrightarrow ltrim(),str(),rtrim()\end{cases} ⎩⎪⎨⎪⎧@a,@b都为数值型⟶加法@a,@b都为字符串⟶连接@a为数值型,@b为字符型⟶ltrim(),str(),rtrim()
4.注释
注
释
{
−
−
⟶
单
行
/
∗
∗
/
⟶
多
行
注
释
注释 \begin{cases} --\quad\longrightarrow单行\\/*\quad*/\longrightarrow多行注释\end{cases}
注释{−−⟶单行/∗∗/⟶多行注释
二、流程控制
控制语句说明begin
⋯
\cdots
⋯end
⟶
\longrightarrow
⟶{
⋯
\cdots
⋯}语句块，允许嵌套if
⋯
\cdots
⋯else条件语句，表达式用不用括号都可以，语句结束后加不加**’;’**都可以case分支语句，**多**条件判定while循环语句
→
\rightarrow
→多用于游标continue用于重新开始下一次循环break用于退出本次循环return无条件返回
case※※
case语句上边通常为半句话
※※※※使用case语句给变量赋值
--查询05880101学生选课的平均成绩，并根据平均成绩的范围输出等级A、B、C、D、E五个字母use demo
go
declare@avgint,@flagvarchar(20)select@avg=avg(grade)from sc
where sno='05880101'select@flag=casewhen@avg>=90and@avg<=100then'A'when@avg>=80and@avg<90then'B'when@avg>=70and@avg<80then'C'when@avg>=60and@avg<70then'D'when@avg<60then'E'else'No such grade'endselect@flagas'等级'
三、游标
1.两种题型
2种题型
{
1.
结
果
遍
历
输
出
2.
指
向
某
一
行
，
操
纵
数
据
，
对
行
进
行
修
改
\begin{cases}1.结果遍历输出\\2.指向某一行，操纵数据，对行进行修改\end{cases}
{1.结果遍历输出2.指向某一行，操纵数据，对行进行修改
2.游标作用
使用游标可以在查询数据的同时对数据进行处理(删、改)
3.游标种类
游标种类
{
普
通
游
标
游
标
变
量
{
局
部
游
标
变
量
全
局
游
标
变
量
\begin{cases}普通游标\\游标变量\begin{cases}局部游标变量\\全局游标变量\end{cases}\end{cases}
⎩⎪⎨⎪⎧普通游标游标变量{局部游标变量全局游标变量
4.5个步骤（普通游标的使用）
步骤关键词说明1.声明declare绑定2.打开open游动3.读取fetch取值4.关闭close关闭5.删除deallocate删除游标
5.语句格式
- daclare 游标名 cursor
- for select * from student where dept=‘系名’
- open 游标名
- fetch (next|prior|frist|last|absosute|relative) from 游标名/into@t
- close 游标名
- deallocate 游标名
6.游标基础代码
declare c1 cursorforselect/*update/delete*/open c1
fetchnextfrom c1
while @@fetch_status=0beginfetchnextfrom c1
/*游标遍历结果集全部输出*//*delete/update where current of c1当前行的数据*/endclose c1
deallocate c1
7.全局游标变量(选择)
- @@cursor_rows：返回最后打开的游标中当前存在的满足条件的行数。返回0：表示游标未打开;返回**-1：表示游标为动态游标；返回值（n）：是游标中的总行数**。
- @@fetch_status：返回fetch语句执行后游标的状态。返回0：表示fetch语句执行成功；返回**-1：表示fetch语句执行失败；返回-2：表示被读取的记录不存在**。
8.声明游标
declare c1 cursor[local|global]/*游标作用域*/[ forward_only | scroll ]/*游标移动方向*/[dynamic |static | fast_forward ]/*游标类型*/[ read_only]/*访问属性*/forselect/*SELECT查询语句*/forupdateof 列名 /*可修改的列*/
（1）游标作用域
- scroll：说明所声明的游标可以前滚、后滚，可使用所有的提取选项（frist、last、prior、next）。
（2）游标移动方向
- forward_only：表示游标只能从第一行滚动到最后一行，即该游标只能支持fetch的next提取选项。
（3）游标类型
- dynamic：为动态游标。它能够反映对结果集中所做的更改(update、delete)，并且如果使用 where current of子句通过游标进行更新，则它们也立即在游标中反映出来。
- static：为静态游标。数据库中所做的任何影响结果集成员的更改（增加、修改或删除数据），都不会反映到游标中，新的数据值不会显示在静态游标中。
- fast_forward：为只进游标。只支持游标从头到尾顺序提取数据。在结果集行提取后对行所做的更改对游标是不可见的。
（4）访问属性
- read_only：说明游标为只读的，不能通过该游标更新数据库中的数据。
（5）目的
- for update****of 某一列：指出游标中可以更新的列。
（6）游标类型与移动方向之间的关系
fast_forward不能与scroll一起使用，且fast_forward与forward_only只能选用一个。- 若指定了移动方向为
forward_only，而没有用static或dynamic关键字指定游标类型，则默认所定义的游标为动态游标。 - 若移动方向
forward_only和scroll都没有指定，那么移动方向关键字的默认由以下条件决定： 1. 如果指定了游标移动类型为static或dynamic，则移动方向默认为scroll；2. 如果没有用static或dynamic关键字指定游标类型，则移动方向默认值为forward_only。
9.打开游标
global
说明打开的是全局游标，否则打开局部游标。游标变量名可以引用要进行提取操作的已打开的游标。
10.读取数据
打开游标后，可以使用
fetch
语句从中读取数据说明读取数据的位置
fetch [next,prior,first,last,absosute,relative]
⟶
\longrightarrow
⟶说明读取数据的**位置**
from (global) 游标名/@<游标变量名>
into @<游标变量名> (n)
- next 紧跟当前行返回结果行，并且当前行递增为返回行。 如果fetch next 为对游标的第一次提取操作，则返回结果集中的第一行。 next 为默认的游标提取选项。
- prior 返回紧邻当前行前面的结果行，并且当前行递减为返回行。 如果 fetch prior 为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。
- frist 返回游标中的第一行并将其作为当前行。
- last 返回游标中的最后一行并将其作为当前行。
- absosute
n为正数读取从游标头开始的后第n行，并且使其位置为当前行。
n为负数读取从游标尾开始的前第n行，并且使其位置为当前行。
- relative
n为正数读取从当前行之后的第n行，并且使其位置为当前行。
n为负数读取从当前行之前的第n行，并且使其位置为当前行。
11.关闭游标
游标使用完以后，要及时关闭。关闭游标使用
close
语句
close (global) 游标名/@<游标变量名>
12.删除游标
游标关闭后，其定义仍在，需要时可用
open
语句打开，再次使用。若确认游标不再需要，就要释放其定义占用的系统空间，即删除游标。删除游标使用
deallocate
语句
deallocate (global) 游标名/@<游标变量名>
四、函数
1.函数分类
函数分为系统函数和用户自定义函数
2.系统函数
系
统
函
数
{
聚
组
函
数
{
a
v
g
c
o
u
n
t
c
o
u
n
t
(
∗
)
m
a
x
m
i
n
s
u
m
数
学
函
数
{
a
b
s
→
绝
对
值
c
e
i
l
i
n
g
→
取
大
于
等
于
指
定
值
的
最
小
整
数
f
l
o
o
r
→
小
于
等
于
指
定
值
得
最
大
整
数
p
o
w
e
r
r
o
u
n
d
s
q
u
a
r
e
→
返
回
指
定
浮
点
值
的
平
方
s
q
r
t
字
符
串
函
数
{
c
h
a
r
⟶
获
取
A
S
C
I
I
码
对
应
的
字
符
C
h
a
r
s
t
r
l
t
r
i
m
/
t
r
i
m
l
e
f
t
/
r
i
g
h
t
⟶
截
取
左
/
右
边
字
符
串
c
h
a
r
i
n
d
e
x
→
在
指
定
的
字
符
串
中
搜
索
特
定
的
字
符
串
，
并
可
以
指
定
开
始
搜
索
的
位
置
，
返
回
第
一
次
找
到
目
标
字
符
串
的
字
符
数
l
e
n
l
o
w
e
r
/
u
p
p
e
r
数
据
类
型
转
换
函
数
日
期
时
间
函
数
{
g
e
t
d
a
t
e
y
e
a
r
/
m
o
n
t
h
/
d
a
y
d
a
t
e
a
d
d
⋯
系统函数\begin{cases}聚组函数\begin{cases}avg\\count\\count(*)\\max\\min\\sum\end{cases}\\数学函数\begin{cases}abs\rightarrow绝对值\\ceiling\rightarrow取大于等于指定值的最小整数\\floor\rightarrow小于等于指定值得最大整数\\power\\round\\square\rightarrow返回指定浮点值的平方\\sqrt\end{cases}\\字符串函数\begin{cases}char\longrightarrow获取ASCII码对应的字符Char\\str\\ltrim/trim\\left/right\longrightarrow截取左/右边字符串\\charindex\rightarrow在指定的字符串中搜索特定的字符串，并可以指定开始搜索的位置，返回第一次找到目标字符串的字符数\\len\\lower/upper\end{cases}\\数据类型转换函数\\日期时间函数\begin{cases}getdate\\year/month/day\\dateadd\end{cases}\\\cdots\end{cases}
系统函数⎩⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎨⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎧聚组函数⎩⎪⎪⎪⎪⎪⎪⎪⎪⎨⎪⎪⎪⎪⎪⎪⎪⎪⎧avgcountcount(∗)maxminsum数学函数⎩⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎨⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎧abs→绝对值ceiling→取大于等于指定值的最小整数floor→小于等于指定值得最大整数powerroundsquare→返回指定浮点值的平方sqrt字符串函数⎩⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎨⎪⎪⎪⎪⎪⎪⎪⎪⎪⎪⎧char⟶获取ASCII码对应的字符Charstrltrim/trimleft/right⟶截取左/右边字符串charindex→在指定的字符串中搜索特定的字符串，并可以指定开始搜索的位置，返回第一次找到目标字符串的字符数lenlower/upper数据类型转换函数日期时间函数⎩⎪⎨⎪⎧getdateyear/month/daydateadd⋯
3.用户定义函数
自
定
义
函
数
{
标
量
函
数
表
值
函
数
{
内
嵌
表
值
函
数
多
语
句
表
值
函
数
自定义函数\begin{cases}标量函数\\表值函数\begin{cases}内嵌表值函数\\多语句表值函数\end{cases}\end{cases}
自定义函数⎩⎪⎨⎪⎧标量函数表值函数{内嵌表值函数多语句表值函数
- 标量函数：只能返回单个确定类型的标量值。
- 内嵌表值函数：以表的形式返回一个返回值，即返回的是一个表，相当于一个参数化的视图。
- 多语句表值函数：可以看作标量型和内嵌表值型函数的结合体。返回值是一个表，但它和标量型函数一样，有一个begin ⋯ \cdots ⋯end语句括起来的函数体，该函数体包含插入语句，向返回表中插入数据。
1.标量函数的定义
语法格式：
- create function 架构名.函数名 ⟶ \longrightarrow ⟶函数名部分（demo数据库中架构名为dbo）
- （@参数名 as 数据类型 =default readonly) ⟶ \longrightarrow ⟶形参定义部分
- returns 返回值类型 ⟶ \longrightarrow ⟶返回参数的类型
- as
- begin函数体 ⟶ \longrightarrow ⟶函数体部分return 标量表达式 ⟶ \longrightarrow ⟶返回语句end
说明：参数和返回值的数据类型可以是任何有效的SQL标量数据类型，不能是用户自定义的数据类型、timestamp或
cursor游标。
/*使用标量函数average实现以下功能：
1.查询全体学生选修某门课程（课程号）的平均成绩。
2.调用上述函数，输出选修“c2”课程的平均成绩。*/createfunction average(@cnochar(8))returnsintasbegindeclare@avgintselect@avg=avg(grade)from sc
where cno=@cnoreturn@avgEnd/*函数调用*/declare@averintselect@aver=dbo.average('c2')print'c2号课程的平均分为：'+ltrim(@aver)print'c2号课程的平均分为：'+ltrim(dbo.average('c2'))
2.标量函数的调用
- 当调用用户定义的标量函数时，必须提供至少由两部分组成的名称（架构名.函数名）。
- 可以在select、set 和print语句中调用标量函数。
3.表值函数
表
值
函
数
{
内
嵌
(
内
联
)
表
值
函
数
多
语
句
表
值
函
数
表值函数\begin{cases}内嵌(内联)表值函数\\多语句表值函数\end{cases}
表值函数{内嵌(内联)表值函数多语句表值函数
内嵌函数可用于实现参数化视图。例如，视图如下：
createview view1
asselect sno,sname
from student where dept='计算机系'
3.1 内嵌表值函数的定义
- create function (架构名.) 函数名 ⟶ \longrightarrow ⟶函数名部分
- (@参数名 as 数据类型=（default)) readonly ⟶ \longrightarrow ⟶形参定义部分
- returns table ⟶ \longrightarrow ⟶返回值为表类型
- as
- return select语句 ⟶ \longrightarrow ⟶通过select语句返回内嵌表
- 说明：在内嵌表值函数中，通过单个select语句定义table返回值，因此内联函数没有相关联的返回变量。
createfunction getinfo(@deptvarchar(15))returnstableasreturn(select sno,sname
from student
where dept=@dept)select*from getinfo('计算机系')
3.2 内嵌表值函数的调用
- 内嵌表值函数只能通过select语句调用
- select * from 函数名（实参）内嵌表值函数调用时，可以仅使用函数名。
/*创建内嵌表值函数Course_Student完成以下功能：
（1）查询student表中选修了某门课程（课程号）的所有学生的信息。
（2）调用上述函数，输出选修“c4”课程的所有学生的信息。*/createfunction course_student1(@cnochar(8))returnstableasreturnselect*from student
where sno in(select sno from sc
where cno=@cno)select*from course_student1('c4')createfunction course_student(@cnochar(8))returnstableasreturnselect student.*from student,sc
where student.sno=sc.sno
and cno=@cnoselect*from course_student('c4')
3.3 多语句表值函数的定义
语法格式：
- create function (架构名.) 函数名 ⟶ \longrightarrow ⟶ 定义函数名部分
- ( @参数名 as 数据类型 (=default) ) (n)) ⟶ \longrightarrow ⟶形参定义部分
- returns @return_variable table ⟶ \longrightarrow ⟶返回值为表类型
- (列名 数据类型 列选项(n) ) ⟶ \longrightarrow ⟶定义表的内容
- as
- begin
- insert @return_variable函数体 ⟶ \longrightarrow ⟶ 定义函数体
- return
- end
3.4 多语句表值函数的调用。
多语句表值函数的调用与内嵌表值函数的调用方法相同。
select * from 函数名（实参）
/*在数据库中创建返回表的多语句表值函数，完成以下功能：
（1）显示某个学生的学号，姓名及其选修课程的名称，学分和成绩。
（2）通过以学号“05880103”作为实参，调用该函数输出*/createfunction score_table(@snochar(8))returns@scoretable(
s_sno char(8),
s_sname char(20),
c_cname char(20),
c_grade int,
c_credit int)asbegininsert@scoreselect student.sno,sname,cname,grade,credit
from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno
and student.sno=@snoreturnend
4.内嵌表值函数和多语句表值函数不同之处
- 内嵌表值函数没有函数主体，返回的表是单个SELECT语句的结果集；
- 而多语句表值函数在begin ⋯ \cdots ⋯end块中定义的函数主体包含T-SQL语句，这些语句可生成行并将行插入到表中，最后返回表。
5形式参数为空的函数定义和调用
/*使用多语句表值函数返回“计算机系”和“信息系”学生的学号、姓名和院系。*/createfunction dept_table()returns@depttable(
s_sno char(8),
s_sname char(20),
d_dept varchar(15))asbegininsert@deptselect sno,sname,dept
from student
where dept='计算机系'or dept='信息系'returnendselect*from dept_table()/*内嵌表值函数：*/createfunction dept_table1()returnstableasreturnselect sno,sname,dept
from student
where dept='计算机系'or dept='信息系'select*from dept_table1()
6.用户定义函数的删除
使用界面方式定义与修改用户自定义函数。
对于一个已创建的用户定义函数，可有2种方法删除：
- 通过对象资源管理器删除，此方法非常简单。
- 利用T-SQL语句
drop function删除，语法格式：
drop function (架构名.) 函数名(n))
五、存储过程和触发器
- 存储过程和触发器都是SQL Server的数据库对象。
- 存储过程由一组预先编译好的SQL语句组成。存储过程的存在独立于表，存放在服务器上，供客户端调用。
- 触发器是一种特殊类型的存储过程，他不是由用户直接调用的，而是当用户对数据进行某种操作（包括数据的insert、update或delete操作）时自动执行。因此触发器的使用和表的更新操作紧密结合。
- 使用触发器可以大大提高数据库应用程序的灵活性和健壮性，可以利用触发器来实现复杂的业务规则，更有效地实施数据完整性。
1.存储过程
- 可以接受输入参数、返回表格或标量结果和消息，调用“数据定义语言（DDL）”和“数据操作语言（DML）”语句，然后返回输出参数。（DDL：数据定义 ⟶ \longrightarrow ⟶create,alter,drop ⟶ \longrightarrow ⟶结构上，DML：数据操纵 ⟶ \longrightarrow ⟶insert,update,delete）
1.1存储过程的类型
{
(
1
)
系
统
存
储
过
程
→
系
统
存
储
过
程
是
由
S
Q
L
S
e
r
v
e
r
提
供
的
存
储
过
程
，
可
以
作
为
命
令
执
行
。
(
2
)
扩
展
存
储
过
程
→
扩
展
存
储
过
程
是
指
在
S
Q
L
S
e
r
v
e
r
环
境
之
外
，
使
用
编
程
语
言
（
例
如
C
#
语
言
）
创
建
的
外
部
例
程
形
成
的
动
态
链
接
库
（
D
L
L
）
。
(
3
)
用
户
存
储
过
程
→
是
用
户
根
据
需
要
，
在
自
己
的
普
通
数
据
库
中
创
建
的
存
储
过
程
，
用
户
的
存
储
过
程
可
以
使
用
T
−
S
Q
L
语
言
编
写
。
\begin{cases}(1)系统存储过程\rightarrow系统存储过程是由SQL\ Server提供的存储过程，可以作为命令执行。\\(2) 扩展存储过程\rightarrow扩展存储过程是指在SQL\ Server环境之外，使用编程语言（例如C\#语言）创建的外部例程形成的动态链接库（DLL）。\\(3)用户存储过程\rightarrow是用户根据需要，在自己的普通数据库中创建的存储过程，用户的存储过程可以使用T-SQL语言编写。\end{cases}
⎩⎪⎨⎪⎧(1)系统存储过程→系统存储过程是由SQL Server提供的存储过程，可以作为命令执行。(2)扩展存储过程→扩展存储过程是指在SQL Server环境之外，使用编程语言（例如C#语言）创建的外部例程形成的动态链接库（DLL）。(3)用户存储过程→是用户根据需要，在自己的普通数据库中创建的存储过程，用户的存储过程可以使用T−SQL语言编写。
1.2存储过程的创建
- 基本语法： 1. create proc/procedure 存储过程名2. @参数名 数据类型 ⟶ \longrightarrow ⟶定义参数的类型3. varying (=default) (out/out put) ⟶ \longrightarrow ⟶定义参数的属性4. (with encryption) ⟶ \longrightarrow ⟶对语句文本加密5. as{T-SQL语句执行的操作} ⟶ \longrightarrow ⟶执行的操作
- 说明 1. 不要以sp_为前缀创建任何存储过程。sp_前缀是SQL Server用来命名系统存储过程的，使用这样的名称可能会与以后的某些系统存储过程发生冲突。2. varying：指定作为输出参数支持的结果集。该参数由存储过程动态构造，其内容可能发生改变，仅适用于定义cursor输出参数。3. default: 指定存储过程输入参数的默认值，默认值必须是常量或NULL。如果定义了默认值，执行存储过程时可以不提供实参。4. 如果希望其他用户无法查看存储过程的定义，则可以使用WITH ENCRYPTION子句创建存储过程。这样，存储过程定义将以不可读的形式存储。5. out 或 output：指定参数为输出参数类型，用于从存储过程返回结果。
1.3存储过程的执行
- 通过
execute或exec命令可以执行一个已定义的存储过程，exec是execute的简写。 - 语法格式：1. exec2. @return_status = 存储过程名3. @参数名=值/变量名 output/default (n)4. 设计简单的存储过程，不带任何参数的存储过程。-
/*返回05880101号学生的成绩情况，该存储过程不使用任何参数。存储过程定义后，执行存储过程student_info：EXECUTE student_info 或者Exec student_info*/createprocedure student_infoasselect*from student where sno='05880101'/*执行存储过程student_info*/execute student_info5. 使用带输入参数的存储过程/*从学生选课数据库的三个表中查询某人指定课程的成绩和学分。（该存储过程接收与传递参数精确匹配的值）*/createproc student_info1 @snamechar(20),@cnamechar(20)asselect student.sno,sname,cname,grade,credit from student,course,sc where student.sno=sc.sno and course.cno=sc.cno and sname=@snameand cname=@cname/*执行存储过程student_info1*/exec student_info1'周一','java'6. 使用带输入和输出（OUTPUT）参数的存储过程。/*创建一个存储过程do_action，根据条件处理相应数据，处理后输出相应的信息。*/createproc do_action @flagbit,@strchar(8) outputasif@flag=0beginupdate student set sname='qqq'where sno='05880105'set@str='修改成功'endelseif@flag=1begindeletefrom student where sno='05880105'set@str='删除成功'end/* 执行存储过程do_action，并查看结果*/declare@s_strchar(8)exec do_action 0,@s_str output print@s_str7. 使用带有通配符参数的存储过程，实现模糊查询/*从三个表的连接中返回指定学生、指定课程的成绩。若没有传递输入参数的值，姓名默认为“周%”，课程默认为“java”(该存储过程在参数中使用了模式匹配，如果没有提供参数，则使用预设的默认值。 )*/createproc st_info @snamevarchar(20)='周%',@cnamechar(20)='java'asbeginselect grade from student,course,sc where student.sno=sc.sno and course.cno=sc.cno and sname like@snameand cname=@cnameendexec st_info '张%'8. 使用output游标参数的存储过程。output游标用于返回存储过程的局部游标。游标类型只能用于定义输出参数/*创建一个带有OUTPUT游标参数的存储过程，在student表中声明一个scroll类型的游标并打开。*/createproc p13(@stu_curcursorvarying output)asbegindeclare stu_cur1 cursor scroll forselect*from student set@stu_cur=stu_cur1 open@stu_curend/*声明一个局部游标变量，执行上述存储过程，并将执行结果中返回的游标赋值给该局部游标变量，然后通过该局部游标变量读取student中最后一行记录。 */declare@my_curcursorexec p13 @my_cur outputfetchlastfrom@my_curclose@my_curdeallocate@my_cur
1.4存储过程的创建与执行
- 使用界面方式定义与修改存储过程。
- 对于一个已创建的存储过程，可有2种方法删除： 1. 通过对象资源管理器删除，此方法非常简单，请读者自己练习。2. 利用T-SQL语句drop proc删除3. 语法格式：drop proc 存储过程名
2.触发器
- 触发器是一类特殊的存储过程，与表的关系密切，用于保护表中的数据。
- 触发器是一个被指定关联到一个表的数据对象，触发器是不需要调用的，当对一个表的特别事件出现时，如对表执行插入、更新或删除操作时，它就会被激活。
- 触发器代码也是由T-SQL语句组成的，因此用在存储过程中的语句也可以在触发器的定义中。
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LHIxF7T2-1647074669988)(image-20211212170609021.png)]
2.1触发器作用
- 触发器的主要作用是能实现由主键和外键所不能保证的、复杂的参照完整性和数据的一致性，除此之外，触发器还有其他许多不同的功能。
- 跟踪变化
- 触发器可以侦测数据库内的操作，从而禁止了数据库未经许可的更新和变化，使数据库的修改、更新操作更安全，数据库运行更稳定。
- 可以强化数据条件约束
- 触发器能够实现比
check语句更为复杂的约束，更适合在大型数据库管理系统中用来约束数据的完整性。（rollback回滚） - 级联和并行运行 - 触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表的触发器中包含有对另外一个表的数据操作，如删除、更新、插入，而该操作又导致该表上的触发器被触发。
- 由此可见，触发器可以实现高级形式的业务规则、复杂行为限制和定制记录等功能。
2.触发器的类型
触
发
器
{
D
M
L
触
发
器
D
D
L
触
发
器
触发器\begin{cases}DML触发器\\DDL触发器\end{cases}
触发器{DML触发器DDL触发器
- DML触发器。当数据库中发生数据操纵语言（DML）事件时将调用(激活)DML触发器。DML事件包括对表或视图的insert语句、update语句和delete语句，因而DML触发器可分为3种类型：insert、update和delete。
- DDL触发器。DDL触发器是SQL Server 2005新增的功能，也是由相应的事件触发，但DDL触发器触发的事件是数据定义语句（DDL）。这些语句主要是以create、alter、drop等关键字开头的语句。
2. 2 创建DML触发器(编程题只出DML)
语法格式：
- create trigger (架构名.)触发器名
- on 表名或视图名 ⟶ \longrightarrow ⟶指定操作对象
- (with encryption) ⟶ \longrightarrow ⟶说明是否采用加密方式
- after|instead of
- insert|update|delete
- as
- begin
- <T-SQL语句>
- end
说明：
- after：用于说明触发器在指定操作成功执行后触发，如
after Insert表示向表中插入数据时激活触发器。 - Instead of：指定用DML触发器中的T-SQL语句代替触发语句。
- Insert|Update|Delete：指定激活触发器的语句类型，必须至少指定一个选项。在触发器定义中允许使用上述选项的任意顺序组合。
- 如果触发器表存在约束，则在after触发器执行之前检查这些约束。如果违反了约束，则不执行after触发器。
2.2.1 对student表创建一个insert触发器
/*对student表创建一个insert触发器。*/createtrigger tg_insert
on student afterinsertasbeginprint'insert student...'endinsertinto student
values('05880209','刘同学','男',23,'计算机系')
2.2.2 对SC表创建一个delete触发器
/*对SC表创建一个DELETE触发器。*/createtrigger tg_delete
on sc afterdeleteasbeginprint'delete sc _'enddeletefrom sc
where grade<70
2.2.3 对course表创建一个update触发器
/*对course表创建一个update触发器*/createtrigger tg_delete
on sc afterdeleteasbeginprint'delete sc _'enddeletefrom sc
where grade<70
2.3 创建DML触发器时通常使用的两个特殊表
在执行触发器时，系统创建了两个特殊的临时表inserted表和deleted表，这两个表的内容如下
- Inserted表：当向表中插入数据时，INSERT触发器触发执行，新的记录插入到触发器表和inserted表中
- Deleted表：用于保存已从表中删除的记录，当触发一个DELETED触发器时，被删除的记录存放到deleted表中。
- 修改一条记录等于插入一条新记录，同时删除旧记录。当对定义了
update触发器的表记录修改时，表中原记录移到deleted表中，而修改过的新记录插入到inserted表 - 对行影响
注意：由于inserted表和deleted表都是临时表，它们在触发器执行时被创建，触发器执行完后就消失了，所以只可以在本触发器的语句中使用SELECT语句查询这两个表。
/*对student表创建一个UPDATE触发器，并输出某个学生修改前与修改后的年龄值。*/createtrigger tg_update
on student afterupdateasbegindeclare@namechar(8),@oldageint,@newageintselect@name=sname,@oldage=age from deleted
select@newage=age from inserted
print@nameprint@oldageprint@newageendupdate student
set age=20where sno='05880102'
2.4 Update(column)函数
Update()函数返回一个布尔值，指示是否对表的指定列进行了insert或update操作。其中insert操作可是看做是把原来的null值修改成了新值。
例：在student表中创建update和delete触发器，当修改或删除student表中的学号时，同时修改或删除sc表中的该学号。
/*在course表中创建update和delete触发器，当修改或删除course表中的课程号时，同时修改或删除sc表中的该课程号。*/createtrigger tg_course
on course afterupdate,deleteasbeginifupdate(cno)update sc
set cno=(select cno from inserted)where cno=(select cno from deleted)elsedeletefrom sc
where cno=(select cno from deleted)endDeletefrom course
Where cname='java‘
update course
set cno='c1new'
where cno='c1'
2.5 Instead of 触发器
After触发器是在触发语句执行后触发的。与after触发器不同的是，instead of触发器触发时只执行触发器内部的SQL语句，而不执行激活该触发器的SQL语句- 在一个表或视图上，每个insert、update或delete语句最多可以定义一个instead of触发器。
/*创建表table_2，属性只有一个a，整数类型，在表中创建instead of insert触发器，当向表中插入记录时显示相应消息。*/createtrigger table_insert
on table_2 instead ofinsertasbeginprint'instead of trigger is working'end
2.5.1 Instead of 触发器的主要作用
是用于不可更新的视图，支持更新。如果视图不可更新，则必须使用
instead of
触发器支持基表中数据的插入、更新和删除操作。
- 视图更新操作的限制
- 如果视图是从多个基本表使用连接操作导出的，则不允许更新。
- 如果定义视图的select语句包含group by、distinct、聚组函数（虚列），则不允许更新。
- 如果建立视图时带with read only选项，则不能对该视图进行任何插入、更新和删除操作。
/*创建一个基于多表的视图，通过instead of触发器来实现对视图的更新操作。*/createview stu_sc
asselect student.sno,dept,cno,grade
from student,sc
where student.sno=sc.sno
insertinto stu_sc
values('05880909','英语系','c2',79)
2.6 创建DDL触发器
语法格式：
- create trigger 触发器名
- on all server|database
- (with encryption)
- after event_group |event_type
- as
- begin
- T-SQL语句
- end
DDL触发要用于：防止对数据库架构进行某些修改；希望数据库中发生某些变化以利于相应数据库架构中的更改；记录数据库架构中的更改或事件。
DDL触发器只在响应由T-SQL语法所指定的DDL事件时才会触发。
all server |database：
all server
是指当前DDL触发器的作用于当前服务器。database指DDL触发器作用于当前数据库。
event_type： on关键字后面为
database
，此选项包括create_table、alter_table、drop_table、create_view等。
event_group： on关键字后面为
all server
，此选项包括create_database、alter_database、drop_database 等。
2.6.1 创建数据库作用域的DDL触发器
/*创建数据库作用域的DDL触发器，当删除一个表时，提示禁止该操作，然后回滚删除表的操作。*/createtrigger satety
ondatabaseafter drop_table
asbeginprint'不能删除表'rollbackend
2.6.2 创建服务器作用域的DDL触发器
/*创建服务器作用域的DDL触发器，当删除一个数据库时，提示禁止该操作并回滚删除数据库的操作。 */createtrigger satety_server
onall server after drop_database
asbeginprint'不能删除该数据库'rollbackend
2.6.3 删除触发器
- 使用界面方式可以定义、修改、查看和删除DML触发器，但是对于DDL触发器，则只能查看和删除。
- 触发器本身是存在表中的，因此，当表被删除时，表中的触发器也将被删除。删除触发器使用drop trigger语句。
- 语法格式： 1. drop trigger 架构名.触发器名 ⟶ \longrightarrow ⟶删除DML触发器2. drop trigger 触发器名 on all server |database ⟶ \longrightarrow ⟶ 删除DDL触发器
注意：
如果是删除DDL触发器，则要使用on关键字指定在数据库作域还是服务器作用域。
事件时才会触发。
all server |database：
all server
是指当前DDL触发器的作用于当前服务器。database指DDL触发器作用于当前数据库。
event_type： on关键字后面为
database
，此选项包括create_table、alter_table、drop_table、create_view等。
event_group： on关键字后面为
all server
，此选项包括create_database、alter_database、drop_database 等。
2.6.1 创建数据库作用域的DDL触发器
/*创建数据库作用域的DDL触发器，当删除一个表时，提示禁止该操作，然后回滚删除表的操作。*/createtrigger satety
ondatabaseafter drop_table
asbeginprint'不能删除表'rollbackend
2.6.2 创建服务器作用域的DDL触发器
/*创建服务器作用域的DDL触发器，当删除一个数据库时，提示禁止该操作并回滚删除数据库的操作。 */createtrigger satety_server
onall server after drop_database
asbeginprint'不能删除该数据库'rollbackend
2.6.3 删除触发器
- 使用界面方式可以定义、修改、查看和删除DML触发器，但是对于DDL触发器，则只能查看和删除。
- 触发器本身是存在表中的，因此，当表被删除时，表中的触发器也将被删除。删除触发器使用drop trigger语句。
- 语法格式： 1. drop trigger 架构名.触发器名 ⟶ \longrightarrow ⟶删除DML触发器2. drop trigger 触发器名 on all server |database ⟶ \longrightarrow ⟶ 删除DDL触发器
注意：
如果是删除DDL触发器，则要使用on关键字指定在数据库作域还是服务器作用域。
标签：
sqlserver
本文转载自: https://blog.csdn.net/weixin_44964841/article/details/123446215
版权归原作者 慕真真 所有， 如有侵权，请联系我们删除。
版权归原作者 慕真真 所有， 如有侵权，请联系我们删除。