Mysql-多表連接的操作和用法

来源:https://www.cnblogs.com/ManyQian/archive/2018/05/11/9026606.html
-Advertisement-
Play Games

一 、介紹 二 、多表連接查詢 三 、符合條件連接查詢 四 、子查詢 一、介紹 本節主題 多表連接查詢 複合條件連接查詢 子查詢 準備表 #建表 create table dep( id int, name varchar(20) ); create table emp( id int primar ...


一、介紹

本節主題

  • 多表連接查詢
  • 複合條件連接查詢
  • 子查詢

準備表

#建表
create table dep(
id int,
name varchar(20) 
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入數據
insert into dep values
(200,'技術'),
(201,'人力資源'),
(202,'銷售'),
(203,'運營');

insert into emp(name,sex,age,dep_id) values
('大娃','male',18,200),
('二娃','female',48,201),
('三娃','male',38,201),
('四娃','female',28,202),
('五娃','male',18,200),
('六娃','female',18,204)
;


#查看表結構和數據
mysql> desc dep;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

mysql> desc emp;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+

mysql> select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技術 |
| 201 | 人力資源 |
| 202 | 銷售 |
| 203 | 運營 |
+------+--------------+

mysql> select * from emp;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | 大娃 | male | 18 | 200 |
| 2 | 二娃 | female | 48 | 201 |
| 3 | 三娃 | male | 38 | 201 |
| 4 | 四娃 | female | 28 | 202 |
| 5 | 五娃 | male | 18 | 200 |
| 6 | 六娃 | female | 18 | 204 |
+----+------------+--------+------+--------+
dep表和emp表

二 、多表連接查詢

#重點：外鏈接語法

SELECT 欄位列表

FROM 表1

INNER|LEFT|RIGHT JOIN 表2

ON 表1.欄位 = 表2.欄位;

1 、交叉連接：不適用任何匹配條件。生成笛卡爾積

mysql> select * from emp,dep;
+----+------------+--------+------+--------+------+--------------+
| id | name       | sex    | age  | dep_id | id   | name         |
+----+------------+--------+------+--------+------+--------------+
|  1 | 大娃       | male   |   18 |    200 |  200 | 技術         |
|  1 | 大娃       | male   |   18 |    200 |  201 | 人力資源     |
|  1 | 大娃       | male   |   18 |    200 |  202 | 銷售         |
|  1 | 大娃       | male   |   18 |    200 |  203 | 運營         |
|  2 | 二娃       | female |   48 |    201 |  200 | 技術         |
|  2 | 二娃       | female |   48 |    201 |  201 | 人力資源     |
|  2 | 二娃       | female |   48 |    201 |  202 | 銷售         |
|  2 | 二娃       | female |   48 |    201 |  203 | 運營         |
|  3 | 三娃       | male   |   38 |    201 |  200 | 技術         |
|  3 | 三娃       | male   |   38 |    201 |  201 | 人力資源     |
|  3 | 三娃       | male   |   38 |    201 |  202 | 銷售         |
|  3 | 三娃       | male   |   38 |    201 |  203 | 運營         |
|  4 | 四娃       | female |   28 |    202 |  200 | 技術         |
|  4 | 四娃       | female |   28 |    202 |  201 | 人力資源     |
|  4 | 四娃       | female |   28 |    202 |  202 | 銷售         |
|  4 | 四娃       | female |   28 |    202 |  203 | 運營         |
|  5 | 五娃       | male   |   18 |    200 |  200 | 技術         |
|  5 | 五娃       | male   |   18 |    200 |  201 | 人力資源     |
|  5 | 五娃       | male   |   18 |    200 |  202 | 銷售         |
|  5 | 五娃       | male   |   18 |    200 |  203 | 運營         |
|  6 | 六娃       | female |   18 |    204 |  200 | 技術         |
|  6 | 六娃       | female |   18 |    204 |  201 | 人力資源     |
|  6 | 六娃       | female |   18 |    204 |  202 | 銷售         |
|  6 | 六娃       | female |   18 |    204 |  203 | 運營         |
+----+------------+--------+------+--------+------+--------------+
交叉連接演示

2 、內連接：只連接匹配的行

#找兩張表共有的部分，相當於利用條件從笛卡爾積結果中篩選出了正確的結果
#dep沒有204這個部門，因而emp表中關於204這條員工信息沒有匹配出來
mysql> select emp.id,emp.name,emp.age,emp.sex,dep.name from emp inner join dep on emp.dep_id=dep.id; 
+----+-----------+------+--------+--------------+
| id | name      | age  | sex    | name         |
+----+-----------+------+--------+--------------+
|  1 | 大娃     |   18 | male    | 技術         |
|  2 | 二娃     |   48 | female | 人力資源     |
|  3 | 三娃     |   38 | male    | 人力資源     |
|  4 | 四娃     |   28 | female | 銷售         |
|  5 | 五娃     |   18 | male    | 技術         |
+----+-----------+------+--------+--------------+

#上述sql等同於
mysql> select emp.id,emp.name,emp.age,emp.sex,dep.name from emp,dep where emp.dep_id=dep.id;
內連接演示

3 、外鏈接之左連接：優先顯示左表全部記錄

#以左表為準，即找出所有員工信息，當然包括沒有部門的員工
#本質就是：在內連接的基礎上增加左邊有右邊沒有的結果
mysql> select emp.id,emp.name,dep.name as depart_name from emp left join dep on emp.dep_id=dep.id;
+----+------------+--------------+
| id | name       | depart_name  |
+----+------------+--------------+
|  1 | 大娃       | 技術         |
|  5 | 五娃       | 技術         |
|  2 | 二娃       | 人力資源     |
|  3 | 三娃       | 人力資源     |
|  4 | 四娃       | 銷售         |
|  6 | 六娃       | NULL         |
+----+------------+--------------+
左連接演示

4 、外鏈接之右連接：優先顯示右表全部記錄

#以右表為準，即找出所有部門信息，包括沒有員工的部門
#本質就是：在內連接的基礎上增加右邊有左邊沒有的結果
mysql> select emp.id,emp.name,dep.name as depart_name from emp right join dep on emp.dep_id=dep.id;
+------+-----------+--------------+
| id   | name      | depart_name  |
+------+-----------+--------------+
|    1 | 大娃       | 技術         |
|    2 | 二娃       | 人力資源     |
|    3 | 三娃       | 人力資源     |
|    4 | 四娃       | 銷售         |
|    5 | 五娃       | 技術         |
| NULL | NULL       | 運營         |
+------+-----------+--------------+
右連接演示

5 全外連接：顯示左右兩個表全部記錄

全外連接：在內連接的基礎上增加左邊有右邊沒有的和右邊有左邊沒有的結果
#註意：mysql不支持全外連接 full JOIN
#強調：mysql可以使用此種方式間接實現全外連接
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id
;
#查看結果
+------+------------+--------+------+--------+------+--------------+
| id   | name       | sex    | age  | dep_id | id   | name         |
+------+------------+--------+------+--------+------+--------------+
|    1 | 大娃       | male   |   18 |    200 |  200 | 技術         |
|    5 | 五娃       | male   |   18 |    200 |  200 | 技術         |
|    2 | 二娃       | female |   48 |    201 |  201 | 人力資源     |
|    3 | 三娃       | male   |   38 |    201 |  201 | 人力資源     |
|    4 | 四娃       | female |   28 |    202 |  202 | 銷售         |
|    6 | 六娃       | female |   18 |    204 | NULL | NULL         |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 運營         |
+------+------------+--------+------+--------+------+--------------+

#註意 union與union all的區別：union會去掉相同的紀錄
全外接演示

三 、符合條件連接查詢

#示例1：以內連接的方式查詢emp和dep表，並且emp表中的age欄位值必須大於25,即找出年齡大於25歲的員工以及員工所在的部門
select emp.name,dep.name from emp inner join dep
    on emp.dep_id = dep.id
    where age > 25;

#示例2：以內連接的方式查詢emp和dep表，並且以age欄位的升序方式顯示
select emp.id,emp.name,emp.age,dep.name from emp,dep
    where emp.dep_id = dep.id
    and age > 25
    order by age asc;
具體連接演示

四 、子查詢

#1：子查詢是將一個查詢語句嵌套在另一個查詢語句中。

#2：內層查詢語句的查詢結果，可以為外層查詢語句提供查詢條件。

#3：子查詢中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等關鍵字

#4：還可以包含比較運算符：= 、 !=、> 、<等

1 、帶IN關鍵字的子查詢

#查詢平均年齡在25歲以上的部門名
select id,name from dep
    where id in 
        (select dep_id from emp group by dep_id 、having avg(age) > 25);

#查看技術部員工姓名
select name from emp
    where dep_id in 
        (select id from dept where name='技術');

#查看不足1人的部門名(子查詢得到的是有人的部門id)
select name from dep where id not in (select distinct dep_id from emp);

2 帶比較運算符的子查詢

#比較運算符：=、!=、>、>=、<、<=、<>
#查詢大於所有人平均年齡的員工名與年齡
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| 大娃 | 48 |
| 三娃 | 38 |
+---------+------+
2 rows in set (0.00 sec)

#查詢大於部門內平均年齡的員工名、年齡
select t1.name,t1.age from emp t1
inner join 
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age; 

3 、帶EXISTS關鍵字的子查詢

EXISTS關字鍵字表示存在。在使用EXISTS關鍵字時，內層查詢語句不返回查詢的記錄。
而是返回一個真假值。True或False
當返回True時，外層查詢語句將進行查詢；當返回值為False時，外層查詢語句不進行查詢

#dept表中存在dept_id=203，Ture
mysql> select * from emp
    ->     where exists
    ->         (select id from dep where id=200);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | 大娃       | male   |   18 |    200 |
|  2 | 二娃       | female |   48 |    201 |
|  3 | 三娃       | male   |   38 |    201 |
|  4 | 四娃       | female |   28 |    202 |
|  5 | 五娃       | male   |   18 |    200 |
|  6 | 六娃       | female |   18 |    204 |
+----+------------+--------+------+--------+

#dep表中存在dept_id=205，False
mysql> select * from emp
    ->     where exists
    ->         (select id from dep where id=204);
Empty set (0.00 sec)

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Linux（CentOS7）設置自動備份資料庫到阿裡雲OSS
    環境：阿裡雲伺服器CentOS7.4 + MySQL5.6 基本思路： 1、編寫shell腳本，備份資料庫到指定目錄下 2、編寫Python腳本，把文件上傳到OSS 3、把shell腳本和Python腳本添加Linux的crontab定時器，設置定時執行 首先，環境要配置好，Python環境，cen ...
  • CentOS-Minimal版本下安裝telnet服務和xinetd服務
    預設在CentOS-Minimal版本下沒有安裝telnet和xinetd服務。 1、安裝telnet [root@localhost ~]# rpm -qa | grep telnet --檢查是否安裝telnet，沒有文件表示未安裝 [root@localhost ~]# yum -y inst ...
  • 解決docker pull出現 error pulling image configuration: Get https://dseasb33srnrn.cloudfront.net······: net/http: TLS handshake timeout的問題
    [root@MyCentos7 var]# docker pull javaUsing default tag: latestTrying to pull repository docker.io/library/java ... latest: Pulling from docker.io/lib ...
  • Linux系統初學-第三課 Linux網路配置
    Linux系統初學-第三課 Linux網路配置 1.動態IP配置 配置文件路徑 /etc/sysconfig/network-scripts/ ls查看網卡eth0，其中HWADDR值得獲取：ifconfig eht0 | grep HWaddr，保存之後，service network resta ...
  • 每天一個linux命令（1）：find命令之exec
    ind是我們很常用的一個Linux命令，但是我們一般查找出來的並不僅僅是看看而已，還會有進一步的操作，這個時候exec的作用就顯現出來了。 exec解釋：-exec 參數後面跟的是command命令，它的終止是以;為結束標誌的，所以這句命令後面的分號是不可缺少的，考慮到各個系統中分號會有不同的意義， ...
  • 進程間通信（管道）
    1、pipe 本質： 匿名管道 內核緩衝區 偽文件（偽文件和操作文件類似） 創建方式： fd[0] 表示讀端 fd[1] 表示寫端 返回值： 成功返回0，失敗返回-1 特點： 有讀端和寫端，對應兩個文件描述符，數據從寫端流入，讀端流出 操作管道的進程掛掉後管道自動釋放 管道預設是阻塞的 管道原理： ...
  • 右鍵菜單怎樣添加“在此處打開命令提示符”選項
    效果圖： 1.增加命令提示符 2.增加PowerShell 以上內容保存為綴名為OpenCmdHere.reg，雙擊OpenCmdHere.reg文件運行，彈出的提示點確認，修改註冊表就大功告成了！ 下載鏈接：https://files.cnblogs.com/files/dyj057/opencm ...
  • 「SQL歸納」樹形結構表的存儲與查詢功能的實現——通過路徑方法（非遞歸）
    一、樹形結構例子分析： 以360問答頁面為例：http://wenda.so.com/c/ 我們通過觀察URL，可以明確該頁面的數據以樹形結構存儲，下麵三塊模塊分別為： ①根節點 ②根節點的第一層子節點 ③為左側所選擇節點的下一層子節點 （圖1） 該例簡化的樹形結構圖如下： （圖2） 我們不難發現， ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類