数据库表和视图的基本操作.docx
《数据库表和视图的基本操作.docx》由会员分享，可在线阅读，更多相关《数据库表和视图的基本操作.docx（28页珍藏版）》请在冰豆网上搜索。
数据库表和视图的基本操作
实验一数据库表和视图的基本操作
一、实验大纲
实验目的：
1.掌握数据库表和视图的概念
2.熟练掌握创建、修改、删除表的方法
3.熟练掌握创建、修改、删除视图的方法
4.掌握表和视图的区别
5.熟练掌握单表和视图的各种查询操作
6.掌握空值的概念
7.了解别名的概念
8.掌握不精确查询的操作方法
9.熟练掌握数据插入、修改、删除的概念
10.熟练掌握INSERT、UPDATE、DELETE命令的实现方式
11.掌握将子查询嵌套在INSERT、UPDATE、DELETE中的操作方法
12.掌握分别用T-SQL语言和企业管理器两种方法进行以上各种操作
实验要求：
1．创建表
表1结构为Employee（eno（职工编号），ename（职工姓名），esex（性别）,address（家庭住址）,sdept（所在部门），eyear（工龄），title（职务））
表2结构为Dept（dno（部门编号），dept（部门名称），header（部门领导））
表3结构为Salary（eno（职工编号），ename（职工姓名），dept（所在部门），sal（基本工资），btsal（基本津贴），sdbt（水电补贴），shbt（生活补贴），yjjt（业绩津贴），bybt（边远补贴），kfz（扣房租），kgjj（扣公积金）,sybx（失业保险）,ylbx（医疗保险））
表4结构为Header（hno（领导编号），hname（领导姓名），dept（所在部门），title（职务））
2．修改表结构
向Employee表中加入sage（年龄）字段，tel（联系电话），将字段名sdept改为dept。
将表Salary中的eno数据类型由原来的数值型改为字符型
将表Salary中字段sdbt删除。
3．删除表Header。
4．分别向表Employee,表Dept和表Salary中加入数据，至少10条记录。
5．创建视图
建立只包含职工编号，职工姓名，工龄和职务的视图E_view1
建立工龄在10年以上且职务为总经理的员工的视图E_view2。
建立职务为高级工程师且应发工资大于5000的职工的视图E_view3。
建立销售部门职务为总经理或副总经理的应发工资的视图E_view4
6．查看视图定义
7．修改视图
将视图E_view1中的职务名为总经理改为部门经理。
将视图E_view2中应发工资减去180元。
将视图E_view3中应发工资大于10000元且职务为副总经理的职务改为总经理。
8．删除视图
将已建立的视图E_view1和E_view2删除掉。
9．向表中插入数据，如表1，2，3所示
10．数据更新
（1）向职工表Employee插入职工编号，职工姓名，性别，家庭住址，所在部门，工龄，职务分
别为，张扬，男，七里河北街254号，人事科，5，职员。
（2）向工资表Salary中插入职工编号为，所在部门为人事科，基本工资为412，基本津贴为218，水电补贴为10，生活补贴为400，边远补贴为12.5。
（3）将所有职工的基本工资增加80。
（4）将职工编号为的职务改为职员。
（5）将职工赵前进的基本工资减少80。
（6）将职工表进行复制。
（7）从表Header中删除赵前进的所有数据。
（8）删除采购科所有职工的信息。
11．简单查询
查找所有职工编号，姓名
查找所有职工的详细信息
该单位所有的部门数
12．单表条件查询
查找所有的女职工信息
查找销售科的所有男职工信息
查找职工编号为的职工的基本工资和实发工资
查找基本津贴为空值的职工信息
查找李姓职工的基本信息
查找不姓王的所有职工信息
13．利用表别名实现以上操作
实验环境：
SQLServer2000
二、实验指导
说明：
本示例用例的表结构如下所示：
Student（sno,sname,sex，sage，dept,oldgrade,advisor）
Teacher（tno,tname,dept,salary,title）
Course（cno,cname,descry,dept,credit）
SC（sno,cno,grade）
以上数据库表的含义为：
Student—学生：
sno（学号），sname（学生姓名），sex（性别），sage（年龄），dept（系），oldgrade（高考成绩），advisor（导师）
Teacher—教师：
tno（教师编号），tname（教师姓名），dept（所在系），salary（工资），title（职称）
Course—课程：
cno（课程号），cname（课程名），descry（课程说明），dept（开课系），credits（学分）
SC—成绩：
sno（学号），cno（课程号），grade（成绩）
1.利用企业管理器实现表和视图的基本操作
（1）创建表
鼠标右键单击所要建表的数据库，“新建”→“表…”弹出如图1所示界面，在别名里输入字段名，然后选择数据类型，确定数据长度，是否允许为空。
也可以填写有关列的部分
属性。
图1
（2）修改表
在相应的数据库中选定要修改的名，右键单击，或选择菜单“操作”，选择“设计表”，弹出如图2所示界面，可以对表的列进行修改，增加或删除列等操作。
图2图3
（3）删除表
选择要删除的表，右键单击（或选择菜单“操作”），在弹出菜单中选择删除，弹出如图3所示界面。
选择按钮“全部除去”。
（4）创建视图
在相应的数据库下，选择视图，在“操作”菜单下选择“新建视图”，弹出如图4所示界面，在上方工作区，右键单击，在弹出菜单中选择“添加表”，如图5。
图4图5
在弹出的“添加表”对话框中，选择所要建立视图的表或视图，如图6所示。
选择添加按钮。
若要添加多个表重复刚才过程，直至添加完成。
“关闭”添加表对话框，返回添加视图界面，可以看出表已经添加成功，在相应表中选择要建立视图的字段，如图7。
系统会自动生成SQL代码，下面窗口出现视图运行结果。
图6图7
（5）查看或修改视图
右键单击所要查看或修改的视图，选择“设计视图”，弹出如图3-7所示界面，可以对视图进行查看或修改。
（6）删除视图
右键单击所要删除的视图，在弹出菜单中选择“删除”按钮，弹出如图3-3所示对话框。
选择“全部除去”按钮。
（7）数据插入
打开企业管理器，右键单击要插入数据的表或选中要插入数据的表，选择菜单“操作”→“打开表”→“返回所有行”，如图8所示，则会弹出该表的数据对话框，即可以插入数据，如图9所示。
图8图9
或者：
单击图3-9上的按钮
，则显示弹出式菜单，如图10所示，选择“从中插入”，则弹出对话框，选择插入的数据源表，如图11所示。
图10图11
选择表Student，则弹出如图3-12所示界面，下面列表区显示表Student的内容，中间代码区显示代码，将表Student中的内容插入到表st中。
单击“运行”按钮，则弹出如图3-13所示，提示此次插入数据的行数，表明插入成功。
图12图13
（8）数据更新
在如图10所示菜单中，选择“更新”，弹出如图14所示，在上面网格区中的“列”中选择所要修改的列，如sage，在“表”中选择Student表，在“新值”中写入新设定值，如18。
同理可以设置多个列。
如果是有条件修改，则在“准则”中写入条件。
如图是将所在系为空的学生的年龄修改为18，系修改为“材料学院”。
代码区会自动显示刚才操作的SQL语句。
运行后，提示如图15所示，表示此次修改成功。
图14图15
（9）数据删除
在如图10的菜单中选择“删除”，则弹出如图16所示的窗口，操作类似更新。
如果是无条件删除，则上面网格区不做任何操作，如果是条件删除，则在网格区中进行条件设定，如在“列”中选择sno，在“表”中选择Student，在“准则”中写入该列满足的条件，如0000。
上述设置表示在表Student中删除学号为0000的学生记录。
代码区自动显示此次操作的代码形式。
设置完成后，单击“运行”按钮，则弹出如图17所示对话框，说明此次删除数据成功。
图16图17
（10）单表查询
右键单击要查询的表或选择要进行查询的表，如图18所示，选择菜单“操作”→“打开表”→“查询”，弹出查询窗口如图19所示。
选择所要显示的列名，代码区会自动生成SQL语句，然后点击按钮
，运行查询。
查询结果显示在显示区中。
图18图19
也可利用新建视图进行查询，如下例所示：
[例1]查询所有学生信息，如图20所示。
注：
图中代码区的代码为自动生成，读者也可以自行在代码区进行修改。
图20图21
[例2]查询所有学生的出生年份。
提示：
表中有学生年龄字段，可以用当前年份减去年龄字段值得到出生年份。
如本例用2007-sage来进行计算，如图21所示。
[例3]查询所有女生信息。
提示：
在表中选择相应要显示的字段。
然后在“准则”栏中写入条件，如在列sex对应的“准则”中写入=“女”，如图22所示。
图22
[例4]查询所有年龄大于或等于20岁女生信息。
如图23所示。
提示：
多条件查询，只需在相应的“准则”栏中写入多个条件即可。
如本例中在列sex对应的“准则”栏中写入=“女”，同时在列sage对应的“准则”栏中写入>=20即可。
图23
2.利用T-SQL语言实现基本表和视图操作
（1）创建表
语法结构：
CREATETABLE
[database_name.[owner].|owner.]table_name
（{
|column_nameAScomputed_column_expression
|:
:
=[CONSTRAINTconstraint_name]}
|[{PRIMARYKEY|UNIQUE}[,...n]
）
主要参数说明：
database_name：
是要在其中创建表的数据库名称。
database_name必须是现有数据库的名称。
如果不指定数据库，database_name默认为当前数据库。
当前连接的登录必须在database_name所指定的数据库中有关联的现有用户ID，而该用户ID必须具有创建表的权限。
owner：
是新表所有者的用户ID名，owner必须是database_name所指定的数据库中的现有用户ID，owner默认为与database_name所指定的数据库中的当前连接相关联的用户ID。
如果CREATETABLE语句由sysadmin固定服务器角色成员或database_name所指定的数据库中的db_dbowner或db_ddladmin固定数据库角色成员执行，则owner可以指定与当前连接的登录相关联的用户ID以外的其它用户ID。
如果与执行CREATETABLE语句的登录相关联的用户ID仅具有创建表的权限，则owner必须指定与当前登录相关联的用户ID。
sysadmin固定服务器角色成员或别名为dbo用户的登录与用户IDdbo相关联；因此，由这些用户创建的表的默认所有者为dbo。
不是由上述两种角色的登录创建的表所有者默认为与该登录相关联的用户ID。
table_name：
是新表的名称。
表名必须符合标识符规则。
数据库中的owner.table_name组合必须唯一。
table_name最多可包含128个字符，但本地临时表的表名（名称前有一个编号符#）最多只能包含116个字符。
column_name：
是表中的列名。
列名必须符合标识符规则，并且在表内唯一。
以timestamp数据类型创建的列可以省略column_name。
如果不指定column_name，timestamp列的名称默认为timestamp。
[例5]创建表Course和表Teacher。
其中Course表中课程编号为主键，课程名和开课院系不能为空，Teacher表中教师编号为主键，姓名、所在系不能为空。
CREATETABLECourse
（cnochar（10）notnullprimarykey,
cnamechar（10）notnull,
descrychar（10）,
deptchar（10）notnull,
creditdecimal（3,1）
）；
CREATETABLETeacher
（tnochar（10）notnullprimarykey,
tnamechar（10）notnull,
deptchar（10）notnull,
salarydecimal（6.2）
titlechar（10）
）；
[例6]创建表Student，设学号为主键，姓名不能为空，性别为“男”或“女”。
CREATETABLEStudent
（snochar（10）notnullprimarykey,
snamechar（10）notnull,
sexchar
（2）CHECKin（“男”，“女”）,
sageint,
deptchar（10）,
oldgradedecimal（3.1）
advisorchar（8）
）；
[例7]建立部门DEPT表，要求部门名称Dept列取值唯一，部门编号Dno列为主码。
CREATETABLEDEPT
（Dnochar（8）,
Deptchar（9）unique,
Locationchar（10）,
Headerchar（8）,
Primarykey（Dno）
）；
（2）修改表
修改表语法结构：
ALTERTABLEtable_name
{[ALTERCOLUMNcolumn_name
{new_data_type[（precision[,scale]）]
[COLLATE]
[NULL|NOTNULL]
|{ADD|DROP}ROWGUIDCOL}
]
|ADD
{[]
|column_nameAScomputed_column_expression
}[,...n]
|[WITHCHECK|WITHNOCHECK]ADD
{}[,...n]
|DROP
{[CONSTRAINT]constraint_name
|COLUMNcolumn}[,...n]
|{CHECK|NOCHECK}CONSTRAINT
{ALL|constraint_name[,...n]}
|{ENABLE|DISABLE}TRIGGER
{ALL|trigger_name[,...n]}
}
参数说明详见SQLServer联机丛书。
[例8]添加新列。
CREATETABLEdoc_exa（column_aINT）
GO
ALTERTABLEdoc_exaADDcolumn_bVARCHAR（20）NULL
GO
[例9]删除列。
CREATETABLEdoc_exb（column_aINT,column_bVARCHAR（20）NULL）
GO
ALTERTABLEdoc_exbDROPCOLUMNcolumn_b
GO
[例10]修改字段定义。
ALTERTABLEstudent；
ALTERCOLUMNdeptvarchar（25）NULL；
[例11]修改表，向表中添加约束。
ALTERTABLEDEPT
ADDCONSTRAINTPK_Dno
PRIMARYKEY（Dno）；
[例12]向表中添加具有UNIQUE约束的新列。
CREATETABLEdoc_exc（column_aINT）
GO
ALTERTABLEdoc_excADDcolumn_bVARCHAR（20）NULL
CONSTRAINTexb_uniqueUNIQUE
GO
（3）删除表
语法结构：
DROPTABLEtable_name[,...n]
参数说明：
table_name：
指定要删除的表名称。
[例13]删除单表。
DROPTABLEdoc_exc；
[例14]删除多表。
DROPTABLEdoc_exa,doc_exb；
（4）创建视图
语法格式：
CREATEVIEW[.][.]view_name[（column[,...n]）]
[WITH[,...n]]
AS
select_statement
[WITHCHECKOPTION]
:
:
=
{ENCRYPTION|SCHEMABINDING|VIEW_METADATA}
主要参数说明：
view_name：
视图的名称。
视图名称必须符合标识符规则。
可以选择是否指定视图所有者名称。
column：
是视图中的列名。
只有在下列情况下，才必须命名CREATEVIEW中的列；当列是从算术表达式、函数或常量派生的，两个或更多的列可能会具有相同的名称（通常是因为联接），视图中的某列被赋予了不同于派生来源列的名称。
还可以在SELECT语句中指派列名。
如果未指定column，则视图列将获得与SELECT语句中的列相同的名称。
说明：
在视图的各列中，列名的权限在CREATEVIEW或ALTERVIEW语句间均适用，与基础数据源无关。
例如，如果在CREATEVIEW语句中授予了title_id列上的权限，则ALTERVIEW语句可以将title_id列改名（例如改为qty），但权限仍与使用title_id的视图上的权限相同。
n：
表示可以指定多列的占位符。
AS：
视图要执行的操作。
select_statement：
是定义视图的SELECT语句。
该语句可以使用多个表或其它视图。
若要从创建视图的SELECT子句所引用的对象中选择，必须具有适当的权限。
视图不必是具体某个表的行和列的简单子集。
可以用具有任意复杂性的SELECT子句，使用多个表或其它视图来创建视图。
在索引视图定义中，SELECT语句必须是单个表的语句或带有可选聚合的多表JOIN。
对于视图定义中的SELECT子句有几个限制。
CREATEVIEW语句不能：
●包含COMPUTE或COMPUTEBY子句。
●包含ORDERBY子句，除非在SELECT语句的选择列表中也有一个TOP子句。
●包含INTO关键字。
●引用临时表或表变量。
WITHCHECKOPTION：
强制视图上执行的所有数据修改语句都必须符合由select_statement设置的准则。
通过视图修改行时，WITHCHECKOPTION可确保提交修改后，仍可通过视图看到修改的数据。
WITHENCRYPTION：
表示SQLServer加密包含CREATEVIEW语句文本的系统表列。
使用WITHENCRYPTION可防止将视图作为SQLServer复制的一部分发布。
[例15]创建视图，查看各院系男女生情况，如图3-7所示。
CREATEVIEWview_s
AS
SELECTStudent.deptAS所在院系,Student.sexAS性别
FROMStudent；
[例16]创建带有运算的视图。
CREATEVIEWdbo.v_s
AS
SELECTyear（getdate（））-sageas出生年
FROMStudent
whereStudent.dept=’计算机’；
（5）查看视图和修改视图
●查看视图：
EXECsp_helptext‘视图名’
●修改视图
语法结构
ALTERVIEW[.][.]view_name[（column[,...n]）]
[WITH[,...n]]
AS
select_statement
[WITHCHECKOPTION]
:
:
=
{ENCRYPTION|SCHEMABINDING|VIEW_METADATA}
参数说明参见视图创建。
[例17]修改视图。
CREATEVIEWdbo.v_e_s
as
selectsno,snameas姓名，sex
FROMStudent
ALTERVIEWdbo.v_e_s
as
selectsno,snameas姓名，oldgrade
FROMStudent
[例18]用查询分析器查看视图。
EXECsp_helptext‘dbo.v_e_s’；
（6）删除视图
语法结构
DROPVIEW{view}[,...n]
参数说明：
view：
是要删除的视图名称。
视图名称必须符合标识符规则。
有关更多信息，请参见使用标识符。
可以选择是否指定视图所有者名称。
ｎ：
是表示可以指定多个视图的占位符。
[例19]删除视图v_e_v。
DROPVIEWv_e_v；
（7）数据插入
INSERT命令的语法格式:
INSERT[INTO]表名或视图名[（column_list）]VALUES（data_values）
说明：
此语句将使data_values作为一行或者多行插入已命名的表或视图中。
column_list是由逗号分隔的列名列表，用来指定为其提供数据的列。
如果没有指定column_list，表或者视图中的所有列都将接收数据。
如果column_list没有为表或视图中的所有列命名，将在列表中没有命名的任何列中插入一个NULL值（或者在默认情况下为这些列定义的默认值）。
在列的列表中没有指定的所有列都必须允许null值或者指定的默认值。
创建INSERTFROM查询
通过INSERTFROM查询，可以将行从一个表复制到另一个表中或在同一个表内复制。
例如，在employee表中，可以通过INSERTFROM查询，将有关某科室的信息都复制到另一个表中，并使该表可由该科室使用。
[例20]向学生表Student中插入新同学：
，李济，男，19，计算机，698，可闻。
INSERT
INTOStudent
升级会员 