MairaDB 函数(九)

作者: simuty | 来源:发表于2016-12-20 10:55 被阅读30次

MySQL 有很多内置函数可助你对列中的数据进行操作。

1. 数学函数
2. 字符串函数
3. 日期和时间函数
4. 条件判断函数
5. 系统信息函数
6. 加密函数

第一部分数学函数

1. ABS(X):返回X的绝对值<absolute value>;
2. MOD(N,M)或%:返回N被M除的余数;MOD(7, 3) 表示7除以3得1;
3. FLOOR(X):返回不大于X的最大整数值;FLOOR(1.2) => 1;FLOOR<地板>
4. CEILING(X):返回不小于X的最小整数值。 CEILING(1.2) => 2;CEILING<上限,天花板>
5. ROUND(X) :返回参数X的四舍五入的一个整数。 ROUND(5.4) => 5;ROUND<圆的>
6. SQRT(x): 平方根函数;
7. RAND(x): 随机数函数. 产生一个在 0 和 1 之间的随机数; 若要在i ≤ R ≤ j 这个范围得到一个随机整数R ，需要用到表达式 FLOOR(i + RAND() * (j – i + 1))

例子:

MariaDB [(none)]> SELECT ABS(-1.2), MOD(7, 3), FLOOR(1.2), CEILING(1.2), ROUND(5.4), ROUND(5.6), SQRT(5), SQRT(4), RAND(), FLOOR(10 + (RAND() * 81));
+-----------+-----------+------------+--------------+------------+------------+------------------+---------+--------------------+---------------------------+
| ABS(-1.2) | MOD(7, 3) | FLOOR(1.2) | CEILING(1.2) | ROUND(5.4) | ROUND(5.6) | SQRT(5)          | SQRT(4) | RAND()             | FLOOR(10 + (RAND() * 81)) |
+-----------+-----------+------------+--------------+------------+------------+------------------+---------+--------------------+---------------------------+
|       1.2 |         1 |          1 |            2 |          5 |          6 | 2.23606797749979 |       2 | 0.2063866781130309 |                        49 |
+-----------+-----------+------------+--------------+------------+------------+------------------+---------+--------------------+---------------------------+
1 row in set (0.00 sec)

屏幕快照 2016-12-19 上午11.44.28.png

8. AVG(列名) 函数用来在不同记录中找出某一字段的平均值。

第二部分字符串函数

2.1 第一个表

1. ASCII('str'): 返回字符串 str 中最左边字符的 ASCII 代码值。
2. BIN(N): 返回十进制数值 N 的二进制数值的字符串表现形式。
3. BIT_LENGTH(str): 返回字符串 str 所占的位长度。
4. CHAR(N,... [USING charset_name]): 会将每一个参数 N 都解释为整数，返回由这些整数在 ASCII 码中所对应字符所组成的字符串。
5. CHAR_LENGTH(str): 单纯返回 str 的字符串长度;
6. CONV(N,from_base,to_base): 将数值在不同进制间转换。
7. COMPRESS():压缩(不推荐使用 CHAR 或VARCHAR 来存储压缩字符串。最好使用一个 BLOB 列代替);
8. UNCOMOPRESS(): 解压内容

压缩实例:

MariaDB [study_db]> SELECT 
    -> LENGTH(REPEAT('a', 50)),
    -> LENGTH(COMPRESS(REPEAT('a',50))),
    -> COMPRESS(REPEAT('a',50)),
    -> UNCOMPRESS(COMPRESS(REPEAT('a',50)));
+-------------------------+----------------------------------+--------------------------+----------------------------------------------------+
| LENGTH(REPEAT('a', 50)) | LENGTH(COMPRESS(REPEAT('a',50))) | COMPRESS(REPEAT('a',50)) | UNCOMPRESS(COMPRESS(REPEAT('a',50)))               |
+-------------------------+----------------------------------+--------------------------+----------------------------------------------------+
|                      50 |                               16 | 2   x?KL$  ?\?            | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
+-------------------------+----------------------------------+--------------------------+----------------------------------------------------+
1 row in set (0.00 sec)

2.2 大小写UPPER() 与LOWER()

MariaDB [study_db]> select book_name, book_author_name FROM new_books;
+------------------+---------------------------+
| book_name        | book_author_name          |
+------------------+---------------------------+
| 跟我学MariaDB    | hhw                       |
| 跟我学NodeJS     | hhw                       |
| HTTP权威指南     | O'Reilly                  |
| 追风筝的人       | Khaled Hosseini           |


MariaDB [study_db]> select UPPER(book_name),LOWER( book_author_name) FROM new_books;
+------------------+---------------------------+
| UPPER(book_name) | LOWER( book_author_name)  |
+------------------+---------------------------+
| 跟我学MARIADB    | hhw                       |
| 跟我学NODEJS     | hhw                       |
| HTTP权威指南     | o'reilly                  |
| 追风筝的人       | khaled hosseini           |
| 小王子           | antoine marie jean-b      |

2.3 CONCAT(str1,str2,...):返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。

MariaDB [study_db]> SELECT CONCAT(book_name, '   的作者是    ', book_author_name) FROM new_books;
+--------------------------------------------------------------+
| CONCAT(book_name, '    的作者是    ', book_author_name)      |
+--------------------------------------------------------------+
| 跟我学MariaDB    的作者是    hhw                             |
| 跟我学NodeJS    的作者是    hhw                              |
| HTTP权威指南    的作者是    O'Reilly                         |
| 追风筝的人    的作者是    Khaled Hosseini                    |

2.4 LENGTH(str):返回字符串str的长度。

MariaDB [study_db]> SELECT book_name  ,LENGTH(book_name) FROM new_books;
+------------------+-------------------+
| book_name        | LENGTH(book_name) |
+------------------+-------------------+
| 跟我学MariaDB    |                16 |
| 跟我学NodeJS     |                15 |
| HTTP权威指南     |                16 |


MariaDB [study_db]> SELECT LENGTH('123');
+---------------+
| LENGTH('123') |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

2.5 LOCATE(substr,str):返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0.

MariaDB [study_db]> SELECT LOCATE('ABC', 'labce');
+------------------------+
| LOCATE('ABC', 'labce') |
+------------------------+
|                      2 |
+------------------------+
1 row in set (0.00 sec)

MariaDB [study_db]> SELECT LOCATE('ABC', 'labe');
+-----------------------+
| LOCATE('ABC', 'labe') |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)

2.6 LEFT(str,len):返回字符串str的最左面len个字符。RIGHT(str,len):返回字符串str的最右面len个字符。

MariaDB [study_db]> SELECT LEFT('HUANG', 2), RIGHT('HUANG', 3);
+------------------+-------------------+
| LEFT('HUANG', 2) | RIGHT('HUANG', 3) |
+------------------+-------------------+
| HU               | ANG               |
+------------------+-------------------+
1 row in set (0.00 sec)

2.7 TRIM(str):返回字符串str，所有前缀或后缀被删除了。

1. LTRIM(str):返回删除了其前置空格字符的字符串str。
2. RTRIM(str):返回删除了其拖后空格字符的字符串str。

实例

MariaDB [study_db]> SELECT TRIM('    HUAGN  ');
+---------------------+
| TRIM('    HUAGN  ') |
+---------------------+
| HUAGN               |
+---------------------+
1 row in set (0.00 sec)

2.8 替换REPLACE(str, from_str, to_str):返回字符串str，其字符串from_str的所有出现由字符串to_str代替。

MariaDB [study_db]> SELECT REPLACE('NODEJS', 'JS', 'study');
+----------------------------------+
| REPLACE('NODEJS', 'JS', 'study') |
+----------------------------------+
| NODEstudy                        |
+----------------------------------+
1 row in set (0.00 sec)

2.9 REPEAT(str,count):返回由重复countTimes次的字符串str组成的一个字符串。如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL。

MariaDB [study_db]> SELECT REPEAT('HHW', 24);
+--------------------------------------------------------------------------+
| REPEAT('HHW', 24)                                                        |
+--------------------------------------------------------------------------+
| HHWHHWHHWHHWHHWHHWHHWHHWHHWHHWHHWHHWHHWHHWHHWHHWHHWHHWHHWHHWHHWHHWHHWHHW |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.10.1 REVERSE(str):返回颠倒字符顺序的字符串str。

MariaDB [study_db]> SELECT REVERSE('123456');
+-------------------+
| REVERSE('123456') |
+-------------------+
| 654321            |
+-------------------+
1 row in set (0.00 sec)

2.10.2 SUBSTRING(str, pos):取出str前pos个字符, 返回剩余的字符;

MariaDB [study_db]> SELECT SUBSTRING('ABCDEFGHIJKLMN', 4);
+--------------------------------+
| SUBSTRING('ABCDEFGHIJKLMN', 4) |
+--------------------------------+
| DEFGHIJKLMN                    |
+--------------------------------+
1 row in set (0.00 sec)

2.10.3 INSERT(str,pos,len,newstr):返回字符串str，在位置pos起始的子串且len个字符长的子串由字符串newstr代替。

MariaDB [study_db]> SELECT INSERT('1234567890', 4, 2, 'mariadb');
+---------------------------------------+
| INSERT('1234567890', 4, 2, 'mariadb') |
+---------------------------------------+
| 123mariadb67890                       |
+---------------------------------------+
1 row in set (0.00 sec)

第三部分日期和时间函数

3.1 当前的日期时间

1. NOW(): 当前日期+时间;
2. CURDATE(); 获得当前日期（date);
3. CURDATETIME(); 当前时间;
4. UTC_TIMESTAMP(); (当前日期+/-区时)+(时间+/-区时);
5. UTC_DATE(); 当前日期+/-区时;
6. UTC_TIME(); 当前时间+/-区时

实例一: 本地时间

MariaDB [study_db]> SELECT NOW(), CURDATE(), CURTIME();
+---------------------+------------+-----------+
| NOW()               | CURDATE()  | CURTIME() |
+---------------------+------------+-----------+
| 2016-12-19 14:01:03 | 2016-12-19 | 14:01:03  |
+---------------------+------------+-----------+
1 row in set (0.00 sec)

实例二: UTC(世界协调时)表示的当地时间

MariaDB [study_db]> SELECT UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME();
+---------------------+------------+------------+
| UTC_TIMESTAMP()     | UTC_DATE() | UTC_TIME() |
+---------------------+------------+------------+
| 2016-12-19 06:01:49 | 2016-12-19 | 06:01:49   |
+---------------------+------------+------------+
1 row in set (0.00 sec)

3.2 MySQL 日期时间** Extract（选取）函数。**

选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒


1. DATE(T);
2. TIME(T);
3. YEAR(T);
4. QUARTER(T);//季度
4. MONTH(T);
5. DAY(T);
6. HOUR(T);
7. MINTUE(T);
8. SECOND(T);
9. microsecond(T)

屏幕快照 2016-12-19 下午2.14.00.png

3.3 MySQL OF 函数：

设 T=NOW(); 
----该天分别在周/月/年中的位置----
1. DAYOFWEEK(T);（1 = Sunday, 2 = Monday, ..., 7 = Saturday）
2. DAYOFMONTH(T);
3. DAYOFYEAR(T);
4. ----------------
5. WEEK(T); 
6. WEEKOFYEAR(T); 等同于WEEK(T);
7. WEEKDAY(T); (0 = Monday, 1 = Tuesday, ..., 6 = Sunday)；

MariaDB [study_db]> SELECT DAYOFWEEK(NOW()), DAYOFMONTH(NOW()), DAYOFYEAR(NOW());
+------------------+-------------------+------------------+
| DAYOFWEEK(NOW()) | DAYOFMONTH(NOW()) | DAYOFYEAR(NOW()) |
+------------------+-------------------+------------------+
|                2 |                19 |              354 |
+------------------+-------------------+------------------+
1 row in set (0.00 sec)

MariaDB [study_db]> SELECT WEEK(NOW()), WEEKOFYEAR(NOW()), WEEKDAY(NOW()), YEARWEEK(NOW());
+-------------+-------------------+----------------+-----------------+
| WEEK(NOW()) | WEEKOFYEAR(NOW()) | WEEKDAY(NOW()) | YEARWEEK(NOW()) |
+-------------+-------------------+----------------+-----------------+
|          51 |                51 |              0 |          201651 |
+-------------+-------------------+----------------+-----------------+
1 row in set (0.00 sec)

3.4 计算函数

------加--------
1. DATE_ADD(T, interval 1 YEAR);
2. DATE_ADD(T, interval 1 SQUARTER);
4. --------减------
5. DATE_SUB(T, interval 1 YEAR);
6. 
6. 加减的参数: MONTH/DAY/HOUT/SECOND/MINUTE

实例代码

MariaDB [study_db]> SELECT publication_date, DATE_ADD(publication_date, interval 1 year) FROM new_books;
+------------------+---------------------------------------------+
| publication_date | DATE_ADD(publication_date, interval 1 year) |
+------------------+---------------------------------------------+
| 2016-12-03       | 2017-12-03                                  |
| 2016-12-02       | 2017-12-02                                  |

3.5 MySQL 日期、时间相减函数

1. DATEDIFF(date1,date2)：两个日期相减date1 date2，返回天数。
2. TIMEDIFF(time1,time2): 两个日期相减time1 time2，返回time 差值。

实例

MariaDB [study_db]> SELECT DATEDIFF(DATE(NOW()), '2016-12-31') AS '天数差',
    -> TIMEDIFF(TIME(NOW()), '24:24:00') AS '时间差';
    
+--------+-----------+
| 天数差  | 时间差      |
+--------+-----------+
|    -12 | -09:08:10 |
+--------+-----------+
1 row in set (0.00 sec)

第四部分条件判断函数

4.1 IFNULL 与 NULLIF

1. IFNULL(expr1,expr2): 假如expr1 不为 NULL，则返回expr1; 否则返回expr2。
2. NULLIF(expr1,expr2): 如果 expr1 = expr2，那么返回 NULL，否则返回expr1。

实例:

MariaDB [study_db]> SELECT book_author_name,  
    -> IFNULL(book_author_name, '匿名') AS '作者' 
    -> FROM new_books 
    -> WHERE book_id > 20;
+------------------+----------------+
| book_author_name | 作者           |
+------------------+----------------+
|  司马迁          |  司马迁        |
|  Ken Follett     |  Ken Follett   |
|  马克.李维       |  马克.李维     |
| NULL             | 匿名           |
+------------------+----------------+
4 rows in set (0.01 sec)


MariaDB [study_db]> SELECT NULLIF(1, 2), NULLIF(1, 1);
+--------------+--------------+
| NULLIF(1, 2) | NULLIF(1, 1) |
+--------------+--------------+
|            1 |         NULL |
+--------------+--------------+
1 row in set (0.00 sec)

4.2 IF流程 -- IF(expr1,expr2,expr3)

如果expr1为真, 返回expr2, 否则返回expr3;

实例

MariaDB [study_db]> SELECT IF(1 > 2, 'Y', 'N');
+---------------------+
| IF(1 > 2, 'Y', 'N') |
+---------------------+
| N                   |
+---------------------+
1 row in set (0.00 sec)

4.3 CASE..THEN..END

先看CASE的用法：将case的值（或数据库里的字段）与条件进行匹配判断，如为真则将值置为THEN后面的值，命令表达式:

1. CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END 
2. CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

实例代码

MariaDB [study_db]> SELECT 
    ->      CASE 
    ->      WHEN book_price < 40 THEN 'A'
    ->      WHEN book_price > 50 AND book_price < 100 THEN 'B'
    ->      WHEN book_price > 100 THEN 'C'
    ->      ELSE 'D'
    ->      END 
    ->      AS '价格分类', 
    ->      book_price FROM new_books;
    
+--------------+------------+
| 价格分类     | book_price |
+--------------+------------+
| D            |      44.00 |
| D            |      44.44 |
| B            |      79.00 |
| A            |      26.00 |
| A            |      26.00 |
| A            |      26.60 |
| D            |      40.00 |
| A            |      30.00 |
| D            |      44.00 |
| B            |      80.00 |
| C            |     123.00 |
| A            |      30.00 |
| D            |       NULL |

第五部分. 系统信息函数

5.1 当前用户名,三种都行USER()/SYSTEM_USER()/SESSION_USER()

MariaDB [study_db]> SELECT USER(), SYSTEM_USER(), SESSION_USER();

+----------------+----------------+----------------+
| USER()         | SYSTEM_USER()  | SESSION_USER() |
+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+
1 row in set (0.00 sec)

5.2 版本号

MariaDB [study_db]> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 10.1.19-MariaDB |
+-----------------+
1 row in set (0.00 sec)

第六部分. 加密函数

1. AES_ENCRYPT(str, key);//返回使用密钥key对字符串str利用高级**加密**标准算法加密后的结果 encrypt(编码)
2. AES_DECRYPT(str, key);//返回使用密钥key对字符串str利用高级加密标准算法**解密**后的结果 decrypt(破译)
3. ENCODE(str, key);//使用key作为密钥**加密**字符串str,Encode结果是一个二进制字符串，以BLOB类型存储。(加密程度相对比较弱);
4. DECODE(str, key);//使用key作为密钥**解密**加密字符串str;

ENCRYPT(str, salt);//使用UNIX crypt()函数，用关键词salt加密字符串str

1. MD5();//计算字符串str的MD5校验和
2. PASSWORD(str);//**PASSWORD()：**创建一个经过加密的密码字符串，适合于插入到MySQL的安全系统。**该加密过程不可逆，**和unix密码加密过程使用不同的算法。**主要用于MySQL的认证系统。**
3. SHA();//计算字符串str的安全散列算法(SHA)校验和

*实例一(AES_ENCRYPT/AES_DECRYPT)

MariaDB [study_db]> INSERT INTO book_classify
    -> (classify_id, classify_name, description) 
    -> VALUES
    -> (100, 'AES', AES_ENCRYPT(123, 'KEY'));
Query OK, 1 row affected (0.01 sec)



MariaDB [study_db]> SELECT classify_id, classify_name, description,
    -> AES_DECRYPT(description, 'KEY') 
    -> FROM book_classify 
    -> WHERE classify_id = 100;
+-------------+---------------+------------------+---------------------------------+
| classify_id | classify_name | description      | AES_DECRYPT(description, 'KEY') |
+-------------+---------------+------------------+---------------------------------+
|         100 | AES           | ?^ڻIi?6wvn??a      | 123                             |
+-------------+---------------+------------------+---------------------------------+
1 row in set (0.00 sec)

*实例二(ENCODE/DECODE)

MariaDB [study_db]> INSERT INTO book_classify
    ->      (classify_id, classify_name, description) 
    ->      VALUES
    ->      (111, 'AES', ENCODE(123, 'KEY'));
Query OK, 1 row affected (0.00 sec)



MariaDB [study_db]> SELECT classify_id, classify_name, description,
    -> DECODE(description, 'KEY') 
    -> FROM book_classify 
    -> WHERE classify_id = 111;
+-------------+---------------+-------------+----------------------------+
| classify_id | classify_name | description | DECODE(description, 'KEY') |
+-------------+---------------+-------------+----------------------------+
|         111 | AES           | W?          | 123                        |
+-------------+---------------+-------------+----------------------------+
1 row in set (0.00 sec)

三, MD5加密

MD5主要用途：

1、对一段信息生成信息摘要，该摘要对该信息具有唯一性,可以作为数字签名。
2、用于验证文件的有效性(是否有丢失或损坏的数据),
3、对用户密码的加密，
4、在哈希函数中计算散列值

MD5是没有解密算法的，经常用来作为对账号密码的验证。验证的一般流程为：

1. 注册账号密码时对密码进行MD5加密，账号明文存入数据库，密码存入的是经过MD5加密的密文。
2. 登录时先根据账号，在数据库进行检测。
3. 检测到账号后对本次登录输入的密码进行MD5加密，拿本次密文与数据库里的密文进行验证。通过后即可成功登陆。

四. ENCRYPT 加密

本部分摘录自为什么要在密码里加点“盐”

**ENCRYPT(str, salt); **使用UNIX crypt()函数，用关键词salt加密字符串str

Salt可以是任意字母、数字、或是字母或数字的组合，但必须是随机产生的，每个用户的 Salt 都不一样，用户注册的时候，数据库中存入的不是明文密码，也不是简单的对明文密码进行散列，而是 MD5( 明文密码 + Salt)，也就是说：

1. MD5('123' + '1ck12b13k1jmjxrg1h0129h2lj') = '6c22ef52be70e11b6f3bcf0f672c96ce'
2. MD5('456' + '1h029kh2lj11jmjxrg13k1c12b') = '7128f587d88d6686974d6ef57c193628'

当用户登陆的时候，同样用这种算法就行验证。

由于加了 Salt，即便数据库泄露了，但是由于密码都是加了 Salt 之后的散列，坏人们的数据字典已经无法直接匹配，明文密码被破解出来的概率也大大降低。

是不是加了 Salt 之后就绝对安全了呢？淡然没有！坏人们还是可以他们数据字典中的密码，加上我们泄露数据库中的 Salt，然后散列，然后再匹配。但是由于我们的 Salt 是随机产生的，假如我们的用户数据表中有 30w 条数据，数据字典中有 600w 条数据，坏人们如果想要完全覆盖的坏，他们加上 Salt 后再散列的数据字典数据量就应该是 300000* 6000000 = 1800000000000，一万八千亿啊，干坏事的成本太高了吧。但是如果只是想破解某个用户的密码的话，只需为这 600w 条数据加上 Salt，然后散列匹配。可见 Salt 虽然大大提高了安全系数，但也并非绝对安全。

实际项目中，Salt 不一定要加在最前面或最后面，也可以插在中间嘛，也可以分开插入，也可以倒序，程序设计时可以灵活调整，都可以使破解的难度指数级增长。

参考
MySql常用函数大全讲解
 MySQL：日期函数、时间函数总结（MySQL 5.X）
MySQL 日期与时间方面的函数
 mysql加密解密函数
 加密函数
 MD5加密简述
 为什么要在密码里加点“盐”

更多精彩内容请关注“IT实战联盟”哦~~~

IT实战联盟.jpg

MairaDB 函数(九)

第一部分数学函数

第二部分字符串函数

第三部分日期和时间函数

第四部分条件判断函数

第五部分. 系统信息函数

第六部分. 加密函数

三, MD5加密

四. ENCRYPT 加密

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

MairaDB 函数(九)

第一部分 数学函数

第二部分 字符串函数

第三部分 日期和时间函数

第四部分 条件判断函数

第五部分. 系统信息函数

第六部分. 加密函数

三, MD5加密

四. ENCRYPT 加密

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

第一部分数学函数

第二部分字符串函数

第三部分日期和时间函数

第四部分条件判断函数