MySQL学习之函数

一、数学函数

1、绝对值函数abs(x)和返回圆周率的函数PI()

2、平方根函数sqrt(x)和求余函数MOD（x,y）

3、获取整数的函数CEIL(X)、CEILING(X)——不大于和FLOOR(X)——不小于

4、获取随机数的函数RAND()和RAND(X)（其中x为指定的一个整数，用作种子值，用来产生重复的序列）

5、函数 ROUND(X),ROUND(X,Y)和TRUNCATE(X,Y)

Round(x)返回最接近于参数x的整数，对x进行四舍五入。

Round(x,y)参数y表示取值四舍五入保留到小数点后面的y位，

若y位负值，保留到x值到小数点左边y位，相应的位数直接变为0，不进行四舍五入。

TRUNCATE(X,Y)函数操作数进行截取操作，结果保留小数点后面指定y位。

6、符号函数sign(x):返回值0、-1/1

7、幂函数pow(x,y)、power(x,y)、EXP(X)

POW(X,Y)/POWER(X,Y)函数返回x的y次方的结果值

EXP(x)是指e的x次方

8、对数函数log(x)和log10(x)

Log（x）返回的是x的自然对数，以e为底数，Log10(x)是以10为底数

X为负数时非法返回null

9、角度和弧度互相转换的函数radians(x)——角度转为弧度和degrees(x)——弧度转为角度

10、正弦函数sin(x)和反正弦函数asin(x)、余弦函数和反余弦函数……

Cos(x),acos(x),tan(x),atan(x),cot(x)

 

二、字符串函数

1、计算字符串字符数函数char_length(字符串）和字符串长度的函数length(字符串)

Select char_length(‘data’),length(data);

 

2、合并字符串函数concat(s1,s2,……)/concat_ws(x,s1,s2,……)

Concat函数中如果有一个为Null返回null，如果有二进制字符串则返回结果为二进制字符串。

concat_ws函数中x代表放在两个字符串之间的分隔符

 

3、替换字符串insert(s1,x,len,s2）

返回字符串s1，始于x，取代s2中的len个字符。

如果x超出范围，返回原s1;

如果len超出其他字符串的范围，从x开始截取后面所有的字符

如果有一个参数为null,则转换为null

 

4、字母大小写转换函数LOWER(str)/LCASE(str)、upper(str)/Ucase(str)

 

5、获取指定长度的字符串函数left(s,n)和right(s,n)

 

6、填充字符串串函数lpad(s1,len,s2)和rpad(s1,len,s2)

如果len的长度小于s1则返回值缩短至len字符

 

7、删除空格的函数LTRIM(S)/rtrim(s)/trim(s)

 

8、删除指定字符串函数trim(s1 from s)——其中s1为可选项，如果没有s1默认删除空格

用于删除字符串S中两端所有的字符串s1

9、重复生成字符串函数repeat(s,n)

10、

11、空格函数space(n)和替换函数replace(s,s1,s2)

空格函数即生成n个空格

替换函数s2替换s中的s1

12、比较字符串大小的函数strcmp（s1,s2）

13、获取子串的长度substring(s,n,len)和mid(s,n,len)

如果n为负数，则从后往前数；如果len参数没有则默认取到最后；如果len是一个小于1的值，则结果始终为空字符串。

14、返回指定字符串的函数ELT(n,str1,str2,str3……,strn)

若n<0,则返回Null，n=1则返回n1

15、返回指定字符串位置的函数field(s,s1,s2,……)

返回字符串s的位置，若有返回位置若无返回null,若s为null则返回0

16、返回子串位置的函数find_in_set(s1,s2)

S2是一个字符串列表，列表用多个逗号隔开。

若s1不在s2或者s2为空字符串，则返回0

S1/s2任意一个为null返回值则为null

17、取字符串的函数make_set(x,s1,s2,……)——用二进制取得字符串

返回有x的二进制数指定的相应位的字符串组成的字符串，s1对应比特1，s2对应比特01.

如果s1,s2,……中有null值不会添加在结果中，且x=0则返回空字符串。

三、日期和时间函数

日期和时间函数主要用来处理日期和时间值，一般日期函数是用DATE,也可以使用DATETIME/TIMESTAMP,但是会忽略他们的时间值。时间函数TIME类型，其他类型会忽略掉日期值。

1、获取当前日期/时间函数

Curdate()/current_date()/current_date()+0是将时间值转换为数值

curtime()/current_time()

2、获取当前日期和时间函数

Current_timestamp()/localtime()/now()/sysdate()

3、Unix时间戳函数

Unix_timestamp(date)，如果无参数调用，则返回一个时间戳（‘1970-01-01 00:00:00’即GMT（世界时）之后的秒数）

From_unixtime(date),与unix_timestamp(date)是反函数.

4、返回utc日期/时间的函数——返回当前utc时间/日期

Utc——世界标准时间。Utc_date()/utc_time()

5、获取月份的函数month(date)/monthname(date)

6、获取星期的函数dayname(d)/dayofweek(d)/weekday(d)

Dayofweek(d)——其中1表示周日

Weekday(d)——其中0表示周一

7、获取星期数week(d),weekofyear(d)——用于计算d是一年中的第几周

Week()双参数可以指定起始为周一/周日  0——周日，1——周一

8、获取天数dayofyear(d),dayofmonth(d)

9、获取年份、季度、小时、分钟、秒

Year(date)、quarter(date)、minute(time)、second(time)、

10、获取日期指定值extract(type from date)

Type为year时，只返回年份;type为year_month返回年份与月；type为day_minute时，返回日、小时、分钟值

11、时间和秒钟转换time_to_sec(time)/sec_to_time(seconds)

12、计算日期和时间的函数

Date_add()、addDate()、date_sub()、subDate()、addTime()、subtime()、datediff(d1,d2)

Date_add(date,interval expr type)/date_sub(date,interval expr type):其中expr是一个表达式用于指定起始日期添加或减去的时间间隔值；type为关键字，指示了表达式被解释的方式。

datediff(d1,d2):计算两个日期间隔了多少天。

13、将日期和时间格式化

Date_format(date,format)根据format指定格式显示date

四、条件判断函数

条件判断函数根据条件不同来执行相应的流程。

1、if(expr,v1,v2)函数

2、Ifnull(v1,v2)函数——如果v1不为null，返回v1值，否则返回v2值

3、Case函数

Case expr when v1 then r1[when v2 then r2][else rn] end;

 

五、系统信息函数

MySQL系统信息有：数据库版本号、当前用户名和连接数、系统字符集、最后一个自动生成的ID值等

1、获取MySQL版本号、连接数和数据库名称

Version()——查看当前MySQL版本号

connection_id()——查看当前连接次数，每个连接都有各自唯一的ID

show processlist——输出当前用户的连接信息

Database()/schema()返回utf8字符集的默认当前数据库名

2、获取当前用户数
user()/current_user()/system_user()/session_user()

3、获取字符串的字符集和排序方式的函数

Charset(str)返回str的字符集

Collation(str)返回str的字符排列方式

4、获取最后一个自动生成的ID值的函数

Last_insert_id()自动返回最后insert/update/auto_increment列设置的第一个发生的值。

如果向表中插入单条记录时，返回值为那条记录的ID

如果同时向表总插入多条记录时，返回值为多条记录的第一条ID值

Last_insert_id是与table无关的，若先向a表插入数据后在向b表插入数据，则返回值是b表的ID

 

六、加密和解密函数

加密函数主要用来对数据的加密和界面处理，以保证某些重要的数据不被别人获取。

1、加密函数password(str)——从原明文密码str计算并返回加密后的密码字符串，当参数为null时也返回null。

Password函数是在MySQL服务器鉴定系统中使用的，不将它运用在个人的应用程序中，其加密是单向的，password()执行密码方式与unix中密码加密方式不同。

2、加密函数MD5（str）——为字符串计算出一个MD5 128比特检验和

3、加密函数 ENCODE(str,pswd_str)——用pswd_str作为密码对str进行加密

4、解密函数decode(crypt_str,pswd_str),crypt_str是由encode()返回的字符

Select decode(encode(‘secret’,’cry’),’cry’);

 

七、其他函数

1、格式化函数format(x,n)——将数字x格式化，并四舍五入保留到小数点n位，结果以字符串返回.若n=0,则返回结果不含小数。

2、不同进制的数字进行转换函数CONV(N,from_base,to_base)——N被理解为一个整数，如果任意一个参数为null，则结果返回null。

3、IP地址与数字互换

Inet_aton(expr)——给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。

Inet_ntoa(epxr)——给定一个数字网络地址，返回作为字符串的网络地址的点地址

4、加锁函数和解锁函数

Get_lock(str,timeout)——设法使用字符串str给定的名字得到一个锁，持续时间为timeout.成功返回1超时返回0

Release_lock(str)解开被get_lock()的锁。若锁开返回1，没有解开返回（或者锁未创建）返回0

Is_free_lock(str):锁是否可以使用

Is_used_lcok(str)：锁是否在用

5、重复执行指定操作函数

Benchmark(count,expr)函数重复执行expr表达式。——通常用于计算MySQL处理表达式的速度，一般结果为0（处理速度非常快）

6、改变字符集的函数

Convert(...using...)带有using的convert函数用来在不同的字符集之间转换数据。

Select charset(‘string’ using latin1);

7、改变数据类型的函数

Cast(x,as type)/convert(x,type)

可转换的type有：binary/char(n)/date/time/datetime/decimal/signed/unsigned

 

解惑：

如何修改默认的字符集？

在windows中，MySQL的配置文件为my.ini,存放在MySQL的安装目录下，修改配置文件中的default-character-set和character-set-server参数值，将其改为想要的字符集名称即可。