模式表视图和索引.docx
- 文档编号:10072725
- 上传时间:2023-05-23
- 格式:DOCX
- 页数:18
- 大小:96.26KB
模式表视图和索引.docx
《模式表视图和索引.docx》由会员分享,可在线阅读,更多相关《模式表视图和索引.docx(18页珍藏版)》请在冰点文库上搜索。
模式表视图和索引
SQL的动词
SQL功能
动词
数据查询
select
数据定义
create,dropalter
数据操纵
insert,update,delete
数据控制
grant,revoke
SQL的数据定义语句
操作对象
操作方式
创建
删除
修改
模式
Createschema
Dropschema
表
Createtable
Droptable
Altertable
视图
Createview
Dropview
索引
Createindex
Dropindex
注:
以下是基于sqlserver2000的测试,其中2000不支持模式,可以在2005/2008以上高版本测试
模式:
--语法:
createschema<模式名>authorization<用户名>
createschema"S-T"authorizationHMH
消息15151,级别16,状态1,第3行
无法对用户'HMH'执行查找,因为它不存在,或者您没有所需的权限。
消息2759,级别16,状态0,第3行
由于前面的错误,CREATESCHEMA失败。
解决:
1、去掉后面的authorizationHMH
2、必须要创建HMH用户才能这样创建模式
--没有指定<模式名>,那么<模式名>隐含为<用户名>
createschemaauthorizationHMH
执行结果:
命令已成功完成
但是并没有创建模式,有两点可以证明
1、再次执行这句语句,结果仍是成功。
而一个数据库只能有一个模式
2、执行dropschemaHMH
结果:
消息15151,级别16,状态1,第1行
无法对架构'HMH'执行删除,因为它不存在,或者您没有所需的权限。
/*语法:
createschema<模式名>authorization<用户名>
[<表定义子句>|<视图定义子句>|<授权定义子句>]
创建模式的同时创建基本表、视图,定义授权
*/
createschema"S-T"authorizationHMH
createtabletab1(
col1smallint,
col2int,
col3char(20),
col4numeric(10,3),
col5decimal(5,2)
);
--语法:
dropschema<模式名>
/*cascade(级联),表示在删除模式的同时把该模式中
所有的数据库对象全部一起删除
restrict(限制),表示如果该模式中已经定义了下属
的数据库对象(如表、视图等),则拒绝该删除语句的
执行
*/
dropschema"S-T"cascade
表:
/*语法:
createtable<表名>(
<列名><数据类型>[列级完整性约束条件]
[,<列名><数据类型>[列级完整性约束条件]]
...
[,<表级完整性约束条件>]
)
createtable<表名>(
<列名><数据类型>|<标识列>|null|notnull
)
*/
--建立一个“学生表”Student
createtableStudent(
Snochar(9)primarykey,--列级完整性约束条件,Sno是主码
Snamechar(20)unique,--Sname取唯一值
Ssexchar
(2),
Sagesmallint,
Sdeptchar(20)
)
--建立一个“课程表”Course
createtableCourse(
Cnochar(4)primarykey,--列级完整性约束条件,Sno是主码
Cnamechar(40),
Cpnochar(4),--Cpno的含义是先修课
Ccreditsmallint,
foreignkeyCpnoreferencesCourse(Cno)
--表级完整性约束条件,Cpno是外码,被参照表是Course,被参照列是Cno
)
消息170,级别15,状态1,第6行
第6行:
'Cpno'附近有语法错误。
修改:
foreignkey(Cpno)referencesCourse(Cno)
解析:
Cpno必须要加上括号
--建立学生选课表SC
createtableSC(
Snochar(9),
Cnochar(4),
Gradesmallint,
primarykey(Sno,Cno),
--主码由两个属性构成,必须作为表级完整性进行定义
foreignkey(Sno)referencesStudent(Sno),
--表级完整性约束条件,Sno是外码,被参照表是Student
foreignkey(Cno)referencesCourse(Cno)
--表级完整性约束条件,Cpno是外码,被参照表是Course
)
-----------------------------模式与表-----------------------------
--方法一:
在表名中明显地给出模式名
createtable"S-T".Student(...);--Student所属的模式是S-T
createtable"S-T".Course(...);--Course所属的模式是S-T
createtable"S-T".SC(...);--SC所属的模式是S-T
--方法二:
在创建模式语句中同时创建表,参照schema.sql
--方法三:
设置所属的模式
setserch_pathto"S-T",public;
createtableStudent(...);
----------------------------修改表----------------------------
/*语法:
altertable<表名>
[add<新列名><数据类型>[完整性约束]]
[drop<完整性约束名>]
[altercolumn<列名><数据类型>]
*/
--向Student表增加“入学时间”列,其数据类型为日期型
altertableStudentaddS_entrancedate
消息2715,级别16,状态7,第1行
第6个列或参数:
无法找到数据类型date。
解决:
将date类型改为datetime,即
altertableStudentaddS_entrancedatetime
--将年龄的数据类型由字符型改为整数
altertableStudentaltercolumnSageint
--增加课程名称必须取唯一值的约束条件
altertableCourseaddunique(Cname);
--查看表信息,sp开头均属系统存储过程
sp_helpStudent--查看Student所有情况
sp_spaceusedStudent--查看Student的行数和存储空间情况
sp_dependsStudent--查看与Student表相关联的数据库对象
sp_renameStudent,Student_new--修改Student表名为Student_new
---------------------------删除表-----------------------
/*语法:
droptable<表名>[restrict|cascade]
选择restrict,则该表的删除是有限制条件的,该表不能被其他表
的约束所引用(如check,foreignkey等约束),不能有视图,不能
有触发器,不能有储存过程或函数等
选择cascade,则该表的删除没有限制条件。
在删除表的同时,相关
的依赖对象,例如视图,都将被一起删除
注:
sqlserver2008中删除表的格式为
droptable<表名>
不支持添加[restrict|cascade]
DROPTABLE不能用来删除FOREIGNKEY约束引用的表。
必须首先删除引用FOREIGNKEY约束或引用表。
删除表时,表中的规则或默认值会失去绑定,还会自动删除与其相关的所有约束。
如果重新创建一个表,则必须重新绑定适当的规则和默认值,添加所有必要的约束。
*/
--7、删除Student表
droptableStudentcascade
--同时删除多个表
droptableSC,Course,Student;
视图:
----------------------------视图-----------------------------
/*语法:
createview[<数据库名>.][用户.]视图名[<列名>[,...n]]
[withencryption]
as<子查询>
[withcheckoption]
说明:
1、对查询的表或视图要有查询权限
2、子查询不能使用compute、computeby、orderby和into子句
3、不能在临时表上创建视图
4、对视图加密
5、withcheckoption指定在视图上修改都要符合<子查询>的限制条件
*/
--1、建立信息系学生的视图
createviewIS_Student
as
selectSno,Sname,Sage
fromStudent
whereSdept='IS'--不能带分号,否则出错
--2、要求进行修改和插入操作时仍需保证该视图只有信息系的学生
createviewIS_Student
as
selectSno,Sname,Sage
fromStudent
whereSdept='IS'
withcheckoption--不能带分号,否则出错
--3、视图建立在多个基本表上
createviewIS_S1(Sno,Sname,Grade)
as
selectStudent.Sno,Sname,Grade
fromStudent.SC
whereSdept='IS'and
Student.Sno=SC.Snoand
SC.Cno='1'--不能带分号,否则出错
--4、视图建立在视图的基础上
createviewIS_S2
as
selectSno,Sname,Grade
fromIS_S1
whereGrade>=90--不能带分号,否则出错
--5、带表达式的视图
createviewBT_S(Sno,Sname,Sbirth)
as
selectSno,Sname,2004-Sage
fromStudent--不能带分号,否则出错
--6、分组视图
createviewS_G(Sno,Gavg)
as
selectSno,avg(Grade)
fromSC
groupbySno--不能带分号,否则出错
---------------------------删除视图------------------------------
/*语法:
dropview<视图名>[,n]
一次可删除多个视图
*/
dropviewIS_S1;
dropviewIS_S1,IS_S2,BT_S;
--------------------------修改视图定义----------------------------
/*语法:
alterview[<数据库名>.][<用户名.>]<视图名>[(<列名>[,...n]]
as<子查询>
[withcheckoption]
*/
alterviewIS_Student
as
selectSno,Sname,Ssex,
fromStudent
whereSdept='CS'--不能带分号,否则出错
--------------------------查询视图--------------------------------
/*语法:
select<列名>[,n]from<视图名>[条件表达式]
*/
selectSno,Sage
fromIS_Student
whereSage<=20;
---------------------------更新视图----------------------------------
updateIS_Student
setSname='刘辰'
whereSno='200215122';
--转换后的更新语句
updateIS_Student
setSname='刘辰'
whereSno='200215122'andSdept='IS';
--执行结果:
(0行受影响)
--原因:
Sno='200215122'不在视图上
updateviewS_G
setGavg=90
whereSno='200215121'
/*执行结果:
消息4403,级别16,状态1,第1行
视图或函数'S_G'不可更新,因为它包含聚合。
*/
insertintoIS_Student
values('200215129','赵新',20);
--执行结果:
(1行受影响)
insertintoIS_Student
values('200215129','赵新',20);
/*执行结果:
消息550,级别16,状态1,第1行
试图进行的插入或更新已失败,原因是目标视图或者目标视图所跨越的某一视图指定了
WITHCHECKOPTION,而该操作的一个或多个结果行又不符合CHECKOPTION约束的条件。
语句已终止。
(0行受影响)
原因:
withcheckoption可以这么解释:
通过视图进行的修改,必须也能通过该视图看到修改后的结果。
比如insert,那么加的这条记录在刷新视图后必须可以看到;如果修改,修改完的结果也必须能通过该
视图看到;如果删除,当然只能删除视图里有显示的记录。
IS_Student视图只是查询出sdept='is'的纪录,而插入的根本不符合sdept='is'呀,所以就不行。
insertintoIS_Student
values('200615129','赵新',20);
/*执行结果:
消息550,级别16,状态1,第1行
试图进行的插入或更新已失败,原因是目标视图或者目标视图所跨越的某一视图指定了
WITHCHECKOPTION,而该操作的一个或多个结果行又不符合CHECKOPTION约束的条件。
语句已终止。
(0行受影响)
原因:
'200615129'不符合Snolike'2002%'
*/
--修改后:
insertintoIS_Student
values('200215129','赵新',20);
--执行结果:
(1行受影响)
updateIS_S1
setGrade=Grade-20,
Sname='李勇'
whereSno='200215121'
/*执行结果:
消息4405,级别16,状态2,第1行
视图或函数'IS_S1'不可更新,因为修改会影响多个基表。
*/
--只能一次修改一个基本表,即分多条修改语句:
updateIS_S1
setGrade=Grade-20
whereSno='200215121'
updateIS_S1
setSname='李勇'
whereSno='200215121'
/*执行结果:
(1行受影响)(1行受影响)*/
--更改了基本表上的数据
deletefromIS_Student
whereSno='200215129';
--执行结果:
(1行受影响);删除了基本表上的记录
索引:
------------------------索引------------------------------
/*语法:
create[unique]--唯一索引
[clustered|nonclustered]--索引储存方式,聚集索引或非聚集索引
index<索引名>
on{[<表名>|<视图>]}(column[asc|desc][,n]--索引定义的依据,
[with
[onfilegroup]--指定索引文件所在的文件组
说明:
1、对于视图创建的聚集索引必须是unique索引
2、必须使用schemabinding关键字定义的视图才能创建索引
3、指定IGNORE_DUP_KEY,当用户插入重复值时将发出警告并取消重复行的插入,
如果没有指定,则会发出警告,并回滚整个insert语句
4、指定DROP_EXISTING,删除已存在的同名索引,有重建索引的作用
语法:
dropindex{<表名|视图]}.<索引名>[,n]
说明:
不能删除用过定义primarykey或unique约束创建的索引,不能对系统表使用
*/
ifexists(selectnamefromsysindexeswherename='Stusname')
dropindexStudent.Stusname
go
createclusteredindexStusnameonStudent(Sname);
go
/*
消息1902,级别16,状态3,第2行
不能在表'Student'上创建多个聚集索引。
请在创建新聚集索引前除去现有的聚集索引'PK__Student__060DEAE8'。
原因:
数据库在创建主键同时,会自动建立一个唯一索引。
如果这个表之前没有聚集索引,同时建立主键时候没有强制指定使用非聚集索引,
则建立主键时候,同时建立一个唯一的聚集索引。
一个表最多一个聚集索引。
*/
主键
聚集索引
用途
强制表的实体完整性
对数据行的排序,方便查询用
一个表多少个
一个表最多一个主键
一个表最多一个聚集索引
是否允许多个字段来定义
一个主键可以多个字段来定义
一个索引可以多个字段来定义
是否允许null数据行出现
如果要创建的数据列中数据存在null,无法建立主键。
创建表时指定的PRIMARYKEY约束列隐式转换为NOTNULL。
没有限制建立聚集索引的列一定必须notnull.
也就是可以列的数据是null
参看最后一项比较
是否要求数据必须唯一
要求数据必须唯一
数据即可以唯一,也可以不唯一。
看你定义这个索引的UNIQUE设置。
(这一点需要看后面的一个比较,虽然你的数据列可能不唯一,但是系统会替你产生一个你看不到的唯一列)
创建的逻辑
数据库在创建主键同时,会自动建立一个唯一索引。
如果这个表之前没有聚集索引,同时建立主键时候没有强制指定使用非聚集索引,则建立主键时候,同时建立一个唯一的聚集索引
如果未使用UNIQUE属性创建聚集索引,数据库引擎将向表自动添加一个四字节uniqueifier列。
必要时,数据库引擎将向行自动添加一个uniqueifier值,使每个键唯一。
此列和列值供内部使用,用户不能查看或访问。
ifexists(selectnamefromsysindexeswherename='UQ__Student__07020F21')
dropindexStudent.UQ__Student__07020F21
/*
消息3723,级别16,状态5,第2行
不允许对索引'Student.UQ__Student__07020F21'显式地使用DROPINDEX。
该索引正用于UNIQUEKEY约束的强制执行。
原因:
UQ__Student__07020F21索引是建表时指定的unique索引
*/
createuniqueindexStusnoonStudent(Sno);
createuniqueindexSCnoonSC(Snoasc,Cnodesc);
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 模式 视图 索引
![提示](https://static.bingdoc.com/images/bang_tan.gif)