--现有一个商店的数据库,记录顾客及其购物情况,由下面三个表组成:
-- ·商品product(商品号productid,商品名productname,单价unitprice,商品类别category,供应商--provider);
-- ·顾客customer(顾客号customerid,姓名name,住址location);
-- ·购买purchase(顾客号customerid,商品号productid,购买数量quantity);
-- 每个顾客可以购买多件商品,每件商品可以被多个顾客购买。属于多对多的关系。
--使用SQL语言完成下列功能:
--1、建表,在定义中要求声明如下约束:
-- (1)、每个表的主外键;
-- (2)、顾客的姓名和商品名不能为空值;
-- (3)、单价必须大于0,购买数量必须在0到20之间;
--2、往表中插入数据:
-- 商品( M01,佳洁士,8.00,牙膏,宝洁;
-- M02,高露洁,6.50,牙膏,高露洁;
-- M03,洁诺,5.00,牙膏,联合利华;
-- M04,舒肤佳,3.00,香皂,宝洁;
-- M05,夏士莲,5.00,香皂,联合利华;
-- M06,雕牌,2.50,洗衣粉,纳爱斯
-- M07,中华,3.50,牙膏,联合利华;
-- M08,汰渍,3.00,洗衣粉,宝洁;
-- M09,碧浪,4.00,洗衣粉,宝洁;)
-- 顾客( C01,Dennis,海淀;
-- C02,John,朝阳;
-- C03,Tom,东城;
-- C04,Jenny,东城;
-- C05,Rick,西城;)
-- 购买( C01,M01,3;
-- C01,M05,2;
-- C01,M08,2;
-- C02,M02,5;
-- C02,M06,4;
-- C03,M01,1;
-- C03,M05,1;
-- C03,M06,3;
-- C03,M08,1;
-- C04,M03,7;
-- C04,M04,3;
-- C05,M06,2;
-- C05,M07,8;)
--3、用SQL语句完成下列查询:
--(1)、求购买了供应商"宝洁"产品的所有顾客;
--(2)、求购买的商品包含了顾客“Dennis”所购买的所有商品的顾客(姓名);
--(3)、求牙膏卖出数量最多的供应商。
--4、将所有的牙膏商品单价增加10%。
--5、删除从未被购买的商品记录。
--1数据表的创建
--删除数据表
-- 购买
drop table purchase purge;
-- 商品
drop table product purge;
-- 顾客
drop table customer purge;
--01创建数据表:商品表
create table product(
-- 商品号
productid varchar2(5),
-- 商品名
productname varchar2(20)not null, -- 非空约束
-- 单价
unitprice number,
-- 商品类别
category varchar2(50),
-- 供应商
provider varchar2(50),
-- 主键约束
constraint pk_productid primary key(productid),
-- 检查约束
constraint ck_unitprice check (unitprice>=0)
);
-- 02顾客表
create table customer(
-- 顾客号
customerid varchar2(5),
-- 姓名
name varchar2(20)not null,
-- 住址
location varchar2(50),
--主键约束
constraint pk_customerid primary key(customerid)
);
-- 03购买表
create table purchase(
-- 顾客号
customerid varchar2(5),
-- 商品号
productid varchar2(5),
-- 购买数量
quantity number,
-- 外键约束,级联删除
constraint fk_customerid foreign key(customerid) references customer(customerid) on delete cascade,
--
constraint ck_quantity check(quantity between 0 and 20)
);
-- 整个的创建过程都是按照学习过的语法进行的,特别需要注意的是:设置为外键的字段,在主表之中必须使用主键或者是唯一约束。
-- 测试数据的编写,增加商品信息
INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M01','佳洁士',8.00,'牙膏','宝洁') ;
INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M02','高露洁',6.50,'牙膏','高露洁') ;
INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M03','洁诺',5.00,'牙膏','联合利华') ;
INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M04','舒肤佳',3.00,'香皂','宝洁') ;
INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M05','夏士莲',5.00,'香皂','联合利华') ;
INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M06','雕牌',2.50,'洗衣粉','纳爱斯') ;
INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M07','中华',3.50,'牙膏','联合利华') ;
INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M08','汰渍',3.00,'洗衣粉','宝洁') ;
INSERT INTO product(productid,productname,unitprice,category,provider) VALUES ('M09','碧浪',4.00,'洗衣粉','宝洁') ;
-- 增加用户信息
INSERT INTO customer (customerid,name,location) VALUES ('C01','Dennis','海淀') ;
INSERT INTO customer (customerid,name,location) VALUES ('C02','John','朝阳') ;
INSERT INTO customer (customerid,name,location) VALUES ('C03','Tom','东城') ;
INSERT INTO customer (customerid,name,location) VALUES ('C04','Jenny','东城') ;
INSERT INTO customer (customerid,name,location) VALUES ('C05','Rick','西城') ;
-- 增加购买记录
INSERT INTO purchase (customerid,productid,quantity) VALUES ('C01','M01',3) ;
INSERT INTO purchase (customerid,productid,quantity) VALUES ('C01','M05',2) ;
INSERT INTO purchase (customerid,productid,quantity) VALUES ('C01','M08',2) ;
INSERT INTO purchase (customerid,productid,quantity) VALUES ('C02','M02',5) ;
INSERT INTO purchase (customerid,productid,quantity) VALUES ('C02','M06',6) ;
INSERT INTO purchase (customerid,productid,quantity) VALUES ('C03','M01',1) ;
INSERT INTO purchase (customerid,productid,quantity) VALUES ('C03','M05',1) ;
INSERT INTO purchase (customerid,productid,quantity) VALUES ('C03','M06',3) ;
INSERT INTO purchase (customerid,productid,quantity) VALUES ('C03','M08',1) ;
INSERT INTO purchase (customerid,productid,quantity) VALUES ('C04','M03',7) ;
INSERT INTO purchase (customerid,productid,quantity) VALUES ('C04','M04',3) ;
INSERT INTO purchase (customerid,productid,quantity) VALUES ('C05','M06',2) ;
INSERT INTO purchase (customerid,productid,quantity) VALUES ('C05','M07',8) ;
-- 最后一定要提交事务(COMMIT),如果事务不提交,那么数据库(session)一旦关数据就消失了
commit;
--(1)、求购买了供应商"宝洁"产品的所有顾客;
-- ·确定要使用的数据表:
-- |- customer表:可以取得顾客信息;
-- |- product表:商品表中可以找到供应商信息;
-- |- purchase表:保存顾客购买商品的记录。
--第一步:找到供应商"宝洁"的所有商品编号,因为有了商品编号才可以查找到购买记录;
select productid 商品编号 from product
where provider='宝洁';
--这样就找到了:
-- M01,佳洁士,8.00,牙膏,宝洁;
-- M04,舒肤佳,3.00,香皂,宝洁;
-- M08,汰渍,3.00,洗衣粉,宝洁;
-- M09,碧浪,4.00,洗衣粉,宝洁;
<!--[endif]-->
第二步:以上的查询返回多行单列,按照要求来讲,应该在WHERE子句之中出现,所以现在又属于一个范围的匹配,那么可以使用IN进行判断,找出购买记录是为了找到顾客信息。
select customerid 顾客编号 from purchase 购买表
where productid in(
select productid --商品号
from product --商品表
where provider='宝洁');
-- 商品供应商↓ 顾客↓ 购买↓
-- M01,佳洁士,8.00,牙膏,宝洁; C01,Dennis,海淀; C01,M01,3;
-- M04,舒肤佳,3.00,香皂,宝洁; C03,Tom,东城; C01,M05,2;
-- M08,汰渍,3.00,洗衣粉,宝洁; C04,Jenny,东城; C01,M08,2;
-- M09,碧浪,4.00,洗衣粉,宝洁; C03,M01,1;
C03,M05,1;
C03,M06,3;
C03,M08,1;
C04,M03,7;
C04,M04,3;
<!--[endif]-->
第三步:以上返回了顾客的编号,直接利用WHERE子句过滤
select *
from customer -- 顾客表
where customerid in( -- 顾客编号
select customerid
from purchase -- 购买表
where productid in ( --商品号
select productid --商品编号
from product -- 商品表
where provider='宝洁'));
<!--[endif]-->
(2)、求购买的商品包含了顾客“Dennis”所购买的所有商品的顾客(姓名);
·确定要使用的数据表:
|- customer表:顾客信息;
|- purchase表:购买的商品记录。
第一步:至少需要知道“Dennis”购买了那些商品?所有的购买记录保存在了purchase表之中,而要想查购买记录,只需要知道顾客的编号即可。顾客编号通过customer表查询。
select productid 商品编号
from purchase -- 购买表
where customerid=( --顾客编号
select customerid
from customer --顾客表
where name='Dennis');
Dennis购买了如下商品:
顾客 购买 商品
C01,M01,3; M01,佳洁士,8.00,牙膏,宝洁;
顾客 C01,Dennis,海淀; C01,M08,2; M08,汰渍,3.00,洗衣粉,宝洁;
C01,M05,2; M05,夏士莲,5.00,香皂,联合利华;
<!--[endif]-->
商品product(商品号productid,商品名productname,单价unitprice,商品类别category,供应商--provider);
顾客customer(顾客号customerid,姓名name,住址location);
购买purchase(顾客号customerid,商品号productid,购买数量quantity);
范例:“C01”的购买记录:M01、M05、M08
select productid from purchase where customerid='C01';
范例:“C02”的购买记录:M02、M06
select productid from purchase where customerid='C02';
范例:“C03”的购买记录:M01、M05、M06、M08
select productid from purchase where customerid='C03';
范例:“C04”的购买记录:M03、M04
select productid from purchase where customerid='C04';
范例:“C05”的购买记录: M06、M07
select productid from purchase where customerid='C05';
问题:将3号保留,其他编号删除。于是可以借助集合操作。
通过差集的计算可以发现,能找到规律
范例:01和02顾客做差运算:
01(M01、M05、M08)
02(M02、M06)
03(M01、M05、M06、M08)
04(M03、M04)
05(M06、M07)
select productid from purchase where customerid='C01'
minus --差集:M01,M05,M08
select productid from purchase where customerid='C02';
--2------------------------------------------------------------------
select productid from purchase where customerid='C03'
minus --差集:M06
select productid from purchase where customerid='C01';
--3------------------------------------------------------------------
select productid from purchase where customerid='C01'
minus --差集:M01,M05,M08
select productid from purchase where customerid='C04';
--4------------------------------------------------------------------
select productid from purchase where customerid='C04'
minus --差集:M03,M04
select productid from purchase where customerid='C05';
--5------------------------------------------------------------------
select productid from purchase where customerid='C03'
minus --差集:M01,M05,M08
select productid from purchase where customerid='C05';
--6------------------------------------------------------------------
范例:01和03顾客做差运算(NULL)
select productid from purchase where customerid='C01'
minus --差集:没有数据
select productid from purchase where customerid='C03';
如果包含有C01的全部内容差的结果是null,前面过一个运算符,这个运算符的特点是如果有数据则查询,如果没有数据则不查询。
商品product(商品号productid,商品名productname,单价unitprice,商品类别category,供应商--provider);
顾客customer(顾客号customerid,姓名name,住址location);
select *
from customer ca
where not exists(
select p1.productid
from purchase p1
where customerid=(
select customerid
from customer
where name='Dennis')
minus
select p2.productid
from purchase p2
where customerid=ca.customerid)
and ca.name<>'Dennis';
<!--[endif]-->
(3)、求牙膏卖出数量最多的供应商。
·确定要使用的数据表:
|- product表:供应商信息;
|- product表:商品分类、以及出售的数量;
|- purcase表:销售记录。
第一步:查找出牙膏的商品编号,如果没有编号就不可能知道购买记录
商品product(商品号productid,商品名productname,单价unitprice,商品类别category,供应商--provider);
顾客customer(顾客号customerid,姓名name,住址location);
select productid from product where category='牙膏';
<!--[endif]-->
第二步:以上查询返回多行单列,想也不用想,在WHERE子句使用。根据purcase找到所有的购买过此商品的数量。
select productid,sum(quantity)
from purchase
where productid in(
select productid from product where category='牙膏')
group by productid;
<!--[endif]-->
第三步:要找的最高的商品销售的数量,所以需要进行统计函数嵌套,而一旦嵌套之后,统计查询的SELECT子句里面不允许出现任何字段
select productid ,sum(quantity)
from purchase
where productid in(
select productid from product where category='牙膏')
group by productid
having sum(quantity)=(
select max(sum(quantity))
from purchase
where productid in(
select productid from product where category='牙膏')
group by productid);
<!--[endif]-->
第四步:由于最后只是需要一个供应商的信息,只需要根据商品编号查找到供应商信息即可
select provider 供应商
from product
where productid=(
select productid
from purchase
where productid in(
select productid from product where category='牙膏')
group by productid
having sum(quantity)=(
select max(sum(quantity))
from purchase
where productid in(
select productid from product where category='牙膏')
group by productid));
<!--[endif]-->
2数据更新
1将所有的牙膏商品单价增加10%
update product set unitprice=unitprice*1.1 where category='牙膏';
查看:
select category 商品,unitprice 单价 from product where category='牙膏';
<!--[endif]-->
删除从未被购买的商品记录
第一步:找出所有购买过的商品信息
select productid 商品编号 from purchase;
第二步:使用NOT IN就可以表示未购买过的商品记录
delete from product where productid not in(select productid from purchase);