数据库之多表查询

多表查询

多表查询语法：
select 列名列表  from 表名的列表 wheere...

接下来先创建两张表:

# 创建部门表
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(20),
                    gender enum('男','女') default '男',
                    galary double,
                    join_data  date, -- 入职日期
                    dept_id int,
                    foreign key (dept_id) references dept(id) -- 外键，关联部门表（部门表的主键）
);


insert into emp (name, gender,galary, join_data, dept_id) values ('孙悟空','男',7200,'2013-02-22',1);
insert into emp (name,gender, galary, join_data, dept_id) values ('猪八戒','男',3600,'2014-02-22',2);
insert into emp (name,gender, galary, join_data, dept_id) values ('唐僧','男',2000,'2012-04-22',2);
insert into emp (name, gender,galary, join_data, dept_id) values ('白骨精','女',9000,'2015-06-21',3);
insert into emp (name,gender, galary, join_data, dept_id) values ('蜘蛛精','女',8000,'2016-02-22',1);

查询   select * from dept，emp;

查询结果如下为笛卡尔积。（笛卡尔积:有两个集合A,B，去这两个集合的所有组成情况）要完全多表查询，需要消除无用的数据。

多表查询的分类：

1.内连接查询：

		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;

		2.显式内连接：

语法: select 字段列表 from 表名1 (inner) join 表名2  on 条件
		 -- 查询所有员工信息和对应的部门信息
		 select * from emp inner join dept on emp.dept_id = dept.id;

注意事项：1.从哪些表中查询数据
					2.条件是什么
					3.查询哪些字段

2.外连接查询：
1.左外连接：查询的是左表所有数据以及其交集部分。

语法: select 字段列表 from 表1 left (outer) join 表2 on 条件;
 -- 查询所有员工信息, 如果员工有部门，显示部门名称。 没有部门，不显示部门
select  t1.*, t2.name from emp t1,dept t2 where  t1.dept_id=t2.id;

-- 使用左连接查询所有员工信息, 如果员工有部门，显示部门名称。 没有部门，不显示部门
 select  t1.*, t2.name from emp t1  left join dept t2 on t1.dept_id = t2.id;

2.右外连接：查询的是右表所有数据以及其交集部分。

语法: select 字段列表 from 表1 right (outer) join 表2 on 条件;
-- 使用右连接查询所有员工信息, 如果员工有部门，显示部门名称。 没有部门，不显示部门
 select  t1.*, t2.name from emp t1  right join dept t2 on t1.dept_id = t2.id;

3.子查询：查询中嵌套查询，称嵌套查询为子查询。

-- 查询工资最高的员工信息
    -- 1.查询最高的工资是 9000
select max(galary) from emp ;
    -- 2.查询最高工资的员工
select * from emp where galary=9000;

-- 子查询
select * from emp where galary=(select max(galary) from emp );

子查询的不同情况：
1.子查询的结果是单行单列的：
*子查询可以作为条件，使用运算符去判断。
运算符：>, >= ,< ,<=,=

-- 查询工资小于平均工资的员工信息
select * from emp where galary<(select avg(galary) 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.子查询的结果是多行多列的：

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

-- 查询员工的入职日期是2014-02-22之后的员工信息和部门信息`
select * from  emp where  dept_id in(select id from dept where name='财务部' or name='市场部');