SQL语句之数据查询（二）多表查询-----------------数据查询的重点难点

心已赠人 2022-05-24 06:43 705阅读 0赞

### 1.0 连接查询—多表查询 ###

数据库已有的表和里面的内容  
![这里写图片描述][70]  
**1. 等值连接与非等值连接**

###### 什么叫等值连接呢？ ######

就拿上面的的student 表与 SC表来说 我们把他们合到一起 可以比较的列在一起进行比较，如果值相等，那么这列的元素所在的 行就会合并

eg:

select student.\* ,SC.\* from student ,sc where student.Sno = Sc.Sno;

![这里写图片描述][70 1]  
可以看出来 Sno 的值相等的行合并为一行了

等值连接的过程是 我们在student表中的一个字段，去Sc表扫描每一行，满足条件就输出，知道扫描完成，  
这是一种可能情况，也叫嵌套循环连接算法 我们可以用建立索引来提高效率

###### 什么叫非等值连接呢？ ######

还是上面的例子 简单的来说就是满足不等条件的放在一行

select student.\* ,SC.\* from student ,sc where student.Sno > Sc.Sno;  
![这里写图片描述][70 2]

一种可能的比较方法是 student中每一个Sno 的值与sc表的第一个元素比较 等student.Sno 的值都与Sc.sno 第一个值比较完成后，在与下一个值比较，以此类推 满足条件就是一行

为了满足接下来的例子 插入数据后的三张表如下  
![这里写图片描述][70 3]

eg： select student.*,sc.* from student,sc where student.sno>sc.sno;  
![这里写图片描述][70 4]

数据我已经插入了，注意因为sc含有表级完整性约束 也就是外键 所以应该先插入course 在插入sc表，否则插不进去

至于原因很简单 既然SC表的cno 是参照course的，那么course要是没有cno与sc 相等的，那肯定差不进去呗

**自然连接**

我们在进行等值连接的时候 student.Sno 和sc.sno 数值是一样 显=显示两遍了 让他只显示一遍就是自然连接

select A.sno,A.sname,A.sex,A.sage,A.sdept,B.cno,B.grage from student A,sc B where A.sno = B.sno;

![这里写图片描述][70 5]

其中 A B 是分别起的别名  
**2. 自身连接**  
顾名思义 就是自己和自己连接 ，为了区分用到的是哪个自己 要起别名

select A.sno,A.sname,A.sex,A.sage,A.sdept, B.sno,B.sname,B.sex,B.sage,B.sdept from student A,student B where A.sno > B.sno;

![这里写图片描述][70 6]

select A.sno,A.sname,A.sex,A.sage,A.sdept, B.sno,B.sname,B.sex,B.sage,B.sdept from student A,student B where A.sno = B.sno;

![这里写图片描述][70 7]

**3.0 外连接** 分为左外连接与右外链接  
外链接与正常连接的区别是 通常的连接只会输出满足条件的连接 不满足的不会输出  
例如上面的自然连接的例子中 学号为002的学生并没有显示出来 因为他不符合要求，左连接就是保留左边表的左右数据，连接的表如果没有就是null

语句格式是 left outer join 表明 on(..) ;

**eg：** select \* from student left outer join sc on(student.sno = sc.sno);  
![这里写图片描述][70 8]

右链接就是保存右表的所有数据 right outer join 表明 on();

**4.0 多表连接** 查询每个小学生的学号 姓名 年龄 性别 课程 课程号

select \* from student a,sc b,course c where a.sno = b.sno and b.cno = c.cno;

![这里写图片描述][70 9]

### 2.0 嵌套查询 ###

在sql语言中 有个 select .. from … where .. 叫做查询块

把一个查询快放在另一个查询块的where或者 having子句中的查询叫做**嵌套查询**

#### 1. 带有 **in**谓词的子查询 ####

因为子查询出来的往往是一个集合，所以in是嵌套查询中最常用的**谓词**

例如：我们查询与xyd 在同一个系的学生

大致分为以下几步：

1.0 查询xyd在哪个系？

select Sdept from student where Sname = ‘xyd’;  
结果是计算机科学与技术  
2.0 查询这个系都有哪些学生

select Sname from student where Sdept = ‘计算机科学与技术’;

这样就查出来了 然后我们进行嵌套

select Sname from student where Sdept in(select Sdept from student where Sname = ‘xyd’) ;

![这里写图片描述][70 10]

**子查询的条件与父查询无关 叫做不相关子查询**  
这件事也可以用连接查询来查询  
select B.Sname from student A,Student B where A.sdept = B.sdept and A.sname = ‘xyd’;  
![这里写图片描述][70 11]

这就涉及到数据库调优的知识了，由于嵌套查询的调优技术还没有成熟，所以我们能用连接查询就用连接查询

#### 2. 带有比较运算符的子查询 ####

因为一般我们子查询的结果是个集合，索引要用in 但是当我们知道子查询的结果是单个值得时 就可以不用in了，

可以用 < > = 等比较运算符运算

例如上面的例子select Sname from student where Sdept in(select Sdept from student where Sname = ‘xyd’) ; 我们查询的只能是一个值 所以就可以用 = 了

eg:变为select Sname from student where Sdept = (select Sdept from student where Sname = ‘xyd’) ;

![这里写图片描述][70 12]

上面我们讲了什么叫做不相关子查询，

**如果子查询的查询条件与父查询有关就叫做相关子查询**  
eg:查询每个学生超过他选修课程平均成绩的课程号

select Sno,Cno from Sc x where Grade > (select avg(grade) from sc y where y.sno = x.sno);

上述SQL的一种可能执行过程是这样的

1.  首先查询x.sno 假设找到的是1
2.  然后执行 select avg(grade) from sc y where y.sno = 1;
3.  假设结果设80
4.  然后执行 select Sno,Cno from Sc x where Grade > 80；
5.  回到1 找到下一个 x.sno 假设2
6.  重复执行1-5 知道 x表中的Sno 全部查询完毕

我们看到子查询的y.sno 的值依赖于父查询的x.sno 这就叫做相关子查询

*由此我们可以看出来 求解相关子查询的时候不能像不相关子查询那样一次性把子查询结果求出来，然后在求解父查询 因为相关子查询与父查询有关，所以必须反复进行子查询 而不是子查询这次完事后就不用了*

#### 3. 带有any或者all 谓词的子查询 ####

我们用比较运算符的时候，只能在返回值是一个的时候用，并且 单个值得时候 = 和in 的左右是一样的，但是你不能用 in 代替

< > 那返回多值得时候 假设返回的是1-100之间的数，但是我只想要大于结果集中所有数的怎么办呢？

这就是要带有any 和all 的子查询

any 和 all 什么意思怎么用呢？ 看下表：  
使用 any或者all的时候必须配合比较运算符 并且有的数据库不用any 用some

<table> 
 <thead> 
  <tr> 
   <th>谓词</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>&gt;any</td> 
   <td>大于子查询结果集中的某个值</td> 
  </tr> 
  <tr> 
   <td>&gt;all</td> 
   <td>大于子查询结果集中的所有值</td> 
  </tr> 
  <tr> 
   <td>&lt; any</td> 
   <td>小于子查询结果集中的某个值</td> 
  </tr> 
  <tr> 
   <td>&lt; all</td> 
   <td>小于子查询结果集中的所有值</td> 
  </tr> 
  <tr> 
   <td>&gt;=any</td> 
   <td>大于等与子查询结果集中的某个值</td> 
  </tr> 
  <tr> 
   <td>&gt;=all</td> 
   <td>大于等与子查询结果集中的所有值</td> 
  </tr> 
  <tr> 
   <td>&lt;=any</td> 
   <td>小于等于子查询结果集中的某个值</td> 
  </tr> 
  <tr> 
   <td>&lt;=all</td> 
   <td>小于等于子查询结果集中的所有值</td> 
  </tr> 
  <tr> 
   <td>=any</td> 
   <td>等于子查询结果集中的某个值</td> 
  </tr> 
  <tr> 
   <td>=all</td> 
   <td>等于子查询结果集中的所有值</td> 
  </tr> 
  <tr> 
   <td>!=any</td> 
   <td>不等于子查询结果集中的某个值</td> 
  </tr> 
  <tr> 
   <td>!=all</td> 
   <td>不等于子查询结果集中的所有值</td> 
  </tr> 
 </tbody> 
</table>

eg:查询student中年龄小于最大值得所有人

select sname,sage from student where sage < any (select sage from student);  
![这里写图片描述][70 13]

上述SQL也可以用聚集函数来查询  
select sname,sage from student where sage < (select max(sage) from student);

结果是一样的 但是聚集函数的查询效率要高 聚集函数和any或者all的对应关系如下

#### 4. 带有exists 的子查询 ####

exists 代表存在量词 的意思 返回值是true 或者false

我们可以用它判断是否属于这个集合，是否是子集，两个集合是否相等 交集是否为空  
我们查询所有选修了005号课程的学生  
select sname from student where exists (select \* from Sc where sc.sno = student.sno and cno = ‘001’);

结果应该是空

select sname from student where exists (select \* from Sc where sc.sno = student.sno and cno = ‘001’);  
![这里写图片描述][70 14]

第一个语句返回的是false 也就是 where后是false 第二个是where true

因为子查询只显示真假 所有我们一般select后面跟\* 号 因为写列名无意义

我们还可以用存在量词表示全称量词，这里不深入了

### 3.0 集合查询 ###

就是实现的集合的运算 主要有 交集 intersect 并集 union 差集except

用法一样 只举一个例子：  
eg； 查询student 表中所有人和年龄大于24的人

select \* from student union select \* from student where sage > 24;

![这里写图片描述][70 15]

这实际就是求 查询student 表中所有人和年龄大于24的人 的并集 系统会自动除去重复元素，如果不想除去 可以用 union all

![这里写图片描述][70 16]

### 4.0 基于派生表的查询 ###

子查询除了可以放在where 或者 having 后面 还可以放在from 后面 这时候子查询出来的表叫做派生表 我们必须要为派生表起别名  
例如  
select \* from student; 的结果如下  
![这里写图片描述][70 17]

我们在这个基础上查询年龄等于22的所有人的名字：

select A.Sname from (select \* from student) A where A.Sage = 22;

![这里写图片描述][70 18]

这里的A就是别名

select A.Sname from (select \* from student) as A where A.Sage = 22

as 可以省略 也是起别名的方式

### 5.0 select 语句的一般格式(中括号为可选项) ###

select \[distinct|all\] 目标列表达式 别名 ，目标表达式 别名 。。。

from 视图或者表 别名，视图或者表 别名 。。。 （子查询） \[as\] 别名

\[where 条件表达式\]

\[group by 列名 having 条件表达式\]

\[order by 列名asc|desc \] /*升序，降序* /

#### 5.1 目标列表达式的一般格式： ####

\*  
列名  
表名.列名  
字符串  
算数表达式  
聚集函数

#### 5.2 聚集函数的一般格式 ####

count ( \[ distinct | all \] 列名)

#### 5.3 where子句表达式的可选格式 ####

##### 5.3.1 属性列名 算数表达式 属性列名|常数|\[any|all\] 子查询语句 #####

##### 5.3.2 \[not \]between。。。and .. #####

##### 5.3.3 not in() #####

##### 5.3.4 not like #####

##### 5.3.5 is null #####

##### 5.3.6 exists #####

##### 5.3.7 and or #####

[70]: /images/20220524/cbf19440e58944bb89d2f173d85ef198.png
[70 1]: /images/20220524/35fdda91ee1c4bbb87f71bda2c93da52.png
[70 2]: /images/20220524/c7dc484d2e5b40129be3744a1efb9315.png
[70 3]: /images/20220524/6a4c0e3f9c524c4eacd7f03662e46c7b.png
[70 4]: /images/20220524/ba25b395d70e454ab0109adcb39fb08a.png
[70 5]: /images/20220524/301175bfa4b74445ac96ad7dea4e260d.png
[70 6]: /images/20220524/c5e03ee90671480e9bb068659e11cdc5.png
[70 7]: /images/20220524/467711f29e4949758e281f4d84d602e9.png
[70 8]: /images/20220524/deb354f2bb2c4c1eabdacbaa0d049809.png
[70 9]: /images/20220524/db99bf41839640f0afe3365853bd16d1.png
[70 10]: /images/20220524/e73b63d1407149ba8355a70bcadfa8e6.png
[70 11]: /images/20220524/cd44cb2bf4bb4cc0b0cecb991d7b4a0c.png
[70 12]: /images/20220524/d1ba9d1d061e4191a65e727cf358997e.png
[70 13]: /images/20220524/1316901d0d9245e18b516994190e517e.png
[70 14]: /images/20220524/76fadcc5edf941ae9c20146f7ff7cb6f.png
[70 15]: /images/20220524/c83a854e82254083a0663fa9b9de5fe5.png
[70 16]: /images/20220524/a7b26bbb4d6f4e70be0c69f971b1e045.png
[70 17]: /images/20220524/c6aa243d46ea4b0193f51130ea30c01d.png
[70 18]: /images/20220524/31354d9fdda847ada6f1efb1f892762e.png