一、往表中插入数据
1、命令语法：
insert into <表名> [(<字段名1>[,..<字段名n>])] values (值1)[,(值n)]
2、新建一个简单的测试表test
create table `test`(
`id` int(4) not null auto_increment,
`name` char(20) not null,
primary key (`id`)
);
3、往表中插入数据的不同的语法例子：
1）按规矩指定所有列名，并且每列都插入值
mysql> insert into test(id,name) values(1,‘oldboy‘);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | oldboy |
+----+--------+
1 row in set (0.00 sec)
2）由于id列为自增的，所有，可以只在name列插入值
mysql> insert into test(name) values(‘oldgirl‘);
Query OK, 1 row affected (0.11 sec)
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
+----+---------+
2 rows in set (0.00 sec)
3）如果不知道列，就要按规矩为每列都插入恰当的值
mysql> insert into test values(3,‘inca‘);
Query OK, 1 row affected (0.36 sec)
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
+----+---------+
3 rows in set (0.00 sec)
4）批量插入数据方法，提升效率
mysql> insert into test values(4,‘zuma‘),(5,‘kaka‘); #批量插入2条记录，提升效率
Query OK, 2 rows affected (0.35 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
5 rows in set (0.00 sec)
insert into `test` values (1,‘oldboy‘),(2,‘oldgirl‘),(3,‘inca‘),(4,‘zuma‘),(5,‘kaka‘);
4、插入数据实践演示：
mysql> delete from test;
Query OK, 5 rows affected (0.36 sec)
mysql> select * from test;
Empty set (0.00 sec)
mysql> insert into `test` values (1,‘oldboy‘),(2,‘oldgirl‘),(3,‘inca‘),(4,‘zuma‘),(5,‘kaka‘);
Query OK, 5 rows affected (0.10 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
5 rows in set (0.00 sec)
5、测试完毕，退出数据库，然后备份上述数据，留着备用：
[root@localhost ~]# mysqldump -uroot -pdubin -B oldboy >/opt/oldboy_bak.sql
[root@localhost ~]# ls -l /opt/oldboy_bak.sql
-rw-r--r--. 1 root root 2826 9月 20 08:42 /opt/oldboy_bak.sql
[root@localhost ~]#
6、备份后检查备份的sql数据内容：过滤无用信息
[root@localhost ~]# grep -E -v "#|\/|^$|--" /opt/oldboy_bak.sql
USE `oldboy`;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT ‘0‘,
`dept` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uni_inde_name` (`name`),
KEY `index_dept` (`dept`(8)),
KEY `ind_name_dept` (`name`(8),`dept`(10))
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
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=latin1;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,‘oldboy‘),(2,‘oldgirl‘),(3,‘inca‘),(4,‘zuma‘),(5,‘kaka‘);
UNLOCK TABLES;
7、一个备份错误案例：如果不检查备份数据可能导致数据不是想要的。
[root@localhost ~]# mysqldump -uroot -pdubin -A -B oldboy >/opt/oldboy_bak1.sql
[root@localhost ~]# grep -E -v "#|\/|^$|--" /opt/oldboy_bak1.sql
Usage: mysqldump [OPTIONS] database [tables]
[root@localhost ~]# cat /opt/oldboy_bak1.sql
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
提示：
1、错误的点事 -A 表示所有库，后面不能指定oldboy库了
2、5.1.68版本：
[root@localhost ~]# mysqldump -uroot -pdubin -A -B --event >/tmp/oldboy_bak.sql
8、补存强调：我们平时登录网站发帖子，发博文，实质上都是调用web网站的程序连接MySQL数据库，通过上述的insert语句把帖子博文数据存入数据库的。
MySQL数据库应用（8）DML语句之insert知识
京公网安备 11010802041100号