Transcript 设计和创建学生成绩数据库的索引

《网络数据库技术》
—— 省级精品课程
http://172.16.38.204/index.asp
学习情境5
设计和创建学生成绩数据库的索引、视图
主讲：余建军
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
技能目标：

掌握索引的创建

掌握视图的创建
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务1 用SQL-EM创建表s列sname非聚集索引
IX_SNAME
SQL-EM→S表→右键→所有任务→管理索引→新建
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
[相关知识1.1] 索引
概念：要提高查询速度，必须按查询字段对记录排序
索引是表中数据和存储位置的对应表
聚集(簇)索引 ： 索引顺序与数据物理顺序相同 1个
种类
非聚集(簇)索引 ：索引顺序与数据物理顺序不同 249个
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
索引实例
记录的物理顺序：
主键的升序，聚集索引
记录的逻辑顺序：
索引表的升序，非聚集索引
学号
0001
0002
0003
0004
0005
0006
姓名 性别 ……
张三 男
李四 女
王五 女
赵六 男
杨七 女
马八 男
按姓名排序
衢州职业技术学院
学号
0002
0006
0003
0005
0004
0001
索引表
网络数据库技术
http://172.16.38.204/index.asp
[相关知识1.2]索引规则





按查询字段与索引字段对应使用索引。
索引是非显示的，查询时自动调用。
创建主键（唯一性键）时，自动创建（唯一性非）聚集索
引。
索引可以提高查询速度，但维护索引要占一定的时间和空
间。
常用查询字段应建索引，域小字段不应建索引。
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务2 使用SQL语句创建表c列cname唯一非聚集索引
IX_CNAME
create unique index IX_CNAME
on c(cname)
[相关知识2.1]唯一索引
create unique index 是创建唯一索引，前提是索引列上的数
据不能有重复值。
当唯一索引创建后,如果插入或更新的值回导致索引列的值
重复时将导致一个错误．
如将某列定义为唯一键(完整性定义)时,自动创建该列的唯一
性非聚集索引.
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
[相关知识2.2]唯一索引
语句格式:
CREATE [UNIQUE] [CLUSTERED] INDEX <索引名>
ON <表名>(<列名> [DESC][,…n])]
CLUSTERED:聚集索引
[ASC | DESC]:确定具体某个索引列的升序或降序排序
方向。默认设置为 ASC。
可以对多列建立索引
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务3 删除C表索引IX_CNAME,改成非唯一索引(因
为出现课程同名,但学分不同的情况)—用SQL-EM
SQL-EM→C表→右键→所有任务→管理索引→删除
注意
“表→设计表→管理索引/键”也可以管理索引
主键和唯一键创建的索引只能用这种方法删除
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务4 删除C表索引IX_CNAME,改成非唯一索引(因
为出现课程同名,但学分不同的情况)—用SQL
drop index c.IX_CNAME
create unique index IX_CNAME
on c(cname)
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
[相关知识4.1] DROP INDEX语句
语句格式：
DROP INDEX <表名>.<索引名>[,…n]
注意
唯一键创建的索引不能用这种方法删除
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务5 查看C表的索引
1. 使用SQL-EM
SQL-EM→C表→右键→所有任务→管理索引
2. 使用SQL语句
sp_helpindex c
[相关知识5.1]
语句格式：sp_helpindex ' <表名>'
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务6 创建学生成绩视图v_sc，包括学号、姓名、
课程编号、课程名、成绩.并查询v_sc
CREATE VIEW v_sc
as
select s.sno,sname,c.cno,cname,score
from s,sc,c
where s.sno=sc.sno and c.cno=sc.cno
select * from v_sc
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
[相关知识6.1]视图
概念： 视图是若干表上构造的虚拟表
视图只存在结构，数据在运行时从基表中生成
视图一般用于查询，任何一条SELECT语句都可以
作用： 构建一个视图.
(1)方便查询
(2)隐藏数据库的复杂性
(3)安全控制:访问权限控制,将不同试图的访问权
限授予不同用户(如学生只能访问自己的成绩),基
本表权限不赋予普通用户
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
[相关知识6.2] CREATE VIEW SQL语句
语句格式:
CREATE VIEW <视图名>[<列名>[,…]]
AS
<SELECT语句>
方法：执行SELECT语句，加CREATE
注意：视图名前一般加“v_”或“view_”以区别于表
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务7 使用SQL-EM创建补考学生视图v_bk，包
括学号、姓名、课程编号、课程名、成绩，并查
询v_bk
SQL-EM→视图→右键→新建视图
注意
当定义了外键将自动定义表间关系
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务8 修改视图v_sc,增加班级号和班级名称两列,并
查询
alter VIEW v_sc
as
Select
s.sno,sname,s.classno,classname,c.cno,cname,score
from s,sc,c,class
where s.sno=sc.sno and c.cno=sc.cno and
class.classno=s.classno
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
[相关知识8.1]修改视图语句
1. 使用SQL语句
语句格式：
ALTER VIEW <视图名>[(<列名>)[,…n])]
AS
<SELECT语句>
2. 使用SQL-EM
SQL-EM→视图→右键→设计视图
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
[相关知识8.2]删除视图
1. 使用SQL语句
语句格式：DROP VIEW <视图名>[,…n]
2. 使用SQL-EM
SQL-EM→视图→右键→删除
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务9 创建10网络1班的学生信息视图v_S_10wl1，
并把密码修改为654321
create view v_S_10wl1 as
select * from s
where classno =(select classno from class
where classname='10网络1')
Select * from v_S_10wl1
update v_S_10wl1 set spd='654321‘
Select * from v_S_10wl1
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
[相关知识9.1]使用视图
1. 查询数据
视图可以和表一样在SELECT语句中使用
2. 编辑数据
编辑视图中的数据，实际是编辑基表中的数据，有许多限制
●一次只能编辑一个基表的数据
●不能编辑计算列
●不能违背基表数据完整性规则
注意
原则上视图为查询工具，一般不使用视图编辑数据
视图可以集中数据、隐蔽数据库复杂性、限制访问
视图是外模式，利用视图可以实现数据的逻辑独立性
衢州职业技术学院