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

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

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

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

在數(shù)據(jù)庫(kù)設(shè)計(jì)的范式下，有時(shí)候數(shù)據(jù)不可以放在一個(gè)表中，我們要分成多個(gè)表，但是我們?cè)诓樵?xún)的時(shí)候可能是要獲取這多個(gè)表中的信息，因此我們就需要聯(lián)合這多個(gè)表進(jìn)行查詢(xú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);

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

select * from student,class;

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

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

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

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

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

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

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

--查詢(xún)其對(duì)應(yīng)列的信息
select student.id,student.name,sex,student.class_id,class.id,class.name from student,class where student.class_id = class.id;
--查詢(xún)張三的信息
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 = '張三';

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

聯(lián)合查詢(xún)步驟

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

內(nèi)連接

上面我們只是簡(jiǎn)單的介紹了可以聯(lián)合查詢(xún)，下面我們就來(lái)詳細(xì)介紹一些聯(lián)合查詢(xú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è)表來(lái)舉例

--創(chuàng)建課程表
create table course(
       id int primary key auto_increment,
			 name varchar(20)
);
--班級(jí)表
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)');
# 班級(jí)表
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);
# 成績(jī)表
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);

查詢(xún)唐三藏的所有學(xué)科成績(jī)

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

例如查詢(xún)所有學(xué)生的總成績(jī)

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é)生的成績(jī)

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

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是不支持全外連接

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

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

全外連接：結(jié)合了左外連接和右外連接的特點(diǎn)，返回左右表中的所有記錄。如果某?邊表中沒(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é)生
這時(shí)候就可以讓student左外連接score，這樣就會(huì)把學(xué)生全部顯示出來(lái)
到時(shí)候如果成績(jī)?yōu)榭眨f(shuō)明其沒(méi)有參加考試

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

# 因?yàn)槲纯荚嚨某煽?jī)?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;

右外連接

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

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

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

自連接

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

select * from score,score;

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

select * from score as s1,score as s2;

查詢(xún)MySQL成績(jī)大于java成績(jī)大的學(xué)生

分開(kāi)寫(xiě)
# 先獲取起對(duì)應(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 成績(jī)>java

-- 放在一起寫(xiě)
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 班級(jí),
	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

子查詢(xún)

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

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

單行子查詢(xún)

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

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

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

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

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

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

多列子查詢(xún)

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

查詢(xún)課程MySQL和java的成績(jī)

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

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

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

多列子查詢(xún)

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

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

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

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

例如：查詢(xún)所有比‘java001班’平均分高的成績(jī)信息

# 先求平均分
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í)表

合并查詢(xún)

開(kāi)發(fā)過(guò)程中，為了合并多個(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ù)行

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

# 查詢(xún)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ù)的行

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

可以將一個(gè)查詢(xún)結(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)合查詢(xún)實(shí)的文章就介紹到這了,更多相關(guān)MySQL聯(lián)合查詢(xún)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

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

目錄

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

聯(lián)合查詢(xún)步驟

內(nèi)連接

外連接

左外連接

右外連接

自連接

子查詢(xún)

單行子查詢(xún)

多列子查詢(xún)

多列子查詢(xún)

from 語(yǔ)句中使用子查詢(xún)

合并查詢(xún)

union

Union all

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

總結(jié)

相關(guān)文章

最新評(píng)論

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

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

常用在線(xiàn)小工具