文章目录
创建表
先提前准备好表格
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
一起用.需满足- 提前建表
- 全部字段(因为不能指定字段)
delete from tmp_date;
insert into tmp_date
select * from dim_date