多表查询

多表查询

• 查询语法：

select
列名列表
from
表名列表
where…

• 示例：

-- 创建部门表

create table dept(
   id int primary key auto_increment,
   name varchar(20)
);

-- 创建员工表

create table emp(
   id int primary key auto_increment,  
   name varchar(20),
   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-02-24‘，1)；
​
insert into emp(name,gender,salary,join_date,dept_id) values("猪八戒",'男'，3600，’2010-12-02‘，2)；
​
insert into emp(name,gender,salary,join_date,dept_id) values("唐僧",'男'，9000，’2008-08-08‘，2)；
​
insert into emp(name,gender,salary,join_date,dept_id) values("白骨精",'女'-5000，’2015-10-07‘，3)；
​
insert into emp(name,gender,salary,join_date,dept_id) values("蜘蛛精",'女'，4500，’2011-03-14‘，1)；
   

• ​查询

  select * from emp,dept;

• 笛卡尔积

有两个集合A,B，取这两个集合的所有组成情况。
要完成多表查询，需要消除无用的数据。

多表查询的分类

• 1.内连接查询

  • 隐式内连接使用where条件消除无用数据

例如：

-- 查询所有员工信息和对应的部门信息

SELECT * FROM emp,dept WHERE emp.dept_id=dept.id;

-- 查询员工表的名称，性别，部门表的名称，方法一：

SELECT emp.name,emp.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 条件

例如：

select * from emp [inner] join dept on emp.dept_id=dept.id;

select * from emp [inner] join dept on emp.dept_id=dept.id;

• 内连接查询：

  • 1.从哪些表中查询数据

  • 2.条件是什么

  • 3.查询哪些字段

2.外连接查询

左外连接

• 语法：

   	select 字段列表 from 表1 left [outer] join 表2 on 条件;
   	查询的是左表所有数据及其交集部分	
  

• 例如：

  	select t1.*,t2.name from emp t1 left join dept on t1.dept-id=t2.id;		(其中，emp为左表，dept为右表)
  

右外连接

• 语法：

       select 字段列表 from 表1 right [outer] join 表2 on 条件;
       查询的是右表所有数据及其交集部分
  

3.子查询

• 概念：查询中嵌套查询，嵌套的查询为子查询
• 查询工资最高的员工信息
• 1.查询最高的工资是多少

select MAX(salary) from emp;

• 2.查询员工信息，并且工资等于9000的

select * from emp where emp.salary=9000;

• 一条sql就完成这个操作（子查询）

select * from emp where emp.salary=(select MAX(salary) from emp);

子查询的不同情况

• 1.子查询的结果是单行单列
  • 子查询可以作为的条件，使用运算符去判断
  • 运算符：< = <= > >=

--  查询员工工资小于平均工资的人
select * from emp where emp.salary <(select AVG(salary) from emp);

• 2.子查询的结果是多行单列的
  • 子查询可以作为条件，使用运算符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= '市场部);

• 3.子查询的结果是多行多列的

• 子查询可以作为一张虚拟表

  • 查询员工入职日期是2011-11-11日之后的员工信息和部门信息

select * from dept t1,(select * from emp where emp.join_date>"2011-11-11") t2 where t1.id =t2.dept_id;

普通内连接

select * from emp t1,dept t2 where t1.dept_id =t2.id AND t1.join_date>'2011-11-11';