MySQL知识（十二）——数据的插入、更新和删除

0 样例表

　　样例表person，创建语句：

CREATE TABLE person
(
  id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name   CHAR(40) NOT NULL DEFAULT '',
  age    INT NOT NULL DEFAULT 0,
  info   CHAR(50) NULL,
  PRIMARY KEY (id)
);

1 插入数据

1.1 为表的所有字段插入数据

　　语法：

INSERT INTO 表名(列名列表) VAULES(对应值列表);

　　（1）指定所有字段名

mysql> INSERT INTO person(id,name,age,info)
    -> VALUES(1,'Green',21,'Lawyer');
Query OK, 1 row affected

　　（2）完全不指定字段名

mysql> INSERT INTO person
    -> VALUES(2,'Suse',2,'dancer'),
    -> (3,'Mary',24,'Musician');
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

　　（3）查看执行结果

mysql> SELECT * FROM person;
+----+-------+-----+----------+
| id | name  | age | info     |
+----+-------+-----+----------+
|  1 | Green |  21 | Lawyer   |
|  2 | Suse  |   2 | dancer   |
|  3 | Mary  |  24 | Musician |
+----+-------+-----+----------+
3 rows in set

1.2 为表的指定字段插入数据

　　为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义的默认值。

mysql> INSERT INTO person(name,age,info) VALUES('Willam',20,'sports man');
Query OK, 1 row affected

mysql> INSERT INTO person(name,age) VALUES('Laura',20);
Query OK, 1 row affected

　　查看执行结果

mysql> SELECT * FROM person;
+----+--------+-----+------------+
| id | name   | age | info       |
+----+--------+-----+------------+
|  1 | Green  |  21 | Lawyer     |
|  2 | Suse   |   2 | dancer     |
|  3 | Mary   |  24 | Musician   |
|  4 | Willam |  20 | sports man |
|  5 | Laura  |  20 | NULL       |
+----+--------+-----+------------+
5 rows in set

1.3 同时插入多条数据

　　语法：

INSERT INTO table_name(column_list) VALUES(value_list),(value_list2),...,(value_listn);

　　例子：（等同于执行多条INSERT语句，但MySQL执行单条INSERT语句插入多行数据，比使用多条INSERT语句快。）

mysql> INSERT INTO person(name,age,info)
    -> VALUES('Evans',27,'secretary'),
    -> ('Dale',22,'singer'),
    -> ('Edison',28,'cook');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

• Records：表明插入的记录条数
• Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值
• Warnings：表明有问题的数据值，例如发生数据类型转换

1.4 将查询结果插入到表中

　　INSERT可以将SELECT语句查询的结果插入到表中，语法格式：

INSERT INTO table_name1(column_list1)
SELECT(column_list2) FROM table_name2 WHERE(condition)

　　例子：

mysql> SELECT * FROM person;
+----+---------+-----+------------+
| id | name    | age | info       |
+----+---------+-----+------------+
|  1 | Green   |  21 | Lawyer     |
|  2 | Suse    |   2 | dancer     |
|  3 | Mary    |  24 | Musician   |
|  4 | Willam  |  20 | sports man |
|  5 | Laura   |  20 | NULL       |
|  6 | Evans   |  27 | secretary  |
|  7 | Dale    |  22 | singer     |
|  8 | Edison  |  28 | cook       |
+----+---------+-----+------------+
8 rows in set

mysql> SELECT * FROM person_copy;
+----+---------+-----+----------+
| id | name    | age | info     |
+----+---------+-----+----------+
|  9 | Harry   |  21 | magician |
| 10 | Harriet |  19 | pianist  |
+----+---------+-----+----------+
2 rows in set

mysql> INSERT INTO person
    -> SELECT * FROM person_copy;
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM person;
+----+---------+-----+------------+
| id | name    | age | info       |
+----+---------+-----+------------+
|  1 | Green   |  21 | Lawyer     |
|  2 | Suse    |   2 | dancer     |
|  3 | Mary    |  24 | Musician   |
|  4 | Willam  |  20 | sports man |
|  5 | Laura   |  20 | NULL       |
|  6 | Evans   |  27 | secretary  |
|  7 | Dale    |  22 | singer     |
|  8 | Edison  |  28 | cook       |
|  9 | Harry   |  21 | magician   |
| 10 | Harriet |  19 | pianist    |
+----+---------+-----+------------+
10 rows in set

2 更新数据

　　使用UPDATE语句更新表中的记录，可以更新特定的行或者同时更新所有的行。语法：

UPDATE table_name
SET column_name1 = value1,column_name2=value2,...,column_namen=valuen
WHERE(condition);

　　例子：

mysql> SELECT * FROM person WHERE id=10;
+----+---------+-----+---------+
| id | name    | age | info    |
+----+---------+-----+---------+
| 10 | Harriet |  19 | pianist |
+----+---------+-----+---------+
1 row in set

mysql> UPDATE person SET age=15,name='LiMing' WHERE id=10;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM person WHERE id=10;
+----+--------+-----+---------+
| id | name   | age | info    |
+----+--------+-----+---------+
| 10 | LiMing |  15 | pianist |
+----+--------+-----+---------+
1 row in set

　　更新多行数据：

mysql> SELECT * FROM person WHERE age BETWEEN 19 AND 22;
+----+--------+-----+------------+
| id | name   | age | info       |
+----+--------+-----+------------+
|  1 | Green  |  21 | Lawyer     |
|  4 | Willam |  20 | sports man |
|  5 | Laura  |  20 | NULL       |
|  7 | Dale   |  22 | singer     |
|  9 | Harry  |  21 | magician   |
+----+--------+-----+------------+
5 rows in set

mysql> UPDATE person SET info='student' WHERE age BETWEEN 19 AND 22;
Query OK, 5 rows affected
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT * FROM person WHERE age BETWEEN 19 AND 22;
+----+--------+-----+---------+
| id | name   | age | info    |
+----+--------+-----+---------+
|  1 | Green  |  21 | student |
|  4 | Willam |  20 | student |
|  5 | Laura  |  20 | student |
|  7 | Dale   |  22 | student |
|  9 | Harry  |  21 | student |
+----+--------+-----+---------+
5 rows in set

3 删除数据

　　使用DELETE语句删除数据，DELETE语句允许WHERE子句指定删除条件。语法：

DELETE FROM table_name [WHERE<condition>];

　　如果没有WHERE子句，DELETE语句将删除表中所有记录。

mysql> SELECT * FROM person WHERE id=10;
+----+--------+-----+---------+
| id | name   | age | info    |
+----+--------+-----+---------+
| 10 | LiMing |  15 | pianist |
+----+--------+-----+---------+
1 row in set

mysql> DELETE FROM person WHERE id=10;
Query OK, 1 row affected

mysql> SELECT * FROM person WHERE id=10;
Empty set

4 说明

阅读《MySQL5.5 从零开始学》笔记记录。