一起学mysql 06.mysql 插入数据

mysql 插入数据

mysql> create table product(id int(10) NOT NULL primary key, name varchar(20) NOT NULL,function varchar(50),company varchar(20) not null,address varcchar(50));
Query OK, 0 rows affected (0.35 sec)

mysql> desc product;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(10)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   |     | NULL    |       |
| function | varchar(50) | YES  |     | NULL    |       |
| company  | varchar(20) | NO   |     | NULL    |       |
| address  | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

为表的所有字段插入数据

mysql> insert into product values(1001,'ABC药物','治疗感冒','ABC制药厂','北京市昌平区');
Query OK, 1 row affected (0.00 sec)

mysql> select * from product;
+------+-----------+--------------+--------------+--------------------+
| id   | name      | function     | company      | address            |
+------+-----------+--------------+--------------+--------------------+
| 1001 | ABC药物   | 治疗感冒     | ABC制药厂    | 北京市昌平区       |
+------+-----------+--------------+--------------+--------------------+
1 row in set (0.00 sec)

insert 语句中列出所有字段

mysql> insert into product(id,name,function,company,address) values(1002,'BCD','治疗头疼','BCD制药厂','北京市海淀区');
Query OK, 1 row affected (0.00 sec)

mysql> select * from product where id=1002;
+------+------+--------------+--------------+--------------------+
| id   | name | function     | company      | address            |
+------+------+--------------+--------------+--------------------+
| 1002 | BCD  | 治疗头疼     | BCD制药厂    | 北京市海淀区       |
+------+------+--------------+--------------+--------------------+
1 row in set (0.39 sec)

mysql> insert into product(id,function,name,address,company) values(1003,'治疗头疼','AB康复丸','北京市顺义区','AB康复制药厂');
Query OK, 1 row affected (0.00 sec)

mysql> select * from product where id=1003;
+------+-------------+--------------+-------------------+--------------------+
| id   | name        | function     | company           | address            |
+------+-------------+--------------+-------------------+--------------------+
| 1003 | AB康复丸    | 治疗头疼     | AB康复制药厂      | 北京市顺义区       |
+------+-------------+--------------+-------------------+--------------------+
1 row in set (0.00 sec)

为表的指定字段插入数据

mysql> insert into product(id,name,company) values(1004,'EF咳嗽灵','EF制药厂');
Query OK, 1 row affected (0.01 sec)

mysql> select * from product where id=1004;
+------+-------------+----------+-------------+---------+
| id   | name        | function | company     | address |
+------+-------------+----------+-------------+---------+
| 1004 | EF咳嗽灵    | NULL     | EF制药厂    | NULL    |
+------+-------------+----------+-------------+---------+
1 row in set (0.00 sec)

mysql> insert into product(id,company,name) values(1005,'北京制药厂','OK护嗓药');
Query OK, 1 row affected (0.00 sec)

mysql> select * from product where id=1005;
+------+-------------+----------+-----------------+---------+
| id   | name        | function | company         | address |
+------+-------------+----------+-----------------+---------+
| 1005 | OK护嗓药    | NULL     | 北京制药厂      | NULL    |
+------+-------------+----------+-----------------+---------+
1 row in set (0.00 sec)

同时插入多条记录

mysql> insert into product values(1006,'头疼灵1号','治疗头疼','DD制药厂','北京市房山区'), (1007,'头疼灵2号','治疗头疼','DD制药厂','北京市房山区'), 1008,'头疼灵3号','治疗头疼','DD制药厂','北京市房山区');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from product where id>=1006 and id<=1008;
+------+---------------+--------------+-------------+--------------------+
| id   | name          | function     | company     | address            |
+------+---------------+--------------+-------------+--------------------+
| 1006 | 头疼灵1号     | 治疗头疼     | DD制药厂    | 北京市房山区       |
| 1007 | 头疼灵2号     | 治疗头疼     | DD制药厂    | 北京市房山区       |
| 1008 | 头疼灵3号     | 治疗头疼     | DD制药厂    | 北京市房山区       |
+------+---------------+--------------+-------------+--------------------+
3 rows in set (0.34 sec)

mysql> insert into product(id,name,company) values(1009,'护发1号','北京护发素厂'),(1010,'护发2号','北京护发素厂'),(1011,'护发3号','北京护发素厂'); 
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from product where id>=1009 and id<=1011;
+------+------------+----------+--------------------+---------+
| id   | name       | function | company            | address |
+------+------------+----------+--------------------+---------+
| 1009 | 护发1号    | NULL     | 北京护发素厂       | NULL    |
| 1010 | 护发2号    | NULL     | 北京护发素厂       | NULL    |
| 1011 | 护发3号    | NULL     | 北京护发素厂       | NULL    |
+------+------------+----------+--------------------+---------+
3 rows in set (0.00 sec)

将查询结果插入到表中

mysql>  create table medicine(id int(10) NOT NULL primary key, name varchar(20) NOT NULL,function varchar(50),company varchar(20) not null,address varchar(50));
Query OK, 0 rows affected (0.45 sec)

mysql> insert into medicine values(2001,'止咳1号','治疗咳嗽','咳嗽药制药厂','北京市顺义区'),(2002,'止咳2号','治疗咳嗽','咳嗽药制药厂','北京市顺义区'),(2003,'止咳3号','治疗咳嗽','咳嗽药制药厂','北京市顺义区');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from medicine;
+------+------------+--------------+--------------------+--------------------+
| id   | name       | function     | company            | address            |
+------+------------+--------------+--------------------+--------------------+
| 2001 | 止咳1号    | 治疗咳嗽     | 咳嗽药制药厂       | 北京市顺义区       |
| 2002 | 止咳2号    | 治疗咳嗽     | 咳嗽药制药厂       | 北京市顺义区       |
| 2003 | 止咳3号    | 治疗咳嗽     | 咳嗽药制药厂       | 北京市顺义区       |
+------+------------+--------------+--------------------+--------------------+
3 rows in set (0.00 sec)

mysql> insert into product(id,function,name,address,company) select id,function,name,address,company from medicine;
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from product where id>=2001 and id<=2003;
+------+------------+--------------+--------------------+--------------------+
| id   | name       | function     | company            | address            |
+------+------------+--------------+--------------------+--------------------+
| 2001 | 止咳1号    | 治疗咳嗽     | 咳嗽药制药厂       | 北京市顺义区       |
| 2002 | 止咳2号    | 治疗咳嗽     | 咳嗽药制药厂       | 北京市顺义区       |
| 2003 | 止咳3号    | 治疗咳嗽     | 咳嗽药制药厂       | 北京市顺义区       |
+------+------------+--------------+--------------------+--------------------+
3 rows in set (0.00 sec)