当前位置：首页 > article >正文

MySQL数据库（4）-基础-＞高阶查询

article 2025/3/11 9:14:18

1.测试数据库文件

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 50721
 Source Host           : localhost:3306
 Source Schema         : test

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

 Date: 08/10/2018 12:37:19
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_bonus
-- ----------------------------
DROP TABLE IF EXISTS `t_bonus`;
CREATE TABLE `t_bonus` (
  `empno` int(4) NOT NULL,
  `job` varchar(20) DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `comm` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for t_dept
-- ----------------------------
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
  `deptno` int(2) NOT NULL,
  `dname` varchar(20) DEFAULT NULL,
  `loc` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_dept
-- ----------------------------
BEGIN;
INSERT INTO `t_dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `t_dept` VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `t_dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `t_dept` VALUES (40, 'OPERATIONS', 'BOSTON');
COMMIT;

-- ----------------------------
-- Table structure for t_emp
-- ----------------------------
DROP TABLE IF EXISTS `t_emp`;
CREATE TABLE `t_emp` (
  `empno` int(4) NOT NULL,
  `ename` varchar(20) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL,
  `mgr` int(4) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `comm` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL,
  PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_emp
-- ----------------------------
BEGIN;
INSERT INTO `t_emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17 00:00:00', 800.00, NULL, 20);
INSERT INTO `t_emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20 00:00:00', 1600.00, 300.00, 30);
INSERT INTO `t_emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22 00:00:00', 1250.00, 500.00, 30);
INSERT INTO `t_emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02 00:00:00', 2975.00, NULL, 20);
INSERT INTO `t_emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28 00:00:00', 1250.00, 1400.00, 30);
INSERT INTO `t_emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01 00:00:00', 2850.00, NULL, 30);
INSERT INTO `t_emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09 00:00:00', 2450.00, NULL, 10);
INSERT INTO `t_emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09 00:00:00', 3000.00, NULL, 20);
INSERT INTO `t_emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17 00:00:00', 5000.00, NULL, 10);
INSERT INTO `t_emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08 00:00:00', 1500.00, 0.00, 30);
INSERT INTO `t_emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12 00:00:00', 1100.00, NULL, 20);
INSERT INTO `t_emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03 00:00:00', 950.00, NULL, 30);
INSERT INTO `t_emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03 00:00:00', 3000.00, NULL, 20);
INSERT INTO `t_emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23 00:00:00', 1300.00, NULL, 10);
COMMIT;

-- ----------------------------
-- Table structure for t_salgrade
-- ----------------------------
DROP TABLE IF EXISTS `t_salgrade`;
CREATE TABLE `t_salgrade` (
  `grade` int(11) NOT NULL,
  `losal` decimal(10,2) DEFAULT NULL,
  `hisal` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`grade`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_salgrade
-- ----------------------------
BEGIN;
INSERT INTO `t_salgrade` VALUES (1, 700.00, 1200.00);
INSERT INTO `t_salgrade` VALUES (2, 1201.00, 1400.00);
INSERT INTO `t_salgrade` VALUES (3, 1401.00, 2000.00);
INSERT INTO `t_salgrade` VALUES (4, 2001.00, 3000.00);
INSERT INTO `t_salgrade` VALUES (5, 3001.00, 9999.00);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

2.基本查询

2.1 去重 DISTINCT

SELECT DISTINCT 字段 FROM ……；

SELECT DISTINCT job FROM t_emp;

2.2 查询特定员工信息 AND&OR

SELECT ...... FROM ...... WHERE 条件 [AND|OR] 条件 ……;

名字为SMITH且工资大于5000：

SELECT * FROM t_emp WHERE ename = "SMITH" AND sal > 5000;

名字为SMITH或工资大于等于5000：

SELECT * FROM t_emp WHERE ename = "SMITH" OR sal >= 5000;

2.3 复杂条件查询 WHERE AND

部门编号为10、年收入至少为15000且入职超过20年的员工信息：

SELECT empno, ename, sal, hiredate FROM t_emp
WHERE deptno = 10 AND (sal + IFNULL(comm, 0)) * 12 >= 15000
AND DATEDIFF(NOW(), hiredate) / 365 > 20;

2.4 查询特定部门信息 IN

SELECT * FROM t_dept WHERE deptno IN (10, 20, 30);

2.5 查询奖金为空的员工 NULL

SELECT * FROM t_emp WHERE comm IS NULL;

2.6 工资范围查询 BETWEEN AND

SELECT * FROM t_emp WHERE sal BETWEEN 2000 AND 3000;

2.7 模糊匹配查询 LIKE

名字中包含S的员工：

SELECT * FROM t_emp WHERE ename LIKE "%S%";

职位中包含S，且S在倒数第二位的员工：

SELECT * FROM t_emp WHERE job LIKE "%S_";

3.高阶查询：

3.1 聚合函数

平均工资+绩效 AVG

SELECT AVG(sal + IFNULL(comm, 0)) FROM t_emp;

求和 SUM

SELECT SUM(sal) FROM t_emp;

最大值 MAX

SELECT MAX(sal + IFNULL(comm, 0)) FROM t_emp WHERE deptno IN (10, 20);
SELECT MAX(LENGTH(ename)) FROM t_emp;

最小值 MIN

SELECT MIN(sal) FROM t_emp WHERE deptno = 10;
SELECT * FROM t_emp WHERE sal = (SELECT MIN(sal) FROM t_emp WHERE deptno = 10) AND deptno = 10;

平均值 AVG

SELECT AVG(sal + IFNULL(comm, 0)) FROM t_emp;
SELECT AVG(ename) FROM t_emp;

记录 COUNT

SELECT COUNT(*) FROM t_emp

WHERE deptno IN(10,20)

AND sal>=2000

AND DATEDIFF(NOW(),hiredate)/365>=15

3.2 分组函数 GROUP BY

每个部门的平均工资：

SELECT deptno, AVG(sal) FROM t_emp GROUP BY deptno;

每个部门每种职位的人员数量和平均底薪：

SELECT deptno, job, COUNT(*), AVG(sal) FROM t_emp GROUP BY deptno, job ORDER BY deptno;

对分组结果集再次汇总运算：

SELECT deptno, COUNT(*), AVG(sal), MAX(sal), MIN(sal) FROM t_emp GROUP BY deptno WITH ROLLUP;

每个部门内底薪超过2000元的人数和员工姓名：

SELECT deptno, GROUP_CONCAT(ename), COUNT(*) FROM t_emp GROUP BY deptno;

3.3 分组条件查询 HAVING

部门平均底薪超过2000元的部门编号：

SELECT deptno, AVG(sal) FROM t_emp GROUP BY deptno HAVING AVG(sal) > 2500;

4. 表的内外连接查询

4.1 笛卡尔积

如果不定义关联条件就会出现无条件连接，两张表的数据会交叉连接，产生笛卡尔积。

SELECT * FROM t_emp, t_dept;
SELECT * FROM t_emp JOIN t_dept WHERE t_emp.deptno = t_dept.deptno;

4.2 列别名和表别名

SELECT * FROM t_emp e JOIN t_dept d WHERE e.deptno = d.deptno;
SELECT e.ename, d.dname FROM t_emp e JOIN t_dept d WHERE e.deptno = d.deptno;

4.3 表连接：内/外连接

4.3.1 内连接

SELECT …… FROM 表1 JOIN 表2 ON 连接条件;
SELECT …… FROM 表1 JOIN 表2 WHERE 连接条件;
SELECT …… FROM 表1,表2 WHERE 连接条件；

查询每个员工的工号、姓名、部门名称、底薪、职位、工资等级：

SELECT ename, d.deptno
FROM t_emp e, t_dept d, t_salgrade s
WHERE e.deptno = d.deptno
AND e.sal BETWEEN s.losal AND s.hisal;

查询SCOTT相同部门的员工：

SELECT * FROM t_emp WHERE deptno = (SELECT deptno FROM t_emp WHERE ename = 'SCOTT');
SELECT e2.* FROM t_emp e1, t_emp e2 WHERE e1.ename = 'SCOTT' AND e2.ename != 'SCOTT' AND e1.deptno = e2.deptno;

查询底薪超过公司平均底薪的员工信息：

SELECT e1.* FROM t_emp e1, (SELECT AVG(sal) avg FROM t_emp) e2 WHERE e1.sal > e2.avg;
SELECT e1.* FROM t_emp e1 JOIN (SELECT AVG(sal) avg FROM t_emp) e2 ON e1.sal > e2.avg;

4.3.2 外连接

SELECT …… FROM 表1 LEFT/RIGHT JOIN 表2 ON 条件

SELECT e.empno,e.ename,d.dname

FROM t_emp e LEFT JOIN t_dept d

ON e.deptno=d.deptno;

4.4 复杂查询

查询RESEARCH部门的人数、最高底薪、最低底薪、平均底薪、平均工龄：

SELECT COUNT(*), MAX(e.sal), MIN(e.sal), AVG(DATEDIFF(NOW(), e.hiredate) / 365)
FROM t_emp e, t_dept d
WHERE e.deptno = d.deptno
AND d.dname = 'RESEARCH';

5.UNION 和子查询

5.1 UNION

UNION (去重) 和 UNION ALL (全部输出) ：

select * from t_emp where ename='SMITH' UNION
(select * from t_emp where ename='ALLEN');
#UNION ALL(全部输出)
select * from t_emp where ename='SMITH' UNION ALL
(select * from t_emp where ename='ALLEN');