网站建设视频万网,常见的推广方式,wordpress域名如何申请,网站开发研究综述第07章_单行函数
讲师#xff1a;尚硅谷-宋红康#xff08;江湖人称#xff1a;康师傅#xff09;
官网#xff1a;http://www.atguigu.com 1. 函数的理解
1.1 什么是函数
函数在计算机语言的使用中贯穿始终#xff0c;函数的作用是什么呢#xff1f;它可以把我们经…第07章_单行函数
讲师尚硅谷-宋红康江湖人称康师傅
官网http://www.atguigu.com 1. 函数的理解
1.1 什么是函数
函数在计算机语言的使用中贯穿始终函数的作用是什么呢它可以把我们经常使用的代码封装起来需要的时候直接调用即可。这样既提高了代码效率又提高了可维护性。在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数可以极大地提高用户对数据库的管理效率。 从函数定义的角度出发我们可以将函数分成内置函数和自定义函数。在 SQL 语言中同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数而自定义函数是我们根据自己的需要编写的本章及下一章讲解的是 SQL 的内置函数。
1.2 不同DBMS函数的差异
我们在使用 SQL 语言的时候不是直接和这门语言打交道而是通过它使用不同的数据库软件即 DBMS。DBMS 之间的差异性很大远大于同一个语言不同版本之间的差异。实际上只有很少的函数是被 DBMS 同时支持的。比如大多数 DBMS 使用||或者来做拼接符而在 MySQL 中的字符串拼接函数为concat()。大部分 DBMS 会有自己特定的函数这就意味着采用 SQL 函数的代码可移植性是很差的因此在使用函数的时候需要特别注意。
1.3 MySQL的内置函数及分类
MySQL提供了丰富的内置函数这些函数使得数据的维护与管理更加方便能够更好地提供数据的分析与统计功能在一定程度上提高了开发人员进行数据分析与统计的效率。
MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里我将这些丰富的内置函数再分为两类单行函数、聚合函数或分组函数。
两种SQL函数 1.4 单行函数介绍
什么是单行函数
单行函数作用于一个数据并对一个数据返回一个值
单行函数能嵌套使用
2. 数值函数
2.1 基本函数
函数用法ABS(x)返回x的绝对值SIGN(X)返回X的符号。正数返回1负数返回-10返回0PI()返回圆周率的值CEIL(x)CEILING(x)返回大于或等于某个值的最小整数FLOOR(x)返回小于或等于某个值的最大整数LEAST(e1,e2,e3…)返回列表中的最小值GREATEST(e1,e2,e3…)返回列表中的最大值MOD(x,y)返回X除以Y后的余数RAND()返回0~1的随机值RAND(x)返回0~1的随机值其中x的值用作种子值相同的X值会产生相同的随机数ROUND(x)返回一个对x的值进行四舍五入后最接近于X的整数ROUND(x,y)返回一个对x的值进行四舍五入后最接近X的值并保留到小数点后面Y位TRUNCATE(x,y)返回数字x截断为y位小数的结果SQRT(x)返回x的平方根。当X的值为负数时返回NULL
举例
SELECT ABS(2), ABS(-2), ABS(0), SIGN(2), SIGN(-2), SIGN(0), PI(), CEIL(32.32), FLOOR(32.32), CEILING(-43.23), FLOOR(-43.23), MOD(12,5), MOD(-12,5);SELECT RAND(), RAND(), RAND(10), RAND(10), RAND(-1), RAND(-1);SELECT ROUND(654.456), ROUND(654.456,0), ROUND(654.456,1), ROUND(654.456,2), ROUND(654.456,-1), ROUND(654.456,-2);SELECT TRUNCATE(654.456,0), TRUNCATE(654.456,1), TRUNCATE(654.456,2), TRUNCATE(654.456,-1), TRUNCATE(654.456,-2);SELECT SQRT(4), SQRT(-4);2.2 角度与弧度互换函数
函数用法RADIANS(x)将角度转化为弧度其中参数x为角度值DEGREES(x)将弧度转化为角度其中参数x为弧度值
SELECT RADIANS(0), RADIANS(30), RADIANS(45), RADIANS(60), RADIANS(90), RADIANS(180), RADIANS(360);SELECT DEGREES(0), DEGREES(PI()/6), DEGREES(PI()/4), DEGREES(PI()/3), DEGREES(PI()/2), DEGREES(PI()), DEGREES(2*PI());2.3 三角函数
函数用法SIN(x)返回x的正弦值其中参数x为弧度值ASIN(x)返回x的反正弦值即获取正弦为x的值。如果x的值不在-1到1之间则返回NULLCOS(x)返回x的余弦值其中参数x为弧度值ACOS(x)返回x的反余弦值即获取余弦为x的值。如果x的值不在-1到1之间则返回NULLTAN(x)返回x的正切值其中参数x为弧度值ATAN(x)返回x的反正切值即返回正切值为x的值ATAN2(m,n)返回两个参数的反正切值COT(x)返回x的余切值其中X为弧度值
SELECT SIN(RADIANS(30)), SIN(RADIANS(90)), COS(RADIANS(60)), COS(RADIANS(0)), TAN(RADIANS(45)), COT(RADIANS(45));SELECT ASIN(0.5), ASIN(1), ASIN(2), ASIN(-2), ACOS(0.5), ACOS(1), ACOS(2), ACOS(-2), ATAN(1);2.4 指数与对数
函数用法POW(x,y)POWER(X,Y)返回x的y次方EXP(X)返回e的X次方其中e是一个常数2.718281828459045LN(X)LOG(X)返回以e为底的X的对数当X 0 时返回的结果为NULLLOG10(X)返回以10为底的X的对数当X 0 时返回的结果为NULLLOG2(X)返回以2为底的X的对数当X 0 时返回NULL
SELECT POW(2,2), POWER(2,4), EXP(2), LN(EXP(2)), LN(-1*EXP(2)), LOG(EXP(2)), LOG(-1*EXP(2)), LOG10(10), LOG10(-10), LOG2(2), LOG2(-2);2.5 进制间的转换
函数用法BIN(x)返回x的二进制编码OCT(x)返回x的八进制编码HEX(x)返回x的十六进制编码CONV(x,f1,f2)返回f1进制数变成f2进制数
SELECT BIN(10), OCT(10), HEX(10), CONV(10,2,10), CONV(10,10,8), CONV(10,10,16);3. 字符串函数
函数用法ASCII(S)返回字符串S中的第一个字符的ASCII码值CHAR_LENGTH(s)返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同LENGTH(s)返回字符串s的字节数和字符集有关CONCAT(s1,s2,…,sn)连接s1,s2,…,sn为一个字符串CONCAT_WS(x, s1,s2,…,sn)同CONCAT(s1,s2,…)函数但是每个字符串之间要加上xINSERT(str, idx, len, replacestr)将字符串str从第idx位置开始len个字符长的子串替换为字符串replacestrREPLACE(str, a, b)用字符串b替换字符串str中所有出现的字符串aUPPER(s) 或UCASE(s)将字符串s的所有字母转成大写字母LOWER(s) 或LCASE(s)将字符串s的所有字母转成小写字母LEFT(str,n)返回字符串str最左边的n个字符RIGHT(str,n)返回字符串str最右边的n个字符LPAD(str, len, pad)用字符串pad对str最左边进行填充直到str的长度为len个字符RPAD(str ,len, pad)用字符串pad对str最右边进行填充直到str的长度为len个字符LTRIM(s)去掉字符串s左侧的空格RTRIM(s)去掉字符串s右侧的空格TRIM(s)去掉字符串s开始与结尾的空格TRIM(s1 FROM s)去掉字符串s开始与结尾的s1TRIM(LEADING s1 FROM s)去掉字符串s开始处的s1TRIM(TRAILING s1 FROM s)去掉字符串s结尾处的s1REPEAT(str, n)返回str重复n次的结果SPACE(n)返回n个空格STRCMP(s1,s2)比较字符串s1,s2的ASCII码值的大小SUBSTR(s,index,len)返回从字符串s的index位置其len个字符作用与SUBSTRING(s,n,len)、MID(s,n,len)相同LOCATE(substr,str)返回字符串substr在字符串str中首次出现的位置作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到返回0ELT(m,s1,s2,…,sn)返回指定位置的字符串如果m1则返回s1如果m2则返回s2如果mn则返回snFIELD(s,s1,s2,…,sn)返回字符串s在字符串列表中第一次出现的位置FIND_IN_SET(s1,s2)返回字符串s1在字符串s2中出现的位置。其中字符串s2是一个以逗号分隔的字符串REVERSE(s)返回s反转后的字符串NULLIF(value1,value2)比较两个字符串如果value1与value2相等则返回NULL否则返回value1注意MySQL中字符串的位置是从1开始的。 SELECT ASCII(abc), CHAR_LENGTH(hello), CHAR_LENGTH(我们), LENGTH(hello), LENGTH(我们);SELECT CONCAT(e1.last_name, work for ,e2.last_name)
FROM employees e1 JOIN employees e2
ON e1.manager_id e2.employee_id;SELECT CONCAT_WS(,hello,world,hello,beijing), CONCAT_WS(-,hello,world,hello,beijing);SELECT INSERT(helloworld,2,4,aaaa), REPLACE(helloworld,ll,mm);SELECT UPPER(Hello), LOWER(Hello);SELECT LEFT(hello,2), LEFT(hello,10), RIGHT(hello,3) , RIGHT(hello,10);SELECT employee_id, last_name, LPAD(salary,10,*)
FROM employees;SELECT employee_id, last_name, RPAD(salary,10,*)
FROM employees;SELECT CONCAT(---,LTRIM( h e l lo ),***), CONCAT(---,RTRIM( h e l lo ),***), CONCAT(---,TRIM( h e l lo ),***), TRIM(o FROM oohelloo), TRIM(LEADING o FROM oohelloo), TRIM(TRAILING o FROM oohelloo);SELECT REPEAT(hello,4), SPACE(5), LENGTH(SPACE(5)), STRCMP(abc,abc) , STRCMP(abc,abd), STRCMP(abc,abe), STRCMP(abd,abc), STRCMP(abe,abc);SELECT SUBSTR(hello,2,2), LOCATE(l,hello), LOCATE(lll,hello);SELECT ELT(1,a,b,c,d), ELT(2,a,b,c,d), ELT(3,a,b,c,d);SELECT FIELD(bb,aa,bb,cc,dd), FIND_IN_SET(bb,aa,bb,cc,dd);SELECT REVERSE(abc);SELECT employee_id, first_name, last_name, NULLIF(LENGTH(first_name), LENGTH(last_name))
FROM employees;4. 日期和时间函数 参数date代表日期CURDATE() 只包含年、月、日 参数time代表时间CURTIME() 只包含时、分、秒 参数now代表日期和时间NOW() 包含年、月、日、时、分、秒 4.1 获取日期、时间
函数用法CURDATE() CURRENT_DATE()返回当前日期只包含年、月、日时分秒默认为000CURTIME() CURRENT_TIME()返回当前时间只包含时、分、秒年月日默认为当前年月日NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()返回当前系统日期和时间UTC_DATE()返回UTC世界标准时间日期UTC_TIME()返回UTC世界标准时间时间
SELECT CURDATE(), CURRENT_DATE(), CURTIME(), CURRENT_TIME(), NOW(), NOW()0, UTC_DATE(), UTC_DATE()0, UTC_TIME(), UTC_TIME()0;4.2 日期与时间戳的转换
函数用法UNIX_TIMESTAMP()以UNIX时间戳的形式返回当前日期和时间UNIX_TIMESTAMP(date/time/now)将date/time/now以UNIX时间戳的形式返回FROM_UNIXTIME(timestamp)将UNIX时间戳转换为日期和时间
SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(CURDATE()),UNIX_TIMESTAMP(CURTIME()), UNIX_TIMESTAMP(NOW()), FROM_UNIXTIME(1676553223); 4.3 获取月份、星期、星期数、天数等函数
函数用法YEAR(date/now) / MONTH(date/now) / DAY(date/now)返回具体的年、月、日HOUR(time/now) / MINUTE(time/now) / SECOND(time/now)返回具体的时、分、秒MONTHNAME(date/now)返回月份January…DAYNAME(date/now)返回星期几MONDAYTUESDAY…SUNDAYWEEKDAY(date/now)返回周几注意周1是0周2是1。。。周日是6QUARTER(date/now)返回季度范围为14WEEK(date/now) WEEKOFYEAR(date/now)返回一年中的第几周DAYOFYEAR(date/now)返回一年中的第几天DAYOFMONTH(date/now)返回所在月份的第几天DAYOFWEEK(date/now)返回周几注意周日是1周一是2。。。周六是7
SELECT NOW(), YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()); SELECT NOW(), MONTHNAME(NOW()), DAYNAME(NOW()), WEEKDAY(NOW()), QUARTER(NOW()), WEEK(NOW()), DAYOFYEAR(NOW()), DAYOFMONTH(NOW()), DAYOFWEEK(NOW()); 4.4 日期的操作函数
函数用法EXTRACT(type FROM now)返回now中特定的部分type指定返回的值
EXTRACT(type FROM now)函数中type的取值与含义 SELECT NOW(), EXTRACT(MICROSECOND FROM NOW()), EXTRACT(SECOND FROM NOW()), EXTRACT(MINUTE FROM NOW()), EXTRACT(HOUR FROM NOW());4.5 时间和秒钟转换的函数
函数用法TIME_TO_SEC(time/now)将time/now转化为秒并返回结果值。转化的公式为小时*3600分钟*60秒SEC_TO_TIME(seconds)将 seconds 转化为时间
SELECT NOW(), TIME_TO_SEC(NOW()), SEC_TO_TIME(78259); 4.6 计算日期和时间的函数
第1组
函数用法DATE_ADD(now,INTERVAL expr type)ADDDATE(now,INTERVAL expr type)返回与now相差INTERVAL时间段的日期和时间DATE_SUB(now,INTERVAL expr type)SUBDATE(now,INTERVAL expr type)返回与now相差INTERVAL时间段的日期和时间
上述函数中type的取值 SELECT NOW(), DATE_ADD(NOW(),INTERVAL 1 DAY), DATE_SUB(NOW(),INTERVAL 1 DAY);第2组
函数用法ADDTIME(time1/now1,time2)返回time1/now1加上time2后的日期和时间。当time2为一个数字时代表的是秒可以为负数SUBTIME(time1/now1,time2)返回time1/now1减去time2后的日期和时间。当time2为一个数字时代表的是秒可以为负数DATEDIFF(date1/now1,date2/now2)返回date1/now1- date2/now2的日期间隔天数TIMEDIFF(time1, time2)或TIMEDIFF(now1,now2)返回time1- time2或now1-now2的时间间隔FROM_DAYS(N)返回从0000年1月1日起N天以后的日期TO_DAYS(date/now)返回date/now距离0000年1月1日的天数LAST_DAY(date/now)返回date/now所在月份的最后一天的日期MAKEDATE(year,n)针对给定年份与所在年份中的天数返回一个日期MAKETIME(hour,minute,second)将给定的小时、分钟和秒组合成时间并返回PERIOD_ADD(x,n)返回x加上n后的时间x日期和时间0)
SELECT NOW(), ADDTIME(NOW(),20), ADDTIME(NOW(),1:1:1), SUBTIME(NOW(),20), SUBTIME(NOW(),1:1:1); SELECT NOW(), DATEDIFF(NOW(),2023-02-16), DATEDIFF(NOW(),2023-02-16 00:00:00), TIMEDIFF(CURTIME(),00:00:00), TIMEDIFF(NOW(),2023-02-16 00:00:00);SELECT NOW(), FROM_DAYS(366), TO_DAYS(NOW()), LAST_DAY(NOW()), MAKEDATE(YEAR(NOW()),12), MAKETIME(10,21,23), PERIOD_ADD(20200101010101,10);4.7 日期的格式化与解析
函数用法DATE_FORMAT(date/now,fmt)按照字符串fmt格式化date/nowTIME_FORMAT(time/now,fmt)按照字符串fmt格式化time/nowGET_FORMAT(date_type,format_type)返回日期字符串的显示格式STR_TO_DATE(str, fmt)按照字符串fmt对str进行解析解析为一个日期
上述非GET_FORMAT函数中fmt参数常用的格式符
格式符说明格式符说明%Y4位数字表示年份%y表示两位数字表示年份%M月名表示月份January,…%m两位数字表示月份01,02,03。。。%b缩写的月名Jan.Feb.…%c数字表示月份1,2,3,…%D英文后缀表示月中的天数1st,2nd,3rd,…%d两位数字表示月中的天数(01,02…)%e数字形式表示月中的天数1,2,3,4,5…%H两位数字表示小数24小时制01,02…%h和%I两位数字表示小时12小时制01,02…%k数字形式的小时24小时制(1,2,3)%l数字形式表示小时12小时制1,2,3,4…%i两位数字表示分钟00,01,02%S和%s两位数字表示秒(00,01,02…)%W一周中的星期名称Sunday…%a一周中的星期缩写Sun.Mon.,Tues.…%w以数字表示周中的天数(0Sunday,1Monday…)%j以3位数字表示年中的天数(001,002…)%U以数字表示年中的第几周1,2,3。。其中Sunday为周中第一天%u以数字表示年中的第几周1,2,3。。其中Monday为周中第一天%T24小时制%r12小时制%pAM或PM%%表示%
GET_FORMAT函数中date_type和format_type参数取值如下 举例
SELECT NOW(), DATE_FORMAT(NOW(),%Y-%m-%d), DATE_FORMAT(NOW(),%m/%d/%Y), TIME_FORMAT(NOW(),%h:%i:%s);SELECT NOW(), DATE_FORMAT(NOW(),GET_FORMAT(DATE,USA)), TIME_FORMAT(NOW(),GET_FORMAT(TIME,USA)), DATE_FORMAT(NOW(),GET_FORMAT(DATETIME,USA));SELECT STR_TO_DATE(02/17/2023,%m/%d/%Y);5. 流程控制函数
流程处理函数可以根据不同的条件执行不同的处理流程可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
函数用法IF(value,value1,value2)如果value的值为TRUE返回value1否则返回value2IFNULL(value1, value2)如果value1不为NULL返回value1否则返回value2CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE resultn] END相当于Java的if…else if…else…CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END相当于Java的switch…case…
SELECT employee_id, salary, commission_pct, 12 * salary * (1 IF(commission_pct IS NOT NULL,commission_pct,0)) annual_sal
FROM employees;
或
SELECT employee_id, salary, commission_pct, 12 * salary * (1 IFNULL(commission_pct,0)) annual_sal
FROM employees;SELECT employee_id, salary, CASE WHEN salary 15000 THEN 富哥WHEN salary 10000 THEN 潜力股WHEN salary 8000 THEN 屌丝ELSE 草根 END details,department_id
FROM employees;SELECT employee_id, salary, CASE WHEN salary 15000 THEN 富哥WHEN salary 10000 THEN 潜力股WHEN salary 8000 THEN 屌丝END details,department_id
FROM employees; SELECT employee_id, salary, department_id, CASE department_id WHEN 10 THEN salary * 1.1WHEN 20 THEN salary * 1.2WHEN 30 THEN salary * 1.3END details
FROM employees
WHERE department_id IN (10,20,30);6. 加密与解密函数
加密与解密函数主要用于对数据库中的数据进行加密和解密处理以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。
函数用法PASSWORD(str)返回字符串str的加密版本41位长的字符串。加密结果不可逆常用于用户的密码加密MD5(str)返回字符串str的md5加密后的值也是一种加密方式。若参数为NULL则会返回NULLSHA(str)从原明文密码str计算并返回加密后的密码字符串当参数为NULL时返回NULL。SHA加密算法比MD5更加安全。ENCODE(value,password_seed)返回使用password_seed作为加密密码加密valueDECODE(value,password_seed)返回使用password_seed作为加密密码解密value
#PASSWORD()在mysql8.0中弃用
SELECT MD5(mysql), MD5(NULL), SHA(mysql), SHA(NULL);
#ENCODE()和DECODE()在mysql8.0中弃用7. 获取MySQL信息函数
MySQL中内置了一些可以查询MySQL信息的函数这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。
函数用法VERSION()返回当前MySQL的版本号CONNECTION_ID()返回当前MySQL服务器的连接数DATABASE()SCHEMA()返回MySQL命令行当前所在的数据库USER()CURRENT_USER()、SYSTEM_USER()SESSION_USER()返回当前连接MySQL的用户名返回结果格式为“主机名用户名”CHARSET(value)返回字符串value自变量的字符集COLLATION(value)返回字符串value的比较规则
SELECT VERSION(), CONNECTION_ID(), DATABASE(), SCHEMA(), USER(), CHARSET(尚硅谷), COLLATION(尚硅谷); 8. 其他函数
MySQL中有些函数无法对其进行具体的分类但是这些函数在MySQL的开发和运维过程中也是不容忽视的。
函数用法FORMAT(value,n)返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位CONV(value,from,to)将value的值进行不同进制之间的转换INET_ATON(ipvalue)将以点分隔的IP地址转化为一个数字INET_NTOA(value)将数字形式的IP地址转化为以点分隔的IP地址BENCHMARK(n,expr)将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间CONVERT(value USING char_code)将value所使用的字符编码修改为char_code
SELECT FORMAT(645.456,3), FORMAT(645.456,2), FORMAT(645.456,1), FORMAT(645.456,0), FORMAT(645.456,-1), FORMAT(645.456,-2), FORMAT(645.456,-3);SELECT CONV(16,10,2), CONV(8888,10,16), CONV(NULL,10,2);SELECT INET_ATON(192.168.1.100), INET_NTOA(3232235876);SELECT BENCHMARK(10000000,MD5(mysql));#看运行时间不看返回结果返回结果都是0不懂为啥SELECT CHARSET(atguigu), CONVERT(atguigu USING gbk), CHARSET(CONVERT(atguigu USING gbk));
