1 insert new record

Insert a new record into the table.
For example, to add a new piece of data to a user table, you can write

insert into t_user (user_id,user_name,user_age,user_address,user_phone)
values(32,'a tiny tot',18,"No.1 yard, Longcheng Street",192303023994)

In MySQL and DB2, you can choose to insert one row at a time or multiple rows at a time

insert into t_user (user_id,user_name,user_age,user_address,user_phone)
values
(32,'a tiny tot',18,"No.1 yard, Longcheng Street",192303023994)，
(33,'Little plum',20,"No.2 yard, Longcheng Street",192303023995)

The insert statement allows you to create new rows in database tables. In all types of database systems, the syntax of the insert statement is exactly the same. Of course, there is a simple way to write it

insert into t_user 
values('Shanxi Province','a tiny tot',"No.1 yard, Longcheng Street",18,32,192303023994,)

Note that if the target fields in the inserted row are not listed, all columns in the table must be inserted.

2 insert default

When defining a table, you can define default values for some columns. Now you want to insert a row with the default values without specifying the values for each column.

2.1 specifying column defaults when creating tables

creat table t_user (user_id default 0)

All database systems support using the default keyword to explicitly specify a column insertion value. Some databases also support other methods to solve this problem

2.2 use default values when inserting data

insert into t_user (user_id,user_name,user_age,user_address,user_phone)
values(32,'a tiny tot',18,default,default)

When you do not need to insert all the columns in the table into the values, you can explicitly specify the column names to use the default values, such as the user address written above, and the default used by user phone

In MySQL, if all columns in the table have default values defined, you can use an empty value to solve the problem

insert into t_user values()

In this case, all columns will be set to their default values

Support default values clause in PostgreSQL and SQL Service

insert into t_user default values 

The default values clause sets all columns to their own default values.

The default keyword in the value list inserts the default value for the corresponding column. The default value is defined when creating the table. All databases can use some keywords.

3 use NULL instead of default

Insert data in a column with a default value defined, and set the value to NULL regardless of the default value of the column

create table t_user (user_id defalut 0,user_flag varchar(100))

Now insert a piece of data

insert into t_user (user_id,user_flag) values (null,"Today, I had a heart to heart exchange")

In the above insert statement, null is explicitly specified, but in the actual development, not all people know that null value can be explicitly specified in the insert statement. All common practices are that when inserting data, when a column of data does not need to be inserted, a column will not be specified. For example, the above insert null can be modified to

insert into t_user (user_flag) values ("Today, I had a heart to heart exchange")

4 copy rows from one table to another

Copy the data in one table to another. First, query the data in the first table, and then copy it. In this process, query the data may be complex or simple, but the final result is copy
For example, copy the data in table t ﹣ user to table t ﹣ dep ﹣ user

insert into t_dep_user (dep_user_name,dep_user_flag)
select user_name,user_flag from t_user 

The method used here is to follow the insert statement with a query to return the desired row.

5 copy rows from one table to multiple tables

Different from the above, query the data from one table, and then copy the data to multiple tables, for example, copy the data from table t user to T User 2, t user 3, t user 4:

This can be written in Oracle

insert all 
    when user_age < 18 then 
        into t_user_2(user_name,user_age,user_flag) values (dep_user_name,dep_user_age,dep_user_flag)
    when user_age >60 then 
        into t_user_3(user_name,user_age,user_flag) values (dep_user_name,dep_user_age,dep_user_flag)
    else 
        into t_user_4(user_name,user_age,user_flag) values (dep_user_name,dep_user_age,dep_user_flag)
    select dep_user_name,dep_user_age,dep_user_flag from t_user 

All targets can be joined with union all in DB2

///Create table first
create table t_user_2(user_name varchar(10),user_age integer check (user_age <18),user_flag varchar(44))
create table t_user_3(user_name varchar(10),user_age integer check (user_age >=18 and user_age <=60),user_flag varchar(44))
create table t_user_4(user_name varchar(10),user_age integer check (user_age >60),user_flag varchar(44))

///Insert data 

insert into (
 select * from t_user_2 union all
 select * from t_user_3 union all
 select * from t_user_4 
) select * from t_user 

6 copy table structure

In practice, if you want to create a new table with the same structure settings as the existing table, you can consider copying only the table structure
In DB2, use the create table command with the like clause

create table t_user_2 like t_user

In Oracle, MySQL, and PostgreSQL, use a subquery that does not return any rows

create table t_user_2 as select * from t_user where 1=0

Here, where 1=0 causes the query to return no rows, so the final created table is an empty table generated according to the column name in the select clause of the query

Complete