oracle数据库怎么查询某个表有多少个字段
1、创建测试表，
create table test_cols(id varchar2(20),remark varchar2(20),ex_filed1 varchar2(20),ex_filed2 varchar2(20));
2、编写sql，查看系统视图，可以看到该用户下所有表的字段信息，select * from user_tab_cols；
3、编写sql，查询刚创建的表，字段信息，select * from user_tab_cols t where table_name = 'TEST_COLS';
4、编写sql，查询该表的字段数，这样对于字段较多的表，结果更明显;
select count(distinct column_name) from user_tab_cols t where table_name = 'TEST_COLS'
绛旓細瑙嗗浘dba_tab_columns,user_tab_columns,all_columns,dba_tab_clos,user_tab_cols,all_tab_cols 閮藉彲浠鏌ヨ鍒帮紝渚嬪:select * from user_tab_columns where column_name='FPQH';
绛旓細浣跨敤鍏锋湁dba鏉冮檺鐨勫笎鍙风櫥褰鏁版嵁搴 鎵ц鍛戒护"select username from dba_users"瀹樻柟鏂囨。瀵筪ba_users鐨勫畾涔夛細DBA_USERS describes all users of the database.鍥犳鍙互濡傛鏌ヨ銆
绛旓細鍙敤濡備笅鏂规硶锛1銆佺敤鎸囧畾鐢ㄦ埛鐧诲綍鎸囧畾鐨鏁版嵁搴瀹炰緥锛2銆佹墦寮sql绐楀彛锛屽啓濡備笅璇彞锛歴elect * from user_tables;3銆佹鏃讹紝鏌ヨ鍑虹殑缁撴灉锛坱able_name鍒楋級鍗充负杩欎釜瀹炰緥涓墍鏈夌殑琛ㄥ悕銆
绛旓細娣诲姞鍒楀悕鐨勫叧閿瓧锛屽苟鍗曞嚮銆愭墽琛屻戞寜閽傚彲浠ョ湅鍒癝QL璇彞鎵ц鎴愬姛鐨勬彁绀轰俊鎭紝濡備笅鍥炬墍绀恒6銆佹渶鍚庯紝鍦ㄣ怱QL宸ヤ綔琛ㄣ戠殑绐楀彛涓婏紝閫氳繃鏌ヨ琛ㄦ牸鐨勫瓧娈电被鍨嬬殑SQL璇彞锛屽彲浠ョ湅鍒拌〃鏍间腑瀛楁鐨勫瓧娈电被鍨嬩簡锛屽涓嬪浘鎵绀恒傞氳繃杩欐牱鐨勬搷浣滃氨瀛︿細浜嗗湪Oracle鏁版嵁搴涓煡璇㈣〃鏍肩殑瀛楁绫诲瀷浜嗐
绛旓細娣诲姞鍒楀悕鐨勫叧閿瓧锛屽苟鍗曞嚮銆愭墽琛屻戞寜閽傚彲浠ョ湅鍒癝QL璇彞鎵ц鎴愬姛鐨勬彁绀轰俊鎭紝濡備笅鍥炬墍绀恒6銆佹渶鍚庯紝鍦ㄣ怱QL宸ヤ綔琛ㄣ戠殑绐楀彛涓婏紝閫氳繃鏌ヨ琛ㄦ牸鐨勫瓧娈电被鍨嬬殑SQL璇彞锛屽彲浠ョ湅鍒拌〃鏍间腑瀛楁鐨勫瓧娈电被鍨嬩簡锛屽涓嬪浘鎵绀恒傞氳繃杩欐牱鐨勬搷浣滃氨瀛︿細浜嗗湪Oracle鏁版嵁搴涓煡璇㈣〃鏍肩殑瀛楁绫诲瀷浜嗐
绛旓細select TABLE_NAME from dba_tab_columns where s.column_name='AAAAABBBBCCCC';濡傛灉涓婇潰璇彞鎵ц娌℃湁缁撴灉鎴栬呭け璐ャ傚垯鎵ц select TABLE_NAME from user_tab_columns where s.column_name='AAAAABBBBCCCC';鍙﹀娉ㄦ剰AAAAABBBBCCCC杩欓噷涓瀹氳澶у啓銆
绛旓細褰撲綘闇瑕佸湪Oracle鏁版嵁搴涓鏌ユ壘琛ㄧ┖闂寸殑瀛樺偍璺緞鏃讹紝鐢变簬Oracle閫氬父杩愯鍦↙inux鐜涓紝棣栧厛纭繚浣犲凡缁忛氳繃杩滅▼缁堢宸ュ叿锛堝CRT锛夋垚鍔熻繛鎺ュ埌浜哃inux鏈嶅姟鍣ㄣ備竴鏃︾櫥褰曞埌Linux绯荤粺锛屾帴涓嬫潵鐨勬楠ゆ槸鐧诲綍鍒癘racle鏁版嵁搴撴帶鍒跺彴锛屼娇鐢ㄧ鐞嗗憳鏉冮檺锛堝sysdba锛夎繘琛屾搷浣溿傚湪SQL*Plus鍛戒护琛屼腑锛岃緭鍏ヤ互涓婼QL鏌ヨ璇彞锛歴ql...
绛旓細1銆丮ySQL鏁版嵁搴撴煡璇㈠甫鏈鏌愪釜瀛楁鐨勬墍鏈夎〃鍚嶏細SELECT * FROM information_schema.columns WHERE column_name='column_name';2銆Oracle鏁版嵁搴撴煡璇甯︽湁鏌愪釜瀛楁鐨勬墍鏈夎〃鍚嶏細SELECT column_name,table_name FROM user_tab_columns WHERE column_name='column_name';3銆丼QLServer鏁版嵁搴撴煡璇㈠甫鏈夋煇涓瓧娈电殑鎵鏈夎〃...
绛旓細鏍规嵁绱㈠紩鍙凤紝鏌ヨ琛ㄧ储寮曞瓧娈碉細select * from user_ind_columns where index_name=('绱㈠紩鍚')銆傛牴鎹储寮曞悕锛屾煡璇㈠垱寤虹储寮曠殑璇彞锛歴elect dbms_metadata.get_ddl('INDEX','绱㈠紩鍚', ['鐢ㄦ埛鍚']) from dual ; --['鐢ㄦ埛鍚']鍙渷锛岄粯璁や负鐧诲綍鐢ㄦ埛銆Oracle鏁版嵁搴浜у搧涓鸿储瀵屾帓琛屾涓婄殑鍓1000瀹跺叕鍙告墍...
绛旓細鏌ヨORACLE鏁版嵁搴鍚嶏細SQL> select name from v$database銆俓x0d\x0a鏌ョ湅瀹炰緥鍚嶏細SQL> select instance_name from v$instance銆俓x0d\x0a鏁版嵁搴撴槸涓涓崟浣嶆垨鏄竴涓簲鐢ㄩ鍩熺殑閫氱敤鏁版嵁澶勭悊绯荤粺锛屽畠瀛樺偍鐨勬槸灞炰簬浼佷笟鍜屼簨涓氶儴闂ㄣ佸洟浣撳拰涓汉鐨勬湁鍏虫暟鎹殑闆嗗悎銆傛暟鎹簱涓殑鏁版嵁鏄粠鍏ㄥ眬瑙傜偣鍑哄彂寤虹珛鐨勶紝鎸...
