九.多表查询

1.多表关系

(1).介绍

实际开发中，一个项目通常需要很多张表才能完成。例如: 一个商城项目就需要分类表(category), 商品表(products), 订单表(orders)等多张表。且这些表的数据之间存在一定的关系。

(2).分类

[1].一对一关系

示例:

一个学生只有一张身份证；一张身份证只能对应一学生

在任一表中添加唯一外键，指向另一主键，确保一对一关系

一般一对一关系很少见，遇到一对一关系的表最好是合并表

[2].一对多/多对一关系

示例

学生和课程

分析: 一个学生可以选择多门课程，一个课程也可以被多个学生选择

​ 实现原则: 多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，将多对多关系，拆成一对多关系，中间表至少要有两个外键，这两个外键分别指向原来的那张表的主键。

(3).外键约束

[1].简介

MySQL外键约束(FOREIGN KEY)是表的一个特殊字段，经常与主键一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表(父表), 外键所在的表就是从表

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。比如，一个水果摊，只有苹果，桃子，李子，西瓜4种水果，那么，你来到水果摊要买水果就只能选择苹果，桃子，李子和西瓜，其他的水果都是不能购买的。

[2].特点

定义一个外键时，需要遵守下列规则:

[a].主表必须已经存在于数据库中，或者是当前正在创建的表。

[b].必须为主表定义主键。

[c].主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

[d].在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。

[e].外键中列的数目必须和主表的主键列的数目相同

[f].外键中列的数据类型必须和主表主键中对应列的数据类型相同。

[3].语法与示例

[a].创建外键约束

[a1].方式1- 在创建表时设置外键约束
在create table语句中，通过foregin key关键字来指定外键，具体语法格式如下:

[constraint <外键名>] foreign key 字段名 [, 字段名2, ...] references <主表名> 主键列1 [,]

示例

create database mydb3;
use mydb3;
-- 创建部门表
create table if not exists dept(
  deptno varchar(20) primary key, -- 部门号
  name varchar(20) -- 部门名称  
);

-- 创建员工表
create table if not exists emp(
       eid varchar(20) primary key, -- 员工编号
       ename varchar(20), -- 员工姓名
       age int, -- 员工年龄
       dept_id, varchar(20), -- 员工所属部门
       constraint emp_fk foreign key (dept_id) references dept(detpno) -- 外键约束  
);

[a2].在创建表时设置外键约束
外键约束也可以在修改表时添加，但是添加外键约束前提是: 从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

alter table <数据表名> add constraint <外键名> foregin key(<列名>) references <主表名>(<列名>);

示例

-- 创建部门表
create table if not exists dept(
    detpno varchar(20) primary key, -- 部门号
    name varchar(20) -- 部门名称  
);
-- 创建员工表
create table if not exists emp(
    eid varchar(20) primary key, --员工编号
    ename varchar(20), -- 员工名字
    age int, -- 员工年龄
    dept_id varchar(20) -- 员工属性部门
);
-- 创建外键约束
alter table emp add constraint dept_id_fk foreign key(dept_id) references dept(deptno);

[b].删除外键约束

当一个表中不需要外键约束时，就需要从表中将其删除。外键一旦删除，就会解除主表和从表间的关联关系
格式:

alter table <表名> drop foreign key <外键约束名>;

实现:

alter table emp2 drop foreign key dept_id_fk;

2.多表联合查询

(1).简介

多表查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候，需要显示的数据来自多张表。

(2).分类

[1].交叉连接查询(产生笛卡尔积，了解)

语法:

select * from A, B

[2].内连接查询(使用的关键字inner join – inner可以省略)

[a].隐式内连接(SQL92标准)

select * from A, B where 条件;

[b].显示内连接(SQL99标准)

select * from A inner join B on 条件;

[3].外连接查询(使用的关键字outer join – outer可以省略)

[a].左外连接:
格式:

left outer join

示例:

select * from A left outer join B on 条件;

[b].右连接
格式:

right outer join

示例:

select * from A right outer join B on 条件;

[c].满外连接
格式:

full outer join

示例

select * from A full outer join B on 条件;

[4].子查询

select的嵌套

[5].表自关联

将一张表当成多张表来用

(3).示例

[1].数据准备

-- 创建部门表
create table if not exists dept(
     deptno varchar(20) primary key, -- 部门号
     name varchar(20) -- 部门名称
);

-- 创建员工表
create table if not exists emp(
  eid varchar(20) primary key, -- 员工编号
  ename varchar(20), -- 员工姓名
  age int, --员工年龄
  dept_id varchar(20) --员工所属部分
);

-- 给部门表添加数据
insert into dept values('1001', '研发部');
insert into dept values('1002', '销售部');
insert into dept values('1003', '财务部');
insert into dept values('1004', '人事部');

-- 给员工表添加数据
insert into emp values('1', '乔峰', 20, '1001')
insert into emp values('2', '段誉', 21, '1001')
insert into emp values('3', '虚竹', 23, '1001')
insert into emp values('4', '阿紫', 18, '1001')
insert into emp values('5', '扫地僧', 85, '1002')
insert into emp values('6', '李秋水', 33, '1002')
insert into emp values('7', '鸠摩智', 50, '1002')
insert into emp values('8', '天山童姥', 60, '1003')
insert into emp values('9', '莫容复', 58, '1003')
insert into emp values('10', '丁春秋', 71, '1005')

[2].交叉连接查询

[a].简介
[a1].交叉连接查询返回被连接的两个表所有数据行的笛卡尔积。

​ [a2].笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配。

​ [a3].假如A表有m行数据，B表有n行数据，则返回m*n行数据

​ [a4].笛卡尔积会产生很多冗余的数据，后期的其他数据可以在该集合的基础上进行条件筛选
[b].格式

select * from 表1, 表2, 表3...

[c].示例

-- 交叉连接查询
select * from dept, emp;

[3].内连接查询

[a].ven图

[b].格式
隐式内连接(SQL92标准):

select * from A, B where 条件;

显示内连接(SQL99标准):

select * from A inner join B on 条件;

[c].示例

-- 查询每个部门的所有员工
-- 隐式内连接
select * from dept, emp where dept.deptno = emp.dept_id;
-- 显示内连接
select * from dept inner join emp on dept.deptno = emp.dept_id;

-- 查询研发部门的所属员工
-- 隐式内连接
 select * from dept d, emp e where a.deptno = b.dept_id and a.name = '研发部';
-- 显示内连接
select * from dept d join emp e on d.deptno = e.dept_id and name = '研发部';
 
-- 查询研发部和销售部的所属员工
select * from dept d, emp e where a.deptno = b.dept_id and (a.name = '研发部' or a.name = '销售部');
或
select * from dept d, emp e where a.deptno = b.dept_id and a.name in ('研发部', '销售部');
  
-- 查询每个部门的员工数，并升序排序
select d.name, d.deptno, count(1) from dept d join emp e on d.deptno = e.dept_id group by d.deptno ORDER BY count(1);
  
-- 查询人数大于等于3的部门, 并按照人数降序排序
select d.name, d.deptno, count(1) from dept d join emp e on d.deptno = e.dept_id group by d.deptno HAVING count(1) >= 3 ORDER BY count(1) desc;

[4].外连接查询

[a].简介

外连接分为左连接(left outer join), 右外连接(right outer join), 满外连接(full outer join).
注意: oracle里面有full join, 可是在mysql对full join支持的不好。我们可以使用union来达到目的.

[b].格式

[b1].左外连接: left outer join

select * from A left outer join B on 条件;

[b2].右外连接: right outer join

select * from A right outer join B on 条件;

[b3].满外连接: full outer join

select * from A full outer join B on 条件;

[c].示例

-- 外连接查询

-- 查询哪些部门有员工，哪些部门没有员工
select * from dept d left outer join emp e on d.deptno = e.dept_id;

 -- 查询员工有对应的部门，哪些没有
select * from dept d right outer join emp e on d.depton = e.dept_id;
 
 -- 使用union关键字实现左外连接和右外连接的并集
select * from dept d left outer join emp e on d.deptno = e.dept_id
union
select * from dept d right outer join emp e on d.depton = e.dept_id;

[5].子查询

[a].子查询关键字

在子查询中，有一些常用的逻辑关键字，这些关键字可以给我们提供更丰富的查询功能，主要关键字如下:

[a1].ALL关键字

[a2].ANY关键字

[a3].SOME关键字

[a4].IN关键字

[a5].EXISTS关键字

[6].子查询关键字-ALL

[a].格式

select ... from ... where c > all(查询语句)
--- 等价于:
select ... from ... where c > result1 and c > result2 and c > result3;

[b].特点

[b1].ALL:与子查询返回所有值比较为true, 则返回true

[b2].ALL可以与=, >, >=, <, <=, <>结合是来使用，分别表示等于，大于，大于等于，小于，小于等于，不等于其中的其中的所有数据。

[b3].ALL表示指定列中的值必须要大于子查询集的每一个值，即必须要大于查询集的最大值；如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。

[c].示例

-- 查询黏连大于'1003'部门所有年龄的员工信息
select * from emp where age > all(select age from emp where dept_id = '1003');

-- 查询不属于任何一个部门的员工信息
select * from emp where dept_id != all(select deptno from dept);

[7].子查询关键字-ANY和SOME

[a].格式

select ... from ... where c > any(查询语句);
--- 等价于:
select ... from ... where c > result1 or c > result2 or c > result3; 

[b].特点:

[b1].ANY: 与子查询返回的任何值比较为true, 则返回true.

[b2].ANY可以与=, >, >=, <, <=, <>结合是来使用，分别表示等于，大于，大于等于，小于，小于等于，不等于其中的其中的任何一个数据。

[b3].表示指定列中的值要大于子查询中的任意一个值，即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。

[b4].SOME和ANY的作用一样，SOME可以理解为ANY的别名。

[c].示例

-- 查询年龄大于'1003'部门任意一个员工年龄的员工信息
select * from emp where age > any(select age from emp where dept_id = '1003');

[8].子查询关键字-IN

[a].格式

select ... from ... where c in (查询语句);
--- 等价于:
select ... from ... where c = result1 or c = result2 or c = result3;    

[b].特点

[b1].IN关键字，用于判断某个记录的值，是否在指定的集合中

[b2].在IN关键字前边加上not可以将条件反过来

[c].示例

-- 查询研发部的销售部的员工信息，包含员工号，员工信息
select eid, ename from emp where dept_id in (select deptno from dept where name = '研发部' or name = '销售部');

[9].子查询关键字-EXISTS

[a].格式

select ... from ... where exists(查询语句);

[b].特点

[b1].该子查询如果"有数据结果"(至少返回一行数据)。则该EXISTS()的结果为"true", 外层查询执行。

[b2].该子查询如果"没有数据结果"(没有任何数据返回)，则该EXISTS()的结果为"false",外层查询不执行.

[b3].EXISTS后面的子查询不返回任何实际数据，只返回真或假，但返回真时where条件成立

注意:EXISTS关键字，比IN关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用EXISTS关键字。

[c].操作

-- 查询公司是否大于60岁员工，有则输出
select * from emp e where EXISTS(select * from emp where e.age > 60);
-- 查询有属部门的员工信息
select * from dept d where exists (select * from emp e where a.deptno = b.dept_id);

[10].自关联查询

[a].概念

MySQL有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。注意自关联时表必须给表起别名。

[b].格式

select 字段列表 from 表1 a, 表1 b where 条件;
或者
select 字段列表 from 表1 a [left] join 表1 on 条件;

[c].示例

-- 创建表，并建立自关联约束
create table t_sanguo(
    eid int primary key,
    ename varchar(20),
    manager_id int,
    foreign key (manager_id) references t_sanguo(eid) -- 添加自关联约束  
);

-- 添加数据
insert into t_sanguo values(1, '刘协', NULL);
insert into t_sanguo values(2, '刘备', 1);
insert into t_sanguo values(3, '关羽', 2);
insert into t_sanguo values(4, '张飞', 2);
insert into t_sanguo values(5, '曹操', 1);
insert into t_sanguo values(6, '许褚', 5);
insert into t_sanguo values(7, '典韦', 5);
insert into t_sanguo values(8, '孙权', 1);
insert into t_sanguo values(9, '周瑜', 8);
insert into t_sanguo values(10, '鲁肃', 8);

-- 进行关联查询
-- 1.查询每个三国人物及他的上级信息，如果: 关羽，刘备
select a.name, b.name from t_sanguo a, t_sanguo b where a.manager_id = b.eid;
或者
select a.name, b.name from t_sanguo a join t_sanguo b on a.manager_id = b.eid;

 -- 2.查询所有任务及上级
 select a.name, b.name from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;  
 
 -- 3.查询所有人物，上级， 上上级
 select 
 a.ename, b.ename, c.ename 
 from t_sanguo a
 left join t_sanguo b on a.manager_id = b.eid
 left join t_sanguo c on b.manger_id = c.eid; 

3.示例

(1).数据准备

-- 创建test1数据库
create database test1;
-- 选择使用test1数据库
use test1;
-- 创建部门表
create table dept(
  deptno int primary key, -- 部门编号
  dname varchar(14), -- 部门名称
  loc varchar(13) -- 部门地址  
);
insert into dept values (10, 'accounting', 'new york');
insert into dept values (20, 'research', 'dallas');
insert into dept values (30, 'sales', 'chicago');
insert into dept values (40, 'operations', 'boston');
-- 创建员工表
create table emp(
  empno int primary key, -- 员工编号
  ename varchar(10), -- 员工姓名
  job varchar(9), -- 员工工作
  mgr int, -- 员工直属领导编号
  hiredate DATE, -- 入职日期
  sal double, -- 工资
  comm double, -- 奖金
  deptno int -- 对应dept表的外键  
);
-- 添加部门和员工之间的主外键关系
alter table emp add constraint foreign key emp(deptno) references dept(deptno);

insert into emp values(7369, 'smith', 'clerk', 7902, '1980-12-17', 800, null, 20);
insert into emp values(7499, 'allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30);
insert into emp values(7521, 'ward', 'salesman', 7698, '1981-02-22', 1250, 500, 30);
insert into emp values(7566, 'jones', 'manager', 7839, '1981-04-02', 2975, null, 20);
insert into emp values(7654, 'martin', 'salesman', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp values(7698, 'blake', 'manager', 7839, '1981-05-01', 2850, null, 30);
insert into emp values(7782, 'clark', 'manager', 7839, '1981-06-09', 2450, null, 10);
insert into emp values(7788, 'scott', 'analyst', 7566, '1981-07-03', 3000, null, 20);
insert into emp values(7839, 'king', 'president', null, '1981-11-17', 5000, null, 10);
insert into emp values(7844, 'turner', 'salesman', 7698, '1981-09-08', 1500, 0, 30);
insert into emp values(7876, 'adams', 'clerk', 7788, '1987-07-13', 1100, null, 20);
insert into emp values(7900, 'james', 'clerk', 7698, '1981-12-03', 950, null, 30);
insert into emp values(7902, 'ford', 'analyst', 7566, '1981-12-03', 3000, null, 20);
insert into emp values(7934, 'millier', 'clerk', 7782, '1981-01-23', 1300, null, 10);

-- 创建工资等级表
create table salgrade(
    grade int, -- 等级
    losal double, -- 最低工资
    hisal double -- 最高工资
);
insert into salgrade values(1, 700, 1200);
insert into salgrade values(2, 1201, 1400);
insert into salgrade values(3, 1401, 2000);
insert into salgrade values(4, 2001, 3000);
insert into salgrade values(5, 3001, 9999);

(2).返回拥有员工的部门名，部门号

select distinct d.dname, d.deptno from dept d join emp e on d.deptno = e.deptno;

(3).工资水平多于smith的员工信息

select * from emp where sal >(select sal from emp where ename = 'smith');

(4).返回员工和所属经理的姓名

select a.ename, b.ename from emp a join emp b on a.mgr = b.empno;

(5).返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名

select a.ename, a.hiredate, b.ename, b.hiredate from emp a join emp b on a.mgr = b.empno and a.hiredate < b.hiredate; 

(6).返回员工姓名及其所在的部门名称

select a.ename, b.dname from emp a join dept b on a.deptno = b.deptno;

(7).返回从事clerk工作的员工姓名和所在部门名称

select a.ename, b.dname from emp a join dept b on a.deptno = b.deptno and job = 'clerk';

(8).返回部门号及其本部门的最低工资

select deptno, min(sal) min_sal from emp group by deptno;

(9).返回销售部(sales)所有员工的姓名

select b.ename from dept a join emp b on a.deptno = b.deptno and a.dname='sales';

(10).返回工资水平多于平均工资的员工

select * from emp where sal > (select avg(sal) from emp);

(11).返回与scott从事相同工作的员工

select * from emp where job = (select job from emp where ename = 'scott') and ename != 'scott';

(12).返回工资高于30部门所有员工工资水平的员工信息

select * from emp where sal > all(select sal from emp where deptno = 30);

(13).返回员工工作及其从事此工作的最低工资

select job, min(sal) from emp group by job;

(14).计算出员工的年薪，并且以年薪排序

select ename, (sal * 12 + ifnull(comm, 0)) as year_sal from emp order by(sal * 12 + ifnull(comm, 0)) desc;

(15).返回工资处于第四级别的员工的姓名

select * from emp where sal between (select losal from salgrade where grade = 4) and (select hisal from salgrade where grade = 4);

(16).返回工资为二等级的职员名字，部门所在地

select * from dept a , emp b, salgrade c where a.deptno = b.deptno and grade = 2 and b.sal >= c.losal and b.sal <= c.hisal;