MySQL必知必会-查询

本篇主要内容为查询

过滤数据

使用WHERE指定过滤条件

使用通配符过滤

模式区分大小写，注意，尽量不要把通配符放在搜索模式的开始处，搜索起来是最慢的

使用正则表达式过滤

关键字是REGEXP，后面跟正则表达式

计算

文本处理

substring_index

SELECT cust_name,cust_contact FROM customers WHERE Soundex(cust_contact)=Soundex('Y Lie');

时间处理

数值处理

聚集函数

数据分组

GROUP BY分组数据，GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。WITH ROLLUP可以得到每个分组以及每个分组汇总级别的值，如

SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend WITH ROLLUP;

HAVING与WHERE用法类似，只不过WHERE过滤行，HAVING过滤分组

HAVING与WHERE的区别：WHERE在数据分组前过滤，HAVING在数据分组之后进行过滤，WHERE排除的行不包括在分组内。这可能会改变计算值，进而影响HAVING子句中基于这些值过滤掉的分组

ORDER BY与GROUP BY区别

虽然用GROUP BY分组的数据经常按分组顺序输出，但情况不总是这样，因为它并不是SQL规范所要求的。一般使用GROUP BY子句时，应该也给出ORDER BY子句以保证数据正确排序

SELECT子句顺序

子查询

在SELECT语句中，子查询总是从内向外处理

相关子查询:涉及外部查询的子查询。列名有多义性就必须使用 完全限定列名(表名与列名由一个句点分隔)

# 查询订购物品TNT2的所有用户
$ SELECT cust_name,cust_contact FROM customers WHERE cust_id IN(
    SELECT cust_id FROM orders WHERE order_num IN(
      SELECT order_num FROM orderitems WHERE pro_id='TNT2'
    )
  );

# 查询customers中每个用户的订单总数
$ SELECT cust_name,
         cust_state,
         (SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id) AS orders
  FROM customers
  ORDER BY cust_name;

联结表

外键（foreign key）: 外键 为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系

联结：一种机制，用来联结多个表返回一组输出，联结在运行时关联表中正确的行

笛卡尔积（cartesian product）：由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数

应该保证所有联结都有WHERE子句并保证WHERE子句的正确性，否则MySQL将返回比想要数据多得多的数据

等值联结

等值联结（equijoin）:它基于两个表之间的相等测试，又称为 内部联结

# 等值联结
# 相等测试
$ SELECT vend_name,prod_name,prod_price
  FROM vendors,products
  WHERE vendors.vend_id=products.vend_id
  ORDER BY vend_name,prod_name;

# INNER JOIN
$ SELECT vend_name,prod_name,prod_price
  FROM vendors INNER JOIN products
  ON vendors.vend_id=products.vend_id;

ANSI SQL规范首选INNER JOIN语法。此外，尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能

自联结

联结相同的表(使用表别名)

$ SELECT p1.pro_id,p1.prod_name 
  FROM products AS p1,products AS p2
  WHERE p1.vend_id=p2.vend_id
    AND p2.prod_id='DTNTR'; 

自然联结

无论何时对标进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（内部联结）返回所有数据，甚至相同的列多次出现。自然联结 排除多次出现，使每个列只返回一次。自然联结使这样一种联结，其中你只能选择那些唯一的列。这一版是通过对标使用通配符（SELECT *）,对所有其他表的列使用明确的子集来完成的

外部联结

外部联结：联结包含了那些在相关表中没有关联行的行

如：对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户

内部联结无法包括那些尚未下订单的客户,而外部联结可以包括

#内部联结
  +---------+-----------+
  | cust_id | order_num |
  +---------+-----------+
  |   10001 |     20005 |
  |   10001 |     20009 |
  |   10003 |     20006 |
  +---------+-----------+

# 外部联结
$ SELECT cusomers.cust_id,orders.order_num
  FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id=orders.cust_id;

  +---------+-----------+
  | cust_id | order_num |
  +---------+-----------+
  |   10001 |     20005 |
  |   10001 |     20009 |
  |   10002 |      NULL |
  |   10003 |     20006 |
  +---------+-----------+

使用OUTER JOIN语法时，必须使用LEFT或RIGHT关键字指定包括其所有行的表（LEFT指出的使OUTER JOIN左边的表；RIGHT指出的使OUTER JOIN右边的表）

组合查询

MySQL允许执行多个查询，并将结果作为单个查询结果集返回，这些组合查询通常称为 并（union） 或 符合查询（compound query）

多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同

# 组合查询
$ SELECT vend_id,prod_id,prod_price
  FROM products
  WHERE prod_price<=5
  UNION
  SELECT vend_id,prod_id,prod_price
  FROM products
  WHERE vend_id IN (1001,1002);

# 等价于
$ SELECT vend_id,prod_id,prod_price
  FROM products
  WHERE prod_price<=5
    OR vend_id IN (1001,1002);

注意事项：

• UNION必须由两条或以上的SELECT语句组成，语句之间用UNION分隔
• UNION中的每个查询必须包含相同的列、表达式或聚集函数
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐形转换的类型

UNION默认会去除重复的行，如果想返回所有行，使用UNION ALL（这是WHERE完成不了的）。此外，使用UNION组合查询时，只能使用一条ORDER BY子句，而且ORDER BY是对所有SELECT返回的结果进行排序

全文本搜索

并非所有引擎都支持全文本搜索，如MyISAM支持，但InnoDB不支持

使用LIKE或正则表达式可以匹配文本，但是有及格重要限制

• 性能–通配符和正则通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引），随着被搜索行数不断增加，这些搜许哦可能非常耗时
• 明确控制–使用通配符和正则很难明确控制匹配什么和不匹配什么。例如指定一个词必须匹配，一个词必须不匹配
• 智能化的结果–不能智能化选择结果

在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行，这样，MySQL可以快速有效地决定那些词匹配或不匹配以及匹配频率

一般在创建表时启用全文本搜索，在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新

CREATE TABLE productnotes(
  note_id   int   NOT NULL AUTO_INCREMENT,
  note_text text  NULL,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MyISAM;

在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。全文本搜索返回以文本匹配的良好程度排序的数据，每个行都有一个等级值，不包含关键词的为0，文本中词考前的行比词靠后的行等级值高，含关键词多的等级值高

$ SELECT note_text
  FROM productnotes
  WHERE Macth(note_text) Against('rabbit');

传递给Match()的值必须与FULLTEXT()定义中的相同。

查询扩展：能找出可能相关的结果，即使它们并不精确包含所查找的词

$ SELECT note_text
  FROM productnotes
  WHERE Macth(note_text) Against('rabbit' WITH QUERY EXPANSION);

布尔文本搜索可以提供如下内容的细节：

• 要排斥的词
• 要匹配的词
• 排列提示
• 表达式分组

相关

MySQL必知必会-查询

MySQL必知必会-插入、增加、修改、删除

MySQL必知必会-视图、存储过程