木木彡blog

《Mysql必知必会》(MySQL Crash Course)内容很丰富，mysql的各个方面都涉及到了，从基本的SQL增删改查到触发器、事务以及安全管理和性能提升，本书都做了精简的介绍。
相比于另外两本相关的初学者读物——《SQL基础教程》MICK著和《MySQL与MariaDB学习指南》。这本书可能更适合想对mysql有全面了解的（比如对mysql的高级功能）或者想成为数据库管理员的人阅读。

简要介绍

Ref: http://dev.mysql.com/

本书的配套网站MySQL Crash Course，原作者很厉害，毕竟他之前就出了一本堪称全世界用得最多的一本SQL教程-- Sams Teach Yourself SQL in 10 Minutes( 中文版《SQL必知必会》，人民邮电出版社出版)
数据库(database) 是保存有组织的数据的容器(通常是一个文件或一组文件，而数据库软件应称为DBMS（数据库管理系统）
模式(schema) && 数据库(database)? —— 一般情况下，模式用作数据库的同义词，模式(schema) 关于数据库和表的布局及特性的信息
记录(record) && 行(row)？—— 在很大程度上，这两个术语是可以互相替代的，但从技术上说，行才是正确的术语
SQL(发音为字母S-Q-L或sequel)是结构化查询语言(Structured Query Language)的缩写。
DBMS可分为两类:一类为基于共享文件系统的DBMS，另一类为基于客户机—服务器的DBMS。
- 前者如Microsoft Access和FileMaker等，用于桌面用途，通常不用于高端或更关键的应用
- 后者如MySQL、Oracle以及Microsoft SQL Server等数据库是基于客户机—服务器的数据库。
写SQL语句的惯例——关键词都大写，表明列名小写

基本用法

SQL基本用法可参考笔记1和笔记2，这里仅就个别的点总结下

多用SHOW, HELP SHOW, SHOW GRANTS, SHOW ERRORS, SHOW WARNINGS
- DESCRIBE customers;是 SHOW COLUMNS FROM customers;的快捷方式
- INFORMATION_SCHEMA
在处理SQL语句时，其中所有空格都被忽略。
查询结果默认是升序排序(从A到Z)
!=等同于<>操作符
SQL(像多数语言一样)在处理OR操作符前，优先处理AND操作符！！！
- 最好使用圆括号明确地分组相应的操作符
- WHERE id=1002 OR id=1003 AND price>=10 这个WHERE字句的计算次序呢？
MySQL支持使用NOT对IN、BETWEEN和 EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。
从技术上说，LIKE是谓词而不是操作符。
虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE '%'也不能匹配用值NULL作为产品名的行
MySQL正则表达式
- 用REGEXP代替LIKE
- 为了匹配特殊字符，必须用\\为前导。\\-表示查找-，\\.表示查找.。如WHERE name REGEXP '\\.'
- \ && \\ ? 多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但MySQL要求两个反斜杠(MySQL 自己解释一个，正则表达式库解释另一个)。
- 常用的字符类(character class)，如[:alnum:]匹配任意字母和数字(同[a-zA-Z0-9]), [:alpha:]匹配任意字符(同[a-zA-Z])
- 定位元字符: [[:<:]]和[[:>:]]分别匹配词的开始和结尾
LIKE和REGEXP 的不同在于，LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使 REGEXP的作用与LIKE一样。
多数DBMS使用+或||来实现拼接， MySQL则使用Concat()函数来实现
SUM(), MIN(), MAX()会忽略列值为NULL的行。
COUNT()函数有两种使用方式。
- 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值(NULL)还是非空值。
- 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值，如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT 不能用于COUNT(*)，因此不允许使用COUNT(DISTINCT)，否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
分组
- 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
- 使用WITHROLLUP关键字，可以得到每个分组以及每个分组汇总级别(针对每个分组)的值, GROUP BY id WITH ROLLUP
- WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤
ANSI SQL规范首选INNER JOIN语法。此外，尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。
UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)。
在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL 降低INSERT语句的优先级, INSERT LOW_PRIORITY INTO
如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消 (错误发生前更新的所有行被恢复到它们原来的值)。为即使是发
生错误，也继续进行更新，可使用IGNORE关键字，UPDATE IGNORE customers...
如果仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS
last_insert_id()函数可以获取AUTO_INCREMENT对应的值
需要知道的引擎
- InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索;
- MEMORY在功能等同于MyISAM，但由于数据存储在内存(不是磁盘)中，速度很快(特别适合于临时表);
- MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
RENAME TABKE xx TO yy;
CREATE OR REPLACE VIEW xxx, 视图可以嵌套，但不能索引，也不能有关联的触发器

高级用法

1. 全文本搜索

两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持
全文本搜索在性能、明确控制和智能化的结果(例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行)方面很有优势
为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引, FULLTEXT(note_text)的表示对note_text进行索引
使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式
除非使用BINARY方式，否则全文本搜索不区分大小写。
全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回(因为这些行很可能是你真正想要的行)。
Match()和Against() 用来建立一个计算列(别名为rank)，此列包含全文本搜索计算出的等级值。
文本中词靠前的行的等级值比词靠后的行的等级值高。
查询扩展——利用查询扩展，能找出可能相关的结果，即使它们并不精确包含所查找的词。
- WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION)
- WHERE Match(note_text) Against('anvils')
布尔文本搜索
- 即使没有FULLTEXT索引也可以使用, 但这是一种非常缓慢的操作
- WHERE Match(note_text) Against('heavy' IN BOLLEAN MODE)
- WHERE Match(note_text) Against('heavy -rope*' IN BOLLEAN MODE)匹配包含heavy但不包含任意以rope开始的词的行
- 全文本布尔操作符-排除一个词，而*是截断操作符(可想象为用于词尾的一个通配符)
- 在布尔方式中，不按等级值降序排序返回的行。
在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词(如果需要，这个数目可以更改)
MySQL带有一个内建的非用词(stopword)列表，这些词在索引全文本数据时总是被忽略。
MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
忽略词中的单引号。例如，don't索引为dont。

2. 存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合
MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。
mysql命令行客户机的分隔符
- 默认的MySQL语句分隔符为;
- DELIMITER //告诉命令行实用程序使用//作为新的语句结束分隔符
- 除\符号外，任何字符都可以用作语句分隔符
SHOW PROCEDURE STATUS, SHOW PROCEDURE STATUS LIKE 'xxx', SHOW CREATE PROCEDURE xxx
所有MySQL变量都必须以@开始, set total=1; select @total;
MySQL支持IN(传递给存储过程)、OUT(从存储过程传出，如这里所用)和INOUT(对存储过程传入和传出)类型的参数。
简单范例，调用存储过程则键入call productpricing;即可

mysql DELIMITER // CREATE PROCEDURE productpricing() COMMENT 'xxxx' BEGIN SELECT Avg(xx) AS yy INTO zz FROM aa; -- comment DECLAR total DECIMAL(8,2); IF taxable THEN SELECT xx INTO yy; END IF; END // DELIMITER ;

3. 游标

游标(cursor)是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。
SELECT -- MySQL检索操作返回一组称为结果集的行
不像多数DBMS，MySQL游标只能用于存储过程(和函数)。
使用游标
- 声明（定义）--> 打开使用 --> 取出数据 --> 关闭
如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它
DECLARE语句的发布存在特定的次序。用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。不遵守此顺序将产生错误消息。
通常REPEAT语句的语法使它更适合于对游标进行循环
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
- 这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。
- 这里，它指出当SQLSTATE '02000'出现时，SET done=1。SQLSTATE '02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。
简单例子

CREATE PROCEDURE processorders() BEGIN -- Declare local variable DECLARE done BOOLEAN DEFAULT 0; DECLARE o INT; DECLARE t DECIMAL(8,2); -- Declare the cursor DECLARE ordernumbers CURSOR FOR SELECT order_num FROM ordres; DECLARE CONINTUE HANDLER FOR SQLSTATE '02000' SET done=1; -- Create a table to store the results CREATE TABLE IF NOT EXISTS ordertotals (order_num int, total decimal(8,2)) -- Open the cursor OPEN ordernumbers; -- Loop through all rows REPEAT -- get order number FETCH ordernumbers INTO o; -- get the total for the order CALL ordertotal(o, 1, t); -- Insert order and total into ordertotals INSERT INTO ordertotals(order_num, total) VALUES(o, t); -- End of loop UNTIL DONE END REPEAT; -- Close the cursor CLOSE ordernumbers; END;

4. 触发器

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于BEGIN和END语句之间的一组语句):
- DELETE
- INSERT
- UPDATE
每个表最多支持6个触发器(每条INSERT、UPDATE 和DELETE的之前和之后)。
触发器名必须在每个表中唯一，但不是在每个数据库中唯一。
DROP TRIGGER
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added'
- FOR EACH ROW对每个插入执行
只有表才支持触发器，视图不支持(临时表也不支持)。
INSERT
- 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行;
- 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0, 在INSERT 执行之后包含新的自动生成值。
- CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;
DELETE
- 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行
- OLD中的值全都是只读的，不能更新
UPDATE
- 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前(UPDATE语句前)的值，引用一个名为NEW的虚拟表访问新更新的值;
- CREATE TRIGGER updatevendeor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state=Upper(New.ven_state);
进一步介绍
- MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。
- 使用触发器，把更改(如果需要，甚至还有之前和之后的状态)记录到另一个表非常容易。
- 应该用触发器来保证数据的一致性(大小写、格式等)。
简单举例
sql CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW BEGIN INSERT INTO archive_orders(order_num, order_date, cust_id) values(OLD.order_num, OLD.order-date, OLD.cust_id); END;

5. 事务处理

事务处理(transaction processing)可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
术语
- 事务(transaction)指一组SQL语句;
- 回退(rollback)指撤销指定SQL语句的过程;
- 提交(commit)指将未存储的SQL语句结果写入数据库表;
- 保留点(savepoint)指事务处理中设置的临时占位符(placeholder)，你可以对它发布回退(与回退整个事务处理不同)。
不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。
一般的Mysql语句是隐含提交(implicit commit)，即提交(写或保存)操作是自动进行的。
START TRANSACTION; DELETE FROM orderitems WHERE order_num=20120; DELETE FROM orders WHERE order_num=2001; COMMIT;
SAVEPOINT delete1;, ROLLBACK TO delete1;
- 保留点在事务处理完成(执行一条ROLLBACK或 COMMIT)后自动释放
- RELEASE SAVEPOINT可以明确地释放保留点。
autocommit标志决定是否自动提交更改, SET autocommit=0 --> 指示Mysql不自动提交
- autocommit标志是针对每个连接而不是服务器的。

深入了解

字符和校对
- 字符集为字母和符号的集合;
- 编码为某个字符集成员的内部表示;
- 校对为规定字符如何比较的指令。
SHOW CHARACTER SET;, SHOW COLLATION;
- SHOW VARIABLES LIKE 'character%';
- SHOW VARIABLES LIKE 'collation%';
使用Cast()或Convert()函数，可在字符集之间进行转化
SHOW GRANTS FOR yourname;
SET PASSWORD=Password('hello');
为了保证所有数据被写到磁盘(包括索引数据)，可能需要在进行备份前使用FLUSH TABLES语句。
数据库维护
- ANALYZE TABLE tblname; 用来检查表键是否正确
- CHECK TABLE tblname1, tblname2; 可选CHANGED,EXTENED, FAST, MEDIUM, QUICK
- CHECK TABLE支持一系列的用于MyISAM表的方式
  - CHANGED 检查自最后一次检查以来改动过的表
  - EXTENDED 执行最彻底的检查
  - FAST 只检查未正常关闭的表
  - MEDIUM 检查所有被删除的链接并进行键检验
  - QUICK 只进行快速扫描
- REPAIR TABLE tblname3; 这条语句不应该经常使用，如果需要经常使用，可能会有更大的问题要解决
- OPTIMIZE TABLE tblname4; 可用来在删除大量数据后回收所用空间
mysqld 的--safe-mode 装载减去某些最佳配置的服务器
MySQL的主要日志文件：错误日志、查询日志、二进制日志、缓慢查询日志
SHOW PROCESSLIST可以显示所有活动的进程(以及它们的线程ID和执行时间)。
使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句
有的操作(包括INSERT)支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操
LIKE很慢，一般来说，最好是使用FULLTEXT而不是LIKE。

您现在的位置是：首页 > 学无止境

《Mysql必知必会》读书笔记