Mysql系统函数

一、concat();

mysql> select concat("张三","太棒了");+-------------------------+| concat("张三","太棒了") |+-------------------------+| 张三太棒了              |+-------------------------+1 row in set (0.00 sec)mysql> select concat("小明在",now(),"买了一个笔记本");+-----------------------------------------+| concat("小明在",now(),"买了一个笔记本") |+-----------------------------------------+| 小明在2017-04-29 18:46:17买了一个笔记本 |+-----------------------------------------+1 row in set (0.01 sec)

二、case

insert into student values(4,"小狗",12,2,"男"),(5,"张三",12,2,"女"),(6,"大锤",12,1,"男");select id,age,course,sex,(case when sex = "男" then concat(name,"很帅")            when sex = "女" then concat(ifnull(sex,name),"很美")           end) as one from student;+------+------+--------+------+----------+| id   | age  | course | sex  | one      |+------+------+--------+------+----------+|    1 |   18 | 1      | 男   | 小红很帅 ||    2 |   18 | 2      | 男   | 小强很帅 ||    3 |   18 | 1      | 女   | 分度很美 ||    4 |   12 | 2      | 男   | 小狗很帅 ||    5 |   12 | 2      | 女   | 张三很美 ||    6 |   12 | 1      | 男   | 大锤很帅 |+------+------+--------+------+----------+6 rows in set (0.00 sec)

三、ifnull();

mysql> select ifnull("yiben","erben");+-------------------------+| ifnull("yiben","erben") |+-------------------------+| yiben                   |+-------------------------+1 row in set (0.04 sec)mysql> select ifnull(null,"yiben");+----------------------+| ifnull(null,"yiben") |+----------------------+| yiben                |+----------------------+1 row in set (0.04 sec)mysql> select ifnull("","yiben");+--------------------+| ifnull("","yiben") |+--------------------+|                    |+--------------------+1 row in set (0.00 sec)mysql> select ifnull("null","yiben");+------------------------+| ifnull("null","yiben") |+------------------------+| null                   |+------------------------+1 row in set (0.00 sec)

四、if

if(1,2,3);1成立吗？成立执行2，不成立执行3；select if(1>2,"1大于2","1小于2");mysql> select if(1>2,"1大于2","1小于2");+---------------------------+| if(1>2,"1大于2","1小于2") |+---------------------------+| 1小于2                    |+---------------------------+1 row in set (0.00 sec)

五、字符串截取

substring()

set @a ="abcdefghig";select substring(@a,2);# 从第二个开始截取到末尾mysql> select substring(@a,2);+-----------------+| substring(@a,2) |+-----------------+| bcdefghig       |+-----------------+1 row in set (0.05 sec)substring(@a,2,3);mysql> select substring(@a,2,3);#从第二个开始截取三个+-------------------+| substring(@a,2,3) |+-------------------+| bcd               |+-------------------+1 row in set (0.00 sec)select left(@a,2);select right(@a,2);select lower(@a);select upper(@a);mysql> select left(@a,2);+------------+| left(@a,2) |+------------+| ab         |+------------+1 row in set (0.00 sec)mysql> select right(@a,2);+-------------+| right(@a,2) |+-------------+| ig          |+-------------+1 row in set (0.00 sec)mysql> select lower(@a);+------------+| lower(@a)  |+------------+| abcdefghig |+------------+1 row in set (0.00 sec)mysql> select upper(@a);+------------+| upper(@a)  |+------------+| ABCDEFGHIG |+------------+1 row in set (0.00 sec)

六、数学函数

遗漏知识点： 随机数rand()自己学习

select avg(course) from student;select sum(course) from student;select max(course) from student;select min(course) from student;mysql> select avg(course) from student;+--------------------+| avg(course)        |+--------------------+| 1.7142857142857142 |+--------------------+1 row in set (0.06 sec)mysql> select sum(course) from student;+-------------+| sum(course) |+-------------+|          12 |+-------------+1 row in set (0.00 sec)mysql> select max(course) from student;+-------------+| max(course) |+-------------+| 3           |+-------------+1 row in set (0.00 sec)mysql> select min(course) from student;+-------------+| min(course) |+-------------+| 1           |+-------------+1 row in set (0.00 sec)

八、日期函数

(1)date_format();select date_format(now(),"%Y年%M月%d日 %H时%i分%s秒");mysql> select date_format(now(),"%Y年%M月%d日 %H时%i分%s秒");+------------------------------------------------+| date_format(now(),"%Y年%M月%d日 %H时%i分%s秒") |+------------------------------------------------+| 2017年April月29日 20时17分00秒                 |+------------------------------------------------+1 row in set (0.00 sec)mysql> select date_format(now(),"%Y年%M月%d日 %H时%i分%s秒");+------------------------------------------------+| date_format(now(),"%Y年%M月%d日 %H时%i分%s秒") |+------------------------------------------------+| 2017年April月29日 20时17分05秒                 |+------------------------------------------------+1 row in set (0.00 sec)mysql> select date_format(now(),"%Y年%M月%d日 %H时%i分%s秒");+------------------------------------------------+| date_format(now(),"%Y年%M月%d日 %H时%i分%s秒") |+------------------------------------------------+| 2017年April月29日 20时17分10秒                 |+------------------------------------------------+1 row in set (0.00 sec)

(2)datediff()# 时间1减去时间2

select datediff(now(),"2016/4/29");mysql> select datediff(now(),"2016/4/29");+-----------------------------+| datediff(now(),"2016/4/29") |+-----------------------------+|                         365 |+-----------------------------+1 row in set (0.10 sec)

(3)date_add()#增加一点时间
(4)date_sub()

mysql> select date_sub(now(),interval 1 hour);
+———————————+
| date_sub(now(),interval 1 hour) |
+———————————+
| 2017-04-29 19:25:20 |
+———————————+
1 row in set (0.03 sec)

(5)str_to_date()
把字符串类型转化为时间类型

select str_to_date("2015年3月4日","%Y年%m月%d日");mysql> select str_to_date("2015年3月4日","%Y年%m月%d日");+--------------------------------------------+| str_to_date("2015年3月4日","%Y年%m月%d日") |+--------------------------------------------+| 2015-03-04                                 |+--------------------------------------------+1 row in set (0.00 sec)