其他:
sql语句的执行顺序:from-->join-->on-->where-->groupby-->having-->select-->orderby
sql语句的分类:DQL查询语句:select from where
DDL定义语句:create alter drop
DML操作语句:update delete insert
DCL控制语句:grant rollback commit
删除表:drop table t:整个表删除,包括表里的内容和表结构
truncate table t:删除表里的全部内容,但保留表结构
delete from table t where col = 'cc':删除满足条件的记录
表操作:1.创建:create table t() 主键:primary key 自增:anto_increment 非null:not null
2.插入:insert into table t values();
3.更新:update t set col = 'aaa' where col = '123'
排序:同时对两列进行排序:order by col1 desc,col2 asc
子查询:查询语句里的查询嵌套
组合查询:union语句
1.表order有三个字段,店铺ID,订单时间,订单金额,查询一个月内每周都有销量的店铺。weekofyear(day)函数
--每天属于一年中的哪周
with tmp_order as
(select
id
,weekofyear(dt) as dt_new
,amount
from order
where dt >= '2019-07-01'
and dt <= '2019-07-31')
--有5个不同周的店铺即为所求
select id, dt_new
from tmp_order
group by id, dt_new
having count(dt_new) = 5
2. 如