ORACLE基礎知識

ORACLE基礎知識

一：oracle資料庫的連結與退出：

#sqlplus /nolog    連接配接資料庫，但沒有使用賬号登入
>conn oracleuser/123456    使用oracleuser使用者登入
#sqlplus / as sysdba    以資料庫管理者的身份登入
>disconnect        退出目前使用者
>exit            退出資料庫      

二：建立使用者、賦權、建立表空間：

SQL> create tablespace TBspace datafile '/root/TBspace.dbf' size 30m;      

        建立表空間TBspace，大小30m，位置/root/TBspace.dbf。

SQL> create temporary tablespace TMPTBspace tempfile '/root/TBspace.dbf' size 30m;      

       建立臨時表間。臨時表空間的主要用途是在資料庫進行排序運算、管理索引、通路試圖等操作時提供臨時的運算空間，當運算完成之後系統會自動清理臨時表空間。

>create user USER identified by 123456 default tablespace TBspace|temporary tablespace TMPTBspace;      

          建立使用者USER，密碼123456，表空間為TBspace（或臨時表空間為TMPTBspace）    

SQL>drop user USER;                删除使用者USER
SQL>grant connect dba to USER;      向USER使用者賦予連接配接和管理者角色
SQL>select * from all_user;        檢視所有使用者
SQL>select * from all_sys_privs;    檢視所有系統權限
SQL>select * from all_tab_privs;    檢視所有對象權限
SQL>select * from dba_roles;        檢視所有角色
SQL>select * from user_role_privs;    檢視使用者所擁有的角色      

三：oracle常用語句   

SELECT	資料檢索
INSERT UPDATE DELETE MERGE	資料操縱語言（DML）
CREATE ALTER DROP RENAME TRUNCATE	資料定義語言（DDL）
COMMIT ROLLABCK SAVEPOINT	事物處理控制
GRANT REVOKE	資料控制語言（DCL）

oracle中SQL語句規範：  

    1：SQL語句不區分大小寫。

    2：SQL語句可以是一行，也可以是多行。

    3：關鍵字不能縮寫或在中間另起一行。

    4：子句通常放在單獨的行中。

    5：縮進隻是為了增強可讀性，沒有具體實際意義。

SQL>create table tb1(name varchar2(10),job varchar2(20),gongzi varchar2(20))tablespace TBspace;    建立表tb1
SQL>alter table tb1 add id varchar(10);    添加字段
SQL> alter table tb1 drop column id;    删除字段
SQL> insert into tb1(NAME,JOB,GONGZI,ID)values('jim','baidu','5600','3');    插入資料
SQL>truncate table tb1;    清空表tb1中的資料
SQL>drop table tb1;    删除表      

基本SELECT語句

    SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;

    SELECT确認處理哪些列，FROM确認處理哪些表。

    使用算術運算符，可以建立數字和日期資料表達式。如運算符+,-,*,/等。

    例如：select name,gongzi,12*gognzi from tb1;

    定義空值：空值是指不可用、未配置設定、未知或不适用的值；空值不同于零或空格。

    例如：select name,gongzi,commission_pct from tb1;    在查詢結果中會建立一個名為commission_pct的空列。

定義列别名

    列别名具有以下特點和功能：

    1：重命名列标題。

    2：有助于計算。

    3： 緊跟在列名的後面，在列名和别名之間有可選的關鍵字AS。

    4：如果列别名包含空格或特殊字元或者區分大小寫，則需要使用雙引号。

    例如：select name as"Name",gongzi*12"NIANgongzi"from tb1;       

連接配接運算符

    連接配接運算符具有以下特點和功能：

    1：将列或字元串連接配接到其它列。

    2：有兩條豎線（||）表示。

    3：建立一個合成列，它是字元表達式。

     例如：selectname||jobas "GongZuo" from tb1;    就會将name和job兩列連接配接起來作為一個單獨列“GongZuo”。

文字字元串

    1：文字是指包含select清單中的字元、數字或日期。

    2：日期和字元文字必須包含在單引号中。

    3：在結果輸出中，每個傳回的行都回将每個字元串顯示一次。

    例如：select name ||'de gognzi shi'||gognzi as "GongZi" from tb1;    就會顯示一個名為GongZi的列，列中的資訊是“XX de gongzi shi XXX”`。

重複行

    查詢時預設會顯示重複行，想消除重複行需要使用關鍵字"DISTINCT"。

    例如：select DISTINCT gongzi from tb1;

顯示表結構：

    DESC tb1;

限制和排序資料：

    使用where子句限制傳回的行：‘

    SELECT *|{[DISTINCT] column|expression [alias],...} FROM table[where condition(s)];

    例如：select * from tb1 where gongzi > 5600;    

    where中的字元串和日期：

    1：字元串和日期值包含在單引号中。

    2：字元值是區分大小寫的，而日期值是區分格式的。

    3：預設日期格式為 DD-MON-YY

    比較條件：=,>,>=,<,<=,<>            其中<>為不等于。

    邏輯條件：AND,OR,NOT

     例如：select name job from tb1 where gognzi NOT IN (5300,5600,5900);  

    其它條件：

運算符	含義
BETWEEN...AND...	介于兩值之間（包含這兩個值）
IN(SET)	與值清單中的任一值比對
LIKE	與某個字元模式比對
IS NULL	為空值

    例如：select name job from tb1 where gognziIN (5300,5600,5900);    

    使用LIKE條件可以對有效字元串執行通配符搜尋，其中"%"比對零個或人一個字元，"_"比對一個字元。    例如：select * from tb1 where jobLIKE 't%';

ORDER BY子句

     SELECT *|{[DISTINCT] column|expression [alias],...} FROM table [where condition(s)]ORDER BY {column,expr,alias} [ASC|DESC];

    1：使用ORDER BY子句可以對行進行排序。

            ASC 升序，也是預設設定

            DESC 降序

    2：ORDER BY子句在SELECT語句的最後。

    例如：SELECT * FROM TB1 order by gognzi desc;    就會按gongzi降序排列。

四：oracle常用函數

    oracle中有許多内置的SQL函數，可以友善的實作某些功能。通過向SQL函數傳遞參數，函數執行後會為每行後傳回結果；接受的參數可以是列也可以是表達式，函數可以嵌套。

    function_name [(arg1,arg2,...)]

    一些常用SQL函數：

    1：大小寫處理函數

    這些函數主要用于轉換字元串的大小寫：

函數	處理結果
LOWER('SQL Function')	sql function
UPPER('SQL Function')	SQL FUNCTION
INITCAP('SQL Function')	Sql Function

    例如：select * from tb1 where LOWER(name)='landy orten';

    字元串處理函數：

	結果
CONCAT('Hello','World')	HelloWorld
SUBSTR('HelloWorld',1,5)	Hello
LENGTH('HelloWorld')	10
INSTR('HelloWorld',W)	6
LPAD(aaaaa,10,'*')	*****aaaaa
RPAD(aaaaa,10,'*')	aaaaa*****
TRIM('H' FROM 'HelloWorld')	elloWorld

        SUBSTR取得字元串中指定起始位置和長度的字元串 ,substr ( string, start_position, [ length ] ),當start_position為負數時，表示從字元串末尾向前算起。

        RPAD(String a,int length,String addString)，将addString添加到a的右邊，length 是傳回值的長度。

    2：數字函數

        ROUND：将值舍入到制定的小數位

                ROUND(33.363,2)——>33.36

          TRUNC：将數值截取到指定的小數位（不舍入）

                TRUNC(33.363,2)——>33.36

        MOD：傳回除法運算的餘數

                  MOD(16,3)——>1

    例如：select ROUND(33.336,2),MOD(16,3) from DUAL;    //DUAL是用于檢視函數或計算結果的空表。

    3：日期處理

    oracle資料庫以内部數字格式存儲日期：世紀、年、月、日、小時、分鐘和秒。

    預設日期格式為：DD-MON-YY    例如：07-MAY-13

    ‍‍SYDATE函數是一個日期函數，它會傳回資料庫伺服器的目前日期‍‍‍時間。

    例如：select SYSDATE from DUAL;‍‍‍‍‍‍‍‍‍

    與日期有關的運算：    

    1：對日期進行加減運算，以獲得新的日期值。

    2：将兩個日期相減，以得到他們之間的天數。

    3：将小時除以24，以将小時添加到日期中。

    例如：select name,(SYSDATE-hire_date)/7 AS weeks from tb1 ;