MySQL练习——指定查询条件 WHERE 的使用_mysql选择工资不在5000到12000的员工的姓名和工资-CSDN博客

本文链接：https://blog.csdn.net/weixin_47540149/article/details/129781057

指定查询条件的查询语句格式如下：

SELECT {*|字段列名} FROM 数据表名 WHERE 查询条件;

查询条件可以是：

带比较运算符和逻辑运算符的查询条件
带 BETWEEN AND 关键字的查询条件
带 IS NULL 关键字的查询条件
带 IN 关键字的查询条件
带 LIKE 关键字的查询条件

练习：

-- 1. 查询工资大于12000的员工姓名和工资
SELECT emp_name, salary FROM emps WHERE salary > 12000;

-- 2. 查询员工号为176的员工的姓名和部门号
SELECT emp_name, dept_id FROM emps WHERE emp_id = 176;

-- 3. 选择工资不在5000到12000的员工的姓名和工资
SELECT emp_name, salary FROM emps WHERE salary NOT BETWEEN 5000 and 20000;

-- 4. 选择雇用时间在1998-02-01到1998-05-01之间的员工姓名，job_id和雇用时间
SELECT emp_name, job_id, hiredate FROM emps WHERE hiredate BETWEEN '1998-02-01' AND '1998-05-01';

-- 5. 选择在20或50号部门工作的员工姓名和部门号
SELECT emp_name, dept_id FROM emps WHERE dept_id IN(20,50);

-- 6. 选择在1994年雇用的员工的姓名和雇用时间
SELECT emp_name, hiredate FROM emps WHERE hiredate LIKE '1994%'; 

-- 7. 选择公司中没有管理者的员工姓名及job_id
SELECT emp_name, job_id FROM emps WHERE mgr_id IS NOT NULL;

-- 8. 选择公司中有奖金的员工姓名，工资
SELECT emp_name, salary FROM emps WHERE bonus IS NOT NULL;

-- 9. 选择员工姓名的第三个字母是a的员工姓名
SELECT emp_name FROM emps WHERE emp_name LIKE '__a%';

-- 10. 选择姓名中有字母a和e的员工姓名
SELECT emp_name FROM emps WHERE emp_name LIKE '%a%' AND emp_name LIKE '%e%';

-- 11. 选择部门 30中的所有员工
SELECT emp_name FROM emps WHERE dept_id=30;

-- 12. 列出所有办事员（CLERK）的姓名、编号和部门编号，在Oracle中是区分大小写的，所以此时要么将来 CLERK大写, 要么使用upper函数
SELECT clerk_id, clerk_name, dept_id FROM clerks;

-- 13. 找出佣金高于薪金的员工 comm.字段表示佣金或奖金
SELECT emp_name FROM emps WHERE comm > salary;

-- 14. 找出佣金高出奖金60%的员工
SELECT emp_name FROM emps WHERE comm > salary * 1.6;

-- 15. 查找部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK）的详细资料			
-- AND优先级高于OR
SELECT * FROM emps WHERE dept_id = 10 AND job = 'MANAGER' OR dept_id = 20 AND job = 'CLERK';

-- 16. 查找部门10中所有经理（MANAGER）和部门20中所有办事员（CLERK），既不是经理又不是办事员，但薪金大于或等2000的所有员工的详细资料
-- not > and > or
SELECT * FROM emps WHERE NOT (job = 'MANAGER' OR job = 'CLERK') AND (salary > 2000 OR salary = 2000);

-- 17. 找出收取佣金的员工的不同的工作，工作会出现重复，所以 DISTINCT	 关键字消除重复的列
SELECT DISTINCT job FROM emps WHERE comm IS NOT NULL;

-- 18. 找出不收取佣金或收取的佣金低于100的员工
SELECT emp_name FROM emps WHERE comm < 100 OR comm IS NULL;

以下为数据库各表的创建与新增数据语句，可创建.sql文件粘贴以下数据，使用可视化工具Navicat进行导入

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50722
 Source Host           : localhost:3306
 Source Schema         : db10

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

 Date: 26/03/2023 16:09:39
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `dept_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '部门ID',
  `dept_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名字',
  `mgr_id` int(4) NULL DEFAULT NULL COMMENT '经理ID',
  `location_id` int(4) NULL DEFAULT NULL COMMENT '位置ID',
  PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (1, '行政部', 3, 1);
INSERT INTO `dept` VALUES (2, '技术部', 4, 1);
INSERT INTO `dept` VALUES (3, '销售部', 7, 2);
INSERT INTO `dept` VALUES (4, '后勤部', 8, 3);
INSERT INTO `dept` VALUES (5, '保洁部', NULL, 3);

-- ----------------------------
-- Table structure for emps
-- ----------------------------
DROP TABLE IF EXISTS `emps`;
CREATE TABLE `emps`  (
  `emp_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '员工工号',
  `emp_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
  `emp_sex` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工性别',
  `emp_age` int(3) NULL DEFAULT NULL COMMENT '员工年龄',
  `dept_id` int(4) NULL DEFAULT NULL COMMENT '部门',
  `hiredate` date NULL DEFAULT NULL COMMENT '入职时间',
  `job_id` int(4) NULL DEFAULT NULL COMMENT '职位ID',
  `salary` double(8, 2) NULL DEFAULT NULL COMMENT '薪资',
  `mgr_id` int(4) NULL DEFAULT NULL COMMENT '经理工号',
  `comm` double(7, 2) NULL DEFAULT NULL COMMENT '奖金',
  PRIMARY KEY (`emp_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emps
-- ----------------------------
INSERT INTO `emps` VALUES (1, 'Tom', '男', 28, 1, '2020-06-03', 1, 5600.00, NULL, 1500.00);
INSERT INTO `emps` VALUES (2, 'Jack', '男', 30, 1, '2010-05-06', 2, 8900.00, 1, NULL);
INSERT INTO `emps` VALUES (3, 'Bob', '男', 43, 2, '2010-05-06', 3, 12200.00, 1, 1500.00);
INSERT INTO `emps` VALUES (4, 'Rose', '女', 27, 2, '1998-02-05', 5, 8000.00, 2, NULL);
INSERT INTO `emps` VALUES (5, 'Ann', '男', 54, 3, '2010-05-06', 2, 6800.00, 3, 200.00);
INSERT INTO `emps` VALUES (6, 'Lucy', '女', 19, NULL, '1994-05-06', 2, 7300.00, 3, 30.00);
INSERT INTO `emps` VALUES (7, 'Cindy', '女', 29, 4, '2010-05-06', 1, 8000.00, 4, NULL);
INSERT INTO `emps` VALUES (8, 'Lily', '女', 32, 4, '2010-05-06', 3, 11000.00, 4, 100.00);

-- ----------------------------
-- Table structure for job
-- ----------------------------
DROP TABLE IF EXISTS `job`;
CREATE TABLE `job`  (
  `job_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '职位ID',
  `job_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '职位名称',
  PRIMARY KEY (`job_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of job
-- ----------------------------
INSERT INTO `job` VALUES (1, '职员');
INSERT INTO `job` VALUES (2, '组长');
INSERT INTO `job` VALUES (3, '经理');
INSERT INTO `job` VALUES (4, '部长');
INSERT INTO `job` VALUES (5, '库长');

-- ----------------------------
-- Table structure for location
-- ----------------------------
DROP TABLE IF EXISTS `location`;
CREATE TABLE `location`  (
  `location_id` int(4) NOT NULL AUTO_INCREMENT COMMENT '位置ID',
  `city` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '城市',
  PRIMARY KEY (`location_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of location
-- ----------------------------
INSERT INTO `location` VALUES (1, '北京');
INSERT INTO `location` VALUES (2, '上海');
INSERT INTO `location` VALUES (3, '大连');

-- ----------------------------
-- Table structure for sal_val
-- ----------------------------
DROP TABLE IF EXISTS `sal_val`;
CREATE TABLE `sal_val`  (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `level` int(4) NULL DEFAULT NULL,
  `min_val` int(8) NULL DEFAULT NULL,
  `max_val` int(8) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sal_val
-- ----------------------------
INSERT INTO `sal_val` VALUES (1, 1, 2000, 10000);
INSERT INTO `sal_val` VALUES (2, 2, 10001, 20000);
INSERT INTO `sal_val` VALUES (3, 3, 30001, 80000);

SET FOREIGN_KEY_CHECKS = 1;