mysql on、having、where、order by、group by的用法笔记

创建mysql的测试表

SET FOREIGN_KEY_CHECKS=0; -- 影响的是当前页面，没改变全局的设置

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门地址',
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

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

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(4) NOT NULL COMMENT '员工号',
  `ename` varchar(10) DEFAULT NULL COMMENT '员工姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '工作岗位',
  `mgr` int(4) DEFAULT NULL COMMENT '领导编号',
  `hiredate` date DEFAULT NULL COMMENT '入职时间',
  `sal` int(7) DEFAULT NULL COMMENT 'salary工资',
  `comm` int(7) DEFAULT NULL COMMENT 'commission 佣金',
  `deptno` int(2) DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`empno`),
  KEY `fk_deptno` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', null, '10');
INSERT INTO `emp` VALUES ('8000', 'SEASON', 'CLERK', '7782', '1999-01-23', '1300', null, '50');

-- ----------------------------
-- Table structure for salgrade
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` decimal(10,0) NOT NULL COMMENT '工资级别',
  `losal` decimal(10,0) DEFAULT NULL COMMENT '最低工资',
  `hisal` decimal(10,0) DEFAULT NULL COMMENT '最高工资',
  PRIMARY KEY (`grade`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工资级别';

-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade` VALUES ('1', '700', '1200');
INSERT INTO `salgrade` VALUES ('2', '1201', '1400');
INSERT INTO `salgrade` VALUES ('3', '1401', '2000');
INSERT INTO `salgrade` VALUES ('4', '2001', '3000');
INSERT INTO `salgrade` VALUES ('5', '3001', '9999');

1. 关于他们之间的区别，之前查过，但是总是会弄糊涂，搞不清，所以我专门记下来，下次遇到疑问的时候，直接在这里查找就行。
   话不多说，开始进入正题吧
   首先得有测试数据，所以呢，我找到了oracle的3张测试表以及相关的测试数据，而这就基本够我们使用了

2. 执行的顺序：on -> where -> group by -> having -> order by
   where在group by 之前，是为了先过滤单表／视图／结果集，再JOIN

3. 特殊的用法：

1. 在inner join 中，可以将where的条件全部迁移到on的后面，也就是说on与where都可以对表进行限制
2. 在left join中，on不会对左表进行任何限制，所以闲置条件只能卸载where中
3. having后面可以对聚合后的数据进行条件过滤，对于非聚合的数据，比如查询中加减，也应该在having中处理(非聚合sql也可以)
4. where只能对原始的数据进行条件过滤，加工后的数据需要用having处理，同上一条一致

4. 另外需要注意的：在left join 语句中，on后面的条件不会影响到左表，right join同理，而这时就需要考虑on与where的选择了