第11章数据处理之增删改

第11章数据处理之增删改

插入数据

使用 INSERT 语句向表中插入数据。

方式1：VALUES的方式添加

使用这种语法一次只能向表中插入一条数据。

情况1：为表的所有字段按默认顺序插入数据

INSERT INTO 表名
VALUES (value1,value2,....);

值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

示例：

INSERT INTO departments 
VALUES (70, 'Pub', 100, 1700);

如果字段值为空，则不填写或直接写NULL即可。

INSERT INTO departments
VALUES (100, 'Finance', NULL, NULL);

情况2：为表的指定字段插入数据

INSERT INTO 表名(column1 [, column2, …, columnn]) 
VALUES (value1 [,value2, …, valuen]);

为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。

在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,…valuen需要与column1,…columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。

举例：

INSERT INTO departments(department_id, department_name) 
VALUES (80, 'IT');

情况3：同时插入多条记录

INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开，基本语法格式如下：

INSERT INTO table_name 
VALUES 
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

或者

INSERT INTO table_name(column1 [, column2, …, columnn]) 
VALUES 
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

举例：

INSERT INTO emp(emp_id,emp_name)
VALUES 
(1001,'shkstart'),
(1002,'atguigu'),
(1003,'Tom');

运行代码返回的结果：

Query OK, 3 rows affected (0.00 sec) 
Records: 3  Duplicates: 0  Warnings: 0

使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：

• Records：表明插入的记录条数。

• Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。

• Warnings：表明有问题的数据值，例如发生数据类型转换。

多行插入与单行插入的小区别：

一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中 效率更高 。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。

注意：

• VALUES 也可以写成VALUE ，但是VALUES是标准写法。

• 字符和日期型数据应包含在单引号中。

方式2：将查询结果插入到表中

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 子句中的列名对应。

举例：

INSERT INTO emp2 
SELECT * 
FROM employees
WHERE department_id = 90;

INSERT INTO sales_reps(id, name, salary, commission_pct) 
SELECT employee_id, last_name, salary, commission_pct 
FROM employees
WHERE job_id LIKE '%REP%';

插入数据两种方式的练习：

#数据处理之增删改
#储备工作
USE atguigudb;

CREATE TABLE IF NOT EXISTS emp1(
                                id INT,
                                `name` VARCHAR(15),
                                hire_data DATE,
                                salary DOUBLE(10,2)
                                );

DESC emp1;

SELECT *
FROM emp1;

#添加数据
#方式一：一条一条的添加数据
#① 没有指明添加的字段，则此时一定要按照声明的字段的先后顺序添加
#正确的
INSERT INTO emp1
VALUES (1,'Tom','2000-12-21',3400);

#错误的
INSERT INTO emp1
VALUES (2,3400,'2000-12-21','Jerry')

#② 指明要添加的字段(推荐)
INSERT INTO emp1(id,hire_date,salary,`name`)
VALUES (2,'1999-09-09',4000,'Jerry');

#说明：没有进行赋值的hire_date的值为null
INSERT INTO emp1(id,salary,'name')
VALUES (3,4500,'shk');

#③ 同时插入多条记录(推荐).效率比②高
INSERT INTO emp1(id,`name`,salary)
VALUES 
(4,'Jim',5000),
(5,'Mary',5500);
#注：同时添加多条数据时如果有的字段部分为空，则直接填写NULL即可


#方式二：将查询的结果插入到表中
INSERT INTO emp1(id,`name`,salary,hire_date)
#查询语句
SELECT employee_id,last_name,salary,hire_date 	#查询的字段一定要与添加到表的字段一一对应
FROM employees
WHERE department_id IN (60,70);

#针对方式二需要注意:emp1表中要添加的字段的长度不能低于 employees 表中查询的字段的长度，否则报错。
#因此使用方式二之前应先查看对应的表结构字段的类型是否符合标准。
#如果emp1表中要添加数据的字段的长度低于 employees表中查询的字段的长度的话，就有添加不成功的风险。

说明：针对方式二需要注意:emp1表中要添加的字段的长度不能低于 employees 表中查询的字段的长度，否则报错。因此使用方式二之前应先查看对应的表结构字段的类型是否符合标准。如果emp1表中要添加数据的字段的长度低于 employees表中查询的字段的长度的话，就有添加不成功的风险。

更新数据

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

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

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

使用 WHERE 子句指定需要更新的数据。

UPDATE employees
SET department_id = 70 
WHERE employee_id = 113;

如果省略 WHERE 子句，则表中的所有数据都将被更新。

UPDATE copy_emp
SET department_id = 110;

更新中的数据完整性错误:

UPDATE employees
SET department_id = 55 
WHERE department_id = 110;

原因说明：这是因为约束存在的原因，employees与departments表相关联，employees表中的department_id必须存在于departments表中才可以，而departments表中不存在于55号部门，因此更新失败。

更新数据举例：

#更新数据 (或修改数据)
# UPDATE ... SET ... WHERE ...		一般都会有过滤条件，如果没有那么代表这一列都将会被修改
#没有过滤条件就可以实现批量修改数据
UPDATE emp1
SET hire_date = CURDATE()
WHERE id = 5;

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

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

#修改数据时(包括添加数据)，是可能存在不成功的情况的。(可能是由于约束的影响造成的)
UPDATE employees
SET department_id = 10000		#约束修改的 department_id必须存在于 departments表中
WHERE employee_id = 102;

一般都会有过滤条件，如果没有那么代表这一列都将会被修改,没有过滤条件就可以实现批量修改数据.

修改数据时(包括添加数据)，是可能存在不成功的情况的。(可能是由于约束的影响造成的).

删除数据

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

DELETE FROM table_name 
[WHERE <condition>];

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

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

DELETE FROM departments
WHERE department_name = 'Finance';

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

DELETE FROM copy_emp;

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

DELETE FROM departments
WHERE department_id = 60;

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

意思就是不能删除在其他表中作为外键的数据。可以则有理解，如果删除了department_id = 60的数据，那么employees 表中department_id = 60的员工怎么办？因此删除失败，报错。

删除数据举例：

#删除操作 
# DELETE FROM ... WHERE ...  
# WHERE 实现过滤，作用同上面的更新操作
DELETE FROM emp1
WHERE id = 1;

#在删除数据时，也有可能因为约束的影响，导致删除失败
DELETE FROM departments
WHERE department_id = 50; #约束，如果删除了，那么员工表中部门id为50的员工怎么办，所以删除失败

在删除数据时，也有可能因为约束的影响，导致删除失败。

小结：

​ 增删改查等DML操作默认情况下，执行完以后都会自动提交数据。如果希望执行完以后不自动提交数据，则需要使用

​ SET autocommit = FALSE .

​ 建议执行增、删、改数据时先进行查询，看一下表结构和内容，这样可以减少出错。

MySQL8新特性：计算列

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

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

举例：定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算a+b的值。 首先创建测试表tb1，语句如下：

CREATE TABLE tb1(
                id INT,
                a INT,
                b INT,
                c INT GENERATED ALWAYS AS (a + b) VIRTUAL 	#创建计算列
                );

插入演示数据，语句如下：

INSERT INTO tb1(a,b) 
VALUES (100,200);

查询数据表tb1中的数据，结果如下：

mysql> SELECT * FROM tb1;
+------+------+------+------+ 
| id   | a    | b    | c    |
+------+------+------+------+ 
| NULL |  100 |  200 |  300 |
+------+------+------+------+
1 row in set (0.00 sec)

更新数据中的数据，语句如下：

UPDATE tb1 
SET a = 500;

再查询数据表tb1中的数据，结果如下：

mysql> SELECT * FROM tb1;
+------+------+------+------+ 
| id   | a    | b    | c    |
+------+------+------+------+ 
| NULL |  500 |  200 |  700 |
+------+------+------+------+
1 row in set (0.00 sec)

举例2：

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

INSERT INTO test1(a,b)
VALUES (10,20); # 此时c自动变为30

SELECT * FROM test1;

UPDATE test1
SET a = 100; # 此时c自动变为120

练习

查询书名达到10个字符的书，不包括里面的空格

SELECT REPLACE(`name`,' ','')	#这个操作就是将书名中的所有空格给去除掉，在显示出来
FROM books;

SELECT CHAR_LENGTH(REPLACE(`name`,' ','')) #统计占几个字符
FROM books;

SELECT `name`
FROM books
WHERE CHAR_LENGTH(REPLACE(`name`,' ','')) >= 10;

统计每一种note的库存量，并合计总量

SELECT IFNULL(note,'合计库存总量') AS note,SUM(num)
FROM books
GROUP BY note WITH ROLLUP;			#使用到了新特性WITH ROLLUP

统计每一种note的数量，并合计总量

SELECT IFNULL(note,'合计总量') AS note,COUNT(*)
FROM books
GROUP BY note WITH ROLLUP;			#使用到了新特性WITH ROLLUP

---

向my_employees表中插入下列数据

#除了前面两种方式，一条一条插入数据等方式，还可以用如下方式
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895 UNION ALL 
SELECT 2,'Dancs','Betty','Bdancs',860 UNION ALL 
SELECT 3,'Biri','Ben','Bbiri',1100 UNION ALL 
SELECT 4,'Newman','Chad','Cnewman',750 UNION ALL 
SELECT 5,'Ropeburn','Audrey','Aropebur',1550;
#这种方式也是可以的，进行了解，需要知道可以这样做

将userid为Bbiri的user表和my_employees表的记录全部删除

#方式一
DELETE FROM my_employees
WHERE userid = 'Bbiri';

DELETE FROM users
WHERE userid = 'Bbiri';

#方式二
DELETE u,e
FROM users u
JOIN my_employees e 
ON u.userid=e.userid
WHERE u.userid = 'Bbiri';

将名称为Claws的猫的主人改为kevin

UPDATE pet
SET `owner` = 'kevin'
WHERE `name` = 'Claws' AND species = 'Cat';		#两个过滤条件 名称为Claws，且物种为猫