1.往表中插入数据

命令语法

insert into<表名>[(<字段名1>[…<字段名n>])]values(值1)[,(值n)]

1.2 插入数据演示

在linzhongniao库中创建一个test表

mysql> create table test(
 -> id int(4) not null AUTO_INCREMENT,
 -> name char(20) not null,
 -> PRIMARY KEY(id)
 -> );
Query OK, 0 rows affected (0.08 sec)

mysql> desc test;
 +-------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
 +-------+----------+------+-----+---------+----------------+
| id     | int(4)   | NO   | PRI | NULL    | auto_increment |
| name   | char(20) | NO   |     | NULL    |                |
 +-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

1.2.1 指定表中所有列插入数据

mysql> insert into test(id,name) values(1,'linzhogniao');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
 +----+-------------+
 | id |         name|
 +----+-------------+
 |  1 | linzhogniao |
 +----+-------------+
1 row in set (0.00 sec)

1.2.2 只在name列插入数据

因为id列为自增，所有只往name列插入数据即可

mysql> insert into test(name) values('wwn1314');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
+----+-------------+
2 rows in set (0.00 sec)

1.2.3 如果不指定列，就要按规矩为每列都插入适当的值

mysql> insert into test values(3,'lisi');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
 +----+-------------+
| id  | name        |
 +----+-------------+
|  1  | linzhogniao |
|  2  | wwn1314     |
|  3  | lisi        |
 +----+-------------+
3 rows in set (0.00 sec)

1.2.4 插入多个数据

一次插入多个数据，多个数据之间用逗号分隔

mysql> insert into test values(4,'woshishei'),(5,'xiaozhang');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | lisi|
|  4 | woshishei   |
|  5 | xiaozhang   |
+----+-------------+
5 rows in set (0.00 sec)

数据插入成功，然后备份上述数据，留着备用。备份后检查备份的sql数据内容；过滤无用信息。

[root@linzhongniao ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B linzhongniao >/opt/linzhongniao_bak.sql
[root@linzhongniao ~]# grep -E -v "#|\/|^$|--" /opt/linzhongniao_bak.sql
USE `linzhongniao`;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_dept` (`name`(8)),
  KEY `index_name_dept` (`name`(8),`dept`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `student` WRITE;
UNLOCK TABLES;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'linzhogniao'),(2,'wwn1314'),(3,'lisi'),(4,'woshishei'),(5,'xiaozhang');
UNLOCK TABLES;

一个错误的备份案列，如果不检查备份数据可能导致数据不是想要的

[root@localhost ~]# mysqldump -uroot -p123456 -A -B linzhongniao >/opt/linzhongniao_bak1.sql
[root@localhost ~]# grep -E -v "#|\/|^$|--" /opt/zbf_bak1.sql 
Usage: mysqldump [OPTIONS] database [tables]

提示：

1、-A备份所有的库，-B备份指定的库

2、5.1.68版本mysql备份数据

[root@localhost ~]# mysqldump -usystem -pzbf666 –A -B --events >/opt/zbf_bak.sql