mysql count 大于100 mysql in_use 大于0

文章目录

• 创建表
• 查询`select`

• 排序`order by`
• 限定显示`limit`
• 聚合`group by`
• 聚合后筛选`having`

• 插入`insert`
• 删除`delete`
• 修改`update`
• 条件`where`

• 条件表达式
• 多条件组合

• 查询连接

• join
• union (all)

• 性能调优

• 1. 查看语句效率`explain`
• 2. 创建索引
• 3. 比对表的字符编码
• 4. 比对字段数据类型

• 实用语句

创建表

先提前准备好表格

CREATE TABLE `dim_date` (
  `日期` date NOT NULL,
  `年` int(255) DEFAULT NULL,
  `月` varchar(255) DEFAULT NULL,
  `日` tinyint(255) DEFAULT NULL,
  `周` varchar(255) DEFAULT NULL,
  `星期` tinyint(255) DEFAULT NULL,
  `季度` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`日期`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 comment '日期映射表'

CREATE TABLE `dim_promotion`(
	`日期` date NOT NULL,
	`主题` varchar(255) DEFAULT NULL
)  ENGINE=InnoDB DEFAULT CHARSET=utf8 comment '促销主题表'

查询select

查询所有字段

select * from dim_date

查询部分字段

select 星期,年,月,日 from dim_date

排序order by

降序

select * from dim_date order by 日期 desc

升序

select * from dim_date order by 日期

select * from dim_date order by 日期 asc

限定显示limit

只取3行

select * from dim_date limit 3

一般结合order by取前/后n,例如最新的3个日期

select * from dim_date order by 日期 desc limit 3

聚合group by

查询每月有多少数据

select 月,count(1) as 当月天数 from dim_date group by 月

可以用数字表示第n个字段

select 月,count(1) as 当月天数 from dim_date group by 1

聚合后筛选having

查询每月天数在31天及以上的月

select 月,count(1) as 当月天数 from dim_date group by 月 having count(1) >= 31

插入insert

整个表插入

insert into dim_date values ('2021-01-01',2021,'1',1,'w2101',5,'Q1')

部分字段插入.未指定的字段会默认为null

insert into dim_date (日期,年) valus ('2021-01-02',2021)

删除delete

清空表,亦即删除整个表的数据,但是保留表结构(字段)

delete from dim_date

truncate dim_date

删除表数据和结构,亦即让它不再存在于数据库

drop table dim_date

修改update

一般指定条件修改

update dim_date set 月 = '1' where 日期 = '2020-01-02'

条件where

查询日期在2020年及之后的记录

select * from dim_date where 日期 >= '2020-01-01'

条件表达式

等于=

select * from dim_date where 年 = 2020

大于>

select * from dim_date where 年 > 2019

大于等于>=

select * from dim_date where 年 >= 2020

小于<

select * from dim_date where 年 < 2021

小于等于<=

select * from dim_date where 年 <= 2020

不等于<>

select * from dim_date where 年 <> 2019

模糊匹配%

select * from dim_date where 日期 like '2020%'

多条件组合

且and

select * from dim_date where 年 >= 2020 and 日期 <= 2021

介于,在…之间between and

select * from dim_date where 年 between 2020 and 2021

或or

select * from dim_date where 年 >= 2020 and 日期 <= 2021

查询连接

join

left join,左连接,以左为主

a表	b表	连接结果
有	有	有
有	无	有
无	有	无
无	无	无

select a.日期,b.主题 from dim_date a 
	left join dim_promotioin b on a.日期 = b.日期

---

right join,右连接,以右为主

a表	b表	连接结果
有	有	有
有	无	无
无	有	有
无	无	无

select a.日期,b.主题 from dim_date a
	right join dim_promotion b on a.日期 = b.日期

---

inner join内连接,左右都有

a表	b表	连接结果
有	有	有
有	无	无
无	有	无
无	无	无

select a.日期,b.主题 from dim_date a
	inner join dim_promotion b on a.日期 = b.日期

条件连接等价于inner join

select a.日期,b.主题 from dim_date a,dim_promotion b 
	where a.日期 = b.日期

---

outer join,外连接,没有现成的要通过现有的编译

#a表的所有数据
select a.日期,b.主题 from dim_date a
	left join dim_promotioin b on a.日期 = b.日期

union all 

#b表有,a表没有的数据
select b.日期,b.主题 from dim_date a
	left join dim_promotion b on a.日期 = b.日期
	where b.日期 is not null

---

笛卡尔积,也就是a表 * b表

select a.日期,b.日期 from dim_date a,dim_promotion b

union (all)

union,上下连接,会去重

select 日期 from dim_date
union 
select 日期 from dim_promotion

union all,上下连接,不会去重,无脑拼接

select 日期 from dim_date
union all 
select 日期 from dim_promotion

性能调优

1. 查看语句效率explain

在语句前加一个explain就可以了

explain
select a.日期,b.主题 from dim_date a
	left join dim_promotion b on a.日期 = b.日期

出来的字段看type,性能从优到差分别是

null>>const/system>>eq_reg>>ref>>range>>index>>ALL

实际操作中大部分都是ALL到ref层级,所以都是追求ref较多

2. 创建索引

数据量太大的时候,也就是查询太慢的时候(建议超过200s,需根据实际定),除了优化SQL,一般需要增加索引,因为简单粗暴

create index idx_dt on dim_date(日期)

3. 比对表的字符编码

尽量一致.
例如有一次两个简单的万级别的表,结果查询了1000s,后面发现是因为一个表字符是utf8mb4,一个表字符是utf8

4. 比对字段数据类型

尽量一致.

实用语句

1. create table ... select ... 从现有的表取全部或者部分字段直接创建新表,一般结合drop一起用

drop table if exists tmp_date;
create table tmp_date
	select * from dim_date

3. insert into ... select ... 从现有的表取全部或部分字段插入到另一个表,一般结合delete一起用.需满足
1. 提前建表
2. 全部字段(因为不能指定字段)

delete from tmp_date;
insert into tmp_date 
	select * from dim_date