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','lewen@163.com',sysdate);
1 row inserted
SQL> select * from userinfo;
ID USERNAME USERPWD EMAIL REGDATE
------- -------------------- -------------------- ------------------------------ -----------
1 lewen 123 lewen@163.com 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 lewen@163.com
2
3 NULL
SQL> insert into userinfo(id,email) values(4,'kevin@126.com');
1 row inserted
SQL> select id,email from userinfo;
ID EMAIL
------- ------------------------------
1 lewen@163.com
2
3 NULL
4 kevin@126.com
复制表数据
在建表时复制
CREATE TABLE table_new
AS
SELECT column1,....| *FROM table_old
操作实例
SQL> select id,email from userinfo;
ID EMAIL
------- ------------------------------
1 lewen@163.com
2
3 NULL
4 kevin@126.com
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='1111@126.com';
4 rows updated
SQL> select userpwd,email from userinfo;
USERPWD EMAIL
-------------------- ------------------------------
1111 1111@126.com
1111 1111@126.com
1111 1111@126.com
1111 1111@126.com
有条件更新
SQL> update userinfo set userpwd='123456' where username='lewen';
1 row updated
SQL> select userpwd,email from userinfo;
USERPWD EMAIL
-------------------- ------------------------------
123456 1111@126.com
1111 1111@126.com
1111 1111@126.com
1111 1111@126.com
删除数据
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
