常用sql命令@oracle資料型別概括@許可權、角色、使用者的建立於使用@偽列及其注意事項

1.各種不同許可權的使用者

系統許可權:

使用者名稱:sys/system（使用者名稱預設的是 sys,system）;密碼：Oracle11g（密碼是你安裝的密碼，oracle11g的密碼必須填Oracle11g）

重置使用者密碼,這裡以scott使用者為例:

alter user scott identified by tiger;（當忘記密碼時,可以採用sqlplus sys/aaa as sysdba;--以資料庫管理員的身份登入；alter user identified by 都是關鍵字）

對SCOTT解:
     alter user scott account unlock;

scott也可以在登入的時候 用一個錯誤的密碼，會讓你重新輸入密碼 和設定密碼。

2.oracle資料庫命令操作的使用

安裝成功後進入DOS介面操作在進行以下操作時,需啟動oracle服務(oracleServiceoral),如果要在專案中連線資料庫則還需要開啟兩個服務(oracleOraDb11g_home1第二個、OracleDBConsoleorcl)；最好設定成手動的，每次使用之前開啟服務，因為oracle非常佔記憶體，如果開機啟動，不但導致開機慢還容易導致效能不好的電腦還會開機當機狀態。

1）進入oracle命令視窗有以下三種方法：

A、開始--執行—cmd（或win+r輸入cmd）回車：輸入sqlplus 回車；提示輸入正確的使用者名稱和密碼；

B、開始—>所有程式-àoracle檔案-à應用程式開發—>sqlplus；

    C、開始—>輸入sqlplus回車：提示輸入正確的使用者名稱和密碼；

2)切換使用者操作

 A、先退出 再登陸

Exit、quit：退出命令視窗；crtl+c :強制退出（可以從正在輸入狀態的命令中退出來）；sqlplus視窗會直接關閉，cmd視窗會退出。 

oracle；登入命令 cmd下-》sqlplus回車 system/Oracle11g 直接登入或者搜尋sqlplus直接輸入使用者密碼

B、也可以不退出直接切換使用者

在sql>操作中採用 conn/使用者名稱/密碼切換使用者；例：conn scott/tiger；注意：登入的時候沒有分號結束。

3）oracle 中的表是按照使用者進行分類的

   sys 超級使用者(許可權最高)

   system 資料庫管理員(執行大部分管理操作)

   自定使用者 測試使用者

3.常用oracle命令以及知識點串講

常用命令：

select * from tabs：查詢當前使用者下有哪些表(多查詢出系統表)；

select table_name from user_tables：查詢當前使用者下的所有表名

desc 表名 ：檢視錶有哪些列(表的結構)

conn system/Oracle11g：連線到使用者

create user scott identified by tiger: 建立使用者

conn scott/tiger: 切換使用者    （使用者切換失敗提示Not logged on；出現使用者沒有session許可權的提示，應當分配session許可權）

使用者與密碼：

sys/Oracle11g

scott/tiger

hr/hr

dbshop/tiger

 

設定列寬：set linesize 120

設定具體列寬：col ename for 9999（只能是9，小於位數的會用#代替當前值）

sqlplus命令視窗清屏的命令：host cls

設定deptno不重複顯示：    break on deptno skip 0;

設定deptno不重複顯示並且不同deptno欄位中間相隔2行：    break on deptno skip 2;

備份表（建立一個與emp表一樣的表）：create table emps as select * from emp;

分配許可權：

grant 許可權名 to 使用者名稱;

SQL> conn system/Oracle11g;//第一步切換到管理員

SQL> grant create session to scott; //為使用者受權

提示:Grant succeeded//受權成功

備註當受權成功後,scott就可以登入,但是登入後,當使用者建立表時,也會出現許可權不足，這就需要通過使用者的角色來給使用者分配許可權的.角色(一個角色包含多個許可權)：

connect

   create session、create table、resource（對錶空間使用許可權） ……

   grant connect,resource to scott;

注意：對用使用者的建立,許可權的分配,只有管理員才能夠操作,普通使用者不能實現.

建立使用者：

    切換到system管理員中 以system管理員進行登入

    1）、Create user 使用者名稱 identified by 密碼;(不能是全數字)  //建立使用者

    2）、grant create session,create table,resource to 使用者名稱; //給使用者分配許可權

3）、start  目錄(D:\emp.sql);//注意該檔案不能放在桌面 (因為目錄中不能包含空格否則會出現如下錯誤：

SQL> start C:\a a a\emp.sql;  Error reading file)

案例：

SQL> create user redarmychen identified by redarmy;

 //建立使用者併為使用者設定密碼為redarmy

SQL>  grant connect,resource to redarmychen;

 //給使用者授權 connect,resource

SQL> conn redarmychen/redarmy; //切換到新建立的使用者中來

SQL> start c:\emp.sql;  //匯入外部sql檔案的方式

SQL Plus常見的命令：

conn    切換連線使用者

desc    顯示錶結構

host    執行作業系統命令

start   執行檔案系統SQL語句

exit       退出

col        格式化輸出

/       執行最近一條SQL或新定義過程

startup    啟動資料庫例項 (DBA)

shutdown   關閉資料庫例項 (DBA)

startup shutdown必須是sys system使用者才能夠執行；其它使用者不能使用.並且此命令只能在sqlplus中執行.不能在客戶端工具中使用。

   資料是資訊數字表現形式,資訊的加工處理是以大量的結構化資料為載體進行的,資料庫管理系統的核心是資料庫,資料庫的主要物件是表,表是結構化資料儲存的地方.

   Oracle系統也提供了大量的資料型別主要包括兩大類:使用者自定義的資料型別、置的資料型別

資料型別：

A、字元型

字元資料型別可以用於宣告包含了多個字母數字資料的欄位。固定長度的字元型別

   char:用於儲存固定長度的字元,一旦宣告長度固定(不論你儲存的實際大小,但是大小絕對不能大於宣告的長度)長度不足時,採用空格補充。1B的預設大小,最大尺寸為2000B。

nchar與char的解析是一樣的,不過ncarh儲存的是Unicode字元資料。

可變長度的字元型別varchar與char類似,但是它是用於儲存可變的字串,而char用於儲存固定的字串。

nvarchar與varchar解析是一樣的,不過nvarchar用於儲存的資料位雙位元組的資料。

B、日期型

  date 短日期格式(1990-10-10)

  Timestamp長日期格式(1990-10-10 10:10:10)

C、數字型

Number(2),代表：宣告一個2位數字的整數。

Number(3,2)代表：宣告為3數字位,並且小數後有2位。

D、文字型-àlob資料型別

Blob：可以儲存影象,音訊檔案及視訊等檔案。

Clob：字元格式的大型物件,oracle資料àunicode格式的編碼

Bfile：用於儲存二進位制格式的檔案。

E、rowid 偽劣型別

用於在oracle內部儲存表中的每條記錄的實體地址。

4.建立表命令與過程再現

插入語句：

向表中插入一條新的(全欄位)記錄

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(1111,'test','test','7369',to_date('1992-12-12','yyyy-mm-dd'),100,100,20);

或者寫成：insert into emp values(2222,'test','test','7369',to_date('1992-12-12','yyyy-mm-dd'),100,100,20);

向表中插入一條新的(部分欄位)記錄

SQL>  insert into emp(empno,ename,hiredate,deptno) values(3333,'test',to_date('1992-12-12','yyyy-mm-dd'),30);

注意：在插入操作的時候如果有關聯,一定要插入相應關聯的欄位的值.備註：插入之後都需要手動的提交 commit;

    SQL是structured Query Language(結構化查詢語言)的縮寫。可以使用sql語句建立或刪除資料庫的物件,插入,修改和更新資料庫中的資料，並且可以對資料庫執行各種日常管理的操作。它是所有關聯式資料庫管理系統的標準語言.換句話說使用sql可以對所有的關聯式資料庫進行操作。

5. SQL按照功能分類：

資料庫定義語言

DDL（Data Definition Language）:用於建立、修改、刪除資料庫物件。

資料庫操作語言

DML（Data Manipulation Language）用於：查詢，新增修改或刪除存在資料庫物件中的資料。

資料庫控制語言

DCL(Data Control Language)用於控制訪問資料庫中特定物件的使用者、grant revoke

Oracle系統中經過對Sql語言擴充套件被稱為PL/SQL語言。

資料查詢語言

DQL:（Data Query Language） SELECT 資料查詢語言

6.集合運算子

UNION 並集 並去掉重複的行

UNION ALL 並集 不去除重複的行

MINUS  差集

INTERSECT 交集(oracle特有的)

7、偽列

1）、rowNum的偽列

SQL> select ename, rownum from emp; //rownum為查詢的結果加上序列號

 

ENAME      ROWNUM

---------- ----------

SMITH            1

ALLEN            2

WARD            3

 

SQL> select ename,rownum from emp where rownum<=5; //查詢記錄的前五條記錄

ENAME      ROWNUM

---------- ----------

SMITH            1

ALLEN            2

WARD            3

JONES            4

MARTIN          5

注意：如果把rownum用於比較條件時,只能用於小於、小於等於。

對於等於、大於 、大於等於不使用.

但有個特殊值就是大於等於1 或者等於1的時候可以。這就與rownum的產生原理有關.就是每當從磁碟拿出結果時,加上序列號。通過測試分析

測試：

SQL> select ename,rownum from emp where rownum >5;//當使用大於判斷時,沒有查詢出任何結果.

ENAME      ROWNUM

---------- ----------

分析：當從磁碟讀取的資料時,加上序列號為1,1>5不成立,讀取資料,加上序號為1(這裡很關鍵?)，>5不成立,讀取資料…….因此說1是特殊值.

RowNUM與Order By連線的問題

SQL> select ename,sal,rownum from emp  order by sal; //注意觀察 rownum的輸出結果.

 

ENAME        SAL     ROWNUM

---------- --------- ----------

test         100.00         15

SMITH      800.00          1

JAMES      950.00         12

ADAMS    1100.00         11

WARD     1250.00          3

以上說明先執行的rownum 再執行的排序.

解決方法：

SQL> select ename,sal,rownum from (select * from emp order by sal);//通過子查詢實現

ENAME        SAL     ROWNUM

---------- --------- ----------

test         100.00          1

SMITH      800.00          2

JAMES      950.00          3

……..

rowid偽列：代表資料庫表中記錄的唯一標識(也可以理解成資料庫表中該條記錄的實體地址)。

SQL> select ename,sal,rowid from emp;

ENAME        SAL ROWID

---------- --------- ------------------

SMITH      800.00 AAADVsAABAAAI1qAAA

ALLEN     1600.00 AAADVsAABAAAI1qAAB

…….

備註說明： 當使用rowid查詢時,速度最快.

SQL> select empno,rowid from emp where empno =7369;

 

     EMPNO ROWID                                                               

---------- ------------------                                                  

      7369 AAAR3sAAEAAAACXAAA 

 

select empno,rowid from emp where rowid='AAAR3sAAEAAAACXAAA';

    EMPNO ROWID                                                               

---------- ------------------                                                  

      7369 AAAR3sAAEAAAACXAAA