MySQL练习三

emp表、salary表、posMess

Navicat设置字段为null：
右键字段——》设置为null

-- emp表、salary表、posMess
SELECT * FROM emp
SELECT * FROM salary
SELECT * FROM posmess
-- 1．查询每个员工的姓名，职位和工资情况
SELECT emp.employeeName AS 员工,posmess.positionName AS 职位,salary.wage AS 工资
FROM salary
LEFT JOIN emp ON emp.employeeID = salary.employeeID
LEFT JOIN posmess ON salary.position = posmess.positionID


-- 2．查询职位是“Teacher”的员工包括哪些，显示员工姓名，工资
SELECT emp.employeeName AS 姓名,salary.wage AS 工资,posmess.positionName AS 职位
FROM salary
LEFT JOIN emp ON emp.employeeID = salary.employeeID
LEFT JOIN posmess ON salary.position = posmess.positionID
WHERE posmess.positionName = 'Teacher'

-- 3．查询电话号为空的员工是什么职位，显示员工姓名和职位名称
SELECT *
FROM salary
LEFT JOIN emp ON emp.employeeID = salary.employeeID
LEFT JOIN posmess ON salary.position = posmess.positionID
WHERE emp.employeeTel IS NULL

-- 4．查询出所有员工的基本信息及工资信息，显示员工编号，员工姓名，所处职位名称，工资数额，没有工资的员工只显示员工的编号和姓名
SELECT emp.employeeID AS 员工编号,emp.employeeName as 员工姓名,
if(salary.wage is not null,posmess.positionName,'')  AS 职位名称,
if(salary.wage is not null,salary.wage,'' ) AS 工资数额
FROM salary
LEFT JOIN emp ON emp.employeeID = salary.employeeID
LEFT JOIN posmess ON salary.position = posmess.positionID

-- 5．查询工资总额（基本工资+奖金）最高的员工叫什么
SELECT emp.employeeName
FROM emp
LEFT JOIN salary ON emp.employeeID = salary.employeeID
ORDER BY salary.wage DESC
LIMIT 1

-- 6．查询哪个职工目前还没有分配职位(求两个集合的差集)
SELECT t1.employeeID
FROM emp t1
LEFT JOIN (SELECT salary.employeeID FROM salary) t2 ON t1.employeeID = t2.employeeID
WHERE t2.employeeID is NULL


-- 7．查询出最年长的员工是什么职位，工资多少钱
SELECT *
FROM salary
LEFT JOIN emp ON emp.employeeID = salary.employeeID
LEFT JOIN posmess ON salary.position = posmess.positionID
ORDER BY emp.employeeAge DESC
LIMIT 1

emp表的sql文：

/*
 Navicat Premium Data Transfer

 Source Server         : my_project
 Source Server Type    : MySQL
 Source Server Version : 80027
 Source Host           : localhost:3306
 Source Schema         : order

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

 Date: 01/05/2022 13:12:47
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `employeeID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `employeeName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `employeeAge` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `joinDate` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `employeeTel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('DJ001', 'lengyonglin', '30', '2002-02-01', '13811112222');
INSERT INTO `emp` VALUES ('DJ002', 'chenjinping', '29', '2003-10-29', '13944447777');
INSERT INTO `emp` VALUES ('DJ003', 'liufulian', '28', '2005-09-01', '13923456778');
INSERT INTO `emp` VALUES ('DJ004', 'yuzhixin', '29', '2006-04-21', '13344445555');
INSERT INTO `emp` VALUES ('DJ005', 'chenyanli', '29', '2002-09-30', '13698765432');
INSERT INTO `emp` VALUES ('DJ006', 'wangyanmei', '27', '2004-06-22', '13245677654');
INSERT INTO `emp` VALUES ('DJ007', 'donghaiping', '26', '2007-09-01', '13111115555');
INSERT INTO `emp` VALUES ('DJ008', 'zhangguoli', '20', '2007-09-17', NULL);
INSERT INTO `emp` VALUES ('DJ009', 'gutianle', '28', '2007-09-17', '13356789876');

SET FOREIGN_KEY_CHECKS = 1;

salary表的sql文：

/*
 Navicat Premium Data Transfer

 Source Server         : my_project
 Source Server Type    : MySQL
 Source Server Version : 80027
 Source Host           : localhost:3306
 Source Schema         : order

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

 Date: 01/05/2022 13:14:08
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for salary
-- ----------------------------
DROP TABLE IF EXISTS `salary`;
CREATE TABLE `salary`  (
  `salaryID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `employeeID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `position` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `wage` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of salary
-- ----------------------------
INSERT INTO `salary` VALUES ('1', 'DJ001', 'pos1', '2390');
INSERT INTO `salary` VALUES ('2', 'DJ002', 'pos1', '1640');
INSERT INTO `salary` VALUES ('3', 'DJ002', 'pos3', '3180');
INSERT INTO `salary` VALUES ('4', 'DJ003', 'pos3', '5218');
INSERT INTO `salary` VALUES ('5', 'DJ004', 'pos1', '800');
INSERT INTO `salary` VALUES ('6', 'DJ004', 'pos2', '2819');
INSERT INTO `salary` VALUES ('7', 'DJ005', 'pos3', '2367');
INSERT INTO `salary` VALUES ('8', 'DJ006', 'pos3', '4117');
INSERT INTO `salary` VALUES ('9', 'DJ007', 'pos1', '1983');
INSERT INTO `salary` VALUES ('10', 'DJ007', 'pos2', NULL);
INSERT INTO `salary` VALUES ('11', 'DJ008', 'pos2', NULL);
INSERT INTO `salary` VALUES ('12', 'DJ001', 'pos3', NULL);

SET FOREIGN_KEY_CHECKS = 1;

posmess表sql文：

/*
 Navicat Premium Data Transfer

 Source Server         : my_project
 Source Server Type    : MySQL
 Source Server Version : 80027
 Source Host           : localhost:3306
 Source Schema         : order

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

 Date: 01/05/2022 13:14:18
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for posmess
-- ----------------------------
DROP TABLE IF EXISTS `posmess`;
CREATE TABLE `posmess`  (
  `positionID` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `positionName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of posmess
-- ----------------------------
INSERT INTO `posmess` VALUES ('pos1', 'Teacher');
INSERT INTO `posmess` VALUES ('pos2', 'Manager');
INSERT INTO `posmess` VALUES ('pos3', 'Structor');

SET FOREIGN_KEY_CHECKS = 1;