oracle sql 知識小結

標籤：

Oracle_sql 培訓：第一單元：select 語句：①：字串串連操作符： ||②：去除重複行：distinct 第二單元：條件限制和排序①：關鍵字：where②：比較操作符：=，>,<,between...and...,in,like,is null     :使用like做模糊比對：可使用%或者_作為萬用字元；%代表0個或多個字元。_代表一個單個字元。escape為識別逸出字元 escape ‘/‘。③：排序：order by  asc（升序預設）/desc（倒序） 第三單元：單行函數①：大小寫轉換：lower（） A——a                          upper（）a——A                          initcap（）first——First②：字串操作函數函數                                   結果CONCAT(‘Hello‘, ‘World‘)    HelloWorldSUBSTR(‘HelloWorld‘,1,5)    HelloLENGTH(‘HelloWorld‘)        10INSTR(‘HelloWorld‘, ‘W‘)      6LPAD(salary,10,‘*‘)               *****24000RPAD(salary, 10, ‘*‘)             24000*****TRIM(‘H‘ FROM ‘HelloWorld‘)elloWorldTRIM(‘ HelloWorld‘)              HelloWorldTRIM(‘Hello World‘)              Hello World③：數字操作函數：函數                          結果ROUND(45.926, 2)   45.93TRUNC(45.926, 2)    45.92MOD(1600, 300)      10④：日期操作函數：函數                                                                               結果MONTHS_BETWEEN (‘01-SEP-95‘,‘11-JAN-94‘)              19.6774194ADD_MONTHS (‘11-JAN-94‘,6)                                      11-Jul-94NEXT_DAY (‘01-SEP-95‘,‘FRIDAY‘)                                   8-Sep-95NEXT_DAY (‘01-SEP-95‘,1)                                              3-Sep-95NEXT_DAY (‘1995-09-01‘,1)                                            ORA01861:literal does not match format stringNEXT_DAY (to_date(‘1995-09-01‘,‘YYYY-MM-DD‘),1)      3-Sep-95LAST_DAY(‘01-FEB-95‘)                                                   28-Feb-95ROUND(‘25-JUL-95‘,‘MONTH‘)                                       1-Aug-95ROUND(‘25-JUL-95‘ ,‘YEAR‘)                                           1-Jan-96TRUNC(‘25-JUL-95‘ ,‘MONTH‘)                                        1-Jul-95TRUNC(‘25-JUL-95‘,‘YEAR‘)                                              1-Jan-9⑤：不同類型的資料轉換函式：Oracle資料類型的隱私轉換規則：對於賦值操作可以： 從到VARCHAR2 or CHAR            NUMBERVARCHAR2 or CHAR            DATENUMBER                              VARCHAR2DATE                                    VARCHAR對於運算式比較操作僅可以：從到 VARCHAR2 or CHAR       NUMBER        VARCHAR2 or CHAR        DATE TO_CHAR() 函數：日期到字串的轉換TO_CHAR(date, ‘format_model‘) ;日期格式化元素意義YYYY                  4位元字表示的年份YEAR                  英文描述的年份MM                    2位元字表示的月份MONTH              英文描述的月份MON                  三個字母的英文描述月份簡稱DD                      2位元字表示的日期DAY                    英文描述的星期幾DY                      三個字母的英文描述的星期幾簡稱HH24:MI:SS AM  時分秒的格式化DDspth               英文描述的月中第幾天fm                      格式化關鍵字，可選 TO_CHAR() 函數：數字到字串的轉換TO_CHAR(number, ‘format_model‘) ;數字格式化元素意義9表示一個數字0強制顯示0$放一個美元預留位置L使用浮點本地幣種符號.顯示一個小數點預留位置,顯示一個千分位預留位置  TO_NUMBER() 函數：字串到數位轉換TO_NUMBER(char[, ‘format_model‘]) ;TO_DATE() 函數：字串到日期的轉換TO_DATE(char[, ‘format_model‘])  其他常用單行函數函數用途NVL (expr1, expr2)如果expr1為空白，這返回expr2NVL2 (expr1, expr2, expr3)如果expr1為空白，這返回expr3（第2個結果）否則返回expr2NULLIF (expr1, expr2)如果expr1和expr2相等，則返回空COALESCE (expr1, expr2, ..., exprn)如果expr1不為空白，則返回expr1,結束；否則計算expr2,直到找到 一個不為NULL的值或者如果全部為NULL，也只能返回NULL   條件運算式：decode函數：DECODE(col|expression, search1, result1 [, search2, result2,...,] [, default]) 第四單元：多表關聯查詢：1，等於鏈結2，不等鏈結。3，外連結（左鏈結，右鏈結）。4，自連接 第五單元：分組計算函數和group by句子分組計算函數：相對於單行函數，也可稱之為多行函數，它的輸入是多個行構成得一個行集（這個行集可以是 一張表的所有行，也可以是按照某個維度進行分組後的某一組行），而輸出都是一個值； 分組計算函數(常用)：包括1、求和(SUM)2、求平均值(AVG)3、計數（COUNT）4、求標準差(STDDEV)5、求方差(VARIANCE)6、求大值(MAX)7、求小值(MIN)8、COUNT 函數說明:函數用法意義COUNT(*) 返回滿足選擇條件的所有行的行數，包括值為空白的行和重複的行COUNT(expr)返回滿足選擇條件的且運算式不為空白行數。COUNT(DISTINCT expr)返回滿足選擇條件的且運算式不為空白，且不重複的行數。9、group bySELECT 查詢語句中同時選擇分組計算函數運算式和其他獨立欄位時，其他欄位必須出現在Group By子 句中，否則不合法。不能在Where 條件中使用分組計算函數運算式，當出現這樣的需求的時候，使用Having 子句  第六單元：子查詢SELECTselect_list FROMtable WHEREexproperator(SELECTselect_list FROM  table);  第七單元：dml語句DML: Data Manipulation Language , 資料操縱語言；簡單的說就是SQL中的增、刪、改等語句 INSERT INTO table [(column [, column...])] VALUES(value [, value...]);UPDATE table SETcolumn= value[, column = value, ...] [WHERE condition];DELETE [FROM]table [WHEREcondition];  第九章：資料庫物件-表規則要求：表的命名要求和表中列的命名要求：1、必須以字母開頭2、長度不能超過30個字元3、只能包含A–Z, a–z, 0–9, _, $, and #4、不能與資料庫中的已有對象重名5、不能使用Oracle  資料庫的保留  資料類型描述VARCHAR2(size)可變長字串CHAR(size)  定長字串NUMBER(p,s)  可變長數值DATE 日期時間LONG 可變長大字串，大可到2GCLOB可變長大字串資料，大可到4GRAW and LONG RAW 位元據BLOB大位元據，大可到4G BFILE儲存於外部檔案的位元據，大可到4GROWID64進位18位長度的資料，用以標識行的地址TIMESTAMP精確到分秒級的日期類型（9i以後提供的增強資料類型）INTERVAL YEAR TO MONTH表示幾年幾個月的間隔（9i以後提供的增強資料類型-極其少見）INTERVAL DAY TO SECOND表示幾天幾小時幾分幾秒的間隔（9i以後提供的增強資料類型-極其 少見）   更改表的文法：添加列：ALTER TABLE tableADD(column datatype[DEFAULT expr] [, column datatype]...);更改列：ALTER TABLE tableMODIFY(column datatype[DEFAULT expr] [, column datatype]...);刪除列：ALTER TABLE tableDROP(column);刪除表：DROP TABLE tableName;更改表名：RENAME oldtablenameto  newtableName；  第十單元：資料庫物件-約束約束的概念：Oracle 資料庫使用“約束”來阻止對資料庫表中資料的不合法的“增刪改”動作。常用的約束有如下幾種：NOT NULL      （非空約束）UNIQUE （唯一性限制式）PRIMARY KEY   （主鍵約束）FOREIGN KEY   （外鍵約束）CHECK         （自訂約束)  單獨建立約束文法：ALTER TABLE tablenameADD CONSTRAINT  constraintname constrainttype(column1,…);  刪除約束：ALTER TABLE tablenameDROP CONSTRAINT  constraintname;  失效/生效約束：ALTER TABLE tablename DISABLE CONSTRAINT constraintname CASCADE; ALTER TABLE tablenameENABLE CONSTRAINT  constraintname; 查詢系統中存在哪些約束:SELECT constraint_name, constraint_type, search_conditionFROM user_constraints   第十一單元：資料庫物件-視圖視圖的概念：有的時候我們需要關聯多張表獲得一個查詢結果集，有的時候我們需要寫很複雜的條件得到一個想要的結果集， 我們不想每次要想這些資料的時候都重新去寫很複雜的SQL語句，怎麼辦？我們可以把這些結果集建立為視圖-View 視圖建立的文法：CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view[(alias[, alias]...)]AS subquery[WITH CHECK OPTION [CONSTRAINT constraint]][WITH READ ONLY [CONSTRAINT constraint]] 刪除視圖：drop view viewname;   第十二章：資料庫物件-序列、索引、同義字 序列的概念：有的時候我們定義某一張表中某一列為主鍵，當我們往表中插入資料的時候，對於主鍵欄位的賦值要求唯一性， 我們希望能有個自增長類型的資料庫物件，我們每擷取一次，它自動成長，保證下次擷取時肯定是不一樣的值， 這樣我們就方便了，Oracle 資料庫提供“序列”這種對象來滿足我們的要求   序列的建立：CREATE SEQUENCE sequence[INCREMENT BY n][START WITH n][{MAXVALUE n| NOMAXVALUE}][{MINVALUE n| NOMINVALUE}][{CYCLE | NOCYCLE}][{CACHE n| NOCACHE}];  更改序列定義：ALTER SEQUENCE sequenceINCREMENT BY 20MAXVALUE 999999NOCACHE NOCYCLE;刪除序列：DROP SEQUENCE sequence;  索引建立舉例：CREATE INDEX emp_last_name_idxON employees(last_name);  同義字的概念：當資料庫使用者A要訪問資料庫使用者B中的一張表Table1的時候，需要加首碼Select * from B.table1但我們要通過DB-LINK訪問另一個資料庫中的某張表的時候我們需要加@尾碼Select * from [email protected]為了在程式中能夠簡化寫法，Oracle 提供同義字，也就是可以在A使用者下建立一個同義字指向B使用者下的 Table1, 以後訪問的時候可以直接存取這個同義字，而不用加首碼了文法：CREATE SYNONYM Table1 for B.Table1  第十三單元：控制使用者權限 文法：GRANT object_priv [(columns)]ON  object TO{user|role|PUBLIC}[WITH GRANT OPTION]  （傳遞許可權） 收回許可權：REVOKE {privilege [, privilege...]|ALL}ON   objectFROM   {user[, user...]|role|PUBLIC}  UNIONUNION  ALL   保留重複記錄 INTERSECT   取交集 MINUS  取差集SELECT employee_id,job_idFROM   employeesMINUSSELECT employee_id,job_id FROM   job_history;  第十四單元：group by 句子增強①：在Group By 中使用Rollup 產生常規分組合計列以及分組小計：1、常規分組行；2，3 、分層小計行； Rollup 後面跟了n個欄位，就將進行n+1次分組，從左至右每次減少一個欄位進行分組；然後進行 union②：在Group By 中使用Cube產生Rollup結果集+ 多維度交叉表資料來源：   

oracle sql 知識小結