MySQL 学习 --->>> 数据处理之增删改
数据处理之增删改
数据的插入
①为表的所有字段按默认顺序插入数据
VALUES的方式添加;使用这种语法一次只能向表中插入一条数据。
为表的所有字段按默认顺序插入数据
语法：
INSERT INTO 表名
VALUES (value1,value2,....);
值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。
SHOW tables;
DESC test_tbl1;
INSERT INTO test_tbl1 VALUES (1,'Jebbain',123,NOW());
SELECT * FROM test_tbl1;
②为表的指定字段插入数据
情况2：为表的指定字段插入数据 [推荐用这种]
语法：
INSERT INTO 表名(column1 [, column2, …, columnn])
VALUES (value1 [,value2, …, valuen]);
为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的
默认值。
在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,....valuen需要与column1,...columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。
INSERT INTO test_tbl1(id,`name`,birthday) VALUES (2,'Tryeesfgt','2000-09-8');
③同时插入多条记录
INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔
开，基本语法格式如下：
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]);
使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含
义如下：
- Records：表明插入的记录条数。
- Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
- Warnings：表明有问题的数据值，例如发生数据类型转换。
INSERT INTO
test_tbl1(id,`name`,birthday)
VALUES
(3,'FinGON','2000-09-8'),
(4,'PerwRon','2000-09-8'),
(5,'Zgenin','2000-09-8');
一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中 效率更高 。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。
VALUES 也可以写成 VALUE ，但是VALUES是标准写法。
字符和日期型数据应包含在单引号中。
④将查询结果插入到表中
方式2：将查询结果插入到表中
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 子句中的列名对应。
这种依赖已有的表对表插入数据时，需要注意查询结果字段的类型字长要跟插入列的类型字长一样
USE testdb2;
SHOW TABLES;
CREATE TABLE
IF
NOT EXISTS test_tbl2 AS SELECT
*
FROM
test_tbl1
WHERE
1 = 2;-- 复制表结构，创建一个结构跟已有表一样的空表
DESC test_tbl1;
SELECT * FROM test_tbl1;
INSERT INTO test_tbl2 ( `id`, `name`, birthday ) SELECT
`id`,
`name`,
birthday
FROM
test_tbl1
WHERE
`id` = 1; -- 查询结果会对应插入
SELECT * FROM test_tbl2;
数据的更新
更新语句：
UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]
可以一次更新多条数据。
如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
使用 WHERE 子句指定需要更新的数据。
如果省略 WHERE 子句，则表中的所有数据都将被更新。
SHOW TABLES;
SELECT * FROM test_emp3;
INSERT INTO test_emp3 SELECT * FROM employees;
-- 关闭自动提交，尝试
SET AUTOCOMMIT = FALSE;
UPDATE test_emp3
# 添加WHERE 子句修改指定内容
SET salary = salary * 1.2
WHERE
department_id = 50;
ROLLBACK;
SELECT DISTINCT * FROM test_emp3 WHERE employee_id = 120;
# 不添加条件更改整个表中记录数据
UPDATE test_emp3 SET salary = salary * 1.1;
UPDATE test_emp3
SET salary = salary * 3,
department_id = 90
WHERE
employee_id = 120; -- 可以回滚
-- 更新数据也会失败,受到外部约束影响
UPDATE employees SET department_id = 55 WHERE department_id = 90;
/*
Cannot add or update a child row: a foreign key constraint fails (`atguigudb`.`employees`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`)).
departments 表中没有值为55 的 department_id
*/
数据的删除
删除语句：
DELETE FROM table WHERE condition;
table_name指定要执行删除操作的表；“[WHERE]”为可选参数，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。
# WHERE 添加条件删除指定表记录
SET AUTOCOMMIT = FALSE;
DELETE FROM test_emp3 WHERE department_id = 30;
SELECT * FROM test_emp3 WHERE department_id = 30;
ROLLBACK;
# 省略WHERE 表里面的记录被删光
DELETE FROM test_emp3;
SELECT * FROM test_emp3;
# 受约束影响，删除操作也会出错
DELETE FROM departments WHERE department_id = 60;
/*
Cannot delete or update a parent row: a foreign key constraint fails (`atguigudb`.`employees`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`))
这个字段是给人家当外键用的，不能动
*/
新特性：计算列
新特性 ： 计算列
什么叫计算列呢？
简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。
在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。
CREATE TABLE IF NOT EXISTS abc(
a INT,
b INT,
c INT GENERATED ALWAYS AS ( a + b ) VIRTUAL
);
INSERT INTO abc(a,b) VALUES (12314,879475);
SELECT * FROM abc;
UPDATE abc
SET b = 123
WHERE a = 12314; -- 更新后计算列的值也会跟着变
练习
表
| 字段名 | 字段说明 | 数据类型 |
|---|---|---|
| id | 书编号 | INT |
| name | 书名 | VARCHAR(50) |
| authors | 作者 | VARCHAR(100) |
| price | 价格 | DECIMAL |
| pubdate | 出版日期 | YEAR |
| note | 说明 | VARCHAR(100) |
| num | 库存 | INT |
可用值
| 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 |
要求：
- 创建数据库test01_library
- 创建表 books
- 向books表中插入记录
- 不指定字段名称，插入第一条记录
- 指定所有字段名称，插入第二记录
- 同时插入多条记录（剩下的所有记录）
- 将小说类型(novel)的书的价格都增加5。
- 将名称为EmmaT的书的价格改为40，并将说明改为drama。
- 删除库存为0的记录。
- 统计书名中包含a字母的书
- 统计书名中包含a字母的书的数量和库存总量
- 找出“novel”类型的书，按照价格降序排列
- 查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
- 按照note分类统计书的数量
- 按照note分类统计书的库存量，显示库存量超过30本的
- 查询所有图书，每页显示5本，显示第二页
- 按照note分类统计书的库存量，显示库存量最多的
- 查询书名达到10个字符的书，不包括里面的空格
- 查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通， joke显示笑话
- 查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货
- 统计每一种note的库存量，并合计总量
- 统计每一种note的数量，并合计总量
- 统计库存量前三名的图书
- 找出最早出版的一本书
- 找出novel中价格最高的一本书
- 找出书名中字数最多的一本书，不含空格
-- 练习
USE testdb2;
CREATE TABLE IF NOT EXISTS book_testdb(
bk_id INT AUTO_INCREMENT COMMENT '书编号',
bk_name VARCHAR(50) COMMENT '书名',
bk_authors VARCHAR(100) COMMENT '作者',
bk_price FLOAT COMMENT '价格',
bk_pubdate YEAR COMMENT '出版日期',
note VARCHAR(100) COMMENT '说明',
num INT COMMENT '库存',
PRIMARY KEY (bk_id)
);
DESC book_testdb;
/*
# 3、向books表中插入记录
# 1）不指定字段名称，插入第一条记录
# 2）指定所有字段名称，插入第二记录
# 3）同时插入多条记录（剩下的所有记录）
*/
INSERT INTO book_testdb
VALUES
( 1, 'Still LIFE', 'abcd', 4, 2022, 'goodbay', 999);
INSERT INTO book_testdb
( bk_id, bk_name, bk_authors, bk_price, bk_pubdate, note, num )
VALUES
( NULL, 'He`s a pirvate', 'rrr', 7, 2018, 'bay', 999 );
INSERT INTO book_testdb
( bk_id, bk_name, bk_authors, bk_price, bk_pubdate, note, num )
VALUES
( NULL, "Tal of AAA", 'Dickes', 23, 1995, 'novel', 11 ),
( NULL, "EmmaT Jane", 'lura', 35, 1993, 'joke', 22 ),
( NULL, "Story of Jane", "Jane Tim", 40, 2001, 'novel', 0 ),
( NULL, "Lovey Day", "George Byron", 20, 2005, 'novel', 30 ),
( NULL, "Old land", "Honore Blade", 30, 2010, 'law', 0 ),
( NULL, "The Battle Upton", 'Sara', 30, 1999, 'medicine', 40 ),
( NULL, "Rose Hood", "Richard haggard", 28, 2008, 'cartoon', 28 );
# 4、将小说类型(novel)的书的价格都增加5。
UPDATE book_testdb
SET bk_price = bk_price + 5
WHERE note = 'novel';
SELECT * FROM book_testdb;
# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。
UPDATE book_testdb
SET bk_price = 40 , note = 'drama' , bk_name = 'EmmaT' , bk_authors = "Jane lura"
WHERE bk_name = "EmmaT Jane";
# 6、删除库存为0的记录。
DELETE
FROM
book_testdb
WHERE
num = 0;
# 7、统计书名中包含a字母的书
SELECT
bk_name
FROM
book_testdb
WHERE
bk_name REGEXP 'a';
# 8、统计书名中包含a字母的书的数量和库存总量
SELECT
COUNT( num ),
COUNT(*)
FROM
book_testdb
WHERE
bk_name REGEXP 'a';
# 9、找出“novel”类型的书，按照价格降序排列
SELECT
bk_name,
bk_price,
note
FROM
book_testdb
WHERE
note = 'novel'
ORDER BY
bk_price DESC;
# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
SELECT
*
FROM
book_testdb
ORDER BY
num DESC,
note ASC;
# 11、按照note分类统计书的数量
SELECT
note,
COUNT(*)
FROM
book_testdb
GROUP BY
note;
# 12、按照note分类统计书的库存量，显示库存量超过30本的
SELECT
note,
num
FROM
( SELECT note, num FROM book_testdb GROUP BY note, num ) abc -- 查询结果集作为假表用的时候要给结果集起个别名
WHERE
num > 30;
# 13、查询所有图书，每页显示5本，显示第二页
SELECT
*
FROM
book_testdb
LIMIT 5,5;
# 14、按照note分类统计书的库存量，显示库存量最多的
SELECT
note,
num
FROM
book_testdb
GROUP BY
note,
num
ORDER BY
num DESC,
note DESC
LIMIT 0,1;
# 15、查询书名达到10个字符的书，不包括里面的空格
SELECT
bk_name
FROM
book_testdb
WHERE
CHAR_LENGTH(REPLACE ( bk_name, ' ', '' )) > 10;
# 16、查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话
SELECT
bk_name '书名',
note,
CASE
note
WHEN 'novel' THEN
'小说'
WHEN 'law' THEN
'法律'
WHEN 'medicine' THEN
'医药'
WHEN 'cartoon' THEN
'卡通'
WHEN 'joke' THEN
'笑话' ELSE 'bay'
END '类型'
FROM
book_testdb;
SELECT * FROM book_testdb;
# 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货
SELECT
bk_name '书名',
num '库存',
CASE
WHEN num > 30 THEN
'滞销'
WHEN 0 < num < 10 THEN
'畅销'
WHEN num = 0 THEN
'无货'
END '状态'
FROM
book_testdb;
# 18、统计每一种note的库存量，并合计总量
SELECT
IFNULL( note, '合计总库存' ) AS note, -- 这个IFNULL是因为WITH ROLLUP 计算分组后所有分组总量时note这一列是null，只会统计数量
SUM( num )
FROM
book_testdb
GROUP BY
note WITH ROLLUP;
# 19、统计每一种note的数量，并合计总量
SELECT
IFNULL( note, '所有种类数量' ) AS note,
COUNT( note )
FROM
book_testdb
GROUP BY
note WITH ROLLUP;
# 20、统计库存量前三名的图书
SELECT
bk_name,
num
FROM
book_testdb
ORDER BY
num DESC
LIMIT 0,3;
# 21、找出最早出版的一本书
SELECT
bk_name,
bk_pubdate
FROM
book_testdb
ORDER BY
bk_pubdate
LIMIT 0,1;
# 22、找出novel中价格最高的一本书
SELECT
bk_name,
bk_price
FROM
book_testdb
WHERE
note = 'novel'
ORDER BY
bk_price DESC
LIMIT 0,1;
# 23、找出书名中字数最多的一本书，不含空格
SELECT
bk_name
FROM
book_testdb
ORDER BY
CHAR_LENGTH(REPLACE ( bk_name, " ", '' )) DESC
LIMIT 0,1;
浙公网安备 33010602011771号