MySQL聯(lián)合查詢詳細(xì)示例代碼

更新時間：2025年10月22日 11:35:31 作者：蘇小瀚

MySQL聯(lián)合查詢是數(shù)據(jù)庫操作中十分重要的技能之一,它允許用戶從多個表中提取并組合數(shù)據(jù),下面這篇文章主要介紹了MySQL聯(lián)合查詢的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下

1. 聯(lián)合查詢的由來

因為我們在創(chuàng)建數(shù)據(jù)表時候遵循范式的規(guī)則，將數(shù)據(jù)拆分成多個表，而我們想要查詢表的所有屬性列，就需要將幾張表的數(shù)據(jù)結(jié)合起來顯示，因此就產(chǎn)生了聯(lián)合查詢。比如：學(xué)生表和課程表，這里我們想要在一張表中顯示學(xué)生表和課程表的信息，就需要用到聯(lián)合查詢。

2. 笛卡爾積

2.1 理論介紹

數(shù)據(jù)庫中笛卡爾積指的是兩個表之間的運(yùn)算，我們可以將兩個表進(jìn)行笛卡爾積就能得到兩個表的完整信息。

對下面這兩個表進(jìn)行笛卡爾積的運(yùn)算：

得到的結(jié)果表是：

兩個表通過笛卡爾積得到的表的行數(shù)是兩個表行數(shù)的乘積，表的列數(shù)是兩個表列數(shù)的和。

我們觀察新得到的表會發(fā)現(xiàn)這張表里面有很多無效數(shù)據(jù)，也就是張三應(yīng)該是一班的，所以第二行是無效數(shù)據(jù)，那我們會發(fā)現(xiàn)無效數(shù)據(jù)是兩個班級id不相同的數(shù)據(jù)，那我們在查詢的時候可以添加一個where條件。

如何區(qū)分兩個班級id呢？

我們可以這樣表示 student.id 和 class.id。通過成員訪問運(yùn)算符 . 來實現(xiàn)。

2.2 SQL語句編寫

下面是聯(lián)合查詢的代碼實現(xiàn)：

create table class1(class_id int primary key, class_name varchar(20));
insert into class1 values(1,'一班'), (2,'二班');

create table student1(id int primary key, name varchar(20), gender varchar(10), class_id int, foreign key (class_id) references class1(class_id));
insert into student1 values(1,'張三','男',1), (2,'李四','男',1),(3,'王五','女',2);

select * from student1, class1 where student1.class_id = class1.class_id;

查詢結(jié)果為：

2.3 聯(lián)合查詢的流程

我們先對兩個表進(jìn)行笛卡爾積。
接著添加連接條件。
然后根據(jù)需求添加其他條件。
最后針對列進(jìn)行篩選/計算表達(dá)式/聚合查詢等操作。

下面我舉幾個例子，幫助理解聯(lián)合查詢的流程：

首先我們先準(zhǔn)備幾張表進(jìn)行查詢，下面有學(xué)生表，班級表，成績表，課程表四張表：

create table student(student_id int primary key auto_increment, name varchar(20), sno varchar(20), age int, gender int, enroll_date datetime, class_id int, foreign key (class_id) references class(class_id));
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);

create table class(class_id int primary key auto_increment, name varchar(20));
insert into class(name) values('001班'), ('002班'), ('003班');

create table course(course_id int primary key auto_increment, name varchar(20));
insert into course(name) values('Java'),('C++'),('MySQL'),('操作系統(tǒng)'),('計算機(jī)網(wǎng)絡(luò)'),('數(shù)據(jù)結(jié)構(gòu)');

create table score(score double, student_id int, course_id int, primary key(student_id,course_id));
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);

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

1. 我們確定要查詢的表來自那幾張表，進(jìn)行笛卡爾積運(yùn)算。

學(xué)生表，班級表

select * from student, class;

2. 確認(rèn)連接條件，進(jìn)行查詢。

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

3. 根據(jù)需求進(jìn)一步增加條件。

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

4. 根據(jù)需求來查找對應(yīng)的列。

select student.name, student.sno, student.age, student.gender, student.enroll_date, class.name as '班級名稱' 
from student,class 
where student.class_id = class.class_id and student.name = '孫悟空';

2.4 內(nèi)連接

內(nèi)連接相當(dāng)于在原來的聯(lián)合查詢的語句上進(jìn)行修改，這里用到 join on兩個關(guān)鍵字。

原來查詢是：

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

改為內(nèi)連接為：

select * from student join class on student.class_id = class.class_id;

這兩個SQL語句查詢出來的內(nèi)容都是一樣的。

我們可以將上面的聯(lián)合查詢語句改成：

#內(nèi)連接
# 1.笛卡爾積
select * from student join class;
# 2.添加連接條件
select * from student join class on student.class_id = class.class_id;
# 3.進(jìn)一步添加條件
select * from student join class on student.class_id = class.class_id
where student.name = '孫悟空';
# 4.對列進(jìn)行精簡
select student.name, student.sno, student.age, student.gender, student.enroll_date, class.name
from student join class on student.class_id = class.class_id
where student.name = '孫悟空';

查詢所有同學(xué)的總成績和同學(xué)的個人信息。

確認(rèn)從學(xué)生表和成績表中查找。

# 笛卡爾積
select * from student join score;
# 添加連接條件
select * from student join score on student.student_id = score.student_id;
# 沒有條件添加
#對列進(jìn)行精簡
select student.name, sum(score.score) as total_score
from student join score on student.student_id = score.student_id 
group by student.name;

查詢所有同學(xué)每門課的成績，及同學(xué)的個人信息。

確認(rèn)從學(xué)生表，成績表和課程表中查找：

# 笛卡爾積
select * from score join student join course;
# 添加連接條件
select * from score 
join student on student.student_id = score.student_id
join course on course.course_id = score.course_id;
#沒有條件進(jìn)行添加
#對列進(jìn)行精簡
select student.name, course.name as course_name, score.score
from score
join student on student.student_id = score.student_id
join course on course.course_id = score.course_id;

2.5 外連接

外連接分為左外連接，右外連接，全外連接，mysql不支持全外連接。

左外連接：如果左邊表的數(shù)據(jù)在右邊表中沒有匹配記錄，那么就會將對應(yīng)右邊記錄為null。

右外連接：如果右邊表對應(yīng)左邊表數(shù)據(jù)沒有匹配記錄，那么就會將對應(yīng)左邊記錄為null。

全外連接：左右兩張表互相存在不對應(yīng)的匹配數(shù)據(jù)，就會為null。

創(chuàng)建新的兩張表，學(xué)生表和成績表：

create table student(id int, name varchar(20));
insert into student values(1,'張三'), (2,'李四'), (3,'王五');

create table score(student_id int, score int);
insert into score values(1,88), (2,99), (4,77);

內(nèi)連接：

select * from student, score where student.id = score.student_id;

左外連接：

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

右外連接：

select * from student right join score on student.id = score.student_id;

2.6 自連接

自連接是表自己對自己進(jìn)行連接，我們可以把行變換成列，而列跟列之間能進(jìn)行比較，所以相當(dāng)于間接實現(xiàn)了行與行進(jìn)行比較。我們在表連接時候，要為表起兩個不同的別名。

顯示所有"MySQL"成績比"JAVA"成績高的成績信息：

這里我們是用成績表進(jìn)行自連接查詢：

select s1.student_id, s1.course_id, s1.score, s2.course_id, s2.score
from score as s1, score as s2 
where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;

2.7 子查詢

子查詢通常是把一個SQL語句的結(jié)果當(dāng)作另一個SQL語句的條件來進(jìn)行查詢的。

但是這種查詢方式比較難以閱讀，違背軟件開發(fā)的核心原則（將大問題轉(zhuǎn)換為多個小問題），很少使用。

單行子查詢：

查詢與不想畢業(yè)的同學(xué)的同班同學(xué)：

select name from student where class_id = 
(select class_id from student where name = '不想畢業(yè)');

多行子查詢：

查詢mysql或Java課程的成績信息：

select course_id,score from score where course_id in (
select course_id from course where name = 'MySQL' or name = 'Java');

多列子查詢：

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

 select * form 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(0) > 1);

2.8 合并查詢

這里我們可以將多個查詢結(jié)果合并在一起進(jìn)行，使用union 或者union all。

這里我們需要創(chuàng)建一個新表來進(jìn)行演示：

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);

union操作符：

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

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

查詢結(jié)果：

這里得union會把兩個查詢的結(jié)果取并集，并且會自動取出重復(fù)的行。

union all操作符：

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

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

查詢結(jié)果：

union all查詢時候查詢兩個結(jié)果的并集，不會去除重復(fù)的行。

2.9 插入查詢結(jié)果

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

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

這里后面的查詢出來的條件應(yīng)該與前面的條件類型和數(shù)量對應(yīng)。