從入門(mén)到精通MySQL聯(lián)合查詢(xún)

更新時(shí)間：2025年07月02日 15:49:24 作者：長(zhǎng)安城沒(méi)有風(fēng)

這篇文章主要介紹了從入門(mén)到精通MySQL聯(lián)合查詢(xún),本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),需要的朋友可以參考下

??摘要

前面我們學(xué)習(xí)了數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)要滿(mǎn)足三大范式，也就意味著數(shù)據(jù)會(huì)被拆分到許多張表中，當(dāng)我們想查詢(xún)一個(gè)學(xué)生的基本信息與成績(jī)時(shí)，此時(shí)就會(huì)涉及到學(xué)生表，班級(jí)表，成績(jī)表等多張數(shù)據(jù)表，但我們給用戶(hù)展示信息時(shí)并不會(huì)把冗余的數(shù)據(jù)也展示給用戶(hù)，所以我們就需要用到聯(lián)合查詢(xún)從多張表中查詢(xún)出有用的數(shù)據(jù)。此時(shí)的‘聯(lián)合’，就是指多張數(shù)據(jù)表的組合。

??1. 多表聯(lián)合查詢(xún)時(shí)MySQL內(nèi)部原理

當(dāng)我們進(jìn)行多表聯(lián)合查詢(xún)時(shí)，MySQL內(nèi)部會(huì)進(jìn)行以下操作：

參與查詢(xún)的所有表取笛卡爾積，結(jié)果集在臨時(shí)表中

觀察哪些記錄是有效數(shù)據(jù)，根據(jù)兩個(gè)表的關(guān)聯(lián)關(guān)系過(guò)濾掉無(wú)效數(shù)據(jù)

=======================================================================
首先我們要構(gòu)造一個(gè)練習(xí)數(shù)據(jù)

create database if not exists test; -- 創(chuàng)建庫(kù)
use test;
-- 課程表
create table if not exists course(
  id bigint primary key auto_increment,
  `name` varchar(20) not null
);
insert into course (name) values ('Java'), ('C++'), ('MySQL'), ('操作系統(tǒng)'), ('計(jì)
算機(jī)網(wǎng)絡(luò)'), ('數(shù)據(jù)結(jié)構(gòu)');
-- 學(xué)生表
create table if not exists student(
  id bigint primary key auto_increment,
  `name` varchar(20),
  sno varchar(20),
  age bigint,
  gender bigint,
  enroll_date varchar(20),
  class_id bigint
);
insert into student (name, sno, age, gender, enroll_date, class_id) values
('唐三藏', '100001', 18, 1, '1986-09-01', 1),
('孫悟空', '100002', 18, 1, '1986-09-01', 1),
('豬悟能', '100003', 18, 1, '1986-09-01', 1),
('沙悟凈', '100004', 18, 1, '1986-09-01', 1),
('宋江', '200001', 18, 1, '2000-09-01', 2),
('武松', '200002', 18, 1, '2000-09-01', 2),
('李逹', '200003', 18, 1, '2000-09-01', 2),
('不想畢業(yè)', '200004', 18, 1, '2000-09-01', 2);
-- 班級(jí)表
create table if not exists class(
  id bigint primary key auto_increment,
  `name` varchar(20)
);
insert into class(name) values ('Java001班'), ('C++001班'), ('前端001班');
-- 分?jǐn)?shù)表
create table if not exists score(
  id bigint primary key auto_increment,
  score bigint,
  student_id bigint,
  course_id bigint
);
insert into score (score, student_id, course_id) values
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
(60, 2, 1),(59.5, 2, 5),
(33, 3, 1),(68, 3, 3),(99, 3, 5),
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
(81, 5, 1),(37, 5, 5),
(56, 6, 2),(43, 6, 4),(79, 6, 6),
(80, 7, 2),(92, 7, 6);

Navicat可視化圖：

班級(jí)表

課程表

分?jǐn)?shù)表

學(xué)生表

??1.1 實(shí)例：一個(gè)完整的聯(lián)合查詢(xún)過(guò)程

查詢(xún)學(xué)生姓名為孫悟空的詳細(xì)信息，包括學(xué)生個(gè)人信息和班級(jí)信息

首先確定參與查詢(xún)的表，分別是student表與class表

select * from student,class;

確定連接條件，條件為student表中的class_id要與class表中的id相等

select * from student,class where student.class_id = class.id;

加入查詢(xún)條件

select * from student,class where student.class_id = class.id and student.`name` = '孫悟空';

在這里插入圖片描述

精減查詢(xún)結(jié)果字段

select
 student.id,
 student.name,
 class.name
from 
  student,class 
where
  student.class_id = class.id 
and 
  student.`name` = '孫悟空';

可以為表名指定別名

select
 stu.id,
 stu.name,
 c.name
from 
  student as stu,class as c
where
  stu.class_id =c.id 
and 
  stu.`name` = '孫悟空';

??2. 內(nèi)連接

select * from 表名1 as 別名1 , 表名2 as 別名2 where 連接條件 and 其他條件;

查詢(xún)"唐三藏"同學(xué)的成績(jī)

-- 查詢(xún)唐三藏同學(xué)的成績(jī)
select
 student.`name`,score.score,course.`name` 
 from
  student,score,course 
 where
  student.id = score.student_id 
 and
  score.course_id = course.id 
 and
  student.`name` = '唐三藏';

在這里插入圖片描述

查詢(xún)所有同學(xué)的總成績(jī)，及同學(xué)的個(gè)人信息

  select 
    student.`name`,sum(score.score) as '總分'
  from 
    student,score
  where
    student.id = score.student_id
  group by 
    `name`;

在這里插入圖片描述

查詢(xún)所有同學(xué)每門(mén)課的成績(jī)，及同學(xué)的個(gè)人信息

select
 student.`name`,score.score,course.`name`
 from
 student,score,course 
 where 
 student.id = score.student_id 
 and 
 score.course_id = course.id;

??3. 外連接

外連接分為左外連接、右外連接和全外連接三種類(lèi)型，因?yàn)镸ySQL不支持全外連接，所以本文不再介紹外連接部分。
• 左外連接：返回左表的所有記錄和右表中匹配的記錄。如果右表中沒(méi)有匹配的記錄，則結(jié)果集中對(duì)
應(yīng)字段會(huì)顯示為NULL。
• 右外連接：與左外連接相反，返回右表的所有記錄和左表中匹配的記錄。如果左表中沒(méi)有匹配的記
錄，則結(jié)果集中對(duì)應(yīng)字段會(huì)顯示為NULL。

-- 左外連接，表1完全顯?
select 字段名 from 表名1 left join 表名2 on 連接條件;
-- 右外連接，表2完全顯?
select 字段 from 表名1 right join 表名2 on 連接條件;

查詢(xún)沒(méi)有參加考試的同學(xué)信息

select * from student left join score on student.id = score.student_id where score.score is null;

在這里插入圖片描述

查詢(xún)沒(méi)有學(xué)生的班級(jí)

select * from student right join class on class.id = student.class_id where student.id is null;

??4. 自連接

自連接是自己與自己取笛卡爾積，可以把行轉(zhuǎn)化成列，在查詢(xún)的時(shí)候可以使用where條件對(duì)結(jié)果進(jìn)行過(guò)濾，以至于實(shí)現(xiàn)行與行之間的比較，在做自連接時(shí)要為表起別名（否則報(bào)錯(cuò)）。

--不為表指定別名
mysql> select * from score, score;
ERROR 1066 (42000): Not unique table/alias: 'score'
--指定別名
mysql> select * from score s1, score s2;

顯示所有"MySQL"成績(jī)比"JAVA"成績(jī)高的成績(jī)信息

select s1.student_id as '學(xué)生',s1.score as 'MySQL',s2.score as 'JAVA' from (select * from score where  course_id = 3) as s1 ,(select * from score where course_id = 1 ) as s2 where s1.student_id = s2.student_id and s1.score > s2.score;

思路：先查出JAVA的成績(jī)，在查出MYSQL的成績(jī)，兩張表分別各自包含JAVA和MYSQL成績(jī)，然后進(jìn)行連接，連接條件為表一與表二學(xué)生id相同，限制條件為MYSQL成績(jī)大于JAVA成績(jī)

??5. 子查詢(xún)

子查詢(xún)是把?個(gè)SELECT語(yǔ)句的結(jié)果當(dāng)做別一個(gè)SELECT語(yǔ)句的條件，也叫嵌套查詢(xún)。

select * from table1 where condition [= |in]（select * from where (......)）

??5.1 單行子查詢(xún)

示例：查詢(xún)與"不想畢業(yè)"同學(xué)的同班同學(xué)

select student.`name`,student.class_id 
  from 
    student  
  where 
    class_id   = (select class_id from student where `name` = '不想畢業(yè)' ) 
  and 
    `name` != '不想畢業(yè)';

??5.2 多行子查詢(xún)

示例：查詢(xún)"MySQL"或"Java"課程的成績(jī)信息

select * from score where course_id in (select course.id from course where `name` = 'Java' or `name` = 'MySQL');

在這里插入圖片描述

使用 not in 可以查詢(xún)除了"MySQL"或"Java"課程的成績(jī)

??5.3 多列子查詢(xún)

單行子查詢(xún)和多行子查詢(xún)都只返回一列數(shù)據(jù)，多列子查詢(xún)中可以返回多個(gè)列的數(shù)據(jù)，外層查詢(xún)與嵌套的內(nèi)層查詢(xún)的列要匹配

示例：查詢(xún)重復(fù)錄入的分?jǐn)?shù)

select *  from score where (score,student_id,course_id) in (select score,student_id,course_id from score group by score,student_id,course_id having count(*)>1);

在這里插入圖片描述

??5.4 在from子句中使用子查詢(xún)

當(dāng)?個(gè)查詢(xún)產(chǎn)生結(jié)果時(shí)，MySQL自動(dòng)創(chuàng)建一個(gè)臨時(shí)表，然后把結(jié)果集放在這個(gè)臨時(shí)表中，最終返回
給用戶(hù)，在from子句中也可以使用臨時(shí)表進(jìn)行子查詢(xún)或表連接操作

示例：查詢(xún)所有比"Java001班"平均分高的成績(jī)信息

select * from score as s ,(select avg(score) as avg_score from score where student_id in ( select student_id from student where class_id = 1))  as tmp where s.score > tmp.avg_score;

??6. 合并查詢(xún)

為了合并多個(gè)select操作返回的結(jié)果，可以使?集合操作符 union，union all

-- 創(chuàng)建?個(gè)新表并初始化數(shù)據(jù)
 create table student1 like student;
 
insert into student1 (name, sno, age, gender, enroll_date, class_id) values
('唐三藏', '100001', 18, 1, '1986-09-01', 1),
('劉備', '300001', 18, 1, '1993-09-01', 3),
('張?', '300002', 18, 1, '1993-09-01', 3),
('關(guān)?', '300003', 18, 1, '1993-09-01', 3);

??6.1 union

該操作符用于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行。

示例：查詢(xún)student表中 id < 3 的同學(xué)和student1表中的所有同學(xué)

select * from student where id<3 union select * from student1;

??6.2 union all

該操作符?于取得兩個(gè)結(jié)果集的并集。當(dāng)使用該操作符時(shí)，不會(huì)去掉結(jié)果集中的重復(fù)行。

示例：查詢(xún)student表中 id < 3 的同學(xué)和student1表中的所有同學(xué)

select * from student where id<3 union all select * from student1;

??7. 插入查詢(xún)結(jié)果

insert into 表名(列名1，列名2) select .....

示例：將student表中C++001班的學(xué)生復(fù)制到student1表中

insert into student1 (name, sno, age, gender, enroll_date, class_id)
select s.name, s.sno, s.age, s.gender, s.enroll_date, s.class_id
from student s, class c where s.class_id = c.id and c.name = 'C++001班';