数据库概述06（函数）

函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了代码效率 ，又提高了可维护性。在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 DiffDate
ROUND(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’); -> 729669
FROM_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(*)返回表中记录总数，适用于任意数据类型

• 可以对数值型数据使用AVG 和 SUM 函数

• 可以对任意数据类型的数据使用 MIN 和 MAX 函数

• COUNT(*)返回表中记录总数，适用于任意数据类型

• COUNT(expr) 返回 expr 不为空的记录总数

• COUNT(expr) 返回 expr 不为空的记录总数

• • 问题1：用count()，count(1)，count(列名)谁好呢? 其实对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
    Innodb引擎的表用count(),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)
• • 问题2：能不能使用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关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量
• 注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY
  是互相排斥的

分组过滤HAVING

• 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/DESC
LIMIT ...,...
#方式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：分页

表和表之间的关系

数据库设计方法：试凑法、规范化法和CAD法数据库设计工具：powerdesigner

在实际开发中，一个项目经常会涉及很多方面的数据。例如产品和类目
商品编号 商品名称 价格 类别名称 等级
1 哇哈哈 2.34 饮料 3
2 茅台 456.78 饮料 3
3 Java从入门到放弃 12.45 书籍 2

数据冗余和外键

数据冗余：类别信息重复
数据冗余导致的问题：空间浪费、增加异常、删除异常和修改异常
解决方案：引入外键约束产品表
商品编号 商品名称 商品价格 所属类别编号
1 哇哈哈 2.34 1
2 茅台 456.78 1
3 Java从入门到放弃 12.45 2
类目表
类别编号 类别名称 等级
1 饮料 3
2 书籍 2
通过引入新的表，同时使用外键约束保证取值的合理性，从而减少数据冗余

create table tb_catalog(
id bigint primary key auto_increment, -- 实体完整性
title varchar(32) not null
) comment '类别表';
create table tb_product(
id bigint primary key auto_increment comment '商品标号',
name varchar(32) not null,
price numeric(8,2) default 0,
-- 引入额外的列用于表示商品所属于的类别
catalog_id bigint, -- 外键列，允许为null
-- 外键表示该列的允许取的值必须在tb_catalog的id列中出现
foreign key(catalog_id) references tb_catalog(id)
) comment '商品表';

• 类目表中的id为主键，产品表中的列参照于类目表中的主键，所以一般类目表会被称为主表，产品表称为从表，产品表中的catalog_id称为外键
• 通过主表的主键和从表中的外键来描述的主外键关系，呈现的是一种一对多的关系
• • 一个类目有多个商品
• • 一个商品只能属于一个类目
• 在MySQL中innodb支持外键和事务，MyISAM不支持外键和事务
• 在具体开发中，为了提高性能，会故意删除外键约束，通过代码来控制数据的合理性

外键的特点

• 从表中的外键的值是对应主表中主键值的引用
• 从表中的外键数据类型必须和主表中的主键数据类型一致

基础语法

引入外键的目的在于保证数据的参照完整性
创建外键语法1：

create table tb_product(
id bigint primary key auto_increment comment '商品标号',
-- 引入额外的列用于表示商品所属于的类别
catalog_id bigint, -- 外键列，允许为null
-- 外键表示该列的允许取的值必须在tb_catalog的id列中出现
foreign key(catalog_id) references tb_catalog(id)
) comment '商品表';

创建外键语法2：

create table tb_product(
id bigint primary key auto_increment comment '商品标号',
catalog_id bigint -- 外键列，允许为null。是否允许为空取决于业务规则
)
-- alter table 从表名称 add [constraint fk_catalog外键约束名称] foreign key(从表中的外
键列名) references 主表名称(主表中的主键列名称)
alter table tb_product add constraint fk_catalog foreign key(catalog_id)
references tb_catalog(id);

删除外键约束的语法：

alter table 从表名称 drop foreign key 外键约束名称;
alter table tb_product drop foreign key fk_catalog;

需要记住 SELECT 查询时的两个顺序：
1、 关键字的顺序是不能颠倒的：SELECT … FROM … WHERE … GROUP BY … HAVING …
ORDER BY … LIMIT…
2、 SELECT语句的执行顺序：

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5 
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1 
WHERE height > 1.80 # 顺序 2 
GROUP BY player.team_id # 顺序 3 
HAVING num > 2 # 顺序 4 
ORDER BY num DESC # 顺序 6 
LIMIT 2 # 顺序 7

一个 SQL 语句的关键字顺序和执行顺序是FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT。在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个
虚拟表 ，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

SQL的执行原理

SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表vt1
2. 通过 ON 进行筛选，在虚拟表 vt1的基础上进行筛选，得到虚拟表vt2;
3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt2 的基础上增加外部行，得到虚拟表 vt3。
   当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。
   然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段 。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4 。
   当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT阶段 。

• 首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1 和 vt5-2 。
• 当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段 ，得到虚拟表vt6。
• 后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段 ，得到 终的结果，对应的是虚拟表vt7 。
  当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。
  同时因为 SQL 是一门类似英语的结构化查询语言，所以在写SELECT语句时还要注意相应的关键字顺序，所谓底层运行的原理，就是执行顺序。