Oracle基礎知識

標籤：

一、oracle安裝過程略

二、sys使用者和system使用者

(1)sys使用者是超級使用者，具有最高許可權，具有sysdba角色，有create database的許可權

預設密碼是change_oninstall

(2)system使用者是管理操作員，許可權也很大，具有sysoper角色，但沒有create database的許可權，預設密碼為manager

(3)一般對資料庫的維護,使用system使用者登入就足夠了

三、oracle的啟動

所謂的啟動是指啟動oracle的執行個體，即OracleServiceORCL,在使用java串連資料庫時必須要啟動監聽器，否則就不能夠使用JDBC。

四、oracle的資料對象

在oracle中表、視圖、預存程序、觸發器在oracle中被稱為資料對象

五、oracle管理工具

sqlplus是oracle內建的工具軟體，主要用來執行sql語句

 

 

 

一、以下是sqlplus常用命令

(1)conn[ect]

用法:conn 使用者名稱/密碼@網路服務名[as sysbda/sysoper]

當使用者是特權使用者時必須帶上as sysbda/sysoper

例如:conn system/manager

(2)disc[onnect]

中斷連線

(3)passw[ord]

修改密碼,當想要修改其他使用者密碼用sys或system登入

(4)show user

顯示目前使用者

(5)exit

中斷連線,並退出sqlplus

二、檔案操作命令

(1)start、@

說明：運行sql指令碼

如：在d: 下有a.sql這個檔案，運行下面的命令即可執行a.sql中的內容

sql>@ d:a.sql 或者

sql>start d:a.sql

(2)edit

編輯指定的sql指令碼

sql>edit d:a.sql

(3)spool

該命令可以將sqlplus螢幕上的內容輸出到制定的檔案中去

sql>spool d:b.sql

sql>select * from emp;

spl>spool off

說明：spool命令將select * from emp；的查詢結果輸出到指定位置的檔案中 然後spool off類似於IO的開啟/關閉

三、互動式命令

(1)&

可以替代變數，而該變數在執行時需要使用者輸入

sql>select * from emp where job=‘&job‘;

oracle會提示使用者輸入值

四、顯示和設定環境變數

可以用來控制輸出的各種格式，如果希望永久的儲存相關設定，可以修改glogin.sql指令碼

(1)linesize

設定顯示行的寬，預設是80個自己字元

sql>show linesize

sql>set linesize 120

(2)pagesize

設定每頁顯示的行數預設是14，用法同linesize

其他環境參數的使用也是大同小異

 

 

一、Oracle使用者管理

建立使用者,需要DBA許可權

命令：create user 【使用者名稱】identified by 【密碼】

修改密碼

命令：password 【使用者名稱】 ( 在使用者已經串連的情況下 )

注意：在給其他使用者修改密碼時  需要具有DBA的許可權或擁有alter user的系統許可權

命令：alter user 【使用者名稱】 identified by 【新密碼】

刪除使用者

一般以DBA身份去刪除使用者

如果要刪除的使用者，已經建立了表，要在刪除時加上一個參數 cascade

命令：drop user 【使用者名稱】 [cascade]

二、許可權和角色

剛剛建立完的新使用者是沒有任何許可權的，甚至連登入資料庫的許可權都沒有。這是時候使用conn 【使用者名稱】/【密碼】會提示沒有許可權。

在建立一個使用者之後還要對這個使用者進行授權操作。當然了,要使用有能力授權的使用者，如sys、system

許可權包含系統許可權和對象許可權

系統許可權：使用者對資料庫的相關許可權

對象許可權：使用者對其他使用者的資料對象操作的許可權

角色

角色是指由系統許可權集合。通常給某個使用者授予許可權時如果沒有角色存在的話，那麼需要一條一條的操作，角色的存在

就是使得授權變得很方便。通常一個角色由多個系統許可權組成。常用的角色有三個connect（7種許可權）、dba、resource（在任何錶空間建表）。

這裡只是簡單的提一下，在以後會作為一個專題進行研究。

使用grant命令給使用者分配許可權：

grant 【許可權名】 to 【使用者名稱】

分配角色：

grant 【角色名稱】 to 【使用者名稱】

收回許可權：

revoke 【許可權名】 from 【使用者名稱】

舉個例子來說明：

1、建立使用者

create user stu identified by stu；

2、使stu能夠被串連

grant create session to stu；

3、讓stu能夠在任何錶空間下建表

grant resource to stu

3、建立一個簡單的表

create table users(name varchar2(10)，age number(2));

4、插入幾條資料

insert into users values(‘houjinxin‘,22);

5、登入到scott給stu授權讓stu可以查看scott下的emp表

grant select on emp to stu;

6、登入到stu下查看emp表

select * from scott.emp;

如果這時想要更新scott.emp中的資料

update scott.emp set ename=‘ok2‘ where ename=‘ok‘;

會提示ORA-01031: 許可權不足 。因為scott只給了stu查看的權利，如果仍然想更新，要到scott下進行授權

7、登入到system下收回resource角色

revoke resource from stu;

8、登入scott下收回select 許可權

revoke select on emp from stu;

這是stu就不能再查詢scott.emp的資料了

許可權的傳遞

當希望stu使用者可以去查詢scott的emp表時，還希望stu能夠把這個許可權繼續傳給其他使用者時

如果要傳遞的是對象許可權，就加入with grant option

grant select on emp to stu with grant option

如果是系統許可權：就加上with admin option

grant connect to stu with admin option

當system給stu授權時，會給stu給其他使用者授權的能力

做個實驗來驗證下

1、登入到system使用者下，重建立立兩個使用者

create user hou identified by hou;

create user jin identified by jin;

並為hou分配connect角色

grant connect to hou with admin option;

2、登入到scott下個hou授權

grant select on emp to hou with grant option;

3、登入到hou下開始對jin授權

grant select on scott.emp to jin;

grant connect to jin;

4、登入到jin下查詢scott.emp

select * from scott.emp;

到目前位置都正常，問題來了！

如果system收回分配給hou的許可權，那麼jin的許可權會不會也被一起收回，繼續實驗。

5、登入到scott下收回hou的許可權

revoke select on emp from hou;

revoke connect from hou;

6、登入到jin下看現象

發現仍然能夠登入到jin上這說明connect角色並未被收回

而當查詢scott.emp時卻提示ORA-00942: 表或視圖不存在

這說明系統許可權和對象許可權是不同的。對於系統許可權，hou分配給jin之後不再收回，對象許可權卻隨著hou的許可權被收回也被同時收回了

 

 

使用profile 系統管理使用者口令  

profile是口令限制，資源限制的命令集合。當建立資料庫時，oracle會自動建立名稱為default的profile。當建立使用者沒有制定profile選項，那oracle就會將default分配給使用者。  

(1)帳號鎖定  

指定登入時最多可以輸入密碼的次數，也可以指定使用者鎖定的時間，以天為單位。一般用dba的身份去執行命令例如：指定stu最多隻能嘗試三次登入，鎖定時間為2天

sql>create profile lock_account limit failed_login_attempts 3 password_lock_time 2;

sql>alter user stu profile lock_account;  

(2)給賬戶解鎖  

sql>alter user stu account unlock；

(3)終止口令  

為了讓使用者定期修改密碼，可以使用終止口令的指令完成，同樣這個命令也要dba身份來操作  給stu建立一個profile檔案，要求該使用者每隔10天要修改登入密碼，寬限期2天

 sql>create profile stu limit password_life_time 10 password_grace_time 2;

 sql>alter user stu profile stu;

 解鎖方式同上

 (4)口令曆史  

如果希望使用者在修改密碼時，不能使用以前用過的密碼，可以使用口令曆史，這樣oracle就會將口令修改的資訊存放在資料字典中，這樣當使用者修改密碼時，oracle就會對新密碼與就得進行對比，如果一樣提示使用者重新輸入。

  例如:

 sql>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10  

sql>alter user stu profile password_history;

password_reuse_time 10 表示10天后口令可重複使用

 (5)刪除profile  

 drop profile password_history [cascade]

 cascade表示如果已經將profile分配給某個使用者時，仍要刪除profile，就要加上cascade

 

 

表的管理 

一、表名和列的命名規範

1.必須以字母開頭

2.長度不能超過30個字元

3.不能使用oracle的保留字

4.只能使用如下字串，A-->Z,a-->z,0-->9,$,#等

二、oracle支援的資料類型

1、字元型

char 定長，最長2000字元

例如：char(10) 儲存內容為“小韓”時，前4個字元放‘小韓’，後六位由空格補齊

優點是：效率高，查詢速率快。如身份證的欄位可以設定成char（18）。

varchar2 變長 最大4000字元（oracle推薦使用）

varchar2（10） 儲存內容為“小韓”時 oracle分配4個字元

clob（character large object） 字元型大對象

最大4G

2、數字類型

number範圍-10的38次方到10的38次方

可以是整數，也可以是小數

number（5，2）表示一個小數有5位有效數字，2位是小數

例如：定義一個範圍在-999.99-999.99的數字可以用number（5，2）

定義一個範圍在-99999-99999可以用number（5）

3、日期類型

date 包含年月日和時分秒

timestamp oracle對date類型的擴充

4、圖片類型

blob 位元據，可以存放圖片，音頻，視頻最大4G

這個類型允許我們將大檔案儲存體進資料庫，但是一般在資料庫裡，存放的應該是這些檔案的路徑，如果對安全性有要求，可以將檔案放入資料庫

三、建表語句

1）建表

sql>create table student(--表名

Idnumber(4), --學號

Namevarchar(20), --姓名

Sex char(2),--性別

birthday date);--出生日期

上面的語句足以建立一個簡單的學生表

2）向已經建立的表中添加欄位

sql>alter table student add(ClassId number(2));

3)修改欄位的長度

sql>alter table studentmodify(Name varchar2(50) );

4)修改欄位的類型/或名字（不能有資料）

sql>alter table student modify(Name char(20));

sql>alter table student rename Name to Sname;

5)刪除一個欄位（謹慎使用）

alter table student drop column Sex；

6)修改表的名字

sql>rename student to stu；

7)刪除表

drop table student；

8)查看錶結構

desc student；

四、動作表

1、添加資料，所有欄位必須都插入

insert into student values(1,‘張三‘,‘男‘,‘01-5月-05‘);

不要以為這裡寫錯了，oracle中預設的日期格式‘DD-MON-YY’ （日-月-年）

想要修改日期的預設格式可以這樣做

sql>alter session set nls_date_format=‘YYYY-MM-DD‘;

修改以後就可以用我們熟悉的格式來添加類型

insert into student values(1,‘張三‘,‘男‘,‘2000-08-31‘);

但是這裡的修改只是臨時成立的，要想永久改變日期輸入格式是需要改註冊表的，還有一個方法是使用函數，暫且略過

2、插入部分欄位,前提是未插入的欄位允許為null

insert into student(Id,Name) values(1,‘張三‘);

3、插入空值

insert into student(Id,Name,Sex) values(‘1‘,null,null);

4、查詢Name為空白的一條記錄

按照正常的邏輯，許多人會這樣做

select * from studentwhere Name=null;

但是這樣的結果是什麼都查不到,正確的方法如下

select * from student where Name is null;

查詢所有非空的就在is後面加上not

5、修改一個欄位

update student set sex=‘女’ where Id=‘1’；

6、修改多個欄位

update student set sex=‘男’,Name=‘趙四‘ whereId=‘1’；

7、修改含有null值的欄位

update student set Name=‘張三’ where Name is null；

8、刪除資料（三中方式）

1）刪除一條記錄

delete from student where Id=‘1’；

2）刪除所有記錄，表結構還在，會記錄日誌，這種刪除是可以恢複的，速度會稍慢

delete from student；

3）刪除表的結構和資料

drop table student ；

4）刪除所有記錄，表結構還在，不記錄日記，所有這種刪除無法找回資料，但是速度很快

truncate table student；

9、恢複資料

用delete from student 時資料可恢

1）首先要設定一個儲存點

savepoint sp;--sp是儲存點名稱，可以隨意起名，作用是將資料儲存在日誌中

2）刪除資料

delete from student ;

3)查詢驗證資料是否被刪掉

select * from student；

結果是肯定的，沒有資料

4)復原資料

rollback to sp；

5）在查詢驗證資料復原是否成功

select * from student；

結果還是肯定的資料回來了！

當然可以設定多個儲存點，但是如果不做處理，新的儲存點會預設覆蓋前一個儲存點

10、取消重複行

select distinct deptno,job from emp;

在查詢時select後面加上distinct即可將重複資料略去

 

 

Oracle的基本查詢

首先,介紹PL/SQL軟體中兩個命令

1.清屏命令

  clear

2.關閉/開啟顯示操作時間命令

    set timing off/on

其次,是兩個sql技巧

1.快速向資料庫中插入大量資料

insert into users(userid,username,userpass)

       select * from user;

使用這個語句的前提是表中至少要有一條資料

2.查詢所有記錄數

select count(*) from user;

需要注意的是在寫SQL 陳述式時,要注意大小寫問題

Orace的欄位不區分大小寫,實體卻區分大小寫

一、使用算數運算式

? 顯示每個僱員的年工資

可以使用列的別名

select ename "姓名",sal*12 as "年度營收" from emp;

這裡的中文最好用引號引上,盡量不要用中文

?如果計算運算式中有一個null值那麼計算結果就為null,如何處理null值?

使用nvl函數處理

select sal*13+nvl(comm,0) "年工資" ,ename from emp;

nvl(comm,0)的意思是如果comm為null,那麼按0計算,不是0按本身計算

?如何連接字串

用"||"

select ename || ‘ is a ‘ || job from emp;

二、使用where子句

？如何顯示工資高於3000的員工

select ename,sal from emp where sal >3000;

?如何尋找1982.1.1後入職的員工

select ename from emp where hiredate>‘1-1月-1982‘;

?尋找工資在2000-2500之間的員工

select ename from emp where sal>=2000 and sal <=2500;

三、如何使用like操作符

%： 表示0到多個字元

_：表示任意單個字元

？如何顯示首字母為s的員工

select ename from emp where ename like ‘s%’;

?如何顯示第三個字母為大寫O的所有員工的姓名和工資

select ename, sal from emp where ename like ‘__O%‘;

四、在where條件中使用in

?如何顯示empno為123,456,234的僱員情況

select * from emp where empno in(123,234,456);

這種查詢效率很高

五、使用is null操作符

? 如何顯示沒有上級的僱員情況

select * from emp where mgr is null;

六、使用邏輯操作符號

?查詢工資高於500或是崗位為MANAGER的僱員,同時還要滿足他們的姓名首寫字母為大寫的T

select * from emp where (sal>500 or job=‘MANAGER‘) and ename like ‘T%‘;

這裡的括弧不可以忘記,否則條件就變了,因為and的優先順序高於or

七、使用order by 子句

?如何按照工資從高到低的順序顯示僱員

select ename from emp order by sal desc;

desc 為逆序 asc為順序(預設)

?按照部門號升序而僱員工資降序排列

select * from emp order by deptno asc,sal desc;

八、使用列的別名排序

select ename ，sal*12 "年薪" from emp order by "年薪" asc;

Oracle基礎知識