五、mysql的插入、更新、删除数据

---

一、插入数据

1、为表的所有字段插入数据

2、为表的指定字段插入数据

3、同时插入多条记录

4、将查询结果插入到表中

二、更新数据

三、删除数据

四、为表增加计算列

五、mysql8.0的新特性

---

一、插入数据

1、为表的所有字段插入数据

insert  into  table_name (column_list)  values (value_list)

#案例
mysql> insert into a1 (id,name) values(3,'jiaofan');
Query OK, 1 row affected (0.00 sec)

mysql> select * from a1;
+----+----------+
| id | name     |
+----+----------+
|  1 | liming   |
|  2 | xiaohong |
|  3 | jiaofan  |
+----+----------+
3 rows in set (0.00 sec)

2、为表的指定字段插入数据

mysql> insert into a1 (name) values('nihao');
Query OK, 1 row affected (0.00 sec)

mysql> select * from a1;
+----+----------+
| id | name     |
+----+----------+
|  1 | liming   |
|  2 | xiaohong |
|  3 | jiaofan  |
|  4 | nihao    |
+----+----------+
4 rows in set (0.00 sec)

3、同时插入多条记录

insert  into  table_name(column_list)
values (values_list),(values_list), ... ，(values_list)；

#案例
mysql> insert  into  a1 (id,name) values(5,'aaa'),(6,'bbb'),(8,'ccc');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from a1;
+----+----------+
| id | name     |
+----+----------+
|  1 | liming   |
|  2 | xiaohong |
|  3 | jiaofan  |
|  4 | nihao    |
|  5 | aaa      |
|  6 | bbb      |
|  8 | ccc      |
+----+----------+

4、将查询结果插入到表中

insert  into  table_name1(column_lsit)
select (column_list2)  from  table_name2 where (condition)

mysql> select * from a1;
+----+----------+
| id | name     |
+----+----------+
|  1 | liming   |
|  2 | xiaohong |
|  3 | jiaofan  |
|  4 | nihao    |
|  5 | aaa      |
|  6 | bbb      |
|  8 | ccc      |
|  9 | ddd      |
+----+----------+
8 rows in set (0.00 sec)

mysql> insert  into a1(name)
    -> select name  from a3;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from a1;
+----+----------+
| id | name     |
+----+----------+
|  1 | liming   |
|  2 | xiaohong |
|  3 | jiaofan  |
|  4 | nihao    |
|  5 | aaa      |
|  6 | bbb      |
|  8 | ccc      |
|  9 | ddd      |
| 10 | qqq      |
| 11 | www      |
+----+----------+
10 rows in set (0.00 sec)

二、更新数据

update table_name
set column_name = value1, column_name2 = value2, ... , column_namen = valuen
where (condition)

mysql> select * from a1;
+----+----------+
| id | name     |
+----+----------+
|  1 | liming   |
|  2 | xiaohong |
|  3 | jiaofan  |
|  4 | nihao    |
|  5 | aaa      |
|  6 | bbb      |
|  8 | ccc      |
|  9 | ddd      |
| 10 | qqq      |
| 11 | www      |
+----+----------+
10 rows in set (0.00 sec)

mysql> update  a1
    -> set name = 'beijing'
    -> where name regexp '[ng]';
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from a1;
+----+---------+
| id | name    |
+----+---------+
|  1 | beijing |
|  2 | beijing |
|  3 | beijing |
|  4 | beijing |
|  5 | aaa     |
|  6 | bbb     |
|  8 | ccc     |
|  9 | ddd     |
| 10 | qqq     |
| 11 | www     |
+----+---------+
10 rows in set (0.00 sec)

三、删除数据

delete  from table_name [where <condition>]

#案例
mysql> select * from a1;
+----+---------+
| id | name    |
+----+---------+
|  1 | beijing |
|  2 | beijing |
|  3 | beijing |
|  4 | beijing |
|  5 | aaa     |
|  6 | bbb     |
|  8 | ccc     |
|  9 | ddd     |
| 10 | qqq     |
| 11 | www     |
+----+---------+
10 rows in set (0.00 sec)

mysql> delete from a1
    -> where name regexp '[e]';
Query OK, 4 rows affected (0.01 sec)

mysql> select * from a1;
+----+------+
| id | name |
+----+------+
|  5 | aaa  |
|  6 | bbb  |
|  8 | ccc  |
|  9 | ddd  |
| 10 | qqq  |
| 11 | www  |
+----+------+
6 rows in set (0.00 sec)

四、为表增加计算列

什么叫计算列？简单来说就是某一列的值是通过别的列来计算来的。列如，a列值为1、b列值为2，c列不需要手动输入，定义a+b的结果为c的值，那么c就是计算列。

col_name  data_type  [generated  always]  as  (expression)
         [ virtual | stored ]  [unique  [key] ]  [comment  comment]
         [not null | null ]  [ [primary]  key ]

在mysql8.0中，create talbe 和 alter table 中都支持增加计算列。下面以create talbe为列。

mysql> create  table tb1(
    ->    id int(9)  not null  auto_increment,
    ->    a  int(9)  default null,
    ->    b  int(9)  default  null,
    ->    c  int(9)  generated  always  as  ((a + b)) virtual,
    ->    primary key (id)
    -> );
mysql> insert into tb1(a,b)
    -> values(200,300);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |  200 |  300 |  500 |
+----+------+------+------+
1 row in set (0.00 sec)

mysql> update tb1 
    -> set b = 400
    -> where b = 300;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb1;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |  200 |  400 |  600 |
+----+------+------+------+
1 row in set (0.00 sec)

五、mysql8.0的新特性----DDL原子化