"MYSQL" INSERT INTO

• INSERT into table-name values ()

Mysql>desc NS;+-------+------------------+------+-----+---------+----------------+| Field | Type | Null | Key |Default| Extra |+-------+------------------+------+-----+---------+----------------+| UID | Int (Ten) unsigned | NO | PRI | NULL | auto_increment | | uname | varchar (20) |     NO | |                NULL | || udate | Date |     YES | |                NULL | |+-------+------------------+------+-----+---------+----------------+3 rowsinchSet (0.00sec) MySQL>drop table ns; Query OK,0 Rows Affected (0.00sec) MySQL>INSERT INTO NS VALUES (NULL, ' Alex ', Now ()); Query OK,1 row affected, 1 warning (0.05sec) MySQL> select *from NS;+-----+-------+------------+| UID | uname |   Udate |+-----+-------+------------+| 1 | Alex | 2016-11-08 |+-----+-------+------------+1 rowinchSet (0.00 sec)

• Insert some columns

Mysql>1136 (21s01): Column count doesn ' t match value count at row 1mysql>1 row affected ( 0.00 sec) MySQL> select * from NS; +-----+-------+------------+| UID | uname | Udate      |+-----+-------+------------+|   1 | Alex  | 2016-11-08 | |   2 | Alex  | NULL in        Set (0.00 sec)

• Insert illegal NULL

Mysql>1048 (23000): Column ' uname ' cannot be null

• Insert two lines

mysql> INSERT INTO NS VALUES (NULL, ' Jat ', 19871117), (null, ' Jet ', 145811222 rows affected (0.002  duplicates:0  warnings:0mysql> select * from NS; +-----+-------+------------+| UID | uname | Udate      |+-----+-------+------------+|   1 | Alex  | 2016-11-08 | |   2 | Alex  | NULL       | |   3 | Jat   | 1987-11-17 | |   4 | Jet in    Set (0.00 sec)

• Insert some columns again

mysql>1 row Affected (0.00 sec) MySQL> select * from NS; +-----+-------+------------+| UID | uname | Udate      |+-----+-------+------------+|   1 | Alex  | 2016-11-08 | |   2 | Alex  | NULL       | |   3 | Jat   | 1987-11-17 | |   4 | Jet   | 1458-11-22 | |   5 | James | NULL in        Set (0.00 sec)

• Inserting data Using Set

mysql> INSERT INTO NS set uname=1 row Affected (0.00 sec) MySQL> select * from NS; +-----+--------+------------+| UID | uname  | udate      |+-----+--------+------------+|   1 | Alex   | 2016-11-08 | |   2 | Alex   | NULL       | |   3 | Jat    | 1987-11-17 | |   4 | Jet    | 1458-11-22 | |   5 | James  | NULL       | |   6 | Lecake | NULL in        Set (0.00 sec)

• Column order is inconsistent with table

mysql> INSERT INTO NS (UNAME,UDATE,UID) VALUES (' Kyrie ', 20160711, null)    ,1 row affected ( 0.00 sec) MySQL> select * from NS; +-----+--------+------------+| UID | uname  | udate      |+-----+--------+------------+|   1 | Alex   | 2016-11-08 | |   2 | Alex   | NULL       | |   3 | Jat    | 1987-11-17 | |   4 | Jet    | 1458-11-22 | |   5 | James  | NULL       | |   6 | Lecake | NULL       | |   7 | Kyrie in   Set (0.00 sec)

• Use Insert ... SELECT statement inserts rows selected from other tables
• The query cannot contain an ORDER BY clause, and the destination table of the INSERT statement cannot appear in the FROM clause in the Select query section.

Mysql> SELECT *From ns2;+-----+--------+-----------+------------+| UID | uname | Uemail |   Udate |+-----+--------+-----------+------------+| 1 | Config | [Email protected] |   1987-11-17 | | 2 | Enable | [Email protected] | 1458-11-22 |+-----+--------+-----------+------------+2 rowsinchSet (0.00sec) MySQL>select Uname,udate from NS2;+--------+------------+| uname | Udate |+--------+------------+| Config | 1987-11-17 | | Enable | 1458-11-22 |+--------+------------+2 rowsinchSet (0.00sec) MySQL>INSERT INTO NS (uname,udate) select Uname,udate from NS2; Query OK,2 rows affected (0.05sec) Records:2 duplicates:0 warnings:0MySQL> select *from NS;+-----+--------+------------+| UID | uname |   Udate |+-----+--------+------------+| 1 | Alex |   2016-11-08 | | 2 | Alex |   NULL | | 3 | Jat |   1987-11-17 | | 4 | Jet |   1458-11-22 | | 5 | James |   NULL | | 6 | Lecake |   NULL | | 7 | Kyrie |   2016-07-11 | | 8 | Config |   1987-11-17 | | 9 | Enable | 1458-11-22 |+-----+--------+------------+9 rowsinchSet (0.00sec) MySQL>

INSERT syntax

INSERT [Low_priority | DELAYED | High_priority] [IGNORE]    [Into] tbl_name [(col_name,...)]     DEFAULT },...),(...),...    [on DUPLICATE KEY UPDATE col_name=| DELAYED | High_priority] [IGNORE]    [into] tbl_name    SET col_nameDEFAULT}, ...    [on DUPLICATE KEY UPDATE col_name=| High_priority] [IGNORE]    [Into] tbl_name [(col_name,...)]    SELECT ...    [on DUPLICATE KEY UPDATE col_name=expr, ...]

"MYSQL" INSERT INTO