25.8 MySQL 数据处理之增删改

1. 数据完整性问题

数据完整性问题:
数据的插入, 更新, 删除等操作都需要满足数据库表的完整性约束条件, 否则这些操作将会失败.
完整性约束在数据库设计中起到了至关重要的作用, 它们确保了数据库中数据的准确性和一致性.

约束条件的全称通常被称为'完整性约束条件'(Integrity Constraints).
这些约束条件用于定义和维护数据库中的数据完整性, 确保数据的正确性和一致性.

以下是一些主要的完整性约束及其作用:
* 1. 主键约束(Primary Key Constraint): 主键用于唯一标识数据库表中的每一行.
     它确保表中每一行的主键值都是唯一的, 并且不允许为空.
     这有助于维护数据的唯一性和准确性.
     
* 2. 外键约束(Foreign Key Constraint): 外键用于建立表与表之间的关系.
     它确保一个表中的字段值必须对应另一个表的主键值.
     这有助于维护数据的一致性和引用完整性.

* 3. 唯一性约束(Unique Constraint): 唯一性约束确保表中的某个字段或字段组合的值是唯一的.
     这有助于防止重复数据的出现, 从而保持数据的准确性.
     
* 4. 非空约束(Not Null Constraint): 非空约束确保表中的某个字段在插入或更新记录时不能为空.
     这有助于确保所有必要的数据都被正确记录.

当执行数据插入, 更新或删除操作时, 数据库管理系统会检查这些操作是否违反了上述完整性约束.
如果违反了任何约束, 操作将失败, 并返回一个错误消息, 指示违反了哪个约束.

2. 插入数据

在MySQL中, 使用INSERT INTO语句插入数据.
以下是几种常见的插入数据的方式:
* 1. 完整插入.
* 2. 部分插入.
* 3. 多行插入.
* 4. 查询结构插入.

2.1 完整插入

完整插入: 为表中的所有列提供值, 并将这些值作为新行添加到表中.

语法格式:
INSERT INTO table_name (column1, column2, column3, ...)  
VALUES (value1, value2, value3, ...);

省略列名:
INSERT INTO table_name  
VALUES (value1, value2, value3, ...);

* VALUES也可以写成VALUE, 但是VALUES是标准写法.

注意事项:
* 1. 顺序说明: 当执行INSERT INTO语句时, 确实需要确保column与value的参数位置是一一对应的.
     如果列名的顺序改变了, 那么相应的值的顺序也必须随之改变, 以保持匹配.
     这是因为SQL语句是严格按照语法规则来执行的, 它依赖于列和值的正确对应关系来正确地将数据插入到表中.

* 2. 省略列名: 如果插入值的顺序与表中定义的列的顺序一致, 可以省略列名.
     这样做的一个前提条件是, 你提供的值的数量, 顺序和数据类型必须与表中定义的列完全一致.

-- 使用数据库:
mysql> use db0;
Database changed

-- 创建表格:
mysql> CREATE TABLE emp2(
    id INT,
    name VARCHAR(20),
    age INT
);
Query OK, 0 rows affected (0.02 sec)

-- 插入数据:
mysql> INSERT INTO emp2(id, name, age) VALUE(1, 'kid', 18);
Query OK, 1 row affected (0.01 sec)

-- 省略列名写法(按字段默认顺序写入):
mysql> INSERT INTO emp2 VALUE(2, 'qq', 18);
Query OK, 1 row affected (0.01 sec)

-- 按列名顺序传入(自定义顺序):
mysql> INSERT INTO emp2(id, age, name) VALUE(3, 18, 'qz');
Query OK, 1 row affected (0.01 sec)

-- 查看表数据:
mysql> SELECT * FROM emp2;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | kid  |   18 |
|    2 | qq   |   18 |
|    3 | qz   |   18 |
+------+------+------+
3 rows in set (0.00 sec)

2.2 部分插入

部分插入: 明确指定要插入数据的列名, 并为这些列提供值.
如果表中有自增字段或默认值字段, 可以省略这些字段的值, 数据库会自动处理它们. 

当在MySQL中使用INSERT INTO语句插入数据时, 如果未为表中的某些列指定值, 这些列的值将根据以下规则确定:
* 1. 默认值: 如果某列在表定义中有默认值, 并且该列在INSERT语句中没有被显式地指定值, 那么该列将被自动赋予默认值.
     默认值可以是固定的值, 如字符串或数字, 也可以是表达式(如当前日期和时间).

* 2. 允许NULL值: 如果某列在表定义中允许NULL值, 并且该列在INSERT语句中没有被显式地指定值, 同时也没有设置默认值,
     那么该列的值将被设置为NULL.

* 3. 不允许NULL值且没有默认值: 如果某列在表定义中不允许NULL值, 同时也没有设置默认值,
     并且该列在INSERT语句中没有被显式地指定值, 那么执行INSERT语句时将会产生一个错误.
     这个错误通常是一个约束违反错误, 指示你试图插入一个不符合表定义要求的行.

-- 查看表结构:
mysql> DESC emp2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       | 
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
-- 允许为NULL.
-- 默认值为NULL.

-- 为指定字段插入值, 此处没有指定的字段默认为NULL:
mysql> INSERT INTO emp2(id, name) VALUE(4, 'ez');
Query OK, 1 row affected (0.01 sec)

-- 查看表数据:
mysql> SELECT * FROM emp2;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | kid  |   18 |
|    2 | qq   |   18 |
|    3 | qz   |   18 |
|    4 | ez   | NULL |
+------+------+------+
4 rows in set (0.00 sec)

2.3 多行插入

多行插入: 在一次插入操作中插入多行数据.
通常是通过在VALUES子句中提供多个值列表来实现的, 每个值列表用逗号分隔.
这种方式可以减少与数据库的交互次数, 提高数据插入的效率.

语法格式:
INSERT INTO table_name (column1, column2, column3, ...)  
VALUES   
    (value1a, value1b, value1c, ...),  
    (value2a, value2b, value2c, ...),  
    ...  
;

注意事项:
* 1. 在VALUES子句中, 每个括号内的值列表代表一行数据.
* 2. 所有值列表中的列数必须与表中定义的列数相匹配(可以是完整插入或部分插入).

-- 插入多行数据:
mysql> INSERT INTO emp2 (id, name, age)
VALUES
    (5, 'vn', 19),
    (6, 'js', 20);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- 查看表数据:
mysql> SELECT * FROM emp2;
+------+------+------+
| id   | name | age  |
+------+------+------+
|  ... | ...  |  ... |  -- 省略
|    5 | vn   |   19 |
|    6 | js   |   20 |
+------+------+------+
6 rows in set (0.00 sec)

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

2.4 查询结构插入

查询结构插入: 从一个数据库表查询数据, 并将这些数据按照某种结构或格式插入到另一个数据库表或存储结构中.
这种操作在数据库管理和应用中非常常见, 特别是在数据迁移, 数据转换或数据同步等场景中.

语法格式(不必书写VALUES子句, 查询中的值列表与INSERT子句中的列名相对应):
INSERT INTO target_table (column1, column2, column3, ...)  
SELECT column1, column2, column3, ...  
FROM source_table  
WHERE condition; 

以下是查询结构插入的一般步骤和考虑因素:
* 1. 确定源表(target_table)和目标表(source_table).
     源表: 包含要查询和提取数据的表.
     目标表: 数据将被插入的表.

* 2. 分析表结构.
     比较源表和目标表的字段, 确保它们之间的数据类型和格式兼容(如果不匹配, 可能需要进行类型转换).
     如果目标表有额外的字段, 需要决定这些字段的值如何设置(例如, 使用默认值, 空值或计算得出的值).

* 3. 编写SQL查询.
     编写SELECT语句, 从源表中选择要插入的数据.
     如果需要, 可以使用WHERE子句来过滤数据.
     可以使用聚合函数, 子查询或其他SQL特性来构建所需的数据结构.

* 4. 插入数据.
     使用INSERT INTO语句将数据插入到目标表.
     如果目标表有自增字段, 确保在插入数据时省略这些字段, 以便数据库自动分配值.

-- 将员工表中工资大于17000的员工信息(id, 名字)插入到emp2表中:
mysql> INSERT INTO emp2(id, name)
SELECT employee_id, first_name FROM atguigudb.employees
WHERE salary > 17000;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

-- 查看emp2表数据:
mysql> SELECT * FROM emp2;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|  ... | ...  |  ... |  -- 省略
|  100 | Steven | NULL |
+------+--------+------+
7 rows in set (0.00 sec)

3. 更新数据

在MySQL中, 使用UPDATE语句修改已存在的记录.

语法格式:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

说明:
- UPDATE 关键字后面是要更新的表名.
- SET 关键字后面是要更新的列名和对应的新值, 用逗号分隔.
- WHERE 关键字后面是更新条件, 用于指定哪些记录需要被更新.
  如果不使用WHERE子句, 将更新表中的所有记录(通常不推荐这样做, 除非你确定要更新所有记录).

注意事项:
* 1. 始终使用WHERE子句来限制更新的记录, 除非你确定要更新所有记录.
* 2. 在执行更新操作之前, 最好先备份数据或在测试环境中验证SQL语句的正确性.
* 3. 尽量避免在更新操作中使用子查询, 因为子查询可能会使更新操作变得复杂且难以优化.

-- 修改emp2表中Steven的id为7, 年龄改为25:
mysql> UPDATE emp2
    SET id = 7, age = 25
    WHERE id = 100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 查看emp2表中的数据:
mysql> SELECT * FROM emp2;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|  ... | ...  |  ... |  -- 省略
|    7 | Steven |   25 |
+------+--------+------+
7 rows in set (0.00 sec)

执行一个UPDATE语句或其他修改数据的SQL操作时, MySQL或类似的数据库管理系统通常会返回一些操作结果的统计信息.
这些信息具体解释如下:
* 1. Rows matched: 1: 这意味着SQL语句的WHERE条件匹配了表中的一行记录.
* 2. Changed: 1: 这表示实际上有一行记录被修改了.
* 3. Warnings: 0: 这表示在执行操作期间没有产生任何警告.

-- 如果省略WHERE子句, 则表中的所有数据都将被更新:
-- 临时关闭commit自动提交事务:
mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

-- 修改数据(id全为7, 年龄全为25):
 mysql>  UPDATE emp2 SET id = 7, age = 25;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 7  Changed: 6  Warnings: 0

-- 查看emp2表中的数据:
mysql> SELECT * FROM emp2;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    7 | kid    |   25 |
|    7 | qq     |   25 |
|    7 | qz     |   25 |
|    7 | ez     |   25 |
|    7 | vn     |   25 |
|    7 | js     |   25 |
|    7 | Steven |   25 |
+------+--------+------+
7 rows in set (0.00 sec)

-- 回滚:
mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

-- 查看emp2表中的数据:
mysql> SELECT * FROM emp2;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | kid    |   18 |
|    2 | qq     |   18 |
|    3 | qz     |   18 |
|    3 | ez     | NULL |
|    5 | vn     |   19 |
|    6 | js     |   20 |
|    7 | Steven |   25 |
+------+--------+------+
7 rows in set (0.00 sec)

4. 删除数据

在MySQL中, 使用DELETE语句来删除表格中的数据.
DELETE语句允许你基于某些条件来删除行, 或者如果你不提供任何条件, 它将删除表中的所有行.

以下是一些基本示例来说明如何使用DELETE语句:
* 1. 删除表中的所有行: DELETE FROM table_name;
     这将删除表中的所有数据, 并且这些数据是不可恢复的(除非你有备份).
     因此, 在执行此操作之前, 请确保你真的想删除所有数据.

* 2. 基于条件删除行: DELETE FROM table_name WHERE condition;
     如果只想删除满足某些条件的行, 可以在DELETE语句中使用WHERE子句.
     
     在某些情况下, 可能想在删除行之前查看它们.
     可以通过先执行一个SELECT语句来查看要删除的行, 然后再执行DELETE语句来实现.

-- 临时关闭commit自动提交事务:
mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

-- 删除所有数据:
mysql> DELETE FROM emp2;
Query OK, 7 rows affected (0.01 sec)

-- 查看emp2表中的数据:
mysql> SELECT * FROM emp2;
Empty set (0.00 sec)

-- 回滚:
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

-- 查看emp2表中的数据:
mysql> SELECT * FROM emp2;
...
7 rows in set (0.00 sec)

-- 删除指定的数据:
-- 先查看一番需要删除的数据, 进行确定
mysql> SELECT * FROM emp2 WHERE id = 7;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    7 | Steven |   25 |
+------+--------+------+
1 row in set (0.00 sec)

-- 删除id为7的数据:
mysql> DELETE FROM emp2 WHERE id = 7;
Query OK, 1 row affected (0.00 sec)

-- 查看emp2表中的数据:
mysql> SELECT * FROM emp2;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | kid  |   18 |
|    2 | qq   |   18 |
|    3 | qz   |   18 |
|    3 | ez   | NULL |
|    5 | vn   |   19 |
|    6 | js   |   20 |
+------+------+------+
6 rows in set (0.00 sec)

5. 计算列

5.1 计算列说明

MySQL 8中引入了计算列(Generated Column, 也称为生成列或虚拟列)这一新特性.
计算列是一种虚拟列, 它的值不会实际存储在数据库中, 而是在查询时根据指定的表达式动态计算得出.
通过使用计算列, 开发者可以方便地在查询结果中添加衍生字段, 而无需修改底层数据表结构.
这为数据分析, 报表生成等任务提供了更大的灵活性和便捷性.

例如, 假设有一个包含a列和b列的数据表, 我们想要得到一个表示a和b之和的新列c, 而不修改原始数据表.
在MySQL 8中, 我们可以使用计算列来实现这一需求.
这样, 当查询这个表时, c列的值会根据a列和b列的值动态计算得出, 而不需要在物理存储中增加一列来存储这些计算结果.

需要注意的是, 计算列在插入或更新数据时并不会进行实际的插入或更新操作, 因为它们的值是基于其他列动态计算得出的.
因此, 对于计算列, 我们不能直接进行插入或更新操作.

创建计算列的语法格式如下:
column_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL|STORED] [other_column_attributes]

- colunm: 是计算列的名称.
- data_type: 是计算列的数据类型, 可以是MySQL支持的任何数据类型.
- GENERATED ALWAYS: 是可选的, 表示这个列的值总是由表达式计算得到, 不能手动插入或更新.
- AS (expression): 用于定义计算列值的表达式, 表达式可以引用表中的其他列.
- VIRTUAL|STORED: 指定计算列是虚拟列还是存储列.
  VIRTUAL表示计算列的值不存储, 只在查询时计算;
  STORED表示计算列的值会实际存储, 在添加或更新行时计算并存储.
- other_column_attributes: 其他列属性.

5.2 计算列的使用

例如, 假设我们有一个名为tb1的表, 其中包含a和b两列, 想要添加一个计算列c, 其值为a和b的和.
可以使用以下语句创建计算列:

CREATE TABLE tb1 (
  id INT,
  a INT,
  b INT,
  c INT GENERATED ALWAYS AS (a + b) VIRTUAL  -- 每个列只能使用一种计算表达式
);

在这个例子中, c列是一个计算列, 其值是根据a和b列的值动态计算得出的.
由于使用了VIRTUAL关键字, c列的值不会实际存储在数据库中, 而是在查询时计算得出.

需要注意的是, 在插入或更新tb1表的数据时, 不需要对c列进行任何操作, 它的值会自动根据a和b列的值计算得出.
同时, 由于c列是GENERATED ALWAYS的, 尝试手动插入或更新c列的值将会导致错误.

在使用计算列时, 始终明确指定要插入值的列名, 并省略计算列.
不能使用完全省略列名的写法!!!

-- 创建表格:
mysql> CREATE TABLE tb1 (
  id INT,
  a INT,
  b INT,
  c INT GENERATED ALWAYS AS (a + b) VIRTUAL  -- 设置计算列
);
Query OK, 0 rows affected (0.02 sec)

-- 插入数据:
mysql> INSERT INTO tb1 (id, a, b) VALUES(1, 1, 2);
Query OK, 1 row affected (0.01 sec)

-- 查看表中数据:
mysql> select * from tb1;
+------+------+------+------+
| id   | a    | b    | c    |
+------+------+------+------+
|    1 |    1 |    2 |    3 |
+------+------+------+------+
1 row in set (0.00 sec)

5.3 修改计算列表达式

在MySQL中, 不能够直接修改计算列的表达式.
必须先删除现有的计算列, 然后重新添加它, 使用新的表达式.

以下是修改计算列表达式的步骤:

* 1. 删除计算列: ALTER TABLE table_name DROP COLUMN virtual_column_name;

* 2. 重新添加计算列, 使用新的表达式:
     ALTER TABLE table_name ADD COLUMN virtual_column_name data_type GENERATED ALWAYS AS (new_expression);

这里是一个具体的例子, 我们有一个名为tb1的表, 它有一个计算列c，用于计算a和b的和.
原始计算列定义: c INT GENERATED ALWAYS AS (a + b) VIRTUAL .
现在, 如果想要修改c列的表达式为计算ab列的乘机,  可以这样做:
* 1. 删除计算列: ALTER TABLE tb1 DROP COLUMN c;
* 2. 重新添加计算列, 使用新的表达式: ALTER TABLE tb1 ADD  c INT GENERATED ALWAYS AS (a * b) VIRTUAL;

-- 删除计算列:
mysql> ALTER TABLE tb1 DROP COLUMN c;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 重新添加计算列:
mysql> ALTER TABLE tb1 ADD  c INT GENERATED ALWAYS AS (a * b) VIRTUAL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 查看表数据:
mysql> SELECT * FROM tb1;
+------+------+------+------+
| id   | a    | b    | c    |
+------+------+------+------+
|    1 |    1 |    2 |    2 |
+------+------+------+------+
1 row in set (0.00 sec)

6. 练习

在练习时最好关闭自动提交事务, 每次手动COMMIT提交数据, 这样的话在写错题的时候能够使用回滚ROLLBACK撤销.

-- 关闭自动提交事务:
mysql> SET autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

-- 1. 创建数据库test01_library:
mysql> CREATE DATABASE test01_library;
Query OK, 1 row affected (0.01 sec)

-- 2. 使用test01_library库:
mysql> USE test01_library;
Database changed

-- 3. 创建表books, 表结构如下:
-- 字段名   字段说明     数据类型
-- id      书编号       INT
-- name    书名         VARCHAR(50)
-- authors 作者         VARCHAR(100)
-- price   价格         FLOAT
-- pubdate 出版日期     YEAR
-- note    说明         VARCHAR(100)
-- num     库存         INT

mysql> CREATE TABLE books(
    id INT,
    name VARCHAR(50),
    authors VARCHAR(100),
    price FLOAT,
    pubdate YEAR,
    note VARCHAR(100),
    num INT
);
Query OK, 0 rows affected (0.02 sec)

-- 查看表结构:
mysql> DESC books;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int          | YES  |     | NULL    |       |
| NAME    | varchar(50)  | YES  |     | NULL    |       |
| authors | varchar(100) | YES  |     | NULL    |       |
| price   | float        | YES  |     | NULL    |       |
| pubdate | year         | YES  |     | NULL    |       |
| note    | varchar(100) | YES  |     | NULL    |       |
| num     | int          | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

-- 提交事务:
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

书编号	书名	作者	价格	出版日期	说明	库存
`1`	`Tal of AAA Dickes`	`Dickes`	`23`	`1995`	`Dickes`	`11`
`2`	`Emma`	`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. 向books表中插入记录.
-- 4.1 不指定字段名称, 插入第一条记录:
mysql> INSERT INTO books VALUE(1, 'Tal of AAA', 'Dickes', 23, '1995', 'novel', 11);
Query OK, 1 row affected (0.00 sec)

-- 4.2 指定所有字段名称, 插入第二记录:
mysql> INSERT INTO 
books(id, name, authors, price, pubdate, note, num) 
VALUE(2, 'EmmaT', 'Jane lura', 35, '1993', 'joke', 22);
Query OK, 1 row affected (0.00 sec)

-- 4.3 同时插入多条记录(剩下的所有记录):
mysql> INSERT INTO books 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);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

-- 查看所有数据:
mysql> SELECT * FROM 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 |
+------+---------------+-----------------+-------+---------+----------+------+
7 rows in set (0.00 sec)

-- 提交事务:
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

-- 5. 将小说类型(note = novel)的书的价格都增加5.
-- 将获取所有note为novel的小说数据价格:
mysql> SELECT price FROM books WHERE note = 'novel';
+-------+
| price |
+-------+
|    23 |
|    40 |
|    20 |
+-------+
3 rows in set (0.00 sec)

-- 修改表中的数据:
mysql> UPDATE books SET price = price + 5 WHERE note = 'novel';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

-- 将获取所有note为novel的小说数据价格:
mysql> SELECT price FROM books WHERE note = 'novel';
+-------+
| price |
+-------+
|    28 |
|    45 |
|    25 |
+-------+
3 rows in set (0.00 sec)

-- 提交事务:
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

-- 6. 将名称为EmmaT的书的价格改为40, 并将说明改为drama.
-- 查看名称为EmmaT的书籍信息:
mysql> SELECT price, note FROM books WHERE name = 'EmmaT';
+-------+------+
| price | note |
+-------+------+
|    35 | joke |
+-------+------+
1 row in set (0.00 sec)

-- 更新数据:
mysql> UPDATE books SET price = 40, note = 'drama' WHERE name = 'EmmaT';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 查看名称为EmmaT的书籍信息:
mysql> SELECT price, note FROM books WHERE name = 'EmmaT';
+-------+-------+
| price | note  |
+-------+-------+
|    40 | drama |
+-------+-------+
1 row in set (0.00 sec)

-- 提交事务:
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

-- 7. 删除库存为0的记录:
-- 查看库存为0的记录:
mysql> SELECT * FROM books WHERE num = 0;
+------+---------------+--------------+-------+---------+-------+------+
| id   | NAME          | authors      | price | pubdate | note  | num  |
+------+---------------+--------------+-------+---------+-------+------+
|    3 | Story of Jane | Jane Tim     |    45 |    2001 | novel |    0 |
|    5 | Old land      | Honore Blade |    30 |    2010 | law   |    0 |
+------+---------------+--------------+-------+---------+-------+------+
2 rows in set (0.00 sec)

-- 删除:
mysql> DELETE FROM books WHERE num = 0;
Query OK, 2 rows affected (0.00 sec)

-- 查看库存为0的记录:
mysql> SELECT * FROM books WHERE num = 0;
Empty set (0.00 sec)

-- 提交事务:
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)

-- 8. 统计书名中包含a字母的书:
mysql> SELECT COUNT(name) FROM books WHERE name LIKE '%a%';
+-------------+
| COUNT(name) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

-- 9. 统计书名中包含a字母的书的数量和库存总量:
+----------+----------+
| 书籍数量 | 库存总量  |
+----------+----------+
|        4 |      103 |
+----------+----------+
1 row in set (0.00 sec)

-- 10. 找出'novel'类型的书, 按照价格降序排列:
mysql> SELECT note, price FROM books WHERE note = 'novel' ORDER BY price;
+-------+-------+
| note  | price |
+-------+-------+
| novel |    25 |
| novel |    28 |
+-------+-------+
2 rows in set (0.00 sec)

-- 11.查询图书信息, 按照库存量降序排列, 如果库存量相同的按照note升序排列:
mysql> SELECT * FROM books ORDER BY num DESC, note;
+------+------------+-----------------+-------+---------+----------+------+
| id   | NAME       | authors         | price | pubdate | note     | num  |
+------+------------+-----------------+-------+---------+----------+------+
|    6 | The Battle | Upton Sara      |    30 |    1999 | medicine |   40 |
|    4 | Lovey Day  | George Byron    |    25 |    2005 | novel    |   30 |
|    7 | Rose Hood  | Richard haggard |    28 |    2008 | cartoon  |   28 |
|    2 | EmmaT      | Jane lura       |    40 |    1993 | drama    |   22 |
|    1 | Tal of AAA | Dickes          |    28 |    1995 | novel    |   11 |
+------+------------+-----------------+-------+---------+----------+------+
5 rows in set (0.01 sec)

-- 12. 按照note分类统计书的数量:
mysql> SELECT note, COUNT(*) FROM books GROUP BY note;
+----------+----------+
| note     | COUNT(*) |
+----------+----------+
| novel    |        2 |
| drama    |        1 |
| medicine |        1 |
| cartoon  |        1 |
+----------+----------+
4 rows in set (0.00 sec)

-- 13. 按照note分类统计书的库存量, 显示库存量超过30本的:
mysql> SELECT note, SUM(num) AS `stock_quantity` 
FROM books
GROUP BY note
HAVING stock_quantity > 30;
+----------+----------------+
| note     | stock_quantity |
+----------+----------------+
| novel    |             41 |
| medicine |             40 |
+----------+----------------+
2 rows in set (0.00 sec

-- 14. 查询所有图书, 每页显示5本, 显示第二页:
mysql> SELECT * FROM books LIMIT 5, 5;
Empty set (0.00 sec)

-- 15. 按照note分类统计书的库存量, 显示库存量最多的:
mysql> SELECT note, SUM(num) AS `stock_quantity`
FROM books
GROUP BY note 
ORDER BY stock_quantity DESC
LIMIT 1;
+-------+----------------+
| note  | stock_quantity |
+-------+----------------+
| novel |             41 |
+-------+----------------+
1 row in set (0.00 sec)

-- 16. 查询书名达到10个字符的书, 不包括里面的空格:
mysql> SELECT NAME FROM books WHERE CHAR_LENGTH(name) >= 10 AND name NOT like '% %';
Empty set (0.00 sec)

-- 17. 查询书名和类型, 其中note值为novel显示小说, law显示法律, medicine显示医药, cartoon显示卡通, joke显示笑话:
mysql> SELECT NAME, note,
CASE note
    WHEN 'novel' THEN '小说'
    WHEN 'law' THEN '法律'
    WHEN 'medicine' THEN '医药'
    WHEN 'cartoon' THEN '卡通'
    WHEN 'joke' THEN '笑话'
    END AS `类型`
FROM books;
+------------+----------+------+
| NAME       | note     | 类型 |
+------------+----------+------+
| Tal of AAA | novel    | 小说 |
| EmmaT      | drama    | NULL |
| Lovey Day  | novel    | 小说 |
| The Battle | medicine | 医药 |
| Rose Hood  | cartoon  | 卡通 |
+------------+----------+------+
5 rows in set (0.00 sec)

-- 18. 查询书名, 库存, 其中num值超过30本的, 显示滞销, 大于0并低于10的, 显示畅销, 为0的显示无货, 否则显示其他:
mysql> SELECT name, num,
CASE 
    WHEN num > 30 THEN '滞销'
    WHEN num > 0 AND num < 10 THEN '畅销'  -- 不能使用 0 < num < 10;
    WHEN num = 0 THEN '无货'
    ELSE '其他'
    END AS `库存`
FROM books;
+------------+------+------+
| name       | num  | 库存 |
+------------+------+------+
| Tal of AAA |   11 | 其他 |
| EmmaT      |   22 | 其他 |
| Lovey Day  |   30 | 其他 |
| The Battle |   40 | 滞销 |
| Rose Hood  |   28 | 其他 |
+------------+------+------+
5 rows in set (0.00 sec)

-- 19. 统计每一种note的库存量, 并合计总量:
mysql> SELECT note, SUM(num) 
FROM books
GROUP BY note
WITH ROLLUP;
+----------+----------+
| note     | SUM(num) |
+----------+----------+
| cartoon  |       28 |
| drama    |       22 |
| medicine |       40 |
| novel    |       41 |
| NULL     |      131 |  -- 不想显示NULL
+----------+----------+
5 rows in set (0.00 sec)

mysql> SELECT IFNULL(note, '合计总库存量') AS note, SUM(num) -- note为NULL显示合计总库存量
FROM books 
GROUP BY note 
WITH ROLLUP;
+--------------+----------+
| note         | SUM(num) |
+--------------+----------+
| cartoon      |       28 |
| drama        |       22 |
| medicine     |       40 |
| novel        |       41 |
| 合计总库存量  |      131 |
+--------------+----------+
5 rows in set, 1 warning (0.00 sec)

-- 21. 统计库存量前三名的图书:
mysql> SELECT name, num FROM books ORDER BY num DESC LIMIT 3;
+------------+------+
| name       | num  |
+------------+------+
| The Battle |   40 |
| Lovey Day  |   30 |
| Rose Hood  |   28 |
+------------+------+
3 rows in set (0.00 sec)

-- 22. 找出最早出版的一本书:
mysql> SELECT name, pubdate FROM books ORDER BY pubdate LIMIT 1;
+-------+---------+
| name  | pubdate |
+-------+---------+
| EmmaT |    1993 |
+-------+---------+
1 row in set (0.00 sec)

-- 23 找出novel中价格最高的一本书:
mysql> SELECT name, note, price FROM books 
WHERE note = 'novel' 
ORDER BY price
LIMIT 1;
+-----------+-------+-------+
| name      | note  | price |
+-----------+-------+-------+
| Lovey Day | novel |    25 |
+-----------+-------+-------+
1 row in set (0.00 sec)

-- 24. 找出书名中字数最多的一本书, 不含空格:
-- 使用REPLACE()函数将空格替换掉:
mysql> SELECT NAME, 
REPLACE(name, ' ', '') AS name2,
CHAR_LENGTH(REPLACE(name, ' ', '')) AS 'name_length' 
FROM books
ORDER BY name_length;
+------------+-----------+-------------+
| NAME       | name2     | name_length |
+------------+-----------+-------------+
| EmmaT      | EmmaT     |           5 |
| Tal of AAA | TalofAAA  |           8 |
| Lovey Day  | LoveyDay  |           8 |
| Rose Hood  | RoseHood  |           8 |
| The Battle | TheBattle |           9 |
+------------+-----------+-------------+
5 rows in set (0.00 sec)

-- 过滤出最长的:
mysql> SELECT NAME, 
REPLACE(name, ' ', '') AS name2,
CHAR_LENGTH(REPLACE(name, ' ', '')) AS 'name_length' 
FROM books
ORDER BY name_length DESC
LIMIT 1;
+------------+-----------+-------------+
| NAME       | name2     | name_length |
+------------+-----------+-------------+
| The Battle | TheBattle |           9 |
+------------+-----------+-------------+
1 row in set (0.00 sec)