(4)常用函数_员工性别统计 if(表达式, tvalue, fvalue) :当表达式为true时,取值tvalu-优快云博客

本文链接：https://blog.youkuaiyun.com/u011509781/article/details/42081753

一：字符串函数

1：CONCAT(S1，S2，...Sn)函数：将传入的参数连接成一个字符串。

mysql> select concat('aaa','bbb','ccc'),concat('aaa',null);
+---------------------------+--------------------+
| concat('aaa','bbb','ccc') | concat('aaa',null) |
+---------------------------+--------------------+
| aaabbbccc | NULL |
+---------------------------+--------------------+
1 row in set (0.00 sec)

注意，任何字符串和null相连，结果都会是null。

2：INSERT(str,x,y,instr)函数：将字符串str从x位置开始，y长度的字串替换为instr。

mysql> select INSERT('beijing2008you',12,3,'me');
+------------------------------------+
| INSERT('beijing2008you',12,3,'me') |
+------------------------------------+
| beijing2008me |
+------------------------------------+
1 row in set (0.00 sec)

3：UPPER()将字符串转换为大写，LOWER()将字符串转换为小写

4：LEFT(str,x)和RIGHT(str,x) 返回字符串左边x字符和字符串右边x字符。

mysql> select LEFT('beijing2008',7),RIGHT('beijing2008',4);
+-----------------------+------------------------+
| LEFT('beijing2008',7) | RIGHT('beijing2008',4) |
+-----------------------+------------------------+
| beijing | 2008 |
+-----------------------+------------------------+
1 row in set (0.00 sec)

5：LPAD(str,n,pad) 和RPAD(str,n,pad) 对str从左边或者右边填充，填充n个字符(pad)

mysql> select LPAD(2008,20,'nihao'),RPAD(2008,20,'nihao');
+-----------------------+-----------------------+
| LPAD(2008,20,'nihao') | RPAD(2008,20,'nihao') |
+-----------------------+-----------------------+
| nihaonihaonihaon2008 | 2008nihaonihaonihaon |
+-----------------------+-----------------------+
1 row in set (0.00 sec)

6：LTRIM(str)、RTRIM(str) 、TRIM(str)去掉字符串左边、右边、两边空格

7：repeat(str,n) 重复str n次

mysql> select repeat('mysql',3);
+-------------------+
| repeat('mysql',3) |
+-------------------+
| mysqlmysqlmysql |
+-------------------+
1 row in set (0.02 sec)

8：replace(str,搜索字符，替换字符)

mysql> select replace('beijing2010','2010','2008');
+--------------------------------------+
| replace('beijing2010','2010','2008') |
+--------------------------------------+
| beijing2008 |
+--------------------------------------+
1 row in set (0.00 sec)

10：strcmp(s1,s2) 比较s1和s2的ASCII值。如果s1>s2，则返回1；若s1=s2，则返回0；若s1<s2，则返回-1.

mysql> select strcmp('a','b'),strcmp('b','b'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('b','b') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
| -1 | 0 | 1 |
+-----------------+-----------------+-----------------+
1 row in set (0.01 sec)

11：substring(str,x,y)将str从x开始，截取y个字符串。

mysql> select substring('hello',2,4);
+------------------------+
| substring('hello',2,4) |
+------------------------+
| ello |
+------------------------+
1 row in set (0.00 sec)

二：数值函数

1：返回绝对值 ABS(X)

mysql> select abs(-1.2);
+-----------+
| abs(-1.2) |
+-----------+
| 1.2 |
+-----------+
1 row in set (0.01 sec)

2：ceil(x)返回不小于x的最小整数

mysql> select ceil(2.8),ceil(-0.8);
+-----------+------------+
| ceil(2.8) | ceil(-0.8) |
+-----------+------------+
| 3 | 0 |
+-----------+------------+
1 row in set (0.00 sec)

3：floor(x) 返回小于x的最大值

mysql> select floor(2.4);
+------------+
| floor(2.4) |
+------------+
| 2 |
+------------+
1 row in set (0.00 sec)

4：mod(x,y) 返回x%y的值

mysql> select mod(2,3);
+----------+
| mod(2,3) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

5：RAND()返回0-1内的随机数

mysql> select rand(),rand();
+--------------------+--------------------+
| rand() | rand() |
+--------------------+--------------------+
| 0.5917097978216688 | 0.5511597139305991 |
+--------------------+--------------------+
1 row in set (0.00 sec)

6：round(x,y)返回x四舍五入y位后的结果

mysql> select round(1.2,0),round(1.2,4);
+--------------+--------------+
| round(1.2,0) | round(1.2,4) |
+--------------+--------------+
| 1 | 1.2000 |
+--------------+--------------+
1 row in set (0.00 sec)

7：truncate（x,y）返回数字x截断为y位后的结果，只是截断，不四舍五入。

mysql> select round(1.235,2),truncate(1.235,2);
+----------------+-------------------+
| round(1.235,2) | truncate(1.235,2) |
+----------------+-------------------+
| 1.24 | 1.23 |
+----------------+-------------------+
1 row in set (0.00 sec)

三：日期和时间函数

1：curdate()返回当前日期

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

2：curtime()返回当前时间

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:32:44 |
+-----------+
1 row in set (0.01 sec)

3：now()返回当前日期、时间

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2014-12-30 15:34:16 |
+---------------------+
1 row in set (0.00 sec)

4：UNIX_TIMESTAMP()返回UNIX的时间戳。

mysql> select UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
| 1419925971 |
+-----------------------+
1 row in set (0.00 sec)

5：week(now()) ,year(now())分别返回now()的第几周，第几年。

mysql> select week(now()),year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
| 52 | 2014 |
+-------------+-------------+
1 row in set (0.00 sec)

6：hour(now()),minute(now())返回当前时间的小时，当前时间的分钟。

mysql> select hour(now()),minute(now());
+-------------+---------------+
| hour(now()) | minute(now()) |
+-------------+---------------+
| 15 | 56 |
+-------------+---------------+
1 row in set (0.00 sec)

7.monthname(now()) 返回当前时间的英文月份名称

mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| December |
+------------------+
1 row in set (0.00 sec)

8.datediff(date1,date2)计算两个日期之间相差的天数

mysql> select datediff('2015-11-11',now());
+------------------------------+
| datediff('2015-11-11',now()) |
+------------------------------+
| 315 |
+------------------------------+
1 row in set (0.01 sec)

四：流程函数

1.if(value,t,f) 当value为true时，返回t,否则返回f

2.ifnull(value1,value2) 如果value1不为空，返回value1,value1为空，返回value2。

mysql> select ifnull(salary,0) from salary;
+------------------+
| ifnull(salary,0) |
+------------------+
| 1000.00 |
| 2000.00 |
| 3000.00 |
| 4000.00 |
| 5000.00 |
| 0.00 |
+------------------+
6 rows in set (0.00 sec)

3.case when [value1] then [result1]... else[default] end;

4.case [expr] when [value1] then [result1] when[value2] then[value2]...else[default] end

mysql> select case salary when 1000 then 'low' when 2000 then 'mid' else 'high'
end from salary;
+-----------------------------------------------------------------------+
| case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end |
+-----------------------------------------------------------------------+
| low |
| mid |
| high |
| high |
| high |
| high |
+-----------------------------------------------------------------------+
6 rows in set (0.00 sec)

五：其他函数

1.database() 返回当前数据库名

mysql> select database();
+------------+
| database() |
+------------+
| eric |
+------------+
1 row in set (0.00 sec)

2.version()返回数据库版本

mysql> select version();
+------------+
| version() |
+------------+
| 5.5.20-log |
+------------+
1 row in set (0.00 sec)

3.user()返回当前用户名

mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

4.inet_aton(ip)返回IP地址的数字地址

mysql> select INET_ATON('192.168.134.27');
+-----------------------------+
| INET_ATON('192.168.134.27') |
+-----------------------------+
| 3232269851 |
+-----------------------------+
1 row in set (0.00 sec)

5.INET_NTOA(num) 返回数字代表的ip地址

mysql> select INET_NTOA('3232269851');
+-------------------------+
| INET_NTOA('3232269851') |
+-------------------------+
| 192.168.134.27 |
+-------------------------+
1 row in set (0.00 sec)

6.password('str') 加密str

mysql> select password('nihao');
+-------------------------------------------+
| password('nihao') |
+-------------------------------------------+
| *364870DF09C7E82F6A8ED19ED529F3375E57B3CF |
+-------------------------------------------+
1 row in set (0.00 sec)

7.md5('str')返回md5()加密后的字符串

mysql> select md5('nihao');
+----------------------------------+
| md5('nihao') |
+----------------------------------+
| 194ce5d0b89c47ff6b30bfb491f9dc26 |
+----------------------------------+
1 row in set (0.00 sec)