【MySQL】表的改，删-优快云博客

本文链接：https://blog.youkuaiyun.com/Sakura_ding/article/details/146293520

CRUD：Create(创建), Retrieve(读取), Update(更新), Delete (删除)

6.3 Update

语法：

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

对查询到的结果进行列值更新

6.3.1 将孙悟空同学的数学成绩变更为80分

-- 更新值为具体值
-- 查看原数据
SELECT name, math FROM exam_result WHERE name = '孙悟空';
+--------+------+
| name   | math |
+--------+------+
| 孙悟空 | 78   |
+--------+------+
1 row in set (0.00 sec)

-- 数据更新
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  warnings: 0

-- 查看更新后数据
SELECT name, math FROM exam_result WHERE name = '孙悟空';
+--------+------+
| name   | math |
+--------+------+
| 孙悟空 | 80   |
+--------+------+
1 row in set (0.00 sec)

6.3.2 将曹孟德同学的数学成绩变更为60分，语文成绩变更为70分

-- 一次更新多个列
-- 查看原数据
SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德';
+--------+------+---------+
| name   | math | chinese |
+--------+------+---------+
| 曹孟德 | 84   | 82      |
+--------+------+---------+
1 row in set (0.00 sec)

-- 数据更新
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1  warnings: 0

-- 查看更新后数据
SELECT name, math, chinese FROM exam_result WHERE name = '曹孟德';
+--------+------+---------+
| name   | math | chinese |
+--------+------+---------+
| 曹孟德 | 60   | 70      |
+--------+------+---------+
1 row in set (0.00 sec)

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

-- 更新值为原值基础上变更
-- 查看原数据
-- 别名可以在ORDER BY中使用
SELECT name, math,chinese + math + english 总分 FROM exam_result
ORDER BY 总分 LIMIT 3;
+--------+------+------+
| name   | math | 总分 |
+--------+------+------+
| 宋公明 | 65   | 170  |
| 刘宏德 | 85   | 180  |
| 曹孟德 | 60   | 197  |
+--------+------+------+
3 rows in set (0.00 sec)

-- 数据更新，支持 math += 30 这种语法
UPDATE exam_result SET math = math + 30
ORDER BY chinese + math + english LIMIT 3;

-- 查看更新后数据
-- 思考: 这里还可以按总分升序排序取前 3 个么?
SELECT name, math,(chinese + math + english) 总分 FROM exam_result
WHERE name IN ('宋公明', '刘宏德', '曹孟德');
+--------+------+------+
| name   | math | 总分 |
+--------+------+------+
| 宋公明 | 95   | 200  |
| 曹孟德 | 90   | 227  |
| 刘宏德 | 115  | 210  |
+--------+------+------+
3 rows in set (0.00 sec)

-- 按总成绩排序后查询结果
SELECT name, math,chinese + math + english 总分 FROM exam_result
ORDER BY 总分 LIMIT 3;
+--------+------+------+
| name   | math | 总分 |
+--------+------+------+
| 宋公明 | 95   | 200  |
| 刘宏德 | 115  | 200  |
| 唐三藏 | 98   | 221  |
+--------+------+------+
3 rows in set (0.00 sec)

6.3.4 将所有同学的语文成绩更新为原来的2倍

注意：更新全表的语句慎用！

-- 没有 WHERE 子句，则更新全表
-- 查看原数据
SELECT * FROM exam_result;
+----+--------+---------+------+--------+
| id | name   | chinese | math | english|
+----+--------+---------+------+--------+
| 1  | 唐三藏 | 67      | 80   | 56     |
| 2  | 孙悟空 | 87      | 98   | 77     |
| 3  | 猪悟能 | 88      | 90   | 90     |
| 4  | 刘玄德 | 70      | 110  | 67     |
| 5  | 曹孟德 | 55      | 90   | 45     |
| 6  | 孙权  | 50      | 73   | 78     |
| 7  | 宋公明 | 00      | 95   | 70     |
+----+--------+---------+------+--------+
7 rows in set (0.00 sec)

-- 数据更新
UPDATE exam_result SET chinese = chinese * 2;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  warnings: 0

-- 查看更新后数据
SELECT * FROM exam_result;
+----+--------+---------+------+--------+
| id | name   | chinese | math | english|
+----+--------+---------+------+--------+
| 1  | 唐三藏 | 134     | 80   | 56     |
| 2  | 孙悟空 | 174     | 98   | 77     |
| 3  | 猪悟能 | 176     | 90   | 90     |
| 4  | 刘玄德 | 140     | 110  | 67     |
| 5  | 曹孟德 | 110     | 90   | 45     |
| 6  | 孙权  | 100     | 73   | 78     |
| 7  | 宋公明 | 00      | 95   | 70     |
+----+--------+---------+------+--------+

6.4 Delete

6.4.1删除数据

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

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

-- 查看原数据
SELECT * FROM exam_result WHERE name = '孙悟空';
+----+--------+---------+------+--------+
| id | name   | chinese | math | english|
+----+--------+---------+------+--------+
| 2  | 孙悟空 | 174     | 80   | 77     |
+----+--------+---------+------+--------+
1 row in set (0.00 sec)

-- 删除数据
DELETE FROM exam_result WHERE name = '孙悟空';
Query OK, 1 row affected (0.17 sec)

-- 查看删除结果
SELECT * FROM exam_result WHERE name = '孙悟空';
Empty set (0.00 sec)

6.4.1.2 删除整张表（操作要慎用！）

-- 准备测试表
CREATE TABLE for_delete (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)

-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (1.05 sec)
Records: 3  Duplicates: 0  warnings: 0

-- 查看测试数据
SELECT * FROM for_delete;
+----+------+
| id | name |
+----+------+
| 1  | A    |
| 2  | B    |
| 3  | C    |
+----+------+
3 rows in set (0.00 sec)

-- 删除整表数据
DELETE FROM for_delete;
Query OK, 3 rows affected (0.00 sec)

-- 查看删除结果
SELECT * FROM for_delete;
Empty set (0.00 sec)

-- 再插入一条数据，自增id在重新增长
INSERT INTO for_delete (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)

-- 查看数据
SELECT * FROM for_delete;
+----+------+
| id | name |
+----+------+
| 4  | D    |
+----+------+
1 row in set (0.00 sec)

-- 查看表结构，会有 AUTO_INCREMENT=n 项
SHOW CREATE TABLE for_delete\G
*************************** 1. row ***************************
       Table: for_delete
Create Table: CREATE TABLE `for_delete` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

重点：删除数据，并不影响原有表结构

6.4.2 截断表

语法：

TRUNCATE [TABLE] table_name

注意：

这个操作慎用

只能对整表操作，不能像DELETE一样针对部分数据操作；
实际上对MySQL，不能做DELETE，所以比DELETE更快，但是TRUNCATE在删除数据的时候，并不经过真正的事务，所以无法回滚操作，所以比DELETE更快；
会重置AUTO_INCREMENT项。（不会记录在日志中）

-- 准备测试表
CREATE TABLE for_truncate (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);
Query OK, 0 rows affected (0.16 sec)

-- 插入测试数据
INSERT INTO for_truncate (name) VALUES ('A'), ('B'), ('C');
Query OK, 3 rows affected (1.05 sec)
Records: 3  Duplicates: 0  warnings: 0

-- 查看测试数据
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1  | A    |
| 2  | B    |
| 3  | C    |
+----+------+
3 rows in set (0.00 sec)

-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
TRUNCATE for_truncate;
Query OK, 0 rows affected (0.10 sec)

-- 查看删除结果
SELECT * FROM for_truncate;
Empty set (0.00 sec)

-- 再插入一条数据，自增id在重新增长
INSERT INTO for_truncate (name) VALUES ('D');
Query OK, 1 row affected (0.00 sec)

-- 查看数据
SELECT * FROM for_truncate;
+----+------+
| id | name |
+----+------+
| 1  | D    |
+----+------+
1 row in set (0.00 sec)

-- 查看表结构，会有 AUTO_INCREMENT=2 项
SHOW CREATE TABLE for_truncate\G
*************************** 1. row ***************************
       Table: for_truncate
Create Table: CREATE TABLE `for_truncate` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8