⑩数据处理中的增删改

1.插入数据（INSERT）

当我们在表中添加数据时，我们需要使用INSERT关键字。

1.1用VALUES的方式添加数据

方式一：使用这种方法时，一次只能向表中插入一条数据。

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

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

例如：

departments表中需要插入对应的字段如下图所示：

其代码表示为：

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

方式二：为表指定字段插入数据

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

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

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

其代码表示为：
 

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

方式三：同时插入多条记录

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

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

或者

INSERT INTO table_name(column1 [,column,...,columnn])
VALUES
(value1 [,value2,...,valuen]),
(value1 [,value2,...,valuen]),
......
(value1 [,value2, ... ,valuen]);

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

• Records：表名插入的条数
• Duplicates：表名插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
• Warnings：表明有问题的数据值，例如发生数据类型转换。

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

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

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

基本语法格式如下：

INSERT INTO 目标表明
(tar_column1 [,tar_column2,...,tar_columnn])
SELECT
(src_column1 [,src_column2,...,src_column])
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%';

2.更新数据（UPDATE）

将表中旧的值进行修改。

UPDATE 修改表名
SET 需要修改的字段
[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;

3.删除数据（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;

4.MySQL8新特性：计算列

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

在MySQL8.0中，CREATE TABLE 和ALTER TABLE 中都支持增加计算列。

例：定义数据表tb，然后定义字段a、字段b和字段c，其中c为计算列，用于计算a+b的值。

其代码为：

CREATE TABLE tb(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

5.综合案例

其中   2、创建表 books，表结构如下：

 

-- 1、创建数据库test01_library

CREATE DATABASE IF NOT EXISTS test01_library;

USE test01_library;
-- 2、创建表 books

CREATE TABLE IF NOT EXISTS books(
	id INT,
	`name` VARCHAR(50),
	`authors` VARCHAR(100),
	price FLOAT,
	pubdate YEAR,
	note VARCHAR(100),
	num INT
);

DESC books;

SELECT * FROM books;

-- 3、向books表中插入记录
-- 1）不指定字段名称，插入第一条记录

INSERT INTO books
VALUES (1,'Tal of AAA','Dickes',23,'1995','novel',11);

-- 2）指定所有字段名称，插入第二记录

INSERT INTO books(id,`name`,`authors`,price,pubdate,note,num)
VALUES (2,'EmmaT','Jane lura',35,'1993','joke',22);

-- 3）同时插入多条记录（剩下的所有记录）

INSERT INTO books(id,`name`,`authors`,price,pubdate,note,num)
VALUES 
(3,'Story of Jane','Jane Tim',40,'2001','novel',0),
(4,'Lovey Day','George Byron',20,'2005','novel',30),
(5,'Old land','Honore Blade',30,'2010','law',0),
(6,'The Battle','Upton Sara',30,'1999','medicine',40),
(7,'Rose Hood','Richard haggard',28,'2008','cartoon',28);

-- 4、将小说类型(novel)的书的价格都增加5。

UPDATE books
SET price = price + 5
WHERE note = 'novel';

-- 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。

UPDATE books
SET price = 40,note = 'drama'
WHERE `name` = 'EmmaT';

-- 6、删除库存为0的记录。

DELETE FROM books
WHERE num = 0;

-- 7、统计书名中包含a字母的书

SELECT `name`
FROM books
WHERE `name` LIKE '%a%';

-- 8、统计书名中包含a字母的书的数量和库存总量

SELECT COUNT(*),SUM(num)
FROM books
WHERE `name` LIKE '%a%';

# 9、找出“novel”类型的书，按照价格降序排列

SELECT *
FROM books
WHERE note = 'novel'
ORDER BY price DESC;

-- 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列

SELECT *
FROM books
ORDER BY num DESC,note ASC;

-- 11、按照note分类统计书的数量

SELECT note,COUNT(num)
FROM books
GROUP BY note;

-- 12、按照note分类统计书的库存量，显示库存量超过30本的

SELECT note,SUM(num) total
FROM books
GROUP BY note
HAVING total > 30;

-- 13、查询所有图书，每页显示5本，显示第二页

SELECT *
FROM books
LIMIT 5,5;

-- 14、按照note分类统计书的库存量，显示库存量最多的

SELECT note,SUM(num) total
FROM books
GROUP BY note
ORDER BY total DESC
LIMIT 0,1;

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

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

-- 16、查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示
-- 卡通，joke显示笑话

SELECT `name` "书名",note,CASE note WHEN 'novel' THEN '小说'
				    WHEN 'law' THEN '法律'
				    WHEN 'medicine' THEN '医药'
				    WHEN 'cartoon' THEN '卡通'
				    WHEN 'joke' THEN '笑话'
				    ELSE '其他'
				    END "类型"
FROM books;

-- 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货

SELECT `name`,num,CASE WHEN num > 30 THEN '滞销'
			   WHEN num > 0 AND num < 10 THEN '畅销'
			   WHEN num = 0 THEN '无货'
			   ELSE '售卖中'
			   END "销售情况"
FROM books;

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

SELECT IFNULL(note,'合计库存总量') AS node,SUM(num)
FROM books
GROUP BY note WITH ROLLUP;

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

SELECT IFNULL(note,'合计') AS note,COUNT(*)
FROM books
GROUP BY note WITH ROLLUP;

-- 20、统计库存量前三名的图书

SELECT `name`,num
FROM books
ORDER BY num DESC
LIMIT 0,3;

-- 21、找出最早出版的一本书

SELECT *
FROM books
ORDER BY pubdate ASC
LIMIT 0,1;

-- 22、找出novel中价格最高的一本书

SELECT price,note
FROM books
WHERE note = 'novel'
ORDER BY price DESC
LIMIT 1;

-- 23、找出书名中字数最多的一本书，不含空格

SELECT `name`
FROM books
ORDER BY CHAR_LENGTH(REPLACE(`name`,' ','')) DESC
LIMIT 1;