MySQL的基础指令DML指令二

一、函数

• 函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了代码效率，又提高了可维护性 。在SQL中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地 提高用户对数据库的管理效率 。
• 从函数定义的角度出发，我们可以将函数分成内置函数和自定义函数 。在SQL语言中，同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的，本次讲解的单行函数和聚合函数都是SQL 的内置函数。
• 特别注意的是，不同的数据库中，函数的定义是不一样的，远大于同一个语言不同版本的差异，所以SQL函数的可以执行是很差的，使用函数一定要注意所使用的数据库是否符合。

1. 单行函数

1.1 特点

• 单行函数是用来操作数据对象的
• 单行函数接收参数后会返回一个结果
• 单行函数每次只对一行进行变换，每行返回一个结果
• 单行函数可以嵌套使用
• 单行函数参数可以是一列或一个值

1.2 常用函数

1.2.1 常用数值函数

函数名称	作用
ABS(x)	返回x的绝对值
CEIL(x)，CEILING(x)	返回大于或等于某个值的最小整数
FLOOR(x)	返回小于或等于某个值的最大整数
MOD(x,y)	返回X除以Y后的余数
RAND()	返回0~1的随机值
RAND(x)	返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
ROUND(x)	返回一个对x的值进行四舍五入后，最接近于X的整数
ROUND(x,y)	返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根，当X的值为负数时，返回NULL

1.2.2 常用字符串函数

函数名称	作用
LENGTH(s)	返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,…,sn)	连接s1,s2,…,sn为一个字符串

• 注意：字符串中的索引从1开始

1.2.3 常用日期和时间函数

函数名称	作用
CURDATE()	返回当前日期，只包含年、月、日
CURTIME()	返回当前时间，只包含时、分、秒
NOW()	返回当前系统日期和时间

1.2.4 常用流程控制函数

函数名称	作用
IF(value,value1,value2)	如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2)	如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2… [ELSE 结果n] END	相当于Java的if…else if…else…
CASE 字段 WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN值1 … [ELSE 值n] END	相当于Java的switch…case…

1.2.5 常用加解密函数

1.2.4 常用MySQL信息函数

2. 聚合函数

2.1 特点

• 聚合（或聚集、分组）函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值

2.2 常用函数

函数名称	作用
AVG(字段)	求该字段数据的平均值，要求字段类型是数值型，否则没意义，注意数据为空值(NULL)时，不会被计算
SUM(字段)	求该字段数据的总和，要求字段类型是数值型，否则没意义，注意数据为空值(NULL)时，不会被计算
MAX(字段)	求该字段数据的最大值，适用于数值类型、字符串类型、日期时间类型的字段（或变量）
MIN(字段)	求该字段数据的最小值，适用于数值类型、字符串类型、日期时间类型的字段（或变量）
COUNT(字段)	计算指定字段在查询结果中出现的个数，注意数据为空值(NULL)时，不会被计算

• 附加：计算表中有多少条记录的实现方式

# 方式一：
count(*);
# 方式二：不一定是1，只要是一个常量就行
count(1);
方式三：不一定对，计算指定字段出现的个数时，是不会计算空值(NULL)的
count(具体字段);

二、数据分组-GROUP BY关键字的使用

1. 需求一

查询各个部门的平均工资和最高工资

select department_id , AVG(salary), SUM(salary)
from employees
group by department_id;

2. 需求二

查询各个部门的每个工种的平均工资

select department_id, job_id, AVG(salary)
from employees
group by department_Id, job_id;

3. 细节处理

• SELECT语句查询的字段中非组函数的字段，必须声明在GROUP BY中；但是GROUP BY中声明的字段，可以不出现在SELECT中；记住，尽量让SELECT后的字段和GROUP BY的字段保持一致就好
• GROUP BY的位置在FROM和WHERE之后，在ORDER BY和LIMIT之前

三、分组后筛选数据-HAVING关键字的使用

1. 需求一

查询各个部门中最高工资比10000高的部门信息

select department_id, MAX(salary)
from employees
group by department_id
having MAX(salary) > 10000;

2.需求二

查询部门id为10，20，30，30中最高工资比10000高的部门信息

select department_id, MAX(salary)
from employees
where department_id in (10, 20, 30, 40)
group by department_id
having MAX(salary) > 10000;

select department_id, MAX(salary)
from employees
group by department_id
having MAX(salary) > 10000 and  department_id in (10, 20, 30, 40);

• 推荐使用前者，因为执行效率高（可以分析SQL执行流程）

3.细节处理

• 如果筛选条件中使用到聚合函数，那么必须用HAVING来替换WHERE
• HAVING必须声明在GROUP BY的后面
• 开发中，我们使用HAVING的前提是要使用到了GROUP BY
• 当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中，当过滤条件中没有聚合函数时，则此过滤条件可以声明在WHERE中或HAVING中都可以，但我们只写在HAVING中