oracle初學(-)

本文以Scott使用者作為講解的執行個體，安裝oracle11g：

下載連結：

http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html

我在oracle官網註冊了，可以直接使用：使用者名稱：wenzibo259@126.com 密碼：ohe5xAkl

我已經安裝好了(Oracle11g 個人習慣設定的密碼，實際中你們可以根據自己的習慣設定

:全域ID:oracle;Sys/system:Admin259;scott/Scott259)

下面在安裝過程中，或安裝後會出現常見的錯誤

 

如果網路環境發生改變，則修改 D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora,tnsnames.ora兩個檔案中的host 如果監聽無法啟動：regedit開啟註冊表 ImagePath D:\app\Administrator\product\11.2.0\dbhome_1\BIN\TNSLSNR.EXE(.exe有可能沒有，該項如果沒有則建立一份) 如果出現emca錯誤 emca -config dbcontrol db -repos drop,然後create重建一份

安裝成功後啟動sqlplus，有一些基本的命令，先來熟悉一下 Set linesize 300;// 每行300 Set pagesize 30;每頁顯示30行 ed a;可以儲存sql語句；@a;執行 也可以使用 D:/demo.sql;可以使用 @d:demo Select * from tab;//查多少個表 Show user;// 顯示目前使用者 Conn 使用者名稱/密碼 [AS SYSDBA] //切換使用者，中括弧可選的 Select * from scott.emp;// 模式名或使用者名稱.表名，方可查詢其他使用者的表 SHUTDOWN IMMEDIATE;// 關閉資料庫執行個體 Select * from scott.emp;就會出現以下錯誤 第 1 行出現錯誤: ORA-01034: ORACLE not  available 進程 ID: 4152 會話 ID: 197 序號: 34 此時使用/nolog 登陸 SQL> /nolog SP2-0042: 未知命令 "/nolog" - 其餘行忽略。 使用startup啟動資料庫執行個體   可以使用windows命令只要使用Host命令 如：HOST COPY D:/demo.sql  D;/demo.txt;

 

Scott 作為案例：

部門表dept

 

No.	名稱	類型	描述
1	DEPTNO	NUMBER(2)	部門編號，由2位元字組成
2	DNAME	VARCHAR2(14)	部門名稱，由14個字元組成
3	LOC	VARCHAR2(13)	部門所在的位置，又13個字元組成

 

僱員表emp

 

No.	名稱	類型	描述
1	EMPNO	NUMBER(4)	僱員編號，由4位元字組成
2	ENAME	VARCHAR2(10)	僱員名稱
3	JOB	VARCHAR2(9)	僱員職位
4	MGR	NUMBER(4)	僱員對應的領導編號
5	HIREDATE	DATE	僱員僱傭的日期
6	SAL	NUMBER(7,2)	僱員的基本薪資，其中有5位整數，兩位小數，一共7位
7	COMM	NUMBER(7,2)	僱員（銷售人才）的傭金
8	DEPTNO	NUMBER(2)	僱員所在的部門編號

 

工資等級表salgrade

 

No.	名稱	類型	描述
1	GRADE	NUMBER	工資等級
2	LOSAL	NUMBER	此等級的最低工資
3	HISAL	NUMBER	此等級的最高工資

 

工資表bonus

 

No.	名稱	類型	描述
1	ENAME	VARCHAR2(10)	僱員名稱
2	JOB	VARCHAR2(9)	僱員職位
3	SAL	NUMBER	僱員的工資
4	COMM	NUMBER	僱員的傭金

 

 

SQL 種類：   . DML (Data Manipulation  Language,資料操作語言) -- 用於建設或修改資料   . DDL(Data Definiton Language,資料庫定義語言) -- 用於定義資料庫結構，建立、刪除、修改資料庫物件   . DCL(Data Control Language , 資料庫控制語言)--   用於定義資料使用者的許可權

簡單查詢：

文法：SELECT [DISTINCT] * | 欄位名[別名] … FROM 表名稱[別名]

範例：查詢出每個僱員的編號、姓名、年薪（不包含傭金）

 

支援數學四則運算

如果員工每月的福利：200元房屋補助+200元車補,年底多發一個月薪水

SELECT e.empno, e.ename,(e.sal + 200 + 200)*12 + e.sal income FROM EMP e;

範例：查詢員工的職位

 

 

顯示格式：

“僱員編號是：7369的僱員姓名是：SMITH，基本工資是：800，職位是：CLERK!”

 

 SELECT '僱員編號是：'|| e.empno ||'僱員姓名是：' ||e.ename || '基本工資是：'||e.sal || '職位是：'|| e.job||'!' 僱員資訊 from EMP e;

限定查詢：

SELECT [DISTINCT] * | 欄位名[別名] … FROM 表名稱[別名]

[WHERE 條件(s)]

常用的運算子：>/</<=/>=/!=(<>)/BETWEEN…AND/LIKE/ISNULL/IN/AND/OR/NOR;等

範例：查詢工作大於1500的僱員資訊

SELECT * FROM emp WHEREsal>1500;

查詢辦事員是CLERK的僱員資訊

SELECT * FROM emp WHEREjob='CLERK';

注意：oracle是區分大小寫。

範例：查詢職位是辦事員，或者是銷售人員的全部資訊，並且要求僱員的工作大於1200；

SELECT * FROM emp WHERE(job='CLERK' OR job='SALESMEN') AND sal>1200;

範例：查詢所有的職位不是辦事員的僱員資訊

SELECT * FROM emp WHERE NOTjob='CLERK';

SELECT * FROM emp WHERE job!='CLERK';

SELECT * FROM emp WHERE job<>'CLERK';

範例：範圍 BETWEEN … AND（表示大於等於..小於等於），可運算元字、日期

查詢工資在1500~3000的僱員資訊: SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;

查詢1981年僱傭的僱員資訊：SELECT * FROM emp WHERE hiredate BETWEEN '01-1月 -1981' AND '31-12月 -81';

範例：判斷是否為空白：IS(NOT) NULL 和0以及Null 字元串是不同的概念

查詢獎金的僱員資訊：SELECT * FROMemp WHERE comm IS NOT NULL;

SELECT * FROM emp WHERE NOTcomm IS NULL;

範例：制定範圍的判斷：(NOT)IN 操作符

查詢僱員編號是：7369、7566、7799的僱員資訊

SELECT * FROM emp WHERE empnoIN (7369,7566,7788);

注意 IN(7369,null)—正常查詢，NOT IN(null)-不會返回結果

範例：模糊查詢LIKE

 _ 單個字元

% 所有字元

查詢僱員姓名中帶有字母A的僱員資訊

SELECT * FROM emp WHERE enameLIKE '%A%';

查詢僱員姓名中第二字母以A開頭的僱員資訊

SELECT * FROM emp WHERE enameLIKE '_A%;'

SELECT * FROM emp WHERE enameLIKE '%%';--表示查詢所有資訊

範例：排序 ORDER BY 欄位[ASC,DESC] [,欄位 [ASC,DESC]] 預設是升序：ASC

查詢所有僱員資訊，工作由高到底排序，如果工資相同，按在僱員日期又早到晚排序

SELECT * FROM emp ORDER BYsal DESC, hiredate ASC;

排序在sql語句最後端

2 單行行數

單行函數主要分為以下5類：字元函數、數學函數、日期函數、轉換函式、通用函數

2.1、字元函數

    字元函數的功能：主要是進行字串的操作，下面給出幾個函數：

    UPPER(字串|列)：將輸入的字元或列以大寫的形式返回

    LOWER(字串|列)：將輸入的字元或列以小寫形式返回

    INITCAP(字串|列)：首字母大寫

    LENGTH(字串|列)：求出字串的長度

    INITCAP(字串|列)：首字母大寫

REPLACE(字串|列)：進行字串替換

SUBSTR(字串|列, 開始點 [, 結束點])：字串截取

 

SQL> SELECT * FROM empWHERE ename=UPPER('&str'); &str—表示輸入變數

輸入 str 的值:  smith

將僱員的名稱首字母大寫: SELECTINITCAP(ename) FROM emp;

查詢姓名長度為5的僱員資訊：SELECT LOWER(ename),LENGTH(ename) l FROM emp WHERE LENGTH(ename)=5;

範例：使用字元’_’替換僱員姓名’A’: SELECT REPLACE(ename,'A','_') FROM emp;

範例：字串截取 SELECT ename,SUBSTR(ename, 0, 3) FROM emp;

文法一：SUBSTR(字串|列,開始點) 從開始點截取到最後

文法二：SUBSTR(字串|列,開始點,結束點) 從開始點截取到結束點最後(包括首尾0和1是一樣的)

字串截取後三位,可以使用負數

SELECT ename, SUBSTR(ename,-3) FROM emp;

2.2數字函數

ROUND(數字|列 [,保留幾位小數]) – 四捨五入

TRUNC(數字|列 [,保留幾位小數]) – 捨棄指定的內容

MOD(字元1,字元2) 對字元1和字元2求模即餘數

2.3 日期函數

SELECT SYSDATE FROM dual;--查詢當前日期

日期+數字—若干天后的日期

日期-數字—若干天前的日期

SELECT SYSDATE+3, SYSDATE+300FROM dual;

日期-日期—日期間的天數，大日期減去小日期

沒有日期加日期

範例：求出僱員的僱傭日期距離今天的天數

SELECT ename,SYSDATE-hiredate FROM emp;

LAST_DAY(日期)：求出指定日期的最後一天

NEXT_DAY(日期,星期數)：求出下一個指定星期數的日期

ADD_MONTHS(日期，數字):求若干月之後的日期

MONTHS_BETWEEN(日期1，日期2);求出日期之間的月份

範例：求出僱員的僱傭日期距離今天的天數

開發中如果日期操作建議使用以上函數，避免閏年的問題。

2.4 轉換函式（核心）

目前接觸的oracle資料類型：number、varchar2、date

轉換函式的主要功能，完成資料之間的轉換，下面來看下三種轉換函式

TO_CHAR(字串 | 列， 格式字串)：將日期或數字轉換為字串顯示；

TO_DATE(字串，格式字串)：將字串轉換為日期顯示

TO_NUMBER(字串)：將字串轉換為數字顯示

fm:表示前置0

SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh24:mi:ss')FROM dual;

2012-11-13 00:37:02

可以格式數字

SELECT TO_CHAR(132132465464,’999,999,999,999’) FROM dual;

SELECT TO_CHAR(132132465464,'L999,999,999,999.00') FROM dual;--9表示數字，L表示當前環境下的貨幣符號

SELECTTO_DATE(‘2012-01-02’, ‘yyyy-mm-dd’) FROM dual;--顯示 02-1月 -12

TO_NUMBER一般不用，用和不用一樣的

通用函數之oracle特色函數

NVL()，DECODE(數值|列，顯示值1，判斷值1，顯示值2，判斷值2，…)—類似if…else

範例：查詢僱傭的年薪

SELECTsal, comm, (sal+NVL(comm,0))*12 income, NVL(comm,0) FROM emp;

SELECTjob, DECODE(job, 'CLERK','辦事員','SALESMAN','銷售','MANAGER','經理','ANALYST','分析員','PRESIDENT','總裁') FROM emp;

範例：顯示的所有員工姓名、加入公司的年份和月份，僱傭日期所在月排序，若月份相同個年份排在最前面

 SELECTename, TO_CHAR(hiredate, 'yyyy') year, TO_CHAR(hiredate, 'mm') months,TO_CHAR(hiredate, 'dd') day FROM emp ORDER BY months,year