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)