Orcale查询表字段、注释等

Orcale sql

1.查看表信息

select
t1.COLUMN_NAME 字段名,
t1.DATA_TYPE 数据类型,
t1.data_length 数据长度,
t2.comments 注释
from
(select t.COLUMN_NAME,t.DATA_TYPE,t.data_length from user_tab_columns t where t.TABLE_NAME=upper('ODS_XSCYSTSJ')) t1,
(select tt.column_name,tt.comments from user_col_comments tt where tt.table_name=upper('ODS_XSCYSTSJ')) t2
where t1.COLUMN_NAME=t2.column_name

2.新增列

ALTER TABLE ODS_BYQXSXSJ ADD (BH VARCHAR2(10) );
comment on column ODS_BYQXSXSJ.BH is '班号';

3.修改列

--1.修改一个列
alter table ODS_CJSTHDSJ modify ZDJSXM varchar2(180);
-- 2.修改多个列
alter table ODS_SZJZGSJ modify (ZKSXZYMC varchar(60),SSSXZYMC varchar2(60) ,BSSXZYMC varchar2(60) );

4.删除列

--  删除列
alter table ODS_LXSXBLSJ drop (JCQKQRRQ);

5.更新字段

--  更新
update a set aaa = (select aaa from b where a.id = b.id),bbb = (select bbb from b where a.id = b.id)

update   ODS_ZYSZSJ a   set  a.ZYSSYXBDWH = (select b.JW_XYDM from ODS_DIM_DW b where a.ZYSSYXBMC = b.XYMC)

Mysql sql

未完待续…