读《MySQL必知必会》我学到了什么？
前言
最近在写项目的时候发现自己的SQL基本功有些薄弱，遂上知乎查询MYSQL关键字，期望得到某些高赞答案的指点，于是乎发现了
https://www.zhihu.com/question/34840297/answer/272185020 这位老兄的建议的书单，根据他的建议首先拜读了《MYSQL必知必会》这本书，整体讲的很基础，页数也不多一共 253 页，适合基础比较薄弱的同学进行食用。然后循序渐进，阅读更深层次的书籍进行自我提升。这里记载了自己在阅读的过程中记录的一些关键内容，分享给大家。书本 PDF 可以在上面的知乎链接获取，或者点击 http://www.notedeep.com/note/38/page/282 前往老哥的深度笔记进行下载。
阅读心得
SQL语句和大小写
SQL语句不区分大小写，并且在 Windows 环境下,4.1.1版本之后(现在常用的都是 5.6/5.7/8.0+)，MYSQL表名，字段名也是不区分大小写的，因此我们在命名的时候建议使用单个单词_单个单词的形式命名，如：mysql_crash_course user_role
这里附上阿里代码规范的一条强制要求：
【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
说明： MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。
不能部分使用DISTINCT
DISTINCT 关键字应用于所有列而不仅是前置它的列。如果给出 SELECT DISTINCT vend_id,prod_price ，除非指定的两个列都不同，否则所有行都将被检索出来。
DISTINCT 不会返回其后面跟的所有字段都相同的列。即两行中SELECT查询的任何一个字段都相同才会被去重。不能通过DISTINCT加括号 () 等方式来对单个字段进行去重。
比如项目中有这么一个需求：
需要分页查询绑了某收费标准的房屋，因为是房屋列表查询，我们默认相同ID的房屋只出现一次,错误的SQL如下：
房屋表
1573986248201
收费标准表
1573986284763
SELECT DISTINCT h.id, h.num, s.name
FROM house h
LEFT JOIN standard s
ON h.id = s.room_id
WHERE s.name = '物业费' OR s.name = '暖气费';
这条语句并不能返回想要的结果，即每套房屋只出现一次，因为不同的收费标准名称不一样，DISTINCT 不能对部分查询条件去重。可以看到房号为1-001的记录出现了两次。
1573986349142
不过其实按照需求的描述我这里仅查询房屋的信息，对于查询结果来说同一条记录的房屋信息肯定完全相同，因此 DISTINCT 在我的业务中满足要求。而有其他业务需要此关键字的时候，请大家慎重使用，切记不能部分使用该关键字
区分大小写和排序顺序
在对文本性的数据进行排序时，A与a相同吗？a位于B之前还是位于Z之后？
在创建字段时可以指定字符集，一般使用 utf8mb4, 此时可以选择相应的排序规则。
utf8mb4_general_ci ci即大小写不敏感，排序时忽略大小写，A a 视作相同
utf8mb4_bin / 带 cs 的即大小写敏感，相应的升序排列的话， A~Z 在前，小a~z在后
相应的，在设置大小写敏感后，查询条件 where cs = 'a' 只能查找到表中该字段为小写 a 的行。而不敏感，即ci时，A，a都可以被查询出来。
BETWEEN关键字的注意事项
在区间查询时，我们最关注的不应该是区间内的能否被匹配到，因为这是肯定的。而区间的边界能否被匹配才是我们应该注意的知识点，BETWEEN AND 关键字匹配区间时，包含左右边界条件。如下面的 SQL 执行结果如下：
SELECT prod_name, prod_price
FROM products p
WHERE p.`prod_price` BETWEEN 5.99 AND 10
1573990863960
NULL 与不匹配
在通过过滤选择出不具有特定值的行时，你可能希望返回具有 NULL 值的行。但是，不行。常见的错误会发生在is_xxx 字段上，我经常有这个毛病，
1573991122233
对于 is_delete 字段，我认为为 null 或者 0 都是未删除的房屋，所以当我使用
SELECT * FROM house WHERE is_delete = '0';
查询未删除的房屋时，我只能查到 id 为 3 的房屋，这显然与我的预期是不符的，解决办法是where后面加 or is_delete is null ,或者给 is_delete 列默认值 0；建议使用后者。
这里附上阿里代码手册的一条强制项目：
【强制】表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（1 表示是，0 表示否）。说明：任何字段如果为非负数，必须是 unsigned 。
解释：tinyint 相当于 Java 中的 byte，取值范围 -128 ~ 127 ，用来表达是否长度已经足够，也可以用来表示人的年龄。而 unsigned 表示无符号的，对于确定为非负数的字段，使用 unsigned 可以将取值范围扩大一倍。
AND 和 OR 的计算次序
举个例子：假如需要列出价格为10美元（含）以上且由 1002 或 1003 制造的所有产品。下面的 SELECT 语句使用 AND 和 OR 操作符的组合建立了一个WHERE 子句：
SELECT *
FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
查询结果如下：
1573991836719
而被我用红框标注的行很显然不是我们需要的行，为什么会这样呢？原因在于计算的次序。SQL（像多数语言一样）在处理 OR 操作符前，优先处理 AND 操作符。当SQL看到上述 WHERE 子句时，它理解为由供应商 1003 制造的任何价格为10美元（含）以上的产品，或者由供应商 1002 制造的任何产品，而不管其价格如何。换句话说，由于 AND 在计算次序中优先级更高，操作符被错误地组合了。解决方法就是加 ();正确的 SQL 如下：
SELECT *
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
建议在任何时候使用具有 AND 和 OR 操作符的 WHERE 子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处，它能消除歧义。
UNION 组合查询
利用 UNION ，可给出多条SELECT 语句，将它们的结果组合成单个结果集。
UNION 中的每个查询必须包含相同的列、表达式或聚集函数。
在使用UNION 时，重复的行被自动取消。这是 UNION 的默认行为，但是如果需要，可以改变它。事实上，如果想返回所有匹配行，可使用 UNION ALL 而不是 UNION 。
在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须出现在最后一条 SELECT 语句之后
INSERT语句总是使用列的列表
一般不要使用没有明确给出列的列表的 INSERT 语句。使用列的列表能使SQL代码继续发挥作用，即使表结构发生了变化。实际开发中有可能由于业务的需要，对表结构进行修改，添加/删除某一列。这时如果代码中使用的SQL语句是没有明确列表的插入语句就会报错。当然一般我们使用逆向工程生成的 insertSelective(POJO) 并不存在这个问题,因为它对应生成的 SQL 会为我们生成列的列表。
小心使用更新和删除语句
MySQL 没有撤销按钮，因此在使用 UPDATE / DELETE 时一定要加上 WHERE 条件，并且在执行更新/删除操作之前先进行 SELECT 操作，开启事务。在执行结束后核对影响的行数和 SELECT 查询出来的行数一致后再 COMMIT；
另外，使用 ALTER TABLE 要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。
视图的规则和限制
与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
对于可以创建的视图数目没有限制。
为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
ORDER BY 可以用在视图中，但如果从该视图检索数据 SELECT 中也含有 ORDER BY ，那么该视图中的 ORDER BY 将被覆盖。
视图不能索引，也不能有关联的触发器或默认值。
视图可以和表一起使用。例如，编写一条联结表和视图的 SELECT语句。
其中视图不能索引这一点要格外注意，在我开发过程中遇到过这样一个视图：使用 UNION 连结了好几张表进行查询，对查询的结果使用 WHERE 条件再过滤，这里虽然对于被连接的表对于 WHERE 条件后的字段都建立了索引，但是使用 UNION 连结生成视图的临时表并不能拥有索引。因此查询的效率会很慢！所以建议在 UNION 查询中将 WHERE 条件放在每个 SELECT 语句中。
改善性能的一些建议
首先，MySQL（与所有DBMS一样）具有特定的硬件建议。在学习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但
对用于生产的服务器来说，应该坚持遵循这些硬件建议。
一般来说，关键的生产DBMS应该运行在自己的专用服务器上。
MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用 SHOW VARIABLES; 和 SHOWSTATUS; ）
MySQL一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执
行缓慢。如果你遇到显著的性能不良，可使用 SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL 命令终结某个特定的进程（使用这个命令需要作为管理员登录）。
总是有不止一种方法编写同一条 SELECT 语句。应该试验联结、并、子查询等，找出最佳的方法。
使用 EXPLAIN 语句让MySQL解释它将如何执行一条 SELECT 语句。
一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快。但存储过程一般难以调试和扩展，并且没有移植性，因此阿里代码规约里面强制禁止使用存储过程
应该总是使用正确的数据类型。
决不要检索比需求还要多的数据。换言之，不要用 SELECT * （除非你真正需要每个列）。
有的操作（包括 INSERT ）支持一个可选的 DELAYED 关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作。
延迟插入，当插入和查询并发执行时，插入被放入等待队列中。直至所有查询执行完毕后执行插入。并且MYSQL会在收到插入请求后直接返回给客户端状态信息，既是INSERT语句还在队列中
在导入数据时，应该关闭自动提交。你可能还想删除索引（包括FULLTEXT 索引），然后在导入完成后再重建它们。
必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的 SELECT 语句以找出重复的WHERE 和 ORDER BY 子句。如果一个简单的 WHERE 子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。
你的 SELECT 语句中有一系列复杂的 OR 条件吗？通过使用多条SELECT 语句和连接它们的 UNION 语句，你能看到极大的性能改进。
索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。）
LIKE 很慢。一般来说，最好是使用 FULLTEXT 而不是 LIKE 。但是 MYSQL FULLTEXT 对汉字并不友好，如果需要使用全文索引，建议使用搜索引擎 ES，可以参考我的另一篇博客： https://www.cnblogs.com/keatsCoder/p/11341835.html
数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
最重要的规则就是，每条规则在某些条件下都会被打破。
实际开发过程中，我们需要根据业务的需要，开启慢查询日志，然后针对慢SQL，不断地进行 EXPLAIN 与修改SQL和索引，以求达到 ref 级别，至少达到 range 级别。这就需要强大的内功支持而不是每次都通过百度来解决，希望阅读此篇文章的你和我一起不断修炼。加油！
常用的函数
文本处理函数
1573992432663
日期和时间处理函数
1573992462010
数值处理函数
1573992518509
聚合函数
1573992537792
虽然 MAX() 一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则 MAX() 返回最后一行。MIN() 函数类似
MAX() 函数忽略列值为 NULL 的行。MIN() 函数类似，SUM() 也是
附录
由于数中所附的附件内容(建表语句即数据插入语句)需要外网才能访问，为了方便大家使用。这里我已经下载下来附在了这篇博客里面。如果不需要这些语句，可以直接通过网页右边的目录跳跃到下一章进行浏览
建表语句
########################################
# MySQL Crash Course MYSQL必知必会建表语句
# http://www.forta.com/books/0672327120/
# 提供者博客园：后青春期的Keats 复制请注明出处
########################################
########################
# Create customers table
########################
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL ,
cust_address char(50) NULL ,
cust_city char(50) NULL ,
cust_state char(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
#########################
# Create orderitems table
#########################
CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_id char(10) NOT NULL ,
quantity int NOT NULL ,
item_price decimal(8,2) NOT NULL ,
PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;
#####################
# Create orders table
#####################
CREATE TABLE orders
(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL ,
cust_id int NOT NULL ,
PRIMARY KEY (order_num)
) ENGINE=InnoDB;
#######################
# Create products table
#######################
CREATE TABLE products
(
prod_id char(10) NOT NULL,
vend_id int NOT NULL ,
prod_name char(255) NOT NULL ,
prod_price decimal(8,2) NOT NULL ,
prod_desc text NULL ,
PRIMARY KEY(prod_id)
) ENGINE=InnoDB;
######################
# Create vendors table
######################
CREATE TABLE vendors
(
vend_id int NOT NULL AUTO_INCREMENT,
vend_name char(50) NOT NULL ,
vend_address char(50) NULL ,
vend_city char(50) NULL ,
vend_state char(5) NULL ,
vend_zip char(10) NULL ,
vend_country char(50) NULL ,
PRIMARY KEY (vend_id)
) ENGINE=InnoDB;
###########################
# Create productnotes table
###########################
CREATE TABLE productnotes
(
note_id int NOT NULL AUTO_INCREMENT,
prod_id char(10) NOT NULL,
note_date datetime NOT NULL,
note_text text NULL ,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
) ENGINE=MyISAM;
#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);
数据语句
########################################
# MySQL Crash Course MYSQL必知必会数据语句
# http://www.forta.com/books/0672327120/
# 提供者博客园：后青春期的Keats 复制请注明出处
########################################
##########################
# Populate customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');
########################
# Populate vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');
#########################
# Populate products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi