MySQL聯(lián)合查詢實(shí)例代碼(非常全)

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

聯(lián)合查詢是一種將多個(gè)select語句的結(jié)果合并成一個(gè)結(jié)果集,允許垂直合并來自不同查詢的數(shù)據(jù),前提是這些查詢具有相同數(shù)量的列且對應(yīng)列的數(shù)據(jù)類型兼容,這篇文章主要介紹了MySQL聯(lián)合查詢的相關(guān)資料,需要的朋友可以參考下

使用聯(lián)合查詢的原因

在數(shù)據(jù)庫設(shè)計(jì)的范式下，有時(shí)候數(shù)據(jù)不可以放在一個(gè)表中，我們要分成多個(gè)表，但是我們在查詢的時(shí)候可能是要獲取這多個(gè)表中的信息，因此我們就需要聯(lián)合這多個(gè)表進(jìn)行查詢

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

如果我們用多表查詢，則會(huì)出現(xiàn)下面這種情況

select * from student,class;

這兩個(gè)表的聯(lián)合查詢，其實(shí)是笛卡爾積，因此其查詢的列數(shù)就是兩個(gè)表的列數(shù)之和，行數(shù)就是其兩個(gè)表列數(shù)之積

如果表內(nèi)容多，其數(shù)據(jù)就會(huì)變成非常龐大，因此上面這樣我們聯(lián)合查詢是不對的，并且其班級id要一樣才是正確的結(jié)果，剩余都是錯(cuò)誤的結(jié)果

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

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

此時(shí)查詢結(jié)果就合理了

由于使用select * 查詢比較耗時(shí)，因此我們此時(shí)也可以指定列，但是我們的形式是 表名.列名，因?yàn)閮蓚€(gè)表中可能有相同的列名，因此要加上列名

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

--查詢其對應(yīng)列的信息
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 = '張三';

如果我們不添加其對應(yīng)的表，并且還是相同的列名，此時(shí)就會(huì)出現(xiàn)此時(shí)的id對應(yīng)的列是摸棱兩可的，就是我們不知道這個(gè)列是來自那個(gè)表，因此就會(huì)出錯(cuò)

聯(lián)合查詢步驟

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

內(nèi)連接

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

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

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

--創(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)
);
--學(xué)生表
create table student(
       id int primary key auto_increment,
			 name varchar(20),
			 class_id int,
			 foreign key(class_id) references class(id)
);
--分?jǐn)?shù)表
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'), ('操作系統(tǒng)'), ('計(jì)
算機(jī)網(wǎng)絡(luò)'), ('數(shù)據(jù)結(jié)構(gòu)');
# 班級表
insert into class(name) values ('Java001班'), ('C++001班'), ('前端001班');
# 學(xué)?表
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);

查詢唐三藏的所有學(xué)科成績

-- 我們用上面兩種方式都可以
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 = '唐三藏';

例如查詢所有學(xué)生的總成績

select s.name, sum(sc.score) from student as s,score as sc
      where s.id = sc.student_id
      group by (s.id);-- 要進(jìn)行分組，因?yàn)槠淝蠛涂隙ㄒ且粋€(gè)學(xué)生的成績

查詢所有同學(xué)的詳細(xì)信息包括考試成績

select
	s.id,
	s.name as 姓名,
	c.name as 課程,
	sc.score as 分?jǐn)?shù)
from
	course c,
	student s,
	score sc 
where
	s.id = sc.student_id 
	and c.id = sc.course_id 
order by
	s.id;

外連接

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

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

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

全外連接：結(jié)合了左外連接和右外連接的特點(diǎn)，返回左右表中的所有記錄。如果某?邊表中沒有匹配的記錄，則結(jié)果集中對應(yīng)字段會(huì)顯?為NULL

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

左外連接

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

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

# 因?yàn)槲纯荚嚨某煽優(yōu)榭?，因此其可以篩選掉考試的同學(xué)
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;

右外連接

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

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

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

自連接

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

select * from score,score;

此時(shí)會(huì)報(bào)錯(cuò)，因?yàn)?strong>使用兩個(gè)名字相同的表或者是沒有取別名

select * from score as s1,score as s2;

查詢MySQL成績大于java成績大的學(xué)生

分開寫
# 先獲取起對應(yīng)課程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 #學(xué)生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;

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

	# 顯示其所有信息
	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

子查詢

有時(shí)候我們進(jìn)行查詢時(shí)候的where條件可能還需要進(jìn)行查詢，就是一個(gè)查詢的結(jié)果作為另一個(gè)查詢的條件

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

單行子查詢

where條件里的查詢只返回一行數(shù)據(jù)

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

因此要先查找其班級id，后面再根據(jù)其班級id進(jìn)行查找

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

當(dāng)然這個(gè)我們還是可以分開寫的

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

多列子查詢

返回多行數(shù)據(jù)，因此我們要是用[not ] in 關(guān)鍵字

查詢課程MySQL和java的成績

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

當(dāng)然也可以查詢除了MySQL和java的成績

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

多列子查詢

多列子查詢就是返回的是多個(gè)列的數(shù)據(jù)

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

insert into score(score, student_id, course_id) 
 values(70.5, 1, 1),(98.5, 1, 3),(60, 2, 1);
 # 查詢重復(fù)成績
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 語句中使用子查詢

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

例如：查詢所有比‘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是臨時(shí)表

合并查詢

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

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

union

用于兩個(gè)結(jié)果的并集，并且可以去掉重復(fù)行

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

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

此時(shí)會(huì)根據(jù)要求將兩個(gè)select結(jié)果合并，并且去掉了重復(fù)部分

Union all

也是用于合并結(jié)果集，此操作符是不可以去掉結(jié)果的重復(fù)行

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

此時(shí)就可能會(huì)有重復(fù)的行

插入查詢結(jié)果

可以將一個(gè)查詢結(jié)果當(dāng)作數(shù)據(jù)插入一個(gè)表中

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

例如：將student表中C++001班的學(xué)?復(fù)制到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班';

總結(jié)

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

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL聯(lián)合查詢實(shí)例代碼(非常全)

目錄

使用聯(lián)合查詢的原因

聯(lián)合查詢步驟

內(nèi)連接

外連接

左外連接

右外連接

自連接

子查詢

單行子查詢

多列子查詢

多列子查詢

from 語句中使用子查詢

合并查詢

union

Union all

插入查詢結(jié)果

總結(jié)

相關(guān)文章

最新評論

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

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

常用在線小工具