mysql脏数据怎么处理 mysql如何处理数据

数据处理之增删改总结目录

• 1. 数据处理

• 1.1 插入数据
• 1.2 更新数据
• 1.3 删除数据

• 2. MySQL8新特性：计算列

• 2.1 计算列概念
• 2.2 计算列使用
• 2.3 图书使用综合案例

说明：
【放在文末】。

1. 数据处理

1.1 插入数据

方式①：VALUES的方式添加

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

数据表中字段定义时的顺序相同。

举例：

INSERT INTO emp1
VALUES (1,'Jerry','2022-06-07',45000);

②指明添加的字段

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

③同时插入多条数据

INSERT INTO emp1(id,salary,`name`)
VALUES
(4,65000,'Jack'),
(5,66000,'汤姆');

     方式二：将查询结果添加到表中

INSERT INTO emp1(id,`name`,salary,hire_date)
SELECT employee_id, last_name, salary, hire_date
#查询的字段要与被添加表的字段一一对应，另外，被添加的表的数据类型要一致，数据的长度只能比查询的大
FROM employees
WHERE department_id IN (50,60);

同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中 效率更高最好选择使用单条INSERT语句的方式插入。

1.2 更新数据

使用格式

UPDATE...SET...WHERE...

举例：

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

     同时修改一条数据的多个字段

UPDATE emp1
SET hire_date = CURDATE(),salary = 60000
WHERE id = 4;

将姓名中包含a的提薪20%

UPDATE emp1
SET salary = salary * 1.2
WHERE name LIKE '%a%';

1.3 删除数据

使用格式：

DELETE FROM ... WHERE(可有可无，没有的话就会把表的数据全部删除)

将id为134的账户删除

DELETE FROM emp1
WHERE id  = 134;

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

2. MySQL8新特性：计算列

2.1 计算列概念

某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。在MySQL 8.0中，CREATE TABLE中都支持增加计算列。

2.2 计算列使用

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

建表;

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

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

C的值会根据a和b自动计算

2.3 图书使用综合案例

1、创建数据库test01_library

CREATE DATABASE test01_library;

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

/*
字段名 字段说明 数据类型
id 书编号 INT 
name 书名 VARCHAR(50) 
authors 作者 VARCHAR(100) 
price 价格 FLOAT 
pubdate 出版日期 YEAR 
note 说明 VARCHAR(100) 
num 库存 INT
*/
USE test01_library;
CREATE TABLE IF NOT EXISTS books(
id INT,
name VARCHAR(50),
authors VARCHAR(100), 
price FLOAT, 
pubdate YEAR, 
note VARCHAR(100), 
num INT
);

3、向books表中插入记录

1 Tal of AAA Dickes 23 1995 novel 11
 2 EmmaT Jane lura 35 1993 joke 22
 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

1）不指定字段名称，插入第一条记录

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

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

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

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

INSERT INTO books(id,authors,name, 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。

USE test01_library;

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

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

DELETE  FROM books
WHERE num = 0;

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

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

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

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

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

SELECT id, name, price, note
FROM books
WHERE note = 'novel'
ORDER BY price DESC;

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

SELECT *
FROM books
ORDER BY num DESC, note;

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

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

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

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

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

SELECT *
FROM books
LIMIT 5, 5;

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

use test01_library;

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

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

SELECT name
FROM books;

SELECT CHAR_LENGTH(REPLACE(name, ' ',''))
FROM books;


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 AS "书名", num AS "库存", 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,'合计库存总量'), SUM(num)
FROM books
GROUP BY note WITH ROLLUP;

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

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

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

SELECT *
FROM books
ORDER BY num DESC
LIMIT 0,3

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

SELECT *
FROM books;

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

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

SELECT *
FROM books
ORDER BY price DESC
LIMIT 0,1;

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

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

总结：

对于表的创建以及管理

表的创建
CREATE TABLE IF NOT EXISTS 表名(
字段名 数据类型
);

表的修改
①添加字段
ALTER TABLE 表名
ADD 字段名 数据类型;

②修改字段的数据长度，类型、默认值
ALTER TABLE 表名
MODIFY 字段名 数据类型 DEFAULT ‘’;

③重命名字段
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 数据类型 长度;

④删除字段
ALTER TABLE 表名
DROP COLUMN 字段名

⑤重命名表
RENAME TABLE 旧表名
TO　新表名

⑤删除表
DROP TABLE IF EXISTS 表名