mysql 流程函数_巧用MySQL流程函数

流程函数

MariaDB [test]> create table salary(userid int,salary decimal(9,2));

Query OK, 0 rows affected (0.04 sec)

MariaDB [test]> insert into salaryvalues(1,1000),(2,2000),(3,3000),(4,4000);

Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0  Warnings: 0

MariaDB [test]> selectif(salary>2000,'high','low')as xinzi from salary;

+-------+

| xinzi |

+-------+

| low  |

| low  |

| high |

| high |

+-------+

4 rows in set (0.00 sec)

MariaDB [test]> selectcase whensalary<=2000 then 'low' else 'high' endfrom salary;

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

| case when salary<=2000 then 'low' else'high' end |

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

| low                                              |

| low                                              |

| high                                              |

| high                                              |

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

4 rows in set (0.00 sec)

MariaDB [test]> select case salary when1000 then 'low' when 2000 then 'mid' else 'high' end from salary;

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

| case salary when 1000 then 'low' when2000 then 'mid' else 'high' end |

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

| low                                                                  |

| mid                                                                  |

| high                                                                 |

| high                                                                 |

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

4 rows in set (0.00 sec)

其他常用函数函数功能

DATABASE()返回当前数据库名

VARSION()返回当前数据库版本

USER()返回当前登录用户名

INET_ATON(IP)返回IP地址的数字表示

INET_NTOA(num)返回数字代表的IP地址

PASSWORD(str)返回字符串str的加密版本

MD5()返回字符串str的MD5值