mysql数据库更新表_Mysql数据库（四）表记录的更新操作

一、插入表记录

1.使用INSERT...VALUES语句插入新纪录

(1)插入完整数据

mysql> desc tb_manager;

+-------+------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| name | varchar(30) | YES | | NULL | |

| PWD | varchar(30) | YES | | NULL | |

+-------+------------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

mysql> INSERT INTO tb_manager VALUES(1,'mr','mrsoft');

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tb_manager;

+----+------+--------+

| id | name | PWD |

+----+------+--------+

| 1 | mr | mrsoft |

+----+------+--------+

1 row in set (0.00 sec)

(2)插入数据记录的一部分

mysql> INSERT INTO tb_manager(name,PWD) VALUES('Lianjiang','lianjiang');

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tb_manager;

+----+-----------+-----------+

| id | name | PWD |

+----+-----------+-----------+

| 1 | mr | mrsoft |

| 2 | Lianjiang | lianjiang |

+----+-----------+-----------+

2 rows in set (0.00 sec)

2.插入多条记录

mysql> INSERT INTO tb_manager(name,PWD) VALUES('lian','111'),('qiao','222'),('tian','333');

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM tb_manager;

+----+-----------+-----------+

| id | name | PWD |

+----+-----------+-----------+

| 1 | mr | mrsoft |

| 2 | Lianjiang | lianjiang |

| 3 | lian | 111 |

| 4 | qiao | 222 |

| 5 | tian | 333 |

+----+-----------+-----------+

5 rows in set (0.00 sec)

3.使用INSERT...SELECT语句将查询结果插入到指定的数据表中，实现从图书馆tb_borrow中获取部借阅信息插入到归还表tb_giveback中

(1)创建借阅表并插入两条数据

mysql> CREATE TABLE tb_borrow(

-> id int(10) unsigned NOT NULL AUTO_INCREMENT,

-> readerid int(10) unsigned,

-> bookid int(10),

-> borrowTime date,

-> backTime date,

-> operator varchar(30),

-> ifback tinyint(1) DEFAULT '0',

-> PRIMARY KEY(id)

-> )DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tb_borrow(readerid,bookid,borrowTime,backTime,operator,ifback) VALUES

-> (1,1,'2018-04-17','2018-04-20','mr',1),

-> (1,2,'2018-04-16','2018-04-21','mr',0);

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM tb_borrow;

+----+----------+--------+------------+------------+----------+--------+

| id | readerid | bookid | borrowTime | backTime | operator | ifback |

+----+----------+--------+------------+------------+----------+--------+

| 1 | 1 | 1 | 2018-04-17 | 2018-04-20 | mr | 1 |

| 2 | 1 | 2 | 2018-04-16 | 2018-04-21 | mr | 0 |

+----+----------+--------+------------+------------+----------+--------+

2 rows in set (0.00 sec)

(2)创建归还表并查询readerid和bookid字段的值，插入到数据表tb_giveback中

mysql> CREATE TABLE tb_giveback(

-> id int(10) unsigned NOT NULL AUTO_INCREMENT,

-> readerid int(10) unsigned,

-> bookid int(10),

-> backTime date,

-> operator varchar(30),

-> PRIMARY KEY(id)

-> )DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tb_giveback

-> (readerid,bookid)

-> SELECT readerid,bookid FROM tb_borrow;

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM tb_giveback;

+----+----------+--------+----------+----------+

| id | readerid | bookid | backTime | operator |

+----+----------+--------+----------+----------+

| 1 | 1 | 1 | NULL | NULL |

| 2 | 1 | 2 | NULL | NULL |

+----+----------+--------+----------+----------+

2 rows in set (0.00 sec)

4.使用REPLACE语句插入新纪录

REPLACE语句与INSERT INTO语句相似，所不同的是，如果一个要插入数据的表中存在主键约束或者唯一约束，而且要插入的数据中又包含于要插入数据的表中相同的主键约束或者唯一约束列的值，那么使用INSERT INTO不能插入这条记录，而使用REPLACE可以插入，只不过它会先将原数据表中起冲突的记录删除，然后再插入新的记录。

mysql> INSERT INTO tb_giveback

-> SELECT id,readerid,bookid,backTime,operator FROM tb_borrow;

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> REPLACE INTO tb_giveback

-> SELECT id,readerid,bookid,backTime,operator FROM tb_borrow;

Query OK, 4 rows affected (0.01 sec)

Records: 2 Duplicates: 2 Warnings: 0

mysql> SELECT * FROM tb_giveback;

+----+----------+--------+------------+----------+

| id | readerid | bookid | backTime | operator |

+----+----------+--------+------------+----------+

| 1 | 1 | 1 | 2018-04-20 | mr |

| 2 | 1 | 2 | 2018-04-21 | mr |

+----+----------+--------+------------+----------+

2 rows in set (0.00 sec)

二、修改表记录，将借阅表中id字段为2的记录的“是否归还”字段值设为1

mysql> UPDATE tb_borrow SET ifback=1 WHERE id=2;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM tb_borrow;

+----+----------+--------+------------+------------+----------+--------+

| id | readerid | bookid | borrowTime | backTime | operator | ifback |

+----+----------+--------+------------+------------+----------+--------+

| 1 | 1 | 1 | 2018-04-17 | 2018-04-20 | mr | 1 |

| 2 | 1 | 2 | 2018-04-16 | 2018-04-21 | mr | 1 |

+----+----------+--------+------------+------------+----------+--------+

2 rows in set (0.00 sec)

三、删除表记录

1.使用DELETE语句删除表记录

mysql> SELECT * FROM tb_manager;

+----+-----------+-----------+

| id | name | PWD |

+----+-----------+-----------+

| 1 | mr | mrsoft |

| 2 | Lianjiang | lianjiang |

| 3 | lian | 111 |

| 4 | qiao | 222 |

| 5 | tian | 333 |

+----+-----------+-----------+

5 rows in set (0.00 sec)

mysql> DELETE FROM tb_manager WHERE name='Lianjiang';

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tb_manager;

+----+------+--------+

| id | name | PWD |

+----+------+--------+

| 1 | mr | mrsoft |

| 3 | lian | 111 |

| 4 | qiao | 222 |

| 5 | tian | 333 |

+----+------+--------+

4 rows in set (0.00 sec)

2.使用TRUNCATE语句清空表记录

mysql> TRUNCATE TABLE tb_manager;

Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM tb_manager;

Empty set (0.00 sec)