SQL-多表查询

多表查询

多表查询顾名思义就是从多张表中一次性的查询出我们想要的数据。

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;


# 创建部门表
	CREATE TABLE dept(
        did INT PRIMARY KEY AUTO_INCREMENT,
        dname VARCHAR(20)
    );

	# 创建员工表
	CREATE TABLE emp (
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(10),
        gender CHAR(1), -- 性别
        salary DOUBLE, -- 工资
        join_date DATE, -- 入职日期
        dep_id INT,
        FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
    );
	-- 添加部门数据
	INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
	-- 添加员工数据
	INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
	('孙悟空','男',7200,'2013-02-24',1),
	('猪八戒','男',3600,'2010-12-02',2),
	('唐僧','男',9000,'2008-08-08',2),
	('白骨精','女',5000,'2015-10-07',3),
	('蜘蛛精','女',4500,'2011-03-14',1),
	('小白龙','男',2500,'2011-02-14',null);	

1 连接查询

* 内连接查询 ：相当于查询AB交集数据
* 外连接查询
 	* 左外连接查询 ：相当于查询A表所有数据和交集部门数据
	* 右外连接查询 ： 相当于查询B表所有数据和交集部分数据

• 子查询

1.1 内连接查询

• 语法

-- 隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;

-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

1.2 外连接查询

语法

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

2 子查询

• 概念

  查询中嵌套查询，称嵌套查询为子查询。

  什么是查询中嵌套查询呢？我们通过一个例子来看：

  需求：查询工资高于猪八戒的员工信息。

  来实现这个需求，我们就可以通过二步实现，第一步：先查询出来 猪八戒的工资

  select salary from emp where name = '猪八戒'
  

  第二步：查询工资高于猪八戒的员工信息

  select * from emp where salary > 3600;
  

  第二步中的3600可以通过第一步的sql查询出来，所以将3600用第一步的sql语句进行替换

  select * from emp where salary > (select salary from emp where name = '猪八戒');
  

  这就是查询语句中嵌套查询语句。

• 子查询根据查询结果不同，作用不同

  • 子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断
  • 子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断
  • 子查询语句结果是多行多列，子查询语句作为虚拟表

• 案例

  • 查询 ‘财务部’ 和 ‘市场部’ 所有的员工信息

    -- 查询 '财务部' 或者 '市场部' 所有的员工的部门did
    select did from dept where dname = '财务部' or dname = '市场部';
    
    select * from emp where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');
    

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

    -- 查询入职日期是 '2011-11-11' 之后的员工信息
    select * from emp where join_date > '2011-11-11' ;
    -- 将上面语句的结果作为虚拟表和dept表进行内连接查询
    select * from (select * from emp where join_date > '2011-11-11' ) t1, dept where t1.dep_id = dept.did;