MySQL计算环比、同比(年、月、季度)

MySQL计算环比、同比(年、月、季度)

环比

所谓“环比”即连续2个单位周期(比如连续两月)内的量的变化比。如今年8月比今年7月。 当然这里的单位不一定是月,可以是任何时间单位。

环比增长率=(本期数-上期数)/上期数×100%。
反映本期比上期增长了多少;环比发展速度,一般是指报告期水平与前一时期水平之比,表明现象逐期的发展速度。

同比

所谓“同比”即同期比较,可以是本年度与上年度,也可以是上年同一个月份或季度与本年同期。
同比增长率=(本期数-同期数)/|同期数|×100%。

案例

年月统计不同年份的销售总值,并计算环比(销售总额同比上期)、同比(销售总额同比去年同期)
在这里插入图片描述

查询订单销售表结构

CREATE TABLE `food_orders_info` (
  `id` varchar(36) NOT NULL COMMENT '主键id',
  `pid` varchar(36) NOT NULL COMMENT '主订单id',
  `order_no` varchar(30) NOT NULL COMMENT '订单号生成规则 3大写英文-时间戳-3随机码(ZXC-201906241558-SX2)',
  `merchant_id` varchar(36) NOT NULL COMMENT '订单归属的经销商id',
  `buyer_id` varchar(36) NOT NULL COMMENT '买家id',
  `buyer_name` varchar(100) NOT NULL COMMENT '买家名称',
  `order_money` decimal(10,2) NOT NULL COMMENT '订单金额',
  `out_stock_money` decimal(10,2) DEFAULT NULL COMMENT '出库金额',
  `freight_money` decimal(10,2) NOT NULL COMMENT '运费',
  `driver_id` varchar(36) DEFAULT NULL COMMENT '司机id',
  `driver_name` varchar(36) DEFAULT NULL COMMENT '司机名称',
  `payment_state` bit(1) NOT NULL DEFAULT b'0' COMMENT '支付状态(0=未支付,1=已支付)',
  `payment_type` varchar(20) DEFAULT NULL COMMENT '支付方式(支付宝,微信,xx银行),预留字段暂不填写',
  `order_state` varchar(10) NOT NULL COMMENT '订单状态(00=预约单,10=待分拣,20=分拣中,30=待配送,40=配送中,50=待确认,60=已完成)',
  `abnormal_state` bit(1) NOT NULL DEFAULT b'1' COMMENT '异常状态,0=异常,1=正常',
  `order_resource` varchar(20) NOT NULL COMMENT '订单来源,暂时只有微信公众号',
  `receive_address` varchar(128) NOT NULL COMMENT '收货地址',
  `receive_man` varchar(100) NOT NULL COMMENT '收货人名称',
  `receive_tel` varchar(20) NOT NULL COMMENT '收货人联系电话',
  `address_longitude` varchar(36) DEFAULT NULL COMMENT '地址经度',
  `address_latitude` varchar(36) DEFAULT NULL,
  `delivery_date_start` timestamp NULL DEFAULT NULL COMMENT '配送开始时间',
  `delivery_date_end` timestamp NULL DEFAULT NULL COMMENT '配送时间结束时间',
  `receive_date_start` timestamp NULL DEFAULT NULL COMMENT '收货时间-开始时间',
  `receive_date_end` timestamp NULL DEFAULT NULL COMMENT '收货时间-截止时间',
  `receive_date_sure` timestamp NULL DEFAULT NULL COMMENT '确认收货时间',
  `if_end` bit(1) NOT NULL COMMENT '订单是否结束(0=未结束,1=结束)',
  `remark` varchar(512) DEFAULT NULL COMMENT '订单备注',
  `bill_id` varchar(36) DEFAULT NULL COMMENT '票据id,为空时表示该订单不开票',
  `sort` tinyint(4) NOT NULL DEFAULT '1' COMMENT '排序码',
  `state` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态(0=删除,1=正常)',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间(下单时间)',
  `create_user` varchar(36) NOT NULL COMMENT '创建人',
  `create_user_name` varchar(128) DEFAULT NULL,
  `last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  `last_modify_user` varchar(36) NOT NULL COMMENT '最后修改人',
  `last_modify_user_name` varchar(128) DEFAULT NULL,
  `order_time_type` varchar(1) NOT NULL DEFAULT '1' COMMENT '订单类型,0预约单/1正常单/2加急单',
  `reduction_money` decimal(10,2) DEFAULT '0.00' COMMENT '满减金额',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='订单表';

备注:需要统计的是每个月的销售金额,而销售金额=出库金额-运费,即
sale_money= out_stock_money-freight_money;
create_time字段为下单时间,这里可以理解为销售时间

计算环比
思路:表a统计出当前年月份的销售总额,表b统计出上一个月的销售总额(在表a的基础上加上一个月,可得到上个月的销售量),然后再将两个表进行左联接(通过now_time进行关联)。
在这里插入图片描述
#当前月份的销售额

select 
date_format(a.create_time,'%Y-%m') as now_time,
sum( a.out_stock_money) AS  a_out_money,
sum( a.freight_money) AS a_fre_money,
CONVERT (sum( a.out_stock_money)-sum( a.freight_money),DECIMAL ( 10,2 )) as a_sale_money
from food_orders_info a
where a.state=1
and a.order_state=60
GROUP BY date_format(a.create_time,'%Y-%m')

#上个月的销售额

select 
date_format(DATE_ADD(b.create_time,INTERVAL 1 MONTH ), '%Y-%m') 	as now_time,
sum( b.out_stock_money) AS bout_money,
sum( b.freight_money) AS bfe_money,
CONVERT (sum(b.out_stock_money)-sum( b.freight_money),DECIMAL ( 10,2 )) as bsale_money
from food_orders_info b
where b.state=1
and b.order_state=60
GROUP BY date_format(DATE_ADD(b.create_time,INTERVAL 1 MONTH ), '%Y-%m')

备注:

DATE_ADD(b.create_time,INTERVAL 1 MONTH )  #表示加一个月

#当前月和上月销售额进行关联

SELECT
	now_sale.now_time,
	now_sale.now_year,
CASE
		
		WHEN a_sale_money IS NULL 
		OR a_sale_money = 0 THEN
			0 ELSE a_sale_money 
		END this_sale_money,
CASE
		
		WHEN b_sale_money IS NULL 
		OR b_sale_money = 0 THEN
			0 ELSE b_sale_money 
		END last_month_money,

CASE
		
		WHEN b_sale_money IS NULL 
		OR b_sale_money = 0 THEN
			0 ELSE ( CONVERT ( ( ( a_sale_money - b_sale_money ) / b_sale_money ) * 100, DECIMAL ( 10, 2 ) ) ) 
		END month_ratio
FROM
	(
	SELECT
		date_format( a.create_time, '%Y-%m' ) AS now_time,
		date_format( a.create_time, '%Y' ) AS now_year,
		sum( a.out_stock_money ) AS a_out_money,
		sum( a.freight_money ) AS a_fre_money,
		CONVERT ( sum( a.out_stock_money ) - sum( a.freight_money ), DECIMAL ( 10, 2 ) ) AS a_sale_money 
	FROM
		food_orders_info a 
	WHERE
		a.state = 1 
		AND a.order_state = 60 
	
	GROUP BY
		date_format( a.create_time, '%Y-%m' ),
		date_format( a.create_time, '%Y' ) 
	ORDER BY
		date_format( a.create_time, '%Y-%m' ) ASC 
	) now_sale
	LEFT JOIN (
	SELECT
		date_format( DATE_ADD( b.create_time, INTERVAL 1 MONTH ), '%Y-%m' ) AS now_time,
		sum( b.out_stock_money ) AS bout_money,
		sum( b.freight_money ) AS bfe_money,
		CONVERT ( sum( b.out_stock_money ) - sum( b.freight_money ), DECIMAL ( 10, 2 ) ) AS b_sale_money 
	FROM
		food_orders_info b 
	WHERE
		b.state = 1 
		AND b.order_state = 60 
	GROUP BY
		date_format( DATE_ADD( b.create_time, INTERVAL 1 MONTH ), '%Y-%m' ) 
	ORDER BY
		date_format( DATE_ADD( b.create_time, INTERVAL 1 MONTH ), '%Y-%m' ) ASC 
	) old_sale ON now_sale.now_time = old_sale.now_time
ORDER BY
	now_sale.now_year DESC,
	now_sale.now_time ASC

得到的关联表
在这里插入图片描述

由于数据库销售金额有为空的情况,所以的加上判断条件,让为空或被除数为空的值,返回0。
再查询去年同期的销售金额

SELECT
		date_format( DATE_ADD( c.create_time, INTERVAL 1 YEAR ), '%Y-%m' ) AS last_year_time,
		sum( c.out_stock_money ) AS bout_money,
		sum( c.freight_money ) AS bfe_money,
		CONVERT ( sum( c.out_stock_money ) - sum( c.freight_money ), DECIMAL ( 10, 2 ) ) AS last_year_money 
	FROM
		food_orders_info c 
	WHERE
		c.state = 1 
		AND c.order_state = 60 
	GROUP BY
	date_format( DATE_ADD( c.create_time, INTERVAL 1 YEAR ), '%Y-%m' ) 

DATE_ADD( c.create_time, INTERVAL 1 YEAR ) 表示加一年。
得到的表如下:
在这里插入图片描述
最后,整合当前年月销售、上个月销售、去年同期销售。

SELECT
	now_sale.now_time,
	now_sale.now_year,
CASE
		
		WHEN a_sale_money IS NULL 
		OR a_sale_money = 0 THEN
			0 ELSE a_sale_money 
		END this_sale_money,
CASE
		
		WHEN b_sale_money IS NULL 
		OR b_sale_money = 0 THEN
			0 ELSE b_sale_money 
		END last_month_money,
CASE
		
		WHEN last_year_money IS NULL 
		OR last_year_money = 0 THEN
			0 ELSE last_year_money 
		END last_year_money,
CASE
		
		WHEN b_sale_money IS NULL 
		OR b_sale_money = 0 THEN
			0 ELSE ( CONVERT ( ( ( a_sale_money - b_sale_money ) / b_sale_money ) * 100, DECIMAL ( 10, 2 ) ) ) 
		END month_ratio,
CASE
		
		WHEN last_year_money IS NULL 
		OR last_year_money = 0 THEN
			0 ELSE ( CONVERT ( ( ( a_sale_money - last_year_money ) / last_year_money ) * 100, DECIMAL ( 10, 2 ) ) ) 
		END year_ratio 
FROM
	(
	SELECT
		date_format( a.create_time, '%Y-%m' ) AS now_time,
		date_format( a.create_time, '%Y' ) AS now_year,
		sum( a.out_stock_money ) AS a_out_money,
		sum( a.freight_money ) AS a_fre_money,
		CONVERT ( sum( a.out_stock_money ) - sum( a.freight_money ), DECIMAL ( 10, 2 ) ) AS a_sale_money 
	FROM
		food_orders_info a 
	WHERE
		a.state = 1 
		AND a.order_state = 60 
	
	GROUP BY
		date_format( a.create_time, '%Y-%m' ),
		date_format( a.create_time, '%Y' ) 
	ORDER BY
		date_format( a.create_time, '%Y-%m' ) ASC 
	) now_sale
	LEFT JOIN (
	SELECT
		date_format( DATE_ADD( b.create_time, INTERVAL 1 MONTH ), '%Y-%m' ) AS now_time,
		sum( b.out_stock_money ) AS bout_money,
		sum( b.freight_money ) AS bfe_money,
		CONVERT ( sum( b.out_stock_money ) - sum( b.freight_money ), DECIMAL ( 10, 2 ) ) AS b_sale_money 
	FROM
		food_orders_info b 
	WHERE
		b.state = 1 
		AND b.order_state = 60 
	GROUP BY
		date_format( DATE_ADD( b.create_time, INTERVAL 1 MONTH ), '%Y-%m' ) 
	ORDER BY
		date_format( DATE_ADD( b.create_time, INTERVAL 1 MONTH ), '%Y-%m' ) ASC 
	) old_sale ON now_sale.now_time = old_sale.now_time
	LEFT JOIN (
	SELECT
		date_format( DATE_ADD( c.create_time, INTERVAL 1 YEAR ), '%Y-%m' ) AS last_year_time,
		sum( c.out_stock_money ) AS bout_money,
		sum( c.freight_money ) AS bfe_money,
		CONVERT ( sum( c.out_stock_money ) - sum( c.freight_money ), DECIMAL ( 10, 2 ) ) AS last_year_money 
	FROM
		food_orders_info c 
	WHERE
		c.state = 1 
		AND c.order_state = 60 
	GROUP BY
		date_format( DATE_ADD( c.create_time, INTERVAL 1 YEAR ), '%Y-%m' ) 
	ORDER BY
		date_format( DATE_ADD( c.create_time, INTERVAL 1 YEAR ), '%Y-%m' ) ASC 
	) last_year_sale ON now_sale.now_time = last_year_sale.last_year_time 
	
ORDER BY
	now_sale.now_year DESC,
	now_sale.now_time ASC

查询得到
在这里插入图片描述
month_radio即环比,year_ratio即同比。

按季度统计销售总额、环比

查询季度主要使用 QUARTER ( 时间)函数
方法和按年月统计类似,就直接上sql语句了

#查询当前季度销售总额
SELECT
		date_format( a.create_time, '%Y' ) AS now_year,
		QUARTER ( a.create_time ) a_quarter,
		CONCAT( YEAR ( a.create_time ), QUARTER ( a.create_time ) ) now_quarter,
		sum( a.out_stock_money ) AS a_out_money,
		sum( a.freight_money ) AS a_fre_money,
		CONVERT ( sum( a.out_stock_money ) - sum( a.freight_money ), DECIMAL ( 10, 2 ) ) AS a_sale_money 
	FROM
		food_orders_info a 
	WHERE
		a.state = 1 
		AND a.order_state = 60 
	GROUP BY
		CONCAT( YEAR ( a.create_time ), QUARTER ( a.create_time )),date_format( a.create_time, '%Y' ),	QUARTER ( a.create_time )

QUARTER ( a.create_time ) 指查询当前时间在哪一个季度
在这里插入图片描述

查询上一季度销售总额

SELECT
		CONCAT( YEAR ( b.create_time ), QUARTER ( DATE_ADD( b.create_time, INTERVAL 1 QUARTER ) ) ) AS last_quarter,
		sum( b.out_stock_money ) AS b_out_money,
		sum( b.freight_money ) AS b_fre_money,
		CONVERT ( sum( b.out_stock_money ) - sum( b.freight_money ), DECIMAL ( 10, 2 ) ) AS b_sale_money 
	FROM
		food_orders_info b 
	WHERE
		b.state = 1 
		AND b.order_state = 60 
	GROUP BY
	CONCAT( YEAR ( b.create_time ), QUARTER ( DATE_ADD( b.create_time, INTERVAL 1 QUARTER ) ) ) 	

在这里插入图片描述

查询去年同季度销售额

SELECT
		CONCAT(YEAR( DATE_ADD( c.create_time, INTERVAL 1 year )),QUARTER(c.create_time)) as last_year_quarter,
		sum( c.out_stock_money ) AS c_out_money,
		sum( c.freight_money ) AS c_fre_money,
		CONVERT ( sum( c.out_stock_money ) - sum( c.freight_money ), DECIMAL ( 10, 2 ) ) AS c_sale_money 
	FROM
		food_orders_info c 
	WHERE
		c.state = 1 
		AND c.order_state = 60 
	GROUP BY
	CONCAT(YEAR( DATE_ADD( c.create_time, INTERVAL 1 year )),QUARTER(c.create_time)) 

在这里插入图片描述
查询本季度、上一季度的销售额,并统计环比

SELECT
	now_quarter_sale.now_year,
	now_quarter_sale.a_quarter,
	now_quarter_sale.now_quarter,
CASE
		
		WHEN now_quarter_sale.a_sale_money IS NULL 
		OR now_quarter_sale.a_sale_money = 0 THEN
			0 ELSE now_quarter_sale.a_sale_money 
		END this_sale_money,
CASE
		
		WHEN last_quarter_sale.b_sale_money IS NULL 
		OR last_quarter_sale.b_sale_money = 0 THEN
			0 ELSE last_quarter_sale.b_sale_money 
		END last_sale_money,
		
CASE
		WHEN last_quarter_sale.b_sale_money IS NULL 
		OR last_quarter_sale.b_sale_money = 0 THEN
			0 ELSE CONVERT ( ( ( now_quarter_sale.a_sale_money - last_quarter_sale.b_sale_money ) / last_quarter_sale.b_sale_money ) * 100, DECIMAL ( 10, 2 ) )
		END last_quarter_ratio 
FROM
	(
	SELECT
		date_format( a.create_time, '%Y' ) AS now_year,
		QUARTER ( a.create_time ) a_quarter,
		CONCAT( YEAR ( a.create_time ), QUARTER ( a.create_time ) ) now_quarter,
		sum( a.out_stock_money ) AS a_out_money,
		sum( a.freight_money ) AS a_fre_money,
		CONVERT ( sum( a.out_stock_money ) - sum( a.freight_money ), DECIMAL ( 10, 2 ) ) AS a_sale_money 
	FROM
		food_orders_info a 
	WHERE
		a.state = 1 
		AND a.order_state = 60 
	GROUP BY
		CONCAT( YEAR ( a.create_time ), QUARTER ( a.create_time )),date_format( a.create_time, '%Y' ),	QUARTER ( a.create_time )
	) now_quarter_sale
	LEFT JOIN (
	SELECT
		CONCAT( YEAR ( b.create_time ), QUARTER ( DATE_ADD( b.create_time, INTERVAL 1 QUARTER ) ) ) AS last_quarter,
		sum( b.out_stock_money ) AS b_out_money,
		sum( b.freight_money ) AS b_fre_money,
		CONVERT ( sum( b.out_stock_money ) - sum( b.freight_money ), DECIMAL ( 10, 2 ) ) AS b_sale_money 
	FROM
		food_orders_info b 
	WHERE
		b.state = 1 
		AND b.order_state = 60 
	GROUP BY
	CONCAT( YEAR ( b.create_time ), QUARTER ( DATE_ADD( b.create_time, INTERVAL 1 QUARTER ) ) ) 
	) last_quarter_sale ON now_quarter_sale.now_quarter = last_quarter_sale.last_quarter

在这里插入图片描述
最终 ,统计当前季度、上一季度、以及去年同一季度的环比、同比

SELECT
	now_quarter_sale.now_year,
	now_quarter_sale.a_quarter,
	now_quarter_sale.now_quarter,
CASE
		
		WHEN now_quarter_sale.a_sale_money IS NULL 
		OR now_quarter_sale.a_sale_money = 0 THEN
			0 ELSE now_quarter_sale.a_sale_money 
		END this_sale_money,
CASE
		
		WHEN last_quarter_sale.b_sale_money IS NULL 
		OR last_quarter_sale.b_sale_money = 0 THEN
			0 ELSE last_quarter_sale.b_sale_money 
		END last_sale_money,
		
CASE
		WHEN last_quarter_sale.b_sale_money IS NULL 
		OR last_quarter_sale.b_sale_money = 0 THEN
			0 ELSE CONVERT ( ( ( now_quarter_sale.a_sale_money - last_quarter_sale.b_sale_money ) / last_quarter_sale.b_sale_money ) * 100, DECIMAL ( 10, 2 ) )
		END last_quarter_ratio ,
CASE
		WHEN last_year_quarter_sale.c_sale_money	 IS NULL 
		OR last_year_quarter_sale.c_sale_money	 = 0 THEN
			0 ELSE last_year_quarter_sale.c_sale_money	
		END last_year_money,
CASE
		WHEN last_year_quarter_sale.c_sale_money	 IS NULL 
		OR last_year_quarter_sale.c_sale_money	 = 0 THEN
			0 ELSE CONVERT ( ( ( now_quarter_sale.a_sale_money - last_year_quarter_sale.c_sale_money ) / last_year_quarter_sale.c_sale_money ) * 100, DECIMAL ( 10, 2 ) )
		END last_year_ratio
FROM
	(
	SELECT
		date_format( a.create_time, '%Y' ) AS now_year,
		QUARTER ( a.create_time ) a_quarter,
		CONCAT( YEAR ( a.create_time ), QUARTER ( a.create_time ) ) now_quarter,
		sum( a.out_stock_money ) AS a_out_money,
		sum( a.freight_money ) AS a_fre_money,
		CONVERT ( sum( a.out_stock_money ) - sum( a.freight_money ), DECIMAL ( 10, 2 ) ) AS a_sale_money 
	FROM
		food_orders_info a 
	WHERE
		a.state = 1 
		AND a.order_state = 60 
	GROUP BY
		CONCAT( YEAR ( a.create_time ), QUARTER ( a.create_time )),date_format( a.create_time, '%Y' ),	QUARTER ( a.create_time )
	) now_quarter_sale
	LEFT JOIN (
	SELECT
		CONCAT( YEAR ( b.create_time ), QUARTER ( DATE_ADD( b.create_time, INTERVAL 1 QUARTER ) ) ) AS last_quarter,
		sum( b.out_stock_money ) AS b_out_money,
		sum( b.freight_money ) AS b_fre_money,
		CONVERT ( sum( b.out_stock_money ) - sum( b.freight_money ), DECIMAL ( 10, 2 ) ) AS b_sale_money 
	FROM
		food_orders_info b 
	WHERE
		b.state = 1 
		AND b.order_state = 60 
	GROUP BY
	CONCAT( YEAR ( b.create_time ), QUARTER ( DATE_ADD( b.create_time, INTERVAL 1 QUARTER ) ) ) 
	) last_quarter_sale ON now_quarter_sale.now_quarter = last_quarter_sale.last_quarter
	LEFT JOIN 
	(
SELECT
		CONCAT(YEAR( DATE_ADD( c.create_time, INTERVAL 1 year )),QUARTER(c.create_time)) as last_year_quarter,
		sum( c.out_stock_money ) AS c_out_money,
		sum( c.freight_money ) AS c_fre_money,
		CONVERT ( sum( c.out_stock_money ) - sum( c.freight_money ), DECIMAL ( 10, 2 ) ) AS c_sale_money 
	FROM
		food_orders_info c 
	WHERE
		c.state = 1 
		AND c.order_state = 60 
	GROUP BY
	CONCAT(YEAR( DATE_ADD( c.create_time, INTERVAL 1 year )),QUARTER(c.create_time)) 
	)last_year_quarter_sale  on now_quarter_sale.now_quarter=last_year_quarter_sale.last_year_quarter

在这里插入图片描述
last_quarter_ratio = ((this_sale_money-last_sale_money)/last_sale_money)*100%
last_year_ratio = ((this_sale_money-last_year_money)/last_year_money)*100%

界面实现效果:
在这里插入图片描述
有兴趣的老爷,可以关注我的公众号【一起收破烂】,回复【006】获取2021最新java面试资料以及简历模型120套哦~
在这里插入图片描述

已标记关键词 清除标记
<p> <span style="font-size:14px;color:#E53333;">限时福利1:</span><span style="font-size:14px;">购课进答疑群专享柳峰(刘运强)老师答疑服务</span> </p> <p> <br /> </p> <p> <br /> </p> <p> <span style="font-size:14px;"></span> </p> <p> <span style="font-size:14px;color:#337FE5;"><strong>为什么需要掌握高性能的MySQL实战?</strong></span> </p> <p> <span><span style="font-size:14px;"><br /> </span></span> <span style="font-size:14px;">由于互联网产品用户量大、高并发请求场景多,因此对MySQL的性能、可用性、扩展性都提出了很高的要求。使用MySQL解决大量数据以及高并发请求已经是程序员的必备技能,也是衡量一个程序员能力和薪资的标准之一。</span> </p> <p> <br /> </p> <p> <span style="font-size:14px;">为了让大家快速系统了解高性能MySQL核心知识全貌,我为你总结了</span><span style="font-size:14px;">「高性能 MySQL 知识框架图」</span><span style="font-size:14px;">,帮你梳理学习重点,建议收藏!</span> </p> <p> <br /> </p> <p> <img alt="" src="https://img-bss.csdnimg.cn/202006031401338860.png" /> </p> <p> <br /> </p> <p> <span style="font-size:14px;color:#337FE5;"><strong>【课程设计】</strong></span> </p> <p> <span style="font-size:14px;"><br /> </span> </p> <p> <span style="font-size:14px;">课程分为四大篇章,将为你建立完整的 MySQL 知识体系,同时将重点讲解 MySQL 底层运行原理、数据库的性能调优、高并发、海量业务处理、面试解析等。</span> </p> <p> <span style="font-size:14px;"><br /> </span> </p> <p> <span style="font-size:14px;"></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>一、性能优化篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括经典 MySQL 问题剖析、索引底层原理和事务与锁机制。通过深入理解 MySQL 的索引结构 B+Tree ,学员能够从根本上弄懂为什么有些 SQL 走索引、有些不走索引,从而彻底掌握索引的使用和优化技巧,能够避开很多实战中遇到的“坑”。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>二、MySQL 8.0新特性篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括窗口函数和通用表表达式。企业中的许多报表统计需求,如果不采用窗口函数,用普通的 SQL 语句是很难实现的。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>三、高性能架构篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">主要包括主从复制和读写分离。在企业的生产环境中,很少采用单台MySQL节点的情况,因为一旦单个节点发生故障,整个系统都不可用,后果往往不堪设想,因此掌握高可用架构的实现是非常有必要的。</span> </p> <p style="text-align:justify;"> <br /> </p> <p style="text-align:justify;"> <span style="font-size:14px;"><strong>四、面试篇:</strong></span> </p> <p style="text-align:justify;"> <span style="font-size:14px;">程序员获得工作的第一步,就是高效的准备面试,面试篇主要从知识点回顾总结的角度出发,结合程序员面试高频MySQL问题精讲精练,帮助程序员吊打面试官,获得心仪的工作机会。</span> </p>
相关推荐
©️2020 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页