本文共 2554 字，大约阅读时间需要 8 分钟。

题目：

create table 商品(编号 bigint, 品名 nchar(30), 进价 float, 库存 int, 售价 float, 厂商编号 bigint, primary key(编号));create table 顾客(卡号 bigint, 姓名 nchar(15), 电话 bigint, 积分 float, primary key(卡号));create table 厂商(编号 bigint, 厂址 nchar(30), 名称 nchar(30), 电话 bigint, primary key(电话),);create table 销售(顾客卡号 bigint, 商品编号 bigint, 数量 int, 日期 nchar(20),);alter table 商品add constraint fksc1check(进价 between 0 and 200)alter table 商品add constraint fksc2check(售价 between 20 and 300)alter table 顾客add constraint fksc3check(积分 between 0 and 5000)alter table 商品add constraint fksc4check(库存 between 0 and 5000)alter table 销售add constraint fksc5foreign key(商品编号) references 商品(编号)insert into 商品(编号,品名,进价,库存,售价,厂商编号)values(101,'伊利牛奶',88,2000,100,3541);insert into 商品(编号,品名,进价,库存,售价,厂商编号)values(102,'蒙牛牛奶',90,2000,120,3542);insert into 商品(编号,品名,进价,库存,售价,厂商编号)values(103,'旺旺牛奶',98,2000,150,3543);insert into 商品(编号,品名,进价,库存,售价,厂商编号)values(104,'旺旺雪饼',67,2000,150,3543);insert into 商品(编号,品名,进价,库存,售价,厂商编号)values(105,'旺旺仙贝',87,2000,150,3543);insert into 商品(编号,品名,进价,库存,售价,厂商编号)values(106,'旺旺qq糖',78,2000,150,3543);insert into 商品(编号,品名,进价,库存,售价,厂商编号)values(107,'旺旺小小酥',65,2000,150,3543);insert into 商品(编号,品名,进价,库存,售价,厂商编号)values(108,'旺旺泡芙',102,2000,150,3543);insert into 顾客(卡号,姓名,电话,积分)values(1702043,'王博',13709999999,89);insert into 顾客(卡号,姓名,电话,积分)values(1702044,'李卫',15033333333,121);insert into 顾客(卡号,姓名,电话,积分)values(1702045,'李帅',18004444444,105);insert into 厂商(编号,厂址,名称,电话)values(3541,'内蒙古','伊利乳品',14888888888);insert into 厂商(编号,厂址,名称,电话)values(3542,'内蒙古','蒙牛乳品',15044555555);insert into 厂商(编号,厂址,名称,电话)values(3543,'台湾','旺旺乳品',16099999999);insert into 销售(顾客卡号,商品编号,数量,日期)values(1702043,103,1234,'20190203');insert into 销售(顾客卡号,商品编号,数量,日期)values(1702044,104,1432,'20190204');insert into 销售(顾客卡号,商品编号,数量,日期)values(1702045,105,678,'20190205');insert into 销售(顾客卡号,商品编号,数量,日期)values(1702045,106,435,'20190206');insert into 销售(顾客卡号,商品编号,数量,日期)values(1702045,107,512,'20190207');insert into 销售(顾客卡号,商品编号,数量,日期)values(1702045,102,1111,'20190208');select * from 商品select * from 顾客select * from 厂商select * from 销售delete from 商品delete from 顾客 delete from 厂商delete from 销售--一create view shopasselect 商品.品名,销售.数量from 商品,销售where 销售.商品编号 = 商品.编号select 品名,数量'销售数量'from shop    --二update 商品 set 品名='旺仔qq糖' where 编号=106update 销售 set 数量=1432where 商品编号=106select 品名,数量'销售数量'from shop--三select 品名,数量'销售数量'from shopwhere 数量 =		(		select MAX(数量)'销售数量'		from shop		)

转载地址：http://oxyki.baihongyu.com/