资料 SQL语言(5)

(三) 更新表中的数据

使用UPDATE命令可更新或修改表中的数据(字段值)，命令形式如下： UPDATE 表名 [别名]

SET 列名={表达式|常量|列名}[，列名={表达式|常量|列名}]...... [WHERE 逻辑表达式];

在这里，SET子句决定了需要修改的列和修改成的值。WHERE子句是可选的，形式与SELECT命令中的WHERE子句一样，它决定了需要更新的行。如果不选WHERE子句，则表中所有的记录行全被更新;如果规定了WHERE子句，则使WHERE子句中逻辑表达式为真的行被更新。

表名的别名也是可选的，它常被用在set子句和WHERE子句的相关子查询。 例8：把职工徐雪飞调为小车驾驶员，并且工资提高10% UPDATE people

SET job = '小车驾驶员'， salary=1.1*salary WHERE peopname='徐雪飞';

例9：把工资低于120元的推销员和采购员的工资增加15% UPDATE people

SET salary=salary*1.15

WHERE job in ('采购员'，'推销员') and salary<120;

例10：把财务处及其下属单位(编号为P1%)的所有人员的工资增加10% UPDATE people

SET salary=salary * 1.10 WHERE deptcode like 'P1%'；

有时，SET子句中的表达式也可以是一个子查询， 例11：使所有职务为处长的工资为其平均工资的1.1倍 UPDATE people

SET salary=(select 1.1*avg(salary)from people where job ='处长') WHERE job='处长';

有时，带有子查询的SET子句可写为： SET (列名，列名， ...)=(子查询)

例12：使所有推销员的工资和奖金都比平均数增加10%： UPDATE people

SET (salary，award)=(SELECT 1.1*avg(salary)，1.1*avg (award) FROM people

WHERE job='推销员')， WHERE job='推销员';

四、数据定义(DDL)

本节主要描述建立和修改数据模型的各条命令，它们是：CREATE、DROP、ALTER和RENAME命令。

(一) 表的建立，修改和删除

1、建立表

在数据库中，数据是按表的形式组织的，所有信息都是贮存在表中，表与表之间的联系是通过表中的某些列来体现的。只有建立了表，才可以往表中加入数据。

为建立一个表，用户必须首先确定：表的名字、表中各列的名字、各列的数据类型和宽度以

及其它一些可选的信息。

表的名字和列的名字必须遵循ORACLE数据库客体命名的标准规则，列的数据类型可以是某一种合法的数据类型。列的最大宽度应根据实际情况而定(数值型的最大宽度包括小数部分和整数部分)，对于会自动地按照字段的实际值而不是定义长度分配存贮空间的数据库系统不必太苟刻。

有时对一列禁止空值是很有用的，例如，要保证人员表中的每一行确确实实对应一个人，就应该禁止人员编号和人员姓名为空，这样任何人如果试图往人员表中加入人员编号和姓名为空的行时，数据库将显示错误信息。在列信息(数据类型及长度)的后面输入not null就可禁止列中空值。

建立表用CREATE TABLE命令，该命令最简单的形式如下： CREATE TABLE 表名 (列名 列描述，......); 其中列描述指定该列的数据类型和宽度。 例1：建立存放关于人员信息的表people CREATE TABLE people

(peopcode number (2)， peopname char (8)， sex char (2)， birthday date， workdate date， job char(10)，

salary mumber(5，2)， award mumber(5，2)， deptcode char(4));

如果要让peopcode和peopname这两列为非空值，则应输入： CREATE TABLE people

(peopcode number(2) not null， peopname char (8) not null，

┋ ┋

deptcode char (4));

2、修改表的定义

当需要贮存的信息发生变化时，用户可能需要改变原先表的定义，这就必须要修改表。修改表定义必须使用ALTER TABLE命令。

(1) 使用带有MODIFY子句的ALTER TABLE命令可以改变一个表中存在列的定义，它可以改变的数据类型及其宽度，也可以把列变为允许空值或不允许空值。该语句的形式如下：?

ALTER TABLE 表名

MODIFY (列名 新的列描述，......);

如果同时要改变多个列，可以像建表时那样，用逗号(，)把各列分开。

例2：由于五金批发公司的发展，职工已超过一百，所以必须把peopcode的宽度增加，同时也要求把sex列变为非空(不允许空值)，输入： ALTER TABLE people

MODIFY (peopcode number(3) not null, sxe char(2) not null);

注意：若要把某列变为不允许空值，则应首先把已存在的行的该列赋值。若要改变列的类型

或减小其宽度，此列必须全为空值。 (2) 增加列

使用带有ADD子句的ALTER TABLE命令可以把一新列增加到数据库中已存在的表中，该命令的形式如下：

ALTER TABLE 表名 ADD (新列名 列描述，......);

同样，如果想同时增加多列，也必须用逗号(，)把各列隔开。

例3：由于我国实行了邮政编码，所以我们应该在相关单位表(co-unit)中增加邮 政编码列，

ALTER TABLE co_unit ADD (postcode unmber(6));

注意：除非表中没有行，一般不能把新增加的列定义为not null。但可用下面三步来增加一个不允许空值的列：首先增加列(不加not null);然后给列中每个字段赋一非空的值;最后用带有MODIFY子句的ALTER TABLE命令把该列改为not null。

3、删除表

当某些表已经没有用时，就应该把它们从ORACLE数据库中删除掉。 用DROP TABLE命令可删除一个表，该命令的形式如下： DROP TABLE 表名;

例4：删除名为project的表， DROP TABLE project;

注意：当删除表时，SQL*Plus自动地删除建立在此表上的索引(而不管它是由该用户或由其它用户所建立的)以及对该表上的特权。尽管DROP TABLE命令不能删除引用该表的各视图和同义词，但它们已成为无效的了，对于这些视图和同义词应当删除或重新定义它们，或者重新定义另一些表或视图，以使这些视图和同义词重新成为有效。

删除表时，表中的数据也全部被删除。如果需要保留表中的数据以作它用，应首先用实用程序把数据卸出，然后再删除表。 (二) 使用视图

1、视图的概念

视图看起来像一个表，有它自己的行和列，并且也确可把它当作表来对待(当然会有一些限制)。但视图没有属于自己的数据，也不占存贮空间，所以视图有时又被称作为“虚表”。视图也像个窗口，用户可以通过它来观察或修改实表中的数据。 视图主要以下几种用途，它们是：

* 简化数据查询：通过建立视图，用户常常可以不必使用复杂的查询，而只用一简单查询就可得到同样的数据。

* 提供数据的安全保密性：通过建立视图，我们可以用视图来授权，而不必用表来进行授权，从而达到数据保密的作用。 * 提供数据独立性：一旦建立了视图，使用视图的人则无需知道他们所使用的数据来自的表，这样，视图把用户与他所使用的数据所实际存贮着的表分开了，用户也看不到他所使用视图中的数据是怎样贮存的。这种分离可以把用户对数据模型所作的改变与已有的查询，已有的程序等分离开，这样当数据模型改变时，已开发的程序可以保持不变。这就是所谓的数据独立性，这种独立性将大大地节省用户在系统维护上所花费的代价。

2、建立视图

因为任何一个查询结果其本身就是一个表，所以一个查询可被用于定义一个视图。使用CREATE VIEW命令可以建立视图，命令形式如下： CREATE VIEW 视图名 AS 查询;

这里：视图名是任何合法的数据库客体名。

AS子句后的是任何一个不包含ORDER BY子句的有效的查询。

例5：建立一个公司顾客的视图customer，该视图是由co-unit表中，unitcode列以C开头的那些行，输入：

CREATE VIEW customer

AS SELECT * FROM co_unit WHERE unitcode like 'C%';

例6：建立一个公司内部具有处长职务的职员视图offico_head CREATE VIEW office_head

AS SELECT * FROM people WHERE job='处长';

由于视图本身相当于一个表，所以在定义视图的查询中，甚至可以使用另一个视图。 3、使用视图

使用视图应遵循以下一些规则：

(1)在查询操作中，视图可以与表同样地对待; 例7：查询顾客视图，列出所有的顾客，输入： SELECT * FROM customer;

(2)如果要通过视图删除表中的行，这些视图必须满足：条件一，仅从一个表中选取行;条件二，视图定义中不包括DISTINCT子句、GRDUP BY子句、组函数、伪列等。

(3)如果要通过视图更新表中的行，则这些视图必须满足：条件一，必须是仅从一个表中选取行；条件二，视图定义中不包含DISTINCT子句，GROUP BY子句、 组函数、伪列等；条件三，视图定义中不包含有表达式的列。

(4)如果要通过视图向表中插入行，则这些视图必须满足：条件一，视图必须仅从一个表中选取行；条件二，视图定义中不能包含DISTINDT子句，GROUP BY子句、 组函数、伪列；条件三，视图定义中不包含有表达式的列;条件四，在基表中定义的任何not null列都必须出现在视图中。

如果在建立视图时使用了WITH CHECK OPTION选项，则SQL将自动地进行检查，防止利用该视图的插入和更新而产生由该视图不能查询到的数据。例如，当用户在对customer视图进行插入或更新时，如果SQL发现提交的行中unitcode字段的值不是以字母Ｃ开头的话，则拒绝操作，并给出错误信息。

4、视图的删除

如果某个视图已没有什么用了，则可利用DROP VIEW命令进行删除，该命令的形式如下：DROP VIEW 视图名;

例8：删除公司中职务为处长的职员的视图office_head。 DROP VIEW office_head;

与表不同，视图不能修改，如果想修改视图，只能先把它删除，然后重新定义。 (三) 使用索引加快查询速度

1、索引的概念和用途

如果一本书没有目录，使用起来就很不方便，若读者想寻找某些内容时，就须以书的前面开始，遂页向后查找，直到找到为止。若有了目录，则读者可先从目录中找到这些内容所在的页码，然后直接翻到该页阅读。

表的索引就像书的目录一样，如果用户希望在表中查找某些行，可能的话，SQL可以先查找索引，确定这些行存贮的位置， 然后直接从该位置上取出所需要的行。假如没有索引，则SQL只有扫描整个表，把这个表中的行依次地比较，以找出所需要的行。当表很大时(超过几百行)，在表上建立索引，能大大地减少SQL执行查询的时间。在进行表连接查询的操

作中，如果对连接列建立索引，则也可大大地改善连接查询的速度。

除此以外， 建立索引也能确保表中每行的某个字段或若干字段组合的值是唯一的，这种索引称为唯一性索引。

例如： PEOPLE 表中的 PEOPCODE 列唯一地标识出一个人；out_item表中的head_code列和goods_code列组合起来也能唯一地标识出一项提货。如果在这些表上对这些列建立唯一性索引，那末，若在建立索引前表中已有两行具有相同的索引列值，则在建立索引时就会给出错误信息;索引建立后，若插入或修改表时使两行具有相同索引列值时，也会给出错误信息。当然，建立索引也要占用存贮空间。

另外要说明的是：尽管索引是由SQL建立的，但索引本身是数据库的特性，所以索引也将在其它数据库外围产品中发挥作用。

2、索引的建立

表的主人或任何对该表被授予了INDEX特权的用户都可在该表上建立索引。CREATE INDEX命令用于建立索引，该命令的形式如下： CREATE [unique] INDEX 索引名 ON 表名(列名1，列名2......);

unique选项能确保该表绝对不会有多个记录行在索引列具有相同的值，如果unique省略则表示该表可以含有任意个相同索引列值的记录行； 下面是几个建立索引的例子： 例9：建立人员编号的唯一索引。 CREATE unique INDEX index1 ON people(peopcode);

例10：建立人员所在部门的索引 CREATE INDEX index2 ON people(deptcode);

例11：建立提货项目表中提货单编号和物资编号的唯一索引 CREATE unique INDEX index3

ON out_items(head_code，goodscode)；

一般来说，用户应该对WHERE子句中最常使用的列建立索引，绝对没有必要对只是显示其值的列建立索引。只要可能，尽量将索引列定义为not null。

表越大，索引越能有效地改善查询的响应时间。一般说来， 如果一个表有几百行或更多的数据，就应该建立索引，而对少于一百行的数据建立索引可能不太合算。

一个表可以建立任意多个索引，但是索引越多，占用存贮空间也越多，并且在对该表进行插入或更新时为自动维护索引所需要的时间开销也就越大，为此应当在查找速度和插入更新速度之间进行权衡。在空间较富裕，且对于一个仅仅用来查询的表，较多的索引可能是合理的;但对于一个插入更新较频繁的表，则索引少一些更为合适。通常，不要在一个表上建立多于三个索引。 还需说明的是：一个索引可能使用多个索引列，在第一个索引列中具有相同值的记录行按第二个索引列来索引，依次类推。所以在WHERE 子句中如果使用的是较后面的索引列，则改善效果可能不是很明显。

3、删除不需要的索引

对已不需要的索引，可以用DROP INDEX命令来删除它，该命令的形式如下： DROP INDEX 索引名 [ON 表名];

ON 子句是任选的，它导致索引与表名的核实。

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库资料 SQL语言(5)在线全文阅读。