MySQL 数据库 笔记 RCM

SQL-入门学习笔记（下）

数据库进阶知识点 | MySQL

Posted by Paradise on December 10, 2020

储存过程、游标、触发器和事务

储存过程

相当于自定义函数，每次相似的查询只需要输入对应变量。使用 PROCEDURE 定义储存过程，使用 CALL 调用储存过程。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

-- 不含变量的储存过程：返回默认的查询结果
CREATE PROCEDURE procedure_name()
BEGIN
    SELECT ... FROM ...;
END;
-- 查看创建储存过程语句
SHOW CREATE PROCEDURE procedure_name;
-- 删除储存过程
DROP PROCEDURE procedure_name;

-- 自定义分割符：当储存过程中含有多条语句时
DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
    SELECT ...;
    SELECT ...;
END//
DELIMITER ;

-- 含有变量的储存过程：定义变量类型，使用 INTO 关键字将结果存进变量
CREATE PROCEDURE procedure_name(
    OUT var1 INT, OUT var2 FLOAT(3,10), IN var3 char(20)
)
BEGIN
    SELECT COUNT(field_name1) FROM t INTO var1;
    SELECT AVG(field_name2) FROM t 
    WHERE field_name3 = var3
    INTO var2;
END;
-- 调用函数：可直接传入值，也可事先储存进变量
CALL procedure_name(@variable1, @variable2, 'example');
-- 显示结果
SELECT @valiable1, @variable2;

-- 智能储存过程（《MySQL必知必会》示例代码）
-- Name: ordertotal
-- Parameters: onumber = order number
--              taxable = boolean, 0 or 1
--              ototal = order total variable
CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAl(8, 2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN 
    -- Declare total and tax rate
    DECALRE total DECIMAL(8,2);
    DECLARE taxrate INT DEFAULT 6;
    -- Get order total
    SELECT SUM(item_price * quantity) FROM orderitems
    WHERE order_number = onumber
    INTO total
    -- Whether it's taxable
    IF taxable THEN
        SELECT total + (total / taxrate * 100) INTO total;
    END IF;
    -- Output
    SELECT total INTO ototal
END;

游标

游标是一个存储在MySQL服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用在交互式应用，以支持用户进行滚动浏览以及相关工作。

使用游标步骤

  • 声明游标，这时没有发生检索操作，只是定义要使用的SELECT语句
  • 一旦声明后，必须打开游标以供使用。也就是进行实际上的检索操作
  • 结束使用后需要关闭游标

触发器

触发器作用：使某些语句或储存过程在事件发生时自动执行。触发程序是与表有关的命名数据库对象，当该表出现特定事件时，将激活该对象 监听：记录的增加、修改、删除。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-- 创建触发器
CREATE TRIGGER trigger_name trigger_time trigger_event 
ON tbl_name 
FOR EACH ROW trigger_stmt
    /* 参数：
    trigger_time：是触发程序的动作时间。它可以是 before 或 after；
    trigger_event：指明了激活触发程序的语句的类型；
        - INSERT：将新行插入表时激活触发程序
        - UPDATE：更改某一行时激活触发程序
        - DELETE：从表中删除某一行时激活触发程序
    tbl_name：监听的表，必须是永久性的表，不能将触发程序与TEMPORARY表或视图关联起来；
    trigger_stmt：当触发程序激活时执行的语句。执行多个语句，可使用BEGIN...END复合语句结构；
    */
-- 删除
DROP TRIGGER [schema_name.]trigger_name

-- 注意：对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。

管理事务处理

事务处理（transaction processing）用于维护数据库的完整性。保证成批的SQL操作要么完全执行，要么完全不执行。

相关术语：

  • 事务（transaction）：指一组MySQL语句
  • 回退（rollback）：撤销指定的SQL语句
  • 提交（commit）：指将未存储的执行结果写入数据库
  • 保留点（savepoint）：可回退到指定点而不是整个事务
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 使用方法：首先启动transaction，再执行操作
START TRANSACTION;
DELETE FROM test_table;     -- 删表
ROLLBACK;   -- 回退到事务开始时的状态（ROLLBACK后事务自动关闭）

START TRANSACTION;
ALTER TABLE test_table ADD k CHAR(20);
COMMIT;     -- 提交本次事务中的执行结果（事务自动关闭）

START TRANSACTION;
SELECT * FROM t;
SAVEPOINT sp1;  -- 创建保留点
DROP TABLE t;
SAVEPOINT sp2;
ROLLBACK TO sp1;    -- 回退到指定位置
COMMIT;     -- 提交，释放保留点

安全管理、数据库维护与性能优化

安全管理

root 账户具有完全控制，日常工作中非必要不能使用 root 登陆。

1
2
3
4
5
6
7
8
9
10

-- 创建用户
CREATE USER test_user BY 'password';
-- 删除用户
DROP USER test_user;
-- 查看某个用户的权限
SHOW GRANTS FOR root;
-- 更改密码
SET PASSWORD FOR test_user = Password("12345678")
-- 设置当前用户的密码
SET PASSWORD = Password("12345678")

数据库维护

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 检查表键是否正确
ANALYZE TABLE test_table;
-- 检查多数问题
CHECK TABLE test_table;
-- 查看日志
-- data目录下的hostname.err文件、hostname.log文件

-- 锁表与解锁：MyISAM支持表锁，InnoDB支持行锁
LOCK TABLES t [AS alias];
UNLOCK TABLES;

-- 备份与还原：导出备份与导入备份（在终端运行）
mysqldump -uroot -p123456 test_db tb1, tb2, tb3, ... > C:/test.sql
-- 导出整个库
mysqldump -uroot -p123456 test_db > C:/test.sql
-- 导入：
mysql -uroot -p123456 test_db < C:/test.sql
-- 或者在mysql终端执行
SOURCE C:/test.sql

-- 导出导入数据
SELECT ... INTO OutFile C:/test.sql FROM tb;
LOAD Data InFile C:/test.sql INTO TABLE tb;

改善性能

性能优化的切入点：

  • 遵循硬件建议，用于生产的DBMS应该运行在专用服务器上
  • 更改默认设置以调整内存分配、缓冲区大小等（使用 SHOW VARIABLES; SHOW STATUS 等，查看设置）
  • MySQL为多用户多线程DBMS。可使用SHOW PROCESSLIST显示所有活动进程，并使用KILL命名结束特定进程
  • 尝试联结、并、子查询等，寻找最优的查询方法
  • 使用正确的数据类型
  • 存储过程比单独执行SQL语句快
  • 不检索不需要的数据
  • 使用DELAYED关键字
  • 导入数据时关闭自动提交
  • 索引数据库表可提高检索性能，但降低增删改性能
  • 使用UNION代替多个条件子句
  • 全文本搜索比 LIKE 快
  • 理想的优化和配置会随数据库改变而改变



附录：MySQL数据类型

串数据类型

数据类型 说明
CHAR 1~255个字符的定长字符串，长度在创建时指定，默认长度1
VARCHAR 1~255个字符的变长字符串
TEXT 最大长度为64K的变长文本
LONGTEXT 最大长度为4GB的变长文本
MEDIUMTEXT 最大长度为16K的变长文本
TINYTEXT 最大长度为255B的变长文本
ENUM 接受最多64K个串组成的一个预定义集合中的某个字符串
SET 接受最大64个串组成的一个预定义集合的零个或多个字符串

数值数据类型

数据类型 说明
BIT 位字段，1~64位
BOOLEAN 布尔型，0或1
REAL 4字节的浮点数
DECIMAL 精度可变的浮点值
DOUBLE 双精度浮点值
FLOAT 单精度浮点值
INT -2^31 ~ 2^31-1 的整数值（10位）
BIGINT -2^63 ~ 2^63-1 的整数值（19位）
MEDIUMINT -2^23 ~ 2^23-1 的整数值（7位）
SMALLINT -2^15 ~ 2^15-1 的整数值（5位）
TINYINT -2^7 ~ 2^7-1 的整数值（3位）

日期时间数据类型

数据类型 说明
DATE 1000-01-01 ~ 9999-12-31之间的日期
TIME HH:MM:SS表示的时间
DATETIME DATE和TIME的结合
YAER 用两位数字表示，为70（1970）~69（2069）；用4位数字表示，为1901~2155
TIMESTAMP 与DATATIME相同，但范围更小

二进制数据类型

用于存放任意形式的数据，如图像、多媒体、文本文档等

数据类型 说明
TINYBLOB 最大长度为255B
BLOB 最大长度为64KB
MEDIUMBLOB 最大长度为16MB
LONGBLOB 最大长度为4GB

枚举与集合

  • 枚举：

    enum(v1, v3, v3, ...)

    在已知的值中进行单选，最数量为 2^16 个。枚举值在保存时，以 2 个字节的整型（smalint）进行保存。

  • 集合：

    set(v1, v2, v3, ...)

    最多有 64 个成员，不接受重复值。以 bigint 类型存储，共 8 个字节。采用位运算的形式。

END

目录
    文章标签
    R 数据分析 Python RCM 笔记 总结 编程语言 数据可视化 统计分析 商业分析 机器学习 爬虫 转载 市场分析 MySQL 数据库 神经网络 计算机视觉 Java