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

1、数据库表和视图的基本操作实验一 数据库表和视图的基本操作一、实验大纲实验目的：1. 掌握数据库表和视图的概念2. 熟练掌握创建、修改、删除表的方法3. 熟练掌握创建、修改、删除视图的方法4. 掌握表和视图的区别5. 熟练掌握单表和视图的各种查询操作6. 掌握空值的概念7. 了解别名的概念8. 掌握不精确查询的操作方法9. 熟练掌握数据插入、修改、删除的概念10. 熟练掌握INSERT、UPDATE、DELETE命令的实现方式11. 掌握将子查询嵌套在INSERT、UPDATE、DELETE中的操作方法12. 掌握分别用T-SQL语言和企业管理器两种方法进行以上各种操作实验要求：1创建表表1结构

2、为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（领导

3、编号），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。建立销售部

4、门职务为总经理或副总经理的应发工资的视图E_view46查看视图定义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，所在部门为

5、人事科，基本工资为412，基本津贴为218，水电补贴为10，生活补贴为400，边远补贴为12.5。（3）将所有职工的基本工资增加80。（4）将职工编号为01180406的职务改为职员。（5）将职工赵前进的基本工资减少80。（6）将职工表进行复制。（7）从表Header中删除赵前进的所有数据。（8）删除采购科所有职工的信息。11简单查询查找所有职工编号，姓名查找所有职工的详细信息该单位所有的部门数12单表条件查询查找所有的女职工信息查找销售科的所有男职工信息查找职工编号为01180506的职工的基本工资和实发工资查找基本津贴为空值的职工信息查找李姓职工的基本信息查找不姓王的所有职工信息13利用表

6、别名实现以上操作实验环境：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（导师）T

7、eacher教师：tno（教师编号），tname（教师姓名），dept（所在系），salary（工资），title（职称）Course课程：cno（课程号），cname（课程名），descry（课程说明），dept（开课系），credits（学分）SC成绩：sno（学号），cno（课程号），grade（成绩）1.利用企业管理器实现表和视图的基本操作（1）创建表鼠标右键单击所要建表的数据库，“新建”“表”弹出如图1所示界面，在别名里输入字段名，然后选择数据类型，确定数据长度，是否允许为空。也可以填写有关列的部分属性。图1（2）修改表在相应的数据库中选定要修改的名，右键单击，或选择菜单“操作”，选

8、择“设计表”，弹出如图2所示界面，可以对表的列进行修改，增加或删除列等操作。图2 图3（3）删除表选择要删除的表，右键单击（或选择菜单“操作”），在弹出菜单中选择删除，弹出如图3所示界面。选择按钮“全部除去”。（4）创建视图在相应的数据库下，选择视图，在“操作”菜单下选择“新建视图”，弹出如图4所示界面，在上方工作区，右键单击，在弹出菜单中选择“添加表”，如图5。图4 图5在弹出的“添加表”对话框中，选择所要建立视图的表或视图，如图6所示。选择添加按钮。若要添加多个表重复刚才过程，直至添加完成。“关闭”添加表对话框，返回添加视图界面，可以看出表已经添加成功，在相应表中选择要建立视图的字段，如图

9、7。系统会自动生成SQL代码，下面窗口出现视图运行结果。图6 图7（5）查看或修改视图右键单击所要查看或修改的视图，选择“设计视图”，弹出如图3-7所示界面，可以对视图进行查看或修改。（6）删除视图右键单击所要删除的视图，在弹出菜单中选择“删除”按钮，弹出如图3-3所示对话框。选择“全部除去”按钮。（7）数据插入打开企业管理器，右键单击要插入数据的表或选中要插入数据的表，选择菜单“操作”“打开表”“返回所有行”，如图8所示，则会弹出该表的数据对话框，即可以插入数据，如图9所示。图8 图9或者：单击图3-9上的按钮，则显示弹出式菜单，如图10所示，选择“从中插入”，则弹出对话框，选择插入的数据源

10、表，如图11所示。图10 图11选择表Student，则弹出如图3-12所示界面，下面列表区显示表Student的内容，中间代码区显示代码，将表Student中的内容插入到表st中。单击“运行”按钮，则弹出如图3-13所示，提示此次插入数据的行数，表明插入成功。图12 图13（8）数据更新在如图10所示菜单中，选择“更新”，弹出如图14所示，在上面网格区中的“列”中选择所要修改的列，如sage，在“表”中选择Student表，在“新值”中写入新设定值，如18。同理可以设置多个列。如果是有条件修改，则在“准则”中写入条件。如图是将所在系为空的学生的年龄修改为18，系修改为“材料学院”。代码区会自

11、动显示刚才操作的SQL语句。运行后，提示如图15所示，表示此次修改成功。图14 图15（9）数据删除在如图10的菜单中选择“删除”，则弹出如图16所示的窗口，操作类似更新。如果是无条件删除，则上面网格区不做任何操作，如果是条件删除，则在网格区中进行条件设定，如在“列”中选择sno，在“表”中选择Student，在“准则”中写入该列满足的条件，如0000。上述设置表示在表Student中删除学号为0000的学生记录。代码区自动显示此次操作的代码形式。设置完成后，单击“运行”按钮，则弹出如图17所示对话框，说明此次删除数据成功。 图16 图17（10）单表查询右键单击要查询的表或选择要进行查询的表

12、，如图18所示，选择菜单“操作”“打开表”“查询”，弹出查询窗口如图19所示。选择所要显示的列名，代码区会自动生成SQL语句，然后点击按钮 ，运行查询。查询结果显示在显示区中。 图18 图19也可利用新建视图进行查询，如下例所示：例1查询所有学生信息，如图20所示。注：图中代码区的代码为自动生成，读者也可以自行在代码区进行修改。 图20 图21例2查询所有学生的出生年份。提示：表中有学生年龄字段，可以用当前年份减去年龄字段值得到出生年份。如本例用2007-sage来进行计算，如图21所示。例3查询所有女生信息。提示：在表中选择相应要显示的字段。然后在“准则”栏中写入条件，如在列sex 对应的“

13、准则”中写入=“女”，如图22所示。图22例4查询所有年龄大于或等于20岁女生信息。如图23所示。提示：多条件查询，只需在相应的“准则”栏中写入多个条件即可。如本例中在列sex对应的“准则”栏中写入=“女”，同时在列sage对应的“准则”栏中写入=20即可。图232.利用T-SQL语言实现基本表和视图操作（1）创建表语法结构：CREATE TABLE database_name. owner .| owner. table_name ( | column_name AS computed_column_expression | := CONSTRAINT constraint_name | P

14、RIMARY KEY | UNIQUE ,.n ) 主要参数说明：database_name：是要在其中创建表的数据库名称。database_name 必须是现有数据库的名称。如果不指定数据库，database_name 默认为当前数据库。当前连接的登录必须在 database_name 所指定的数据库中有关联的现有用户 ID，而该用户 ID 必须具有创建表的权限。owner：是新表所有者的用户 ID 名，owner 必须是 database_name 所指定的数据库中的现有用户 ID，owner 默认为与 database_name 所指定的数据库中的当前连接相关联的用户 ID。如果 CRE

15、ATE TABLE 语句由 sysadmin 固定服务器角色成员或 database_name 所指定的数据库中的 db_dbowner 或 db_ddladmin 固定数据库角色成员执行，则 owner 可以指定与当前连接的登录相关联的用户 ID 以外的其它用户 ID。如果与执行 CREATE TABLE 语句的登录相关联的用户 ID 仅具有创建表的权限，则 owner 必须指定与当前登录相关联的用户 ID。sysadmin 固定服务器角色成员或别名为 dbo 用户的登录与用户 ID dbo 相关联；因此，由这些用户创建的表的默认所有者为 dbo。不是由上述两种角色的登录创建的表所有者默认为

16、与该登录相关联的用户 ID。table_name：是新表的名称。表名必须符合标识符规则。数据库中的 owner.table_name 组合必须唯一。table_name 最多可包含 128 个字符，但本地临时表的表名（名称前有一个编号符 #）最多只能包含 116 个字符。column_name：是表中的列名。列名必须符合标识符规则，并且在表内唯一。以 timestamp 数据类型创建的列可以省略 column_name。如果不指定 column_name，timestamp 列的名称默认为 timestamp。例5创建表Course和表Teacher。其中Course表中课程编号为主键，课程名

17、和开课院系不能为空，Teacher表中教师编号为主键，姓名、所在系不能为空。CREATE TABLE Course (cno char(10) not null primary key, cname char(10) not null, descry char(10), dept char(10) not null,credit decimal(3,1)；CREATE TABLE Teacher (tno char(10) not null primary key, tname char(10) not null, dept char(10) not null, salary decimal(

18、6.2)title char(10)；例6创建表Student，设学号为主键，姓名不能为空，性别为“男”或“女”。CREATE TABLE Student(sno char(10) not null primary key, sname char(10) not null, sex char(2) CHECK in (“男”，“女”), sage int, dept char(10), oldgrade decimal(3.1) advisor char(8)；例7建立部门DEPT表，要求部门名称Dept列取值唯一，部门编号Dno列为主码。CREATE TABLE DEPT（Dno char(

19、8),Dept char(9) unique,Location char(10),Header char(8),Primary key(Dno)）；（2）修改表修改表语法结构：ALTER TABLE table_name ALTER COLUMN column_name new_data_type ( precision , scale ) COLLATE NULL | NOT NULL | ADD | DROP ROWGUIDCOL | ADD | column_name AS computed_column_expression ,.n | WITH CHECK | WITH NOCHEC

20、K ADD ,.n | DROP CONSTRAINT constraint_name | COLUMN column ,.n | CHECK | NOCHECK CONSTRAINT ALL | constraint_name ,.n | ENABLE | DISABLE TRIGGER ALL | trigger_name ,.n 参数说明详见SQL Server联机丛书。例8添加新列。CREATE TABLE doc_exa ( column_a INT) GOALTER TABLE doc_exa ADD column_b VARCHAR(20) NULLGO例9删除列。CREATE

21、TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL) GOALTER TABLE doc_exb DROP COLUMN column_bGO例10修改字段定义。ALTER TABLE student；ALTER COLUMN dept varchar(25) NULL；例11修改表，向表中添加约束。ALTER TABLE DEPTADD CONSTRAINT PK_DnoPRIMARY KEY(Dno)；例12 向表中添加具有 UNIQUE 约束的新列。 CREATE TABLE doc_exc ( column_a INT) GO

22、ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUEGO（3）删除表语法结构：DROP TABLE table_name ,.n 参数说明：table_name：指定要删除的表名称。例13删除单表。DROP TABLE doc_exc；例14删除多表。DROP TABLE doc_exa, doc_exb；（4）创建视图语法格式：CREATE VIEW . . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK O

23、PTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA 主要参数说明：view_name：视图的名称。视图名称必须符合标识符规则。可以选择是否指定视图所有者名称。column：是视图中的列名。只有在下列情况下，才必须命名 CREATE VIEW 中的列；当列是从算术表达式、函数或常量派生的，两个或更多的列可能会具有相同的名称（通常是因为联接），视图中的某列被赋予了不同于派生来源列的名称。还可以在 SELECT 语句中指派列名。如果未指定 column，则视图列将获得与 SELECT 语句中的列相同的名称。 说明：在视图的各列中，列名的权限在 CR

24、EATE VIEW 或 ALTER VIEW 语句间均适用，与基础数据源无关。例如，如果在 CREATE VIEW 语句中授予了 title_id 列上的权限，则 ALTER VIEW 语句可以将 title_id 列改名（例如改为 qty），但权限仍与使用 title_id 的视图上的权限相同。n：表示可以指定多列的占位符。AS：视图要执行的操作。select_statement：是定义视图的 SELECT 语句。该语句可以使用多个表或其它视图。若要从创建视图的 SELECT 子句所引用的对象中选择，必须具有适当的权限。视图不必是具体某个表的行和列的简单子集。可以用具有任意复杂性的 SELE

25、CT 子句，使用多个表或其它视图来创建视图。在索引视图定义中，SELECT 语句必须是单个表的语句或带有可选聚合的多表 JOIN。对于视图定义中的 SELECT 子句有几个限制。CREATE VIEW 语句不能： 包含 COMPUTE 或 COMPUTE BY 子句。 包含 ORDER BY 子句，除非在 SELECT 语句的选择列表中也有一个 TOP 子句。 包含 INTO 关键字。 引用临时表或表变量。WITH CHECK OPTION：强制视图上执行的所有数据修改语句都必须符合由 select_statement 设置的准则。通过视图修改行时，WITH CHECK OPTION 可确保提

26、交修改后，仍可通过视图看到修改的数据。WITH ENCRYPTION：表示 SQL Server 加密包含 CREATE VIEW 语句文本的系统表列。使用 WITH ENCRYPTION 可防止将视图作为 SQL Server 复制的一部分发布。例15创建视图，查看各院系男女生情况，如图3-7所示。CREATE VIEW view_sASSELECT Student.dept AS 所在院系, Student.sex AS 性别 FROM Student；例16创建带有运算的视图。CREATE VIEW dbo.v_sASSELECT year(getdate()-sage as 出生年FR

27、OM Studentwhere Student.dept=计算机；（5）查看视图和修改视图 查看视图：EXEC sp_helptext 视图名 修改视图语法结构ALTER VIEW . . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA 参数说明参见视图创建。例17修改视图。CREATE VIEW dbo.v_e_sasselect sno,sname as 姓名，sexFROM StudentALTER V

28、IEW dbo.v_e_sasselect sno,sname as 姓名，oldgradeFROM Student例18用查询分析器查看视图。EXEC sp_helptext dbo.v_e_s；（6）删除视图语法结构DROP VIEW view ,.n 参数说明：view：是要删除的视图名称。视图名称必须符合标识符规则。有关更多信息，请参见使用标识符。可以选择是否指定视图所有者名称。：是表示可以指定多个视图的占位符。例19删除视图v_e_v。DROP VIEW v_e_v；（7）数据插入INSERT 命令的语法格式:INSERT INTO 表名或视图名 (column_list) VALU

29、ES（data_values）说明：此语句将使 data_values 作为一行或者多行插入已命名的表或视图中。column_list 是由逗号分隔的列名列表，用来指定为其提供数据的列。如果没有指定 column_list，表或者视图中的所有列都将接收数据。如果column_list 没有为表或视图中的所有列命名，将在列表中没有命名的任何列中插入一个 NULL 值（或者在默认情况下为这些列定义的默认值）。在列的列表中没有指定的所有列都必须允许 null 值或者指定的默认值。创建 INSERT FROM 查询通过 INSERT FROM 查询，可以将行从一个表复制到另一个表中或在同一个表内复制。例如，在 employee 表中，可以通过 INSERT FROM 查询，将有关某科室的信息都复制到另一个表中，并使该表可由该科室使用。例20向学生表Student中插入新同学：03250606，李济，男