2、复杂查询与视图.md

### 复杂查询与视图

#### 子查询

为什么需要子查询？

* 集合成员资格（关系代数思想，**(NOT) IN-子查询**）

  * 某一元素是否是某一个集合的成员
* 集合之间的比较（关系演算的思想，以元组为单位，且集合没有元组存在和全部的概念，**$\theta$-Some/ $\theta$-All子查询**）
  * 某一个集合是否包含另一个集合等
* 集合基数的测试（关系演算的思想，以元组为单位，且关系代数没有真与假的概念，**(NOT) EXISTS子查询**）
  * 测试集合是否为空
  * 测试集合是否存在重复元组



相关性

* 非相关子查询

  * 内层查询独立进行，没有涉及任何外层查询相关信息的子查询

  ![在这里插入图片描述](https://img-blog.csdnimg.cn/20201127000040409.png#pic_center)


  

* 相关子查询

  * 内层查询需要依靠外层查询的某些参量作为限定条件才能进行的子查询
  * 外层向内层传递的参量需要使用外层的表名或表别名来限定
  * 注意：相关子查询只能由外层向内层传递参数，而不能反之；这也称为变量的作用域原则。

  ![在这里插入图片描述](https://img-blog.csdnimg.cn/20201127000054610.png#pic_center)






(NOT) IN子查询

* 基本语法：

  ```sql
  表达式 [not] in (子查询)
  ```

  * 语法中，表达式的最简单形式就是列名或常数。
  * 语义：判断某一表达式的值是否在子查询的结果中。

* 示例

  * 求既学过001号课程, 又学过002号课程的学生的学号

    ```sql
    Select S#   From  SC
    Where C# = ‘001’ and S# in( Select S# From SC  
                                Where C# = ‘002’ ) 
    ```

  * 列出没学过李明老师讲授课程的所有同学的姓名

    ```sql
    Select Sname From  Student 
    Where S# not in( Select  S# From  SC, Course  C, Teacher  T
                    Where T.Tname = ‘李明’  and  SC.C# = C.C# and  T.T# = C.T#  );`
    ```

    



$\theta$-some / $\theta$-all子查询

* 基本语法：

  * 表达式 $\theta$-some  (子查询)

   ![在这里插入图片描述](https://img-blog.csdnimg.cn/20201127000110928.png#pic_center)


  * 表达式 $\theta$-all (子查询)

    ![在这里插入图片描述](https://img-blog.csdnimg.cn/20201127000126790.png#pic_center)


    

  * 语法中，$\theta$是比较运算符：< , > , >= , <= , = , <>。

  * 语义：将表达式的值与子查询的结果进行比较：

    * 如果表达式的值至少与子查询结果的某一个值相比较满足$\theta$-some关系，则“表达式 $\theta$-some  (子查询)”的结果便为真；
    * 如果表达式的值与子查询结果的所有值相比较都满足$\theta$-all关系，则“表达式 $\theta$-all (子查询)”的结果便为真；

* 示例

  * 找出001号课成绩最高的所有学生的学号

    ```sql
    Select S#   From  SC
    Where C# = “001”  and  Score >= all ( Select   Score  From  SC  
                                        WhereC# = “001” );
    ```

  * 找出98030101号同学成绩最低的课程号

    ```sql
    Select C# From  SC
    Where S# = “98030101”  and  Score <= all ( Select   Score  From SC 
                                             Where S# = “98030101” );
    ```

  * 找出张三同学成绩最低的课程号

    ```sql
    Select C#   From  SC, Student S
    Where Sname = “张三”  and S.S#=SC.S# and Score <= all ( Select   Score  From  SC  
                                                         Where S#=S.S# );
    ```

  

* 等价

  * `表达式 = some (子查询)`   === `表达式  in  (子查询)`
  * `表达式  not  in  (子查询)`  === `表达式<> all  (子查询)`

  



(NOT) EXISTS 子查询

* 基本语法：

  ```sql
  [not] Exists (子查询)
  ```

  * 语义：子查询结果中有无元组存在

* 示例

  * 检索选修了赵三老师主讲课程的所有同学的姓名

    ```sql
    Select   DISTINCT  SnameFrom  Student
    Where  exists ( Select    *   From  SC, Course, Teacher
                   Where SC.C# = Course.C#   and SC. S# = Student.S#and  Course.T# = Teacher.T#  and Tname = ‘赵三’)
    ```

  * 检索没学过李明老师讲授任何一门课程的所有同学的姓名

    ```sql
    Select Sname From  Student
    Where  not  exists                   			//不存在
    	( Select * From   Course, SC, Teacher	    //学过一门课程
         Where Tname=‘李明’ and Course.T# =Teacher.T#and Course.C# = SC.C#  and S# = Student.S# );
    ```

    

  * 检索学过001号教师主讲的所有课程的所有同学的姓名

    ```sql
    Select   Sname
    From  Student Where  not exists                   //不存在
    	( Select * From   Course				      //有一门001教师主讲课程
     	Where Course.T# = ‘001’ and not  exists 		  //该同学没学过
     		( Select * From  SC
      		Where S# = Student.S# and C# = Course.C# ) );
    ```

  

  * 列出至少学过98030101号同学学过所有课程的同学的学号

    ```sql
    Select   DISTINC  S#  From  SC  SC1
    Where  not  exists                                   //不存在
    	( Select   *   From   SC  SC2                    //有一门课程
     	 Where SC2.S# = ‘98030101’ and   not  exists     //该同学没学过
     		( Select   *   From   SC
             Where   C# = SC2.C#  and   S# = SC1.S# ) );
    ```

    关系代数：$\prod_{S\#,C\#}$, (SC)$\div$$\prod_{C\#}$($\sigma_{S\#=‘98030101’}$(SC))

    关系演算：{ t[S#] | t$\in$SC $\wedge$ $\forall$(u$\in$SC $\wedge$ u[S#]=‘98030101’)($\exists$(w$\in$SC)(w[S#]=t[S#]  $\wedge$ w[C#]=u[C#] )) }





#### 查询结果聚集

Select-From-Where语句中，Select子句后面不仅可是列名，而且可是一些计算表达式或聚集函数，表明在投影的同时直接进行一些运算

```sql
Select   列名| expr| agfunc(列名) [[, 列名| expr| agfunc(列名)] ... ]
From 表名1 [, 表名2 ... ]
[ Where 检索条件] ;
```

* expr可以是常量、列名、或由常量、列名、特殊函数及算术运算符构成的算术运算式。特殊函数的使用需结合各自DBMS的说明书
* agfunc()是一些聚集函数





SQL提供了五个作用在简单列值集合上的内置聚集函数 agfunc,

* COUNT、SUM、AVG、MAX、MIN

* SQL聚集函数的参数类型、结果类型与作用如下：

  ![在这里插入图片描述](https://img-blog.csdnimg.cn/20201127000139820.png#pic_center)






#### 分组查询

SQL可以将检索到的元组按照某一条件进行分类，具有相同条件值的元组划到一个组或一个集合中，同时处理多个组或集合的聚集运算。

* 分组的基本语法：

  ```sql
  Select 列名| expr| agfunc(列名) [[, 列名| expr| agfunc(列名)] ... ]
  From 表名1 [, 表名2 ... ]
  [Where 检索条件] 
  [Group  by 分组条件] ;
  ```

  * 分组条件可以是列名1, 列名2, ...



分组过滤：

* 聚集函数是不允许用于Where子句中的：Where子句是对每一元组进行条件过滤，而不是**对集合进行条件过滤**

* 若要对集合(即分组)进行条件过滤，即满足条件的集合/分组留下，不满足条件的集合/分组剔除。

  * Having子句，又称分组过滤子句。需要有Groupby子句支持，换句话说，没有Groupby子句，便不能有Having子句。

  ```sql
  Select 列名| expr| agfunc(列名) [[, 列名| expr| agfunc(列名)] ... ]
  From 表名1 [, 表名2 ... ]
  [Where 检索条件] 
  [Group  by 分组条件 [Having  分组过滤条件]] ;
  ```



* 示例

  * 求不及格课程超过两门的同学的学号

    ```sql
    Select   S# From  SC
    Where  Score < 60
    Group by S# Having Count(*)>2; 
    ```

  * 求有两门以上不及格课程同学的学号及其平均成绩

    ```sql
    Select S#, Avg(Score)From  SC
    Where  S#  in
    	(Select S# From  SC
         Where  Score < 60 
         Group by S# Having Count(*)>2 )
    Group by S# ;
    ```



#### 集合查询操作

* 上面说的都是关系代数的关系操作 ：$\times$、 $\Join$、 $\sigma$、 $\prod$、$\rho$、$\div$
* 下面介绍集合操作（需要两个集合所有属性的域完全相同）：$\bigcup$、$-$、 $\bigcap$
  * 可转换为不用集合操作（同一关系的两个集合）
  * 不可转换为集合操作（不同关系但是每个属性域都相同的两个集合）



并运算

* UNION

* 示例

  * 可转换为不用 union：求学过002号课的同学或学过003号课的同学学号

    ```sql
    Select   S# From  SC  Where  C# = ‘002’
    UNION
    Select   S# From  SC  Where  C# = ‘003’
    
    // 不用集合操作
    Select   S# From  SC  Where  C# = ‘002’ OR  C# = ‘003’
    ```

  

  * 不可转换为 union：已知两个表 Customers(CID, Cname, City, Discnt)、Agents(AID, Aname, City, Percent)

    求客户所在的或者代理商所在的城市

    ```sql
    Select   City From  Customers UNION Select   City From  Agents;
    ```



交运算

* INTERSECT

* 示例：求既学过002号课，又学过003号课的同学学号

  ```sql
  Select   S# From  SC  Where  C# = ‘002’
  INTERSECT
  Select   S# From  SC  Where  C# = ‘003’;
  
  // 不用INTERSECT
  Select  S# From  SC  
  Where  C# = ‘002’   and  S#  IN (Select   S# From  SC  
                                   Where C# = ‘003’);
  ```

* 交运算符Intersect并没有增强SQL的表达能力，没有Intersect，SQL也可以用其他方式表达同样的查询需求。只是有了Intersect更容易表达一些，但增加了SQL语言的不唯一性。



差运算

* EXCEPT

* 示例：假定所有学生都有选课，求没学过002号课程的学生学号

  ```sql
  Select   DISTINCT  S# From  SC
  EXCEPT
  Select   S# From  SC Where  C# = ‘002’;
  
  //不用EXCEPT
  Select   DISTINCT  S# From  SC  SC1 
  Where  not exists (Select  *  From SC 
                     Where C# = ‘002’   and  S# = SC1.S#) 
  ```

* 差运算符Except也没有增强SQL的表达能力，没有Except， SQL也可以用其他方式表达同样的查询需求。只是有了Except更容易表达一些，但增加了SQL语言的不唯一性。





#### 视图

SQL数据库结构

* 基本表是实际存储于存储文件中的表，基本表中的数据是需要存储的
* 视图在SQL中只存储其由基本表导出视图所需要的公式，即由基本表产生视图的映像信息，其数据并不存储，而是在运行过程中动态产生与维护的
* 对视图数据的更改最终要反映在对基本表的更改上。



定义视图

* 创建视图

  ```sql
  create   view view_name  [(列名[，列名] ...)]  
  as 子查询 [with check option]
  ```

  * 如果视图的属性名缺省，则默认为子查询结果中的属性名；也可以显式指明其所拥有的列名。
  * with checkoption指明当对视图进行insert，update，delete时，要检查进行insert/update/delete的元组是否满足视图定义中子查询中定义的条件表达式

* 撤消视图

  ```sql
  Drop   View view_name
  ```

  

* 示例

  * 定义一个视图 CompStud 为计算机系的学生，通过该视图可以将Student表中其他系的学生屏蔽掉

    ```sql
    Create  View  CompStud
    AS ( Select   *  From  Student 
        Where  D#  in( Select   D# From  Dept 
                      Where  Dname = ‘计算机’) );
    ```

* 作用：

  * 方便用户进行检索操作
  * 或者屏蔽一些隐私信息。



操作

* 查询：直接像表一样查询即可。

* 更新：是比较复杂的问题，因视图不保存数据，对视图的更新最终要反映到对基本表的更新上，而有时，视图定义的映射不是可逆的。

  * 如果视图中包括经算术表达式计算出来的列，则不能更新
  * 如果视图的select子句使用了unique或distinct，则不能更新
  * 如果视图的select目标列包含聚集函数，则不能更新
  * 如果视图中包括了groupby子句，则不能更新
  * 如果视图是由单个表的列构成，但并没有包括主键，则不能更新
  * 对于由单一Table子集构成的视图，即如果视图是从单个基本表使用选择、投影操作导出的，并且包含了基本表的主键，则可以更新

* 可更新SQL视图示例：

  ```sql
  create  view  CStud(S#, Sname, Sclass) 
  as  ( select   S#, Sname, Sclassfrom   Student  
       where  D# =‘03’);
       
  // 可以更新
  Insert  into   CStud Values  ( “98030104”, “张三丰”, “980301”);
  ```