1、修改表结构
①添加表中字段
alter table tablename
add(name varchar2(32),
password varchar2(32));
②修改表中字段
alter table tablename
modify(name varchar2(32));
③删除表中字段
alter table tablename
drop(name varchar2(32));
2、查看表结构
①在cmd模式下使用
sql>describe tablename;
Name Type Nullable Default Comments
----------------- ------------ -------- ------- --------
FID VARCHAR2(32)
FUSERNAME VARCHAR2(32) Y
FPASSWORD VARCHAR2(32) Y
FGENDER VARCHAR2(32) Y
FAGE NUMBER(2) Y
FADDRESS VARCHAR2(32) Y
FTELEPHONE VARCHAR2(32) Y
FEMAIL VARCHAR2(32) Y
FSTART_DATE DATE Y
FEND_DATE DATE Y
CHECKED NUMBER Y
FCREATOR_ID NUMBER Y
FCREATE_TIME TIMESTAMP(6) Y
FCHECKER_ID NUMBER Y
FCHECK_TIME TIMESTAMP(6) Y
FCHECKED NUMBER Y
FLAST_EDIT_TIME TIMESTAMP(6) Y
FLAST_EDITOR_ID NUMBER Y
FDELETE_USER_ID NUMBER Y
FMARK_DELETE_TIME TIMESTAMP(6) Y
FDELETED NUMBER Y
FRELID VARCHAR2(32) Y
②DBMS_METADATA.GET_DDL包(查看表的建表语句)
使用方法如下:
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','NCHAR_TST') FROM DUAL;
如果显示的结果并未完整,如下:
CREATE TABLE "SCOTT"."NCHAR_TST"
( "NAME" NCHAR(6),
"ADDR" NVARCHAR2(16
那么修改如下:
[sql] view plain copy
SQL> SET LONG 9999
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','NCHAR_TST') FROM DUAL;
则显示如下的结果:
DBMS_METADATA.GET_DDL('TABLE','NCHAR_TST')
------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."NCHAR_TST"
( "NAME" NCHAR(6),
"ADDR" NVARCHAR2(16),
"SAL" NUMBER(9,2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
①添加表中字段
alter table tablename
add(name varchar2(32),
password varchar2(32));
②修改表中字段
alter table tablename
modify(name varchar2(32));
③删除表中字段
alter table tablename
drop(name varchar2(32));
2、查看表结构
①在cmd模式下使用
sql>describe tablename;
Name Type Nullable Default Comments
----------------- ------------ -------- ------- --------
FID VARCHAR2(32)
FUSERNAME VARCHAR2(32) Y
FPASSWORD VARCHAR2(32) Y
FGENDER VARCHAR2(32) Y
FAGE NUMBER(2) Y
FADDRESS VARCHAR2(32) Y
FTELEPHONE VARCHAR2(32) Y
FEMAIL VARCHAR2(32) Y
FSTART_DATE DATE Y
FEND_DATE DATE Y
CHECKED NUMBER Y
FCREATOR_ID NUMBER Y
FCREATE_TIME TIMESTAMP(6) Y
FCHECKER_ID NUMBER Y
FCHECK_TIME TIMESTAMP(6) Y
FCHECKED NUMBER Y
FLAST_EDIT_TIME TIMESTAMP(6) Y
FLAST_EDITOR_ID NUMBER Y
FDELETE_USER_ID NUMBER Y
FMARK_DELETE_TIME TIMESTAMP(6) Y
FDELETED NUMBER Y
FRELID VARCHAR2(32) Y
②DBMS_METADATA.GET_DDL包(查看表的建表语句)
使用方法如下:
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','NCHAR_TST') FROM DUAL;
如果显示的结果并未完整,如下:
CREATE TABLE "SCOTT"."NCHAR_TST"
( "NAME" NCHAR(6),
"ADDR" NVARCHAR2(16
那么修改如下:
[sql] view plain copy
SQL> SET LONG 9999
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','NCHAR_TST') FROM DUAL;
则显示如下的结果:
DBMS_METADATA.GET_DDL('TABLE','NCHAR_TST')
------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."NCHAR_TST"
( "NAME" NCHAR(6),
"ADDR" NVARCHAR2(16),
"SAL" NUMBER(9,2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"
--以上内容来自网络--