一、准备数据
sql语句
/*
Navicat MySQL Data Transfer
Source Server : lzl
Source Server Version : 50540
Source Host : localhost:3306
Source Database : yggl
Target Server Type : MYSQL
Target Server Version : 50540
File Encoding : 65001
Date: 2023-04-13 12:20:19
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`dno` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门编号',
`dname` varchar(255) DEFAULT NULL COMMENT '部门名称',
`comment` varchar(255) DEFAULT NULL,
PRIMARY KEY (`dno`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES ('1', '财务部', null);
INSERT INTO `dept` VALUES ('2', '人力资源部', null);
INSERT INTO `dept` VALUES ('3', '经理办公室', null);
INSERT INTO `dept` VALUES ('4', '研发部', null);
INSERT INTO `dept` VALUES ('5', '市场部', null);
-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`eno` char(6) NOT NULL COMMENT '员工编号',
`ename` varchar(255) DEFAULT NULL COMMENT '员工姓名',
`degree` varchar(255) DEFAULT NULL COMMENT '学历',
`birthday` date DEFAULT NULL COMMENT '出生日期',
`sex` char(1) DEFAULT NULL COMMENT '性别',
`workyears` int(11) DEFAULT NULL COMMENT '工作年限',
`address` varchar(255) DEFAULT NULL COMMENT '地址',
`tel` varchar(255) DEFAULT NULL COMMENT '电话号码',
`deptno` int(11) DEFAULT NULL COMMENT '员工部门号',
PRIMARY KEY (`eno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('000001', '王林', '大专', '1966-01-23', '1', '8', '中山路32-1-508', '83355668', '2');
INSERT INTO `emp` VALUES ('010008', '伍容华', '本科', '1986-03-28', '1', '3', '北京东路100-2', '83321321', '1');
INSERT INTO `emp` VALUES ('020010', '王向容', '硕士', '1982-12-09', '1', '2', '四牌楼10-0108', '83792361', '1');
INSERT INTO `emp` VALUES ('020018', '李丽', '大专', '1960-07-30', '0', '6', '中山东路102-2', '83413301', '1');
INSERT INTO `emp` VALUES ('102201', '刘明', '本科', '1972-10-18', '1', '3', '虎踞路100-2', '83606608', '5');
INSERT INTO `emp` VALUES ('102208', '朱俊', '硕士', '1965-09-28', '1', '2', '牌楼巷5-3-106', '84708817', '5');
INSERT INTO `emp` VALUES ('108991', '钟敏', '硕士', '1979-08-10', '0', '2', '中山路10-3-105', '83346722', '3');
INSERT INTO `emp` VALUES ('111006', '张石兵', '本科', '1974-10-01', '1', '1', '解放路34-1-203', '83467336', '5');
INSERT INTO `emp` VALUES ('210678', '林涛', '大专', '1977-04-02', '1', '2', '中山北路24-35', '58765991', '3');
INSERT INTO `emp` VALUES ('302566', '李玉珉', '本科', '1968-09-20', '1', '3', '热和路209-3', '58765991', '4');
INSERT INTO `emp` VALUES ('308759', '叶凡', '本科', '1978-11-18', '1', '2', '北京西路3-7-52', '83308901', '4');
INSERT INTO `emp` VALUES ('504209', '陈林琳', '大专', '1969-09-03', '0', '5', '汉中路120-4-12', '84468158', '4');
-- ----------------------------
-- Table structure for sal
-- ----------------------------
DROP TABLE IF EXISTS `sal`;
CREATE TABLE `sal` (
`empno` char(6) NOT NULL COMMENT '员工编号',
`income` double(10,2) DEFAULT NULL COMMENT '收入',
`outlay` double(10,2) DEFAULT NULL COMMENT '支出',
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of sal
-- ----------------------------
INSERT INTO `sal` VALUES ('000001', '2100.80', '123.09');
INSERT INTO `sal` VALUES ('010008', '1582.62', '88.03');
INSERT INTO `sal` VALUES ('020010', '2860.00', '198.00');
INSERT INTO `sal` VALUES ('020018', '2347.68', '180.00');
INSERT INTO `sal` VALUES ('102201', '2569.88', '185.65');
INSERT INTO `sal` VALUES ('102208', '1980.00', '100.00');
INSERT INTO `sal` VALUES ('108991', '3259.98', '281.52');
INSERT INTO `sal` VALUES ('111006', '1987.01', '79.58');
INSERT INTO `sal` VALUES ('210678', '2240.00', '121.00');
INSERT INTO `sal` VALUES ('302566', '2980.70', '210.20');
INSERT INTO `sal` VALUES ('308759', '2531.98', '199.08');
INSERT INTO `sal` VALUES ('504209', '2066.15', '108.00');
数据图片
dept表图片.png
emp表图片.png
sal表图片.png
二、select 语句的基本使用
- 查询emp表的员工部门号和性别，要求消除重复行（DISTINCT）
- 计算每个雇员的实际收入（列运算）
- 查询emp表中员工的姓名和性别，要求性别值为1时，显示为“男”，为0时显示为“女”(结果替换)
- 查询每个雇员的地址和电话号码，显示的标题为adress,telephone（别名as）
- 计算 sal表中员工月收入的平均数(聚合函数avg)
- 计算所有员工的总支出（聚合函数sum）
- 计算员工总数(聚合函数count)
- 显示最高收入和最低收入的员工的员工编号（聚合函数max、min, 子查询）
三、条件查询
- 显示月收入高于2000元的员工编号（比较数字）
- 查询1970年以后出生的员工的姓名和地址（比较日期）
- 显示工作年限3年以上（含3年）、学历在本科以上（含本科）的男性员工的信息(逻辑运算 && AND || OR)
- 查找员工编号中倒数第2个数字为0的员工编号、姓名、地址、学历（模糊查询）
- 查询月收入在2000元~3000元的员工编号和收入（比较，between and）
- 显示女雇员的地址和电话号码
四. 分类汇总与排序
- 按部门列出该部门工作的员工人数（按部门分组）
- 分别统计男性员工和女性员工的人数（按性别分组）
- 查找雇员超过2人的部门编号和员工数量（分组后加条件）
- 按员工学历分组统计各种学历人数（按学历分组）
- 将员工信息按出生日期从大到小排序（降序）
- 将员工薪水按收入多少从小到大排序（升序）
- 按员工的工作年限分组，统计各个工作年限的人数，并按人数从小到大排序
五、多表查询
- 查询‘王林’的基本情况和所工作的部门名称
- 查询财务部、研发部、市场部的员工信息
- 查询每个雇员的基本情况和薪水情况
- 查询研发部在1970年以前出生的员工姓名和薪水情况
- 查询员工的姓名、地址、收入水平，要求2000元以下显示为“低收入”，2000元~3000元显示为“中等收入”, 3000元以上显示为“高收入”
