MySQL數(shù)據(jù)庫(kù)查詢之多表查詢總結(jié)

更新時(shí)間：2022年08月04日 14:55:57 作者：計(jì)科_xiao_cai_ji

最近遇到了多表查詢的需求,也稱為關(guān)聯(lián)查詢,指兩個(gè)或更多個(gè)表一起完成查詢操作,下面這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫(kù)查詢之多表查詢的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

多表關(guān)系

在進(jìn)行數(shù)據(jù)庫(kù)表結(jié)構(gòu)的設(shè)計(jì)時(shí)，會(huì)根據(jù)業(yè)務(wù)的需求和業(yè)務(wù)模塊之間的關(guān)系，分析設(shè)計(jì)表結(jié)構(gòu)，由于業(yè)務(wù)之間相互關(guān)聯(lián)，所以各個(gè)表結(jié)構(gòu)之間也存在各種聯(lián)系

表與表之間的聯(lián)系：

1.一對(duì)多(多對(duì)一)

2.多對(duì)多

3.一對(duì)一

一對(duì)多(多對(duì)一)

例如，一個(gè)員工對(duì)應(yīng)一個(gè)部門，一個(gè)部門可以對(duì)應(yīng)多個(gè)員工

一般在多的一方創(chuàng)建外鍵，指向一的那一方

員工與部門，在員工表上設(shè)置外鍵，指向部門表

多對(duì)多

例如，一個(gè)學(xué)生可以選修多門課程，一個(gè)課程可以被多名學(xué)生選修

一般會(huì)建立第三張表，至少包含兩個(gè)外鍵，分別指向兩張表的主鍵

一對(duì)一

例如，用戶和自己的學(xué)歷信息的關(guān)系，一個(gè)人只對(duì)應(yīng)一條學(xué)歷信息

可以在任意一方加入外鍵，關(guān)聯(lián)另一方的主鍵，并且設(shè)置外鍵為唯一(unique)

注：可以放在一張表中，但是對(duì)其進(jìn)行拆分，一張表放基礎(chǔ)信息，另一張表放詳情，可以提升操作效率

多表查詢

概述：

從多張表中查詢數(shù)據(jù)

笛卡爾積：

笛卡爾積為兩個(gè)集合(兩張表)中的每條數(shù)據(jù)進(jìn)行兩兩組合的結(jié)果

在多表查詢時(shí)會(huì)產(chǎn)生笛卡爾積，要通過(guò)添加條件消除笛卡爾積

dept表：

請(qǐng)?zhí)砑訄D片描述

emp表：

請(qǐng)?zhí)砑訄D片描述

查詢產(chǎn)生笛卡爾積的結(jié)果：

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

消除笛卡爾積(添加條件)：

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

請(qǐng)?zhí)砑訄D片描述

多表查詢的分類

1.連接查詢：

內(nèi)連接：
   相當(dāng)于查詢AB的交集部分
外連接：
       左外連接：
           查詢A的所有數(shù)據(jù)，同時(shí)拼接上B對(duì)應(yīng)的數(shù)據(jù)
       右外連接：
           查詢B的所有數(shù)據(jù)，同時(shí)拼接上A中對(duì)應(yīng)的數(shù)據(jù)
自連接：
   表與自身連接查詢
   自連接必須給表取別名

2.子查詢

數(shù)據(jù)準(zhǔn)備

部門表：

請(qǐng)?zhí)砑訄D片描述

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, '研發(fā)部'),
       (2, '市場(chǎng)部'),
       (3, '財(cái)務(wù)部'),
       (4, '銷售部'),
       (5, '總經(jīng)辦'),
       (6, '人事部');

員工表：

請(qǐng)?zhí)砑訄D片描述

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 '入職時(shí)間',
    managerid int comment '直屬領(lǐng)導(dǎo)id',
    dept_id int comment '所在部門id'
) comment '員工表';

insert into emp
values ( 1, '金庸', 66, '總裁', 20000, '2000-01-01', null, 5 ),
       ( 2, '張無(wú)忌', 20, '項(xiàng)目經(jīng)理', 12500, '2005-12-05', 1, 1 ),
       ( 3, '楊曉', 33, '開(kāi)發(fā)', 8400, '2000-11-03', 2, 1 ),
       ( 4, '韋一笑', 48, '開(kāi)發(fā)', 11000, '2002-02-05', 2, 1 ),
       ( 5, '陳玉存', 43, '開(kāi)發(fā)', 10500, '2004-09-07', 3, 1 ),
       ( 6, '小昭', 19, '程序員鼓勵(lì)師', 6600, '2004-10-12', 2, 1 ),
       ( 7, '滅絕', 60, '財(cái)務(wù)總監(jiān)', 8500, '2002-09-12', 1, 3 ),
       ( 8, '周芷若', 19, '會(huì)計(jì)', 48000, '2006-06-02', 7, 3 ),
       ( 9, '丁敏君', 23, '出納', 5250, '2009-05-13', 7, 3 ),
       ( 10, '趙敏', 20, '市場(chǎng)部總監(jiān)', 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, '銷售總監(jiān)', 14000, '2004-10-12', 1, 4 ),
       ( 15, '魚梁洲', 38, '銷售', 4600, '2004-10-12', 14, 4 ),
       ( 16, '宋遠(yuǎn)橋', 40, '銷售', 4600, '2004-10-12', 14, 4 ),
       ( 17, '陳友諒', 42, null, 2000, '2011-10-12', 1, null );

內(nèi)連接

語(yǔ)法：

# 隱式內(nèi)連接
select 字段列表 from 表1,表2 where 條件;
# 顯示內(nèi)連接
select 字段列表 from 表1 [inner] join 表2 on 連接條件;

內(nèi)連接查詢的是兩張表交集的部分

# 查詢每一個(gè)員工的姓名及關(guān)聯(lián)的部門的名稱
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;

外連接

語(yǔ)法：

# 左外連接
select 字段列表 from 表1 left [outer] join 表2 on 條件;
# 右外連接
select 字段列表 from 表1 right [outer] join 表2 on 條件;

左外連接相當(dāng)于查詢表1的所有數(shù)據(jù)包含表1和表2交集的部分?jǐn)?shù)據(jù)

右外連接相當(dāng)于查詢表2的所有數(shù)據(jù)包含表1和表2交集部分的數(shù)據(jù)

# 查詢emp表的所有數(shù)據(jù)，和應(yīng)于的部門信息(左)
select emp.*, dept.* from emp left outer join dept on emp.dept_id = dept.id;
# 查詢dept表的所有數(shù)據(jù)，和對(duì)于的員工信息(右)
select dept.*, emp.* from emp right outer join dept on emp.dept_id = dept.id;

左外連接和右外連接可以進(jìn)行相互轉(zhuǎn)化

自連接

語(yǔ)法：

select 字段列表 from 表a 別名a join 表a 別名b on 條件;

自鏈接查詢可以是內(nèi)連接查詢也可以是外連接查詢

# 查詢員工及其所屬領(lǐng)導(dǎo)的名字
# 自連接可以看成兩張一樣的表進(jìn)行連接查詢
select a.name, b.name from emp a join emp b on a.managerid=b.id;

聯(lián)合查詢

union、union all

對(duì)于聯(lián)合查詢就是把多次查詢的結(jié)果合并起來(lái)，形成一個(gè)新的查詢結(jié)果集

語(yǔ)法：

select 字段列表 from 表a
union [all]
select 字段列表 from 表b

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

# 沒(méi)有all重復(fù)滿足條件的只出現(xiàn)一次
# 將薪資低于5000的員工和年齡大于50的員工查詢出來(lái)
select * from emp where salary>5000
union
select * from emp where age>50;

對(duì)于聯(lián)合查詢的多張表的列數(shù)必須保持一致，字段類型也要保持一致

union all會(huì)將全部的數(shù)據(jù)直接合并在一起，union會(huì)對(duì)合并之后的數(shù)據(jù)去重

子查詢

概念：SQL語(yǔ)句中嵌套select語(yǔ)句為嵌套查詢，又稱子查詢

select * from 表1 where 字段=(select 字段 from 表2);

子查詢外的語(yǔ)句可以是insert、update、delete、select中的一個(gè)

根據(jù)子查詢的結(jié)構(gòu)不同，分為：

標(biāo)量子查詢：子查詢的結(jié)果為單個(gè)值
列子查詢：子查詢的結(jié)果為一列
行子查詢：子查詢的結(jié)果為一行
表子查詢：子查詢的結(jié)果為多行多列

根據(jù)子查詢的位置，分為：

where之后
from之后
select之后

標(biāo)量子查詢

子查詢返回的結(jié)果是單個(gè)值(數(shù)字、字符串、日期等)，最簡(jiǎn)單的形式，這種子查詢稱為標(biāo)量子查詢
常用符號(hào)：=、<>、>、>=、<、<=

# 根據(jù)銷售部門的id查詢員工信息
# 先分開(kāi)查詢
# 查詢銷售部門的id
select id from dept where name='銷售部'; #id為4
# 查詢銷售部門中員工的信息
select * from emp where dept_id=4;
# 合并為一個(gè)查詢
select * from emp where dept_id=(select dept.id from dept where dept.name='銷售部' );

列子查詢

子查詢的結(jié)果為一列(可以是多行)的，這種子查詢?yōu)榱凶硬樵?/p>

常用操作符：

# 列子查詢
# 查詢銷售部和市場(chǎng)部的所有員工信息
# 查詢銷售部和市場(chǎng)部的id
select id from dept where name='銷售部' or name='市場(chǎng)部'; #id為2 4
# 查詢兩個(gè)部門的所有員工
select * from emp where dept_id in (2,4);
# 合并
select * from emp where dept_id in (select id from dept where name='銷售部' or name='市場(chǎng)部');

行子查詢

子查詢返回的結(jié)果是一行(可以是多列)，這種子查詢?yōu)樾凶硬樵?/p>

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

# 查詢與張無(wú)忌的薪資及直屬領(lǐng)導(dǎo)相同的員工信息
# 查詢張無(wú)忌的薪資和直屬領(lǐng)導(dǎo)
select salary, managerid from emp where name='張無(wú)忌';
# 查詢與張無(wú)忌的薪資及直屬領(lǐng)導(dǎo)相同的員工信息
select * from emp where (salary,managerid)=(select salary, managerid from emp where name='張無(wú)忌');

表子查詢

子查詢的結(jié)果是多行多列這種查詢?yōu)楸碜硬樵?/p>

常用操作符：in

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

表子查詢的子表作為臨時(shí)表

# 查詢?nèi)肼毴掌谑?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;

多表查詢案例

數(shù)據(jù)準(zhǔn)備：

create table salgrade (
    grade int,
    losal int comment '本薪資等級(jí)的最低界限',
    hisal int comment '最高界限'
) comment '薪資等級(jí)表';
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.查詢員工的姓名，年齡，職位，部門信息(隱式內(nèi)連接)

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

2.查詢年齡小于30的員工的姓名、年齡、職位、部門信息(顯示內(nèi)連接)

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的員工，及其歸屬部門名稱，如果員工沒(méi)有分配部門也要顯示

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

5.查詢所有員工的工資等級(jí)

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

6.查詢研發(fā)部所有員工的信息即工資等級(jí)

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

7.查詢研發(fā)部員工的平均工資

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

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.查詢所有部門信息，并統(tǒng)計(jì)部門的員工人數(shù)

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

總結(jié)

到此這篇關(guān)于MySQL數(shù)據(jù)庫(kù)查詢之多表查詢的文章就介紹到這了,更多相關(guān)MySQL多表查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL數(shù)據(jù)庫(kù)查詢之多表查詢總結(jié)

目錄

多表關(guān)系

表與表之間的聯(lián)系：

一對(duì)多(多對(duì)一)

多對(duì)多

一對(duì)一

多表查詢

多表查詢的分類

1.連接查詢：

2.子查詢

內(nèi)連接

外連接

自連接

聯(lián)合查詢

子查詢

標(biāo)量子查詢

列子查詢

行子查詢

表子查詢

多表查詢案例

總結(jié)

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具