sql语句汇总

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

 

 

 

 

 

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值