数据库教程-Oracle更新表数据

跨零代码为大家提供高品质的解决方案,请大家多多来访,跨零不胜感激,在此谢过。

在本教程中将学习如何使用Oracle UPDATE语句来更改表中的已存在值。

Oracle UPDATE语句简介

要更改表中已存在的值,请使用以下Oracle UPDATE语句:

UPDATE     table_name SET     column1 = value1,     column2 = value2,     column3 = value3,     ... WHERE     condition; 

Oracle UPDATE示例

下面让我们来创建一个包含示例数据的新表。

首先,下面的CREATE TABLE语句创建一个名为parts的新表:

-- oracle 12c的写法 CREATE TABLE parts (   part_id NUMBER GENERATED BY DEFAULT AS IDENTITY,   part_name VARCHAR(50) NOT NULL,   lead_time NUMBER(2,0) NOT NULL,   cost NUMBER(9,2) NOT NULL,   status NUMBER(1,0) NOT NULL,   PRIMARY KEY (part_id) );  -- oracle 11g的写法 drop sequence parts_seq;  create sequence parts_seq  increment by 1  start with 1  maxvalue 9999999999  nocache;  drop table parts; CREATE TABLE parts (   part_id NUMBER,   part_name VARCHAR(50) NOT NULL,   lead_time NUMBER(2,0) NOT NULL,   cost NUMBER(9,2) NOT NULL,   status NUMBER(1,0) NOT NULL,   PRIMARY KEY (part_id) ); 

其次,以下INSERT语句将示例数据添加到parts表中:

-- oracle 11g的插入数据写法 INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'sed dictum',5,134,0); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'tristique neque',3,62,1); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'dolor quam,',16,82,1); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'nec, diam.',41,10,1); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'vitae erat',22,116,0); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'parturient montes,',32,169,1); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'metus. In',45,88,1); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'at, velit.',31,182,0); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'nonummy ultricies',7,146,0); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'a, dui.',38,116,0); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'arcu et',37,72,1); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'sapien. Cras',40,197,1); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'et malesuada',24,46,0); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'mauris id',4,153,1); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'eleifend egestas.',2,146,0); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'cursus. Nunc',9,194,1); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'vivamus sit',37,93,0); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'ac orci.',35,134,0); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'arcu. Aliquam',36,154,0); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'at auctor',32,56,1); INSERT INTO parts (part_id,part_name,lead_time,cost,status) VALUES (parts_seq.nextval,'purus, accumsan',33,12,1); 

Oracle 12c 插入数据语句 –

-- oracle 12c写法 INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('sed dictum',5,134,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('tristique neque',3,62,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('dolor quam,',16,82,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('nec, diam.',41,10,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('vitae erat',22,116,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('parturient montes,',32,169,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('metus. In',45,88,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('at, velit.',31,182,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('nonummy ultricies',7,146,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('a, dui.',38,116,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('arcu et',37,72,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('sapien. Cras',40,197,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('et malesuada',24,46,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('mauris id',4,153,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('eleifend egestas.',2,146,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('cursus. Nunc',9,194,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('vivamus sit',37,93,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('ac orci.',35,134,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('arcu. Aliquam',36,154,0); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('at auctor',32,56,1); INSERT INTO parts (part_name,lead_time,cost,status) VALUES ('purus, accumsan',33,12,1); 

现在,查询上面创建的parts表中的数据 –

Oracle更新表数据

1. Oracle UPDATE – 更新单个行的一列

以下UPDATE语句更改ID3的零件的成本:

UPDATE     parts SET     cost = 130 WHERE     part_id = 3; 

要验证更新,请使用以下查询:

SELECT     * FROM     parts WHERE     part_id = 3; 

执行上面查询语句,得到以下结果 –

Oracle更新表数据

2. Oracle UPDATE – 更新单个行的多个列

以下语句更新ID6的零件的前置时间,成本和状态。

UPDATE     parts SET     lead_time = 30,     cost = 120,     status = 1 WHERE     part_id = 6; 

要验证更新,请使用以下查询:

SELECT     * FROM     parts WHERE     part_id = 6; 

执行上面查询语句,得到以下结果 –

Oracle更新表数据

3. Oracle UPDATE – 更新多行示例

以下声明增加了parts表中所有零件的成本5%

UPDATE     parts SET     cost = cost * 1.05; 

执行上面查询语句后,再次查询每个零件的成本 –

Oracle更新表数据

在本教程中,您已学习如何使用Oracle UPDATE语句更改表中的现有值。

  

从零到一,创造未来!跨零综合IT问题解决服务站,欢迎你的到来。Oracle数据库教程 只为你绽放。

本文固定链接: http://kua0.com/2019/01/17/数据库教程-oracle更新表数据/

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注