MySQL：物理删除，逻辑删除，子查询，增删改查【诗书画唱】
1.使用sql语句创建三张表:
create table sp(
spid int primary key auto_increment,
spname varchar(100) not null unique,
spjinhuojia float default 0,
spchushoujia float default 0,
spshengchanriqi timestamp default current_timestamp,
spbaozhiqi timestamp ,
spshuliang int default 0,
spbeizhu text
)
create table yonghu(
yhid int primary key auto_increment,
yhuname varchar(100) not null unique,
yhpwd varchar(100) not null ,
yhname varchar(100) not null unique
)
create table dingdan(
did int primary key auto_increment,
yhid int,
spid int,
spbuynum int default 0,
foreign key(yhid ) references yonghu(yhid ) on delete cascade,
foreign key(spid) references sp(spid) on delete cascade
)
--drop table dingdan
2.使用insert语句给以上表分别插入10条数据
insert into sp (spname ,
spjinhuojia ,
spchushoujia ,
spshengchanriqi,
spbaozhiqi ,
spshuliang ,
spbeizhu
) values('可口可乐',1.3,2.5,'2000-07-07','2000-07-07',2,'好吃'),
('苹果1',1.3,2.5,'2000-07-07','2000-07-07',2,'好吃'),
('苹果',1.3,2.5,'2000-07-07','2000-07-07',2,'好吃')
,('苹果2',1.3,2.5,'2000-07-07','2000-07-07',2,'好吃')
,('苹果3',1.3,2.5,'2000-07-07','2000-07-07',2,'好吃')
,('苹果4',1.3,2.5,'2000-07-07','2000-07-07',2,'好吃')
,('苹果5',1.3,2.5,'2000-07-07','2000-07-07',2,'好吃')
,('苹果6',1.3,2.5,'2000-07-07','2000-07-07',2,'好吃')
,('苹果7',1.3,2.5,'2000-07-07','2000-07-07',2,'好吃')
,('苹果8',1.3,2.5,'2000-07-07','2000-07-07',2,'好吃')
,('苹果9',1.3,2.5,'2000-07-07','2000-07-07',2,'好吃')
,('苹果10',1.3,2.5,'2000-07-07','2000-07-07',2,'好吃')
————————————
常用（用于测试语句是否有用等）的语句：
select * from sp
select * from dingdan
--drop table dingdan
--drop table yonghu
--drop table sp
select * from yonghu
——————————
--【数据插入的顺序要注意，按照下面我写的顺序插入数据等】
insert into yonghu (
yhuname ,
yhpwd ,
yhname
) values
('诗书画唱用户名2','pwd','真名2')
,('诗书画唱用户名3','pwd','真名3')
,('诗书画唱用户名4','pwd','真名4')
,('诗书画唱用户名5','pwd','真名5')
,('诗书画唱用户名6','pwd','真名6'),
('诗书画唱用户名7','pwd','真名7')
,('诗书画唱用户名8','pwd','真名8')
,('诗书画唱用户名9','pwd','真名9')
,('诗书画唱用户名10','pwd','真名10')
,('诗书画唱用户名1','pwd','真名1')
insert into dingdan(
yhid,
spid,
spbuynum ) values
(1,2,3)
,(2,2,3)
,(3,2,3)
,(4,2,3)
,(5,2,3)
,(6,2,3)
,(7,2,3)
,(8,2,3)
,(9,2,3)
,(10,2,3)
3.修改商品表商品编号为3的商品名称为可口可乐
update sp set sp.spname='可口可乐' where sp.spid=3
--【设置了商品名称唯一，就不可以改成同名的内容，有要改同名等的名称等，就要先删掉】
--delete from sp where spname='可口可乐'
4.修改用户表用户编号为5的用户姓名为“钟无艳”
update yonghu set yhname='钟无艳' where yhid=5
5.修改购物车表商品订单编号为3的数量为10
--要有订单编号为3，这个语句才有用，所以有时用drop table dingdan
update dingdan set spbuynum=10 where did=3
6.添加钟无艳购买可口可乐的记录，购买数量为5
insert into dingdan(yhid,
spid,spbuynum) values((select yhid from yonghu where yhname ='钟无艳'),
(select spid from sp where spname ='可口可乐'),5)
7.修改钟无艳购买可口可乐的数量为10
update dingdan set spbuynum=10 where yhid=(select yhid from yonghu
where yhname ='钟无艳') and spid=(select spid from sp where spname ='可口可乐')
8.修改购买数量大于5的商品为可口可乐
update dingdan set spid =(select spid from sp where spname='可口可乐') where spbuynum>5
9.修改用户编号为3的用户购买商品为可口可乐的数量为5
update dingdan set spbuynum=5 where spid =(select spid from sp where spname='可口可乐') and yhid=3
10.修改商品剩余数量为0的购买数量为0
update dingdan set spbuynum=0 where spid =(select spid from sp where spshuliang=0)
11.删除商品可口可乐，要求级联删除
delete from sp where spname='可口可乐'
12.删除商品剩余数量大于为0的商品购买记录
delete from sp where spshuliang>0
13.删除用户编号为3的用户信息，购买记录
delete from dingdan where yhid=3
14.联合查询3张表
select * from sp inner join dingdan on sp.spid=dingdan.spid inner join yonghu on yonghu.yhid=dingdan.yhid
15.查询3张表显示购买记录编号，购买的名称，商品价格，购买的用户姓名，
购买数量和单件商品总价格
select did,spname, spchushoujia,yhname,spbuynum, spjinhuojia from sp
inner join dingdan on sp.spid=dingdan.spid inner join yonghu on yonghu.yhid=dingdan.yhid
