Mysql中單表和多表DQL查詢語句詳解
更新時間：2025年10月27日 10:22:16 作者：道之極萬物滅
這篇文章主要為大家詳細(xì)介紹了Mysql中單表和多表DQL查詢語句的相關(guān)知識,文中的示例代碼講解詳細(xì),感興趣的小伙伴可以跟隨小編一起學(xué)習(xí)一下
多表DQL查詢語句
一、多表關(guān)系
1、一對多
- 案例: 部門 與 員工的關(guān)系
- 關(guān)系: 一個部門對應(yīng)多個員工，一個員工對應(yīng)一個部門
- 實(shí)現(xiàn): 在多的一方建立外鍵，指向一的一方的主鍵
2、多對多
- 案例: 學(xué)生 與 課程的關(guān)系
- 關(guān)系: 一個學(xué)生可以選修多門課程，一門課程也可以供多個學(xué)生選擇
- 實(shí)現(xiàn): 建立第三張中間表，中間表至少包含兩個外鍵，分別關(guān)聯(lián)兩方主鍵
3、一對一
- 案例: 用戶 與 用戶詳情的關(guān)系
- 關(guān)系: 一對一關(guān)系，多用于單表拆分，將一張表的基礎(chǔ)字段放在一張表中，其他詳情字段放在另一張表中，以提升操作效率
- 實(shí)現(xiàn): 在任意一方加入外鍵，關(guān)聯(lián)另外一方的主鍵，并且設(shè)置外鍵為唯一的(UNIQUE)
二、數(shù)據(jù)準(zhǔn)備
-- 創(chuàng)建dept表，并插入數(shù)據(jù)
create table dept
(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '部門名稱'
) comment '部門表';
INSERT INTO dept (id, name)
VALUES (1, '研發(fā)部'),
(2, '市場部'),
(3, '財務(wù)部'),
(4,
'銷售部'),
(5, '總經(jīng)辦'),
(6, '人事部');
-- 創(chuàng)建emp表，并插入數(shù)據(jù)
create table emp
(
id int auto_increment comment 'ID' primary key,
name varchar(50) not null comment '姓名',
age int comment '年齡',
job varchar(20) comment '職位',
salary int comment '薪資',
entrydate date comment '入職時間',
managerid int comment '直屬領(lǐng)導(dǎo)ID',
dept_id int comment '部門ID',
constraint fk_emp_dept_id foreign key (dept_id) references dept (id)
) comment '員工表';
INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id)
VALUES (1, '金庸', 66, '總裁', 20000, '2000-01-01', null, 5),
(2, '張無忌', 20, '項目經(jīng)理', 12500, '2005-12-05', 1, 1),
(3, '楊逍', 33, '開發(fā)', 8400, '2000-11-03', 2, 1),
(4, '韋一笑', 48, '開發(fā)', 11000, '2002-02-05', 2, 1),
(5, '常遇春', 43, '開發(fā)', 10500, '2004-09-07', 3, 1),
(6, '小昭', 19, '程序員鼓勵師', 6600, '2004-10-12', 2, 1),
(7, '滅絕', 60, '財務(wù)總監(jiān)', 8500, '2002-09-12', 1, 3),
(8, '周芷若', 19, '會計', 48000, '2006-06-02', 7, 3),
(9, '丁敏君', 23, '出納', 5250, '2009-05-13', 7, 3),
(10, '趙敏', 20, '市場部總監(jiān)', 12500, '2004-10-12', 1, 2),
(11, '鹿杖客', 56, '職員', 3750, '2006-10-03', 10, 2),
(12, '鶴筆翁', 19, '職員', 3750, '2007-05-09', 10, 2),
(13, '方東白', 19, '職員', 5500, '2009-02-12', 10, 2),
(14, '張三豐', 88, '銷售總監(jiān)', 14000, '2004-10-12', 1, 4),
(15, '俞蓮舟', 38, '銷售', 4600, '2004-10-12', 14, 4),
(16, '宋遠(yuǎn)橋', 40, '銷售', 4600, '2004-10-12', 14, 4),
(17, '陳友諒', 42, null, 2000, '2011-10-12', 1, null);
三、內(nèi)連接
1、概念
- 相當(dāng)于查詢A、B交集部分?jǐn)?shù)據(jù)
- 語法分為兩種: 隱式內(nèi)連接、顯式內(nèi)連接
2、隱式內(nèi)連接
// SELECT 字段列表 FROM 表1 , 表2 WHERE 條件 ... ; // 查詢每一個員工的姓名 , 及關(guān)聯(lián)的部門的名稱 (隱式內(nèi)連接實(shí)現(xiàn)) select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
3、顯示內(nèi)連接
- 只返回兩個表中連接字段相等的行
- A inner join B：取交集
// SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 連接條件 ... ; // 查詢每一個員工的姓名 , 及關(guān)聯(lián)的部門的名稱 (顯式內(nèi)連接實(shí)現(xiàn)) select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id;
四、外連接
1、左外連接
- 返回包括左表中的所有記錄和右表中連接字段相等的記錄
- A left join B：取A全部，B沒有對應(yīng)的值，則為null
# SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 條件 ... ; # 查詢emp表的所有數(shù)據(jù), 和對應(yīng)的部門信息 select emp.*, dept.name from emp left outer join dept on emp.dept_id = dept.id
2、右外連接
- 返回包括右表中的所有記錄和左表中連接字段相等的記
- A right join B：取B全部，A沒有對應(yīng)的值，則為null
# SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 條件 ... ; # 查詢dept表的所有數(shù)據(jù), 和對應(yīng)的員工信息(右外連接) select dept.*, emp.* from emp right outer join dept on emp.dept_id = dept.id
五、自連接
1、自連接查詢
- 顧名思義，就是自己連接自己，也就是把一張表連接查詢多次
- 在自連接查詢中，必須要為表起別名，要不然我們不清楚所指定的條件、返回的字段，到底是哪一張表的字段。
# SELECT 字段列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ... ; # 查詢員工 及其 所屬領(lǐng)導(dǎo)的名字 select a.name , b.name from emp a , emp b where a.managerid = b.id; # 查詢所有員工 emp 及其領(lǐng)導(dǎo)的名字 emp , 如果員工沒有領(lǐng)導(dǎo), 也需要查詢出來 select a.name '員工', b.name '領(lǐng)導(dǎo)' from emp a left join emp b on a.managerid =b.id;
2、聯(lián)合查詢
- 是把多次查詢的結(jié)果合并起來，形成一個新的查詢結(jié)果集
- 對于聯(lián)合查詢的多張表的列數(shù)必須保持一致，字段類型也需要保持一致。
- union all 會將全部的數(shù)據(jù)直接合并在一起，union 會對合并之后的數(shù)據(jù)去重。
SELECT 字段列表 FROM 表A ... UNION [ ALL ] SELECT 字段列表 FROM 表B ....; # 將薪資低于 5000 的員工 , 和 年齡大于 50 歲的員工全部查詢出來。會出現(xiàn)相同數(shù)據(jù) select * from emp where salary < 5000 union all select * from emp where age > 50; # 將薪資低于 5000 的員工 , 和 年齡大于 50 歲的員工全部查詢出來。去重 select * from emp where salary < 5000 union select * from emp where age > 50;
單表DQL查詢語句
一、準(zhǔn)備數(shù)據(jù)
create table emp
(
id int comment '編號',
number varchar(10) comment '工號',
name varchar(10) comment '姓名',
gender char(1) comment '性別',
age tinyint unsigned comment '年齡',
idCard char(18) comment '身份證號',
workAddress varchar(50) comment '工作地址',
joinDate date comment '入職時間'
) comment '員工表';
INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (1, '00001', '柳巖666', '女', 20, '123456789012345678', '北京', '2000-01-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (2, '00002', '張無忌', '男', 18, '123456789012345670', '北京', '2005-09-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (3, '00003', '韋一笑', '男', 38, '123456789712345670', '上海', '2005-08-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (4, '00004', '趙敏', '女', 18, '123456757123845670', '北京', '2009-12-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (6, '00006', '楊逍', '男', 28, '12345678931234567X', '北京', '2006-01-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (7, '00007', '范瑤', '男', 40, '123456789212345670', '北京', '2005-05-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (8, '00008', '黛綺絲', '女', 38, '123456157123645670', '天津', '2015-05-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (9, '00009', '范涼涼', '女', 45, '123156789012345678', '北京', '2010-04-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (10, '00010', '陳友諒', '男', 53, '123456789012345670', '上海', '2011-01-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (11, '00011', '張士誠', '男', 55, '123567897123465670', '江蘇', '2015-05-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (13, '00013', '張三豐', '男', 88, '123656789012345678', '江蘇', '2020-11-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (14, '00014', '滅絕', '女', 65, '123456719012345670', '西安', '2019-05-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01'); INSERT INTO emp (id, number, name, gender, age, idCard, workAddress, joinDate) VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');
二、單表查詢
基本查詢
1.查詢多個字段
select 字段 from 表名; select * from emp; select name,gender from emp;
2.字段設(shè)置別名
SELECT 字段1 [ AS 別名1 ] , 字段2 [ AS 別名2 ] ... FROM 表名; select name as username,gender as sex from emp;
3.去除重復(fù)記錄
SELECT DISTINCT 字段列表 FROM 表名; select distinct workaddress from emp;
三、多條件查詢
| 比較運(yùn)算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| BETWEEN … AND … | 在某個范圍之內(nèi)(含最小、最大值) |
| IN(…) | 在in之后的列表中的值，多選一 |
| LIKE 占位符 | 模糊匹配(_匹配單個字符, %匹配任意個字符) |
| IS NULL | 是NULL |
| 邏輯運(yùn)算符 | 功能 |
|---|---|
| AND 或 && | 并且 (多個條件同時成立) |
| OR 或 | |
| NOT 或 ! | 非 , 不是 |
查詢出id為1的name、idcard數(shù)據(jù)
查詢年齡小于 20 的員工信息
查詢年齡小于等于 20 的員工信息
查詢沒有身份證號的員工信息
查詢有身份證號的員工信息
查詢年齡不等于 88 的員工信息
查詢年齡在15歲(包含) 到 20歲(包含)之間的員工信息
查詢性別為 女 且年齡小于 25歲的員工信息
查詢年齡等于18 或 20 或 40 的員工信息
查詢姓名為兩個字的員工信息。模糊匹配(_匹配單個字符, %匹配任意個字符)
查詢身份證號最后一位是X的員工信息
SELECT 字段列表 FROM 表名 WHERE 條件列表 ; // 查詢出id為1的name、idcard數(shù)據(jù) select name,idcard from emp where id = 1; // 查詢年齡小于 20 的員工信息 select * from emp where age < 20; // 查詢年齡小于等于 20 的員工信息 select * from emp where age <= 20; // 查詢沒有身份證號的員工信息 select * from emp where idcard is null; // 查詢有身份證號的員工信息 select * from emp where idcard is not null; // 查詢年齡不等于 88 的員工信息 select * from emp where age != 88; select * from emp where age <> 88; // 查詢年齡在15歲(包含) 到 20歲(包含)之間的員工信息 select * from emp where age >= 15 && age <= 20; select * from emp where age >= 15 and age <= 20; select * from emp where age between 15 and 20; // 查詢性別為 女 且年齡小于 25歲的員工信息 select * from emp where gender = '女' and age < 25; // 查詢年齡等于18 或 20 或 40 的員工信息 select * from emp where age = 18 or age = 20 or age =40; select * from emp where age in(18,20,40); // 查詢姓名為兩個字的員工信息。模糊匹配(_匹配單個字符, %匹配任意個字符) select * from emp where name like '__'; // 查詢身份證號最后一位是X的員工信息 select * from emp where idcard like '%X'; select * from emp where idcard like '_________________X';
四、聚合函數(shù)
| 函數(shù) | 功能 |
|---|---|
| count | 統(tǒng)計數(shù)量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
統(tǒng)計該企業(yè)員工數(shù)量
統(tǒng)計該企業(yè)員工的平均年齡
統(tǒng)計該企業(yè)員工的最大年齡
統(tǒng)計該企業(yè)員工的最小年齡
統(tǒng)計西安地區(qū)員工的年齡之和
注意 : NULL值是不參與所有聚合函數(shù)運(yùn)算的。
SELECT 聚合函數(shù)(字段列表) FROM 表名 ; // 統(tǒng)計該企業(yè)員工數(shù)量 select count(*) from emp; -- 統(tǒng)計的是總記錄數(shù) select count(idcard) from emp; -- 統(tǒng)計的是idcard字段不為null的記錄數(shù) // 統(tǒng)計該企業(yè)員工的平均年齡 select avg(age) from emp; // 統(tǒng)計該企業(yè)員工的最大年齡 select max(age) from emp; // 統(tǒng)計該企業(yè)員工的最小年齡 select min(age) from emp; // 統(tǒng)計西安地區(qū)員工的年齡之和 select sum(age) from emp where workaddress = '西安';
五、分組查詢
where與having區(qū)別：
- 執(zhí)行時機(jī)不同：where是分組之前進(jìn)行過濾，不滿足where條件，不參與分組；而having是分組之后對結(jié)果進(jìn)行過濾。
- 判斷條件不同：where不能對聚合函數(shù)進(jìn)行判斷，而having可以。
注意事項:
- 分組之后，查詢的字段一般為聚合函數(shù)和分組字段，查詢其他字段無任何意義。
- 執(zhí)行順序: where > 聚合函數(shù) > having。
- 支持多字段分組, 具體語法為 : group by columnA,columnB
統(tǒng)計該企業(yè)員工數(shù)量
統(tǒng)計該企業(yè)員工的平均年齡
統(tǒng)計該企業(yè)員工的最大年齡
統(tǒng)計該企業(yè)員工的最小年齡
統(tǒng)計西安地區(qū)員工的年齡之和
SELECT 字段列表 FROM 表名 [ WHERE 條件 ] GROUP BY 分組字段名 [ HAVING 分組后過濾條件 ]; // 根據(jù)性別分組 , 統(tǒng)計男性員工 和 女性員工的數(shù)量 select gender, count(*) from emp group by gender ; // 根據(jù)性別分組 , 統(tǒng)計男性員工 和 女性員工的平均年齡 select gender, avg(age) from emp group by gender ; // 查詢年齡小于45的員工 , 并根據(jù)工作地址分組 , 獲取員工數(shù)量大于等于3的工作地址 select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3; // 統(tǒng)計各個工作地址上班的男性及女性員工的數(shù)量 select workaddress, gender, count(*) '數(shù)量' from emp group by gender , workaddress;
六、排序查詢
- ASC : 升序(默認(rèn)值)
- DESC: 降序
- 如果是多字段排序，當(dāng)?shù)谝粋€字段值相同時，才會根據(jù)第二個字段進(jìn)行排序
根據(jù)年齡對公司的員工進(jìn)行升序排序
根據(jù)入職時間, 對員工進(jìn)行降序排序
根據(jù)年齡對公司的員工進(jìn)行升序排序 , 年齡相同 , 再按照入職時間進(jìn)行降序排序
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ; // 根據(jù)年齡對公司的員工進(jìn)行升序排序 select * from emp order by age asc; select * from emp order by age; // 根據(jù)入職時間, 對員工進(jìn)行降序排序 select * from emp order by entrydate desc; // 根據(jù)年齡對公司的員工進(jìn)行升序排序 , 年齡相同 , 再按照入職時間進(jìn)行降序排序 select * from emp order by age asc , entrydate desc;
七、分頁查詢
- 起始索引從0開始，起始索引 = （查詢頁碼 - 1）* 每頁顯示記錄數(shù)。
- 分頁查詢是數(shù)據(jù)庫的方言，不同的數(shù)據(jù)庫有不同的實(shí)現(xiàn)，MySQL中是LIMIT。
- 如果查詢的是第一頁數(shù)據(jù)，起始索引可以省略，直接簡寫為 limit 10。
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查詢記錄數(shù) ; // 查詢第1頁員工數(shù)據(jù), 每頁展示10條記錄 select * from emp limit 0,10; select * from emp limit 10; // 查詢第2頁員工數(shù)據(jù), 每頁展示10條記錄 --------> (頁碼-1)*頁展示記錄數(shù) select * from emp limit 10,10
八、綜合案例
查詢年齡為20,21,22,23歲的員工信息。
查詢性別為 男 ，并且年齡在 20-40 歲(含)以內(nèi)的姓名為三個字的員工
統(tǒng)計員工表中, 年齡小于60歲的 , 男性員工和女性員工的人數(shù)。
查詢所有年齡小于等于35歲員工的姓名和年齡，并對查詢結(jié)果按年齡升序排序，如果年齡相同按入職時間降序排序。
查詢性別為男，且年齡在20-40 歲(含)以內(nèi)的前5個員工信息，對查詢的結(jié)果按年齡升序排序，年齡相同按入職時間升序排序。
// 查詢年齡為20,21,22,23歲的員工信息。 select * from emp where age in(20,21,22,23) // 查詢性別為 男 ，并且年齡在 20-40 歲(含)以內(nèi)的姓名為三個字的員工 select * from emp where gender = '男' and (age between 20 and 40) and name like '___' // 統(tǒng)計員工表中, 年齡小于60歲的 , 男性員工和女性員工的人數(shù)。 select gender,count(*) from emp where age < 60 group by gender // 查詢所有年齡小于等于35歲員工的姓名和年齡，并對查詢結(jié)果按年齡升序排序，如果年齡相同按入職時間降序排序。 select * from emp where age <= 35 order by age, joinDate desc // 查詢性別為男，且年齡在20-40 歲(含)以內(nèi)的前5個員工信息，對查詢的結(jié)果按年齡升序排序，年齡相同按入職時間升序排序。 select * from emp where gender = '男' and (age between 20 and 40) order by age,joinDate desc limit 5
九、執(zhí)行順序
到此這篇關(guān)于Mysql中單表和多表DQL查詢語句詳解的文章就介紹到這了,更多相關(guān)Mysql DQL查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要給大家介紹了關(guān)于SQL替換字符串值的5種常用方法,常用SQL的朋友應(yīng)該知道,SQL中一般都會提供一些字符串處理函數(shù),需要的朋友可以參考下2023-08-08
MySQL更改用戶權(quán)限從localhost到%,涉及登錄MySQL、使用GRANT語句更新權(quán)限及RENAMEUSER修改用戶主機(jī)名,強(qiáng)調(diào)安全考慮與數(shù)據(jù)備份的重要性2026-05-05
MySQL使用SHOW PROCESSLIST的實(shí)現(xiàn)
本文主要介紹了MySQL使用SHOW PROCESSLIST的實(shí)現(xiàn),文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2025-03-03
這篇文章主要介紹了MySQL中關(guān)于case when的用法,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-06-06
子查詢可以單獨(dú)作為臨時數(shù)據(jù),作為一張表或者一個字段,通過()進(jìn)行包裹,表達(dá)一個整體,這篇文章主要介紹了mysql單表查詢、多表查詢、分組查詢、子查詢案例解析,需要的朋友可以參考下2024-07-07
MySQL修改lower_case_table_names參數(shù)的方法實(shí)踐
本文主要介紹了MySQL修改lower_case_table_names參數(shù),文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-05-05
這篇文章主要給大家介紹了關(guān)于MySQL磁盤碎片整理的相關(guān)資料,為什么數(shù)據(jù)庫會產(chǎn)生碎片,以及如何清理磁盤碎片，還有一些清理磁盤碎片的注意事項,需要的朋友可以參考下2022-04-04
MySQL遞歸查詢樹狀表的子節(jié)點(diǎn)、父節(jié)點(diǎn)具體實(shí)現(xiàn)
本程序?qū)懥藘蓚€sql存儲過程，子節(jié)點(diǎn)查詢算是照搬了，父節(jié)點(diǎn)查詢是逆思維弄的2014-03-03
這篇文章主要介紹了MySQL存儲過程參數(shù)的用法及說明，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
為了提高數(shù)據(jù)庫效率，建索引是家常便飯；那么當(dāng)查詢條件為2個及以上時，我們是創(chuàng)建多個單列索引還是創(chuàng)建一個聯(lián)合索引好呢？他們之間的區(qū)別是什么？哪個效率高呢？本文將詳細(xì)測試分析下。2021-05-05