美文网首页
mysql 复杂查询

mysql 复杂查询

作者: 一夜游神 | 来源:发表于2022-09-19 17:12 被阅读0次

    基础数据表

    /*
 Navicat Premium Data Transfer

 Source Server         : 118.107.47.139
 Source Server Type    : MySQL
 Source Server Version : 50732
 Source Host           : icake.top:3306
 Source Schema         : emp

 Target Server Type    : MySQL
 Target Server Version : 50732
 File Encoding         : 65001

 Date: 20/09/2022 17:10:21
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of dept
-- ----------------------------
BEGIN;
INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES (40, 'OPERATIONS', 'BOSTON');
COMMIT;

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(4) NOT NULL COMMENT '员工编号',
  `ename` varchar(10) DEFAULT NULL,
  `job` varchar(9) DEFAULT NULL,
  `mgr` int(4) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` float(7,2) DEFAULT NULL,
  `comm` float(7,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `fk_deptno` (`deptno`),
  CONSTRAINT `fk_deptno` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of emp
-- ----------------------------
BEGIN;
INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20);
INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30);
INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30);
INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20);
INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30);
INSERT INTO `emp` VALUES (7697, 'BLAKE_L', 'MANAGER', 7839, '1981-05-01', 2830.00, NULL, 30);
INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30);
INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10);
INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-07-13', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-07', 5000.00, NULL, 10);
INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30);
INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-07-13', 1100.00, NULL, 20);
INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30);
INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20);
INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10);
COMMIT;

-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '最高工资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资等级';

-- ----------------------------
-- Records of salgrade
-- ----------------------------
BEGIN;
INSERT INTO `salgrade` VALUES (1, 700, 1200);
INSERT INTO `salgrade` VALUES (2, 1201, 1400);
INSERT INTO `salgrade` VALUES (3, 1401, 2000);
INSERT INTO `salgrade` VALUES (4, 2001, 3000);
INSERT INTO `salgrade` VALUES (5, 3001, 9999);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

    题目

    1.  列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
2.  列出公司各个工资等级雇员的数量、平均工资。
3.  列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
4.  列出在每个部门工作的员工数量、平均工资和平均服务期限。
5.  列出所有员工的姓名、部门名称和工资。
6.  列出所有部门的详细信息和部门人数。
7.  列出各种工作的最低工资及从事此工作的雇员姓名。
8.  列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。
9.  列出所有员工的年工资，所在部门名称，按年薪从低到高排序。
10. 查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000
11. 求出部门名称中，带‘S’字符的部门员工的、工资合计、部门人数。
12. 给任职日期超过30年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，依次类推。
13. 列出至少有一个员工的所有部门的信息：
14. 列出薪金比SMITH对的所有员工：
15. 列出所有员工的姓名以及其直接上级的姓名：
16. 列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称
17. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
18. 列出所有"CLERK(职员)"的姓名以及部门名称，部门的人数
19. 列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数
20. 列出在部门"SALES"工作的员工的姓名，假定不知道销售部的部门编号
21. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
22. 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。 
23. 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。 
24. 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。 
25. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。 
26. 列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。 
27. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。 
28. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、平均工资。 
29. 列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号。 
30. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。    
31. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
32. 查询dept表的结构
33. 检索emp表，用is a 这个字符串来连接员工姓名和工种两个字段
34. 检索emp表中有提成的员工姓名、月收入及提成。

    答案

    -- 1、 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
# 按照部门分组 , 求出部门名称和人数
# 多表联查
SELECT
    e.*,
    nt.dname,
    nt.ct 
FROM
    emp e,
    (
    SELECT
        e.deptno,
        dname,
        count( empno ) ct 
    FROM
        emp e,
        dept d 
    WHERE
        e.deptno = d.deptno 
    GROUP BY
        e.deptno,
        d.dname 
    ) nt 
WHERE
    e.deptno = nt.deptno 
    AND job = ( SELECT job FROM emp WHERE ename = 'SCOTT' );

-- 2、 列出公司各个工资等级雇员的数量、平均工资。
SELECT
    salgrade.grade,
    COUNT( emp.empno ),
    AVG( emp.sal ) 
FROM
    salgrade
    JOIN emp ON emp.sal BETWEEN salgrade.losal 
    AND salgrade.hisal 
GROUP BY
    salgrade.grade


-- 3、 列出薪金高于在部门30工作的所有员工的薪金, 的员工姓名和薪金、部门名称。
SELECT emp.ename, emp.sal, dept.dname
FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno
WHERE emp.sal > (SELECT MAX(sal) FROM emp WHERE emp.deptno = 30)

-- 4、 列出在每个部门工作的员工数量、平均工资和平均服务期限。
SELECT deptno, COUNT(ename), AVG(sal), AVG(DATEDIFF(CURRENT_DATE, hiredate))
FROM emp
GROUP BY deptno
-- 5、 列出所有员工的姓名、部门名称和工资。
SELECT emp.ename, dept.dname, emp.sal
FROM emp LEFT JOIN dept on emp.deptno = dept.deptno
-- 6、 列出所有部门的详细信息和部门人数。
SELECT
    dept.*,
    n.ct 
FROM
    dept
    JOIN ( SELECT deptno, COUNT(*) ct FROM emp GROUP BY deptno ) n ON n.deptno = dept.deptno
-- 7、 列出各种工作的最低工资及从事此工作的雇员姓名。
SELECT MIN(sal) min_sal
FROM emp 
GROUP BY emp.job;
SELECT ename, emp.job, min_sal
FROM emp RIGHT JOIN (SELECT MIN(sal) min_sal
FROM emp 
GROUP BY emp.job) n on emp.sal = n.min_sal
-- 8、 列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。
SELECT
    min_sal,
    e.ename,
    dept.dname,
    d_count 
FROM
    emp e
    RIGHT JOIN ( SELECT deptno, MIN( sal ) min_sal FROM emp WHERE job = 'MANAGER' GROUP BY deptno ) m_min ON e.sal = m_min.min_sal
    LEFT JOIN ( SELECT deptno, COUNT(*) d_count FROM emp GROUP BY deptno ) d ON m_min.deptno = d.deptno
    LEFT JOIN dept ON m_min.deptno = dept.deptno 
WHERE
    e.job = 'MANAGER'
-- 9、 列出所有员工的年工资，所在部门名称，按年薪从低到高排序。
SELECT (emp.sal + IFNULL(comm, 0)) * 12 year_sal, dept.dname
FROM emp LEFT JOIN dept on emp.deptno = dept.deptno
ORDER BY year_sal
-- 10、查出某个员工的上级主管及所在部门名称，并要求出这些主管中的薪水超过3000
SELECT e.ename, m.ename, d.dname, m.sal
FROM emp e JOIN emp m on e.mgr = m.empno 
                JOIN dept d on m.deptno = d.deptno
WHERE m.sal > 3000

-- 11、求出部门名称中，带‘S’字符的部门员工的、工资合计、部门人数。
SELECT e.deptno, SUM(e.sal), COUNT(*)
FROM emp e JOIN (SELECT deptno, dname
from dept
WHERE dname like '%S%') d on e.deptno = d.deptno 
GROUP BY e.deptno

-- 12、给任职日期超过30年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%，20部门增长20%，30部门增长30%，依次类推。
SELECT e.ename, e.sal * deptno / 100
FROM emp e
WHERE YEAR(e.hiredate) = 1987 OR (YEAR(CURRENT_DATE) - YEAR(e.hiredate) > 30)
-- 13.列出至少有一个员工的所有部门的信息：
SELECT
    dept.*,
    m_size 
FROM
    dept
    RIGHT JOIN ( SELECT deptno, COUNT(*) m_size FROM emp GROUP BY deptno HAVING m_size > 1 ) ed ON dept.deptno = ed.deptno
-- 14.列出薪金比SMITH多的所有员工：
SELECT * 
FROM emp
WHERE sal > (SELECT sal from emp WHERE ename = 'SMITH')

-- 15.列出所有员工的姓名以及其直接上级的姓名：
SELECT e.ename, m.ename
FROM emp e LEFT JOIN emp m on e.mgr = m.empno

-- 16.列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称
SELECT e.empno, e.ename, d.dname
FROM emp e LEFT JOIN emp m on e.mgr = m.empno JOIN dept d on e.deptno = d.deptno
WHERE e.hiredate < m.hiredate

-- 17.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT d.*, e.*
FROM dept d LEFT JOIN emp e ON e.deptno = d.deptno

-- 18.列出所有"CLERK(职员)"的姓名以及部门名称，部门的人数

SELECT e.ename, d.dname, d_count.d_size
FROM emp e JOIN dept d on e.deptno = d.deptno JOIN (SELECT d.deptno, COUNT(e.empno) d_size
FROM dept d LEFT JOIN emp e on d.deptno = e.deptno
GROUP BY d.deptno) d_count ON d.deptno = d_count.deptno
WHERE e.job = 'CLERK'

-- 19.列出最低薪金大于1500的各种工作以及从事此工作的全部雇员人数
SELECT job, COUNT(*)
FROM emp
GROUP BY job
HAVING MIN(sal) > 1500

-- 20.列出在部门"SALES"工作的员工的姓名，假定不知道销售部的部门编号
SELECT e.ename
FROM emp e JOIN dept d on e.deptno = d.deptno
WHERE d.dname = 'SALES'
-- 21.列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级


SELECT
    e.ename,
    m.ename mgr,
    d.dname,
    sd.grade 
FROM
    emp e
    LEFT JOIN emp m ON e.mgr = m.empno
    JOIN dept d ON e.deptno = d.deptno
    JOIN salgrade sd ON e.sal BETWEEN sd.losal 
    AND sd.hisal 
WHERE
    e.sal > (
    SELECT
        AVG( sal ) 
    FROM
    emp)

-- 22. 列出至少有一个员工的所有部门编号、名称，并统计出这些部门的平均工资、最低工资、最高工资。 
SELECT
    e_count.*,
    d.dname 
FROM
    dept d
    JOIN ( SELECT deptno, COUNT(*) size, AVG( sal ), MIN( sal ), MAX( sal ) FROM emp GROUP BY deptno ) e_count ON d.deptno = e_count.deptno 
WHERE
    size >= 1
-- 23. 列出薪金比“SMITH”或“ALLEN”多的所有员工的编号、姓名、部门名称、其领导姓名。
SELECT e.empno, e.ename,  d.dname, m.ename mname, e.sal
FROM
    emp e
    LEFT JOIN emp m ON e.mgr = m.empno
    JOIN dept d ON e.deptno = d.deptno 
WHERE
    e.sal > (
        SELECT
            MIN( sal ) 
        FROM
            emp 
        WHERE
        ename IN ( "SMITH", "ALLEN" )
    )
-- 24. 列出所有员工的编号、姓名及其直接上级的编号、姓名，显示的结果按领导年工资的降序排列。 
SELECT e.empno, e.ename, m.empno as mno, m.ename as mname
FROM emp e LEFT JOIN emp m ON e.mgr = m.empno
ORDER BY m.sal DESC

-- 25.列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称、部门位置、部门人数。 
SELECT
    e.empno,
    e.ename,
    d.dname,
    d_count.size,
    e.hiredate
FROM
    emp e
    LEFT JOIN emp m ON e.mgr = m.empno
    JOIN dept d ON e.deptno = d.deptno
    JOIN ( SELECT deptno, COUNT(*) size FROM emp GROUP BY deptno ) d_count ON d_count.deptno = d.deptno 
WHERE
    e.hiredate < m.hiredate

-- 26. 列出部门名称和这些部门的员工信息（数量、平均工资），同时列出那些没有员工的部门。 


SELECT
    d.dname,
    d_count.* 
FROM
    dept d
    LEFT JOIN ( SELECT deptno, COUNT( 1 ) size, AVG( sal ) avg_sal FROM emp GROUP BY deptno ) d_count ON d.deptno = d_count.deptno
-- 27. 列出所有“CLERK”（办事员）的姓名及其部门名称，部门的人数，工资等级。 
SELECT
    e.ename,
    d.dname,
    d_count.size,
    s.grade,
    e.job 
FROM
    emp e
    JOIN dept d ON e.deptno = d.deptno
    JOIN salgrade s ON e.sal BETWEEN s.losal 
    AND s.hisal
    JOIN ( SELECT deptno, COUNT( 1 ) size FROM emp GROUP BY deptno ) d_count ON d.deptno = d_count.deptno 
WHERE
    e.job = 'CLERK'

-- 28. 列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数及所在部门名称、位置、平均工资。 

-- 29. 列出在部门“SALES”（销售部）工作的员工的姓名、基本工资、雇佣日期、部门名称，假定不知道销售部的部门编号。 
-- 30. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级。     
-- 31. 列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。
--

    相关文章

      网友评论

          本文标题：mysql 复杂查询

          本文链接：https://www.haomeiwen.com/subject/iadvortx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|mysql 复杂查询|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！