用Mysql進行emp、dept、salgrade表的相關查詢操作

来源:https://www.cnblogs.com/ncl-960301-success/archive/2019/04/27/10778154.html
-Advertisement-
Play Games

初學者都會接觸到三種表：emp、dept、salgrade表，進行練習各種語句操作再合適不過 但是，網上大多數的操作語句都是用oracle進行操作的，小編在學習mysql的時候，參考網上的書寫遇到了不少問題 都是由於oracle語句和mysql語句的不相容的引起的。 寫多行sql語句的時候或者嵌套查 ...


初學者都會接觸到三種表：emp、dept、salgrade表，進行練習各種語句操作再合適不過

但是，網上大多數的操作語句都是用oracle進行操作的，小編在學習mysql的時候，參考網上的書寫遇到了不少問題

都是由於oracle語句和mysql語句的不相容的引起的。

寫多行sql語句的時候或者嵌套查詢的時候，切記，分行與縮進，條理清晰

大家在學習的時候，註意看小編的書寫格式和書寫縮進，同一級的SELECT、WHERE儘量對其，子級的要縮進，避免干擾

簡單的語句給出答案即可，複雜的語句，會給出書寫思路的

一：單表查詢

 1 -- 1)    試用SQL語言完成下列查詢(單表查詢)：
 2 -- a)    查詢20號部門的所有員工信息：
 3 SELECT * FROM emp WHERE deptno = 20;
 4 -- b)    查詢獎金（COMM）高於工資（SAL）的員工信息：
 5 SELECT * FROM emp WHERE comm > sal;
 6 -- c)    查詢獎金高於工資的20%的員工信息:
 7 SELECT * FROM emp WHERE comm > sal*0.2;
 8 -- d)    查詢10號部門中工種為MANAGER和20號部門中工種為CLERK的員工的信息:
 9 SELECT * FROM emp WHERE (job = 'manager' AND deptno = 10) OR (job = 'clerk' AND deptno = 20);
10 -- e)    查詢所有工種不是MANAGER和CLERK，且工資大於或等於2000員工的詳細信息：
11 SELECT * FROM emp WHERE (sal >= 2000) AND (job NOT IN('manager','clerk'));
12 -- f)    查詢沒有獎金或獎金低於100的員工信息:
13 SELECT * FROM emp WHERE (comm < 100 ) OR comm IS NULL;
14 -- g)    查詢員工工齡大於或等於10年的員工信息:
15 SELECT * FROM emp WHERE (NOW() - hiredate) >= 10;
16 -- h)    查詢員工信息，要求以首字母大寫的方式顯示所有員工的姓名:
17 SELECT CONCAT(UPPER(SUBSTRING(ename,1,1)),LOWER(SUBSTRING(ename,2,(CHAR_LENGTH(ename)-1))))FROM emp;

（h）解析：用到的五個函數：

　　upper（）：將字元串大寫處理

　　lower（）：將字元串小寫處理

　　substring(ename， 1 ， 1 )：從一段字元串中截取字元串，例如：smith：

　　　　　　　substring（Smith，1，1）：表示：從第一個字元開始，截取1個字元，結果就是：s

　　　　　　　substring（smith，2，（char_length('smith')-1））：表示：從第二個字元開始，截取字元長度-1個字元，即除去首字母剩下的字元，結果：mith:

　　char_length():獲取指定字元串的的長度

　　concat(str1，str2);拼接兩個字元串

-- i)    查詢在2月份入職的所有員工信息:
SELECT * FROM emp WHERE hiredate LIKE '%-02-%';
-- j)    顯示所有員工的姓名、入職的年份和月份，按入職日期所在的月份排序，若月份相同則按入職的年份排序:
SELECT ename,SUBSTR(hiredate FROM 1 FOR 7) FROM emp ORDER BY SUBSTR(hiredate FROM 6 FOR 2),SUBSTRING(hiredate FROM 1 FOR 4);

二：多表查詢

-- a)    查詢從事同一種工作但不屬於同一部門的員工信息:
SELECT e.* FROM emp e
CROSS JOIN emp m
ON (e.job = m.job) AND (e.deptno <> m.deptno);
-- b)    查詢各個部門的詳細信息以及部門人數、部門平均工資:
SELECT d.*,COUNT(e.ename),AVG(e.sal) FROM emp e,dept d
WHERE e.deptno = d.deptno
GROUP BY d.deptno,d.dname;

多表查詢不是很難，不做過多的解釋，不懂得，可以留言或者查看小編的前兩篇隨筆，有解釋的。

三：嵌套子查詢

-- a)    查詢10號部門員工以及領導的信息:
-- 不相關子查詢
SELECT * FROM emp
WHERE empno IN(SELECT empno FROM emp WHERE deptno = 10);
-- b)    查詢工資為某個部門平均工資的員工信息:
-- 不相關子查詢
SELECT * FROM emp
WHERE sal IN(SELECT AVG(sal) FROM emp  GROUP BY deptno);
-- c)    查詢工資高於本部門平均工資的員工的信息: 
-- 相關子查詢
SELECT * FROM emp e
WHERE sal > (SELECT AVG(sal) FROM emp WHERE e.deptno = deptno GROUP BY deptno);
-- d)    查詢工資高於本部門平均工資的員工的信息及其部門的平均工資:
-- 不相關子查詢
SELECT e.*,a.avgsal 
FROM emp e,(SELECT deptno,AVG(sal) avgsal FROM emp GROUP BY deptno) a 
WHERE e.sal > a.avgsal AND e.deptno = a.deptno;

嵌套子查詢，不是很難，如果有不懂得，看小編下一篇文章，這篇文章主要以語句練習為主，不做太多的知識講解。

四：使用聚合函數進行查詢（重點介紹）

 常見的五個聚合函數：sum()、count（）、max（）、min（）、avg（）。聚合函數通常與group  by 子句一起使用

 

-- a)    統計各個工種的人數與平均工資:
SELECT job,COUNT(ename),AVG(sal) FROM emp GROUP BY job;

  -- b) 統計每個部門中各個工種的人數與平均工資:
SELECT deptno,job,COUNT(*),AVG(sal) FROM emp GROUP BY deptno,job;

註：很簡單，不做解釋

 

-- c)    查詢人數最多的部門信息：

有兩種方法：

方法一：利用分頁 語句

-- 1、查詢出各部門的人數, 按人數進行降序排序
SELECT deptno,count(*) count FROM emp GROUP BY deptno ORDER BY count desc ;
-- 2、分頁查詢，只顯示第一行，即最大的人數部門
SELECT deptno,count(*) count FROM emp GROUP BY deptno ORDER BY count desc  LIMIT 1;
-- 3、將上面的查詢結果當做一張表，聯合部門表進行查詢
SELECT * FROM dept d WHERE d.deptno =(
                SELECT deptno  FROM emp GROUP BY deptno ORDER BY count(*) desc  LIMIT 1);

方法二：較麻煩

WHERE deptno IN(
            SELECT b.deptno FROM (SELECT COUNT(*) count,deptno FROM emp GROUP BY deptno) b
            WHERE 
            b.count IN (
                        SELECT MAX(a.count)FROM (SELECT COUNT(*) count,deptno FROM emp GROUP BY deptno ) a));
-- d)    部門平均薪水最高的部門編號:

有兩種方法：

方法一：利用分頁查詢：

-- 1、查詢各部門的平均薪水,併進行降序排序
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC ;
-- 2、分頁查詢，取出第一行數據，即最高的平均薪水部門編號
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC LIMIT 1;
-- 3、經上面數據當做一張表，只顯示部門編號
SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1;

方法二：

SELECT deptno FROM dept
WHERE deptno IN(
            SELECT b.deptno FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) b
            WHERE
            b.avgsal IN(
                         SELECT MAX(a.avgsal)FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) a));
-- e)    部門平均薪水最高的部門名稱:

有兩種方法：

方法一：利用分頁

-- 1、查詢各部門的平均薪水,併進行降序排序
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC ;
-- 2、分頁查詢，取出第一行數據，即最高的平均薪水部門編號
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal DESC LIMIT 1;
-- 3、將上面的查詢結果當做一張表，聯合部門表，查詢最終結果
SELECT dname FROM dept d WHERE d.deptno = (
            SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 1);

方法二：

SELECT dname FROM dept
WHERE
deptno IN(
            SELECT b.deptno FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) b
            WHERE
                b.avgsal IN(
                        SELECT MAX(a.avgsal)FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) a));
-- f)    平均薪水最低的部門的部門名稱:

有兩種方法：

方法一：利用分頁

-- 1、查詢各部門的平均薪水,併進行升序排序
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal ;
-- 2、分頁查詢，取出第一行數據，即最低的平均薪水部門編號
SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno ORDER BY avgsal LIMIT 1;
-- 3、將上面的查詢結果當做一張表，聯合部門表，查詢最終結果
SELECT dname FROM dept d WHERE d.deptno = (
            SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal)  LIMIT 1);

方法二：

SELECT dname FROM dept
WHERE
deptno IN(
            SELECT b.deptno FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) b
            WHERE
            b.avgsal IN(
                       SELECT MIN(a.avgsal)FROM (SELECT AVG(sal) avgsal,deptno FROM emp GROUP BY deptno) a));
-- g)    平均薪水等級最低的部門的部門名稱:
SELECT d.dname FROM dept d
WHERE 
d.deptno IN
            (SELECT a.deptno FROM (SELECT e.deptno FROM emp e,salgrade s
                                   WHERE (e.sal BETWEEN s.losal AND s.hisal)GROUP BY e.deptno ORDER BY avg(s.grade)) a) LIMIT 1;
-- h)    部門經理人中，薪水最低的部門名稱:

方法一：

-- 1、查詢部門經理的薪水及其所屬的部門編號
SELECT deptno,job,MIN(sal) FROM emp WHERE job = 'manager' GROUP BY deptno;
-- 2、分頁查詢，升序排列，只顯示第一行數據，即工資最低的部門經理
SELECT deptno FROM emp  WHERE job = 'manager' GROUP BY deptno ORDER BY MIN(sal) LIMIT 1 ;
-- 3、將上面結果當做一張表，聯合部門表
SELECT dname FROM dept d WHERE d.deptno = (
                SELECT deptno FROM emp  WHERE job = 'manager' GROUP BY deptno ORDER BY MIN(sal) LIMIT 1 );

方法二：

select dname from dept d where d.deptno IN

(select deptno from emp where job='MANAGER' group by deptno order by min(sal));
-- i)    比普通員工的最高薪水還要高的經理人名稱:
-- 1、查詢普通員工的最高薪水
SELECT MAX(sal) FROM emp WHERE job NOT IN('manager','president');
-- 2、查詢
SELECT ename,sal FROM emp 
    WHERE  sal >(SELECT MAX(sal) FROM emp WHERE job NOT IN('manager','president'))
        AND job = 'manager' OR job = 'president';

五：嵌套子查詢

-- a)    查詢所有【員工工資都大於1000】的部門的信息:
            -- 1、查詢員工工資小於1000的員工編號,及其部門編號
            SELECT deptno FROM emp WHERE sal < 1000 GROUP BY deptno;
            -- 2、將上面的查詢結果當做一張表，查詢部門編號不在裡面的部門編號
            SELECT d.* FROM dept d 
            WHERE d. deptno NOT IN(SELECT deptno FROM emp WHERE sal < 1000 GROUP BY deptno);
-- b)    查詢所有員工工資都大於1000的部門的信息及其員工信息:
        -- 1、查詢員工工資小於1000的員工編號,及其部門編號
        SELECT deptno FROM emp WHERE sal < 1000 GROUP BY deptno;
        -- 2、聯合查詢，聯合部門表
        SELECT * FROM emp e 
        JOIN dept d
        ON e.deptno = d.deptno 
        WHERE e.deptno NOT IN(SELECT deptno FROM emp WHERE sal < 1000 GROUP BY deptno);
-- c)    查詢所有員工工資都在900~3000之間的部門的信息: 
        -- 1、查詢員工工資都在900-3000的部門編號
        SELECT deptno,MAX(sal),MIN(sal) FROM emp GROUP BY deptno HAVING MAX(sal) <= 3000 AND MIN(sal) >=900;
        -- 2、根據上面查詢的部門編號，查詢出部門信息
        SELECT * from dept d WHERE d.deptno IN(
                    SELECT deptno FROM emp GROUP BY deptno HAVING MAX(sal) <= 3000 AND MIN(sal) >=900); 
-- d)    查詢所有工資都在900~3000之間的員工所在部門的員工信息:
        SELECT * FROM emp e
        WHERE e.deptno = (SELECT deptno FROM emp GROUP BY deptno HAVING MAX(sal) <= 3000 AND MIN(sal) >=900);
-- e)    查詢每個員工的領導所在部門的信息：
        -- 1、查詢每個員工的領導，及其部門編號
        SELECT DISTINCT e2.deptno FROM emp e1,emp e2 WHERE e1.mgr = e2.empno;
        -- 2、根據部門編號，查出部門信息
        SELECT * FROM dept 
        WHERE deptno IN(SELECT DISTINCT e2.deptno FROM emp e1,emp e2 WHERE e1.mgr = e2.empno);
-- f)    查詢30號部門中工資排序前3名的員工信息:
        -- 1、查詢30號部門的員工工資
        SELECT * FROM emp WHERE deptno = 30 ORDER BY sal DESC
        -- 2、分頁查詢，查出前三名即可
        SELECT  e.* from (SELECT * FROM emp WHERE deptno = 30 ORDER BY sal DESC ) e LIMIT 3;
-- g)    查詢工資等級為2級，1985年以後入職的工作地點為DALLAS的員工編號、姓名和工資：
SELECT e.empno,e.ename,e.sal,e.hiredate
FROM emp e,dept d,salgrade s
WHERE (e.sal BETWEEN  losal AND hisal) AND s.GRADE = 2
AND e.hiredate>'1985' 
AND d.loc = 'dallas';
-- 6)    用SQL語句完成下列操作：
-- a)    將各部門員工的工資修改為該員工所在部門平均工資加1000:
UPDATE emp b
SET sal=(SELECT sal FROM (SELECT deptno,avg(sal)+1000 sal FROM emp GROUP BY deptno) a 
WHERE a.deptno=b.deptno)
SELECT * FROM emp;
-- b)    更新員工工資,獎金為他的主管的工資,獎金:
UPDATE emp e SET (sal,comm)=(SELECT sal,comm FROM emp WHERE empno=mgr);
SELECT * FROM emp

 


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

-Advertisement-
Play Games
更多相關文章
  • 存放臨時文件的小工具Yoink Mac版
    Yoink Mac版是專門為電腦桌面小的用戶而設計的一款存放臨時文件的小工具，界面簡潔，幾乎任何在Mac桌面上可以移動的文件都可以拖放到Yoink里。當您需要的時候，它又會替您釋放出來，您可以使用快捷鍵，讓您的操作更迅速。 yoink mac軟體功能丟掉一切。Yoink為您想要移動或複製的文件和應用 ...
  • STM32學習筆記（二）：GPIO口工作原理
    STM32每個IO口具有7個寄存器來控制，每個IO口都可以自由進行編程式控制制，我們編程實際上控制的是通過控制那7個寄存器來控制我們的IO口，我們可以通過編程式控制制IO口，把IO口配置成如下八種模式： 1、輸入浮空2、輸入上拉3、輸入下拉4、模擬輸入5、開漏輸出6、推輓輸出7、推輓式復用功能8、開漏復用功 ...
  • MySQL讀取Binlog日誌常見的3種錯誤
    1. mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4' 當我們在my.cnf中添加default-character-set=utf8mb4選項，那麼在mysqlbinlog查看binlog時就會報錯。 解決方 ...
  • Hive的原理
    閱讀目錄 一、Hive內部表和外部表 1、Hive的create創建表的時候，選擇的創建方式： - create table - create external table 2、特點： ● 在導入數據到外部表，數據並沒有移動到自己的數據倉庫目錄下，也就是說外部表中的數據並不是由它自己來管理的！而表則 ...
  • 資料庫概論_3標準化的SQL語言_ 數據定義
    Student: Course: SC: 1.資料庫的定義、刪除 1.1資料庫的定義（創建） 1 CREATE DATABASE student; 1.2資料庫的刪除 1 DROP DATABASE student; 2.表的定義、修改與刪除 2.1表的定義 建表語句： 1 CREATE TABLE ...
  • mysql主從複製搭建
    原理： MySQL主從複製涉及到三個線程，一個運行在主節點（log dump thread），其餘兩個(I/O thread, SQL thread)運行在從節點，如下圖所示: l 主節點 binary log dump 線程 當從節點連接主節點時，主節點會創建一個log dump 線程，用於發送b ...
  • 如何購買並配置linux伺服器上的資料庫
    首先百度搜索阿裡雲 如果是學生可以學生認證 然後註冊賬號->個人認證->學生認證 然後你會發現 伺服器一年只要114，114你買不了上當，買不了吃虧，買下麵的ECS伺服器，系統可以選擇window也可以選擇linux，編者用的centos7。 當然輕量級的應用伺服器也是可以的，這些輕量級伺服器會預裝 ...
  • Mysql的子查詢相關知識，少但是精
    Mysql子查詢 概念分析： 根據相關性分： （1）不相關子查詢：一條Sql語句中含有多條SELECT語句，先執行子查詢，再執行外查詢，子查詢可對立運行 關鍵字：（1）先子查詢，再外查詢 （2）可以對立運行，即可以單獨運行子查詢，對外查詢不幹擾 （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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類