数据库——DML：增删改

目录

数据库——DML：增删改

1. 插入数据（增） 

1.2 方式1：VALUES的方式添加 

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

2. 更新数据 （改）

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

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

 3. 删除数据

4. MySQL8新特性：计算列

5. 综合案例

数据库——DML：增删改

1. 插入数据（增） 

1.2 方式1：VALUES的方式添加 

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

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

1- INSERT INTO 表名

VALUES (value1,value2,....);

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

#DML
 
#储备工作
USE dbtest;
 
CREATE TABLE IF NOT EXISTS emp1(
id INT,
emp_name VARCHAR(15),
hire_date DATE,
salary DOUBLE(10,2)
);
 
DESC emp1;
 
SELECT * FROM emp1;
#添加数据
#方式一：一条一条地添加数据
 
#1-没有指明添加的字段
INSERT INTO emp1
VALUES(1,'TOM','2022-10-29',5000);#注意：一定要按声明的字段的先后顺序添加

2- INSERT INTO 表名 (column1 [, column2, …, columnn])

VALUES (value1 [,value2, …, valuen]);

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

 
#2-指明要添加的字段（推荐）
INSERT INTO emp1(id,hire_date,salary,emp_name)
VALUES(2,'2022-10-30',4500,'Marry');
 
INSERT INTO emp1(id,salary,emp_name)
VALUES(2,4500,'Bell');

#3-
INSERT INTO emp1(id,hire_date,salary,emp_name)
VALUES
(3,'2022-10-30',4200,'Sherry'),
(4,'2022-10-30',4700,'John');

使用 INSERT 同时插入多条记录时， MySQL 会返回一些在执行单行插入时没有的额外信息，这些信息的含 义如下： ●　Records ：表明插入的记录条数。 ●　 Duplicates ：表明插入时被忽略的记录，原因可能是这 些记录包含了重复的主键值。 ●　Warnings ：表明有问题的数据值，例如发生数据类型转换。

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

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

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%' ;

2. 更新数据 （改）

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

UPDATE 表名

set 。。。。。

[WHERE condition]

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

#方式二：将查询结果插入到表中
 
#2.更新数据
#UPDATE ....SET,,,,WHERE,,,,,
 
UPDATE emp1
SET hire_date=CURDATE()
WHERE emp_name='Marry';

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

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

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

UPDATE copy_emp
SET department_id = 110;

 更新中的数据完整性错误

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

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

#需求：将姓名中包含字符r的提薪20%

 
#需求：将姓名中包含字符r的提薪20%
UPDATE emp1
SET salary=salary*1.2
WHERE emp_name LIKE'%r%';
 
SELECT * FROM emp1;

#修改数据时，可能存在修改不成功的情况（可能是由于约束的影响造成的）
 

 3. 删除数据

语法：DELETE FROM table_name [ WHERE < condition >];

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

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

DELETE FROM departments
WHERE department_name = 'Finance';

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

DELETE FROM copy_emp;

删除id=1的： 

#删除数据-  DELETE FROM ..... WHERE....
DELETE FROM emp1
WHERE id=1;

 #删除数据时，可能存在删除失败的情况（可能是由于约束的影响造成的）

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

4. 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
);

 
#4. MySQL8新特性：计算列
USE dbtest;
 
CREATE TABLE test1(
a INT,
b INT,
c INT GENERATED ALWAYS AS(a+b) VIRTUAL#c字段即为计算列
);
 
INSERT INTO test1(a,b)
VALUES(10,20);
 
SELECT*
FROM test1;

 
UPDATE test1
SET a=100;

SELECT*
FROM test1;

5. 综合案例

# 1 、创建数据库 test01_library

# 1、创建数据库test01_library
CREATE DATABASE IF NOT EXISTS test01_library;

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

# 2、创建表 books，表结构如下：
CREATE TABLE IF NOT EXISTS books(
id INT,
name VARCHAR(50),
author VARCHAR(100),
price FLOAT,
pubdate YEAR,
note VARCHAR(100),
num INT
);
 
DESC books;

# 3 、向 books 表中插入记录

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

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

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

 
# 3、向books表中插入记录
 
INSERT INTO books
VALUES(1,'Tal of AAA','Dickes',23,'1995','novel',11);
 
INSERT INTO books(id,name,authors ,price,pubdate,note ,num)
VALUES(2,'EmmaT','Jane lura',35,'1993','joke',22);
 
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);
 
 
SELECT* FROM books;
 

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

 
# 4、将小说类型(novel)的书的价格都增加5。
 
UPDATE books
SET price=price+5
WHERE note ='novel';

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

# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。
UPDATE books
SET price=40,note='drama'
WHERE name='EmmaT';
 
SELECT* FROM books;
 
 

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

# 6、删除库存为0的记录。
DELETE FROM books
WHERE num=0;

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

# 7、统计书名中包含a字母的书
SELECT name
FROM books
WHERE name LIKE '%a%';
 

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

# 8、统计书名中包含a字母的书的数量和库存总量
SELECT COUNT(*),SUM(num)
FROM books
WHERE name LIKE '%a%';

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

# 9、找出“novel”类型的书，按照价格降序排列
SELECT name,note
FROM books
WHERE note='novel'
ORDER BY price DESC; 

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

# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列  
SELECT*
FROM books
ORDER BY num DESC,note ASC;
 
 

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

# 11、按照note分类统计书的数量
SELECT note,COUNT(*)
FROM books
GROUP BY note;
 
 

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

# 12、按照note分类统计书的库存量，显示库存量超过30本的
SELECT note,SUM(num)
FROM books
GROUP BY note
HAVING SUM(num)>30;

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

# 13、查询所有图书，每页显示5本，显示第二页
SELECT *
FROM books
LIMIT 5,5;

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

# 14、按照note分类统计书的库存量，显示库存量最多的
SELECT note,SUM(num) sum_num
FROM books
GROUP BY note
ORDER BY sum_num DESC
LIMIT 0,1;
 

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

#先去掉空格
SELECT REPLACE(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 AS "类型"
FROM
	books
 
 
 

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

# 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 AS "显示状态"
FROM
	books;
 
 
 
 
 
 

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

 # 18、统计每一种note的库存量，并合计总量
SELECT IFNULL(note,'合计库存总量') AS note,SUM(num)
FROM books
GROUP BY note WITH ROLLUP;

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

# 19、统计每一种note的数量，并合计总量
SELECT IFNULL(note,'合计总量') AS note,COUNT(*)
FROM books
GROUP BY note WITH ROLLUP;

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

# 20、统计库存量前三名的图书
SELECT *
FROM books
ORDER BY num DESC
LIMIT 0,3;

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

# 21、找出最早出版的一本书
SELECT *
FROM books
ORDER BY pubdate  ASC
LIMIT 0,1;

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

# 22、找出novel中价格最高的一本书
SELECT *
FROM books
WHERE note='novel'
ORDER BY price DESC
LIMIT 0,1;

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

# 23、找出书名中字数最多的一本书，不含空格
SELECT *
FROM books
ORDER BY CHAR_LENGTH(REPLACE(name,' ','')) DESC
LIMIT 0,1;