MySQL聯(lián)合查詢實例代碼(非常全)
使用聯(lián)合查詢的原因
在數(shù)據(jù)庫設計的范式下，有時候數(shù)據(jù)不可以放在一個表中，我們要分成多個表，但是我們在查詢的時候可能是要獲取這多個表中的信息，因此我們就需要聯(liá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);
如果我們用多表查詢，則會出現(xiàn)下面這種情況
select * from student,class;
這兩個表的聯(lián)合查詢，其實是笛卡爾積，因此其查詢的列數(shù)就是兩個表的列數(shù)之和，行數(shù)就是其兩個表列數(shù)之積
如果表內容多，其數(shù)據(jù)就會變成非常龐大，因此上面這樣我們聯(lián)合查詢是不對的，并且其班級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 = '張三';
如果我們不添加其對應的表，并且還是相同的列名，此時就會出現(xiàn)此時的id對應的列是摸棱兩可的，就是我們不知道這個列是來自那個表，因此就會出錯
聯(lián)合查詢步驟
1.確定查詢表 -->進行笛卡爾積
2.確定連接條件
3.加入查詢條件
4.精簡查詢列，也可以給表取別名
內連接
上面我們只是簡單的介紹了可以聯(lián)合查詢，下面我們就來詳細介紹一些聯(lián)合查詢的方式
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)
);
--分數(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)'), ('計
算機網(wǎng)絡'), ('數(shù)據(jù)結構');
# 班級表
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 分數(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是不支持全外連接
左外連接：返回左表中的所有記錄和右表匹配的記錄，如果右表沒有匹配到，結果集會顯示空
右外連接：和左外連接相反，返回右表中的所有記錄和左表匹配的記錄，如果左表沒有匹配到，結果集會顯示空
全外連接：結合了左外連接和右外連接的特點，返回左右表中的所有記錄。如果某?邊表中沒有匹配的記錄，則結果集中對應字段會顯?為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條件對結果進行過濾，或者可以使用自己對自己表的數(shù)據(jù)進行比較，但是自連接的時候要對表起別名
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條件里的查詢只返回一行數(shù)據(jù)
例如查詢姓名為 '不想畢業(yè)’的同學
因此要先查找其班級id，后面再根據(jù)其班級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;
多列子查詢
返回多行數(shù)據(jù)，因此我們要是用[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');
多列子查詢
多列子查詢就是返回的是多個列的數(shù)據(jù)
例如：查詢重復錄入的分數(shù)
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;
# 插入一些數(shù)據(jù)
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;
此時會根據(jù)要求將兩個select結果合并，并且去掉了重復部分
Union all
也是用于合并結果集，此操作符是不可以去掉結果的重復行
select * from student where id < 3
union all select * from student1;
此時就可能會有重復的行
插入查詢結果
可以將一個查詢結果當作數(shù)據(jù)插入一個表中
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聯(lián)合查詢實的文章就介紹到這了,更多相關MySQL聯(lián)合查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了MySQL去重的方法整理的相關資料,需要的朋友可以參考下2017-07-07
MySQL中的text和longtext類型是常用的字符串類型,可以儲存大量文本數(shù)據(jù),這篇文章主要介紹了MySQL中TEXT和LONGTEXT區(qū)別的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2025-08-08
Linux下二進制方式安裝mysql5.7版本和系統(tǒng)優(yōu)化的步驟
這篇文章主要介紹了Linux下二進制方式安裝mysql5.7版本和系統(tǒng)優(yōu)化的步驟，本文給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下2020-01-01
深入理解MySQL雙字段分區(qū)(OVER(PARTITION BY A,B)
本文主要介紹了MySQL中的窗口函數(shù)雙字段分區(qū)功能(OVER(PARTITION BY A,B),分析其在數(shù)據(jù)分組和性能優(yōu)化中的應用,提高查詢效率,具有一定的參考價值,感興趣的可以了解一下2024-09-09
這篇文章主要介紹了Mysql存儲過程和函數(shù)的區(qū)別,需要的朋友可以參考下2014-03-03
為 id 列選擇一個好的數(shù)據(jù)類型非常重要，id 列會經(jīng)常用于做比較（例如聯(lián)合查詢的條件），以及用于查找其他列。而且，id 也經(jīng)常用于外鍵。因此，id 列的數(shù)據(jù)類型不僅僅關系自身數(shù)據(jù)表，也關系到與之關聯(lián)的其他數(shù)據(jù)表。因此，id 用何種數(shù)據(jù)類型就顯得十分重要2021-06-06
這篇文章主要介紹了mysql存儲過程實例,需要的朋友可以參考下2014-04-04
這篇文章主要介紹了MySQL存儲過程及常用函數(shù)代碼解析,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2020-08-08
這篇文章主要介紹了mysql、oracle默認事務隔離級別的說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
Ubuntu下完美實現(xiàn)遷移MySQL數(shù)據(jù)庫位置
這篇文章主要介紹了Ubuntu下完美實現(xiàn)遷移MySQL數(shù)據(jù)庫位置,十分詳細，有需要的小伙伴可以參考下2015-03-03