#1创建一个数据库hainan_data
create database if not exists hainan_data; use hainan_data; create table contract( contract_num int, customer_id int, name varchar(10), company varchar(20), start_time date, end_time date, dp_num int, pl_mun int, all_buy int); #drop table contract;
#2创建customer表，客户ID为主键,并且插入一些自建数据
create table customer( id int primary key, name varchar(10), company varchar(20), sales varchar(10)); create table data( name varchar(10), company varchar(20), request_time date, used_num int); insert into contract values(01,1,"tom","tomA","2013-05-01","2013-06-05",10,20,30), (02,2,"jim","jimB","2013-06-01","2013-07-05",20,30,50), (03,3,"jimmy","jimmyC","2013-07-01","2013-07-31",30,40,70), (04,4,"sun","sunD","2013-07-11","2013-07-25",40,50,90), (05,5,"sunny","sunnyE","2013-07-01","2013-07-15",50,50,100), (06,6,"ann","annF","2013-07-01","2013-07-30",60,70,130);
select * from contract; insert into customer values(1,"tom","tomA","张雪君"), (2,"jim","jimB","张雪雪"), (3,"jimmy","jimmyC","严鼎文"), (4,"sun","sunD","严文文"), (5,"sunny","sunnyE","鲍海地"), (6,"ann","annF","鲍海地"); select * from customer; insert into data values("jim","jimB","2013-06-20",100), ("sun","sunD","2013-07-20",80), ("sunny","sunnyE","2013-07-01",120), ("jimmy","jimmyC","2013-07-10",110), ("tom","tomA","2013-07-20",150), ("ann","annF","2013-07-30",130); select * from data;
#3修改customer表结构，增加一个表字段：公司地址address
alter table customer add address varchar(50);
#4删除customer表
drop table customer;
#5查询customer表中所有客户的客户名称，要求：客户名称不重复
select distinct name from customer;
#6查询“张雪君”和“严鼎文”两名销售人员名下的所有客户的客户名称和公司名称
select name as 客户名称,company as 公司名称 from customer where sales in("张雪君","严鼎文");
#7查询客户id在240,367,58,233之中且合同开始时间在“2012-01-01”到“2013-07-31”之间的合同信息
select * from contract where customer_id in (2,3,5,6) and start_time between "2012-01-01" and "2013-07-31" group by contract_num;
#8查询销售人员：鲍海地名下的客户信息，并按客户id升序排列
select * from customer where sales="鲍海地" order by id;
#9查询销售人员鲍海地名下的合同总份数
select count(contract_num) from contract where customer_id in (select id from customer where sales="鲍海地");
#10查询所有客户的购买数量，并按合同编号进行分组
select id,customer.name,sum(all_buy) as 购买数量 from customer left join contract on id=customer_id group by contract_num;
#11查询合同表中，购买总量大于等于100万的客户信息
select * from customer left join contract on id=customer_id where all_buy>=100;
#12统计每个客户在2013-07-01至2013-07-31 7月份月度使用总量，要求按合同统计
select customer.name,contract_num,sum(used_num) as 月度使用量 from customer left join contract on id=customer_id left join data on customer.name=data.name where request_time between "2013-07-01" and "2013-07-31" group by contract_num;
#13向客户表中添加一条数据（客户id：1，客户名称：test，公司名称：test，销售：test）
insert into customer values (7,"test","test","test");