1、查询 类型 在某时间范围内,并做sum处理;
SELECT SUM(play_times) FROM tb_ids_ad_exposure WHERE type='3' and play_starttime BETWEEN '2019-07-02 00:00:00' and '2019-07-02 23:59:59' ;
2、删除 时间大于当前时间44天的数据
DELETE FROM tenant_visitor_access_record WHERE DATE_FORMAT(sysc_time, '%Y-%m-%d') <= DATE_SUB(CURDATE(), INTERVAL 44 DAY)
3、查询所有type 3信息
select * from td_ids_ad_exposure where type=3
4.
SELECT COUNT(1) from warning_message w LEFT JOIN hotel h on w.hotel_code = h.code
where w.result_handling = 1
a.增删改查:
insert into user(name, age) values("aaa", 18)
select * from user where id = 1
update user set user_name = "aaa" where id = "1"
b.排序和去重:
1、select * from user order by age desc降序/asc
2、去重 select distinct(colume) from table
select distinct(age) from user
c.范围查询:
范围:select * from user where age between 18 and 20 范围在18到20之间
select * from user where age in(18, 20) 范围在18或20
select * from user where phone_num like '%159%' 模糊查询, 比较消耗性能
d.连接查询:
morder表:
user表:
内连接:select * from user u INNER JOIN morder m ON u.id= m.user_id 两个表中同时存在的用户才会展示
左连接:select * from user u LEFT JOIN morder m ON u.id= m.user_id 字段重复的话最好加别名比如where u.age in (18, 95),左边的是左表,以左表为基础,若右表无匹配为空
右连接: select * from user u RIGHT JOIN morder m ON u.id = m.user_id
左边的是左表,以右表为基础,若左表无匹配为空
e.嵌套查询
select user_name, phone_name FROM USRE where id IN(SELECT user_id from morder where region='北京')
注:嵌套的sql语句,可以放在select \ from\ where条件后
f.聚合函数
max min avg sum count
select max(total_price) from morder
select avg(total_price) from morder
select sum(total_price) from morder where region='北京'
select count(*) from morder where region='北京'
group by 和having
使用group by 的sql语句,select 后的字段,只能是group by 后的字段,如果想展示其他数据,需要给该列使用聚合函数;否则默认展示分组里的第一行数据
select regon, sum(total_price) from morder Group by region
select region,type, max(total_price) from morder where total_price >10 Group by region, type
having 对分组之后的数据进行过滤
select region , type sum(total_price) AS ‘区域总金额’ from morder where total_price >10 Group by region, type having sum(total_price) > 1000
统计每个地区订单总金额占全国订单总金额的比例,安装倒叙排列
select tmp.region as '城市', tmp.region_price/tmp.all_price as 比例 from (
select region , sum(total_price) as region_price, (select sum(total_price) from morder ) as all_price from morder Group by region
)tmp
order by 比例 desc
sql 语句执行顺序:
from-> where->group by ->having-> select->order by