MYSQL系列-MYSQL基础增强(Myql函数)

时间：2023-03-08 22:58:12
MYSQL系列-MYSQL基础增强(Myql函数)

MYSQL基础增强(Myql函数)

在这里只介绍一些常用的，比较新颖的：

字符串函数：

 CONCAT：//字符串连接函数

 mysql> SELECT CONCAT('My', 'S', 'QL');

         -> 'MySQL'

 mysql> SELECT CONCAT('My', NULL, 'QL');//与null连接会变null

         -> NULL

 mysql> SELECT CONCAT(14.3);

         -> '14.3'

 CONCAT_WS：//含分隔符的字符串连接

 mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');

         -> 'First name,Second name,Last Name'

 mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');

         -> 'First name,Last Name'

 LOWER(str)/UPPER(str) //把字符串转换成小写或者大写

 LEFT(str,x)/RIGHT(str,x) //返回字符串最左边的x个字符和最右边的x个字符

 SUBSTRING(str,x,y) //对给定字符串进行提取x位置往后的y个字符

 mysql> select substring('I love mysql',8,4);

        -> ‘mysql’

 find_in_set(str,strList)//实现如果字符串str在由N子串组成的表strlist之中，返回一个1到N的值。strList是被‘，’分隔的字符串组成的。

 例1：

 create table books(

     bookid int primary key not null auto_increment 

 comment '用户id',

     bookname varchar(50) not null,

     sort varchar(100)

 );

 insert into books(bookid,bookname,sort) values

 (1,'大学英语','english,teach'),

 (2,'C语言','language,c,old'),

 (3,'高等数学','teach,olding');

 select bookid,bookname from books where find_in_set('teach',sort);//其中sort是book表的列

 mysql> SELECT LENGTH('text');//返回str的长度

         -> 4

数值函数：

函数 功能
ABS(x) 返回x的绝对值
CEIL(x) 向上取整
FLOOR(x) 向下取整
MOD(x,y) 返回x/y的模
RAND() 返回0-1之间的随机数
ROUND(x,y) 返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y) 返回x截断为y位小数的结果 
PI();

mysql> SELECT PI();

        -> 3.141593

mysql> SELECT PI()+0.000000000000000000;

        -> 3.141592653589793116
5.7数值函数地址:http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_abs

控制流函数：

CASE：

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

第一种版本：返回 result 当value=compare_value   result

第二种版本：返回 result 当condition为真时。

返回的数据类型依赖于你使用的数据类型

例如：

mysql> SELECT CASE 1 WHEN 1 THEN 'one'

    ->     WHEN 2 THEN 'two' ELSE 'more' END;

        -> 'one'  //1=1所以输出one

mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;

        -> 'true' //1>0位真所以输出true

mysql> SELECT CASE BINARY 'B'

    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;

        -> NULL  

IF(str1,str2,str3):

mysql> SELECT IF(1<2,'yes','no');

        -> 'yes'

IFNUll(str1,str2)

mysql> SELECT IFNULL(1,0);

        -> 1

mysql> SELECT IFNULL(NULL,10);

        -> 10

mysql> SELECT IFNULL(1/0,10);

        -> 10

mysql> SELECT IFNULL(1/0,'yes');

        -> 'yes'

NULL IF(str1,str2):

如果str1==str2返回NULL，否则返回str1，跟CASE when str1=str2 THEN NULL ELSE str1 end 相似。

mysql> SELECT NULLIF(1,1);

        -> NULL

mysql> SELECT NULLIF(1,2);

        -> 1

注意：在连接操作不要这么使用 IFNULL(a.col,1) = IFNULL(b.col,1) 

下面通过一个例子来说明流程函数的应用：

假设每个学生都会有高数成绩，和英语成绩。新建一个表如下：（避免乱码尽量不适用中文）

create table students(
   sid int,
  _math decimal(9,2),
  english varchar(3)
);

插入测试数据：

insert into students values(1,50,''),(2,70,''),(3,80,''),(4,null,''),(5,80,null),(6,null,null);

mysql> select * from students;

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

| 　　sid | 　　_math | 　　english |

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

|       1 |    50.00 | 50          |

|       2 |    70.00 | 70          |

|       3 |    80.00 | 80          |

|       4 |     NULL | 80          |

|       5 |    80.00 | NULL        |

|       6 |     NULL | NULL        |

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

案例1：使用CASE将成绩分级

mysql> select case when _math<60 then 'unpass' else 'pass' end result from students;

+--------+

| result |

+--------+

| unpass |

| pass   |

| pass   |

| pass   |

| pass   |

| pass   |

+--------+

mysql> select case _math when 80 then '优秀' when 70 then '合格' else '不合格' end result from students;

+--------+

| result |

+--------+

| 不合格 |

| 合格   |

| 优秀   |

| 不合格 |

| 优秀   |

| 不合格 |

+--------+

案例2：查询students表，将数学成绩超过60分的学生用pass表示，未超过60分的用unpass表示。

mysql> select IF(_math>=60,'pass','unpass') result from students;

+--------+

| result |

+--------+

| unpass |

| pass   |

| pass   |

| unpass |

| pass   |

| unpass |

+--------+

从上面这个例子可以看出它默认统计了NULL值。

案例3：NULL值用0代替

mysql> select ifnull(_math,0) from students;

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

| ifnull(_math,0) |

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

|           50.00 |

|           70.00 |

|           80.00 |

|            0.00 |

|           80.00 |

|            0.00 |

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

 其他常用函数

函数 功能
DATABASE() 返回当前数据库名称
MD5() 返回str的md5值
USER() 返回当前登录用户名
INET_ATON() 返回IP地址的数字表示
INET_NTOA() 返回数字表示的ip地址
PASSWORD(str) 加密str
VERSION() 返回当当前数据库的版本 
注意：
PASSWORD()函数只能用来设置系统用户的密码，不能对应用数据进行加密，如果想对数据进行加密可使用MD5()。
mysql> select version();

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

| version() |

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

| 5.7.3-m13 |

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

mysql> select DATABASE();

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

| DATABASE() |

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

| liuning    |

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

mysql> select INET_ATON("192.168.3.100");

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

| INET_ATON("192.168.3.100") |

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

|                 3232236388 |

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

INET_ATON()函数可以实现ip地址的比较：

案例1：

mysql> select * from t1;

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

| id   | ip           |

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

|    1 | 192.168.1.1  |

|    2 | 192.168.1.3  |

|    3 | 192.168.1.10 |

|    4 | 192.168.1.13 |

|    5 | 192.168.1.20 |

|    6 | 192.168.1.30 |

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

mysql> select * from t1 where ip >= '192.168.1.3' and ip <= '192.168.1.20';

Empty set (0.00 sec)

mysql> select * from t1 where inet_aton(ip)>=inet_aton('192.168.1.3') and inet_aton(ip)<= inet_aton('192.168.1.20');

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

| id   | ip           |

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

|    2 | 192.168.1.3  |

|    3 | 192.168.1.10 |

|    4 | 192.168.1.13 |

|    5 | 192.168.1.20 |

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

相关网址：

Mysql5.7控制流函数地址：http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#operator_case
Mysql5.x手册 http://doc.mysql.cn/Mysql官网 http://dev.mysql.com/
Mysql5.7参考手册 http://dev.mysql.com/doc/refman/5.7/en/
其他参考手册****-Mysql知识库

author： liuning

相关文章