oracle 表修改语句,ORACLE修改表结构语句

创建表

CREATE TABLE Student_YXY(

StudentID INT,

StudentName VARCHAR2(10),

Grade VARCHAR2(5),

CourseID INT,

CONSTRAINT PK_Student_YXY primary key(StudentID,CourseID) ,–主键

CONSTRAINT FK_JHZFXGJLITEM_JLBH foreign key(JLBH) references JHZFXGJL(JLBH)–外键

CONSTRAINT UN_MACSTRESS UNIQUE(MAC)–唯一属性

)

修改表

ALTER TABLE TABLE_NAME

ADD COLUMN_NAME

MODIFY COLUMN_NAME

DROP COLUMN_NAME

RENAME COLUMN 旧表名 TO 新表名

删除表

DROP TABLE DROP_NAME

修改主键

ALTER TABLE LP_DHD_ITEM DROP CONSTRAINTS SYS_C0098697

ALTER TABLE LP_DHD_ITEM ADD CONSTRAINTS SYS_C0098697 PRIMARY KEY(JLBH,LPNO)

添加索引

create unique index INX_HTKL on WY_HTJSKL (HTH, CODE, FDBH);

删除索引

DROP INDEX PK_SPXX_HT;

drop index INX_HTKL;

增加外键

ALTER TABLE SPXX_HT ADD CONSTRAINT SPXX_HT_FDBH FOREIGN KEY(FDBH) REFERENCES FDDEF(FDBH);

修改列的属性

ALTER TABLE LP_KC MODIFY(SPCODE VARCHAR2(20));

增加一列

ALTER TABLE LP_KC ADD KCDD VARCHAR(10)

ALTER TABLE LP_KC ADD DEPTID INT DEFAULT 0;

删除一列

ALTER TABLE STUDENTXX_YXY DROP COLUMN DJR

修改某列值

update bfpub.module_def_menu set POS=‘02’ where id=907 and MSG_ID=1013800

给列换值

UPDATE WY_WLXX SET SB=SBID

插入一行

INSERT INTO TABLE(ID,NAME) VALUES(1,‘小明’);

INSERT INTO TABLE VALUES(1,‘小明’);

删除一行

DELETE FROM TABLE WHERE ID=1;