MySQL——多表查询细致介绍以及实例

在运用数据库的历程中，我们不成能单一的运用一两张表，很可能要运用许多张表，而且这些表之间或许会有联络，所以，就需要运用到数据库的多表查询。

1.表与表之间的关系

一对一：会员表和身份信息表，会员表是主表
例如：男人表 、女人表

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

2.为何要运用多张表

以免涌现批量的数据的冗余。
并不是表拆的越多就越好，依据现实状况进行拆分。

3.概念

同时查询多张表

4.分类

<1>合并查询
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;

<2>连贯查询
员工表

  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;

注意：内连贯不写连贯前提会涌现笛卡尔积的效果，应当以免这种状况，而外连贯不写连贯前提会报错。

<3>子查询（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='研发部')
分类: MySQL数据库

想理解更多相干题目请拜访百分百源码网：MySQL视频教程

以上就是MySQL——多表查询细致介绍以及实例的细致内容，更多请关注 百分百源码网 其它相干文章！