数据库原理及应用实验指导实验3 表与视图的基础操作.docx
《数据库原理及应用实验指导实验3 表与视图的基础操作.docx》由会员分享，可在线阅读，更多相关《数据库原理及应用实验指导实验3 表与视图的基础操作.docx（19页珍藏版）》请在冰点文库上搜索。
数据库原理及应用实验指导实验3表与视图的基础操作
实验3表与视图的基本操作
实验目的
1、掌握数据库表与视图的基础知识。
2、掌握创建、修改、使用、删除表与视图的不同方法。
背景知识
在关系数据库中，每个关系都对应为一张表，表是数据库的最主要对象，是用来存储与操作数据的逻辑结构，使用数据库时，绝大多数时间都是在与表打交道。
表中有两个基本的对象：
字段和记录。
字段也叫属性，也就是表中的列。
字段的值也称为属性值，属性值的取值范围叫做值域。
记录是字段的有序集合。
一般用记录描述一个实体，在二维表格中也就是行。
设计数据库时应该先确定需要什么样的表，各表中都有什么样的数据以及各个表的存取权限等。
在创建和操作表的过程中，需要对表进行细致的设计，在创建表之前必须先确定所有表字段。
创建一张表最有效的方法是将表中所需的信息一次定义完成，包括数据约束和各种附加成分。
也可以先创建一张基础表，向表中添加一些数据并使用一段时间，然后在需要的时候利用修改表的方法再添加各种约束、索引、默认设置、规则以及其他对象。
最好在创建表及其对象时预先完整设计好以下内容。
1．指定表中所包含数据的类型：
表中的每个字段有特殊的数据类型，可以限制插入数据的变化范围。
SQLServer2000还支持用户自定义数据类型，自定义数据类型建立在系统已定义的数据类型基础之上。
选择数据类型时应该遵循以下规则：
（1）如果列的长度可变，使用某个变长数据类型；
（2）对于数值数据类型，数值大小和所需要的精度有助于做出相应的决定；（3）如果存储量超过8000字节，使用Text或Image类型，否则的话可以使用Char或Binary，也可以使用Varchar，因为它比Text或Image具有更强的功能；（4）对于货币数据来说，使用Money数据类型；（5）不要把Float或Real数据类型作为主键，因为这些数据的值是不准确的。
2．哪些列允许空值：
空值或NULL并不等于零、空白或零长度字符串，而是意味着没有输入，常用来表明值未知或不确定。
指定一列不允许空值而确保行中该列永远有数据以保证数据的完整性，如果不允许空值，用户在向表中写数据的时候必须在列中输入一个值，否则该行不被接收入数据库。
3．是否要使用以及何时使用约束、默认设置以及规则，哪些列是主键，哪些是外键。
4．需要的索引类型以及哪里需要索引。
5．设计的数据库应该符合第三范式。
数据库要想符合第三范式，必须首先符合第一、二范式。
6．SQLServer2000中，每个数据库可以有20亿个表，每张表可以有1024个列，每列可以有8060个字节。
实验示例
1．表的创建与使用
1、创建表
SQLServer2000提供了两种方法创建数据库表，第一种方法是利用企业管理器创建表；另一种方法是利用transact-sql语句中的createtable命令创建表。
（1）利用企业管理器创建表
-
图3-1创建表结构对话框
在企业管理器中，展开指定的服务器和数据库，打开想要创建新表的数据库，用右键单击表对象，从弹出的快捷菜单中选择“新建表”选项，或者在工具栏中选择
新建图标，就会出现新建表对话框如图3-1，在该对话框中，可以定义列名称、列类型、长度、精度、小数位数、是否允许为空、缺省值、标识列、标识列的初始值、标识列的增量值和是否有行的标识。
（2）利用createtable命令创建表。
其语法为：
CREATETABLE[database_name.[owner].|owner.]table_name
（{|column_nameAScomputed_column_expression|:
:
=[CONSTRAINTconstraint_name]}|[{PRIMARYKEY|UNIQUE}[,...n]][ON{filegroup|DEFAULT}][TEXTIMAGE_ON{filegroup|DEFAULT}]
:
:
={column_namedata_type}[COLLATE][[DEFAULTconstant_expression]|[IDENTITY[（seed,increment）[NOTFORREPLICATION]]]][ROWGUIDCOL][][...n]
:
:
=[CONSTRAINTconstraint_name]{[NULL|NOTNULL]
|[{PRIMARYKEY|UNIQUE}[CLUSTERED|NONCLUSTERED][WITHFILLFACTOR=fillfactor][ON{filegroup|DEFAULT}]]}
|[[FOREIGNKEY]REFERENCESref_table[（ref_column）][ONDELETE{CASCADE|NOACTION}][ONUPDATE{CASCADE|NOACTION}][NOTFORREPLICATION]]
|CHECK[NOTFORREPLICATION]（logical_expression）}
:
:
=[CONSTRAINTconstraint_name]{
[{PRIMARYKEY|UNIQUE}[CLUSTERED|NONCLUSTERED]{（column[ASC|DESC][,...n]）}[WITHFILLFACTOR=fillfactor][ON{filegroup|DEFAULT}]]
|FOREIGNKEY[（column[,...n]）]REFERENCESref_table[（ref_column[,...n]）][ONDELETE{CASCADE|NOACTION}][ONUPDATE{CASCADE|NOACTION}][NOTFORREPLICATION]
|CHECK[NOTFORREPLICATION]（search_conditions）}
[例1]创建一个雇员信息表的createtable命令如下：
createtableemployee（empidintnotnullprimarykeyclustered,
namevarchar（20）NOTNULL,sexchar
（2）NULL,
birthdaydatetimenull,hire_datedatetimeNOTNULLDEFAULT（getdate（））,
professional_titlevarchar（10）null,
salarymoneynullcheck（salaryisnullorsalary>=450）,
memontextnull）。
[例2]创建一个学生信息表的createtable命令如下：
（注意该命令执行时，需要class班级表与speciality专业表已存在，或者先暂时把两外码参照子句去掉后执行。
）
CREATETABLEstudent（snochar（8）NOTNULLPRIMARYKEY,snamechar（20）NOTNULL,
sclasschar（10）NULLDEFAULT（'本科'）,
ssexchar
（2）NULLDEFAULT（'男'）CHECK（ssex='男'orssex='女'）,
sdhvarchar（14）NULL,scsrqdatetimeNULL,
srxrqdatetimeNULL,saddrvarchar（50）NULL,
smemovarchar（200）NULL,spnochar（4）NULL,csnochar（4）NULL,
CONSTRAINTFK_student_classFOREIGNKEY（csno）REFERENCESclass（csno）,
CONSTRAINTFK_student_specialityFOREIGNKEY（spno）REFERENCESspeciality（spno））
2、增加、删除和修改字段
（1）利用企业管理器操作表字段
利用企业管理器增加、删除和修改字段。
在企业管理器中，打开指定的服务器中要修改表的数据库，用右键单击要进行修改的表，从弹出的快捷菜单中选择“设计表”选项，则会出现设计表对话框如图3-2，在该对话框中，可以利用图形化工具完成增加、删除和修改字段的操作。
图3-2表设计器修改表字段
（2）利用altertable命令修改表。
利用transact-sql语言中的altertable命令也可增加、删除和修改表中字段，其语法略，请读者自己查阅。
[例3]给student表添加sage年龄字段，命令为：
altertablestudentaddsageint--添加sage字段。
[例4]删除student表中的sage年龄字段，命令为：
altertablestudentdropcolumnsage--即可删除sage字段。
3、创建、删除和修改约束
在SQLServer2000中有5种约束：
主键约束、惟一性约束、检查约束、缺省约束、外部键约束，它们的定义也是createtable命令的一部分，创建、删除和修改这些约束还可参阅相关完整性约束的内容。
在SQLServer2000中，在出现设计表窗口时，按工具栏上的属性按钮，即能出现图3-3所示的属性窗体，从中能通过“表”、“关系”、“索引/键”、“check约束”等选项卡来创建、删除和修改完整性约束。
图3-3表设计器修改表字段
4、查看表格信息
图3-4添加、编辑表记录
（1）查看表格的定义：
在企业管理器中，打开指定的服务器中的数据库，用右键单击要查看的表，从弹出的快捷菜单中选择“设计表”选项，则会出现设计表对话框，在该对话框中，可以查看表及各字段的信息。
（2）查看表格中的数据：
在企业管理器中，在指定的服务器中某数据库中某表上，右键单击该表，从弹出的快捷菜单中选择“打开表”→“返回所有行”，即可网格方式查看表格中的数据，并能在表的最后交互式添加记录，如图3-4。
（3）查看表格与其他数据库对象的依赖关系：
打开数据库中“关系图”，即可直观地查看表格与其他数据库对象的依赖关系。
（4）利用系统存储过程sp_help查看表的信息，如图3-5
图3-5查看表格信息
5、对表查询
在企业管理器中，右键单击某表，从弹出的快捷菜单中选择“打开表”→“查询”，如图3-6。
在出现的交互式查询子窗口中选定表、指定列及查询准则等，即能交互式查询表或多表中的数据，如图3-7所示，不妨自己来实践各种查询操作。
图3-6启动表查询
图3-7交互式查询表
6、删除表
（1）利用企业管理器删除表
在企业管理器中，展开指定的数据库和表格项，用右键单击要删除的表，从快捷菜单中选择“删除”选项，则会出现除去对象对话框，如图3-8，单击“全部删除”按钮，即可真正删除选定的表。
图3-8确定删除表
（2）利用droptable语句删除表
droptable语句可以删除一个表和表中的数据及其与表有关的所有索引、触发器、约束、许可对象等。
droptable语句的语法形式为：
DROPTABLEtable_name
[例5]删除表employee的命令为：
droptableemployee。
7、创建、重命名、使用及删除用户定义的数据类型
（1）创建用户定义的数据类型
使用T-SQL语句创建一个名为nametype、数据长度为8、定长字符型、不允许为空的自定义数据类型。
USEJXGL
Execsp_addtypenametype,'char（8）','notnull'
（2）重命名用户定义的数据类型
使用系统存储过程sp_rename能重命名用户自定义的数据类型。
Execsp_renamenametype,domain_name
（3）使用自定义数据类型
一旦创建了用户定义的数据类型后，创建表结构时，能如使用系统标准类型一样使用自定义的类型。
如：
创建学生表的命令为：
CREATETABLEST（snochar（5）primarykey,snamedomain_name）
（4）删除用户定义的数据类型
使用系统存储过程sp_droptype能删除用户自定义的数据类型。
Execsp_droptypedomain_name--在删除自定义数据类型前，先要删除表ST。
[注意]正在被表或其他数据库对象使用的用户定义类型不能删除，必须先删除使用者才行。
2．视图的创建与使用
1、创建视图
（1）利用企业管理器创建与修改视图
在企业管理器中，展开指定的数据库，点按“视图”，按鼠标右键，从弹出的快捷菜单中选择“新建视图”菜单项；或者在工具栏中选择
新建图标，就会出现新建视图对话框，如图3-9，在该对话框中，通过选定一个或多个表，指定多个字段，设定连接或限定条件，最后保存并给视图取个名称，即完成了视图的创建，请参阅图3-10，图3-11。
在企业管理器中修改视图，只要找到该视图后，按鼠标右键，从弹出的快捷菜单中选择“设计视图”菜单项或双击该视图，均可即时修改，如图3-10。
图3-9设计视图前选定表图3-10交互式设计视图
图3-11保存时为视图取名
（2）视图向导创建视图
企业管理器“工具”菜单→“向导”→“数据库”→“创建视图向导”能向导方式创建视图，其过程为：
图3-12选择“创建视图向导”图3-13欢迎使用创建视图向导
图3-14选择视图将引用的数据库图3-15选择视图将引用的表或视图
图3-16选择视图将显示的列图3-17定义视图的限制
图3-18对视图命名图3-19正在完成创建视图向导
（3）利用createview创建视图。
创建一个虚拟表，该表以另一种方式表示一个或多个表中的相关数据。
CREATEVIEW必须是查询批处理中的第一条语句。
createview语法：
CREATEVIEW[.][.]view_name[（column[,...n]）][WITH[,...n]]ASselect_statement[WITHCHECKOPTION]
:
:
={ENCRYPTION|SCHEMABINDING|VIEW_METADATA}
[例6]创建视图stgrade，要求显示出学生的学号、姓名、课程号与该课程成绩。
其命令为：
createviewstgradeas
selectstudent.sno,student.sname,o,sc.grade
fromstudentinnerjoinsconstudent.sno=sc.sno
[例7]创建视图stbk，要求显示学生的学号、姓名、类别、性别与电话。
其命令为：
createviewstbkas
selectsno,sname,sclass,ssex,sdh
fromstudent
wheresclass='本科'withcheckoption
2、视图的使用
视图的使用基本同基本表的使用，不同处是有些视图是不可更新的，只能对这些只读视图作查询操作，不能通过它们更新数据。
图3-20交互式打开视图图3-21打开的视图
图3-20交互式打开视图后，显示的视图记录如图3-21，通过图3-21打开的视图能直接更新数据，更新的数据将最终更新到视图stbk基于的基本表student中，请你试试。
3、删除视图
（1）在企业管理器中删除视图
图3-22快捷菜单删除视图图3-23删除视图确认
展开student数据库后，单击“视图”→在右边视图对象中选中要删除的视图→鼠标右键，弹出快捷菜单，如图3-22→点击“删除”菜单项→在出现的确认删除对话框（如图3-23）中，按“全部删去”按钮。
（2）利用dropview语句删除视图
dropview语法：
DROPVIEW{view}[,...n]
[例8]删除视图stbk的命令为：
DROPVIEWstbk。
3．表与视图的导入与导出操作
1、启动导入或导出功能
图3-24在某数据库上启动数据导入导出功能
法一：
导入和导出数据是SQLServer2000程序组中的一个程序项，为此，开始→所有程序→MicosoftSQLServer→导入和导出数据,能启动导入或导出功能
法二：
直接在企业管理器中，选择需导入或导出的某数据库，鼠标右键弹出的快捷菜单中选择“导入数据”或“导出数据”菜单即可。
如图3-24。
2、导出数据
选择“导出数据”功能后，启动了导出数据向导过程。
首先，看到的是“数据转化服务数据导入/导出向导”首页，如图3-25。
单击“下一步”，进入选择数据源步骤，如图3-26。
图3-25数据转化服务数据导入/导出向导图3-26选择数据源
3、选择数据源
选择数据源窗口有两部分组成，上面部分是数据源选择的组合框，下面是相应选择的数据源的信息区。
如图3-26所示，数据源为MicrosoftOLEDB为提供程序的SQLServer；数据源的信息区需指定服务器名及身份验证。
图3-27所示为选择不同数据源的情况。
要注意的是当选择不同数据源时，数据源的信息区会有不同的待填信息内容，可参阅图3-29（尽管为选择目的对话框）。
图3-27选择数据源图3-28选择数据要复制到的目的地
4、选择目的
图3-37选择数据源后按“下一步”，出现图3-28，选择数据要复制到的目的地。
同样可选择不同数据源，并输入目的数据源信息。
这里，目的数据源选择ACCESS数据库，如图3-29。
ACCESS目的数据源主要要指定ACCESS数据库文件，当按“文件名”文本框右边的文件选择按钮时，出现图3-30所示的选择文件对话框，并指定jxgl2.mdb为数据库文件。
图3-29选择并指定ACCESS数据库文件图3-30选择ACCESS数据库文件
5、指定表复制或查询
指定好目的数据源后，按“下一步”，指示将从“MicrosoftSQLServer”复制数据到“MicrosoftAceess”，如图3-31，再按“下一步”，如图3-32，显示出了数据源的所有用户表和视图。
选中要复制的表或视图的左边的复选框，如图3-32。
图3-31指定表复制或查询的方向图3-32选择要复制的表和视图
6、保存、调度和查询包
再按“下一步”，出现图3-33，再按“下一步”，出现图3-34，表示将要开始复制了。
图3-33保存、调度和复制包图3-34正在完成DTS导入/导出向导
7、正在完成DTS导入/导出向导
当按“完成”按钮时，从数据源到数据目的地的表与视图的复制开始了，如图3-35。
完成复制后将显示图3-36。
图3-35正在执行复制图3-36已成功地完成表的复制
[说明]
（1）导入与导出数据的过程是类似的，不同处为数据源与数据目的的指定不同，数据复制的方向不同，导入往往是指从其它数据源复制到本数据库（作为数据目的），导出往往是指从本数据库（作为数据源）复制到其它数据源。
（2）导入与导出是相对的，也就是说导入能完成导出功能，导出也能完成导入功能，关键在于指定什么样的数据源与数据目的。
在数据源与数据目的均指定非SQLServer2000数据库时，导入或导出还能实现非SQLServer2000数据源间的数据复制，如ACCESS数据库间，ACCESS数据库与VFP数据库间等的数据复制。
实验内容
1、创建数据库及表
用你掌握的某种方法，创建订报管理子系统的数据库DingBao，在DingBao数据库中用交互式界面操作方法或CREATETABLE创建如下三表的表结构（表名及字段名使用括号中给出的英文名），并完成三表所示内容的输入，根据需要可自行设计输入更多的表记录。
创建表结构时要求满足：
（1）报纸编码表（PAPER）以报纸编号（pno）为主键；
（2）顾客编码表（CUSTOMER）以顾客编号（cno）为主键；（3）报纸订阅表（CP）以报子编号（pno）与顾客编号（cno）为主键，订阅份数（num）的缺省值为1。
报纸编码表（PAPER）报纸订阅表（CP）
报纸编号（pno）
报纸名称（pna）
单价（ppr）
000001
人民日报
12.5
000002
解放军报
14.5
000003
光明日报
10.5
000004
青年报
11.5
000005
扬子晚报
18.5
顾客编号（cno）
报纸编号（pno）
订阅份数（num）
0001
000001
2
0001
000002
4
0001
000005
6
0002
000001
2
0002
000003
2
0002
000005
2
0003
000003
2
0003
000004
4
0004
000001
1
0004
000003
3
0004
000005
2
0005
000003
4
0005
000002
1
0005
000004
3
0005
000005
5
0005
000001
4
顾客编码表（CUSTOMER）
顾客编号（cno）
顾客姓名（cna）
顾客地址（adr）
0001
李涛
无锡市解放东路123号
0002
钱金浩
无锡市人民西路234号
0003
邓杰
无锡市惠河路270号
0004
朱海红
无锡市中山东路432号
0005
欧阳阳文
无锡市中山东路532号
创建一个ACCESS数据库DingBao（DingBao.MDB文件），把你在SQLSERVER中创建的三表导出到ACCESS数据库中。
2、创建与使用视图
（1）在DingBao数据库中,创建含有顾客编号、顾客名称、报纸编号、报纸名称、订阅份数等信息的视图，视图名设定为C_P_N。
（2）修改已创建的视图C_P_N，使其含报纸单价信息。
（3）通过视图C_P_N，查询“人民日报”被订阅的情况，能通过
升级会员 