数据库日常查询-工作案例(重点)

mysql日常开发总结：

mysql查询系统当前时间：

-- 年月日
select CURDATE();
-- 年月日 时分秒
select NOW();
-- 将日期按照指定格式转换为字符串
select to_char(date,'yyyyMMdd');

mysql创建表时间为系统当前时间：

CREATE TABLE `t_mip_xxxx_xxxx` (
  `brand_code` varchar(50) NOT NULL COMMENT '品牌编码',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`brand_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='xxxxTOP20占比';

mysql日期格式化：

DATE_FORMAT(start_time,'%Y-%m-%d %H:%i:%s') AS startTime,
DATE_FORMAT(end_time,'%Y-%m-%d %H:%i:%s') AS endTime

mysql聚合函数：sum(),count(),avg(),max(),min()

SELECT
	city_level AS cityLevel,
	city_level_name AS cityLevelName,
	count(city_level) AS count
FROM
	t_mip_base_area
GROUP BY city_level

执行结果：

mysql统计前7天数据：

-- 方法一：使用between and 
SELECT
    date,
    count(code)
FROM
    XXX_ASJ
WHERE
    1=1
AND status=1
AND date between 20200603 and 20200609
group by date

-- 方法二：使用between and + interval
SELECT
    date,
    count(code)
FROM
    XXX_ASJ
WHERE
    1=1
AND status=1
AND CAST(date AS CHAR(10)) between to_char(now() - INTERVAL '6D','yyyyMMdd') and to_char(now() ,'yyyyMMdd')
group by date

执行结果：

date         count

20200603  1

20200604  1

20200607  2

mysql sum函数统计求和,coalesce()：返回参数中的第一个非空表达式（从左向右）

select coalesce(a,b,c);  如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

select
    code,
    sum(coalesce(jzkll,0) + coalesce(czkll,0)) AS count
from
    xx_station
where
    1=1
and status=1
group by code 

执行结果：

code  count

1001   1

1002   2

1004   5

mysql max()函数：

select
    code
    count(mgsfhm) AS count
from
    xx_label
where hc_date =(
  select 
    max(hc_date)
  from 
    xx_label
)
group by code

执行结果：

code     count

ZA1001  1

ZA1002  5

ZA1003  2

备注：mysql使用聚合函数时如sum()，count()一定要记得分组group by，日常开发中经常根据某个字段分组统计个数

mysql修改主键：

ALTER TABLE `t_mip_category_coincdence` DROP PRIMARY KEY,
 ADD PRIMARY KEY (
	`brand_code`,
	`category_code`,
	`crowd_type`,
	`collect_time`,
	`top_name`
)

修改表字段类型：

ALTER TABLE t_mip_insight_appclassification MODIFY COLUMN classification_type VARCHAR (50) NOT NULL COMMENT 'app一级分类类型';

ALTER TABLE t_mip_insight_app MODIFY COLUMN classification_type VARCHAR (50) NOT NULL COMMENT 'app一级分类类型';

增加字段：

ALTER TABLE t_mip_base_area ADD COLUMN city_level_name varchar(100) NOT NULL COMMENT '城市级别名称';

mysql操作索引：

-- 删除索引
/**
	语法：
	方法一：drop index index_name on table_name ;
	方法二：alter table table_name drop index index_name ;
*/
DROP INDEX IX_insight_profession_collect_time ON  t_mip_insight_profession

-- 创建联合索引
/**
方法一：create index indexname on tablename (column1,column2,...) index_type;
方法二：alter table tablename add index indexname (column1,column2,...) index_type;
index_type: 
USING {BTREE | HASH | RTREE} 
*/
ALTER TABLE t_mip_insight_preference ADD INDEX IX_insight_preference_union (data_type, insight_type, preference_type) USING BTREE;

CREATE INDEX IX_insight_preference_union ON t_mip_insight_preference(data_type, insight_type, preference_type) USING BTREE;

mysql模糊查询：（重点）

-- 方法一：like '%keyword%' 效率极其低，全盘扫描，sql注入问题
SELECT
	*
FROM
	t_mip_crowd_own
WHERE
	1 = 1
AND audience_name like '&san&';

-- 方法二：INSTR(str,substr)  结果集大于0说明匹配到，否则匹配不到
SELECT
	*
FROM
	t_mip_crowd_own
WHERE
	1 = 1
AND INSTR(audience_name, 'san') >0;

-- 方法三：LOCATE(substr,str) 
SELECT
	*
FROM
	t_mip_crowd_own
WHERE
	1 = 1
AND LOCATE('san',audience_name);

mysql内连接查询，排除重复数据：

SELECT
	mia.grail_type AS grailType,
	mia.province_code AS provinceCode,
	mia.insight_percent AS insightPercent,
	mia.grail_percent AS grailPercent,
	mia.compare_percent AS comparePercent,
	mba.province_name AS provinceName
FROM
	t_mip_insight_area mia
INNER JOIN (
	SELECT
		DISTINCT(province_code),
		province_name
	FROM
		t_mip_base_area
) mba ON mia.province_code = mba.province_code
WHERE
	1 = 1

业务：根据如下截图信息，查询category_code包含industry_code

则查询sql语句：

SELECT
	category_code
FROM
	t_mip_base_brand
WHERE
	1 = 1
AND brand_code = '0001'
AND (INSTR(
	category_code,
	industry_code
) > 0)

结果集：

mysql函数：cast(字段 AS 类型) 将某个字段类型转换为另一种类型

如：cast(xx AS signed) 将字符串类型转换为数值类型

cast(xx AS char(10)) 将数值类型转换为固定长度字符串类型

cast(xx AS varchar(30)) 将数值类型转换为可变长度字符串类型

数据库设计：

SELECT
	'城市等级分布' AS dimension,
	mba.city_level_name AS feature,
	CAST(micl.city_level AS SIGNED) AS cityLevel,
	CONCAT(ROUND(micl.insight_percent * 100, 2),'%') AS insightPercent,
    CONCAT(ROUND(micl.grail_percent * 100, 2),'%') AS grailPercent,
    CONCAT(ROUND(micl.compare_percent * 100, 2),'%') AS comparePercent
FROM
	t_mip_insight_city_level micl
LEFT JOIN (
	SELECT DISTINCT
		(city_level),
		city_level_name
	FROM
		t_mip_base_area
) mba ON micl.city_level = mba.city_level
WHERE
	1 = 1
AND micl.insight_type = 1
AND micl.data_type = 1
AND micl.brand_code = '0001'
AND micl.start_crowd_type = '1'
ORDER BY cityLevel

备注：业务要求根据城市级别排序，排序字段类型为字符串类型时无效，排序值对数值类型起作用，故需要将查询字段类型转换为数值类型(重点)

以下案例是mysql函数总结：

mysql函数：CONCAT：字符串连接，ROUND四舍五入，小数点保留位数：

SELECT
	'性别分布' AS dimension,
	CASE sex_type
	WHEN 0 THEN '男'
	WHEN 1 THEN '女'
	END AS feature,
  CONCAT(ROUND(insight_percent * 100, 2),'%') AS insightPercent,
  CONCAT(ROUND(grail_percent * 100, 2),'%') AS grailPercent,
  CONCAT(ROUND(compare_percent * 100, 2),'%') AS comparePercent
FROM
	t_mip_insight_sex
WHERE
	1 = 1
AND insight_type = 1
AND data_type = 1
AND brand_code = '0001'
AND start_crowd_type = '1'

mysql条件函数：

if函数：

语法：if(expre1,expre2,expre3)

解释：当expre1为true时返回expre2，false时返回expre3，跟代码的三目运算类似

场景：只是处理一次判断

ifnull函数：

语法：ifnull(column1,column2)

解释：当column1不为null时返回column1，当column1为null时返回column2

场景：只是处理一次判断

elt函数：

语法：elt(condition,str1,str2,str3,...)

解释：condition值必须是顺序1,2,3，...才会对应str1,str2,str3,...

场景：固定值如周期1,2,3,4,5,6,7对应星期一，星期二，星期三，星期四，星期五，星期六，星期天

case...when...then...else...end函数：

语法：case column1

           when 条件1 then 结果1

           when 条件2 then 结果2

          ....

          else 其他结果

          end

解释：当column1符合条件1时返回结果2，符合条件2时返回结果2，当都没有对应条件符合时，返回其他结果，跟代码的switch...case 类似

场景：适用于处理复杂逻辑判断

案例：

SELECT
	'性别分布' AS dimension,
	IF(sex_type=0,'男','女') AS feature,
  CONCAT(ROUND(insight_percent * 100, 2),'%') AS insightPercent,
  CONCAT(ROUND(grail_percent * 100, 2),'%') AS grailPercent,
  CONCAT(ROUND(compare_percent * 100, 2),'%') AS comparePercent
FROM
	t_mip_insight_sex
WHERE
	1 = 1
AND insight_type = 1
AND data_type = 1
AND brand_code = '0001'
AND start_crowd_type = '1'

结果：

SELECT
	'年龄分布' AS dimension,
  CONCAT(IF(sex_type=0,'(男)-','(女)-'),
	CASE 	age_type
  WHEN 0 THEN '(18岁以下)'
  WHEN 1 THEN '(18-23)'
  WHEN 2 THEN '(24-30)'
  WHEN 3 THEN '(31-40)'
  WHEN 4 THEN '(41-50)'
  WHEN 5 THEN '(51-60)'
  WHEN 6 THEN '(60以上)'
  END) AS feature,
	CONCAT(ROUND(insight_percent * 100, 2),'%') AS insightPercent,
  CONCAT(ROUND(grail_percent * 100, 2),'%') AS grailPercent,
  CONCAT(ROUND(compare_percent * 100, 2),'%') AS comparePercent
FROM
	t_mip_insight_age
WHERE
	1 = 1
AND insight_type = 1
AND data_type = 1
AND brand_code = '0001'
AND start_crowd_type = '1'

结果：

<!--根据状态统计报名数-->
SELECT
		count(CASE WHEN status=0 THEN 1 END) AS pendingReview,
		count(CASE WHEN status=1 THEN 1 END) AS examinationPassed,
		count(CASE WHEN status=2 THEN 1 END) AS auditRejection,
		count(CASE WHEN status=3 or status=9 THEN 1 END) AS preAndStop
FROM
		im_commodity_sign
WHERE 1=1
AND merchant_id = '70057239'
AND act_id = '587656417390927872'

结果：

场景：业务要求展示金额区间范围：

数据库设计字段：

则sql实现方式一：if函数

SELECT
	'产品偏好金额' AS dimension,
	CONCAT(
	'(',IF(end_momey IS NULL,CONCAT('>',start_momey),CONCAT(start_momey,'-',end_momey)),')') AS feature, 
	CONCAT(ROUND(insight_percent * 100, 2),'%') AS insightPercent,
	'0' AS grailPercent,
	'0' AS comparePercent
FROM
	t_mip_insight_preference_money
LEFT JOIN t_mip_base_preference_money ON t_mip_insight_preference_money.money_id = t_mip_base_preference_money.momey_id
WHERE
	1 = 1
AND preference_type = 1
AND insight_type = 1
AND data_type = 1
AND brand_code = '0001'
AND start_crowd_type = '1'

方法二：ifnull函数

SELECT
	'产品偏好金额' AS dimension,
	CONCAT(
	'(',IFNULL(CONCAT(start_momey, '-', end_momey),CONCAT('>', start_momey)),')') AS feature, 
	CONCAT(ROUND(insight_percent * 100, 2),'%') AS insightPercent,
	'0' AS grailPercent,
	'0' AS comparePercent
FROM
	t_mip_insight_preference_money
LEFT JOIN t_mip_base_preference_money ON t_mip_insight_preference_money.money_id = t_mip_base_preference_money.momey_id
WHERE
	1 = 1
AND preference_type = 1
AND insight_type = 1
AND data_type = 1
AND brand_code = '0001'
AND start_crowd_type = '1'

结果：

SELECT 
	'类目偏好占比' AS dimension,
	CONCAT('(',preference_top_name,')','-',
	'(',ELT(category_level,'一级类目','二级类目','三级类目'),')','-',
	'(',top_name,')') AS feature,
	CONCAT(ROUND(insight_percent * 100, 2),'%') AS insightPercent,
  CONCAT(ROUND(grail_percent * 100, 2),'%') AS grailPercent,
  CONCAT(ROUND(compare_percent * 100, 2),'%') AS comparePercent
FROM t_mip_insight_preference
WHERE
	1 = 1
AND preference_type=2
AND insight_type = 1
AND data_type = 1
AND brand_code = '0001'
AND start_crowd_type = '1'

结果：

SELECT 
	IF(behavior_type=1,'按周浏览商品分布','按周购买商品分布') AS dimension,
  ELT(weektime_type,'星期一','星期二','星期三','星期四','星期五','星期六','星期天') AS feature,
	CONCAT(ROUND(insight_percent * 100, 2),'%') AS insightPercent,
  CONCAT(ROUND(grail_percent * 100, 2),'%') AS grailPercent,
  CONCAT(ROUND(compare_percent * 100, 2),'%') AS comparePercent
FROM
	t_mip_insight_weektime
WHERE
	1 = 1
AND insight_type = 1
AND data_type = 1
AND brand_code = '0001'
AND start_crowd_type = '1'

结果：

mysql union all  组合查询，将两个查询结果合并，并且排除重复数据

select
    '正常人员' AS title,
    code,
    count(cyry_id) AS count
from
    xx_extend
where
    1=1
and status=1
group by code
union all
select
    '异常人员' AS title
    code,
    count(zhm) AS count
from
    xx_person
where
    1=1
and status=1
group by code