MySQL高级函数

IF(expr,v1,v2)如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。

SELECT IF(1 >0,'正确','错误') 

 ->正确

IFNULL(v1,v2)如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。

SELECT IFNULL(null,'Hello Word')

->Hello Word

CASE expression

WHEN condition1 THEN result1

WHEN condition2 THEN result2

  ...    

WHEN conditionN THEN resultN

ELSE resultEND

CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。

SELECT CASE WHEN 1 >0THEN '1 >0'WHEN 2 >0THEN '2 >0'ELSE '3 >0'END

->1 >0

注释：个人感觉有点像java中的switch语句

CAST(x AS type) 转换数据类型

字符串日期转换为日期：SELECT CAST("2017-08-29" AS DATE)->2017-08-29

用户函数？那应该是自己定义的函数吧

常用的函数有

1、MySQL 提供几个处理null的函数

1）、ifnull（expr1，expr2）：如果expr1不为null，则返回expr1，否则返回expr2；

2）、nullif（expr1，expr2）：expr1与expr2相等，则返回null，否则返回expr1；

3）、if（expr1，expr2，expr3）：类似于三目运算符，expr1为true，返回expr2，否则返回expr3；

4）、isnull（expr1）：判断expr1是否为null，expr1为null，返回true，否则返回false。

2、MySQL case函数

CASE 函数是一个流程控制函数，作用同c++中switch语句。有如下两种使用方法：

1）、用值比较

case value

when compareValue1 then result1

when compareValue2 then result2

……

else result

end

用value依次与各compareValue比较，相等则返回对应的result，并退出case函数。

2）、用条件判断

case

when condition1 then result1

when condition2 then result2

……

else result

end

各condition都为布尔表达式，从上到下判断，为true则返回对应的result，并退出case函数。

使用示例：

SELECT student_name，CASE

WHEN student_id<3 THEN ‘初级班’

WHEN student_id<=6 THEN '中级班'

ELSE ‘高级版'

END

FROM student_table

说明：上面语句选择student_name与student_id两列，只是student_id用CASE 函数替换为对应的文字说明。

3、部分时间日期函数

1）、CURTIME（）：返回完整的时间类型值，如：’2016-08-10 22：13：30‘；

2）、CURDATE（）：返回当前日期，时间部分为0，如：’2013-08-10 00：00：00‘；

3）、ADDTIME（time1，time2）：time1为time或datetime表达式，time2为time表达式，如：

addtime（’2016-08-10 22:20:00‘，’10:10:10'）返回2016-08-11 08:30:10

addtime（’2016-08-10 22:20:00‘，’1 10:10:10'）返回2016-08-12 08:30:10

addtime（’22:20:00‘，’10:10:10'）返回32:30:10,所以只有时分秒时应注意相加后的值是否大于23:59:59！

注：上面的参数值都可带微秒[.xxxxxx]。

4、部分字符串函数

1）、LEFT(param,length)：返回包含param左边的length 个字符的字符串。length 大于param 长度时返回整个param。

2）、RIGHT(param,length)：返回包含param右边length 个字符的字符串。同理。

最大用处在于param 可以是选出的某个列的数据，如最基础的用法：

select LEFT(e.name,5) from table1 e；

三、常用分组和组函数

常用组函数：

注：distinct：区别的。指定计算是否包含所有重复值。

1、avg（[distinct|all]expr）：计算多行expr的平均值，其中expr可以是变量、常量或数据列，但其类型必须是数值型；

2、count（*|[distinct|all]expr）：计算多行的记录数。expr同上，但数据类型可以是任意类型；

3、max（expr）：计算多行expr的最大值，expr同count中；

4、min（expr）；

5、sum（[distinct|all]expr）：计算多行expr的总和，expr可以是变量、常量或数据列，但数据类型必须是数值型。

group by 分组与having过滤：

语法：

#查询结果按分组列不重复的显示。

select * from tableName group by columnName1[,columnName2,……]；

说明：对于MySQL 如果被分组的列对应的其它列有多个对应值，则只显示第一条记录的值。

• create procedure用来创建 存储过程 ，create function用来创建 函数

• Delimiter命令是改变语句的结束符 ，MySQL默认的结束符为号，由于procedure和function中的号并不代表创建的结束，所以要替换成另外的结束符以便表示创建的结束

• rontine_body子句可以包含一个简单的SQL语句，也可以包含多个SQL语句， 通过begin…end将这多个SQL语句 包含在一起

• MySQL存储过程和函数中也可以包含类似create和drop等DDL语句

• comment子句用来写入对存储过程和函数的注释

• Language子句用来表示此存储过程和函数的创建语言

• 存储过程和函数被标注为deterministic表明当输入相同的参数是会返回相同的结果，反之如果是not deterministic则表示相同参数不会是相同结果，默认是not deterministic

• 相关属性短语只有咨询含义，并不是强制性的约束

• Drop procedure/function语句用来 删除指定名称的存储过程或函数

• Begin…end语句通常出现在存储过程、函数和触发器中，其中 可以包含一个或多个语句 ，每个语句用号隔开

• 标签label可以加在begin…end语句以及loop, repeat和while语句

• 语句中通过iterate和leave来控制流程，iterate表示返回指定标签位置，leave表示跳出标签

• Declare语句通常用来声明本地变量、游标、条件或者handler

• Declare语句只允许出现在begin … end语句中而且必须出现在第一行

• Declare的顺序也有要求，通常是先声明本地变量，再是游标，然后是条件和handler

• 本地变量可以通过declare语句进行声明

• 声明后的变量可以通过select … into var_list进行赋值，或者通过set语句赋值，或者通过定义游标并使用fetch … into var_list赋值

• 通过declare声明变量方法：

• MySQL支持if,case,iterate,leave,loop,while,repeat语句作为存储过程和函数中的 流程控制语句 ，另外return语句也是函数中的特定流程控制语句

• Case语句在存储过程或函数中表明了 复杂的条件选择语句

• IF语句在存储过程或函数中表明了 基础的条件选择语句

其中在 function 里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的 function 指定一个参数。

在 MySQL 中创建函数时出现这种错误的解决方法：

set global log_bin_trust_function_creators=TRUE

• Iterate语句 仅出现在loop,repeat,while循环语句中，其含义表示重新开始此循环

• Leave语句表明 退出指定标签的流程控制语句块

• 通常会用在begin…end，以及loop,repeat,while的循环语句中

• Loop语句是存储过程或函数中表达 循环执行 的一种方式

• repeat语句是存储过程或函数中表达 循环执行 的一种方式

• while语句是存储过程或函数中表达 循环执行 的一种方式

• Return语句用在 函数中，用来终结函数的执行并将指定值返回给调用者

• Cursor游标用来 声明一个数据集

• 游标的声明必须在变量和条件声明之后，在handler声明之前

• Cursor close语句用来 关闭之前打开的游标

• Cursor declare语句用来声明一个游标和指定游标对应的数据集合， 通常数据集合是一个select语句

• Cursor fetch语句用来获取游标指定数据集的 下一行数据 并将各个字段值赋予后面的变量

• Open cursor语句用来打开一个之前已经 声明好的游标

• Declare condition语句命名 特定的错误条件 ，而该特定错误可以在declare…handler中指定 处理方法

• 比如在MySQL中1051error code表示的是unknown table的错误，如果要对这

个错误做特殊处理，可以用三种方法：

• Declare handler语句用来声明一个handler来处理一个或多个特殊条件，当其中的某个条件满足时则触发其中的statement语句执行

• Statement可以是一个简单SQL语句，也可以是begin…end组成的多个语句

• Handler_action子句声明当执行完statement语句之后应该怎么办

Condition_value的值有以下几种：

• 当condition发生但没有声明handler时，则存储过程和函数依照如下规则处理

• create trigger语句用来创建一个触发器，触发器的作用是当表上有对应SQL语句发生时，则触发执行

• 触发器创建时需要 指定对应的表名 tbl_name

• Definer关键词用来指定trigger的安全环境

• Trigger_time指定触发器的执行时间，BEFORE和AFTER指定触发器在表中的 每行数据修改前或者后 执行

• Trigger_event指定触发该触发器的具体 事件

• INSERT当新的一行数据插入表中时触发，比如通过执行insert,load data,replace语句插入新数据

• UPDATE当表的一行数据被修改时触发，比如执行update语句时

• DELETE当表的一行数据被删除时触发，比如执行delete,replace语句时

• 当执行insert into … on duplicate key update语句时，当碰到重复行执行update时，则触发update下的触发器

• 从5.7.2版本开始，可以创建具有相同trigger_time和trigger_event的同一个表上的多个触发器，默认情况下按照创建的时间依次执行，通过 指定FOLLOWS/PRECEDES改变执行顺序 ，即FOLLOWS时表示新创建的触发器后执行，PRECEDES则表示新触发器先执行

• Trigger_body表示触发器触发之后要执行的一个或多个语句，在内部可以引用涉及表的字段， OLD.col_name表示行数据被修改或删除之前的字段数据，NEW.col_name表示行数据被插入或修改之后的字段数据

• Drop trigger语句用来 删除一个触发器

• If exists短语用来避免删除不存在的触发器时引发报错

• 当你执行drop table时，表上的触发器也被drop掉了

---