检索数据——mysql必知必会（一）

• 检索数据
• 排序检索 #
• 过滤数据
• 计算
• 分组数据
• 子查询
• 联结
• 组合查询
• 全文本搜索

检索数据

SELECT检索

• 检索多个列。

SELECT col1, col2 FROM table;

• 检索所有列。通配符*

SELECT * FROM table;

• 检索不同的行，相同的值不重复出现。
  
  • DISTINCT，用在列前，针对所有列。col1和col2都相同，才认定为相同的结果。

SELECT DISTINCT col1 FROM table;
SELECT DISTINCT col1, col2 FROM table;

分页限制结果

• LIMIT检索前4行。

SELECT col1 FROM table LIMIT 4;

• 检索下标3（第4行）开始的4行。

SELECT col1 FROM table LIMIT 4 OFFSET 3;
SELECT col1 FROM table LIMIT 3, 4;

排序检索 #

排序数据

• ORDER BY操作。
• 先按照col1，先按照col2排序。

SELECT col1, col2, col3 FROM table ORDER BY col1, col2;

降序排序

• DESC，用在order by的列后，针对一列
• 按col1降序排列，col2升序排列。

SELECT col1 FROM table ORDER BY col1 DESC, col2;

• 检索按col1排列的TOP k。

SELECT col1 FROM table ORDER BY col1 DESC LIMIT k;

过滤数据

WHERE过滤

• WHERE操作。

SELECT col1 FROM table WHERE col1 = x;
SELECT col1 FROM table WHERE col1 < x;

• 范围检查。BETWEEN、IN。
  
  • IN一般效率高。可以在IN后跟SELECT语句。

SELECT col1 FROM table WHERE col1 BETWEEN x AND y;
SELECT col1 FROM table WHERE col1 IN (x,y);

• 空值检查。IS NULL。

SELECT col1 FROM table WHERE col1 IS NULL;

• 使用 EXISTS 判断是否存在。可与 IS NULL 转化。

SELECT * FROM employees 
WHERE NOT EXISTS (
    SELECT emp_no FROM dept_emp WHERE emp_no = employees.emp_no
);

• 逻辑条件。AND、OR、NOT。
  
  • AND优先于OR，最好使用（）确定优先级。
  • NOT否定之后的条件。支持NOT IN， NOT BETWEEN。

SELECT col1 FROM table WHERE c1 AND c2;
SELECT col1 FROM table WHERE c1 OR c2;
SELECT col1 FROM table WHERE NOT c1 OR c2;

通配符过滤

• LIKE操作符。
  
  • % 表示任意字符串（包括空字符串），但不能匹配NULL。
  • _ 匹配单个字符。
  • 通配符效率较低，不应过度使用。
  • 通配符置于搜索模式开始处，效率最低。

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

正则表达式过滤

• mysql支持正则表达式的一个子集。
• REGEXP操作，通常不区分大小写，可用BINARY区分大小写。

SELECT prod_name FROM products WHERE prod_name REGEXP '[^123] Ton' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP BINARY 'jetpack .000' ORDER BY prod_name;

• 类似java里的正则表达式，特殊字符需要转义。

SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.' ORDER BY vend_name;

• 字符类的使用，需要再使用一个[]。
  

SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;

• 正则表达式的验证。返回0不匹配，1匹配。

SELECT '' REGEXP '';

计算

拼接字段

• CONCAT ， GROUP_CONCAT(默认,分隔) 等拼接操作。拼接字符串相关。

SELECT CONCAT(TRIM(vend_name),' (',TRIM(vend_country),')')  AS vend_title
FROM vendors ORDER BY vend_name;

SELECT dept_no, GROUP_CONCAT(emp_no) AS employees
FROM dept_emp
GROUP BY dept_no;

算数计算

• 四则运算。

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price 
FROM orderitems WHERE order_num = 20005;

文本处理函数

• 常用子串、去空格。
  
  
• Soundex能对文本的发音比较。

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

日期和时间处理函数

• 直接 x='2005-09-01'，只有x的日期为2005-09-01且时间为00:00:00才匹配。一般使用方式为：

SELECT cust_id, order_num FROM orders 
WHERE DATE(order_date) = '2005-09-01';

• 查询和修改日期对应的年月等。
  

数值处理函数

• 代数运算。
• 

聚集函数

• 按照行组汇总数据，返回结果。
• 用在 SELECT 后面。
• 聚集函数忽略值为NULL的行，除了COUNT(*)。
• 

SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min 
FROM products;

• 汇总不同值的结果，DISTINCT。
  
  • DISTINCT不能用于COUNT(*)，即COUNT(DISTINCT)。

SELECT AVG(DISTINCT prod_price) AS avg_price 
FROM products WHERE vend_id = 1003;

分组数据

• GROUP BY操作。
  
  • 除了聚集函数，SELECT中的每个列都要在GROUP BY中给出。
  • WITH ROLLUP可以汇总每个分组的结果。
  • 使用ORDER BY明确分组排序。

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

过滤分组

• HAVING。过滤分组，语法类似WHERE。

SELECT vend_id, COUNT(*) AS num_prods FROM products 
GROUP BY vend_id HAVING num_prods > 2;

• WHERE在分组前对行过滤，HAVING在分组后对分组过滤。

SELECT子句顺序

• SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT。

子查询

• 嵌套在查询中的查询。

用作过滤

• WHERE中的列需要和子查询SELECT中列匹配。

SELECT cust_id FROM orders 
WHERE order_num IN 
( SELECT order_num FROM orderitems WHERE prod_id = 'TNT2' );

用作计算

• 用作计算字段。

SELECT cust_name, cust_state, 
( SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id ) AS orders 
FROM customers ORDER BY cust_name;

联结

• 在一条SELECT语句中关联表。
• 联结步骤：将第一张表的每一行和第二张表的每一行配对，之后使用WHERE**过滤，得到匹配联结条件的配对，该配对排除了重复出现的列**。
• 没有联结条件的返回结果称为笛卡尔积，行的数目为表一行数*表二行数。
• 联结的表越多，性能下降越厉害。
• 和子查询相比，联结有可能效率更高，但是受索引、数据量、数据类型等的影响，也可能子查询效率高。

内部联结

• 基于两个表之间的相等测试，也叫等值联结。

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连接表，ON表示联结条件。这样可以避免忘记联结条件，有时性能更优。

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;

自联结

• 联结相同的表。需要使用表的别名。

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

外部联结

• 可以包含没有关联行的行。
• 分为LEFT OUTER JOIN和RIGHT OUTER JOIN，语法类似内部联结。left显示左表的所有关联词，right相反。

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;  

• 多个外部联结连用是需要一个LEFT JOIN 对应一个 ON，不能只在最后使用一个ON，相关

select e.last_name, e.first_name, d.dept_name
from employees e 
left join dept_emp de on e.emp_no = de.emp_no
left join departments d on de.dept_no = d.dept_no;

使用聚集函数的联结

• 从多个表汇总数据。

SELECT customers.cust_id ,customers.cust_name , COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

组合查询

• 执行多个查询，并将结果作为单个查询结果返回。
• UNION必须由两个以上SELECT组成，每个查询必须包含相同的列、表达式、聚集函数。
• 组合查询只能在最后一个查询之后使用一个ORDER BY，即结果按照一个规则排序。
• 使用UNION ALL包含重复的行，UNION自动去重。
  使用WHERE的多个条件无法做到包含重复的行，只能得到UNION的结果。

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);

全文本搜索

• 相比通配符和正则表达式的优点：
  
  1. 性能较高。索引被搜索的列，正则等通常需要匹配所有行。
  2. 控制明确。可以指定什么匹配什么不匹配等。
  3. 结果智能。例如按照更好的规则排列结果。
• 创建表时使用FULLTEXT（索引的列）。或者在导入数据之后修改表。后者效率高，因为前者在导入数据的时候需要更新索引。

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;

• MATCH和AGAINST执行全文本搜索。
  
  • 返回结果按照优先级排序。该优先级由行中词的数目等因素决定。

SELECT note_text FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit');

扩展查询

• WITH QUERY EXPANSION搜索词相关的其他所有行。
  
  • 通过两遍扫描完成。全文本搜索找出匹配的行->选出有用的词->使用原来的条件和有用的词再次全文本搜索。

SELECT note_text FROM productnotes
WHERE MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION);

布尔文本搜索

• IN BOOLEAN MODE可以决定
  
  • 要匹配的词、要排斥的词。
  • 排列提示，调高或者降低词的优先级。
  • 表达式分组。
• 布尔文本搜索无须FULLTEXT索引也可使用，但是效率会较低。

SELECT note_text FROM productnotes
WHERE MATCH(note_text) AGAINST('+heavy -rope*' IN boolean MODE);

• 

说明

• 短词（<=3个字符的词）被忽略。
• 超过50%的行出现某个词，这个词为无用词。不适用于布尔文本搜索。
• 表中行数<3，则不返回结果，因为出现的词都是无用词。
• 词中的单引号忽略。
• MyISAM支持全文本搜索，InnoDB不支持。