读书笔记《MySQL必知必会》

前言

MySQL 是世界上最受欢迎的数据库管理系统之一。书中从介绍简单的数据检索开始，逐步深入一些复杂的内容，包括联结的使用、子查询、正则表达式和基于全文本的搜索、存储过程、游标、触发器、表约束，等等。通过重点突出的章节，条理清晰、系统而扼要地讲述了读者应该掌握的知识，使他们不经意间立刻功力大增。
本书注重实用性，操作性很强，适用于广大软件开发和数据库管理人员学习参考。
本书基于Mysql5，如果你认为你对MySQL已经非常熟悉了，建议对照下文看看有没有什么遗漏再进行进一步深入学习。

正文

• 通配

like % 匹配0个或任意个任意字符，_ 匹配一个任意字符
使用like搜索效率降低
用通配符开头是最慢的
regexp 效率低，如果要区分大小写 regexp binary

• 日期

datetime日期格式 年份应总是用yyyy-mm-dd表示
需过滤某一天 Date(date)='2020-02-02'
过滤月份 Year(date)=2020 and Month(date)=2

• 总计行数

count(*) 返回包含null所有行 count(col) 返回该列不含null

• 排序

order by 排序数据 如果该列数据相同时返回顺序仍不确定 应再加个排序列如主键id

• 分组

group by 分组数据
SELECT entity_name, COUNT(*) FROM action_log GROUP BY entity_name;
SELECT entity_name, nickname, COUNT(*) FROM action_log GROUP BY entity_name, nickname;
group by 必须在where之后 order by之前
group by和select不总保证熟悉怒排序 需要排序需要给出 order by

• 过滤组

having 支持所有 where 操作符，唯一的区别是where过滤行 having过滤组
where在数据分组前过滤 having在数据分组后过滤
SELECT entity_name, COUNT(*) FROM action_log GROUP BY entity_name having count(*)>100;

• 等值联结

等值联结也称内部联结 (equljoin)  from后跟多张表或 inner join table on

• 外部联结

外部联结 相比内部联结可以包含没有关联的行

• 组合查询

组合查询 可执行多个查询 称为并(union)或符合查询(compound query) 
UNION 中每个查询必须包含相同的列，类型不必完全相同但要可隐式转换
UNION 自动去重行 不去重用 UNION ALL 这种时候没法使用where代替完成
ORDER BY 只能在最后一个select后且对总结果集有效

• 全文本搜索

全文本搜索 MyISAM支持 InnoDB不支持 自动按等级值排序(5.6版innodb已支持英文全文本)
创建列用 FULLTEXT(note_text) 自动创建索引 新增修改删除时索引同步自动更新
检索 SELECT note_text FROM table WHERE Match(note_text) Against('word');
查看得分 SELECT note_text,Match(note_text) Aganist('word') AS rank FROM table;
出现匹配词靠前的等级值更高 多个匹配或多个搜索项命中的等级值更高
扩展查询 Against('word' WITH QUERY EXPANSION) 第一次查询到的行再做key查一次
MySQL带有一个内建的非用词stopword列表 在索引全文本数据时总是被忽略
一个词在50%的行里出现就被作为非用词忽略 (不用于IN BOOLEAN MODE)
表中的行少于3行则搜不到结果(因为每个词不出现或至少出现在50%行内)
忽略词中的单引号 don't索引为dont
不具有词分隔符(包括日语和汉语)的语音不能恰当返回全文本搜索结果

• 布尔文本搜索

布尔文本搜索 boolean mode 可以用包含和排除 可以不建FULLTEXT索引但非常慢

• 插入

INSERT 操作可能会很耗时(特别是有很多索引需要更新时) 
可以指定优先级 INSERT LOA_PRIORITY INTO
用单条INSERT语句处理多个插入比使用多条INSERT语句块
select last_insert_id() 函数返回最后一个AUTO_INCREMENT值

• 引擎对比

MyISAM和InnoDB
InnoDB支持事务处理 不支持全文本搜索(5.6版已支持，他们对中文文本都不友好(英文词有明确的空格))
MEMORY在功能等同于MyISAM但数据存储在内存中速度快 特别适合于临时表
MyISAM性能极高 支持全文本搜索 但不支持事务

• 视图

虚拟表(包含的SQL查询语句)
重用SQL语句 简化复杂的SQL操作而不必知道它的基本查询细节
保护数据 可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
更改数据格式和表示 视图可返回与底层表的表示和格式不同的数据
复杂视图或嵌套视图可能引起性能问题 应多测试
视图主要用于数据检索 CREATE VIEW viewname AS sql（SELECT...）
增删改有限制（分组、联结、子查询、并、聚集函数、DISTINCT、导出计算列等）

• 存储过程

为使用方便而保存的一条或多条sql语句的集合 可视为批处理文件
简化操作 安全 高性能
保证数据的完整性 所有应用使用和测试同一存储过程 防止错误
保证安全性 变动修改容易 如修改表名列名或业务逻辑等 只需更改存储过程的代码
提高性能 使用存储过程比单独使用的SQL语句更快
只能用在单个请求中的MySQL元素和特性
一般来说 存储过程编写较复杂
一般用户没有创建存储过程权限 但有使用存储过程权限
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceavg
FROM products;
END;
CALL productpricing()

• 游标

cursor MySQL游标只能用于存储过程 用DECLEAR命名
CREATE PROCEDURE processorders()
BEGIN
DECLEAR o INT;
DECLEAR ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
OPEN ordernumbers;
FETCH ordernumbers INTO o;
CLOSE ordernumbers;
END;

• 触发器

应在库里唯一名称 关联的表 应该相应的活动(增删改) 之前还是之后触发
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';
单一触发器不能与多个事件或多个表关联 每个表最多支持6个触发器
可以触发器返回自增长id AFTER并使用NEW    
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;
OLD虚拟表可以被使用在DELETE之前或之后 只读不能更新 可用来备份删除数据
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT archive_orders(order_num, order_date)
VALUES(OLD.order_num, OLD.order_date);
END;
BEFORE DELETE比AFTER DELETE的好处是如果存档失败不会继续执行删除

• 事务

事务 transaction 回退 rollback 提交 commit 保留点 savepoint
事务对增删改有效 CREATE和DROP不会被撤销
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 1;
DELETE FROM orders WHERE order_num = 1;
COMMIT;
当COMMIT或ROLLBACK执行后 事务会自动关闭
SAVEPOINT delete1;
ROLLBACK TO delete1;
可以用尽可能多用保留点能更灵活的回退 保留点在事务处理完成后(执行一条ROLLBACK或COMMIT)自动释放
SET autocommit=0; 可以指示MySQL让当前这个连接下不自动提交(增删改)

• 字符集和校对顺序

字符集为字母和符号的集合
编码为某个字符集成员的内部表示
校对为规定字符如何比较的指令

• 安全管理

访问控制
GRANT SELECT, INSERT ON crashcourse.* TO bforta;

• 数据库维护

备份所有库mysqldump 
转储数据BACKUP TABLE或SELECT INTO OUTFILE 恢复RESTORE TABLE
被分钱使用FLUSH TABLES将所有数据(包括索引数据)保证写到磁盘
检查表键正确 ANALYZE TABLE table; CHECK TABLE table;
表内大量删除数据 应使用OPTIMIZE TABLE table; 回收空间 优化性能

• 日志文件

位于data目录
错误日志 通常名hostname.err 可用--log-error更改
查询日志 通常名hostname.log 可用--log更改 可能会很快非常大
二进制日志 通常名hostname-bin 可用--log-bin更改 记录更新过数据的所有语句
缓慢查询日志 通常名hostname-slow.log 可用--log-slow-queries更改
执行FLUSH LOGS可刷新和重新开始所有日志文件

• 改善性能

调整内存分配 缓冲区大小等 SHOW VARIABLES; 和 SHOW STATUS;
显示所有活动进程 SHOW PROCESSLIST; 可以用 KILL 终结特定的进程
总有不止一种编写方法 试验联结、并、子查询等找出最佳方法
使用EXPLAIN前缀让MySQL解释如何执行一条SELECT语句
应总是使用正确的数据类型
不要检索更多数据 即不要首先用 SELECT *
导入数据时关闭自动提交 删除索引并在导入完成后重建索引
必须索引数据库表改善检索性能 分析SELECT找出重复的WHERE和ORDER BY
SELECT有一系列复杂的OR 可用多条SELECT和连接UNION能看到极大的性能改善
索引改善数据检索性能 但降低增删改性能 不经常被搜索则不要索引(据需实时添删索引)
LIKE REGEXP很慢

总结

很好的MySQL入门使用手册，可以帮助你上手或了解MySQL所能提供的功能全貌。