数据库表和视图的基本操作

实验一 数据库表和视图的基本操作

一、实验大纲

实验目的：

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插入职工编号，职工姓名，性别，家庭住址，所在部门，工龄，职务分

别为01180202，张扬，男，七里河北街254号，人事科，5，职员。

（2）向工资表Salary中插入职工编号为01180202，所在部门为人事科，基本工资为412，基本津贴为218，水电补贴为10，生活补贴为400，边远补贴为12.5。

（3）将所有职工的基本工资增加80。

（4）将职工编号为01180406的职务改为职员。

（5）将职工赵前进的基本工资减少80。

（6）将职工表进行复制。

（7）从表Header中删除赵前进的所有数据。

（8）删除采购科所有职工的信息。

11．简单查询

查找所有职工编号，姓名

查找所有职工的详细信息

该单位所有的部门数

12．单表条件查询

查找所有的女职工信息

查找销售科的所有男职工信息

查找职工编号为01180506的职工的基本工资和实发工资

查找基本津贴为空值的职工信息

查找李姓职工的基本信息

查找不姓王的所有职工信息

13．利用表别名实现以上操作

实验环境：

SQL Server 2000

二、实验指导

说明：本示例用例的表结构如下所示：

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）创建表

语法结构：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 ]

)

主要参数说明：

database_name：是要在其中创建表的数据库名称。database_name 必须是现有数据库的名称。如果不指定数据库，database_name 默认为当前数据库。当前连接的登录必须在