能运行在多个体系上的代码具有可移植性，在数据库软件中，多数sql语句时可移植的，可移植性比拟强；而函数的移植性不强，重要是由于各种数据库软件都支撑自己所独有的函数。因此许多sql用户不认同应用数据库软件所独有的的函数
字符串函数
concat(str1,...,strn) : 衔接n个字符串为一个完全的字符串
insert(str,x,y,instr) : 将字符串str从第x地位开端，y个字符长的子串调换为instr
lower(str) : 所有字符变成小写
upper(str) ：所有字符变成大写
left(str) : 返回字符串最左侧的x个字符
right(str) : 返回字符串str最右侧的x个字符
lpad(str,n,pad) : 应用pad字符串对字符串最左侧进行填充，直到长度为n个字符
rpad(str,n,pad) : 应用pad字符串对字符串最右侧进行填充，直到长度为n个字符
ltrim(str) : 去掉字符串str最左侧的空格
rtrim(str) : 去掉字符串str最右侧的空格
repeat(str,x) : 返回字符串str反复x次的成果
replace(str,a,b) : 应用字符串b调换字符串str中所有涌现的字符串
strcmp(str1,str2) : 比拟字符串str1和str2
trim(str) : 去掉字符串str行头和行尾的空格
substring(str,x,y) : 返回字符串str中从x地位起y个字符串长度的字符串
1、concat(str1,...,strn)
mysql> select concat("M","y","sql");
+-----------------------+
| concat("M","y","sql") |
+-----------------------+
| Mysql |
+-----------------------+
mysql> select concat("M","y","sql",null);
+----------------------------+
| concat("M","y","sql",null) |
+----------------------------+
| NULL |
+----------------------------+
注意：如果传入的参数有null，则返回的成果将是null
2、insert(str,x,y,instr)
mysql> select insert("mysql learning",3,5,"miss you");
+-----------------------------------------+
| insert("mysql learning",3,5,"miss you") |
+-----------------------------------------+
| mymiss youearning |
+-----------------------------------------+
3、strcmp(str1,str2)
mysql> select strcmp("my","sql");
+--------------------+
| strcmp("my","sql") |
+--------------------+
| -1 |
+--------------------+
1 row in set (0.01 sec)
mysql> select strcmp("mysd","mysql");
+------------------------+
| strcmp("mysd","mysql") |
+------------------------+
| -1 |
+------------------------+
1 row in set (0.00 sec)
mysql> select strcmp("mysd","mysal");
+------------------------+
| strcmp("mysd","mysal") |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
mysql> select strcmp("mysd","mysd");
+-----------------------+
| strcmp("mysd","mysd") |
+-----------------------+
| 0 |
+-----------------------+
4、获得字符串长度length()和字符数函数char_length()
mysql> select "mysql" as "英文字符串字节长度",
-> length("mysql") as "字符串长度",
-> "常建功" as "中文字符串",
-> length("常建功") as "字符串字节长度";
+--------------------+------------+------------+----------------+
| 英文字符串字节长度 | 字符串长度 | 中文字符串 | 字符串字节长度 |
+--------------------+------------+------------+----------------+
| mysql | 5 | 常建功 | 6 |
+--------------------+------------+------------+----------------+
select "mysql" as "英文字符串",char_length("mysql") as "字符串字符数", "常建功" as "中文字符串",char_length("常建功") as "字符串字符数";
+------------+--------------+------------+--------------+
| 英文字符串 | 字符串字符数 | 中文字符串 | 字符串字符数 |
+------------+--------------+------------+--------------+
| mysql | 5 | 常建功 | 6 |
+------------+--------------+------------+--------------+
理论上是3，但是实际上我的显示时6.
5、大小字母转换upper()和lower()
select upper("aBcD") as "aBcD",lower("HJjdIUE") as "HJjdIUE";
+------+---------+
| aBcD | HJjdIUE |
+------+---------+
| ABCD | hjjdiue |
+------+---------+
6、查找字符串
find_in_set(),field(),locate(),position(),instr(),ELT()
find_in_set(str1，str2)获得相匹配字符串的地位，参数str2中将包括若干个用逗号隔开的字符串
mysql> select find_in_set("mysql","I,love,mysql,and,you?") as "地位";
+------+
| 地位 |
+------+
| 3 |
+------+
filed(str,str1,str2,...):返回第一个与字符串str相匹配的字符串的地位
mysql> select field("mysql","oracle","sql server","mysql") as "地位";
+------+
| 地位 |
+------+
| 3 |
+------+
locate(str1,str):返回参数str中字符串str1的开端地位
position(str1 in str)
instr(str,str1)
mysql> select locate("sql","mysql") as"locate",position("sql" in "mysql") as "position",instr("mysql","sql") as "instr";
+--------+----------+-------+
| locate | position | instr |
+--------+----------+-------+
| 3 | 3 | 3 |
+--------+----------+-------+
elt(n,str1,str2,...)
返回第n个字符串
mysql> select elt(1,"mysql","oracle","sql server") as ELT;
+-------+
| ELT |
+-------+
| mysql |
+-------+
make_set(num,str1,str2,...,strn)
首先将数值num转换成二进制数，然后依照二进制从参数str1，str2，...，strn当选取相应的字符串。在通过二进制来选择字符串时，会从右到左读取该值，如果值为1选择该字符串，否则将不选择字符串。
mysql> select bin(13) as BIN, make_set(13,"a","b","c","d","e","f","g") as make_set;
+------+----------+
| BIN | make_set |
+------+----------+
| 1101 | a,c,d |
+------+----------+
1 row in set (0.00 sec)
mysql> select bin(23) as BIN, make_set(23,"a","b","c","d","e","f","g") as make_set;
+-------+----------+
| BIN | make_set |
+-------+----------+
| 10111 | a,b,c,e |
+-------+----------+
1 row in set (0.00 sec)
mysql> select bin(23) as BIN, make_set(23,"a","b") as make_set;
+-------+----------+
| BIN | make_set |
+-------+----------+
| 10111 | a,b |
+-------+----------+
7、从现有字符串中截取子字符串
left(str,num)
mysql> select left("algfdg",3) as "left";
+------+
| left |
+------+
| alg |
+------+
right(str,num)
mysql> select right("algfdg",3) as "right";
+-------+
| right |
+-------+
| fdg |
+-------+
substring(str,num,len)和mid(str,num,len)：截取指定地位和长度的子字符串
mysql> select substring("algfdg",3,4) as "substring",mid("algfdg",3,4) as "mid";
+-----------+------+
| substring | mid |
+-----------+------+
| gfdg | gfdg |
+-----------+------+
8、去除字符串的首尾空格
ltrim(str)
mysql> select ltrim(" mysql") as " mysql";
+-------+
| mysql |
+-------+
| mysql |
+-------+
1 row in set, 1 warning (0.00 sec)
mysql> select " mysql" as " mysql";
+----------+
| mysql |
+----------+
| mysql |
+----------+
1 row in set, 1 warning (0.00 sec)
right(str)
mysql> select rtrim("mysql ") as "mysql";
+-------+
| mysql |
+-------+
| mysql |
+-------+
1 row in set (0.00 sec)
mysql> select "mysql " as "mysql";
+-------------+
| mysql |
+-------------+
| mysql |
+-------------+
trim(str)
mysql> select trim(" mysql ") as " mysql ";
+---------+
| mysql |
+---------+
| mysql |
+---------+
9、调换字符串
insert(str,pos,len,newstr):
mysql> select "这是mysql数据库" as "oldstring",insert("这是mysql数据库",3,5,"oracle") as "newstring";
+-----------------+------------------+
| oldstring | newstring |
+-----------------+------------------+
| 这是mysql数据库 | 这oracleql数据库 |
+-----------------+------------------+
replace(str,substr,newstr)
mysql> select "这是mysql数据库" as "oldstring",replace("这是mysql数据库","mysql","oracle") as newstring;
+-----------------+------------------+
| oldstring | newstring |
+-----------------+------------------+
| 这是mysql数据库 | 这是oracle数据库 |
+-----------------+------------------+
数值函数
abs(x) 返回数值x的绝对值
ceil(x) 向上取整
float(x) 向下取整
mod(x,y) 返回x模y的值
rand() 返回0~1内的随机数
rand(n) 指定种子
round(x,y) 返回x的四舍五入后有y位小数的数值
truncate(x,y) 返回数值x截断y位小数的数值
mysql> select truncate(903.2432,2),truncate(902.324534,-2);
+----------------------+-------------------------+
| truncate(903.2432,2) | truncate(902.324534,-2) |
+----------------------+-------------------------+
| 903.24 | 900 |
+----------------------+-------------------------+
日期和时光函数
curdate()
curtime()
now()
unix_timestamp(date)
from_uinxtime()
week(date)
year(date)
hour(time)
minute(time)
monthname(date)
1、获得当前日期和时光
now(),current_timestamp(),localtime(),sysdate();
推举应用now()
mysql> select now() as "now",current_timestamp() as "current_timestamp",localtime() as "localtime",sysdate() as "sysdate";
+---------------------+---------------------+---------------------+---------------------+
| now | current_timestamp | localtime | sysdate |
+---------------------+---------------------+---------------------+---------------------+
| 2015-04-29 21:28:38 | 2015-04-29 21:28:38 | 2015-04-29 21:28:38 | 2015-04-29 21:28:38 |
+---------------------+---------------------+---------------------+---------------------+
2、获得当前日期
curdate(),current_date()
推举应用curdate()
mysql> select current_date() as "current_date",curdate() as "curdate";
+--------------+------------+
| current_date | curdate |
+--------------+------------+
| 2015-04-29 | 2015-04-29 |
+--------------+------------+
3、获得当前时光
curtime()和current_time()
推举应用curtime()
mysql> select current_time() as "current_time",curtime() as "curtime";
+--------------+----------+
| current_time | curtime |
+--------------+----------+
| 07:00:27 | 07:00:27 |
+--------------+----------+
4、通过各种方法显示日期和时光
(1)unix方法显示
unix_timestamp():返回时光戳格局的时光
from_unixtime():将时光戳格局时光转换成普通格局的时光
mysql> select now() as "当前时光",unix_timestamp(now()) "unix格局",from_unixtime(unix_timestamp(now())) as "普通格局";
+---------------------+------------+---------------------+
| 当前时光 | unix格局 | 普通格局 |
+---------------------+------------+---------------------+
| 2015-04-30 07:04:21 | 1430348661 | 2015-04-30 07:04:21 |
+---------------------+------------+---------------------+
注意：unix_timestamp()函数没有参数传入，则会显示出当前时光和日期的时光戳情势，如果传入了某个时光参数，则会显示所传入时光的时光戳。
(2)通过UTC方法显示日期和时光
UTC，即国际调和时光
utc_date()：实现日期
utc_time()：实现时光
mysql> select now() as "now",utc_date() as "utc date",utc_time() as "utc time";
+---------------------+------------+----------+
| now | utc date | utc time |
+---------------------+------------+----------+
| 2015-04-30 07:14:49 | 2015-04-29 | 23:14:49 |
+---------------------+------------+----------+
注意：返回的时光与现在的时光有8小时之差
5、获得日期和时光各部份值
year() :日期的年份
quarter() :日期所属第几个季度
month() :月
week() :日期属于第几个星期
dayofmonth() :属于当前月的第几天
hour() :时光的小时
minute() :分钟
second() :秒
mysql> select
-> now() as "now",
-> quarter(now()) as "quarter",
-> month(now()) as "month",
-> week(now()) as "week",
-> dayofmonth(now()) as "day",
-> hour(now()) as "hour",
-> minute(now()) as "minute",
-> second(now()) as "second";
+---------------------+---------+-------+------+------+------+--------+--------+
| now | quarter | month | week | day | hour | minute | second |
+---------------------+---------+-------+------+------+------+--------+--------+
| 2015-04-30 07:29:19 | 2 | 4 | 17 | 30 | 7 | 29 | 19 |
+---------------------+---------+-------+------+------+------+--------+--------+
1、月
month() :1~12
monthname() :月份的英文名字
mysql> select now() as "now",
-> month(now()) as "month",
-> monthname(now()) as "month";
+---------------------+-------+-------+
| now | month | month |
+---------------------+-------+-------+
| 2015-04-30 07:33:08 | 4 | April |
+---------------------+-------+-------+
2、星期
week()和weekofyear() :返回日期和时光中星期是当前年的第几个星期，规模为1~53
实际运用中，常常不须要实现上述功效
mysql> select now() as "now",week(now()) as "week",weekofyear(now()) as "weekofyear";
+---------------------+------+------------+
| now | week | weekofyear |
+---------------------+------+------------+
| 2015-04-30 07:41:59 | 17 | 18 |
+---------------------+------+------------+
dayname() :星期的英文名
dayofweek() :星期几，1~7，星期日为1
weekday() :星期几，0~6,0表现星期一，顺次类推
mysql> select now() as "now",dayname(now()) as "dayname",dayofweek(now()) as "dayofweek",weekday(now()) as "weekday";
+---------------------+----------+-----------+---------+
| now | dayname | dayofweek | weekday |
+---------------------+----------+-----------+---------+
| 2015-04-30 07:45:08 | Thursday | 5 | 3 |
+---------------------+----------+-----------+---------+
mysql> select now() as "now",dayname(now()) as "dayname",dayofweek(now())-1 as "dayofweek",weekday(now())+1 as "weekday";
+---------------------+----------+-----------+---------+
| now | dayname | dayofweek | weekday |
+---------------------+----------+-----------+---------+
| 2015-04-30 08:07:24 | Thursday | 4 | 4 |
+---------------------+----------+-----------+---------+
3、天
dayofmonth() :月中的第几天
dayofyear() :年中的第几天
mysql> select now() as "now",dayofmonth(now()) as "dayofmonth",dayofyear(now()) as "dayofyear";
+---------------------+------------+-----------+
| now | dayofmonth | dayofyear |
+---------------------+------------+-----------+
| 2015-04-30 07:52:37 | 30 | 120 |
+---------------------+------------+-----------+
1 row in set (0.00 sec)
4、获得指定时光域的值
extract() :extract,提取，获得
extract(type from date)
type:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND :天和毫秒
DAY_SECOND :天和秒
DAY_MINUTE :天和分钟
DAY_HOUR :天和小时
YEAR_MONTH :年和月
mysql> select now() as "now",extract(year from now()) as "year",extract(month from now()) as "month",extract(day from now()) as "day",extract(hour from now()) as "hour",extract(minute from now()) as "minute",extract(second from now()) as "second",extract(week from now()) as "week",extract(hour_minute from now()) as "hour_minute",extract(quarter from now()) as "quarter",extract(day_second from now()) as "day_second" ;
+---------------------+------+-------+------+------+--------+--------+------+-------------+---------+------------+
| now | year | month | day | hour | minute | second | week | hour_minute | quarter | day_second |
+---------------------+------+-------+------+------+--------+--------+------+-------------+---------+------------+
| 2015-04-30 08:14:22 | 2015 | 4 | 30 | 8 | 14 | 22 | 17 | 814 | 2 | 30081422 |
+---------------------+------+-------+------+------+--------+--------+------+-------------+---------+------------+
6、盘算日期和时光的函数
1、盘算与默许日期和时光（0000年1月1日）相互操作的函数，
to_days(date)
盘算日期参数date与默许日期和时光(0000年1月1日)之间相隔天数
from_days(number)
该函数盘算从默许日期和时光(0000年1月1日)开端阅历number天后的日期和时光
datediff(date1,date2)
盘算date1和date2之间相隔天数
mysql> select now() as "now",to_days(now()) as "toDays",from_days(to_days(now())+7) as "from_days",datediff(now(),"2000-12-01") "datediff";
+---------------------+--------+------------+----------+
| now | toDays | from_days | datediff |
+---------------------+--------+------------+----------+
| 2015-04-30 08:27:11 | 736083 | 2015-05-07 | 5263 |
+---------------------+--------+------------+----------+
2、盘算与指定日期和时光相互操作的函数
adddate(date,n)
subdate(date,n)
mysql> select curdate() as "curdate",adddate(curdate(),5) as "five day after",subdate(curdate(),5) as "five day before";
+------------+----------------+-----------------+
| curdate | five day after | five day before |
+------------+----------------+-----------------+
| 2015-04-30 | 2015-05-05 | 2015-04-25 |
+------------+----------------+-----------------+
adddate(d,interval expr type)
subdate(d,interval expr type)
interval:间隔，区间
type的值
SECOND 秒 ss
MINUTE 分钟 mm
HOUR 小时 hh
DAY 日 DD
MONTH 月 MM
YEAR 年 YY
MINUTE_SECOND 分钟和秒 mm和ss之间用任意符号隔开
HOUR_SECOND 小时和秒 hh和ss之间用任意符号隔开
HOUR_MINUTE
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
mysql> select curdate() as "curdate",adddate(curdate(),interval "2,3" year_month) as "two year three month after";
+------------+----------------------------+
| curdate | two year three month after |
+------------+----------------------------+
| 2015-04-30 | 2017-07-30 |
+------------+----------------------------+
1 row in set (0.00 sec)
mysql> select curdate() as "curdate",adddate(curdate(),interval "2,3" day_minute) as "two year three month after";
+------------+----------------------------+
| curdate | two year three month after |
+------------+----------------------------+
| 2015-04-30 | 2015-04-30 02:03:00 |
+------------+----------------------------+
注意：第二个查询语句没有涌现想要的成果
addtime()
subtime()
mysql> select curtime() as "curtime",addtime(curtime(),5) as "five second after",subtime(curtime(),5) as "five second before";
+----------+-------------------+--------------------+
| curtime | five second after | five second before |
+----------+-------------------+--------------------+
| 08:42:08 | 08:42:13 | 08:42:03 |
+----------+-------------------+--------------------+
1 row in set (0.01 sec)
mysql> select curtime() as "curtime",addtime(curtime(),5*60) as "five minute after",subtime(curtime(),5*60) as "five minute before";
+----------+-------------------+--------------------+
| curtime | five minute after | five minute before |
+----------+-------------------+--------------------+
| 08:42:54 | 08:45:54 | 08:39:54 |
+----------+-------------------+--------------------+
体系信息函数
version()
database()
user()
last_insert_id()
mysql> select version() as "version",database() as "database",user() as "user",last_insert_id() as "auto_increment_id"
+--------------------------+----------+----------------+-------------------+
| version | database | user | auto_increment_id |
+--------------------------+----------+----------------+-------------------+
| 5.0.51b-community-nt-log | test3 | root@localhost | 0 |
+--------------------------+----------+----------------+-------------------+
能运行在多个体系上的代码具有可移植性，在数据库软件中，多数sql语句时可移植的，可移植性比拟强
阅读背景：
mysql学习笔记之十一（常用函数）
来源：互联网
分享到：
