MYSQL常用函数以及分组操作

SELECT CONVERT("234",SIGNED);
SELECT CAST("123" AS SIGNED);
SELECT "124"+0;


SELECT LENGTH("姜浩真帅！");

SELECT ABS(-1)；

SELECT CONCAT("123","456","7890");

SELECT LEFT("abcdef")

SELECT REPLACE("姜浩真帅","真","特别")

SELECT CAST("123" AS SIGNED)

ABS(X) 返回X的绝对值
CEIL(X) 向上取整 1.5->2 -1.5->-1
FLOOR(X) 向下取整 1.5->1 -1.5->-2
ROUND(X) 四舍五入取整
ROUND(X,D) 将X保留D位小数，进行四舍五入
`TRUNCATE`(X,D) 将X保留D位小数，不进行四舍五入
RAND() 返回随机数
SIN(X) 返回x的符号，x是负数、0、正数分别返回-1、0、1 SELECT SIGN(-10)--(-1)
POW(X,Y) 返回x的y次幂
PI() 返回圆周率

[字符串函数]
LENGTH(str) 返回字符串的长度，其中一个中文占三个长度
CHAR_LENGTH(str) 返回字符串的字符数，其中一个中文只占一个字符
CONCAT(str1,str2,...) 拼接多个字符串
UPPER(str) 将字符串全部转为大写
LOWER(str) 将字符串全部转为小写
LEFT(str,n) 返回字符串的前N个字符
RIGHT(str,n) 返回字符串的后N个字符
TRIM(str) 去掉str左右两边的空格
REPLACE(str,s1,a2); 将字符串中所有的s1换成a2
SUBSTRING(str,pos[,len])截取字符串str 从pos个字符开始，截取len个，如果省略len，则截取到最后
INSTR(s1,s2) 返回s1在字符串中的下标

[字符串转数字格式]

[常用日期函数]
CURDATE()；返回当前日期 2017-06-20
CURTIME(); 返回当前时间 09:52:52
NOW(); 返回当前的日期和时间 2017-06-20 09:53:45
DATEDIFF(date1,date2) 返回date1和date2之间的间隔天数
ADDDATE(datetime1,days) 返回datetime1加上days天后的日期
MONTH() 返回日期中的月份
YEAR() 返回日期中的年份
DAY() 返回日期中一个月中的第几天
WEEKDAY() 返回一周中的第几天，0 -> 周一
HOUR() 返回小时数
MINUTE() 返回分钟数
SECOND() 返回秒数

DATE_FORMAT(date,format) 将date使用format要求的格式显示

format中可用的占位符格式：

%Y年份, 数字形式,4位数
%y年份, 数字形式 (2位数)

%M月份名称 (January..December)
%m月份, 数字形式 (00..12)
%b月份的缩写名称 (Jan..Dec)
%c月份，数字形式(0..12)

%D带有英语后缀的该月日期 (0th, 1st, 2nd, 3rd, ...)
%d该月日期, 数字形式 (00..31)
%e该月日期, 数字形式(0..31)

%H小时(00..23)
%h小时(01..12)
%k小时(0..23)
%l小时(1..12)

%i分钟,数字形式 (00..59)

%S秒 (00..59)
%s秒 (00..59)

%W工作日名称 (周日..周六)
%w一周中的每日 (0=周日..6=周六)

SELECT CURDATE();
SELECT CURTIME();
SELECT NOW();
SELECT DATEDIFF("2017-06-21","2017-06-23");
SELECT ADDDATE(NOW(),100);
SELECT MONTH()

【系统信息函数】
VERSION() 取到当前数据库的版本号
DATABASE() 返回当前数据库的名字
USER() 返回当前数据库的用户
CONNECTION_ID() 返回服务器的连接数
CHARSET(str) 返回字符串的字符集
LAST_INSERT_ID() 返回最后一个auto_increment的值

[加密函数]
1、PASSWORD() 对字符串进行加密，常用于加密密码
2、MD5():对字符串进行MD5散列加密，常用于不需要解密的数据
3、ENCODE(str,pwd) 使用加密密码pwd对字符串str进行加密，加密之后的结果为二进制，该字段需要使用BLOB类型保存
DECODE(mistr,pwd) 将ENCODE加密的二进制进行解密，解密密码需要与ENCODE的加密密码一致

SELECT PASSWORD("jianghao")

SELECT ENCODE("juianghao","pwd");
SELECT DECODE(ENCODE("juianghao","pwd"),"pwd");

SELECT DATE_FORMAT("2017-06-20 09:53:45","%Y")

UPDATE `Card` SET `PASSWORD` = REPLACE(REPLACE(`PASSWORD`,"O","0"),"i","1");

INSTR(ListNumber,"-");
LEFT(ListNumber,INSTR(ListNumber,"-")-1);
SUBSTRING(ListNumber,INSTR(ListNumber,"-")+1);

SELECT * FROM SellRecord ORDER BY LEFT(ListNumber,INSTR(ListNumber,"-")-1)+0 DESC,SUBSTRING(ListNumber,INSTR(ListNumber,"-")+1),DESC;

[多行函数]
SUM(expr) 求和 SELECT SUM(age) FROM tb1;
AVG()；求平均值 SELECT AVG(age) FROM tb1;
MIN(expr); 求最小值
MAX(expr)；求最大值
COUNT(1); 求总个数 SELECT COUNT(*) FROM tb1

USE mydb;
SELECT * FROM tb1;
SELECT COUNT(*) FROM tb1

SELECT SUM(age) FROM tb1;

SELECT AVG(age) FROM tb1 GROUP BY sex HAVING AVG(age)>1;

{分组操作}
GROUP BY ----FROM <表名>
WIERE
GROUP BY
HAVING
上述代码执行难执行流程
1线 SELECT.....FROM 查询所有数据
② 使用where 将数据源进行帅选
③ 对wher帅选后的数据，使用GTOUP BY进行分组
分组后，使用聚合函数，将会产生n条数据
④ HAVING字句对产生的多条聚合数据，进行处理操作
注意事项
① 代码顺序必须是 WHERE GROUP BY HAVING
② 有HAVING字句。必须有group BY，有group BY 并不一定有HAVING
③ WHERE和 HAVING 的区别

WHERE实在分组之前先筛选数据。HAVING是对分组后的数据，新型筛选，农行订单
HAVING语句中一定不能包含就、䄦函数，having 是专门用来过滤
-- 求出，平进均薪资大于1万，门对的平均薪资
-- 注各部门只统计出勤率大于15天的员工
SELECT AVG(薪资) FROM 公司表 WHERE 出勤率>15
GROUP BY 部门 HAVING AVG(薪资)>10000;

[表连接]

使用WHERE进行多表连接
与内连接一致
USE mydb;
SELECT i.cusname"客户名称",o.ordmoney"订单总额" -- 多表关联，字段前必须有表名
FROM cusorder AS o,cusinfo as i-- 为方便书写，给表起别名
WHERE o.cusno = i.cusno -- 两张表通过那个键关联

内连接
特点相关联的两张表，总是以数据少的为准，另一张表中没有被匹配的行，会被删除不显示
USE mydb;
SELECT i.cusname"客户名称",o.ordmoney"订单总额" -- 多表关联，字段前必须有表名
FROM cusorder AS o INNER JOIN cusinfo as i-- 为方便书写，给表起别名
ON o.cusno = i.cusno -- 两张表通过那个键关联

左链接 LEFT JOIN
特点以左边的表为准，
把左表的全部数据显示，右表多的删除不显示，右表少的补null
USE mydb;
SELECT i.cusname"客户名称",o.ordmoney"订单总额" -- 多表关联，字段前必须有表名
FROM cusorder AS o LEFT JOIN cusinfo as i-- 为方便书写，给表起别名
ON o.cusno = i.cusno -- 两张表通过那个键关联

右链接 right JOIN
特点以右边的表为准，
把右表的全部数据显示，左表多的删除不显示，左表少的补null
USE mydb;
SELECT i.cusname"客户名称",o.ordmoney"订单总额" -- 多表关联，字段前必须有表名
FROM cusorder AS o RIGHT JOIN cusinfo as i-- 为方便书写，给表起别名
ON o.cusno = i.cusno -- 两张表通过那个键关联

自连接同一张表与自己链接
USE mydb;
SELECT e1.ename,e2.ename
FROM emp e1 LEFT JOIN emp e2
ON e1.mgr = e2.empno;
[限制行数]
LIMIT n 表示的前N条数据，相当于LIMIT 0,n
LIMIT n,m 第n+1 到第m条

SELECT * FROM tb1 LIMIT 0,5;

@page = 1; -- 第几页
@rows = 5; -- 每页几条
分页语句

SELECT * FROM cusborder LIMIT @rows*(@page-1),@rows*@page;

各种限制语句的顺序

SELECT * FROM cusorder
WHERE NOT ISNULL(ordno)
GROUP BY ordno
HAVING SUM(ordmoney)>0
ORDER BY ordmoney DESC
LIMIT 0,10;