MySQL学习10 – 多表查询

本节重点:

  • 多表连接查询
  • 符合条件连接查询
  • 子查询

准备工作:准备两张表,部门表(department)、员工表(employee)

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('egon','male',18,200),('alex','female',48,201),('wupeiqi','male',38,201),('yuanhao','female',28,202),('nvshen','male',18,200),('xiaomage','female',18,204);# 查看表结构和数据mysql> desc department;+-------+-------------+------+-----+---------+-------+| 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 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.01 sec)mysql> select * from department;+------+--------------+| id | name |+------+--------------+| 200 | 技术 || 201 | 人力资源 || 202 | 销售 || 203 | 运营 |+------+--------------+4 rows in set (0.02 sec)mysql> select * from employee;+----+----------+--------+------+--------+| id | name | sex | age | dep_id |+----+----------+--------+------+--------+| 1 | egon | male | 18 | 200 || 2 | alex | female | 48 | 201 || 3 | wupeiqi | male | 38 | 201 || 4 | yuanhao | female | 28 | 202 || 5 | nvshen | male | 18 | 200 || 6 | xiaomage | female | 18 | 204 |+----+----------+--------+------+--------+6 rows in set (0.00 sec)

ps:观察两张表,发现department表中id=203部门在employee中没有对应的员工,发现employee中id=6的员工在department表中没有对应关系。

一、多表连接查询

两张表的准备工作已完成,比如现在我要查询的员工信息以及该员工所在的部门。从该题中,我们看出既要查员工又要查该员工的部门,肯定要将两张表进行连接查询,多表连接查询。

重点:外链接语法

语法:

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 | egon | male | 18 | 200 | 200 | 技术 || 1 | egon | male | 18 | 200 | 201 | 人力资源 || 1 | egon | male | 18 | 200 | 202 | 销售 || 1 | egon | male | 18 | 200 | 203 | 运营 || 2 | alex | female | 48 | 201 | 200 | 技术 || 2 | alex | female | 48 | 201 | 201 | 人力资源 || 2 | alex | female | 48 | 201 | 202 | 销售 || 2 | alex | female | 48 | 201 | 203 | 运营 || 3 | wupeiqi | male | 38 | 201 | 200 | 技术 || 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 || 3 | wupeiqi | male | 38 | 201 | 202 | 销售 || 3 | wupeiqi | male | 38 | 201 | 203 | 运营 || 4 | yuanhao | female | 28 | 202 | 200 | 技术 || 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 || 4 | yuanhao | female | 28 | 202 | 202 | 销售 || 4 | yuanhao | female | 28 | 202 | 203 | 运营 || 5 | nvshen | male | 18 | 200 | 200 | 技术 || 5 | nvshen | male | 18 | 200 | 201 | 人力资源 || 5 | nvshen | male | 18 | 200 | 202 | 销售 || 5 | nvshen | male | 18 | 200 | 203 | 运营 || 6 | xiaomage | female | 18 | 204 | 200 | 技术 || 6 | xiaomage | female | 18 | 204 | 201 | 人力资源 || 6 | xiaomage | female | 18 | 204 | 202 | 销售 || 6 | xiaomage | female | 18 | 204 | 203 | 运营 |

2.内连接

内连接:只连接匹配的行

#找两张表共有的部分,相当于利用条件从笛卡尔积结果中筛选出了匹配的结果#department没有204这个部门,因而employee表中关于204这条员工信息没有匹配出来mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;+----+---------+------+--------+--------------+| id | name | age | sex | name |+----+---------+------+--------+--------------+| 1 | egon | 18 | male | 技术 || 2 | alex | 48 | female | 人力资源 || 3 | wupeiqi | 38 | male | 人力资源 || 4 | yuanhao | 28 | female | 销售 || 5 | nvshen | 18 | male | 技术 |+----+---------+------+--------+--------------+5 rows in set (0.00 sec)#上述sql等同于mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;

3.外连接之左连接

左连接:优先显示左表全部记录

#以左表为准,即找出所有员工信息,当然包括没有部门的员工#本质就是:在内连接的基础上增加左边有,右边没有的结果mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;+----+----------+--------------+| id | name | depart_name |+----+----------+--------------+| 1 | egon | 技术 || 5 | nvshen | 技术 || 2 | alex | 人力资源 || 3 | wupeiqi | 人力资源 || 4 | yuanhao | 销售 || 6 | xiaomage | NULL |+----+----------+--------------+6 rows in set (0.00 sec)

4.外连接之右连接

右连接:优先显示右表全部记录

#以右表为准,即找出所有部门信息,包括没有员工的部门#本质就是:在内连接的基础上增加右边有,左边没有的结果mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;+------+---------+--------------+| id | name | depart_name |+------+---------+--------------+| 1 | egon | 技术 || 2 | alex | 人力资源 || 3 | wupeiqi | 人力资源 || 4 | yuanhao | 销售 || 5 | nvshen | 技术 || NULL | NULL | 运营 |+------+---------+--------------+6 rows in set (0.00 sec)

5.全外连接

全外连接:显示左右两个表全部记录(了解)

#外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果#注意:mysql不支持全外连接 full JOIN#强调:mysql可以使用此种方式间接实现全外连接语法:select * from employee left join department on employee.dep_id = department.id union all 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 | egon | male | 18 | 200 | 200 | 技术 || 5 | nvshen | male | 18 | 200 | 200 | 技术 || 2 | alex | female | 48 | 201 | 201 | 人力资源 || 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 || 4 | yuanhao | female | 28 | 202 | 202 | 销售 || 6 | xiaomage | female | 18 | 204 | NULL | NULL || NULL | NULL | NULL | NULL | NULL | 203 | 运营 |+------+----------+--------+------+--------+------+--------------+7 rows in set (0.01 sec)#注意 union与union all的区别:union会去掉相同的纪录

二、符合条件连接查询

示例1:以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门

select employee.name,department.name from employee inner join department  on employee.dep_id = department.id  where age > 25;

示例2:以内连接的方式查询employee和department表,并且以age字段的升序方式显示。

select employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc;

三、子查询

#1:子查询是将一个查询语句嵌套在另一个查询语句中。#2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。#3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字#4:还可以包含比较运算符:= 、 !=、> 、<等

1.带in关键字的子查询

#查询平均年龄在25岁以上的部门名select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);# 查看技术部员工姓名select name from employee where dep_id in (select id from department where name='技术');#查看不足1人的部门名select name from department where id not in (select dep_id from employee group by dep_id);

2.带比较运算符的子查询

#比较运算符:=、!=、>、>=、<、<=、<>#查询大于所有人平均年龄的员工名与年龄mysql> select name,age from employee where age > (select avg(age) from employee);+---------+------+| name | age |+---------+------+| alex | 48 || wupeiqi | 38 |+---------+------+#查询大于部门内平均年龄的员工名、年龄思路: (1)先对员工表(employee)中的人员分组(group by),查询出dep_id以及平均年龄。 (2)将查出的结果作为临时表,再对根据临时表的dep_id和employee的dep_id作为筛选条件将employee表和临时表进行内连接。 (3)最后再将employee员工的年龄是大于平均年龄的员工名字和年龄筛选。mysql> select t1.name,t1.age from employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;+------+------+| name | age |+------+------+| alex | 48 |

3.带EXISTS关键字的子查询

#EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录。而是返回一个真假值。True或False#当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询#department表中存在dept_id=203,Turemysql> select * from employee where exists (select id from department where id=200);+----+----------+--------+------+--------+| id | name | sex | age | dep_id |+----+----------+--------+------+--------+| 1 | egon | male | 18 | 200 || 2 | alex | female | 48 | 201 || 3 | wupeiqi | male | 38 | 201 || 4 | yuanhao | female | 28 | 202 || 5 | nvshen | male | 18 | 200 || 6 | xiaomage | female | 18 | 204 |+----+----------+--------+------+--------+#department表中存在dept_id=205,Falsemysql> select * from employee where exists (select id from department where id=204);Empty set (0.00 sec)

小练习:

查询每个部门最新入职的那位员工

#创建表create table employee(id int not null unique auto_increment,name varchar(20) not null,sex enum('male','female') not null default 'male', #大部分是男的age int(3) unsigned not null default 28,hire_date date not null,post varchar(50),post_comment varchar(100),salary double(15,2),office int, #一个部门一个屋子depart_id int);#查看表结构mysql> desc employee;+--------------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------------+-----------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(20) | NO | | NULL | || sex | enum('male','female') | NO | | male | || age | int(3) unsigned | NO | | 28 | || hire_date | date | NO | | NULL | || post | varchar(50) | YES | | NULL | || post_comment | varchar(100) | YES | | NULL | || salary | double(15,2) | YES | | NULL | || office | int(11) | YES | | NULL | || depart_id | int(11) | YES | | NULL | |+--------------+-----------------------+------+-----+---------+----------------+#插入记录#三个部门:教学,销售,运营insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部('alex','male',78,'20150302','teacher',1000000.31,401,1),('wupeiqi','male',81,'20130305','teacher',8300,401,1),('yuanhao','male',73,'20140701','teacher',3500,401,1),('liwenzhou','male',28,'20121101','teacher',2100,401,1),('jingliyang','female',18,'20110211','teacher',9000,401,1),('jinxin','male',18,'19000301','teacher',30000,401,1),('成龙','male',48,'20101111','teacher',10000,401,1),('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门('丫丫','female',38,'20101101','sale',2000.35,402,2),('丁丁','female',18,'20110312','sale',1000.37,402,2),('星星','female',18,'20160513','sale',3000.29,402,2),('格格','female',28,'20170127','sale',4000.33,402,2),('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门('程咬金','male',18,'19970312','operation',20000,403,3),('程咬银','female',18,'20130311','operation',19000,403,3),('程咬铜','male',18,'20150411','operation',18000,403,3),('程咬铁','female',18,'20140512','operation',17000,403,3);

小练习答案:

select * from employee as t1inner join(select post,max(hire_date) as new_date from employee group by post) as t2on t1.post=t2.postwhere t1.hire_date=t2.new_date;

相关文章