数据库多表查询

建表

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

多表连接查询

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

内连接

只连接匹配的行

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 | egon      |   18 | male   | 技术      |
#|  2 | alex      |   48 | female | 人力资源  |
#|  3 | wupeiqi   |   38 | male   | 人力资源  |
#|  4 | yunhao    |   28 | female | 销售      |
#|  5 | liwenzhou |   18 | male   | 技术      |
#+----+-----------+------+--------+----------+

# 上述sql等同于
SELECT employee.id,employee.name,employee.age,employee.sex,department.name FROM employee,department WHERE employee.dep_id=department.id;

外连接之左连接

以左表为准，即找出所有员工信息，当然包括没有部门的员工
本质就是：在内连接的基础上增加左边有右边没有的结果，优先显示左表内容

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 | egon       | 技术        |
#|  5 | liwenzhou  | 技术        |
#|  2 | alex       | 人力资源    |
#|  3 | wupeiqi    | 人力资源    |
#|  4 | yunhao     | 销售        |
#|  6 | jingliyang | NULL        |
#+----+------------+-------------+

外链接之右连接

以右表为准，即找出所有部门信息，包括没有员工的部门
本质就是：在内连接的基础上增加右边有左边没有的结果,优先显示右表全部内容

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 | egon       | 技术        |
#|  5 | liwenzhou  | 技术        |
#|  2 | alex       | 人力资源    |
#|  3 | wupeiqi    | 人力资源    |
#|  4 | yunhao     | 销售        |
#|  6 | jingliyang | NULL        |
#+----+------------+-------------+

全外连接

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

注意 union与union all的区别：union会去掉相同的纪录

符合条件连接查询

以内连接的方式查询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;

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

SELECT employee.id,employee.name,employee.age,department.name FROM employee INNER JOIN department
    ON employee.dep_id = department.id
    WHERE age > 25
    ORDER BY age ASC ;

子查询

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

带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人的部门名     
SELECT name FROM department
    WHERE id IN 
        (SELECT dep_id FROM employee GROUP BY dep_id HAVING count(id) <=1);

带比较运算符的子查询

# 查询大于所有人平均年龄的员工名与年龄
SELECT name,age FROM emp WHERE age > (SELECT avg(age) FROM emp);

# 查询大于部门内平均年龄的员工名、年龄
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关键字的子查询

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

# department表中存在dept_id=203,Ture
SELECT * FROM employee
    WHERE exists
         (SELECT id FROM department WHERE id=200);