MySQL－多表查詢詳細介紹以及實例-mysql教程-PHP中文網

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 (&#39;大拿&#39;);     
insert into student (sname) values (&#39;唐嫣&#39;);
insert into student (sname) values (&#39;王健林&#39;);

登入後複製

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

登入後複製

insert into course (cname) values (&#39;语文&#39;);    
insert into course (cname) values (&#39;数学&#39;);    
insert into course (cname) values (&#39;英语&#39;);    
insert into course (cname) values (&#39;化学&#39;);

登入後複製

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
OUTER JOIN | left join ... on
OUTER 所代表，則行的全部，右邊則沒有右邊沒有

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=&#39;从浩&#39;);

登入後複製

定義：子查詢允許把一個查詢嵌套在另一個查詢當中
子查詢又稱為內部查詢，相當於內部查詢。包含內部查詢的就稱為外部查詢。子查詢的結果被主查詢所使用。
注意的問題：

#1.括號

2. 主查詢的where select having from 後面，可使用子查詢 #

#3.不能再group by 後面使用子查詢

#4.主查詢和子查詢可以不是同一張表；只有子查詢回傳的值，主查詢可以使用。

需求:查詢部門名稱是人力的員工資訊

第一種方式：利用子查詢

select * from emp where deptno=(select deptno from dept where dname=&#39;人力部&#39;);

登入後複製

第二種方式：利用關聯查詢

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

登入後複製

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=&#39;人力部&#39; or dname=&#39;研发部&#39;)
分类: MySQL数据库

登入後複製

想了解更多相关问题请访问PHP中文网：MySQL视频教程

以上是MySQL－多表查詢詳細介紹以及實例的詳細內容。更多資訊請關注PHP中文網其他相關文章！