4 Oracle 操作表中数据

流云追风

2020-06-18

关注关注

添加数据

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