oracle DDL（数据定义语言）基本语句
--创建表格
create table production(
ProductIdvarchar2(10),
ProductNamevarchar2(20),
ProductPricenumber(8,2),
Quantitynumber(10),
Categoryvarchar2(10),
Desperationvarchar2(1000),
Originvarchar2(10)
)
--添加一列
alter table production
add remark varchar2(200);
select * from production
--修改列类型
alter table production
modify remark number(2,2)
--删除一列
alter table production
drop column remark
create table categoryinfo(
CategotyIdvarchar2(10),
CategoryNamevarchar2(30)
);
alter table categoryInfo
DROP COLUMN CategotyId;
alter table categoryinfo
ADD CategoryIdvarchar2(10);
--添加主键约束
alter table production
ADD CONSTRAINTpk_Productionprimary key(ProductId);
alter table categoryinfo
ADD CONSTRAINT pk_CategoryInfo primary key(CategoryId);
--删除主键约束
alter table categoryinfo
DROP CONSTRAINT pk_CategoryInfo;
--完整练习
create table productioninfo (
Prod uctIdvarchar2(10),
ProductNamevarchar2(20),
ProductPricenumber(8,2),
Quantitynumber(10),
Categoryvarchar2(10),
Desperationvarchar2(1000),
Originvarchar2(10),
primary key(ProductId),
--添加外键
CONSTRAINT fk_pro FOREIGN KEY(Category) REFERENCES categoryinfo(CategoryId) ON DELETE CASCADE
);
alter table productioninfo
DROP CONSTRAINT fk_pro;
--修改添加外键约束
alter table productioninfo
ADD CONSTRAINT fk_pro FOREIGN KEY (Category) REFERENCES categoryinfo(CategoryId)
ON DELETE CASCADE;
select * from categoryinfo;
select * from production;
alter table production
--创建带有check条件的消费者表
create table custominfo(
CustomIdvarchar2(10),
CustomNamevarchar2(20),
CustomAgenumber(2),
Gendervarchar2(2),
Telvarchar2(11),
Addressvarchar2(100),
CONSTRAINT CHK_AGE
CHECK(CustomAge>=18 and CustomAge<=50)
);
--修改时添加check条件
alter table custominfo
ADD CONSTRAINTS chk_gender CHECK(Gender = '男' or Gender = '女');
--删除约束条件
alter table custominfo
DROP CONSTRAINS chk_gender;
--unique约束唯一约束
--订单信息表
create table orderinfo(
OrderIdvarchar2(10),
CustomIdvarchar2(10),
ProductIdvarchar2(10),
OrderDatevarchar2(10),
OrderQuantitynumber(10),
SendDatevarchar2(10),
CONSTRAINTS UNQ_ORDERID UNIQUE(OrderId)
);
--NOT NULL约束
--管理员信息表
create table managerinfo(
ManagerIdvarchar2(10),
LoginNamevarchar2(10) NOT NULL,
Passwordvarchar2(10) NOT NULL,
Namevarchar2(10),
Telnumber(11)
);
--修改notnull约束
alter table managerinfo
MODIFY LoginName Null;
