怎样用Access数据库做个客户信息表_sql

1、进入Access，

2、创建空白表，

3、然后添加字段

--在右侧字段模板中选择一个，双击

--选中某一列，右键，【重命名列】

--想改字段数据类型的话，上面工具条中有【数据类型】

解题思路分析：

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

--删除数据表

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','西城')