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

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

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

??摘要

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

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

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

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

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

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

create database if not exists test; -- 創(chuàng)建庫
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)合查詢過程

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

首先確定參與查詢的表，分別是student表與class表

select * from student,class;

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

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

加入查詢條件

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

在這里插入圖片描述

精減查詢結(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 其他條件;

查詢"唐三藏"同學(xué)的成績

-- 查詢唐三藏同學(xué)的成績
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` = '唐三藏';

在這里插入圖片描述

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

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

在這里插入圖片描述

查詢所有同學(xué)每門課的成績，及同學(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. 外連接

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

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

查詢沒有參加考試的同學(xué)信息

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

在這里插入圖片描述

查詢沒有學(xué)生的班級(jí)

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

??4. 自連接

自連接是自己與自己取笛卡爾積，可以把行轉(zhuǎn)化成列，在查詢的時(shí)候可以使用where條件對(duì)結(jié)果進(jìn)行過濾，以至于實(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"成績比"JAVA"成績高的成績信息

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的成績，在查出MYSQL的成績，兩張表分別各自包含JAVA和MYSQL成績，然后進(jìn)行連接，連接條件為表一與表二學(xué)生id相同，限制條件為MYSQL成績大于JAVA成績

??5. 子查詢

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

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

??5.1 單行子查詢

示例：查詢與"不想畢業(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 多行子查詢

示例：查詢"MySQL"或"Java"課程的成績信息

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

在這里插入圖片描述

使用 not in 可以查詢除了"MySQL"或"Java"課程的成績

??5.3 多列子查詢

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

示例：查詢重復(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子句中使用子查詢

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

示例：查詢所有比"Java001班"平均分高的成績信息

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. 合并查詢

為了合并多個(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ù)行。

示例：查詢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ù)行。

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

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

??7. 插入查詢結(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班';