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;