12-Mysql資料庫----多表查詢

来源:https://www.cnblogs.com/dalaoban/archive/2018/08/31/9562783.html
-Advertisement-
Play Games

本節重點： 多表連接查詢 符合條件連接查詢 子查詢 準備工作：準備兩張表，部門表（department）、員工表（employee） ps:觀察兩張表，發現department表中id=203部門在employee中沒有對應的員工，發現employee中id=6的員工在department表中沒有對 ...


本節重點：

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

　　

 

準備工作：準備兩張表，部門表（department）、員工表（employee）

複製代碼 
create table department(
id int,
name varchar(20) 
);

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

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

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('nvshen','male',18,200),
('xiaomage','female',18,204)
;

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

mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| 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    |                |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> select * from department;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技術         |
|  201 | 人力資源     |
|  202 | 銷售         |
|  203 | 運營         |
+------+--------------+
4 rows in set (0.02 sec)

mysql> select * from employee;
+----+----------+--------+------+--------+
| id | name     | sex    | age  | dep_id |
+----+----------+--------+------+--------+
|  1 | egon     | male   |   18 |    200 |
|  2 | alex     | female |   48 |    201 |
|  3 | wupeiqi  | male   |   38 |    201 |
|  4 | yuanhao  | female |   28 |    202 |
|  5 | nvshen   | male   |   18 |    200 |
|  6 | xiaomage | female |   18 |    204 |
+----+----------+--------+------+--------+
6 rows in set (0.00 sec)
複製代碼

ps:觀察兩張表，發現department表中id=203部門在employee中沒有對應的員工，發現employee中id=6的員工在department表中沒有對應關係。

 

 

一、多表連接查詢

兩張表的準備工作已完成，比如現在我要查詢的員工信息以及該員工所在的部門。從該題中，我們看出既要查員工又要查該員工的部門，肯定要將兩張表進行連接查詢，多表連接查詢。

重點：外鏈接語法

語法：

SELECT 欄位列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.欄位 = 表2.欄位;

 

（1）先看第一種情況交叉連接：不適用任何匹配條件。生成笛卡爾積（關於笛卡爾積的含義，大家百度自行補腦）。

複製代碼 
mysql> select * from employee,department;
+----+----------+--------+------+--------+------+--------------+
| id | name     | sex    | age  | dep_id | id   | name         |
+----+----------+--------+------+--------+------+--------------+
|  1 | egon     | male   |   18 |    200 |  200 | 技術         |
|  1 | egon     | male   |   18 |    200 |  201 | 人力資源     |
|  1 | egon     | male   |   18 |    200 |  202 | 銷售         |
|  1 | egon     | male   |   18 |    200 |  203 | 運營         |
|  2 | alex     | female |   48 |    201 |  200 | 技術         |
|  2 | alex     | female |   48 |    201 |  201 | 人力資源     |
|  2 | alex     | female |   48 |    201 |  202 | 銷售         |
|  2 | alex     | female |   48 |    201 |  203 | 運營         |
|  3 | wupeiqi  | male   |   38 |    201 |  200 | 技術         |
|  3 | wupeiqi  | male   |   38 |    201 |  201 | 人力資源     |
|  3 | wupeiqi  | male   |   38 |    201 |  202 | 銷售         |
|  3 | wupeiqi  | male   |   38 |    201 |  203 | 運營         |
|  4 | yuanhao  | female |   28 |    202 |  200 | 技術         |
|  4 | yuanhao  | female |   28 |    202 |  201 | 人力資源     |
|  4 | yuanhao  | female |   28 |    202 |  202 | 銷售         |
|  4 | yuanhao  | female |   28 |    202 |  203 | 運營         |
|  5 | nvshen   | male   |   18 |    200 |  200 | 技術         |
|  5 | nvshen   | male   |   18 |    200 |  201 | 人力資源     |
|  5 | nvshen   | male   |   18 |    200 |  202 | 銷售         |
|  5 | nvshen   | male   |   18 |    200 |  203 | 運營         |
|  6 | xiaomage | female |   18 |    204 |  200 | 技術         |
|  6 | xiaomage | female |   18 |    204 |  201 | 人力資源     |
|  6 | xiaomage | female |   18 |    204 |  202 | 銷售         |
|  6 | xiaomage | female |   18 |    204 |  203 | 運營         |
複製代碼

 

（2）內連接：只連接匹配的行

 

複製代碼 
#找兩張表共有的部分，相當於利用條件從笛卡爾積結果中篩選出了匹配的結果
#department沒有204這個部門，因而employee表中關於204這條員工信息沒有匹配出來
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+---------+------+--------+--------------+
| id | name    | age  | sex    | name         |
+----+---------+------+--------+--------------+
|  1 | egon    |   18 | male   | 技術         |
|  2 | alex    |   48 | female | 人力資源     |
|  3 | wupeiqi |   38 | male   | 人力資源     |
|  4 | yuanhao |   28 | female | 銷售         |
|  5 | nvshen  |   18 | male   | 技術         |
+----+---------+------+--------+--------------+
5 rows in set (0.00 sec)

#上述sql等同於
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
複製代碼

 

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

複製代碼 
#以左表為準，即找出所有員工信息，當然包括沒有部門的員工
#本質就是：在內連接的基礎上增加左邊有，右邊沒有的結果
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+----------+--------------+
| id | name     | depart_name  |
+----+----------+--------------+
|  1 | egon     | 技術         |
|  5 | nvshen   | 技術         |
|  2 | alex     | 人力資源     |
|  3 | wupeiqi  | 人力資源     |
|  4 | yuanhao  | 銷售         |
|  6 | xiaomage | NULL         |
+----+----------+--------------+
6 rows in set (0.00 sec)
複製代碼

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

複製代碼 
#以右表為準，即找出所有部門信息，包括沒有員工的部門
#本質就是：在內連接的基礎上增加右邊有，左邊沒有的結果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+---------+--------------+
| id   | name    | depart_name  |
+------+---------+--------------+
|    1 | egon    | 技術         |
|    2 | alex    | 人力資源     |
|    3 | wupeiqi | 人力資源     |
|    4 | yuanhao | 銷售         |
|    5 | nvshen  | 技術         |
| NULL | NULL    | 運營         |
+------+---------+--------------+
6 rows in set (0.00 sec)
複製代碼

 

（5） 全外連接：顯示左右兩個表全部記錄(瞭解)

複製代碼 
#外連接：在內連接的基礎上增加左邊有右邊沒有的和右邊有左邊沒有的結果
#註意：mysql不支持全外連接 full JOIN
#強調：mysql可以使用此種方式間接實現全外連接
語法：select * from employee left join department on employee.dep_id = department.id 
       union all
      select * from employee right join department on employee.dep_id = department.id;

 mysql> select * from employee left join department on employee.dep_id = department.id
          union
        select * from employee right join department on employee.dep_id = department.id
           ;
+------+----------+--------+------+--------+------+--------------+
| id   | name     | sex    | age  | dep_id | id   | name         |
+------+----------+--------+------+--------+------+--------------+
|    1 | egon     | male   |   18 |    200 |  200 | 技術         |
|    5 | nvshen   | male   |   18 |    200 |  200 | 技術         |
|    2 | alex     | female |   48 |    201 |  201 | 人力資源     |
|    3 | wupeiqi  | male   |   38 |    201 |  201 | 人力資源     |
|    4 | yuanhao  | female |   28 |    202 |  202 | 銷售         |
|    6 | xiaomage | female |   18 |    204 | NULL | NULL         |
| NULL | NULL     | NULL   | NULL |   NULL |  203 | 運營         |
+------+----------+--------+------+--------+------+--------------+
7 rows in set (0.01 sec)

#註意 union與union all的區別：union會去掉相同的紀錄
複製代碼

 

 

二、符合條件連接查詢

示例1：以內連接的方式查詢employee和department表，並且employee表中的age欄位值必須大於25，即找出年齡大於25歲的員工以及員工所在的部門

select employee.name,department.name from employee inner join department
　　on employee.dep_id = department.id
　　where age > 25;

 

示例2：以內連接的方式查詢employee和department表，並且以age欄位的升序方式顯示。

select employee.id,employee.name,employee.age,department.name from employee,department
    where employee.dep_id = department.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 department
    where id in 
        (select dep_id from employee group by dep_id having avg(age) > 25);
# 查看技術部員工姓名
select name from employee
    where dep_id in 
        (select id from department where name='技術');
#查看不足1人的部門名
select name from department
    where id not in 
        (select dep_id from employee group by dep_id);
複製代碼

 

（2）帶比較運算符的子查詢

複製代碼 
#比較運算符：=、!=、>、>=、<、<=、<>
#查詢大於所有人平均年齡的員工名與年齡
mysql> select name,age from employee where age > (select avg(age) from employee);
+---------+------+
| name    | age  |
+---------+------+
| alex    |   48 |
| wupeiqi |   38 |
+---------+------+

#查詢大於部門內平均年齡的員工名、年齡
思路：
      （1）先對員工表(employee)中的人員分組(group by)，查詢出dep_id以及平均年齡。
       (2)將查出的結果作為臨時表,再對根據臨時表的dep_id和employee的dep_id作為篩選條件將employee表和臨時表進行內連接。
       (3)最後再將employee員工的年齡是大於平均年齡的員工名字和年齡篩選。



mysql> select t1.name,t1.age from employee as t1
             inner join
            (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2
            on t1.dep_id = t2.dep_id
            where t1.age > t2.avg_age;
+------+------+
| name | age  |
+------+------+
| alex |   48 |
複製代碼

 

(3)帶EXISTS關鍵字的子查詢

複製代碼 
#EXISTS關字鍵字表示存在。在使用EXISTS關鍵字時，內層查詢語句不返回查詢的記錄。而是返回一個真假值。True或False
#當返回True時，外層查詢語句將進行查詢；當返回值為False時，外層查詢語句不進行查詢
#department表中存在dept_id=203，Ture
mysql> select * from employee  where exists (select id from department where id=200);
+----+----------+--------+------+--------+
| id | name     | sex    | age  | dep_id |
+----+----------+--------+------+--------+
|  1 | egon     | male   |   18 |    200 |
|  2 | alex     | female |   48 |    201 |
|  3 | wupeiqi  | male   |   38 |    201 |
|  4 | yuanhao  | female |   28 |    202 |
|  5 | nvshen   | male   |   18 |    200 |
|  6 | xiaomage | female |   18 |    204 |
+----+----------+--------+------+--------+
#department表中存在dept_id=205，False
mysql> select * from employee  where exists (select id from department where id=204);
Empty set (0.00 sec)
複製代碼

小練習：

查詢每個部門最新入職的那位員工

複製代碼 
#創建表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一個部門一個屋子
depart_id int
);


#查看表結構
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

#插入記錄
#三個部門：教學，銷售，運營
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩駐沙河辦事處外交大使',7300.33,401,1), #以下是教學部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龍','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是銷售部門
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('張野','male',28,'20160311','operation',10000.13,403,3), #以下是運營部門
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬銀','female',18,'20130311','operation',19000,403,3),
('程咬銅','male',18,'20150411','operation',18000,403,3),
('程咬鐵','female',18,'20140512','operation',17000,403,3)
;
複製代碼 
select * from employee as t1
inner join
(select post,max(hire_date) as new_date from employee group by post) as t2
on t1.post=t2.post
where t1.hire_date=t2.new_date;
小練習答案

 


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

-Advertisement-
Play Games
更多相關文章
  • [原創]分散式系統之緩存的微觀應用經驗談（一） 【設計細節篇】
    前言 近幾個月一直在忙些瑣事，幾乎年後都沒怎麼閑過。忙忙碌碌中就進入了2018年的秋天了，不得不感嘆時間總是如白駒過隙，也不知道收穫了什麼和失去了什麼。最近稍微休息，買了兩本與技術無關的書，其一是Yann Martel 寫的《The High Mountains of Portugal》（葡萄牙的... ...
  • 如何創建和還原SQL Server 2000資料庫？
    說明：這篇文章是幾年前我發佈在網易博客當中的原創文章，但由於網易博客現在要停止運營了，所以我就把這篇文章搬了過來，雖然現如今SQL Server 2000軟體早已經過時了，但仍然有一部分人在使用它，尤其是某些高校的學生在做畢業設計或者課程設計的時候可能會使用到，所以就把該軟體的資料庫還原過程保留在這 ...
  • SQL Server 2000詳細安裝過程及配置
    說明：這篇文章是幾年前我發佈在網易博客當中的原創文章，但由於網易博客現在要停止運營了，所以我就把這篇文章搬了過來，雖然現如今SQL Server 2000軟體早已經過時了，但仍然有一部分人在使用它，尤其是某些高校的學生在做畢業設計或者課程設計的時候可能會使用到，所以就把該軟體的安裝過程保留在這裡吧。 ...
  • mysql 開發進階篇系列 34 工具篇 mysqlcheck(MyISAM表維護工具)
    一.概述 mysqlcheck客戶端工具可以檢查和修複MyISAM表，還可以優化和分析表。實際上，它集成了mysql工具中check,repair,analyze,optimize功能，對於check 則不支持MEMORY表, repair 則不支持 InnoDB表。mysqlcheck只有在資料庫 ...
  • （轉載）Oracle 樹操作(select…start with…connect by…prior)
    轉載地址：https://www.cnblogs.com/linjiqin/p/3152674.html 備註：如有侵權，請立即聯繫刪除。 oracle樹查詢的最重要的就是select…start with…connect by…prior語法了。依托於該語法，我們可以將一個表形結構的以樹的順序列出 ...
  • （轉載）令人迷糊的Oracle遞歸查詢(start with)
    轉載地址：https://blog.csdn.net/weiwenhp/article/details/8218091 備註：如有侵權，請聯繫立即刪除。 寫代碼時碰到要弄清楚Oracle的role之間的傳遞關係,就是有role A的話,可以通過grant A to B,把A賦予給B,又通過grant ...
  • 13-Mysql資料庫----許可權設置
    許可權管理 我們知道我們的最高許可權管理者是root用戶，它擁有著最高的許可權操作。包括select、update、delete、update、grant等操作。那麼一般情況在公司之後DBA工程師會創建一個用戶和密碼，讓你去連接資料庫的操作，並給當前的用戶設置某個操作的許可權（或者所有許可權）。那麼這時就需要 ...
  • redis-4.0.11主從配置初步探究
    redis-4.0.11相較於以前版本,新增了幾個安全措施,稍稍研究了6379.conf配置文件,在這裡記錄一下. 實驗環境: centos7.4 redis:redis-4.0.11 1. redis-4.0.11.tar.gz解壓後直接執行安裝腳本. 2. ~/redis-4.0.11/util ...
一周排行
    -Advertisement-
    Play Games
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類