前言
前面的两篇文章中,我们已经对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语句执行前,尽量做好数据备份
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器