SQL学习笔记（五）分组操作、子查询

本文为《SQL学习指南》一书的学习笔记，供自己记录查找使用。如有侵权可私信删除。

今日文章包括：chap 8 分组与聚集

chap 9 子查询

分组与聚集

select open_emp_id
    -> from account
    -> group by open_emp_id;

 group by子句在where子句被评估后进行，因此在分组后计算各组内相应值的函数（如count 函数、max 函数）不能出现在where语句之中。（因为where过滤的时候压根还没有创建这个列呢！）对于group by 之后的过滤，使用having子句来进行分组过滤。如我们想过滤掉账户数不足五个的银行柜员：

 常用的聚集函数有max(),min(),avg(),sum(),count()等。

可以不分组，从而得到整张表单的某些信息（如，max与min）。但如果你select了一个非聚集函数定义的列出来。据本人观察，这时候这一列它显示的是“中位数”。若用这一列分了组，那么各个聚集函数列显示的则为分组后各组内的描述性统计。

select open_emp_id,count(*) count ,max(avail_balance),avg(avail_balance)
    -> from account
    -> group by open_emp_id;

 count函数可以使用distinct关键字。如以上的程序，若select count(open_emp_id）from account，得到结果为24；而若使用select count(DISTINCT open_emp_id) ...，得到结果则为4.

 注意：在执行聚集函数或其他数值计算时，应当首先考虑null值是否可能影响计算结果。

 sum(),max(),avg()函数在遇到null值时会自动忽略（就当没见到过它）。而count()不顾内容是什么，会把它计算进去。所以对于一个表，如果我们加了一行所有值均为null的数据，除了count会变化之外其余不变。

• 对于两层分组后又想计算第一层分组合计数的需求：使用 WITH ROLLUP 选项

select product_cd,open_branch_id,SUM(avail_balance) tot_balance 
from account 
where product_cd in ("BUS","CD","CHK","MM") 
group by product_cd,open_branch_id WITH ROLLUP;

 在低版本的MySQL中可以使用WITH CUBE来计算各支行的合计数（即把group by的方式掉个个儿），但是6.0版本开始就不能使用了。

 总的来说，MySQL的这个和SAS没什么区别，还是挺容易上手的。

子查询

子查询可以根据是否完全独立被划分为两种：非关联子查询（完全独立）与关联子查询（引用包含语句中的列）。

非关联子查询

我们把返回单行单列的非关联子查询称为标量子查询，这种子查询可以位于常用运算符（=,<>,<,>,<=,>=）的任意一边。

当子查询返回单行单列的结果时，可以直接被用作等式条件其中的一个表达式。如果多于一行时，可以被用于比较，但不能用于等式判断。一旦多于一行又被用在了等号的一端，便会报错（表达式不能等于表达式集）。

多行单列子查询 

当返回的子查询结果多于一行时，可以使用 in / not in / all /any 这四个运算符。使用起来与SAS中的使用方法相同，但是要注意的一点是当使用not in或<>运算符比较一个值和一个值集时，值集中一定确保不能含有null值，而“任何一个将值与null进行比较的企图都将产生未知的结果”。（原书原话）

另外再叨叨一句，我对 all 和 any 的表达很不习惯，但是如果把 all 理解成“任意”（集合的交），把 any 理解成“存在”（集合的并）会豁然开朗。大概对any all表达的不熟悉也和我没读过什么英文数学原著有关系吧……

多列子查询

记得过滤条件用括号括起来，其他没别的了。主要是自己实战经验不足所以常常想不到连接表的好方法，常常用最简单的语法，多做多习惯就会慢慢精进了吧。

关联子查询

与非关联子查询不同，关联子查询不是在包含语句执行之前一次执行完毕，而是为每一个候选行执行一次。也就是说，非关联子查询在执行包含语句时，其子查询语句已近作为一个值/值集存在了；而关联子查询要对每一个数据行执行一遍子查询语句（因为子查询中存在与包含语句相关的描述，这对于每一个候选行来说可能会是不一样的值。）才能得到用于包含语句的值/值集。这是它的工作方式。

举个栗子：我们想筛选出账户数多于2的客户id.当然我们可以利用表连接之后进行筛选（但显然一下子就想到的方式必将繁琐得要命），这里我们采用关联查询的方式：首先利用关联查询计算每个客户的账户数，再由包含查询检索拥有两个账户的客户。

SELECT c.cust_id,c.cust_type_cd,c.city
FROM customer c
WHERE 2 = (SELECT COUNT(*) 
FROM account a 
WHERE a.cust_id = c.cust_id);

 这里我的小反思：① 善用函数。count()分组计数是专业的！这都没想到只能说太不熟悉了。。有必要回去复习前几天笔记了，不然白学了。② 这样做一个子查询相当于给原来的表增加了一列用以存放子查询结果，非常直观便于理解。

exist运算符是构造包含关联子查询条件的最常用运算符。如果只关心存在关系而不关心数量时就可以使用exist运算符：具体来说，就是子查询可能会返回0、1或多行结果，EXIST操作符的作用是检验子查询是否能返回至少一行，通常只写select 1或select （*）即可达到目的。例如：

SELECT a.account_id,a.product_cd,a.cust_id,a.avail_balance
FROM account a
WHERE EXISTS (SELECT 1 FROM transaction t
WHERE t.account_id = a.account_id
AND t.txn_date = '2008-09-22');

 也可以使用not exist运算符以检验不符合子查询中过滤条件的account表中所有数据。

除了在select语句中使用关联子查询，我们也可以在update，insert和delete语句中使用它来操作数据。

UPDATE account a
SET a.last_activity_date =
(SELECT MAX(t.txn_date)
FROM transaction t
WHERE t.account_id = a.account_id)
WHERE EXISTS (SELECT 1
FROM transaction t
WHERE t.account_id = a.account_id);

由于set子句的子查询仅当update语句中where子句为真才执行，这里的where exists语句确保了只有在每个账户都发生过交易的情况下才会进行修改操作（否则max的值将为null.).这也是实际操作中需要注意的点，记得随时问问自己：如果缺失了怎么办？ 

 也可以在delete语句中使用：

DELETE FROM department
WHERE NOT EXISTS (SELECT 1
FROM employee
WHERE employee.dept_id = department.dept_id);

        注意！在MySQL中的delete语句中使用关联子查询时，无论如何都不能使用表别名！别问我为什么，不行就是了QAQ

何时使用子查询？

1.作为数据源，将子查询的结果集作为一张表放在from子句中

     ！ 注意：from子句中的子查询必须是非关联子查询。他们首先被执行，然后一直保留与内存之中直到查询执行完毕。

2.数据加工

SELECT g.name,COUNT(*) num_customers
FROM 
    (SELECT SUM(a.avail_balance) cust_balance 
    FRom account a INNER JOIN product p
    ON a.product_cd = p.product_cd
    WHERE p.product_type_cd = 'ACCOUNT'
    GROUP BY a.cust_id) cust_rollup
INNER JOIN
    (SELECT 'Small Fry' name,0 low_limit,4999.99 high_limit
    UNION ALL
    SELECT 'Average Joes' name,5000 low_limit,9999.99 high_limit
    UNION ALL
    SELECT 'Heavy Hitters' name,10000 low_limit,999999.99 high_limit) g
ON cust_rollup.cust_balance 
BETWEEN g.low_limit AND g.high_limit
GROUP BY g.name;

我也不知道为什么两个子查询都可以运行但是整个查询不能运行，提示的错误是: 

 反复检查没有发现错误啊。——————>反复更改表的别名之后发现给第二个表的别名改成g就可以了，group和groups都不能做它的别名，真是岂有此理，不过我记住了……和电脑是没有道理可讲的【苦涩】。

以下为连接情况+分组依据+最终结果：

（count函数和sum函数的使用又多了一次，经验+，开心~） 

哎  面向实习面试的MySQL学习感觉暂时还用不到这些 感觉太实战了哈哈

慢慢学到时候就会了  暂时先停下来吧~这里未完待续~

面向任务的子查询

过滤条件中的子查询

子查询作为表达式生成器

子查询小结