mysql逻辑删除语句怎么写_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