oracle數(shù)據(jù)庫基礎(chǔ)學(xué)習(xí)

1.內(nèi)置用戶

? SYS :超級(jí)用戶? 最高權(quán)限用戶???? sys/oracle AS SYSDBA
? SYSTEM :管理員????????????????? system/oracle
? scott : 測試連接??????????????? scott/tiger
? hr: 案例擁有者????????????????? hr/

?

簡單查詢（單表查詢）

基本語法：SELECT 目標(biāo)列 FROM 表名
? 1）所有列：*????? select * from employees;
? 2)部分列，直接寫列名，用逗號(hào)分割
???? select employee_id,first_name,salary,department_id from employees;
? 3）字符串連接：||
???? select employee_id,first_name||' '||last_name from employees;
? 4) 使用別名
????
??? select employee_id AS 員工號(hào),first_name||' '||last_name AS 員工名 from employees;

???
?? 語句后的分號(hào)表示執(zhí)行的意思，不是語句本身組成部分

?select employee_id?? "員 工 號(hào)" ,first_name||' '||last_name "Name" from employees;

?? 如果別名中包含空格特殊符號(hào)或區(qū)分大小寫,需要用雙引號(hào)將別名引起來

? 5)加入常量字符串
? select employee_id,'the employee name is :',first_name||' '||last_name name from employees;

? 6)使用函數(shù)
? select employee_id,upper(first_name) from employees;

? select substr('101**joan**20',1,3) from dual;

// dual :啞表 ，是個(gè)虛擬表，用于實(shí)現(xiàn)語法完整性
?
? 7）表達(dá)式
? select employee_id,salary,salary*12 yearsal from employees;

? 8)不重復(fù)顯示（重復(fù)記錄顯示一次）
?? select DISTINCT department_id from employees;

?

??
有條件查詢:
?? select 目標(biāo)列
?? from?? 表名
?? where? 條件?????? // 對(duì)表中數(shù)據(jù)進(jìn)行過濾

運(yùn)算符號(hào)的使用：
??? 1）簡單的關(guān)系運(yùn)算符?? 列名 運(yùn)算符 值
??? 2）特殊運(yùn)算符號(hào)
????????? between...and :? column between x and y :???? column>=x 而且column<=y
????? not between...and : column not between x and y :? column

????????? in : column in(x,y)?? : column=x 或者column=y
????? not in : column not in(x,y) :column<>x 而且 column<>y

????????? like: 模糊查詢
????? not like
??????????????? column like
??????????????? column not like
??????????????? 通配符號(hào)：
???????????????????????? % :代表任意個(gè)字符
???????????????????????? _ ：代表某一個(gè)字符
????????????? select * from employees where first_name like 'A%'
????????????? select * from employees where first_name like '__a%'

??????? 注意：如果查詢字符串中本身的_或%（不是通配符），則需要用escape定義轉(zhuǎn)義符號(hào)，轉(zhuǎn)義符號(hào)后面
的_或%為實(shí)際意義的符號(hào)。
????????????? select * from employees where job_id like '%/__l%' escape '/'

????? is null????????? : column is null
????? is not null????? :column is not null
?????????????
???? 3)符合條件：NOT AND OR

?

排序
??? SELECT 目標(biāo)列
??? FROM?? 表
??? WHERE 條件
??? ORDER BY? column|表達(dá)式? ASC|DESC

1）單列排序? order by column? asc|desc
??? select * from employees order by salary;
2) 別名排序? order by 別名? asc|desc
?? select employee_id,salary*12 allsalary from employees order by allsalary desc;
3) 表達(dá)式排序???
?? select employee_id,salary from employees order by salary*12 desc;
4) 基于列的序號(hào)排序
?? select employee_id,salary from employees order by 2；
5）基于非查詢列排序??
??? select employee_id,salary from employees order by department_id;
6)多列排序
?? select employee_id,department_id,salary from employees order department_id desc,employee_id;

空值：（可以理解為無窮大值）：升序時(shí)在最后，降序時(shí)在最前

?

函數(shù)：
? 單行函數(shù)：每次作用于一行的某個(gè)列上
? 多行函數(shù)：每次作用于一組行的某個(gè)列上

字符函數(shù)：
LOWER：將所有字符小寫
UPPER：將所有字母大寫
INITCAP：將字符中每個(gè)單詞的第一個(gè)字母大寫，其他字符小寫
CANCAT：實(shí)現(xiàn)字符串的連接 ，功能同符號(hào)? ||
SUBSTR：求子串? substr(str,x,y):從字符串str的x個(gè)字母開始截取長度為y的子串，如果沒有y，則截取從x位置開始到最后
LENGTH：返回字符串的長度
INSTR：判斷字符串2在字符串1中出現(xiàn)的位置，如果沒有返回0
?instr(str1,str2,m,n): 返回從str1的m個(gè)字符位置開始查找第n次出現(xiàn)的str2的位置，m n默認(rèn)為1

LTRIM（str1,str2)：從str1左側(cè)截取str2，str2默認(rèn)為空格
RTRIM(str1,str2）:從str1右側(cè)截取str2，str2默認(rèn)為空格
TRIM（leading|training|both str2 from str1)

REPLACE(str1,str2,str3): 將str1中的str2用str3替換
lpad（str1,n,char1):在str1的左側(cè)填充char1,使其達(dá)到n個(gè)字符長度，默認(rèn)char1為空格
rpad（str1,n,char1):在str1的右側(cè)填充char1,使其達(dá)到n個(gè)字符長度，默認(rèn)char1為空格

數(shù)值函數(shù)
ROUND(列名|表達(dá)式, n)：四舍五入函數(shù)。
??? 如果n為負(fù)數(shù)，表示從小數(shù)點(diǎn)左側(cè)n進(jìn)行四舍五入。
??? round（156.785,2) 156.79
??? round (156.785,-1):160
?
TRUNC(列名|表達(dá)式,n)：截?cái)嗪瘮?shù)。
??? 如果n為負(fù)數(shù)，表示對(duì)小數(shù)點(diǎn)左側(cè)n進(jìn)行截?cái)唷?br />??? trunc（156.785,2) 156.78
??? trunc (156.785,-1):150

MOD(m,n)：取余函數(shù)。（求模）
?mod(5,3) 2

floor(m):小于等于n的最大整數(shù)
?? floor(10.5)?? 10
ceil(m)：大于等于n的最小整數(shù)
?? ceil(10.5)?? 11

日期函數(shù)：

?? 注意： 1）兩個(gè)日期相減，差為天數(shù)；
????????? 2）一個(gè)日期與一個(gè)數(shù)字可以進(jìn)行加或減的運(yùn)算，但是數(shù)字表示天數(shù)

SYSDATE：返回系統(tǒng)日期：

?? 顯示格式問題：（1）系統(tǒng)默認(rèn)格式（2）進(jìn)行格式顯示的設(shè)置?
?????????????????? alter session set nls_language='american'
?????????????????? alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'

MONTHS_BETWEEN(d1,d2)：返回兩個(gè)日期間隔的月數(shù)
ADD_MONTHS(d,m)：在指定日期基礎(chǔ)上加上相應(yīng)的月數(shù)，其中m表示月數(shù)。

?

NEXT_DAY（d,m)：返回某一日期d的下一個(gè)指定工作日或 星期幾的日期
??? m:1-7 或者為 星期幾
?
LAST_DAY：返回指定日期當(dāng)月最后一天的日期

?
ROUND(date[,'fmt'])對(duì)日期進(jìn)行指定格式的四舍五入操作。按照YEAR、MONTH、DAY等進(jìn)行四舍五入。
?
TRUNC(date[,'fmt'])對(duì)日期進(jìn)行指定格式的截?cái)嗖僮鳌０凑誝EAR、MONTH、DAY等進(jìn)行截?cái)唷?br />?
EXTRACT ([YEAR] [MONTH][DAY][DD] FROM[日期類型表達(dá)式])：從日期中抽取部分

?

‘12-8-9’
? 1234

轉(zhuǎn)換函數(shù)：

? to_char(d,format): 將日期按指定格式轉(zhuǎn)換為字符串
??? select to_char(sysdate,'mm-dd-yyyy') from dual;
??? format格式：
?
??????? yyyy 2012
??????? yy?? 12
??????? rr?? 12
??????? year

??????? mm???? 1-12
??????? month? january,..../1月/2月????????
??????? mon??? 用月份前3個(gè)字符表示月份 jan feb?
??????? MON??? JAN
??????? Mon??? Jan

??????? dd?? 1-31
??????? d??? 1-7
??????? ddd? 1-365
??????? day? monday - sunday
??????? dy?? mon? - sun
??????? DY?? MON? -SUN
???????
??????? WW?? 1-53：一年中第幾周
??????? W??? 1-5：本月中的第幾周

??????? hh/hh12
??????? hh24

??????? mi
??????? ss
??????

? to_char(n,format):? 將數(shù)字按指定格式轉(zhuǎn)換為字符串

?

?
? to_number(str,format):將指定格式的字符串轉(zhuǎn)換為數(shù)字
? to_date(str,format):將指定格式的字符串轉(zhuǎn)換為日期

??

CASE expr
WHEN value1 THEN return_expr1
WHEN value2 THEN return_expr2
WHEN valuen THEN return_exprn
ELSE else_expr
END

CASE?
WHEN condition1 THEN return_expr1
WHEN condition2 THEN return_expr2
WHEN conditionn THEN return_exprn
ELSE else_expr
END

多表查詢（連接查詢）

?? 語法：oracle自身語法
???????? SQL99：標(biāo)準(zhǔn)語法

? 1）笛卡爾連接（交叉連接）：連個(gè)表無條件連接，一個(gè)表所有行分別與另一個(gè)表中所有行進(jìn)行連接

????? select? 目標(biāo)列
????? from? 表1，表2，...

???? select employee_id,first_name,e.department_id,department_name
???? from employees e ,departments d
? 2）內(nèi)連接：根據(jù)特定條件進(jìn)行連接，只有滿足條件的數(shù)據(jù)才發(fā)生連接
????? 等值連接:
?????? SELECT table1.column, table2.column
?????? FROM table1, table2
?????? WHERE table1.column1 =table2.column2;

????????? select employee_id,first_name,e.department_id,department_name
????????? from employees e ,departments d
????????? where e.department_id=d.department_id and salary>5000

????????
?????? 為表起別名，簡化，一旦起了表名，只能使用別名而不是使用原名
?????? 目標(biāo)列中的列如果在兩個(gè)表中都出現(xiàn)了，則需要說明該列的來源表
??????? 通常，將過濾條件放在連接條件之后

?

????? 不等值連接
??
????? 自身連接：把同一個(gè)表虛擬成兩個(gè)表

???????? SELECT e.employee_id,e.first_name,e.manager_id,m.first_name
???????? FROM employees e,employees m
???????? WHERE e.manager_id=m.employee_id
?
? 3）外連接：在內(nèi)連接的基礎(chǔ)上加上某個(gè)表中不符合連接條件的記錄。

?????? FROM table1,table2

????? 左外連接：在內(nèi)連接的基礎(chǔ)上+左側(cè)表（table1）中不符合連接連接條件的數(shù)據(jù)
??????? SELECT table1.column, table2.column
??????? FROM table1, table2
??????? WHEREtable1.column = table2.column(+);

?

????? 右外連接：在內(nèi)連接的基礎(chǔ)上+右側(cè)表(table2)中不符合連接連接條件的數(shù)據(jù)
??????? SELECT table1.column, table2.column
??????? FROM? table1, table2
??????? WHERE table1.column(+) = table2.column;

?
????? 全外連接：在內(nèi)連接的基礎(chǔ)上+右側(cè)表(table2)中不符合連接連接條件的數(shù)據(jù)+
??????????????????????????????? 左側(cè)表（table1）中不符合連接連接條件的數(shù)據(jù)

利用SQL99標(biāo)準(zhǔn)語法實(shí)現(xiàn)連接查詢

?? （1）交叉連接：?
??????? select 目標(biāo)列 from table1 CROSS JOIN table2;
?????? SELECT employee_id,department_name FROM employees CROSS JOIN departments
?????? SELECT employee_id,department_name FROM employees,departments
??? (2) 等值連接：
????? 1）自然連接：兩個(gè)表基于相同類型的、同名列的等值連接
???????? SELECT 目標(biāo)列 FROM table1 NATURAL JOIN table2；

???????? SELECT employee_id,first_name,department_id,department_name
???????? FROM?? employees NATURAL JOIN departments

???????? SELECT employee_id,first_name,e.department_id,department_name
???????? FROM employees e,departments d
???????? WHERE e.department_id=d.department_id AND e.manager_id=d.manager_id
????? 2）如果發(fā)生連接的列同名但類型不同，進(jìn)行等值連接，可以使用USING
???????? SELECT 目標(biāo)列 FROM table1? JOIN table2 USING(列名)

???????? SELECT employee_id,first_name,department_id,department_name
???????? FROM?? employees? JOIN departments USING(department_id)

???????? SELECT employee_id,first_name,e.department_id,department_name
???????? FROM employees e,departments d
???????? WHERE e.department_id=d.department_id?
???
????? 3）通用
???????? SELECT 目標(biāo)列
???????? FROM table1 JOIN table2
???????? ON?? table1.col1=table2.col2 （連接條件）
???????? WHERE 過濾條件

SELECT employee_id,first_name,e.department_id,department_name
FROM employees e JOIN departments d
ON? e.department_id=d.department_id?
WHERE salary>5000

SELECT employee_id,first_name,e.department_id,department_name
FROM employees e , departments d
WHERE? e.department_id=d.department_id AND salary>5000

?

?? （3）外連接
???
???????? SELECT 目標(biāo)列
???????? FROM table1 LEFT|RIGHT|FULL JOIN table2
???????? ON?? table1.col1=table2.col2 （連接條件）
???????? WHERE 過濾條件

1)左外連接
SELECT employee_id,first_name,e.department_id,department_name
FROM employees e LEFT JOIN departments d
ON? e.department_id=d.department_id?

SELECT employee_id,first_name,e.department_id,department_name
FROM employees e , departments d
WHERE? e.department_id=d.department_id (+)

2）右外連接
SELECT employee_id,first_name,e.department_id,department_name
FROM employees e RIGHT JOIN departments d
ON? e.department_id=d.department_id?

SELECT employee_id,first_name,e.department_id,department_name
FROM employees e , departments d
WHERE? e.department_id(+)=d.department_id

3）全外連接

? SELECT employee_id,first_name,e.department_id,department_name
FROM employees e FULL JOIN departments d
ON? e.department_id=d.department_id?

?

分組函數(shù)：多行函數(shù) 統(tǒng)計(jì)函數(shù) 聚集函數(shù)
?SUM（DISTINCT|ALL 表達(dá)式或列名）：統(tǒng)計(jì)非空值的列的和
?AVG（DISTINCT|ALL 表達(dá)式或列名）：求非空值的列平均值
?MAX（DISTINCT|ALL 表達(dá)式或列名）：求非空值的列最大值
?MIX（DISTINCT|ALL 表達(dá)式或列名）：求非空值的列最小值
?COUNT（DISTINCT|ALL 表達(dá)式或列名）：求列值非空的記錄的個(gè)數(shù)
注意：如果某個(gè)記錄在該列的值為空，則不參與統(tǒng)計(jì)

?COUNT（*）：統(tǒng)計(jì)記錄個(gè)數(shù)

?

分組：

? SELECT 目標(biāo)列或分組函數(shù)
? FROM? 表
? WHERE 記錄過濾
? GROUP BY? 列名?? //指名分組列
? ORDER BY? 列名

? select department_id,count(*),avg(salary) from employees group by department_id

? 注意：1) 在目標(biāo)列中只能出現(xiàn)分組列或分組函數(shù)表達(dá)式
??????? 2) 如果進(jìn)行了分組，則分組函數(shù)作用范圍為組，對(duì)每個(gè)組進(jìn)行一次運(yùn)算
??????? 3）分組函數(shù)只可以出現(xiàn)在：SELECT、HAVING、ORDER BY子句后

1）統(tǒng)計(jì)不同職位的員工的人數(shù)、平均工資、最高工資
2）統(tǒng)計(jì)各個(gè)職位在各個(gè)部門中的平均工資、人數(shù)、最低工資
3）統(tǒng)計(jì)各個(gè)部門中工資高于5000的員工的人數(shù)。

?? SELECT 目標(biāo)列或分組函數(shù)
?? FROM? 表
?? WHERE 記錄過濾
?? GROUP BY? 列名?? //指名分組列??
?? HAVING 條件????? //對(duì)組進(jìn)行過濾
?? ORDER BY? 列名

?

子查詢

?? 概念：包含在另一個(gè)語句（select、update、insert、create... 、where 、having、from子句）中的select語句
?? 注意事項(xiàng)：
?? 1）先計(jì)算子查詢，將查詢的結(jié)果返回給外部語句使用
?? 2）在表達(dá)式中，子查詢必須放在運(yùn)算符的右側(cè)

?? 單行單列子查詢
?? 單行多列

?

?? 多行單列 ：返回結(jié)果為集合
?? 多行多列 ：返回結(jié)果為集合
??
??? 多行關(guān)系運(yùn)算符：IN? ALL ANY

??? IN：c in (x,y): c=x or c=y
??? NOT in : c not in(x,y) : c<>x and c<>y

??? any : 某一個(gè)

??? =any : c=any(x,y): in
??? >any: c>any(x,y) :表示大于其中某個(gè)值，即大于最小值
SELECT * FROM employees
WHERE salary? >ANY(SELECT salary FROM employees WHERE department_id=50)
???? AND department_id=10
????
SELECT * FROM employees
WHERE salary>(SELECT min(salary) FROM employees WHERE department_id=50)
???? AND department_id=10

???<any：c<any(x,y):表示小于其中某個(gè)值，即小于最大值

?? all

?? =all 無意義
?? >all：比其中所有值都大，即大于最大的
??<all ：比所有值都小，即小于最小
?? >=all：等于最大值
?? <=all：等于最小值

?

無關(guān)子查詢：子查詢的執(zhí)行與外部語句無關(guān)系
相關(guān)子查詢：子查詢在執(zhí)行時(shí)需要使用外部語句的信息

insert：
??? 1）單行插入

???? INSERT INTO 表名[(列名1[,列名2，…，列名n])]
???? VALUES (值1[,值2，…，值n]);

???? 注意：
???????? 值與列名順序是對(duì)應(yīng)關(guān)系；
???????? 如果插入一個(gè)完整記錄，且值的順序與表中列的順序一致，則可以省略列名
???????? 如果某列值為空，則可以使用NULL賦值或者不給該列賦值
???????? 字符值和日期值需要用單引號(hào)引起來

正常符號(hào)： 字母（大小寫）、數(shù)字（0-9）、_、#、$?

??? 2）多行插入
??? INSERT INTO表名[(列名1[,列名2，…，列名n])] 子查詢
???
??? 將子查詢的結(jié)果寫入表中。
??? 注意: 子查詢的結(jié)果與表結(jié)構(gòu)上兼容

??? 向部門表中插入一條記錄，部門號(hào)為800，部門名為‘SALES'，其他信息與10號(hào)部門相同。

??? 3）數(shù)據(jù)裝載

????? 數(shù)據(jù)操作過程不寫入重做日志文件，所以出現(xiàn)故障無法恢復(fù)。
????? 語法：

??????? INSERT /*+APPEND*/INTO 表名[(列名1[,列名2，…，列名n])] 子查詢

??? 4）多表插入

update
delete：

? 單條記錄操作?
? 多條記錄操作
? 利用子查詢操作

事務(wù)控制：

??? 概念：作為整體的一組操作，要么全執(zhí)行，要么一個(gè)也不執(zhí)行

??? 特性：A(原子性）C（一致性）I（隔離性）D（持久性）

??? 事務(wù)控制方式：
???????? 1）顯式控制： commit、rollback
???????? 2）隱式控制： ddl（CREATE、ALTER、DROP、TRUNCATE）、dcl（GRANT、REVOKE）
?????????????????????? 用戶退出（正常退出 commit、非正常退出rollback）
?????????????????????? 系統(tǒng)崩潰（rollback）

????

表：
?? 命名規(guī)范：
　　　　?。保┮宰帜搁_頭，后面接字母、數(shù)字、＿、＃、＄，總長度不超過３０個(gè)字符
　　　　　２）如果包含關(guān)鍵字、空格、區(qū)分大小寫，則需要用雙引號(hào)引起來。
　

　　直接創(chuàng)建表：
　　　　　CREATE TABLE [schema.]table(column datatype [DEFAULT expr][, ...]);

　　使用子查詢間接創(chuàng)建表：
　　　　CREATE TABLE table[(column, column...)]
　　　　AS subquery;

　　　?。保┤绻付忻?，則列名與子查詢目標(biāo)列之間需要對(duì)應(yīng)（個(gè)數(shù)、順序）
　　　?。玻┤绻恢付忻?，會(huì)把子查詢的列名當(dāng)作表的列名，此時(shí)，子查詢的列名必須存在
　　　?。常┳硬樵儗?duì)應(yīng)表中的約束，除了非空約束，其他約束都不會(huì)帶入新建的表中。
　　　?。矗┤绻硬樵冇袛?shù)據(jù)，則數(shù)據(jù)插入新表；如果子查詢沒有數(shù)據(jù)，則創(chuàng)建一個(gè)空表。
　　　?。担┎荒苤付械臄?shù)據(jù)類型，列的數(shù)據(jù)類型取決于子查詢中的表的相應(yīng)列。

　　修改表結(jié)構(gòu)：
　　　ALTER TABLE table_name
????? ADD (column_name datatype DEFAULT...,....)
??????? 如果表中已經(jīng)有數(shù)據(jù)，則新填加的列不能有非空的約束。

????? ALTER TABLE table_name
????? MODIFY(column_name datatype defult...)
??????? 1）如果已經(jīng)有數(shù)據(jù)，注意修改后的數(shù)據(jù)類型長度要滿足已有數(shù)據(jù)的要求
??????? 2)如果沒有數(shù)據(jù)，可以修改為非同一系列的數(shù)據(jù)類型
??????? 3）缺省值的修改只影響以后的數(shù)據(jù)
?
????? ALTER TABLE table_name
????? DROP COLUMN column_name? //單列刪除
????

????? ALTER TABLE table_name????
????? DROP (column1,column2...)//多列刪除
　　　　　　
?????? 1）如果當(dāng)前列被其他對(duì)象引用則不能刪除

???? 刪除表
??????? drop table table_name [purge][cascade constraints]

?

?? 表的截?cái)郥RUNCATE
???? truncate table table_name
???? 注意：截?cái)啾硎乔蹇毡碇兴袛?shù)據(jù)，與delete相似，與drop完全不同。
?????????? 與delete相比，truncate由于不寫日志文件，所有效率高，但出現(xiàn)故障無法恢復(fù)。

?? 約束：
???? 作用：保證數(shù)據(jù)完整性
???? 類型：
???????? 主鍵約束（primary key）:? 唯一標(biāo)識(shí)一個(gè)記錄,取之不重復(fù),不能為空
???????? 外鍵約束(foreign key):一個(gè)表中某列取值參照與另一個(gè)表的主鍵列或唯一性約束列的值,或?yàn)榭?br />???????? 唯一性約束(unique):列值不重復(fù),但可以為空
???????? 檢查約束(check):限制列的取值范圍
???????? 非空約束(not null):列值不能為空

????? 約束的創(chuàng)建:
???????? 創(chuàng)建表示直接創(chuàng)建約束
???????? 創(chuàng)建表后為表添加約束

????? 約束的表示形式:
???????? 列級(jí)約束:在定義列的同時(shí)定義約束
???????? 表級(jí)約束:在定義完所有列之后,定義約束(非空約束不能使表級(jí)約束)

????? CREATE TABLE [schema.] table(
??????? column datatype [ DEFAULTexpr][column_constraint],
??????? ......,
??????? [table_constraint][,...]);

CREATE TABLE student (
?sno NUMBER PRIMARY KEY,
?sname CHAR(10) UNIQUE,
?sex CHAR(2) DEFAULT 'm' CHECK(sex IN ('m','f')),
?sage NUMBER CHECK(sage BETWEEN 15 AND 40),
?birthdate DATE NOT NULL)
?
?CREATE TABLE course(
?cid NUMBER CONSTRAINT p_cid PRIMARY KEY,
?cname CHAR(20) CONSTRAINT u_cname UNIQUE
?)
?
CREATE TABLE sc(
sno NUMBER REFERENCES student(sno),
cid NUMBER REFERENCES course(cid),
grade NUMBER CHECK(grade BETWEEN 0 AND 100),
CONSTRAINT p_sc PRIMARY KEY(sno,cid)