input data with SQL

The data entry adopts the "Insert" statement. The syntax of the "insert" statement varies according to the different input methods.
Entry of 4.6.1 single record
1. Grammar
Insert into datasheet (field name 1, field name 2,......) VALUES (Value of field name 1, Value of field Name 2,......).
Because of the different types of fields, be aware of formatting when writing field values.
A numeric field that can be directly written to a value.
A character field whose value is preceded by a single quotation mark.
A date field with a single quotation mark on its value, along with the Order of the year, month, and day.
In the INSERT statement of the data, the Insertion column sort and insert values correspond to each other. Character and date fields are enclosed in single quotes, and Non-null columns must have a value.
2. Examples
A total of 3 types of fields are included in the Scott.emp datasheet.
Empno,number (4), NOT NULL, numeric type, length 4, cannot be empty.
ENAME,VARCHAR2 (10), character type, length 10.
Hiredate,date, date type.
Let's take an example of inserting records in these 3 fields.
For date-type data, readers often feel embarrassed, because do not know the year, month, day of the order and format, here to teach you a few ways. First query the data in the sample data table, and then "according to gourd painting scoop" on it.
(1) in the command edit area, enter select Empno, ename, HireDate from Scott.emp, and then click the Execute button to see the results shown in Figure 4.41. Therefore, the author's computer system default date data format should be "day-month-year".
"See CD-ROM File": \ 4th Chapter \4.6\461-1.sql.

(2) in the command editing area, enter insert into Scott.emp (empno, ename, HireDate) VALUES (7999, ' Jone ', ' 2 May-November-2002 '); ", and then click the" Execute "button, The result appears as shown in Figure 4.42.
"See CD-ROM File": \ 4th Chapter \4.6\461-2.sql.

(3) in the command edit area, enter the SELECT * from Scott.emp where empno=7999, and then click the Execute button to see the results shown in Figure 4.43.
"See CD-ROM File": \ 4th Chapter \4.6\461-3.sql.

4.6.2 Multi-line record entry
In the data entry, often need to be from the data table to query the data to modify the batch input, this is the entry of multiple lines of data.
1. Grammar
INSERT INTO Data table (field name 1, field name 2,......)
(Select (Field name 1 or operation, field name 2 or operation,......) from data table where condition)
In fact, you first use the subquery statement to query the results, and then use the INSERT statement to insert the results into the datasheet. The data tables in the subquery and insert can be either the same or different, but the fields that require the query results are exactly the same as the field properties in the Insert-inserted datasheet.
2. Examples
In the command edit area, execute the following statement.
―――――――――――――――――――――――――――――――――――――
Insert into Scott.emp (empno,ename,hiredate) (select Empno+100,ename,hiredate from scott.emp where empno>=6999);
―――――――――――――――――――――――――――――――――――――
"See CD-ROM File": \ 4th Chapter \4.6\462.sql.
Click the Execute button to see the results shown in Figure 4.44.

4.6.3 data replication between tables
You can select the data you want from a datasheet into a new datasheet.
(1) Execute the following statement in the command edit area.
―――――――――――――――――――――――――――――――――――――
CREATE TABLE Scott.test
As
(
SELECT DISTINCT Empno,ename,hiredate
From Scott.emp
where empno>=7000
);
―――――――――――――――――――――――――――――――――――――
"See CD-ROM File": \ 4th Chapter \4.6\463.sql.

Then click the Execute button to see the results shown in Figure 4.45.
The function of the above statement is to create a data table named Scott.test that contains 3 fields. The scott.emp has a different empno field, and the empno>=7000 data is copied to the Scott.test datasheet.
(2) in the command edit area, enter the select * from Scott.test; statement, and then click the Execute button to see the result as shown in Figure 4.46.

The CREATE TABLE statement here is a function of creating a new datasheet, which is actually performed in 3 steps. First you query the data that meets your requirements, then you create a 3-field data blank table named Test, and finally insert the query data into the test datasheet.