sql入门综合实战

create user 王旭@localhost identified by '123456789';
create user 乔少@localhost identified by '123456789';
create user 王老@localhost identified by '123456789';

create database ATM character set utf8

create table userinfo(
客户编号 int(10) primary key auto_increment,
开户名 char(30) not null,
身份证号 char(18) not null,
联系电话 char(11) not null,
居住地址 varchar(30) not null
)character set utf8
select * from userinfo;
drop table userinfo;
grant select on ATM.userinfo to 王旭@localhost,乔少@localhost identified by '123456789';
grant all on ATM.userinfo to 王老@localhost identified by '123456789';
insert into userinfo(开户名,身份证号,联系电话,居住地址) values('乔少','1234567890','11233455667','郑州');
insert into userinfo(开户名,身份证号,联系电话,居住地址) values('王旭','0987654321','99877655443','天津');
insert into userinfo(开户名,身份证号,联系电话,居住地址) values('王老','2345790998','12923457823','北京');
insert into userinfo(开户名,身份证号,联系电话,居住地址) values('是我','2359865765','35635774357','深圳');
insert into userinfo(开户名,身份证号,联系电话,居住地址) values('是你','1234567887','34567898765','郑州');

create table cardinfo(
卡号 char(20) primary key,
存款类型 char(30) default 'RMB',
开户日期 datetime not null,
开户金额 float(9,2) not null,
余额 float(9,2) not null,
密码 varchar(6) default '888888',
是否挂失 char(2) default '否',
客户编号 int(10) not null,
constraint aa foreign key(客户编号) references userinfo(客户编号),
constraint cc foreign key(存款类型) references deposit(存款类型号)
)character set utf8;
drop table cardinfo;
select * from cardinfo;
grant select on ATM.cardinfo to 王旭@localhost,乔少@localhost identified by '123456789';
grant all on ATM.cardinfo to 王老@localhost identified by '123456789';
insert into cardinfo(卡号,存款类型,开户日期,开户金额,余额,密码,是否挂失,客户编号)values 
('1234567890123','001','20191119','5000','5000','888888','否','1');
insert into cardinfo(卡号,存款类型,开户日期,开户金额,余额,密码,是否挂失,客户编号)values 
('3456789098765','002','20191119','3000','8000','888888','否','2');
insert into cardinfo(卡号,存款类型,开户日期,开户金额,余额,密码,是否挂失,客户编号)values 
('2345678987654','001','20191119','6000','6000','888888','否','3');
insert into cardinfo(卡号,存款类型,开户日期,开户金额,余额,密码,是否挂失,客户编号)values 
('8765434567887','001','20191119','2000','8000','888888','否','4');
insert into cardinfo(卡号,存款类型,开户日期,开户金额,余额,密码,是否挂失,客户编号)values 
('9876545678765','001','20191119','3000','7000','888888','否','5');

create table tradeinfo(
id int(5) primary key auto_increment,
交易日期 datetime,
卡号 char(20) not null,
交易类型 char(30),
交易金额 float(9,2),
备注 text,
constraint bb foreign key(卡号) references cardinfo(卡号)
)character set utf8;
drop table tradeinfo;
select * from tradeinfo;
grant select on ATM.tradeinfo to 王旭@localhost,乔少@localhost identified by '123456789';
grant all on ATM.tradeinfo to 王老@localhost identified by '123456789';
insert into tradeinfo(交易日期,卡号,交易类型,交易金额)values('20191122','1234567890123','支出','500'),
('20191123','3456789098765','收入','700'),('20191122','2345678987654','支出','500'),
('20191125','8765434567887','支出','500'),('20191126','9876545678765','收入','500');

create table deposit(
存款类型号 char(5) primary key,
存款类型名称 varchar(30) not null,
描述 varchar(300)
)character set utf8;
select * from deposit;
drop table if exists deposit;
grant select on deposit to 王旭@localhost,乔少@localhost identified by '123456789';
grant all on deposit to 王老@localhost identified by '123456789';
insert into deposit(存款类型号,存款类型名称,描述) values('001','RMB',null);
insert into deposit(存款类型号,存款类型名称,描述) values('002','MJ',null);
insert into deposit(存款类型号,存款类型名称,描述) values('003','YB',null);

#修改客户密码
update cardinfo set 密码='125677' where 卡号='1234567890123';

#办理银行卡挂失

update cardinfo set 是否挂失='是' where 卡号='1234567890123';

#统计银行总存入金额和总支出金额
create view shitu as 
select 交易类型,sum(交易金额) from tradeinfo group by 交易类型 having 交易类型='收入'
union
select 交易类型,sum(交易金额) from tradeinfo group by 交易类型 having 交易类型='支出';

select * from shitu;

#查询本周开户信息

select * from cardinfo where 开户日期>=20191118 and 开户日期<=20191124;

#查询本月交易额最高的卡号

select 卡号 from tradeinfo where 交易金额=(select max(交易金额) from tradeinfo);

#查询挂失的客户

select userinfo.开户名,cardinfo.是否挂失 from userinfo left join cardinfo on userinfo.客户编号=cardinfo.客户编号
where cardinfo.是否挂失='是';
 
#催款提醒业务

create view shitu2 as 
select sum(交易金额) from tradeinfo where 交易金额 in
(select 交易金额 from tradeinfo where 交易日期>=20191101 and 交易日期<=20191130);

select * from shitu2