Java基礎——Oracle（四）

来源:http://www.cnblogs.com/1693977889zz/archive/2017/10/19/7696431.html
-Advertisement-
Play Games

一、Sql * plus 常用命令 1.關於登錄,連接的幾個命令 1) conn[nect] //例 conn system/manager 用法 conn 用戶名/密碼 @網路服務名 (as sysdba/sysoper) 當特權用戶登錄的時候,必須帶上 as sysdba/sysope 比如 s ...


一、Sql * plus 常用命令

1.關於登錄,連接的幾個命令

1) conn[nect] //例  conn system/manager

用法 conn 用戶名/密碼 @網路服務名 (as sysdba/sysoper)

當特權用戶登錄的時候,必須帶上 as sysdba/sysope

比如 sys用戶登錄,就必須這麼寫 conn  sys/aaaaaaa as sysdba

2) show user //當顯示當前登錄的用戶是哪一個

3) disc[onnect] 斷開連接

4) exit 斷開,退出

5) clear scr

6) alter user identified by 新密碼

2.關於文件操作的命令

1) start 和 @

運行文件中的 sql腳本

@ c:\sql; 或 start c:\sql;

2) spool

可以將sqlplus 屏幕上的內容存到文本文件中

spool c:\b.sql;

運行sql腳本

....

select * from table1

spool off;

3.互動式命令

1 & 可以替代變數,該變數在執行時,要由用戶輸入 

select * from userinfo where userId='&AAA' //在 sql developer 中也好用

4. 顯示和設置環境變數

可以用來控制各種格式,如果希望永久保存格式,可以去修改 glogin.sql角本

1）linesize 設置顯示行的寬度,預設是80字元

show linesize
set linesize 120

2）pagesize 設置每頁顯示行的數目 預設40 行

二、Oracle 中表的管理（重要內容）

1) 命名規則

oracle中表和列的命名規則

-- 必須以字母開頭(數字不行)    ,下劃線 _ 也不行

-- 長度不能超過30個字元

-- 不能使和Oracle 保留字 //比如用 level 做欄位名

-- 只能使用如下字元 'A-Z','a-z',0-9,$,# 等               

2) 數據類型

-- char 定長字元串,最大2000個字元

-- varchar2 變長字元串,最大4000個字元

-- clob(character large object ) 字元型大對象 最大4G

-- bolb 二進位數據,可以存放圖片,聲音等 最大 4G

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

-- number (8,2) 可以表示帶小數點的數

-- data 日期類型,包 含年月日,時分秒

-- timestamp 時間戳

3）關於日期類型的說明

//創建一個表
create table STUDENT
(
    ID       NUMBER(4),
    STUNAME  VARCHAR2(20),
    SEX      CHAR(2),
     BIRTHDAY DATE,
     SAL      NUMBER(5,2)
)

添加一條數據

insert into student values (1,'陳鵬飛','男','1999-09-09',9999.99)
//會報錯,文字與格式字元串不匹配
//因為這裡的  '1999-09-09' 格式不正確 oracle 預設的日期格式是 DD-MON-YY
//修改日期格式的語句
alter session set nls_date_format ='yyyy-MM-dd'

3) 刪除數據

delete from student  //刪除所有記錄,表格構還在,寫日誌,可以恢復,速度慢

drop tablue student //連表結構一起刪除

truncate table student //刪除表中的數據,表結構在,不寫日誌,速度快,無法恢復,在sqlserver 或mysql中,它會重置自增主鍵

savepoint aaa;
delete from student ;
rollback to aaa;

4) 關於查詢

查看執行sql語句用的時候

set timing on //顯示一條語句的執行時間

//例如 :
set timing on
select * from student

 結果:

 ID STUNAME              SEX BIRTHDAY        SAL
 ----- -------------------- --- ----------- -------
1 陳鵬飛               男  1999-9-9     999.99
Executed in 0.016 seconds

// 註意：嚴格區分大小寫

select * from student where stuName='AA'; 

//大寫的AA和小寫的aa 在oracle中是不同的

 mysql :預設查詢的時候是不區分大小寫的 如果就想區分大小寫  SELECT * FROM userInfo where userName= binary ('aaa')

sqlservler :預設也不區分 如果想區分, select * from userInfo where userName= 'aaa' collate Chinese_PRC_CS_AI

或 alter  column 欄位名 nvarchar(20) collate Chinese_PRC_CS_AI

//關於 null 值

-- 查看姓名,工資,工資 + 獎金 , 獎金

select ename,sal,sal+comm, comm  from scott.emp;

可以發現 , 數值和null 相加,結果也是 null

輸出結果：                  
ENAME            SAL   SAL+COMM      COMM
---------- --------- ---------- ---------
SMITH         800.00            
ALLEN        1600.00       1900    300.00
WARD         1250.00       1750    500.00
JONES        2975.00            
MARTIN       1250.00       2650   1400.00
BLAKE        2850.00           

如何處理? 使用 nvl 

select ename,sal,nve(sal,0)+nvl(comm,0), comm  from scott.emp;

查年薪 : select ename,sal,sal*12+nvl(comm,0)*12 as 年薪, comm  from scott.emp;

查工資最高的人的姓名和工資 SQL> select ename ,sal from scott.emp where sal =(select max(sal) from scott.emp);

三、Oracle 中的序列（重要內容）

序列( sequence ) 是一個計數器,它不會與特定的表關聯

通過創建 sequence 和觸發器實現表的自增主鍵,序列一般用來添充主鍵或計數

//創建序列

create sequence seq_id    //seq_id 是可以任意指定的序列的名稱
minvalue 1
start with 1
increment by 1
cache 20;   //定義存放序列的記憶體塊的大小,預設是20,實例異常關閉的時候,可能會造成數據丟失
//查看序列

select * from USER_SEQUENCES  //只能查看用戶自己的
select * from ALL_SEQUENCES  
select * from DBA_SEQUENCES 
//刪除序列
drop sequence  seq_id

思考：如何使用

當要使用序列的值的時候, 直接調用它的 nextval

比如：

seq_id.nextval //註意:後面沒有() ,它是一個屬性值

select   seq_id.nextval from dual;

使用的方式

方式一 ,不使用觸發器,在插入數據的時候,使用序列生成主鍵

insert into student (id,stuName,sex,sal) values ( seq_id.nextval,'馬苗','女',8888.88)

說明：　seq_id是序列的名稱

方式二　建立觸發器，當有數據添加的時候由觸發器使用序列生成主鍵           

create trigger TRG_TEST before insert on student
for each row
begin
select seq_id.nextval into :new.id from dual;
end ; //後面的分號不能少

創建觸發器以後,再對這個表進行inser 的時候,主鍵就會自動增長

//需要註意: 對於兩個表的自增主鍵,用同一個序列,它產生的序號會被分著用

附 hibernate  和  sequnce

根據hibernate的文檔，有兩種方式實現實體對象的主鍵自動增長。

第一種：設置ID的增長策略是sequence，同時指定sequence的名字，最好每個表建一個sequence，此種做法就如同MS-SQL,MY-SQL中的自動增長一樣，不需要創建觸發器，具體的oracle資料庫腳本及hibernate配置文件: 略 */

可以看到 在oracle 使用序列還是很麻煩的,所以建議對oracl中的主鍵,儘量不要使用自增,可以使用varchar2 ,添加的時候,用uuid java.util.UUID.randomUUID();

四、Oracle 中的用戶管理 

1、創建用戶

create user  //一般具有 dba(資料庫管理員) 的許可權才能使用

create user nicecat identified by nicecat      建立一個名為nicecat 的用戶,密碼也是nicecat

註意: 密碼必須以 字母開頭

2.刪除用戶

一般情況下,要用dab的身份去刪除用戶, 如果是別的用戶進行用戶刪除操作,要有 drop user 的許可權，自己不能刪除自已 drop user 用戶名 [cascade] 

註意：cascade 是級聯的意思,如果被刪除的用戶已經創建了表,則連他的表一起刪除

3.授權

grant connect to nicecat ; //把 connect 這個角色授給 nicecat

4.口令管理

使用 profile 管理用戶口令

profile 是口令限制,資源限制的集合,當建立資料庫時,oracle 會自動建立名為 default 的profile。當建立用戶的時候,如果我們沒指定 profile,那麼oracle 就會將 default分配給用戶。

思考：如何實現用戶鎖定

1.可以指定用戶登錄時最多可以輸入密碼的次數

2.可以指定用戶鎖定的時間(天)

3.一般會用dba的身份去執行該命令

例子: 指定nice cat 用戶,最多能嘗試3次登錄,鎖定時間為2天

1) 創建 profile 文件

create profile aaa_prifle limit failed_login_attempts 3 password_lock_time 2 ;  
//時間可以用小數

2) 把規則交給某個用戶

alter user nicecat profile aaa_prifle


//附解鎖
alter user nicecat account unlock; //必須有這個許可權的用戶才能執行

3)口令歷史

如果希望用戶在修改密碼的時候,不能使用以前10天以用過的密碼

create  profile bbb_profile limit password_reuse_time 10

4)刪除 profile

drop  profile +名稱

五、Oralcle 中的許可權管理

Oracle 中的許可權分兩類

第一類：系統許可權

系統許可權通常是針對修改數據字典,修改資料庫實例的情況進行控制,例如，創建用戶,創建表空間,控制會話,建庫,建表,建存儲過程,登錄資料庫等,描述的就是用戶對資料庫的相關許可權。

比如

-- 用戶 必須有 create table 許可權 則在自已的方案中建表

-- create any table 許可權,可以在任意的方案中建表

-- create session 要登錄資料庫,就要有這個許可權

常見的系統許可權有

create session  //連接資料庫的許可權

create table //建表

create view // 建視圖

create public synonym //建同意詞

create trigger  //建觸發器

create procedure  //建存儲過程

...

 //查詢一共有多少種系統許可權

select * from system_privilege_map   //在oracle 10g中,實測166條

==授予系統許可權

一般情況下,授予系統許可權是由dba來完成的,如果是其他的用戶,要有grant any privilge 許可權才可以

1) 創建兩個用戶   mm 馬苗   jcp

create user mm identified by mm ;
create user jcp identified by jcp;

2) 授權

grant create session,create table  to mm   with admin option    //註意 是 with "admin" option

如果在授權時,帶用  with admin option  ,則被授權的用戶,可以把這個許可權再傳給別人

3) 回收

revoke  create session, create table  from mm

用的 是system 回收了 mm 的系統許可權,能不能影響到 mm 授給 jcp的許可權呢 ? 答案是不會，對於系統許可權,在回收的時候,不會級聯回儘管。

第二類：對象許可權

訪問其他方案中的數據對象的權力,用戶可以訪問自已方案中的對象,但要訪問別人方案中的對象,則要有對應的對象許可權才。 數據對象 表,存儲過程,觸發器,序列等。常用的對象許可權有:

alter //修改(表結構)

delete

update

insert

index //建索引

references //引用

execute  //執行

...

查詢一共有多少對象許可權  select * from table_privilege_map    //約 24 種

==授予對象許可權

對象許可權即可以授予用戶,也可以授予角色,和public (public 表示數據中所有的用戶都具有些許可權) grant create session to public

如果帶用 with grant option 選項,則可以把許可權往下傳遞, 但是,要註意:    /* with grant option  不能授予角色 */

著重說明:

如果給 mm 授予了對象許可權,帶了  with grant option  選項, 如果對mm 進行對象許可權的回收,會不會影響 mm 授予其他人的許可權呢?

答案是會，對於對象許可權,在回收的時候,是級聯回收的。

//例子 scoot 用戶把emp表的所有對象許可權交給 mm ,mm 再把 該表的select 許可權交給 jcp ,然後再回收 mm 的許可權,查看jcp會不會受到影響

1) 用scott用戶登錄

conn scott/scott
grant all on emp to mm with grant option;  //把emp表的所有許可權授給mm,並且允許mm往下傳遞

2) 用 mm 登錄

conn mm/mm;
select * from scott.emp //可以了,因為有許可權了
grant select on scott.emp to jcp; //由馬苗把 許可權授給 jcp 可以

3) 用 jcp 登錄

conn jcp/jcp
select * from scott.emp  //可以.因為從mm處得到的授權
delete from scott.emp //不可以,沒有許可權

4) 用scott登錄,收回給mm許可權

revoke all on emp from mm;

這時,mm 對 emp表所有的對象許可權都會被回收,同時, jcp對該表的許可權也被回收了。

誰可以對 scott.emp 表進行對象許可權的授權 ?

sys , system , scott

==關於許可權的傳遞

希望 cat 用戶,可以查詢scott 的 dept 表,還希望他可以把這個許可權往下傳遞

===這個需求是對對象許可權的需求 加入 with grant option  //級聯回收

conn system/aaaaaaaa
grant all on scott.emp to cat with grant option

===假如系統許可權 要加入 with admin option  //不級聯回收

grant create session ,create table on 用戶名 with admin option

六、角色管理

角色: 相關許可權命令的集合

一般是由dba 創建的,或者有由有create role 系統許可權的用戶創建的。

角色有兩種：

自定義角色,預定義角色 以下幾個,是系統預定義的角色

--connect //用於連接

--dba

--resurce //可以讓用戶在任意表空間建表

問題 如果nicecat 用戶授權了 dba ,還用不用授權connect 了? 不需要因為 dba 包含 connect

== 建立角色

在建立角色的時候,可以指定驗證方式,也可以不指定

1) 建立角色(不驗證)

如果角色是共用的角色,可以採用不用驗證的方式建立

create role 角色名 not identified  
//創建一個角色 查詢所有的角色 select * from dba_roles (名字變大寫了)

2) 建立角色(數據驗證)

採用這種方式時,角色名,口令,放在資料庫中,當激活角色時,必須提供口令,在建立的時候,也要提供口令

create role 角色名 identified by 密碼

== 角色的授權

角色開始建立的時候,它是空的,必須授予相應的許可權才能使用。

給角色授權和給用戶授權差不多,但系統的 unlimited tablespace 和 對象的 with grant option 選項不能授予角色

create role super_admin not identified 
//創建角色 super_admin 現在是空的,下麵是授權
grant  create session  to super_admin with admin option  
//給角色授予系統許可權,並可以讓它進許可權傳遞
grant  select on scott.emp to super_admin
//給角色授予對象許可權,後面不可以加  with grant option

grant  select,insert,update on scott.dept to super_admin

== 把角色分配給用戶

grant 角色名 to 用戶名  //with admin option

-- 刪除角色

drop role 角色名

//如果刪除了角色,則這個角色對應的用戶將失去角色所對應的許可權

-- Oracle 中包含多少角色 

select * from dba_rolse; //30多種

 -- 顯示角色所具有系統許可權

select * from role_sys_privs where role='角色名'

-- 顯示角色所具有的對象許可權

select * from role_tab_privs where role='角色名'

 -- 顯示用戶具有哪些角色

select * from dba_role_privs where grantee ='SCOTT'

結果:

GRANTEE                        GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------
SCOTT                          RESOURCE                       NO           YES
SCOTT                          CONNECT                        NO           YES
ADMIN_OPTION  表示該用戶是不是可以把這個許可權往下傳遞


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • oracle基礎之游標的理解與使用
    關於游標，首先要知道游標的定義。 游標，是記憶體中的一款區域，用來存放select的結果集 游標用來處理從資料庫中檢索的多行記錄（使用select語句）。利用游標，程式可以逐個的處理和遍歷一次索引返回的整個記錄集。 在資料庫中，存在兩種游標： 一、顯示游標（需要明確定義） 顯示游標被用於處理返回多行數 ...
  • 集群(cluster)和高可用性(HA)的概念
    轉載自:http://www.cnblogs.com/BlackWizard2016/p/5143816.html,侵刪,只為學習所用. 1.1 什麼是集群 簡單的說，集群（cluster）就是一組電腦，它們作為一個整體向用戶提供一組網路資源。這些單個的電腦系統就是集群的節點（node）。一個理 ...
  • 資料庫基礎
    一、基礎 1、說明：創建資料庫 CREATE DATABASE database-name 2、說明：刪除資料庫 drop database 資料庫名 3、說明：備份sql server 創建 備份數據的 device USE master EXEC sp_addumpdevice 'disk',  ...
  • SQL系列學習 存儲過程&事物語法
    /*學習事物基本語法*/ /*增加課室名的唯一索引*/ALTER table class add constraint uni_ClassName unique(name) /*創建存儲過程，其中增加教師，並增加課室*/CREATE proc pro_AddClass @className varc ...
  • SQL系列學習 基礎數據
    //班主任表 CREATE TABLE [dbo].[teacher]( [id] [int] IDENTITY(1,1) NOT NULL primary key, [name] [varchar](50) NOT NULL, [sj] [datetime] default(getdate()) ...
  • Oracle學習筆記之游標詳解
    游標 游標存在意義：解決“select *”返回空、多行記錄問題，但凡select，就可能多行結果集，也就需要用游標。 游標分4步走：cursor、open、fetch、close 可能省略open、close，用for ... in ... loop ... end loop; 1、靜態游標： （ ...
  • PlateSpin備份伺服器時SQL Server的一些活動信息
    以前寫過一篇文章IO is frozen on database xxx, No user action is required“， 主要是介紹PlateSpin在伺服器層面做DR備份時，SQL Server日誌裡面有"I/O is frozen on database xxxx"以及“I/O wa... ...
  • mysql5.6快速安裝及參數詳解
    一、所需軟體 二、安裝說明 三、安裝 1、下載yum源。 官網地址：http://dev.mysql.com/downloads/repo/yum/ centos7系統: wget http://dev.mysql.com/get/mysql57-community-release-el7-7.no ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類