第七讲(第五章视图)
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
视图定义存入数据字典，并不执行其中的
SELECT语句。
在对视图查询时，按视图的定义从基本表中
将数据查出。
5.1.2 定义视图（续）
1.定义单源表视图
[例] 建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= '信息系'；
5.2.2 创建和执行存储过程(续)
1.创建不带参数的存储过程: [例] 查询计算机系学生的考试成绩,列出学生的姓名、课程名 和成绩。 create procedure student_grade1 as select sname,cname,grade from student s inner join sc on s.sno=sc.sno inner join course c on c.cno=sc.cno where sdept='计算机系‘ execute student_grade1
CREATE VIEW BT_S(Sno，Sname，Sbirth) AS SELECT Sno，Sname，2007-Sage
FROM Student；
5.1.2 定义视图（续）
5.含分组统计信息的视图
[例] 将学生的学号及他的平均成绩定义为一个视图
CREATE VIEW S_G(Sno，Gavg)
5.2.2 创建和执行存储过程(续)
4.创建带有多个输入参数并有默认值的存储过程: [例] 查询某个学生某门课程的考试成绩,若没有指定课程，则 默认课程为“数据库基础”。
create procedure student_grade4 @sname char(10) ,@cname char(20)='数据库基础' as select sname,sdept,cname,grade from student s inner join sc on s.sno=sc.sno inner join course c on c.cno=sc.cno where sname=@sname and cname=@cname execute student_grade4 '吴宾' [,'数据库基础‘]
标量函数、内嵌表值函数、多语句表值函数
5.3.2 创建和调用标量函数
定义标量函数（返回单个数据值）:
语法： Create function [拥有者名.]函数名 ([{@参数名[as]标量数据类型[=default]}[,…n]]) Returns 返回值类型 [as] Begin 函数体 return 标量表达式 end
declare @res int exec avg_grade '数据库基础',@res output print @res
5.2.2 创建和执行存储过程(续)
8.创建带删除数据的存储过程: [例] 删除考试成绩不及格学生的修课记录。
create proc p_delete as delete from sc where grade<60
execute p_student execute p_student '信息系','女',19 execute p_student @age=18
5.2.2 创建和执行存储过程(续)
6.创建带输出参数的存储过程: [例] create proc sum @var1 int,@var2 int,@var3 int output as set @var3=@var1+@var2 declare @res int exec sum 10,20,@res output print @res
9.创建带修改数据的存储过程: [例]将指定的课程的学分增加2分
create proc p_update @cn char(20) as update course set ccredit=ccredit+2 where cname=@cn
5.3 用户自定义函数
5.3.1函数的概念
函数:由一个或多个SQL语句组成的子程序,可用于封装 代码以提高代码共享的功能; 函数分类:内置函数、用户自定义函数 SQL Server 2000支持的三种用户自定义函数：
允许进行模块化程序设计 改善性能 减少网络流量 提供安全机制 简化管理和操作
5.2.2 创建和执行存储过程
语句格式：
CREATE Proc[edure] 存储过程名
[{@参数名 数据类型}[=default][output]] As
Sql语句[…n]
语句格式：
[exec[ute]] 存储过程名[实参[,output][,…n]
5.2.2 创建和执行存储过程(续)
7.创建带输入参数和一个输出参数的存储过程:
[例] 统计指定课程的平均成绩,并将统计的结果用输出参数返回。 create proc avg_grade @cno char(20),@avg_grade int output as select @avg_grade=avg(grade) from sc join course c on c.cno=sc.cno where cname=@cno
5.2.2 创建和执行存储过程(续)
3.创建带有多个输入参数的存储过程:
[例] 查询某个学生某门课程的考试成绩,列出学生的姓名、课程名和成绩。
create procedure student_grade3 @sname char(10) ,@cname char(20) as select sname,cname,grade from student s inner join sc on s.sno=sc.sno inner join course c on c.cno=sc.cno where sname=@sname and cname=@cname execute student_grade3 '刘晨','VB' execute student_grade3 @sname='刘晨',@cname='VB'
数据库原理与应用
第五章 视图、存储过程 和用户自定义函数
5.1 视 图
5.1.1视图的概念
虚表，是从一个或几个基本表（或视图）导出的表 数据库中只存放视图的定义，不存放视图对应的数 据 基表中的数据发生变化，从视图中查询出的数据也 随之改变
基于视图的操作
查询、 删除、 受限更新、 基于视图定义新视图
5.3.2 创建和调用标量函数(续)
[例] 创建统计指定课程的选课人数的函数
create function dbo.f_count(@cname char(20)) returns int as begin declare @x int select @x=count(*) from course c join sc on sc.cno=c.cno where cname=@cname return @x end
必须显式地使用DROP VIEW语句删除
5.1.4 视图的作用
1. 简化数据查询语句
可以使用户将注意力集中在所关心的数据上
2. 使用户能从多角度看待同一数据 3. 提高了数据的安全性
可以定制用户能查看哪些数据并屏蔽掉敏感的数据
4. 提供了一定程度的逻辑独立性
5.2 存储过程
存储过程是SQL语句和控制流语句的预编译集
5.1.2 定义视图
语句格式
CREATE VIEW <视图名> [(<列名> [，<列名>]…)] AS <子查询>；
注意：
子查询通常不包含ORDER BY和DISTINCT子句
组成视图的属性列名：全部省略或全部指定
5.1.2 定义视图（续）
RDBMS执行CREATE VIEW语句时只是把
‘信息系'
5.1.2 定义视图（续）
2.定义多源表的视图
[例] 建立信息系选修了1号课程的学生视图。 CREATE VIEW IS_S1(Sno，Sname，Grade)
AS SELECT S.Sno，Sname，Sage
FROM Student S JOIN SC ON
S.Sno=SC.Sno Where Sdept=‘信息系’ And SC.Cno=‘C01’
AS
SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；
5.1.3 删除视图
语句的格式： DROP VIEW <视图名>；
该语句从数据字典中删除指定的视图定义 在删除视图时一定要注意是否有引用被删除对 象的视图，如果有则应同时删除
删除基表时，由该基表导出的所有视图定义都
5.1.2 定义视图（续）
3.基于视图的视图
[例] 建立信息系选修了1号课程且成绩在90分以上的 学生的视图。
CREATE VIEW IS_S2 AS
SELECT Sno，Sname，Grade
FROM IS_S1 WHERE Grade>=90；
5.1.2 定义视图（续）
4.带表达式的视图 [例] 定义一个反映学生出生年份的视图。
5.1.2 定来自百度文库视图（续）
CREATE VIEW IS_S1(Sno，Sname，Grade)
AS SELECT Student.Sno，Sname，Grade FROM Student，SC WHERE Sdept= '信息系' AND Student.Sno=SC.Sno AND SC.Cno= '1'；
合，它以一个名称存储并作为一个单元处理，
应用程序可以通过调用来执行存储过程。
存储过程可以使用户对数据库的管理和操作更
容易、效率更高。
5.2.1 存储过程的概念
SQL语言是应用程序和SQL Server数据库之 间的主要编程接口。 两种方式：
客户端存储代码 存储过程
存储过程的优点：
5.1.2 定义视图（续）
对IS_Student视图的更新操作：
修改操作：自动加上Sdept= ‘信息系'的条件 删除操作：自动加上Sdept= '信息系'的条件 插入操作：自动检查Sdept属性值是否为'信息系' 如果不是，则拒绝该插入操作
如果没有提供Sdept属性值，则自动定义Sdept为
5.2.2 创建和执行存储过程(续)
5.创建带有多个输入参数并均指定默认值的存储过程:
[例] 查询指定系、指定性别的学生中年龄大于等于指定年龄的学生的情况。系的默认值 为“计算机系”，性别的默认值为“男生”，年龄的默认值为20。
create proc p_student @dept char(20)='计算机系',@sex char(2)='男',@age int=20 as select * from student where sdept=@dept and ssex=@sex and sage>=@age
5.2.2 创建和执行存储过程(续)
2.创建带有输入参数的存储过程: [例] 查询某个指定系学生的考试成绩,列出学生的姓名、所在 系、课程名和成绩。 create procedure student_grade2 @sdept char(20) as select sname,sdept,cname,grade from student s inner join sc on s.sno=sc.sno inner join course c on c.cno=sc.cno where sdept=@sdept execute student_grade2 '信息系'
5.3.2 创建和调用标量函数(续)
[例] 创建求立方体体积的函数 create function dbo.cubeVolume
(@cubeLength int,@cubeWidth int,@cubeHeight int) returns int as begin return (@cubeLength*@cubeWidth*@cubeHeight) end select dbo.cubeVolume(4,6,8)
select dbo.f_count('数据库基础') select cname ,dbo.f_count('数据库基础') as 选课人数 from course where cname='数据库基础'
SELECT语句。
在对视图查询时，按视图的定义从基本表中
将数据查出。
5.1.2 定义视图（续）
1.定义单源表视图
[例] 建立信息系学生的视图。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= '信息系'；
5.2.2 创建和执行存储过程(续)
1.创建不带参数的存储过程: [例] 查询计算机系学生的考试成绩,列出学生的姓名、课程名 和成绩。 create procedure student_grade1 as select sname,cname,grade from student s inner join sc on s.sno=sc.sno inner join course c on c.cno=sc.cno where sdept='计算机系‘ execute student_grade1
CREATE VIEW BT_S(Sno，Sname，Sbirth) AS SELECT Sno，Sname，2007-Sage
FROM Student；
5.1.2 定义视图（续）
5.含分组统计信息的视图
[例] 将学生的学号及他的平均成绩定义为一个视图
CREATE VIEW S_G(Sno，Gavg)
5.2.2 创建和执行存储过程(续)
4.创建带有多个输入参数并有默认值的存储过程: [例] 查询某个学生某门课程的考试成绩,若没有指定课程，则 默认课程为“数据库基础”。
create procedure student_grade4 @sname char(10) ,@cname char(20)='数据库基础' as select sname,sdept,cname,grade from student s inner join sc on s.sno=sc.sno inner join course c on c.cno=sc.cno where sname=@sname and cname=@cname execute student_grade4 '吴宾' [,'数据库基础‘]
标量函数、内嵌表值函数、多语句表值函数
5.3.2 创建和调用标量函数
定义标量函数（返回单个数据值）:
语法： Create function [拥有者名.]函数名 ([{@参数名[as]标量数据类型[=default]}[,…n]]) Returns 返回值类型 [as] Begin 函数体 return 标量表达式 end
declare @res int exec avg_grade '数据库基础',@res output print @res
5.2.2 创建和执行存储过程(续)
8.创建带删除数据的存储过程: [例] 删除考试成绩不及格学生的修课记录。
create proc p_delete as delete from sc where grade<60
execute p_student execute p_student '信息系','女',19 execute p_student @age=18
5.2.2 创建和执行存储过程(续)
6.创建带输出参数的存储过程: [例] create proc sum @var1 int,@var2 int,@var3 int output as set @var3=@var1+@var2 declare @res int exec sum 10,20,@res output print @res
9.创建带修改数据的存储过程: [例]将指定的课程的学分增加2分
create proc p_update @cn char(20) as update course set ccredit=ccredit+2 where cname=@cn
5.3 用户自定义函数
5.3.1函数的概念
函数:由一个或多个SQL语句组成的子程序,可用于封装 代码以提高代码共享的功能; 函数分类:内置函数、用户自定义函数 SQL Server 2000支持的三种用户自定义函数：
允许进行模块化程序设计 改善性能 减少网络流量 提供安全机制 简化管理和操作
5.2.2 创建和执行存储过程
语句格式：
CREATE Proc[edure] 存储过程名
[{@参数名 数据类型}[=default][output]] As
Sql语句[…n]
语句格式：
[exec[ute]] 存储过程名[实参[,output][,…n]
5.2.2 创建和执行存储过程(续)
7.创建带输入参数和一个输出参数的存储过程:
[例] 统计指定课程的平均成绩,并将统计的结果用输出参数返回。 create proc avg_grade @cno char(20),@avg_grade int output as select @avg_grade=avg(grade) from sc join course c on c.cno=sc.cno where cname=@cno
5.2.2 创建和执行存储过程(续)
3.创建带有多个输入参数的存储过程:
[例] 查询某个学生某门课程的考试成绩,列出学生的姓名、课程名和成绩。
create procedure student_grade3 @sname char(10) ,@cname char(20) as select sname,cname,grade from student s inner join sc on s.sno=sc.sno inner join course c on c.cno=sc.cno where sname=@sname and cname=@cname execute student_grade3 '刘晨','VB' execute student_grade3 @sname='刘晨',@cname='VB'
数据库原理与应用
第五章 视图、存储过程 和用户自定义函数
5.1 视 图
5.1.1视图的概念
虚表，是从一个或几个基本表（或视图）导出的表 数据库中只存放视图的定义，不存放视图对应的数 据 基表中的数据发生变化，从视图中查询出的数据也 随之改变
基于视图的操作
查询、 删除、 受限更新、 基于视图定义新视图
5.3.2 创建和调用标量函数(续)
[例] 创建统计指定课程的选课人数的函数
create function dbo.f_count(@cname char(20)) returns int as begin declare @x int select @x=count(*) from course c join sc on sc.cno=c.cno where cname=@cname return @x end
必须显式地使用DROP VIEW语句删除
5.1.4 视图的作用
1. 简化数据查询语句
可以使用户将注意力集中在所关心的数据上
2. 使用户能从多角度看待同一数据 3. 提高了数据的安全性
可以定制用户能查看哪些数据并屏蔽掉敏感的数据
4. 提供了一定程度的逻辑独立性
5.2 存储过程
存储过程是SQL语句和控制流语句的预编译集
5.1.2 定义视图
语句格式
CREATE VIEW <视图名> [(<列名> [，<列名>]…)] AS <子查询>；
注意：
子查询通常不包含ORDER BY和DISTINCT子句
组成视图的属性列名：全部省略或全部指定
5.1.2 定义视图（续）
RDBMS执行CREATE VIEW语句时只是把
‘信息系'
5.1.2 定义视图（续）
2.定义多源表的视图
[例] 建立信息系选修了1号课程的学生视图。 CREATE VIEW IS_S1(Sno，Sname，Grade)
AS SELECT S.Sno，Sname，Sage
FROM Student S JOIN SC ON
S.Sno=SC.Sno Where Sdept=‘信息系’ And SC.Cno=‘C01’
AS
SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；
5.1.3 删除视图
语句的格式： DROP VIEW <视图名>；
该语句从数据字典中删除指定的视图定义 在删除视图时一定要注意是否有引用被删除对 象的视图，如果有则应同时删除
删除基表时，由该基表导出的所有视图定义都
5.1.2 定义视图（续）
3.基于视图的视图
[例] 建立信息系选修了1号课程且成绩在90分以上的 学生的视图。
CREATE VIEW IS_S2 AS
SELECT Sno，Sname，Grade
FROM IS_S1 WHERE Grade>=90；
5.1.2 定义视图（续）
4.带表达式的视图 [例] 定义一个反映学生出生年份的视图。
5.1.2 定来自百度文库视图（续）
CREATE VIEW IS_S1(Sno，Sname，Grade)
AS SELECT Student.Sno，Sname，Grade FROM Student，SC WHERE Sdept= '信息系' AND Student.Sno=SC.Sno AND SC.Cno= '1'；
合，它以一个名称存储并作为一个单元处理，
应用程序可以通过调用来执行存储过程。
存储过程可以使用户对数据库的管理和操作更
容易、效率更高。
5.2.1 存储过程的概念
SQL语言是应用程序和SQL Server数据库之 间的主要编程接口。 两种方式：
客户端存储代码 存储过程
存储过程的优点：
5.1.2 定义视图（续）
对IS_Student视图的更新操作：
修改操作：自动加上Sdept= ‘信息系'的条件 删除操作：自动加上Sdept= '信息系'的条件 插入操作：自动检查Sdept属性值是否为'信息系' 如果不是，则拒绝该插入操作
如果没有提供Sdept属性值，则自动定义Sdept为
5.2.2 创建和执行存储过程(续)
5.创建带有多个输入参数并均指定默认值的存储过程:
[例] 查询指定系、指定性别的学生中年龄大于等于指定年龄的学生的情况。系的默认值 为“计算机系”，性别的默认值为“男生”，年龄的默认值为20。
create proc p_student @dept char(20)='计算机系',@sex char(2)='男',@age int=20 as select * from student where sdept=@dept and ssex=@sex and sage>=@age
5.2.2 创建和执行存储过程(续)
2.创建带有输入参数的存储过程: [例] 查询某个指定系学生的考试成绩,列出学生的姓名、所在 系、课程名和成绩。 create procedure student_grade2 @sdept char(20) as select sname,sdept,cname,grade from student s inner join sc on s.sno=sc.sno inner join course c on c.cno=sc.cno where sdept=@sdept execute student_grade2 '信息系'
5.3.2 创建和调用标量函数(续)
[例] 创建求立方体体积的函数 create function dbo.cubeVolume
(@cubeLength int,@cubeWidth int,@cubeHeight int) returns int as begin return (@cubeLength*@cubeWidth*@cubeHeight) end select dbo.cubeVolume(4,6,8)
select dbo.f_count('数据库基础') select cname ,dbo.f_count('数据库基础') as 选课人数 from course where cname='数据库基础'