mysql 多表查询

mysql 多表查询

创建2个对应的表

create table department(
id int,
name varchar(20) 
);

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

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

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

1. 连表查询

inner join　　　　 内连接 　　　　　　只连接匹配的行
left join 　　　　  左连接 　　　　　　 优先显示左表全部记录
right join 　　　　右连接 　　　　　　 优先显示右表全部记录
union 　　　　   全外连接 　　　　　　 显示左右两个表全部记录

select 字段列表 
    from tb1 inner|left|right  join tb2
    on tb1.id=tb2=id

1) inner join

select * from department d  inner join employee e  on d.id=e.dep_id;

select * from department d  inner join employee e  where  d.id=e.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 |
+------+--------------+----+-----------+--------+------+--------+

2) left join

select * from department d  left  join employee e  on   d.id=e.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 |
+------+--------------+------+-----------+--------+------+--------+

3) right join 

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

+------+-----------+--------+------+--------+------+--------------+
| id   | name      | sex    | age  | dep_id | id   | name         |
+------+-----------+--------+------+--------+------+--------------+
|    1 | egon      | male   |   18 |    200 |  200 | 技术         |
|    2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|    5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
| NULL | NULL      | NULL   | NULL |   NULL |  203 | 运营         |
+------+-----------+--------+------+--------+------+--------------+

4) union

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 | egon       | male   |   18 |    200 |  200 | 技术         |
|    5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|    2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|    6 | jingliyang | female |   18 |    204 | NULL | NULL         |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 运营         |
+------+------------+--------+------+--------+------+--------------+

--用来替换  or 、in()

SELECT * FROM world.city
WHERE countrycode IN ('CHN','JPN');
改写为：

SELECT * FROM world.city
WHERE countrycode ='CHN'
union
SELECT * FROM world.city
WHERE countrycode ='JPN';

二 子查询

1：子查询是将一个查询语句嵌套在另一个查询语句中。

2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。

3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字

4：还可以包含比较运算符：= 、 !=、> 、<等

二 练习

1.以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门

select employee.name, department.name  from 
    employee inner join department 
    on employee.dep_id=department.id  
    where employee.age > 25;

2.以内连接的方式查询employee和department表，并且以age字段的升序方式显示

select * from employee 
    inner join department 
    on employee.dep_id=department.id  
    order by  age;

3. 带in关键字的子查询

①查询平均年龄在25岁以上的部门名

select * from department  
    where id in 
        (select dep_id from employee 
        group by dep_id 
        having avg(age) > 25);

②查看技术部员工姓名

select * from employee where dep_id = (select id from department where name="技术");
select * from employee where dep_id in  (select id from department where name="技术");

③查看不足1人的部门名

select * from department 
    where id not in 
        (select dep_id from employee 
        group by dep_id);    

4 带比较运算符的子查询

①查询大于所有人平均年龄的员工名与年龄

select * from  employee where age >(select avg(age) from  employee);

②查询大于部门内平均年龄的员工名、年龄

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;