/*
表格的列属性:
pk: primary key 主键
nn: not null 非空
uq: unique 不能重复
bin: binary 是否/成功失败/
ai: auto increment 适用于id列，自动加1
default: 默认值
*/

• 第四章：Insert update delete
  • 4.2 插入单行
  • 4.3 插入多行
  • 4.4 插入分层行（（往多表插入数据）
  • 4.5 创建表复制
  • 4.6 更新单行
  • 4.7 更新多行
  • 4.8 update 中用子查询
  • 4.9 删除行
• 第五章 汇总数据
  • 5.1 聚合函数
  • 5.2 group by
    • 单列的group by
    • 多列的group by
  • 5.3 having 子句
  • 5.4 rollup 运算符

第四章：Insert update delete

4.2 插入单行

use sql_store;
insert into customers
values (
	default, 
	'yang', 
    'shuyi',
    '1997-01-01', 
    null,
    'sjtu',
    'shanghai',
    'C',
    '0'
    );

4.3 插入多行

insert into shippers(name)
values ('shipper1'),
	('shipper2'),
    ('shipper3');

use sql_inventory;
insert into products(name, quantity_in_stock, unit_price)
values('product1', 10, 1.95),
	('product2', 11, 1.95),
    ('product3', 12, 1.95);
    
select * from products;

4.4 插入分层行（（往多表插入数据）

/*
增加一个新订单，orders 和 order_items 两个表都要变动
*/

use sql_store;
insert into orders(customer_id,order_date, status)
values(1, '2020-11-02', 1);  # 新order id 自动生成
select last_insert_id();    # 获取新的id
insert into order_items     # 把生成的新id插入另一张表
values(last_insert_id(), 4,2,2.22),
	(last_insert_id(), 2,2,2.22),
    (last_insert_id(), 3,2,2.22);

4.5 创建表复制

create table orders_archived as #创建新表 create table xxx as
select * from orders; # 把一个表所有内容复制过去
/*
1.注意，新表和旧表的区别在于：
主键、自动递增（ai）这些标记都没有了
2.选择一个表，然后右键，truncate可以把表格所有内容清空
*/

insert into orders_archived 
select * from orders
where order_date < '2019-01-01';

4.6 更新单行

use invoicing;

update invoices
set payment_total = 10, payment_date  = '2019-01-02'
where invoice_id = 1;

4.7 更新多行

update invoices
set payment_total = invoice_total * 0.2 , payment_date  = due_date
where invoice_id in ( 3, 4 );

use sql_store;
update customers
set points = points + 50
where birth_date < '1990-01-01';

4.8 update 中用子查询

/*
注意把select子查询用括号框起来，这样首先执行子查询
最好用缩进更加清晰
*/

use invoicing;
update invoices
set payment_total = invoice_total * 0.2 , payment_date  = due_date
where invoice_id = 
			(select client_id
			from clients
			where name = 'myworks');

update invoices
set payment_total = invoice_total * 0.2 , payment_date  = due_date
where invoice_id in
			(select client_id
			from clients
			where state in ('ny','ca'));

use sql_store;
update orders
set comments = 'gold customer'
where customer_id in
			(select customer_id
            from customers
            where points > 3000);

4.9 删除行

use invoicing;
delete from invoices
where client_id in 
		(select client_id
		from clients
		where name = 'Myworks');

第五章 汇总数据

/*
第五章 汇总数据
1.聚合函数——描述统计 max min avg sum count(返回非空)
2.group by
3.having 子句

1. rollup 运算符
   */

5.1 聚合函数

use invoicing;
select max(invoice_total) as highest,
	min(invoice_total) as lowest,
    avg(invoice_total) as average,
    count(payment_date) as number_of_payment,    # null不计入
    count( * ) as number_records,    # 总行数
    count(distinct client_id)as unique_id_numers,  # 取unique
    avg(invoice_total) * count(invoice_total) as sum,
    sum(invoice_total + 1)
from invoices
where invoice_date > '2019-05-01';

select 'First half of 2019' as date_range,
	sum(invoice_total) as total_sales,
    sum(payment_total) as total_payment,
    sum(invoice_total) -  sum(payment_total) as what_we_expect
from invoices
where invoice_date between '2019-01-01' and '2019-06-30'
union
select 'Second half of 2019' as date_range,
	sum(invoice_total) as total_sales,
    sum(payment_total) as total_payment,
    sum(invoice_total) -  sum(payment_total) as what_we_expect
from invoices
where invoice_date between '2019-07-01' and '2019-12-31'
union
select 'Year 2019' as date_range,
	sum(invoice_total) as total_sales,
    sum(payment_total) as total_payment,
    sum(invoice_total) -  sum(payment_total) as what_we_expect
from invoices;

5.2 group by

单列的group by

select 
	client_id,
	sum(invoice_total) as total_sales
from invoices
group by client_id
order by total_sales desc;   # 记得orderby在groupby之后alter

多列的group by

select 
	state, city,
    sum(invoice_total) as total_sales
from invoices
join clients using (client_id)
group by state, city;

select 
	date, 
    name as payment_method,
    sum(amount) as total_payment
from payments p
join payment_methods pm
where p.payment_method = pm.payment_method_id
group by date, name
order by date;

5.3 having 子句

对于聚合函数计算之后的数据进行的筛选——having
/*
having v.s. where 不同点：

1. group by 前后用
   2.where 中的列可以不在select 里面
   having 中的列需要在select 里面
   */

select 
	client_id,
	sum(invoice_total) as total_sales,
    count(*) as numbers
from invoices
-- where total_sales > 500    # 会报错因为 total_sales 必须在groupby之后
group by client_id
having total_sales > 800;     # 分组之后的筛选


use sql_store;
select 
	c.customer_id,
    c.first_name,
    c.last_name,
	sum( unit_price * quantity) as total_pay
from customers c
join orders o using(customer_id)
join order_items using(order_id)
where state = 'va'
group by c.customer_id
having total_pay > 100;

5.4 rollup 运算符

• 末尾增加一行汇总结果
• 注意，这个运算符仅仅在mysql里有！sql server 就没法用

use invoicing ;
select
	client_id,
    sum(invoice_total) as total_sales,
    avg(invoice_total) as avg_sales
from invoices
group by client_id with rollup;