mysql_query多表_MySQL的多表查询

多表查询：

#建表

mysql>create table 部门(->id int,-> name varchar(20)->);

Query OK, 0 rows affected (0.48sec)

mysql>create table 雇员(->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.24sec)#插入数据

mysql>insert into 部门 values-> (200,'技术'),-> (201,'人力资源'),-> (202,'销售'),-> (203,'运营');

Query OK,4 rows affected (0.11sec)

Records:4Duplicates: 0 Warnings: 0

mysql>insert into 雇员(name,sex,age,dep_id) values-> ('张明','male',18,200),-> ('李国加','female',48,201),-> ('彭达五','male',38,201),-> ('找刘鹏','female',28,202),-> ('张国民','male',18,200),-> ('两架于','female',18,204);

Query OK,6 rows affected (0.09sec)

Records:6Duplicates: 0 Warnings: 0#查看表结构

mysql>desc 部门;+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| name | varchar(20) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.19sec)

mysql>desc 雇员;+--------+-----------------------+------+-----+---------+----------------+

| 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.04sec)#查看整张表

mysql> select * from部门;+------+--------------+

| id | name |

+------+--------------+

| 200 | 技术 |

| 201 | 人力资源 |

| 202 | 销售 |

| 203 | 运营 |

+------+--------------+

4 rows in set (0.03sec)

mysql> select * from雇员;+----+-----------+--------+------+--------+

| id | name | sex | age | dep_id |

+----+-----------+--------+------+--------+

| 1 | 张明 | male | 18 | 200 |

| 2 | 李国加 | female | 48 | 201 |

| 3 | 彭达五 | male | 38 | 201 |

| 4 | 找刘鹏 | female | 28 | 202 |

| 5 | 张国民 | male | 18 | 200 |

| 6 | 两架于 | female | 18 | 204 |

+----+-----------+--------+------+--------+

6 rows in set (0.01sec)

部门表与雇员表

数据准备

多表连接查询：

外链接语法：

select 字段列表from 表1 INNER|LEFT|RIGHT JOIN 表2

ON 表1.字段= 表2.字段;

1.交叉连接：不适用任何匹配条件

语法select * from 表名1,表名2;

mysql> select * from部门,雇员;+------+--------------+----+-----------+--------+------+--------+

| id | name | id | name | sex | age | dep_id |

+------+--------------+----+-----------+--------+------+--------+

| 200 | 技术 | 1 | 张明 | male | 18 | 200 |

| 201 | 人力资源 | 1 | 张明 | male | 18 | 200 |

| 202 | 销售 | 1 | 张明 | male | 18 | 200 |

| 203 | 运营 | 1 | 张明 | male | 18 | 200 |

| 200 | 技术 | 2 | 李国加 | female | 48 | 201 |

| 201 | 人力资源 | 2 | 李国加 | female | 48 | 201 |

| 202 | 销售 | 2 | 李国加 | female | 48 | 201 |

| 203 | 运营 | 2 | 李国加 | female | 48 | 201 |

| 200 | 技术 | 3 | 彭达五 | male | 38 | 201 |

| 201 | 人力资源 | 3 | 彭达五 | male | 38 | 201 |

| 202 | 销售 | 3 | 彭达五 | male | 38 | 201 |

| 203 | 运营 | 3 | 彭达五 | male | 38 | 201 |

| 200 | 技术 | 4 | 找刘鹏 | female | 28 | 202 |

| 201 | 人力资源 | 4 | 找刘鹏 | female | 28 | 202 |

| 202 | 销售 | 4 | 找刘鹏 | female | 28 | 202 |

| 203 | 运营 | 4 | 找刘鹏 | female | 28 | 202 |

| 200 | 技术 | 5 | 张国民 | male | 18 | 200 |

| 201 | 人力资源 | 5 | 张国民 | male | 18 | 200 |

| 202 | 销售 | 5 | 张国民 | male | 18 | 200 |

| 203 | 运营 | 5 | 张国民 | male | 18 | 200 |

| 200 | 技术 | 6 | 两架于 | female | 18 | 204 |

| 201 | 人力资源 | 6 | 两架于 | female | 18 | 204 |

| 202 | 销售 | 6 | 两架于 | female | 18 | 204 |

| 203 | 运营 | 6 | 两架于 | female | 18 | 204 |

+------+--------------+----+-----------+--------+------+--------+

24 rows in set (0.00 sec)

交叉连接

表已表之间的连接分为两种：内连，外连

内连接：只连接匹配的行

select * from 表1,表2 where 条件

select * from 表1 inner join 表2 on 条件

select * from 部门 inner join 雇员 on 部门 .id = 雇员 .dep_id;

mysql> select * from 部门 inner join 雇员 on 部门.id =雇员.dep_id;+------+--------------+----+-----------+--------+------+--------+

| id | name | id | name | sex | age | dep_id |

+------+--------------+----+-----------+--------+------+--------+

| 200 | 技术 | 1 | 张明 | male | 18 | 200 |

| 201 | 人力资源 | 2 | 李国加 | female | 48 | 201 |

| 201 | 人力资源 | 3 | 彭达五 | male | 38 | 201 |

| 202 | 销售 | 4 | 找刘鹏 | female | 28 | 202 |

| 200 | 技术 | 5 | 张国民 | male | 18 | 200 |

+------+--------------+----+-----------+--------+------+--------+

5 rows in set (0.11 sec)

select * from 部门 as t1 inner join 雇员 as t2 on t1.id = t2.dep_id;

mysql> select * from 部门 as t1 inner join 雇员 as t2 on t1.id =t2.dep_id;+------+--------------+----+-----------+--------+------+--------+

| id | name | id | name | sex | age | dep_id |

+------+--------------+----+-----------+--------+------+--------+

| 200 | 技术 | 1 | 张明 | male | 18 | 200 |

| 201 | 人力资源 | 2 | 李国加 | female | 48 | 201 |

| 201 | 人力资源 | 3 | 彭达五 | male | 38 | 201 |

| 202 | 销售 | 4 | 找刘鹏 | female | 28 | 202 |

| 200 | 技术 | 5 | 张国民 | male | 18 | 200 |

+------+--------------+----+-----------+--------+------+--------+

5 rows in set (0.00 sec)

需求：找出部门表跟雇员表共有的部分

mysql> select 雇员.id,雇员.name,雇员.age,雇员.sex,部门.name from雇员-> inner join 部门 on 雇员.dep_id=部门.id;+----+-----------+------+--------+--------------+

| id | name | age | sex | name |

+----+-----------+------+--------+--------------+

| 1 | 张明 | 18 | male | 技术 |

| 2 | 李国加 | 48 | female | 人力资源 |

| 3 | 彭达五 | 38 | male | 人力资源 |

| 4 | 找刘鹏 | 28 | female | 销售 |

| 5 | 张国民 | 18 | male | 技术 |

+----+-----------+------+--------+--------------+

5 rows in set (0.00sec)#部门表中没有204所有雇员表中关于204的员工信息也不会显示出来#上面sql语句等于：

mysql> select 雇员.id,雇员.name,雇员.age,雇员.sex,部门.name from 雇员,部门where 雇员.dep_id=部门.id;

外连接：左右全连接

左外连接：优先显示左表全部记录 left join ... on ...

select * from 表1 left join 表2 on 条件

select * from department as t1 left join employee as t2 on t1.id = t2.dep_id;

#以左表为准，即找出所有员工信息，当然包括没有部门的员工#本质就是：在内连接的基础上增加左边有右边没有的结果

mysql>select 雇员.id,雇员.name,部门.name as depart_name-> from 雇员 left join 部门 on 雇员.dep_id=部门.id;+----+-----------+--------------+

| id | name | depart_name |

+----+-----------+--------------+

| 1 | 张明 | 技术 |

| 5 | 张国民 | 技术 |

| 2 | 李国加 | 人力资源 |

| 3 | 彭达五 | 人力资源 |

| 4 | 找刘鹏 | 销售 |

| 6 | 两架于 | NULL |

+----+-----------+--------------+

6 rows in set (0.00 sec)

外连接之左连接

右外连接：优先显示右表全部记录 right join ... on ...

select * from 表1 right join 表2 on 条件

select * from department as t1 right join employee as t2 on t1.id = t2.dep_id

#以右表为准，即找出所有部门信息，包括没有员工的部门#本质就是：在内连接的基础上增加右边有左边没有的结果

mysql>select 雇员.id,雇员.name,部门.name as depart_name-> from 雇员 right join 部门 on 雇员.dep_id=部门.id;+------+-----------+--------------+

| id | name | depart_name |

+------+-----------+--------------+

| 1 | 张明 | 技术 |

| 2 | 李国加 | 人力资源 |

| 3 | 彭达五 | 人力资源 |

| 4 | 找刘鹏 | 销售 |

| 5 | 张国民 | 技术 |

| NULL | NULL | 运营 |

+------+-----------+--------------+

6 rows in set (0.00 sec)

外连接之右连接

全外连接:显示左右两个表全部记录full join

select * from department as t1 left join employee as t2 on t1.id = t2.dep_id

union

select * from department as t1 right join employee as t2 on t1.id = t2.dep_id;

#全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果#注意：mysql不支持全外连接 full JOIN#强调：mysql可以使用此种方式间接实现全外连接

mysql> select * from 雇员 left join 部门 on 雇员.dep_id =部门.id->union-> select * from 雇员 right join 部门 on 雇员.dep_id =部门.id;+------+-----------+--------+------+--------+------+--------------+

| id | name | sex | age | dep_id | id | name |

+------+-----------+--------+------+--------+------+--------------+

| 1 | 张明 | male | 18 | 200 | 200 | 技术 |

| 5 | 张国民 | male | 18 | 200 | 200 | 技术 |

| 2 | 李国加 | female | 48 | 201 | 201 | 人力资源 |

| 3 | 彭达五 | male | 38 | 201 | 201 | 人力资源 |

| 4 | 找刘鹏 | female | 28 | 202 | 202 | 销售 |

| 6 | 两架于 | female | 18 | 204 | NULL | NULL |

| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |

+------+-----------+--------+------+--------+------+--------------+

7 rows in set (0.00sec)#注意 union与union all的区别：union会去掉相同的纪录

外连接之全连接

符合条件连接查询：

需求1：找出年龄大于25岁的员工以及员工所在的部门

mysql> select 雇员.name,部门.name from雇员 inner join 部门-> on 雇员.dep_id =部门.id-> where age > 25;+-----------+--------------+

| name | name |

+-----------+--------------+

| 李国加 | 人力资源 |

| 彭达五 | 人力资源 |

| 找刘鹏 | 销售 |

+-----------+--------------+

3 rows in set (0.00 sec)

需求2：以内连接的方式查询雇员表和部门表，并且以age字段的升序方式显示

mysql> select 雇员.id,部门.name,雇员.age,部门.name from雇员,部门-> where 雇员.dep_id =部门.id-> and age > 25

->order by age asc;+----+--------------+------+--------------+

| id | name | age | name |

+----+--------------+------+--------------+

| 4 | 销售 | 28 | 销售 |

| 3 | 人力资源 | 38 | 人力资源 |

| 2 | 人力资源 | 48 | 人力资源 |

+----+--------------+------+--

所谓连表就是把两张表连接在一起之后 就变成一张大表 从from开始一直到on条件结束就看做一张表,之后 where 条件 group by 分组 order by limit 都正常的使用就可以了

子查询：

1：子查询是将一个查询语句嵌套在另一个查询语句中。

2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。

3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字

4：还可以包含比较运算符：= 、 !=、> 、

带in关键字的子查询：

需求1：查看平均年龄在25岁以上的部门名

需求2：查看技术部员工姓名

需求3：查看不足1人的部名(子查询得到的是有人的部门id)

#需求1：

mysql> select id,name from部门-> where id in

-> (select dep_id from 雇员 group by dep_id having avg(age) > 25);+------+--------------+

| id | name |

+------+--------------+

| 201 | 人力资源 |

| 202 | 销售 |

+------+--------------+

2 rows in set (0.00sec)#查看技术部员工姓名

mysql> select name from雇员-> where dep_id in

-> (select id from 部门 where name='技术');+-----------+

| name |

+-----------+

| 张明 |

| 张国民 |

+-----------+

2 rows in set (0.00sec)#查看不足1人的部门名(子查询得到的是有人的部门id)

mysql> select name from 部门 where id not in (select distinct dep_id from雇员);+--------+

| name |

+--------+

| 运营 |

+--------+

1 row in set (0.00 sec)

in关键字的子查询

带比较运算符的子查询

比较运算符：= 、 !=、> 、

需求1：查询大于所有人平均年龄的员工名与年龄

需求2：查询大于部门内平均年龄的员工名、年龄

#查询大于所有人平均年龄的员工名与年龄

mysql> select name,age from 雇员 where age > (select avg(age) from雇员);+-----------+------+

| name | age |

+-----------+------+

| 李国加 | 48 |

| 彭达五 | 38 |

+-----------+------+

2 rows in set (0.00sec)#查询大于部门内平均年龄的员工名、年龄

mysql> select name,age from雇员 as t1 inner join-> (select dep_id,avg(age) avg_age from雇员 group by dep_id) as t2-> on t1.dep_id = t2.dep_id where age>avg_age;+-----------+------+

| name | age |

+-----------+------+

| 李国加 | 48 |

+-----------+------+

1 row in set (0.00 sec)

比较运算符子查询

3 带EXISTS关键字的子查询：

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。True或False当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。

#department表中存在dept_id=203，Ture

mysql> select * from 雇员 where exists (select id from 部门 where id=200);+----+-----------+--------+------+--------+

| id | name | sex | age | dep_id |

+----+-----------+--------+------+--------+

| 1 | 张明 | male | 18 | 200 |

| 2 | 李国加 | female | 48 | 201 |

| 3 | 彭达五 | male | 38 | 201 |

| 4 | 找刘鹏 | female | 28 | 202 |

| 5 | 张国民 | male | 18 | 200 |

| 6 | 两架于 | female | 18 | 204 |

+----+-----------+--------+------+--------+

6 rows in set (0.00sec)#department表中存在dept_id=205，False

mysql> select * from 雇员 where exists (select id from 部门 where id=204);

Empty set (0.00 sec)

带exists关键字子查询