SQL学习指南第三篇
分组与聚集
分组概念
需要清楚的是分组不是去重,而是将查询结果按分组字段相同的值进行分组。
例如:
SELECT open_emp_id, COUNT(*) how_many FROM account GROUP BY open_emp_id;
聚集函数
聚集函数对每个分组的所有行执行特定的操作。聚集函数可以在 select 子句,having 子句中出现。注意:where子句不能包含聚集函数,因为where子句是在分组之前被评估的。
下面是一些通用的聚集函数:
- Max(): 返回集合中的最大值
- Min(): 返回集合中的最小值
- Avg(): 返回集合中的平均值
- Sum(): 返回集合中所有值的和
- Count(): 返回集合中值的个数。count(*) 表示对行的数目进行计数,而 count(列名) 表示对这个列所包含的值的数目进行计数,并且忽略所有遇到的 null 值。
例如:
SELECT MAX(avail_balance), COUNT(*) num_accounts FROM account WHERE product_cd = 'CHK';
-- 这里没有使用 group by 进行分组,因此是个隐式分组
除了使用列作为聚集函数的参数外,还可以创建表达式作为参数。
例如:
SELECT MAX(pending_balance - avail_balance) max_uncleared FROM account;
产生分组
- 对单列分组
- 对多列分组
- 利用表达式分组。例如:根据职员入职年份对职员分组。
- 产生合计数(在分组统计数据的基础上再进行统计汇总,使用 with rollup 和 with cube)。
GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在 SELECT 中使用表达式,则必须在 GROUP BY子句中指定相同的表达式,不能使用别名。
SELECT product_cd, SUM(avail_balance) AS prod_balance FROM account GROUP BY product_cd;
SELECT product_cd, open_branch_id, SUM(avail_balance) AS prod_balance FROM account GROUP BY product_cd, open_branch_id;
SELECT EXTRACT(YEAR FROM start_date) AS year, COUNT(*) AS how_many FROM employee GROUP BY EXTRACT(YEAR FROM start_date);
SELECT product_cd, open_branch_id, SUM(avail_balance) AS tot_balance FROM account GROUP BY product_cd, open_branch_id WITH ROLLUP;
SELECT product_cd, SUM(avail_balance) AS prod_balance FROM account WHERE status = 'ACTIVE'
GROUP BY product_cd HAVING MIN(avail_balance) >= 1000 AND MAX(avail_balance) <= 10000;
子查询
子查询是指包含在另一个 SQL 语句(包含语句)内部的查询。子查询总是由括号包围,并且通常在包含语句之前执行。任何子查询返回的数据在包含语句完成后都会被丢弃,这使子查询像一个具有作用域的临时表(这就意味着服务器在 SQL 语句执行结束后将清空子查询结果所占的内存)。子查询除了可以按照结果集类型(单行单列,单行多列,多行多列)分类外,还可以另外一个因素划分子查询:一些子查询完全独立,即可以单独执行而不需要引用包含语句中的任何内容(称为非关联子查询),其他的则引用包含语句中的列(称为关联子查询)。
非关联子查询
单行单列子查询
如果子查询返回的是一个单行单列的表,则称为标量子查询,并且可以位于常用运算符(=,<>, <, >, <=, >=)的任意一边。
多行单列子查询
返回多行结果的子查询使用以下4个运算符来构建条件。
in 和 not in 运算符
in 运算符被用于查看是否能在一个表达式集合中找到某一个表达式。
例如:
SELECT emp_id, fname, lname, title FROM employee WHERE emp_id IN (SELECT superior_emp_id FROM employee);
SELECT emp_id, fname, lname, title FROM employee WHERE emp_id NOT IN (SELECT superior_emp_id FROM employee WHERE superior_emp_id IS NOT NULL);
--这里还添加了一个过滤条件以确保 null 值不会出现在子查询的返回表中
all 运算符
all 运算符用于将某单值与集合中的每个值进行比较,构建这样的条件需要将其中一个比较运算符(=, <>, <, >)与 all 运算符配合使用。
SELECT emp_id, fname, lname, title FROM employee WHERE emp_id <> ALL
(SELECT superior_emp_id FROM employee WHERE superior_emp_id IS NOT NULL);
--这个查询和上一个例子的结果相同
SELECT account_id, cust_id, product_cd, avail_balance FROM account WHERE avail_balance < ALL
(SELECT a.avail_balance FROM account a INNER JOIN individual i ON a.cust_id = i.cust_id WHERE i.fname = 'Frank' AND i.lname = 'Tucker');
当使用 not in 或 <>运算符比较一个值和一个值集时,必须确保值集中不包含 null 值。
any运算符
与 all 运算符一样,any 运算符允许将一个值与值集中每个成员相比较。与 all 不同的是,使用 any 运算符时,只要有一个比较成立,则条件为真;使用 all 运算符时,只有与集合中的所有成员比较都成立时条件才为真。
多行多列子查询
过滤条件中必须将这些列用括号括起来,并且排列顺序与子查询结果的顺序相同。例如我们想检索出所有 Woburn 分行柜台员开立的账户:
SELECT account_id, product_cd, cust_id FROM account WHERE (open_branch_id, open_emp_id) IN
(SELECT b.branch_id, e.emp_id FROM branch AS b INNER JOIN employee AS e ON b.branch_id = e.assigned_branch_id
WHERE b.name = 'Woburn Branch' AND (e.title = 'Teller' OR e.title = 'Head Teller'));
--这里也可以用多表连接
SELECT a.account_id, a.product_cd, a.cust_id FROM account a
INNER JOIN employee e ON a.open_emp_id = e.emp_id
INNER JOIN branch b ON e.assigned_branch_id = b.branch_id
WHERE (e.title = 'Teller' OR e.title = 'Head Teller') AND b.name = 'Woburn Branch';
--还是感觉用多表连接舒服...
关联子查询
关联子查询依附于包含语句并引用其一列或者多列。与非关联子查询不同,关联子查询不是在包含语句执行之前一次执行完毕,而是为每一个候选行执行一次。例如,下面的查询首先利用关联查询计算每个客户的账户数,接着包含查询检索出那些拥有两个账户的客户:
SELECT c.cust_id, c.cust_type_cd, c.city FROM customer AS c
WHERE 2 = (SELECT COUNT(*) FROM account AS a WHERE a.cust_id = c.cust_id);
除了等式条件,关联子查询还可以用于其他类型的条件,比如下面的范围条件:
SELECT c.cust_id, c.cust_type_cd, c.city FROM customer AS c WHERE
(SELECT SUM(a.avail_balance) FROM account AS a WHERE a.cust_id = c.cust_id) BETWEEN 5000 AND 10000;
exists 运算符
若只关心存在关系而不在乎数量,可以使用 exists 运算符。例如,下面的查询就是检索在特定日期进行过交易的所有账户,并不关心到底进行了多少次交易:
SELECT a.account_id, a.product_cd, a.cust_id, a.avail_balance FROM account AS a WHERE EXISTS
(SELECT 1 FROM transaction AS t WHERE t.account_id = a.account_id AND t.txn_date = '2008-09-22');
使用 exists 运算符时,子查询可能会返回 0,1 或者多行结果,然而条件只是简单地检查子查询能否返回至少1行。这是因为包含查询的条件只需要知道子查询返回的结果是多少行,而与结果的确切内容无关,这里的惯例是 select 1 或者 select * 。与 exists 运算符相反的就是 not exists 运算符了。
子查询除了用在 select 语句,也大量应用于 update, delete 和 insert 语句,并且关联子查询也会频繁出现于 update 和 delete 语句中。
例如,下面的语句用于更新产生过交易的每个账户的最新交易日期:
UPDATE account AS a SET a.last_activity_date =
(SELECT MAX(t.txn_date) FROM transaction AS t WHERE t.account_id = a.account_id)
WHERE EXISTS (SELECT 1 FROM transaction AS t WHERE t.account_id = a.account_id);
--这里使用了两个关联子查询,set 子句的子查询仅当 update 语句中 where 子句为真时才执行,这样就可以保护 last_activity_date 列不被 null 重写。
DELETE FROM department WHERE NOT EXIST
(SELECT 1 FROM employee WHERE employee.dept_id = department.dept_id);
切记,在 MySQL 中 delete 语句使用关联子查询时,无论如何都不能使用表别名,不过,在多数其他数据库服务器中,是可以使用表别名的。
何时使用子查询
--子查询作为数据源
SELECT d.dept_id, d.name, e_cnt.how_many AS num_employees FROM department AS d INNER JOIN
(SELECT dep_id, COUNT(*) AS how_many FROM employee GROUP BY dept_id) e_cnt ON d.dept_id = e_cnt.dept_id;
--过滤条件中的子查询:查找开户最多的雇员。过滤条件中的子查询除了可以出现在 where 子句中,还可以出现在 having 子句中。
SELECT open_emp_id, COUNT(*) AS how_many FROM account GROUP BY open_emp_id HAVING COUNT(*) =
(SELECT MAX(emp_cnt.how_many) FROM (SELECT COUNT(*) AS how_many FROM account GROUP BY open_emp_id) emp_cnt);
/*子查询作为表达式生成器:除了用于过滤条件,标量子查询还能用在表达式可以出现的任何位置,其中包括查询中的 select 和 order by 子句以及 insert 语句中的 values 子句。*/
SELECT emp.emp_id, CONCAT(emp.fname, ' ', emp.lname) AS emp_name,
(SELECT CONCAT(boss.fname, ' ', boss.lname) FROM employee AS boss
WHERE boss.emp_id = emp.superior_emp_id) AS boss_name
FROM employee AS emp WHERE emp.superior_emp_id IS NOT NULL ORDER BY
(SELECT boss.lname FROM employee AS boss WHERE boss.emp_id = emp.superior_emp_id), emp.lname;
--用非关联标量子查询为 insert 子句生成值
INSERT INTO account (account_id, product_cd, cust_id, open_date, last_activity_date, status, open_branch_id, open_emp_id, avail_balance, pending_balance)
VALUES (NULL,
SELECT product_cd FROM product WHERE name = 'savings account'),
SELECT cust_id FROM customer WHERE fed_id = '555-55-5555'),
'2008-09-25', '2008-09-25', 'ACTIVE',
(SELECT branch_id FROM branch WHERE name = 'Quincy Branch'),
(SELECT emp_id FROM employee WHERE lname = 'Portman' AND fname = 'Frank'),
0, 0);
使用单一 SQL 语句可以在 account 表里创建一行,同时查询 4 个外键值。不过,这种方法有一个缺点,就是当插入的列允许 null 值时,即使子查询不能返回值,insert 语句也会成功。例如,如果第四个子查询的 Frank Portman 的名字拼写错误,account 表中仍然会创建一个新行,但此时 open_emp_id 列的值被置为了 null 。