数据库常用函数 与 自定义函数

常用函数：

1.函数储存着 一系列 sql 语句， 调用函数 就是 一次性执行这些语句

这些内部函数可以帮助用户更加 方便的 处理表中的数据

聚合函数， 数学函数， 字符串函数， 日期和时间函数，条件判断函数，系统信息函数，加密函数，格式化函数

 注意：函数注重的是  返回结果，不注重执行过程。所以 一些语句无法执行， 所以函数并不是单纯的 sql 语句集合

1>. 聚合函数

      AVG MAX MIN SUM COUNT 需要个 GRUOP BY 配合使用

2>. 数学函数：是Mysql 中常用的 一类函数。主要用于处理 数字， 包括 整型， 浮点型；数学函数包括： 绝对值函数，正选函数，余弦函数， 获取随机数等等

ABS(x)  返回 X 的绝对值
SELECT ABS(-32)
MOD（n,m） 返回 n 被 m 除的 整数
        SELECT MOD(15,7)
 SELECT 15 % 7
FLOOR (x) 返回不大于 X 的最大整数值 向下取整
SELECT FLOOR(1.23)     1
SELECT FLOOR(-1.23)   -2
CEILING(x) 返回不小于 X 的最小整数值  向上取整
SELECT CEILING（1.23）   2
SELECT CEILING（-1.23） -1
ROUND(x) 返回参数 x 的 四舍五入的一个 整数
SELECT ROUND(1.58)      2
SELECT ROUND(-1.58)    -2

 

3>. 字符串函数

ASCII (str)： 返回支付串的 ASCII编码, 如果 str  为 空字符串 返回 0， 如果是 str NULL 则返回 NULL
SELECT  ASCII('2')        50    SELECT  ASCII(2)  50
SELECT  ASCII('dx')      100
CONCAT(str1,str2): 返回来自与 参数连接的字符串，  如果任何参数 是 NULL， 返回 NULL, 可以有 超过 2个的参数，一个数字参数被变换为 等价的字符串形式
SELECT CONCAT('My','S','QL')
SELECT CONCAT('My',NULL,'QL')  NULL
SELECT CONCAT(14,3)
LENGTH(str): 返回字符串 str 的长度
SELECT LENGTH('test')
LOCATE(substr,str): 返回 字符串substr  在 str 中第一次出现的位置，如果substr 不是在 str里面，返回0，
SELECT LOCATE('bar','footbarbar')    5
SELECT LOCATE('xbar','footvor')        0
TRIM(str): 去前后空格       LTRIM(str) 去左空格    RTRIM(str) 去右空格
SELECT TRIM('bat ')
REPLACE(str,from_str, to_str): 返回字符串 str, 其字符串 form_str的所有 出现有 字符串 由 to_str代替
SELECT REPLACE('www.mysql.com','w','Ww')  WwWwWw.mysql.com
INSERT(str,pos,len,newstr): 返回字符串str. 在位置 pos起始的 子串 len个字符长的 子串 由 newstr 代替
SELECT INSERT('whatareyou',5,3,'is')                whatisyou

4>. 日期和时间函数

DAYOFWEEK(date): 返回日期 date 的星期索引， 1-> 星期天 .... 7-> 星期六
SELECT DAYOFWEEK('1988-02-03')
SELECT DAYOFWEEK(now())
YEAR(date): 返回date的年份，范围在1000 到 999
SELECT YEAR('98-08-09')
HOUR(date): 返回date 的小时 范围0到23
SELECT HOUR('10:05:03')
DATE_ADD(date, INTERVAL expr type) 进行日期的 增加操作， 可以精确到秒
DATE_SUB(date, INTERVAL expr type) 进行日期的减少操作， 可以精确到秒
SELECT '1997-12-32 23:59:59' + INTERVAL  1 SECOND
SELECT DATE_ADD(now(),INTERVAL 1 SECOND)
SELECT DATE_ADD(now(),INTERVAL -1
SELECT DATE_SUB(now(),INTERVAL 1 YEAR)
 
CURRENT_TIME: 以 'HH:MM:SS'   或者  HHMMSS 格式返回当前的时间值
SELECT  CURTIME()
NOW() 以 'YYYY-MM-DD HH:MM:SS'  或者 YYYYMMDDHHMMSS 格式返回当前的日期和时间
SELECT NOW()

 

5>. 条件判断函数 (流程控制函数)

1. CASE  WHEN   THEN  END

SELECT
     CASE                                           --------------->开始条件
     WHEN sex = '1' THEN  '男'          --------------->1 成立
     WHEN sex = '0' THEN  '女'          --------------->0 成立
     ELSE  '未设置'                             --------------->都没成立 默认值
 END                                                 --------------->条件结束
 FROM  user                                     --------------->整体理解, user 中如果 sex='1' 返回男 sex='2'返回女 ，否则 返回 未设置

---用法---

SELECT  ename,
 CASE  
     WHEN mrg IS NULL THEN
         '经理'
     ELSE
         '员工'
 END  as  '职位'
 FROM emp

---用法---

SELECT  ename,mrg
 CASE  
     WHEN IS NULL THEN
         '经理'
     ELSE
         '员工'
 END  as  '职位'
 FROM emp WHERE eno = 1001

 


自定义函数：

概念： 函数只会返回一个值，不允许返回一个结果集， 所以函数不允许返回多个值的情况，即使是查询语句。

语法：

CREATE FUNCTION 函数名（参数列表） RETURN 数据类型
BEGIN
 sql 语句
RETURN 值
END

--最简单的 仅  有一条 sql 的 函数

CREATE FUNCTION myselect2() RETURNS INT  RETURN 666

--调用： SELECT myselect2()

 DETERMINISTIC

 当二进制日志 启动后。 这个变量就会启用， 它控制 是否可以 信任 储存函数的 创建者，不会创建 写入二进制日志 引起不安全事件的储存函数。

  1. 如果 数据库 没有使用 主从复制，那么久可以将 参数 log_trust_function_creators 设置为1
mysql> set global log_bin_trust_funciton_creator = 1

     2. 明确指明函数 的类型， 如果我们开启 二进制 日志，那么我们就必须为我们的 function 指定参数， 其中 下面几种参数类型里面， 只有 DETERMINISTIC，NO SQL 和 READS SQL  DATA 被支持， 这样一来 相当于 明确告知 MySQl 服务器 这个函数 不会修改数据

  • DETERMINISTIC              不确定的
  • NO SQL                             没有SQL语句，当然也不会修改数据
  • READS SQL DATA            只是读数据，当然也不会修改数据
  • MODIFIES SQL DATA       是要修改数据的
  • CONTAINS SQL                包含了SQL语句的

或者在 创建函数的时候  声明函数 为 只读数据的属性

CREATE FUNCTION myselect2()  RETURNS INT READS SQL DATA

CREATE FUNCTION myselect2()  RETURNS INT READS SQL DATA RETURN 666

SELECT myselect2()

DROP FUNCTION myselect2


-- 不带参数
CREATE FUNCTION myselect3() RETURNS INT DETERMINISTIC
BEGIN
	DECLARE c INT;
	SELECT ssex FROM student WHERE sname = '孙凤' INTO c;
	RETURN c;
END

SELECT myselect3()


-- 带参数
CREATE FUNCTION myselect4(cname VARCHAR(20)) RETURNS INT DETERMINISTIC
BEGIN
	DECLARE c INT;
	SELECT ssex FROM student WHERE sname = cname INTO c;
	RETURN c;
END

SELECT myselect4('郑竹')

 

 

函数的调用：

1.直接 使用函数名（），就能 调用【但函数返回是一个 结果， sql中不使用 select 的话 任何结果都是无法显示出来的】

   如何想 使用参数  可以 函数名（参数）

2. 函数的查看

查看函数创建语句   ： show create function 函数名
查看所有函数      ： show function status [like 'pattern']

3.函数的修改

   函数的修改 只能修改一些如 comment  的 选项，不能 修改内部的 sql 语句和 参数列表

4.函数的 删除

Drop function 函数名