数据库原理及应用实验指导

实验3 表与视图的基本操作

实验3 表与视图的基本操作

实验示例

1．表的创建与使用 1、创建表

SQL Server 2000提供了两种方法创建数据库表，第一种方法是利用企业管理器创建表；另一种方法是利用transact-sql语句中的create table命令创建表。

（1）利用企业管理器创建表

-图3-1 创建表结构对话框

1

数据库原理与应用实验指导

在企业管理器中，展开指定的服务器和数据库，打开想要创建新表的数据库，用右键单击表对象，从弹出的快捷菜单中选择“新建表”选项，或者在工具栏中选择新建图标，就会出现新建表对话框如图3-1，在该对话框中，可以定义列名称、列类型、长度、精度、小数位数、是否允许为空、缺省值、标识列、标识列的初始值、标识列的增量值和是否有行的标识。

（2）利用create table命令创建表。其语法为：

CREATE TABLE [ database_name.[ owner ] .| owner.] table_name

( { < column_definition > | column_name AS computed_column_expression | < table_constraint > ::= [ CONSTRAINT constraint_name ] } | [ { PRIMARY KEY | UNIQUE } [ ,...n ] ] [ ON { filegroup | DEFAULT } ] [ TEXTIMAGE_ON { filegroup | DEFAULT } ]

< column_definition > ::= { column_name data_type } [ COLLATE < collation_name > ] [ [ DEFAULT constant_expression ] | [ IDENTITY [ ( seed , increment ) [ NOT FOR REPLICATION ] ] ] ] [ ROWGUIDCOL] [ < column_constraint > ] [ ...n ]

< column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ { PRIMARY KEY | UNIQUE } [ 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 ) } < table_constraint > ::= [ CONSTRAINT constraint_name ] {

[ { PRIMARY KEY | 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_conditions )} [例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 salary>=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('本科'),

2

实验3 表与视图的基本操作

ssex char(2) NULL DEFAULT ('男') CHECK (ssex = '男' or ssex = '女'), sdh varchar(14) NULL ,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）利用企业管理器操作表字段

利用企业管理器增加、删除和修改字段 。在企业管理器中，打开指定的服务器中要修改表的数据库，用右键单击要进行修改的表，从弹出的快捷菜单中选择“设计表”选项，则会出现设计表对话框如图3-2，在该对话框中，可以利用图形化工具完成增加、删除和修改字段的操作。

图3-2 表设计器修改表字段

（2）利用alter table命令修改表。

利用transact-sql语言中的alter table命令也可增加、删除和修改表中字段，其语法略，请读者自己查阅。

[例3] 给student表添加sage年龄字段，命令为：

alter table student add sage int --添加sage字段。 [例4] 删除student 表中的sage年龄字段，命令为：

alter table student drop column sage --即可删除sage字段。

3

数据库原理与应用实验指导

3、创建、删除和修改约束

在SQL Server 2000中有5种约束：主键约束、惟一性约束、检查约束、缺省约束、外部键约束，它们的定义也是create table命令的一部分，创建、删除和修改这些约束还可参阅相关完整性约束的内容。

在SQL Server 2000中，在出现设计表窗口时，按工具栏上的属性按钮，即能出现图3-3所示的属性窗体，从中能通过“表”、“关系”、“索引/键”、“check 约束”等选项卡来创建、删除和修改完整性约束。

图3-3 表设计器修改表字段

4、查看表格信息

图3-4 添加、编辑表记录

（1）查看表格的定义：在企业管理器中，打开指定的服务器中的数据库，用右键单击要查看的表，从弹出的快捷菜单中选择“设计表”选项，则会出现设计表对话框，在该对话框中，可以查看表及各字段的信息。

4

实验3 表与视图的基本操作

（2）查看表格中的数据：在企业管理器中，在指定的服务器中某数据库中某表上，右键单击该表，从弹出的快捷菜单中选择“打开表”→“返回所有行”，即可网格方式查看表格中的数据，并能在表的最后交互式添加记录，如图3-4。

（3）查看表格与其他数据库对象的依赖关系：打开数据库中“关系图”，即可直观地查看表格与其他数据库对象的依赖关系。

（4）利用系统存储过程sp_help查看表的信息，如图3-5

图3-5 查看表格信息

5、对表查询

在企业管理器中，右键单击某表，从弹出的快捷菜单中选择“打开表”→“查询”，如图3-6。在出现的交互式查询子窗口中选定表、指定列及查询准则等，即能交互式查询表或多表中的数据，如图3-7所示，不妨自己来实践各种查询操作。

图3-6 启动表查询

5

数据库原理与应用实验指导

图3-7 交互式查询表

6、删除表

（1）利用企业管理器删除表

在企业管理器中，展开指定的数据库和表格项，用右键单击要删除的表，从快捷菜单中选择“删除”选项，则会出现除去对象对话框，如图3-8，单击“全部删除”按钮，即可真正删除选定的表。

图3-8 确定删除表

（2）利用drop table语句删除表

drop table语句可以删除一个表和表中的数据及其与表有关的所有索引、触发器、约束、

6

实验3 表与视图的基本操作

许可对象等。drop table语句的语法形式为：DROP TABLE table_name

[例5] 删除表employee的命令为：drop table employee。

7、创建、重命名、使用及删除用户定义的数据类型

（1）创建用户定义的数据类型

使用T-SQL语句创建一个名为nametype、数据长度为8、定长字符型、不允许为空的自定义数据类型。

USE JXGL

Exec sp_addtype nametype,'char(8)','not null' （2）重命名用户定义的数据类型

使用系统存储过程sp_rename能重命名用户自定义的数据类型。 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。 [注意] 正在被表或其他数据库对象使用的用户定义类型不能删除，必须先删除使用者才行。

2．视图的创建与使用 1、创建视图

（1）利用企业管理器创建与修改视图

在企业管理器中，展开指定的数据库，点按“视图”，按鼠标右键，从弹出的快捷菜单中选择“新建视图”菜单项；或者在工具栏中选择新建图标，就会出现新建视图对话框，如图3-9，在该对话框中，通过选定一个或多个表，指定多个字段，设定连接或限定条件，最后保存并给视图取个名称，即完成了视图的创建，请参阅图3-10，图3-11。

在企业管理器中修改视图，只要找到该视图后，按鼠标右键，从弹出的快捷菜单中选择“设计视图”菜单项或双击该视图，均可即时修改，如图3-10。

7

数据库原理与应用实验指导

图3-9 设计视图前选定表 图3-10 交互式设计视图

图3-11 保存时为视图取名

（2）视图向导创建视图

企业管理器“工具”菜单→“向导”→“数据库”→“创建视图向导”能向导方式创建视图，其过程为：

图3-12 选择“创建视图向导” 图3-13 欢迎使用创建视图向导

8

实验3 表与视图的基本操作

图3-14 选择视图将引用的数据库 图3-15 选择视图将引用的表或视图

图3-16 选择视图将显示的列 图3-17 定义视图的限制

图3-18 对视图命名 图3-19 正在完成创建视图向导

（3）利用create view创建视图。 创建一个虚拟表，该表以另一种方式表示一个或多个表中的相关数据。CREATE VIEW

9

数据库原理与应用实验指导

必须是查询批处理中的第一条语句。create view语法：

CREATE VIEW [ < database_name > .] [ < owner > .] view_name [ ( column [ ,...n ] ) ] [ WITH < view_attribute > [ ,...n ] ] AS select_statement [ WITH CHECK OPTION ]

< view_attribute > ::= { ENCRYPTION | SCHEMABINDING | VIEW_METADATA }

[例6] 创建视图stgrade，要求显示出学生的学号、姓名、课程号与该课程成绩。其命令为：create view stgrade as

select student.sno, student.sname, sc.cno, sc.grade from student inner join sc on student.sno = sc.sno

[例7] 创建视图stbk，要求显示学生的学号、姓名、类别、性别与电话。其命令为： create view stbk as

select sno, sname, sclass, ssex,sdh from student

where sclass='本科' with check option

2、视图的使用

视图的使用基本同基本表的使用，不同处是有些视图是不可更新的，只能对这些只读视图作查询操作，不能通过它们更新数据。

图3-20 交互式打开视图 图3-21 打开的视图

图3-20交互式打开视图后，显示的视图记录如图3-21，通过图3-21打开的视图能直接更新数据，更新的数据将最终更新到视图stbk基于的基本表student中，请你试试。

3、删除视图

（1）在企业管理器中删除视图

10

实验3 表与视图的基本操作

图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 在某数据库上启动数据导入导出功能

11

数据库原理与应用实验指导

法一：导入和导出数据是SQL Server 2000程序组中的一个程序项，为此，开始→所有程序→Micosoft SQL Server→导入和导出数据,能启动导入或导出功能

法二：直接在企业管理器中，选择需导入或导出的某数据库，鼠标右键弹出的快捷菜单中选择“导入数据”或“导出数据”菜单即可。如图3-24。

2、导出数据

选择“导出数据”功能后，启动了导出数据向导过程。首先，看到的是“数据转化服务数据导入/导出向导”首页，如图3-25。单击“下一步”，进入选择数据源步骤，如图3-26。

图3-25 数据转化服务数据导入/导出向导 图3-26 选择数据源

3、选择数据源

选择数据源窗口有两部分组成，上面部分是数据源选择的组合框，下面是相应选择的数据源的信息区。如图3-26所示，数据源为Microsoft OLE DB为提供程序的SQL Server；数据源的信息区需指定服务器名及身份验证。图3-27所示为选择不同数据源的情况。要注意的是当选择不同数据源时，数据源的信息区会有不同的待填信息内容，可参阅图3-29（尽管为选择目的对话框）。

图3-27 选择数据源 图3-28 选择数据要复制到的目的地

4、选择目的

12

实验3 表与视图的基本操作

图3-37选择数据源后按“下一步”，出现图3-28，选择数据要复制到的目的地。同样可选择不同数据源，并输入目的数据源信息。这里，目的数据源选择ACCESS数据库，如图3-29。ACCESS目的数据源主要要指定ACCESS数据库文件，当按“文件名”文本框右边的文件选择按钮时，出现图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-34，表示将要开始复制了。

13

数据库原理与应用实验指导

图3-33 保存、调度和复制包 图3-34 正在完成DTS导入/导出向导

7、正在完成DTS导入/导出向导

当按“完成”按钮时，从数据源到数据目的地的表与视图的复制开始了，如图3-35。完成复制后将显示图3-36。

图3-35 正在执行复制 图3-36 已成功地完成表的复制

[说明]（1）导入与导出数据的过程是类似的，不同处为数据源与数据目的的指定不同，数据复制的方向不同，导入往往是指从其它数据源复制到本数据库（作为数据目的），导出往往是指从本数据库（作为数据源）复制到其它数据源。（2）导入与导出是相对的，也就是说导入能完成导出功能，导出也能完成导入功能，关键在于指定什么样的数据源与数据目的。在数据源与数据目的均指定非SQL Server 2000数据库时，导入或导出还能实现非SQL Server 2000数据源间的数据复制，如ACCESS数据库间，ACCESS数据库与VFP数据库间等的数据复制。

实验内容

1、创建数据库及表

用你掌握的某种方法，创建订报管理子系统的数据库DingBao，在DingBao数据库中

14

实验3 表与视图的基本操作

用交互式界面操作方法或CREATE TABLE创建如下三表的表结构（表名及字段名使用括号中给出的英文名），并完成三表所示内容的输入，根据需要可自行设计输入更多的表记录。

创建表结构时要求满足：(1)报纸编码表(PAPER)以报纸编号(pno)为主键；(2)顾客编码表(CUSTOMER)以顾客编号(cno)为主键；(3)报纸订阅表（CP）以报子编号(pno)与顾客编号(cno)为主键，订阅份数(num)的缺省值为1。

报纸编码表(PAPER) 报纸订阅表（CP）

报纸编号(pno) 000001 000002 000003 000004 000005 报纸名称(pna) 人民日报 解放军报 光明日报 青年报 扬子晚报 12.5 14.5 10.5 11.5 18.5 单价(ppr)

顾客编号（cno） 0001 0001 0001 0002 0002 0002 0003 报纸编号(pno) 000001 000002 000005 000001 000003 000005 000003 000004 000001 000003 000005 000003 000002 000004 000005 000001 订阅份数(num) 2 4 6 2 2 2 2 4 1 3 2 4 1 3 5 4

顾客编码表(CUSTOMER)

顾客编号(cno) 0001 0002 0003 0004 0005 顾客姓名(cna) 李涛 钱金浩 邓杰 朱海红 欧阳阳文 无锡市解放东路123号 无锡市人民西路234号 无锡市惠河路270号 无锡市中山东路432号 无锡市中山东路532号 顾客地址(adr) 0003 0004 0004 0004 0005 0005 0005 0005 0005

创建一个ACCESS数据库DingBao（DingBao.MDB文件），把你在SQL SERVER中创建的三表导出到ACCESS数据库中。

2、创建与使用视图

（1）在DingBao数据库中,创建含有顾客编号、顾客名称、报纸编号、报纸名称、订阅份数等信息的视图，视图名设定为C_P_N。

（2）修改已创建的视图C_P_N，使其含报纸单价信息。

（3）通过视图C_P_N，查询“人民日报”被订阅的情况，能通过视图C_P_N实现对数据的更新操作吗？请尝试各种更新操作，例如修改某人订阅某报的份数，修改某报的名称等。

（4）删除视图C_P_N

15

本文来源：https://www.bwwdw.com/article/bnjg.html