MySQL学习笔记5—统计和函数

MySQL学习笔记

本系列文章将结合书本、网络教程和实操层面，总结以新手角度学习MySQL的须知须会。

9. 数据统计

9.1 数据汇总

9.1.1 聚集函数

• 定义：运行在行组上，计算和返回单个值的函数
• 聚集函数

函数	说明	用例
AGV()	返回列平均值	SELECT AVG(column) FROM table name
COUNT()	返回行数目	SELECT COUNT(*),COUNT(column)对行和非NULL行计数
MAX()	返回列最大值	SELECT MAX(column) AS max，忽略NULL
MIN()	返回列最小值	SELECT MIN(column)，忽略NULL
SUM()	返回列总和	SELECT SUM(column),SELECT SUM(column1+column2)

9.1.2 聚集不同值

• ALL参数：对所有行计算，为默认行为

• DISTINCT参数：在参数列表中的列名前加入DISTINCT，只将不同值纳入计算。

9.1.3 组合聚集函数

• 多个函数可在同一语句中使用，用,区隔，如：

  SELECT COUNT(*) AS num, AVG(column1) AS avg FROM tablename;

9.2 数据分组

9.2.1 创建分组

• 关键字：GROUP BY

• 语句用例：SELECT column1,COUNT(column2) FROM tablename GROUP BY column1;

  含义：分别计算不同列1值的列2总值。

• 位置：WHERE子句之后，ORDER BY子句之前。

• WITH ROLLUP：至于该子句末尾，

9.2.2 过滤分组

• 关键字：HAVING

• 使用：支持所有WHERE操作符

• 与WHERE：WHERE在数据分组前过滤，HAVING在数据分组后过滤

• 用例：SELECT id, COUNT(*) AS num FROM orderList where price >= 10 GROUP BY id HAVING COUNT(*)>=2;

  含义：检索订单列表中，价格大于等于10的商品ID中，销量大于2的不同产品的销量。

*子句顺序：SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT

10. 函数

10.1 创建计算字段

10.1.1 计算字段

• 含义：在SELECT语句内创建的从数据库检索出转换、计算或格式化过的数据。

10.1.2 拼接字段

• 函数：Concat() ，可以将将列和常量拼接，组成句子等。

• 举例：SELECT Concat(name, 'is from', country, '.') AS introduction FROM info_table ORDER BY name

  含义：将表中姓名和国籍组成一句介绍话。

10.1.3 算术计算

• 使用：在SELECT语句中使用算术操作符(+,-,*,/)得到计算字段

• 举例 SELECT id, quantity*price AS total from salestable ORDER BY total;

  含义：计算不同商品的销售额，并根据销售额排序。

10.2 数据处理函数

10.2.1 文本处理函数

函数	说明	函数	说明
LEFT(str,len)	串左边len个字符	RIGHT(str,len)	串右边len个字符
LENGTH(str)	返回长度	INSTR(str,sub)	返回子串位置
LOCATE(sub,str)	返回子串位置	REPLACE(str,str1,str2)	str中str1改为str2
LOWER(str)	转换小写	UPPER(str)	转换大写
LTRIM(str)	去掉左边空格	RTRIM(str)	去掉右边空格
SOUNDEX()	返回soundex值	STRCMP(str1,str2)	逐字符比较大小
SUBSTRING(str,pos[,len])	从pos开始len个/所有字符	CHARSET(str)	返回字符集
TRIM()	去掉两端空格	CONCAT(str1,str2,…)	拼接

* str是列名或字符串

10.2.2 日期和时间处理函数

• 直接型

函数	说明	函数	说明
CURTIME(), CURRENT_TIME()	返回时间	MONTH(datetime)	返回月
CURDATE(), CURRENT_DATE()	返回日期	DAY(datetime)	返回日
CURRENT_TIMESTAMP()	返回时间戳	DAYOFWEEK(datetime)	返回周几
DATE(datetime)	返回时日的日期	HOUR(datetime)	返回时
TIME(datetime)	返回时日的时间	MINUTE(datetime)	返回分
YEAR(datetime)	返回年	SECOND(datetime)	返回秒
NOW()	返回当前时日

• 计算型

函数	说明
ADDDATE/DATE_ADD(date, INTERVAL d SMHDMY)	增加d秒分等
ADDTIME(date,‘0:0:0’)	增加时间
DATE_SUB(date, INTERVAL d SMHDMY)	减去d秒分等
DATEDIFF(date1,date2)	计算日期差
TIMEDIFF(time1,time2)	计算时间差
UNIX_TIMESTAMP()	返回1970秒数
FROM_UNIXTIME(s,’%Y-%m-%d %H:%i:%s’)	返回1970过了s秒的时间
DATE_FORMAT(date,‘format’)	返回格式化的时间

10.2.3 数值处理函数

函数	说明	函数	说明
ABS(num)	绝对值	CONV(num,s1,s2)	进制转换
BIN(num)	十转二	FORMAT(num,d)	保留d位小数
CEILING(num)	向上取整	FLOOR(num)	向下取整
HEX(num)	转十六进制	LEAST(n1,n2,…)	求最小
MOD(n1,n2)	求余	RAND([seed])	返回[0,1]随机数

10.3 加密函数

函数	说明
USER()	查询用户
DATABASE()	查询数据库名称
MD5(str)	MD5加密
PASSWORD(str)	加密

10.4 流程控制函数

函数	说明
IF(ex1,ex2,ex3)	如果1真，返回2，否则返回3
IFNULL(ex1,ex2)	如果1空返回2，否则返回1
SELECT CASE WHEN ex1 THEN ex2 WHEN ex3 THEN ex4… ELSE ex5 END	1真返回2，3真返回4，否则5