阿里-云数据库Clouder认证-step2：SQL基础开发与应用

Reference:
https://edu.aliyun.com/certification/cldb02?spm=a2cwt.28380597.J_2214327680.21.3a5e3487UKP21u

第1章 课程简介

课时1：课程及场景介绍

1.1 课程简介

欢迎大家来到《SQL基础开发与应用》课程，本课程是“数据库clouder认证”系列中的第二阶段。该课程会通过电商平台后端开发的场景引入，带您掌握RDS for MySQL的SQL基础，了解存储过程、触发器、视图等高阶功能，最后用Python连接完成增、删、改、查操作。

1.2 课程目标

学习完本课程后，您将能够：

1. 掌握RDS for MySQL的SQL基础，如库、表、数据的增删改查等。
2. 了解RDS for MySQL的高阶功能，如存储过程、触发器、视图等。
3. 了解Python连接RDS for MySQL并进行增删改查。

1.3 课程场景

（提示：本课程为场景化教程，旨在帮助学员理解真实的应用场景，该场景讲述如何使用阿里云RDS for MySQL搭建跨境电商网站的数据库）
小明在老王的带领下已经成功创建了RDS实例，并且通过DMS连接上了数据库。在跨境电商行业内用MySQL管理商品、仓储等数据，以及通过对营销数据的分析做决策，SQL技能是必不可少的，有些复杂场景还需要使用视图、存储过程，甚至还需要用Python来做数据分析。这些技术栈对小明来说有些挑战，因此公司安排老王带领小明开启了SQL基础开发与应用的学习之旅。

第2章 RDS for MySQL的SQL基础用法

课时2： RDS for MySQL的SQL分类与数据类型

案例背景
业务方要上线一批商品，将商品信息数据存储在MySQL数据库中进行管理、查询和分析，小明需要使用SQL（Structured Query Language）完成业务要求的操作。
RDS for MySQL的SQL分类与数据类型
老王：你已经在DMS上连接RDS for MySQL，还做过增删改查，商品信息数据放RDS for MySQL上管理就可以啦。
小明：是的，但我是按照你给的SQL复制粘贴，对于SQL的含义并不是很清楚。
老王：SQL(发音为字母S-Q-L或sequel)的英文全称为Structured Query Language，SQL用来和数据库打交道，完成和数据库的通信。SQL是一套标准，但是每一个数据库都有自己的特性，当使用与这个数据库特性相关的功能，这时SQL语句可能就不是标准了，但是百分之九十以上的SQL都是通用的。SQL语句分为DQL、DML、DDL、DCL和TCL五大类。

DDL
数据定义语言（Data Definition Language，简称DDL）：DDL用于定义数据库的结构，包括创建、修改和删除数据库、表、索引、视图等元素。常用的DDL语句包括CREATE、ALTER和DROP。
DML
数据操作语言（Data Manipulation Language，简称DML）：DML用于对数据库中的数据进行操作，包括插入、更新和删除数据。常用的DML语句包括SELECT、INSERT、UPDATE和DELETE。
DCL
数据控制语言（Data Control Language，简称DCL）：DCL用于控制数据库中数据的访问权限和数据完整性的约束，包括授权和回收权限、创建用户和角色等。常用的DCL语句包括GRANT和REVOKE。
TCL
事务控制语言（Transaction Control Language，简称TCL）：TCL用于控制事务的提交和回滚，保证数据库的一致性和完整性。常用的TCL语句包括COMMIT和ROLLBACK。
DQL
数据查询语言（Data Query Language，简称DQL）：DQL用于从数据库中获取数据，进行数据的查询和检索。常用的DQL语句是SELECT。

小明：明白了！DDL主要是用来定义表的结构，比如创建、修改和删除数据库对象，而DML则是用来处理数据库中的实际数据的操作语言。那接下来，我是不是就可以建库、建表、写数据了？
老王：俗话说，磨刀不误砍柴工！学习建表之前，还需要掌握数据类型，MySQL具有丰富的数据类型，这些不需要你都记住，但是都要理解，在建表的时候选择合适的数据类型。
RDS for MySQL的数据类型
老王：数据类型是用来定义数据库中存储数据的格式和属性的规范。不同的数据类型决定了数据在数据库中的存储方式、占用空间以及可以进行的操作。在MySQL中，常见的数据类型包括数值型、字符串型、二进制型、日期型和其他类型。
小明：明白了！数据类型是用来定义数据的格式和属性，比如整数、小数、字符串等。

数值型
MySQL支持多种数值型数据类型，包括整数型、小数型和布尔型。
整数型
TINYINT：1字节，取值范围为-128到127或0到255。
SMALLINT：2字节，取值范围为-32768到32767或0到65535。
MEDIUMINT：3字节，取值范围为-8388608到8388607或0到16777215。
INT：4字节，取值范围为-2147483648到2147483647或0到4294967295。
BIGINT：8字节，取值范围为-9223372036854775808到9223372036854775807或0到18446744073709551615。
小数型
FLOAT：4字节，单精度浮点数，精度为7位。
DOUBLE：8字节，双精度浮点数，精度为15位。
DECIMAL：可变长度，用于存储精确的小数，精度和数据长度可以自定义。
布尔型
MySQL支持布尔型数据类型，即BOOLEAN或BOOL。它只占用1位，取值为TRUE或FALSE。
日期型
DATE：用于存储日期，格式为YYYY-MM-DD。
TIME：用于存储时间，格式为HH:MM:SS。
DATETIME：用于存储日期时间，格式为YYYY-MM-DD HH:MM:SS。
TIMESTAMP：用于存储时间戳，格式为YYYY-MM-DD HH:MM:SS。
YEAR：用于存储年份，格式为YYYY。
字符串型
CHAR：用于存储固定长度的字符串，长度为0到255个字符。
VARCHAR：用于存储可变长度的字符串，长度为0到65535个字符。
TEXT：用于存储大量的文本数据，最大长度为65535个字符。
BLOB：用于存储大量的二进制数据，最大长度为65535个字节。
ENUM：用于存储枚举类型的数据，如性别、状态等。
二进制型
BIT：用于存储比特位，长度为1到64个比特位。
BINARY：用于存储固定长度的二进制数据，长度为0到255个字节。
VARBINARY：用于存储可变长度的二进制数据，长度为0到65535个字节。
其他类型
SET：用于存储一组可选值，最多可以有64个选项。
JSON：用于存储JSON格式的数据。
Geometry：用于存储几何类型的数据，如点、线、面等。
老王：MySQL支持多种数据类型，可以根据不同的需求选择合适的数据类型。掌握MySQL的数据类型，对于开发人员来说非常重要，可以提高开发效率，减少错误。
小明：我大概理解了每种类型的使用场景，但是很多都没记住，尤其是取值范围。
老王：学习SQL的最佳方式就是练习，接下来开启建库、建表、读写数据吧！

课时3： RDS for MySQL的DDL、DML和DQL

CREATE DATABASE语句
老王：咱们商城不是要上一批书籍吗？创建一个goods库来存这些书籍信息吧！

CREATE DATABASE IF NOT EXISTS `goods` CHARACTER SET utf8 COLLATE utf8_general_ci;

● 当创建的数据库本身存在而且没有写明IF NOT EXISTS子句时，则创建数据库的语句会报错。
● create_option子句指明创建的数据库的属性。
○ CHARACTER SET属性指明此数据库的默认字符集。
○ COLLATE属性指明此数据库的默认排序规则。
○ 在数据文件所在目录创建一个自己的文件目录，用来包含后续创建的表文件。
ALTER DATABASE语句
小明：老王，库我已创建好了，我听说utf8不支持表情符号？
老王：那你可以使用ALTER DATABASE语句来修改库的字符集为utf8mb4。

ALTER DATABASE `goods` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

DROP DATABASE语句
小明：老王，那如果库名创建错了或者业务变更，需要修改库名怎么操作呢？
老王：目前MySQL不支持通过SQL修改库名，需要DROP DATABASE语句把老库删掉重建。
DROP DATABASE IF EXISTS goods;
注意：DROP DATABASE 删掉库，库中的表也会删掉！若库中有表且有用，则需先建新库把表移过去，再删老库。
老王：库的操作你已经掌握了，接下来开始对表的操作吧！
CREATE TABLE语句
老王：在MySQL中，使用CREATE TABLE语句来创建表。你需要指定表名和列的定义，包括列名、数据类型以及约束等，结合实际存储和上一课学习的数据类型选取合适的。创建一个book_types表：

CREATE TABLE IF NOT EXISTS `book_types` (
  `type_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '书籍类型ID',
  `book_type` varchar(32) NOT NULL DEFAULT '' COMMENT '书籍类型',
  PRIMARY KEY (`type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

● book_types: 表示被创建的表名，默认在当前数据库下创建此表。
● IF NOT EXISTS: 表示当相同的表名存在时，则不执行此创建语句，避免语句执行错误。
● NOT NULL：表示需要对此字段明确数值，或者要有默认值，否则报错。
● DEFAULT: 表示设置字段的默认值。
● AUTO_INCREMENT：表示该字段为自增，默认是从1开始，一个表中只容许有一个，且该字段必须有key属性，不能含有DEFAULT属性，且插入负值会被当成很大的正数。
● PRIMARY KEY：表示该字段为主键，主键字段必须唯一，必须非空，一个表中只能有一个主键，主键可以包含一个或多个字段。
小明：书籍种类表我已经创建好了，书籍信息怎么存呢？
老王：创建一张名为book的表来存储书籍信息

CREATE TABLE IF NOT EXISTS `book` (
  `book_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '书籍ID',
  `name` varchar(32) NOT NULL DEFAULT '' COMMENT '书籍名称',
  `price` decimal(10,2) unsigned zerofill NOT NULL COMMENT '单价',
  `author` varchar(16) NOT NULL DEFAULT '' COMMENT '作家名称',
  `publishing_house` varchar(32) NOT NULL DEFAULT '' COMMENT '出版社',
  `type_id` int unsigned NOT NULL COMMENT '书籍类型ID',
  PRIMARY KEY (`book_id`),
  KEY `fk_typeid` (`type_id`),
  CONSTRAINT `fk_typeid` FOREIGN KEY (`type_id`) REFERENCES `book_types` (`type_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='书籍信息';

● CONSTRAINT：表示为主键、唯一键、外键等约束条件命名。
● KEY：表示索引字段。
● FOREIGN KEY：表示该字段为外键字段。
ALTER TABLE语句
小明：我还需要存出版日期，但是book表没有这个字段信息。
老王：这个时候就需要用到ALTER TABLE语句来增加字段了。
ALTER TABLE book ADD COLUMN publishing_date date NOT NULL COMMENT ‘出版日期’ AFTER publishing_house;
● publishing_date：增加的字段名字，后面跟字段属性。
● AFTER：在某个字段后面增加。
老王：你还可以使用DROP TABLE语句把表删了，重新创建。
DROP TABLE语句
老王：假如表里有数据，则数据也就被删了，所以使用该语句需要谨慎操作。

DROP TABLE IF EXISTS `book`;

RENAME TABLE语句
小明：我想把表名改成books，是不是也需要把表删掉重建呢？
老王：用RENAME TABLE语句就可以，很方便。
RENAME TABLE book TO books;
老王：表的操作你已经掌握了，接下来开始增、删、改、查数据吧！
INSERT语句
老王：把咱们新上的那批书籍插入books吧！

---

– 书籍类型数据

---

INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (1, '计算机');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (2, '历史');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (3, '小说');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (4, '科幻');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (5, '随笔');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (6, '心理学');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (7, '科学');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (8, '传记');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (9, '励志');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (10, '悬疑');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (11, '哲学');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (12, '语言学');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (13, '人工智能');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (14, '数据分析');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (15, '数据挖掘');

---

– 书籍信息数据

---

INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (1, 'Python编程从入门到精通', 00000049.90, '张三', '图灵出版社', '2022-01-15', 1);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (2, '数据结构与算法分析', 00000035.00, '李四', '清华大学出版社', '2021-09-30', 1);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (3, '人类简史', 00000025.50, '尤瓦尔·赫拉利', '中信出版社', '2020-03-12', 2);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (4, '活着', 00000018.00, '余华', '作家出版社', '2019-05-08', 3);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (5, '白夜行', 00000022.50, '东野圭吾', '南海出版公司', '2021-11-20', 3);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (6, 'Java编程思想', 00000059.00, 'Bruce Eckel', '机械工业出版社', '2020-07-10', 1);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (7, '三体', 00000028.80, '刘慈欣', '重庆出版社', '2018-12-18', 4);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (8, '围城', 00000015.50, '钱钟书', '人民文学出版社', '2017-10-22', 3);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (9, 'JavaScript权威指南', 00000042.50, 'David Flanagan', '电子工业出版社', '2022-02-28', 1);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (10, '活法', 00000019.00, '梁文道', '江苏文艺出版社', '2019-09-10', 5);

● VALUES后括号内为插入数据。
● books表名后括号内为字段名称，两个小括号一一对应。
UPDATE语句
小明：老王，刘慈欣的《三体》出版日期需要修改一下，怎么办？
老王：用UPDATE语句即可，通过WHERE来修改符合条件的数据。

UPDATE `books` SET `publishing_date`='2016-12-18' WHERE `name`='三体' AND `author`='刘慈欣';

注意：如果不加WHERE条件，那表中所有书的出版日期都会改为2016-12-18。
DELETE语句
小明：老王，梁文道的《活法》需要下架，怎么办？
老王：用DELETE语句即可，通过WHERE来删除符合条件的数据。

DELETE FROM `books` WHERE `name`='活法' AND `author`='梁文道';

注意：如果不加WHERE条件，那表中的所有数据将被一行一行地删除。
老王：小明啊，数据的增、删、改你已经掌握了，接下来你可以根据需求去查询数据了！
SELECT语句
老王：先用一个最简单的查询语句查看书籍信息。

SELECT * FROM `books`;

老王：但是这个语句会将表中所有数据查出来，如果表中数据很多，将耗费大量资源甚至引发事故因此我们一般会用LIMIT子句加以限制。

SELECT * FROM `books` LIMIT 10;

说明： LIMIT子句后可以带两位>0的整数，第一位代表offset，第二位代表取多少行；也可以是一位>0的整数，代表取多少行。
小明：我只需要查看书名，是不是用书名的字段name像下面这样就可以了？

SELECT `name` FROM `books` LIMIT 10;

老王：是的，为了方便查看，你还可以用AS取个别名，如下：
SELECT name AS ‘书名’ FROM books LIMIT 10;
小明：那如果我只想看刘慈欣的小说是不是就可以像前面学的加WHERE条件，就可以了呢？

SELECT `name` AS '书名' 
FROM `books` 
  WHERE `author`='刘慈欣'
LIMIT 10;

老王：小王你很棒啊！都学会举一反三了，真是厉害了。刚业务同学说，需要看看每类型书的最高价格，可以用下面这个语句：

SELECT `type_id`, MAX(`price`) 
FROM `books` 
GROUP BY `type_id`
LIMIT 10;

说明：GROUP BY子句代表分组，通常和聚合函数：最大值MAX、最小值MIN、平均值AVG、个数COUNT、求和SUM 配合使用。
小明：那如果要看最高价格高于25元以上类型的书籍是不是就可以用WHERE子句呢？
老王：在MySQL中，分组求最大值，再根据最大值过滤符合条件的就需要用HAVING子句，你刚才的需求用如下语句实现：

SELECT `type_id`, MAX(`price`) 
FROM `books` 
GROUP BY `type_id`
  HAVING MAX(`price`) > 25
LIMIT 10;

小明：业务方需要在此基础上根据每种书最高价格，再由高到低排个序。
老王：排序就要用ORDER BY子句了，如下：

SELECT `type_id`, MAX(`price`) 
FROM `books` 
GROUP BY `type_id`
  HAVING MAX(`price`) > 25
ORDER BY MAX(`price`) DESC
LIMIT 10;

说明：默认是升序排列(ASC)，指定DESC按照降序排列。
小明：老王，有个问题哈，我们查出来的类型显示都是数字，我还得去看表结构才知道是什么类型。
老王：咱们的书籍类型放在book_types表里面的，需要直接看到类型名称，就需要用到连表查询。

SELECT `bt`.`book_type`,MAX( `b`.`price` ) 
FROM `books` AS `b`
  LEFT JOIN `book_types` AS `bt` ON `b`.`type_id` = `bt`.`type_id` 
GROUP BY `b`.`type_id` 
HAVING MAX( `b`.`price` ) > 25 
ORDER BY MAX( `b`.`price` ) DESC 
LIMIT 10;

● 了解表之间的关系： 在执行连表查询之前，你需要了解表之间的关系，包括哪些列用作关联键。通常，表之间的关系由外键（foreign key）来定义，它们用于将一张表中的数据与另一张表中的数据关联起来。
● 选择适当的连接类型： 连表查询可以使用不同的连接类型，包括内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）和全连接（FULL JOIN）。你需要根据查询需求选择适当的连接类型，以确保获取正确的数据。
● 注意性能： 连表查询可能会影响性能，特别是当涉及大量数据时。确保表上有适当的索引可以加速查询，同时避免不必要的连接和复杂的查询条件。
● 使用别名： 当涉及多个表时，使用表的别名（alias）可以使查询更可读。别名是对表的简短引用，使查询语句更加清晰。
小明：这连表查询语句看起来很简洁，但我不是很理解。
老王：通常子查询的方式可以简化复杂JOIN表连接查询，提高了语句的可读性。

SELECT `book_type`, MAX(`price`)
FROM `books`
WHERE `type_id` IN (
    SELECT `type_id`
    FROM `books`
    WHERE `price` > 25
)
GROUP BY `book_type`
ORDER BY MAX(`price`) DESC
LIMIT 10;

小明：子查询看起来容易理解多了，那我是不是以后直接用子查询就可以了呢？
老王：子查询在这种简单查询场景下可读性确实好，但是随着表关系复杂度增加，多层子查询嵌套可读性就会下降，而且在处理大数据量时，性能可能开销会更大。连表查询和子查询各有其用武之地，你应该根据具体的查询需求来选择合适的方法。在实际应用中，通常需要权衡查询的性能、可读性和灵活性，并选择最合适的查询方式。

/*库操作*/
/*创建一个goods库存放书籍信息(CREATE DATABASE语句)*/
CREATE DATABASE IF NOT EXISTS goods CHARACTER SET utf8 COLLATE utf8_general_ci;

/*修改数据库默认字符集为utf8mb4（ALTER DATABASE语句）*/
ALTER DATABASE goods CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

/*无法修改数据库名，MySQL不支持通过SQL修改库名，需要DROP DATABASES把老库删掉重建*/
DROP DATABASE IF EXISTS goods;

/*表操作,创建一个book_types表*/
CREATE TABLE IF NOT EXISTS book_types(
type_id int unsigned NOT NULL AUTO_INCREMENT COMMENT '书籍类型ID',
book_type varchar(32) NOT NULL DEFAULT '' COMMENT '书籍类型',
PRIMARY KEY(type_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*创建一个书籍信息表*/
CREATE TABLE IF NOT EXISTS book(
book_id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '书籍ID',
name varchar(32) NOT NULL DEFAULT '' COMMENT '书籍名称',
`price` decimal(10,2) unsigned zerofill NOT NULL COMMENT '单价',
`author` varchar(16) NOT NULL DEFAULT '' COMMENT '作家名称',
`publishing_house` varchar(32) NOT NULL DEFAULT '' COMMENT '出版社',
`type_id` int unsigned NOT NULL COMMENT '书籍类型ID',
PRIMARY KEY (`book_id`),
KEY `fk_typeid` (`type_id`),
CONSTRAINT `fk_typeid` FOREIGN KEY(type_id) REFERENCES book_types(type_id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='书籍信息';

/*book中增加列*/
ALTER TABLE book
ADD publishing_date date NOT NULL COMMENT '出版日期' AFTER publishing_house;

/*删掉表，表中的数据也会被删掉*/
DROP TABLE IF EXISTS book;

/*重命名表*/
RENAME TABLE book
to books;


/*表中插入数据*/
-- ----------------------------
-- 书籍类型数据
-- ----------------------------
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (1, '计算机');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (2, '历史');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (3, '小说');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (4, '科幻');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (5, '随笔');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (6, '心理学');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (7, '科学');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (8, '传记');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (9, '励志');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (10, '悬疑');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (11, '哲学');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (12, '语言学');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (13, '人工智能');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (14, '数据分析');
INSERT INTO `book_types` (`type_id`, `book_type`) VALUES (15, '数据挖掘');


-- ----------------------------
-- 书籍信息数据
-- ----------------------------
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (1, 'Python编程从入门到精通', 00000049.90, '张三', '图灵出版社', '2022-01-15', 1);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (2, '数据结构与算法分析', 00000035.00, '李四', '清华大学出版社', '2021-09-30', 1);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (3, '人类简史', 00000025.50, '尤瓦尔·赫拉利', '中信出版社', '2020-03-12', 2);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (4, '活着', 00000018.00, '余华', '作家出版社', '2019-05-08', 3);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (5, '白夜行', 00000022.50, '东野圭吾', '南海出版公司', '2021-11-20', 3);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (6, 'Java编程思想', 00000059.00, 'Bruce Eckel', '机械工业出版社', '2020-07-10', 1);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (7, '三体', 00000028.80, '刘慈欣', '重庆出版社', '2018-12-18', 4);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (8, '围城', 00000015.50, '钱钟书', '人民文学出版社', '2017-10-22', 3);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (9, 'JavaScript权威指南', 00000042.50, 'David Flanagan', '电子工业出版社', '2022-02-28', 1);
INSERT INTO `books` (`book_id`, `name`, `price`, `author`, `publishing_house`, `publishing_date`, `type_id`) VALUES (10, '活法', 00000019.00, '梁文道', '江苏文艺出版社', '2019-09-10', 5);

/*修改表结构*/
UPDATE books 
SET publishing_date='2016-12-18' WHERE name='三体' AND author='刘慈欣';

/*查询最高价格高于25元以上类型的书籍*/
SELECT type_id, MAX(price) AS m 
from books 
GROUP BY type_id
HAVING m>25;

/*查询最高价格高于25元以上类型的书籍类型名称*/
SELECT bk.type_id, bt.book_type, MAX(price) AS m 
from books bk LEFT OUTER JOIN book_types bt
ON bk.type_id=bt.type_id
GROUP BY type_id
HAVING m>25;

第三章 RDS for MySQL的SQL高级特性

课时4 RDS for MySQL的视图

案例背景
随着公司的发展壮大，业务要求小明将数据转化为有意义的信息，帮助企业作出更明智的决策，优化业务流程。掌握MySQL的视图、存储过程、触发器等SQL高级特性可以使程序员更好地管理更高效处理数据，提高安全性、可维护性和自动化。
RDS for MySQL的视图
小明：有啥方法可以不需要关心后面对应的表的结构、关联条件和筛选条件，查询已经过滤好的复合条件的结果集吗？
老王：这时就需要用到视图了，它不光能简化查询还能允许查询某些数据提高了安全性；前面我们写的每种数最高价格且大于25并排序的就可以改写成下面这样：

CREATE VIEW `v_booktype_maxprice`
AS 
SELECT `bt`.`book_type`,MAX( `b`.`price` ) 
FROM `books` AS `b`
  LEFT JOIN `book_types` AS `bt` ON `b`.`type_id` = `bt`.`type_id` 
GROUP BY `b`.`type_id` 
HAVING MAX( `b`.`price` ) > 25 
ORDER BY MAX( `b`.`price` ) DESC 
LIMIT 10;

● 当视图被创建之后，则其定义就已经固定不会再改变，比如一个视图是由select *创建的，则后续对表增加的字段不会成为视图的一部分，而后续对表删除字段则会导致查询视图失败。
● 创建的视图默认情况下是属于当前数据库的，当要创建到另外的数据库时则需要在视图名前面加上数据库名。
● 一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列名对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
小明：那我以后就再也不用写那么一堆SQL，就写如下SQL就可以了吗？

SELECT * FROM `v_booktype_maxprice`;

老王：是的。如果要查22元以上的，还能用语句ALTER VIEW用来修改视图的定义，如下：

ALTER VIEW `v_booktype_maxprice`
AS 
SELECT `bt`.`book_type`,MAX( `b`.`price` ) 
FROM `books` AS `b`
  LEFT JOIN `book_types` AS `bt` ON `b`.`type_id` = `bt`.`type_id` 
GROUP BY `b`.`type_id` 
HAVING MAX( `b`.`price` ) > 22 
ORDER BY MAX( `b`.`price` ) DESC 
LIMIT 10;

小明：当我新接手一个库，我怎么查看库里的视图呢？
老王：你可以用下面SQL查询goods库里有哪些视图。

SELECT `TABLE_NAME` 
FROM `information_schema`.`VIEWS` 
WHERE `TABLE_SCHEMA`='goods';

老王：再根据下面的SQL查看视图详情。

SHOW CREATE VIEW `goods`.`v_booktype_maxprice`;

小明：那如果这个视图不用了，是不是可以用下面SQL删除呢？

DROP VIEW IF EXISTS `goods`.`v_booktype_maxprice`;

老王：是的，你棒棒哒！接下来我们学习触发器，处理某个表中的数据发生变化时需要自动执行一组SQL的场景。

课时5 RDS for MySQL数据库触发器介绍

小明：当客户购买书时，书的库存量会根据订单购买的数量的变化而变化，有啥好的实现方法吗？
老王：如果某个表中的数据发生变化时需要自动执行一组SQL，可以为该表创建一个触发器。我们先给books表增加一个库存列，再创建订单表和用户表并插入数据。

SET NAMES utf8;
-- ----------------------------
-- 给books表增加一个库存列
-- ----------------------------
ALTER TABLE `books` ADD COLUMN `stock` BIGINT UNSIGNED  NOT NULL DEFAULT 1000 COMMENT '库存量' AFTER `price`;

SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- 创建用户表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `user_name` varchar(64) NOT NULL DEFAULT '' COMMENT '用户名称',
  `register_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '用户注册时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- 创建订单表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `orders` (
  `order_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `user_id` bigint unsigned NOT NULL COMMENT '用户ID',
  `order_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单时间',
  PRIMARY KEY (`order_id`),
  KEY `fk_user_id_idx` (`user_id`),
  CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- 创建订单详情表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `order_items` (
  `order_item_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '订单项目ID',
  `order_id` bigint unsigned NOT NULL COMMENT '订单ID',
  `book_id` bigint unsigned NOT NULL COMMENT '书籍ID',
  `quantity` int unsigned NOT NULL COMMENT '商品数量',
  PRIMARY KEY (`order_item_id`),
  KEY `fk_order_id` (`order_id`),
  KEY `fk_book_id` (`book_id`),
  CONSTRAINT `fk_book_id` FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS = 1;


-- ----------------------------
-- 注册新用户
-- ----------------------------
INSERT INTO `users` (`user_name`)
VALUES('Alice'),('Bob'),('Charlie'),('David'),('Eve');

说明：因为有外键约束建表有依赖关系，因此我们用SET FOREIGN_KEY_CHECKS=0关闭外键检查，建好表之后再打开即可。
老王：一个触发器会监听一张表，当对该表进行INSERT、UPDATE或DELETE操作时，则触发执行一组SQL。触发器创建语法四要素：监视地点(table)、监视事件(INSERT/UPDATE/DELETE)、触发时间(BEFORE/AFTER)和触发事件(INSERT/UPDATE/DELETE)。用户对订单有增加 、删除和修改动作，因此创建三个触发器。

-- ----------------------------
-- 用户下单
-- ----------------------------
CREATE TRIGGER tg_insert_o
AFTER INSERT
ON order_items FOR EACH ROW
UPDATE `books` SET `stock`=`stock`-new.`quantity` WHERE `book_id`=new.`book_id`;

-- ----------------------------
-- 用户修改订单
-- ----------------------------
CREATE TRIGGER tg_update_o
AFTER UPDATE
ON order_items FOR EACH ROW
UPDATE `books` SET `stock`=old.`quantity`-new.`quantity`+`stock` WHERE `book_id`=old.`book_id`;

-- ----------------------------
-- 用户退订单
-- ----------------------------
CREATE TRIGGER tg_delete_o
AFTER DELETE
ON order_items FOR EACH ROW
UPDATE `books` SET `stock`=`stock`+old.`quantity` WHERE `book_id`=old.`book_id`;

● BEFORE和AFTER指定触发器在表中的每行数据修改前或者后执行。
● old.col_quantity表示行数据被修改或删除之前的字段数据，new.quantity表示行数据被插入或修改之后的字段数据。变化量 = 修改前的量(old.quantity) – 修改后的量(new.quantity)
● 当该表删除时，触发器也自动删除了。
老王：假如Bob下单购买了1本《人类简史》、8本《三体》和2本《围城》，需要先查出对应的ID再插入。

INSERT INTO `orders`(`user_id`) VALUE(2);
INSERT INTO `order_items`(`order_id`,`book_id`,`quantity`)  VALUES(1,3,1),(1,7,8),(1,8,2);

小明：我看books表中的《人类简史》、《三体》和《围城》的库存各少了1、8和2本。
老王：当用户修改订单《三体》只买5本时，执行如下SQL：

UPDATE `order_items` SET `quantity`=5 WHERE `order_item_id`=2;

小明：我看books表中的《三体》库存多了3本，符合预期。
老王：当用户决定不买《围城》了，在订单中把该书删掉：

DELETE FROM `order_items` WHERE `order_item_id`=3;

小明：books表《围城》库存多了2本，符合预期。当我新接手一个库，我怎么查看库里的触发器呢？
老王：你可以用下面SQL查询goods库里有哪些触发器。

select trigger_name from information_schema.triggers where event_object_schema='goods';

老王：再根据下面的SQL查看触发器详情。

show create trigger tg_update_o;

小明：那如果这个触发器不用了，是不是可以用下面SQL删除呢？

DROP TRIGGER IF EXISTS `goods`.`tg_delete_o`;

老王：是的，你棒棒哒！接下来我们学习存储过程，处理需要执行一组SQL，且有一定的重复性的工作。

课时6 RDS for MySQL的存储过程与事件

小明：老王，业务那边需要每天早上9点统计昨天用户增长量，我需要先算截止到前天用户数和昨天的用户总数相减，这样一组SQL集合是不是就可以用存储过程？

老王：是的，接下来我们写一个存储过程，实现这个需求。

DELIMITER //
CREATE PROCEDURE sp_user_growth()
BEGIN
  DECLARE today DATE;
  DECLARE yesterday DATE;
  DECLARE usercount_yesterday INT UNSIGNED DEFAULT 0;
  DECLARE usercount_yesterdaybefore INT UNSIGNED DEFAULT 0;
  
  SELECT CURDATE() INTO today;
  SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) INTO yesterday;
  
  SELECT COUNT(*) FROM `users` WHERE register_time<today INTO usercount_yesterday;  -- 昨天用户总数
  SELECT COUNT(*) FROM `users` WHERE register_time<yesterday INTO usercount_yesterdaybefore;  -- 前天用户总数
  
  SELECT usercount_yesterday - usercount_yesterdaybefore AS '昨天用户新增';
END //  

DELIMITER ;

● MySQL结束符默认是分号，此时普通SQL与存储过程的结束符就会混淆导致语句无法执行，因此用DELIMITER改变语句的结束符。
● 存储过程BEGIN…END其中可以包含一个或多个语句，每个语句用 ; 隔开。
● DECLARE语句通常用来声明变量；声明后的变量可以通过SELECT … INTO进行赋值。
老王：创建好存储过程后，以后只要通过CALL命令来调用存储过程即可得到结果。

CALL sp_user_growth();

小明：好的老王，我学会了。那如果这个存储过程不用了，是不是可以用下面SQL删除呢？

DROP PROCEDURE IF EXISTS sp_user_growth

老王：是的，业务不是说每天早上9点出结果吗？

DELIMITER //
CREATE EVENT callSpusergrowthJob 
ON SCHEDULE EVERY 1 DAY
STARTS DATE_ADD(CURDATE(), INTERVAL 9 HOUR)
ON COMPLETION PRESERVE
COMMENT '每天早上9点调用统计昨日用户增长存储过程'
DO BEGIN
  CALL sp_user_growth();
END //
DELIMITER ;

● ON SCHEDULE：计划任务。
● COMMENT：可选项用来描述event；相当注释，最大长度64个字节。
● ON COMPLETION PRESERVE： 可选项，默认是ON COMPLETION NOT PRESERVE 即计划任务执行完毕后自动drop该事件；ON COMPLETION PRESERVE则不会drop掉。
老王：通过下面这个命令可以查看当前所在库的事件。

SHOW EVENTS\G

老王：要想使Events生效，还需要开启事件调度器：

SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler = ON;

说明：使用SET GLOBAL的方式设置参数需要客户端重连mysql服务，并且在mysql服务重启后失效，要想在mysql服务重启后永久生效需要修改mysql配置文件。
小明：如果这个事件不用了，是不是可以用下面SQL删除呢？

DROP EVENT IF EXISTS callSpusergrowthJob;

老王：是的小明。存储过程虽然很方便、安全性也还不错，但是MySQL的存储过程缺乏debug功能，维护起来成本很高，因此在后端开发中，能用程序解决的尽量不要用存储过程。接下来我带你用python语言来实现这个需求。

第4章 RDS for MySQL数据库应用开发

课时7 使用python对MySQL进行数据分析

老王：首先，需要安装连接MySQL的Python模块：
pip install pymysql
老王：安装完成以后，我们需要在服务器上创建一个Python文件，用于编写代码内容，代码里面的内容包括：连接MySQL，创建游标，通过游标执行SQL。

小明：有没有代码可以提供参考呢？
老王：当然有了，以下的内容就是Python脚本的代码内容，可进行参考。

#!/usr/bin/python
import pymysql


# 1、打开数据库连接
db = pymysql.connect(host='localhost',user='root',password='test123',database='goods')

# 2、使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
 
# 3、使用 execute()  方法执行 SQL  
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()

# 打印结果
print(f"Database version : {data}")

# 4、关闭数据库连接
db.close()

老王：是不是还是没有理解，下面我们来详细学习一下Python操作数据库的步骤。

Python连接MySQL数据库
我们借助Python的pymysql模块连接MSQL数据库，pymysql是一个用于Python编程的第三方模块，用于连接和操作MySQL数据库。它提供了一个简单而强大的接口，使开发者能够轻松地在Python程序中执行各种数据库操作，如查询、插入、更新和删除数据等。
导入模块

导入模块

import pymysql

打开数据库连接
#打开数据库连接
#注意：这里已经假定存在数据库aliyun，db指定了连接的数据库，当然这个参数也可以没有
#打开数据库连接
#注意：这里已经假定存在数据库aliyun，db指定了连接的数据库，当然这个参数也可以没有

db = pymysql.connect(
host='127.0.0.1',  # mysql服务器地址
port=3306,         # mysql服务器端口号
user='root',       # 用户名
passwd='aliyun',     # 密码
db='aliyun',       # 数据库名称
charset='utf8')    # 连接编码，存在中文的时候，连接需要添加charset='utf8'，否则中文显示乱码

创建游标对象cursor
#使用cursor方法创建一个游标

cursor = db.cursor()

数据库基本操作
使用execute()方法来实现对数据库的基本操作。
查询数据库版本
#查询数据库版本

cursor.execute("select version()")
data = cursor.fetchone()
print(f"Database version : {data}")

创建数据库
#创建数据库aliyun

cursor.execute("drop database if exists aliyun")  #如果数据库已经存在，那么删除后重新创建
sql = "create database aliyun"
cursor.execute(sql)

创建数据表
#创建数据库表

cursor.execute("drop table if exists goods")  #如果数据表已经存在，那么删除后重新创建

sql = """
CREATE TABLE goods (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )
"""
cursor.execute(sql)

查询操作
#查询数据表数据

sql = "select * from goods"
cursor.execute(sql)
data = cursor.fetchone()
print(data)

插入操作
#插入数据

sql = "insert into goods values ('小','明',20,'W',5000)"
cursor.execute(sql)
db.commit()

#查看插入后的结果

sql = "select * from goods"
cursor.execute(sql)
data = cursor.fetchone()
print(data)

指定条件查询数据
#指定条件查询数据表数据

sql = " select * from goods where income > '%d' " % (1000)
cursor.execute(sql)
data = cursor.fetchone()
print(data)

更新操作
#更新数据库

sql = " update goods set age = age+1 where sex = '%c' " % ('W')
cursor.execute(sql)
db.commit()

#查看更新后的结果

sql = "select * from goods"
cursor.execute(sql)
data = cursor.fetchone()
print(data)

删除操作
#删除数据

sql = " delete from goods where age > '%d' " % (30)
cursor.execute(sql)
db.commit()

#查看更新后的结果

sql = "select * from goods"
cursor.execute(sql)
data = cursor.fetchone()
print(data)

关闭数据库连接
db.close()
小明：这些真的太详细了，我要赶紧自己写个Python脚本去练习一下，有没有实际的生产案例可以练习？
老王：那我们就用Python实现统计昨天用户增长量，脚本内容如下：

#!/usr/bin/python3
import pymysql
from datetime import date
from dateutil.relativedelta import relativedelta

# 获取今天的日期和昨天的日期
today = date.today()
yesterday = today - relativedelta(days=1)

# 将日期格式化为字符串，适用于 MySQL
today_str = today.strftime('%Y-%m-%d')
yesterday_str = yesterday.strftime('%Y-%m-%d')

# 构建 SQL 语句
today_before_sql = f"SELECT COUNT(*) FROM users WHERE register_time < '{today_str}'"
yesterday_before_sql = f"SELECT COUNT(*) FROM users WHERE register_time < '{yesterday_str}'"

# 连接到数据库
db = pymysql.connect(host='localhost', port=3306, user='root', password='1234', db='goods', charset='utf8mb4')

try:
    # 创建游标并执行 SQL 语句
    cursor = db.cursor()
    cursor.execute(today_before_sql)
    today_before_data = cursor.fetchone()[0]  # 获取结果中的第一个元素（计数）

    cursor.execute(yesterday_before_sql)
    yesterday_before_data = cursor.fetchone()[0]  # 获取结果中的第一个元素（计数）

    # 计算并打印用户增长量
    growth = today_before_data - yesterday_before_data
    print("统计昨天用户增长量为: %d" % growth)

finally:
    # 关闭数据库连接
    db.close()

debug：

1. 连接mysql（使用127.0.0.1）不成功，修改为localhost

课程总结

通过本课程，我们学习了以下内容：

1. RDS for MySQL的SQL分类与数据类型：我们了解了DDL、DML和DQL等SQL语言的分类，并掌握了RDS for MySQL支持的常见数据类型及其用法。
2. RDS for MySQL的DDL、DML和DQL：我们深入了解了DDL、DML和DQL的具体用法和语法；DDL用于定义数据库的结构，DML用于操作数据库的数据，DQL用于查询数据库中的数据。
3. RDS for MySQL的视图：我们学习了视图的创建和使用，掌握了以视图为基础进行数据查询和处理的方法。
4. RDS for MySQL的存储过程：通过学习存储过程的概念和用法，我们能够创建复杂的数据库操作逻辑并通过存储过程进行调用。
5. RDS for MySQL的触发器：我们了解了触发器的作用和实现方式，学会了创建触发器并在特定的数据库操作发生时执行特定的动作。
6. RDS for MySQL的事件：通过事件的学习，我们知道如何设置定期执行的任务，编写自动化处理数据库的代码。
7. RDS for MySQL数据库应用编程语言：我们了解到使用编程语言Python与RDS for MySQL进行交互、执行SQL语句和处理结果等常用操作。

若想成为一名优秀的DBA，我们还需要学习MySQL的原理与优化。下一阶段，我们将继续学习：

1. RDS for MySQL的SQL执行流程：了解SQL在MySQL中的执行过程，包括查询优化、执行计划等关键要点。
2. RDS for MySQL的事务：掌握事务的概念和事务的四个特性（ACID），学会管理和利用事务来确保数据的一致性和完整性。
3. RDS for MySQL的锁：了解不同类型的锁（如行锁、表锁等），掌握锁在数据库并发操作中的应用和优化技巧。
4. RDS for MySQL的索引：学习如何创建和使用索引来优化数据查询，包括索引的类型、索引的选择和调优等。
5. RDS for MySQL的日志：了解RDS for MySQL的日志机制，包括binlog（二进制日志）、redo log（重做日志）和error log（错误日志），学会管理和使用这些日志来实现数据恢复和问题排查。
6. RDS for MySQL的SQL优化：学习如何分析和优化SQL语句，包括使用索引、避免全表扫描、优化查询逻辑等方法。
7. RDS for MySQL的参数介绍与优化：了解RDS for MySQL的各种配置参数，学习如何根据实际需求进行优化。
8. 架构优化之增加Redis缓存：了解如何加入缓存层，使用Redis等内存数据库来提高系统的读取性能和扩展性。