8.4.３ - 数据操作 - 多表查询
多表查询
- 多表连接查询
- 复合条件连接查询
- 子查询
准备工作
create database db6 charset utf8; use db6; #建表 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 ('cmz','male',18,200), ('leco','female',48,201), ('loocha','male',38,201), ('odes','female',28,202), ('huawei','male',18,200), ('icpc','female',18,204);
mysql> create database db6 charset utf8; Query OK, 1 row affected (0.00 sec) mysql> use db6; Database changed mysql> create table department( -> id int, -> name varchar(20) -> ); Query OK, 0 rows affected (0.03 sec) mysql> 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 -> ); Query OK, 0 rows affected (0.03 sec) mysql> insert into department values -> (200,'技术'), -> (201,'人力资源'), -> (202,'销售'), -> (203,'运营'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> mysql> insert into employee(name,sex,age,dep_id) values -> ('cmz','male',18,200), -> ('leco','female',48,201), -> ('loocha','male',38,201), -> ('odes','female',28,202), -> ('huawei','male',18,200), -> ('icpc','female',18,204) -> ; Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ 4 rows in set (0.00 sec) mysql> select * from employee; +----+--------+--------+------+--------+ | id | name | sex | age | dep_id | +----+--------+--------+------+--------+ | 1 | cmz | male | 18 | 200 | | 2 | leco | female | 48 | 201 | | 3 | loocha | male | 38 | 201 | | 4 | odes | female | 28 | 202 | | 5 | huawei | male | 18 | 200 | | 6 | icpc | female | 18 | 204 | +----+--------+--------+------+--------+ 6 rows in set (0.00 sec) 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 | | | sex | 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.字段;
1 交叉连接：不适用任何匹配条件。生成笛卡尔积
mysql> select * from employee,department; +----+--------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+--------+--------+------+--------+------+--------------+ | 1 | cmz | male | 18 | 200 | 200 | 技术 | | 1 | cmz | male | 18 | 200 | 201 | 人力资源 | | 1 | cmz | male | 18 | 200 | 202 | 销售 | | 1 | cmz | male | 18 | 200 | 203 | 运营 | | 2 | leco | female | 48 | 201 | 200 | 技术 | | 2 | leco | female | 48 | 201 | 201 | 人力资源 | | 2 | leco | female | 48 | 201 | 202 | 销售 | | 2 | leco | female | 48 | 201 | 203 | 运营 | | 3 | loocha | male | 38 | 201 | 200 | 技术 | | 3 | loocha | male | 38 | 201 | 201 | 人力资源 | | 3 | loocha | male | 38 | 201 | 202 | 销售 | | 3 | loocha | male | 38 | 201 | 203 | 运营 | | 4 | odes | female | 28 | 202 | 200 | 技术 | | 4 | odes | female | 28 | 202 | 201 | 人力资源 | | 4 | odes | female | 28 | 202 | 202 | 销售 | | 4 | odes | female | 28 | 202 | 203 | 运营 | | 5 | huawei | male | 18 | 200 | 200 | 技术 | | 5 | huawei | male | 18 | 200 | 201 | 人力资源 | | 5 | huawei | male | 18 | 200 | 202 | 销售 | | 5 | huawei | male | 18 | 200 | 203 | 运营 | | 6 | icpc | female | 18 | 204 | 200 | 技术 | | 6 | icpc | female | 18 | 204 | 201 | 人力资源 | | 6 | icpc | female | 18 | 204 | 202 | 销售 | | 6 | icpc | female | 18 | 204 | 203 | 运营 | +----+--------+--------+------+--------+------+--------------+ 24 rows in set (0.01 sec)
单纯的笛卡尔级对应关系没有太多意义。
其实我门是想取出如下关系，employee.dep_id = department.id;
mysql> select * from employee,department where employee.dep_id = department.id; +----+--------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+--------+--------+------+--------+------+--------------+ | 1 | cmz | male | 18 | 200 | 200 | 技术 | | 2 | leco | female | 48 | 201 | 201 | 人力资源 | | 3 | loocha | male | 38 | 201 | 201 | 人力资源 | | 4 | odes | female | 28 | 202 | 202 | 销售 | | 5 | huawei | male | 18 | 200 | 200 | 技术 | +----+--------+--------+------+--------+------+--------------+ 5 rows in set (0.00 sec)
我们有专门的内连接。
mysql> select * from employee inner join department on employee.dep_id = department.id; +----+--------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+--------+--------+------+--------+------+--------------+ | 1 | cmz | male | 18 | 200 | 200 | 技术 | | 2 | leco | female | 48 | 201 | 201 | 人力资源 | | 3 | loocha | male | 38 | 201 | 201 | 人力资源 | | 4 | odes | female | 28 | 202 | 202 | 销售 | | 5 | huawei | male | 18 | 200 | 200 | 技术 | +----+--------+--------+------+--------+------+--------------+ 5 rows in set (0.00 sec)
连表操作
１．内连接，只取两张表的共同部分
２．左连接 在内连接的基础上保留左表的记录
３．右连接 在内连接的基础上保留右边表的记录
４．全外连接 在内连接的基础上保留左右边没有对应关系的记录（左右连接的去重）
连表操作 １．内连接，只取两张表的共同部分 select * from employee inner join department on employee.dep_id = department.id; mysql> select * from employee inner join department on employee.dep_id = department.id; +----+--------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+--------+--------+------+--------+------+--------------+ | 1 | cmz | male | 18 | 200 | 200 | 技术 | | 2 | leco | female | 48 | 201 | 201 | 人力资源 | | 3 | loocha | male | 38 | 201 | 201 | 人力资源 | | 4 | odes | female | 28 | 202 | 202 | 销售 | | 5 | huawei | male | 18 | 200 | 200 | 技术 | +----+--------+--------+------+--------+------+--------------+ 5 rows in set (0.00 sec) ２．左连接 在内连接的基础上保留左表的记录 select * from employee left join department on employee.dep_id = department.id; mysql> select * from employee left join department on employee.dep_id = department.id; +----+--------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+--------+--------+------+--------+------+--------------+ | 1 | cmz | male | 18 | 200 | 200 | 技术 | | 5 | huawei | male | 18 | 200 | 200 | 技术 | | 2 | leco | female | 48 | 201 | 201 | 人力资源 | | 3 | loocha | male | 38 | 201 | 201 | 人力资源 | | 4 | odes | female | 28 | 202 | 202 | 销售 | | 6 | icpc | female | 18 | 204 | NULL | NULL | +----+--------+--------+------+--------+------+--------------+ 6 rows in set (0.01 sec) ３．右连接 在内连接的基础上保留右边表的记录 select * from employee right join department on employee.dep_id = department.id; mysql> select * from employee right join department on employee.dep_id = department.id; +------+--------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+--------+--------+------+--------+------+--------------+ | 1 | cmz | male | 18 | 200 | 200 | 技术 | | 2 | leco | female | 48 | 201 | 201 | 人力资源 | | 3 | loocha | male | 38 | 201 | 201 | 人力资源 | | 4 | odes | female | 28 | 202 | 202 | 销售 | | 5 | huawei | male | 18 | 200 | 200 | 技术 | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+--------+--------+------+--------+------+--------------+ 6 rows in set (0.00 sec) ４．全外连接 在内连接的基础上保留左右边没有对应关系的记录（左右连接的去重） 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; 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 | cmz | male | 18 | 200 | 200 | 技术 | | 5 | huawei | male | 18 | 200 | 200 | 技术 | | 2 | leco | female | 48 | 201 | 201 | 人力资源 | | 3 | loocha | male | 38 | 201 | 201 | 人力资源 | | 4 | odes | female | 28 | 202 | 202 | 销售 | | 6 | icpc | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+--------+--------+------+--------+------+--------------+ 7 rows in set (0.00 sec)
小练习
＃查询平均年龄大于３０的部门名 select department.name,avg(age) from employee inner join department on employee.dep_id = department.id group by department.name having avg(age)>30; #操作过程 mysql> select department.name,avg(age) from employee inner join department on employee.dep_id = department.id -> group by department.name -> having avg(age)>30; +--------------+----------+ | name | avg(age) | +--------------+----------+ | 人力资源 | 43.0000 | +--------------+----------+ 1 row in set (0.00 sec)
