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

前言

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

准备

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

① 通过命令行建库，建表

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

mysql> use db_xiezhr
Database changed
mysql> drop table if exists user_profile;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `user_profile` 
				   (`id` int NOT NULL,
				   `device_id` int NOT NULL,
				   `user_name` varchar(100) NOT NULL ,
				   `gender` varchar(14) NOT NULL,
				   `age` int ,
				   `user_email` varchar(50) ,
				   `user_zip` varchar(10),
				   `university` varchar(32) NOT NULL,
				   `province` varchar(32)  NOT NULL,);
Query OK, 0 rows affected (0.01 sec)

② 当然了，我们可以通过上一篇文章中说到的工具之一建库、建表

这里以sqlyog工具为例，建立db_xiezhr数据库，并创建用user_profile户信息表

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

插入数据

语法格式

#方式一：
INSERT INTO 表名 VALUES(值,...);
#方式二：
INSERT INTO 表名(字段名,...) VALUES(值,...);
#方式三：
INSERT INTO 表名(字段名,...) SELECT (字段名,...) FROM 表名;

插入完整行数据

①简单写法

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

# 向user_profile用户信息表中添加一条数据
insert into user_profile 
values( 
	1,
	1,
	'张三',
	'male',
	'28',
	null,
	null,
	'北京大学',
	'BeiJing'
);

② 安全写法

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

INSERT INTO `db_xiezhr`.`user_profile` (
  `id`,
  `device_id`,
  `user_name`,
  `gender`,
  `age`,
  `university`,
  `province`
) 
VALUES
  (
    1,
    1,
    '张三',
    'male',
    '28',
    '北京大学',
    'BeiJing'
  ) ;

注：

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

插入多行数据

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

INSERT INTO `user_profile` (
  `id`,
  `device_id`,
  `user_name`,
  `gender`,
  `age`,
  `university`,
  `province`
) 
VALUES
  (
    2,
    2,
    '李四',
    'male',
    '36',
    '天津大学',
    'TianJin'
  ) ;
  
INSERT INTO `user_profile` (
  `id`,
  `device_id`,
  `user_name`,
  `gender`,
  `age`,
  `university`,
  `province`
) 
VALUES
  (
    3,
    3,
    '王五',
    'female',
    '25',
    '天津大学',
    'TianJin'
  ) ;  
  

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

INSERT INTO `user_profile` (
  `id`,
  `device_id`,
  `user_name`,
  `gender`,
  `age`,
  `university`,
  `province`
) 
VALUES
  (
    2,
    2,
    '李四',
    'male',
    '36',
    '天津大学',
    'TianJin'
  ),
  (
    3,
    3,
    '王五',
    'female',
    '25',
    '天津大学',
    'TianJin'
  ) ;  
  

将检索出来的数据插入表

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

一张表是tmp_user_profile，数据如下

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

INSERT INTO `user_profile` (
  `id`,
  `device_id`,
  `user_name`,
  `gender`,
  `age`,
  `university`,
  `province`
) 
SELECT 
  `id`,
  `device_id`,
  `user_name`,
  `gender`,
  `age`,
  `university`,
  `province` 
  FROM tmp_user_profile;

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

更新数据

准备两张表

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

# 产品信息表product
mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+--------+
| id | productid | productname                                | saleprice | author |
+----+-----------+--------------------------------------------+-----------+--------+
|  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       100 | xiezhr |
|  2 | 10002     | 公众号XiezhrSpace【MySQL从入门到放弃】     |        99 | xiezhr |
|  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr |
|  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr |
|  5 | 10005     | 公众号XiezhrSpace【Idea从入门到上瘾】      |       200 | xiezhr |
|  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr |
+----+-----------+--------------------------------------------+-----------+--------+
6 rows in set (0.02 sec)

# 产品价格表product_price 
mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |  NULL |
|  2 | 10002     |  NULL |
|  3 | 10003     |  NULL |
|  4 | 10004     |  NULL |
|  5 | 10005     |  NULL |
|  6 | 1006      |  NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)

语法

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

# 2、根据一张表更新另一张表
# ① 使用update
UPDATE 
  表1 别名,
  表2 别名 
SET
  列 = 值,
  ...
WHERE 连接条件 AND 筛选条件 ;
# ②通过INNER JOIN
UPDATE
  表1 别名
INNER JOIN 表2 别名
ON 连接条件 AND 筛选条件
SET
  列 = 值,
  ...；
# ③ 通过LEFT JOIN
UPDATE
  表1 别名
LEFT JOIN  表2 别名
ON 连接条件 AND 筛选条件
SET
  列 = 值,
  ...；
# ③ 通过子查询
UPDATE
  表2 别名
SET 列 = (SELECT 表达式 FROM 表1 WHERE 连接条件 AND 筛选条件)；

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

实践操作

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

mysql> UPDATE product t SET t.`saleprice` =999 WHERE t.`productid` = '10001';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+--------+
| id | productid | productname                                | saleprice | author |
+----+-----------+--------------------------------------------+-----------+--------+
|  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       999 | xiezhr |
|  2 | 10002     | 公众号XiezhrSpace【MySQL从入门到放弃】     |        99 | xiezhr |
|  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr |
|  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr |
|  5 | 10005     | 公众号XiezhrSpace【Idea从入门到上瘾】      |       200 | xiezhr |
|  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr |
+----+-----------+--------------------------------------------+-----------+--------+
6 rows in set (0.00 sec)

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

# 使用“UPDATE table1 t1,table2,...,table n”的方式来多表更新
mysql> UPDATE product t1, product_price t2 SET t2.price = t1.`saleprice` * 0.8 WHERE t1.productid= t2.productId;
Query OK, 5 rows affected, 2 warnings (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 2

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  2 | 10002     |    79 |
|  3 | 10003     |    70 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)

# 通过INNER JOIN
mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  2 | 10002     |    79 |
|  3 | 10003     |    70 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)
#  通过LEFT JOIN
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';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  2 | 10002     |  NULL |
|  3 | 10003     |  NULL |
|  4 | 10004     |  NULL |
|  5 | 10005     |  NULL |
|  6 | 1006      |  NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)
# 通过子查询
mysql> UPDATE product_price t2 SET t2.price=(SELECT t1.`saleprice` *0.8 FROM product t1 WHERE t1.productid = t2.productid);
Query OK, 5 rows affected, 2 warnings (0.01 sec)
Rows matched: 6  Changed: 5  Warnings: 2

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  2 | 10002     |    79 |
|  3 | 10003     |    70 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)

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

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

mysql> UPDATE product t1 INNER JOIN product_price t2 ON t1.productid= t2.productid SET t2.price = t1.`saleprice` * 0.8, t1.`author` = 'xiezhr001';
Query OK, 5 rows affected, 2 warnings (0.00 sec)
Rows matched: 10  Changed: 5  Warnings: 2

mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname                                | saleprice | author    |
+----+-----------+--------------------------------------------+-----------+-----------+
|  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       100 | xiezhr001 |
|  2 | 10002     | 公众号XiezhrSpace【MySQL从入门到放弃】     |        99 | xiezhr001 |
|  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
|  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr001 |
|  5 | 10005     | 公众号XiezhrSpace【Idea从入门到上瘾】      |       200 | xiezhr001 |
|  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr    |
+----+-----------+--------------------------------------------+-----------+-----------+
6 rows in set (0.00 sec)

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  2 | 10002     |    79 |
|  3 | 10003     |    70 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)

删除数据

语法

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

# 2、多表删除（级联删除）
① 关联删除
DELETE 
  表1的别名,
  表2的别名 
FROM
  表1 别名,
  表2 别名 
WHERE 连接条件 AND 筛选条件 ;
② 内连接、左右连接删除
DELETE 
  表1的别名,
  表2的别名 
FROM
  表1 别名 
INNER | LEFT | RIGHT JOIN 表2 别名 ON 连接条件 
WHERE 筛选条件 ;

实践操作

① 单表删除

# 删除产品id为10005的产品信息
mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname                                | saleprice | author    |
+----+-----------+--------------------------------------------+-----------+-----------+
|  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       100 | xiezhr001 |
|  2 | 10002     | 公众号XiezhrSpace【MySQL从入门到放弃】     |        99 | xiezhr001 |
|  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
|  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr001 |
|  5 | 10005     | 公众号XiezhrSpace【Idea从入门到上瘾】      |       200 | xiezhr001 |
|  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr    |
+----+-----------+--------------------------------------------+-----------+-----------+
6 rows in set (0.00 sec)

mysql> delete from product where productid = '10005';
Query OK, 1 row affected (0.02 sec)

mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname                                | saleprice | author    |
+----+-----------+--------------------------------------------+-----------+-----------+
|  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       100 | xiezhr001 |
|  2 | 10002     | 公众号XiezhrSpace【MySQL从入门到放弃】     |        99 | xiezhr001 |
|  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
|  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr001 |
|  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr    |
+----+-----------+--------------------------------------------+-----------+-----------+
5 rows in set (0.00 sec)

② 多表级联删除

mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname                                | saleprice | author    |
+----+-----------+--------------------------------------------+-----------+-----------+
|  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       100 | xiezhr001 |
|  2 | 10002     | 公众号XiezhrSpace【MySQL从入门到放弃】     |        99 | xiezhr001 |
|  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
|  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr001 |
|  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr    |
+----+-----------+--------------------------------------------+-----------+-----------+
5 rows in set (0.00 sec)

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  2 | 10002     |    79 |
|  3 | 10003     |    70 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
6 rows in set (0.00 sec)

mysql> DELETE  t1,  t2 FROM  product t1,  product_price t2 WHERE t1.`productid` = t2.`productid` AND t1.`productid` = '10002';
Query OK, 2 rows affected (0.01 sec)

mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname                                | saleprice | author    |
+----+-----------+--------------------------------------------+-----------+-----------+
|  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       100 | xiezhr001 |
|  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
|  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr001 |
|  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr    |
+----+-----------+--------------------------------------------+-----------+-----------+
4 rows in set (0.00 sec)

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  3 | 10003     |    70 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
5 rows in set (0.00 sec)

mysql> select * from product;
+----+-----------+--------------------------------------------+-----------+-----------+
| id | productid | productname                                | saleprice | author    |
+----+-----------+--------------------------------------------+-----------+-----------+
|  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】    |       100 | xiezhr001 |
|  3 | 10003     | 公众号XiezhrSpace【快速上手Linux核心命令】 |        88 | xiezhr001 |
|  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】      |       150 | xiezhr001 |
|  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】  |       120 | xiezhr    |
+----+-----------+--------------------------------------------+-----------+-----------+
4 rows in set (0.00 sec)

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  3 | 10003     |    70 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
5 rows in set (0.00 sec)

mysql> DELETE   t1,  t2 FROM  product_price t2 INNER JOIN product t1 ON t1.`productid` = t2.`productid` WHERE t1.`productid` = '10003';
Query OK, 2 rows affected (0.01 sec)

mysql> select * from product;
+----+-----------+-------------------------------------------+-----------+-----------+
| id | productid | productname                               | saleprice | author    |
+----+-----------+-------------------------------------------+-----------+-----------+
|  1 | 10001     | 公众号XiezhrSpace【Oralce从入门到放弃】   |       100 | xiezhr001 |
|  4 | 10004     | 公众号XiezhrSpace【Java从入门到精通】     |       150 | xiezhr001 |
|  6 | 10006     | 公众号XiezhrSpace【如何快速搭建个人博客】 |       120 | xiezhr    |
+----+-----------+-------------------------------------------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> select * from product_price;
+----+-----------+-------+
| id | productid | price |
+----+-----------+-------+
|  1 | 10001     |    80 |
|  4 | 10004     |   120 |
|  5 | 10005     |   160 |
|  6 | 1006      |  NULL |
+----+-----------+-------+
4 rows in set (0.00 sec)

小结

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