MySQL基础篇数据处理之增删改总结

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

说明：
此篇文章主要实现对表数据的增删改查，同时把上一篇文章的主要实现总结也在这里展示出来【放在文末】。

1. 数据处理

1.1 插入数据

方式①：VALUES的方式添加

INSERTINTO 表名 
VALUES(value1,value2,....);

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

举例：

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

②指明添加的字段

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

③同时插入多条数据

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

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

INSERTINTO 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语句在处理过程中 效率更高 。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。

1.2 更新数据

使用格式

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

举例：

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

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

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

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

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

1.3 删除数据

使用格式：

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

将id为134的账户删除

DELETEFROM emp1
WHERE id  =134;

 小结：DML默认情况下，执行完以后都会自动提交数据。如果希望执行完之后不自动提交数据，则需要使用SET autocommit = FALSE

2. MySQL8新特性：计算列

2.1 计算列概念

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

2.2 计算列使用

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

建表;

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

INSERTINTO test1(a,b)VALUES(10,20);

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

2.3 图书使用综合案例

1、创建数据库test01_library

CREATEDATABASE test01_library;

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

/*
字段名 字段说明 数据类型
id 书编号 INT 
name 书名 VARCHAR(50) 
authors 作者 VARCHAR(100) 
price 价格 FLOAT 
pubdate 出版日期 YEAR 
note 说明 VARCHAR(100) 
num 库存 INT
*/USE test01_library;CREATETABLEIFNOTEXISTS 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）不指定字段名称，插入第一条记录

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

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

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

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

INSERTINTO 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 +5WHERE note  ='novel';

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

USE test01_library;

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

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

DELETEFROM books
WHERE num =0;

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

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

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

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

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

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

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

SELECT*FROM books
ORDERBY num DESC, note;

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

SELECT note,COUNT(*)FROM books
GROUPBY note;

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

SELECT note,SUM(num)FROM books
GROUPBY note
HAVINGSUM(num)>30;

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

SELECT*FROM books
LIMIT5,5;

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

use test01_library;SELECT note,SUM(num) sum_num
FROM books
GROUPBY note
ORDERBY sum_num DESCLIMIT0,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"库存",CASEWHEN num >30THEN'滞销'WHEN num >0AND num <10THEN'畅销'WHEN num =0THEN'无货'ELSE'正常'END"显示状态"FROM books;

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

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

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

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

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

SELECT*FROM books
ORDERBY num DESCLIMIT0,3

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

SELECT*FROM books;SELECT*FROM books
ORDERBY pubdate ASCLIMIT0,1;

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

SELECT*FROM books
ORDERBY price DESCLIMIT0,1;

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

SELECT*FROM books
ORDERBY CHAR_LENGTH(REPLACE(name,' ',''))DESCLIMIT0,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 表名