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

初學者都會接觸到三種表：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 > sal0.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) 查詢工資為某個部門平均工資的員工資訊:

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

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) 部門平均薪水最高的部門名稱:

方法一：利用分頁

-- 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;

SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal)  LIMIT 1);

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 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);

原文位址

https://www.cnblogs.com/ncl-960301-success/p/10778154.html

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

繼續閱讀

oracle中的start with connect by用法

JDBC連接配接資料庫（statement）

MyBatis-Plus 之AR模式

ASP程式設計經典例子

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離