Mysql查询与统计

单表查询

例如：查询某个表中的某些数据

select * from 表名;
select 字段1,字段2 from 表名;

多表查询：join on

例如：查询多个表中的数据，例如：表1写的是商品信息：商品ID，名字，表2写的是价格信息：商品ID，价格，多表查询看商品ID，商品名称，价格

select * from 表1 join 表2 on 表1的字段a=表2的字段b;

数据筛选：where

例如：查询价格是大于10元的汉堡

  查询条件：

• 空值：IS NULL , IS NOT NULL ,ISNULL()
• 模糊匹配：LIKE ，NOT LIKE 通配符 % ,_
• 确定集合：IN ，NOT IN
• 确定范围：
  • BETWEEN AND(相当于闭集合[BETWEEN,AND]) ,
  • NOT BETWEEN AND(不属于闭集合[BETWEEN,AND]的范围)
• 比较：= , > , < , >= , <= , （<> , !=不等于）

多个条件：

        AND 和、OR   或

select * from 表1 join 表2 on 表1的字段a=表2的字段b
where 字段1>10;

数据分组+筛选：group by...having

例如：查询今天购买不同商品的人有多少个。按照商品分类，按照日期限定

GROUP BY 字段1,字段2,字段3....HAVING 字段1>10

select 字段1, SUM(population), SUM(area)
from 表名
group by 字段1
having SUM(字段1)>10000

备注：

1. where 是先筛选后连接，having是先连接后筛选；一个查询中可以同时使用，where效率会高点
2.  select 后的字段，必须通过group by分组。例如：不能select 字段2...group by 字段1，having不限制

数据排序：order by

例如：按照货品销量从大到小排序显示

        ASC：正序，默认为正序

        DESC：倒序

时间类型的函数

计算同比，环比，去年现在到今年的数据变化（和时间相关的查询）

获取：

• SECOND（date）：获取 date 中的秒
• MINUTE（date）：获取 date 中的分
• HOUR（date）：获取 date 中的小时；EXTRACT(HOUR FROM 日期字段时分秒)
• DAY（date）：获取 date 中的日
• MONTH（date）：获取 date 中的月
• YEAR（date）：获取 date 中的年

计算

• DATE_ADD（date, INTERVAL 表达式 type）：表示计算从时间点“date”开始，向前或者向后一段时间间隔的时间。“表达式”的值为时间间隔数，正数表示向后，负数表示向前，“type”表示时间间隔的单位（比如年、月、日等）。
• LAST_DAY（date）：表示获取日期时间“date”所在月份的最后一天的日期
• DATEDIFF（date1,date2），表示日期“date1”与日期“date2”之间差几天。计算哪天->哪天的平均值

其他

• CURDATE（）获取当前日期
• DAYOFWEEK（date）：获取日期“date”是周几。1 表示周日，2 表示周一，以此类推，直到 7 表示周六

案例：计算去年这时候到今年这时候的数据，去年当前时间后一天为开始时间。假设现在时：2020-12-10

SELECT DATE_ADD('2020-12-10', INTERVAL - 1 YEAR);---2019-12-10
SELECT DATE_ADD(DATE_ADD('2020-12-10', INTERVAL - 1 YEAR),INTERVAL - 1 MONTH);---2019-11-10
SELECT LAST_DAY(DATE_ADD(DATE_ADD('2020-12-10', INTERVAL - 1 YEAR),INTERVAL - 1 MONTH));---2019-11-10
SELECT DATE_ADD(LAST_DAY(DATE_ADD(DATE_ADD('2020-12-10', INTERVAL - 1 YEAR),INTERVAL - 1 MONTH)),INTERVAL 1 DAY);----2019-12-01

条件判断

根据场景显示数据：例如：当查到的次品率<a时，显示为合格，>=a时显示为不合格

CASE 表达式 WHEN 值1 THEN 表达式1 [ WHEN 值2 THEN 表达式2] ELSE 表达式m END

CASE DAYOFWEEK(CURDATE()) - 1 WHEN 0 THEN 7 ELSE DAYOFWEEK(CURDATE()) - 1 END AS 周几

条件判断

• IFNULL（V1，V2）：表示如果 V1 的值为不空值，则返回 V2，否则返回 V1
• IF（表达式，V1，V2）：如果表达式为真（TRUE），则返回 V1，否则返回 V2

数学函数

例如：花费172.5元积分为172

• 向上取整 CEIL(X) 和 CEILING(X)：返回大于等于 X 的最小 INT 型整数--        -172.22=>173
• 向下取整 FLOOR(X)：返回小于等于 X 的最大 INT 型整数.---172.22=>172
• 舍入函数 ROUND(X,D)：X 表示要处理的数，D 表示保留的小数位数，处理的方式是四舍五入。ROUND(X) 表示保留 0 位小数

字符串函数

• CONCAT（s1,s2,...）：表示把字符串 s1、s2……拼接起来，组成一个字符串
• CAST（表达式 AS CHAR）：表示将表达式的值转换成字符串
• CHAR_LENGTH（字符串）：表示获取字符串的长度
• SPACE（n）：表示获取一个由 n 个空格组成的字符串 ---一般是补齐字段
• SUBSTR（s,n）获取字符串s从第n个位置到第s结尾的子字符串
• MID（s,n,len）获取字符串s从第n个位置开始长度为len的字符串
• LTRIM（s）除掉左边的空格
• RTRIM（s）除掉右边的空格

聚合函数

求和函数SUM(字段名称)：例如：最近一周某个商品的销量之和

SELECT SUM(quantity), SUM(salesvalue) FROM  demo.transactiondetails;

求平均函数AVG() 例如：平均每天的销量、收益为多少

SELECT AVG(quantity) FROM  demo.transactiondetails;

最大值MAX()、最小函数MIN() 例如：当天销售量最大的产品

备注：查询中既求最大、也求最小时分别查询，结果为2条数据

SELECT MAX(quantity),MIN(salesvalue) FROM  demo.transactiondetails;

计数函数COUNT()：例如：当天共有多少个订单

COUNT（*）：统计一共有多少条记录； COUNT（字段）：统计有多少个不为空的字段值

SELECT COUNT(*)  FROM demo.goodsmaster;

SELECT COUNT(specification)  FROM demo.goodsmaster;

进阶

        CTE递归查询

场景

不同条件查询同一个表后，数据进行计算

select T1.o1 AS '2014年的2月之后装车重量',T2.o2 as '所有运单的装车重量之和',o1/o2 as '比率'
from
(select COUNT(loading_weight) o1 from order_info as o  where loading_time >'2024-02-01'
)as T1,
(select COUNT(loading_weight) o2 from order_info as o  
)as T2

跨库查询

select * from db1.table1 join db2.table2 on db1.table1.id = db2.table2.id

备注

1.Round()函数:

   对于精确的数值（DECIMAL）， ROUND 函数使用四舍五入

  对于近似值，则依赖于底层的C函数库，在很多系统中 ROUND 函数会使用“取最近的偶数”的规则

例如：mysql字段为double类型，Round(3.345,2)=3.34 、Round(3.346,2)=3.35

          mysql字段为decimal类型，Round(3.345,2)=3.35