Mysql DBA Advanced Operations Learning Note insert Knowledge-DML statement

9.9.7.8 inserting data into a table

(1) Command syntax

Insert into< Table name >[(< field name 1>[...< field name N>])]values (value 1) [, (value N)]

(2) Create a simple test table test1

Create Table: CREATE TABLE `test1` (  `id` int(4) NOT NULL AUTO_INCREMENT,  `name` char(20) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1

(3) Different examples of inserting data into a table

A. Specify all column names according to the rules, and insert values into each column

Insert into test(id,name) values(1,’zbf’);[email protected] 12:0600-> insert into test(id,name) values(1,‘zbf‘);Query OK, 1 row affected (0.00 sec)[email protected] 12:1313->select * from test;+----+------+| id | name |+----+------+|  1 | zbf  |+----+------+1 row in set (0.00 sec)

B. Because the ID column is self-increasing, only the value is inserted in the name

[email protected] 12:1329-> insert into test(name) values(‘wwnwan‘);Query OK, 1 row affected (0.00 sec)[email protected] 12:1738->select * from test;+----+--------+| id | name   |+----+--------+|  1 | zbf||  2 | wwnwan |+----+--------+2 rows in set (0.00 sec)

C. If you do not specify a column, you should insert the appropriate value for each column according to the rules

[email protected] 12:1742->insert into test values(3,‘lisi‘);Query OK, 1 row affected (0.00 sec)[email protected] 12:2349->select * from test;+----+--------+| id | name   |+----+--------+|  1 | zbf||  2 | wwnwan ||  3 | lisi   |+----+--------+

D. Bulk Insert data method improves efficiency.

[email protected] 12:2630->insert into test values(4,‘woshishei‘),(5,‘nimei‘);Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0[email protected] 12:2708->select * from test;+----+-----------+| id | name  |+----+-----------+|  1 | zbf   ||  2 | wwnwan||  3 | lisi  ||  4 | woshishei ||  5 | nimei |+----+-----------+

E. Deleting data (with caution)

[email protected] 12:2723->delete from test;Query OK, 5 rows affected (0.00 sec)[email protected] 12:4005->select * from test;Empty set (0.00 sec)

F. Bulk INSERT Demo

[email protected] 12:4502->select * from test;+----+-----------+| id | name  |+----+-----------+|  1 | wwnwan||  2 | zbf   ||  3 | lisi  ||  4 | woshishei ||  5 | nimei |

G. Test completed, exit the database, and then back up the above data, save the backup. Check the contents of the backed up SQL data after backup and filter out useless information.

[[email protected] ~]# mysqldump -usystem -pzbf666 -B wwn >/opt/zbf_bak.sql[[email protected] ~]# grep -E -v "#|\/|^$|--" /opt/zbf_bak.sql

H. A wrong backup case, if you do not check the backup data may cause the data is not desired

[[email protected] ~]# mysqldump -usystem -pzbf666 -A -B wwn >/opt/zbf_bak1.sql[[email protected] ~]# grep -E -v "#|\/|^$|--" /opt/zbf_bak1.sql Usage: mysqldump [OPTIONS] database [tables]

Tips:

1. -A backs up all libraries,-B backs up the specified libraries
2. 5.1.68 version [[email protected] ~]# mysqldump-usystem-pzbf666–a-b--events >/opt/zbf_bak.sql

Add emphasis: We usually log on to the site posting, send blog, is essentially called the site of the program to connect MySQL database, through the above INSERT statement to the Post blog data stored in the database.

Mysql DBA Advanced Operations Learning Note insert Knowledge-DML statement