(6)Mysql运算符和函数

回顾上节内容：

• 子查询:出现在sql语句的select子句，而子查询必须要出现在小括号()内,它的外层可以是select语句，insert语句，update语句，delete语句，而且在子查询中可以包含多个关键词:group by,order by,limit级相关函数。引发子查询的情况有比较运算符，in，(Not)in运算符，exist，(Not）exists引发的子查询。
• 连接:连接分为外连接和内连接，内连接主要显示的是左表和右表当中都符合连接条件的记录。外连接有分为左外连接和右外连接，左外连接显示的是左表中的全部记录和右表符合条件的记录，右外连接和左外连接相反，mysql中的select，多表更新和删除都可以用到内连接，左，右外连接。
• create语句中的select ，insert select 语句。

Mysql运算符根据功能可以划分为：
1.字符函数
2.数值运算符与函数
3.比较运算符与函数
4.日期时间函数
5.信息函数
6.聚合函数
7.加密函数

1.Mysql字符函数

函数名称 描述 CONCAT() 字符连接 CONCAT_WS() 使用指定的分隔符进行字符连接 FORMAT() 数字格式化 LOWER() 转换成小写字母 UPPER() 转换成大写字母 LEFT() 获取左侧字符 RIGHT() 获取右侧字符 LENGTH() 获取字符串长度 LTRIM() 删除前导空格 RTRIM() 删除后续空格 TRIM() 删除前导和后续空格 SUBSTRING() 字符串截取 [NOT] LIKE 模式匹配 REPLACE() 字符串替换

例子：SELECT CONCAT('A','B','C');连接两个或多个字符。
有一张表存储两个字段 first_name和last_name，需要将这两个字段的信息合并在一起输出SELECT concat（ first_name，last_name）as fullname from test;
SELECT CONCAT_WS('|','A','B','C');使用指定的分隔符（示例中使用|）连接两个或多个字符。
SELECT FORMAT(123456.789,2);保留两位小数（四舍五入），因为它的返回结果是字符型，所以划分在字符函数里。
SELECT LOWER('MySQL');转为小写。
SELECT UPPER('MySQL');转为大写。
SELECT LEFT('MySQL',2);获取左边前两位字符，嵌套使用select lower（SELECT LEFT('MySQL',2)）获取的就是小写my了。
SELECT RIGHT('MySQL',3);获取右边三位字符。
SELECT LENGTH('My SQL');获取字符串长度（包括空格）。
SELECT LTRIM(' My SQL ');删除前导空格，所谓前导空格，指的是第一个字符之前的空格。
SELECT RTRIM(' My SQL ');删除后续空格，最后一个字符之后的空格。
SELECT TRIM(' My SQL ');删除前导和后续空格，除此以外还能够删除某些特定的字符串。
SELECT TRIM(LEADING '?' FROM '??MySQL???');删除这个字符串’??MySQL???’前导的？问号。
SELECT TRIM(TRAILING '?' FROM '??MySQL???');删除后面的？问号。
SELECT TRIM(BOTH'?' FROM '??MySQL???');删除前面和后面的？问号，都删掉。
此时会有一个问题中间的该如何删除呢？此时就用到了字符串替换。SELECT REPLACE('??My??SQL???','?','');把？替换为空字符串，即可删除中间的？。
SELECT SUBSTRING('mysql',1,2);从第一位截取 截取2个字符。注意编号从1开始，程序可能是0。不写截取几个时，会一直到结尾。在起始位置上写负值，如-1，就是倒数第一个，后面的长度不能为负，不写长度一样会读到结尾。
SELECT * FROM test WHERE first_name LIKE '%o%';查找字段first_name 中包含’o’的记录。
SELECT * FROM test WHERE first_name LIKE '%1%%' ESCAPE '1';查找字段first_name 中包含%的记录，这里直接写'%%%'是失效的，mysql会认为都是通配符，加上'%1%%' ESCAPE '1'，代表1后面的这个就是百分号，不再是通配符。
通配符：%代表任意个（包括0个）任意字符，_下划线代表任意一个字符。

2.Mysql数值运算符与函数

名称 描述 ceil() 进一取整 div 整数除法 floor() 舍一取整 mod 取余数（取模运算） power() 幂运算 round() 四舍五入 truncate() 数字截取

1、CEIL(数值); 说明：就是向上取整，如：SELECT CEIL(3.01);结果是4；
2、FLOOR(数值); 说明：就是向下取整，如：SELECT FLOOR(3.99);结果是3；
3、DIV,这是运算符，整数除法，例子：SELECT 3 DIV 4;结果是0；因为3除以4，整数位为0；
4、MOD,这也是一个运算符，相当于C语言的%取余函数运算符，也可以用%号代替；例子：SELECT 4 MOD 3; 结果为1；浮点数也可以，SELECT 5.3 MOD 3; 结果为2.3；
5、POWER(数值,数值);例子：SELECT POWER(3,3); 结果为27；3的3次方。
6、ROUND(数值,小数的位数) 例子：SELECT ROUND(3.652，1); 3.7
7、TRUNCATE(数值，截取位数); 说明：和ROUND()相似，但不是四舍五入，仅仅是把数抹掉了，截取位数还能是负数，如：SELECT TRUNCATE(125.68,-1); 结果为120；

3.Mysql比较运算符与函数

名称 描述 [not] between…and… [不]在范围之内 [not] in() [不]在列出值范围内 is [not] null [不]为空

例子：
[NOT] BETWEEN...AND... [不]在范围内，给的是一个闭合的区间。
SELECT 15 BETWEEN 1 AND 22; 15是否在1-22的范围内，1为true，0为false
[NOT] IN() [不]在列出值的范围内
SELECT 10 IN (1，5，10，20); 10是否在列出值的范围内，1为true，0为false
IS [NOT] NULL [不]为空
SELECT NULL IS NULL;返回值 1，为空。SELECT '' IS NULL;返回值 0 ，不为NULL
SELECT * FROM test WHERE first_name IS NULL;查询first_name为空的数据

4.Mysql日期时间函数

这是比较常用的函数

名称 描述 NOW() 当前日期和时间 CURDATE() 当前日期 CURTIME() 当前时间 DATE_ADD(‘基准日期’ interval xxx 单位) 日期变化 DATEDIFF() 日期差值 DATE_FORMAT() 日期格式化

NOW()，打印和返回当前日期和时间。
例子 SELECT NOW();
CURDATE()，当前日期
例子 SELECT CURDATE();
CURTIME()，当前时间
例子SELECT CURTIME();
DATE_ADD(‘基准日期’ interval xxx 单位)，日期变化，不仅可以完成增加也可以完成减少
例子SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY);==>2015-3-12 在原有给定的时间上增加365天
INTERVAL增加可以增加负值，比如-365 单位 year ,month,week,day
DATEDIFF()，日期差值
例子 SELECT DATEDIFF('2014-1-1','2015-1-1') ==> -365时间差值计算 单位为日，左边的减去右边的。闰年什么的也会自动计算
DATE_FORMAT()，日期格式化
SELECT DATE_FORMAT('2014-3-2','%m/%d/%Y'); ==> 03/02/2014 /*日期格式转换

5.Mysql信息函数

名称 描述 CONNECTION_ID() 连接ID DATABASE() 当前数据库 LAST_INSERT_ID() 最后插入记录的ID号 USER() 当前用户 VERSION() 版本信息

CONNECTION_ID() 连接ID
例子：select CONNECTION_ID() 返回的值是4，多线程返回的值都不同。
DATABASE()，当前数据库
例子 select database()，返回当前数据库
LAST_INSERT_ID()，最后插入记录的ID号。 表中必须存在一个自动编号的字段，设置为主键自增，名字不一定叫ID。而且如果最后一条记录一次插入了多个数据，显示的是插入的多个数据的第一个数据的ID号。
例子： SELECT LAST_INSERT_ID() ;就可以得到目前新写入的最后的ID号，如果你同时写入两个数据，比如之前存在5个数据，此时得到的结果就为6
USER()，当前用户 SELECT user();
VERSION()，版本信息 select version();

6.Mysql 聚合函数

聚合函数有一个很典型的特点，他们只有一个返回值。

名称 描述 AVG() 平均值 COUNT() 计数 MAX() 最大值 MIN() 最小值 SUM() 求和

AVG() 平均值
例子：求一个平均价格 SELECT ROUND(AVG（goods_price）,2) AS avg_price from tdb_goods；
COUNT() 计数
例子 ：查看一共有多少条记录 SELECT COUNT(goods_id) AS counts from tdb_goods；
MAX() 最大值
例子： 最贵的价格 SELECT MAX(goods_price) AS counts from tdb_goods；
MIN() 最小值
例子： 最便宜 SELECT MIN(goods_price) AS counts from tdb_goods；
SUM() 求和
例子： 所有商品价格SELECT SUM(goods_price) AS counts from tdb_goods；

7.Mysql 加密函数

名称 描述 MD5() 信息摘要算法（为web应用做准备） PASSWORD() 密码算法

这里只给大家罗列了两个
1.MD5()：信息摘要算法，这里更严谨的并不能算作加密，而是摘要算法，
举例 SELECT MD5('admin'); 加密结果是一个32位的字符串
2.PASSWORD()：密码算法，通过PASSWORD()修改(用于MYSQL数据库)当前用户和其他用户的密码，修改客户端自己的密码
SET PASSWORD=PASSWORD(‘dimitar’); 把密码修改成dimitar。
举例 SELECT PASSWORD('admin');
建议如果说现在mysql当中的信息如果是为了以后的Web页面做准备，尽量使用MD5()。
而PASSWORD（）只有一个用途，我们在修改自己客户端密码的时候。