多表查询

多表查询

 什么是多表查询

同时查询多张表获取到需要的数据 比如：我们想查询到开发部有多少人，需要将部门表和员工表同时进行查询：

                                        

多表查询的分类：

                                        

准备数据：

-- 创建部门表
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
);

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);

 

---

笛卡尔积现象

(1)什么是笛卡尔积现象

                 多表查询时左表的每条数据和右表的每条数据组合，这种效果成为笛卡尔积

比如：

-- 需求：查询每个部门有哪些人  
select * from dept,emp;

              

                                左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积

(2)如何清除笛卡尔积现象的影响

     我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。

-- 隐式内连接（from后面跟多个表，这是不规范的sql语句，但是开发中多用）


select * from emp,dept where emp.dept_id = dept.id;

-- 为了方便显式，所以在列后加上别名
select emp.*,dept.id dept_id,dept.name dept_name from emp,dept where emp.dept_id = dept.id;

-- 表名太长，可以在from后面给表加上别名
select e.*,d.id dept_id,d.name dept_name from emp e,dept d where e.dept_id = d.id;

                      

-- 标准sql语句，与上面的效果一样，不常用
select e.*,d.id dept_id,d.name dept_name from emp e inner join dept d on e.dept_id = d.id; 

---

左外连接和右外连接

当我们在上述员工表中如果新加入一条数据，但是里面与之关联的部门没有写，但是我们查询的时候还想要将之显现出来，或者右边有一个数据想要显现出来，这个时候我们就要使用左外连接和右外连接。

左外连接：from靠左的表称之为左表，左外连接中将这个表中数据全部显示出来，即使和右表中我们想要查询的数据没有关系

右外连接：from靠右的表称之为右表，其他和上述一样

   

-- 左外连接
select e.name emp_name,d.name dept_name from emp e left outer join dept d on e.dept_id = d.id; 
-- 右外连接
select e.name emp_name,d.name dept_name from emp e right outer join dept d on e.dept_id = d.id;
-- 把之前的右表与左表交换位置 效果和 右外连接一样
select e.name emp_name,d.name dept_name from dept d left outer join emp e on e.dept_id = d.id;

                                       

在mysql中，里面的outer可以去掉不写。

在其他的数据库操作系统中还会有一个全查询，里面的关键字改成full，但是mysql不支持

---

子查询

一条SELECT语句的执行结果作为另一条SELECT语法一部分

有一种自连接查询：

-- 比如查询比孙悟空的工资高的人
select e1.name emp_name,e1.salary emp_salary from emp e1,emp e2 where e1.salary > e2.salary and e2.name = '孙悟空';

select name,salary from emp e where salary > (select salary from emp where name = '孙悟空');

  上面的就是子查询，功能和上面的一样。但是实际上效率没有自连接查询效率高。

类似上面那个子查询，子查询里返回的结果是一个值，类似的可以用 <  = != >= <=等符号。可以说是子查询中返回的是单行单列类型

查询结果是单列多行的时候

这个时候可以用in 来解决。

select * from emp where salary in (select salary from emp where gender = '男' and salary > 5000)

还有关键字为 all any 需要配合大于小于等于号等符号使用

select name , salary from emp where salary >any (select salary from emp where salary >5000)

select name , salary from emp where salary >all (select salary from emp where salary >5000)

any 是指范围中符合最小范围的，all是范围中的最高范围。

返回结果是多行多列的情况（内建视图）

select e.name ,e.salary from (select * from emp where salary>5000) e where e.salary <10000;

这里要放到from后面，而且必须有别名。