数据库的多表查询

一.多表连接查询

　　1.交叉连接:不适用任何匹配条件,生成笛卡儿积

　　　　select * from 表1,表2;

　　2.内连接:只连接匹配的行

　　　　select * from 表1,表2 where 表1.字段 = 表2.字段;

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

　　3.左连接:优先显示左表全部记录

　　　　select * from 表1 left join 表2 on 表1.字段 = 表2.字段;

　　4.右连接:优先显示右表全部记录

　　　　select * from 表1 right join 表2 on 表1.字段 = 表2.字段;

　　5.全外连接:显示两个表全部记录,union all 显示两个表拼接的有重复的,union可以去重.

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

　　　　union

　　　　select * from 表1 right join 表2 on 表1.字段 = 表2.字段;

二.子查询

　　子查询是将一个查询语句嵌套在另一个查询语句中,子查询中的关键字有in,not in , all ,any ,exists,not exists,还有运算符 = ,!= ,< ,>

　　1.in关键字子查询　

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

　　2.exists关键字子查询

　　  exists关键字表示存在,如果exists后面的内层查询存在就查询外层,否则不进行查询.

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

　　3.比较运算符子查询

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
rows in set (0.00 sec)


#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join 
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;