注意下面的只是学习相应的sql语句,没有什么表之间的逻辑
--创建表空间
create tablespace market
datafile 'market.dbf'
size 100M
autoextend on next 30M maxsize unlimited;
--创建用户
create user market
identified by market
default tablespace market;
--用户授权
grant connect,resource,dba to market;
--序列
create sequence SEQ_BILL
minvalue 1
maxvalue 999999999999999999999999999
start with 1111
increment by 1
cache 20;
--删除序列
drop sequence SEQ_BILL;
--create table bill
create table BILL
(
B_ID number not null,
GOODS_NAME VARCHAR2(100) not null,
GOODS_NUM number not null,
TRADE_MONEY number not null,
UNIT number not null,
IS_Pay number not null,
P_NAME VARCHAR2(100) not null,
GOODS_info VARCHAR2(100) not null,
B_date date not null
);
--添加主键
alter table BILL
add constraint PK_b_id primary key (b_id);
--删除表
drop table provider
--删除表中的内容。
delete from provider;
--添加外键约束
alter table provider
add constraint FK_P_NAME
foreign key(P_NAME)
references BILL(P_NAME);
--删除表及表具有的约束
drop table auction cascade constraints;
--单独删除表的某个约束
alter table auctionRecord
drop constraint FK_rec_REF_user;
--创建表连带着创建相关约束
create table auction_Record (
id number(9) not null,
user_Id number(6),
auction_Id number(6) not null,
auction_Time Date default SYSDATE not null,
auction_Price number(9,2) not null,
constraint PK_AUCTIONRECORD primary key (id),
constraint FK_rec_REF_user foreign key (user_Id)
references auction_User (user_Id),
constraint FK_rec_REF_AUC foreign key (auction_Id)
references auction (auction_Id)
);
--为年龄列添加检查约束,约束年龄在15~40岁之间,包含15和40
alter table stu_info
add constraint ck_stu_age check(stu_age between 15 and 40);
--查看约束
select * from user_constraints;