第3章 SQL数据库中表和视图的创建修改

第3章  表和视图的创建修改

数据库实际上是数据表的集合。在关系数据库中，数据库通常包含多个数据表，数据库中的数据信息都是存储在数据表当中的。数据表是对数据进行存储和操作的一种逻辑结构，对于用户而言，一个数据表表示一个数据库对象。

3.1 创建数据表—CREATE TABLE   

创建数据表要符合一定的规范，通常情况下，表的名字应当以字母开头，名称允许的长度随数据库提供商的不同而不同。表名称中不允许用特殊字符，虽然有些数据库提供商支持使用一些特殊符号做表的名称，但是由于它们可能会导致标识符的混乱，所以建议最好还是不要使用。在表的名称中可以使用数字字符。本节中将讲解与创建数据表有关的一些知识。

3.1.1 设计实例表

创建一个数据表时，主要包括以下几个组成部分。

（1）字段名（列名）：字段名可长达128个字符，可包含中文、英文字母、下划线、#号、货币符号（￥）及AT符号（@），且同一表中不许有重名列。

（2）字段数据类型：数据字段的类型。

（3）字段的长度、精度和小数位数：字段的长度是指字段所能容纳的最大数据量，但对不同的数据类型来说，长度对字段的意义可能有些不同。

• 对字符串与Unicode数据类型而言，长度代表字段所能容纳的字符的数目，因此它会限制用户所能输入的文本长度。
• 对数值类的数据类型而言，长度则代表字段使用多少个字节来存放数字。
• 对binary、varbinary、image数据类型而言，长度代表字段所能容纳的字节数。

精度是指数中数字的位数，包括小数点左侧的整数部分和小数点右侧的小数部分；小数位数则是指数字小数点右侧的位数。例如，数字12345.678，其精度为8，小数位数为3。所以只有数值类的数据类型，才有必要指定精度和小数位数。

（4）NULL值与DEFAULT值：

NULL值表示设置该字段值是否运行为空，即不赋任何值。而DEFAULT值表示某一字段的默认值，当没有输入数据时，则使用此默认的值。

本节设计的实例表为员工信息表（t_employ），其所在的数据库为ORCTEXT，表的设计如表3.1所示。

表3.1  实例表t_employ的结构设计

列    名	数据类型	长    度	允许为空
e_name（姓名）	char	10	´
e_nums（工号）	char	20	´
e_bir（籍贯）	char	40	´
e_address（现住址）	char	60	Ö
E_birdate（出生日期）	date	8	Ö
e_depart（部门）	char	20	Ö
e_remarks（备注）	char	100	Ö

3.1.2 创建数据表

与SQL标准语法相同，在Transact-SQL中，创建一张新表可以使用CREATE TABLE命令。CREATE TABLE语句的完整语法非常复杂，可选选项很多，这里只简单介绍其最基本的创建语法。

【语法说明】

创建数据表的语法结构如下所示。

CREATE TABLE table_name

(

column_name1 data_type,

column_name2 data_type,

......

)

在CREATE TABLE命令后，指明创建的数据库表的名称，接着要分别定义表中各列的名称、数据类型等。表中各列的定义在括号中完成，且各列之间以逗号隔开。

【上机实战】

使用CREATE TABLE语句创建员工信息数据表（t_employ），其实现代码代码如下。

create table t_employ

(

   e_name char(10),

   e_nums char(20),

   e_bir char(40),

   e_address char(60),

   E_birdate date,

   e_depart char(20),

   e_remarks char(100)

)

在oracle的“PL/SQL Developer工具”中运行上面的SQL代码，将会创建员工信息数据表（t_employ）。语句执行以后，展开“PL/SQL Developer工具”中的“table”节点，可以看到创建的数据表，如图3.1所示。

右击“t_employ”数据表，然后选择“edit”命令，在弹出的对话框中选择“columns”选项卡，此时可以看到创建数据表的逻辑结构，如图3.2所示。

 

图3.1  在“PL/SQL Developer工具”中查看创建的数据表            

   图3.2  查看“t_employ”数据表的逻辑结构

说明：创建表，必须要保证该表名在数据库中不存在，否则数据库会提示创建失败。如果需要创建已经存在的表，必须先将原来的表删除，再重新创建。

 3.2 修改数据表—ALTER TABLE

在SQL中，通过ALTER TABLE语句进行修改数据表的操作。这一通用命令允许用户添加列或约束条件，通过添加或撤销设在列上的SCOPE来定义用户的数据类型，添加或撤销默认值等。

【语法说明】

在SQL中，修改数据表的语法结构如下所示。

ALTER TABLE table_name

[ADD[COLUMN]column_name data_type attributes]

|[ALTER[COLUMN]column_name SET DEFAULT default_value]

|[ALTER[COLUMN]column_name DROP DEFAULT]

|[ALTER[COLUMN]column_name ADD SCOPE table_name]

|[ALTER[COLUMN]column_name DROP SCOPE {RESTRICT | CASCADE}]

|[DROP [COLUMN]column_name {RESTRICT | CASCADE}]

|[ADD table_constraint_name]

|[DROP CONSTRAINT table_constraint_name {RESTRICT | CASCADE}]

上述语法结构中的参数含义如下所示。

1. table_name：指要修改数据表的名称。
2. COLUMN：可选关键字。
3. column_name：要修改数据表中列的名称。
4. Data_type：表示列的数据类型。
5. SET DEFAULT：为字段设置默认值。
6. DROP DEFAULT：删除设置的默认值。
7. Default_value：要设置的默认值。
8. ADD SCOPE：添加自定义数据类型。
9. DROP SCOPE：删除自定义的数据类型。
10. ADD table_constraint_name：为数据表添加约束。
11. RESTRICT：当撤销约束条件时，若数据库中其他对象依赖于此表或列的约束条件，则DROP RESTRICT会通知DBMS中止此操作。
12. CASCADE：当撤销约束条件时，若数据库中其他对象依赖于此表或列的约束条件，则DROP RESTRICT会通知DBMS撤销依赖词或列约束条件的数据库对象。

以上叙述的是SQL标准中修改数据表的语法，下面介绍一下在SQL Server数据库中修改数据表的语法结构，具体代码如下所示：

ALTER TABLE[database_name.[schema_name.|schema_name.]table_name

{ALTER COLUMN column_name

{[type_schema_name.]type_name

[NULL | NOT NULL]

|{ADD | DROP}

{ROWGUIDCOL | PERSISTED}}

上述语法结构中的参数含义如下所示。

1. Database_name：创建数据表所在的数据库的名称。
2. Schema_name：表示表所属架构的名称。
3. Table_name：要修改的数据表的名称。
4. Column_name：要更改、添加或删除列的名称。Column_name最多可以包含128个字符。
5. type name：更改后列的新数据类型或添加列的数据类型。不能为己分区表的现有列指定type_name。
6. NULL | NOT NULL：指定列是否允许为空值。如果列不允许空值，则只有在指定默认值或表为空的情况下，才能用ALTER TABLE语句添加该列。

ROWGUIDCOL：指定在指定列中添加或删除ROWGUIDCOL属性。

 3.2.1 为数据表添加列

数据表创建完成以后，可能还需要向其中增加新的字段信息。如前面介绍的员工信息数据表（t_employ），在实际的应用中还需要向数据表当中添加员工的职务信息，如果重新创建t_employ表不仅会浪费开发时间，而且如果数据表中有大量数据信息的话，还会使得原来数据表中的数据信息丢失。为了避免这种情况的发生，可以采用SQL提供的ADD关键字向表中添加新列的方法修改数据表的结构。

【语法说明】

使用ADD关键字为数据表添加列的语法结构如下所示。

ALTER TABLE table_name

ADD column_name  data_type

表达式中，table_name指的是要修改数据表的名字，ADD关键字后面接的是要创建列的列名、数据类型、对列设置非空约束和缺省值等。在数据表中存在大量数据的前提下，由于DBMS为已有行上的新列设NULL值，因此当使用ALTER TABLE语句向表中添加新列时，不能简单地添加NOT NULL约束，还必须提供缺省值。因为如果没有提供缺省值，DBMS将假设已有行上的新列为NULL值，这就和NOT NULL约束相抵触。当然，如果数据表中不存在数据信息，则不会出现上述情况。

【上机实战】

向员工信息表（t_employ）中添加职务（e_Duty）列，并且采用NOT NULL约束。

ALTER TABLE t_employ

ADD e_Duty CHAR(10) NOT NULL

代码运行后，查看数据表的结构如图3.3所示。

图3.3  增加新列

从上图中可以看出，数据表中增加了一列“e_Duty”。

 3.2.2 修改列的数据类型

如果数据表中列的数据类型建错，还可以通过SQL语句进行修改。

【语法说明】

修改数据列的数据类型使用MODIFY关键字来实现，其实现的语句结构如下所示。

ALTER TABLE table_name

MODIFY column_name data_type

其中，table_name是要修改数据表的名称，MODIFY关键字后面接要修改列的列名和修改后的数据类型。

【上机实战】

将员工信息表（t_employ）中的e_address列的数据类型由原来的“Char（60）”更改为“varchar2(30)”。

ALTER TABLE t_employ

MODIFY e_address varchar2(30)

代码运行后，员工信息表（t_employ）中e_address列的数据类型由原来的“Char（30）”更改为“varchar2(30)”，如图3.4所示。

 

图3.4  修改数据表列的数据类型

说明：当表中没有数据时，可以把数据的长度从高向低改变，可以把某种数据类型改变为另一种数据类型，如果数据表中有数据，这样改变可能会丢失数据。

 3.2.3 修改列的长度

数据表在创建以后，如果想更改某一列的长度，也可以通过MODIFY关键字来实现。

【上机实战】

将员工信息表（t_employ）中的e_address列的长度由原来的“varchar2(30)”更改为“varchar2(50)”。

ALTER TABLE t_employ

MODIFY e_address varchar2(50)

代码运行后，员工信息表（t_employ）中Address列的长度由原来的30更改为50，如图3.5所示。

图3.5  修改数据表列的长度

 3.2.4 设置或取消非空约束

使用MODIFY关键字也可以设置或取消非空约束。

【语法说明】

使用MODIFY关键字设置或取消设置非空约束的语句结构如下所示。

ALTER TABLE table_name

MODIFY column_name data_type NULL | NOT NULL

其中，NULL表示将数据表列设置为可以为空的状态；NOT NULL表示将数据表列设置为非空约束的状态。

【上机实战】

将员工信息表（t_employ）中的e_name列设置为非空约束的状态；将e_duty列设置为可以为空的状态。

ALTER TABLE t_employ

MODIFY e_name char(10) not null

MODIFY e_duty char(10) null

代码执行以后，e_name列被设置为非空约束的状态；e_duty列设置为可以为空的状态，如图3.6所示。

图3.6  设置及取消非空约束

 3.2.5 删除列

在SQL中，使用DROP COLUMN语句删除数据表中已有的列。从表中删除数据表列会导致数据的丢失。因此，最好不要从表中删除已有的列。

【语法说明】

使用DROP COLUMN删除数据表列的语句结构如下所示。

ALTER TABLE table_name

DROP COLUMN column_name

其中，table_name是要修改数据表的名称，column_name表示要删除数据表列的名称。

注意：在使用DROP COLUMN语句删除数据表中已有的列时，一次只能删除一列。

【上机实战】

使用DROP COLUMN语句删除员工信息表（t_employ）中的e_Duty列。

ALTER TABLE t_employ

DROP COLUMN e_Duty

代码执行以后，员工信息表（t_employ）中的e_Duty列被删除，如图3.7所示。

图3.7  删除数据表中的列

3.3 删除数据表—DROP TABLE

当数据表不在需要时，可以将其从数据库中删除，为创建新的数据表而释放数据库空间。当删除表时，该表的结构定义、数据、全文索引、约束以及索引都会从数据库中永久地删除。原先用于存储该表及其索引的空间可以用于其他表。

SQL中通过DROP TABLE命令删除数据表。该命令能删除数据表的定义以及表的全部数据、索引、触发器、约束等，同时也会将表中的所有记录同时删除。因此，在删除前一定要考虑清楚。

【语法说明】

删除数据表是通过DROP TABLE语句来实现的，在SQL中，删除数据表的语法结构如下所示。

DROP TABLE table_name RESTRICT|CASCADE

在SQL-99中，RESTRICT表示如果有视图或约束条件涉及要删除的数据表时，就禁止DBMS执行该命令。而CASCADE选项则将该表与其涉及的对象一起删除。

在SQL Server数据库当中，使用DROP TABLE语句来删除数据表，但是，其语法结构与SQL标准中的语法结构还有一定的区别，其语法结构如下所示。

DROP TABLE [database_name.[schema_name].|schema_name.]table_name[,…n][;]

在上述语句中，database_name表示数据库的名称，schema_name表示数据表所属架构的名称，table_name表示要删除的数据表的名称。

【上机实战】

使用DROP TABLE语句删除数据表“t_name”。

DROP TABLE t_name

代码运行后，数据表t_name被删除。

3.4 创建视图—CREATE VIEW

视图是从一个或多个表或者视图中导出的表，其结构和数据是建立在对表的查询基础上的。和真实的表一样，视图也包括多个被定义的数据列和多个数据行，但从本质上讲，这些数据列和数据行来源于其所引用的表。

3.4.1  视图概述

视图不是真实存在的基础表而是一个虚拟表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。图3.8演示了在两个表上建立的视图的例子。

图3.8  视图的基本示意图

对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，或者其他视图。

1．视图的分类

在SQL Server数据库当中，可以创建的视图包括标准视图、索引视图和分区视图。

• 标准视图组合了一个或多个表中的数据，主要用于加强数据和简化操作。所谓加强数据是指用户能够着重于他们所感兴趣的特定数据和所负责的特定任务，不必要的数据或敏感数据可以不出现在视图中。而简化操作是指可以将常用连接、UNION查询和SELECT查询定义为视图，以便使用户不必在每次对该数据执行附加操作时指定所有条件和条件限定。
• 索引视图是被具体化了的视图，即它已经过计算并存储。可以为视图创建索引，即对视图创建一个唯一的聚集索引。索引视图可以显著提高某些类型查询的性能，尤其适于聚合许多行的查询，但不太适于经常更新的基本数据集。
• 分区视图在一台或多台服务器间水平连接一组成员表中的分区数据。这样，数据看上去如同来自于一个表，连接同一个SQL Server实例中成员表的视图是一个本地分区视图。

2．视图的优点和作用

视图有很多优点，主要表现在简化操作、定制数据、合并分隔数据、安全性等方面，具体可表述如下：

• 可以使视图集中数据、简化和定制不同用户对数据库的不同数据要求。
• 使用视图可以屏蔽数据的复杂性，用户不必了解数据库的结构，就可以方便地使用和管理数据，简化数据权限管理和重新组织数据以便输出到其他应用程序中。
• 视图可以使用户只关心他感兴趣的某些特定数据和他们所负责的特定任务，而那些不需要的或者无用的数据则不在视图中显示。
• 视图大大地简化了用户对数据的操作。
• 视图可以让不同的用户以不同的方式看到不同或者相同的数据集。
• 在某些情况下，由于表中数据量太大，因此在进行表的设计时常将表进行水平或者垂直分割，但表的结构的变化对应用程序产生不良的影响。
• 视图提供了一个简单而有效的安全机制。

3.4.2  创建视图的语句结构

视图的创建主要由CREATE VIEW关键字来实现。在SQL中，创建视图的语法结构如下所示。

CREATE VIEW view_name[(column1,column2,…)]

AS

(SELECT_statement

[WITH[CASCADED|LOCAL]CHECK OPTION])

上述语句中的几个重要参数说明如下所示。

1. View_name：要创建的视图名称。
2. [(column1,column2,…)]：可选项，缺省时，为子查询结果中的字段名。
3. CHECK OPTION：保证只有视图可读的数据才可以由视图插入、更新或删除。这一子句只能用于对基表进行更新的视图。
4. CASCADED：此选项对当前视图和所有视图执行选项检查，适用于嵌套的视图。
5. LOCAL：此选项只对当前视图执行选项检查，适用于嵌套的视图。

注意：当视图被创建以后，只在数据字典中存放视图的定义，而其中的SELECT语句并不执行。只有当用户对视图进行操作时，才按照视图的定义将数据从基本表中取出。

在SQL Server数据库中，创建视图的语法结构如下所示。

CREATE VIEW[schema_name.]view_name[(column[,…n])]

[WITH<view_attribute>[,…n]]

AS select_statement[;]

[WITH CHECK OPTION]

<view_attribute>::=

{

[ENCRYPTION]

[SCHEMABINDING]

[VIEW_METADATA]

}

上述语法中各参数的含义如下所示。

1. Schema_name：表示视图所属架构的名称。
2. View_name：表示要创建视图的名称。视图名称必须符合有关标识符的规则。
3. column：视图中的列名称。
4. Select_statement：定义视图的SELECT语句。该语句可以使用多个表和其他视图。
5. CHECK OPTION：强制针对视图执行的所有数据修改语句都必须符合在select_statement中设置的条件。
6. ENCRYPTION：对sys.syscomments表中包含CREATE VIEW语句文本的条目进行加密。
7. SCHEMABINDING：将视图绑定到基础表的架构。
8. VIEW_METADATA：指定为引用视图的查询请求浏览模式的元数据时，SQL Server实例将向DB-Library、ODBC和OLEDBAPI返回有关视图的元数据信息，而不返回基表的元数据信息。

注意：在创建视图时，只能在当前数据库中创建视图。同时视图中指定的列名要不同于其派生出的来源列的名称。

3.4.3 从一个表创建视图

如果当只想提取数据表中的一部分信息，可以通过在单表中创建视图的方法来实现。

【上机实战】

为员工信息表（t_employ）中的员工姓名（e_name）、工号（e_nums）、联系地址（e_Address）和部门（e_depart）信息创建一个视图emp_view，使得在视图中只能够显示员工的姓名、工号、联系地址和部门信息，数据表中的其他信息不显示。

CREATE VIEW emp_view

AS

SELECT e_name, e_nums,e_address,e_depart

FROM t_employ

代码执行以后，视图创建完成，在PL/SQL Developer中可以看到所创建的视图，如图3.9所示。

图3.9  查看创建后的视图

视图创建以后，通过下面的语句查询视图中的数据。

SELECT * FROM emp_view

从查询的数据结果可以看出，视图中的员工姓名（e_name）、工号（e_nums）、联系地址（e_Address）和部门（e_depart）信息是从员工信息表（t_employ）中提取出来的，如图3.10所示。

图3.10  查看视图中的数据信息

通过视图也可以查询数据表中符合条件的数据信息。

下面的代码演示的是，为员工信息表（t_employ）中的员工姓名（e_name）、工号（e_nums）、联系地址（e_Address）和部门（e_depart）信息创建一个视图empdepart_view，使得在视图中显示部门为“采购部”的数据信息。

CREATE VIEW empdepart_view

AS

SELECT e_name, e_nums,e_address,e_depart

FROM t_employ

where e_depart='采购部'

代码执行以后，视图创建完成。

视图创建以后，通过下面的语句查询视图中的数据。

SELECT * FROM empdepart_view

从查询的数据结果可以看出，通过视图显示的结果都是“采购部”的员工信息，如图3.11所示。

图3.11  查看视图中的数据信息

3.4.4 从多个表创建视图

通过视图可以获取单个数据表中的数据，也可以获取多个数据表中的数据，本节中将讲解一下如何从多个数据表中获取数据信息。

【上机实战】

创建一个视图view_empInfo，使得在该视图中显示员工信息表（t_employ）中的员工姓名（e_name）、工号（e_nums）和部门（e_depart）信息，以及显示员工工资表（t_salary）中对应员工每个月的工资信息，数据表中的其他信息不显示。

CREATE VIEW view_empInfo

AS

SELECT a.e_name, a.e_nums,a.e_depart,b.s_yf,b.s_jje

FROM t_employ a,t_salary b

where a.e_nums=b.s_gh

代码执行以后，视图创建完成。

通过下面的语句查询视图中的数据。

SELECT * FROM view_empInfo

语句执行以后，查询出视图中的数据信息如图3.12所示。

图3.12  查看视图中的数据信息

3.4.5 从视图创建视图

实际上，数据表和视图都是由一系列的SQL语句组成的，通常情况下，视图也可以看作是一个数据表。因此，也可以在视图中再创建一个视图。

【上机实战】

在视图view_empInfo中再创建一个视图，使得该视图包含所有月工资超过3000的员工工资信息。

CREATE VIEW salary_view

AS

SELECT * FROM view_empInfo

WHERE s_jje >3000

代码执行以后，视图创建完成。

视图创建以后，通过下面的语句查询视图中的数据。

SELECT * FROM salary_view

从查询的数据结果可以看出，查询到的数据信息都是员工工资大于3000的数据信息，如图3.13所示。

图3.13  视图中的数据信息

3.4.6 创建视图列的别名

在前面讲解的创建视图的示例中，视图创建后的列名与数据表中的列名都是相同的。然而，在创建视图时，可以给视图的列名指定为与数据表列名不相同的名称。

【上机实战】

为员工信息表（t_employ）中的员工姓名（e_name）、工号（e_nums）和部门（e_depart）字段信息创建一个视图employ_view，使得在视图中只能够显示员工的姓名、工号和部门信息，并且将视图中员工姓名的字段名称定义为View_sName、工号字段名称定义为View_Nums、部门字段名称定义为View_Depart。

CREATE VIEW employ_view（View_sName，View_Nums，View_Depart）

AS

SELECT e_name, e_nums, e_depart

FROM t_employ

代码执行以后，视图创建完成。

视图创建以后，通过下面的语句查询视图中的数据。

SELECT * FROM employ_view

从查询的数据结果可以看出，视图中的员工姓名、工号和部门信息是从员工信息表（t_employ）中提取出来的，并且这3列的名称与数据表t_employ中的列名称不同，如图3.14所示。

图3.14  视图中列的别名

上面示例中将视图列的别名设置成了英文的形式，通常情况下，视图列的别名都设置为中文的形式，这样看起来比较地直观。

下面的代码演示的是，为员工信息表（t_employ）中的员工姓名（e_name）、工号（e_nums）和部门（e_depart）字段信息创建一个视图cemploy_view，使得在视图中只能够显示员工的姓名、工号和部门信息，并且在创建视图时分别使用列的别名为“员工姓名”、“工号”和“部门”，使得视图中的数据看起来更加的直观。

CREATE VIEW cemploy_view（员工姓名,工号，部门）

AS

SELECT e_name, e_nums, e_depart

FROM t_employ

代码执行以后，视图创建完成。

视图创建以后，通过下面的语句查询视图中的数据。

SELECT * FROM cemploy_view

从查询的数据结果可以看出，视图中列名都是中文的形式，如图3.15所示。

图3.15  在视图中使用中文别名

3.5 修改视图—ALTER VIEW

在SQL标准中没有提供修改视图的语句结构，然而不同的数据库管理系统都对SQL标准做了扩充，提供了修改视图的语句结构。下面介绍一下在SQL Server数据库中修改视图的语句结构。

【语法说明】

在SQL Server数据库中修改视图的语句结构如下所示。

ALTER VIEW[schema_name.]view_name[(column[,…n])]

[WITH<view_attribute>[,…n]]

AS select_statement[;]

[WITH CHECK OPTION]

<view_attribute>::=

{

    [ENCRYPTION]

    [SCHEMABINDING]

    [VIEW_METADATA]

}

上述语句中参数的含义如下所示。

1. Schema_name：表示视图所属架构的名称。
2. View_name：表示要修改视图的名称。视图名称必须符合有关标识符的规则。
3. column：视图中的列名称。
4. Select_statement：定义视图的SELECT语句。该语句可以使用多个表和其他视图。
5. CHECK OPTION：强制针对视图执行的所有数据修改语句都必须符合在select_statement中设置的条件。
6. ENCRYPTION：对sys.syscomments表中包含CREATE VIEW语句文本的条目进行加密。
7. SCHEMABINDING：将视图绑定到基础表的架构。
8. VIEW_METADATA：指定为引用视图的查询请求浏览模式的元数据时，SQL Server实例将向DB-Library、ODBC和OLEDBAPI返回有关视图的元数据信息，而不返回基表的元数据信息。

【上机实战】

修改在3.4.3节中创建的视图emp_view，在视图中再添加一列“e_birdate”。

ALTER VIEW emp_view

AS

SELECT e_name, e_nums,e_address,e_birdate,e_depart

FROM t_employ

代码执行以后，视图修改完成。

视图修改以后，通过下面的语句查询视图中的数据。

SELECT * FROM emp_view

从查询的数据结果可以看出，在原来的基础上，视图中又增加了一个员工的出生日期（e_birdate）列，如图3.16所示。

图3.16  修改视图后视图中的数据信息

上面修改视图的示例只是在视图中增加了一个字段，其实在修改视图时可以任意修改视图中的语句。

下面的代码演示的是将在3.4.4节中创建的视图view_empInfo添加一个限定条件，查询“工资”大于3000的员工工资信息。

ALTER VIEW view_empInfo

AS

SELECT a.e_name, a.e_nums,a.e_depart,b.s_yf,b.s_jje

FROM t_employ a,t_salary b

where a.e_nums=b.s_gh

and b.s_jje>3000

代码执行以后，视图修改完成。

视图修改以后，通过下面的语句查询视图中的数据。

SELECT * FROM view_empInfo

视图中查询出的数据结果如图3.17所示。

图3.17  修改视图后视图中的数据信息

3.6 通过视图维护数据

通过视图可以向数据表中插入数据，修改和删除数据表中的数据信息。本节中将讲解一下通过视图维护数据表中数据信息的实现方法。

3.6.1 向视图中插入记录

在INSERT语句中，通过视图可以向视图中的基表中插入数据。

【语法说明】

可以使用INSERT语句，通过视图向视图底层的表中插入数据。通过视图向表中插入数据的语法与直接向表中插入数据的语法是一样的，只是在INSERT语句中使用的是视图名而不是表名。通过视图向数据表中插入数据的语法结构如下所示。

INSERT INTO View_name

[column1,column2,…,columnN]

VALUES(value1,value2,…,valueN)

【上机实战】

为员工信息表（t_employ）中的员工姓名（e_name）、工号（e_nums）、联系地址（e_Address）和部门（e_depart）信息创建一个视图emps_view，使得在视图中只能够显示员工的姓名、工号、联系地址和部门信息。视图创建完成以后，向视图中添加一条数据信息。

首先对员工信息表（t_employ）创建视图，具体代码如下所示。

CREATE VIEW emps_view

AS

SELECT e_name, e_nums,e_address,e_depart

FROM t_employ

通过视图emps_view向数据表t_employ中添加数据，实现的代码如下所示。

INSERT INTO emps_view (e_name,e_nums,e_depart)

VALUES('刘力','9806','采购部')

代码执行以后，数据被插入到视图当中，使用下列语句查询员工信息表（t_employ）中的数据信息。

SELECT * FROM t_employ

代码执行以后，执行结果如图3.18所示。

图3.18  通过视图插入数据后t_employ表中的数据信息

从图中可以看出，员工编号（E_NUMS）为9806的员工信息就是通过视图添加到员工信息表（t_employ）中的数据信息，记录中的籍贯（E_BIR）和联系地址（E_ADDRESS）等字段为NULL是因为在视图中没有给该字段指定添加的值。

注意：通过视图只能向在单表创建的视图中添加或更新数据，在由2个或2个以上的表创建的视图当中，不能够通过视图对其基表进行添加或更新数据操作。

3.6.2 通过视图更新数据

在使用UPDATE语句时，可以通过视图更新数据表中的数据。视图其实是一张虚拟的表，所以对视图的更新实际上是对视图底层数据表的更新。

【上机实战】

为员工信息表（t_employ）中的员工姓名（e_name）、工号（e_nums）、联系地址（e_Address）和部门（e_depart）信息创建一个视图edit_view，使得在视图中只能够显示员工的姓名、工号、联系地址和部门信息。视图创建完成以后，通过视图将员工信息表（t_employ）所有部门为“采购部”的数据信息修改为“采购商务部”。

首先对员工信息表（t_employ）创建视图，具体代码如下所示。

CREATE VIEW edit_view

AS

SELECT e_name, e_nums,e_address,e_depart

FROM t_employ

通过视图eidt_view更改数据表t_employ中添加数据，实现的代码如下所示。

UPDATE edit_view

SET e_depart ='采购商务部'

WHERE e_depart ='采购部'

代码执行以后，通过下面的代码再次查询数据表中的数据信息。

SELECT * FROM t_employ

代码执行以后，执行结果如图3.19所示。

图3.19  通过视图修改数据后t_employ中的数据信息

3.4.3 通过视图删除数据

通过视图也可以删除数据表中的行数据信息。使用DELETE语句通过视图删除其基表中数据信息。

【上机实战】

通过视图删除员工信息表（t_employ）中所有部门为“人事部”的员工信息。首先将员工信息表（t_employ）中的数据信息显示出来。

SELECT * FROM t_employ

代码运行以后，如图3.20所示。

为员工信息表（t_employ）中的员工姓名（e_name）、工号（e_nums）、联系地址（e_Address）和部门（e_depart）信息创建一个视图del_view，使得在视图中只能够显示员工的姓名、工号、联系地址和部门信息。视图创建完成以后，通过视图将员工信息表（t_employ）所有部门为“人事部”的数据信息删除。

CREATE VIEW del_view

AS

SELECT e_name, e_nums,e_address,e_depart

FROM t_employ

视图创建以后，通过下面的代码删除数据表t_employ中的数据信息。

DELETE FROM del_view

WHERE e_depart ='人事部'

代码执行以后，员工信息表（t_employ）中所有部门为“人事部”的员工信息都被删除。通过下面的代码再次查询数据表中的数据信息。

SELECT * FROM t_employ

代码执行以后，执行结果如图3.20所示。

图3.20  通过视图删除数据后t_employ表中的数据信息

3.7 删除视图—DROP VIEW

使用DROP VIEW语句可以删除视图。

【语法说明】

删除视图的语法格式如下所示。

DROP VIEW  view_name

【上机实战】

删除本章中创建的视图EMP_VIEW，其实现的代码如下所示。

DROP VIEW EMP_VIEW

注：文中字段大小写需根据表中要求创建！当然！习惯上一般都是用小写！

---

---

——文章收藏的！分享出去！若侵犯请联系删除