MySQL基础篇——第11章 DML(数据操作):增删改
1. 插入数据(增) INSERT INTO ...
使用 INSERT INTO
语句向表中插入数据(记录)
1.1 方式1:VALUES (值列表)
- 一次只能向表中插入一条数据(记录)
VALUES
也可以写成VALUE
,但是VALUES
是标准写法- 字符串和日期类型的数据应包含在单引号
''
中
1.1.1 为表的所有字段按默认顺序插入数据
VALUES
子句中的值列表需要为表的每一个字段指定值,并且值的顺序必须和表中字段声明时的顺序相同
- 语法格式:
INSERT INTO 表名
VALUES (值1, 值2, ...);
举例:
CREATE TABLE IF NOT EXISTS emp1(
id INT,
`name` VARCHAR(15),
hire_date DATE,
salary DOUBLE(10, 2)
);
INSERT INTO emp1
VALUES (1, 'Tom', '2000-12-22', 3400);
INSERT INTO emp1
VALUES (2, 'John', NULL, NULL);
1.1.2 为表的指定字段插入数据
为表的指定字段插入数据,即在
INSERT INTO
语句中只向表的部分字段插入值,而其他字段的值为表定义时的默认值在
INSERT INTO
子句中可以按任意顺序列出字段列表,但一旦列出,VALUES
子句中的值列表需要与该字段列表一一对应
- 语法格式:
INSERT INTO 表名(字段名1, 字段名2, ...)
VALUES (值1, 值2, ...);
举例:
INSERT INTO emp1(id, salary, `name`)
VALUES (1, 4000, 'Jerry'); # 插入的该条数据(记录),hire_date的值为默认值NULL
1.1.3 同时插入多条记录
INSERT INTO
语句可以同时向数据表中插入多条记录,插入时指定多个值列表,每个值列表之间用逗号分隔开使用
INSERT INTO
同时插入多条记录时,MySQL会返回一些在执行单行插入时没有的额外信息:
- Records:表明插入的记录条数
- Duplicates:表明插入时被忽略的记录,原因可能是这些记录包含了重复的主键值
- Warnings:表明有问题的数据值,例如发生数据类型转换
同时插入多行记录的
INSERT
语句等同于多个单行插入的INSERT
语句,但是同时插入多条记录的INSERT
语句的效率更高
- 语法格式:
INSERT INTO 表名
VALUES
(值1, 值2, ...),
(值1, 值2, ...),
......
(值1, 值2, ...);
或者
INSERT INTO 表名(字段名1, 字段名2, ...)
VALUES
(值1, 值2, ...),
(值1, 值2, ...),
......
(值1, 值2, ...);
举例:
INSERT INTO emp1(id, `name`, salary)
VALUES
(4, 'Jim', 5000),
(5, '张俊杰', 5500);
1.2 方式2:将子查询结果插入到表中 SELECT ...
INSERT INTO ... SELECT ...
将子查询的结果插入到目标表中,此时不必再声明VALUES
子句如果
INSERT INTO
子句没有指定目标表中的字段,则SELECT
子句中的字段列表必须与目标表中声明的字段一一对应;如果
INSERT INTO
子句指定了目标表中的字段,则SELECT
子句中的字段列表必须与INSERT INTO
的字段列表一一对应目标表中要插入数据的字段的数据类型要与源表中查询的字段的数据类型保持一致;
目标表中要插入数据的字段的长度不能小于源表中查询的字段的长度
- 语法格式:
# 如果 `INSERT INTO` 子句没有指定目标表中的字段,则 `SELECT` 子句中的字段列表必须与目标表中声明的字段一一对应
INSERT INTO 目标表名
SELECT 源表中指定的字段列表
FROM 源表名
[WHERE condition]
......;
或者
# 如果 `INSERT INTO` 子句指定了目标表中的字段,则 `SELECT` 子句中的字段列表必须与 `INSERT INTO` 的字段列表一一对应
INSERT INTO 表名(目标表中指定的字段列表)
SELECT 源表中指定的字段列表
FROM 源表名
[WHERE condition]
......;
或者
INSERT INTO 表名(目标表中指定的字段列表)
SELECT 源表1中指定的字段列表
UNION ALL
SELECT 源表2中指定的字段列表
UNION ALL
SELECT 源表3中指定的字段列表
......;
举例:
INSERT INTO emp1
SELECT employee_id, last_name, hire_date, salary
FROM employees
WHERE department_id IN (60, 70);
INSERT INTO emp1(id, `name`, salary, hire_date)
SELECT employee_id, last_name, salary, hire_date
FROM employees
WHERE department_id IN (60, 70);
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895
UNION ALL
SELECT 2,'Dancs','Betty','Bdancs',860
UNION ALL
SELECT 3,'Biri','Ben','Bbiri',1100
UNION ALL
SELECT 4,'Newman','Chad','Cnewman',750;
2. 更新数据(改) UPDATE ... SET ... WHERE ...
- 使用
UPDATE ... SET ... WHERE ...
语句更新(修改)表中指定记录的字段值- 可以使用
WHERE
子句进行过滤,批量修改指定记录;如果省略WHERE
子句,则表中的所有记录都将被更新- 如果需要回滚数据,需要保证在DML前,进行设置:
SET autocommit = FALSE;
- 语法格式:
UPDATE 表名
SET 字段1 = 值1, 字段2 = 值2, ...
[WHERE condition];
举例:
# 使用 `WHERE` 同时修改多条记录
UPDATE emp1
SET hire_date = CURDATE();
WHERE id IN (5, 6);
# 同时修改一条记录的多个字段值
UPDATE emp1
SET hire_date = CURDATE(), salary = 6000;
WHERE id = 4;
# 将表中姓名中包含字符a的员工提薪20%
UPDATE emp1
SET salary = salary * 1.2
WHERE `name` LIKE '%a%';
- 修改表中数据时,可能存在不成功的情况,可能是由于
约束
造成的
UPDATE employees
SET department_id = 55
WHERE department_id = 110;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sFhqn35K-1652660567295)(MySQL基础篇——第11章 DML(数据操作)]之增删改.assets/image-20220425195823084.png)
说明:不存在 55 号部门
3. 删除数据(删) DELETE FROM ... WHERE ...
- 使用
DELETE FROM ... WHERE ...
语句删除表中指定记录(行)- 可以使用
WHERE
子句进行过滤,批量删除指定记录;如果省略WHERE
子句,则表中的所有记录都将被删除
- 语法格式:
DELETE FROM 表名
[WHERE <condition>];
举例:
# 使用 `WHERE` 同时删除多条记录
DELETE FROM emp1
WHERE id = 1;
# 如果省略 `WHERE` 子句,则表中的所有记录都将被删除
DELETE FROM emp1;
- 修改表中记录时,可能存在不成功的情况,可能是由于
约束
造成的
DELETE FROM departments
WHERE department_id = 60;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8hmnYwJv-1652660567296)(MySQL基础篇——第11章 DML(数据操作)]之增删改.assets/image-20220425200702229.png)
说明:不能删除包含主键的行,该主键在另一个表中被用作外键
4. 总结:DDL(数据定义语言) & DML(数据操作语言)
- DDL的操作一旦执行,就不可回滚。因为在执行完DDL操作之后,一定会自动执行一次
COMMIT
操作,而此COMMIT
操作并不受SET autocommit = FALSE
的影响 - 默认情况下,DML操作是不可回滚的。因为在执行完DML操作之后,默认会执行一次
COMMIT
操作。但如果在执行DML操作前,执行了SET autocommit = FALSE
,则此时再执行DML操作,不会自动COMMIT
,DML操作可以实现回滚
COMMIT; # 先提交一次数据
SELECT * FROM myemp3;
SET autocommit = FALSE;
DELETE FROM emp2;
#TRUNCATE TABLE emp2;
SELECT * FROM myemp3;
ROLLBACK; # 回滚数据
SELECT * FROM myemp3;
5. MySQL8新特性:计算列
计算列:简单来说就是某一列的值是通过别的列计算得来的。例如,a列值为1、b列值为2,c列不需要手动插入,定义a+b的结果为c的值,那么c就是计算列,是通过别的列计算得来的
在MySQL 8.0中,
CREATE TABLE
和ALTER 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 # 计算列
);
插入演示数据,语句如下:
INSERT INTO tb1(a,b)
VALUES (100,200);
查询数据表tb1中的数据,结果如下:
mysql> SELECT * FROM tb1;
+------+------+------+------+
| id | a | b | c |
+------+------+------+------+
| NULL | 100 | 200 | 300 |
+------+------+------+------+
1 row in set (0.00 sec)
更新数据中的数据,语句如下:
mysql> UPDATE tb1 SET a = 500;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
此时再查询表tb1中的数据,结果如下:
mysql> SELECT * FROM tb1;
+------+------+------+------+
| id | a | b | c |
+------+------+------+------+
| NULL | 500 | 200 | 700 |
+------+------+------+------+
1 row in set (0.00 sec)
6. DDL & DML 综合练习
练习1
# 1、创建数据库test01_library
CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8';
USE test01_library;
# 2、创建表 books,表结构如下:
/*
字段名 字段说明 数据类型
id 书编号 INT
name 书名 VARCHAR(50)
authors 作者 VARCHAR(100)
price 价格 FLOAT
pubdate 出版日期 YEAR
note 说明 VARCHAR(100)
num 库存 INT
*/
CREATE TABLE 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表中插入记录
/*
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
*/
# 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(*) "数量"
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分类统计书的库存量,显示库存量最多的
# 方法一:
SELECT note, SUM(num)
FROM books
GROUP BY note
HAVING SUM(num) = (
SELECT MAX(book_num)
FROM (
SELECT SUM(num) "book_num"
FROM books
GROUP BY note
) t_book_num
);
# 方法二:
SELECT note, SUM(num)
FROM books
GROUP BY note
HAVING SUM(num) >= ALL (
SELECT SUM(num) "book_num"
FROM books
GROUP BY note
);
# 方法三:
SELECT note, SUM(num)
FROM books
GROUP BY note
HAVING SUM(num) = (
SELECT SUM(num) "book_num"
FROM books
GROUP BY note
ORDER BY book_num DESC
LIMIT 0, 1
);
# 方法四:
SELECT note, SUM(num) "book_num"
FROM books
GROUP BY note
ORDER BY book_num DESC
LIMIT 0, 1;
# 15、查询书名达到10个字符的书,不包括里面的空格
SELECT *
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 SUM(note_num) "total_num"
FROM (
SELECT note, SUM(num) "note_num"
FROM books
GROUP BY note
) t_note_num;
# 方法二:使用GROUP BY ... WITH ROLLUP
SELECT IFNULL(note, '合计库存总量') "note", SUM(num) "note_num"
FROM books
GROUP BY note WITH ROLLUP;
# 19、统计每一种note的数量,并合计总量
# 方法一:
SELECT SUM(note_num) "total_num"
FROM (
SELECT note, COUNT(*) "note_num"
FROM books
GROUP BY note
) t_note_num;
# 方法二:使用GROUP BY ... WITH ROLLUP
SELECT IFNULL(note, '合计书本数量') "note", COUNT(*) "book_num"
FROM books
GROUP BY note WITH ROLLUP;
# 20、统计库存量前三名的图书
SELECT *
FROM books
ORDER BY num DESC
LIMIT 0, 3;
# 21、找出最早出版的一本书
SELECT *
FROM books
ORDER BY pubdate ASC
LIMIT 0, 1;
# 22、找出novel中价格最高的一本书
SELECT *
FROM books
WHERE note = 'novel'
ORDER BY price DESC
LIMIT 0, 1;
# 23、找出书名中字数最多的一本书,不含空格
SELECT *
FROM books
ORDER BY LENGTH(REPLACE(`name`, ' ', '')) DESC
LIMIT 0, 1;
练习2
#1. 创建数据库dbtest11
CREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8';
USE dbtest11;
#2. 运行以下脚本创建表my_employees
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;
DESC users;
#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
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);
# 方式二:
INSERT INTO my_employees
SELECT 1,'patel','Ralph','Rpatel',895
UNION ALL
SELECT 2,'Dancs','Betty','Bdancs',860
UNION ALL
SELECT 3,'Biri','Ben','Bbiri',1100
UNION ALL
SELECT 4,'Newman','Chad','Cnewman',750
UNION ALL
SELECT 5,'Ropeburn','Audrey','Aropebur',1550;
#5. 向users表中插入数据
/*
1 Rpatel 10
2 Bdancs 10
3 Bbiri 20
4 Cnewman 30
5 Aropebur 40
*/
INSERT INTO users
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 u,e
FROM users u JOIN my_employees e
ON u.`userid` = e.`Userid`
WHERE u.`userid` = 'Bbiri';
#9. 删除my_employees、users表所有数据
DELETE FROM my_employees;
DELETE FROM users;
#10. 检查所作的修正
SELECT * FROM my_employees;
SELECT * FROM users;
#11. 清空表my_employees
TRUNCATE TABLE my_employees;
练习3
# 1. 使用现有数据库dbtest11
USE dbtest11;
# 2. 创建表格pet
/*
字段名 字段说明 数据类型
name 宠物名称 VARCHAR(20)
owner 宠物主人 VARCHAR(20)
species 种类 VARCHAR(20)
sex 性别 CHAR(1)
birth 出生日期 YEAR
death 死亡日期 YEAR
*/
CREATE TABLE 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
SELECT 'Fluffy', 'harold', 'Cat', 'f', '2003', '2010'
UNION ALL
SELECT 'Claws', 'gwen', 'Cat', 'm', '2004', NULL
UNION ALL
SELECT 'Buffy', NULL, 'Dog', 'f', 2009, NULL
UNION ALL
SELECT 'Fang', 'benny', 'Dog', 'm', '2000', NULL
UNION ALL
SELECT 'bowser', 'diane', 'Dog', 'm', '2003', '2009'
UNION ALL
SELECT 'Chirpy', NULL, 'Bird', 'f', '2008', NULL;
# 4. 添加字段:主人的生日owner_birth DATE类型。
ALTER TABLE pet
ADD owner_birth DATE;
# 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;
练习4
# 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 >= 1200 AND salary =< 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 *
FROM employee
WHERE `name` LIKE '%小%';
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 >= 1200 AND salary =< 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 *
FROM employee
WHERE name
LIKE ‘%小%’;