MySQL資料庫查詢之多表查詢總結

2022-08-04 22:07:51

多表關係

在進行資料庫表結構的設計時，會根據業務的需求和業務模組之間的關係，分析設計表結構，由於業務之間相互關聯，所以各個表結構之間也存在各種聯絡

表與表之間的聯絡：

1.一對多(多對一)

2.多對多

3.一對一

一對多(多對一)

例如，一個員工對應一個部門，一個部門可以對應多個員工

一般在多的一方建立外來鍵，指向一的那一方

員工與部門，在員工表上設定外來鍵，指向部門表

多對多

例如，一個學生可以選修多門課程，一個課程可以被多名學生選修

一般會建立第三張表，至少包含兩個外來鍵，分別指向兩張表的主鍵

一對一

例如，使用者和自己的學歷資訊的關係，一個人只對應一條學歷資訊

可以在任意一方加入外來鍵，關聯另一方的主鍵，並且設定外來鍵為唯一(unique)

注：可以放在一張表中，但是對其進行拆分，一張表放基礎資訊，另一張表放詳情，可以提升操作效率

多表查詢

概述：

從多張表中查詢資料

笛卡爾積：

笛卡爾積為兩個集合(兩張表)中的每條資料進行兩兩組合的結果

在多表查詢時會產生笛卡爾積，要通過新增條件消除笛卡爾積

dept表：

emp表：

查詢產生笛卡爾積的結果：

select * from emp, dept where emp.dept_id=dept.id;

消除笛卡爾積(新增條件)：

select * from emp, dept where emp.dept_id=dept.id;

多表查詢的分類

1.連線查詢：

內連線：
   相當於查詢AB的交集部分
外連線：
       左外連線：
           查詢A的所有資料，同時拼接上B對應的資料
       右外連線：
           查詢B的所有資料，同時拼接上A中對應的資料
自連線：
   表與自身連線查詢
   自連線必須給表取別名

2.子查詢

資料準備

部門表：

create table dept (
    id int auto_increment primary key comment 'id',
    name varchar(50) not null comment '部門名稱'
) comment '部門表';

insert into dept (id, name)
values (1, '研發部'),
       (2, '市場部'),
       (3, '財務部'),
       (4, '銷售部'),
       (5, '總經辦'),
       (6, '人事部');

員工表：

create table emp(
    id int auto_increment primary key ,
    name varchar(50) not null ,
    age int,
    job varchar(20) comment '職位',
    salary int ,
    entrydate date comment '入職時間',
    managerid int comment '直屬領導id',
    dept_id int comment '所在部門id'
) comment '員工表';

insert into emp
values ( 1, '金庸', 66, '總裁', 20000, '2000-01-01', null, 5 ),
       ( 2, '張無忌', 20, '專案經理', 12500, '2005-12-05', 1, 1 ),
       ( 3, '楊曉', 33, '開發', 8400, '2000-11-03', 2, 1 ),
       ( 4, '韋一笑', 48, '開發', 11000, '2002-02-05', 2, 1 ),
       ( 5, '陳玉存', 43, '開發', 10500, '2004-09-07', 3, 1 ),
       ( 6, '小昭', 19, '程式設計師鼓勵師', 6600, '2004-10-12', 2, 1 ),
       ( 7, '滅絕', 60, '財務總監', 8500, '2002-09-12', 1, 3 ),
       ( 8, '周芷若', 19, '會計', 48000, '2006-06-02', 7, 3 ),
       ( 9, '丁敏君', 23, '出納', 5250, '2009-05-13', 7, 3 ),
       ( 10, '趙敏', 20, '市場部總監', 12500, '2004-10-12', 1, 2 ),
       ( 11, '鹿杖客', 56, '職員', 3750, '2006-10-03', 10, 2 ),
       ( 12, '何碧文', 19, '職員', 3750, '2007-05-09', 10, 2 ),
       ( 13, '東方白', 19, '職員', 5500, '2009-02-12', 10, 2 ),
       ( 14, '張三丰', 88, '銷售總監', 14000, '2004-10-12', 1, 4 ),
       ( 15, '魚梁洲', 38, '銷售', 4600, '2004-10-12', 14, 4 ),
       ( 16, '宋遠橋', 40, '銷售', 4600, '2004-10-12', 14, 4 ),
       ( 17, '陳友諒', 42, null, 2000, '2011-10-12', 1, null );

內連線

語法：

# 隱式內連線
select 欄位列表 from 表1,表2 where 條件;
# 顯示內連線
select 欄位列表 from 表1 [inner] join 表2 on 連線條件;

內連線查詢的是兩張表交集的部分

# 查詢每一個員工的姓名及關聯的部門的名稱
select emp.name, dept.name from emp, dept where emp.dept_id=dept.id;
select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id;

外連線

語法：

# 左外連線
select 欄位列表 from 表1 left [outer] join 表2 on 條件;
# 右外連線
select 欄位列表 from 表1 right [outer] join 表2 on 條件;

左外連線相當於查詢表1的所有資料包含表1和表2交集的部分資料

右外連線相當於查詢表2的所有資料包含表1和表2交集部分的資料

# 查詢emp表的所有資料，和應於的部門資訊(左)
select emp.*, dept.* from emp left outer join dept on emp.dept_id = dept.id;
# 查詢dept表的所有資料，和對於的員工資訊(右)
select dept.*, emp.* from emp right outer join dept on emp.dept_id = dept.id;

左外連線和右外連線可以進行相互轉化

自連線

語法：

select 欄位列表 from 表a 別名a join 表a 別名b on 條件;

自連結查詢可以是內連線查詢也可以是外連線查詢

# 查詢員工及其所屬領導的名字
# 自連線可以看成兩張一樣的表進行連線查詢
select a.name, b.name from emp a join emp b on a.managerid=b.id;

聯合查詢

union、union all

對於聯合查詢就是把多次查詢的結果合併起來，形成一個新的查詢結果集

語法：

select 欄位列表 from 表a
union [all]
select 欄位列表 from 表b

# 將薪資低於5000的員工和年齡大於50的員工查詢出來
select * from emp where salary>5000
union all
select * from emp where age>50;

# 沒有all重複滿足條件的只出現一次
# 將薪資低於5000的員工和年齡大於50的員工查詢出來
select * from emp where salary>5000
union
select * from emp where age>50;

對於聯合查詢的多張表的列數必須保持一致，欄位型別也要保持一致

union all會將全部的資料直接合並在一起，union會對合並之後的資料去重

子查詢

概念：SQL語句中巢狀select語句為巢狀查詢，又稱子查詢

select * from 表1 where 欄位=(select 欄位 from 表2);

子查詢外的語句可以是insert、update、delete、select中的一個

根據子查詢的結構不同，分為：

標量子查詢：子查詢的結果為單個值
列子查詢：子查詢的結果為一列
行子查詢：子查詢的結果為一行
表子查詢：子查詢的結果為多行多列

根據子查詢的位置，分為：

where之後
from之後
select之後

標量子查詢

子查詢返回的結果是單個值(數位、字串、日期等)，最簡單的形式，這種子查詢稱為標量子查詢
常用符號：=、<>、>、>=、<、<=

# 根據銷售部門的id查詢員工資訊
# 先分開查詢
# 查詢銷售部門的id
select id from dept where name='銷售部'; #id為4
# 查詢銷售部門中員工的資訊
select * from emp where dept_id=4;
# 合併為一個查詢
select * from emp where dept_id=(select dept.id from dept where dept.name='銷售部' );

列子查詢

子查詢的結果為一列(可以是多行)的，這種子查詢為列子查詢

常用操作符：

# 列子查詢
# 查詢銷售部和市場部的所有員工資訊
# 查詢銷售部和市場部的id
select id from dept where name='銷售部' or name='市場部'; #id為2 4
# 查詢兩個部門的所有員工
select * from emp where dept_id in (2,4);
# 合併
select * from emp where dept_id in (select id from dept where name='銷售部' or name='市場部');

行子查詢

子查詢返回的結果是一行(可以是多列)，這種子查詢為行子查詢

常用操作符：=、<>、in、not in

# 查詢與張無忌的薪資及直屬領導相同的員工資訊
# 查詢張無忌的薪資和直屬領導
select salary, managerid from emp where name='張無忌';
# 查詢與張無忌的薪資及直屬領導相同的員工資訊
select * from emp where (salary,managerid)=(select salary, managerid from emp where name='張無忌');

表子查詢

子查詢的結果是多行多列這種查詢為表子查詢

常用操作符：in

# 查詢與鹿杖客和宋遠橋的職位和薪資相同的員工資訊
select * from emp where (job, salary) in ( select job, salary from emp where name in ('鹿杖客', '宋遠橋'));

表子查詢的子表作為臨時表

# 查詢入職日期是'2006-01-01‘之後的員工資訊和部門資訊
# 先查詢出入職在'2006-01-01‘之後員工的所有資訊
# 與部門表左連線
select e.*, dept.* from (select * from emp where entrydate>'2006-01-01') e left outer join dept on e.dept_id=dept.id;

多表查詢案例

資料準備：

create table salgrade (
    grade int,
    losal int comment '本薪資等級的最低界限',
    hisal int comment '最高界限'
) comment '薪資等級表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,025001,30000);

1.查詢員工的姓名，年齡，職位，部門資訊(隱式內連線)

select e.name, e.age, e.job, d.* 
from emp e, dept d 
where e.dept_id=d.id;

2.查詢年齡小於30的員工的姓名、年齡、職位、部門資訊(顯示內連線)

select e.name,e.age,e.job,d.*
from emp e
inner join dept d on e.dept_id = d.id
where e.age<30;

3.查詢擁有員工的部門id，部門名稱

select distinct d.id,d.name
from emp e, dept d
where d.id=e.dept_id;

4.查詢所有年齡大於40的員工，及其歸屬部門名稱，如果員工沒有分配部門也要顯示

select e.*,d.name
from emp e
left outer join dept d on e.dept_id = d.id
where e.age>40;

5.查詢所有員工的工資等級

select e.*,s.grade
from emp e, salgrade s
where e.salary between s.losal and s.hisal;

6.查詢研發部所有員工的資訊即工資等級

select e.*,s.grade
from emp e,dept d,salgrade s
where (e.dept_id=d.id) and (d.name='研發部') and (e.salary between s.losal and s.hisal);

7.查詢研發部員工的平均工資

select avg(e.salary)
from emp e, dept d
where e.dept_id=d.id and d.name='研發部';

8.查詢工資比滅絕高的員工資訊

select *
from emp
where emp.salary > (
                      select e.salary
                      from emp e
                      where e.name='滅絕'
                      );

9.查詢比平均薪資高的員工資訊

select *
from emp
where salary> (
    select avg(e.salary)
    from emp e
    );

10.查詢低於本部門平均工資的員工資訊

select *
from emp
where emp.salary<(
    select avg(salary)
    from emp e
    where e.dept_id=emp.dept_id
    );

11.查詢所有部門資訊，並統計部門的員工人數

select d.*, (
    select count(*)
    from emp
    where emp.dept_id=d.id
    )
from dept d;

總結

到此這篇關於MySQL資料庫查詢之多表查詢的文章就介紹到這了,更多相關MySQL多表查詢內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！