1、MySQL-----运算符和函数-----字符函数，数值运算符，比较运算等----字符函数---ConCAT()--字符连接CONCAT_WS()--使用指定的分隔符进行字符连接mysql> SELECT ConCAT('imooc','-','MySQL');+-----------------------------+| ConCAT('imooc','-','MySQL') |+-----------------------------+| imooc-MySQL |+-----------------------------+mysql> USE TEST;Database changedmysql> SELECT * FROM TEST;+----+----------+| id | username |+----+----------+| 1 | Tom || 2 | Ben |+----+----------+2 rows in set (0.02 sec)mysql> SELECT ConCAT(id,username) AS fullname FROM test;+----------+| fullname |+----------+| 1Tom || 2Ben |+----------+以上是ConCAT()的实例，而CONCAT_WS()如下mysql> SELECT CONCAT_WS('-',id,username) FROM test;+----------------------------+| CONCAT_WS('-',id,username) |+----------------------------+| 1-Tom || 2-Ben |+----------------------------+CONCAT_WS()需要至少三个参数，第一个是分隔符，后面才是需要连接的东西---FORMAT()，数字格式化mysql> SELECT FORMAT(234234.23423,2);+------------------------+| FORMAT(234234.23423,2) |+------------------------+| 234,234.23 |+------------------------+就是标准化，当然是外国人那一套---LOWER()&UPPER()，大小写变换---LEFT()&RIGHT()，获取左右侧字符需要两位参数,从哪个字段取，从第几位开始的左右侧mysql> SELECT UPPER(LEFT('andy',1));+-----------------------+| UPPER(LEFT('andy',1)) |+-----------------------+| A |+-----------------------+---LENGTH()，获取字符串长度。注意，空格也要算在内---LTRIM(),RTRIM(),TRIM()，删除前/后/前后的空格TRIM还能删除其他字符mysql> SELECT TRIM(LEADING '!' FROM '!!!ANDY!!');+------------------------------------+| TRIM(LEADING '!' FROM '!!!ANDY!!') |+------------------------------------+| ANDY!! |+------------------------------------+1 row in set (0.00 sec)mysql> SELECT TRIM(BOTH '!' FROM '!!!ANDY!!');+---------------------------------+| TRIM(BOTH '!' FROM '!!!ANDY!!') |+---------------------------------+| ANDY |+---------------------------------+1 row in set (0.00 sec)mysql> SELECT TRIM(TRAILING '!' FROM '!!!ANDY!!');+-------------------------------------+| TRIM(TRAILING '!' FROM '!!!ANDY!!') |+-------------------------------------+| !!!ANDY |+-------------------------------------+1 row in set (0.00 sec)---REPLACE()mysql> SELECT REPLACE('!!ANDY!SDL!!','!','');+--------------------------------+| REPLACE('!!ANDY!SDL!!','!','') |+--------------------------------+| ANDYSDL |+--------------------------------+1 row in set (0.00 sec)mysql> SELECT REPLACE('!!ANDY!SDL!!','!','LALAL');+-------------------------------------+| REPLACE('!!ANDY!SDL!!','!','LALAL') |+-------------------------------------+| LALALLALALANDYLALALSDLLALALLALAL |+-------------------------------------+1 row in set (0.00 sec)可以看到，替换是比较灵活的---SUBSTRING()mysql> SELECT SUBSTRING('MYSQL','1','2');+----------------------------+| SUBSTRING('MYSQL','1','2') |+----------------------------+| MY |+----------------------------+1 row in set (0.00 sec)注意mysql是从1开始，不是从0开始数数---做匹配mysql> SELECT 'mysql' LIKE 'M%';+-------------------+| 'mysql' LIKE 'M%' |+-------------------+| 1 |+-------------------+1 row in set (0.00 sec)mysql> SELECT * FROM test WHERE username LIKE '%m%';+----+----------+| id | username |+----+----------+| 1 | Tom |+----+----------+这里，%代表任意，类似window中的*_代表任意一个字符----数值运算符以及函数几个很通用的函数简单的介绍下CEIL()-向上取整---------FLOOR()-向下取整DIV()-整数除法，也就是结果只有整数MOD()-取余数POWER()-幂运算ROUND()-四舍五入TRUNCATE()-数字截取（不再四舍五入）不是两位参数，就是一位参数，大家自己试试哈----比较运算符与函数---[NOT] BETWEEN...AND...mysql> SELECT 123 BETWEEN 2 AND 123123;+--------------------------+| 123 BETWEEN 2 AND 123123 |+--------------------------+| 1 |+--------------------------+---[NOT] IN()，给定几个区间来做判断mysql> SELECT 123 IN(123,23,12);+-------------------+| 123 IN(123,23,12) |+-------------------+| 1 |+-------------------+1 row in set (0.00 sec)mysql> SELECT 123 IN(120,23,12);+-------------------+| 123 IN(120,23,12) |+-------------------+| 0 |+-------------------+---IS [NOT] NULL,判断是空么mysql> SELECT 0 IS NULL;+-----------+| 0 IS NULL |+-----------+| 0 |+-----------+常用在检查是否为空mysql> SELECT * FROM test WHERE username IS NULL;Empty set (0.00 sec)----日期时间函数---NOW()，返回当时的日期和时间CURDATE(),CURTIME()---DATE_ADD()，当前日期的变化mysql> SELECT DATE_ADD('2012-2-23', INTERVAL 234 DAY);+-----------------------------------------+| DATE_ADD('2012-2-23', INTERVAL 234 DAY) |+-----------------------------------------+| 2012-10-14 |+-----------------------------------------+1 row in set (0.00 sec)mysql> SELECT DATE_ADD('2012-2-23', INTERVAL -234 DAY);+------------------------------------------+| DATE_ADD('2012-2-23', INTERVAL -234 DAY) |+------------------------------------------+| 2011-07-04 |+------------------------------------------+1 row in set (0.00 sec)---DATEDIFF()，得到两个日期间的差值mysql> SELECT DATEDIFF('2304-2-2','1234-3-22');+----------------------------------+| DATEDIFF('2304-2-2','1234-3-22') |+----------------------------------+| 390760 |+----------------------------------+1 row in set (0.02 sec)---DATE_FORMAT()，日期的格式化，日期的格式是可以选的，也就是说日期的格式转换mysql> SELECT DATE_FORMAT('2013-2-22','%m/%d/%y');+-------------------------------------+| DATE_FORMAT('2013-2-22','%m/%d/%y') |+-------------------------------------+| 02/22/13 |+-------------------------------------+1 row in set (0.00 sec)----信息函数USER(),VERSION(),DATEbase(),CONNECTION_ID(),LAST_INSERT_ID()----聚合函数只有一个返回值是他们的特点AVG(),COUNT(),MAX(),MIN(),SUM()直接调用可能会有错误，一般是针对数据表的字段进行的操作mysql> SELECT AVG(id) FROM test;+---------+| AVG(id) |+---------+| 1.5000 |+---------+----加密函数MD5(),PASSWORD()mysql> SELECT MD5('HELLOWORLD');+----------------------------------+| MD5('HELLOWORLD') |+----------------------------------+| e81e26d88d62aba9ab55b632f25f117d |+----------------------------------+1 row in set (0.00 sec)mysql> SELECT PASSWORD('HELLOWORLD');+-------------------------------------------+| PASSWORD('HELLOWORLD') |+-------------------------------------------+| *3456E7782A7F539BC823C715DB60231B0C7DE847 |+-------------------------------------------+1 row in set (0.00 sec)一般而言，网页的编程用的都是MD5，password仅仅用于修改当前用户的密码----注重自带函数的熟悉、了解，灵活的调用和嵌套运用