MySQL查詢語句之連接配接查詢（五）

文章目錄

• 一.sql92标準
• • 1.等值連接配接
  • • • 拓展：為表起别名
    • 1.1 兩個表的順序可否調換,可以的
    • 1.2 可以加篩選?可以的
    • 1.3可以加分組?可以的
    • 1.4 可以加排序?可以的
    • 1.5 可以實作三表連接配接麼?可以的
  • 2.非等值連接配接
  • 3.自連接配接
• 二.sql99标準
• • 一）内連接配接A∩B
  • • 2.1.1 等值連接配接
    • 2.1.2 非等值連接配接
    • 2.1.3 自連接配接
  • 二）外連接配接
  • • 左外連接配接 A∪(A∩B)
    • 右外連接配接 （A∩B）∪B
    • 全外 A∪B
  • 三）交叉連接配接

含義：

又稱多表查詢,當查詢字段來自于多個表時,就會用到連接配接查詢

笛卡爾乘積現象:

表1,m行,表2有n行,結果等于m×n行

發生原因:

沒有有效的連接配接條件

如何避免:

添加有效的連接配接條件

分類:

按年代分類:

sql 92 标準

:僅僅支援内連接配接

sql 99 标準

[推薦]:内連接配接+外連接配接(左外加右外)+交叉連接配接

按功能分類:

内連接配接

:

                等值連接配接,非等值連接配接,自連接配接

外連接配接

:

                左外連接配接,右外連接配接,全外連接配接

交叉連接配接

show databases;
use girls;
select * from beauty;
select * from boys;
select name,boyName from boys,beauty;  # 笛卡爾乘積現象
select name,boyName from boys,beauty
where beauty.boyfriend_id=boys.id;
           

一.sql92标準

1.等值連接配接

• 多表等值連接配接的結果為多表的交集部分
• n表連接配接,至少有

  n-1

  個連接配接條件
• 多表的順序沒有要求
• 一般需要為表去表名
• 可以搭配前面介紹的所有子句,比如排序,分組,篩選

案例1:查詢女神名和對應的男神名

select name,boyName 
from boys,beauty # 等值連接配接，beauty表boyfriend_id與boys表中id等值
where beauty.boyfriend_id=boys.id;
           

案例2:查詢員工名和對應的部門名

use myemployees;
select last_name,department_name
from employees,departments
where employees.department_id=departments.department_id;
           

拓展：為表起别名

• 提高語句簡潔度,
• 區分多個重名的字段

注意:如果為表起了别名,則查詢的字段就不能使用原來的表名去限定

起别名範例： 查詢員工名，工種号，工種名

select last_name,employees.job_id,job_title
from employees,jobs
where employees.job_id=jobs.job_id;

select last_name,e.job_id,job_title
from employees e,jobs j
where e.job_id=j.job_id;
           

1.1 兩個表的順序可否調換,可以的

select last_name,e.job_id,job_title
from jobs j,employees e
where e.job_id=j.job_id; # 順序調換
           

1.2 可以加篩選?可以的

案例1: 查詢有獎金的員工名和部門名

select last_name,department_name,commission_pct
from employees e,departments d
where e.department_id=d.department_id 
and e.commission_pct is not null;  # 加入篩選
           

案例2: 查詢城市名中第二個字元為

o

的部門名和城市名

select department_name,city
from departments d,locations l
where d.location_id=l.location_id
and city like '_o%'; # 加入篩選
           

1.3可以加分組?可以的

案例1:查詢每個城市的部門個數

select count(*) 個數,city
from departments d,locations l
where d.location_id=l.location_id
group by city; # group by加入分組
           

案例2:查詢出有獎金的每個部門的部門名和部門的上司編号和該部門的最低工資

select department_name,d.manager_id,min(salary)
from employees e,departments d
where d.department_id=e.department_id
and commission_pct is not null
group by department_name,d.manager_id; # 多字段分組
           

1.4 可以加排序?可以的

案例:查詢每個工種的工種名和員工個數,并且按員工個數降序

select job_title,count(*)
from employees e,jobs j
where e.job_id=j.job_id
group by job_title
order by count(*) desc; # 加排序
           

1.5 可以實作三表連接配接麼?可以的

案例:查詢員工名,部門名和所在的城市

select last_name,department_name,city
from employees e,departments d,locations l
where e.department_id=d.department_id
and d.location_id=l.location_id; # where三表連接配接
           

2.非等值連接配接

案例:查詢員工的工資級别和工資等級

select salary,grade_level 
from employees e,job_grades g
where salary between g.lowest_sal and g.highest_sal # 非等值連接配接
and g.grade_level='A';
           

3.自連接配接

案例:查詢員工名和上級的名稱

select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees e,employees m
where e.manager_id=m.employee_id;  # 在同一張表自己連接配接
           

二.sql99标準

文法：

    select 查詢清單

    from 表1 别名【連接配接類型】

    join 表2 别名

    on 連接配接條件

  【where 篩選條件】

  【group by 分組】

  【having 篩選條件】

  【order by 排序清單】

分類：

内連接配接

(★)：inner

外連接配接

左外

(★)：left 【outer】

右外

(★)：right 【outer】

全外

：full 【outer】

交叉連接配接

：cross

一）内連接配接A∩B

文法:

      select 查詢清單 from

表1 别名 inner join 表2 别名

on

連接配接條件

;

分類:

      等值,非等值,自連接配接

特點:

    ①添加排序,分組,篩選

    ②inner 可省略(直接是join)

    ③篩選條件放在where後面,連接配接條件放在on後面,提高分離性,便于閱讀

    ④

inner join

連接配接與sql92文法中等值連接配接最終實作的效果是一樣的,都是查詢多表的交集

2.1.1 等值連接配接

案例1:查詢員工名,部門名

use myemployees;
select last_name,department_name
from employees e
inner join departments d
on e.department_id=d.department_id;
           

案例2:查詢名中含有e的員工名和工種名(添加篩選)

select last_name,job_title
from employees e
inner join jobs j
on e.job_id=j.job_id
where e.last_name like '%e%'; # 添加where篩選，放在on後面
           

案例3:查詢部門個數大于3的城市名和部門個數(添加分組和篩選)

step1.查詢每個城市的部門個數

step2.篩選滿足條件的

select city,count(*) 部門個數
from departments d
inner join locations l
on d.location_id=l.location_id
group by city # 添加分組
having count(*)>3; # 添加篩選
           

案例4:查詢哪個部門員工個數大于3的部門名和員工個數,并按個數降序(添加排序)

select count(*) 個數,department_name
from employees e
inner join departments d
on e.department_id=d.department_id
group by department_name
having count(*)>3
order by count(*) desc; # 添加排序
           

案例5:查詢員工名,部門名,工種名,并按部門名降序(添加三表連接配接)

select last_name,department_name,job_title
from employees e
inner join departments d on e.department_id=d.department_id
inner join jobs j on e.job_id=j.job_id # 三表連接配接
order by department_name desc;
           

2.1.2 非等值連接配接

案例1： 查詢員工工資級别

select salary,grade_level
from employees e
join job_grades g  # 内連接配接可省略inner
on e.salary between g.lowest_sal and g.highest_sal; # 非等值連接配接
           

案例2： 查詢每個工資級别個數>20的個數,降序

select count(*),grade_level
from employees e
join job_grades g
on e.salary between g.lowest_sal and g.highest_sal
group by grade_level
having count(*)>20
order by grade_level desc; # desc：降序，預設是升序
           

2.1.3 自連接配接

案例： 查詢員工名字包含字元k,上級名字

select e.last_name,m.last_name
from employees e
join employees m
on e.manager_id=m.employee_id
where e.last_name like '%k%';
           

二）外連接配接

應用場景:

        用于查詢一個表有,另一個表沒有的記錄

特點:

        1.外連接配接的查詢結果為主表中的所有記錄

                如果從表中有和它比對的,則顯示比對的值

                如果從表中沒有和他比對的,則顯示null值

                外連接配接查詢結果=内連接配接結果+主表有而從表沒有的記錄

        2.左外連接配接,

left join

左邊是主表,右外連接配接

right join

右邊的是主表

        3.左外和右外交換兩個表的順序,可以實作同樣的效果

        4.全外連接配接=内連接配接結果+表1中有的但表2沒有+表2中有而表1中沒有

引入: 查詢男朋友不在boys表的女神名

use girls;
select * from beauty;
select * from boys;
           

左外連接配接 A∪(A∩B)

含義： 主表A，和從表B與主表A的比對值

select b.name
from beauty b
left outer join boys bo # 主表beauty，從表boys
on b.boyfriend_id=bo.id # 連接配接條件
where bo.id is null;
           

右外連接配接 （A∩B）∪B

含義： 主表B，和從表A與主表B的比對值

select b.name,bo.*
from boys bo
right outer join beauty b # 主表beauty，從表boys
on b.boyfriend_id=bo.id
where bo.id is null;
           

全外 A∪B

備注： mysql不支援，oracle支援

use girls;
select b.*,bo.*
from beauty b
full outer join boys bo
on b.boyfriend_id=bo.id;
           

案例1: 查詢哪個部門沒有員工

左外：

use myemployees;
select d.*,e.employee_id
from departments d
left outer join employees e # 主表departments，從表employees
on d.department_id=e.department_id
where e.employee_id is null;
           

右外：

select d.*,e.employee_id
from employees e
right outer join departments d
on d.department_id=e.department_id
where e.employee_id is null;
           

三）交叉連接配接

select b.*,bo.*
from beauty b
cross join boys bo; # 笛卡爾乘積
           

sql92 PK sql99

功能: sql99支援的較多

可讀性: sql99實作連接配接條件和篩選條件的分離,可讀性較高