• 多表查询
  • 数据准备
  • 多表查询方法之连表操作
    • inner join 内连接
    • left join 左连接
    • right join 右连接
    • union 全连接
  • 多表查询方法之子查询

多表查询

数据准备

#建表
create table dep(
    id int primary key auto_increment,
    name varchar(20) 
);

create table emp(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') not null default 'male',
    age int,
    dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营')，
(205,'打酱油');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('tony','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

多表查询方法之连表操作

# 连表操作
	先将查询涉及到的表拼接成一张大表 之后基于单表查询
 
# 笛卡尔积
	select * from emp,dep;
    select * from emp,dep where dep_id=id;
    select emp.name,dep.name from emp,dep where emp.dep_id=dep.id;

mysql>     select emp.name,dep.name from emp,dep where emp.dep_id=dep.id;
+-------+--------------+
| name  | name         |
+-------+--------------+
| jason | 技术         |
| tony  | 人力资源     |
| kevin | 人力资源     |
| nick  | 销售         |
| owen  | 运营         |
+-------+--------------+
5 rows in set (0.11 sec)

"""
涉及到多表操作的时候 为了避免表字段重复 
	需要在字段名的前面加上表名限制
"""
# 上述操作并不是合理的连表操作

inner join 内连接

	inner join	内连接:只连接两表中都存在(有对应关系)的数据
    	select * from emp inner join dep on emp.dep_id = dep.id;

mysql> select * from emp inner join dep on emp.dep_id = dep.id;
+----+-------+--------+------+--------+-----+--------------+
| id | name  | sex    | age  | dep_id | id  | name         |
+----+-------+--------+------+--------+-----+--------------+
|  1 | jason | male   |   18 |    200 | 200 | 技术         |
|  2 | tony  | female |   48 |    201 | 201 | 人力资源     |
|  3 | kevin | male   |   18 |    201 | 201 | 人力资源     |
|  4 | nick  | male   |   28 |    202 | 202 | 销售         |
|  5 | owen  | male   |   18 |    203 | 203 | 运营         |
+----+-------+--------+------+--------+-----+--------------+
5 rows in set (0.00 sec)

left join 左连接

    left join	左连接:以左表为基准展示左表所有的数据没有对应则NULL填充
    	select * from emp left join dep on emp.dep_id = dep.id;

mysql> select * from emp left join dep on emp.dep_id = dep.id;
+----+-------+--------+------+--------+------+--------------+
| id | name  | sex    | age  | dep_id | id   | name         |
+----+-------+--------+------+--------+------+--------------+
|  1 | jason | male   |   18 |    200 |  200 | 技术         |
|  2 | tony  | female |   48 |    201 |  201 | 人力资源     |
|  3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|  4 | nick  | male   |   28 |    202 |  202 | 销售         |
|  5 | owen  | male   |   18 |    203 |  203 | 运营         |
|  6 | jerry | female |   18 |    204 | NULL | NULL         |
+----+-------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)

right join 右连接

    right join	右连接:以右表为基准展示右表所有的数据没有对应则NULL填充
    	select * from emp right join dep on emp.dep_id = dep.id;

mysql> select * from emp  right join dep on emp.dep_id = dep.id;
+------+-------+--------+------+--------+-----+--------------+
| id   | name  | sex    | age  | dep_id | id  | name         |
+------+-------+--------+------+--------+-----+--------------+
|    1 | jason | male   |   18 |    200 | 200 | 技术         |
|    2 | tony  | female |   48 |    201 | 201 | 人力资源     |
|    3 | kevin | male   |   18 |    201 | 201 | 人力资源     |
|    4 | nick  | male   |   28 |    202 | 202 | 销售         |
|    5 | owen  | male   |   18 |    203 | 203 | 运营         |
| NULL | NULL  | NULL   | NULL |   NULL | 205 | 打酱油       |
+------+-------+--------+------+--------+-----+--------------+
6 rows in set (0.00 sec)

union 全连接

  union	    全连接:展示左右两表中所有的数据没有对应则NULL填充
select * from emp left join dep on emp.dep_id = dep.id union select * from emp right join dep on emp.dep_id = dep.id;

mysql> select * from emp left join dep on emp.dep_id = dep.id union select * from emp right join dep on emp.dep_id = dep.id;
+------+-------+--------+------+--------+------+--------------+
| id   | name  | sex    | age  | dep_id | id   | name         |
+------+-------+--------+------+--------+------+--------------+
|    1 | jason | male   |   18 |    200 |  200 | 技术         |
|    2 | tony  | female |   48 |    201 |  201 | 人力资源     |
|    3 | kevin | male   |   18 |    201 |  201 | 人力资源     |
|    4 | nick  | male   |   28 |    202 |  202 | 销售         |
|    5 | owen  | male   |   18 |    203 |  203 | 运营         |
|    6 | jerry | female |   18 |    204 | NULL | NULL         |
| NULL | NULL  | NULL   | NULL |   NULL |  205 | 打酱油       |
+------+-------+--------+------+--------+------+--------------+
7 rows in set (0.00 sec)

多表查询方法之子查询

# 子查询:其实就是分步操作
	将一张表的查询结果当做另外一条SQL语句的查询条件

1.查询部门是技术或者人力资源的员工信息
	1.先查询技术和人力资源的部门编号
    	select id from dep where name in ('技术','人力资源');
    2.根据部门编号去员工表中筛选出对应的员工数据
    	select * from emp where dep_id in (200,201);
    '''子查询:将SQL语句括号括起来即可充当查询条件'''
    select * from emp where dep_id in (select id from dep where name in ('技术','人力资源'));