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 #多列汇总,其显示每个组及整个结果集的汇总值