[mysql]mysql的全部单行函数

单行函数

几乎我们认识的语言都会对一些常用的功能进行,封装,有些叫函数,有些叫方法(Java),后期我们还可以自定义函数.

现在我们就当大家是没有语言基础,我们来从头开始讲.不过大家肯定接触过,中学说的函数,y=f(x)f代表的就是function的缩写,这里其y=2x+1fx代表的就是封装的内容.

函数在我们的语言贯穿始终,他可以把我们经常使用的方法进行封装,需要的时候直接调用就可以,这样提高了代码的效率和可维护性.维护起来也比较方便,我们sql中可以对检索的数据进行函数操作,可以提高对数据的管理效率.

不同数据库管理系统DBMS函数的差异

我们的数据库提供的现成的函数,我们来说一下不同的数据库管理系统的差异.我们sql的语言也是定义了不同的函数,如果是不同的数据库软件,他们之间函数的差异很大,远大于一个语言不同版本的差异,sql,java,python也好,他们虽然版本会有差异,但是如果看mysql,oracle,pgsql,这些的函数差异都是很大的比如很多DBMS都是用||作为连接的的操作

如果我们mysql里面就没有输出结果

,

mysql里面是用concat来进行的连接操作,首先我们来针对mysql的内置函数和分类.

我之后会发一个函数的内容,大家自行寻找,把大部分的函数都放在里面了,基本上都放全了.只有一些用的比较少的,就没放了.大家需要什么函数大家直接进去找就可以了

函数的分类

功能分类

.这么多函数我们分了下类.数值相关的函数,整型阿字符串函数,时间函数,流程控制函数,加密与解密函数,获取mysql信息函数,聚合函数等

这是从功能角度划分的

另一个角度分类

单行函数就是只对一行进行变换,只返回一个结果比如abs,它是可以多个同时使用的

多行函数就是类似聚合函数或者分组函数,

数值函数:

相对于比较好理解的,这里是说数值好理解,不代表函数熟悉

ABS(x)

ABS(x) 返回x的绝对值

SIGN(X)

SIGN(X) 返回X的符号。正数返回1，负数返回-1，0返回0

PI()返回圆周率的值

CEIL(x)，CEILING(x)

CEIL(x)，CEILING(x) 返回大于或等于某个值的最小整数 天花板函数

FLOOR(x)

FLOOR(x) 返回小于或等于某个值的最大整数底部函数

LEAST(e1,e2,e3…)

LEAST(e1,e2,e3…) 返回列表中的最小值

GREATEST(e1,e2,e3…)

GREATEST(e1,e2,e3…) 返回列表中的最大值

MOD(x,y)

MOD(x,y) 返回X除以Y后的余数

上面这一小波是比较简单的,我这边就直接写好了

SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32), FLOOR(-43.23),MOD(12,5) FROM DUAL;

天花板函数是一定去上cell和celling地板函数一定去下,floor

RAND()返回0~1的随机值

RAND(x) 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机 数

SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)

FROM DUAL

这个随机函数比较有意思:是0-1大家想要100是随机数,那你就乘100,参数代表的是一个人性化的设置,添加的数是因子,如果是一样的那么结果就是相同的,我们跑一下,我们会发现是一样的

所以本质上是一个伪随机.只要我们保证我们取的范围0-1返回的值在海量数据的概率上看是平均分布的.

ROUND(x)

ROUND(x) 返回一个对x的值进行四舍五入后，最接近于X的整数

ROUND(x,y)

ROUND(x,y) 返回一个对x的值进行四舍五入后最接近X的值并保留到小数点后面Y位

TRUNCATE(x,y)

TRUNCATE(x,y)返回数字x截断为y位小数的结果

这边是一个四舍五入的函数

SELECT ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1) FROM DUAL

所以ROUND(x,y) y代表的就是保留几位小数的意思如果写的负一保留的就是120 ,,截断是必须要有2个参数,和四舍五入的round不一样,这里我们truncate(x,y)

不会进行四舍五入,就算是9也都白扯,比如truncate(139.15,-1) round(139.15,-1)

另外我们之前不是说单行函数是可以嵌套的,

SELECT TRUNCATE(ROUND(123.456,2),0)

SQRT(x) 返回x的平方根。当X的值为负数时，返回NULL

这个就是开根号,我就不演示了

三角函数RADIANS(x),DEGREES(X)

在一些特殊的科学运算里我们可能会用到三角函数,我们这里的三角函数用的都是弧度值,但是我们平时说的sin30,cos30代表的都是角度,所以我们要先把它替换成弧度,这里就需要用到函数

RADIANS(x) 代表把角度转换为弧度

DEGREES(X) 代表把弧度转化为角度

还记得我们的30 弧度和角度是什么关系,我们有个圆,他的周长就2pi,半径是1的时候,,弧度和角度的关系就是,角度对应的周长,比如pi/4就是对应90度的弧度.所以代表的含义就是角度除以360*2pi.

函数 用法

SIN(x) 返回x的正弦值，其中，参数x为弧度值

ASIN(x) 返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL

COS(x) 返回x的余弦值，其中，参数x为弧度值

ACOS(x) 返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL

TAN(x) 返回x的正切值，其中，参数x为弧度值

ATAN(x)返回x的反正切值，即返回正切值为x的值

ATAN2(m,n) 返回两个参数的反正切值

COT(x) 返回x的余切值，其中，X为弧度值 举例

这里的30代表得到就是角度,我们要先用radians转化为弧度,然后结果就是我们的sin值

这里的degrees代表的把弧度转化为角度,这里asin代表的就是把值对应的弧度求出来,求出来的的是弧度我们就要用degrees转化为角度值

SELECT

SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1) ) FROM DUAL

指数和对数函数

POW(x,y) POWER(X,Y) 返回x的y次方，

这个函数我们倒是经常用到,比如我们要求2的2次方,就可以用

EXP(X) 返回e的X次方，其中e是一个常数，2.718281828459045

LN(X)，LOG(X)返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL

LOG10(X) 返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL

LOG2(X) 返回以2为底的X的对数，当X <= 0 时，返回NUL

SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4) FROM DUAL

进制的装换

BIN(x) 返回x的二进制编码

HEX(x) 返回x的十六进制编码

OCT(x) 返回x的八进制编码

CONV(x,f1,f2) 返回f1进制数变成f2进制数

SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8) FROM DUAL;

Conv可以把2进制的数变化为8进制的数这里的含义是

这里面我们比较常用的还是上面的基本函数

字符串函数:

字符串函数在编程语言里都是一类非常重要的变量,所以和字符串相关的函数就比较多,也相对比较重要一些,首先我们也是来一波一波讲解

虽然我们没有正式的讲解mysql里的数字类型,但是大家从我们的数据里也可以发现,我们的mysq里比较重要的几个类型,数值类型,分为整形和浮点型,还有我们的字符串类型,分为短字符串和长字符串类型,还有我们的日期类型,回头我们再展开说,

ASCII(S)

第一个是ASCII(S) 这个和asc码用的是一样的

这里面的ASCII函数只和第一个字母有关,它只会返回第一个字符的ASC码

CHAR_LENGTH(s) 这里我们写两个函数

这里就涉及到字符集的时候,hello是一个字符用一个字节去储存就好,字母就是用一个字节储存的,所以这块就是5,这里为什么你好的字节就是6,因为汉字代表的就是3个字节来储存,的如果用的CHAR_lenth代表的就是我们有结果字

Concat(s1,s2,s3)代表的就是会把s1,s2,s3都链接起来

SELECT CONCAT('你好','我是一个好人',T1.last_name,'是',T2.last_name,'的员工')

FROM employees T1 JOIN employees T2

ON T1.employee_id=T2.manager_id

CONCAT_WS(s1,s2,s3,s4)它代表的就是会把s1插入s2,s3,s4之间,就像s2s1s3s1s4s1

INSERT(str,idx,len,replacestr) 这里正常是插入的意思,但是这里不是,这里代表从第二个参数开始,一共第三个参数的长度,替换成后面的字符串

SELECT INSERT(‘helloword’,2,3,’aaaaa’) FROM DUAL

这里就可以看到我们的ell被替换成aaaaa了.

REPLACE(str,str,str)这个我们一看就知道是替换,它是把第一个字符串里的字符串替换为另一个字符串

在sql规范里我们是对字符串里的值是应该区分大小写的,比如我们要查询一个带大写的KING,但是有些是king,有些是KING,我们要都查询出来,我们就可以把它LOWER(‘KING’)=’king’,但是mysql的规范就不是特别严谨,加不加就没什么意义了

Left(str,len)对字符串左边提取len长度的字符

RIGHT(sty,LEN)对字符串右边提取LEN长度的字符

这里我们就可以提取了

LPAD(salary,10,’*’)可以实现右对齐效果

RPAD(salary,10,’*’)可以实现左对齐效果

TRIM(‘ he el lo ’)他可以去除字符串首尾的空格

LTRIM(‘ he el lo ’)他可以去除字符串左边的空格

RTRIM(‘ he el lo ’)他可以去除字符串右边的空格

这里还有一个函数 SELECT TRIM(‘0’FROM’011653.sd0’) FROM DUAL

他比较灵活,可以替换你想要替换的前后字符串.

REPEAT 代表的就是重复,可以把第一个字符串重复多少次

SPACE(len)返回len长度个空格

STRCMP(S1,S2),比较前面的字符串和后面的字符串那个大,如果前面的大,就返回1,后面大就返回-1

SUBSTR(s,index,len)从index开始取len个字符

LOCATE(Substr,STR)返回字符串substr在str中出现的位置,第一次出现的位置,如果没有的话就返回0,如果是java就会返回-1,因为java是从0开始索引,sql是从1开始所以会返回0

ELT(m,s1,s2,…,sn)返回指定m位置的字符串,如果m=1,返回s1,m=n,返回sn,m是数值

FIELD(m,s1,s2,…sn)返回指定的字符串,m是字符串,,如果m等于s1,就返回1,如果等于sn就返回n

FIND_IN_SET(s1,’s1,s2,s3,s4’)如果s1=s2,那么就返回1

NULLIF(a,b)如果a和b相等,那么就返回空NULL,如果不相等就返回a

SELECT employee_id,NULLIF(LENGTH(last_name),LENGTH(first_name)) FROM employees

日期和时间函数

我们之前说了最重要的三个类型,数值,字符串,时间类型,我们是说我们要储存日志或者订单的表,都要把时间储存到表里,

获取时间的

日期和时间戳装换

获取月份星期,星期数,天数等函数

日期的操作函数,extract,也是剥离年月日时分秒的

时间和秒钟的转换函数

计算日期和时间的函数,(在现有的基础加几天,减几天,计算时间差值等)

比如时间达到一千天之类的有实际的函数

日期的格式化与解析的问题,其他的语言也会存在一些这种操作.我们往大体去说都说了一小会,大家下来自己可以去测试一下.

我们先看

获取日期和时间的函数

SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0 FROM DUAL;

这里我们记住一个就行找个比较短的,世界时间和我们差8个销售,所以加8个小时就和我们的时间一致了,第一波还是比较柔和的

获取当前的年月日和时分秒就要用这些函数

日期和时间戳的转换

我们先SELECT UNIX_TIMESTAMP(now())可以把时间转换为时间戳

也不难,我们去保存时间也很多都会把时间保存为时间戳加一些其他的信息,作为订单号

获取月份日期,星期天数等函数

这里WEEKDAY返回的周一是0

下面dayofyear返回的是年的第几天,weekofyear就是一年的第几周

我们直接测试一下

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()), HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE()) FROM DUAL;

这里比较简单,我们就直接看,

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'), QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()), DAYOFMONTH(NOW()),DAYOFWEEK(NOW()) FROM DUAL

这里日期的字符串如果和日期的默认格式相同,就会把字符串转化为时间格式

这里因为是周二所以weekday返回的是1,

第4个季度,今年的41周.290天,这个月的16天这周的第4天

日期的操作函数

这里的type给了我们很多丰富的写法

首先我们来写一个 SELECT EXTRACT(DAY FROM NOW()),EXTRACT(year FROM NOW())

主要就看我们想判断当前的时间我们就直接now,如果要指定的时间,就直接写’yyyy-mm-dd hh:ss:mm’

时间和秒钟的转化函数

SELECT TIME_TO_SEC(NOW()),SEC_TO_TIME(TIME_TO_SEC(NOW()))

计算日期和时间的函数

我们看名字就可以知道这是对时间进行一个加减的操作

这里interval是添加的意思,我们加1就是加了一年,如果加-1就是减了一年

下面我们也举了个例子

第一个我们加了1天,第二个我们就爱了1分钟第等,这里面的1_1假如单位代表了年,1_1代表的就是添加1年一个月

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL

1 SECOND) AS col2,

ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,

DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,

DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数

DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号

FROM DUAL;

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10 01'), TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'), LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101, 10) FROM DUAL;

比如我们需要查看用户在7天之内是否注册就可以使用datediff查看差几天,还有timediff可以查看查了多少时间,to_DAY大家可能会觉得为什么要和0000年1月1日的时间差,我们可以拿它当中间值作为标准对比2个时间相差的天数,然后用函数计算.

下面交lastday,返回现在这个月的最后一天,makedate,如果给了2023年,和12,就会返回2023年1月12日也就是2023年的第12天,maketime,将给定的小时分钟秒钟的字符串返回一个日期格式的

这里我们如果

日期的格式化和解析

日期的格式化,把日期转化为字符串这就是格式化

解析:字符串转化为日期

这时我们谈的是日期的显式格式化和解析

之前我们接触的是隐式的格式化和解析

SELECT * FROM WHERE HIRE_DATE=’2019-01-13’

这里其实就会对’2019-01-13进行一个解析,把字符串转换为日期格式,然后去字段里寻找相同的日期.

’ SELECT * FROM WHERE HIRE_DATE=’asds-01-13’ 就会报错;1

如果我们的月日在前面,那就必须进行显示的转换,比如’05-03-2019

’这里我们先看上面的两个,也就是格式化

我们要按照这里的格式来写我们的日期格式

比如SELECT DATE_FORMAT(CURDATE(),'%Y-%M你%D')

这里就是比较标准的日期

下面是时间SELECT DATE_FORMAT(CURtime(),'%H:%i我的秒数%s')

这个是比较标准的时间

还有其他的日期

现在还有日期格式化的逆过程

SELECT

str_TO_DATE('2024-10-16 04:08:00 周3 英文周几Wednesday 今年的第42周','%Y-%m-%d %H:%i:%s 周%w 英文周几%W 今年的第%u周')

我们就可以把一个很复杂的花样字符串转换为日期

现在我们来说函数GET_FORMAT(DATE,USA)

他本质是获得一种格式,大家看最后的结果是不是类似我们的日期格式化参数里的fmr

所以我们是可以直接调用GET_FORMAT,来写DATE_FORMAT

SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))

GET_FORMAT的效果就是怕大家忘记格式,就可以直接用这个函数来获取日期格式化格式.

大家可以发现这一节是比较重的,说的内容也比较多,所以希望大家可以重视.需要大家掌握和注意的

1获取日期时间,时间戳,比如now(),curtime curdate,

2还有时间间隔的函数

3还有格式化日期的函数

流程控制类函数

任何一门语言都会有流程控制函数,if for,这种,mysql也不例外

SELECT last_name,salary,if(salary>=6000,'高工资','低工资') FROM employees

SELECT if(commission_pct is NULL,0,commission_pct) FROM employees

IFNULL(value,value)可以看成是iF结结构的特殊情况

其实就是前面判断条件被忽略了,就是是否为空的条件.很明显if是比较灵活的

我们来看CASE WHEN THEN

SELECT last_name,CASE

   WHEN salary>15000 THEN

          '白骨精'

WHEN salary>10000 THEN

'潜力股'

   ELSE

'草根'

END '别名' FROM employees;

就是在各种条件下对字段进行修改,也可以进行

类似python里的if elif elif else

如果我们忽略else的写法,其他情况就都会变成空

SELECT last_name, job_id, salary,

CASE job_id WHEN 'IT_PROG' THEN 1.10*salary

WHEN 'ST_CLERK' THEN 1.15*salary

WHEN 'SA_REP' THEN 1.20*salary

ELSE

salary END

FROM employees;

我们说别的语言的都有分支结构,循环结构之类的.我们这边说的都是分支结构,那么循环呢,

其实是因为我们sql已经自带循环了,因为我们运行是不是要一个表里的107行都得轮流运行.

我们之后在存储函数的时候我们再进行循环

加密和解密函数.

随着流程控制函数的完结，前面比较重要的函数都已经说完了，因为我们最重要的数值，字符串，时间格式的字段是最重要的。而流程控制函数在我们运行中也经常会使用到。所以重要的内容已经几乎完结了，接下来大家就做了解就可以了。

加密和解密就很清晰了，我们用户名和密码是这些不希望被他人获取的字段，我们就可以进行加密操作。这些信息就可以用到用户表里，用户的密码如何加密呢，在这个数据库里我们如何进行加密呢，如果黑客通过非法手段获取这个表没有加密不就完蛋了。2009年csdn就出现了这个丑闻，它居然是用明文保存的数据库用户密码，显然是十分不应该的。

那我们在发给后台的时候，我们的加密操作，完全可以前置到客户端发送的过程中，相当于我们这只是给了一种给数据端加密的过程，

这里我们用的是8.0,里面已经把password抛弃的了

Mysql5.7则还在使用

SELECT MD5(‘mysql’),SHA(‘mysql’) FROM DUAL

这里面说SHA比我们MD5更加的安全,这里的字符串是不可逆的,我们是不能通过这个字符串变回原来的,这里我们可能会纳闷,我们的密码是mysql,怎么证明我们能登录成功,那就是因为我们MD5得到了这个字符串,这个字符串和保存的内容是一致的那就保证了你可以登录,如果我是一个黑客,得到了一个加密的字符串,也没有用,我输入进来,数据库会对他再次进行MD5加密,还是不能登录我们的账户.

还有一个函数ENCODE和DECODE,这个是可逆的加密解密,它也是在我们的mysql8.0中不可用了,在我们的mysql5.7中还是可以运行的.

这里就是根据暗文你好来对mysql进行解密.

mysql的信息函数,

相对而言比较简单就是信息.

.

SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER(),CHARSET('ABC'),COLLATION('ABC');

比如你要开发一个客户端,我们都要用这个函数来获取,我们的版本,之类相关的信息

最后我们的其他函数:

SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2);

这里如果n等于或者小于0就自保留整数,和ROUND的四舍五入不太一样.

下面是我们得到一个ip地址的转换,因为我们ip地址会尽量保存为整数格式,类似与加密过程, 以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上 100得到这个数.

我们可以通过下面的函数INET_NTOA来还原回去

SELECT BENCHMARK(count,expr)可以对一个功能执行count次来查看我们的表达式的执行时间,也就是查看效率

SELECT CHARSET('收到撒娇的'),CHARSET(CONVERT('atguigu'USING'GBK'))

这个函数就可以实现字符集的装换,这个因为我们客户端有时候字符集不一样就要进行转化,如果我们约定好的不是utf8,那就要进行装换了.这一章还是很重的,大家必要的时候还是要做一下练习.