今日学习内容总结如下:
函数
函数在计算机语言的使用中贯穿始终,函数的作用是什么呢?它可以把经常使用的代码封装起来,需要的时候直接调用即可。这样既提高了代码效率 ,又提高了可维护性。在
SQL
中也可以使用函数对检索出
来的数据进行函数操作。使用这些函数,可以极大地 提高用户对数据库的管理效率。
从函数定义的角度出发,可以将函数分成内置函数和自定义函数。在
SQL
语言中,同样也包括内置函数和自定义函数。内置函数是系统内置的通用函数,而自定义函数是根据自己的需要编写的
函数说明
在使用
SQL
语言的时候,不是直接和这门语言打交道,而是通过它使用不同的数据库软件,即
DBMS
。
DBMS
之间的差异性很大,远大于同一个语言不同版本之间的差异。实际上,只有很少的函数是被
DBMS
同时支持的。比如,大多数
DBMS
使用
||
或者
+
来做拼接符,而在
MySQL
中的字符串拼接函数为
concat()
。大部分
DBMS
会有自己特定的函数,这就意味着采用
SQL
函数的代码可移植性是很差的,因此
在使用函数的时候需要特别注意。
MySQL
提供了丰富的内置函数,这些函数使得数据的维护与管理更加方便,能够更好地提供数据的分析与统计功能,在一定程度上提高了开发人员进行数据分析与统计的效率。
MySQL
提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取
MySQL
信息函数、聚合函数等。这里这些丰富的内置函数再分为两类:单
行函数、聚合函数(或分组函数)
两种SQL函数
单行函数
- 操作数据对象
- 接受参数返回一个结果
- 只对一行进行变换
- 每行返回一个结果
- 可以嵌套
- 参数可以是一列或一个值
多行函数
多行函数又称聚合函数,对行的分组进行操作,对每个组给出一个结果;如果在查询中没有指定分组,则将查询结果看作一个组
聚合函数的类型主要有:
avg
平均值、
count
计数、
max
最大值、
min
最小值、
sum
合计
所有聚合函数忽略空值(不会去处理),可以使用
ifnull
或
coalesce
函数来用一个值代替空值,可以使用distinct
使查询到的数据去重。
聚合函数不能互相嵌套使用!
语法
select
函数名称
()
;
或者
select
函数名称
(
列名称,其它参数
) from
表名称
,在
mysql
中
from
子句不是必须的
名称
|
描述
|
abs()
|
返回绝对值
|
rand()
|
返回一个随机浮点值
|
+ -
|
尽量转换为数字进行计算,不能转为
0
|
ceiling()
|
返回不小于参数的最小整数值
|
floor()
|
返回不大于参数的最大整数值
|
length()
和
char_length()
|
以字节返回字串长度
|
substr(s,index,len)
|
返回从字符串
s
的
index
位置其
len
个字符
|
left(
被截取字串,截取长
度
)
|
返回指定的最左边的字符数,
SELECT LEFT('
www.lanou3g.com
',8)
|
right()
|
返回指定的最右边的字符数
|
trim()
|
删除前导和尾随空格
|
curdate()
|
返回当前日期
|
concat(
列
1
,列
2
,
...)
|
返回串联字符串
|
ifnull(
列名称
,'
默认值
')
| |
format()
|
返回格式化到指定小数位数的数字,
select format(salary,1);
四舍
五入
|
date_format(d,f)
|
按照表达式
f
要求显示日期
select date_format(now(), '%Y-%m-%d')
|
now()
|
返回当前日期和时间
|
uuid()
| 返回通用唯一标识符 |
user()
|
客户端提供的用户名和主机名
|
database()
|
返回默认(当前)数据库名称
|
- CONCAT(A, B) – 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
- LENGTH(str)获取以字节为单位的字符串长度;CHAR_LENGTH函数获取字符串的长度,以字符为单位计算长度
- FORMAT(X, D)- 格式化数字X到D有效数字。
FOMRAT(N,D,locale); 将数字 N 格式化为格式,如 "#,###,###.##" ,舍入到 D 位小数。它返回一个值作为字符串。其中 N 是要格式化的数字。 D 是要舍入的小数位数。 locale 是一个可选参数,用于确定千个分隔符和分隔符之间的分组。如果省略 locale 操作符, MySQL 将默认使用 en_US 。SELECT FORMAT(14500.2018, 2); 返回 14,500.20
- CURDATE(), CURTIME()- 返回当前日期或时间。
-
NOW () – 将当前日期和时间作为一个值返回。另外 MONTH (), DAY (), YEAR (),WEEK (), WEEKDAY () – 从日期值中提取给定数据。 HOUR (), MINUTE (),SECOND () – 从时间值中提取给定数据。
-
DATEDIFF ( A , B ) – 确定两个日期之间的天数差异,通常用于计算年龄
SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDateROUND(DATEDIFF(requiredDate, orderDate) / 365, 1) 四舍五入到 1 位小数
- SUBTIMES(A,B) – 用于执行时间的减法运算。
SUBTIME('2018-10-31 23:59:59','0:1:1') 返回 2018-10-31 23:58:58
- FROM_DAYS(INT) – 将整数天数转换为日期值。
TO_DAYS(date) 给出一个日期 date ,返回一个天数 ( 从 0 年开始的天数 )SELECT TO_DAYS('1997-10-07'); -> 729669FROM_DAYS(N) 给出一个天数 N ,返回一个 DATE 值SELECT FROM_DAYS(729669); -> '1997-10-07'
- IFNULL() 函数用于判断第一个表达式是否为 NULL,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值
SELECT IFNULL(price,0.0);
聚合函数
聚合函数作用于一组数据,并对一组数据返回一个值。
聚合函数类型:
AVG()
、
SUM()
、
MAX()
、
MIN()
、
COUNT()
- 可以对数值型数据使用AVG 和 SUM 函数
- 可以对任意数据类型的数据使用 MIN 和 MAX 函数
- COUNT(*)返回表中记录总数,适用于任意数据类型
- COUNT(expr) 返回 expr 不为空的记录总数
问题 1 :用 count(*) , count(1) , count( 列名 ) 谁好呢 ?其实对于 MyISAM 引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。Innodb 引擎的表用 count(*),count(1) 直接读行数,复杂度是 O(n) ,因为 innodb 真的要去数一遍。但好于具体的 count( 列名 )问题:能不能使用 count( 列名 ) 替换 count(*)?不要使用 count( 列名 ) 来替代 count(*) , count(*) 是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。说明: count(*) 会统计值为某个列值为 NULL 的行,而 count( 列名 ) 不会统计此列为 NULL值的行
分组操作
可以使用 GROUP BY 子句将表中的数据分成若干组
SELECT column , group_function( column ) FROM table[ WHERE condition ][ GROUP BY group_by_expression][ ORDER BY column ];
- 明确:WHERE一定放在FROM后面,如果有where则group by应该在where的后面
- 在 SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中
- select sex,avg(salary) from tb_users group by sex 正确
- select username,max(salary) from tb_users group by sex 语法错误
扩展:特殊用法。使用
WITH ROLLUP
关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量
SELECT department_id, AVG (salary) FROM employeesWHERE department_id > 80GROUP BY department_id WITH ROLLUP ;
注意:当使用
ROLLUP
时,不能同时使用
ORDER BY
子句进行结果排序,即
ROLLUP
和
ORDER BY是互相排斥的
分组过滤HAVING
- 行已经被分组
- 使用了聚合函数
- 满足HAVING 子句中条件的分组将被显示
- HAVING 不能单独使用,必须要跟 GROUP BY 一起使用
SELECT department_id, MAX (salary) FROM employeesGROUP BY department_idHAVING MAX (salary)> 10000
- 非法使用聚合函数 : 不能在WHERE子句中使用聚合函数
SELECT department_id, AVG (salary) FROM employees WHEREAVG (salary) > 8000GROUP BY department_id;
WHERE和HAVING的对比
- 区别1:WHERE可以直接使用表中的字段作为筛选条件,但不能使用分组中的计算函数作为筛选条件;HAVING 必须要与GROUP BY配合使用,可以把分组计算的函数和分组字段作为筛选条件。这 决定了,在需要对数据进行分组统计的时候,HAVING 可以完成 WHERE 不能完成的任务。这是因 为,在查询语法结构中,WHERE 在 GROUP BY 之前,所以无法对分组结果进行筛选。HAVING 在GROUP BY 之后,可以使用分组字段和分组中的计算函数,对分组的结果集进行筛选,这个功能是 WHERE 无法完成的。另外,WHERE排除的记录不再包括在分组中。
- 区别2:如果需要通过连接从关联表中获取需要的数据,WHERE是先筛选后连接,而HAVING是先 连接后筛选。这一点,就决定了在关联查询中,WHERE 比 HAVING 更高效。因为 WHERE 可以先 筛选,用一个筛选后的较小数据集和关联表进行连接,这样占用的资源比较少,执行效率也比较 高。HAVING 则需要先把结果集准备好,也就是用未被筛选的数据集进行关联,然后对这个大的数据集进行筛选,这样占用的资源就比较多,执行效率也较低。
开发中的选择:
WHERE
和
HAVING
也不是互相排斥的,我们可以在一个查询里面同时使用
WHERE
和HAVING
。包含分组统计函数的条件用
HAVING
,普通条件用
WHERE
。这样,我们就既利用了
WHERE条件的高效快速,又发挥了
HAVING
可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候,运行效率会有很大的差别。
SELECT的执行过程
# 方式 1 :SELECT ...,....,...FROM ...,...,....WHERE 多表的连接条件 AND 不包含组函数的过滤条件GROUP BY ...,...HAVING 包含组函数的过滤条件ORDER BY ... ASC /DESCLIMIT ...,...# 方式 2 :SELECT ...,....,...FROM ...JOIN ... ON 多表的连接条件JOIN ... ON ...WHERE 不包含组函数的过滤条件 AND /OR 不包含组函数的过滤条件GROUP BY ...,...HAVING 包含组函数的过滤条件ORDER BY ... ASC /DESC LIMIT ...,...# 其中:1 ) from :从哪些表中筛选2 ) on :关联多表查询时,去除笛卡尔积3 ) where :从表中筛选的条件4 ) group by :分组依据5 ) having :在统计结果中再次筛选6 ) order by :排序7 ) limit :分页