SQL must-know note the 15th chapter inserts data

15.1 Data Insertion

Inserts can be used in several ways:
(1) Insert the full line
(2) part of Insert Line
(3) Insert the results of some queries

15.1.1 inserting the full line

INSERT INTO CustomersVALUES(‘1000000006‘,     ‘Toy Land‘,     ‘123 Any Street‘,     ‘New York‘,     ‘NY‘,     ‘11111‘,     ‘USA‘,     NULL,     NULL);

You must provide a value for each column, and a null value should be used if a column has no value.
Ensure more secure INSERT statements

INSERT INTO Customers(cust_id,     cust_name,     cust_address,     cust_city,     cust_state,     cust_zip,     cust_country,     cust_contact,     cust_email)VALUES(‘1000000006‘,     ‘Toy Land‘,     ‘123 Any Street‘,     ‘New York‘,     ‘NY‘,     ‘11111‘,     ‘USA‘,     NULL,     NULL);

Because column names are provided, values must match the specified column names in the order in which they are specified, not necessarily in the order in which they are listed in the actual table. The advantage is that this INSERT statement works correctly even if the structure of the table changes.
Always use a list of columns: generally do not use INSERT statements that do not explicitly give a list to the column. using a list of columns allows SQL code to continue to work even if the table structure changes.

15.1.2 Insertion Part Branch

The recommended way to use Insert is to explicitly give the table a column name. With this syntax, you can also omit columns.

INSERT INTO Customers(cust_id,     cust_name,     cust_address,     cust_city,     cust_state,     cust_zip,     cust_country)VALUES(‘1000000006‘,     ‘Toy Land‘,     ‘123 Any Street‘,     ‘New York‘,     ‘NY‘,     ‘11111‘,     ‘USA‘);

Omit columns: If the definition of the table allows, some columns can be omitted from the insert operation. The omitted column must meet one of the following conditions:
(1) The column is defined to allow null values.
(2) The default value is given in the table definition. This means that if a value is not given, the default value will be used.

15.1.3 inserting the retrieved data

Insert can use it to insert the results of a SELECT statement into a table. This is called an insert SELECT.

INSERT INTO Customers(cust_id,     cust_name,     cust_address,     cust_city,     cust_state,     cust_zip,     cust_country)SELECT cust_id,     cust_name,     cust_address,     cust_city,     cust_state,     cust_zip,     cust_countryFROM CustNew;

Does not necessarily require the same column name.
The SELECT statement in the Insert select can contain a WHERE clause to filter the inserted data.
Insert multiple lines: Insert usually inserts only one row. To insert multiple rows, you must execute multiple INSERT statements. An exception to insert select is the ability to insert multiple rows with a single insert, which is inserted by the INSERT, regardless of the number of rows returned by the SELECT statement.

15.2 Copying from one table to another

In order to copy one table to another table (the table created in the run). You can use the SELECT INTO statement.
INSERT Select and Selectinto: One important difference between them is that the former exports the data, while the latter imports the table.

SELECT *INTO CustCopyFROM Customers;

MySQL and Oracle use a slightly different syntax

CREATE TABLE CustCopy ASSELECT *FROM Customers;

When using SELECT INTO, you need to be aware that:
(1) Any select option and clause can be used, including where and group by.
(2) A junction can be used to insert data from multiple tables.
(3) data can only be inserted into a single table, regardless of the number of tables that are retrieved.

SQL must-know note the 15th chapter inserts data