yua 一、准备数据
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）
SELECT DISTINCT deptno, sex
FROM emp;
注意：DISTINCT 表示后面的列，如果全部相同就会被消除
- 计算每个雇员的实际收入（列运算）
SELECT income-outlay AS 实际收入
FROM sal;
- 查询emp表中员工的姓名和性别，要求性别值为1时，显示为“男”，为0时显示为“女”(结果替换)
SELECT ename, sex,
CASE
WHEN sex=1 THEN '男'
WHEN sex=0 THEN '女'
END AS 性别
FROM emp;
注意：把case end 当成一列来处理，上一列后面有逗号
- 查询每个雇员的地址和电话号码，显示的标题为adress,telephone（别名as）
SELECT address AS adress, tel AS telephone
FROM emp;
注意：as 可以省略，但是为了可读性，最好别省略
注意2： AS 后面的字符串可以不加单引号
- 计算 sal表中员工月收入的平均数(聚合函数avg)
SELECT AVG(income) AS 平均收入
FROM sal;
- 计算所有员工的总支出（聚合函数sum）
SELECT SUM(outlay) AS 总支出
FROM sal;
- 计算员工总数(聚合函数count)
SELECT COUNT(*) AS 总人数
FROM emp;
- 显示最高收入和最低收入的员工的员工编号（聚合函数max、min, 子查询）
-- 先查询出最高收入和最低收入
SELECT MAX(income) FROM sal;
SELECT MIN(income) FROM sal;
-- 再查员工编号
SELECT empno
FROM sal
WHERE income=(SELECT MAX(income) FROM sal)
OR income=(SELECT MIN(income) FROM sal)
ORDER BY income DESC;
三、条件查询
- 显示月收入高于2000元的员工编号（比较数字）
SELECT empno
FROM sal
WHERE income>2000;
- 查询1970年以后出生的员工的姓名和地址（比较日期）
SELECT ename, address
FROM emp
WHERE birthday>'1970';
注意：日期要用单引号引起来
- 显示工作年限3年以上（含3年）、学历在本科以上（含本科）的男性员工的信息(逻辑运算 && AND || OR)
SELECT *
FROM emp
WHERE workyears>=3 && sex=1 && (degree='本科' || degree='硕士');
SELECT *
FROM emp
WHERE workyears>=3 && sex=1 && degree IN ('本科', '硕士');
SELECT *
FROM emp
WHERE workyears>=3 && sex=1 && degree != '大专';
- 查找员工编号中倒数第2个数字为0的员工编号、姓名、地址、学历（模糊查询）
SELECT eno, ename, address, degree
FROM emp
WHERE eno LIKE '%0_';
注意：模糊查询使用like关键字
%表示任意个字符
_表是1个字符
- 查询月收入在2000元~3000元的员工编号和收入（比较，between and）
SELECT empno, income
FROM sal
WHERE income>=2000 && income<=3000;
SELECT empno, income
FROM sal
WHERE income BETWEEN 2000 AND 3000;
- 显示女雇员的地址和电话号码
SELECT address, tel
FROM emp
WHERE sex=0;
注意：数字不用加单引号
四. 分类汇总与排序
- 按部门列出该部门工作的员工人数（按部门分组）
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno;
注意：分组查询使用group by
能出现在select后面的只能是分组条件和组的信息
- 分别统计男性员工和女性员工的人数（按性别分组）
SELECT sex, COUNT(*)
FROM emp
GROUP BY sex;
- 查找雇员超过2人的部门编号和员工数量（分组后加条件）
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno
HAVING COUNT(*) > 2;
- 按员工学历分组统计各种学历人数（按学历分组）
SELECT degree, COUNT(*)
FROM emp
GROUP BY degree;
- 将员工信息按出生日期从大到小排序（降序）
SELECT *
FROM emp
ORDER BY birthday DESC;
- 将员工薪水按收入多少从小到大排序（升序）
SELECT *
FROM sal
ORDER BY income ASC;
注意：asc可以省略
- 按员工的工作年限分组，统计各个工作年限的人数，并按人数从小到大排序
SELECT workyears, COUNT(*)
FROM emp
GROUP BY workyears
ORDER BY COUNT(*);
注意语句顺序
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
五、多表查询
- 查询‘王林’的基本情况和所工作的部门名称
SELECT emp.*, dept.dname
FROM emp, dept
WHERE emp.deptno=dept.dno
AND emp.ename='王林';
- 查询财务部、研发部、市场部的员工信息
-- 先查询出财务部、研发部、市场部的部门编号
SELECT dno FROM dept WHERE dname IN('财务部', '研发部', '市场部');
SELECT *
FROM emp
WHERE deptno IN (SELECT dno FROM dept WHERE dname IN('财务部', '研发部', '市场部'));
- 查询每个雇员的基本情况和薪水情况
SELECT emp.*, sal.income, sal.outlay
FROM emp, sal
WHERE emp.eno=sal.empno;
- 查询研发部在1970年以前出生的员工姓名和薪水情况
SELECT emp.ename, sal.income, sal.outlay
FROM emp, sal
WHERE emp.eno=sal.empno
AND emp.birthday<'1970'
AND emp.deptno=(SELECT dno FROM dept WHERE dname='研发部');
- 查询员工的姓名、地址、收入水平，要求2000元以下显示为“低收入”，2000元~3000元显示为“中等收入”, 3000元以上显示为“高收入”
SELECT emp.ename, emp.address, sal.income,
CASE
WHEN sal.income < 2000 THEN '低收入'
WHEN sal.income >=2000 && sal.income <=3000 THEN '中等收入'
ELSE '高收入'
END AS 收入水平
FROM emp, sal
WHERE emp.eno=sal.empno;
