六、运算符和函数

6-1回顾和概述

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

 

6-2字符函数

1.CONCAT() ,字符连接

SELECT CONCAT('a','-','b'); 结果为:a-b

2.CONCAT_WS(), 使用指定的分隔符进行字符连接

 SELECT CONCAT_WS('|','A','B','C'); 结果为： A|B|C

3.FORMAT() 数字格式化

SELECT FORMAT(12560.7,2); 结果：12，560.70

SELECT FORMAT(12560.78,1); 结果：12，560.8

4.LOWER() 转换成小写字母

5.UPPER() 转换成大写字母

6.LEFT() 获取左侧字符

SELECT LEFT('mysql',2); 结果：my

7.RIGHT() 获取右侧字符

8.LTRIM() 删除前导空格(=LEFT TRIM())

9.RTRIM() 删除后续空格

10.TRIM（）删除前后两边的空格，还可以删除指定的前导和后续的字符,不能删除中间的字符

SELECT TRIM(LEADING'?','??MYSQL????'); 结果：MYSQL????

SELECT TRIM(TRAILING'?','??MYSQL????'); 结果：??MYSQL

SELECT TRIM(BOTH'?','??MYSQL???'); 结果：MYSQL

11.REPLACE() 替换字符 SELECT REPLACE('??MYSQL???','?','-'); 结果：--MYSQL---

12. SUBSTRING(string，offset，length) 截取字符串

SELECT SUBSTRING('MYSQL',2,3); 结果：SQL

13.[NOT]LIKE 模糊匹配 (%)：代表任意个字符，0个或多个 (_)：代表任意一个字符，只有一个

SELECT name FROM test WHERE name LIKE'%O%'; 结果：输入name 中带‘O’的name

SELECT name FROM test WHERE name LIKE'%1%%' ESCAPE'1'; 找到中间带% 的匹配name

 

6-3数值运算符和函数

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；

6、ROUND(数值,小数的位数)

7、TRUNCATE(数值，截取位数); 说明：和ROUND()相似，只是不四舍五入，截取位数还能是负数

SELECT TRUNCATE(125.68,-1); 结果为120；

 

6-4比较运算符和函数

SELECT 15 BETWEEND 1 AND 20 ==> 1 /*15 在1到20之间 ,返回值是1
SELECT 15 NOT BETWEEND 1 AND 20 ==>0 /* 15在1到20之间,条件不成立 返回值是0
SELECT 10 IN(5,10,15) ==> 1 /*值是否在给定的数值中,如果在返回1,不在返回0 条件成立为1不成立为0
SELECT 10 NOT(5,10,15) ==> 0 /*数值是否不在给定的数值中,如果不在返回1,在返回0 条件成立为1不成立为0
SELECT NULL IS NULL ==>1 /* 空是空 条件成立返回1,不成立返回0
SELECT '' IS NULL ==> 0 /* 除了NULL空其它都是非空 返回都是1
SELECT * FROM ABC WHERE ID IS NOT NULL;

 

6-5日期时间函数

• NOW() /*当前时间 含日期时间
• CURDATE() /* 当前日期 只有日期
•  CURTIME() /*当前时间 值有时间
• DATE_ADD() /*时间增减或减少

 SELECT DATE_ADD('2014-3-12',INTERVAL 365 DAY); ==>2015-3-12 /*在原有给定的时间上增加365天

INTERVAL增加可以增加负值 单位 year ,month,week,day

SELECT DATEDIFF('2014-1-1','2015-1-1') ==> -365 /*时间差值计算 单位为日 前面时间减去后面时间

SELECT DATE_FORMAT('2014-3-2','%m/%d/%d'); ==> 03/02/2014 /*日期格式转换

 

6-6 信息函数

• CONNECTION_ID(); // 连接ID mysql> SELECT CONNECTION_ID();
• SELECT DATABASE(); // 当前数据库
• LAST_INSERT_ID(); // 最后句插入记录的 ID 号，如果是一次insert中插入的是多条记录，得到的是多条中的第一条（而不是最后一条！）
• VERSION(); // 版本的信息
• USER(); // 当前用户 SELECT USER();

 

6-7聚合函数

• 聚合函数只有一个返回值

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() - 最小值 SUM() - 求和

SELECT SUM(goods_price) as counts FROM tdb_goods;

 

6-8加密函数

1.MD5()：信息摘要算法，为以后的Web页面做准备，尽量使用MD5() 举例 SELECT MD5('admin');

2.PASSWORD()：密码算法，通过PASSWORD()修改(重要用于MYSQL数据库)当前用户和其他用户的密码，修改客户端自己的密码

举例 SET PASSWORD=PASSWORD(‘dimitar’); 把密码修改成dimitar。

 

小结

这里只是简单的介绍，具体的使用还会更加复杂