数据库MySQL-5-表与表之间的关系、多表查询(合并查询 连接查询(内连接、外连接、自连接、自然连接)、子查询(ANY、ALL、SOME、IN))

多表查询

表与表之间的关系

• 一对一：用户表和身份信息表，用户表是主表
  • 男人表 、女人表

create table man(
        mid int primary key  auto_increment,
        mname varchar(32),
        wid int unique
    );
    
create table woman(
    wid  int primary key  auto_increment,
    wname varchar(32)
);

• 一对多：最常见的表关系，用户表和订单表
  • 员工表、部门表

create table emp(
            empno int primary key  auto_increment,
            ename varchar(32),
            deptno int
        );
        
create table dept(
   deptno int primary key  auto_increment,
   dname varchar(32)
);

• 多对多：学生表和课程表，通常情况都是将多对多的关系拆分为一对多或者多对一的关系。
  • 至少需要三张表

create table student(
 sid  int primary key  auto_increment,
 sname varchar(32)
);

insert into student (sname) values ('大大');
insert into student (sname) values ('唐嫣');
insert into student (sname) values ('王健林');

create table course(
 cid int primary key  auto_increment,
 cname varchar(32)
);

insert into course (cname) values ('语文');
insert into course (cname) values ('数学');
insert into course (cname) values ('英语');
insert into course (cname) values ('化学');

create table s_c(
 cid int,
 sid int
);

insert into s_c (sid,cid) values (1,1);
insert into s_c (sid,cid) values (1,2);
insert into s_c (sid,cid) values (1,3);
insert into s_c (sid,cid) values (1,4);
insert into s_c (sid,cid) values (2,2);
insert into s_c (sid,cid) values (2,4);
insert into s_c (sid,cid) values (3,1);
insert into s_c (sid,cid) values (3,3);

为什么要使用多张表

• 避免出现大量的数据的冗余。
• 并不是表拆的越多就越好，根据实际情况进行拆分。

概念

• 同时查询多张表

分类

• 合并查询
  • union ，union all
  • 合并结果集，就是把两个select语句的查询结果合并到一起。（相当于并集）
  • 合并的两个结果，列数和列的顺序，类需要一致

create table emp(
 empno int primary key  auto_increment,
 ename varchar(32)
);

create table dept(
 deptno int primary key  auto_increment,
 dname varchar(32)
);

select * from emp  union select * from dept;
select * from emp  union all select * from dept;

• 连接查询

# 员工表
        create table emp(
            empno int primary key auto_increment, # 员工编号
            ename varchar(32),  		#员工姓名
            job varchar(32),        	#员工职位
            mgr  int,                   #上级编号
            hiredate date,          	#入职时间
            sal double,                 #薪水
            comm double,                #奖金
            deptno int                  #所属部门
        );
        
# 部门表
    create table dept(
        deptno int primary key auto_increment,  #部门编号
        dname varchar(32),      			#部门名称
        loc varchar(32)             		#部门地址
    );

• 内连接: inner join…on 、 join 、 ,

  • inner join 是比较运算符，只返回符合条件的行

  • 例如：

    select * from emp inner join  dept  on emp.deptno=dept.deptno;
    select * from emp e ,dept d where e.deptno = d.deptno;
    select * from emp e join dept d where e.deptno = d.deptno;
    

• 外连接:

  • 左外连接：LEFT OUTER JOIN | left join … on

  • 代表查询，左边行的全部，右边没有则null

    select * from emp e LEFT OUTER JOIN  dept d ON e.deptno = d.deptno;
    

  • 右外连接： right join … on 或者 right outer join … on

  • 右连接包含right join 右表所有的行，如果左表中某行在右表没有匹配，则结果中对应的左表的部门全部为空（null）

    select * from emp e right OUTER JOIN  dept d ON e.deptno = d.deptno;
    

• 自连接：

  • 自连接就是说，在同一个数据表中，看作是两个表，表示查找每个人的领导，如果没有领导，则显示无领导

  • 把一张表看作成两张表，一张员工表，一张领导表，都是emp表

    select e.ename,el.ename from emp e left join emp el on e.mgr = el.empno;
    

• 自然连接: natural join （join）| natural left join(同 left join) | natural right join (同 right join)

  • 自然连接会自动判断，以两个表中相同的字段为连接条件，返回查询结果。

     select * from emp natural join dept;
     select * from emp NATURAL left join dept;
     select * from emp NATURAL right join dept;
    

注意：内连接不写连接条件会出现笛卡尔积的结果，应该避免这种情况，而外连接不写连接条件会报错。

子查询（ANY子查询、IN子查询、SOME子查询、ALL子查询）

• 子查询解决的问题：

  • 谁的薪资比丛浩高？？？

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

• 定义：子查询允许把一个查询嵌套在另一个查询当中

  • 子查询又叫做内部查询，相当于内部查询。包含内部查询的就称为外部查询。子查询的结果被主查询所使用

• 注意的问题：

1. 括号
2. 可以在主查询的where select having from 后面，都可以使用子查询
3. 不可以再group by 后面使用子查询
4. 主查询和子查询可以不是同一张表；只有子查询返回的值，主查询可以使用。

   • 需求:查询部门名称是人力的员工信息

   • 第一种方式：利用子查询

       select * from emp where deptno=(select deptno from dept where dname='人力部');
     

   • 第二种方式：利用关联查询

      select * from emp e,dept d where e.deptno = d.deptno and d.dname='人力部';
     

   • SQL优化：尽量使用多表查询

     • 绝大部分的子查询再最终执行的时候他都是转换成一个多表查询来执行的。 通过SQL执行计划可以看出来。
     • 通过SQL执行计划会发现两种方式执行的是一样的。
5. from后面的子查询

   • 需求：查询员工号 姓名 月薪

       select empno,ename,sal from emp;
     

6. 一般不在子查询中排序
7. 一般先执行子查询，再去执行主查询

ANY关键字

• 假设any内部的查询返回结果个数是三个，如：result1，result2，result3，那么

    select .... from .. where a > any(...);
    select ..... from ... where a > result1 or a > result2  or a > result3;
  

• 需求：查询工资比1号部门中任意一个员工高的信息

    select * from emp where sal > any(select sal from emp where deptno = 1);
  

ALL关键字

• ALL关键字与any关键字类似，只不过上面的or改成and ：

    select .... from .. where a > all(...);
    select ..... from ... where a > result1 and a > result2  and a > result3;
  

• 需求：查询工资比1号部门中所有员工号的员工信息

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

SOME关键字

• some 关键字和any关键字是一样的功能。所以：

    select .... from .. where a > any(...);
    select ..... from ... where a > result1 or a > result2  or a > result3;
  

IN关键字

• IN运算符用于where表达式中，以列表向的形式支持多个选择。语法如下：

    where column in (v1,v2,v3,.....);
    where column not in (v1,v2,v3,.....);
  

当in前面加上not运算符时候，表示与in相反的意思，既不在这写列表项中选择。

• 案例：查询部门名称是人力和研发的员工

  select * from emp where deptno in   (select deptno from dept where dname='人力部' or dname='研发部')