17MySQL数据处理之增删改

😹 作者: gh-xiaohe
😻 gh-xiaohe的博客
😽 觉得博主文章写的不错的话，希望大家三连（✌关注，✌点赞，✌评论），多多支持一下！！

💒 数据处理之增删改

🚏 插入数据 INSERT INTO … VALUES …

🚀 方式一：VALUES的方式添加

    一条一条的添加数据

🚬 准备工作

# 选中 atguigudb 数据库
USE atguigudb;

# 创建表
CREATE TABLE IF NOT EXISTS emp1(
id INT,
`name` VARCHAR(15),
hire_date DATE,
salary DOUBLE(10,2)
);

# 查看表结构
DESC emp1;

# 查看表数据
SELECT *
FROM emp1;

🚬 ① 没有指明添加的字段

# 插入数据
INSERT INTO emp1
VALUES (1,'Tom','2000-12-21',3400);

SELECT *
FROM emp1;

注意：一定要按照声明的字段的先后顺序添加，如果不按照顺序添加会报错

🚬 ② 指明要添加的字段 （推荐）

     为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。
    在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,…valuen需要与column1,…columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。

INSERT INTO emp1(id,hire_date,salary,`name`)
VALUES(2,'1999-09-09',4000,'Jerry');

SELECT *
FROM emp1;

# 说明：没有进行赋值的hire_date 的值为 null，初始设置的默认值，没有这是默认null
INSERT INTO emp1(id,salary,`name`)
VALUES(3,4500,'shk');

SELECT *
FROM emp1;

🚬 ③ 同时插入多条记录 （推荐）

     INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开。

INSERT INTO emp1(id,NAME,salary)
VALUES
(4,'Jim',5000),
(5,'张俊杰',5500);

SELECT *
FROM emp1;

🚄 方式二: 将查询结果插入到表中

     INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需
    要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行

基本语法格式如下：

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

• 在 INSERT 语句中加入子查询。
• 不必书写 VALUES 子句。
• 子查询中的值列表应与 INSERT 子句中的列名对应。

🚬 举例

SELECT * FROM emp1;

INSERT INTO emp1(id,NAME,salary,hire_date)
#查询语句
SELECT employee_id,last_name,salary,hire_date  # 查询的字段一定要与添加到的表的字段一一对应
FROM employees
WHERE department_id IN (70,60);

SELECT * FROM emp1;

🚬 需要特别注意:

DESC emp1;
DESC employees;

说明:

    ①emp1表中要添加数据的字段的长度不能低于employees表中查询的字段的长度。

    ②如果emp1表中要添加数据的字段的长度低于employees表中查询的字段的长度的话，就有添加不成功的风险。

举个简答的例子：

  比如搬家，小房子往大房子中搬，可以都搬的下，但是大房子往小房子搬家，就可能会失败

  为什么是可能，而不是一定。

  因为：你住这大房子，但是房子没有住满，还空着很多的房间。此时大房子往小房子搬家也是可以成功的，此题就是如此，employee表中加入的字段都没有超过 emp1 所限制的字段

🚏 更新数据 UPDATE … SET … WHERE …

🚬 使用 UPDATE 语句更新数据。语法如下：

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

• 可以一次更新多条数据。
• 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
• 使用WHERE子句指定需要更新的数据。

🚬 案例

SELECT * FROM emp1;

UPDATE emp1
SET hire_date = CURDATE()
WHERE id = 5;

SELECT * FROM emp1;

SELECT * FROM emp1;

#同时修改一条数据的多个字段
UPDATE emp1
SET hire_date = CURDATE(),salary = 6000
WHERE id = 4;

SELECT * FROM emp1;

SELECT * FROM emp1;

#题目：将表中姓名中包含字符a的提薪20%
UPDATE emp1
SET salary = salary * 1.2
WHERE NAME LIKE '%a%';

SELECT * FROM emp1;

#修改数据时，是可能存在不成功的情况的。（可能是由于约束的影响造成的）
UPDATE employees
SET department_id = 10000
WHERE employee_id = 102;

🚏 删除数据 DELETE FROM … WHERE…

• 使用 DELETE 语句从表中删除数据
  

DELETE FROM table_name [WHERE <condition>];

table_name指定要执行删除操作的表；“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。

• 使用 WHERE 子句删除指定的记录。

DELETE FROM emp1
WHERE id = 1;

• 如果省略 WHERE 子句，则表中的全部数据将被删除

DELETE FROM  copy_emp;

• 删除中的数据完整性错误

#在删除数据时，也有可能因为约束的影响，导致删除失败
DELETE FROM departments
WHERE department_id = 50;

报错：You cannot delete a row that contains a primary key that is used as a foreign key in another table.

🚏 小结：

    DML操作默认情况下，执行完以后都会自动提交数据。

    如果希望执行完以后不自动提交数据，则需要使用 SET autocommit = FALSE

🚏MySQL8新特性：计算列

🚬 什么叫计算列呢？

简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE为例进行讲解。

🚬 解释：

    比如说采取创建这个表的时候，有可能这个表它的字段是非常多的。然后从横向上来看的话，这个表的数据可能也很多，比如说十万，甚至说几百万，这个数据都是有可能的。下面我们来了一个这个 select 语句，select 语句 想去查询这个表中的数据。那恰好我们，这个数据它其实就是借用了我们这个表中的一些字段，做了一些运算，最后得出来的这个结果（数据）。

    如果没有这个计算列的话，我们查这个表中的数据有可能会涉及到了几千条甚至上万条这个记录。那就意味着我们每一条记录都得上来是不是一点点的，这样去计算一下，然后得出来一个值，然后再看下一条，依次这样去得出这个结果。

    涉及到了上万条数据，首先这个数据量是一方面。另外的话你每一条数据都需要这样的临时去做个运算，才能得到你想要的结果，包括select 查询语句 写起来也复杂。

    如果我们有了计算列，咱们在创建这个表的时候就可以指明说这个列就等于前面几个字段一种关系，那我们就设计好了对吧？那设计好以后的话，这时候你再去做这个查询，直接就定位这样一个列就可以了。那么当你这个表中的前面这个字段做修改的话，这个字段的值也会自动的去做一些修改。那这样的话是不是就方便很多？相较于原来的我们的 SQL 写法来说，有了计算列了以后，那我们这个在表的设计当中，我们要是应用了这个计算列，那它的这个查询的效率提升了，比以前方便多了。

🚬 举例：

USE atguigudb;

CREATE TABLE test1(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL  #字段c即为计算列
);

DESC test1;

INSERT INTO test1(a,b)
VALUES(10,20);

SELECT * FROM test1;

UPDATE test1
SET a = 100;

SELECT * FROM test1;