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

大家好，欢迎来到IT知识分享网。

前言

单值函数

单值函数的定义

单值函数的分类

数值函数

字符串函数

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

日期与时间函数

加密与解密函数

MySQL信息函数

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

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

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

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

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

聚合函数

数值型的聚合函数

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

#需求：查询公司中平均奖金率 #错误的！ SELECT AVG(commission_pct) FROM employees; #正确的： SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)), AVG(IFNULL(commission_pct,0)) FROM employees; 

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

GROUP BY

单分组

SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column]; 

使用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 ; 

多分组

使用多个列分组

#需求：查询各个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前面 

WITH ROLLUP

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

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); 

注意事项

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

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

WHERE与HAVING的对比

SQL语句执行过程

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

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

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

SQL语句执行原理

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