Oracle 12c中SQLPlus操作使用總結

這篇文章為大家帶來了關於Oracle的相關知識，其中主要整理了12c中SQLPlus操作使用的相關問題，以及Oracle中SYS和SYSTEM用戶的區別等等內容，下面一起來看一下，希望對大家有幫助。

推薦教學：《Oracle影片教學》

1. 查看sqlplus版本號碼

（1）cmd在dos指令中查看，輸入：#sqlplus -v

（2）啟用sqlplus後，輸入：#select * from V$VERSION;

2. 進入sqlplus的方法

#（1）Windows10環境在開始選單，找到SQLPLUS點選開啟

 （2）Win鍵R鍵開啟執行視窗輸入sqlplus 

（3）Win鍵R鍵開啟執行視窗輸入cmd，再啟用sqlplus

3. Oracle中SYS與SYSTEM使用者的差異 

sys、system、sysman 這三個使用者的口令是你安裝的時候設定的那個口令。

sys as sysdba #就是以##sysdba登入

Oracle登入身分有三種：normal 普通身份，sysdba 系統管理員身分，sysoper 系統操作員身分#，每種身分對應不同的權限。

SYS用戶是超級用戶#，有##最高權限，具有SYSDBA角色，具有資料庫管理員權限，可以建立資料庫－##有create database權限。

SYSTEM使用者是管理操作員

，具有SYSOPE角色，具有資料庫操作員權限，不可以建立資料庫－沒有create database權限。 4.查看Oracle資料庫全域資料庫名稱和SID 

 查看全域資料庫名稱：

select name from v$database;

查看SID：

select instance_name from v$instance;

#5. oracle

之sql使用註解 

單行註解以--開頭，例如：

#--本句查看目前使用者名稱 user from dual; 

多行註釋，以/*開始其後面有一空格，以*/結束其前面有一空格，例如：

#/* 多行註釋，

#下面語句是查看目前使用者名稱*/

#select user from dual;

#6. 連接資料庫

語法

CONN[ECT] [{#登入##| / |代理程式}[AS {SYSOPER |SYSDBA |SYSASM}] [版本=值]]

其中登入具有以下語法：

使用者名稱[/密碼] [@connect_identifier]

其中代理具有下列語法：

代理用戶[用戶名] [/密碼] [@connect_identifier]

注意：代理中使用者名稱周圍的括號是必要的語法，而不是可選術語的指示。例如，使用密碼透過代理連接到使用者。 scotthrwelcome1連接hr[scott]/welcome1

##範例1

#這個簡單的範例以使用者身分連接到本機資料庫。 SQL*加會提示輸入使用者密碼。 SYSTEMSYSTEM

connect system

##此範例以具有特權的使用者身分連接到本機資料庫。 SQL*加會提示輸入使用者密碼。 SYSSYSDBASYS

connect sys as sysdba

#以使用者身分連接時，必須連接 。 SYSAS SYSDBA

#範例3

範例3

#此範例使用作業系統驗證的特權進行本機連線。 SYSDBA

connect / as sysdba

#範例4

此範例使用簡單的連線語法以使用者身分連線到在主機上執行的遠端資料庫。 Oracle Net 偵聽器（偵聽器）正在偵聽預設連接埠 （1521）。資料庫服務是 。 SQL*加會提示輸入使用者密碼。 salesadmindbhost.example.comsales.example.comsalesadmin

connect salesadmin@"dbhost.example.com/sales.example.com"

範例5

#########此範例與範例4相同，只是指示了服務處理程序類型。 #########

connect salesadmin@"dbhost.example.com/sales.example.com:dedicated"

############範例6######################此範例與範例4相同，只是偵聽器正在偵聽非預設連接埠號碼1522。 #########

connect salesadmin@"dbhost.example.com:1522/sales.example.com"

############範例7######################此範例與範例4相同，只是主機IP 位址替換為主機名稱。 #########

connect salesadmin@"192.0.2.5/sales.example.com"

############範例8#####################此範例使用IPv6 位址進行連接。請注意括起來的方括號。 #########

connect salesadmin@"[2001:0DB8:0:0::200C:417A]/sales.example.com"

############範例9######################此範例指定要連接到的實例，並省略資料庫服務名稱。必須已指定預設資料庫服務，否則將產生錯誤。請注意，僅指定實例時，無法指定服務處理程序類型。 #########

connect salesadmin@"dbhost.example.com//orcl"

############範例 10#############

此示例以用户身份远程连接到由网络服务名称 指定的数据库服务。SQL*加会提示输入用户密码。salesadminsales1salesadmin

connect salesadmin@sales1

例子 11

本示例使用外部身份验证远程连接到由网络服务名称 指定的数据库服务。sales1

connect /@sales1

例子 12

此示例使用权限和外部身份验证远程连接到由网络服务名称 指定的数据库服务。SYSDBAsales1

connect /@sales1 as sysdb

例子 13

此示例以用户身份远程连接到由网络服务名称 指定的数据库服务。数据库会话在版本中启动。SQL*加会提示输入用户密码。salesadminsales1rev21salesadmin

connect salesadmin@sales1 edition=rev21

例子 14

要使用用户名 HR 跨 Oracle Net 连接到 Oracle Net 别名称为 FLEETDB 的数据库，请输入

CONNECT HR@FLEETDB

例子 15

要以名为 HR 的特权用户身份连接到当前节点上的实例，请输入

CONNECT HR AS SYSDBA

例子 16

要以特权默认用户身份连接到当前节点上的实例，请输入

CONNECT / AS SYSDBA

在最后两个示例中，默认架构变为 SYS。

7.  断开用户与Oracle数据库服务器的连接

 exit 退出即可

8. 用户的操作使用 

（1）创建表空间

create tablespace 表空间的名 datafile '数据文件' size xxxm;

        数据文件：全名如d:\data.dbf，表空间的存储位置

        xxx：表空间的大小，m单位为兆(M)

【为用户指定表空间】

alter user 用户名 default tablespace 表空间名;

为用户指定临时表空间：

alter user 用户名 temporary tablespace 表空间名;

【删除空的表空间，但是不包含物理文件】

drop tablespace 表空间名;

删除空表空间，包含物理文件（数据文件）：

drop tablespace 表空间名 including datafiles;

如果其他表空间中的表有外键等约束关联到了本表空间中的表的列，就要加上CASCADE CONSTRAINTS，如：drop tablespace 表空间名 including datafiles cascade constraint

注意：如果多个用户使用相同的表空间，删除用户时不要删除表空间

（2）创建用户

create user 用户名 identified by 密码;

【创建用户同时可以指定表空间】

create user 使用者名稱identified by 密碼 default tablespace 資料表空間名稱;

#【刪除使用者及級聯關係】

#drop user 使用者名稱cascade;

指定cascade 會刪除使用者下的所有物件（包括表、視圖、主鍵、外鍵、索引等；但不會刪除預存程序、函數、包）。如果不指定則僅只刪除用戶，一般建議指定

【查詢用戶資訊】

#oracle 查看目前使用者名稱：

#show user 或select user from dual;

#oracle 查看所有使用者名稱：

#select * from all_users;

查看資料庫裡面所有使用者相關資訊（前提是你是有dba權限的帳號，如sys、system）：

select * from dba_users;

#注意SELECT查詢語句中的星號*代表所有欄位

#查看你能管理的所有使用者：

#select * from all_users;

查看目前使用者資訊：

#select * from user_users;

查詢使用者所對應的表空間：

select username,default_tablespace from dba_users;

#使用限制查詢使用者所對應的表空間：

select username, default_tablespace from dba_users where username='大寫使用者名稱';

（3）將空間指派給使用者

alert user 使用者名稱default tablespace表空間名稱;

（4）授權使用者

#Grant 權限to 使用者名稱;

建立的使用者預設沒有任何權限，需要分配權限。

不同使用者登入到同一資料庫中，可能會看到不同數量的表，擁有不同的權限。 Oracle 的權限分為系統權限和資料物件權限，總共一百多種，如果單獨對使用者授權，很麻煩，有一些使用者需要的權限是相同的，就把這些使用者歸為同一類──某種角色，透過設立一些有預定權限的角色簡化和明確授權操作，角色出現的動機也就是為了簡化權限管理，它是權限的集合。一般做法是：系統把權限賦給角色，然後把角色賦給用戶，當然也可以直接把某個權限賦給用戶。 Oracle 提供細粒度的權限，可以對資料表的某一列單獨設定權限，並且可以對某位使用者查詢某表自動增添 where 限制條件。

【權限分類】

系統權限：系統規定使用者使用資料庫的權限。 （系統權限是對使用者而言)。

###實體權限：某種權限使用者對其它使用者的表或檢視的存取權。 （是針對錶或視圖而言的）。 ###############【###系統權限分類###】################DBA: 擁有全部特權，是系統最高權限，只有DBA才可以建立資料庫結構。 ##################RESOURCE:擁有Resource權限的使用者只可以建立實體，不可以建立資料庫結構。 ##########

CONNECT:擁有Connect權限的使用者只可以登入Oracle，不可以建立實體，不可以建立資料庫結構。

【實體權限分類】

#select、update、insert、alter、index 、delete、all（all包含所有權限）、execute（執行預存程序權限）。

【角色】

#角色是一組權限的集合，將角色賦給一個用戶，這個用戶就擁有了這個角色中的所有權限。

預先定義角色是在資料庫安裝後，系統會自動建立的一些常用的角色。下介簡單的介紹一下這些預定角色。

查看所有角色：

#select * from dba_roles;

檢視角色所包含的權限可以用下列語句查詢：

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

#查看目前登入使用者擁有的所有角色和系統權限：

select * from session_privs;

（5）然後用創建的使用者登錄，登入之後建立表

conn 使用者名稱/口令;

## 9. 

建立一個表格空間進行SQL語句實作 

由system系統管理員帳號登入

（1）建立表格空間SKspace：

格式: create tablespace 表間名稱 datafile '資料檔案名稱' size 表格空間大小表空間資料檔自動擴充

create tablespace SKspace datafile 'C:\Oracle12C\MBspce1.dbf' size 20M autoextend on;

（2）建立使用者#（2）建立使用者C##suke密碼為suke123，設定預設表空間是SKspace，暫存表空間是

temp：

#格式: create user 使用者名稱identified by 密碼default tablespace 表空間表;create user C

##suke identified by suke123 default tablespace SKspace temporary tablespace temp;

########################################################## ############錯誤：公用使用者名稱或角色名稱無效###################因為此時在CDB中創建，而CDB中建立使用者必須在使用者名稱的前面加上C#或C######################################（ 3）建立的使用者預設沒有任何權限，需要分配權限。 ##################這裡為了方便，將管理員角色（dba）權限授權給######C##suke######使用者：# #################grant dba to C###############suke;############# ##########--表示把connect,resource權限授予study用戶#########

grant connect,resource to study;
    
 --表示將dba權限授予給study

# grant dba to study;

（4）現在可以用已建立的使用者（用前面建立的使用者C##suke密碼是suke123）登入：

connect C##suke/suke123;

#（5）登入後建立表格：

##CREATE TABLE job( id NUMBER(6),code VARCHAR2(10),  name VARCHAR2(10)#);

（6）為已建立（已存在）的表格插入表格資料插入單一記錄：

INSERT INTO job(id ,code,name) VALUES(1,'NO1','測試');

#（7）為已建立（已存在）的表插入表資料插多單一記錄：

#INSERT ALL 

INTO job(id,code,name) VALUES(2,'NO2','開發') 

INTO job(id,code,name ) VALUES(3,'NO3','運維')

#SELECT 1 FROM dual;

#（8）查詢表格資料：

#SELECT * FROM job where id=1;

SELECT code,name FROM job where code='NO2';

（9）查詢使用者建立的表格：

SELECT table_name FROM ALL_TABLES WHERE OWNER=' C##suke';

未選取行

開始以為是查詢語句寫錯了，左邊改右改結果還是這樣。

後來到網路上一查，原來「未選定行」這個不是錯誤，是因為資料庫中沒有和查詢語句相符的結果。

select count(table_name) from all_tables where owner='C##suke';

看來遇問題不能先入為主，我一開始以為這是語句錯誤，而老是想找出這個語句哪裡錯了。其實這不是錯誤，只是資料庫中資料的問題。

（10）查詢使用者建立的表格結構：

desc job; 

10. Oracle刪除表空間語句

（1）刪除空白的表空間，但不包含實體檔案         drop tablespace tablespace_name;

（2）删除非空表空间，但是不包含物理文件
        drop tablespace tablespace_name including contents;
（3）删除空表空间，包含物理文件
        drop tablespace tablespace_name including datafiles;
（4）删除非空表空间，包含物理文件
        drop tablespace tablespace_name including contents and datafiles;
（5）如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上         CASCADE CONSTRAINTS
        drop tablespace tablespace_name including contents and datafiles CASCADE         CONSTRAINTS;

11. 实验二ORACLE SQL*PLUS环境与查询

注1：如果你想使用如此图的EMP表可查询 实验二 Oracle SQL*PLUS环境与查询

注2：你找不到此图的EMP：Oracle设置和修改system和scott的口令，并且如何连接到system和scott模式下

使用scott内置账户登录：conn scott/口令 可以查找到此图的EMP表

注3：你发现你内置账户scott不存在 / 连接不上

此链接可以解决你的问题：Oracle数据库中scott用户不存在的解决方法

注4：你发现 注3 还是无法连接scott账户，你也不想麻烦操作了

此链接可以粗暴解决问题：重建用户scott以及内容表

-- 创建数据表
CREATE TABLE dept (
 deptno NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
 dname VARCHAR2(14) ,
 loc VARCHAR2(13) ) ;
CREATE TABLE emp (
 empno NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT );
CREATE TABLE bonus (
 enamE VARCHAR2(10) ,
 job VARCHAR2(9)  ,
 sal NUMBER,
 comm NUMBER ) ;
CREATE TABLE salgrade (
 grade NUMBER,
 losal NUMBER,
 hisal NUMBER );

-- 插入测试数据 —— dept
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
-- 插入测试数据 —— emp
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('19-04-1987','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('23-05-1987','dd-mm-yyyy')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
-- 插入测试数据 —— salgrade
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

（1）SQL*Plus的启动

继续上述实例 9 的步骤操作

进入sqlplus环境：sqlplus

输入用户名：C##suke

输入口令：suke123

（2）断开数据库连接与重新连接数据库

断开数据库连接：DISCONECT

重新连接数据库：conn 用户名/口令：conn C##suke/suke123

（3）退出SQL*Plus：exit

（4）查看当前用户名

show user;

select user from dual;

（5）查询表job表结构：desc job;

（6） 查詢表job表中的資料

##select * from job;

select id,code,name from job where id < 2;

#（7）列出緩衝區的內容：##LIST

（8）change 修正錯誤

查詢指令時候誤將code打錯成cose：select id,cose,name from job;

##補充：第

幾行出現錯誤，出入錯誤行的數字，就可以單獨顯示錯誤行程式碼。

使用change修正錯誤代碼：

change /cose/code;

重新執行：

run

（10）input 新增一行查詢條件

查詢資訊：SQL> select id,code,name from job;

#突然想排序操作，又不想重新輸入全部指令，使用input加入以id列的排序條件：

SQL> input
  2  order by id;

（11）append 給上一行結尾加上查詢條件

#繼步驟（10）看完正序後，因為強迫症想看倒敘怎麼辦？

首先列出緩衝區的內容：list

為上一行結尾新增查詢條件：append  desc

#注意：append  desc 中間是兩個空格，如果你只留一個空格，如下圖錯誤顯示

重新運行：run

#（12）del 刪除最後一行查詢條件

（12）del 刪除最後一行查詢條件

繼步驟（11）看完倒敘後，由於腦中有病，想看原始資料怎麼辦？

首先列出緩衝區的內容：

list

刪除上一行查詢條件：

del

#重新執行：

run

（13） 顯示job表中所有的資訊：

###select * from job; ############

（14）算術運算子的使用

使id 2*3顯示：select id 2*3,code,name from job;

（15）連字元的使用

將單位編號和單位名稱連接起來：select code || name codename from job;

將單位編號和單位名字中間用'-' 連接起來，並輸出'WORKS IN DEPARTMENT'：

select id,code||'-'||name codename,'work in department' from job;

（16）DISTINCT子句消除重複
由於我們這裡的資料本身沒有重複值，所以我們需要再增加一些重複資料：

#INSERT ALL 

INTO job(id,code,name) VALUES(1,'NO2','前端') 

INTO job(id,code,name) VALUES(2,'NO2','後端')

INTO job(id,code,name) VALUES(3,'NO3','物聯網')

##SELECT 1 FROM dual;

#如果列舉job表中所有單位id序號：select id from job;

#從上表可以看出單位id序號之間有重複，可以用DISTINCT子句來消除重複的：

#select distinct id from job;

#（17）排序（預設正序）

以單一欄位id 排序：select * from job order by id;

 

按多個欄位排序：如單位序號id升序，依單位名稱name降序排序：

#方法一：##回顧知識點list 和#append ##用法

#方法二：select * from job order by id,name desc;

#

（19）帶有條件的查詢
#查詢單位序號id 是2 的所有單位序號、單位編號、單位名字資訊：

SQL> select * from job where id = 2;

從job 資料表查詢單位序號大於1的單位名字：

SQL> select id,name from job where id > 1;

##複合條件查詢：查詢單位序號大於1 且單位編號是'NO3'，或單位名字是'前端'的所有單位資訊：SQL> select * from job where id > 1 and code = 'NO3' or name = '前端';

（ 20）運算子的應用

BETWEEN 的應用：查詢單位序號id 在2 到3 之間的所有單位資訊：

SQL> select * from job where id between 2 and 3; 

##IN 的應用：查詢單位編號code有'NO2'，'NO3' 二個之一的所有單位資訊：

SQL> select * from job where code in (' NO2','NO3');

LIKE 的應用：

查詢名字以'運' 字開始的所有單位資訊：SQL> select * from job where name like '運% ';

#查詢名字只有三個字符的所有單位資訊：SQL> select * from job where name like '___';

#IS NULL的應用：

查詢沒有單位名字name 的所有單位資訊：

##先新增沒有單位名字的資訊：SQL> INSERT INTO job(id,code) VALUES(1,'NO1');

查詢沒有單位名字name 的所有單位資訊：SQL> select * from job where name is null;

######（21）單一&號替代變數###################數字變數輸入：########### ########SQL> ###select * from job where &job_id;###### 輸入job_id 的值:  ###id ​​= 2#############

字串變數輸入：

#SQL> select id*10 ,code,name from job where code = '&job_code';
輸入job_code 的值:  NO3

#（22）資料型別轉換

TO_NUMBER 字串資料轉換為數字：SQL> select to_number('33') from dual;

# TO_CHAR 數字資料轉換為字符字串：SQL> select to_char(2) from job;

TO_CHAR 字串資料轉換為日期資料：

SQL> select to_char(sysdate,'yy-mm-dd hh24:mi:ss') 從 dual;

##TO_DATE 字串資料轉換為日期資料：

SQL> select to_date('2005-12-25,13:25:59' ,'yyyy-mm-dd,hh24:mi:ss') 從 dual;

（23 ）分組函數的應用

求平均值：SQL> select avg(id) from job;

# 求最小值：SQL> select min(id) from job where code = 'NO3';

求數目：

SQL> select count(*) from job where id = 2;

#注意：由於我這job單位表不適合下列查詢，所以拿別人的圖來展示

GROUP BY子句：求每個部門中的平均薪資：

#HAVING子句：查詢人數超過3人的部門中的平均薪資：

###################（24）連線############### ###再次注意：我這job單位表同樣不適合下列查詢，再次拿別人的圖來展示###################從EMP和DEPT中查詢出職工名字、工作和部門名稱：############################（25）子查詢的應用程式########## #########再次注意：我這job單位表同樣不適合下列查詢，再次拿別人的圖來展示#########

從EMP查詢出薪資最低的職工： 

#從EMP查詢出每個部門工資最低的職工：

#推薦教學：《Oracle影片教學》

以上是Oracle 12c中SQLPlus操作使用總結的詳細內容。更多資訊請關注PHP中文網其他相關文章！