数据库原理及应用实验指导实验3 表与视图的基础操作.docx

1、数据库原理及应用实验指导实验3 表与视图的基础操作实验3 表与视图的基本操作实验目的1、掌握数据库表与视图的基础知识。2、掌握创建、修改、使用、删除表与视图的不同方法。背景知识在关系数据库中，每个关系都对应为一张表，表是数据库的最主要对象，是用来存储与操作数据的逻辑结构，使用数据库时，绝大多数时间都是在与表打交道。表中有两个基本的对象：字段和记录。字段也叫属性，也就是表中的列。字段的值也称为属性值，属性值的取值范围叫做值域。记录是字段的有序集合。一般用记录描述一个实体，在二维表格中也就是行。设计数据库时应该先确定需要什么样的表，各表中都有什么样的数据以及各个表的存取权限等。在创建和操作表的过程

2、中，需要对表进行细致的设计，在创建表之前必须先确定所有表字段。创建一张表最有效的方法是将表中所需的信息一次定义完成，包括数据约束和各种附加成分。也可以先创建一张基础表，向表中添加一些数据并使用一段时间，然后在需要的时候利用修改表的方法再添加各种约束、索引、默认设置、规则以及其他对象。最好在创建表及其对象时预先完整设计好以下内容。1指定表中所包含数据的类型：表中的每个字段有特殊的数据类型，可以限制插入数据的变化范围。SQL Server 2000还支持用户自定义数据类型，自定义数据类型建立在系统已定义的数据类型基础之上。选择数据类型时应该遵循以下规则：（1）如果列的长度可变，使用某个变长数据类型

3、；（2）对于数值数据类型，数值大小和所需要的精度有助于做出相应的决定；（3）如果存储量超过8000字节，使用Text或Image类型，否则的话可以使用Char或Binary，也可以使用Varchar，因为它比Text或Image具有更强的功能；（4）对于货币数据来说，使用Money数据类型；（5）不要把Float或Real数据类型作为主键，因为这些数据的值是不准确的。2哪些列允许空值：空值或NULL并不等于零、空白或零长度字符串，而是意味着没有输入，常用来表明值未知或不确定。指定一列不允许空值而确保行中该列永远有数据以保证数据的完整性，如果不允许空值，用户在向表中写数据的时候必须在列中输入一个

4、值，否则该行不被接收入数据库。3是否要使用以及何时使用约束、默认设置以及规则，哪些列是主键，哪些是外键。4需要的索引类型以及哪里需要索引。5设计的数据库应该符合第三范式。数据库要想符合第三范式，必须首先符合第一、二范式。6SQL Server 2000中，每个数据库可以有20亿个表，每张表可以有1024个列，每列可以有8060个字节。实验示例1表的创建与使用1、创建表SQL Server 2000提供了两种方法创建数据库表，第一种方法是利用企业管理器创建表；另一种方法是利用transact-sql语句中的create table命令创建表。（1）利用企业管理器创建表-图3-1 创建表结构对话框

5、在企业管理器中，展开指定的服务器和数据库，打开想要创建新表的数据库，用右键单击表对象，从弹出的快捷菜单中选择“新建表”选项，或者在工具栏中选择新建图标，就会出现新建表对话框如图3-1，在该对话框中，可以定义列名称、列类型、长度、精度、小数位数、是否允许为空、缺省值、标识列、标识列的初始值、标识列的增量值和是否有行的标识。（2）利用create table命令创建表。其语法为：CREATE TABLE database_name. owner .| owner. table_name ( | column_name AS computed_column_expression | := CONST

6、RAINT constraint_name | PRIMARY KEY | UNIQUE ,.n ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT := column_name data_type COLLATE DEFAULT constant_expression | IDENTITY ( seed , increment ) NOT FOR REPLICATION ROWGUIDCOL .n := CONSTRAINT constraint_name NULL | NOT NULL | PRIMARY KEY | UNIQUE

7、 CLUSTERED | NONCLUSTERED WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT | FOREIGN KEY REFERENCES ref_table ( ref_column ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION ( logical_expression ) := CONSTRAINT constraint_name PRIMARY KEY

8、 | UNIQUE CLUSTERED | NONCLUSTERED ( column ASC | DESC ,.n ) WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT | FOREIGN KEY ( column ,.n ) REFERENCES ref_table ( ref_column ,.n ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION ( search_c

9、onditions )例1 创建一个雇员信息表的create table命令如下：create table employee(empid int not null primary key clustered,name varchar(20) NOT NULL,sex char(2) NULL,birthday datetime null,hire_date datetime NOT NULL DEFAULT (getdate(),professional_title varchar(10) null,salary money null check(salary is null or salar

10、y=450),memo ntext null )。例2 创建一个学生信息表的create table命令如下：（注意该命令执行时，需要class班级表与speciality专业表已存在，或者先暂时把两外码参照子句去掉后执行。）CREATE TABLE student(sno char(8) NOT NULL PRIMARY KEY,sname char(20) NOT NULL,sclass char(10) NULL DEFAULT(本科),ssex char(2) NULL DEFAULT (男) CHECK (ssex = 男 or ssex = 女),sdh varchar(14) N

11、ULL ,scsrq datetime NULL ,srxrq datetime NULL ,saddr varchar(50) NULL ,smemo varchar(200) NULL ,spno char(4) NULL ,csno char(4) NULL ,CONSTRAINT FK_student_class FOREIGN KEY(csno) REFERENCES class(csno),CONSTRAINT FK_student_speciality FOREIGN KEY(spno) REFERENCES speciality(spno)2、增加、删除和修改字段（1）利用企业

12、管理器操作表字段利用企业管理器增加、删除和修改字段 。在企业管理器中，打开指定的服务器中要修改表的数据库，用右键单击要进行修改的表，从弹出的快捷菜单中选择“设计表”选项，则会出现设计表对话框如图3-2，在该对话框中，可以利用图形化工具完成增加、删除和修改字段的操作。图3-2 表设计器修改表字段（2）利用alter table命令修改表。利用transact-sql语言中的alter table命令也可增加、删除和修改表中字段，其语法略，请读者自己查阅。例3 给student表添加sage年龄字段，命令为：alter table student add sage int -添加sage字段。例4

13、 删除student 表中的sage年龄字段，命令为：alter table student drop column sage -即可删除sage字段。3、创建、删除和修改约束在SQL Server 2000中有5种约束：主键约束、惟一性约束、检查约束、缺省约束、外部键约束，它们的定义也是create table命令的一部分，创建、删除和修改这些约束还可参阅相关完整性约束的内容。在SQL Server 2000中，在出现设计表窗口时，按工具栏上的属性按钮，即能出现图3-3所示的属性窗体，从中能通过“表”、“关系”、“索引/键”、“check 约束”等选项卡来创建、删除和修改完整性约束。图3-3

14、 表设计器修改表字段4、查看表格信息图3-4 添加、编辑表记录（1）查看表格的定义：在企业管理器中，打开指定的服务器中的数据库，用右键单击要查看的表，从弹出的快捷菜单中选择“设计表”选项，则会出现设计表对话框，在该对话框中，可以查看表及各字段的信息。（2）查看表格中的数据：在企业管理器中，在指定的服务器中某数据库中某表上，右键单击该表，从弹出的快捷菜单中选择“打开表”“返回所有行”，即可网格方式查看表格中的数据，并能在表的最后交互式添加记录，如图3-4。（3）查看表格与其他数据库对象的依赖关系：打开数据库中“关系图”，即可直观地查看表格与其他数据库对象的依赖关系。（4）利用系统存储过程sp_h

15、elp查看表的信息，如图3-5图3-5 查看表格信息5、对表查询在企业管理器中，右键单击某表，从弹出的快捷菜单中选择“打开表”“查询”，如图3-6。在出现的交互式查询子窗口中选定表、指定列及查询准则等，即能交互式查询表或多表中的数据，如图3-7所示，不妨自己来实践各种查询操作。图3-6 启动表查询图3-7 交互式查询表6、删除表（1）利用企业管理器删除表在企业管理器中，展开指定的数据库和表格项，用右键单击要删除的表，从快捷菜单中选择“删除”选项，则会出现除去对象对话框，如图3-8，单击“全部删除”按钮，即可真正删除选定的表。图3-8 确定删除表（2）利用drop table语句删除表drop

16、table语句可以删除一个表和表中的数据及其与表有关的所有索引、触发器、约束、许可对象等。drop table语句的语法形式为：DROP TABLE table_name例5 删除表employee的命令为：drop table employee。7、创建、重命名、使用及删除用户定义的数据类型（1）创建用户定义的数据类型使用T-SQL语句创建一个名为nametype、数据长度为8、定长字符型、不允许为空的自定义数据类型。USE JXGLExec sp_addtype nametype,char(8),not null（2）重命名用户定义的数据类型使用系统存储过程sp_rename能重命名用户自

17、定义的数据类型。Exec sp_rename nametype,domain_name（3）使用自定义数据类型一旦创建了用户定义的数据类型后，创建表结构时，能如使用系统标准类型一样使用自定义的类型。如：创建学生表的命令为：CREATE TABLE ST(sno char(5) primary key,sname domain_name)（4）删除用户定义的数据类型使用系统存储过程sp_droptype能删除用户自定义的数据类型。Exec sp_droptype domain_name - 在删除自定义数据类型前，先要删除表ST。注意 正在被表或其他数据库对象使用的用户定义类型不能删除，必须先删

18、除使用者才行。2视图的创建与使用1、创建视图（1）利用企业管理器创建与修改视图在企业管理器中，展开指定的数据库，点按“视图”，按鼠标右键，从弹出的快捷菜单中选择“新建视图”菜单项；或者在工具栏中选择新建图标，就会出现新建视图对话框，如图3-9，在该对话框中，通过选定一个或多个表，指定多个字段，设定连接或限定条件，最后保存并给视图取个名称，即完成了视图的创建，请参阅图3-10，图3-11。在企业管理器中修改视图，只要找到该视图后，按鼠标右键，从弹出的快捷菜单中选择“设计视图”菜单项或双击该视图，均可即时修改，如图3-10。图3-9 设计视图前选定表 图3-10 交互式设计视图 图3-11 保存时

19、为视图取名（2）视图向导创建视图企业管理器“工具”菜单“向导”“数据库”“创建视图向导”能向导方式创建视图，其过程为： 图3-12 选择“创建视图向导” 图3-13 欢迎使用创建视图向导 图3-14 选择视图将引用的数据库 图3-15 选择视图将引用的表或视图 图3-16 选择视图将显示的列 图3-17 定义视图的限制 图3-18 对视图命名 图3-19 正在完成创建视图向导（3）利用create view创建视图。创建一个虚拟表，该表以另一种方式表示一个或多个表中的相关数据。CREATE VIEW 必须是查询批处理中的第一条语句。create view语法： CREATE VIEW . .

20、view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA 例6 创建视图stgrade，要求显示出学生的学号、姓名、课程号与该课程成绩。其命令为：create view stgrade as select student.sno, student.sname, o, sc.gradefrom student inner join sc on student.sno = sc.sno例7 创建视图stbk，要求显示学生的

21、学号、姓名、类别、性别与电话。其命令为：create view stbk as select sno, sname, sclass, ssex,sdhfrom student where sclass=本科 with check option2、视图的使用视图的使用基本同基本表的使用，不同处是有些视图是不可更新的，只能对这些只读视图作查询操作，不能通过它们更新数据。 图3-20 交互式打开视图 图3-21 打开的视图图3-20交互式打开视图后，显示的视图记录如图3-21，通过图3-21打开的视图能直接更新数据，更新的数据将最终更新到视图stbk基于的基本表student中，请你试试。3、删除视

22、图（1）在企业管理器中删除视图 图3-22 快捷菜单删除视图 图3-23 删除视图确认展开student数据库后，单击“视图”在右边视图对象中选中要删除的视图鼠标右键，弹出快捷菜单，如图3-22点击“删除”菜单项在出现的确认删除对话框（如图3-23）中，按“全部删去”按钮。（2）利用drop view语句删除视图drop view语法： DROP VIEW view ,.n 例8 删除视图stbk的命令为：DROP VIEW stbk。3表与视图的导入与导出操作1、启动导入或导出功能图3-24 在某数据库上启动数据导入导出功能法一：导入和导出数据是SQL Server 2000程序组中的一个程

23、序项，为此，开始所有程序Micosoft SQL Server导入和导出数据,能启动导入或导出功能法二：直接在企业管理器中，选择需导入或导出的某数据库，鼠标右键弹出的快捷菜单中选择“导入数据”或“导出数据”菜单即可。如图3-24。2、导出数据选择“导出数据”功能后，启动了导出数据向导过程。首先，看到的是“数据转化服务数据导入/导出向导”首页，如图3-25。单击“下一步”，进入选择数据源步骤，如图3-26。 图3-25 数据转化服务数据导入/导出向导 图3-26 选择数据源3、选择数据源选择数据源窗口有两部分组成，上面部分是数据源选择的组合框，下面是相应选择的数据源的信息区。如图3-26所示，数

24、据源为Microsoft OLE DB为提供程序的SQL Server；数据源的信息区需指定服务器名及身份验证。图3-27所示为选择不同数据源的情况。要注意的是当选择不同数据源时，数据源的信息区会有不同的待填信息内容，可参阅图3-29（尽管为选择目的对话框）。 图3-27 选择数据源 图3-28 选择数据要复制到的目的地4、选择目的图3-37选择数据源后按“下一步”，出现图3-28，选择数据要复制到的目的地。同样可选择不同数据源，并输入目的数据源信息。这里，目的数据源选择ACCESS数据库，如图3-29。ACCESS目的数据源主要要指定ACCESS数据库文件，当按“文件名”文本框右边的文件选择

25、按钮时，出现图3-30所示的选择文件对话框，并指定jxgl2.mdb为数据库文件。 图3-29 选择并指定ACCESS数据库文件 图3-30 选择ACCESS数据库文件5、指定表复制或查询指定好目的数据源后，按“下一步”，指示将从“Microsoft SQL Server”复制数据到“Microsoft Aceess”，如图3-31，再按“下一步”，如图3-32，显示出了数据源的所有用户表和视图。选中要复制的表或视图的左边的复选框，如图3-32。 图3-31 指定表复制或查询的方向 图3-32 选择要复制的表和视图6、保存、调度和查询包再按“下一步”，出现图3-33，再按“下一步”，出现图3-

26、34，表示将要开始复制了。 图3-33 保存、调度和复制包 图3-34 正在完成DTS导入/导出向导7、正在完成DTS导入/导出向导当按“完成”按钮时，从数据源到数据目的地的表与视图的复制开始了，如图3-35。完成复制后将显示图3-36。 图3-35 正在执行复制 图3-36 已成功地完成表的复制说明（1）导入与导出数据的过程是类似的，不同处为数据源与数据目的的指定不同，数据复制的方向不同，导入往往是指从其它数据源复制到本数据库（作为数据目的），导出往往是指从本数据库（作为数据源）复制到其它数据源。（2）导入与导出是相对的，也就是说导入能完成导出功能，导出也能完成导入功能，关键在于指定什么样的

27、数据源与数据目的。在数据源与数据目的均指定非SQL Server 2000数据库时，导入或导出还能实现非SQL Server 2000数据源间的数据复制，如ACCESS数据库间，ACCESS数据库与VFP数据库间等的数据复制。实验内容1、创建数据库及表用你掌握的某种方法，创建订报管理子系统的数据库DingBao，在DingBao数据库中用交互式界面操作方法或CREATE TABLE创建如下三表的表结构（表名及字段名使用括号中给出的英文名），并完成三表所示内容的输入，根据需要可自行设计输入更多的表记录。创建表结构时要求满足：(1)报纸编码表(PAPER)以报纸编号(pno)为主键；(2)顾客编码

28、表(CUSTOMER)以顾客编号(cno)为主键；(3)报纸订阅表（CP）以报子编号(pno)与顾客编号(cno)为主键，订阅份数(num)的缺省值为1。报纸编码表(PAPER) 报纸订阅表（CP）报纸编号(pno)报纸名称(pna)单价(ppr)000001人民日报12.5000002解放军报14.5000003光明日报10.5000004青年报11.5000005扬子晚报18.5顾客编号（cno）报纸编号(pno)订阅份数(num)00010000012000100000240001000005600020000012000200000320002000005200030000032000

29、300000440004000001100040000033000400000520005000003400050000021000500000430005000005500050000014顾客编码表(CUSTOMER)顾客编号(cno)顾客姓名(cna)顾客地址(adr)0001李涛无锡市解放东路123号0002钱金浩无锡市人民西路234号0003邓杰无锡市惠河路270号0004朱海红无锡市中山东路432号0005欧阳阳文无锡市中山东路532号创建一个ACCESS数据库DingBao（DingBao.MDB文件），把你在SQL SERVER中创建的三表导出到ACCESS数据库中。2、创建与使用视图（1）在DingBao数据库中,创建含有顾客编号、顾客名称、报纸编号、报纸名称、订阅份数等信息的视图，视图名设定为C_P_N。（2）修改已创建的视图C_P_N，使其含报纸单价信息。（3）通过视图C_P_N，查询“人民日报”被订阅的情况，能通过