MySQL 语法整理（2）

接着上一篇博客，我们继续来学习MySQL的语法。本篇重点讲多表查询，包括嵌套多层的查询，大家做好心里准备哈！

废话不多说，我们先来讲第一个语句，union：将查询结果合并在一起并去重。前面我们学了自然连接，是横向增加表的键，那么这个union就是纵向增加结果表的记录。

/*比如我想查询2009年秋季和2010年春季开课的course_id，以及该课上课的房间号*/
(
	SELECT course_id , room_number
	FROM section
	WHERE semester='Fall' AND YEAR = 2009
)
UNION -- union会自动帮我们的结果去重
(
	SELECT course_id , room_number
	FROM section
	WHERE semester = 'Spring' AND YEAR = 2010
);

/*将结果合并在一起，但是不去重*/
(
	SELECT course_id
	FROM section
	WHERE semester='Fall' AND YEAR=2009
)
UNION ALL
(
	SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR=2010
);

union查询结果如图

上述我们是找到2009年秋季和2010年春季开课的课程，其实实际是对两个查询结果进行了集合并运算。那假如我现在想找出既在2009年秋季开课，同时也在2010年春季的课程呢？可以发现，我这次是对两个查询结果做了集合交运算。很可惜，MySQL没有一个单词或者语句来直接实现这个操作，但是我们可以通过where条件筛选来实现

/*通过对两个表where来实现集合交运算*/
SELECT T.course_id
FROM
	(SELECT course_id
	FROM section
	WHERE  semester='Fall' AND YEAR = 2009
	) AS T , -- T表储存的是2009年秋季开课的course_id
	(SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR = 2010
	) AS S -- S表储存的是2010年春季开课的course_id
WHERE T.course_id = S.course_id;  -- 这里的T中course_id会逐个与S中的course_id进行比较，相等的话就保存到
-- 结果表中

这就是集合交运算，那么同理啊，集合差运算也是类似

/*通过where中not in 操作实现集合差*/
/*这里我们想找出只在2009年秋季开课而不在2010年春季开课的课程*/
SELECT T.course_id
FROM
	(SELECT course_id
	FROM section
	WHERE semester='Fall' AND YEAR = 2009) AS T
WHERE T.course_id NOT IN -- 这里用到了not in
	(SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR = 2010);

/*主意啊，上述两个表的顺序一但调换，结果是不同的！！大家可以自己理解一下为什么不同*/
/*这里我们想找出只在2010年春季开课而不在2009年秋季开课的课程*/
SELECT S.course_id
FROM
	(SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR = 2010) AS S
WHERE S.course_id NOT IN
	(SELECT course_id
	FROM section
	WHERE semester='Fall' AND YEAR = 2009);

查询方法与上面类似，当T集合的一条记录在S集合中没有出现，此时就会返回真，并把该记录储存到结果表中。

接下来我们来学一下一些常用函数，这些也为我们后续学习做好基础

/*利用avg或者其他函数求值*/
SELECT AVG(salary) AS avg_salary
FROM instructor
WHERE dept_name = 'Comp. Sci.';

/*通常利用这个来查找表中有多少行*/
SELECT COUNT(*) AS number
 FROM course;

/*还有像max()还有min()函数大家可以自行去练习，这里我就不举例子了*/

学习这些函数有什么作用呢？很大程度是为我们下面要学的命令做铺垫。group by：顾名思义，就是将表的各种记录分组。

接下来给大家举个例子，让大家对group by有个更好的掌握

 /*统计一下2010年春季有各个系的老师有多少人开课*/
 /*group by 后面接的select语句语法比较严格，需要谨慎，一般接函数或者分组的键，比如这里的dept_name*/
SELECT dept_name , COUNT(DISTINCT instructor.ID) AS inst_count
FROM instructor , teaches
WHERE instructor.ID = teaches.ID AND -- 第一个=是找出某个老师上的课的记录
	semester = 'Spring' AND  -- 第二个=是在上一个=的基础上，找出春季开课，也就是找出该老师春季教的课
	YEAR = 2010  -- 第三个=在上面的结果中，找出是2010年开的课，可以发现三个=其实一直在做集合交运算
GROUP BY dept_name; -- 注意group by是在where后面的，对结果表进行分组

这是最终的查询结果，可以发现，每一个分组，就只有一个记录，因此select最后必须有代表这个组的键

这个时候给大家一个问题：我能不能select这些组中的name键，如下代码所示：

SELECT dept_name , NAME -- 这样做的意义是什么？
FROM instructor , teaches
WHERE instructor.ID = teaches.ID AND
	semester = 'Spring' AND
	YEAR = 2010
GROUP BY dept_name;

事实上我的MySQL是可以执行的，但是这个语句是错误的，大家可以看到，最后select是对不同组的表选择键，比如我可以选择各个组表的dept_name，或者各个组表的平均工资等等。但是要选择老师名字是什么意思？比如计算机系中有三个老师，我该选择哪一个老师的名字呢？可以发现是毫无意义的查询，在Oracle或者其他数据库中这个语句是会报错的！因此：group by 后面接的select语句语法比较严格，需要谨慎，一般接函数或者分组的键，比如这里的dept_name。

上述我们学习了group by语句，那假如我想找出系平均工资大于42000的系呢？这个时候，having语句可以很好的帮我们解决这个问题。having：对gruop by的结果再进行选择

SELECT dept_name,AVG(salary) AS avg_salary
 FROM instructor
 GROUP BY dept_name
 HAVING AVG(salary) > 42000; -- 这里是筛选group by之后的结果

学到这里其实我们已经可以做很多查询了，接下来我会给大家一个复杂点的例子。

 /*求出2009年两个学期中有超过两个学生去学习的课程的平均学分*/
 SELECT course_id,semester,YEAR,sec_id,AVG(tot_cred)
-- FROM takes,student
-- WHERE takes.ID = student.ID AND YEAR = 2009
FROM takes NATURAL JOIN student -- 上面的takes.ID = student.ID相当于NATURAL JOIN，表示找出学生上过的课的记录
WHERE YEAR = 2009
/*这里按如下顺序进行分组*/
GROUP BY course_id,semester,YEAR,sec_id -- 比如当course_id组中按semester又分多个组，如此继续分。
-- 这里给大家思考，如果按主键分组，我们最后可以得到什么，其实就是得到原表的每一条记录！
HAVING COUNT(takes.ID)>=2;

查询结果如图

limit：限定查询结果的记录数目，通常配合一些函数。

/*limit语句用于筛选一定数目的记录，当实际记录少于限定的记录也没关系*/
/*这里找出平均工资前二的系名和该系的平均工资*/
SELECT dept_name,AVG(salary) AS avg_salary
FROM instructor
GROUP BY dept_name
ORDER BY avg_salary DESC
LIMIT 2; -- limit是对结果表的记录进行限制，所以放在一定放在where，group by后面
/*
LIMIT 0,5 -- 表示显示第一到第五条的记录，注意记录是从0下表开始算的。

group by + with rollup：表示在进行分组统计的基础上再次对每一小组进行汇总统计

/*汇总一般是求一个sum*/
SELECT dept_name , SUM(salary) , COUNT(*)
FROM instructor
GROUP BY dept_name WITH ROLLUP;

查询记录如图，最后那一条就是with rollup的字段，但是可以发现，对于无法统计的键dept_name是空值

本篇已经讲解了很多内容，包括集合交，集合差以及一些函数和group by的命令。下一篇博客将继续讲解更多多表查询的语法和命令，包括嵌套使用where和from。