Mysql练习-梦境学习站

一、数据库设计

按照下列要求设计表,并设计表约束

说明:

部门表和雇员表是一对多关系
薪水等级表和雇员表是一对多关系
数据类型合理设置
正确设置表键约束。

二、sql查询总览

查询薪水比”smith”高的员工信息。
查询“ACCOUNTING”部门下所有的员工及其薪水等级。
查询入职时间早于上级领导的所有员工信息。
查询薪水高于30号部门所有员工薪水并且不在30号部门的员工信息及薪水。
查询平均工资最低的部门信息(只统计领导信息)
查询不是领导的员工信息。
查询薪水最高的员工信息(并列时都展示)
查询最后入职的5名员工的信息
查询薪水大于所在部门平均工资的员工信息及其所在部门信息。
查询“SALES”部门的平均薪水的薪水等级。

三、查询语句与解析

1、查询薪水比”smith”高的员工信息

#查询大于"SMITH"工资的员工姓名
SELECT ename FROM emp WHERE sal >
#查询名字是"SMITH"的员工的工资
  (SELECT sal FROM emp WHERE ename = "SMITH")

2、查询“ACCOUNTING”部门下所有的员工及其薪水等级

SELECT ename,grade FROM 
#查询"ACCOUNTING"部门下所有员工信息
  (SELECT * FROM emp LEFT JOIN dept USING(deptno) 
    WHERE dname="ACCOUNTING") AS eld,salgrade 
#条件：薪金在losal和hisal之间
      WHERE eld.sal BETWEEN salgrade.losal AND salgrade.hisal

在left join（左连接）语句中，用using查的数据deptno字段不会重复

这里由于薪水等级是一个区间，所以我们用between and语句

3、查询入职时间早于上级领导的所有员工信息

SELECT * FROM emp eper WHERE eper.hiredate<
#查询员工领导的入职时间
  (SELECT hiredate FROM emp emgr WHERE emgr.empno=eper.mgr)

4、查询薪水高于30号部门所有员工薪水并且不在30号部门的员工信息及薪水

SELECT * FROM emp a WHERE a.sal>
#查询30号部门员工的最大薪水
  (SELECT MAX(sal) FROM emp b WHERE b.deptno=30)

由于题目要求高于30号部门所有员工薪水，可以认为是最大薪水，而且高于最大薪水肯定不属于其部门，所以后半句条件省略即可。

5、查询平均工资最低的部门信息(只统计领导信息)

#方法一 用min函数查询
SELECT DISTINCT c.* FROM emp c,
#查询最低部门的领导编号
  (SELECT mgr FROM emp a,
#查询最低工资的部门信息
    (SELECT ROUND(AVG(sal),3) sala,deptno FROM emp GROUP BY deptno HAVING sala=
#查询最低平均工资
      (SELECT MIN(sal_tb.avg_sal) min_sal FROM 
#查询部门的平均工资
        (SELECT deptno,ROUND(AVG(sal),3) avg_sal FROM emp GROUP BY deptno) AS sal_tb)) b
      WHERE a.deptno = b.deptno) d
    WHERE c.empno=d.mgr 

#方法二 用limit语句查询（最小，可以认为是升序排序后的第一行数据）		
SELECT * FROM emp WHERE empno in 
#查询最低部门的领导编号
  (SELECT DISTINCT mgr from emp where deptno = 
#查询最低工资的部门
    (SELECT deptno  FROM emp GROUP BY deptno ORDER BY AVG(sal) LIMIT 1))

这里两种方法大致思路是一致的。

查询顺序都是：平均工资–>最低工资–>部门编号–>领导编号–>领导信息

只不过第二种方法巧用了limit语句，很快查出了最低工资的部门编号，比方法一省去了不少步骤。

注意：第二种方法要按题目要求，升序降序（desc和asc）要把握准确。

6、查询不是领导的员工信息

#方法一
SELECT * FROM emp a WHERE a.empno NOT IN
  (SELECT DISTINCT mgr FROM emp b WHERE b.mgr IS NOT NULL)
#方法二	
SELECT * from emp where empno not in 
  (SELECT DISTINCT e1.empno from emp e1,emp e2 where e1.empno = e2.mgr)

7、查询薪水最高的员工信息(并列时都展示)

SELECT * FROM emp a,
  (SELECT MAX(sal) max_sal FROM emp) AS b
    WHERE a.sal=b.max_sal

这道题就不能用limit语句了，因为在并列情况时，limit无法判断。

8、查询最后入职的5名员工的信息

SELECT * FROM emp ORDER BY hiredate DESC LIMIT 5

9、查询薪水大于所在部门平均工资的员工信息及其所在部门信息

SELECT a.*,dname,loc FROM emp a LEFT JOIN dept b USING (deptno),
#查询部门平均工资
  (SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno) AS avg_tb
#要判断是否是本部门员工，在判断工资是否大于平均工资
    WHERE a.deptno=avg_tb.deptno AND a.sal>avg_tb.avg_sal

10、查询“SALES”部门的平均薪水的薪水等级

SELECT s.grade FROM 
#查询“SALES”部门的平均薪水
  (SELECT AVG(sal) sal_avg FROM emp a LEFT JOIN dept b USING (deptno) ,salgrade s 
    WHERE b.dname="SALES") AS sal_tb,salgrade s
      WHERE sal_tb.sal_avg BETWEEN s.losal AND s.hisal

附：源码

全部SQL语句

#查询薪水比”smith”高的员工信息
SELECT ename FROM emp WHERE sal >
		(SELECT sal FROM emp WHERE ename = "SMITH") 
		
#查询“ACCOUNTING”部门下所有的员工及其薪水等级。
SELECT ename,grade FROM 
	(SELECT * FROM emp LEFT JOIN dept USING(deptno) 
			WHERE dname="ACCOUNTING") AS eld,salgrade 
				WHERE eld.sal BETWEEN salgrade.losal AND salgrade.hisal

#查询入职时间早于上级领导的所有员工信息。
SELECT * FROM emp eper WHERE eper.hiredate<
	(SELECT hiredate FROM emp emgr WHERE emgr.empno=eper.mgr)
	
#查询薪水高于30号部门所有员工薪水并且不在30号部门的员工信息及薪水。
SELECT * FROM emp a WHERE a.sal>
		(SELECT MAX(sal) FROM emp b WHERE b.deptno=30)

#查询平均工资最低的部门信息(只统计领导信息)
	SELECT DISTINCT c.* FROM emp c,
		(SELECT mgr FROM emp a,
			(SELECT ROUND(AVG(sal),3) sala,deptno FROM emp GROUP BY deptno HAVING sala=
				(SELECT MIN(sal_tb.avg_sal) min_sal FROM 
					(SELECT deptno,ROUND(AVG(sal),3) avg_sal FROM emp GROUP BY deptno) AS sal_tb)) b
			WHERE a.deptno = b.deptno) d
		WHERE c.empno=d.mgr 
		
	SELECT * FROM emp WHERE empno in 
		(SELECT DISTINCT mgr from emp where deptno = 
			(SELECT deptno  FROM emp GROUP BY deptno ORDER BY AVG(sal) LIMIT 1)) 
		
#查询不是领导的员工信息。
SELECT * FROM emp a WHERE a.empno NOT IN
	(SELECT DISTINCT mgr FROM emp b WHERE b.mgr IS NOT NULL)
	
SELECT * from emp where empno not in 
	(SELECT DISTINCT e1.empno from emp e1,emp e2 where e1.empno = e2.mgr)
	
#查询薪水最高的员工信息(并列时都展示)
SELECT * FROM emp a,
	(SELECT MAX(sal) max_sal FROM emp) AS b
	WHERE a.sal=b.max_sal
	
#查询最后入职的5名员工的信息
SELECT * FROM emp ORDER BY hiredate DESC LIMIT 5

#查询薪水大于所在部门平均工资的员工信息及其所在部门信息。
SELECT a.*,dname,loc FROM emp a LEFT JOIN dept b USING (deptno),
	(SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno) AS avg_tb
		WHERE a.deptno=avg_tb.deptno AND a.sal>avg_tb.avg_sal
		
#查询“SALES”部门的平均薪水的薪水等级。
SELECT s.grade FROM 
	(SELECT AVG(sal) sal_avg FROM emp a LEFT JOIN dept b USING (deptno) ,salgrade s 
		WHERE b.dname="SALES") AS sal_tb,salgrade s
	WHERE sal_tb.sal_avg BETWEEN s.losal AND s.hisal

emp表

/*
 Navicat Premium Data Transfer

 Source Server         : Godgy
 Source Server Type    : MySQL
 Source Server Version : 80020
 Source Host           : localhost:3306
 Source Schema         : mydb

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

 Date: 08/09/2022 23:26:02
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `empno` int NOT NULL,
  `ename` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `job` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `mgr` int NULL DEFAULT NULL,
  `hiredate` datetime NULL DEFAULT NULL,
  `sal` decimal(10, 2) NULL DEFAULT NULL,
  `comm` decimal(10, 2) NULL DEFAULT NULL,
  `deptno` int NULL DEFAULT NULL,
  PRIMARY KEY (`empno`) USING BTREE,
  INDEX `FK_DEPTNO`(`deptno` ASC) USING BTREE,
  CONSTRAINT `FK_DEPTNO` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

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

SET FOREIGN_KEY_CHECKS = 1;

dept表

/*
 Navicat Premium Data Transfer

 Source Server         : Godgy
 Source Server Type    : MySQL
 Source Server Version : 80020
 Source Host           : localhost:3306
 Source Schema         : mydb

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

 Date: 08/09/2022 23:26:12
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `deptno` int NOT NULL,
  `dname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `loc` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`deptno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of dept
-- ----------------------------
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');

SET FOREIGN_KEY_CHECKS = 1;

salgrade表

/*
 Navicat Premium Data Transfer

 Source Server         : Godgy
 Source Server Type    : MySQL
 Source Server Version : 80020
 Source Host           : localhost:3306
 Source Schema         : mydb

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

 Date: 08/09/2022 23:26:23
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade`  (
  `grade` int NOT NULL,
  `losal` int NULL DEFAULT NULL,
  `hisal` int NULL DEFAULT NULL,
  PRIMARY KEY (`grade`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of salgrade
-- ----------------------------
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);

SET FOREIGN_KEY_CHECKS = 1;

------本页内容已结束，喜欢请分享------

感谢您的来访，获取更多精彩文章请收藏本站。

文章版权声明 1、本网站名称：梦境学习站
2、本站永久网址：https://blog.godgy.xyz
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长QQ361080778进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END