【必知必会的MySQL知识】③DML语言

新城家电

目录

• 前言
  
• 准备
  
• 插入数据
  
  
  
  • 语法格式
    
  • 插入完整行数据
    
  • 插入多行数据
    
  • 将检索出来的数据插入表
    
  
  
• 更新数据
  
  
  
  • 准备两张表
    
  • 语法
    
  • 实践操作
    
  
  
• 删除数据
  
  
  
  • 语法
    
  • 实践操作
    
  
  
• 小结
  

前言

前面的两篇文章中，我们已经对MySQL有了基本了解。
并且知道了怎么用工具连接数据库？怎么创建数据库？怎么创建表？
这一篇呢我们就来看看怎么在我们创建的表中插入数据、删除数据和修改数据。也就是上一篇文章中提到的DML 数据操作语言
准备

根据上一章所说的，我们创建一个db_xiezhr 数据库，并向数据库中添加一张用户信息表。

① 通过命令行建库，建表

1. mysql> create database if not exists db_xiezhr;
2. Query OK, 1 row affected (0.00 sec)

复制代码

1. mysql> use db_xiezhr
2. Database changed
3. mysql> drop table if exists user_profile;
4. Query OK, 0 rows affected, 1 warning (0.00 sec)
6. mysql> CREATE TABLE `user_profile`
7.                                    (`id` int NOT NULL,
8.                                    `device_id` int NOT NULL,
9.                                    `user_name` varchar(100) NOT NULL ,
10.                                    `gender` varchar(14) NOT NULL,
11.                                    `age` int ,
12.                                    `user_email` varchar(50) ,
13.                                    `user_zip` varchar(10),
14.                                    `university` varchar(32) NOT NULL,
15.                                    `province` varchar(32)  NOT NULL,);
16. Query OK, 0 rows affected (0.01 sec)

复制代码

② 当然了，我们可以通过上一篇文章中说到的工具之一建库、建表
这里以sqlyog工具为例，建立db_xiezhr数据库，并创建用user_profile户信息表

• 建立db_xiezhr数据库
  
  
  
  
  
• 创建用user_profile户信息表
  
  
  
  
  以上，我们数据库和数据表就都已经创建成功了
  

插入数据

语法格式

1. #方式一：
2. INSERT INTO 表名 VALUES(值,...);
3. #方式二：
4. INSERT INTO 表名(字段名,...) VALUES(值,...);
5. #方式三：
6. INSERT INTO 表名(字段名,...) SELECT (字段名,...) FROM 表名;

复制代码

插入完整行数据

①简单写法

• 存储到每个里表中的数据在VALUES子句中给出，值得顺序也必须要与定义时候的字段顺序一致，没有的值需要使用NULL。
  
• 这种写法简单，但并不安全，应尽量避免使用。一旦表结构变化了，可能就会出问题
  

1. # 向user_profile用户信息表中添加一条数据
2. insert into user_profile
3. values(
4.         1,
5.         1,
6.         '张三',
7.         'male',
8.         '28',
9.         null,
10.         null,
11.         '北京大学',
12.         'BeiJing'
13. );

复制代码

② 安全写法

• 跟上面简单写法效果一样，往用户信息表中插入一条数据
  
• 在表名后括号中明确给出了列名，在插入数据时，将用VALUES列表中的相应值填入对应的列名中。第一个列名对应第一个值，第二个列名对应第二个值......
  
• 因为提供了列名，所以值也不需要按照表中的次序来插入。即使表结构变化了，sql语句也能正常执行。
  
• 不需要插入的列user_email和user_zip 列，值也可以不用写出来了，也比较灵活
  

1. INSERT INTO `db_xiezhr`.`user_profile` (
2.   `id`,
3.   `device_id`,
4.   `user_name`,
5.   `gender`,
6.   `age`,
7.   `university`,
8.   `province`
9. )
10. VALUES
11.   (
12.     1,
13.     1,
14.     '张三',
15.     'male',
16.     '28',
17.     '北京大学',
18.     'BeiJing'
19.   ) ;

复制代码

注：

• 不管使用哪种insert语法，都必须给出values的正确数目。
  
• 如果不提供列名，则必须给每个列都提供一个值
  
• 如果提供列名，则必须对每个写出的列提供一个值
  
• 在insert语法中省略某列时，需要具备两个条件，一是该列被定义为允许NULL值二是表定义中给出了默认值
  

插入多行数据

这里你可能说我多写几条sql语句不就可以了么，哈哈，这也可以实现了么？

1. INSERT INTO `user_profile` (
2.   `id`,
3.   `device_id`,
4.   `user_name`,
5.   `gender`,
6.   `age`,
7.   `university`,
8.   `province`
9. )
10. VALUES
11.   (
12.     2,
13.     2,
14.     '李四',
15.     'male',
16.     '36',
17.     '天津大学',
18.     'TianJin'
19.   ) ;
20.   
21. INSERT INTO `user_profile` (
22.   `id`,
23.   `device_id`,
24.   `user_name`,
25.   `gender`,
26.   `age`,
27.   `university`,
28.   `province`
29. )
30. VALUES
31.   (
32.     3,
33.     3,
34.     '王五',
35.     'female',
36.     '25',
37.     '天津大学',
38.     'TianJin'
39.   ) ;  
40.   

复制代码

但这里提供另一种语法,一条语句就可以完成

1. INSERT INTO `user_profile` (
2.   `id`,
3.   `device_id`,
4.   `user_name`,
5.   `gender`,
6.   `age`,
7.   `university`,
8.   `province`
9. )
10. VALUES
11.   (
12.     2,
13.     2,
14.     '李四',
15.     'male',
16.     '36',
17.     '天津大学',
18.     'TianJin'
19.   ),
20.   (
21.     3,
22.     3,
23.     '王五',
24.     'female',
25.     '25',
26.     '天津大学',
27.     'TianJin'
28.   ) ;  
29.   

复制代码

将检索出来的数据插入表

这里有两张表，一张是user_profile，数据如下

一张表是tmp_user_profile，数据如下

我们需要将tmp_user_profile 表中数据插入到user_profile 表中,可以通过如下脚本实现

1. INSERT INTO `user_profile` (
2.   `id`,
3.   `device_id`,
4.   `user_name`,
5.   `gender`,
6.   `age`,
7.   `university`,
8.   `province`
9. )
10. SELECT
11.   `id`,
12.   `device_id`,
13.   `user_name`,
14.   `gender`,
15.   `age`,
16.   `university`,
17.   `province`
18.   FROM tmp_user_profile;

复制代码

执行完上面脚本后，user_profile表中数据

更新数据

准备两张表

• 下面我们建两张表，一张表为 product 表，用来存放产品信息，其中有产品价格字段 saleprice；另外一张表是 product_price 表。
  

1. # 产品信息表product
2. mysql> select * from product;
3. +----+-----------+--------------------------------------------+-----------+--------+
4. | id | productid | productname                                | saleprice | author |
5. +----+-----------+--------------------------------------------+-----------+--------+
6. |  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       100 | xiezhr |
7. |  2 | 10002     | 公众号XiezhrSpace【MySQL从入门到放弃】     |        99 | xiezhr |
8. |  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr |
9. |  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr |
10. |  5 | 10005     | 公众号XiezhrSpace【Idea从入门到上瘾】      |       200 | xiezhr |
11. |  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr |
12. +----+-----------+--------------------------------------------+-----------+--------+
13. 6 rows in set (0.02 sec)
15. # 产品价格表product_price
16. mysql> select * from product_price;
17. +----+-----------+-------+
18. | id | productid | price |
19. +----+-----------+-------+
20. |  1 | 10001     |  NULL |
21. |  2 | 10002     |  NULL |
22. |  3 | 10003     |  NULL |
23. |  4 | 10004     |  NULL |
24. |  5 | 10005     |  NULL |
25. |  6 | 1006      |  NULL |
26. +----+-----------+-------+
27. 6 rows in set (0.00 sec)

复制代码

语法

1. # 1、单表更新
2. UPDATE 表名 SET 列 = 值,... WHERE 查询条件;

复制代码

1. # 2、根据一张表更新另一张表
2. # ① 使用update
3. UPDATE
4.   表1 别名,
5.   表2 别名
6. SET
7.   列 = 值,
8.   ...
9. WHERE 连接条件 AND 筛选条件 ;
10. # ②通过INNER JOIN
11. UPDATE
12.   表1 别名
13. INNER JOIN 表2 别名
14. ON 连接条件 AND 筛选条件
15. SET
16.   列 = 值,
17.   ...；
18. # ③ 通过LEFT JOIN
19. UPDATE
20.   表1 别名
21. LEFT JOIN  表2 别名
22. ON 连接条件 AND 筛选条件
23. SET
24.   列 = 值,
25.   ...；
26. # ③ 通过子查询
27. UPDATE
28.   表2 别名
29. SET 列 = (SELECT 表达式 FROM 表1 WHERE 连接条件 AND 筛选条件)；

复制代码

1. # 2、同时更新两张表
2. UPDATE
3.   表1
4. INNER JOIN 表2
5. ON 连接条件 AND 筛选条件
6. SET 表1.列=值1,
7.         表2.列=值2;

复制代码

实践操作

① 将产品信息表product 中10001 号产品价格更新为999

1. mysql> UPDATE product t SET t.`saleprice` =999 WHERE t.`productid` = '10001';
2. Query OK, 1 row affected (0.01 sec)
3. Rows matched: 1  Changed: 1  Warnings: 0
5. mysql> select * from product;
6. +----+-----------+--------------------------------------------+-----------+--------+
7. | id | productid | productname                                | saleprice | author |
8. +----+-----------+--------------------------------------------+-----------+--------+
9. |  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       999 | xiezhr |
10. |  2 | 10002     | 公众号XiezhrSpace【MySQL从入门到放弃】     |        99 | xiezhr |
11. |  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr |
12. |  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr |
13. |  5 | 10005     | 公众号XiezhrSpace【Idea从入门到上瘾】      |       200 | xiezhr |
14. |  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr |
15. +----+-----------+--------------------------------------------+-----------+--------+
16. 6 rows in set (0.00 sec)

复制代码

② 将 product_price 表中的价格字段 price 更新为 product 表中价格字段 price 的 80%。

1. # 使用“UPDATE table1 t1,table2,...,table n”的方式来多表更新
2. mysql> UPDATE product t1, product_price t2 SET t2.price = t1.`saleprice` * 0.8 WHERE t1.productid= t2.productId;
3. Query OK, 5 rows affected, 2 warnings (0.01 sec)
4. Rows matched: 5  Changed: 5  Warnings: 2
6. mysql> select * from product_price;
7. +----+-----------+-------+
8. | id | productid | price |
9. +----+-----------+-------+
10. |  1 | 10001     |    80 |
11. |  2 | 10002     |    79 |
12. |  3 | 10003     |    70 |
13. |  4 | 10004     |   120 |
14. |  5 | 10005     |   160 |
15. |  6 | 1006      |  NULL |
16. +----+-----------+-------+
17. 6 rows in set (0.00 sec)
19. # 通过INNER JOIN
20. mysql> select * from product_price;
21. +----+-----------+-------+
22. | id | productid | price |
23. +----+-----------+-------+
24. |  1 | 10001     |    80 |
25. |  2 | 10002     |    79 |
26. |  3 | 10003     |    70 |
27. |  4 | 10004     |   120 |
28. |  5 | 10005     |   160 |
29. |  6 | 1006      |  NULL |
30. +----+-----------+-------+
31. 6 rows in set (0.00 sec)
32. #  通过LEFT JOIN
33. mysql> UPDATE product t1 LEFT JOIN product_price t2 ON t1.productid= t2.productid SET t2.price = t1.`saleprice` * 0.8 WHERE t1.productid='10001';
34. Query OK, 1 row affected (0.01 sec)
35. Rows matched: 1  Changed: 1  Warnings: 0
37. mysql> select * from product_price;
38. +----+-----------+-------+
39. | id | productid | price |
40. +----+-----------+-------+
41. |  1 | 10001     |    80 |
42. |  2 | 10002     |  NULL |
43. |  3 | 10003     |  NULL |
44. |  4 | 10004     |  NULL |
45. |  5 | 10005     |  NULL |
46. |  6 | 1006      |  NULL |
47. +----+-----------+-------+
48. 6 rows in set (0.00 sec)
49. # 通过子查询
50. mysql> UPDATE product_price t2 SET t2.price=(SELECT t1.`saleprice` *0.8 FROM product t1 WHERE t1.productid = t2.productid);
51. Query OK, 5 rows affected, 2 warnings (0.01 sec)
52. Rows matched: 6  Changed: 5  Warnings: 2
54. mysql> select * from product_price;
55. +----+-----------+-------+
56. | id | productid | price |
57. +----+-----------+-------+
58. |  1 | 10001     |    80 |
59. |  2 | 10002     |    79 |
60. |  3 | 10003     |    70 |
61. |  4 | 10004     |   120 |
62. |  5 | 10005     |   160 |
63. |  6 | 1006      |  NULL |
64. +----+-----------+-------+
65. 6 rows in set (0.00 sec)

复制代码

③ 同时更新两张表(正式开发中用得比较少)

• 两张表做关联，同时更新了 product_price 表的 price 字段和 product 表的 author两个字段。
  

1. mysql> UPDATE product t1 INNER JOIN product_price t2 ON t1.productid= t2.productid SET t2.price = t1.`saleprice` * 0.8, t1.`author` = 'xiezhr001';
2. Query OK, 5 rows affected, 2 warnings (0.00 sec)
3. Rows matched: 10  Changed: 5  Warnings: 2
5. mysql> select * from product;
6. +----+-----------+--------------------------------------------+-----------+-----------+
7. | id | productid | productname                                | saleprice | author    |
8. +----+-----------+--------------------------------------------+-----------+-----------+
9. |  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       100 | xiezhr001 |
10. |  2 | 10002     | 公众号XiezhrSpace【MySQL从入门到放弃】     |        99 | xiezhr001 |
11. |  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
12. |  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr001 |
13. |  5 | 10005     | 公众号XiezhrSpace【Idea从入门到上瘾】      |       200 | xiezhr001 |
14. |  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr    |
15. +----+-----------+--------------------------------------------+-----------+-----------+
16. 6 rows in set (0.00 sec)
18. mysql> select * from product_price;
19. +----+-----------+-------+
20. | id | productid | price |
21. +----+-----------+-------+
22. |  1 | 10001     |    80 |
23. |  2 | 10002     |    79 |
24. |  3 | 10003     |    70 |
25. |  4 | 10004     |   120 |
26. |  5 | 10005     |   160 |
27. |  6 | 1006      |  NULL |
28. +----+-----------+-------+
29. 6 rows in set (0.00 sec)

复制代码

删除数据

语法

1. # 1、单表删除
2. DELETE FROM 表名 【WHERE 筛选条件 】;

复制代码

1. # 2、多表删除（级联删除）
2. ① 关联删除
3. DELETE
4.   表1的别名,
5.   表2的别名
6. FROM
7.   表1 别名,
8.   表2 别名
9. WHERE 连接条件 AND 筛选条件 ;
10. ② 内连接、左右连接删除
11. DELETE
12.   表1的别名,
13.   表2的别名
14. FROM
15.   表1 别名
16. INNER | LEFT | RIGHT JOIN 表2 别名 ON 连接条件
17. WHERE 筛选条件 ;

复制代码

实践操作

① 单表删除

1. # 删除产品id为10005的产品信息
2. mysql> select * from product;
3. +----+-----------+--------------------------------------------+-----------+-----------+
4. | id | productid | productname                                | saleprice | author    |
5. +----+-----------+--------------------------------------------+-----------+-----------+
6. |  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       100 | xiezhr001 |
7. |  2 | 10002     | 公众号XiezhrSpace【MySQL从入门到放弃】     |        99 | xiezhr001 |
8. |  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
9. |  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr001 |
10. |  5 | 10005     | 公众号XiezhrSpace【Idea从入门到上瘾】      |       200 | xiezhr001 |
11. |  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr    |
12. +----+-----------+--------------------------------------------+-----------+-----------+
13. 6 rows in set (0.00 sec)
15. mysql> delete from product where productid = '10005';
16. Query OK, 1 row affected (0.02 sec)
18. mysql> select * from product;
19. +----+-----------+--------------------------------------------+-----------+-----------+
20. | id | productid | productname                                | saleprice | author    |
21. +----+-----------+--------------------------------------------+-----------+-----------+
22. |  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       100 | xiezhr001 |
23. |  2 | 10002     | 公众号XiezhrSpace【MySQL从入门到放弃】     |        99 | xiezhr001 |
24. |  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
25. |  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr001 |
26. |  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr    |
27. +----+-----------+--------------------------------------------+-----------+-----------+
28. 5 rows in set (0.00 sec)

复制代码

② 多表级联删除

1. mysql> select * from product;
2. +----+-----------+--------------------------------------------+-----------+-----------+
3. | id | productid | productname                                | saleprice | author    |
4. +----+-----------+--------------------------------------------+-----------+-----------+
5. |  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       100 | xiezhr001 |
6. |  2 | 10002     | 公众号XiezhrSpace【MySQL从入门到放弃】     |        99 | xiezhr001 |
7. |  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
8. |  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr001 |
9. |  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr    |
10. +----+-----------+--------------------------------------------+-----------+-----------+
11. 5 rows in set (0.00 sec)
13. mysql> select * from product_price;
14. +----+-----------+-------+
15. | id | productid | price |
16. +----+-----------+-------+
17. |  1 | 10001     |    80 |
18. |  2 | 10002     |    79 |
19. |  3 | 10003     |    70 |
20. |  4 | 10004     |   120 |
21. |  5 | 10005     |   160 |
22. |  6 | 1006      |  NULL |
23. +----+-----------+-------+
24. 6 rows in set (0.00 sec)
26. mysql> DELETE  t1,  t2 FROM  product t1,  product_price t2 WHERE t1.`productid` = t2.`productid` AND t1.`productid` = '10002';
27. Query OK, 2 rows affected (0.01 sec)
29. mysql> select * from product;
30. +----+-----------+--------------------------------------------+-----------+-----------+
31. | id | productid | productname                                | saleprice | author    |
32. +----+-----------+--------------------------------------------+-----------+-----------+
33. |  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       100 | xiezhr001 |
34. |  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
35. |  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr001 |
36. |  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr    |
37. +----+-----------+--------------------------------------------+-----------+-----------+
38. 4 rows in set (0.00 sec)
40. mysql> select * from product_price;
41. +----+-----------+-------+
42. | id | productid | price |
43. +----+-----------+-------+
44. |  1 | 10001     |    80 |
45. |  3 | 10003     |    70 |
46. |  4 | 10004     |   120 |
47. |  5 | 10005     |   160 |
48. |  6 | 1006      |  NULL |
49. +----+-----------+-------+
50. 5 rows in set (0.00 sec)

复制代码

1. mysql> select * from product;
2. +----+-----------+--------------------------------------------+-----------+-----------+
3. | id | productid | productname                                | saleprice | author    |
4. +----+-----------+--------------------------------------------+-----------+-----------+
5. |  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       100 | xiezhr001 |
6. |  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
7. |  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr001 |
8. |  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr    |
9. +----+-----------+--------------------------------------------+-----------+-----------+
10. 4 rows in set (0.00 sec)
12. mysql> select * from product_price;
13. +----+-----------+-------+
14. | id | productid | price |
15. +----+-----------+-------+
16. |  1 | 10001     |    80 |
17. |  3 | 10003     |    70 |
18. |  4 | 10004     |   120 |
19. |  5 | 10005     |   160 |
20. |  6 | 1006      |  NULL |
21. +----+-----------+-------+
22. 5 rows in set (0.00 sec)
24. mysql> DELETE   t1,  t2 FROM  product_price t2 INNER JOIN product t1 ON t1.`productid` = t2.`productid` WHERE t1.`productid` = '10003';
25. Query OK, 2 rows affected (0.01 sec)
27. mysql> select * from product;
28. +----+-----------+-------------------------------------------+-----------+-----------+
29. | id | productid | productname                               | saleprice | author    |
30. +----+-----------+-------------------------------------------+-----------+-----------+
31. |  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】   |       100 | xiezhr001 |
32. |  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】     |       150 | xiezhr001 |
33. |  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】 |       120 | xiezhr    |
34. +----+-----------+-------------------------------------------+-----------+-----------+
35. 3 rows in set (0.00 sec)
37. mysql> select * from product_price;
38. +----+-----------+-------+
39. | id | productid | price |
40. +----+-----------+-------+
41. |  1 | 10001     |    80 |
42. |  4 | 10004     |   120 |
43. |  5 | 10005     |   160 |
44. |  6 | 1006      |  NULL |
45. +----+-----------+-------+
46. 4 rows in set (0.00 sec)

复制代码

小结

• 这篇文章主要说了MySQL中单表多表的增删改，在update、delete 使用的时候一定要细心
  
• 除非打算更新删除表中所有数据，否则绝对不要使用不带where子句的update或delete语句
  
• 保证每个表都有主键
  
• 在对update或delete语句使用where子句之前，先用select进行查询测试，保证过滤出来的记录是正确的；
  
• update或delete语句执行前，尽量做好数据备份
  

来源:https://www.cnblogs.com/xiezhr/p/17343314.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！