数据库系统原理与应用课件(2).ppt

1、nE-R模型的设计及向关系模型的转换模型的设计及向关系模型的转换nE-R模型中的三个组织成分模型中的三个组织成分：实体、属性、联系实体、属性、联系n实体之间的三种联系实体之间的三种联系n把把E-R图转换成关系模式图转换成关系模式12.1 创建视图和删除视图创建视图和删除视图 视图在数据库中是作为一个独立的对象进行存储视图在数据库中是作为一个独立的对象进行存储的。视图和查询很相似，但也有区别：的。视图和查询很相似，但也有区别：存储方式：视图存储为数据库设计的一部分，而查询则不是存储方式：视图存储为数据库设计的一部分，而查询则不是更新结果：对视图和查询的结果集更新限制是不同的更新结果：对视图和查询

2、的结果集更新限制是不同的排序结果：可以排序任何查询结果，而视图只有包括排序结果：可以排序任何查询结果，而视图只有包括TOP子子句时才能排序句时才能排序参数设置：可以为查询创建参数，但不能为视图创建参数参数设置：可以为查询创建参数，但不能为视图创建参数加密：可以加密视图，但不能加密查询。加密：可以加密视图，但不能加密查询。12.1 创建视图和删除视图创建视图和删除视图一、创建视图一、创建视图SQL Server2005提供了提供了2种方法来创建视图：种方法来创建视图：使用使用SQL Server控制管理器控制管理器使用使用CREATE VIEW语句语句12.1 创建视图和删除视图创建视图和删除视

3、图CREATE VIEW view_name (column,n)WITH view_attribute,n AS select_statement WITH CHECK OPTION其中其中WITH CHECK OPTION是强制视图上执行的所是强制视图上执行的所有数据修改都必须符合有数据修改都必须符合select_statement设置的准则。设置的准则。view_attribute:ENCRYPTION，防止将视图作为，防止将视图作为SQL Server复制的一部分发布；复制的一部分发布；SCHEMABINDING，把视，把视图绑定到架构上；图绑定到架构上；VIEW_METADATA，指

4、定为引用，指定为引用视图的查询请求浏览模式的元数据时，返回有关视图视图的查询请求浏览模式的元数据时，返回有关视图的元数据信息，而不是基表或表。的元数据信息，而不是基表或表。12.1 创建视图和删除视图创建视图和删除视图书上例书上例12.2创建名为创建名为st1_degree的视图，包括的视图，包括所有学生的姓名、课程和成绩。所有学生的姓名、课程和成绩。12.1 创建视图和删除视图创建视图和删除视图二、删除视图二、删除视图SQL Server2005提供了提供了2种方法来删除视图：种方法来删除视图：使用使用SQL Server控制管理器控制管理器使用使用DROP VIEW语句语句DROP VIE

5、W view_name,n可以一次删除一个或多个视图可以一次删除一个或多个视图12.2 使用视图使用视图1.使用视图进行数据查询使用视图进行数据查询SELECT*FROM st1_degree2.通过视图向基表中插入数据通过视图向基表中插入数据 一般只针对简单的视图。要符合下面条件：一般只针对简单的视图。要符合下面条件：需要有插入数据的权限需要有插入数据的权限对于视图中未引用的字段，数据表应该允许为空对于视图中未引用的字段，数据表应该允许为空或有默认值或有默认值视图不能包含多个字段值的组合以及统计函数视图不能包含多个字段值的组合以及统计函数视图不能包含视图不能包含DISTINCT或或GROUP

6、 BY不能在一个语句中对多个基表使用数据修改语句不能在一个语句中对多个基表使用数据修改语句12.2 使用视图使用视图书上例书上例12.512.2 使用视图使用视图3.通过视图修改基表中的数据通过视图修改基表中的数据12.2 使用视图使用视图4.通过视图删除基表中的数据通过视图删除基表中的数据12.3 视图定义的修改和信息查看视图定义的修改和信息查看一、视图定义的修改一、视图定义的修改SQL Server2005提供了提供了2种方法来修改视图：种方法来修改视图：使用使用SQL Server控制管理器控制管理器使用使用ALTER VIEW语句语句ALTER VIEW view_name (colu

7、mn,n)WITH view_attribute,n AS select_statement WITH CHECK OPTION12.3 视图定义的修改和信息查看视图定义的修改和信息查看二、视图重命名二、视图重命名SQL Server2005提供了提供了2种方法来重命名视图：种方法来重命名视图：使用使用SQL Server控制管理器控制管理器使用存储过程使用存储过程sp_renamesp_rename objname=object_name,newname=new_name ,objtype=object_type其中：其中：object_name为视图的当前名称为视图的当前名称new_nam

8、e为视图的新名称为视图的新名称object_type为重命名对象的类型，默认值为为重命名对象的类型，默认值为NULL12.3 视图定义的修改和信息查看视图定义的修改和信息查看三、查看视图的信息三、查看视图的信息SQL Server2005提供了提供了2种方法来查看视图信息：种方法来查看视图信息：使用使用SQL Server控制管理器控制管理器使用存储过程使用存储过程sp_helptextsp_helptext objname=object_name,数据库完整性是确保数据库中的数据的一致数据库完整性是确保数据库中的数据的一致性和正确性性和正确性13.1 约束约束SQL Server2005提供

9、了多种强制数据完整性提供了多种强制数据完整性的机制的机制PRIMARY KEY约束约束FOREIGN KEY约束约束UNIQUE约束约束CHECK约束约束NOT NULL约束约束13.1 约束约束1.PRIMARY KEY约束约束 可以约束标识列或列集，这些列或列集的可以约束标识列或列集，这些列或列集的值值唯一唯一地标识表中的行。地标识表中的行。CREATE TABLE department /*部门表部门表*/(dno int PRIMARY KEY,/*部门号为主键部门号为主键*/dname char(20),/*部门名部门名*/)13.1 约束约束如在创建表时指定了主键，如在创建表时指定

10、了主键，SQL Server会自动创建一个会自动创建一个“PK_”且后跟表名的主键索且后跟表名的主键索引，这个索引默认是聚集索引，同时也是一引，这个索引默认是聚集索引，同时也是一个唯一索引。个唯一索引。13.1 约束约束13.1 约束约束2.FOREIGN KEY约束约束 可以约束表之间的关系，为表的一列或多可以约束表之间的关系，为表的一列或多列数据提供参照完整性。列数据提供参照完整性。CREATE TABLE worker CREATE TABLE worker /*职工表职工表*/(no int PRIMARY KEY,(no int PRIMARY KEY,/*编号为主键编号为主键*/n

11、ame char(8),/name char(8),/*姓名姓名*/sex char(2),sex char(2),/*性别性别*/dno int dno int /*部门号部门号*/FOREIGN KEY REFERENCES department(dno)FOREIGN KEY REFERENCES department(dno)ON DELETE NO ACTION,ON DELETE NO ACTION,address char(30),/address char(30),/*地址地址*/)ON ACTION:指定删除指定删除因错误而失败。另一因错误而失败。另一个选项个选项CASCAD

12、E:指指定删除已删除行的外定删除已删除行的外键的所有行。键的所有行。13.1 约束约束13.1 约束约束3.UNIQUE约束约束 可以列集内强制执行值的唯一性。如果列可以列集内强制执行值的唯一性。如果列有有UNIQUE约束，约束，SQL Serevr将确保数据除将确保数据除NULL外的所有值均唯一。外的所有值均唯一。CREATE TABLE table5(c1 int UNIQUE,c2 int)13.1 约束约束13.1 约束约束4.CHECK约束约束 通过限制用户输入的值来加强域完整性。通过限制用户输入的值来加强域完整性。指定应用于列中输入的所有值的布尔条件，指定应用于列中输入的所有值的布

13、尔条件，拒绝所有不为拒绝所有不为TRUE的值。的值。CREATE TABLE table6CREATE TABLE table6(f1 int,(f1 int,f2 int NOT NULL CHECK(f2=0 and f2=0 and f2=100)限定f2只能为0到10013.1 约束约束13.1 约束约束5.列约束和表约束列约束和表约束列约束被指定为列定义的一部分，且仅适列约束被指定为列定义的一部分，且仅适用于那个列用于那个列表约束的声明与列的定义无关，适用于表表约束的声明与列的定义无关，适用于表中一个以上的列中一个以上的列当一个约束中必须包含一个以上的列时，当一个约束中必须包含一个以

14、上的列时，必须使用表约束必须使用表约束13.2 默认值默认值 SQL Server2005提供了两种使用默认值的提供了两种使用默认值的方法方法创建表时指定默认值创建表时指定默认值使用使用CREATE DEFAULT创建默认对象，创建默认对象，然后使用存储过程然后使用存储过程sp_bindefault来绑定到指来绑定到指定列上定列上13.2 默认值默认值1.创建表时指定默认列创建表时指定默认列 1)使用使用SQL Server管理控制器管理控制器性别默认为男13.2 默认值默认值2)使用使用T-SQL语言语言默认为10默认为当前日期也可以通过也可以通过ALTER TABLE来给列加上默认值：来给

15、列加上默认值：ALTER TABLE table8 ADD CONSTRAINT con1 DEFAULT getdate()FOR C313.2 默认值默认值2.使用默认对象使用默认对象 默认对象是单独存储的，删除表时默认对象是单独存储的，删除表时DEFAULT约束会自动删除，但默认对象不会删除。约束会自动删除，但默认对象不会删除。13.3 规则规则 规则限制了可以存储在表中或者用户定义数规则限制了可以存储在表中或者用户定义数据类型的值。据类型的值。规则与规则与CHECK约束功能基本相同，但一个列约束功能基本相同，但一个列只能应用一个规则，却可以应用多个只能应用一个规则，却可以应用多个CHE

16、CK约约束。束。CHECK可作为可作为CREATE TABLE语句的一语句的一部分进行指定，而规则以单独的对象创建，然部分进行指定，而规则以单独的对象创建，然后绑定到列上。后绑定到列上。13.3 规则规则14.1 概述概述 存储过程是在数据库服务器端执行的一存储过程是在数据库服务器端执行的一组组T-SQL语句的集合，经编译后存放在数据语句的集合，经编译后存放在数据库服务器端。库服务器端。SQL Server 2005提供提供3种存储过程：种存储过程：用户存储过程：用户编写的可重复用的用户存储过程：用户编写的可重复用的T-SQL功能模块功能模块系统存储过程：由系统提供，常用系统存储过程：由系统提

17、供，常用sp_为前为前缀缀扩展存储过程：用户使用编程语言创建的扩展存储过程：用户使用编程语言创建的外部例程外部例程14.2 创建存储过程创建存储过程1.使用使用SQL Server管理控制器创建管理控制器创建14.2 创建存储过程创建存储过程2.使用使用CREATE PROCEDURE创建创建CREATE PROCEDURE CREATE PROCEDURE 存储过程名存储过程名 ;number;numberparameter data_typeparameter data_typeVARYING=default OUTPUTVARYING=default OUTPUT,n,nWITH REC

18、OMPILE|ENCRYPTION|WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION RECOMPILE,ENCRYPTION FOR REPLICATIONFOR REPLICATIONAS sql_statement n AS sql_statement n 14.2 创建存储过程创建存储过程例：创建存储过程例：创建存储过程stud_degree用于检索所有用于检索所有学生的成绩记录学生的成绩记录14.3 执行存储过程执行存储过程EXECUTE return_status=EXECUTE return_status=存储过程名存储过程名 ;num

19、ber|procedure_name_var;number|procedure_name_varparameter=value|variable OUTPUT|parameter=value|variable OUTPUT|DEFAULT,nDEFAULT,nWITH RECOMPILE WITH RECOMPILE 其中其中OUTPUTOUTPUT指定存储过程必须返回一个参数指定存储过程必须返回一个参数DEFAULTDEFAULT根据过程的定义提供参数的默认值根据过程的定义提供参数的默认值WITH RECOMPILEWITH RECOMPILE强制编译新的计划强制编译新的计划14.3 执行存

20、储过程执行存储过程例：执行存储过程例：执行存储过程stud_degree14.4 存储过程的参数存储过程的参数1.在存储过程中使用参数在存储过程中使用参数例：设计存储过程例：设计存储过程maxno，以学号为参数，以学号为参数，输出指定学号学生的所有课程中最高分和对输出指定学号学生的所有课程中最高分和对应的课程名。应的课程名。14.4 存储过程的参数存储过程的参数执行存储过程执行存储过程方式方式1：EXEC maxno 105方式方式2：EXEC maxno no=10514.4 存储过程的参数存储过程的参数2.在存储过程中使用默认参数在存储过程中使用默认参数例：设计存储过程例：设计存储过程ma

21、xno1，以学号为参数，以学号为参数，学号默认为学号默认为101，输出指定学号学生的所有课，输出指定学号学生的所有课程中最高分和对应的课程名。程中最高分和对应的课程名。14.4 存储过程的参数存储过程的参数3.在存储过程中使用返回参数在存储过程中使用返回参数例：设计存储过程例：设计存储过程average，返回指定学号的代，返回指定学号的代表姓名和平均分的表姓名和平均分的2个参数个参数st_name、st_avg表示返表示返回参数回参数14.4 存储过程的参数存储过程的参数例：设计存储过程例：设计存储过程stud1_degree，能根据学号和，能根据学号和课程号来返回成绩等级。课程号来返回成绩等

22、级。14.4 存储过程的参数存储过程的参数4.存储过程的返回值存储过程的返回值存储过程在执行后会返回一个整型值，存储过程在执行后会返回一个整型值，如为如为0则表示执行成功，如为负则表示出则表示执行成功，如为负则表示出错。错。例：例：EXEC i=stud1_degree 101,3-105,dj OUTPUT14.5 存储过程的管理存储过程的管理1.查看存储过程查看存储过程使用使用SQL Server管理控制器管理控制器使用系统存储过程使用系统存储过程1)sp_help name 显示存储过程的参数及数据类型显示存储过程的参数及数据类型2)sp_helptext name 显示存储过程的源代码

23、显示存储过程的源代码3)sp_depends name 显示和存储过程相关的数据库显示和存储过程相关的数据库对象对象4)sp_stored_procedures 返回当前数据库中的存储返回当前数据库中的存储过程列表过程列表14.5 存储过程的管理存储过程的管理2.修改存储过程修改存储过程 ALTER PROCEDURE 存储过程名存储过程名参数列参数列表表 AS sql_statement3.重命名存储过程重命名存储过程 sp_rename 原存储过程名原存储过程名,新存储过程名新存储过程名4.删除存储过程删除存储过程 DROP PROCEDURE 存储过程列表存储过程列表自学要求与上课安排自

24、学要求与上课安排自学与上机内容：自学与上机内容：自学第自学第15章章 上机内容：上机内容：完成上机实习：做完上机练习完成上机实习：做完上机练习7的第七的第七部分。部分。做的快的同学可做上机练习做的快的同学可做上机练习7的第八部的第八部分。分。table_sourcePIVOT(聚合函数（value_column）FOR pivot_columnIN()机号、学号、姓名。机号、学号、姓名。你上机的完成情况，各部分做出你上机的完成情况，各部分做出几小题，几小题，没完成的题号没完成的题号。上机练习上机练习5中中二、利用上次上机的学生二、利用上次上机的学生_课程数据课程数据库库(前前12题以机号除以题

25、以机号除以6来选二题，来选二题，13-16中任选一中任选一)上机练习上机练习6中：一、中：一、书上练习题书上练习题10(22-24中选一中选一)二、书上上机实验题二、书上上机实验题5(5-7中选一，中选一，8-10中选一中选一)上机练习上机练习7中：中：三、三、书上上机实验题书上上机实验题8(1-5中中以机号除以以机号除以5来选一题来选一题)上机练习上机练习8中：一中：一、书上上机实验题书上上机实验题9(1-2中中以机号除以以机号除以2来选一题来选一题)二二、书上上机实验题书上上机实验题10(1-2中中以机号除以以机号除以2来选一题来选一题)要求写出要求写出题目题目、SQL命令命令(不能截图不能截图)以及以及界面截图界面截图。文件名为学号文件名为学号+姓名，在姓名，在3.7前发前发。期末书面小结要求期末书面小结要求