MySQL聯合查詢實例代碼(非常全)

更新時間：2025年09月14日 14:48:17 作者：代碼不停

聯合查詢是一種將多個select語句的結果合并成一個結果集,允許垂直合并來自不同查詢的數據,前提是這些查詢具有相同數量的列且對應列的數據類型兼容,這篇文章主要介紹了MySQL聯合查詢的相關資料,需要的朋友可以參考下

使用聯合查詢的原因

在數據庫設計的范式下，有時候數據不可以放在一個表中，我們要分成多個表，但是我們在查詢的時候可能是要獲取這多個表中的信息，因此我們就需要聯合這多個表進行查詢

create table class(
       id int primary key auto_increment,
	   name varchar(20));
create table student(
       id int primary key auto_increment,
	   name varchar(20),
	   sex char(1),
	   class_id int,
	   foreign key(id) references class(id));

insert into class(name) values ('java113'),('java78'),('C++110');
insert into student(name, sex,class_id) values ('張三','男',1),('李四','女',1),('王五','男',2);

如果我們用多表查詢，則會出現下面這種情況

select * from student,class;

這兩個表的聯合查詢，其實是笛卡爾積，因此其查詢的列數就是兩個表的列數之和，行數就是其兩個表列數之積

如果表內容多，其數據就會變成非常龐大，因此上面這樣我們聯合查詢是不對的，并且其班級id要一樣才是正確的結果，剩余都是錯誤的結果

為了讓其按照我們的要求查詢，因此我們要加上where條件，當其class表中的id和student表中的class_id相等時候才可以

--此時只需要加上where條件即可
select * from student,class where student.class_id = class.id;

此時查詢結果就合理了

由于使用select * 查詢比較耗時，因此我們此時也可以指定列，但是我們的形式是 表名.列名，因為兩個表中可能有相同的列名，因此要加上列名

如果兩個表中列名不相同的列，其可以不添加表名

--查詢其對應列的信息
select student.id,student.name,sex,student.class_id,class.id,class.name from student,class where student.class_id = class.id;
--查詢張三的信息
select student.id,student.name,student.sex,student.class_id,class.id,class.name from student,class 
          where student.class_id = class.id and student.name = '張三';

如果我們不添加其對應的表，并且還是相同的列名，此時就會出現此時的id對應的列是摸棱兩可的，就是我們不知道這個列是來自那個表，因此就會出錯

聯合查詢步驟

1.確定查詢表 -->進行笛卡爾積
2.確定連接條件
3.加入查詢條件
4.精簡查詢列，也可以給表取別名

內連接

上面我們只是簡單的介紹了可以聯合查詢，下面我們就來詳細介紹一些聯合查詢的方式

1  select 字段 from 表1 別名1, 表2 別名2 where 連接條件 and 其他條件;
2 select 字段 from 表1 別名1 [inner] join 表2 別名2 on 連接條件 where 其他條件;
--此時的別名不是必須要取的，并且此時取別名的as是可以省略的

此時我們創(chuàng)建四個表來舉例

--創(chuàng)建課程表
create table course(
       id int primary key auto_increment,
			 name varchar(20)
);
--班級表
create table class(
			 id int primary key auto_increment,
			 name varchar(20)
);
--學生表
create table student(
       id int primary key auto_increment,
			 name varchar(20),
			 class_id int,
			 foreign key(class_id) references class(id)
);
--分數表
create table score(
			 student_id int ,
			 course_id int,
			 score int,
			 foreign key (student_id) references student(id),
			 foreign key (course_id) references course(id) 
);
# 課程表
insert into course (name) values ('Java'), ('C++'), ('MySQL'), ('操作系統'), ('計
算機網絡'), ('數據結構');
# 班級表
insert into class(name) values ('Java001班'), ('C++001班'), ('前端001班');
# 學?表
insert into student (name, class_id) values 
('唐三藏', 1),
('孫悟空', 1),
('豬悟能', 1),
('沙悟凈', 1),
('宋江', 2),
('武松',  2),
('李逹',  2),
('不想畢業(yè)', 2);
# 成績表
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);

查詢唐三藏的所有學科成績

-- 我們用上面兩種方式都可以
select s.name,sc.score from student as s,score as sc 
    where s.id = sc.student_id 
    and s.name = '唐三藏';

select s.name,sc.score from student as s 
      join score sc 
      on s.id = sc.student_id and s.name = '唐三藏';

例如查詢所有學生的總成績

select s.name, sum(sc.score) from student as s,score as sc
      where s.id = sc.student_id
      group by (s.id);-- 要進行分組，因為其求和肯定要是一個學生的成績

查詢所有同學的詳細信息包括考試成績

select
	s.id,
	s.name as 姓名,
	c.name as 課程,
	sc.score as 分數
from
	course c,
	student s,
	score sc 
where
	s.id = sc.student_id 
	and c.id = sc.course_id 
order by
	s.id;

外連接

外連接分為左外連接、右外連接和全外連接，但是MySQL是不支持全外連接

左外連接：返回左表中的所有記錄和右表匹配的記錄，如果右表沒有匹配到，結果集會顯示空

右外連接：和左外連接相反，返回右表中的所有記錄和左表匹配的記錄，如果左表沒有匹配到，結果集會顯示空

全外連接：結合了左外連接和右外連接的特點，返回左右表中的所有記錄。如果某?邊表中沒有匹配的記錄，則結果集中對應字段會顯?為NULL

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

左外連接

以上面的為例，查詢沒有參加考試的學生
這時候就可以讓student左外連接score，這樣就會把學生全部顯示出來
到時候如果成績?yōu)榭?，說明其沒有參加考試

select s.id,s.name,sc.* from student s 
 left join score sc on s.id = sc.student_id;

# 因為未考試的成績?yōu)榭?，因此其可以篩選掉考試的同學
select s.id,s.name,s.class_id from student s 
left join score sc on s.id = sc.student_id 
where sc.score is null;

右外連接

查詢沒有學生的班級

select * from student s right join class c on s.class_id = c.id;

# 此時沒有學生班級其學生的id肯定為空
select * from student s right join class c on s.class_id = c.id where s.id is null;

自連接

自連接就是自己對自己求笛卡爾積，在查詢的時候可以使用where條件對結果進行過濾，或者可以使用自己對自己表的數據進行比較，但是自連接的時候要對表起別名

select * from score,score;

此時會報錯，因為使用兩個名字相同的表或者是沒有取別名

select * from score as s1,score as s2;

查詢MySQL成績大于java成績大的學生

分開寫
# 先獲取起對應課程id
select id,name from course where name = 'MySQL' or name = 'java';

select * from  score s1,score s2 
            where s1.student_id = s2.student_id 
	        and s1.course_id = 3 and s2.course_id = 1 #學生id相同
	        and s1.score > s2.score; # MysSQL 成績>java

-- 放在一起寫
SELECT
	s1.*,
	s2.* 
FROM
	score s1,
	score s2,
	course c1,
	course c2 
WHERE
	s1.student_id = s2.student_id 
	AND s1.course_id = c1.id 
	AND s2.course_id = c2.id 
	AND c1.NAME = 'MySQL' 
	AND c2.NAME = 'java' 
	AND s1.score > s2.score;

此時如果想看到其所有信息，就要將其student 、class和score都要進行笛卡爾積

	# 顯示其所有信息
	SELECT
	stu.name as 姓名,
	c.name as 班級,
	s1.score as MySQL,
	s2.score as java
FROM
	score s1,
	score s2,
	course c1,
	course c2,
	student stu,
	class c 
WHERE
	s1.student_id = s2.student_id 
	AND s1.course_id = c1.id 
	AND s2.course_id = c2.id 
	and stu.id = s1.student_id
	and stu.class_id = c.id
	AND c1.NAME = 'MySQL' 
	AND c2.NAME = 'java' 
	AND s1.score > s2.score

子查詢

有時候我們進行查詢時候的where條件可能還需要進行查詢，就是一個查詢的結果作為另一個查詢的條件

select * from table1 where col_name1 {= | IN} (
 select col_name1 from table2 where col_name2 {= | IN} [(
 select ...)
 ] ...
)

單行子查詢

where條件里的查詢只返回一行數據

例如查詢姓名為 '不想畢業(yè)’的同學

因此要先查找其班級id，后面再根據其班級id進行查找

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

當然這個我們還是可以分開寫的

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

多列子查詢

返回多行數據，因此我們要是用[not ] in 關鍵字

查詢課程MySQL和java的成績

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

當然也可以查詢除了MySQL和java的成績

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

多列子查詢

多列子查詢就是返回的是多個列的數據

例如：查詢重復錄入的分數

insert into score(score, student_id, course_id) 
 values(70.5, 1, 1),(98.5, 1, 3),(60, 2, 1);
 # 查詢重復成績
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);

from 語句中使用子查詢

我們在進行查詢的時候，MySQL是自動創(chuàng)建一個臨時表來存放，因此我們可以那這個臨時表進行子查詢或者表連接操作

例如：查詢所有比‘java001班’平均分高的成績信息

# 先求平均分
select avg(sc.score) from score sc,class c,student s where c.id = s.class_id and sc.student_id = s.id and c.name = 'java001班';

SELECT
	* 
FROM
	score s,(
	SELECT
		avg( sc.score ) score 
	FROM
		score sc,
		class c,
		student s 
	WHERE
		c.id = s.class_id 
		AND sc.student_id = s.id 
		AND c.NAME = 'java001班' 
	) tmp 
WHERE
	s.score > tmp.score;

tmp是臨時表

合并查詢

開發(fā)過程中，為了合并多個select 返回的結果，可以使用操作集 union 、union all

# 先創(chuàng)建一個和原本student相同的表
create table student1 like student;
# 插入一些數據
insert into student1 (name,  class_id) values 
('唐三藏',  1),
('劉備',  3),
('張飛',  3),
('關羽',  3);
# 此時
select * from student1;

union

用于兩個結果的并集，并且可以去掉重復行

?例：查詢student表中id<3的同學和student1表中的所有同學

# 查詢student表中student中id < 3的同學和student1表中的所有同學
select * from student where id < 3 
          union select * from student1;

此時會根據要求將兩個select結果合并，并且去掉了重復部分

Union all

也是用于合并結果集，此操作符是不可以去掉結果的重復行

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

此時就可能會有重復的行

插入查詢結果

可以將一個查詢結果當作數據插入一個表中

INSERT INTO table_name [(column [, column ...])] SELECT ...

例如：將student表中C++001班的學?復制到student1表中

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

總結

到此這篇關于MySQL聯合查詢實的文章就介紹到這了,更多相關MySQL聯合查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

Mysql內連接與外連接的區(qū)別詳解
最近別人問我一個問題:數據庫中的左連接和右連接有什么區(qū)別?所以這篇文章主要給大家介紹了關于Mysql內連接和外連接區(qū)別的相關資料,需要的朋友可以參考下
2023-01-01
MySQL學習筆記之創(chuàng)建、刪除、修改表的方法
這篇文章主要介紹了MySQL學習筆記之創(chuàng)建、刪除、修改表的方法,結合實例分析了mysql創(chuàng)建表、增加,修改及刪除列、查看表結構、刪除表等基本語法與使用技巧,需要的朋友可以參考下
2016-09-09
Mysql數據庫的優(yōu)化詳解
這篇文章主要介紹了Mysql數據庫的優(yōu)化詳解,查詢優(yōu)化的本質是讓數據庫優(yōu)化器為SQL語句選擇最佳的執(zhí)行計劃,一般來說，對于在線交易處理(OLTP)系統的數據庫，減少數據庫磁盤I/O是SQL語句性能優(yōu)化的首要方法,需要的朋友可以參考下
2023-07-07
簡單了解MySQL union all與union的區(qū)別
這篇文章主要介紹了簡單了解MySQL union all與union的區(qū)別,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下
2020-03-03
MySQL慢SQL語句常見誘因以及解決方法
在本篇文章里小編給大家整理的關于MySQL慢SQL語句常見誘因以及解決方法，有需要的朋友們可以學習下。
2019-08-08
ubuntu系統中安裝mysql5.6(通過二進制)
今天工作中需要對一臺ubantu的系統安裝mysql，因為以前一直使用的是centos,雖然它也是類unix但是和redhat或centos命令上還是有點差別。所以通過網上查閱資料，終于安裝成功了，現在將步驟分享給大家，有需要的朋友們可以參考借鑒。
2016-10-10
win2003服務器下配置 MySQL 群集(Cluster)的方法
MySQL 群集是 MySQL 適合于分布式計算環(huán)境的高可用、高冗余版本。它采用了 NDB Cluster 存儲引擎，允許在 1 個群集中運行多個 MySQL 服務器。
2010-12-12
MySQL日期ATE、TIME、DATETIME、TIMESTAMP和YEAR的使用語句
本文介紹了MySQL中日期和時間數據類型及其常見使用語句,包括DATE、TIME、DATETIME、TIMESTAMP和YEAR等,以及它們的應用場景,如記錄事件發(fā)生時間、查詢特定時間段內的數據、定期清理過期數據和生成報表等
2024-11-11
MySQL索引原理深度解析與優(yōu)化策略實戰(zhàn)方法
索引是存儲引擎用于快速查找記錄的一種數據結構,通?；谧侄沃禈嫿?能顯著降低查詢的時間復雜度,本文給大家介紹MySQL索引原理深度解析與優(yōu)化策略實戰(zhàn),感興趣的朋友一起看看吧
2025-10-10
mysql的select?into給多個字段變量賦值方式
這篇文章主要介紹了mysql的select?into給多個字段變量賦值方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
2022-09-09