Oracle基礎命令

標籤：ant   traints   那是   intersect   基礎   修改表   off   res   運行   

　　1.create user username identified by password;//建使用者名稱和密碼oracle ,oracle

　　2.grant connect,resource,dba to username;//授權 grant connect,resource,dba,sysdba to username;

　　3.connect username/password//進入。

　　4.select table_name,column_name from user_tab_columns where table_name=‘TABLE_NAME‘;//查詢表中的表名，欄位名等等。 最後的table_name要大寫。

　　5. 如何執行指令碼SQL檔案? SQL>@PATH/filename.sql; 

　　7.查詢使用者下的所有表 select distinct table_name from user_tab_columns; ===僅顯示一列表名。

8.如何搜尋出前N條記錄？ 
select * from tablename where rownum<n；--足矣。（--是注釋用的標記）

　　9.尋找使用者下的所有表：select * from tab; --查詢該使用者下的所有表及視圖（顯示表名tname， 類型tabname和clusterid）

　　2、顯示當前串連使用者

　　SQL> show user –不能用在sql視窗 只能用在command命令視窗。

　　3、查看系統擁有哪些使用者

　　SQL> select * from all_users;

　　4、建立使用者並授權

　　SQL> create user a identified by a;(預設建在SYSTEM資料表空間下)

　　SQL> grant connect,resource to a;

　　5、串連到新使用者

　　SQL> conn a/a –或者是connect a/a

　　6、查詢目前使用者下所有對象

　　SQL> select * from tab; --table或是view

　　7、建立第一個表

　　SQL> create table a(a number);

　　8、查詢表結構

　　SQL> desc a

　　9、插入新記錄

　　SQL> insert into a values(1);

　　10、查詢記錄

　　SQL> select * from a;

　　11、更改記錄

　　SQL> update a set a=2;

　　12、刪除記錄

　　SQL> delete from a;

　　13、復原

　　SQL> roll;

　　SQL> rollback;

　　14、提交

　　SQL> commit;

　　select * from

　　(select t.*,dense_rank() over (order by cardkind) rank from cardkind t)

　　where rank = 2;

　　46. 如何在字串裡加斷行符號?

　　select ‘Welcome to visit‘||chr(10)||‘www.CSDN.NET‘ from dual ; --‘||chr(10)||’作為分行符號

　　53. 如何使select語句使查詢結果自動產生序號?

　　select rownum COL from table; --主要就是oracle中引入了rownum

　　54. 如何知道資料褲中某個表所在的tablespace?

　　select tablespace_name from user_tables where table_name=‘TEST‘; --table_name名稱要大寫。

　　select * from user_tables中有個欄位TABLESPACE_NAME，（oracle）;

　　select * from dba_segments where …;

　　55. 怎麼可以快速做一個和原表一樣的備份表?

　　create table new_table as (select * from old_table);

　　59. 請問如何修改一張表的主鍵?

　　alter table aaa drop constraint aaa_key ;

　　alter table aaa add constraint aaa_key primary key(a1,b1) ;

　　60. 改變資料檔案的大小?

　　用 ALTER DATABASE .... DATAFILE .... ;

　　手工改變資料檔案的大小，對於原來的 資料檔案有沒有損害。

　　61. 怎樣查看ORACLE中有哪些程式在運行之中？

　　查看v$session表

　　62. 怎麼可以看到資料庫有多少個tablespace?

　　select * from dba_tablespaces;

　　 
　　72. 怎樣查看哪些使用者擁有SYSDBA、SYSOPER許可權？

　　SQL>conn sys/change_on_install –登不上去

　　SQL>select * from V_$PWFILE_USERS;

76. 如何顯示當前串連使用者?

　　SHOW USER

　　77. 如何查看資料檔案放置的路徑 ?

　　col file_name format a50

　　SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id; 

　　79. 如何改變一個欄位初始定義的Check範圍？

　　SQL> alter table xxx drop constraint constraint_name;

　　之後再建立新約束:

　　SQL> alter table xxx add constraint constraint_name check(); 

　　83. 如何執行指令碼SQL檔案?

SQL>@所在的檔案路徑 /filename.sql;
例如放在E盤的根目錄下則應該是
@E:\a.sql;斷行符號就OK了。

　　84. 如何快速清空一個大表?

　　SQL>truncate table table_name;

　　85. 如何查有多少個資料庫執行個體?

　　SQL>SELECT * FROM V$INSTANCE;

　　86. 如何查詢資料庫有多少表?

　　SQL>select * from all_tables;

　　87. 如何測試SQL語句執行所用的時間?

　　SQL>set timing on ;

　　SQL>select * from tablename;

　　89. 字串的串連

　　SELECT CONCAT(COL1,COL2) FROM TABLE ;

　　SELECT COL1||COL2 FROM TABLE ;

　　90. 怎麼把select出來的結果導到一個文字檔中？

　　SQL>SPOOL C:\ABCD.TXT;

　　SQL>select * from table;

　　SQL >spool off;

　　91. 怎樣估算SQL執行的I/O數 ?

　　SQL>SET AUTOTRACE ON ;

　　SQL>SELECT * FROM TABLE;

　　OR

　　SQL>SELECT * FROM v$filestat ;

　　可以查看IO數

　　92. 如何在sqlplus下改變欄位大小?

　　alter table table_name modify (field_name varchar2(100));

　　改大行，改小不行（除非都是空的）

　　93. 如何查詢某天的資料?

select * from a where trunc(日期欄位)＝to_date(‘2003-05-02‘,‘yyyy-mm-dd‘); 
若是date型資料
insert into bsyear values(to_date(‘20130427‘,‘yyyymmdd‘));
或者是insert into bsyear values(‘27-4月-2013‘);
　　94. sql 語句如何插入全年日期？

　　create table BSYEAR (d date);

insert into BSYEAR select to_date(‘20030101‘,‘yyyymmdd‘)+rownum-1 
from all_objects where rownum <= to_char(to_date(‘20031231‘,‘yyyymmdd‘),‘ddd‘); --在表後直接插入365行資料日期。
緊急插入幾條重要的：
如何在Oracle中複製表結構和表資料
1. 複製表結構及其資料： 
create table table_name_new as select * from table_name_old 
2. 只複製表結構： 
create table table_name_new as select * from table_name_old where 1=2; 
或者： 
create table table_name_new like table_name_old 
3. 只複製表資料：
如果兩個表結構一樣：
insert into table_name_new select * from table_name_old 
如果兩個表結構不一樣：
insert into table_name_new(column1,column2...) select column1,column2... from table_name_old
建立帶主鍵的表：
create table stuInfo(stuID int primary key,stuName varchar2(20),age int);
或是不直接增加主鍵
alter table stuInfo add constraint stuInfo _PK primary key (stuID)
　　95. 如果修改表名?

　　alter table old_table_name rename to new_table_name;

　　

　　97. 如何知道使用者擁有的許可權?

　　SELECT * FROM dba_sys_privs ; --一個許可權對應一條資料，這樣對於同一個使用者就有多條資料了。

　　98. 從網上下載的ORACLE9I與市場上賣的標準版有什麼區別？

　　從功能上說沒有區別，只不過oracle公司有明文規定；從網站上下載的oracle產品不得用於商業用途，否則侵權。

　
　　101. 如何搜尋出前N條記錄？

SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno; 
Select * from a where rownum<N;

　

　　104. 如何統計兩個表的記錄總數?

　　select (select count(id) from aa)+(select count(id) from bb) 總數 from dual; --總數那是沒有單引號的，雙引號可以。

　　106. 如何在給現有的日期加上2年？(

　　select add_months(sysdate,24) from dual; -- 2015/4/27 9:28:52

　　110. tablespace 是否不能大於4G?

　　沒有限制.

　　111. 返回大於等於N的最小整數值?

　　SELECT CEIL(N) FROM DUAL;

　　112. 返回小於等於N的最小整數值?

　　SELECT FLOOR(N) FROM DUAL;

　　113. 返回當前月的最後一天?

　　SELECT LAST_DAY(SYSDATE) FROM DUAL;

　　;

　　115. 如何找資料庫表的主鍵欄位的名稱?

　　SQL>SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE=‘P‘ and table_name=‘TABLE_NAME‘; --我沒有查出來。

　　116. 兩個結果集互加的函數?

　　SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW;

　　SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW;

　　SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW;

　　117. 兩個結果集互減的函數?

　　SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW;

　　139. 如何尋找重複記錄？

　　SELECT * FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAMe WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);

　　140. 如何重複資料刪除記錄？

　　DELETE FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2);

　　162. 如何知道表在資料表空間中的儲存情況?

　　select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name=‘&tablespace_name‘ and segment_type=‘TABLE‘ group by tablespace_name,segment_name; --把&tablespace_name改成相應的資料表空間名稱。

Oracle基礎命令