视图的创建与使用
为什么需要视图？不同人员关注不同的数据，保证信息的安全性
视图
- 是存储在服务器端的一个查询块，是一张虚拟表。
- 表示一张表的部分数据或多张表的综合数据。
- 其结构和数据是建立在对表的查询基础上。
- 视图的使用，跟对普通的表的查询使用完全一样。
- 视图中不存放数据（数据存放在视图所引用的原始表中）
视图的用途
- 筛选表中的行。
- 防止未经许可的用户访问敏感数据。
- 降低数据库的复杂程度。
- 将多个物理数据库抽象为一个逻辑数据库。
如何创建视图？
--使用T-SQL语句创建视图
create view view_StuInfo
as
<select 语句>
--使用T-SQL语句删除视图
if exists(select * from sysobjects where name=view_StuInfo)
drop view view_StuInfo
--使用T-SQL语句查看视图
select * from view_StuInfo
系统与扩展存储过程
什么是存储过程
- 预先存储好的SQL程序
- 保存在SQL Server中（跟视图的存储方式一样）
- 通过名称和参数执行
- 可带参数，也可返回结果
- 可包含数据操纵语句、变量、逻辑控制语句等
存储过程的优点
- 执行速度更快
- 允许模块化程序设计
- 提高系统安全性
- 减少网络流通量
- 视图和存储过程的重要优点：安全且执行速度快
应用程序发送SQL的过程：传输语句→语法检查→语句优化→语句编译→语句执行
应用程序调用存储过程或视图的过程：传输参数→语句执行
存储过程的分类
-
系统存储过程：以sp_开头，由SQLServer创建、管理和使用，存放在Master数据库中
扩展存储过程：xp_开头，使用编程语言创建的外部存储过程，以DLL形式单独存在
用户自定义存储过程
自定义无参数存储过程
--创建、执行无参的存储过程
--创建存储过程usp_ScoreQuery，查询考试成绩，显示：学号、姓名、班级、总成绩，并按成绩的总分高低排序；统计分析考试成绩，显示班级名称、C#平均分、数据库平均分，按班级分组实现。
use StudentManageDB
go
if exists(select * from sysobjects where name='usp_ScoreQuery')
drop procedure usp_ScoreQuery
go
create procedure usp_ScoreQuery
as
--查询考试成绩
select Students.StudentId,StudentName,ClassName,ScoreSum=(CSharp+SQLServerDB)
from Students
inner join StudentClass on StudentClass.ClassId=Students.ClassId
inner join ScoreList on Students.StudentId=ScoreList.StudentId
order by ScoreSum desc
--分析考试信息
--select StudentClass.ClassName,C#Avg=AVG(CSharp),DBAvg=AVG(SQLServerDB)
--from ScoreList
--inner join Students on Students.StudentId=ScoreList.StudentId
--inner join StudentClass on StudentClass.ClassId=Students.ClassId
--group by ClassName
--order by ClassName
select StudentClass.ClassId,C#Avg=AVG(CSharp),DBAvg=AVG(SQLServerDB)
into #scoreTemp --将查询结果放入临时表
from ScoreList
inner join Students on Students.StudentId=ScoreList.StudentId
inner join StudentClass on StudentClass.ClassId=Students.ClassId
group by StudentClass.ClassId
order by StudentClass.ClassId
--将临时表和班级表关联查询
select ClassName,C#Avg,DBAvg
from #scoreTemp
inner join StudentClass on StudentClass.ClassId=#scoreTemp.ClassId
go
exec usp_ScoreQuery
自定义带输入参数的存储过程
--查询考试成绩，要求能够按照自定义的及格线查询结果
use StudentManageDB
go
if exists(select * from sysobjects where name='usp_ScoreQuery2')
drop procedure usp_ScoreQuery2
go
--创建带参数的存储过程
create procedure usp_ScoreQuery2
@CSharp int=60,
@DB int=60
as
select Students.StudentId,StudentName,C#=CSharp,DB=SQLServerDB
from Students
inner join ScoreList on Students.StudentId=ScoreList.StudentId
where CSharp<@CSharp or SQLServerDB<@DB
go
--调用带参数的存储过程
exec usp_ScoreQuery2 60,65 --按照参数顺序赋值
exec usp_ScoreQuery2 @DB=65,@CSharp=60 --参数顺序可以调换
exec usp_ScoreQuery2
自定义带输出参数的存储过程
-查询考试成绩，要求自定义分数线，显示查询列表，并输出缺考总人数、不及格总人数
use StudentManageDB
go
if exists(select * from sysobjects where name='usp_ScoreQuery3')
drop procedure usp_ScoreQuery3
go
--创建带输出参数的存储过程
create procedure usp_ScoreQuery3
@AsentCount int output,--缺考的总人数
@FailedCount int output,--不及格总人数
@CSharp int=60,
@DB int=60
as
--查询统计结果
select @AsentCount=COUNT(*) from Students
where StudentId not in(select StudentId from ScoreList)--查询缺考的总人数
select @FailedCount=COUNT(*) from ScoreList
where CSharp<@CSharp or SQLServerDB<@DB --查询不及格的总人数
go
--调用带输出参数的存储过程
declare @ASentCount int,@FailedCount int --定义输出参数
exec usp_ScoreQuery3 @ASentCount output,@FailedCount output,65,70
select 缺考总人数=@ASentCount,不及格总人数=@FailedCount
