文章目录
9、多表查询:
多表查询和单表查询在语法上其实没有太大的差别。
创建表:
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept (name) values('开发部'),('市场部'),('财务部');
create table emp(
id int primary key auto_increment,
name varchar(10),
gender char(1),
salary double,
join_date date,
dept_id int,
foreign key (dept_id) references dept(id)
);
insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-10-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',6666,'2016-10-24',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9999,'2017-7-24',2);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',7200,'2012-10-24',3);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',7200,'2011-10-24',1);
笛卡尔积:
有两个集合A,B. 取这两个集合的所有组成情况。
要完成多表查询,需要在笛卡尔积中消除无用的数据。
分类:
网上找了三个图来说明
1.内连接查询:
内连接是把匹配的关联数据显示出来
①隐式内连接:
使用where条件消除无用数据
#查询所有员工信息和对应的部门信息
select * from emp,dept where emp.dept_id =dept.id;
#查询员工表的名称,性别以及部门表的名称
select emp.name,gender,dept.name from emp,dept where emp.dept_id =dept.id;
select
t1.name,
t1.gender,
t2.name
from
emp t1,dept t2
where
t1.dept_id=t2.id
②显示内连接:
select 字段列表 from 表名1 [inner] join 表名2 on 条件
#查询所有员工信息和对应的部门信息(inner可以省略)
select * from emp inner join dept on emp.dept_id=dept.id;
#查询员工表的名称,性别以及部门表的名称
select emp.name,gender,dept.name from emp inner join dept where emp.dept_id =dept.id;
select
t1.name,
t1.gender,
t2.name
from
emp t1 inner join dept t2
where
t1.dept_id=t2.id
③内连接查询注意事项:
1)从那些表中查询数据
2)条件是什么,判断什么算是有效,什么算是无效的数据
3)查询字段
2.外连接查询:
①左外连接:
select 字段列表 from 表1 left [outer] join 表2 on 条件
它查询的是左表(表1)所有数据以及交集部分(表2)。
②右外连接:
它查询的是右表所有数据以及交集部分。
select 字段列表 from 表1 right [outer] join 表2 on 条件
#这两句sql查出来的结果一样。
select t1.*,t2.name from emp t1 left outer join dept t2 on t1.dept_id=t2.id;
select t1.*,t2.name from dept t2 right outer join emp t1 on t1.dept_id=t2.id;
内连接与外连接的区别:
◉内连接只能返回连接表中符合连接条件的数据行。
◉而外连接不仅可以返回符合连接条件的数据行,而且还能返回不符合条件的一些行。
3.子查询:
查询中嵌套查询,称嵌套查询为子查询。
#查询工资最高的员工信息。
#1.先查询最高的工资是多少,再查询员工信息并且工资等于MAX的。
select max(salary) from emp;
select * from emp where emp.salary=9999;
#一条sql就完成这个操作。子查询
select * from emp where emp.salary=(select max(salary) from emp);
①子查询的结果是单行单列的:
子查询可以作为条件,使用运算符去判断。><=…
#查询员工工资小于平均工资的人
select * from emp where emp.salary < (select avg(salary) from emp);
②子查询的结果是多行多单的:
子查询可以作为条件,使用运算符in来判断。
#查询财务部和市场部所有的员工信息
select id from dept where name='财务部' or name='市场部';
select * from emp where dept_id=3 or dept_id=2;
#子查询。
select * from emp where dept_id in(select id from dept where name='财务部' or name='市场部');
③子查询的结果是多行多列的:
子查询可以作为一张虚拟表。
#查询员工入职日期是2012-10-24之后的员工信息和部门信息
select * from emp where emp.join_date > '2012-10-24'#虚拟表
#子查询
select *
from
dept t1 ,(select * from emp where emp.join_date > '2012-10-24') t2
where
t1.id=t2.dept_id;
#普通内连接
select * from emp t1,dept t2 where t1.dept_id=t2.id and join_date > '2012-10-24';