MySQL---增删改

我只想卷死各位，或者被各位卷死，在此特别感谢康师傅的MySQL教程

85:43:12

MySQL数据库入门到大牛，mysql安装到优化，百科全书级，全网天花板

604.6万 19.3万

视频 尚硅谷

---

插入数据

方式1：VALUES的方式添加

• 为表的所有字段按默认顺序插入数据

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

• 同时插入多条记录

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]);

• 举例

-- 准备工作
USE atguigudb;

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

DESC emp1;

-- 1. 添加数据

-- ① 没有指明添加的字段
INSERT INTO emp1
VALUES(1,'Tom','2022-01-15',5000); -- 注意：一定要按照声明的字段的先后顺序添加

-- ② 指明要添加的字段 （推荐）
INSERT INTO emp1(id,`name`,hire_date,salary)
VALUES(2,'Jim','2022-07-15',8000);

-- 没有进行赋值的hire_date 的值为 null
INSERT INTO emp1(id,`name`,salary)
VALUES(3,'Jarry',10000);

-- ③ 同时插入多条记录 （推荐）
INSERT INTO emp1(id,`name`,salary)
VALUES
(4,'张三',6000),
(5,'李四',7000)

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

• 基本语法如下

INSERT INTO 目标表名
(tar_column1 [, tar_column2, ..., tar_columnn])
SELECT
(src_column1 [, src_column2, ..., src_columnn])
FROM 源表名
[WHERE condition]

• 在 INSERT 语句中加入子查询。

• 不必书写 VALUES 子句。

• 子查询中的值列表应与 INSERT 子句中的列名对应。

• 举例

INSERT INTO emp1(id,`name`,hire_date,salary)
SELECT employee_id,last_name,hire_date,salary   -- 查询的字段一定要与添加到的表的字段一一对应
FROM employees
WHERE department_id IN (60,70);

说明：emp1表中要添加数据的字段的长度不能低于employees表中查询的字段的长度。
如果emp1表中要添加数据的字段的长度低于employees表中查询的字段的长度的话，就有添加不成功的风险。
举例：如果emp1中的name类型为VARCHAR(15)，而employees中last_name类型为VARCHAR(25)，当employees表中有一个长度超过15但没超过25的last_name添加到emp1中，就不会添加成功

更新数据

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

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

• 可以一次更新多条数据。

• 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

• 举例

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

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

-- 题目：将表中姓名带a的提薪20%
UPDATE emp1
SET salary = salary * 1.2
WHERE `name` LIKE "%a%";

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

删除数据

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

DELETE FROM table_name [WHERE <condition>];

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

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

DELETE FROM departments
WHERE department_id = 50;   -- 

• 在删除数据时，也有可能因为约束的影响，导致删除失败
• 小结：DML操作默认情况下，执行完以后都会自动提交数据。
• 如果希望执行完以后不自动提交数据，则需要使用 SET autocommit = FALSE.

计算列

CREATE TABLE test01(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL   -- 字段c即为计算列，此时不允许插入数据时为c赋值
);

INSERT INTO test01(a,b) --这里不能带上c哦，不然会报错The value specified for generated column 'c' in table 'test01' is not allowed.
VALUES
(10,20),
(200,520);

SELECT * FROM test01;   -- 查看表中内容时，c的结果已经自动计算完毕了

综合案例

1、创建数据库test01_library

CREATE DATABASE test01_library;

USE test01_library;

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

字段名	字段说明	数据类型
id	书编号	INT
name	书名	VARCHAR(50)
authors	作者	VARCHAR(100)
price	价格	FLOAT
pubdate	出版日期	YEAR
note	说明	VARCHAR(100)
num	库存	INT

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）不指定字段名称，插入第一条记录

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

id	NAME	AUTHORS	price	pubdate	note	num
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

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 * 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 COUNT(*),note
FROM books
GROUP BY note;

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

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

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

SELECT * 
FROM books
LIMIT 3,3;

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

• 方式一
• 方式一

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

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

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

SELECT * 
FROM books
WHERE CHAR_LENGTH(REPLACE(NAME,' ','')) >= 8;

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

SELECT `name` AS "书名",CASE note
WHEN 'novel' THEN '小说'
WHEN 'law' THEN '法律'
WHEN 'medicine' THEN '医药'
WHEN 'cartoon' THEN '卡通'
WHEN 'joke' THEN '笑话' END AS "类型"
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 AS "销售情况"
FROM books;

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

SELECT IFNULL(note,'库存总量') AS note,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 * 
FROM books
ORDER BY num DESC
LIMIT 3;

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

SELECT * 
FROM books
ORDER BY pubdate ASC
LIMIT 1;

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

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

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

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

课后练习

练习一

1. 创建数据库dbtest11

CREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8';

2. 运行以下脚本创建表my_employees

USE dbtest11;
CREATE TABLE my_employees(
id INT(10),
first_name VARCHAR(10),
last_name VARCHAR(10),
userid VARCHAR(10),
salary DOUBLE(10,2)
);

CREATE TABLE users(
id INT,
userid VARCHAR(10),
department_id INT
);

3. 显示表my_employees的结构

DESC my_employees;

4. 向my_employees表中插入下列数据

ID	FIRST_NAME	LAST_NAME	USERID	SALARY
1	patel	Ralph	Rpatel	895
2	Dancs	Betty	Bdancs	860
3	Biri	Ben	Bbiri	1100
4	Newman	Chad	Cnewman	750
5	Ropeburn	Audrey	Aropebur	1550

INSERT INTO my_employees(id,first_name,last_name,userid,salary)
VALUES
(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);

5. 向users表中插入数据

id	userid	department_id
1	Rpatel	10
2	Bdancs	10
3	Bbiri	20
4	Cnewman	30
5	Aropebur	40

INSERT INTO users(id,userid,department_id)
VALUES
(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40);

6. 将3号员工的last_name修改为“drelxer”

UPDATE my_employees
SET last_name = 'drelxer'
WHERE id = 3;

7. 将所有工资少于900的员工的工资修改为1000

UPDATE my_employees
SET salary = 1000
WHERE salary < 900;

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

• 方式一
• 方式二

DELETE FROM users
WHERE userid = 'Bbiri';

DELETE FROM my_employees
WHERE userid = 'Bbiri';

DELETE m,u 
FROM users u
JOIN my_employees m
ON u.`userid` = m.userid
WHERE u.`userid` = 'Bbiri';

9. 删除my_employees、users表所有数据

DELETE FROM my_employees;

DELETE FROM users;

10. 检查所作的修正

SELECT * FROM users;

SELECT * FROM my_employees;

11. 清空表my_employees

TRUNCATE TABLE my_employees;

练习二

1. 使用现有数据库dbtest11

USE dbtest11;

2. 创建表格pet

字段名	字段说明	数据类型
name	宠物名称	VARCHAR(20)
owner	宠物主人	VARCHAR(20)
species	种类	VARCHAR(20)
sex	性别	CHAR(1)
birth	出生日期	YEAR
death	死亡日期	YEAR

CREATE TABLE IF NOT EXISTS pet(
`name` VARCHAR(20),
`owner` VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth YEAR,
death YEAR
);

3. 添加记录

NAME	OWNER	species	sex	birth	death
Fluffy	harold	Cat	f	2003	2010
Claws	gwen	Cat	m	2004
Buffy		Dog	f	2009
Fang	benny	Dog	m	2000
bowser	diane	Dog	m	2003	2009
Chirpy		Bird	f	2008

INSERT INTO pet
VALUES('Fluffy','harold','cat','f',2003,2010);

INSERT INTO pet 
VALUES('bowser','diane','dog','m',2003,2009);

INSERT INTO pet(`NAME`,`OWNER`,species,sex,birth)
VALUES
('Claws','gwen','cat','m',2004),
('Fang','benny','Dog','m',2000);

INSERT INTO pet(`NAME`,species,sex,birth)
VALUES
('Buffy','Dog','m',2009),
('Chirpy','Bird','f',2008);

4. 添加字段:主人的生日owner_birth DATE类型。

UPDATE pet
SET `OWNER` = 'kevin'
WHERE NAME = 'Claws';

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

UPDATE pet
SET `OWNER` = 'kevin'
WHERE NAME = 'Claws'
AND species = 'Cat';

6. 将没有死的狗的主人改为duck

UPDATE pet
SET `OWNER` = 'duck'
WHERE death IS NULL 
AND species = 'Dog';

7. 查询没有主人的宠物的名字

SELECT `NAME`
FROM pet
WHERE `OWNER` IS NULL;

8. 查询已经死了的cat的姓名，主人，以及去世时间

SELECT `NAME`,`OWNER`,death
FROM pet
WHERE death IS NOT NULL
AND species = 'Cat';

9. 删除已经死亡的狗

DELETE FROM pet
WHERE death IS NOT NULL
AND species = 'Dog';

10. 查询所有宠物信息

SELECT * FROM pet;

练习三

1. 使用已有的数据库dbtest11

USE dbtest11;

2. 创建表employee，并添加记录

id	name	sex	tel	addr	salary
10001	张一一	男	13456789000	山东青岛	1001.58
10002	刘小红	女	13454319000	河北保定	1201.21
10003	李四	男	0751-1234567	广东佛山	1004.11
10004	刘小强	男	0755-5555555	广东深圳	1501.23
10005	王艳	女	020-1232133	广东广州	1405.16

CREATE TABLE employee(
id INT,
`name` VARCHAR(20),
sex VARCHAR(2),
tel VARCHAR(20),
addr VARCHAR(50),
salary DOUBLE
);

INSERT INTO employee(id,`name`,sex,tel,addr,salary)
VALUES
(10001,'张一一','男','13456789000','山东青岛',1001.58),
(10002,'刘小红','女','13454319000','河北保定',1201.21),
(10003,'李四','男','0751-1234567','广东佛山',1004.11),
(10004,'刘小强','男','0755-5555555','广东深圳',1501.23),
(10005,'王艳','男','020-1232133','广东广州',1405.16);

3. 查询出薪资在1200~1300之间的员工信息。

SELECT *
FROM employee
WHERE salary BETWEEN 1200 AND 1300;

4. 查询出姓“刘”的员工的工号，姓名，家庭住址.

SELECT id,`name`,addr
FROM employee
WHERE `name` LIKE '刘%';

5. 将“李四”的家庭住址改为“广东韶关”

UPDATE employee
SET addr = '广东韶关'
WHERE `name` = '李四';

6. 查询出名字中带“小”的员工

SELECT `name`
FROM employee
WHERE `name` LIKE '%小%';