【MySQL — 数据库增删改查操作】深入解析MySQL的 Update 和 Delete 操作

   

---

 1. 测试数据

---

mysql> select* from exam1;
+------+-----------+---------+------+---------+
| id   | name      | Chinese | Math | English |
+------+-----------+---------+------+---------+
|    1 | 唐三藏    |    67.0 | 98.0 |    56.0 |
|    2 | 孙悟空    |    87.0 | 78.0 |    77.0 |
|    3 | 猪悟能    |    88.0 | 98.0 |    90.0 |
|    4 | 曹孟德    |    82.0 | 84.0 |    67.0 |
|    5 | 刘玄德    |    55.0 | 85.0 |    45.0 |
|    6 | 孙权      |    70.0 | 73.0 |    78.0 |
|    7 | 宋公明    |    75.0 | 65.0 |    30.0 |
| NULL | NULL      |    NULL | NULL |    90.0 |
+------+-----------+---------+------+---------+
8 rows in set (0.00 sec)

---

2. Update 修改

---

update操作，是真正在改硬盘了；这样的修改是"持久有效" 的，所以一定要确保 update 的修改是改对的；

update 表名 set 列名 = 值,列名 = 值.....;

---

语法 

---

UPDATE [LOW_PRIORITY] [IGNORE] table_referenceSET assignment[，assignment]...        [WHERE where_condition][ORDER BY ...][LIMIT row_count]-- set 在这里的意思是设置

---

示例 

---

更新操作

---

    一次修改一个列     

---

    把孙悟空数学成绩修改成 80    

update exam1 set Math = 80 where name = '孙悟空';Query OK, 1 row affected (0.06 sec)Rows matched: 1  Changed: 1  Warnings: 0  -- 匹配和修改一行数据

---

    把所有数学成绩在原有基础上加10分    

update exam1 set Math = Math + 10 where id is not null;ERROR 1264 (22003): Out of range value for column 'Math' at row 1-- 超出 decimal(3,1)的范围，此时是真正修改硬盘数据，所以修改失败

---

     把所有数学成绩在原有基础上减10分    

update exam1 set Math = Math - 10 ;Query OK, 7 rows affected (0.01 sec)Rows matched: 8  Changed: 7  Warnings: 0-- 匹配的行数和修改的行数不同，说明如果修改的数据基础是 null，则无法修改

---

    一次修改多个列      

---

    把曹孟德的语文成绩修改为60，数学成绩修改为70    

update exam1 set Chinese = 60 , Math = 70 where name = '曹孟德';Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0

---

    引入 order by 操作     

---

    将总成绩倒数前三的3位同学的数学成绩加上20分     

-- 查看总分倒数前三的同学select* from exam1 where Chinese + Math + English is not null order by  Chinese + Math + English limit 3;-- 更新总分倒数前三的同学的数学成绩update exam1 set Math = Math + 20 where Chinese + Math + English is not null order by  Chinese + Math + English limit 3;

---

    特殊情况：好几个同学成绩相同，倒数前三的成绩里有不止3个同学     

---

Update 注意事项

---

---

3. Delete 删除

---

语法 

---

DELETE FROM tbl_name  [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]delete from 表名 where 条件/ order by / limit;

---

示例 

---

删除操作

---

    删除孙悟空同学的考试成绩    

---

delete from exam1 where name = '孙悟空';Query OK, 1 row affected (0.01 sec)

---

Delete 注意事项

---

---