用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根据经纬查距离