【Oracle学习】之 操作表中数据

一、添加数据

insert into userinfo values(1, 'xxx', '123', 'xxx@126.com', sysdate);
//注：sysdate是获取当前日期
//注意：即使是默认值，也不能空着 没有足够的值


//向表中指定字段如添加值：
insert into userinfo(id,username,userpwd)values(2,'yyy','123');

二、复制数据

-在建表时复制
CREATE TABLE table_new AS SELECT column1,…|*FROM table_old

create table userinfo_new as  select * from userinfo;
create table userinfo_new1 as select id, username from userinfo;

-在添加时复制
INSERT INTO table_new [(column1,…)] SELECT column1, …|*FROM table_old
后面的表要是已经存在的！

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

三、修改数据

UPDATE table_name SET column1=value1,…[WHERE conditions]

//无条件更新
update userinfo set userpwd=111111;
select userpwd from userinfo;

//有条件更新
update userinfo set userpwd='123456' where username='xxx';

四、删除数据

//删除所有数据,效率没trancate高
DELETE FROM table_name;
//有条件删除
DELETE FROM table_name [where conditions];