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……