MySQL查詢語句總結(jié)之單表查詢和多表查詢
??1. 單表查詢
??1.1 全列查詢和指定列查詢
全列查詢：
select * from exam;
在實(shí)際開發(fā)中不要使用 * 來進(jìn)行查詢，因為數(shù)據(jù)庫會很大，影響效率
指定列查詢：
select id,name,math from exam;
查詢的結(jié)果是一個表達(dá)式，例如，在查詢命令中添加一個數(shù)值，每一列都會有這個數(shù)值
select id ,name ,10 from exam;
之后，可以通過as關(guān)鍵字來為查詢結(jié)果中的列指定別名，as和引號可以省略，但如果別名中存在空格就不能省略引號了
select id as '編號' ,name as '姓名' from exam;
列與列之間還可以進(jìn)行運(yùn)算：
select chinese + math + english as '總分' from exam;
還可以直接加上指定的數(shù)字
select math + 10 from exam;
??1.2 去重查詢
去重查詢是通過關(guān)鍵字 distinct 來實(shí)現(xiàn)的，在之前的math中，是存在一個重復(fù)的98的，我們來試驗一下去重查詢操作：
select distinct math from exam;
有一點(diǎn)需要注意的是，在查詢結(jié)果中，每一列都相同才認(rèn)為是重復(fù)數(shù)據(jù)，剛剛只查詢的是math這一列，這次加上id試試：
可以看到，這一次重復(fù)的98并沒有被去掉，因為id不同
??1.3 排序查詢
對于多字段，按照字段的前后順序，如果第一個字段相同，按照第二個字段進(jìn)行排序
select math from exam order by math desc ;
如果數(shù)據(jù)為 null 就認(rèn)為是最小的，升序排最前面，降序排最后面
還可以加上之前的表達(dá)式和別名進(jìn)行排序
select id ,name, math + chinese + english as '總分' from exam order by math + chinese + english desc;
前面已經(jīng)定義了別名，所以后面可以直接用
select id ,name, math + chinese + english as '總分' from exam order by 總分 desc;
關(guān)于null的特殊情況：
1.不論和任何值相加，結(jié)果都是null
2.始終被看作false
3.null 并不等同于 0
所以說，如果想上面的按照總分排序，如果其中一門為Null,那么他的總分就是null
最后還可以通過多個字段進(jìn)行排序，排序的優(yōu)先級就按照書寫的順序進(jìn)行排列
例如：先按數(shù)學(xué)降序排列，再按語文升序排列，再按英語順序排列
select id, name, math, chinese,english from exam order by math desc, chinese asc, english asc;
??1.4 條件查詢
??1.4.1 比較運(yùn)算符
先來看比較運(yùn)算符
前面的大于等于這些符號和java中一樣，就不多說了，而java中相等是用 "==" 表示的，mysql中是 "=" ，判斷不等于的這兩個寫法都可以
這里有一些小細(xì)節(jié)需要注意：
-- 查詢英語不及格的 select name ,english from exam where english < 60;
如果表中有null，不參與篩選，所以最終的結(jié)果不會包括null
-- 查詢英語比語文好的 select * from exam where english > chinese;
在上面的比較方式中，同一行的數(shù)據(jù)是可以比較的，但是不能跨行比較
select name, english + math + chinese as total from exam where english + math + chinese < 250 order by total desc ;
如果where 后面使用了表達(dá)式的話要寫完整的表達(dá)式，不能寫別名
原因：和SQL的執(zhí)行順序有關(guān)
1.如果要在數(shù)據(jù)庫中查找某些數(shù)據(jù)，首先要確定表，先執(zhí)行from
2.在查詢過程中，要根據(jù)指定的查詢條件把符合條件的數(shù)據(jù)過濾出來，這時執(zhí)行的是where字句
3.執(zhí)行select后面的指定的列，這些列最終要加到結(jié)果的展示集里
4.根據(jù)order by 子句的列名和排序規(guī)則進(jìn)行最后的排序
根據(jù)以上順序可以推斷出，在執(zhí)行where語句時，還沒到select里的total，所以用不了
| <=> | 表示等于，是專門針對null判斷的，例如null <=> null 的結(jié)果為1 |
null的判斷是不能用 "=" 判斷的
接下來的 between...and...和 in (...) 都是字面意思，分別用來判斷是否在一個區(qū)間和是否在括號中的列表中
select 1 in (1,2,3); select 2 between 1 and 4;
而like表示模糊匹配的意思是，按照單個字符和任意個字符進(jìn)行匹配，來看一個例子：
當(dāng)用 '%' 表示的是找出姓張的人，任意字符也就是張后面可以跟任意個字符，'_' 表示單個字符，也就是張后面只能跟一個字符
最后還有一個判斷是否為null的命令
??1.4.2 邏輯運(yùn)算符
接下來看邏輯運(yùn)算符
| 運(yùn)算符 | 說明 | 對應(yīng)Java中的邏輯運(yùn)算符 |
| AND | 多個條件必須都為 TRUE(1)，結(jié)果才是 TRUE(1) | && |
| OR | 任意一個條件為 TRUE(1), 結(jié)果為 TRUE(1) | || |
| NOT | 條件為 TRUE(1)，結(jié)果為 FALSE(0) | ! |
-- 語文大于80或英語大于80 select name, english, chinese from exam where english > 80 or chinese > 80;
or 的話是任意一個條件為true 就符合，哪怕另一個條件為null不參與比較
-- 語文英語都大于80分 select name, english, chinese from exam where english > 80 and chinese > 80;
下面來分析一下and 和 or 的優(yōu)先級：
-- 比較and 和 or 的運(yùn)算優(yōu)先級 select name, chinese, math, english from exam where chinese > 80 or math > 70 and english > 70; select name, chinese, math, english from exam where (chinese > 80 or math > 70) and english > 70
通過對比發(fā)現(xiàn)，and 和 or 和 java 中的優(yōu)先級是一樣的，都是and > or ，不過還是建議根據(jù)需求加括號
??1.5 分頁查詢
在實(shí)現(xiàn)已經(jīng)提到過，如果直接通過select * from不加限制來查詢?nèi)康臄?shù)據(jù)是不安全的，通過分頁查詢可以有效的控制一次查詢出來的結(jié)果集中的記錄條數(shù)，可以有效的減少數(shù)據(jù)庫服務(wù)器的壓力，也有利于用戶查看，例如我們經(jīng)常見到的這種就是用到了分頁查詢
例如從第0條開始，往后讀取2條數(shù)據(jù)有一下這幾種寫法：
-- 從第0條開始往后讀取2條數(shù)據(jù) select * from exam order by id limit 2; select * from exam order by id limit 0,2; select * from exam order by id limit 2 offset 0;
可以通過下面這個公式來計算第 s 頁所需要的偏移量 n:
n = (s - 1) * 每頁顯示的記錄數(shù)
如果說指定的起始位置超出了整個表的范圍就會返回一個空的結(jié)果集
??1.6 分組查詢
where 和 having的區(qū)別：
執(zhí)行時機(jī)不同：where 是分組前進(jìn)行過濾，不滿足where條件不進(jìn)行分組，having是對分組后的結(jié)果進(jìn)行過濾
判斷條件不同：where不能對聚合函數(shù)進(jìn)行判斷，而having可以
-- 根據(jù)角色進(jìn)行分組，顯示出角色和平均薪資,使用round四舍五入 select role, round(avg(salary), 2) from emp group by role;
-- 講分組后的數(shù)據(jù)根據(jù)薪資進(jìn)行升序排列 select role, round(avg(salary), 2) as 平均薪資 from emp group by role order by 平均薪資 ASC;
通過having 對分組后的數(shù)據(jù)進(jìn)行過濾：
-- 對分組后的數(shù)據(jù)進(jìn)行過濾 select role, round(avg(salary), 2) as 平均薪資 from emp group by role having 平均薪資 > 10000 and 平均薪資 < 100000;
綜合小練習(xí)：
查詢平均薪資低于10000的角色和平均薪資
-- 顯示平均薪資低于10000的角色和其平均薪資 select role ,avg(salary) as 平均薪資 from emp group by role having 平均薪資 < 10000;
??2. 多表查詢
聯(lián)合查詢就是聯(lián)合多個表進(jìn)行查詢，為了消除表中字段的依賴關(guān)系，設(shè)計數(shù)據(jù)時把表進(jìn)行拆分，這時就會導(dǎo)致一條SQL語句查找出來的數(shù)據(jù)不夠完整，就可以通過聯(lián)合查詢把關(guān)系中的數(shù)據(jù)全部查出來，在一個數(shù)據(jù)行中顯示詳細(xì)信息
步驟：
1.首先確定哪幾張表要參與查詢
2.根據(jù)表于表之間的主外鍵關(guān)系，確定過濾條件
3.精簡查詢字段
例如上面的兩個表，通過聯(lián)合查詢獲取下面的表的這種信息：
??2.1 內(nèi)連接
1. 取多張表的笛卡爾集
分別創(chuàng)建上面的class，student表，并添加數(shù)據(jù)：
create table class
(
id bigint primary key auto_increment,
name varchar(20)
);
create table student
(
id bigint primary key auto_increment,
name varchar(20),
gender varchar(1),
class_id bigint,
foreign key (class_id) references class (id)
);
insert into class(id, name)
values (1, 'Java01'),
(2, 'Java02');
insert into student(id, name, gender, class_id)
values (1, '張三', '男', 1),
(2, '李四', '男', 2),
(3, '王五', '女', 2),
(4, '趙六', '男', 1);通過下面的這個命令可以查詢到兩張表取笛卡爾集后的數(shù)據(jù)：
select * from student,class;
但是會發(fā)現(xiàn)，有四條數(shù)據(jù)是不匹配的
2.通過連接條件過濾掉無效的數(shù)據(jù)
由于這兩個表是存在主外鍵關(guān)系的，只需要判斷主外鍵字段是否相等
-- 通過連接條件過濾掉無效的數(shù)據(jù)
select *
from student,
class
where student.class_id = class.id;
這樣，得到的就都是正確的數(shù)據(jù)了
3.通過指定列查詢精簡結(jié)果集
-- 指定列查詢精簡結(jié)果集
select student.id, student.name, class.name
from student,
class
where student.class_id = class.id;通過指定列查詢可以使結(jié)果更加精簡，這也就是內(nèi)連接的第一種寫法
還可以加入別名的方式進(jìn)行簡化SQL語句
-- 別名方式簡化
select s.id, s.name, c.name
from student s,
class c
where s.class_id = c.id;第二種寫法：
通過關(guān)鍵字 inner join 來實(shí)現(xiàn)的，from 表1 inner join 表2 意思是從表1到表2建立內(nèi)連接，關(guān)注點(diǎn)是表1，從表1里面找表2的連接
-- 寫法2
select s.id, s.name, c.name
from student s
inner join class c on class_id = c.id;inner 也可以省略
-- inner可以省略
select s.id, s.name, c.name
from student s
join class c on class_id = c.id;??2.1.1小練習(xí)
創(chuàng)建的表的部分內(nèi)容如下
找出許仙的成績：
首先確定要聯(lián)合的表，這里聯(lián)合student和score表，然后對目標(biāo)表去笛卡爾集，再通過連接條件進(jìn)行過濾，再精確字段
select *
from student,
score
where student.student_id = score.student_id
and name = '許仙';
此時發(fā)現(xiàn)顯示的字段有點(diǎn)多，再根據(jù)要求查詢指定字段
-- 指定字段
select name, score
from student,
score
where student.student_id = score.student_id
and name = '許仙';
查詢每位同學(xué)的總成績和學(xué)生信息：
和前面步驟一樣
select student.student_id, sum(score.score)
from student,
score
where student.student_id = score.student_id;
之后，由于求的是每個同學(xué)的總分，所以還需要按照student_id進(jìn)行分組，再精簡字段
select student.student_id,student.name, sum(score.score)
from student,
score
where student.student_id = score.student_id
group by student.student_id;
查詢每位同學(xué)每一門的成績：
還是按照之前的步驟進(jìn)行，只不過這次需要用到三張表
select student.student_id,student.name,course.name,score.score
from student,
score,
course
where student.student_id = score.student_id
and score.course_id = course.course_id;使用 [inner] join on 的形式：
select st.student_id, st.name, c.name, sc.score
from student st
inner
join score sc
on st.student_id = sc.student_id
join course c
on c.course_id = sc.course_id;??2.2 外連接
外連接又分為左外連接和右外連接
內(nèi)連接和外連接的區(qū)別：
內(nèi)連接只會查詢到兩個表的交集部分，外連接可以查詢左邊或右邊整個表
??2.2.1右外連接
右鏈接就是以 join 右邊的表為基準(zhǔn)，顯示這個表的全部數(shù)據(jù)，左邊的表如果沒有匹配的記錄的話會以null作為補(bǔ)充
-- 使用右外連接
select student.student_id, student.name, class.class_id, class.name
from student
right join class on student.class_id = class.class_id;這里從 student 表到 class 表建立右外連接，沒有學(xué)生的班級id也會顯示出來，用null代替
右邊class表中的數(shù)據(jù)都顯示出來了，左邊沒有與之對應(yīng)的行用null補(bǔ)充
??2.2.2 左外連接
需求：查詢哪位同學(xué)沒有參加考試
也就是在student表里有記錄，在score表里沒有對應(yīng)的記錄
這時就可以使用左連接，把student表作為基準(zhǔn)表
-- 左外連接
select student.student_id, student.name, score
from student
left join score on student.student_id = score.student_id;
??2.3 自連接
自連接可以實(shí)現(xiàn)行與行之間的比較功能
需求：找出計算機(jī)原理課程成績大于Java的
還是和之前的步驟一樣，只不過這里由于是自連接，取笛卡爾集的時候需要確定別名
-- 找出計算機(jī)原理的成績大于Java的
-- 取笛卡爾集
use test3;
select *
from score s1,
score s2
where s1.student_id = s2.student_id;
-- 確定過濾條件
select *
from score s1,
score s2
where s1.student_id = s2.student_id
and s1.course_id = 3 and s2.course_id = 1
and s1.score > s2.score;自連接查詢可以是內(nèi)連接，也可以是外連接，在下面這張員工表中
| id | name | job | manager_id |
| 1 | 張三 | 總裁 | null |
| 2 | 李四 | 項目經(jīng)理 | 1 |
| 3 | 王五 | 工程師 | 2 |
來看內(nèi)連接的例子：
查詢員工及其所屬領(lǐng)導(dǎo)的名字
select a.name, b.name
from emp a,
emp b
where a.manager_id = b.id;查詢所有員工及其領(lǐng)導(dǎo)的名字（如果員工沒有領(lǐng)導(dǎo)也要表示出來）
select a.name, b.name
from emp a
left join emp b on a.manager_id = b.id;??2.4 子查詢
SQL語句中嵌套select語句稱為嵌套查詢，又叫子查詢
根據(jù)查詢的結(jié)果又可以分為：標(biāo)量子查詢（查詢結(jié)果為單個值），列子查詢（查詢結(jié)果為一列），行子查詢（查詢結(jié)果為一行），表子查詢（查詢結(jié)果為多行多列）
??2.4.1 單行子查詢
需求：查詢與許仙一個班級的同學(xué)
如果不適用子查詢的話，就需要用多條SQL語句來查詢
-- 查找和許仙一個班級的同學(xué) select class_id from student where name = '許仙'; -- 得出許仙的 class_id 是 1 select name from student where class_id = 1;
下面來看子查詢的形式
select * from student where class_id = (select class_id from student where name = '許仙') -- 也可以對子查詢的整體加上再加上過濾條件 and name != '許仙';
要注意的是外層條件的列，與同層查詢條件的列必須要匹配
??2.4.2 多行子查詢
需求：獲取語文和英文的成績信息
如果不用子查詢，還是需要兩步進(jìn)行，獲取課程的 id 之后，再根據(jù) id 去查找成績信息
-- 獲取課程 id select course_id from course where name = '語文' or name = '英文'; select * from score where course_id = 4 or course_id = 6;
由于這里查詢到的是多行的信息，所以在進(jìn)行子查詢的時候需要使用 IN（） 來判斷
select *
from score
where course_id in (select course_id
from course
where name = '語文'
or name = '英文');??2.4.3 多列子查詢
需求：查詢重復(fù)的分?jǐn)?shù)
按照同一個學(xué)生，同一門課程，同樣的成績這三個列同時去分組，然后分組之后在 having字句中用count(*)判斷分組中的記錄數(shù)
select student_id, course_id, score from score group by student_id, course_id, score; -- 加上having過濾條件 select student_id, course_id, score,count(*) from score group by student_id, course_id, score having count(*) > 1;
多列分組查詢
select *
from score
where (student_id, course_id, score) in (select student_id, course_id, score
from score
group by student_id, course_id, score
having count(*) > 1);外層查詢中的條件字段和內(nèi)層查詢中的結(jié)果對比，完全符合條件才可以
??2.5 EXISTS關(guān)鍵字
語法：select * from 表名 where exists (select * from 表名)；
exists 后面括號中的查詢語句，如果有結(jié)果返回就執(zhí)行外層查詢，如果返回的是一個空結(jié)果集，就不執(zhí)行外層的查詢
select * from course where exists(select * from course where course_id = 100);
select * from course where exists(select null);
對于上面的SQL語句，雖然exists中返回的是 null ，但不是空結(jié)果集，所以還會執(zhí)行外層查詢
??2.6 臨時表查詢
在 from 子句中使用子查詢，就是把一個子查詢當(dāng)做一個臨時表來使用，下面還是通過一個例子來介紹
需求：查詢所有比"中文系2019級3班"平均分高的成績信息
首先獲取 '' 中文系2019級3班"的平均分，將其看做臨時表
-- 獲取平均分作為臨時表
select avg(sc.score)
from score sc,
student st,
class c
where c.class_id = st.class_id
and st.student_id = sc.student_id
and c.name = '中文系2019級3班';接下來用表中的真實(shí)成績和臨時表比較
-- 用表中的真實(shí)成績和臨時表比較
select *from score sc,
(select avg(sc.score) score -- 這里要起別名
from score sc,
student st,
class c
where c.class_id = st.class_id
and st.student_id = sc.student_id
and c.name = '中文系2019級3班') tmp
where sc.score > tmp.score;
??3. 合并查詢
合并查詢就是合并多個查詢結(jié)果到一個結(jié)果集中
需求：查找score_id < 3 或者 score > 90的同學(xué)
-- 查找score_id < 3 或者 score > 90的同學(xué) use test3; select * from score where score_id < 3; select * from score where score > 90; -- 兩次查詢結(jié)果聯(lián)合起來 select * from score where score_id < 3 union select * from score where score > 90;
在單表中用 or 更加簡潔，在多表中，就沒有辦法用 or ，如果最終的查詢結(jié)果是從多個表中獲取到的，必須要用 union 來進(jìn)行合并
下面來看兩張表的
-- 復(fù)制表結(jié)構(gòu)
create table student2 like student;
insert into student2(student_id, sn, name, mail, class_id)
values (1, 2022, '張三', null, 2),
(2, 2023, '李四', null, 1);
select * from student;
select * from student2;
-- 兩張表數(shù)據(jù)在一個數(shù)據(jù)表中顯示
select * from student union
select * from student2; 
需要注意的是，合并顯示的兩張表的列名要匹配一致
union 和 union all 的區(qū)別：
union 會自動去除合并結(jié)果中的重復(fù)行
union all 則會保留所有結(jié)果集中的所有行，包括重復(fù)的行
到此這篇關(guān)于MySQL查詢語句總結(jié)之單表查詢和多表查詢的文章就介紹到這了,更多相關(guān)mysql單表查詢和多表查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
MySQL數(shù)據(jù)庫如何給表設(shè)置約束詳解
約束主要完成對數(shù)據(jù)的檢驗，保證數(shù)據(jù)庫數(shù)據(jù)的完整性；如果有相互依賴數(shù)據(jù)，保證該數(shù)據(jù)不被刪除，本篇文章教你如何給表設(shè)置約束2022-03-03
MySQL 是最流行的數(shù)據(jù)庫管理系統(tǒng) (DBMS) 之一,它輕量、開源且易于安裝和使用,因此對于那些剛開始學(xué)習(xí)和使用關(guān)系數(shù)據(jù)庫的人來說是一個不錯的選擇, 本文主要系統(tǒng)介紹Windows的環(huán)境下MySQL的安裝過程和驗證過程,需要的朋友可以參考下2024-12-12
這篇文章主要介紹了如何干涉MySQL優(yōu)化器使用hash?join,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-09-09
這篇文章主要介紹了Mysql MVCC機(jī)制原理詳解，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下2021-04-04
SELECT語句是SQL中最常用的語句。它的用途是從一個或多個表中檢索信息。為了使用SELECT檢索表數(shù)據(jù)，必須至少給出兩條信息：想選擇什么、從什么地方選擇2023-02-02
MySQL8.0 GA版本發(fā)布了，展現(xiàn)了眾多新特性，下面這篇文章主要給大家介紹了關(guān)于MySQL8新特性：自增主鍵的持久化的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，需要的朋友可以參考借鑒，下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-07-07
這篇文章主要介紹了MySQL數(shù)據(jù)表使用的SQL語句整理，文章基于MySQL的相關(guān)資料展開舉例說明，具有一定的參考價值，需要的小伙伴可以參考一下2022-05-05
mysql 從 frm 文件恢復(fù) table 表結(jié)構(gòu)的3種方法【推薦】
這篇文章主要介紹了mysql 從 frm 文件恢復(fù) table 表結(jié)構(gòu)的3種方法 ,非常不錯，具有一定的參考借鑒價值，需要的朋友可以參考下2018-09-09
mysql5.7使用binlog 恢復(fù)數(shù)據(jù)的方法
MySQL的binlog日志是MySQL日志中非常重要的一種日志,記錄了數(shù)據(jù)庫所有的DML操作，那么怎樣通過binlog 恢復(fù)數(shù)據(jù)，本文就詳細(xì)的來介紹一下2021-06-06
這篇文章主要介紹了mysql數(shù)據(jù)庫保存路徑查找方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教方法2023-05-05