文章目录

创建表
查询`select`
- 排序`order by`
- 限定显示`limit`
- 聚合`group by`
- 聚合后筛选`having`
插入`insert`
删除`delete`
修改`update`
条件`where`
- 条件表达式
- 多条件组合
查询连接
- join
- union (all)
性能调优
- 1. 查看语句效率`explain`
- 1. 创建索引
- 1. 比对表的字符编码
- 1. 比对字段数据类型
实用语句

创建表

先提前准备好表格

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. 比对字段数据类型

尽量一致.

实用语句

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

drop table if exists tmp_date;
create table tmp_date
```
select * from dim_date
```
insert into ... select ...
从现有的表取全部或部分字段插入到另一个表,一般结合delete一起用.需满足
1. 提前建表
2. 全部字段(因为不能指定字段)
delete from tmp_date;
insert into tmp_date
```
select * from dim_date
```