赞
踩
代表本机:( . , 计算机名 , Localhost , (Local) , 127.0.0.1)
SQL Server Management Studio ⟶ \longrightarrow ⟶ 启动程序
5种状态(选择)
3种验证方式
5个表
表名 | PRIMARY KEY(主键约束) | 主从表 |
---|---|---|
student | sno | 主表 |
course | cno | 主表 |
sc | sno,cno | 从表 |
dept | deptno | 主表 |
emp | empno | 从表 |
其中sc表中,通过sno,cno主外键关联与student表,course表产生联系 ⟶ \longrightarrow ⟶三表之间
主表不可以随便更改
insert into ⟶ \longrightarrow ⟶表(列,列,列 ⋯ \cdots ⋯) ⟶ \longrightarrow ⟶values(值,值,值 ⋯ \cdots ⋯) ⟹ \Longrightarrow ⟹行操作
update ⟶ \longrightarrow ⟶表
set ⟶ \longrightarrow ⟶列=新值,列=新值,列=新值 ⋯ \cdots ⋯
where ⟶ \longrightarrow ⟶行(行的过滤) ⟹ \Longrightarrow ⟹为空时判定用is
delete from ⟶ \longrightarrow ⟶ 表
where ⟶ \longrightarrow ⟶行(行的过滤)
drop table ⟶ \longrightarrow ⟶表名
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
Order by ⟶ \longrightarrow ⟶只能用在最后一行,且只能出现一次
select中除聚簇函数外其他属性列必须放在group by 中
select deptno,avg(sal) group by deptno
select sname from student where sno in(select sno from sc where cno='c1')
//自然连接
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
⟶
在
其
中
一
个
表
中
存
在
匹
配
项
时
返
回
行
外连接(信息是全的)
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
又称为字面值或标量值,程序运行过程中值不变
’O’'Bbaar’,如果单引号中的字符串包含引号,可以使用两个单引号表示嵌入的单引号。
常
量
{
字
符
串
常
量
数
值
型
常
量
日
期
型
常
量
⟶
单
引
号
包
起
来
,
中
间
不
能
有
汉
字
{
′
A
p
r
i
l
20
,
200
0
′
→
字
母
日
期
格
式
′
4
/
15
/
199
8
′
→
数
字
日
期
格
式
′
2001120
7
′
→
未
分
隔
的
字
符
串
格
式
M
o
n
e
y
(
货
币
)
常
量
→
前
缀
为
d
o
l
l
a
r
符
常量
变量名不能与系统变量相同
※
※
※
※
T
S
Q
L
中
无
s
t
r
i
n
g
类
型
⟶
{
c
h
a
r
v
a
r
c
h
a
r
n
c
h
a
r
⋯
※※※※TSQL中无string类型\longrightarrow
变
量
{
@
a
⟶
(
可
)
自
定
义
,
局
部
变
量
@
@
⟶
系
统
定
义
(
不
可
操
控
)
,
全
局
变
量
,
作
为
函
数
引
用
变量
declare @i int ⟶ \longrightarrow ⟶声明变量
set @i=10 ⟶ \longrightarrow ⟶变量赋值,同时给多个变量赋值时使用select
print @a ⟶ \longrightarrow ⟶变量打印输出,同时输出多个变量时使用select
注
释
{
−
−
⟶
单
行
/
∗
∗
/
⟶
多
行
注
释
注释
控制语句 | 说明 |
---|---|
begin ⋯ \cdots ⋯end ⟶ \longrightarrow ⟶{ ⋯ \cdots ⋯} | 语句块,允许嵌套 |
if ⋯ \cdots ⋯else | 条件语句,表达式用不用括号都可以,语句结束后加不加**’;’**都可以 |
case | 分支语句,多条件判定 |
while | 循环语句 → \rightarrow →多用于游标 |
continue | 用于重新开始下一次循环 |
break | 用于退出本次循环 |
return | 无条件返回 |
case语句上边通常为半句话
※※※※使用case语句给变量赋值
--查询05880101学生选课的平均成绩,并根据平均成绩的范围输出等级A、B、C、D、E五个字母 use demo go declare @avg int,@flag varchar(20) select @avg=avg(grade) from sc where sno='05880101' select @flag= case when @avg>=90 and @avg<=100 then 'A' when @avg>=80 and @avg<90 then 'B' when @avg>=70 and @avg<80 then 'C' when @avg>=60 and @avg<70 then 'D' when @avg<60 then 'E' else 'No such grade' end select @flag as '等级'
2种题型
{
1.
结
果
遍
历
输
出
2.
指
向
某
一
行
,
操
纵
数
据
,
对
行
进
行
修
改
使用游标可以在查询数据的同时对数据进行处理(删、改)
游标种类
{
普
通
游
标
游
标
变
量
{
局
部
游
标
变
量
全
局
游
标
变
量
步骤 | 关键词 | 说明 |
---|---|---|
1.声明 | declare | 绑定 |
2.打开 | open | 游动 |
3.读取 | fetch | 取值 |
4.关闭 | close | 关闭 |
5.删除 | deallocate | 删除游标 |
declare c1 cursor for select/*update/delete*/
open c1
fetch next from c1
while @@fetch_status=0
begin
fetch next from c1
/*游标遍历结果集全部输出*/
/*delete/update where current of c1当前行的数据*/
end
close c1
deallocate c1
@@cursor_rows:返回最后打开的游标中当前存在的满足条件的行数。
返回0:表示游标未打开;
返回**-1**:表示游标为动态游标;
返回值(n):是游标中的总行数。
@@fetch_status:返回fetch语句执行后游标的状态。
返回0:表示fetch语句执行成功;
返回**-1**:表示fetch语句执行失败;
返回**-2**:表示被读取的记录不存在。
declare c1 cursor
[ local | global ] /*游标作用域*/
[ forward_only | scroll ] /*游标移动方向*/
[dynamic |static | fast_forward ] /*游标类型*/
[ read_only] /*访问属性*/
for select /*SELECT查询语句*/
for update of 列名 /*可修改的列*/
fast_forward
不能与scroll
一起使用,且fast_forward
与forward_only
只能选用一个。forward_only
,而没有用static
或dynamic
关键字指定游标类型,则默认所定义的游标为动态游标。forward_only
和scroll
都没有指定,那么移动方向关键字的默认由以下条件决定:
static
或dynamic
,则移动方向默认为scroll
;static
或dynamic
关键字指定游标类型,则移动方向默认值为forward_only
。global
说明打开的是全局游标,否则打开局部游标。游标变量名可以引用要进行提取操作的已打开的游标。
打开游标后,可以使用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行,并且使其位置为当前行。
n为正数读取从当前行之后的第n行,并且使其位置为当前行。
n为负数读取从当前行之前的第n行,并且使其位置为当前行。
游标使用完以后,要及时关闭。关闭游标使用close
语句
close (global) 游标名/@<游标变量名>
游标关闭后,其定义仍在,需要时可用open
语句打开,再次使用。若确认游标不再需要,就要释放其定义占用的系统空间,即删除游标。删除游标使用deallocate
语句
deallocate (global) 游标名/@<游标变量名>
函数分为系统函数和用户自定义函数
系
统
函
数
{
聚
组
函
数
{
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
⋯
系统函数
自
定
义
函
数
{
标
量
函
数
表
值
函
数
{
内
嵌
表
值
函
数
多
语
句
表
值
函
数
自定义函数
语法格式:
create function 架构名.函数名 ⟶ \longrightarrow ⟶函数名部分(demo数据库中架构名为dbo)
(@参数名 as 数据类型 =default readonly) ⟶ \longrightarrow ⟶形参定义部分
returns 返回值类型 ⟶ \longrightarrow ⟶返回参数的类型
as
begin
函数体 ⟶ \longrightarrow ⟶函数体部分
return 标量表达式 ⟶ \longrightarrow ⟶返回语句
end
说明:参数和返回值的数据类型可以是任何有效的SQL标量数据类型,不能是用户自定义的数据类型、timestamp或
cursor游标。
/*使用标量函数average实现以下功能: 1.查询全体学生选修某门课程(课程号)的平均成绩。 2.调用上述函数,输出选修“c2”课程的平均成绩。*/ create function average(@cno char(8)) returns int as begin declare @avg int select @avg=avg(grade) from sc where cno=@cno return @avg End /*函数调用*/ declare @aver int select @aver=dbo.average('c2') print 'c2号课程的平均分为:'+ltrim(@aver) print 'c2号课程的平均分为:'+ltrim(dbo.average('c2'))
表
值
函
数
{
内
嵌
(
内
联
)
表
值
函
数
多
语
句
表
值
函
数
表值函数
内嵌函数可用于实现参数化视图。例如,视图如下:
create view view1
as
select sno,sname
from student where dept='计算机系'
create function getinfo(@dept varchar(15))
returns table
as
return
(
select sno,sname
from student
where dept=@dept
)
select * from getinfo('计算机系')
/*创建内嵌表值函数Course_Student完成以下功能: (1)查询student表中选修了某门课程(课程号)的所有学生的信息。 (2)调用上述函数,输出选修“c4”课程的所有学生的信息。*/ create function course_student1(@cno char(8)) returns table as return select * from student where sno in(select sno from sc where cno=@cno) select * from course_student1('c4') create function course_student(@cno char(8)) returns table as return select student.* from student,sc where student.sno=sc.sno and cno=@cno select * from course_student('c4')
语法格式:
create function (架构名.) 函数名 ⟶ \longrightarrow ⟶ 定义函数名部分
( @参数名 as 数据类型 (=default) ) (n)) ⟶ \longrightarrow ⟶形参定义部分
returns @return_variable table ⟶ \longrightarrow ⟶返回值为表类型
(列名 数据类型 列选项(n) ) ⟶ \longrightarrow ⟶定义表的内容
as
begin
insert @return_variable
函数体 ⟶ \longrightarrow ⟶ 定义函数体
return
end
多语句表值函数的调用与内嵌表值函数的调用方法相同。
select * from 函数名(实参)
/*在数据库中创建返回表的多语句表值函数,完成以下功能: (1)显示某个学生的学号,姓名及其选修课程的名称,学分和成绩。 (2)通过以学号“05880103”作为实参,调用该函数输出*/ create function score_table(@sno char(8)) returns @score table ( s_sno char(8), s_sname char(20), c_cname char(20), c_grade int, c_credit int) as begin insert @score select student.sno,sname,cname,grade,credit from student,course,sc where student.sno=sc.sno and course.cno=sc.cno and student.sno=@sno return end
/*使用多语句表值函数返回“计算机系”和“信息系”学生的学号、姓名和院系。*/ create function dept_table() returns @dept table ( s_sno char(8), s_sname char(20), d_dept varchar(15) ) as begin insert @dept select sno,sname,dept from student where dept='计算机系' or dept='信息系' return end select * from dept_table() /*内嵌表值函数:*/ create function dept_table1() returns table as return select sno,sname,dept from student where dept='计算机系' or dept='信息系' select * from dept_table1()
使用界面方式定义与修改用户自定义函数。
对于一个已创建的用户定义函数,可有2种方法删除:
通过对象资源管理器删除,此方法非常简单。
利用T-SQL语句drop function
删除,语法格式:
drop function (架构名.) 函数名(n))
{
(
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
语
言
编
写
。
通过execute
或exec
命令可以执行一个已定义的存储过程,exec是execute的简写。
语法格式:
exec
@return_status = 存储过程名
@参数名=值/变量名 output/default (n)
设计简单的存储过程,不带任何参数的存储过程。
/*返回05880101号学生的成绩情况,该存储过程不使用任何参数。
存储过程定义后,执行存储过程student_info:
EXECUTE student_info 或者Exec student_info
*/
create procedure student_info
as
select *
from student
where sno='05880101'
/*执行存储过程student_info*/
execute student_info
使用带输入参数的存储过程
/*从学生选课数据库的三个表中查询某人指定课程的成绩和学分。(该存储过程接收与传递参数精确匹配的值)*/
create proc student_info1 @sname char(20),@cname char(20)
as
select student.sno,sname,cname,grade,credit
from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno
and sname=@sname and cname=@cname
/*执行存储过程student_info1*/
exec student_info1'周一','java'
使用带输入和输出(OUTPUT)参数的存储过程。
/*创建一个存储过程do_action,根据条件处理相应数据,处理后输出相应的信息。*/ create proc do_action @flag bit,@str char(8) output as if @flag=0 begin update student set sname='qqq' where sno='05880105' set @str='修改成功' end else if @flag=1 begin delete from student where sno='05880105' set @str='删除成功' end /* 执行存储过程do_action,并查看结果*/ declare @s_str char(8) exec do_action 0,@s_str output print @s_str
使用带有通配符参数的存储过程,实现模糊查询
/*从三个表的连接中返回指定学生、指定课程的成绩。若没有传递输入参数的值,姓名默认为“周%”,课程默认为“java”(该存储过程在参数中使用了模式匹配,如果没有提供参数,则使用预设的默认值。 )*/
create proc st_info @sname varchar(20)='周%',@cname char(20)='java'
as
begin
select grade
from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno
and sname like @sname and cname=@cname
end
exec st_info '张%'
使用output
游标参数的存储过程。output
游标用于返回存储过程的局部游标。游标类型只能用于定义输出参数
/*创建一个带有OUTPUT游标参数的存储过程,在student表中声明一个scroll类型的游标并打开。*/create proc p13(@stu_cur cursor varying output) as begin declare stu_cur1 cursor scroll for select * from student set @stu_cur=stu_cur1 open @stu_cur end /*声明一个局部游标变量,执行上述存储过程,并将执行结果中返回的游标赋值给该局部游标变量,然后通过该局部游标变量读取student中最后一行记录。 */ declare @my_cur cursor exec p13 @my_cur output fetch last from @my_cur close @my_cur deallocate @my_cur
check
语句更为复杂的约束,更适合在大型数据库管理系统中用来约束数据的完整性。(rollback回滚)2.触发器的类型
触
发
器
{
D
M
L
触
发
器
D
D
L
触
发
器
触发器
语法格式:
说明:
after Insert
表示向表中插入数据时激活触发器。/*对student表创建一个insert触发器。*/
create trigger tg_insert
on student after insert
as
begin
print 'insert student...'
end
insert into student
values('05880209','刘同学','男',23,'计算机系')
/*对SC表创建一个DELETE触发器。*/
create trigger tg_delete
on sc after delete
as
begin
print 'delete sc _'
end
delete from sc
where grade<70
/*对course表创建一个update触发器*/
create trigger tg_delete
on sc after delete
as
begin
print 'delete sc _'
end
delete from sc
where grade<70
在执行触发器时,系统创建了两个特殊的临时表inserted表和deleted表,这两个表的内容如下
update
触发器的表记录修改时,表中原记录移到deleted表中,而修改过的新记录插入到inserted表注意:由于inserted表和deleted表都是临时表,它们在触发器执行时被创建,触发器执行完后就消失了,所以只可以在本触发器的语句中使用SELECT语句查询这两个表。
/*对student表创建一个UPDATE触发器,并输出某个学生修改前与修改后的年龄值。*/ create trigger tg_update on student after update as begin declare @name char(8),@oldage int,@newage int select @name=sname,@oldage=age from deleted select @newage=age from inserted print @name print @oldage print @newage end update student set age=20 where sno='05880102'
Update()函数返回一个布尔值,指示是否对表的指定列进行了insert或update操作。其中insert操作可是看做是把原来的null值修改成了新值。
例:在student表中创建update和delete触发器,当修改或删除student表中的学号时,同时修改或删除sc表中的该学号。
/*在course表中创建update和delete触发器,当修改或删除course表中的课程号时,同时修改或删除sc表中的该课程号。*/ create trigger tg_course on course after update,delete as begin if update(cno) update sc set cno=(select cno from inserted) where cno=(select cno from deleted) else delete from sc where cno=(select cno from deleted) end Delete from course Where cname='java‘ update course set cno='c1new' where cno='c1'
After
触发器是在触发语句执行后触发的。与after
触发器不同的是,instead of
触发器触发时只执行触发器内部的SQL语句,而不执行激活该触发器的SQL语句
在一个表或视图上,每个insert、update或delete语句最多可以定义一个instead of触发器。
/*创建表table_2,属性只有一个a,整数类型,在表中创建instead of insert触发器,当向表中插入记录时显示相应消息。*/
create trigger table_insert
on table_2 instead of insert
as
begin
print 'instead of trigger is working'
end
是用于不可更新的视图,支持更新。如果视图不可更新,则必须使用instead of
触发器支持基表中数据的插入、更新和删除操作。
如果视图是从多个基本表使用连接操作导出的,则不允许更新。
如果定义视图的select语句包含group by、distinct、聚组函数(虚列),则不允许更新。
如果建立视图时带with read only选项,则不能对该视图进行任何插入、更新和删除操作。
/*创建一个基于多表的视图,通过instead of触发器来实现对视图的更新操作。*/
create view stu_sc
as
select student.sno,dept,cno,grade
from student,sc
where student.sno=sc.sno
insert into stu_sc
values('05880909','英语系','c2',79)
语法格式:
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 等。
/*创建数据库作用域的DDL触发器,当删除一个表时,提示禁止该操作,然后回滚删除表的操作。*/
create trigger satety
on database after drop_table
as
begin
print '不能删除表'
rollback
end
/*创建服务器作用域的DDL触发器,当删除一个数据库时,提示禁止该操作并回滚删除数据库的操作。 */
create trigger satety_server
on all server after drop_database
as
begin
print '不能删除该数据库'
rollback
end
注意:
如果是删除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 等。
/*创建数据库作用域的DDL触发器,当删除一个表时,提示禁止该操作,然后回滚删除表的操作。*/
create trigger satety
on database after drop_table
as
begin
print '不能删除表'
rollback
end
/*创建服务器作用域的DDL触发器,当删除一个数据库时,提示禁止该操作并回滚删除数据库的操作。 */
create trigger satety_server
on all server after drop_database
as
begin
print '不能删除该数据库'
rollback
end
注意:
如果是删除DDL触发器,则要使用on关键字指定在数据库作域还是服务器作用域。
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。