一 INSERT语句
INSERT INTO table_name
(column1,column2...)
VALUES(value1,value2,...)
二 实例
1、向表中所有字段添加值
sql> desc userinfo名称是否为空?类型-----------------------------------------------------------------------------ID NUMBER(6)USERNAME VARCHAR2(20)USERPWD VARCHAR2(20)EMAIL VARCHAR2(30)REGDATE DATEsql> insert into userinfo2 values(1,'xxx','123','xxx@qq.com',sysdate);已创建1行。sql>select*from userinfo;ID USERNAME USERPWD--------------------------------------------------EMAIL REGDATE--------------------------------------------1 xxx 123xxx@qq.com 25-6月-17
2、向表中指定字段添加值
3、在创建表时向表中添加默认值
sql> alter table userinfo2 modify email default'无';表已更改。sql> insert into userinfo(id)2 values(3);已创建1行。sql>select id,email from userinfo;ID EMAIL----------------------------------------1 xxx@qq.com23无sql> insert into userinfo(id,email)2 values(4,'aa');已创建1行。email from userinfo;ID EMAIL----------------------------------------1 xxx@qq.com23无4 aa
三 复制表数据
1、在建表时复制
CREATE TABLE table_new
AS
SELECT column1,...|* FROM table_old
复制所有的字段
sql> create table userinfo_new2as3select*from userinfo;sql> desc userinfo_new名称是否为空?类型-----------------------------------------------------------------------------ID NUMBER(6)USERNAME VARCHAR2(20)USERPWD VARCHAR2(20)EMAIL VARCHAR2(30)REGDATE DATEsql>select*from userinfo_new;ID USERNAME USERPWD--------------------------------------------------EMAIL REGDATE--------------------------------------------1 xxx 123xxx@qq.com 25-6月-172 yyy 1233无ID USERNAME USERPWD--------------------------------------------------EMAIL REGDATE--------------------------------------------4aa
复制部分字段
2、在添加时复制
复制时，新表必须已经存在。
INSERT INTO table_new
[(column1,...)]
SELECT column1,...|* FROM table_old
sql> insert into userinfo_new2select*from userinfo;已创建4行。sql>select id from userinfo_new2;ID----------12341234已选择8行。sql> insert into userinfo_new(id,username)2select id ,monospace; margin: 8px 0px;" class="language-sql">已创建4行。sql>select id ,username from userinfo_new;1 xxx2 yyy1 xxx2 yyy3ID USERNAME------------------------------4
