Home  >  Article  >  Database  >  How to add data in oracle

How to add data in oracle

青灯夜游
青灯夜游Original
2022-01-07 18:01:428763browse

In Oracle, you can use the "INSERT...VALUES" statement to add data, the syntax "INSERT INTO data table name (field name 1, field name 2...) VALUES (data value 1, data value 2 ...)".

How to add data in oracle

The operating environment of this tutorial: Windows 7 system, Oracle 11g version, Dell G3 computer.

Oracle inserts data (adds data)

1. Create a table and specify the structure

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. Insert data

Standard usage

INSERT INTO 数据表名 (字段名1,字段名2...) VALUES(数据值1, 数据值2...)

For example:

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');

You can find that Oracle can intelligently identify numbers even if they are enclosed in quotation marks.

You can also omit the column name declaration after the table when there is a one-to-one correspondence between the current and the following:

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

When the column name declaration is omitted, an error will be reported if the before and after types do not correspond or if the information is missing.

When specifying a column name, columns with unspecified values ​​are filled with null by default:

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');

How to add data in oracle

You can also specify default values ​​for columns while creating the table structure. , automatically filled in when there is no corresponding input:

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);

can be obtained:

How to add data in oracle

##3. Can be used with subquery

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

How to add data in oracle

STUINFO construction general command:

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');

Recommended tutorial: "

Oracle Tutorial"

The above is the detailed content of How to add data in oracle. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn