MySQL聯(lián)合查詢?cè)敿?xì)示例代碼
1. 聯(lián)合查詢的由來(lái)
因?yàn)槲覀冊(cè)趧?chuàng)建數(shù)據(jù)表時(shí)候遵循范式的規(guī)則，將數(shù)據(jù)拆分成多個(gè)表，而我們想要查詢表的所有屬性列，就需要將幾張表的數(shù)據(jù)結(jié)合起來(lái)顯示，因此就產(chǎn)生了聯(lián)合查詢。比如：學(xué)生表和課程表，這里我們想要在一張表中顯示學(xué)生表和課程表的信息，就需要用到聯(lián)合查詢。
2. 笛卡爾積
2.1 理論介紹
數(shù)據(jù)庫(kù)中笛卡爾積指的是兩個(gè)表之間的運(yùn)算，我們可以將兩個(gè)表進(jìn)行笛卡爾積就能得到兩個(gè)表的完整信息。
對(duì)下面這兩個(gè)表進(jìn)行笛卡爾積的運(yùn)算：
得到的結(jié)果表是：
兩個(gè)表通過(guò)笛卡爾積得到的 表的行數(shù)是兩個(gè)表行數(shù)的乘積，表的列數(shù)是兩個(gè)表列數(shù)的和。
我們觀察新得到的表會(huì)發(fā)現(xiàn)這張表里面有很多無(wú)效數(shù)據(jù)，也就是張三應(yīng)該是一班的，所以第二行是無(wú)效數(shù)據(jù)，那我們會(huì)發(fā)現(xiàn)無(wú)效數(shù)據(jù)是兩個(gè)班級(jí)id不相同的數(shù)據(jù)，那我們?cè)诓樵兊臅r(shí)候可以添加一個(gè)where條件。
如何區(qū)分兩個(gè)班級(jí)id呢？
我們可以這樣表示 student.id 和 class.id。通過(guò)成員訪問(wèn)運(yùn)算符 . 來(lái)實(shí)現(xiàn)。
2.2 SQL語(yǔ)句編寫(xiě)
下面是聯(lián)合查詢的代碼實(shí)現(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)合查詢的流程
- 我們先對(duì)兩個(gè)表進(jìn)行笛卡爾積。
- 接著添加連接條件。
- 然后根據(jù)需求添加其他條件。
- 最后針對(duì)列進(jìn)行篩選/計(jì)算表達(dá)式/聚合查詢等操作。
下面我舉幾個(gè)例子，幫助理解聯(lián)合查詢的流程：
首先我們先準(zhǔn)備幾張表進(jìn)行查詢，下面有學(xué)生表，班級(jí)表，成績(jī)表，課程表四張表：
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ì)算機(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é)生個(gè)人信息和班級(jí)信息。
1. 我們確定要查詢的表來(lái)自那幾張表，進(jìn)行笛卡爾積運(yùn)算。
學(xué)生表，班級(jí)表
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ù)需求來(lái)查找對(duì)應(yīng)的列。
select student.name, student.sno, student.age, student.gender, student.enroll_date, class.name as '班級(jí)名稱' from student,class where student.class_id = class.class_id and student.name = '孫悟空';
2.4 內(nèi)連接
內(nèi)連接相當(dāng)于在原來(lái)的聯(lián)合查詢的語(yǔ)句上進(jìn)行修改，這里用到 join on兩個(gè)關(guān)鍵字。
原來(lái)查詢是：
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;
這兩個(gè)SQL語(yǔ)句查詢出來(lái)的內(nèi)容都是一樣的。
我們可以將上面的聯(lián)合查詢語(yǔ)句改成：
#內(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.對(duì)列進(jìn)行精簡(jiǎ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é)的總成績(jī)和同學(xué)的個(gè)人信息。
確認(rèn)從學(xué)生表和成績(jī)表中查找。
# 笛卡爾積 select * from student join score; # 添加連接條件 select * from student join score on student.student_id = score.student_id; # 沒(méi)有條件添加 #對(duì)列進(jìn)行精簡(jiǎ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é)每門(mén)課的成績(jī)，及同學(xué)的個(gè)人信息。
確認(rèn)從學(xué)生表，成績(jī)表 和課程表中查找：
# 笛卡爾積 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; #沒(méi)有條件進(jìn)行添加 #對(duì)列進(jìn)行精簡(jiǎ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ù)在右邊表中沒(méi)有匹配記錄，那么就會(huì)將對(duì)應(yīng)右邊記錄為null。
右外連接：如果右邊表對(duì)應(yīng)左邊表數(shù)據(jù)沒(méi)有匹配記錄，那么就會(huì)將對(duì)應(yīng)左邊記錄為null。
全外連接：左右兩張表互相存在不對(duì)應(yīng)的匹配數(shù)據(jù)，就會(huì)為null。
創(chuàng)建新的兩張表，學(xué)生表和成績(jī)表：
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 自連接
自連接是表自己對(duì)自己進(jìn)行連接，我們可以把行變換成列，而列跟列之間能進(jìn)行比較，所以相當(dāng)于間接實(shí)現(xiàn)了行與行進(jìn)行比較。我們?cè)诒磉B接時(shí)候，要為表起兩個(gè)不同的別名。
顯示所有"MySQL"成績(jī)比"JAVA"成績(jī)高的成績(jī)信息：
這里我們是用成績(jī)表進(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 子查詢
子查詢通常是把一個(gè)SQL語(yǔ)句的結(jié)果當(dāng)作另一個(gè)SQL語(yǔ)句的條件來(lái)進(jìn)行查詢的。
但是這種查詢方式比較難以閱讀，違背軟件開(kāi)發(fā)的核心原則（將大問(wèn)題轉(zhuǎn)換為多個(gè)小問(wèn)題），很少使用。
單行子查詢：
查詢與不想畢業(yè)的同學(xué)的同班同學(xué)：
select name from student where class_id = (select class_id from student where name = '不想畢業(yè)');
多行子查詢：
查詢mysql或Java課程的成績(jī)信息：
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 合并查詢
這里我們可以將多個(gè)查詢結(jié)果合并在一起進(jìn)行，使用union 或者union all。
這里我們需要?jiǎng)?chuàng)建一個(gè)新表來(lái)進(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會(huì)把兩個(gè)查詢的結(jié)果取并集，并且會(huì)自動(dòng)取出重復(fù)的行。
union all操作符：
查詢student表中id < 3 的同學(xué)和student1表中的所有同學(xué)：
select * from student where student_id < 3 union all select * from student1;
查詢結(jié)果：
union all查詢時(shí)候查詢兩個(gè)結(jié)果的并集，不會(huì)去除重復(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班';
這里后面的查詢出來(lái)的條件應(yīng)該與前面的條件類(lèi)型和數(shù)量對(duì)應(yīng)。
總結(jié)
到此這篇關(guān)于MySQL聯(lián)合查詢的文章就介紹到這了,更多相關(guān)MySQL聯(lián)合查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Mysql刪除重復(fù)數(shù)據(jù)保留最小的id 的解決方法
這篇文章主要介紹了Mysql刪除重復(fù)數(shù)據(jù)保留最小的id 的解決方法,需要的朋友可以參考下2017-10-10
這篇文章主要為大家詳細(xì)介紹了mysql 8.0.17 安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-08-08
mysql免安裝版1067錯(cuò)誤終極解決辦法圖文教程
這篇文章主要介紹了mysql免安裝版1067錯(cuò)誤終極解決辦法圖文教程，從筆記本到pc端。本文給大家介紹的非常詳細(xì)，需要的朋友參考下2017-01-01
MySQL數(shù)據(jù)類(lèi)型DECIMAL用法詳解
這篇文章主要介紹了MySQL數(shù)據(jù)類(lèi)型DECIMAL用法詳解，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
MySQL簡(jiǎn)單了解“order by”是怎么工作的
在MySQl中ORDER BY 語(yǔ)句用于對(duì)結(jié)果集進(jìn)行排序，那么它是怎么工作的，以及如何優(yōu)化，下面由小編跟大家講一講2019-05-05
這篇文章主要為大家分享了Win7下安裝MySQL5.7.16過(guò)程的筆記，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
備份是數(shù)據(jù)安全的最后一道防線，對(duì)于任何數(shù)據(jù)丟失的場(chǎng)景，備份雖然不一定能恢復(fù)百分之百的數(shù)據(jù)(取決于備份周期)，但至少能將損失降到最低2016-05-05
一次MySql重置root密碼無(wú)效的實(shí)戰(zhàn)記錄
最近碰見(jiàn)過(guò)幾次mysql?root密碼修改無(wú)效的情況,在此做一下總,下面這篇文章主要給大家介紹了一次MySql重置root密碼無(wú)效的實(shí)戰(zhàn)記錄,文中通過(guò)示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-04-04
這篇文章主要介紹了Mysql 8.0安裝及重置密碼問(wèn)題,非常不錯(cuò)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2018-11-11
MySQL InnoDB引擎ibdata文件損壞/刪除后使用frm和ibd文件恢復(fù)數(shù)據(jù)
mysql的ibdata文件被誤刪、被惡意修改,沒(méi)有從庫(kù)和備份數(shù)據(jù)的情況下的數(shù)據(jù)恢復(fù),不能保證數(shù)據(jù)庫(kù)所有表數(shù)據(jù)的100%恢復(fù),目的是盡可能多的恢復(fù),下面是具體的操作方法2025-03-03