（1）准备环境
1）创建员工表
mysql> create table company.employee6(
-> emp_id int auto_increment primary key not null,
-> emp_name varchar(10),
-> age int,
-> dept_id int);
mysql> insert into employee6(emp_name,age,dept_id) values
-> ('tom',19,200),
-> ('jack',30,201),
-> ('alice',24,202),
-> ('robin',40,200),
-> ('natasha',28,204);
2）创建部门表
mysql> create table company.department(
-> dept_id int,
-> dept_name varchar(100));
mysql> insert into department values (200,'hr'), (201,'it'), (202,'sale'), (203,'fd');
（2）交叉连接
生成笛卡尔积，不使用任何匹配条件
语法：select 表1.字段1,表1.字段2,表2.字段1 from 表1,表2;
mysql> select employee6.emp_name,employee6.age,employee6.dept_id,department.dept_name from employee6,department;
+----------+------+---------+-----------+
| emp_name | age | dept_id | dept_name |
+----------+------+---------+-----------+
| tom | 19 | 200 | hr |
| tom | 19 | 200 | it |
| tom | 19 | 200 | sale |
| tom | 19 | 200 | fd |
| jack | 30 | 201 | hr |
| jack | 30 | 201 | it |
| jack | 30 | 201 | sale |
| jack | 30 | 201 | fd |
| alice | 24 | 202 | hr |
| alice | 24 | 202 | it |
| alice | 24 | 202 | sale |
| alice | 24 | 202 | fd |
| robin | 40 | 200 | hr |
| robin | 40 | 200 | it |
| robin | 40 | 200 | sale |
| robin | 40 | 200 | fd |
| natasha | 28 | 204 | hr |
| natasha | 28 | 204 | it |
| natasha | 28 | 204 | sale |
| natasha | 28 | 204 | fd |
+----------+------+---------+-----------+
（3）内连接：根据两张表的相同字段只连接匹配的行
语法：select 表1.字段n,表2.字段n from 表1,表2 表1.字段 = 表2.字段
根据员工表的dept_id 和部门表的dept_id进行连接，只匹配dept_id相同的行
mysql> select employee6.dept_id,employee6.emp_name,employee6.age,department.dept_name from employee6,department where employee6.dept_id = department.dept_id;
+---------+----------+------+-----------+
| dept_id | emp_name | age | dept_name |
+---------+----------+------+-----------+
| 200 | tom | 19 | hr |
| 201 | jack | 30 | it |
| 202 | alice | 24 | sale |
| 200 | robin | 40 | hr |
+---------+----------+------+-----------+
（4）外连接
语法：select 字段列表 from 表1 left|right join 表2 on 表1.字段 = 表2.字段
1）外连接之左连接：会显示左边表内所有的值，不论在右边表内匹不匹配
mysql> select emp_id,emp_name,age,dept_name from employee6 left join department on employee6.dept_id = department.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | tom | 19 | hr |
| 4 | robin | 40 | hr |
| 2 | jack | 30 | it |
| 3 | alice | 24 | sale |
| 5 | natasha | 28 | NULL |
+--------+----------+------+-----------+
2）外连接之右连接:会显示右边表内所有的值，不论在左边表内匹不匹配
mysql> select emp_id,emp_name,age,dept_name from employee6 right join department on employee6.dept_id = department.dept_id;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 1 | tom | 19 | hr |
| 2 | jack | 30 | it |
| 3 | alice | 24 | sale |
| 4 | robin | 40 | hr |
| NULL | NULL | NULL | fd |
+--------+----------+------+-----------+
（5）复合条件连接查询
以内连接的方式查询 employee6 和 department 表，并且 employee6 表中的 age 字段值必须大于 25，排序
mysql> select emp_id,emp_name,age,dept_name from employee6,department where employee6.dept_id = department.dept_id and age >25;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 4 | robin | 40 | hr |
| 2 | jack | 30 | it |
+--------+----------+------+-----------+
2 rows in set (0.00 sec)
mysql> select emp_id,emp_name,age,dept_name from employee6,department where employee6.dept_id = department.dept_id and age >25 order by age;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 2 | jack | 30 | it |
| 4 | robin | 40 | hr |
+--------+----------+------+-----------+
2 rows in set (0.00 sec)
mysql> select emp_id,emp_name,age,dept_name from employee6,department where employee6.dept_id = department.dept_id and age >25 order by age desc;
+--------+----------+------+-----------+
| emp_id | emp_name | age | dept_name |
+--------+----------+------+-----------+
| 4 | robin | 40 | hr |
| 2 | jack | 30 | it |
+--------+----------+------+-----------+
（6）子查询
子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果，可以为外层查询语句提供查询条件。
1）带in的子查询
mysql> select * from employee6 where dept_id in (select dept_id from department);
+--------+----------+------+---------+
| emp_id | emp_name | age | dept_id |
+--------+----------+------+---------+
| 1 | tom | 19 | 200 |
| 2 | jack | 30 | 201 |
| 3 | alice | 24 | 202 |
| 4 | robin | 40 | 200 |
+--------+----------+------+---------+
4 rows in set (0.00 sec)
2）带比较运算符的子查询
mysql> select dept_name from department where dept_id in (select dept_id from employee6 where age >25);
+-----------+
| dept_name |
+-----------+
| it |
| hr |
+-----------+
2 rows in set (0.00 sec)