结构化查询语言之 SQL 嵌套子查询(以Mysql为例)

@[TOC]

查询使用的数据库文件下载

  SQL 提供嵌套子查询机制：子查询是嵌套在另一个查询中的select-from-where表达式。子查询嵌套在where子句中，通常用于对集合的成员资格、集合的比较及集合的基数进行检查；子查询也可以嵌套from子句中；除此之外，还有一类子查询是标量子查询。

1. 集合成员资格

• SQL允许测试元祖在关系中的成员资格：

  • 连接词in测试元祖是否是集合中的成员；
  • 连接词not in测试元祖是否不是集合中的成员；

• 例子：

  • 找出2009年秋季和2010年春季学期同时开课的所有课程(交运算)

    • select distinct course_id
      from section
      where semester='Fall' and year=2009 and
      	course_id in (select course_id
      						 from section
      			  			 where semeter='Spring' and year=2010);
      

  • 找出2009年秋季开课，而不在2010年春季学期开课的所有课程(差运算)

    • select distinct course_id
      from section
      where semester='Fall' and year=2009 and
      course_id not in (select course_id
      				  	  		from section
      			  	     		where semeter='Spring' and year=2010)
      

  • 找出选修了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)
    ```
    ![在这里插入图片描述](https://img-blog.csdnimg.cn/20210409222802793.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0OTkyNTU5,size_16,color_FFFFFF,t_70)
  

• in与not in也可用于枚举集合:

  • 找出既不叫"Mozart"，也不叫"Einstein"的教师的姓名；

    select distinct name 
    from instructor 
    where name not in ('Mozart', 'Einstein')
    

    

2. 集合的比较

• >some、<some、<=some、>=some、=some(等价于in)、<>(!=)some(不等价于not in)

• >all、<all、<=all、>=all、=all(不等价于in)、<>(!=)all(等价于not in)

• 例子：

  • 找出工资至少比Biology系某一教师的工资高的所有教师姓名(更名运算也可)

    select name
    from instructor
    where salary>some(select salary
    				  from instructor
    				  where dept_name = 'Biology');
    

    

  • 找出工资比Biology系任一教师的工资都高的所有教师姓名

    select name
    from instructor
    where salary>all(select salary
    				  from instructor
    				  where dept_name = 'Biology');
    

    

  • 找出平均工资最高的系

    select dept_name
    from instructor
    group by dept_name
    having avg(salary)>=all(select avg(salary)
    				  	   from instructor
    				  	   group by dept_name);
    

    

3. 空关系测试：exist、not exist

• exists 在作为参数的子查询非空时返回true值：

  • 例子：找出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
    )
    

  

  • 来自外层查询的一个相关名称可以用在where子句的子查询中，使用了来自外层查询相关名称的子查询被称作相关子查询(correlated sub_query)，相关子查询的查询条件依赖于父查询；
    • 相关子查询的理解过程：
      • 取外层查询中表的第一个元祖，根据它与内层查询相关的属性值处理内层查询，假设第一个元祖的课程ID为CS-111，我们在子查询中查询的就是2010年春CS-111课程是否开课，开课的话，外层查询的where子句的第三个条件为true，根据前两个条件，即该门课2009年秋是否开课来决定是否放入最终查询结果。

• not exists 结构测试子查询结果集中是否不存在元祖，不存在则返回true：

  • 包含操作模拟：A包含B 即 not exists (B except A)，(except为差集)

  • 例子：找出选修了Biology系开设的所有课程的学生

    select S.ID，S.name
    from student as S
    where not exists(
    	(select course_id
    	from course
    	where dept_name = 'Biology')
    	except
    	(select course_id
    	from takes as T
    	where S.ID=T.ID
    	)
    )
    

    • select course_id from course where dept_name = 'Biology'查询的是Biology系开设的所有课程(B)，select course_id from takes as T where S.ID=T.ID查询的是当前学生选修的所有课程，若学生选修的所有课程(A)包括Biology系开设的所有课程，那么上述子查询的结果必然为空，那么where返回true；否则，where放回false。
    • 由于mysql无except操作数，故改为：

  select S.ID,S.name
    from student as S
    where not exists(
    	select course_id
    	from course
    	where dept_name = 'Biology'
    	and course_id not in
    	(select course_id
    	from takes as T
    	where S.ID=T.ID
    	)
    );
  

4. 重复元祖存在性测试

• unique(在空集上计算出真值)\not unique 测试子查询的结果是否存在重复元祖

  • 例子：找出所有在2009年最多开设一次的课程

    select T.course_id
    from course as T
    where unique (
    			  select R.course_id
    			  from section as R
    			  where T.course_id = R.course_id and R.year = 2009
    			  );
    

    

    mysql(5&8)似乎没有unique子查询，略过，可采用以下语句实现。

    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
    );
    

    

  • 例子：找出所有在2009年最少开设二次的课程

    select T.course_id
    from course as T
    where not unique (select R.course_id
    			  from section as R
    			  where T.course_id = R.course_id and R.year = 2009)
    );
    

    mysql应该还是不行，修改如下。

    select T.course_id
    from course as T
    where 2<=(
    	select count(R.course_id)
    	from section as R
    	where T.course_id = R.course_id and R.year = 2009
    );
    

    

5. from子句中的子查询

• 由于任何select-from-where表达式返回的结果都是关系，故可被插入到另一个select-from-where中任何关系可以出现的位置；

• 例子一：找出系平均工资超过42000美元的系与该系教师的平均工资

  select dept_name,avg_salary
  from (
  	(select dept_name,avg(salary) as avg_salary
  	from instructor
  	group by dept_name)
  	as dept_avg
  )
  where avg_salary>42000;
  

  

  • 上述查询使用as子句给子查询的结果关系起名

• 例子二：找出所有系中工资总额最大的系的工资总额

  select max(tot_salary)
  from (
  	select dept_name,sum(salary) tot_salary
  	from instructor
  	group by dept_name
  ) as dept_total;
  

  

6. with子句：mysql 8 之前不支持

• with子句提供定义临时关系的方法，这个定义只对包含with子句的查询有效

• Example：

  • 找出具有最大预算的系：(以下查询定义了临时关系max_budget)

  with max_budget as 
  (select max(budget) as value from department)
  select budget
  from department,max_budget
  where department.budget=max_budget.value;
  

  

  • mysql 5

  drop table if exists max_budget;
  create temporary table max_budget(value double)
  select max(budget) as value from department;
  select budget
  from department,max_budget
  where department.budget=max_budget.value;
  

  

• 找出系工资总额大于所有系平均工资总额的系：

  with dept_total as(select dept_name,sum(salary) as value
  from instructor
  group by dept_name
  ),
  dept_total_avg as(
  select avg(value) as value
  from dept_total
  )
  select dept_name
  from dept_total,dept_total_avg
  where dept_total.value>=dept_total_avg.value;
  

  

  • mysql 5

    drop table if exists dept_total;
    create temporary table dept_total(dept_name varchar(12), value double)
    select dept_name,sum(salary) as value
    from instructor
    group by dept_name;
    drop table if exists dept_total_avg;
    create temporary table dept_total_avg(value double)
    select avg(value) as value
    from dept_total;
    select dept_name
    from dept_total,dept_total_avg
    where dept_total.value>=dept_total_avg.value;
    -- select * from dept_total;
    -- select * from dept_total_avg;
    

    

7. 标量子查询

• SQL 允许只返回包含单个属性的单个元祖的子查询出现在返回单个值的表达式的能够出现的任何地方，这样的子查询称为标量子查询(scalar sub_query)；

• Example: 列出所有的系以及它们拥有的教师数

  select dept_name,
  	(select count(*)
  	from instructor
  	where department.dept_name = instructor.dept_name)
  	as num_instructors
  from department;
  

  

References: [1] Abraham Silberschatz, Henry F Korth, S Sudarshan． Database System Concepts. New York: McGraw-Hill, 2010 Database System Concepts