1.使用sql語句創(chuàng)建三張表:

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條數(shù)據(jù)

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

——————————

--【數(shù)據(jù)插入的順序要注意，按照下面我寫的順序插入數(shù)據(jù)等】

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

--【設(shè)置了商品名稱唯一，就不可以改成同名的內(nèi)容，有要改同名等的名稱等，就要先刪掉】

--delete from sp where spname='可口可樂'

4.修改用戶表用戶編號為5的用戶姓名為“鐘無艷”

update yonghu set yhname='鐘無艷' where yhid=5

5.修改購物車表商品訂單編號為3的數(shù)量為10

--要有訂單編號為3，這個語句才有用，所以有時用drop table dingdan

update dingdan set spbuynum=10? where did=3

6.添加鐘無艷購買可口可樂的記錄，購買數(shù)量為5

insert into dingdan(yhid,

spid,spbuynum) values((select yhid? from yonghu where yhname ='鐘無艷'),

(select spid from? sp where spname ='可口可樂'),5)

7.修改鐘無艷購買可口可樂的數(shù)量為10

update dingdan set? spbuynum=10? where yhid=(select yhid? from yonghu?

where yhname ='鐘無艷') and spid=(select spid from? sp where spname ='可口可樂')

8.修改購買數(shù)量大于5的商品為可口可樂

update dingdan set spid =(select spid from sp where spname='可口可樂') where spbuynum>5

9.修改用戶編號為3的用戶購買商品為可口可樂的數(shù)量為5

update dingdan set spbuynum=5 where spid =(select spid from sp where spname='可口可樂')? and yhid=3

10.修改商品剩余數(shù)量為0的購買數(shù)量為0

update dingdan set spbuynum=0 where spid =(select spid from sp where spshuliang=0)?

11.刪除商品可口可樂，要求級聯(lián)刪除

delete from sp where spname='可口可樂'?

12.刪除商品剩余數(shù)量大于為0的商品購買記錄

delete from sp where spshuliang>0

13.刪除用戶編號為3的用戶信息，購買記錄

delete from dingdan where yhid=3

14.聯(lián)合查詢3張表

select * from sp? ?inner join dingdan on sp.spid=dingdan.spid inner join yonghu on yonghu.yhid=dingdan.yhid

15.查詢3張表顯示購買記錄編號，購買的名稱，商品價格，購買的用戶姓名，

購買數(shù)量和單件商品總價格

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