准备工作：准备两张表，部门表（department）、员工表（employee）
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), ('nvshen','male',18,200), ('xiaomage','female',18,204) ; # 查看表结构和数据 mysql> desc department; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.19 sec) mysql> desc employee; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(11) | YES | | NULL | | | dep_id | int(11) | YES | | NULL | | +--------+-----------------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec) mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ 4 rows in set (0.02 sec) mysql> select * from employee; +----+----------+--------+------+--------+ | 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 | nvshen | male | 18 | 200 | | 6 | xiaomage | female | 18 | 204 | +----+----------+--------+------+--------+ 6 rows in set (0.00 sec)
多表连接查询：
两张表的准备工作已完成，比如现在我要查询的员工信息以及该员工所在的部门。从该题中，我们看出既要查员工又要查该员工的部门，肯定要将两张表进行连接查询，多表连接查询。
外连接语法：
select 字段列表 from 表1 inner|left|right join 表2 on 表1.字段 = 表2.字段；
（1）先看第一种情况交叉连接：不适用任何匹配条件。生成笛卡尔积（关于笛卡尔积的含义，大家百度自行补脑）。
mysql> select * from employe,department; +----+----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+----------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 1 | egon | male | 18 | 200 | 201 | 人力资源 | | 1 | egon | male | 18 | 200 | 202 | 销售 | | 1 | egon | male | 18 | 200 | 203 | 运营 | | 2 | alex | female | 48 | 201 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 2 | alex | female | 48 | 201 | 202 | 销售 | | 2 | alex | female | 48 | 201 | 203 | 运营 | | 3 | wupeiqi | male | 38 | 201 | 200 | 技术 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 202 | 销售 | | 3 | wupeiqi | male | 38 | 201 | 203 | 运营 | | 4 | yuanhao | female | 28 | 202 | 200 | 技术 | | 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 4 | yuanhao | female | 28 | 202 | 203 | 运营 | | 5 | nvshen | male | 18 | 200 | 200 | 技术 | | 5 | nvshen | male | 18 | 200 | 201 | 人力资源 | | 5 | nvshen | male | 18 | 200 | 202 | 销售 | | 5 | nvshen | male | 18 | 200 | 203 | 运营 | | 6 | xiaomage | female | 18 | 204 | 200 | 技术 | | 6 | xiaomage | female | 18 | 204 | 201 | 人力资源 | | 6 | xiaomage | female | 18 | 204 | 202 | 销售 | | 6 | xiaomage | female | 18 | 204 | 203 | 运营 | +----+----------+--------+------+--------+------+--------------+ 24 rows in set (0.00 sec)
（2）内连接：只连接匹配的行
找到两张表共有的部分，相当于利用条件从笛卡儿积结果中筛选出了匹配的结果。
department没有204这个部门，因而employe表中关于204这条员工信息没有匹配出来。
mysql> select employe.id,employe.name,employe.age,employe.sex,department.name from employe inner join department on employe.dep_id=department.id; +----+---------+------+--------+--------------+ | id | name | age | sex | name | +----+---------+------+--------+--------------+ | 1 | egon | 18 | male | 技术 | | 2 | alex | 48 | female | 人力资源 | | 3 | wupeiqi | 38 | male | 人力资源 | | 4 | yuanhao | 28 | female | 销售 | | 5 | nvshen | 18 | male | 技术 | +----+---------+------+--------+--------------+ 5 rows in set (0.00 sec)
##上述sql等同于
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
（3）外链接之左连接：优先显示左表全部记录
以左表为准，即找出所有员工信息，当然包括没有部门的员工。本质就是，在内连接的基础上增加左边有，右边灭有的结果。
mysql> select employe.id,employe.name,department.name as depart_name from employe left join department on employe.dep_id = department.id; +----+----------+--------------+ | id | name | depart_name | +----+----------+--------------+ | 1 | egon | 技术 | | 5 | nvshen | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 6 | xiaomage | NULL | +----+----------+--------------+ 6 rows in set (0.00 sec)
（4） 外链接之右连接：优先显示右表全部记录
以右表为准，即找出所有部门信息，包括没有员工的部门，本质就是，在内连接的基础上增加右边有，左边没有的结果。
mysql> select employe.id,employe.name,department.name as depart_name from employe right join department on employe.dep_id = department.id; +------+---------+--------------+ | id | name | depart_name | +------+---------+--------------+ | 1 | egon | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 5 | nvshen | 技术 | | NULL | NULL | 运营 | +------+---------+--------------+ 6 rows in set (0.00 sec)
（5） 全外连接：显示左右两个表全部记录(了解)
外连接：在内连接的基础上增加左边有，右边没有的或者是右边有，左边没有的结果。
注意：mysql不支持全外连接 full join
强调：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;
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;
union 与 union all 的区别：union会去掉相同的记录。
mysql> select * from employe left join department on employe.dep_id = department.id union all select * from employe right join department on employe.dep_id = department.id; +------+----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+----------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | nvshen | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | xiaomage | female | 18 | 204 | NULL | NULL | | 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 | nvshen | male | 18 | 200 | 200 | 技术 | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+----------+--------+------+--------+------+--------------+ 12 rows in set (0.01 sec) mysql> select * from employe left join department on employe.dep_id = department.id union select * from employe right join department on employe.dep_id = department.id; +------+----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+----------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | nvshen | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | xiaomage | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+----------+--------+------+--------+------+--------------+ 7 rows in set (0.00 sec)
符合条件查询：
找出年龄大于25岁的员工所在的部门：
mysql> select employe.name,employe.age,department.name from employe join department on employe.dep_id = department.id where age>25; +---------+------+--------------+ | name | age | name | +---------+------+--------------+ | alex | 48 | 人力资源 | | wupeiqi | 38 | 人力资源 | | yuanhao | 28 | 销售 | +---------+------+--------------+ 3 rows in set (0.00 sec)
以内链接的方式查询employe 和 department 表，并且以age字段的升序方式显示：
mysql> select * from employe join department on employe.dep_id = department.id order by age desc; +----+---------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+---------+--------+------+--------+------+--------------+ | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | nvshen | male | 18 | 200 | 200 | 技术 | +----+---------+--------+------+--------+------+--------------+ 5 rows in set (0.00 sec) mysql>
子查询：
1，子查询是将一个查询语句嵌套在另一个查询语句中。
2，内层查询语句结果，可以为外层查询语句提供查询条件。
3，子查询中可以包含： in , not , in , any , all ,exists , not exists 等关键字。
4，还可以包含比较运算符： = ， != ，>，< 等
带 in 关键字的查询：
查询平均年龄在25岁以上的部门名。
mysql> select department.name from department where id in (select dep_id from employe where age > 25); +--------------+ | name | +--------------+ | 人力资源 | | 销售 | +--------------+ 2 rows in set (0.00 sec)
查看技术部员工的姓名：
mysql> select employe.name from employe where dep_id in (select id from department where department.name = '技术'); +--------+ | name | +--------+ | egon | | nvshen | +--------+ 2 rows in set (0.00 sec)
查看不足1人的部门：
（1） mysql> select department.name from department where id not in (select dep_id from employe inner join department where department.id = employe.dep_id); +--------+ | name | +--------+ | 运营 | +--------+ 1 row in set (0.00 sec) （2） mysql> select name from department where id not in (select dep_id from employe group by dep_id); +--------+ | name | +--------+ | 运营 | +--------+ 1 row in set (0.00 sec)
带比较运算符的子查询：
查询大于所有人平均年龄的员工名与年龄：
mysql> select employe.name,employe.age from employe where age > (select avg(age) from employe); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ 2 rows in set (0.00 sec)
查询大于部门内平均年龄的员工名：
mysql> select employe.name,employe.age from employe inner join (select dep_id,avg(age) as b from employe group by dep_id) as A on employe.dep_id = A.dep_id where employe.age>A.b; +------+------+ | name | age | +------+------+ | alex | 48 | +------+------+ 1 row in set (0.00 sec)
##注意：where 后不能直接用聚合函数，若想用就在之前为聚合函数取一个别名，然后再后面用这个别名调用。
带exists关键字的子查询：
exists 关键字表示存在，在使用exists关键字时，内层查询语句不返回查询记录，而是返回一个真假值。True或False。
当返回True时，外层查询语句将进行查询：当返回值为False时，外层查询语句不进行查询。
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 | nvshen | male | 18 | 200 | | 6 | xiaomage | 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)
查询每个部门中最新入职的员工：
create table employee( id int not null unique auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); #查看表结构 mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+ #插入记录 #三个部门：教学，销售，运营 insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部 ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('jinxin','male',18,'19000301','teacher',30000,401,1), ('成龙','male',48,'20101111','teacher',10000,401,1), ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 ('丫丫','female',38,'20101101','sale',2000.35,402,2), ('丁丁','female',18,'20110312','sale',1000.37,402,2), ('星星','female',18,'20160513','sale',3000.29,402,2), ('格格','female',28,'20170127','sale',4000.33,402,2), ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3) ; （1） mysql> select employee.name,employee.hire_date from employee where hire_date in (select max(hire_date) from employee group by post); +--------+------------+ | name | hire_date | +--------+------------+ | egon | 2017-03-01 | | alex | 2015-03-02 | | 格格 | 2017-01-27 | | 张野 | 2016-03-11 | +--------+------------+ 4 rows in set (0.00 sec) mysql> （2） select * from employee as t1 inner join (select post,max(hire_date) as new_date from employee group by post) as t2 on t1.post=t2.post where t1.hire_date=t2.new_date;