MySQL 连接查询、联合查询、子查询

1. MySQL 连接和联合；

• 连接的概念

SELECT cname, tname FROM categories, titles 
WHERE categories.cid = titles.cid AND categories.cname = 'Current Affairs';

• 连接的类型：交叉连接、内连接、外连接、自连接
• 连接的关键字：JOIN

1.1 交叉连接；

# 最简单的连接方式，不带 `WHERE` 语句
// 演示表
SELECT * FROM categories;
SELECT * FROM titles;
# 连接操作：
SELECT * FROM categories, titles;
# 或者
SELECT * FROM categories CROSS JOIN titles;

1.2 内连接；

# 带 `WHERE` 语句，左右表可以交换顺序
# INNER JOIN 可以用逗号代替
SELECT cname, tname FROM categories INNER JOIN titles 
WHERE categories.cid = titles.cid;

1.3 外连接；

# 不对称，也分左连接和右连接
// 演示表
SELECT * FROM users;			// uid, name
SELECT * FROM groups;			// gid, name
SELECT * FROM user_groups;		// uid, gid

# 首先用“内连接”来连接三个表的内容（users 的名字和 groups 的名字联系起来）
SELECT users.name AS uname, groups.name AS gname FROM users, groups, user_groups 
WHERE users.uid = users_groups.uid AND groups.gid = users_groups.gid;
# 外连接保证一个表的信息全部完整，然后每一行再对应另一个表对应的信息
# LEFT JOIN 和 RIGHT JOIN 必须写

# 左链接
SELECT * FROM users LEFT JOIN users_group ON users.uid = users.group.uid 
WHERE user_groups.gid IS NULL;	// uid, name, uid, gid

# 右连接
# USING 可以直接带上列名
SELECT * FROM user_groups RIGHT JOIN groups USING(gid);	// gid, name, uid 

1.4 自连接；

# 和自身这张表做连接
// 演示表
SELECT * FROM menu;	// id, label, parent
# 自连接把一个表看成两个表（分别是 a 和 b），然后对它们做内连接，达到了一个自连接的效果
SELECT a.label AS parent_label, b.label AS child_label FROM menu AS a, menu AS b 
WHERE a.id = b.parent;	// parent_label, child_label

1.5. 联合；

# 关键字是 `UNION`
// SELECT * FROM exchangeA;		// symbol, price
// SELECT * FROM exchangeB;		// symbol, price
SELECT symbol, price FROM exchangeA WHERE price > 35 UNION SELECT symbol, price FROM exchangeB 
WHERE price BETWEEN 40.00 AND 90.00;
# UNION 自带 DISTRICT 作用，同样的结果只显示一次
# 如果需要带出重复的数据，使用 UNION ALL

2. MySQL 子查询。

• 子查询概念：在通常的查询中，需要使用 WHERE 语句和 HAVING 语句对结果进行一些过滤和限制。
• 通常过滤的条件都是针对一个固定的常数，比如列出所有年级大于 40 岁的用户（固定常数 40）。但是在实际的生产环境中，查询数据的限制条件也是查询出来的结果，是动态变化的。所以就要用到 MySQL 的子查询。
• 子查询就是一个 SELECT 查询语句是另一个 SELECT 查询语句的附属。它的查询结果是根据另一个语句的查询结果查询的。这种嵌套关系可以是多个，这样就可以同时执行一连串指令，而不需要分成多个独立的查询语句。执行顺序是从内层开始执行。
• 运用子查询需要注意语句的性能。在数据表很大的时候，子查询会影响性能。在生产环境中会使用连接、联合等方式来代替子查询。

// 演示表
SELECT * FROM services;			// 服务表，字段：sid, sname, sfee
SELECT * FROM clients;			// 用户表，字段：cid, cname
SELECT * FROM branches;			// 分支机构表，字段：bid, cid, bdesc, bloc
SELECT * FROM branches_service;	// 分支服务对应表，字段：bid, sid

# 内连接查询
SELECT bdesc FROM branches, clients 
WHERE branches.cid = clients.cid AND clients.cname = 'Rabbit';
# 子查询（和上面得到的结果一样）
SELECT bdesc FROM branches WHERE cid = (SELECT cid FROM clients WHERE cname = 'Rabbit');

# 子查询常见使用方式 1
// 查询每一个用户对应的 branch 的个数
SELECT cid, COUNT(bid) FROM branches GROUP BY cid;
// 追加条件，branch 的个数为 2
SELECT cid, COUNT(bid) FROM branches GROUP BY cid HAVING COUNT(bid) = 2;	// 返回 cid = 104
// 查找用户名字
SELECT cname FROM clients WHERE cid = 104;
// 以上用了 2 句 SQL 语句查找到了用户的名字
// 下面用子查询实现一句 SQL 语句查询结果（使用 WHERE 和 HAVING 关键词组合）
SELECT cname FROM clients WHERE cid = (SELECT cid FROM branches GROUP BY cid HAVING COUNT(bid) = 2);

# 子查询常见使用方式 2
// 普通查询
SELECT sid FROM branches_services WHERE bid = 1031;	// 返回：sid 为 2、3、4
SELECT sname FROM services WHERE sid = 2;
SELECT sname FROM services WHERE sid = 3;
SELECT sname FROM services WHERE sid = 4;

// 子查询（使用关键字 IN）
SELECT sname FROM services WHERE sid IN (SELECT sid FROM branches_services WHERE bid = 1031);

# 子查询常见使用方式 3
// 子查询（使用关键字 EXISTS）
// EXISTS 判断括号里面的语句是否成立，成立再执行外面的语句
SELECT * FROM clients WHERE EXISTS 
(SELECT bid FROM branches_services GROUP BY bid HAVING COUNT(bid) >= 4);

# 子查询常见使用方式 4
// 查询分支机构使用服务的平均数
SELECT bid, COUNT(sid) AS stotal FROM branches_services GROUP BY bid;
SELECT AVG(s.stotal) FROM (SELECT bid, COUNT(sid) AS stotal FROM branches_services GROUP BY bid) AS s;

# 子查询常见使用方式 5
// 用一个左链接得到 branches 里面没有登记的客户
SELECT cname FROM clients LEFT JOIN branches ON clients.cid = branches.cid WHERE branches.bid IS NULL;
// 同样可以用子查询实现
SELECT cname FROM clients WHERE cid = 
	(SELECT cid FROM branches RIGHT JOIN clients 
	ON clients.cid = branches.cid WHERE branches.cid IS NULL);

# 子查询常见使用方式 6
// 给指定服务涨价
SELECT sid FROM branch_services GROUP BY sid HAVING COUNT(bid) >= 3;	// 返回：sid 为 1、3、4
UPDATE services SET sfree = sfree * 1.25 WHERE cid = 1;
UPDATE services SET sfree = sfree * 1.25 WHERE cid = 3;
UPDATE services SET sfree = sfree * 1.25 WHERE cid = 4;

// 子查询实现
UPDATE services SET sfree = sfree * 1.25 WHERE sid 
	IN (SELECT sid FROM branch_services GROUP BY sid HAVING COUNT(bid) >= 3);