【MySQL】表的修改操作，插入查询结果

目录

1.表的修改操作 

 1.1.将孙悟空同学的数学成绩修改为80分

1.2.将曹孟德同学的数学成绩修改为60分，语文成绩修改为70分

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

1.4.将所有同学的语文成绩修改为原来的2倍

2.插入查询结果

2.1.基本用法

2.2.删除表中的的重复记录，重复的数据只能有一份

1.表的修改操作 

修改数据的SQL如下：

UPDATE table_name SET column1=expr1 [, column2=expr2] ... [WHERE ...] [ORDER BY ...] [LIMIT ...];

说明：

1. 标准 SQL 的 UPDATE 语句只支持 SET 和 WHERE 子句。ORDER BY 和 LIMIT 子句在标准 SQL 的 UPDATE 语句中是不被支持的。
2. SQL中大写的表示关键字，[ ]中代表的是可选项。
3. SQL中的column=expr，表示将记录中列名为column的值修改为expr。
4. 在使用 UPDATE 语句时，请务必小心，因为一旦执行了更新操作，原始数据通常就无法恢复了（除非您有备份）。因此，在执行 UPDATE 之前，最好先使用 SELECT 语句来测试您的 WHERE 子句，确保它只选择了您想要更新的记录，update语句中的where、order by和limit就是用来定位数据的。

为了方便我们进行演示，下面创建一个成绩表，表当中包含：自增长的主键id、姓名、以及该同学的语文成绩、数学成绩和英语成绩。如下：

CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩'
);
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);

 1.1.将孙悟空同学的数学成绩修改为80分

在修改数据之前，我们先查看孙悟空同学当前的数学成绩。如下：

select name, math from exam_result where name='孙悟空';

在update语句中指明要将筛选出来的记录的数学成绩改为80分，并在修改后再次查看数据确保数据成功被修改。如下：

update exam_result set math=80 where name='孙悟空';

select name, math from exam_result where name='孙悟空';

1.2.将曹孟德同学的数学成绩修改为60分，语文成绩修改为70分

同理，在修改数据之前，先查看曹孟德同学当前的数学成绩和语文成绩。如下：

select * from exam_result where name='曹孟德';

在update语句中指明要将筛选出来的记录的数学成绩改为60分，语文成绩改为70分，并在修改后再次查看数据确保数据成功被修改。如下：

update exam_result set math=60,chinese=70 where name = '曹孟德';

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

在修改数据之前，我们先看总成绩排名。

select name,math,chinese+math+english as 总分 from exam_result order by chinese+math+english asc;

我们再查看总成绩倒数前三的3位同学的数学成绩

select name,math,chinese+math+english as 总分 from exam_result order by chinese+math+english asc limit 3;

在update语句中指明要将筛选出来的记录的数学成绩加上30分，如下：

需要注意的是，MySQL中不支持+=这种复合赋值运算符

update exam_result set math = math + 30 order by chinese+math+english asc limit 3;

我们看一下结果 

select name,math,chinese+math+english as 总分 from exam_result order by chinese+math+english asc limit 3;

嗯？孟玄德呢?这里在查看更新后的数据时不能查看总成绩倒数前三的3位同学，因为之前总成绩倒数前三的3位同学，数学成绩加上30分后可能就不再是倒数前三了。 

select name,math,chinese+math+english as 总分 from exam_result where name in ('宋公明','刘玄德','曹孟德');

怎么样？数学分数是不是都提高了 

1.4.将所有同学的语文成绩修改为原来的2倍

在修改数据之前，先查看所有同学的语文成绩。如下：

select name,chinese from exam_result;

在update语句中指明要将筛选出来的记录的语文成绩变为原来的2倍，因为要修改的的所有同学所以我们不需要使用where子句，如下：

update exam_result set chinese = chinese * 2 ;

我们看一下数据 

select name,chinese from exam_result;

2.插入查询结果

经过上面的学习，我们对于表的增删查改基本上都没有问题了，但是在这里我们还要学习一下如果将这些SQL组合起来使用。

例如有些时候我们要插入的数据是：某个查询的结果，这时我们就要将SQL组合在一起了。

在SQL中，INSERT INTO ... SELECT ... 语句用于从一个表中选择数据并将其插入到另一个表中。这种操作非常有用，特别是在需要将数据从一个表复制到另一个表，或者基于某些条件筛选数据并插入到新表时。

以下是该语句的详细解释和示例：

语法

 INSERT INTO table_name [(column1, column2, ...)]  
 SELECT column1, column2, ...  
 FROM source_table  
 [WHERE condition]; 

1. table_name：目标表的名称，即数据将要被插入的表。
2. (column1, column2, ...)：目标表中的列名（可选）。如果省略，则必须确保SELECT语句返回的列数与目标表的列数完全匹配，并且顺序一致。
3. SELECT column1, column2, ...：从源表中选择要插入的列。
4. source_table：源表的名称，即数据将要被选择的表。
5. [WHERE condition]：可选的条件子句，用于筛选源表中的记录。

示例

假设有两个表：employees 和 new_employees，并且我们希望将employees表中所有部门为'Sales'的员工插入到new_employees表中。

-- 假设 employees 表有以下列：id, name, department, salary  
-- 假设 new_employees 表结构与 employees 表相同  
   INSERT INTO new_employees (id, name, department, salary)  
 SELECT id, name, department, salary  
 FROM employees  
 WHERE department = 'Sales'; 

在这个例子中，new_employees表将包含employees表中所有部门为'Sales'的员工的记录。

注意事项

1. 列匹配：如果指定了目标表的列名，则SELECT语句中的列数和数据类型必须与这些列匹配。
2. 数据完整性：确保目标表中的数据完整性约束（如主键、外键、唯一约束等）不会被违反。
3. 性能：对于大型表，这种操作可能会很慢，并且可能会锁定表，影响其他操作。因此，在生产环境中执行此类操作时，请考虑在低峰时段进行，并可能需要进行适当的优化或分批处理。

2.1.基本用法

• 示例 1：复制特定条件下的数据

假设有两个表：students（学生表）和graduated_students（已毕业学生表）。我们想要将students表中所有已经毕业（假设毕业状态存储在graduation_status列中，值为'Graduated'）的学生复制到graduated_students表中。

 INSERT INTO graduated_students (student_id, name, graduation_date)  
 SELECT student_id, name, graduation_date  
 FROM students  
 WHERE graduation_status = 'Graduated'; 

• 示例 2：跨数据库复制数据

假设有两个数据库：db1和db2，它们都有相同的表结构orders（订单表）。我们想要将db1中的orders表的所有数据复制到db2中的orders表中。

 INSERT INTO db2.orders (order_id, customer_id, order_date, amount)  
 SELECT order_id, customer_id, order_date, amount  
 FROM db1.orders; 

注意：这要求两个数据库之间具有适当的访问权限，并且表结构必须匹配。

• 示例 3：部分列复制并插入到新表

假设我们有一个products表（产品表），包含产品的详细信息。我们想要创建一个新表products_summary（产品摘要表），只包含产品的ID、名称和价格，并从products表中复制这些数据。

 CREATE TABLE products_summary (  
 product_id INT,  
 product_name VARCHAR(255),  
 price DECIMAL(10, 2)  
 );  
   INSERT INTO products_summary (product_id, product_name, price)  
 SELECT product_id, product_name, price  
 FROM products; 

在这个例子中，我们首先创建了新表products_summary，然后使用INSERT INTO ... SELECT ...语句从products表中复制了所需的数据。

• 示例 4：基于计算的结果插入数据

假设我们有一个sales表（销售表），包含销售记录。我们想要创建一个新表monthly_sales_summary（月度销售摘要表），包含每个月的总销售额。

 CREATE TABLE monthly_sales_summary (  
 sales_month DATE,  
 total_sales DECIMAL(15, 2)  
 );  
   INSERT INTO monthly_sales_summary (sales_month, total_sales)  
 SELECT DATE_FORMAT(sale_date, '%Y-%m-01') AS sales_month, SUM(sale_amount) AS total_sales  
 FROM sales  
 GROUP BY DATE_FORMAT(sale_date, '%Y-%m'); 

在这个例子中，我们使用了聚合函数SUM()和日期格式化函数DATE_FORMAT()来计算每个月的总销售额，并将结果插入到新表monthly_sales_summary中。

这些示例展示了INSERT INTO ... SELECT ...语句在数据复制、跨数据库操作、部分列复制以及基于计算的结果插入数据等方面的广泛应用。

2.2.删除表中的的重复记录，重复的数据只能有一份

为了更好的学习这个SQL语法，我们来看一下面的这个案例：

现在有一张表，表中的数据是有重复的，要求如下：

• 删除表中的的重复复记录，重复的数据只能有一份

我们先来伪造一下题目的数据：

CREATE TABLE duplicate_table (id int, name varchar(20));

INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');

select * from duplicate_table;

那我们怎么完成题目的要求呢？

思路：

1. 创建一张空表 no_duplicate_table，其结构和 duplicate_table 一样的。
2. 对 duplicate_table表进行查询去重。
3. 将去重的结果插入到新表no_duplicate_table
4. 将表duplicate_table进行重命名为old_duplicate_table
5. 将表no_duplicate_table进行重命名为 duplicate_table。

这里我们就会发现：

• 第二步和第三步的SQL必须要合在一起才能完成我们想要的插入
• 操作4和操作5的重命名是为了保证底层数据表的改变不影响上层应用的使用

 1.创建一张空表 no_duplicate_table，其结构和 duplicate_table 一样的。

由于创建的空表 no_duplicate_table的表的结构与duplicate_table 相同，因此在创建临时表的时候可以借助like进行创建。如下：

create table no_duplicate_table like duplicate_table;
desc no_duplicate_table;

2.对 duplicate_table表进行查询去重。

select distinct * from duplicate_table;

3.将去重的结果插入到新表no_duplicate_table

 由于表 no_duplicate_table和表duplicate_table的结构相同，所以可以通过插入查询语句将去重查询后的结果插入到临时表中。并且我们可以对select进行全列查询，因此在插入时不用在表名后指明column列表。如下：

insert into no_duplicate_table select distinct * from duplicate_table;

select * from no_duplicate_table;

4. 将表duplicate_table重命名为old_duplicate_table 

要修改表名，我们使用 RENAME TABLE语句如下：

RENAME TABLE old_table_name TO new_table_name;

旧表( old_table_name)必须存在，而新表( new_table_name)一定不存在，如果新表 new_table_name 确实存在，该语句将失败。

rename table duplicate_table to old_duplicate_table;
select * from old_duplicate_table;

5.将表no_duplicate_table进行重命名为 duplicate_table。

rename table no_duplicate_table to duplicate_table;
select * from duplicate_table;