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

商品（编号，品名，进价，库存，售价，厂商编号）

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 顾客where 积分 in    (select MAX(积分)     from 顾客    )    --二select distinct 电话from 厂商where 厂商.编号 in    (select 厂商编号     from 商品     where 商品.编号 in            (select 商品编号             from 销售             group by 商品编号             having SUM(数量)>1000            )    )--三select *from 厂商where 编号 in    (select 厂商编号     from 商品     group by 厂商编号     having COUNT(*)>5    )    --四 ①嵌套查询select *from 商品where 编号 not in    (select 商品编号     from 销售    )--四 ②组合查询select * from 商品where 编号 in    (select 编号	 from 商品	 except	 select 商品编号	 from 销售    )

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