mysql练习

create table crd_kh(
    khm int primary key auto_increment,
    khmc varchar(30) -- 客户名
);
insert crd_kh(khmc) values 
("客户1"),
("客户2"),
("客户3"),
("客户4"),
("客户5")
create table crd_ht(
    htbh int primary key auto_increment,
    htzt int, -- 交易状态
    dkje decimal(20,3), -- 合同总价
    khm int REFERENCES crdkh(khm)
);
alter table crd_ht add  dkje decimal(20,3) after htzt
-- 已签合同的数量
select count(*) from crd_ht where htzt = 1
update crd_ht set dkje = 100
-- 查询已签合同的客户，合同份数，合同总金额。
select COUNT(*) from crd_ht ht 
    left join crd_kh kh on ht.khm = kh.khm where htzt = 1
select khmc as "客户名称",count(*) as "合同数量",sum(dkje) as "合同总价" from crd_kh as kh
    left join crd_ht as ht 
    on ht.khm = kh.khm
    where htzt = 1  
    GROUP BY kh.khmc
    
    select sum(dkje) as "总价" from crd_ht
insert crd_ht(htzt,khm) values 
(1,1),
(0,1),
(1,1),
(0,2),
(1,2),
(1,3),
(0,4),
(1,5),
(0,1)