MySQL 语法整理（3）

继续接着上一篇博客，今天终于终于要讲比较复杂的多表查询啦！

in：查询表的元素是否在另一个表中。听着是不是觉得很简单，我们来看一个例子

/*我想找出满足semester='Fall' AND YEAR = 2009并且semester='Spring' AND YEAR=2010的课程id
此时，我们会想起上一篇博客中讲到的集合交运算，这里in同样可以实现*/
SELECT DISTINCT course_id
 FROM section
 WHERE semester='Fall' AND YEAR = 2009 AND
 course_id IN 
	(SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR=2010);

当然，类似于集合交的操作，我们也可以用另一种写法来实现

/*这是另一种写法，先构造两个表，然后再判断course_id是否相等*/
SELECT DISTINCT A.course_id
FROM (SELECT DISTINCT course_id
	FROM section
	WHERE semester='Fall' AND YEAR = 2009
	) AS A ,
	(SELECT course_id
	FROM section
	WHERE semester='Spring' AND YEAR=2010
	) AS B
WHERE A.course_id = B.course_id;

not in：查询表的元素是否不在另一个表中。与in类似，不过恰恰相反。

/*not in 和 in 类似，当不在就返回true*/
SELECT DISTINCT course_id
 FROM section
 WHERE semester='Fall' AND YEAR=2009 AND
 course_id NOT IN (
 SELECT course_id
FROM section
WHERE semester='Spring' AND YEAR=2010);

/*这里是另一种做法，比较复杂。首先先把2009年秋季并且2010春季也开课的课程找出来，
然后在把2009年秋季的课程找出不在2009年秋，2010春季的课程*/
SELECT B.course_id
FROM(
	SELECT DISTINCT course_id
	FROM section
	WHERE semester='Fall' AND YEAR = 2009 AND
	course_id IN (SELECT course_id
	FROM section
	WHERE semester = 'Spring' AND YEAR = 2010)
	) AS A , section AS B
WHERE B.semester='Fall' AND B.YEAR = 2009 AND B.course_id != A.course_id;

刚刚我们只是一个键进行in 或者 not in 操作，如果多个键值进行时，就要加个括号。

/*这是多个值进行比较，比如主键是多个键的时候，就要这样*/
/*查询的是id为'10101'的老师所教的课程数*/
SELECT COUNT(DISTINCT ID)
 FROM takes
 WHERE (course_id,sec_id,semester,YEAR) IN -- 这里当四个键值都相等时后才会把该记录放到结果表中
	(SELECT course_id,sec_id,semester,YEAR
	FROM teaches
	WHERE teaches.ID='10101');

some表示只要一个为真即可，相当于or。比如两个表进行比较，只要A表的某个记录对任意一个B表的记录满足条件，那么就将A表该记录保存到结果表中。

/*这里是找出只要比计算机系最低工资高的老师的名字*/
SELECT NAME
 FROM instructor
 WHERE salary > SOME -- 因为最低工资的老师的工资不会满足大于条件，所以会被排除
	(SELECT salary
	FROM instructor
	WHERE dept_name='Comp. Sci.');

all：相当于and，要全为真才行。

/*这里意思是找出比计算机系所有老师的工资都要高的老师的名字*/
SELECT NAME
 FROM instructor
 WHERE salary > ALL 
	(SELECT salary
	FROM instructor
	WHERE dept_name='Comp. Sci.');

我们可以结合之前学习的分组group by 和having来进行一些组合查询。这里给几个例子：

/*这里要查询哪个系的平均工资最高*/
/*这里由于要各个系比较，因此要用到group by，值得注意，>= 不能换成 >
因为假设某个系是最大的，最大的总不能比自己还要大吧！*/
SELECT dept_name
 FROM instructor
 GROUP BY dept_name
 HAVING AVG(salary) >= ALL 
	(SELECT AVG(salary)
	FROM instructor
	GROUP BY dept_name);

回顾刚刚的几个查询，我们发现内部的查询并没有依赖外部的查询，内部的查询是独立的，即执行顺序为由内到外。接下来我们要将的查询语句将依赖外部的查询，其执行顺序是由外到内。

exists：与in类似，但是不同在于，exists里面的不是独立查询，要依赖外面的查询。

/*这里想找出即在2009年秋季，也在2010年春季开课的课程*/
SELECT course_id
 FROM section AS S
 WHERE semester='Fall' AND YEAR=2009 AND
 EXISTS (SELECT *
	FROM section AS T
	WHERE semester='Spring' AND YEAR=2010 AND
	S.course_id = T.course_id); -- 这一步要依赖外部的S表

接下来给大家一个比较难的例子，去感受一下exists和not exists的具体用法。

/*关系代数中的除法运算：给定两个表A（1，2，3），B（1，2），由于B中的元素
A都有，因此A除B是为真的。说白了，A集合包含B集合，那么A除B就为真
反之，B除A为假，因为B没有包含A集合的全部元素*/
SELECT S.ID,S.name
 FROM student AS S -- 这里先吧student表改名成S
 WHERE NOT EXISTS
	(SELECT *
	FROM course C
	WHERE dept_name='Biology' -- 这里先把生物课程先选出来
	AND NOT EXISTS -- exits的作用可以看成把C中的记录一个一个放到下面判断
		(SELECT *
		FROM takes AS T 
		WHERE S.ID = T.ID -- 到这里筛选出一个学生选的所有课
		AND C.course_id = T.course_id)-- 这里是确定生物课是否都被学生选
		);

/*可以通过课程数小于等于1找出在2009年没开或者只开一次课的课程*/
SELECT T.course_id
 FROM course AS T
 WHERE 1 >= (SELECT COUNT(R.course_id)
	FROM section AS R
	WHERE T.course_id = R.course_id AND
	R.year=2009); 

/*需要把子查询结果重新命名，就可以不用加引用都能直接使用*/
SELECT dept_name,avg_salary
 FROM (SELECT dept_name,AVG(salary)-- as avg_salary
	FROM instructor
	GROUP BY dept_name)
	AS dept_avg(dept_name,avg_salary)
 WHERE avg_salary > 42000;

with：创建一个临时表。这个非常常用，可以避免多层查询而导致可读性降低。

 /*with运算符，创建一个临时表，格式with 表名（属性1 ， 属性2 ...） as （查询语句）*/
 WITH max_budget(VALUE) AS -- 赋值运算
	(SELECT MAX(budget)
	FROM department  -- 注意格式
	)
 SELECT budget
 FROM department,max_budget
 WHERE department.budget = max_budget.value;

/*本查询语句查询高于系平均工资的系，并列举出来*/
WITH dept_total(dept_name,VALUE) AS -- 第一个临时表
	(SELECT dept_name,SUM(salary)
	FROM instructor
	GROUP BY dept_name) , -- 注意格式
	dept_total_avg(VALUE) AS -- 第二个临时表
	(SELECT AVG(VALUE)
	FROM dept_total)
SELECT dept_name
FROM dept_total,dept_total_avg -- 在两个临时表上做查询
WHERE dept_total.value >= dept_total_avg.value;

这里说一下标量子查询：将查询语句放到select语句中。其实意义本人觉得不大。

/*标量子查询,特点select子句的查询记录一定要等于总select记录的数目
常见的是总select的元素作为select子句中where语句的关键词*/
SELECT dept_name,
	(SELECT COUNT(*) -- select子句的结果作为一个属性
	FROM instructor
	WHERE department.dept_name = instructor.dept_name)
	AS num_instructors
FROM department;