SQL.Cookbook 读书笔记5 元数据查询

5.1 查询test库下的所有表信息

MYSQL

SELECT *  from  information_schema.`TABLES` WHERE TABLE_SCHEMA =  ‘  test  ‘ ;

ORACLE

 select  table_name  from  all_tables  where  owner =  ‘  test  ‘ ;

5.2 查询表中列的信息

MYSQL

 SELECT   *   from  information_schema.`COLUMNS`  WHERE  TABLE_SCHEMA  =   ‘  test  ‘   AND  TABLE_NAME  =   ‘  student  ‘ ;

ORACLE

 select   *   from  all_tab_columns  where  owner  =   ‘  test  ‘   and  table_name  =   ‘  student  ‘ ;

5.3 列出表的索引

MYSQL

show  index   from  emp;

ORACLE

 select  table_name,index_name,column_name,column_position  from  sys.all_ind_columns  where  table_name  =   ‘  emp  ‘   and  table_owner  =   ‘  test  ‘ ;

5.4 列出表约束

ORACLE

 select  a.table_name,a.constraint_name,b.coulumn_name,a.constraint_type  from  all_constraints a,all_cons_columns b  where  a.table_name  =   ‘  EMP  ‘   and  a.owner  =   ‘  test  ‘   and  a.table_name  =  b.table_name  and  a. owner  =  b.owner  and  a.constraint_name  =  b.constraint_name;

MYSQL

 select  a.table_name,a.constraint_name,b.coulumn_name,a.constraint_type  from  information_schema.table_constraints a,information_schema.key_column_usage b  where  a.table_name  =   ‘  EMP  ‘   and  a.table_schema =   ‘  test  ‘   and  a.table_name  =  b.table_name  and  a. table_schema  =  b.table_schema  and  a.constraint_name  =  b.constraint_name;

 5.5 显示表结构

 desc   user ;

 

SQL.Cookbook 读书笔记5 元数据查询

标签：

查看更多关于SQL.Cookbook 读书笔记5 元数据查询的详细内容...