MySQL的多表查询
多表查询：
#建表 mysql> create table 部门( -> id int, -> name varchar(20) -> ); Query OK, 0 rows affected (0.48 sec) 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.24 sec) # 插入数据 mysql> insert into 部门 values -> (200,'技术'), -> (201,'人力资源'), -> (202,'销售'), -> (203,'运营'); Query OK, 4 rows affected (0.11 sec) Records: 4 Duplicates: 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.09 sec) Records: 6 Duplicates: 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.19 sec) 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.04 sec) # 查看整张表 mysql> select * from 部门; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ 4 rows in set (0.03 sec) 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.01 sec) 部门表与雇员表
多表连接查询：
外链接语法： 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.00 sec) #部门表中没有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.00 sec) #注意 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.00 sec) #查看技术部员工姓名 mysql> select name from 雇员 -> where dep_id in -> (select id from 部门 where name='技术'); +-----------+ | name | +-----------+ | 张明 | | 张国民 | +-----------+ 2 rows in set (0.00 sec) #查看不足1人的部门名(子查询得到的是有人的部门id) mysql> select name from 部门 where id not in (select distinct dep_id from 雇员); +--------+ | name | +--------+ | 运营 | +--------+ 1 row in set (0.00 sec)
带比较运算符的子查询
比较运算符：= 、 !=、> 、<等
需求1：查询大于所有人平均年龄的员工名与年龄
需求2：查询大于部门内平均年龄的员工名、年龄
#查询大于所有人平均年龄的员工名与年龄 mysql> select name,age from 雇员 where age > (select avg(age) from 雇员); +-----------+------+ | name | age | +-----------+------+ | 李国加 | 48 | | 彭达五 | 38 | +-----------+------+ 2 rows in set (0.00 sec) #查询大于部门内平均年龄的员工名、年龄 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.00 sec) #department表中存在dept_id=205，False mysql> select * from 雇员 where exists (select id from 部门 where id=204); Empty set (0.00 sec)
