Oracle資料庫函數

資料庫設計過程

選擇資料庫

• Oracle 大規模的通路
• MySQL 小規模的通路
• SQL server 運作速率較慢

需求分析

• 收集使用者的資訊要求、處理要求、安全性與完整性要求。
• 自頂向下的分析方法，structured analysis (SA方法)
• 逐層分解的方式分析系統
  • 資料流圖(看懂)、ER圖、資料字典

概念結構設計

• 将需求分析得到的使用者需求抽象為
• 設計概念結構的四類方法：自頂向下、自底向上、逐漸擴張、混合政策
• 混合政策：将自頂向下和自底向上相結合，用自頂向下的政策設計一個全局概念結構的架構，以它為骨架內建自底向上政策中設計各局部概念結構。

視圖的內建

• 屬性沖突
• 命名沖突
• 機構沖突

資料模型的優化

• Oracle
• win10 裝12C

資料庫的實施

• 用DDL定義資料庫結構
• 組織資料入庫
• 編制與調試應用程式

資料庫的運作與維護

Oracle伺服器

• 資料庫及其執行個體

執行個體名

• 大寫英文字母，不能包含特殊字元。

記憶體結構

• 塊緩沖區、重做日志緩沖區、共享池、固定SGA、大池、Java池

oracle的使用

安裝及連接配接

• 不要安裝執行個體方案
• 建立過程中需要關閉防火牆
• 啟動資料庫方式：
  • dos指令：

    sqlplus 使用者名/密碼

  • 超級管理者：：

    sqlplus 使用者名/密碼 as sysdba

  • sqlplus：主機字元串

    SQLPLUS.EXE

  • dos指令與圖形化操作的轉換：
• 檢視目前帳号：

  show user

• 連接配接資料庫：

  conn 使用者名/密碼

• 加鎖、解鎖：

  alter user 使用者名 account lock|unlock

• 斷開資料庫：

  disc

• set linesize 500
• 設定頁面大小：

  set pagesize 20

編輯緩沖區

• run：執行緩沖區并顯示緩沖區内容
• /：不顯示緩沖區内容
• list顯示緩沖區
• 修改密碼：password 使用者名
• 密碼失效：alter user 使用者名 PASSWORD expire

檔案操作

建立腳本檔案

• save d:\abc.sql
• select ename,sal from emp;
• save 檔案 append
• save 檔案 replace

載入sql腳本檔案

• 裝載：

  get 檔案.sql 展示腳本檔案的内容

• 裝載：

  get 檔案.sql nolist 不顯示緩沖區内容

• 腳本檔案的裝載+執行：

  start 檔案.sql 裝載并且執行

  或者

  @檔案名.sql

• 如果是.sql結尾的檔案，可以不寫字尾名，但如果是其他格式，需要寫。

注釋

• 單行：

  --

  或者

  remark

  隻能放在頭
• 多行：

清除螢幕

• clear screen

• 清除螢幕緩沖區：快捷鍵

  SHIFT+DELETE

Clear  SCR
CLEA  SCR
CLE   SCR
CLE  SCREE
CLE   SCRE
           

幫助指令

help   指令
           

環境變量的設定與顯示

show --顯示
set --設定
           

其他

autocommit：是否自動送出DML語句

autocommit on/off

COLSEP：設定列之間的間隔符。

feedback：顯示回報資訊的最低行數，預設值是6。

heading：是否顯示列标題。

time：指令提示符前面顯示時間。

timing：顯示執行slq語句的時間。

環境變量的備份

store set 檔案

arraysize：設定從資料庫中提取的行數，預設值是15。

SQL語句

1. 查詢

SELECT 列名 FROM 表名;
           

• 查詢所有資訊

SELECT empno,ename,job,mg FROM emp;
SELECT * FROM emp;
           

2. 算術運算符

+,-,*,/

3. 空值：無效的，未定義的。空值不是0

4. 連接配接

• ||

  列與列之間的合并，列與字元串的合并。

5. 去重

• DISTINCT

  ：隻能放在第一列的前面。

6. 排序

SELECT  列名1  【as】  列别名1， 列名2 【as】   列别名2，。。。。  
FROM 表名
ORDER   BY  排序列名1  【ASC】|DESC,  排序列名2 【ASC】|DESC,….;

           

7. 過濾查詢

SELECT 列名1 [as] 列别名1, 列名2 [as] 列别名2，。。。。  
FROM 表名
WHERE 條件
ORDER BY 排序列名1 [ASC]|[DESC], 排序列名2 [ASC]|[DESC],….;
           

8. 比較運算符

=
>
<
>=
<=
<> ,!=
BETWEEN   AND
IN
           

9. 模式比對(

LKIE

語句)

LIKE 
%: 任意個數的任意字元
_: 表示一個字元
           

10. 邏輯運算符

AND, OR, NOT
           

11. 函數

• 1. 單行函數：字元、數值、日期、轉換、通用
• 1. 多行函數：

12. 字元函數

• 1. 大小寫控制函數（LOWER、upper、initcap）
• 1. 字元控制函數（concat、substr、length、instr、lpad、rpad、trim）
• 數值（round、trunc、mod）

12. 日期 (sysdate)

• 日期的算術計算：
  • 日期+(-)數字=日期
  • 日期-日期=天數

MONTHS_BETWEEN (date1, date2)

• 用于計算date1和date2之間有幾個月。

SELECT MONTHS_BETWEEN(to_date('2014-1-10','yyyy-mm-dd'), to_date('2014-3-21','yyyy-mm-dd')) MONTHS
           

ADD_MONTHS(date, integer)

• ADD_MONTHS傳回日期日期加上INT月份。 date參數可以是datetime值或可以隐式轉換為DATE的任何值。 INT參數可以是整數或可以隐式轉換為整數的任何值。如果始終是該月的最後一天，或者如果結果月份有更多的日期，或者結果月份是日期的月份組成部分的更多日期，則結果是結果月份的最後一天。結果與日期具有相同的日期元件。

SELECT TO_CHAR(
     ADD_MONTHS(hire_date,1),
     'DD-MON-YYYY') "Next month"
     FROM employees 
     WHERE last_name = 'Baer';
Next Month
-----------
07-JUL-1994
           

NEXT_DAY(date, char)

• 傳回

  date

  後面的第一個由

  char

  描述的日期，傳回值為

  date

  類型。

  char

  描述的日期必須是

  Monday, Tuesday, Wednesday, Thirsday, Friday, Saturday, Sunday

  中的一個。
• Example: Returns the date of the next Tuesday after February 2, 2001

SELECT NEXT_DAY('02-FEB-2001','TUESDAY') "NEXT DAY"
     FROM DUAL;

NEXT DAY
-----------
06-FEB-2001
           

LAST_DAY(date)

• 傳回

  date

  所在月的最後一天，

  date

  是日期類型，傳回值也是

  date

  類型。
• Example: The following statement determines how many days are left in the current month.

SELECT SYSDATE,
   LAST_DAY(SYSDATE) "Last",
   LAST_DAY(SYSDATE) - SYSDATE "Days Left"
   FROM DUAL;
 
SYSDATE   Last       Days Left
--------- --------- ----------
30-MAY-01 31-MAY-01          1
           

• Example: The following example adds 5 months to the hire date of each employee to give an evaluation date:

SELECT last_name, hire_date, TO_CHAR(
   ADD_MONTHS(LAST_DAY(hire_date), 5)) "Eval Date"
   FROM employees;

LAST_NAME                 HIRE_DATE Eval Date
------------------------- --------- ---------
King                      17-JUN-87 30-NOV-87
Kochhar                   21-SEP-89 28-FEB-90
De Haan                   13-JAN-93 30-JUN-93
Hunold                    03-JAN-90 30-JUN-90
Ernst                     21-MAY-91 31-OCT-91
Austin                    25-JUN-97 30-NOV-97
Pataballa                 05-FEB-98 31-JUL-98
Lorentz                   07-FEB-99 31-JUL-99
. . .
           

EXTRACT

• 文法結構：

  

轉換函數

• TO_CHAR

  ：

• TO_DATE

  ：

• TO_NUMBER(expr [, fmt [, 'nlsparam' ] ])

  :

通用

NVL(expr1, expr2)

• 如果

  expr1

  非空，傳回

  expr1

  ，如果

  expr1

  為空，傳回

  expr2

  。

DECODE(expr, search,result[, search, result ]...[, default ])