MySQL-基础-多表查询

多表关系

概述

项目开发中, 再进行数据库表结构设计时, 会根据业务需求及业务模块之间的关系, 分析并设计表结构, 由于业务之间相互关联, 所以各个表结构之间也存在着各种联系, 基本上分为三种:

一对多(多对一)

        案例: 部门与员工的关系        

        关系: 一个部门对应多个员工, 一个员工对应一个部门

        实现: 在多的一方建立外键, 指向一的一方的主键

多对多

        案例: 学生与课程的关系

        关系: 一个学生可以选修多门课程, 一门课程也可以提供多个学生选择

        实现: 建立第三张中间表, 中间表至少包含两个外键, 分别关联两方主键

一对一

        案例: 用户与用户详情的关系

        关系: 一对一的关系, 多用于单表拆分, 将一张表的基础字段放在一张表中, 其他详情字段放在另一张表中, 以提升操作效率.

        实现: 在任意一方加入外键, 关联另外一方的主键, 并且设置外键为唯一的(UNIQUE).

多表查询概述

概述:指从多张表中查询数据

笛卡尔积: 笛卡尔积是指在数学中, 两个集合 A集合和 B集合的所有组合情况. (在多表查询时, 需要消除无效的笛卡尔积).

笛卡尔积

消除笛卡尔积后

连接查询-内连接 

内连接查询语法: 

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

SELECT emp.name, dept.NAME FROM dept, emp WHERE EMP.DEPT_ID = dept.ID;
SELECT E.NAME, D.NAME FROM dept D, emp E WHERE D.ID = E.dept_id;
SELECT E.NAME, D.NAME FROM dept D, emp E WHERE DEPT.ID = EMP.dept_id;-- 报错
-- 给表名起过别名后就只能用别名, 否则会报错

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

SELECT E.NAME, D.NAME FROM emp E INNER JOIN dept D ON E.dept_id = D.ID;
SELECT E.NAME, D.NAME FROM emp E  JOIN dept D ON E.dept_id = D.ID;

内连接查询的是两张表交集的部分(绿色部分) 

 连接查询-外连接

外连接查询语法: 

左外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

相当于查询表1(左表)的所有数据包 含表1和表2交集部分的数据.

SELECT E.*,D.NAME FROM emp E LEFT OUTER JOIN  dept D ON D.ID = E.dept_id;
SELECT E.*,D.NAME FROM emp E LEFT JOIN  dept D ON D.ID = E.dept_id;

右外连接

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

相当于查询表2(右表)的所有数据包 含表1和表2交集部分的数据.

SELECT E.*,D.* FROM dept D RIGHT OUTER JOIN EMP E on D.ID = E.DEPT_ID;
SELECT E.*,D.* FROM dept D RIGHT JOIN EMP E on D.ID = E.DEPT_ID;

连接查询-自连接:

自连接查询语法:

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

自连接查询, 可以是内连接查询, 也可以是外连接查询.

SELECT A.NAME '员工', B.NAME '领导' FROM emp A LEFT OUTER JOIN emp B ON A.managerid = B.ID;

联合查询-union, union all

对于union查询, 就是把多次查询的结果合并在一起, 形成一个新的查询结果集.

SELECT 字段列表 FROM 表A...

UNION[ALL]

SELECT 字段列表 FROM 表B...;

对于联合查询的多张表的列数必须保持一致, 字段类型也需要保持一致.

UNION ALL 会将全部的数据直接合并在一起, UNION 会对合并之后的数据去重.

子查询

概念: SQL语句中的嵌套SELECT语句, 称为嵌套查询, 又称子查询.

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个.

根据子查询结果不同, 分为:

        标量子查询(子查询结果为单个值)

子查询返回的结果是单个值(数字, 字符串, 日期等), 最简单的形式, 这种子查询称为标量子查询.

常用的操作符: =  <>  >  >=  <  <= 

-- 查询销售把所有的员工信息
-- 1.查询销售部的id
select id from dept where NAME = '销售部';

-- 根据销售部门ID, 查询员工信息
select * from emp where DEPT_ID = (select id from dept where NAME = '销售部');

        列子查询(子查询结果为一列)

 子查询返回的结果是一列(可以是多行), 这种子查询称为列子查询.

常用的操作符: IN,  NOT IN,  ANY,  SOME,  ALL

        行子查询(子查询结果为一行)

子查询返回的结果是一行(可以是多列), 这种子查询称为行子查询.

常用的操作符: = , <>, IN,  NOT IN

-- 查询与张无忌薪资与直属领导相同的员工信息
select emp.salary, emp.managerid from emp where NAME = '张无忌';

select * from emp where (SALARY, MANAGERID) = (select emp.salary, emp.managerid from emp where NAME = '张无忌');

        表子查询(子查询结果为多行多列)

子查询返回的结果是多行多列的, 这种查询称为表子查询

常用的操作符: IN.

-- 查询与 金庸, 张无忌 的职位和薪资相同的员工信息
select emp.job, emp.salary from emp where NAME = '金庸' or NAME = '张无忌';

select * from emp where (job, SALARY) in (select emp.job, emp.salary from emp where NAME = '金庸' or NAME = '张无忌');

-- 查询入职时间是 2002-01-01 之后的员工信息, 及其部门信息
select * from emp where ENTERDATE > '2002-01-01';

select e.*, d.* from (select * from emp where ENTERDATE > '2002-01-01') e left join dept d on e.DEPT_ID = d.ID;

根据子查询位置, 分为: WHERE之后, FROM之后, SELECT之后.