mysql多表查询

目录

• 多表查询
  • 连表查询
    • 内连接
    • 外连接
      • 左外连接
      • 右外连接
      • 全外连接
    • 总结
  • 子查询
    • 带in关键字的子查询
    • 带比较运算符的子查询
    • 带exists关键字的子查询
  • 总结与练习

多表查询

#表和数据的准备工作
mysql> create table department(
    -> id int,
    -> name varchar(20)
    -> );

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

mysql> insert into department values
    -> (200,'技术'),
    -> (201,'人力资源'),
    -> (202,'销售'),
    -> (203,'运营');

mysql> insert into employee(name,sex,age,dep_id) values
    -> ('egon','male',18,200),
    -> ('alex','female',48,201),
    -> ('wupeiqi','male',38,201),
    -> ('yuanhao','female',28,202),
    -> ('liwenzhou','male',18,200),
    -> ('jingliyang','female',18,204)
    -> ;

连表查询

• 所谓连表其实就是在连接的时候先创建一张大表，里面存放的是两张表的笛卡尔积，接下来就按照条件进行筛选即可
• 连表查询主要分为内连接和外连接两种，两种各有异同

内连接

• 内连接是根据两张表的相同之处进行连接处理，只会显示连表关联处相同的内容

• 有如下两种方式

  • select 列 from 表1,表2 where 条件;
    • select * from department,employee where department.id=employee.dep_id;
  • select 列 from 表1 inner join 表2 on 条件;
    • select * from department inner join employee on department.id=employee.dep_id;

  mysql> select * from department inner join employee on department.id=
  employee.dep_id;
  +------+--------------+----+-----------+--------+------+--------+
  | id   | name         | id | name      | sex    | age  | dep_id |
  +------+--------------+----+-----------+--------+------+--------+
  |  200 | 技术         |  1 | egon      | male   |   18 |    200 |
  |  201 | 人力资源     |  2 | alex      | female |   48 |    201 |
  |  201 | 人力资源     |  3 | wupeiqi   | male   |   38 |    201 |
  |  202 | 销售         |  4 | yuanhao   | female |   28 |    202 |
  |  200 | 技术         |  5 | liwenzhou | male   |   18 |    200 |
  +------+--------------+----+-----------+--------+------+--------+
  5 rows in set (0.00 sec)
  

  相比如上两种内连接方式，第二种更好，因为第二种使用了inner join….on….的方法，后面可以跟单表查询筛选方式一致继续使用where等条件，而第一种已经使用了where条件，后面再接where条件稍显别扭

外连接

• 外连接主要分为三种：左外连接，右外连接，全连接

左外连接

• 左外连接相比于内连接而言增加了一点：会将左表的内容全部显示，右表没有内容会默认为null

• select 列 from 表1 left join 表2 on 条件

  • select * from department left join employee on department.id=employee.dep_id;

  mysql> select * from department left join employee on department.id= employee.dep_id;
  +------+--------------+------+-----------+--------+------+--------+
  | id   | name         | id   | name      | sex    | age  | dep_id |
  +------+--------------+------+-----------+--------+------+--------+
  |  200 | 技术          |   1 | egon      | male   |   18 |    200 |
  |  201 | 人力资源       |   2 | alex      | female |   48 |    201 |
  |  201 | 人力资源       |   3 | wupeiqi   | male   |   38 |    201 |
  |  202 | 销售          |   4 | yuanhao   | female |   28 |    202 |
  |  200 | 技术          |   5 | liwenzhou | male   |   18 |    200 |
  |  203 | 运营          | NULL | NULL     | NULL   | NULL |   NULL |
  +------+--------------+------+-----------+--------+------+--------+
  6 rows in set (0.02 sec)
  

右外连接

• 右外连接相比于右连接而言增加了一点：会将右表的内容全部显示，左表表没有内容会默认为null

• select 列 from 表1 right join 表2 on 条件

  • select * from department right join employee on department.id=employee.dep_id;

  mysql> select * from department right join employee on department.id=employee.dep_id;
  +------+--------------+----+------------+--------+------+--------+
  | id   | name         | id | name       | sex    | age  | dep_id |
  +------+--------------+----+------------+--------+------+--------+
  |  200 | 技术         |  1 | egon       | male   |   18 |    200 |
  |  200 | 技术         |  5 | liwenzhou  | male   |   18 |    200 |
  |  201 | 人力资源     |  2 | alex       | female |   48 |    201 |
  |  201 | 人力资源     |  3 | wupeiqi    | male   |   38 |    201 |
  |  202 | 销售         |  4 | yuanhao    | female |   28 |    202 |
  | NULL | NULL         |  6 | jingliyang | female |   18 |    204 |
  +------+--------------+----+------------+--------+------+--------+
  6 rows in set (0.00 sec)
  

全外连接

• 全连接是左外连接与右外连接的集合，但mysql没有全外连接的直接语句，sqlserver的全外连接为full join，但mysql可以使用union连接左右外连接从而实现全外连接的功能1

  • select 列 from 表1 left join 表2 on 条件 union select 列 from 表1 right join 表2 on 条件
    • select * from department left join employee on department.id=employee.dep_id
    • union
    • select * from department right join employee on department.id=employee.dep_id;

  mysql> select * from department left join employee on department.id= 		  employee.dep_id
      -> union
      -> select * from department right join employee on department.id= employee.dep_id;
  +------+--------------+------+------------+--------+------+--------+
  | id   | name         | id   | name       | sex    | age  | dep_id |
  +------+--------------+------+------------+--------+------+--------+
  |  200 | 技术         |    1 | egon       | male   |   18 |    200 |
  |  201 | 人力资源     |    2 | alex       | female |   48 |    201 |
  |  201 | 人力资源     |    3 | wupeiqi    | male   |   38 |    201 |
  |  202 | 销售         |    4 | yuanhao    | female |   28 |    202 |
  |  200 | 技术         |    5 | liwenzhou  | male   |   18 |    200 |
  |  203 | 运营         | NULL | NULL       | NULL   | NULL |   NULL |
  | NULL | NULL         |    6 | jingliyang | female |   18 |    204 |
  +------+--------------+------+------------+--------+------+--------+
  7 rows in set (0.02 sec)
  

总结

• 所谓连表就是将两张表按一定规则连接起来变成一张大表，从from开始一直到on条件结束就是形成一张大表的过程，然后就可以使用单表查询的条件where、group by、having、order by 、limit等条件就行数据筛选
• 注意一点：两张表中重复的字段注意使用表名.字段名的方式处理

子查询

• 子查询是将一个查询语句嵌套在另一个查询语句中。
• 内层查询语句的查询结果，可以为外层查询语句提供查询条件
• 子查询中可以包含：in、not in、any、all、exits和 not exists等关键字
• 还可以包含比较运算符：= 、 !=、> 、<等

带in关键字的子查询

#查询平均年龄在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);

带比较运算符的子查询

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

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

总结与练习

• 如果一个问题既可以使用连表查询解决也可以使用子查询解决，尽量使用连表查询解决，因为连表查询速度快效率高

#连表查询的练习
mysql> select t1.name,t2.name from department as t1 left join employee as t2 on t1.id=t2.dep_id where age>25;

mysql> select * from department as t1 inner join employee as t2 on t1.id=t2.dep_id order by age;

mysql> select * from department as t1 inner join employee as t2 on t1.id=t2.dep_id order by age;

mysql> select count(t1.name) from department as t1 left join employee as t2 on t1.id=t2.dep_id group by t1.name;

mysql> select t1.id,count(t2.id) from department as t1 left join employee as t2 on t1.id=t2.dep_id group by t1.name;

mysql> select t1.id,t1.name,count(t2.id) from department as t1 left join employee as t2 on t1.id=t2.dep_id group by t1.name;

mysql> select t1.id,t1.name,count(t2.id) from department as t1 left join employee as t2 on t1.id=t2.dep_id group by t1.name order by count(t2.id);

mysql> select t1.id,t1.name,count(t2.id) as c from department as t1 left join employee as t2 on t1.id=t2.dep_id group by t1.name order by c;

#子查询
mysql> select name from department where id not in(select dep_id from employee group by dep_id);  #查询部门中不足一人的部门名

mysql> select * from department where id in(select dep_id from employee group by dep_id having count(name)=1);   #查询部门中只有一人的部门名