MySQL 小结（二）

前言

承接上节继续介绍。上节中仅阐述了 SELECT 检索数据基础用法，实则 SELECT 还有很多更高级的检索方法用来检索出更为复杂需求数据。示例数据就是《MySQL必知必会》一书中给出示例表，去http://www.forta.com/books/0672327120/ 中下载数据脚本。

高级筛选

之前SELECT与WHERE关键字连用已能做简单条件的筛选，还有实用的用法。

LIKE

LIKE关键字是为和通配符配合使用，如果要检索出某个字段中包含指定子串的数据，可以借助通配符来实现。

-- '%'通配任何字符，任意次数
> SELECT * FROM `orderitems` WHERE `prod_id` LIKE 'ANV%'; 

-- '_'仅通配一个字符
> SELECT `prod_id`, `prod_name` FROM `products` 
> WHERE 
> `prod_name` LIKE '_ ton anvil';

通配符相比上节中介绍的WHERE简单条件的耗时要多，那么能减少使用通配符会加快检索效率，同时可以先过滤其他非通配符的筛选，然后再过滤通配符，这样也会提高检索效率。
建议下载 示例表，即可执行看到检索结果。

REGEXP

想必字符串处理必不可少的神器便是正则表达式，只要是有一定规律，都可通过编写正则匹配出想要结果。

-- 整行匹配 ex1
> SELECT * FROM `products` WHERE `prod_name` REGEXP '1000';

-- 常用正则语法 ex2
> SELECT * FROM `vendors` WHERE `vend_city` REGEXP '^[L|P]';

REGEXP 是整行匹配，ex1表示指定字段prod_name中只要包含’1000’字符串即会被检索出来，相当于 LIKE ‘%1000%’。
ex2是在vendors表中检索出vend_city字段中以L或P起始的城市。
MySQL可以根据配置，设置大小写是否敏感。个人PC中设置大小写是不敏感的。

去重，分组

DISTINCT

-- DISTINCT 去重
> SELECT Distinct `order_num`, `item_price` FROM `orderitems`;

DISTINCT是前置关键字，用来去除检索出的重复值，Distinct column表示选择出column不重复的数据。
但是DISTINCT是全部作用，上面示例中表示取order_num和item_price搭配不重复的。
如:

order_num	item_price
20005	5.99
20005	9.99

均会被检索出。

GROUP BY

分组用来对检索数据进行归类

-- 分组检索
> SELECT `order_item`, `prod_id` FROM `orderitems` GROUP BY 
> `order_item`, `prod_id`;

-- 过滤分组
> SELECT `order_item`, `prod_id` FROM `orderitems` GROUP BY 
> `order_item`, `prod_id` HAVING 
> `order_item`<4 AND `order_item`>1;

某些方面分组的效应可以达到DISTINCT效，示例一的效果，类似DISTINCT前置筛选order_item， prod_id。
HAVING是配合分组使用的条件过滤关键字，类似WHERE，正常使用WHERE过滤而分组中的条件用HAVING。

函数

sql中自带了一些函数进行运算

-- AVG() 返回指定字段平均值，非数值字段返回0
-- 计算`order_item`字段平均值
> SELECT AVG(`order_item`) FROM `orderitems`;

-- COUNT 返回指定字段的行数
-- 统计每个`order_item`值对应行数`order_item_number`
> SELECT `order_item`, COUNT(*) AS `order_item_number` FROM `orderitems` GROUP BY `order_item`;

-- MAX() 返回指定字段最大值, 非数值字段一般按字母序
-- 检索`order_item`最大值
> SELECT MAX(`order_item`) FROM `orderitems`;

-- MIN() 返回指定字段最小值, 非数值字段一般按字母序
-- 检索`order_item`最小值
> SELECT MIN(`order_item`) FROM `orderitems`;

-- SUM() 返回指定字段总和, 非数值字段返回0
-- 检索`order_item`总和
> SELECT SUM(`order_item`) FROM `orderitems`;

联结查询

table_a中内容如下：

id	b_id	a_value
1	12	a1
2	13	a2
3	14	a3
4	NULL	a4
5	14	a5

table_b中内容如下：

id	b_value
11	b1
12	b2
13	b3
14	b4

普通联结

关系数据库中有一种关系叫外键，上两个表中table_a中b_id字段就是table_a的一个外键子段。

-- 联结查询
> SELECT a.id, a.a_value, b.b_value 
> FROM table_a AS a, table_b AS b 
> WHERE a.b_id = b.id AND a.id > 1;

查询结果

id	a_value	b.b_value
2	a2	b3
3	a3	b4
5	a5	b4

JOIN

LEFT JOIN

左联结以左表为主表，使用方法

-- LEFT JOIN 左联结查询
> SELECT a.id, a.a_value, b.b_value 
> FROM table_a AS a LEFT JOIN table_b AS b 
> ON a.b_id = b.id;

左表为table_a，那么table_a中所有行都会被检索出，结果如下：

id	a_value	b.b_value
1	a1	b2
2	a2	b3
3	a3	b4
4	a4	NULL
5	a5	b4

RIGHT JOIN

右联结以右表为主表，使用方法

-- RIGHT JOIN 右联结查询
> SELECT a.id, a.a_value, b.b_value 
> FROM table_a AS a RIGHT JOIN table_b AS b 
> ON a.b_id = b.id;

此时table_b为右表，table_b中所有内容都会罗列出，并且只要是table_a中能够匹配上table_b中的列，即使重复也会罗列，上句sql查询结果：

id	a_value	b.b_value
NULL	NULL	b1
1	a1	b2
2	a2	b3
3	a3	b4
5	a5	b4

INNER JOIN

全联结只有当所有表都有匹配上的数据，才会被检索出，使用方法

-- INNER JOIN 全联结查询
> SELECT a.id, a.a_value, b.b_value 
> FROM table_a AS a INNER JOIN table_b AS b 
> ON a.b_id = b.id AND a.id>1;

此时查询结果：

id	a_value	b.b_value
2	a2	b3
3	a3	b4
5	a5	b4

此处在ON条件中还附加使用了AND条件且生效，试问如果在LEFT JOIN 或 RIGHT JOIN使用类似条件会得到怎样的效果？

至此检索的基本常见用法都已介绍结束，但是还有更高级的sql语法。To be continued……