能运行在多个系统上的代码具有可移植性，在数据库软件中，多数sql语句时可移植的，可移植性比较强；而函数的移植性不强，主要是由于各种数据库软件都支持自己所独有的函数。因此许多sql用户不认同使用数据库软件所独有的的函数
字符串函数
concat(str1,...,strn) : 连接n个字符串为1个完全的字符串
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 row in set (0.01 sec)
mysql> select strcmp('mysd','mysql');
+------------------------+
| strcmp('mysd','mysql') |
+------------------------+
| ⑴ |
+------------------------+
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,...):返回第1个与字符串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转换成2进制数，然后依照2进制从参数str1，str2，...，strn当选取相应的字符串。在通过2进制来选择字符串时，会从右到左读取该值，如果值为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的4舍5入后有y位小数的数值
truncate(x,y) 返回数值x截断y位小数的数值
mysql> select truncate(903.2432,2),truncate(902.324534,⑵);
+----------------------+-------------------------+
| truncate(903.2432,2) | truncate(902.324534,⑵) |
+----------------------+-------------------------+
| 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⑵9 21:28:38 | 2015-04⑵9 21:28:38 | 2015-04⑵9 21:28:38 | 2015-04⑵9 21:28:38 |
+---------------------+---------------------+---------------------+---------------------+
2、获得当前日期
curdate(),current_date()
推荐使用curdate()
mysql> select current_date() as 'current_date',curdate() as 'curdate';
+--------------+------------+
| current_date | curdate |
+--------------+------------+
| 2015-04⑵9 | 2015-04⑵9 |
+--------------+------------+
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⑶0 07:04:21 | 1430348661 | 2015-04⑶0 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⑶0 07:14:49 | 2015-04⑵9 | 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⑶0 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⑶0 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⑶0 07:41:59 | 17 | 18 |
+---------------------+------+------------+
dayname() :星期的英文名
dayofweek() :星期几，1~7，星期日为1
weekday() :星期几，0~6,0表示星期1，顺次类推
mysql> select now() as 'now',dayname(now()) as 'dayname',dayofweek(now()) as 'dayofweek',weekday(now()) as 'weekday';
+---------------------+----------+-----------+---------+
| now | dayname | dayofweek | weekday |
+---------------------+----------+-----------+---------+
| 2015-04⑶0 07:45:08 | Thursday | 5 | 3 |
+---------------------+----------+-----------+---------+
mysql> select now() as 'now',dayname(now()) as 'dayname',dayofweek(now())⑴ as 'dayofweek',weekday(now())+1 as 'weekday';
+---------------------+----------+-----------+---------+
| now | dayname | dayofweek | weekday |
+---------------------+----------+-----------+---------+
| 2015-04⑶0 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⑶0 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⑶0 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⑴2-01') 'datediff';
+---------------------+--------+------------+----------+
| now | toDays | from_days | datediff |
+---------------------+--------+------------+----------+
| 2015-04⑶0 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⑶0 | 2015-05-05 | 2015-04⑵5 |
+------------+----------------+-----------------+
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⑶0 | 2017-07⑶0 |
+------------+----------------------------+
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⑶0 | 2015-04⑶0 02:03:00 |
+------------+----------------------------+
注意：第2个查询语句没有出现想要的结果
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 |
+--------------------------+----------+----------------+-------------------+