《大型数据库技术》MySQL的进阶开发技巧

末蓝、 2022-12-07 15:05 237阅读 0赞

### MySQL的进阶开发技巧 ###

*  1、MySQL的存储过程
 *   *  1.1 创建企业销售系统的数据库，命名为salesdb
     *  1.2 创建一张商品销售表，命名为salerecords，包括如下字段：商品ID，商品名称，销售单价，销售数量。
     *  1.3 创建一个存储过程，名称自订，通过输入商品ID，商品名称，销售单价，销售数量往表中插入一条记录。
     *  1.4 创建一个存储过程，名称自订，通过输入商品名称往表中插入100条记录。其中，所插入第一条记录的商品ID由现有表中商品ID的最大值+1构成，后续记录中商品ID依次递增1。所插入第一条记录的商品价格与现有表中商品价格的最小值相同，后续记录的商品价格依次递增1。商品销售数量随机生成。
     *  1.5 创建一个存储过程，名称自定，通过输入商品ID的最小值，最大值，将表中商品ID处于(最小值，最大值)范围内，且为偶数的记录删除，包括最小值与最大值本身。此处不考虑商品ID不存在的情况，在实验时请选择1.4中创建的商品ID范围的子集。
     *  1.6 创建两个事件调度器，第一个每3秒往1.2的表中插入一条记录，第二个每30秒清空1.2的表中的所有记录。
 *  2、MySQL的触发器
 *   *  定义一个触发器，实现如下功能，在往1.2的表中插入记录的时候，将记录同时也插入到一张新的表sale\_backup.
 *  3、MySQL的事务控制
 *   *  3.1 启动一个事务往1.2的表中插入任意三条记录，提交在第二条和第三条记录中定义一个savepoint，在插入完成后回滚到定义的savepoint。
     *  3.2 简述读锁和写锁的区别
 *  4、MySQL的分区
 *   *  4.1 分别使用range分区（自行决定区间）和hash分区创建对应与1.2中表的分区。
     *  4.2 插入100条记录后，确认分区中的记录分布情况。

> 实验一：[《大型数据库技术》MySQL数据库的开发基础][MySQL]  
> 实验二：[《大型数据库技术》MySQL数据库安装配置及基础使用][MySQL 1]  
> 实验三：[《大型数据库技术》MySQL的进阶开发技巧][MySQL 2]  
> 实验四：[《大型数据库技术》MySQL管理维护][MySQL 3]  
> 实验五：

# 1、MySQL的存储过程 #

## 1.1 创建企业销售系统的数据库，命名为salesdb ##

CREATE DATABASE salesdb;

![在这里插入图片描述][20200916123306428.png_pic_center]

## 1.2 创建一张商品销售表，命名为salerecords，包括如下字段：商品ID，商品名称，销售单价，销售数量。 ##

CREATE TABLE salerecords (
    	id INT(10) PRIMARY KEY AUTO_INCREMENT,
    	name VARCHAR(20),
    	price FLOAT(10),
    	number INT(10)
    );

![在这里插入图片描述][2020091612371967.png_pic_center]

## 1.3 创建一个存储过程，名称自订，通过输入商品ID，商品名称，销售单价，销售数量往表中插入一条记录。 ##

CREATE PROCEDURE pr_add(a INT(10), b VARCHAR(20), c FLOAT(10), d INT(10))
    INSERT INTO salerecords VALUES(a, b, c, d);

CALL pr_add(1, "雷蛇哆啦A梦50周年限定版机械键盘", 499, 80);
    CALL pr_add(2, "雷蛇哆啦A梦50周年限定版鼠标", 279, 70);
    CALL pr_add(3, "雷蛇哆啦A梦50周年限定版耳机", 349, 60);

## 1.4 创建一个存储过程，名称自订，通过输入商品名称往表中插入100条记录。其中，所插入第一条记录的商品ID由现有表中商品ID的最大值+1构成，后续记录中商品ID依次递增1。所插入第一条记录的商品价格与现有表中商品价格的最小值相同，后续记录的商品价格依次递增1。商品销售数量随机生成。 ##

delimiter $$ # 修改分隔符为 $$, 因为 ; 会影响命令中语句的执行

CREATE PROCEDURE add100(name VARCHAR(20))
    BEGIN
    DECLARE a INT; # 商品ID
    DECLARE b DOUBLE; # 商品价格
    DECLARE c INT; # 商品销售数量
    DECLARE n INT; # 循环变量
    #################################################
    SELECT MAX(id) INTO a FROM salerecords; # ID最大值
    SET a = a + 1; # 第一个商品ID为ID最大值 + 1
    #################################################
    SELECT MIN(price) INTO b FROM salerecords; # 第一个商品价格为价格最小值
    #################################################
    SET n = 0; # 循环100次
    WHILE n < 100 DO
    	SET c = rand(); # 商品销售数量随机
    	INSERT INTO salerecords VALUES(a, name, b, c);
    	SET a = a + 1; # 后续商品ID+1
    	SET b = b + 1; # 后续商品价格+1
    	SET n = n + 1;
    END WHILE;
    END
    $$

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70_pic_center]

delimiter ; # 将分隔符修改回 ;

调用存储过程：

CALL add100("C++ Primer Plus");

![在这里插入图片描述][2020091613125723.png_pic_center]  
表中前10条查询结果：

SELECT * FROM salerecords limit 0, 10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_16_color_FFFFFF_t_70_pic_center]

补充：删除存储过程：

ALTER PROCEDURE add100

## 1.5 创建一个存储过程，名称自定，通过输入商品ID的最小值，最大值，将表中商品ID处于(最小值，最大值)范围内，且为偶数的记录删除，包括最小值与最大值本身。此处不考虑商品ID不存在的情况，在实验时请选择1.4中创建的商品ID范围的子集。 ##

delimiter $$ # 修改分隔符为 $$, 因为 ; 会影响命令中语句的执行

CREATE PROCEDURE deleteEven(a int, b int)
    BEGIN
    	DELETE FROM salerecords 
    	WHERE MOD(id,2)=0 # 偶数
    	AND id BETWEEN a AND b;
    END
    $$

delimiter ; # 将分隔符修改回 ;

CALL deleteEven(1,10); # 删除前10条数据中的偶数数据

SELECT * FROM salerecords LIMIT 0,10; # 分页查询前10条数据

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_16_color_FFFFFF_t_70_pic_center 1]

## 1.6 创建两个事件调度器，第一个每3秒往1.2的表中插入一条记录，第二个每30秒清空1.2的表中的所有记录。 ##

注意事项：  
全局的 event 是关闭的：通过下列语句查看是否开启

SHOW variables LIKE 'event_scheduler';

![在这里插入图片描述][20201001094655510.png_pic_center]  
如未开启，通过下列语句开启：

SET GLOBAL event_scheduler = 1;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70_pic_center 1]

CREATE EVENT sale_insert
    ON SCHEDULE EVERY 3 SECOND
    DO INSERT INTO salerecords(name, price, number) VALUES (
    	'海洋之心', 888, 10
    );

CREATE EVENT sale_delete
    ON SCHEDULE EVERY 30 SECOND
    DO DELETE FROM salerecords;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_16_color_FFFFFF_t_70_pic_center 2]

每过3秒查询一下表中数据数量，可以看到确实发生了变化：

SELECT COUNT(*) FROM salerecords;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_16_color_FFFFFF_t_70_pic_center 3]

# 2、MySQL的触发器 #

## 定义一个触发器，实现如下功能，在往1.2的表中插入记录的时候，将记录同时也插入到一张新的表sale\_backup. ##

CREATE TABLE sale_backup(id INT,name VARCHAR(20),price INT,number INT);

![在这里插入图片描述][20201001095959180.png_pic_center]

CREATE TRIGGER tri AFTER INSERT
    ON salerecords FOR EACH ROW
    INSERT INTO sale_backup VALUES (new.id, new.name, new.price, new.number);

![在这里插入图片描述][20201001100010644.png_pic_center]

SELECT * FROM sale_backup LIMIT 0,10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70_pic_center 2]

# 3、MySQL的事务控制 #

## 3.1 启动一个事务往1.2的表中插入任意三条记录，提交在第二条和第三条记录中定义一个savepoint，在插入完成后回滚到定义的savepoint。 ##

注：做这个任务时要把前面定义的 EVENT 删除，顺便清空表，效果更明显；

DELETE EVENT sale_insert;
    DELETE EVENT sale_delete;
    DELETE FROM salerecords;

--------------------

START TRANSACTION; # 开启事务

INSERT INTO salerecords VALUES (1000, '考研数学', 40, 3); # 插入数据1
    INSERT INTO salerecords VALUES (1001, '考研英语', 35, 3); # 插入数据2

SAVEPOINT sp; # 保存点sp

INSERT INTO salerecords VALUES (1003, '考研政治', 30, 2); # 插入数据3

SELECT * FROM salerecords; # 此时应当有3个数据

ROLLBACK TO SAVEPOINT sp; # 回滚到保存点sp

SELECT * FROM salerecords; # 此时应当有2个数据

COMMIT; # 提交事务

SELECT * FROM salerecords; # 此时应当只有2个数据

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70_pic_center 3]

## 3.2 简述读锁和写锁的区别 ##

**共享锁(S锁)** 又称 **读锁**。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S 锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

**排他锁(X锁)** 又称 **写锁**。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

# 4、MySQL的分区 #

## 4.1 分别使用range分区（自行决定区间）和hash分区创建对应与1.2中表的分区。 ##

ALTER TABLE salerecords
    PARTITION BY RANGE(id) (
    	PARTITION p0 VALUES LESS THAN (100),
    	PARTITION p1 VALUES LESS THAN (200),
    	PARTITION p2 VALUES LESS THAN (2000)
    );

ALTER TABLE salerecords
    PARTITION BY HASH(id) partitions 4;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70_pic_center 4]

## 4.2 插入100条记录后，确认分区中的记录分布情况。 ##

INSERT INTO salerecords VALUES(1, '数据结构', 50, 100);
    
    CALL add100("Java程序设计语言");
    
    EXPLAIN partitions SELECT * FROM salerecords WHERE id = 4;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_16_color_FFFFFF_t_70_pic_center 4]

[MySQL]: https://blog.csdn.net/weixin_43734095/article/details/108616137
[MySQL 1]: https://blog.csdn.net/weixin_43734095/article/details/108614409
[MySQL 2]: https://blog.csdn.net/weixin_43734095/article/details/108619833
[MySQL 3]: https://blog.csdn.net/weixin_43734095/article/details/109221570
[20200916123306428.png_pic_center]: /images/20221123/34c73c5d831f432a9b7e1ecad9f01d74.png
[2020091612371967.png_pic_center]: /images/20221123/6090df2f482e4419b413ee02381e971b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70_pic_center]: /images/20221123/ee7889cea3cd48ff876e84b7e5233729.png
[2020091613125723.png_pic_center]: /images/20221123/1579b9cd2f374e85b29c54649c61bbac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/cb88b9c73243427c8eb6060f3dd93823.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/131d1248d0a84504aa7518bc755ddbf9.png
[20201001094655510.png_pic_center]: /images/20221123/f5fe9da106454785aa86597ba6817c07.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70_pic_center 1]: /images/20221123/51eac15c455b4c2fa25a7315c780d2dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/0a123a189c1f462ea54cb0cbe25855f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221123/b2b3a7b5948347cab411dc832a270f2d.png
[20201001095959180.png_pic_center]: /images/20221123/fe9e42abeabd4922bdef71b0bbeca070.png
[20201001100010644.png_pic_center]: /images/20221123/33101610fdfe48e48082f41b2c3f1197.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70_pic_center 2]: /images/20221123/e2bac1ae248846acbe6bbdc1815d728a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70_pic_center 3]: /images/20221123/debe195548b74b5abbca0b8b3c3de2eb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_1_color_FFFFFF_t_70_pic_center 4]: /images/20221123/dd23c902b968400a8789fa44dc9331b6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczNDA5NQ_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221123/f7fc738f4cf6494c8a4e883d026839f9.png