Oracle创建表

--范例：创建商品表products(pid,pname,pcount),会员购买商品表buyinfo(mid,pid,buydate)，会员--表(mid,mname,mage,msex)

--1、创建商品表products(pid,pname,pcount)
--如果商品表存在删除
drop table products purge;
--创建商品表
create table products(
pid int,
pname varchar2(200),
pcount int,
constraints pk_pid primary key(pid)
);
--添加商品表数据
insert into products(pid,pname,pcount)values(1,'shoes',30);
insert into products(pid,pname,pcount)values(2,'shirt',40);
insert into products(pid,pname,pcount)values(3,'janes',50);
--提交事务
commit;

--2、会员表(mid,mname,mage,msex)
--如果会员表存在删除
drop table member purge;
--创建会员表
create table member(
mid int,
mname varchar2(12),
mage int,
msex varchar2(8),
constraints pk_mid primary key(mid)
);
--添加会员表数据
insert into member(mid,mname,mage,msex)values(1,'jack',30,'male');
insert into member(mid,mname,mage,msex)
values(2,'rose',40,'female');
insert into member(mid,mname,mage,msex)values(3,'tom',25,'male');
--提交事务
commit;

--3、会员购买商品表buyinfo(mid,pid,buydate)
create table buyinfo(
mid int,
pid int,
buydate date default sysdate,
constraints fk_mid foreign key(mid)
references member(mid),
constraints fk_pid foreign key(pid)
references products(pid)
);

--输出表

SELECT * FROM tab;
SELECT * FROM products;
SELECT * FROM MEMBER;