day05多表查詢01

来源:https://www.cnblogs.com/liyuelian/archive/2022/10/05/16756290.html
-Advertisement-
Play Games

多表查詢 前面講過的基本查詢都是對一張表進行查詢，但在實際的開發中遠遠不夠。 下麵使用表emp，dept，salgrade進行多表查詢 emp： dept： salgrade： 1.前置-mysql表查詢-加強 1.1查詢增強 使用where子句 如何查找1992.1.1後入職的員工 在mysql中 ...


多表查詢

前面講過的基本查詢都是對一張表進行查詢，但在實際的開發中遠遠不夠。

下麵使用表emp，dept，salgrade進行多表查詢

emp：

image-20221004191354055

dept：

image-20221004191437441

salgrade：

image-20221004191524292

1.前置-mysql表查詢-加強

1.1查詢增強

  • 使用where子句

    如何查找1992.1.1後入職的員工

在mysql中，日期類型可以直接比較，需要註意格式

  • 如何使用like操作符

    %表示0到多個任意字元 _表示單個任意字元

    如何顯示首字元為S的員工姓名和工資

    如何顯示第三個字元為大寫O的所有員工的姓名和工資

  • 如何顯示沒有上級的雇員的情況

  • 查詢表結構

  • 使用order by子句

    如何按照工資的從低到高的順序，顯示雇員的信息

    按照部門號升序而雇員的工資降序排列，顯示雇員的信息

練習

SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;

-- - 使用where子句 
-- 在mysql中，日期類型可以直接比較
--  如何查找1992.1.1後入職的員工
 SELECT * FROM emp 
	WHERE hiredate > '1992-01-01';
image-20221005162027153 
-- - 如何使用like操作符
--   %表示0到多個字元   _表示單個字元
--   如何顯示首字元為S的員工姓名和工資
 SELECT ename,sal FROM emp 
	WHERE ename LIKE 'S%';

--   如何顯示第三個字元為大寫O的所有員工的姓名和工資
 SELECT ename,sal FROM emp 
	WHERE ename LIKE '__O%';

image-20221005162113291 image-20221005162139750

-- - 如何顯示沒有上級的雇員的情況
 SELECT * FROM emp 
	WHERE mgr IS NULL;

-- - 查詢表結構
DESC emp;
image-20221005162204237 image-20221005162228037 
-- 使用order by子句
-- 如何按照工資的從低到高的順序，顯示雇員的信息
 SELECT * FROM emp 
	ORDER BY sal ASC;

-- 按照部門號升序而雇員的工資降序排列，顯示雇員的信息
 SELECT * FROM emp 
	ORDER BY deptno ASC,sal DESC;
image-20221005162250721 image-20221005162312631

1.2分頁查詢

  1. 按照雇員的id號升序取出，每頁顯示3條記錄，請分別顯示第一頁，第二頁，第三頁

  2. 基本語法

    select ... limit start,rows

    表示從start+1行開始取，取出rows行，start從0開始計算

練習

-- 按照雇員的id號升序取出，每頁顯示3條記錄，請分別顯示第一頁，第二頁，第三頁
SELECT * FROM emp 
	ORDER BY empno
	LIMIT 0,3
	
SELECT * FROM emp 
	ORDER BY empno
	LIMIT 3,3
	
SELECT * FROM emp 
	ORDER BY empno
	LIMIT 6,3

公式：

SELECT * FROM emp 
	ORDER BY empno
	LIMIT 每頁顯示記錄數*（第幾頁-1）,每頁顯示記錄數

1.3分組函數和分組子句加強

  • 使用分組函數和分組子句group by
  1. 顯示每種崗位的雇員總數，平均工資
  2. 顯示雇員總數以及獲得補助的雇員數
  3. 顯示管理者的總人數
  4. 顯示雇員工資的最大差額
# 使用分組函數和分組子句groupby

-- 1. 顯示每種崗位的雇員總數，平均工資
SELECT COUNT(*),AVG(sal),job FROM emp 
	GROUP BY job;

-- 2. 顯示雇員總數以及獲得補助的雇員數
-- 思路：COUNT(列)  如果該列的值為空，是不會統計進去的
SELECT COUNT(*),COUNT(comm)
	FROM emp 
	
-- 擴展：統計沒有獲得補助的雇員數
SELECT COUNT(*),COUNT(IF(comm IS NULL,1,NULL))
	FROM emp 	
-- 或者
SELECT COUNT(*),COUNT(*)-COUNT(comm)
	FROM emp
	
-- 3. 顯示管理者的總人數
SELECT COUNT(DISTINCT mgr) 
	FROM emp;

-- 4. 顯示雇員工資的最大差額
SELECT MAX(sal)-MIN(sal)
	FROM emp;
  • 數據分組的總結

如果select語句同時包含有group by，having，limit，order by子句，

那麼他們的順序應該為 group by，having，order by，limit

應用案例

請統計每個部門的平均工資，並且是大於1000的，並且按照平均工資從高到低排序，取出前兩行記錄

-- 請統計每個部門group by 的平均avg工資，
-- 並且是大於1000的，having
-- 並且按照平均工資從高到低排序，order by
-- 取出前兩行記錄 limit

SELECT deptno,AVG(sal) AS avg_sal 
	FROM emp
	GROUP BY deptno
	HAVING avg_sal > 1000
	ORDER BY avg_sal DESC
	LIMIT 0,2
image-20221005172508496

2.多表查詢

2.1笛卡爾積

  • 說明

多表查詢是指基於兩個或兩個以上的表查詢，在實際的應用中，查詢單個表可能不能滿足需求，這時候就要用到多表查詢

例子-笛卡爾集（積）

SELECT * FROM emp,dept;

顯示的結果如下：共有52行記錄

image-20221005175003770

emp表：共有13行記錄

image-20221005175310130

dept表：共有4行記錄

image-20221005175447873

分析如下：

當兩張表查詢時，規則為

  1. 從第一張表中，取出一行 和第二張表的每一行進行組合，返回結果[含有兩張表的所有列]
  2. 一共返回的記錄數=第一張表的行數*第二張表的行數
  3. 這樣多表查詢預設處理返回的結果，稱為笛卡爾集（積）
  4. 解決這個多表的關鍵就是要寫出正確的過濾條件 where
  5. 多表查詢的條件不能少於 表的個數 -1 ，否則會出現笛卡爾積

練習

  1. 顯示雇員名，雇員工資以及所在部門的名字

  2. 如何顯示部門號為10的部門名，員工名和工資

  3. 顯示各個員工的姓名，工資及其工資的級別

-- 1. 顯示雇員名，雇員工資以及所在部門的名字
SELECT ename,sal,dname,emp.deptno
FROM emp,dept
WHERE emp.deptno = dept.deptno;

-- 2. 如何顯示部門號為10的部門名，員工名和工資
SELECT ename,sal,dname,emp.deptno
FROM emp,dept
WHERE emp.deptno = dept.deptno AND emp.deptno =10;
 
-- 3. 顯示各個員工的姓名，工資及其工資的級別
SELECT ename,sal,grade
FROM emp,salgrade
WHERE sal BETWEEN losal AND hisal;

-- 4.顯示雇員名，雇員工資以及所在部門的名字，並按照部門名排序
SELECT ename,sal,dname
FROM emp,dept
WHERE emp.deptno = dept.deptno
ORDER BY dept.dname DESC;

2.2自連接

  • 自連接

自連接是指在同一張表的連接查詢

  • 自連接的特點
    • 將同一張表看做兩張表使用
    • 需要給表取別名 ，格式為 表名 表別名

思考：顯示公司員工和他上級的名字

分析：可以發現員工的名字和上級的名字都是在emp表中

員工和上級是通過emp表的mgr列關聯的

image-20221005184007973 
-- 顯示公司員工和他上級的名字
SELECT worker.ename AS '職員名', boss.ename AS '上級名'-- 列的別名
	FROM emp worker,emp boss -- 為表起別名
	WHERE worker.mgr = boss.empno; -- 過濾條件
image-20221005185626994

3.子查詢

  • 什麼是子查詢

子查詢是指嵌入在其他SQL語句的select語句，也叫嵌套查詢

  • 單行子查詢

單行子查詢是指只返回一行數據的子查詢語句

請思考：如何顯示與Smith同一部門的所有員工？

  • 多行子查詢

多行子查詢指返回多行數據的子查詢 使用關鍵字 in

3.1多行子查詢

練習1

-- 請思考：如何顯示與Smith同一部門的所有員工？
/*
	1.先查詢到Smith的部門編號
	2.把上面的select語句當做是一個子查詢來使用
*/

SELECT deptno
FROM emp 
WHERE ename = 'SMITH';-- 先查詢到Smith的部門編號

-- 單行子查詢
SELECT *
	FROM emp 
	WHERE deptno = (SELECT deptno
			FROM emp 
			WHERE ename = 'SMITH'
			);

-- 多行子查詢
-- 如何查詢和部門10的工作相同 的雇員的名字、崗位、工資、部門號，但是不含10號部門自己的雇員
/*
	1.查詢到10號部門有哪些工作崗位
	2.把上面的查詢結果當做是一個子查詢來使用
*/

SELECT DISTINCT job
	FROM emp 
	WHERE deptno=10;

SELECT ename,job,sal,deptno
	FROM emp 
	WHERE job IN( -- 返回了一個集合，用in
		SELECT DISTINCT job
		FROM emp 
		WHERE deptno=10) 
		AND deptno !=10; -- 不含10號部門自己的雇員
image-20221005192645084 image-20221005192706983

3.2all操作符

  • 在多行子查詢中使用all操作符

請思考：顯示工資比部門30所有員工工資高的員工的姓名、工資和部門號

-- 顯示工資比部門30所有員工工資高的 員工的姓名、工資和部門號
SELECT ename,sal,deptno
	FROM emp 
	WHERE sal>ALL(
		SELECT sal
		FROM emp
		WHERE deptno = 30
		)
-- 或者
SELECT ename,sal,deptno
	FROM emp 
	WHERE sal>(
		SELECT MAX(sal)
		FROM emp
		WHERE deptno = 30
		)
image-20221005194826425

3.3any操作符

  • 在多行子查詢中使用any操作符

請思考：如何顯示工資比部門30其中一個員工工資高的 員工的姓名、工資和部門號

-- 請思考：如何顯示工資比部門30其中一個員工工資高的 員工的姓名、工資和部門號
SELECT ename,sal,deptno
	FROM emp 
	WHERE sal>ANY(
		SELECT sal
		FROM emp
		WHERE deptno = 30
		)
		
-- 或者
SELECT ename,sal,deptno
	FROM emp 
	WHERE sal>(
		SELECT MIN(sal)
		FROM emp
		WHERE deptno = 30
		)
image-20221005195212687

3.4子查詢臨時表

  • 子查詢當做一張表來使用

例子

ecshop表：

image-20221005200035647

image-20221005200101349

要求：查詢ecshop中各個類別中價格最高的商品

  1. 先得到各個類別中，價格最高的商品 --當做一個臨時表
image-20221005200655807 image-20221005200509235

  1. 選擇臨時表和原本的表格，過濾條件為

    臨時表的cat_id = 原商品表的cat_id

    && 臨時表的max_price=原商品表的price

image-20221005201843596 image-20221005201928876

3.5多列子查詢

多列子查詢是指查詢返回多個列數據的子查詢語句

語法：

(欄位1，欄位2...) = (select 欄位1 ，欄位2 from ...)

例子

請思考如何查詢與ALLEN的部門和崗位完全相同的所有雇員（並且不包含ALLEN本人）

-- 請思考如何查詢與allen的部門和崗位完全相同的所有雇員（並且不包含Smith本人）
-- 1.得到ALLEN的部門和崗位
	SELECT deptno , job 
		FROM emp
		WHERE ename = 'ALLEN';
		
-- 2.把上面的查詢當做子查詢來使用，並且使用多列子查詢的語法進行匹配
SELECT * FROM emp
	WHERE (deptno,job)=(
		SELECT deptno , job 
		FROM emp
		WHERE ename = 'ALLEN')
		AND ename != 'ALLEN';
image-20221006161223173

練習

-- 請得到和宋江數學、英語、語文成績完全相同的學生
SELECT * FROM student
	WHERE (math,english,chinese)=(
		SELECT math,english,chinese
		FROM student
		WHERE `name`= '宋江'	
	);

3.6子查詢練習

  1. 在from子句中使用子查詢

請思考：查找每個部門工資高於本部門平均工資的人的資料

-- 查找每個部門工資高於本部門平均工資的人的資料

 -- 1.先得到對應的部門號和部門對應的平均工資
SELECT deptno,AVG(sal) FROM emp
	GROUP BY deptno;
	
-- 2.把上面的結果當做子查詢，和emp進行多表查詢

SELECT ename,sal,temp.avg_sal,emp.deptno FROM emp,
		(SELECT deptno,
		AVG(sal) AS avg_sal FROM emp
		GROUP BY deptno) temp
	WHERE emp.deptno=temp.deptno AND emp.sal > avg_sal
image-20221006164236567

  1. 查找每個部門工資最高的人的詳細信息

    -- 查找每個部門工資最高的人的詳細信息
 -- 1. 先得到每個部門的最高工資
 SELECT MAX(sal) FROM emp
	GROUP BY deptno;
	
-- 2.將上面的結果作為子查詢，和emp表進行多表查詢
SELECT ename,sal,temp.max_sal,emp.deptno
	FROM emp,(
		SELECT MAX(sal) AS max_sal FROM emp
		GROUP BY deptno
	) temp
	WHERE emp.sal = temp.max_sal
    image-20221006165300820

  2. 查詢每個部門的信息（包括部門名、編號、地址）和人員數量

-- 查詢每個部門的信息
-- 1.部門名、編號、地址來自dept表
-- 2.各個部門的人員數量  ---》構建一個臨時表	
SELECT COUNT(*),deptno
		FROM emp
		GROUP BY deptno
		
		
SELECT dname,dept.deptno,loc,person_num 
	FROM dept,(
		SELECT COUNT(*) AS person_num,deptno
		FROM emp
		GROUP BY deptno
		) temp
	WHERE dept.deptno = temp.deptno
	
-- 還有一種寫法 表.* 表示將該表所有列都顯示出來,可以簡化SQL語句
-- 在多表查詢中，當多個表的列不重覆時，才可以直接寫列名
SELECT dname,loc,temp.*
	FROM dept,(
		SELECT COUNT(*) AS person_num,deptno
		FROM emp
		GROUP BY deptno
		) temp
	WHERE dept.deptno = temp.deptno
image-20221006172035543
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 《痞子衡嵌入式半月刊》 第 64 期
    痞子衡嵌入式半月刊： 第 64 期 這裡分享嵌入式領域有用有趣的項目/工具以及一些熱點新聞，農曆年分二十四節氣，希望在每個交節之日準時發佈一期。 本期刊是開源項目（GitHub: JayHeng/pzh-mcu-bi-weekly），歡迎提交 issue，投稿或推薦你知道的嵌入式那些事兒。 上期回顧 ...
  • SQL高級（事務和觸發器）
    事務 四大屬性 1，原子性：事務是一個完整的整體，要麼都執行，要麼都不執行 2，一致性：當事務完成時，數據必須處於一致狀態 3，隔離性：對數據修改的所有併發事務是彼此隔離的，即事務必須是獨立的，不應以任何方式依賴於或影響其他 4，永久性：事務完成後，對資料庫的操作永久保留 事務控制 BEGIN：開始 ...
  • day07-1MySQL約束
    MySQL約束 基本介紹 約束用於確保資料庫的數據滿足特定的商業規則 在mysql中，約束包括：not null，unique，primary key，foreign key 和check 5種 1.primary key(主鍵) 欄位名 欄位類型 primary key 用於唯一地標識表行的數據， ...
  • sqlserver學習
    一、什麼是關係型和非關係型資料庫，兩者都包含那種資料庫 1、關係型資料庫 關係型資料庫是指採用了關係模型來組織數據的資料庫。簡單來說，關係模式就是二維表格模型。 常見關係型資料庫管理系統(ORDBMS)： Oracle、MySql、Microsoft SQL Server、 SQLite、Postg ...
  • 淺談Mysql常見註意事項一
    一、CentOS 7.9 安裝 redis-6.2.0 1 下載地址：https://download.redis.io/releases/redis-6.2.0.tar.gz 2 安裝gcc來進行編譯 Redis 由 C語言編寫，所以需要系統中有 gcc 編譯器 使用 gcc --version  ...
  • CentOS 7.9 安裝 mongodb 5.0.13
    一、CentOS 7.9 安裝 mongodb5.0.13 1 下載地址：https://www.mongodb.com/try/download/community2 2 安裝前的準備 # 操作系統內核版本 uname -a # 操作系統發行版本 cat /etc/redhat-release 3 ...
  • CentOS 7.9 安裝 mysql-5.7.35
    一、CentOS 7.9 安裝 mysql-5.7.35 1 下載地址：https://downloads.mysql.com/archives/community/ 2 mysql-5.7.35 安裝包上傳到linux伺服器 使用Xftp 或者 wget 在伺服器上下載 # 推薦使用wget yu ...
  • day06-多表查詢02
    多表查詢02 4.表複製 自我複製數據（蠕蟲複製） 有時，為了對某個sql語句進行效率測試，我們需要海量數據時，可以用此法為表創建海量數據 -- 為了對某個sql語句進行效率測試，我們需要海量數據時，可以用此法為表創建海量數據 CREATE TABLE my_tab01( id INT , `nam ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類