php表中数据 1后更新,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表中的数据 -

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

以下UPDATE语句更改ID为3的零件的成本：

UPDATE

parts

SET

cost = 130

WHERE

part_id = 3;

要验证更新，请使用以下查询：

SELECT

*

FROM

parts

WHERE

part_id = 3;

执行上面查询语句，得到以下结果 -

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

以下语句更新ID为6的零件的前置时间，成本和状态。

UPDATE

parts

SET

lead_time = 30,

cost = 120,

status = 1

WHERE

part_id = 6;

要验证更新，请使用以下查询：

SELECT

*

FROM

parts

WHERE

part_id = 6;

执行上面查询语句，得到以下结果 -

3. Oracle UPDATE - 更新多行示例

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

UPDATE

parts

SET

cost = cost * 1.05;

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

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

¥ 我要打赏

纠错/补充

收藏

加QQ群啦，易百教程官方技术学习群

注意：建议每个人选自己的技术方向加群，同一个QQ最多限加 3 个群。