10月26日学习内容整理：多表查询

 

一、多表查询

1、交叉连接：不使用任何匹配条件，生成笛卡尔积

》》select * from 表1，表2；就是将表1的每条记录和表2中所有记录组成一条记录

》》笛卡尔积：AxB 就是将A中的每个元素和B中的所有可能组成有序对

》》作用：就是将两张表合为一张表（虚拟表）

 

 

2、内连接：inner join，只连接匹配上的记录

》》原理：select * from 表1，表2 where 表1.字段=表2.字段 先生成笛卡尔积，再按约束条件筛选，最后合成一张虚拟表，但是一般我们不这么写

》》select * from 表1 inner join 表2 on 表1.字段=表2.字段

利用inner join和on的组合，on 后是以什么条件从笛卡尔积中筛选出来想要的表，也就是连接条件

后面也可以跟where和其它条件，用表名.字段名来表示，因为两张表中可能有重复的字段名

》》作用：取出两张表中的共同部分

 

 

 

3、外连接之左连接：left join

》》作用：在内连接的基础上保留左表全部的记录，也就是左表有而右表没有的记录，先写谁谁就是左表

》》select * from 表1 left join 表2 on 连接条件 ，后面也可以跟where和其它条件

 

 

 

4、外连接之右连接：right join

》》作用：在内连接的基础上，保留右表的全部记录，也就是右表有而左表没有的记录

》》select * from 表1 right join 表2 on 连接条件 ，后面也可以跟where和其它条件

 

 

5、全外连接：union

》》作用：在内连接的基础上，保留左表和右表中的全部记录

》》注意mysql不支持full join

》》union all不会去掉重复的部分

 

6、优先级问题

》》首先执行from，找到左表和右表，并生成笛卡尔积

》》按照on的连接条件得到内连接的结果（也就是两张表共同部分组成的虚拟表）

》》然后再执行 inner/left/right  join（在上一得到的虚拟表的基础上）又得到一个新的虚拟表

》》之后再继续where和后面条件的优先级

7、3张表或多张表连接

》》可以用两张表连接的最终结果（一张虚拟表）经过as改名后，再跟第三张表连接，以此类推

》》select * from （两张表连接产生的虚拟表） as  新表名  inner join  表3  on  连接条件

》》注意经过练习，应该多张表做连接最后生成的表中不能有2个以上的完全相同的列，就是字段名和字段中所有的值都相同

 

 

 

 

》》》》》》补充：null值会被聚合函数count计数为0

8、子查询

》》作用：一个查询语句嵌套在另一个查询语句内；内层查询语句的结果可作为外层查询语句的条件，注意内层查询语句不能有分号

》》 in 关键字

#查询employee表，但dep_id必须在department表中出现过
select * from employee
    where dep_id in
        (select id from department);

 

》》比较运算

#比较运算符：=、!=、>、>=、<、<=、<>
#查询平均年龄在25岁以上的部门名
select id,name from department
    where id in 
        (select dep_id from employee group by dep_id having avg(age) > 25);

#查看技术部员工姓名
select name from employee
    where dep_id in 
        (select id from department where name='技术');

#查看不足1人的部门名
select name from department
    where id in 
        (select dep_id from employee group by dep_id having count(id) <=1);

 

》》exists关键字：内层查询语句不会返回一个记录结果，而是返回True或False，当返回True时外层查询语句才会执行，返回False时外层查询语句不会执行

#department表中存在dept_id=203，Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+

#department表中存在dept_id=205，False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)