如何将购买了的产品的数量保存在数据库个人信息数量列表中

建议你按如下结构建表：

用户表：USER(id,name,tel, address)

商品表：GOODS(goodID, goodname,type)

购买表：BUY(id,goodID,goodname,buycount,price,buytime)，其中buycount表示购买数量

获取当前用户购买各个商品的数量可以通过查询实现

解题思路分析：

第一步：创建表，确定数据类型，建立约束

--删除数据表

drop table purcase

drop table product

drop table customer

---创建数据表

---解题思路分析：

---第一步：创建表，确定数据类型，建立约束

----创建商品表product

create table product (

productid varchar2(10) ,

productname varchar2(20) NOT NULL,

unitprice number,

category varchar2(20),

provider varchar2(20),

CONSTRAINT pk_productid primary key (productid),

CONSTRAINT CK_unitprice CHECK (unitprice>0)

)

--创建顾客表customer：

create table customer(

customerid varchar2(10),

name varchar2(20) NOT NULL,

location varchar2(20),

CONSTRAINT pk_customerid primary key(customerid)

)

--创建购买记录表 purcase：

create table purcase(

customerid varchar2(10),

productid varchar2(10),

quantity number,

CONSTRAINT FK_customerid FOREIGN KEY(customerid) REFERENCES customer(customerid) on delete cascade,

CONSTRAINT FK_productid FOREIGN KEY(productid) REFERENCES product(productid) 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','雕牌',8.00,'洗衣粉','纳爱斯')

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 purcase(customerid,productid,quantity)

values('C01','M01',3)

insert into purcase(customerid,productid,quantity)

values('C01','M05',2)

insert into purcase(customerid,productid,quantity)

values('C01','M08',2)

insert into purcase(customerid,productid,quantity)

values('C02','M02',5)

insert into purcase(customerid,productid,quantity)

values('C02','M06',4)

insert into purcase(customerid,productid,quantity)

values('C03','M01',1)

insert into purcase(customerid,productid,quantity)

values('C03','M05',1)

insert into purcase(customerid,productid,quantity)

values('C03','M06',3)

insert into purcase(customerid,productid,quantity)

values('C03','M08',1)

insert into purcase(customerid,productid,quantity)

values('C04','M03',7)

insert into purcase(customerid,productid,quantity)

values('C04','M04',3)

insert into purcase(customerid,productid,quantity)

values('C05','M06',2)

insert into purcase(customerid,productid,quantity)

values('C05','M07',8)

---提交事务

commit；

---问题分析

--（1）求购买了供应商"宝洁"产品的所有顾客；

1、确定要使用的表

product 表：供应商信息

customer表：顾客信息

purcase表：顾客的购买记录

2、确定关联关系

purcase.customerid=customer.customerid

purcase.productid=customer.productid

CREATE TABLE

表名＿客户表

（

列名1 类型 是否为空，

列名2 类型 是否为空，

列名3 类型 是否为空

）

例如：

Create table 客户表

（

姓名 char(16) NOT NULL,

电话 char(11) NOT NULL,

)

注意：在建表前先要选择数据库，

---