在oracle中，可以利用“INSERT…VALUES”語句來增加數(shù)據(jù)，語法“INSERT INTO 數(shù)據(jù)表名 (字段名1,字段名2…) VALUES(數(shù)據(jù)值1, 數(shù)據(jù)值2…)”。

本教程操作環(huán)境：Windows7系統(tǒng)、Oracle 11g版、Dell G3電腦。

Oracle插入數(shù)據(jù)（增加數(shù)據(jù)）

1、創(chuàng)建表格并指定結(jié)構(gòu)

CREATE TABLE DB3.STUINFO( STUID INT, STUNAME VARCHAR(10), SEX INT, AGE INT, CLASSNO VARCHAR(10), STUADDRESS VARCHAR(10), GRADE INT, ENROLDATE DATE, IDNUMBER VARCHAR(20));

2、插入數(shù)據(jù)

規(guī)范用法

INSERT INTO 數(shù)據(jù)表名 (字段名1,字段名2...) VALUES(數(shù)據(jù)值1, 數(shù)據(jù)值2...)

如：

INSERT INTO DB3.STUINFO (STUID, STUNAME, SEX, AGE, CLASSNO, STUADDRESS, GRADE, ENROLDATE, IDNUMBER) values ('1', '龍七', '1', 26, 'C201801', '廈門市', '2018', to_date('01-09-2018', 'dd-mm-yyyy'),  '3503021992XXXXXXXX');

可以發(fā)現(xiàn)數(shù)字即便加上引號(hào)，Oracle也能智能識(shí)別。

當(dāng)前后一一對(duì)應(yīng)時(shí)也可省略table后的列名聲明：

INSERT into DB3.STUINFO  values (2, '龍八', 1, 25, 'C201801', '廈門市', '2018', to_date('01-09-2018', 'dd-mm-yyyy'), '3503021993XXXXXXXX');

省略列名聲明的情況下，前后類型不對(duì)應(yīng)或缺失信息時(shí)將報(bào)錯(cuò)。

指定列名時(shí)，未指定值的列默認(rèn)填充為null：

INSERT into DB3.STUINFO (STUID, STUNAME, SEX, AGE, STUADDRESS, GRADE, ENROLDATE, IDNUMBER) values (2, '龍八', 1, 25, '廈門市', '2018', to_date('01-09-2018', 'dd-mm-yyyy'), '3503021993XXXXXXXX');

也可在創(chuàng)建表結(jié)構(gòu)的同時(shí)為列指定默認(rèn)值，當(dāng)無對(duì)應(yīng)輸入時(shí)自動(dòng)填充：

CREATE TABLE a ( id INT, name CHAR(10) default 'a', class INT NOT NULL );

INSERT INTO a VALUES (1,NULL,1);   INSERT INTO a (id,class) VALUES (1,1);

可得到：

3、可與子查詢合用

INSERT INTO a(id,class) VALUES ((SELECT STUID FROM STUINFO WHERE STUNAME='龍七'),2);

STUINFO構(gòu)建總命令：

CREATE TABLE DB3.STUINFO( STUID INT, STUNAME VARCHAR(10), SEX INT, AGE INT, CLASSNO VARCHAR(10), STUADDRESS VARCHAR(10), GRADE INT, ENROLDATE DATE, IDNUMBER VARCHAR(20));   INSERT into DB3.STUINFO (STUID, STUNAME, SEX, AGE, CLASSNO, STUADDRESS, GRADE, ENROLDATE, IDNUMBER) values ('1', '龍七', '1', 26, 'C201801', '廈門市', '2018', to_date('01-09-2018', 'dd-mm-yyyy'),  '3503021992XXXXXXXX');    INSERT into DB3.STUINFO  values (2, '龍八', 1, 25, 'C201801', '廈門市', '2018', to_date('01-09-2018', 'dd-mm-yyyy'), '3503021993XXXXXXXX');  INSERT into DB3.STUINFO (STUID, STUNAME, SEX, AGE, STUADDRESS, GRADE, ENROLDATE, IDNUMBER) values (2, '龍八', 1, 25, '廈門市', '2018', to_date('01-09-2018', 'dd-mm-yyyy'), '3503021993XXXXXXXX');  INSERT into DB3.STUINFO (STUID, STUNAME, SEX, AGE, STUADDRESS, GRADE, ENROLDATE, IDNUMBER) values (3, '龍九', 2, 23, '三門市', '2017', to_date('01-09-2017', 'dd-mm-yyyy'), '3503041995XXXXXXXX');  INSERT into DB3.STUINFO (STUID, STUNAME, SEX, AGE,CLASSNO, STUADDRESS, GRADE, ENROLDATE, IDNUMBER) values (3, '龍九', 2, 23, 'C201702', '三門市', '2017', to_date('01-09-2017', 'dd-mm-yyyy'), '3503041995XXXXXXXX');  INSERT into DB3.STUINFO (STUID, STUNAME, SEX, AGE,CLASSNO, STUADDRESS, GRADE, ENROLDATE, IDNUMBER) values (4, '龍十', 2, 23, 'C201702', '三門市', '2017', to_date('01-09-2017', 'dd-mm-yyyy'), '3503041995XXXXXXXX');

推薦教程：《Oracle教程》