网站开发技术

admin

管理员

mysql中的函数总结

数学函数：

abs(x) -- 绝对值

pi() -- pi

sqrt(x） -- 平方根

mod(x, y) 求余数

ceil (x） 上地板函数 

floor (x) 下底板函数

rand([x]) 0-1的随机数

round(x, [y]) 四舍五入

sign(x) 符号函数

pow(x,y) x的y次方

exp(x) e 的 x 次方

log(x) 用e做底数

log10(x)用10做底数

radians(x) 角度变弧度

degrees(x) 弧度变角度

sin(x）， asin(x)

cos(x）， acos(x）

tan(x), atan（x)

################

字符串函数

char_length('abc’) 长度

concat(str1, str2 , ....) 字符串拼接， 如果结果中有一个是null 返回null

mysql> select concat(1,2,'a');
+-----------------+
| concat(1,2,'a') |
+-----------------+
| 12a             |
+-----------------+
1 row in set (0.00 sec)

concat_ws（’分隔符', str1, str2 .....) 字符串拼接

insert("被操作字符", 开始地方， 多少个， 替换成什么）， 字符开的是从1开始的

mysql> select insert ('123456', 1, 4, 'aaa');
+--------------------------------+
| insert ('123456', 1, 4, 'aaa') |
+--------------------------------+
| aaa56                          |
+--------------------------------+
1 row in set (0.00 sec)

lower, upper 大小写

left(s, n) , right(s, n) 字符串截取

lpad(s1, len, s2), rpad(s1, len, s2) 字符串填充

ltrim, rtrim, trim 删除两边的空白

删除指定的字符串 trim (s1 from s);

mysql> select trim('xx' from 'xxddd');
+-------------------------+
| trim('xx' from 'xxddd') |
+-------------------------+
| ddd                     |
+-------------------------+
1 row in set (0.00 sec)

repeat (str, n) 重复字符串

spache(n) 空格字符串

替换函数

mysql> select replace('1234', '34', '12');
+-----------------------------+
| replace('1234', '34', '12') |
+-----------------------------+
| 1212                        |
+-----------------------------+
1 row in set (0.00 sec)

字符串比较 ： strcmp(str1, str2)

获取字串  substring(str, n , lenx); 如果lenx过的， 返回最多可以个数  ， substring <==> mid

mysql> select substring('1234', 2, 5);
+-------------------------+ 
| substring('1234', 2, 5) |
+-------------------------+
| 234                     |
+-------------------------+
1 row in set (0.00 sec)

字符串匹配  locate('x', 'abxy') , position(str1 in str), insert('x', 'abxy')

mysql> select locate('abc', '012abc');
+-------------------------+
| locate('abc', '012abc') |
+-------------------------+
|                       4 |
+-------------------------+
1 row in set (0.00 sec)

字符串翻转  reverse(str)

返回指定的字符串  elt(n, str1, str2, str3...)

mysql> select elt(2, 'abc', 'abcd', 'abcde');
+--------------------------------+
| elt(2, 'abc', 'abcd', 'abcde') |
+--------------------------------+
| abcd                           |
+--------------------------------+
1 row in set (0.02 sec)

field 第一次再那里出现

mysql> select field ('x', 'ab', 'abc', 'abx', 'x');
+--------------------------------------+
| field ('x', 'ab', 'abc', 'abx', 'x') |
+--------------------------------------+
|                                    4 |
+--------------------------------------+
1 row in set (0.00 sec)

find_in_set(s1, s2)

mysql> select find_in_set('x', 'xx,x,xxx');
+------------------------------+
| find_in_set('x', 'xx,x,xxx') |
+------------------------------+
|                            2 |
+------------------------------+

make_set(n, s1, s2, s3);

mysql> select make_set(1, 'a', 'b', 'c');
+----------------------------+
| make_set(1, 'a', 'b', 'c') |
+----------------------------+
| a                          |
+----------------------------+
1 row in set (0.00 sec)

mysql> select make_set(2, 'a', 'b', 'c');
+----------------------------+
| make_set(2, 'a', 'b', 'c') |
+----------------------------+
| b                          |
+----------------------------+
1 row in set (0.00 sec)

mysql> select make_set(3, 'a', 'b', 'c');
+----------------------------+
| make_set(3, 'a', 'b', 'c') |
+----------------------------+
| a,b                        |
+----------------------------+
1 row in set (0.00 sec)

###############

日期函数

1.curdate() , curdate_date()

mysql> select curdate() , current_date();
+------------+----------------+
| curdate()  | current_date() |
+------------+----------------+
| 2014-12-01 | 2014-12-01     |
+------------+----------------+
1 row in set (0.00 sec)

2. curtime(), current_time();

3. now(), current_timestamp(), localtime(), sysdate()

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2014-12-01 11:15:23 |
+---------------------+
1 row in set (0.01 sec)
mysql> select now()+0;
+----------------+
| now()+0        |
+----------------+
| 20141201111547 |
+----------------+

4.unix_timestamp()

mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1417403806 |
+------------------+
1 row in set (0.00 sec)

5. month(data), monthname(data)

mysql> select month(now()), monthname(now()) , month("2014-12-1");
+--------------+------------------+--------------------+
| month(now()) | monthname(now()) | month("2014-12-1") |
+--------------+------------------+--------------------+
|           12 | December         |                 12 |
+--------------+------------------+--------------------+

6. dayname(date), dayofweek(date), weekday(date);

mysql> select dayname(now()) , dayofweek(now()), weekday(now());
+----------------+------------------+----------------+
| dayname(now()) | dayofweek(now()) | weekday(now()) |
+----------------+------------------+----------------+
| Monday         |                2 |              0 |
+----------------+------------------+----------------+
1 row in set (0.00 sec)

7. week

mysql> select week(now()), weekofyear(now());
+-------------+-------------------+
| week(now()) | weekofyear(now()) |
+-------------+-------------------+
|          48 |                49 |
+-------------+-------------------+
1 row in set (0.00 sec)

8. dayofyear, dayofmonth

mysql> select dayofyear(now()), dayofmonth(now()), now();
+------------------+-------------------+---------------------+
| dayofyear(now()) | dayofmonth(now()) | now()               |
+------------------+-------------------+---------------------+
|              335 |                 1 | 2014-12-01 11:24:49 |
+------------------+-------------------+---------------------+
1 row in set (0.00 sec)

9. year, moth, day, hour, minute, second

mysql> select year(now()), month(now()), day(now()) , hour(now()), minute(now()) , second(now());

+-------------+--------------+------------+-------------+---------------+---------------+

| year(now()) | month(now()) | day(now()) | hour(now()) | minute(now()) | second(now()) |

+-------------+--------------+------------+-------------+---------------+---------------+

|        2014 |           12 |          1 |          11 |            28 |             8 |

+-------------+--------------+------------+-------------+---------------+---------------+

1 row in set (0.00 sec)

10. time_to_sec(now())

mysql> select time(now()) , time_to_sec(now()), time_to_sec(time(now()));
+-------------+--------------------+--------------------------+
| time(now()) | time_to_sec(now()) | time_to_sec(time(now())) |
+-------------+--------------------+--------------------------+
| 11:31:15    |              41475 |                    41475 |
+-------------+--------------------+--------------------------+
1 row in set (0.00 sec)

---

ipbbs.net