《数据库实验》PPT课件.ppt

1、数据库系统原理实验,计算机与信息科学系 段华斌,实 验 一,实验名称：熟悉 SQL SERVER 2000的环境及数据库的管理 实验学时：2 实验目的： 1）熟悉SQL SERVER 2000企业管理器、查询分析器的基本使用方法。 2）了解SQL SERVER 2000数据库的逻辑结构和物理结构及其结构特点。 3）掌握在企业管理器中创建数据库。 4）掌握使用T-SQL语句创建数据库,实验内容及步骤：,一、SQL SERVER 2000管理工具介绍 1、企业管理器介绍 企业管理器是基于一种新的被称为微软管理控制 台的公共服务器管理环境，它不仅能够配置系统环境 和管理SQL Server，而且所有

2、SQL Server对象的建立 与管理都可以通过它来完成。 2、服务管理器（Service Manager） 服务管理器是在服务器端实际工作时最有用的实用 程序。服务管理器用来启动、暂停、继续和停止数据 库服务器的实时服务。 3、查询分析器（Query Analyzer） 图形化查询分析器用于输入和执行Transaction-SQL 语句，并且迅速查看这些语句的结果，以分析和处理 数据库中的数据。,（二）创建数据库,a.在企业管理器中创建数据库。 （）在企业管理器中，单击工具栏中的 图标，或在数据库文件夹或其下属任一数据库图标上单击右键，选择新建数据库选项，就会出现数据库属性对话框。 （）在常

3、规（General）页框中，要求用户输入数据库名称以及排序规则名称。 （）点击数据文件（Data Files）页框，该页框用来输入数据库文件的逻辑名称、存储位置、初始容量大小和所属文件组名称。 （）点击事务日志（Transaction Log）页框，该页框用来设置事务日志文件信息， （）单击“确定”按钮，则开始创建新的数据库。,（二）创建数据库,b.使用T-SQL语句创建数据库 语法如下： CREATE DATABASE database_name ON PRIMARY ，n ， ，n LOG ON ，n FOR RESTORE :=(NAME=logical_file_name， FILEN

4、AME=os_file_name ，SIZE=size ，MAXSIZE=max_size|UNLIMITED ，FILEGROWTH=growth_increment ) ，n :=FILEGROUP filegroup_name ，n,（二）创建数据库,例1：使用CREATE DATABASE创建一个student数据库，所有参数均取默认值。 Create database student 例2：创建一个gzgl数据库，该数据库的主文件逻辑名称为gzgl，物理文件名为gzgl_data.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为gzgl_lo

5、g，物理文件名为gzgl_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。,Create database gzgl On primary (name= gzgl _data, filename=e:datagzgl_data.mdf, size=10, maxsize=unlimited, filegrowth=10%) log on (name= gzgl _log, filename=e:datagzgl_log.ldf, size=1, maxsize=5, filegrowth=1),（二）创建数据库,利用企业管理器删除数据库 在企业管理器中，右击要删除的数据库

6、，从弹 出的快捷菜单中选择“删除”或按下“delete”键。 利用Drop语句删除数据库 语法格式：Drop database database_name,n 说明：只有处于正常状态下的数据库，才能使用 DROP语句删除。当数据库处于以下状态时不能 被删除：数据库正在使用；数据库正在恢复；数 据库包含用于复制的已经出版的对象。 例1：删除已创建的数据库student drop database student,(三)删除数据库,（四）修改数据库,利用企业管理器修改数据库 数据库创建以后，可以在企业管理器中 利用数据库的属性设置，来更改数据库创建 的某些设置，以及创建时无法设置的属性。 在企业管

7、理器中，右击要修改的数据库，选 择“属性”选项，在弹出的数据库属性窗口 中进行修改。,（四）修改数据库,利用Drop语句修改数据库 Alter database databasename add file,n to filegroup filegroupname |add log file ,n |remove file logical_file_name |remove filegroup filegroup_name |modify file |modify name=new_databasename |add filegroup filegroup_name |modify filegr

8、oup filegroup_name filegroup_property|name=new_filegroup_name,实验二,实验名称：表的创建和管理 实验学时：2 实验目的： 了解SQL SERVER的基本数据类型及空值的概念。 掌握在企业管理器中进行表的创建和管理。 掌握使用T-SQL语句进行表的创建和管理。,实验内容及步骤：,创建表 a.利用企业管理器创建表 在企业管理器中，展开指定的服务器和数据 库，打开想要创建新表的数据库，右击表对象， 并从弹出的快捷菜单中选择“新建表”，出现对话 框。,b.利用T-SQL语句创建表,CREATE TABLE database_name. ow

9、ner .| owner. table_name （ | column_name AS computed_column_expression| ，n） ON filegroup | DEFAULT NULL | NOT NULL | PRIMARY KEY | UNIQUE |FOREIGN KEY （column ,n） REFERENCES ref_table （ ref_column ,n） NOT FOR REPLICATION,创建、删除和修改约束,a.利用企业管理器 在企业管理器中，右击要操作的数据表，从 弹出的快捷菜单中选择“设计表”选项，出现设计 表对话框，选择要设定为主键的字

10、段，如果有 多个字段，按住Ctrl键的同时，用鼠标单击选中 的某个字段，从弹出的快捷菜单中选择“设置主 键”选项；在属性对话框中的“索引/键”选项卡中 设置。,b.利用T-SQL语句 CONSTRAINT constraint_name PRIMARY KEY/UNIQUE CLUSTERED|NONCLUSTERED （column_name,n） 参数说明： constraint_name约束名称，在数据库中应该唯一，不指定，系统会自动生成一个约束名。 CLUSTERED|NONCLUSTERED索引类型，聚族索引或非聚族索引，CLUSTERED为默认值。 column_name用于指定主

11、键的列名。最多由16个列组成。,创建、删除和修改约束,增加、删除和修改字段,a.利用企业管理器 在企业管理器中，右击要操作的数据表，从 弹出的快捷菜单中选择“设计表”选项，出现设 计表对话框，在对话框可完成增加、删除和修 改字段的操作。,增加、删除和修改字段,c.利用T-SQL语句 ALTER TABLE table ALTER COLUMN column_name new_data_type （ precision , scale ） COLLATE NULL | NOT NULL | ADD | DROP ROWGUIDCOL | ADD | column_name AS computed

12、_column_expression ,.n | WITH CHECK | WITH NOCHECK ADD ,.n | DROP CONSTRAINT constraint_name | COLUMN column ,.n | CHECK | NOCHECK CONSTRAINT ALL | constraint_name ,.n | ENABLE | DISABLE TRIGGER ALL | trigger_name ,.n ,查看表格,a.利用企业管理器 在企业管理器中，右击要操作的数据表，从弹出的快捷菜单中选择“属性”选项，出现表属性对话框，在对话框可显示对表格的定义。 在企业管理器

13、中，右击要操作的数据表，从弹出的快捷菜单中选择“打开表”选项，该选项有三个子项：“返回所有行”：显示所有记录；“返回首行”：显示前N条记录，N可自己输入；“查询”：查询具体某行记录。,查看表格,b.利用T-SQL语句 语法：sp_help objname=name 系统存储过程Sp_help可以提供指定数据库对象的信息，只用于当前数据库。其中objname=name字句用于指定对象的名称，如果不指定对象名称，sp_help存储过程就会列出当前数据库中的所有对象名称、对象的所有者和对象的类型。 例1：显示当前数据库中所有对象的信息 use gzgl go exec sp_help,更改表的名称,

14、语法：sp_rename objname=object_name,newname= new_name ,objtype= object_type objtype= object_type指定要改名的对象的类型，其值可以为COLUMN,DATABASE,INDEX,USERDATATYPE,OBJECT。值OBJECT指代系统表中的所有对象，OBJECT值为默认值。,删除表,a.利用企业管理器 在企业管理器中，右击要删除的数据表，从弹出的快捷菜单中选择“删除”选项，出现“除去对象”对话框，在对话框完成操作。 b.利用T-SQL语句 DROP TABLE table_name,实验三,实验名称：表

15、中数据的操作 实验学时：4 实验目的： 掌握在企业管理器中对表进行插入、修改和删除数据的操作。 掌握使用T-SQL语句对表进行插入、修改和删除数据的操作。 了解使用企业管理器完成查询操作。 重点掌握 SELECT语句的使用方法。 掌握子查询（嵌套查询）、连接查询的使用。,在企业管理器中对数据库中的表进行插入、修改和删除数据,例1：在企业管理器中向数据库gzgl中的表输入数据 在企业管理器中向jbxx表插入记录，选择并用鼠标右击表jbxx 选择“返回所有行” 逐字段输入各记录值，输入完后关闭窗口。 例2：在企业管理器中将表jbxx中编号为020805的记录的部门号改为003 在企业管理器中选择表

16、并用鼠标右击表jbxx 选择“返回所有行” 将光标定位至employee_id为020805的记录的department_id字段，改为003。 例3：在企业管理器中删除数据库gzgl表数据 在企业管理器中删除表jbxx的第3、6行操作步骤：在企业管理器中选择表并用鼠标右击表jbxx 选择“返回所有行” 选择要删除的行 单击鼠标右键 删除 关闭表窗口。,使用T-SQL语句对表进行插入、修改和删除数据的操作,使用INSERT语句插入数据 语法：INSERT INTO table_name| view_name （ column_list ） VALUES （ DEFAULT | NULL | e

17、xpression ,.n ）| derived_table,使用UPDATE语句更新数据,UPDATE table_name | view_name FROM ,.n SET column_name = expression | DEFAULT | NULL ,.n WHERE search_condition ,使用DELETE语句删除数据,DELETE FROM table_name WITH ( .n )| view_name WHERE 或者使用TRUNCATE TABLE name,SELECT语句的使用方法,基本格式如下： SELECT select_list INTO new_

18、table FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression ASC | DESC ,实 验 四,实验名称：视图的创建和使用 实验学时：2 实验目的： 了解视图和数据表之间的主要区别 掌握在企业管理器中创建视图的方法。 掌握使用T-SQL语句创建视图的方法。 掌握查看视图修改数据表的方法。,实验内容及步骤：,在企业管理器中创建视图 打开企业管理器窗口，打开新建视图对话框。 方法一：在企业管理器左边的“树”选

19、项卡中选择指定的SQL SERVER组，展开指定的服务器，打开要创建视图的数据库文件夹，选中指定的数据库，右击该数据库图标，从弹出的快捷菜单中依次选择“新建”|“视图”选项，打开新建视图对话框。 方法二：在数据库文件夹中，用鼠标右击下一层的“视图”选项，在弹出的快捷菜单中选择“新建视图”选项。,在企业管理器中创建视图,在新建视图对话框中，右击窗口上部的空白部分，从弹出的快捷菜单中选择“添加表”选项，或者单击工具栏中的按钮，出现“添加表”对话框，在该框中可以选择需要添加的基本表，单击“添加”按钮，就可以添加进去；也可以某个表名来添加表。使用同样的方法可以切换到“视图”或“函数”选项卡，从中选择需

20、要的视图或函数，并依次创建新的视图。 这里利用Ctrl键和鼠标配合，同时选前面建立的3个表S,SC,C，并单击“添加”按钮，即可将这3个表添加到创建视图对话框中。然后通过单击字段左边的复选框选择需要的字段，这里选择s表中sno,sname，C表中cno，SC表中sno,cno，属性设置如下： 选中“输出”复选框，可以在输出结果中显示该字段。 在“准则”复选框中输入限制条件，可以限制输出的记录。在定义视图的查询语句中该相知条件对应WHERE子句。,在企业管理器中创建视图,右击字段定义对话框，从弹出的快捷菜单中选择 “属性”选项，出现视图属性对话框。该对话框中， “distinct值”可以选择不输

21、出重复的记录，“加密浏览” 可以实现对视图定义加密，选中“顶端”复选框可以限 制视图最多输出的记录条数。 要运行并输出该视图结果，可以在视图设计窗口中 单击工具栏中“！”按钮，或右击窗口空白区，在弹出 的快捷菜单中选择“运行”选项，则可根据设置的查询 语句，在本窗口最下面的数据结果区显示出生成的视 图内容。 可以单击工具栏中的“保存”按钮，或者在窗口上部 显示数据表的窗格内单击鼠标右键，从弹出的快捷菜 单中选择“保存”选项保存视图。,使用T-SQL语句创建视图,语法：CREATE VIEW . . view_name （ column ,.n ） WITH ,.n AS select_stat

22、ement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA ,使用企业管理器查看视图信息,在企业管理器左边的“树”选项卡中选择指定的SQL SERVER组，展开指定的服务器，打开要查看视图的数据库文件夹，选择数据库文件夹下的“视图”目录，在右边窗格中回列出当前数据库中的所有视图。若要查看视图的基本信息，右键单击要查看的视图，在弹出的快捷菜单中选择“属性”选项，打开视图属性对话框；在弹出的快捷菜单中依次选择“所有任务”“显示相关性”选项，打开视图相关性对话框。在弹出的快捷菜单中依次选择“打开视图”“返回所有行”，在企

23、业管理器中就会显示该视图的输出数据。,使用T-SQL语句查看视图信息,sp_help 数据库对象名称 报告有关数据库对象（sysobjects 表中列 出的任何对象）、用户定义数据类型或 Microsoft SQL Server 所提供的数据类型的 信息 sp_helptext 视图（触发器、存储过程） 显示规则、默认值、未加密的存储过程、用户 定义函数、触发器或视图的文本。 sp_depends 数据库对象名称 显示有关数据库对象相关性的信息（例如，依 赖表或视图的视图和过程，以及视图或过程所 依赖的表和视图）,删除视图,在企业管理器打开要查看视图的数据库文件夹，选择数据库文件夹下的“视图”

24、目录，右键单击要删除的视图，在快捷菜单中选择“删除”命令，打开“出去对象”对话框 使用T-SQL语句删除视图 DROP VIEW view_name ,n 删除视图时，将从sysobjects、syscolumns、syscomments、sysdepends和sysprotects系统表中删除视图的定义及其他有关视图的信息。还将删除视图的所有权限。已删除的表上的任何视图必须通过使用DROP VIEW显示删除,使用视图,使用视图插入表数据 例：通过视图v_s向学生表插入一行数据 Insert into v_s Values (20050009,李华，19，男) 使用视图修改表数据 例：upda

25、te v_s Set sex=女 Where sno=20040001 使用视图删除表数据 例：delete v_s Where sno=20040001,实 验 五,实验名称：索引的创建和使用 实验学时：2 实验目的： 了解索引的概念、优点及分类 掌握在企业管理器中创建、修改和删除索引的操作。 掌握使用T-SQL语句创建、修改和删除索引的操作。,索引的概念：,数据库中的索引是一个列表，在这个列表中包含了某个表中一列或者若干列值的集合，以及这些值的记录在数据表中的存储位置的物理地址。 索引的优点： 可以大大加快数据检索速度。通过创建唯一索引，可以保证数据记录的唯一性。在使用ORDER BY和G

26、ROUP BY子句进行检索数据时，可以显著减少查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。,索引的分类：,1聚集索引和非聚集索引 2复合索引 3唯一索引,在企业管理器中创建索引,在企业管理器中，展开指定的服务器和数据库，右击要创建索引的表，从弹出的快捷菜单中依次选择“所有任务|管理索引”选项。在出现的管理索引对话框中，可以选择要处理的数据库和表，然后单击“新建”按钮，出现“新建索引”对话框。在“索引名称”文本框中输入新建索引的名称，在下面的复选框中选择用于创建索引的字段。可以设定索引

27、的属性，例如是否聚集、是否唯一，还可以建立复合索引，指定填充度属性，在选中字段后，可设置“排序次序”属性。最后单击“确定”按钮，即可生成新的索引。,使用T-SQL语句创建索引,语法：CREATE UNIQUE CLUSTEREDNONCLUSTEREDINDEX index_name ON tableview (column ASCDESC ,n) 例1：为表jbxx创建一个非聚集索引，索引字段为employee_name，索引名为i_employeename create index i_employeename on jbxx(employee_name),使用企业管理器查看、修改和删除索

28、引的操作,在企业管理器中，展开指定的服务器和数据库，右击要创建索引的表，从弹出的快捷菜单中依次选择“所有任务|管理索引”选项，在出现的管理索引对话框中，选择要查看或修改的索引，单击“编辑”按钮，出现“编辑现有索引”对话框。在该对话框中，可以修改索引的大部分设置，还可以直接修改其SQL脚本，只需单击“编辑SQL”按钮，即可出现“编辑Transact_SQl脚本”对话框，在此可以编辑、分析、执行索引的Transact_SQl脚本。,使用企业管理器查看、修改和删除索引的操作,要在企业管理器中修改索引的名称，需要在表的“属性”对话框中进行。在企业管理器中，右击要修改名称的表，从弹出的快捷菜单中选择“设

29、计表”选项，在打开的设计表的窗口中，打开表的“属性”对话框，选择“索引/键”选项卡，在此对话框中，先选定要修改索引名称的索引，然后直接在“索引名”文本框中输入心得索引名称替换原来的索引名称。 要删除索引，可以在“管理索引”对话框中或表的“属性”对话框中，选择要删除的索引，单击“删除”按钮，即可删除索引。,使用T_SQL查看、修改和删除索引的操作,使用系统存储过程查看索引信息，语法如下： sp_helpindex objname= name 例1：查看jbxx表的索引信息 sp_helpindex jbxx 使用系统存储过程修改索引名称，语法如下: sp_renameobjname= objec

30、t_name,newname= new_name ,objtype= object_type,实 验 六,实验名称：存储过程的创建和使用 实验学时：2 实验目的： 了解存储过程的概念 了解使用存储过程的特点及用途 掌握创建存储过程的方法 掌握执行存储过程的方法 了解查看、修改和删除存储过程的方法,存储过程的概念,SQL Server的存储过程类似于编程语言中的过程。在使用Transact-SQL语言编程的过程中，我们可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。 存储过程在创建时就

31、被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。,存储过程的优点及分类,存储过程的优点 ： 实现了模块化编程。 存储过程具有对数据库立即访问的功能。 使用存储过程可以加快程序的运行速度。 使用存储过程可以减少网络流量。 使用存储过程可以提高数据库的安全性。 存储过程的分类 系统存储过程 ：系统自动创建，主要存储在master数据库中，一般以sp_为前缀。 用户自定义存储过程 ：由用户创建并能完成某一特定功能的存储过程。,创建存储过程的方法,使用企业管理器创建存储过程： 在SQL Server企业管理器中，选择指定的服务器和数据库，右击要创建存储过程的数据库，在弹出的

32、快捷菜单中依次选择“新建|存储过程”选项。在弹出的“新建存储过程”对话框中的文本框中输入创建存储过程的T-SQL语句。,使用 T-SQL创建存储过程,使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。语法如下： CREATE PROCEDURE procedure-name;number parameter data_type varying=defaultoutpur ,n WITH RECOMPILEENCRYPTIONRECOMPILE，ENCRYPTION FOR REPLICATION AS sql_statement n,执行存储过程的方法,存储

33、过程创建成功后，保存在数据库中。在SQL Server中可以使用EXECUTE命令来直接执行存储过程。语法如下： EXECUTE RETURN_STATUS= procedure_name;numberprocedure_name_var parameter=valuevariableOUTPUTDEFAULT ,n WITH RECOMPILE,查看存储过程,使用企业管理器查看用户创建的存储过程： 在企业管理器中，选择指定的服务器和数据库，单击“存储过程”文件夹，在右边的页框中右击要查看的存储过程，在弹出的快捷菜单中选择“属性”选项，弹出“存储过程属性” 对话框，在此对话框中可看到存储过程的

34、源代码。,查看存储过程,使用系统存储过程查看用户创建的存储过程： sp_help objname=name 用于显示存储过程的参数及其数据类型。 sp_helptext objname=name 用于显示存储过程的代码。 sp_depends objname=object 用于显示和存储过程相关的数据库对象。,修改存储过程,使用企业管理器修改存储过程 在企业管理器中，选择指定的服务器和数据库，单击“存储过程”文件夹，在右边的页框中右击要查看的存储过程，在弹出的快捷菜单中选择“属性”选项，弹出“存储过程属性” 对话框，在此对话框中可直接修改存储过程的代码。,修改存储过程,使用T_SQl修改存储过

35、程 ALTER PROCEDURE procedure-name;number parameter data_type varying=defaultoutpur,n WITH RECOMPILEENCRYPTIONRECOMPILE，ENCRYPTION FOR REPLICATION AS sql_statement n,重命名存储过程,使用企业管理器修改存储过程 在企业管理器中，右击要操作的存储过程，在弹出的快捷菜单中选择“重命名”选项。 使用T_SQl修改存储过程 sp_rename 原存储过程名称，新存储过程名称,删除存储过程,使用企业管理器修改存储过程 在企业管理器中，右击要操作的

36、存储过程，在弹出的快捷菜单中选择“删除”选项。 使用T_SQl修改存储过程 drop procedure procedure ,n,实 验 七,实验名称：触发器的创建和使用 实验学时：2 实验目的： 了解触发器和一般存储过程的主要区别 了解使用触发器的优点 掌握创建触发器的方法 掌握查看触发器信息的方法 了解删除触发器的方法,触发器的概念,触发器是一种特殊类型的存储过程，它不同于前面介绍过的一般的存储过程。一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行触发器是一个功能强大的工具，它与表格紧密相连，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以用普通约束实现的复杂功能。,触发器的类型,AFTER触发器：这种类型的触发器将在数据变动完成后才被触发，AFTER触发器只能在表上定义。在同一个数据表中可以创建多个AFTER触发器。 INSTEAD OF触发器：SQL