oracle最新sql回忆性演练1(二)
ct * from bbb;
alter table bbb drop column id;
select * from example;
select * from xxx;
--USER_TAB_COLS中记录了用户表的列信息
SELECT USER_TAB_COLS.TABLE_NAME as 表名,
USER_TAB_COLS.COLUMN_NAME as 列名,
USER_TAB_COLS.DATA_TYPE as 数据类型,
USER_TAB_COLS.DATA_LENGTH as 长度,
USER_TAB_COLS.NULLABLE as 是否为空,
USER_TAB_COLS.COLUMN_ID as 列序号,
user_col_comments.comments as 备注
FROM USER_TAB_COLS
inner join user_col_comments
on user_col_comments.TABLE_NAME = USER_TAB_COLS.TABLE_NAME
and user_col_comments.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME；
--关联到表的所有字段信息
select col.column_name,
uc.constraint_type,
case uc.constraint_type
when 'P' then
'√'
else
''
end "PrimaryKey"
from user_tab_columns col
left join user_cons_columns ucc
on ucc.table_name = col.table_name
and ucc.column_name = col.column_name
left join user_constraints uc
on uc.constraint_name = ucc.constraint_name
and uc.constraint_type = 'P'
where col.table_name = 'example';
--查询某个表中的外键字段名称、所引用表名、所应用字段名
select distinct (col.column_name), r.table_name, r.column_name
from user_constraints con,
user_cons_columns col,
(select t2.table_name, t2.column_name, t1.r_constraint_name
from user_constraints t1, user_cons_columns t2
where t1.r_constraint_name = t2.constraint_name
and t1.table_name = 'example') r
where con.constraint_name = col.constraint_name
and con.r_constraint_name = r.r_constraint_name
and con.table_name = 'example';