SQL中的函数：单值函数、聚合函数

前言

单值函数

单值函数的定义

首先什么是单值函数，单值单值就是单个输入，操作单个变量或数值的函数。
具体的特性如下：
操作数据对象
接受参数返回一个结果
只对一行进行变换
每行返回一个结果
可以嵌套
参数可以是一列或一个值

单值函数的分类

数值函数

字符串函数

注意在MySQL中，字符串的位置是从1开始的。

日期与时间函数

加密与解密函数

MySQL信息函数

VERSION()、CONNECTION_ID()、DATABASE()、SCHEMA()、USER()、

使用LPAD,RPAD实现左对齐与右对齐的效果

SELECT employee_id,last_name,LPAD(salary,10,' ')
FROM employees;
1
2

4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT

SELECT CONCAT(employee_id,',',last_name,',',salary) OUT_PUT
FROM employees;
1
2

MySQL语句自带循环控制结构
我们在客户端登录信息，然后发送信息给服务器验证的过程中，客户端就已经实现了加密，信息在传输的过程中都是加密的
MD5与SHA加密都是不可逆的加密。

聚合函数

数值型的聚合函数

聚合函数类型：
AVG():限制数值类型
SUM()：限制数值类型
MAX()：任意数据类型
MIN()：任意数据类型
COUNT()：返回表中某种记录的总数
COUNT(*)：会统计值为 NULL 的行
COUNT(expr)：返回expr不为空的记录总数

Mysql中的聚合函数是不能够嵌套的（ORACLE支持）,单行函数可以嵌套，因为聚合函数的输出也是单个值，无法作为参数传递给多值函数

#需求：查询公司中平均奖金率
#错误的！
SELECT AVG(commission_pct)
FROM employees;

#正确的：
SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),
AVG(IFNULL(commission_pct,0))
FROM employees;
1
2
3
4
5
6
7
8
9

--在mysql中会报错
MIN(AVG(salary))
1
2

GROUP BY

单分组

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
1
2
3
4
5

使用GROUP BY子句将表中的数据分成若干组，在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中，包含在 GROUP BY 子句中的列不必包含在SELECT 列表中。

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

SELECT AVG(salary)
FROM employees
GROUP BY department_id ;
1
2
3
4
5
6
7

多分组

使用多个列分组

#需求：查询各个department_id,job_id的平均工资
#方式1：
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY  department_id,job_id;
#方式2：
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;

#错误的！
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id;

#结论1：SELECT中出现的非组函数的字段必须声明在GROUP BY 中。注意非组函数的字段这个词的意思
#      反之，GROUP BY中声明的字段可以不出现在SELECT中。
#结论2：GROUP BY 声明在FROM后面、WHERE后面，ORDER BY 前面、LIMIT前面
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

WITH ROLLUP

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量
当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的

#错误的：
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;
1
2
3
4
5

HAVING

过滤分组：HAVING子句

1. 行已经被分组。
2. 使用了聚合函数。
3. 满足HAVING 子句中条件的分组将被显示。
4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用

#要求1：如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。
#要求2：HAVING 必须声明在 GROUP BY 的后面,注意与WHERE的不同
#要求3：开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。
#练习：查询各个部门中最高工资比10000高的部门信息
#错误的写法：
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary) > 10000
GROUP BY department_id;

#正确的写法：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

#结论：当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
#      当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。但是，建议大家声明在WHERE中。

#练习：查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
#方式1：推荐，执行效率高于方式2.
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;

#方式2：
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

注意事项

非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数。

SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
1
2
3
4

WHERE与HAVING的对比

WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发
挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

SQL语句执行过程

#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

1. 关键字的顺序是不能颠倒的：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
1

3. SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：
   
   在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表 ，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

SQL语句执行原理

对虚拟表有了解，具体还是比较复杂