mysql查询2个isbn数据_（七）MySQL数据操作DQL:多表查询2

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