数据库主键、外键、表结构查询

oracle

 

查询一批记录

SELECT *  FROM (SELECT ROWNUM SN, t.* FROM 数据库名.表名 t ORDER BY ROWID) WHERE SN>5 AND SN<=10

 

查询主键

1.select   *   from   user_constraints where   table_name='表名' and   constraint_type   ='P';

2.

select
 *
from
    user_constraints con,user_cons_columns col
where
    con.constraint_name=col.constraint_name and con.constraint_type='P'
    and col.table_name='表名' 

 

查询外键

1.select   *   from   user_constraints where   table_name='表名' and   constraint_type   ='R';

2.

select
 *
from
    user_constraints con,user_cons_columns col
where
    con.constraint_name=col.constraint_name and con.constraint_type='R'
    and col.table_name='表名' 

 

查询表结构

select
*
from
    user_tab_columns utc,user_col_comments ucc
where
    utc.table_name = ucc.table_name
    and utc.column_name = ucc.column_name
    and utc.table_name = '表名'

 

查询键类型

select
 *
from
    user_constraints con,user_cons_columns col
where
    con.constraint_name=col.constraint_name
    and col.column_name='列名'
    and col.table_name='表名'

 

查询主外键

select
 col.column_name,con.constraint_type
from
 user_constraints con,user_cons_columns col
where
 con.constraint_name=col.constraint_name
 and (con.constraint_type='P' or con.constraint_type='R')
 and col.table_name='ADM_DOC_DOCUMENTS'

 

 

mysql

 

查询所有外键

select
    concat(table_name, '.', column_name) as 'foreign key',
    concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
    information_schema.key_column_usage
where
    table_schema='dsreli' and
    referenced_table_name is not null;

 

查询表结构

describe dsreli.area_main_info

 

插入一条记录

insert into SYS_PARAM_NAME(NAME,TYPE,CODE,PARAM_MODULE_IDX) select '表格分页大小',2,'tablePageSize',PARAM_MODULE_IDX from `sys_param_module` where code='viewsStyle'