oracle 表管理
主键自增策略
参考:
Oracle实现主键自增有4种方式
Oracle 自增长主键 三种方式
Oracle中sequence（序列）详解
mybatis+oracle数据库新增数据，返回主键，主键回显
oracle 数据类型
查询当前用户所有的表
select table_name from user_tables;
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
SYS_DICT_TYPE
SYS_DICT_DATA
SQL>
创建表
create table sys_dict_type(
id number(20) primary key,
name varchar2(100) ,
type varchar2(100) ,
group_code varchar2(100) ,
status char(1)
);
comment on table sys_dict_type is '系统字典类型表';
comment on column sys_dict_type.name is '字典名称';
comment on column sys_dict_type.type is '字典类型编码';
comment on column sys_dict_type.group_code is '字典分组 (system:系统字典)';
comment on column sys_dict_type.status is '状态 (0:正常 1:停用)';
查询表结构
desc
SQL> desc sys_dict_type;
Name Type Nullable Default Comments
---------- ------------- -------- ------- ------------------
ID NUMBER(20)
NAME VARCHAR2(100) Y 字典名称
TYPE VARCHAR2(100) Y 字典类型编码
GROUP_CODE VARCHAR2(100) Y 字典分组 (system:系统字典)
STATUS CHAR(1) Y 状态 (0:正常 1:停用)
SQL>
user_tab_columns
表名要大写
SQL> select * from user_tab_columns where table_name = 'SYS_DICT_TYPE';
TABLE_NAME COLUMN_NAME DATA_TYPE DATA_TYPE_MOD DATA_TYPE_OWNER DATA_LENGTH DATA_PRECISION DATA_SCALE NULLABLE COLUMN_ID DEFAULT_LENGTH DATA_DEFAULT NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE CHARACTER_SET_NAME CHAR_COL_DECL_LENGTH GLOBAL_STATS USER_STATS AVG_COL_LEN CHAR_LENGTH CHAR_USED V80_FMT_IMAGE DATA_UPGRADED HISTOGRAM
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------- ------------------------------ ----------- -------------- ---------- -------- ---------- -------------- -------------------------------------------------------------------------------- ------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------- ----------- ------------- ----------- -------------------------------------------- -------------------- ------------ ---------- ----------- ----------- --------- ------------- ------------- ---------------
SYS_DICT_TYPE ID NUMBER 22 20 0 N 1 NO NO 0 NO YES NONE
SYS_DICT_TYPE NAME VARCHAR2 100 Y 2 CHAR_CS 100 NO NO 100 B NO YES NONE
SYS_DICT_TYPE TYPE VARCHAR2 100 Y 3 CHAR_CS 100 NO NO 100 B NO YES NONE
SYS_DICT_TYPE GROUP_CODE VARCHAR2 100 Y 4 CHAR_CS 100 NO NO 100 B NO YES NONE
SYS_DICT_TYPE STATUS CHAR 1 Y 5 CHAR_CS 1 NO NO 1 B NO YES NONE
SQL>
all_col_columns
表名要大写
select ts.table_name, ts.column_name, ts.data_type, ts.data_length, tc.comments
from all_tab_columns ts
left join all_col_comments tc
on ts.table_name = tc.table_name
and ts.column_name = tc.column_name
where ts.table_name = 'SYS_DICT_TYPE';
查询表注释
select * from user_tab_comments where table_name = 'SYS_DICT_TYPE';
修改表
ALTERR TABLE tablename ADD (columnname datatype) ;
ALTERR TABLE tablename MODIFY (columnname datatype) ;
ALTERR TABLE tablename DROP (columnname datatype) ;
rename 表名 to 新表名;
