3-1-3 MySQL基础-数据库的高级查询

测试表结构

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;

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;

聚合函数

SUM函数

SUM函数用于求和,只能用于数字类型,字符类型的统计结果为0,日期类型统计结果是毫秒数相加

SELECT SUM(ename), SUM(sal) FROM t_emp;

 

 注意SUM函数求和会排除NULL值

MAX函数

MAX函数用于获取非空值的最大值

SELECT MAX(comm) FROM t_emp;

 

MIN函数

MIN函数用于获取非空值的最小值

SELECT MIN(comm) FROM t_emp;

 

 AVG函数

AVG函数用于获得非空值的平均值,非数字数据统计结果为0

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

 

COUNT函数

COUNT(*)用于获得主键条数,COUNT(列名)用于获得包含非空值的记录数.

SELECT COUNT(*), COUNT(comm) FROM t_emp;

 

 分组查询

默认情况下聚合函数是对全表范围内的数据做统计

GROUP BY子句的作用是通过一定的规则蒋一个数据集划分成若干个小的区域,然后针对每个小区域分进行数据聚合处理

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

 

 对SELECT子句要求

查询语句中含有GROUP BY子句,那么SELECT子句中的内容就必须遵守规定:SELECT子句中可以包括聚合函数,或者GROUP BY子句的分组列,其余内容均不可以出现在SELECT子句中.

WITH ROLLUP

在group分组字段的基础上再进行统计数据

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

 

 GROUP_CONCAT函数

GROUP_CONCAT函数可以把分组查询中的某个字段拼成一个字符串

SELECT deptno, GROUP_CONCAT(ename),COUNT(*) 
FROM t_emp 
WHERE sal >=2000
GROUP BY deptno;

 

 GROUP BY语句在查询语句的执行顺序

FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

HAVING子句

因为WHERE子句先于GROUP BY,一旦WHERE中出现了聚合函数,数据库根本不知道按照什么范围计算聚合值,此时需要HAVING子句

SELECT deptno 
FROM t_emp
WHERE hiredate >= '1982-01-01'
GROUP BY deptno
HAVING COUNT(*) >=2
ORDER BY deptno;

 

HAVING子句的特殊用法

按照数字1分组,MySQL会根据SELECT子句中的列进行分组,HAVING子句也可以正常执行

SELECT deptno,COUNT(*) 
FROM t_emp
GROUP BY 1
HAVING deptno IN (10,20);

 表连接查询

表连接查询必须指定关联条件,否则会产生笛卡尔积

SELECT e.empno,e.ename,d.dname
FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno

表连接的分类

表连接分为两种:内连接和外连接

内连接是结果集中只保留符合连接条件的记录

外连接是不管符不符合连接条件,记录都要保留在结果集中

内连接

语法:

SELECT ...... FROM 表1

[INNER] JOIN 表2 ON 条件

[INNER] JOIN 表3 ON 条件

......;

内连接多种语法形式

1.SELECT ...... FROM 表1 JOIN 表2 ON 连接条件;

2.SELECT ...... FROM 表1 JOIN 表2 WHERE 连接条件;

3.SELECT ...... FROM 表1, 表2 WHERE 连接条件;

练习

查询与SCOTT相同部门的员工都有谁

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

不建议使用子查询,因为遍历e1.deptno时每次都会运行一次子查询.效率低

建议改成表连接,如下

SELECT e2.ename
FROM t_emp e1 JOIN t_emp e2
ON e1.deptno = e2.deptno
WHERE e1.ename = 'SCOTT' AND e2.ename != 'SCOTT';

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

SELECT e1.empno,e1.ename,e1.sal
FROM t_emp e1
JOIN (
      SELECT AVG(e2.sal) avg_sal
      FROM t_emp e2) t ON e1.sal > t.avg_sal;

外连接

左连接和右连接

左外连接就是保留左表所有的记录,与右表做连接.如果游标有符合条件的记录就与左表连接.如果右表没有符合条件的记录,就用NULL与左表连接.右外连接也是如此.

子查询

子查询可以写在三个地方:WHERE子句,FROM子句,SELECT子句,但是只有FROM子句才是最可取的

WHERE子查询

这种子查询最简单,最容易理解,但是效率很低,比较每条记录时都要重新执行子查询,请用表连接替代WHERE子查询.

FROM子查询

FROM子查询只会执行一次,所以查询效率很高

SELECT子查询

SELECT子查询每输出一条记录都要执行一次,查询效率很低

相关子查询

查询执行的时候要多次依赖子查询的结果,这种查询被称为相关子查询

WHERE子查询和SELECT子查询都属于相关子查询

因为相关子查询要反复多次执行,所以应该避免使用

 多行子查询

WHERE子句中可以使用IN,ALL,ANY,EXISTS关键字处理多行表达式结果集的条件判断

SELECT ename
FROM t_emp
WHERE sal >= ALL
(SELECT sal FROM t_emp WHERE ename IN ("FORD", "MARTIN"))
AND ename NOT IN ("FORD","MARTIN");

EXISTS关键字是把原来在子查询之外的条件判断,写到子查询里面

SELECT ...... FROM 表名 WHERE [NOT] EXISTS (子查询);