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

MYSQL基础增强(Myql函数)

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

字符串函数：

 1 CONCAT：//字符串连接函数
 2 mysql> SELECT CONCAT('My', 'S', 'QL');
 3         -> 'MySQL'
 4 mysql> SELECT CONCAT('My', NULL, 'QL');//与null连接会变null
 5         -> NULL
 6 mysql> SELECT CONCAT(14.3);
 7         -> '14.3'
 8 
 9 CONCAT_WS：//含分隔符的字符串连接
10 mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
11         -> 'First name,Second name,Last Name'
12 mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
13         -> 'First name,Last Name'
14 
15 LOWER(str)/UPPER(str) //把字符串转换成小写或者大写
16 LEFT(str,x)/RIGHT(str,x) //返回字符串最左边的x个字符和最右边的x个字符
17 SUBSTRING(str,x,y) //对给定字符串进行提取x位置往后的y个字符
18 mysql> select substring('I love mysql',8,4);
19        -> ‘mysql’
20 
21 find_in_set(str,strList)//实现如果字符串str在由N子串组成的表strlist之中，返回一个1到N的值。strList是被‘，’分隔的字符串组成的。
22 例1：
23 create table books(
24     bookid int primary key not null auto_increment 
25 
26 comment '用户id',
27     bookname varchar(50) not null,
28     sort varchar(100)    
29 );
30 insert into books(bookid,bookname,sort) values
31 (1,'大学英语','english,teach'),
32 (2,'C语言','language,c,old'),
33 (3,'高等数学','teach,olding');
34 select bookid,bookname from books where find_in_set('teach',sort);//其中sort是book表的列
35 
36 mysql> SELECT LENGTH('text');//返回str的长度
37         -> 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,'50'),(2,70,'70'),(3,80,'80'),(4,null,'80'),(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 |
+------+--------------+