Syntax format
insert into Table name (field name 1, field name 2, field name 3,...) value(Value 1, value 2, value 3,...);
Note: field names and values should correspond one by one, quantities and data types.
Example
The t_persistent table has been created in the creation of the table. The field types in the whole table are as follows:
mysql> desc t_student; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | no | int | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | age | int | YES | | NULL | | | email | varchar(255) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 5 rows in set (0.02 sec)
Now insert a piece of data:
mysql> insert into t_student(no,name,sex,age,email) values(1,'kuangdi','m',29,'7190**87@qq.com');
View data:
mysql> select * from t_student; +----+---------+-----+-----+-----------------+ | no | name | sex | age | email | +----+---------+-----+-----+-----------------+ | 1 | kuangdi | m | 29 | 7190**87@qq.com | +----+---------+-----+-----+-----------------+ 1 row in set (0.03 sec)
Note: once the insert statement is executed successfully, there must be one more record.
Insert another one without assigning values to other fields:
mysql> insert into t_student(no) values(2); Query OK, 1 row affected (0.05 sec) mysql> select * from t_student; +----+---------+------+------+-----------------+ | no | name | sex | age | email | +----+---------+------+------+-----------------+ | 1 | kuangdi | m | 29 | 7190**87@qq.com | | 2 | NULL | NULL | NULL | NULL | +----+---------+------+------+-----------------+ 2 rows in set (0.03 sec)
Specify default values
The default values of t_student above are as follows:
mysql> desc t_student; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | no | int | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | age | int | YES | | NULL | | | email | varchar(255) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 5 rows in set (0.02 sec)
You can see that the default value of all fields is NULL. This is because we did not specify the default value when creating.
After drop ping t_student, we can create it with the following statement:
create table t_student(no int,name varchar(32),sex char(1) default 'm',age int(3),email varchar(255));
Check the default value of each field again:
mysql> desc t_student; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | no | int | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | sex | char(1) | YES | | m | | | age | int | YES | | NULL | | | email | varchar(255) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 5 rows in set (0.03 sec)
You can see that the default value of sex is m.
Field name omitted
Omitting the previous field names means that all field names are written, so the values should also be written.
Examples of errors:
mysql> insert into t_student values(2); 1136 - Column count doesn't match value count at row 1
Correct example:
mysql> insert into t_student values(1,'lili','f',10,'lili@139.com'); Query OK, 1 row affected (0.04 sec) mysql> select * from t_student; +----+------+-----+-----+--------------+ | no | name | sex | age | email | +----+------+-----+-----+--------------+ | 1 | lili | f | 10 | lili@139.com | +----+------+-----+-----+--------------+ 1 row in set (0.03 sec)
Insert date
str_to_date: converts the string varchar type to date type
Syntax format and date format
str_to_date('String date','Date format');
Date format:
%Y year
%m month
%d day
%h Time
%i branch
%s secondCreate a table t_user with a date field:
mysql> create table t_user(id int,name varchar(32),birth date); Query OK, 0 rows affected (0.24 sec) mysql> desc t_user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | birth | date | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.03 sec)
You can see that birth is of type date.
Insert a piece of data:
mysql> insert into t_user(id,name,birth) values(1,'kd','01-10-1992'); 1292 - Incorrect date value: '01-10-1992' for column 'birth' at row 1
An error will be reported when writing like this. You can use str_to_date for type conversion.
Insert correctly:
mysql> insert into t_user(id,name,birth) values(1,'kd',str_to_date('01-10-1992','%d-%m-%Y'));
Query OK, 1 row affected (0.04 sec)
mysql> select * from t_user;
+----+------+------------+
| id | name | birth |
+----+------+------------+
| 1 | kd | 1992-10-01 |
+----+------+------------+
1 row in set (0.03 sec)If the supplied string date is% Y-%m-%d, str_to_date may not be used.
mysql> insert into t_user(id,name,birth) values(1,'kd','1992-10-01'); Query OK, 1 row affected (0.05 sec) mysql> select * from t_user; +----+------+------------+ | id | name | birth | +----+------+------------+ | 1 | kd | 1992-10-01 | | 1 | kd | 1992-10-01 | +----+------+------------+ 2 rows in set (0.03 sec)
Displayed in characteristic date format during query
date_format: convert date type to varchar string type with certain format.
Syntax format
date_format(Date type data,'Date format')
This function is usually used to set the date format of the display in terms of query date.
mysql> select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user; +----+------+------------+ | id | name | birth | +----+------+------------+ | 1 | kd | 10/01/1992 | | 1 | kd | 10/01/1992 | +----+------+------------+ 2 rows in set (0.03 sec)
The difference between date and datetime
Date is a short date, only including mm / DD / yy.
datetime long date, including mm / DD / yyyy, hours, minutes and seconds.
insert inserts multiple records at a time
rule of grammar
insert into t_user(Field name 1,Field name 2,...) values(),(),();
Example
insert into t_user(id,name,birth) values (1,'kd','1992-01-01'), (2,'kd','1992-01-01'), (3,'kd','1992-01-01'); Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t_user; +----+------+------------+ | id | name | birth | +----+------+------------+ | 1 | kd | 1992-01-01 | | 2 | kd | 1992-01-01 | | 3 | kd | 1992-01-01 | +----+------+------------+ 3 rows in set (0.03 sec)