这六个mysql的代码是怎么写的呀

Question

这六个mysql的代码是怎么写的，这几个代码并没有答案不知道是什么

Answer 1

/*
 Navicat Premium Data Transfer

 Source Server         : 127
 Source Server Type    : MySQL
 Source Server Version : 80028
 Source Host           : localhost:3306
 Source Schema         : test

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

 Date: 24/05/2022 18:24:48
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `deptID` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '部门',
  `deptName` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '部门名',
  `place` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '办公地点',
  `managerID` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '部门经理编号',
  PRIMARY KEY (`deptID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('1', '研发部', '北京', '1');
INSERT INTO `dept` VALUES ('2', '财务部', '北京', '1');

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `empID` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '职工号',
  `name` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名',
  `birth` date NOT NULL COMMENT '出生日期',
  `sex` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '性别',
  `phone` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '电话号码',
  `hireDate` date NULL DEFAULT NULL COMMENT '入职日期',
  `PID` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '岗位号',
  `deptID` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '部门号',
  PRIMARY KEY (`empID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES ('1', '刘芳', '1994-01-01', '男', '18695332473', '2022-05-24', '1', '1');
INSERT INTO `employee` VALUES ('2', '张三', '2022-05-23', '女', '15095332473', '2022-05-23', '2', '2');
INSERT INTO `employee` VALUES ('3', '李四', '1994-01-01', '男', '18695332473', '2022-05-24', '1', '1');

-- ----------------------------
-- Table structure for post
-- ----------------------------
DROP TABLE IF EXISTS `post`;
CREATE TABLE `post`  (
  `PID` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '岗位号',
  `Pname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '岗位名',
  `Pdesc` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '岗位描述',
  `Pallowance` decimal(7, 2) NULL DEFAULT 2000.00 COMMENT '岗位津贴',
  PRIMARY KEY (`PID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of post
-- ----------------------------
INSERT INTO `post` VALUES ('1', '后端技术', '掉头发', 2000.00);
INSERT INTO `post` VALUES ('2', '前端技术', '妹子少', 2000.00);

-- ----------------------------
-- Table structure for salary
-- ----------------------------
DROP TABLE IF EXISTS `salary`;
CREATE TABLE `salary`  (
  `SID` int NOT NULL AUTO_INCREMENT COMMENT '薪水号',
  `empID` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '职工号',
  `Bsalary` decimal(7, 2) NOT NULL COMMENT '基本工资',
  `Psalary` decimal(7, 2) NULL DEFAULT NULL COMMENT '岗位津贴',
  `Sdate` date NULL DEFAULT NULL COMMENT '日期',
  PRIMARY KEY (`SID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of salary
-- ----------------------------
INSERT INTO `salary` VALUES (1, '1', 5000.00, 2500.00, '2022-05-24');
INSERT INTO `salary` VALUES (2, '2', 6000.00, 2500.00, '2022-05-24');

SET FOREIGN_KEY_CHECKS = 1;

# （1） 答案

SELECT 
    b.empID,
    b.`name`,
    TIMESTAMPDIFF(YEAR,b.birth,CURDATE()) AS age
FROM post a 
LEFT JOIN employee b ON a.PID = b.PID
where a.Pname LIKE '%技术%' AND b.phone LIKE '186%';

# （2） 答案

SELECT
    b.deptName,
    c.Pname,
    COUNT(a.empID) AS count
FROM employee a
LEFT JOIN dept b ON a.deptID=b.deptID
LEFT JOIN post c ON c.PID=a.PID;

# (3) 答案

SELECT 
    *
FROM employee
where 
PID IN (SELECT PID FROM employee where name='刘芳')
AND deptID IN (SELECT deptID FROM employee where name='刘芳');

#（4）答案

SELECT
a.empID,
a.`name`,
b.Bsalary
FROM employee a
LEFT JOIN salary b ON b.empID=a.empID
where b.Bsalary > (
SELECT Bsalary FROM salary 
where empID=(SELECT empID FROM employee where NAME='刘芳'));

# (5) 
SELECT 
    *
FROM post 
WHERE PID NOT IN (SELECT GROUP_CONCAT(PID) FROM employee);

# (6)
SELECT 
    AVG(a.Bsalary) 
FROM salary a
where empID IN (SELECT empID FROM employee WHERE deptID = (SELECT deptID FROM dept WHERE deptName='研发部'))

第五个可能不合适，你再看看