创建表：
create database multi_table charset utf8;
use multi_table;
部门表：
create table department(id int, name varchar(20));
员工表
create table employee(id int primary key auto_increment, name varchar(20), gender
enum('male', 'female') not null default 'male', age int, dep_id int);
插入数据：
insert into department values
-> (200,'技术'),
-> (201,'人力资源'),
-> (202,'销售'),
-> (203,'运营');
Query OK, 4 rows affected (0.04 sec)
mysql> INSERT INTO employee(name, gender, age, dep_id) VALUES
-> ('Andy', 'male', 18, 200),
-> ('Mary','female', 20, 201),
-> ('Jack', 'male', 25, 201),
-> ('Lili', 'female', 28, 202),
-> ('Andrew', 'male',18, 200),
-> ('Lemon', 'female', 21, 203);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| gender | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
多表联合查询 ：
语法：
select 字段 from 表1 inner|left|right join 表2 on 表1.字段 = 表2.字段
交叉连接，不带任何匹配条件，生迪卡尔积
mysql> select * from department, employee;
+------+--------------+----+--------+--------+------+--------+
| id | name | id | name | gender | age | dep_id |
+------+--------------+----+--------+--------+------+--------+
| 200 | 技术 | 1 | Andy | male | 18 | 200 |
| 201 | 人力资源 | 1 | Andy | male | 18 | 200 |
| 202 | 销售 | 1 | Andy | male | 18 | 200 |
| 203 | 运营 | 1 | Andy | male | 18 | 200 |
| 200 | 技术 | 2 | Mary | female | 20 | 201 |
| 201 | 人力资源 | 2 | Mary | female | 20 | 201 |
| 202 | 销售 | 2 | Mary | female | 20 | 201 |
| 203 | 运营 | 2 | Mary | female | 20 | 201 |
| 200 | 技术 | 3 | Jack | male | 25 | 201 |
| 201 | 人力资源 | 3 | Jack | male | 25 | 201 |
| 202 | 销售 | 3 | Jack | male | 25 | 201 |
| 203 | 运营 | 3 | Jack | male | 25 | 201 |
| 200 | 技术 | 4 | Lili | female | 28 | 202 |
| 201 | 人力资源 | 4 | Lili | female | 28 | 202 |
| 202 | 销售 | 4 | Lili | female | 28 | 202 |
| 203 | 运营 | 4 | Lili | female | 28 | 202 |
| 200 | 技术 | 5 | Andrew | male | 18 | 200 |
| 201 | 人力资源 | 5 | Andrew | male | 18 | 200 |
| 202 | 销售 | 5 | Andrew | male | 18 | 200 |
| 203 | 运营 | 5 | Andrew | male | 18 | 200 |
| 200 | 技术 | 6 | Lemon | female | 21 | 203 |
| 201 | 人力资源 | 6 | Lemon | female | 21 | 203 |
| 202 | 销售 | 6 | Lemon | female | 21 | 203 |
| 203 | 运营 | 6 | Lemon | female | 21 | 203 |
+------+--------------+----+--------+--------+------+--------+
24 rows in set (0.00 sec)
将人力资源修改为人力
mysql> update multi_table.department set name='人力' where id=201;
这里的迪卡尔积是将所有可能的关系都对应了一遍，
如200 技术部门与所有的员工都对应了一遍，但只有Mary Jack的对应关系是对的
内连接：只连接匹配的就行
mysql> select * from employee,department where employee.dep_id = department.id;
+----+--------+--------+------+--------+------+--------+
| id | name | gender | age | dep_id | id | name |
+----+--------+--------+------+--------+------+--------+
| 1 | Andy | male | 18 | 200 | 200 | 技术 |
| 2 | Mary | female | 20 | 201 | 201 | 人力 |
| 3 | Jack | male | 25 | 201 | 201 | 人力 |
| 4 | Lili | female | 28 | 202 | 202 | 销售 |
| 5 | Andrew | male | 18 | 200 | 200 | 技术 |
| 6 | Lemon | female | 21 | 203 | 203 | 运营 |
+----+--------+--------+------+--------+------+--------+
6 rows in set (0.00 sec)
查出技术部门员工的名字：
mysql> select employee.name from employee where employee.dep_id=department.id and department.name='技术';
ERROR 1054 (42S22): Unknown column 'department.id' in 'where clause'
select name from employee where employee.dep_id=department.id;
ERROR 1054 (42S22): Unknown column 'department.id' in 'where clause'
上面报错一是合并后有两个name,而如果 只写employee表，则没有department.id字段
mysql> select employee.name from employee, department where employee.dep_id=department.id and department.name='技术';
+--------+
| name |
+--------+
| Andy |
| Andrew |
+--------+
2 rows in set (0.00 sec)
外连接之左连接，优先显示左表全部记录
以左表为准，查找出所有员工信息
本质是，在内连接的基础上增加左边有，但右边没有的结果
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 | Andy | 技术 |
| 5 | Andrew | 技术 |
| 2 | Mary | 人力 |
| 3 | Jack | 人力 |
| 4 | Lili | 销售 |
| 6 | Lemon | 运营 |
+----+--------+-------------+
6 rows in set (0.00 sec)
外连接之右连接，优先显示右表所有记录
以打开个为准，找出所有部门信息，包括没有员工的信息
本质就是：在内连接的基础上增加右边有，左边没有的结果
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 | Andy | 技术 |
| 2 | Mary | 人力 |
| 3 | Jack | 人力 |
| 4 | Lili | 销售 |
| 5 | Andrew | 技术 |
| 6 | Lemon | 运营 |
+------+--------+-------------+
6 rows in set (0.00 sec)
全外连接，显示左右两个表全部记录
在内连接的基础上增加左边有和右边没有和右边有左边没有的结果
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 | gender | age | dep_id | id | name |
+------+--------+--------+------+--------+------+--------+
| 1 | Andy | male | 18 | 200 | 200 | 技术 |
| 5 | Andrew | male | 18 | 200 | 200 | 技术 |
| 2 | Mary | female | 20 | 201 | 201 | 人力 |
| 3 | Jack | male | 25 | 201 | 201 | 人力 |
| 4 | Lili | female | 28 | 202 | 202 | 销售 |
| 6 | Lemon | female | 21 | 203 | 203 | 运营 |
+------+--------+--------+------+--------+------+--------+
6 rows in set (0.00 sec)
union 与union all的区别， union会去掉相同的记录，而union all是left join与right join的合并，所以有重复的记录，union会将重复记录去重了。
看下面：
mysql> select * from employee left join department on employee.dep_id = department.id union all select * from employee right join department on employee.dep_id = department.id;
+------+--------+--------+------+--------+------+--------+
| id | name | gender | age | dep_id | id | name |
+------+--------+--------+------+--------+------+--------+
| 1 | Andy | male | 18 | 200 | 200 | 技术 |
| 5 | Andrew | male | 18 | 200 | 200 | 技术 |
| 2 | Mary | female | 20 | 201 | 201 | 人力 |
| 3 | Jack | male | 25 | 201 | 201 | 人力 |
| 4 | Lili | female | 28 | 202 | 202 | 销售 |
| 6 | Lemon | female | 21 | 203 | 203 | 运营 |
| 1 | Andy | male | 18 | 200 | 200 | 技术 |
| 2 | Mary | female | 20 | 201 | 201 | 人力 |
| 3 | Jack | male | 25 | 201 | 201 | 人力 |
| 4 | Lili | female | 28 | 202 | 202 | 销售 |
| 5 | Andrew | male | 18 | 200 | 200 | 技术 |
| 6 | Lemon | female | 21 | 203 | 203 | 运营 |
+------+--------+--------+------+--------+------+--------+
12 rows in set (0.00 sec)
符合条件连接查询
找出年龄大于25的员工及员工的部门
mysql> select employee.name, department.name from employee inner join department
-> on employee.dep_id = department.id
-> where age > 25;
+------+--------+
| name | name |
+------+--------+
| Lili | 销售 |
+------+--------+
1 row in set (0.00 sec)
子查询
子查询是将你的一个查询结果用括号括起来，将它交给另一个sql语句，作为它的一个查询依据来进行操作。
技术员工都有哪些人？
步骤：
看和哪个表有关，然后from找到两个表
进行连表操作
基于连表的结果过滤
mysql> select * from employee inner join department on employee.dep_id = department.id;
+----+--------+--------+------+--------+------+--------+
| id | name | gender | age | dep_id | id | name |
+----+--------+--------+------+--------+------+--------+
| 1 | Andy | male | 18 | 200 | 200 | 技术 |
| 2 | Mary | female | 20 | 201 | 201 | 人力 |
| 3 | Jack | male | 25 | 201 | 201 | 人力 |
| 4 | Lili | female | 28 | 202 | 202 | 销售 |
| 5 | Andrew | male | 18 | 200 | 200 | 技术 |
| 6 | Lemon | female | 21 | 203 | 203 | 运营 |
+----+--------+--------+------+--------+------+--------+
6 rows in set (0.01 sec)
再过滤：
mysql> select employee.name from employee inner join department on employee.dep_id = department.id where department.name='技术';
+--------+
| name |
+--------+
| Andy |
| Andrew |
+--------+
2 rows in set (0.00 sec)
用子查询 ：
先从部门表里找到技术部门id
mysql> select id from department where name='技术';
+------+
| id |
+------+
| 200 |
+------+
1 row in set (0.00 sec)
将上面的查询 结果用括号括起来，它就表示一条id=200的数据，然后通过员工表来查询dep_id=已经查到的id数据
mysql> select name from employee where dep_id=(select id from department where name='技术');
+--------+
| name |
+--------+
| Andy |
| Andrew |
+--------+
2 rows in set (0.01 sec)
带in关键字的子查询
查询员工平均年龄在25岁以上的部门，可以边表，也可以用子查询
mysql> select id,name from department
-> where id in
-> (select dep_id from employee group by dep_id having avg(age) >25);
+------+--------+
| id | name |
+------+--------+
| 202 | 销售 |
+------+--------+
1 row in set (0.01 sec)
带比较运算符的子查询，= ！=，> < !=,< <=, <>
查询大于所有人平均年龄的员工名与年龄
mysql> select avg(age) from employee;
+----------+
| avg(age) |
+----------+
| 21.6667 |
+----------+
1 row in set (0.00 sec)
mysql> select name, age from employee where age>(select avg(age) from employee);
+------+------+
| name | age |
+------+------+
| Jack | 25 |
| Lili | 28 |
+------+------+
2 rows in set (0.00 sec)
带exists关键字的查询
exists关键字表示存在，在使用exists关键字时，内层查询语句不返回查询的记录，而是返回一个真假值，True, or false; 返回True时，外层查询将进行查询，否则外层不进行查询。
department表中存在dept_id=203 ? True
mysql> select * from employee
-> where exists
-> (select id from department where id=200);
+----+--------+--------+------+--------+
| id | name | gender | age | dep_id |
+----+--------+--------+------+--------+
| 1 | Andy | male | 18 | 200 |
| 2 | Mary | female | 20 | 201 |
| 3 | Jack | male | 25 | 201 |
| 4 | Lili | female | 28 | 202 |
| 5 | Andrew | male | 18 | 200 |
| 6 | Lemon | female | 21 | 203 |
+----+--------+--------+------+--------+
