SQL查询进阶

一、分组查询

把具有相同的数据值的行放在同一组中。

指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。

GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;

 WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

分组规定：

• GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
• 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
• NULL 的行会单独分为一组；
• 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;

 二、子查询

子查询中只能返回一个字段的数据。

//可以将子查询的结果作为 WHRER 语句的过滤条件：
SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
               FROM mytable2);

//下面的语句可以检索出客户的订单数量
//子查询语句会对第一个查询检索出的每个客户执行一次：

SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

三、连接

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

内连接

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

//可以不明确使用 INNER JOIN
//而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。
SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;

 自连接

自连接可以看成内连接的一种，只是连接的表是自身而已。

//子查询版本
SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");

//自连接版本
SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

自然连接 

自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。

内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。

SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

外连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

四、组合查询

使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。

每个查询必须包含相同的列、表达式和聚集函数。

默认会去除相同行，如果需要保留相同行，使用 UNION ALL。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;

五、视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

对视图的操作和对普通表的操作一样。

视图具有如下好处：

• 简化复杂的 SQL 操作，比如复杂的连接；
• 只使用实际表的一部分数据；
• 通过只给用户访问视图的权限，保证数据的安全性；
• 更改数据格式和表示。

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;