能运行在多个系统上的代码具有可移植性，在数据库软件中，多数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 | [email protected] | 0 | +--------------------------+----------+----------------+-------------------+