4 Oracle 操作表中数据

添加数据

INSERT语句

INSERT INTO table_name

（columnl, column2，）

VALUES（valuel, value2…）

操作实例

SQL> desc userinfo

Name     Type         Nullable Default Comments

-------- ------------ -------- ------- --------

ID       NUMBER(6)    Y                        

USERNAME VARCHAR2(20) Y                        

USERPWD  VARCHAR2(20) Y                        

EMAIL    VARCHAR2(30) Y                        

REGDATE  DATE         Y 

向表中所有字段添加值

SQL> insert into userinfo  values(1,‘lewen‘,‘123‘,‘‘,sysdate);

1 row inserted

SQL> select * from userinfo;

     ID USERNAME             USERPWD              EMAIL                          REGDATE

------- -------------------- -------------------- ------------------------------ -----------

      1 lewen                123                                    2020/6/17 2

向表中指定字段添加值

SQL> insert into userinfo(id,username,userpwd) values(2,‘fadewalk‘,123);

1 row inserted

SQL> select username,userpwd from userinfo;

USERNAME             USERPWD

-------------------- --------------------

lewen                123

fadewalk             123

向表中添加默认值

SQL> create table userinfo1(id number(6,0),regdate date default sysdate);

Table created

SQL> select username,userpwd from userinfo;

USERNAME             USERPWD

-------------------- --------------------

lewen                123

fadewalk             123

SQL> create table userinfo1(id number(6,0),regdate date default sysdate);

Table created

SQL> insert into userinfo1(id) values(1);

1 row inserted

SQL> select * from userinfo1;

     ID REGDATE

------- -----------

      1 2020/6/17 2

SQL> alter table userinfo modify email default ‘NULL‘;

Table altered

SQL> insert into userinfo(id) values(3);

1 row inserted

SQL> select id,email from userinfo;

     ID EMAIL

------- ------------------------------

      1

      2

      3 NULL

SQL> insert into userinfo(id,email) values(4,‘‘);

1 row inserted

SQL> select id,email from userinfo;

     ID EMAIL

------- ------------------------------

      1

      2

      3 NULL

      4

复制表数据

在建表时复制

CREATE TABLE table_new

AS

SELECT column1,....| *FROM table_old

操作实例

SQL> select id,email from userinfo;

     ID EMAIL

------- ------------------------------

      1

      2

      3 NULL

      4

SQL> create table userinfo_new as select * from userinfo;

Table created

SQL> desc userinfo_new

Name     Type         Nullable Default Comments

-------- ------------ -------- ------- --------

ID       NUMBER(6)    Y                        

USERNAME VARCHAR2(20) Y                        

USERPWD  VARCHAR2(20) Y                        

EMAIL    VARCHAR2(30) Y                        

REGDATE  DATE         Y   

SQL> create table userinfo_new_1 as select id,username from userinfo;

Table created

SQL> select * from userinfo_new_1;

     ID USERNAME

------- --------------------

      1 lewen

      2 fadewalk

      3

      4

在添加时复制

INSERT INTO table_new

[(column1,.....）

SELECT columnl,....| *FROM table_old

操作实例

SQL> insert into userinfo_new select * from userinfo;

4 rows inserted

SQL> select id from userinfo_new;

     ID

-------

      1

      2

      3

      4

      1

      2

      3

      4

8 rows selected

SQL> insert into userinfo_new(id,username) select id,username from userinfo;

4 rows inserted

SQL> select id,username from userinfo_new;

     ID USERNAME

------- --------------------

      1 lewen

      2 fadewalk

      3

      4

      1 lewen

      2 fadewalk

      3

      4

      1 lewen

      2 fadewalk

      3

      4

12 rows selected

修改数据

UPDATE语句

UPDATE table_name

SET columnl=value1,.....

[WHERE conditions]

操作实例

SQL> desc userinfo

Name     Type         Nullable Default Comments

-------- ------------ -------- ------- --------

ID       NUMBER(6)    Y                        

USERNAME VARCHAR2(20) Y                        

USERPWD  VARCHAR2(20) Y                        

EMAIL    VARCHAR2(30) Y        ‘NULL‘          

REGDATE  DATE         Y                        

无条件更新

SQL> update userinfo set userpwd=‘1234‘;

4 rows updated

SQL> update userinfo set userpwd=‘1111‘,email=‘‘;

4 rows updated

SQL> select userpwd,email from userinfo;

USERPWD              EMAIL

-------------------- ------------------------------

1111                

1111                

1111                

1111                

有条件更新

SQL> update userinfo set userpwd=‘123456‘ where username=‘lewen‘;

1 row updated

SQL> select userpwd,email from userinfo;

USERPWD              EMAIL

-------------------- ------------------------------

123456              

1111                

1111                

1111                

删除数据

DELETE语句

DELETE FROM table_name

[WTHERE conditions]

操作实例

无条件删除

SQL> create table testdel as select * from userinfo;

Table created

SQL> delete from testdel;

4 rows deleted

有条件删除

SQL> delete from userinfo where username=‘fadewalk‘;

1 row deleted