《MySQL数据库》5-1 函数教学材料

函数01系统函数02自定义函数第5章问题导入MySQL数据库内置了一些常用函数，包括数学函数、字符串函数、日期和时间函数、系统信息函数、加密函数和格式化函数。这些常用函数方便了我们日常对数据库、对表及查询等的操作。第5章01系统函数系统函数调用语法：select函数名(参数);注意：系统函数不能被修改和删除第5章01系统函数。1.数学函数函数名作用ABS(x)返回x的绝对值SQRT(x)返回x的非负2次方根MOD(x,y)返回x被y除后的余数CEILING(x)返回不小于x的最小整数FLOOR(x)返回不大于x的最大整数ROUND(x,y)对x进行四舍五入操作，小数点后保留y位TRUNCATE(x,y)舍去x中小数点y位后的数字SIGN(x)返回x的符号，-1，1或0第5章01系统函数。【例1】求-14.26的绝对值。SELECTABS(-14.26);【例2】求1/4的非负平方根。SELECTSQRT(1/4);【例3】求不小于3.1415926的最小值。SELECTCEILING(3.1415926);第5章01系统函数。【例4】对3.1415926四舍五入，保留小数点后3位。SELECTROUND(3.1415926,3);【例5】舍去3.1415926小数点后3位的数字。SELECTTRUNCATE(3.1415926,3);第5章01系统函数。2.字符串函数函数名作用LENGTH(str)返回字符串str的长度CONCAT(s1,s2,…)返回一个或者多个字符串连接产生的新的字符串TRIM(str)删除字符串两侧的空格REPLACE(str,s1,s2)使用字符串s2替换字符串str中所有的字符串s1SUBSTRING(str,n,len)返回字符串str的子串，起始位置为n,长度为lenREVERSE(str)返回字符串反转后的结果LOCATE(s1,str)返回子串s1在字符串str中的起始位置第5章01系统函数。【例6】求字符串'China'的长度。SELECTLENGTH('China');【例7】将字符串'llawtaerg'反转输出。SELECTREVERSE('llawtaerg’);第5章01系统函数。【例8】将字符串'itisaninterestingstory'中的所有'i'用'1'代替。SELECTREPLACE('itisaninterestingstory','i','I');【例9】将字符串'ItisMonday!'中的星期输出。SELECTSUBSTRING('ItisMonday!',7);第5章01系统函数。3.日期函数函数名作用CURDATE()/CURTIME()/NOW()获取系统当前日期YEAR()/MONTH()获取给定日期的年份/月份SYSDATE()获取系统当前的日期和时间TIME_TO_SEC()返回将时间转换成秒的结果ADDDATE(m,[INTERVAL]n[datepart])执行日期的加运算(默认以天为单位),datepart可查阅SUBDATE(m,[INTERVAL]n[datepart])执行日期的减运算(以天为单位)DATE_FORMAT()格式化输出日期和时间值第5章01系统函数。【例10】使用不同方法获取系统当前时间。SELECTNOW();SELECTCURTIME();【例11】获取系统当前时间所在的年份与月份。ELECTYEAR(NOW());SELECTMONTH(NOW());【例12】将系统当前时间增加一个月输出。SELECTADDDATE(NOW(),INTERVAL1MONTH);第5章01系统函数。4.条件判断函数函数名作用IF(expr,v1,v2)如果表达式expr的值为ture(1),则返回v1,否则返回v2IFNULL(v1,v2)如果v1不为NULL返回v1,否则返回v2CASEexprWHENv1THENr1[WHENv2THENr2……][ELSEm]END如果expr值等于v1、v2等，则返回值为其对应的THEN后面的r1、r2，否则返回ELSE后面的结果m第5章01系统函数。【例13】输出学生的姓名与性别，要求性别为男的输出M，性别为女的输出F。先选择数据库stu：USEstu;SELECTsname,IF(sex='男','M','F')FROMstudent;第5章01系统函数。【例14】输出课程的名称与其对应的学分，如果该课程无学分（空直），输出'该课程暂无学分'。SELECTcname,IFNULL(credit,'该课程暂无学分')FROMcourse;SELECTcname,IF(creditISNOTNULL,credit,'该课程暂无学分')FROMcourse;SELECTcname,IF(credit,credit,'该课程暂无学分')FROMcourse;第5章01系统函数。5.系统信息函数函数名作用VERSION()获取数据库的版本号CONNECTION_ID()获取服务器的连接数DATABASE()、SCHEMA()获取当前数据库名USER()、SYSTEM_USER()、SESSION_USER()获取当前用户名CURRENT_USER()、CURRENT_USER获取当前用户名CHARSET(str)获取字符串str的字符集COLLATION(str)获取字符串str的字符排序方法LAST_INSERT_ID()获取最近生成的AUTO_INCREMENT值第5章01系统函数。6.加密函数函数名作用PASSWORD(str)对字符串str进行加密。经此函数加密后的数据是不可逆的。其经常用于对普通数据进行加密MD5(str)对字符串str进行MD5加密。经常用于对普通数据进行加密ENCODE(str,pass_str)使用字符串pass_str来加密字符串str。加密后的结果是一个二进制数，必须使用BLOB类型的字段来保存它DECODE(crypt_str,pass_str)使用字符串pass_str来为crypt_str解密第5章01系统函数。7.其他函数函数名作用FORMAT(X,D)将数字X格式化，将X保留到小数点后D位，截断时要进行四舍五入CONV(N,from_base,to_base)不同进制数之间的转换，返回值为数值N的字符串表示，由from_base进制转换为to_base进制INET_ATON(expr)给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以使4或8比特INET_NTOA(expr)给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示BENCHMARK(count,expr)重复执行count次表达式expr，它可以用于计算MySQL处理表达式的速度，结果值通常是0（0只是表示很快，并不是没有速度）。另一个作用是用它在MySQL客户端内部报告语句执行的时间CONVERT(strUSINGcharset)使用字符集charset表示字符串str第5章02自定义函数MySQL数据库虽然提供了一些内置的函数，但是在开发过程中，有些业务仅靠现有的内置函数并不能满足，这时就可以使用自定义函数。自定义函数(user-definedfunctionUDF)是对sql的扩展，其语法和函数相同。第5章02自定义函数1.创建自定义函数CREATEFUNCTION<函数名>[参数列表]RETURNS<返回值类型><声明函数类型><函数体>第5章02自定义函数2.查看自定义函数查看数据库中存在哪些自定义函数：SHOWFUNCTIONSTATUS；查看数据库中某个具体的自定义函数：SHOWCREATEFUNCTION<函数名>；第5章02自定义函数3.调用自定义函数SELECT<函数名>[参数列表];4.删除自定义函数DROPFUNCTION[IFEXISTS]<函数名>;第5章02自定义函数注意：MySQL8.0的log_bin（也称作binlog）默认是开启的，在调用函数、存储过程、触发器时，会出现错误号为1418的错误，这是因为需要使用DETERMINISTIC或NOSQL与READSSQLDATA等语句对函数进行声明。当log_bin处于关闭状态时，可省略前面的声明语句，而大多数情况下log_bin是不需要开启的，所以本章节均指的是log_bin关闭状态。第5章02自定义函数【例15】创建一个自定义函数hello(),返回一行信息“HelloMySQL！”，然后对该函数进行查看、调用和删除。（1）输入命令查看log_bin。SHOWVARIABLESLIKE'log_bin’;查看结果可以看出，log_bin的值为ON（MySQL8.0默认log_bin是开启的），所以要先关闭log_bin。第5章02自定义函数（2）在MySQL安装目录下找到my.ini文件打开my.ini文件，在[mysqld]下面添加skip-log-bin（3）重新启动MySQL服务器。重启方式一：右键单击【我的电脑】，选择【管理】，在弹出的窗体中单击【服务与应用程序】中的【服务】，在右侧列表中找到MySQL，选中MySQL单击【重启动】第5章02自定义函数重启方式二：点击【开始】，打开【运行】窗口(快捷键Win+R)。输入netstopmysql，单击【确定】，关闭MySQL服务器，然后再次打开运行窗口，输入netstartmysql，单击确定，开启MySQL服务器（4）切换到前面的命令窗口，输入'\q'退出mysql，然后重新登录，查看log_bin，此时log_bin的值为OFF，表示log_bin关闭。第5章02自定义函数（5）创建自定义函数hello()。DELIMITER$$CREATEFUNCTIONhello()RETURNSVARCHAR(255)BEGINRETURN'HelloMySQL!';END$$DELIMITER;注意：使用DELIMITER是因为MySQL中默认是使用分号来结束一个命令的，我们定义的函数体中一条命令写完时会用分号来结束，而MySQL会误以为函数体已经定义完成，因为我们需要定义一个新的标识符来标识一个命令的结束