SQL学习 DAY3

3.1 用sql更新数据

#更改单行里的数据
update invoices
set payment_total=10,payment_date="2019-03-01"
                 #=invoice_total*0.5,payment_date=due_date #计算50%的发票数据 且数据类型与payment_date的一样
where invoice_id =1
#更新多行数据 
update invoices
set 
   payment_total=invoice_total*0.5,payment_date=due_date
where client_id in (3,5)
#eg 更新client_id为3 5的所有客户数据;若更新所有数据则省略这行

3.2 在update中使用子查询

update invoices
set 
  payment_total=invoice_total*0.5
  payment_date=due_date
where client_id=2#括号里面的内容【子查询】与2等价
     (use sql_invoicing;
      select client_id
       from clients
       where name="Myworks" )#查询cname="Myworks"的client_id 是什么【=2】
#如果选择多行的则为 where name in ("Myworks","yadel");此时,client_id=2换为client_id in
##例题:两个表customers、orders,有共同的customer_id
#给customers中points>3000的orders的comments添加gold_customer评论
use sql_store;
update orders
set 
  comments="gold_customer"
where customer_id in 
                  (select customer_id from customers  where points>3000)
#总结:先写子查询(); update+更改数据的表

 3.3删除表

#删除表
#删除invoices中invoice_id=1
delete from invoices
where invoice_id=1
#删除名为Myworks客户的所有发票
delete from invoices
where client_id=(
      select * from clients where name = "Myworks")#找到名为Myworks的客户

第五章 聚类函数汇总数据

5.1 聚合函数汇总数据【max min...]

##在sql中使用聚类函数
use sql_invoicing;
select max(invoice_total) as highest,
       min(invoice_total) as lowest,
       avg(invoice_total *1.1) as average,
       count(payment_date) as count_of_payments,##空值不会被计算进去
       count(client_id) as total_records,####空值被计算进去 含重复数,(*):算总行数; 
	   count( distinct client_id) as total_records
from invoices
where invoice_date>"2019-07-01" #如果有条件计算 用where
#例题
select
     "first_half_of_2019" as date_range, 
     sum(invoice_total) as total_sales,
     sum(payment_total) as total_payments,
     sum(invoice_total-payment_total) as what_we_expect
from invoices
where invoice_date <="2019-06-01"

 5.2 数据分组group by

###一列数据分组
select client_id,
  sum(invoice_total) as total_sales
from invoices
where invoice_date >= "2019-07-01" #加上限制条件
group by client_id #分组 在from/where字句之后
order by total_sales desc #降序排列 【调整排列顺序】

###多列数据分组
#分组数据 查看每个state city的总销售
select state,city,
  sum(invoice_total) as total_sales
from invoices  
join clients using (client_id)
group by state,city

(1)一列和多列分组数据区别

 5.3 分组筛选数据

select client_id,
      sum(invoice_total) as total_sales
from invoices
group by client_id
#只要分组后total_sales大于500的 不能在from 和group by之间加入where 限制条件 因为它是先执行限制条件再分组的
having total_sales>500 #多个筛选条件用and连接 ..>500 and ..>5

 having 和where的区别

(1) having 分组后筛选数据;where分组前筛选数据

(2) having只能用select中有的列,where能用任何列

5.4 汇总数据

select 
  state,
  city,
  sum(invoice_total) as total_sales
from invoices 
join clients  using(client_id)
group by state,city with rollup #多列汇总,其显示每个组及整个结果集的汇总值

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值