多表查询

本节主题

  • 多表连接查询
  • 复合条件连接查询
  • 子查询

准备表

表department与表employee

  


二 多表连接查询

就是在笛卡尔积的基础上做了筛选

#重点:外链接语法

SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> select * from employee,department;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
1 | jack  | male   |   18 |    200 200 | 技术         |
1 | jack  | male   |   18 |    200 201 | 人力资源     |
1 | jack  | male   |   18 |    200 202 | 销售         |
1 | jack  | male   |   18 |    200 203 | 运营         |
2 | alice | female |   48 |    201 200 | 技术         |
2 | alice | female |   48 |    201 201 | 人力资源     |
2 | alice | female |   48 |    201 202 | 销售         |
2 | alice | female |   48 |    201 203 | 运营         |
3 | nico  | female |   38 |    201 200 | 技术         |
3 | nico  | female |   38 |    201 201 | 人力资源     |
3 | nico  | female |   38 |    201 202 | 销售         |
3 | nico  | female |   38 |    201 203 | 运营         |
4 | bob   | male   |   28 |    202 200 | 技术         |
4 | bob   | male   |   28 |    202 201 | 人力资源     |
4 | bob   | male   |   28 |    202 202 | 销售         |
4 | bob   | male   |   28 |    202 203 | 运营         |
5 | tom   | male   |   18 |    200 200 | 技术         |
5 | tom   | male   |   18 |    200 201 | 人力资源     |
5 | tom   | male   |   18 |    200 202 | 销售         |
5 | tom   | male   |   18 |    200 203 | 运营         |
6 | lucy  | female |   18 |    204 200 | 技术         |
6 | lucy  | female |   18 |    204 201 | 人力资源     |
6 | lucy  | female |   18 |    204 202 | 销售         |
6 | lucy  | female |   18 |    204 203 | 运营         |
+----+-------+--------+------+--------+------+--------------+
24 rows in set (0.02 sec)

  

2 内连接:只连接匹配的行(只取两张表的共同部分)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了正确的结果
#department没有204这个部门,因而employee表中关于204这条员工信息没有匹配出来
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+-------+------+--------+--------------+
| id | name  | age  | sex    | name         |
+----+-------+------+--------+--------------+
1 | jack  |   18 | male   | 技术         |
2 | alice |   48 | female | 人力资源     |
3 | nico  |   38 | female | 人力资源     |
4 | bob   |   28 | male   | 销售         |
5 | tom   |   18 | male   | 技术         |
+----+-------+------+--------+--------------+
5 rows in set (0.58 sec)
 
#上述sql等同于
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;

  

3 外链接之左连接:优先显示左表全部记录(在内连接的基础上保留左表的记录)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#以左表为准,即找出所有员工信息,当然包括没有部门的员工
#本质就是:在内连接的基础上增加左边有右边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
 
+----+-------+--------------+
| id | name  | depart_name  |
+----+-------+--------------+
1 | jack  | 技术         |
5 | tom   | 技术         |
2 | alice | 人力资源     |
3 | nico  | 人力资源     |
4 | bob   | 销售         |
6 | lucy  | NULL         |
+----+-------+--------------+
6 rows in set (0.00 sec)

  

4 外链接之右连接:优先显示右表全部记录(在内连接的基础上保留右表的记录)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#以右表为准,即找出所有部门信息,包括没有员工的部门
#本质就是:在内连接的基础上增加右边有左边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+-------+--------------+
| id   | name  | depart_name  |
+------+-------+--------------+
|    1 | jack  | 技术         |
|    2 | alice | 人力资源     |
|    3 | nico  | 人力资源     |
|    4 | bob   | 销售         |
|    5 | tom   | 技术         |
| NULL | NULL  | 运营         |
+------+-------+--------------+
6 rows in set (0.00 sec)

  

5 全外连接:显示左右两个表全部记录(在内连接的基础上,保留左右两表没有对用关系的结果)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意:mysql不支持全外连接 full JOIN
#强调:mysql可以使用此种方式间接实现全外连接
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;
 
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jack  | male   |   18 |    200 200 | 技术         |
|    5 | tom   | male   |   18 |    200 200 | 技术         |
|    2 | alice | female |   48 |    201 201 | 人力资源     |
|    3 | nico  | female |   38 |    201 201 | 人力资源     |
|    4 | bob   | male   |   28 |    202 202 | 销售         |
|    6 | lucy  | female |   18 |    204 | NULL | NULL         |
| NULL | NULL  | NULL   | NULL |   NULL |  203 | 运营         |
+------+-------+--------+------+--------+------+--------------+
7 rows in set (0.00 sec)
 
#注意 union与union all的区别:union会去掉相同的纪录
 

三 符合条件连接查询

1
2
3
4
5
6
7
8
9
10
#示例1:以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department
    on employee.dep_id = department.id
    where age > 25;
 
#示例2:以内连接的方式查询employee和department表,并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name from employee,department
    where employee.dep_id = department.id
    and age > 25
    order by age asc;

  


四 子查询

1:子查询是将一个查询语句嵌套在另一个查询语句中。
2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。
3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4:还可以包含比较运算符:= 、 !=、> 、<等

1 带IN关键字的子查询(在表内)

1
2
3
4
5
6
7
8
9
10
11
12
#查询平均年龄在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人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);

  

2 带比较运算符的子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#比较运算符:=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from employee where age > (select avg(age) from employee);
+-------+------+
| name  | age  |
+-------+------+
| alice |   48 |
| nico  |   38 |
+-------+------+
2 rows in set (0.01 sec)
 
 
#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from employee t1
inner join
(select dep_id,avg(age) avg_age from employee group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;
+-------+------+
| name  | age  |
+-------+------+
| alice |   48 |
+-------+------+
1 row in set (0.01 sec) 

  

3 带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#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 | jack  | male   |   18 |    200 |
2 | alice | female |   48 |    201 |
3 | nico  | female |   38 |    201 |
4 | bob   | male   |   28 |    202 |
5 | tom   | male   |   18 |    200 |
6 | lucy  | female |   18 |    204 |
+----+-------+--------+------+--------+
6 rows in set (0.00 sec)
 
 
#department表中存在dept_id=205,False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

 

练习:查询每个部门最新入职的那位员工

准备表

 答案一(链表)

 答案二(子查询)

答案一为正确答案,答案二中的limit 1有问题(每个部门可能有>1个为同一时间入职的新员工),我只是想用该例子来说明可以在select后使用子查询

可以基于上述方法解决:比如某网站在全国各个市都有站点,每个站点一条数据,想取每个省下最新的那一条市的网站质量信息


五 综合练习

init.sql文件内容

从init.sql文件中导入数据

#准备表、记录
mysql> create database db1;
mysql> use db1;
mysql> source /root/init.sql

表结构为

 

!!!重中之重:练习之前务必搞清楚sql逻辑查询语句的执行顺序

题目

  答案

  更多练习http://www.cnblogs.com/wupeiqi/articles/5748496.html

posted @   混世妖精  阅读(211)  评论(0编辑  收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示