第一篇：Oracle數(shù)據(jù)庫 知識點總結(jié)
1.constraint約束：
alter table [table_name] add constraint [pk_name] primary key(pkname);//添加主鍵
alter table [table_name] drop constraint [pk_name];//刪除主鍵
alter table [table_name] add constraint [fk_name] foreign key(fkname)references [tablename](fkname);//添加外
鍵
alter table [table_name] drop constraint [fk_name];//刪除外鍵
2.union 關(guān)鍵字：
A username, B username
rod
bruce
rose
marina
select username from A
union
select username from B
2、幾個高級查詢運(yùn)算詞
A： UNION 運(yùn)算符
UNION 運(yùn)算符通過組合其他兩個結(jié)果表（例如 TABLE1 和 TABLE2）并消去表中任何重復(fù)行而派生出一個結(jié)果表。當(dāng)
ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重復(fù)行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來 自 TABLE2。
B： EXCEPT 運(yùn)算符
EXCEPT 運(yùn)算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復(fù)行而派生出一個結(jié)果表。當(dāng) ALL 隨
EXCEPT 一起使用時(EXCEPT ALL)，不消除重復(fù)行。
C： INTERSECT 運(yùn)算符
INTERSECT 運(yùn)算符通過只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復(fù)行而派生出一個結(jié)果表。當(dāng) ALL 隨
INTERSECT 一起使用時(INTERSECT ALL)，不消除重復(fù)行。
注：使用運(yùn)算詞的幾個查詢結(jié)果行必須是一致的。
3.復(fù)合主鍵一般不設(shè)外鍵
4.組函數(shù)也稱為聚合函數(shù)。
例如：我們把學(xué)生可以分為男生和女生兩個組，如果想求每組的人數(shù)，平均身高，平均年齡等，就需要用到分組函數(shù)了。
在SQL中常用的組函數(shù)有以下幾個：
COUNT()：求出全部的記錄數(shù)
MAX()：求出一組中的最大值
MIN()：求出一組中的最小值
AVG()：求出一組中的平均值
SUM()：求和
范例：COUNT()函數(shù)
SELECT COUNT(empno)FROM emp;
我們常用COUNT(*)，最好能夠用字段代替*
范例：MAX()、MIN()函數(shù)，求最大最小值，一般是針對于數(shù)值的字段的，求出所有員工的的最高工資，和最底工資和平均工
資。
SELECT MAX(sal)最高工資,MIN(sal)最底工資,AVG(sal)平均工資 FROM emp;
范例：求出部門１０的所有員工工資的總合
SELECT SUM(sal)工資綜合 FROM emp WHERE deptno=10;
如果如下查詢輸出部門編號和其部門所有員工的工資總和，會產(chǎn)生錯誤。
SELECT deptno ,SUM(sal)工資綜合 FROM emp WHERE deptno=10;
錯誤： “不是單組分組函數(shù)”
發(fā)生以上的錯誤信息，是因為這樣的查詢需要進(jìn)行分組統(tǒng)計。
分組統(tǒng)計有其固定的語法格式：
SELECT {DISTINCT} *| 查詢列 列別名1，查詢列2 列別名2,……
FROM 表名稱１ 表別名１，表名稱２ 表別名２,……
{WHERE 條件(s)}
{ORDER BY 排序的字段1,排序的字段2 ASC|DESC}
{GROUP BY 分組字段}
所以老師寫的是錯的！
5.//從t_student表中刪除名字重復(fù)的記錄的信息
delete from t_student where sid not in(select sid from（select min(sid)sid,sname from t_student group by sname));
// delete from tablename where id not in(select max(id)from tablename group by col1,col2,...)
6.sequence
在oracle中sequence就是序號，每次取的時候它會自動增加。sequence與表沒有關(guān)系。
（1）
CREATE SEQUENCE seqTest
INCREMENT BY 1--每次加幾個
START WITH 1--從1開始計數(shù)
NOMAXvalue--不設(shè)置最大值
NOCYCLE--一直累加，不循環(huán)
CACHE 10;--設(shè)置緩存cache個序列，如果系統(tǒng)down掉了或者其它情況將會導(dǎo)致序列不連續(xù)，也可以設(shè)置為----NOCACHE（2）
定義好sequence后，你就可以用currVal，nextVal取得值。
CurrVal：返回 sequence的當(dāng)前值
NextVal：增加sequence的值，然后返回 增加后sequence值
eg：
SELECT Sequence名稱.CurrVal FROM DUAL;
select seqtest.currval from dual（3）
在Sql語句中可以使用sequence的地方：
-不包含子查詢、snapshot、VIEW的 SELECT 語句
-INSERT語句的子查詢中
-INSERT語句的values中
-UPDATE 的 SET中
如在插入語句中
insert into 表名(id,name)values(seqtest.Nextval,'sequence 插入測試');
7.范式：
所謂第一范式（1NF）是指在關(guān)系模型中，對域添加的一個規(guī)范要求，所有的域都應(yīng)該是原子性的，即數(shù)據(jù)庫表的每一
列都是不可分割的原子數(shù)據(jù)項，而不能是集合，數(shù)組，記錄等非原子數(shù)據(jù)項。即實體中的某個屬性有多個值時，必須拆分
為不同的屬性。在符合第一范式（1NF）表中的每個域值只能是實體的一個屬性或一個屬性的一部分。簡而言之，第一范式
就是無重復(fù)的域。
第二范式（2NF）是在第一范式（1NF）的基礎(chǔ)上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。
第二范式（2NF）要求數(shù)據(jù)庫表中的每個實例或記錄必須可以被唯一地區(qū)分。選取一個能區(qū)分每個實體的屬性或?qū)傩越M，作
為實體的唯一標(biāo)識。第二范式（2NF）要求實體的屬性完全依賴于主關(guān)鍵字。所謂完全依賴是指不能存在僅依賴主關(guān)鍵字一
部分的屬性，如果存在，那么這個屬性和主關(guān)鍵字的這一部分應(yīng)該分離出來形成一個新的實體，新實體與原實體之間是一
對多的關(guān)系。為實現(xiàn)區(qū)分通常需要為表加上一個列，以存儲各個實例的唯一標(biāo)識。簡而言之，第二范式就是在第一范式的基礎(chǔ)上屬性完全依賴于主鍵。例如在員工表中的身份證號碼即可實現(xiàn)每個一員工的區(qū)分，該身份證號碼即為候選鍵，任何
一個候選鍵都可以被選作主鍵。在找不到候選鍵時，可額外增加屬性以實現(xiàn)區(qū)分，如果在員工關(guān)系中，沒有對其身份證號
進(jìn)行存儲，而姓名可能會在數(shù)據(jù)庫運(yùn)行的某個時間重復(fù)，無法區(qū)分出實體時，設(shè)計辟如ID等不重復(fù)的編號以實現(xiàn)區(qū)分，被
添加的編號或ID選作主鍵。
第三范式（3NF）是第二范式（2NF）的一個子集，即滿足第三范式（3NF）必須滿足第二范式（2NF）。簡而言之，第三范式（3NF）要求一個關(guān)系中不包含已在其它關(guān)系已包含的非主關(guān)鍵字信息。例如，存在一個部門信息表，其中每個部門
有部門編號（dept_id）、部門名稱、部門簡介等信息。那么在員工信息表中列出部門編號后就不能再將部門名稱、部門簡
介等與部門有關(guān)的信息再加入員工信息表中。如果不存在部門信息表，則根據(jù)第三范式（3NF）也應(yīng)該構(gòu)建它，否則就會有
大量的數(shù)據(jù)冗余。簡而言之，第三范式就是屬性不依賴于其它非主屬性，也就是在滿足2NF的基礎(chǔ)上，任何非主屬性不得傳
遞依賴于主屬性。
第二篇：Oracle數(shù)據(jù)庫總結(jié)范文
創(chuàng)建表及命名規(guī)則？
表名和列名:
必須以字母開頭 必須在1–30個字符之間
必須只能包含A–Z, a–z, 0–9, _, $, 和# 必須不能和用戶定義的其他對象重名 必須不能是Oracle 的保留字 Oracle默認(rèn)存儲是都存為大寫
增刪改查語法？
增加: 例如:使用INSERT語句往customers表中插入數(shù)據(jù)，指定相關(guān)列和值 INSERT INTO customers(customer_id, first_name, last_name, dob, phone)VALUES(6, 'Fred', 'Brown', '01-1月-1970', '800-555-1215');
如果為表所有列都指定值，那么可以忽略列清單
INSERT INTO customersVALUES(6, 'Fred', 'Brown', '01-1月-1970', '800-555-1215');
可以使用NULL為某些列指定空值
INSERT INTO customersVALUES(8, 'Sophie', 'White', NULL, NULL);
查詢：select * fromcustomers；或者select字段 fromcustomerswhere條件 刪除：deletefromcustomerswhere條件
更改：update customersset name = 'xiaoming',age = ‘16’(更改多個字段時候用逗號隔開)where 條件
對查詢結(jié)果進(jìn)行排序？
語句：select * fromcustomersorderby字段 desc；
（asc（升序），desc（降序）如果不寫，默認(rèn)升序）
NULL值了解么？
NULL值表示未知的值。它是一個特殊的值，但并不是空字符串，NULL值表示該列是未知的。當(dāng)某些查詢語句在輸出結(jié)果列上看不到值的時候，可能就是NULL值
NVL（）和NVL2（）：
NULL值被查詢出來的時候沒有顯示信息，如何告知用戶這是空字符串還是NULL，這可以通過NVL()函數(shù)來進(jìn)行處理
NVL(x，value)是有value顯示本身，null顯示為替換的value NVL2(x,value1,value2)是如果x不為NULL值，返回value1，否則返回value2 例程：
select name,nvl2(email,'已知','未知')from student;【代碼含義：代表如果email字段中有值，則顯示已知，null則顯示未知，如果想顯示本來的查詢結(jié)果select name,nvl(email,'未知')from student;】 update student set name = replace(name,'小','大');【代碼含義：代表將STUDENT表中NAME 字段中如果含有小字，那么就將小字替換為大字（操作的不是顯示結(jié)果，而是將表中數(shù)據(jù)進(jìn)行更改）】
Oracle中的簡單函數(shù)？
字符串函數(shù)
? concat：將x和y拼接起來，并返回新字符串
例程：
select concat(first_name,'-'||last_name)姓名 from customers;? Instr字符查找，從1開始。
select instr('asdbcrdbewqrbmde','b')from dual;select instr('asdbcrdbewqrbmde','b'，5，2)from dual;【代表從第5個字符開始，第二個b所在的位置】
? Ltrim : LTRIM(x，[trim_string])從x字符串左側(cè)去除所有的trim_string字符串，如果沒有指定trim_string字符串，則默認(rèn)為去除左側(cè)空白字符
? Rtrim RTRIM(x，[trim_string])從x字符串右側(cè)去除所有的trim_string字符串，如果沒有指定trim_string字符串，則默認(rèn)為去除右側(cè)空白字符 ? Trim TRIM(trim_string FROM x)從x字符串兩側(cè)去除trim_string字符串
? Replace REPLACE(x, search_string, replace_string)從字符串x中搜索search_string字符串，并使用replace_string字符串替換。用select執(zhí)行并不會修改數(shù)據(jù)庫中原始值，但是用update執(zhí)行可以修改。
? Substr SUBSTR(x, start，[length])返回字符串中的指定的字符，這些字符從字符串的第start個位置開始，長度為length個字符；如果start是負(fù)數(shù)，則從x字符串的末尾開始算起；如果length省略，則將返回一直到字符串末尾的所有字符
例程：
select substr('abcd月fg',4,2)from dual;（結(jié)果：d月）select substr('abcdefg',-2)from dual;（結(jié)果：fq）
日期函數(shù)
? Sysdate 例程：
Selectsysdatefromdual；
Select to_char(sysdate, 'yyyy-mm-dd-hh-mm-ss')from dual;
? Add_months(d1,n1)? last_day()：
轉(zhuǎn)換函數(shù)
? To_char TO_CHAR(x，[ format])將x轉(zhuǎn)化為字符串。format為轉(zhuǎn)換的格式，可以為數(shù)字格式或日期格式
select to_char(sysdate,'yyyy-mm-dd')from dual;【一般在查詢時候使用，使返回的值成為指定格式】
? to_date TO_DATE(x，[format])將x字符串轉(zhuǎn)換為日期
insert into student values(seq_stu.nextval,'小明',to_date('1992-2-18','yyyy-mm-dd'),default,'北京',null);【一般在添加使用】 返回所查詢的值中最后一個日期數(shù)據(jù)。
聚合函數(shù)
? Avg:平均數(shù) ? Sum：求和 ? Max：最大值 ? Min：最小值 ? Count：返回統(tǒng)計的行數(shù) ? Round：四舍五入
例程：
select round(avg(bid),1)from bug;分組了解么？
有時需要對表中的行進(jìn)行分組，然后統(tǒng)計每組的信息，可以使用GROUP BY進(jìn)行分組，然后再對每組進(jìn)行統(tǒng)計。
(1)可以使用GROUP BY對多個列進(jìn)行分組
例：
SELECT product_id, avg(customer_id)FROM purchases GROUP BY product_id;
(2)可以對分組后的行使用聚集函數(shù)，聚集函數(shù)會統(tǒng)計每組中的值，對于每組分別統(tǒng)計后返回一個值 例:
SELECT
product_type_id，BY
COUNT(ROWID)FROM
BY
productsGROUP product_type_id;注意:
product_type_idORDER a）如果查詢中使用了聚集函數(shù)，被查詢的列未使用聚集函數(shù)處理，那么這些列必須出現(xiàn)在GROUP BY子句后，否則，會提示ORA-00937錯誤
b)不能使用聚集函數(shù)作為WHERE子句的篩選條件，否則，會提示ORA-00934錯誤
c)可以使用HAVING子句過濾分組后的行
SELECT...FROM...WHERE GROUP BY...HAVING...ORDER BY...;(GROUP BY使用時可以不使用HAVING，但是使用HAVING時必須有GROUP BY才有意義)
(3)同時使用WHERE, GROUP BY和HAVING
a)首先，執(zhí)行WHERE篩選掉不符合條件的行 b)然后，將符合條件的行使用GROUP BY進(jìn)行分組 c)最后，使用HAVING對分組統(tǒng)計的結(jié)果進(jìn)行再次篩選 例:
SELECT product_type_id, AVG(price)FROM products
WHERE price < 15
GROUP BY product_type_id HAVING AVG(price)> 13 ORDER BY product_type_id;
表的約束條件？
目的：
確保表中數(shù)據(jù)的完整性。
常用的約束類型： 主鍵約束(PRIMARY KEY)：要求主鍵列數(shù)據(jù)唯一，并且不允許為空 非空約束(NOT NULL)：指定的列的值不允許為空
唯一鍵約束(UNIQUE)：要求該列唯一，允許為空，但只能出現(xiàn)一個空
值
檢查約束(CHECK)：指定表中一列或多列可以接受的數(shù)據(jù)值格式 默認(rèn)約束(DEFAULT)：指定某列的默認(rèn)值
外鍵約束(FOREIGN KEY)：用于建立和加強(qiáng)兩個表數(shù)據(jù)之間連接的一
列或多列。通過將表中的主鍵列添加到另一個表中。可以創(chuàng)建兩個表之間的連接。這個主鍵的列就稱為第二個表的外鍵。外鍵約束就可以確保添加到外鍵表中的任何行都在主表中都存在相應(yīng)的行
多表查詢？
不同的數(shù)據(jù)存儲在不同的表中，通常要查詢多張表才能找到需要的數(shù)據(jù)
例程： SELECT products.name, product_types.name FROM products, product_types WHERE
products.product_type_id
= product_types.product_type_id AND products.product_id = 3;
products表和product_types表相關(guān)字段會用在SELECT語句及WHERE子句上，可以給表起別名，提高代碼可讀性、降低書寫難度 例程：
SELECT p.name, pt.name FROM products p, product_types pt WHERE p.product_type_id = pt.product_type_id AND p.product_id = 3 注意：
如果查詢兩張表，并且沒有定義連接條件，那么查詢的結(jié)果集是兩表相乘的結(jié)果，這樣的情況稱之為笛卡爾乘積。總結(jié)：多表查詢WHERE時，連接次數(shù)=查詢時連接表的數(shù)量-1
常見的三種連接類型：
內(nèi)連接：
內(nèi)連接返回的行只有滿足連接條件才會返回。如果連接條件的列中有NULL值，那么該行則不會返回 外連接：
外連接返回的行滿足連接條件，也包括在連接條件的列包含空值的行
自連接：
連接的表為同一張表
子查詢？
子查詢是嵌入到另一個SELECT語句中的一個SELECT語句。通過使用子查詢，可以使用簡單的語句組成強(qiáng)大的語句。當(dāng)需要從表中選擇行，而選擇條件卻取決于該表自身中的數(shù)據(jù)時，子查詢非常有用。
單行子查詢:（1）可以將另外一個查詢作為WHERE子句的子查詢
例：查詢尾名是‘Brown’的首名和尾名
SELECT first_name, last_name FROM customers
WHERE customer_id =
(SELECT customer_id FROM customers WHERE last_name = 'Brown');(2)在單行子查詢還可以使用其他比較運(yùn)算符，如<>、<、>、<=和>= 例：查詢價格大于平均價格的商品編號、名稱及價格
WHERE子句中使用>，以及子查詢中使用AVG()聚集函數(shù)
SELECT product_id, name, price FROM products WHERE price >(SELECT AVG(price)FROM products);(3)在HAVING子句中使用子查詢
HAVING是在分組統(tǒng)計后用于過濾行，同樣在HAVING子句后面可以跟子查詢。單行子查詢將返回結(jié)果用于HAVING子句過濾分組統(tǒng)計的行
例如：查詢平均價格小于最大平均值的商品編號及平均值
SELECT product_type_id, AVG(price)FROM products GROUP BY product_type_id HAVING AVG(price)<(SELECT MAX(AVG(price))FROM products GROUP BY product_type_id)ORDER BY product_type_id;
分頁查詢？
可以通過ROWNUM來實現(xiàn)。
序列？
序列是一個數(shù)據(jù)庫對象，用于生成一系列的整數(shù)。
索引？
索引是與表關(guān)聯(lián)的可選結(jié)構(gòu)。可以創(chuàng)建索引以加快對表執(zhí)行SQL語句的速度。就像書的索引可以幫助我們更快速的查找信息一樣，Oracle中的索引也提供了一種更快地訪問表數(shù)據(jù)的途徑。
視圖？
視圖是基于一張表或多張表或另外一個視圖的邏輯表。視圖不同于表，視圖本身不包含任何數(shù)據(jù)。表是實際獨立存在的實體，是用于存儲數(shù)據(jù)的基本結(jié)構(gòu)。而視圖只是一種定義，對應(yīng)一個查詢語句。視圖的數(shù)據(jù)都來自于某些表，這些表被稱為基表。數(shù)據(jù)庫中只在數(shù)據(jù)字典中存儲對視圖的定義。
第三篇：Oracle數(shù)據(jù)庫學(xué)習(xí)總結(jié)
Oracle數(shù)據(jù)庫學(xué)習(xí)總結(jié)
1.set linesize xx;設(shè)置行間距，常用數(shù)值有100，200，300
2.set pagesize xx;設(shè)置每頁顯示行數(shù)
3.ed x;表示新建一個x.sql文件，通過文件編輯SQL語句，然后用@x命令可以調(diào)用剛才的命令
4.CONN username/password;命令可以建立用戶的連接，需要注意的是sys用戶是超級管理員，連接是時需要在末尾加上AS SYSDBA 以系統(tǒng)管理員的身份進(jìn)行連接
5.如果表是歸某個用戶特有的，在查詢的時候需要加上用戶名 即以 用戶名.表名 的格式查詢
6.SHOW USER;命令可以顯示當(dāng)前連接的用戶名
7.SELECT * FROM tab;可以顯示當(dāng)前用戶下的所有數(shù)據(jù)表
8.“ / ”表示重復(fù)執(zhí)行上一次的SQL命令操作
9.SELECT xx別名,xx 別名 FROM xx;搜索指定列名，并指定別名，方便顯示
10.關(guān)鍵字DISTINCT 可以消除重復(fù)值 如 SELECT DISTINCT xx FROM xx;
11.Oracle中提供的字符串連接操作，使用“||”表示，相當(dāng)于Java的“+”普通字符用“ ' ” 括起來
如: SELECT'員工姓名是'||ename||'員工卡號是'||empnoFROM emp;
12.查詢語句 BETWEEN xx AND xx 是包括邊界的13.查詢?nèi)掌诘臅r候要加上''把日期引起來
例如：SELECT * FROM emp WHERE hiredate BETWEEN '1-1月-81' AND '08-9月-81';
14.模糊查詢中“%”可以匹配任意長度的內(nèi)容，“_”可以匹配一個長度的內(nèi)容，如果沒輸入模糊查詢關(guān)鍵字，那么默認(rèn)查詢?nèi)繑?shù)據(jù)，like關(guān)鍵字可以用在任何地方，可以匹配數(shù)字、字符、日期等。
15.SQL中不等于可以用“<>”或者“!=”表示
16.ORDER BY語句中 ASC表示升序，DESC表示降序，在沒指定的時候默認(rèn)按照升序排序
17.Oracle中的單行函數(shù)有如下，默認(rèn)的所有的函數(shù)都要到表中執(zhí)行，加上關(guān)鍵字DUAL只會產(chǎn)生一個臨時表
UPPER('xxx')將小寫轉(zhuǎn)換為大寫
LOWER('xxx')將大寫轉(zhuǎn)換為小寫
INITCAP('xxx')將首字母大寫
CONCAT('','')字符串連接
SUBSTR('xxx',x,x)字符串截取，從0或1開始截取效果是一樣的，因為Oracle比較智能,要是輸入的參數(shù)為負(fù)數(shù)，則表示倒著截取
LENGTH('xxx')字符串長度
REPLACE('xxx','x','x')字符串替換
ROUND(xxx,xx)四舍五入 xxx需要四舍五入的數(shù)值，xx保留的小數(shù)位，可以加負(fù)數(shù) TRUNC(xxx)截斷操作，默認(rèn)小數(shù)點后的全部截斷，也可以指定小數(shù)點保留位數(shù)如TRUNC(789.536,2)得到的結(jié)果是789.53，也可以加負(fù)數(shù)如TRUNC(789.536,-2)結(jié)果是700
18.SELECT sysdate FROM DUAL;可以求出當(dāng)前的日期
19.Oracle 中提供了以下日期函數(shù)支持：
MONTHS_BETWEEN()求出給定日期范圍的月數(shù)
ADD_MONTHS(xxx,xxx)在指定日期加上指定的月數(shù)
NEXT_DATE(xxx,'')求出下一個給定日期數(shù)
TO_CHAR()可以將年、月、日進(jìn)行分割
例如
TO_CHAR(hirdate,'yyyy')year,TO_CHAR(hirdate,'mm')months,TO_CHAR(hirdate,'dd')day 還可以對時間進(jìn)行格式化輸出 如TO_CHAR(hirdate,'yyyy-mm-dd')
TO_CHAR(hirdate,'fmyyyy-mm-dd')可以去掉前導(dǎo)0
TO_CHAR()還可以對數(shù)字進(jìn)行格式化 如
SELECT ename,TO_CHAR(SAL,'99,999')FROM emp;
注意：一定要用9來表示
$表示美元符號，L表示Local的縮寫，以本地語言進(jìn)行金額顯示
TO_NUMBER()將字符串變?yōu)閿?shù)字
TO_DATE()將字符串變?yōu)镈ate類型 例如 SELECT TO_DATE('2009-12-8','yyyy-mm-dd')FROM dual;
TO_NVL()可以將NULL的內(nèi)容變?yōu)橹付ǖ膬?nèi)容
DECODE()相當(dāng)于Java的if else else語句
例如SELECT DECODE(1,1,'內(nèi)容是1',2,'內(nèi)容是2',3,'內(nèi)容是3')FROM dual;將輸入 內(nèi)容是1
20.左右連接
SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d WHERE
e.deptno(+)=d.deptno;此例中是右連接，以deptno表為準(zhǔn)。
21.SQL1999語法
CROSS JOIN 交叉連接 會產(chǎn)生笛卡爾積
NATURAL JOIN 自然連接 自動進(jìn)行關(guān)聯(lián)字段匹配 可以消除笛卡爾積
USING 子句：直接關(guān)聯(lián)操作列 如 SELECT * FROM emp e JOIN dept USING(deptno)WHERE deptno=30;
ON 子句 用戶自己編寫連接條件
LETF JOIN/RIGHT JOIN 左右連接
SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e right OUTER JOIN dept d ON(e.deptno=d.deptno);
22.分組查詢GROUP BY 放在where之后
常見的組函數(shù)有
COUNT();
MAX();
MIN();
AVG();
SUM();
用法如下：SELECT deptno,count(empno)FROM emp GROUP BY deptno;
語法：SELECT deptno,empno,count(empno)FROM emp GROUP BY deptno;是錯誤的，原因是使用分組函數(shù)的時候，不能出現(xiàn)分組函數(shù)和分組條件以外的字段。
語法：SELECT deptno,count(empno)FROM emp；是錯誤的，原因是不使用分組的時候，則只能單獨使用分組函數(shù)
分組函數(shù)只能在分組中使用，不允許子啊where語句中個使用，要使用個分組條件可以加上HAVING
例如：SELECT deptno,avg(sal)FROM emp GROUP BY deptno having avg(sal)>2000;注意：分組函數(shù)可以嵌套使用，但是在組函數(shù)嵌套使用的時候不能再出現(xiàn)分組條件的查詢語句
如下語法是錯誤的：SELECT deptno,max(avg(sal))FROM emp GROUP BY deptno;不能出現(xiàn)deptno
如下語法是正確的：SELECT max(avg(sal))FROM emp GROUP BY deptno;
23.子查詢中
>ANY 比里面的最小值大
=ANY 與IN用法相同 >ALL 比里面的最大值大 24.表復(fù)制 CREATE TABLE myemp AS SELECT * FROM emp;既復(fù)制表結(jié)構(gòu)，又復(fù)制表內(nèi)容 CREATE TABLE myemp AS SELECT * FROM emp where 1=2;后面的條件不可能成立，只復(fù)制表結(jié)構(gòu) 25.Oracle 中常用的數(shù)據(jù)類型 VARCHAR、VARCHAR2 代表一個字符串，有長度限制，為255 NUMBER 分為兩種 1)NUMBER(n)代表一個整數(shù)，數(shù)字的長度是n,可以使用INT 2)NUMBER(m,n)代表一個小數(shù)，小數(shù)長度為n，整數(shù)長度為m-n，可以使用FLOAT DATE 代表日期的類型，日期要按照標(biāo)準(zhǔn)的日期格式進(jìn)行存放 CLOB 大對象，表示大文本數(shù)據(jù),一般可以存放4G的文本 BLOB 大對象，表示二進(jìn)制數(shù)據(jù)最大可以存放4G，例如存放歌曲、電影、圖片 26.表的創(chuàng)建 CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200),birthdate DATE,age NUMBER(4),sex VARCHAR2(2)DEFAULT '男'); 27.插入數(shù)據(jù) INSERT INTO person(pid,name,birthdate,age)VALUES('222','里斯 ',TO_DATE('1989-02-09','yyyy-mm-dd'),45); 28.更改表中數(shù)據(jù) 增加表結(jié)構(gòu)：ALTER TABLE person ADD(address VARCHAR2(50)DEFAULT '暫無地址'); 修改已存在的列：ALTER TABLE person MODIFY(name VARCHAR2(40)DEFAULT '無名氏'); 29.表的重命名（只能在Oracle中使用） RENAME XXX TO XXX; 30.約束（主要分為5類） 1）主鍵約束 主鍵表示是一個唯一的標(biāo)識。本身不能為空 2）唯一約束 在一個表中只允許建立一個主鍵約束，而其他列如果不希望重復(fù)值的話，則可以使用唯一約束 3）檢查約束 檢出一個列的內(nèi)容是否合法 4）非空約束 5）外鍵約束 在兩張表中進(jìn)行約束的操作 刪除時應(yīng)該先刪除子表，再刪除父表 創(chuàng)建主鍵： 語法1 CREATE TABLE person（pid VARCHAR2(18)PRIMARY KEY,name VARCHAR2(200),birthdate DATE,age NUMBER(4),sex VARCHAR2(2)DEFAULT '男'); 語法2： CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200),birthdate DATE,age NUMBER(4),sex VARCHAR2(2)DEFAULT '男',CONSTRAINT p_id PRIMARY KEY(pid)); 創(chuàng)建非空約束 CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200)NOT NULL,birthdate DATE,age NUMBER(4),sex VARCHAR2(2)DEFAULT '男'); 創(chuàng)建唯一約束 語法一 CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200)UNIQUE NOT NULL,birthdate DATE,age NUMBER(4),sex VARCHAR2(2)DEFAULT '男'); 語法二 CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200)NOT NULL,birthdate DATE,age NUMBER(4),sex VARCHAR2(2)DEFAULT '男',CONSTRAINT p_name UNIQUE(name),); 創(chuàng)建檢查約束 語法一 CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200)NOT NULL,birthdate DATE,age NUMBER(4)NOT NULL CHECK(age BETWEEN 0 AND 150),sex VARCHAR2(2)DEFAULT '男' CHECK(sex IN('男','女','中')),); 語法二 CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200)NOT NULL,birthdate DATE,age NUMBER(4)NOT NULL ,sex VARCHAR2(2)DEFAULT '男' ,CONSTRAINT p_age CHECK(age BETWEEN 0 AND 150),CONSTRAINT p_sex CHECK(sex IN('男','女','中'))); 創(chuàng)建外鍵約束 --person表 CREATE TABLE person（pid VARCHAR2(18)PRIMARY KEY,name VARCHAR2(200)NOT NULL,birthdate DATE,age NUMBER(4)NOT NULL ,sex VARCHAR2(2)DEFAULT '男'); --book表 CREATE TABLE book（bid NUMBER PRIMARY KEY,bname VARCHAR2(30),bprice NUMBER(5,2),pid VARCHAR2(18),CONSTRAINT b_pid_fk FOREIGN KEY(pid)REFERENCES person(pid)); 對于刪除，應(yīng)該先刪除book表再刪除person表 也可以使用級聯(lián)刪除,強(qiáng)制刪除某張表 DROP TABLE person CASCADE CONSTRAINT; 設(shè)置外鍵約束級聯(lián)刪除 --person表 CREATE TABLE person（pid VARCHAR2(18)PRIMARY KEY,name VARCHAR2(200)NOT NULL,birthdate DATE,age NUMBER(4)NOT NULL ,sex VARCHAR2(2)DEFAULT '男'); --book表 CREATE TABLE book（bid NUMBER PRIMARY KEY,bname VARCHAR2(30),bprice NUMBER(5,2),pid VARCHAR2(18),CONSTRAINT b_pid_fk FOREIGN KEY(pid)REFERENCES person(pid)ON DELETE CASCADE); 31.修改約束 如果一張表已經(jīng)建立完成之后，則可以為其添加約束 ALTER TABLE 表名稱 ADD CONSTRAINT 約束名稱 約束類型(約束字段); 關(guān)于約束名稱的命名最好要統(tǒng)一： PRIMARY KEY ：主鍵字段_PK UNIQUE:字段_UK CHECK:字段_CK FOREIGH KEY:父字段_子字段_FK 例如： DROP TABLE person; CREATE TABLE person（pid VARCHAR2(18),name VARCHAR2(200)NOT NULL,birthdate DATE,age NUMBER(4)NOT NULL ,sex VARCHAR2(2)DEFAULT '男'); ALTER TABLE person ADD CONSTRAINT pid_PK PRIMARY KEY(pid); ALTER TABLE person ADD CONSTRAINT name_UK UNIQUE(name); ALTER TABLE person ADD CONSTRAINT age_CK CHECK(age BETWEEN 1 AND 150);CREATE TABLE book（bid NUMBER ,bname VARCHAR2(30),bprice NUMBER(5,2),pid VARCHAR2(18)); ALTER TABLE book ADD CONSTRAINT book_PK PRIMARY KEY(bid); ALTER TABLE book ADD CONSTRAINT pid_FK FOREIGN KEY(pid)REFERENCES person(pid);刪除約束 ALTER TABLE person DROP CONSTRAINT name_UK; ALTER TABLE person DROP CONSTRAINT age_CK; 數(shù)據(jù)庫學(xué)習(xí)總結(jié)-Marlon 目錄一、二、三、四、五、六、七、八、ORACLE_簡介.............................................................................................................................................................1 ORACLE_簡單查詢.....................................................................................................................................................3 ORACLE標(biāo)量函數(shù)和算數(shù)運(yùn)算..................................................................................................................................5 ORACLE_多表查詢.....................................................................................................................................................9 ORACLE_列函數(shù)和分組...........................................................................................................................................10 ORACLE_子查詢.......................................................................................................................................................12 ORACLE_表的更新操作...........................................................................................................................................13 ORACLE_表與視圖的管理.......................................................................................................................................15 一、ORACLE_簡介 ? Oralce數(shù)據(jù)庫發(fā)展 Oracle 8 Oracle 8i：i表示internet，標(biāo)識著Oracle公司正式進(jìn)軍互聯(lián)網(wǎng)。Oracle9i Oracle10g：g表示grid，即網(wǎng)絡(luò)技術(shù)。Oracle11g ? Oracle體系結(jié)構(gòu)一 物理結(jié)構(gòu)： 文件系統(tǒng) 控制文件 數(shù)據(jù)文件 日志文件 參數(shù)文件（不是數(shù)據(jù)庫的組成成分） ? Oracel體系結(jié)構(gòu)三 邏輯結(jié)構(gòu)： block 快 extent 盤區(qū) segment 段 tablespace 表空間 datafile 數(shù)據(jù)文件 ? SQL * Plus下的常用命令 ? 連接到SQL*PLUS sqlplus user/password[as sysdaba|sysoper] sqlplus/nolog ? 啟動數(shù)據(jù)庫 startup mount 啟動實例，打開控制文件，但不打開數(shù)據(jù)文件 startup nomount 只啟動實例 ? 關(guān)閉數(shù)據(jù)庫 shutdown immediate ? 迫使每個用戶執(zhí)行為當(dāng)前的SQL語句，立即斷開連接 shutdown transactional ? 迫使用戶執(zhí)行完當(dāng)前事務(wù)時，斷開連接 shutdown abort 強(qiáng)制關(guān)閉數(shù)據(jù)庫 ? 常用SQL命令 show user：查看當(dāng)前連接的用戶 connect scott/tiger： 采用scott的用戶名/tiger的密碼連接數(shù)據(jù)庫 desc table_name：查看tableName表結(jié)構(gòu) quit|exit：退出 disconnect：斷開連接 clear screen：清屏，相當(dāng)于Windows下的cls命令 select * from tab：列出當(dāng)前用戶下的所有表 @pata 執(zhí)行pata制定的腳本文件 ? Oracle常用基本數(shù)據(jù)類型 varchar2/varchar：變成字符串 char：定長字符串 Integer：整型 number(m,n)：數(shù)字型 smallint：短整型 float：浮點數(shù) decimal：十進(jìn)制數(shù)字（小數(shù)）date：日期型 二、ORACLE_簡單查詢 ? SQL結(jié)構(gòu)化查詢語言（Structured Query Language） ? SQL分類 1.數(shù)據(jù)定義語言（Data Definition Language，DDL）：create、alter、drop。 create table 表名;alter table add 新列名 數(shù)據(jù)類型;drop table 表名;2.數(shù)據(jù)操縱語言（Data Manipulation Language，DML）：insert、update、delete、select。Insert into 表名(字段1,字段2…)values(值1,值2…);update student set sage=22 where sno='200215'；(將學(xué)生200215的年齡改為22歲)select distinct job from emp；去除重復(fù)行 3.數(shù)據(jù)控制語言（Data Control Language，DCL）：commit work、rollback work。? 查詢雇員的所有信息 select * from emp； *表示所有列 ? 查詢語句的格式 select *|列名 from 表名 ? 查詢雇員的編號，姓名，工資 select * from empno,ename,sal from emp; ? 查詢所有職位 select job from emp;select distinct job from emp;distinct： 有區(qū)別的（去除重復(fù)行） ? 查詢工資大于1500的雇員信息，列出編號，用戶名，工資 select empno,ename,sal from emp where sal>1500; ? 帶有where條件查詢語句的基本格式 select *|列名 from 表名 where 條件; ? 比較運(yùn)算符 大于：> 小于：< 等于：= 大于等于：>= 小于等于：<= 不等于：!=或者<> ? 限定查詢 is null 和 is not null 的使用 ? 查詢每月可以得到獎金的雇員 select empno,ename,comm from emp where comm is not null; select empno,ename,comm from emp where comm is not null and comm>0;? 查詢誰沒有獎金 select empno,ename from emp where comm is null; ? 限定查詢 and 的使用 ? 查詢工資大于1500，并且可以領(lǐng)取獎金的雇員 SQL> select empno,ename,sal,comm from emp where comm is not null and sal>1500; ? 限定查詢 or 的使用 ? 查詢工資大于1500和可以領(lǐng)取獎金的雇員 select empno,ename,sal,comm from emp where sal>1500 or comm is not null;? 查詢沒有獎金的雇員 select empno,ename,comm from emp where comm=0 or comm is null; ? 限定查詢 使用not對條件整體取反 ? 查詢工資不大于1500并且不能領(lǐng)取獎金的雇員 select empno,ename,sal,comm from emp where sal<=1500 and(comm is null or comm=0);select empno,ename,sal,comm from emp where not(sal>1500 or comm is not null); ? 限定查詢 between...and...的使用 ? 查詢基本工資大于等于1500并且小于等于3000的雇員 select empno,ename,sal from emp where sal>=1500 and sal<=3000;select empno,ename,sal from emp where sal between 1500 and 3000;? 查詢1981年雇傭的所有員工 select empno,ename,hiredate from emp where hiredate between'1-1月 1981' and '31-12月 1981';注：日期格式 日-月 年，要匹配上 ? 限定查詢 字符串的比較 ? 查詢姓名是'SMITH'員工的所有信息 select * from emp where ename='SMITH';select * from emp where ename='smith';注：列值區(qū)分大小寫 ? 限定查詢 in的使用 ? 查詢出編號7369,7499,7521的雇員的具體信息 select * from emp where empno=7369 or empno=7499 or empno=7521;select * from emp where empno in(7369,7499,7521); ? 限定查詢 not in的使用 ? 查詢出雇員編號不是369,7499,7521的雇員的具體信息 select * from emp where empno not in(7369,7499,7521); ? 限定查詢 like的使用 ? 查詢雇員的名字第二個字符是M的雇員信息 select * from emp where ename like'_M%';注：_匹配一個字符，%匹配0個多個字母（前綴或后綴的代表）? 查詢1982年入職的所有雇員的信息 select * from emp where hiredate like'%82';? 查詢工資中包含5的雇員信息 select * from emp sal where like'%5%'; ? 對結(jié)果排序-Oracle By ? 查詢員工工資大于1500員工的信息，按工資排序 select * from emp where sal>1500 order by sal;? 查詢工資大于1500員工的信息，按工資降序，按雇傭日期升序排序 select * from emp where sal>1500 order by sal desc,hiredate asc;注：ASC升序，DESC降序，默認(rèn)ASC。 ascending [?'sendi?]上升的，降序排列（descend [di'send] 的縮寫）； 三、ORACLE標(biāo)量函數(shù)和算數(shù)運(yùn)算 字符函數(shù)的使用 ? 轉(zhuǎn)換為大寫字母 select upper('smith')from dual;注：dual是公共表。upper ['?p?] 上面的，上部的 ? 轉(zhuǎn)換為小寫字母 select lower('SMITH')from dual;注：lower 放下 ? 每個單詞的字母變成大寫，其余字母小寫 select initcap('hello world')from dual ? 串連接(concat):可以使用“||”進(jìn)行串連接 select concat('HELLO','WORLD')from dual;select 'HELLO'||'WORLD' from dual; ? 求子串(SUBSTR)select substr(ename,1,3)from emp;注：含義為截取ename字段從第一個字符開始，總共三個字符 ? 求長度(LENGTH)select length(ename)from emp; ? 串替換(REPLACE)select replace('HELLO WORLD','WOR','WEL')from dual;結(jié)果： REPLACE('HE-----------HELLO WELLD 注：第一個逗號是原字符，第二個逗號是原字符的一部分，第三部分是替換原字符那個部分。 ? 要求顯示所有雇員的姓名和姓名的后三個字符 select ename,substr(ename,length(ename)-2)from emp;select ename,substr(ename,-3)from emp; ? 四舍五入(Round)select round(789.536)from dual;ROUND(789.536)-------------- 790 ? 四舍五入(Round)：指定保留小數(shù)位數(shù) select round(789.536,2)from dual;---------------- 789.54 ? 四舍五入(Round)：對整數(shù)四舍五入 select round(789.536,-2)from dual;ROUND(789.536,-2)----------------- 800 ? 截斷小數(shù)位(TRUNC)select trunc(789.536)from dual;TRUNC(789.536)-------------- 789 ? 截斷小數(shù)位(TRUNC):指定保留小數(shù)位 select trunc(789.536,2)from dual;TRUNC(789.536,2)---------------- 789.53 ? 截斷小數(shù)位(TRUNC):對整數(shù)截斷 select trunc(789.536,-2)from dual;TRUNC(789.536,-2)----------------- 700 ? 除空格（TRIM） SQL> SELECT TRIM(55)FROM DUAL;TRIM(55)--------55 ? 取余數(shù)(MOD)select mod(10,3)from dual; ? 日期函數(shù) 運(yùn)算規(guī)律： 日期+數(shù)字=日期 日期-數(shù)字=日期 日期-日期=數(shù)字（天數(shù)） ? 查詢當(dāng)前日期 select sysdate from dual;SYSDATE-------------10-3月-12 ? 顯示10部門員工進(jìn)入公司的星期數(shù) select empno,ename,round((sysdate-hiredate)/7)from emp where deptno=10; ? 在指定日期上加入指定的月數(shù)之后的日期(ADD_MONTHS)select add_months(sysdate,4)from dual;ADD_MONTHS(SYS--------------10-7月-12 ? 求出給定日期范圍的月數(shù)(months_between)select empno,ename,months_between(sysdate,hiredate)from emp; ? 下一個給定的星期是那個日期(next_day)select next_day(sysdate,'星期一')from dual;注：表示在當(dāng)前時間的前提下，下個禮拜一的具體日期 ? 求出給定日期所在月份的最后一天日期(last_day)select last_day(sysdate)from dual; ? 轉(zhuǎn)換成字符串(to_char)查詢雇員號，姓名，以及入職的年份 select empno,ename,to_char(hiredate,'yyyy')from emp;注：yyyy匹配年份，mm匹配月份，dd匹配日 ? 使用to_char設(shè)置日期的顯示格式 select empno,ename,to_char(hiredate,'yyyy-mm-dd')from emp; ? 使用fm去掉前導(dǎo)0 select empno,ename,to_char(hiredate,'fmyyyy-mm-dd')from emp;EMPNO ENAME TO_CHAR(HI-------------------------- 7369 SMITH 1980-12-17 7499 ALLEN 1981-2-20 注：正常的值是1981-02-20，02前面的0去掉，變成了1981-2-20.? 通過to_char設(shè)置數(shù)值的格式 select empno,ename,to_char(sal,'99,999')from emp;說明：9代表1位數(shù)字 EMPNO ENAME TO_CHAR--------------------------- 7369 SMITH 800 7499 ALLEN 1,600 7521 WARD 1,250 注：把sal的值分成兩個部分，后三位為一個部分，前兩位為一個部分，用逗號隔開。 ? 顯示余額 select empno,ename,to_char(sal,'$99,999')from emp;說明：$代表美元 ? 轉(zhuǎn)換成數(shù)字(to_number)select to_number('123')+to_number('123')from dual;TO_NUMBER('123')+TO_NUMBER('123')-- 246 ? 轉(zhuǎn)換成日期(to_date)select to_date('2009-07-31','yyyy-mm-dd')from dual;TO_DATE('2009---------------31-7月-09 ? 算數(shù)運(yùn)算 ? 查詢每個員工的年收入 select empno,ename,(sal+comm)*12 from emp;年收入=工資+獎金 ? 查詢每個員工的年收入 select empno,ename,(sal+comm)*12 income from emp; ? 查詢每個員工的年收入 select empno,ename,(sal+NVL(comm,0))*12 income from emp;注：NVL(comm,0)，當(dāng)comm的值為null時，用0替換 ORACLE_多表查詢 ? 格式： select 列名1 別名1,......from 表名1,表名2,......where 條件 order by 列名 ? 查詢員工的編號，姓名，部門編號，部門名稱 select empno,ename,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno; 關(guān)聯(lián)查詢-為表命別名 ? 查詢員工的編號，姓名，部門編號，部門名稱 select empno,ename,e.deptno,dname from emp e,dept d where d.deptno=e.deptno;注：這里的e是emp的別名，d是dept的別名 關(guān)聯(lián)查詢-自連接 ? 查詢出每個雇員的姓名、工作、雇員的直接上級領(lǐng)導(dǎo)的姓名 select e.empno,e.ename,e.job,m.ename mname from emp e,emp m where e.mgr=m.empno;注：mname是m.ename的別名 關(guān)聯(lián)查詢-多表關(guān)聯(lián) 四、? 查詢出每個雇員的姓名，工作，雇員的直接上級領(lǐng)導(dǎo)，以及部門名稱 select e.ename,e.job,m.ename,d.dname from emp e,dept d,emp m where e.mgr=m.empno and e.deptno=d.deptno;注：當(dāng)查詢的字段間的關(guān)系涉及到n個表時，則n個表之間關(guān)聯(lián)。 左、右外連接 ? 查詢員工編號，姓名，所在部門號，部門名稱，將沒有員工的部門也顯示出來 select e.ename,d.deptno,d.dname from emp e,dept d where e.deptno(+)=d.deptno;注：(+)在左邊，表示右連接，會列出所有右表中出現(xiàn)但是沒有在左表中出現(xiàn)的行。 ? 查詢雇員的編號，姓名及其領(lǐng)導(dǎo)的編號，將沒有領(lǐng)導(dǎo)的員工也列出來 select e.empno,e.ename,m.empno,m.ename from emp e,emp m where e.mgr=m.empno(+);注：(+)在右邊表示左連接，會列出左表中出現(xiàn)但沒有在右表中出現(xiàn)的行。 ? 交叉連接(cross join)：用來長生笛卡爾積 select * from emp cross join dept; ? 自然連接(nutural join)：自動進(jìn)行關(guān)聯(lián)字段的匹配 select * from emp natural join dept; ? using子句：直接指定操作關(guān)聯(lián)列 select * from emp join dept using(deptno) ? on子句：用戶自己編寫連接條件 select * from emp join dept on emp.deptno=dept.deptno; ? left join：左外連接 ? right join：右外連接 五、ORACLE_列函數(shù)和分組 ? 常用的列函數(shù) sum(expression)求和 max(expression)求最大值 min(expression)求最小值 avg(expression)求平均數(shù) count(expression)統(tǒng)計記錄數(shù) count(distinct colname)統(tǒng)計去除重復(fù)行記錄數(shù) nvl(comm,0)當(dāng)comm為null時，用0替換 列函數(shù)的使用 ? 查詢員工的記錄數(shù)，員工工資的總和，平均工資，最高工資，最低工資 select count(*)count_emp,sum(sal)sum_sal, max(sal)max_sal,min(sal)min_sal, avg(sal)avg_sal from emp; 列函數(shù)的使用-對null的處理 ? 查詢所有員工的年收入 select sum(sal+comm)from emp;SUM(SAL+COMM)------------- 7800 注：當(dāng)comm為null時，sal+comm是沒有結(jié)果值的 select sum(sal)+sum(comm)from emp;SUM(SAL)+SUM(COMM)------------------ 31225 select sum(sal+nvl(comm,0))from emp;SUM(SAL+NVL(COMM,0))-------------------- 31225 ? 分組查詢 語法格式 select 列名...,列函數(shù) from 表名...where 條件...group by 列名...order by 列名...? 查詢每個部門的人數(shù) select deptno,count(*)from emp group by deptno;規(guī)則：如果在select字句中，有不在列函數(shù)中的列，則該列一定要出現(xiàn)在group by之后。 分組查詢having字句的使用 ? 顯示出平均工資大于2000的部門編號和平均工資 select deptno,avg(sal)from emp group by deptno having avg(sal)>2000; SQL語句的書寫順序 select 列名,列函數(shù) from 表名 where 條件 group by列名 having 條件 order by 列名 SQL語句的執(zhí)行順序 from where group by having select order by ? 查詢20,30部門的平均工資，并將平均工資大于2000的輸出，輸出結(jié)果按平均工資排序 select deptno,avg(sal)from emp where deptno in(20,30)group by deptno having avg(sal)>2000 order by avg(sal); 六、ORACLE_子查詢 在where子句中使用子查詢 ? 查詢工資比7654雇員工資高的全部雇員信息 select * from emp where sal>(select sal from emp where empno=7654);子查詢-in的使用 ? 查詢和smith或jones在同一部門，同一職位工作的員工 select ename from emp where(deptno,job)in(select deptno,job from emp where ename in('SMITH','JONES')); 子查詢-any的使用 ? =any：與in操作符的效果一致 查詢和smith或jones在同一部門，同一職位工作的員工 select ename from emp where(deptno,job)=any(select deptno,job from emp where ename in('SMITH','JONES')); ? >any：只要大于子查詢中的任何一個值即可 select * from emp where sal>any(select min(sal)from emp group by deptno); ? select empno from emp where sal 子查詢all的使用 ? >all：比最大的值大 select * from emp where sal>all (select min(sal)from emp group by deptno);注：select min(sal)from emp group by deptno結(jié)果為 MIN(SAL)---------- 950 800 1300 是每個部門當(dāng)中的最少工資。 ? 七、ORACLE_表的更新操作 ? 創(chuàng)建表副本 create table myemp as select * from emp; ? 插入 ? 到表中 insert的語法 insert into 表名(字段名1,字段名2,......)values(值1,值2......) ? 為myemp中增加一條記錄 insert into myemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7899,'張三','清潔工','7369','14-2月-2005',9000,300,40);注：給定的值要和指定的字段數(shù)一致。 1.如果全部字段都插入值的話，那字段名可以省略。即： insert into 表名 values(值1,值2,.......);2.如果只是部分字段插入值的話，字段名又想省略的話，那么，其余沒有數(shù)據(jù)插入的字段用null填補(bǔ)。 ? to_date：字符型轉(zhuǎn)換成日期型 ? 批量插入記錄 格式： insert into 表名 select 字段列表 from 表名 where 條件 注：insert表和select表列數(shù)等都一致 例如： create table test as select * from emp;insert into test select * from emp; ? 修改記錄 update表名set要修改的字段=新值，要修改的字段=新值，...where條件 ? 將雇員號為7896的雇員的獎金修改為100 update myemp set comm=100 where empno=7896;注：執(zhí)行修改操作時，一定要使用where來指定修改的條件，否則會改變表中的所有記錄。 ? 刪除記錄 delete from 表名 where 條件 ? 刪除雇員編號為7896的雇員信息 delete from myemp where empno=7896;注：執(zhí)行delete操作時，一定要使用where來指定修改的條件，否則會刪除表中所有的記錄。 ? Oralce的事務(wù)處理 commit：提交事務(wù) rollback：回滾操作 注：操作一旦執(zhí)行了commit操作，就再也不能回滾操作了。 八、ORACLE_表與視圖的管理 ? oracle中的常見數(shù)據(jù)類型 varchar、varchar2：表示一個字符串，有長度限制，為255 number： number(n)：表示一個整數(shù)，數(shù)字的長度是n，可以使用int number(n,m)：表示一個小數(shù)，數(shù)字的長度為n，整數(shù)長度m-n，可以使用float date：表示日期類型，日期要按照標(biāo)準(zhǔn)格式日期存放。clob：大對象，表示大文本數(shù)據(jù)類型，可存放4G。 blob：大對象，表示二進(jìn)制數(shù)據(jù)，最大可以存放4G，如電影，圖片、歌曲 ? 創(chuàng)建表 語法： create table 表名(字段名稱1 字段類型[default 默認(rèn)值], 字段名稱1 字段類型[default 默認(rèn)值],............字段名稱1 字段類型[default 默認(rèn)值],); ? 復(fù)制表 create table 表名 as(子查詢);注：當(dāng)子查詢不成立時，如果1=2，則只復(fù)制表的結(jié)果，不復(fù)制表的數(shù)據(jù)。 ? 刪除表 語法： drop table 表名; ? 修改表結(jié)構(gòu) ? 增加一列 alter table 表名 add(列名稱 列數(shù)據(jù)類型 default 默認(rèn)值) ? 刪除列 alter table 表名 drop column 列名稱; ? 修改列的數(shù)據(jù)類型 alter table 表名稱 modify(列的名稱 列的類型 default 默認(rèn)值);注：1.如果是更改數(shù)據(jù)的長度，則要求更改時，長度不能小于當(dāng)前表中數(shù)據(jù)所具有的最大長度。 2.如果是更改數(shù)據(jù)類型，則要求更改時，該列的所有記錄值都為空。 ? 修改表結(jié)構(gòu) ? 為表重命名 rename 舊表名 to 新表名;注：這是oracle特有的操作 ? 將myemp表改名為iemp SQL> rename myemp to iemp;表已重命名。? 截斷表 truncate table 表名;意義：清空表中的所有數(shù)據(jù)，并且立即釋放資源，該操作是不可回滾。 ? 約束-約束的分類 主鍵約束：表示一個唯一的標(biāo)識，本身不能為空 唯一約束：列值不允許重復(fù) 檢查約束：檢查一個列的內(nèi)容是否合法 非空約束：不能為空值，如用戶不能為空(no null)外鍵約束：在兩張表中進(jìn)行約束的操作 ? 主鍵約束(primary key)主鍵約束一般在id上使用，而且本身已經(jīng)默認(rèn)了不能為空，主鍵約束可以在建表的時候指定 create table person(pid varchar2(18)primary key, name varchar(30), age number(3), sex varchar2(2)default'男'); 使用constraint指定 constraint [k?n'streint] 約束；強(qiáng)制 create table person(pid varchar2(18), name varchar(30), age number(3), sex varchar2(2)default'男' constraint person_pid_pk primary key(pid););? 非空約束(not null)create table person(pid varchar2(18)primary key, name varchar(30)not null, age number(3), sex varchar2(2)default'男'); ? 唯一約束(unique)create table person(pid varchar2(18)primary key, name varchar(30)unique not null, age number(3), sex varchar2(2)default'男'); ? 視圖 概念：一個視圖實質(zhì)是封裝了一條復(fù)雜的SQL語句 ? 創(chuàng)建視圖 語法： create view 視圖名稱 as 子查詢 ? 創(chuàng)建部門20員工的雇員信息 create view empv20 as select * from emp where deptno=20;注：當(dāng)創(chuàng)建視圖以后，可以像操作表一樣操作視圖。注：視圖的操作會影響到表的操作。 with check option 不能更新創(chuàng)建視圖的條件 with read only 創(chuàng)建只讀視圖 ? 刪除視圖 語法： drop view 視圖名稱 注：當(dāng)刪除視圖所在的表時，則視圖也不能被使用 ? 序列 創(chuàng)建序列語法：sequence ['si:kw?ns] create sequence 序列名稱;create sequence myseq;序列的操作： nextval：取得序列的下一個內(nèi)容 currval：取得當(dāng)前序列的內(nèi)容 ? 創(chuàng)建表驗證序列的操作 create table testseq(next number,curr number);insert into testseq values(myseq.currval,myseq.nextval); ? 創(chuàng)建序列指定每次增長的增量 create sequence myseq increment by 2;注：每次增長2 ? 創(chuàng)建序列指定開始的序列，默認(rèn)的序列從1開始。create sequence myseq increment by 2 start with 10;注：序列從10開始，每次增長2 ? 創(chuàng)建一個序列1，3，5，7，9.create sequence myseq maxvalue 9 increment by 2 start with 1 cache 2 cycle;注：序列從1開始，每次增長2，最大值為9，循環(huán)兩次。 中南林業(yè)科技大學(xué) 實驗報告 課程名稱： Oracle數(shù)據(jù)庫 專業(yè)班級： 姓名： 學(xué)號： ****年**月**日 實驗一 安裝和使用Oracle數(shù)據(jù)庫 【實驗?zāi)康摹?/p> 1.掌握Oracle軟件安裝過程，選擇安裝組件 2.掌握建立Oracle數(shù)據(jù)庫，配置網(wǎng)絡(luò)連接 3.掌握Oracle企業(yè)管理器的基本操作 4.使用SQL*Plus，登錄到實例和數(shù)據(jù)庫 5.掌握命令方式的關(guān)閉和啟動實例及數(shù)據(jù)庫 【實驗內(nèi)容】 1.查看已安裝的Oracle組件 2.查看服務(wù)，記錄下和數(shù)據(jù)庫有關(guān)的服務(wù)名，將他們設(shè)為手動方式，啟動相關(guān)服務(wù)。 3.配置監(jiān)聽器，查看是否在服務(wù)中有LISTENER，是否能啟動。4.配置本地net服務(wù)，提示：設(shè)置正確的服務(wù)器地址和端口號。5.打開SQL*Plus，用SYS和SYSTEM用戶名和密碼登錄。6.思考題：有幾種打開SQL*Plus的方法？分別是什么？ 7.用命令關(guān)閉實例和數(shù)據(jù)庫，記錄命令 8.用命令以NOMOUNT的方式啟動實例，再加載數(shù)據(jù)庫，打開數(shù)據(jù)庫。 【實驗結(jié)論】 1.查看已安裝的Oracle組件 2.查看服務(wù)，記錄下和數(shù)據(jù)庫有關(guān)的服務(wù)名，將他們設(shè)為手動方式，啟動相關(guān)服務(wù)。 3.配置監(jiān)聽器，查看是否在服務(wù)中有LISTENER，是否能啟動。 4.配置本地net服務(wù)，提示：設(shè)置正確的服務(wù)器地址和端口號。 5.打開SQL*Plus，用SYS和SYSTEM用戶名和密碼登錄。 6.思考題：有幾種打開SQL*Plus的方法？分別是什么？（1）直接打開 sql plus 即窗口方式（2）cmd 命令行方式 （3）WEB 頁面中 iSQL*Plus 方式 7.用命令關(guān)閉實例和數(shù)據(jù)庫，記錄命令 SQL>shutdown immediate 8.用命令以NOMOUNT的方式啟動實例，再加載數(shù)據(jù)庫，打開數(shù)據(jù)庫。 sql>startup nomount sql>alter database mount;sql>alter database open;sql>startup (1).用SQLplus 命令：show parameters參數(shù)名 (2).用select [列名] from 表名 2.初始化文件有幾種？默認(rèn)的保存位置在哪里？ 初始化文件有三種：數(shù)據(jù)文件，日志文件，控制文件； 默認(rèn)的保存位置E:oracleproduct10.2.0oradataorcl 4.打開OEM，查看三類物理文件信息 三類物理文件：數(shù)據(jù)文件、控制文件、日志文件 數(shù)據(jù)文件：.DBF存儲表、索引及數(shù)據(jù)結(jié)構(gòu)信息 日志文件：.LOG記錄對數(shù)據(jù)庫的所有修改信息，用于恢復(fù) 控制文件：.CTL二進(jìn)制文件，記錄數(shù)據(jù)庫名、文件標(biāo)識、檢查點 5.分別用select命令查詢V$parameter動態(tài)性能視圖，用show命令查看全局?jǐn)?shù)據(jù)庫名、實例名、是否啟動自動歸檔、標(biāo)準(zhǔn)數(shù)據(jù)塊大小這四個參數(shù)的值，記錄命令和結(jié)果。用select命令查詢V$parameter動態(tài)性能視圖 用show命令查看全局?jǐn)?shù)據(jù)庫名、實例名 用show命令查看是否啟動自動歸檔、標(biāo)準(zhǔn)數(shù)據(jù)塊大小 6.如何用命令查看數(shù)據(jù)文件、控制文件的文件名、存儲位置和狀態(tài)信息？ (1)．查看數(shù)據(jù)文件的文件名、存儲位置和狀態(tài)信息 (2)．查看控制文件的文件名、存儲位置和狀態(tài)信息 0 和服務(wù)器型文件都要找到)，查看各類默認(rèn)位置并記錄下來(包括物理文件所在目錄，該數(shù)據(jù)庫相關(guān)的各個目錄等)，登錄到mydb數(shù)據(jù)庫。 2.用命令方式手工創(chuàng)建數(shù)據(jù)庫testorcl (1)創(chuàng)建批處理文件，建立必需的各級目錄，參考第一題MYDB數(shù)據(jù)庫的默認(rèn)目錄。 (4)運(yùn)行數(shù)據(jù)庫建立腳本，更改相關(guān)服務(wù)為手動啟動方式 (5)運(yùn)行數(shù)據(jù)字典等創(chuàng)建腳本 3.在DBCA中刪除MYDB數(shù)據(jù)庫。 Enter valuefor 4:runner Specify log path as parameter 5: Enter valuefor 5: D:oracleproduct10.2.0db_2RDBMSlog 二、完成下面的查詢，記錄查詢命令和結(jié)果。1.查詢HR方案種有哪些表，列出表名 2.設(shè)置行寬為160，每頁行數(shù)為40 3.查詢employees表中所有薪水在3000到6000元之間的員工編號、姓、名、受聘日期、工作代號、薪水、經(jīng)理代號、部門號，按部門號升序排列，同一部門按員工編號降序排列 4.查詢每個部門的人數(shù)、平均薪水、最高薪水、最低薪水，按部門編號升序排列 5.查詢first_name的第三個字母是t的員工編號，first_name，電話號碼，部門編號，部門名稱 6.在job_history表中查詢?nèi)温殨r間超過1年的員工編號，任職歷時月份(保留整數(shù))，工作代號，部門代號。 7.查詢每個部門經(jīng)理的員工編號、姓名、薪水、部門號。 【實驗結(jié)論】 一、需要訪問HR示例方案中的表，如果機(jī)器上沒有該示例方案，則運(yùn)行相關(guān)包和相應(yīng)配置裝入HR方案。 1.將hr.rar解壓到D:oracleproduct10.2.0db_2demoschema human_resources目錄下，然后在SQL*PLUS 中執(zhí)行 hr_main.sql 二、完成下面的查詢，記錄查詢命令和結(jié)果。1.查詢HR方案種有哪些表，列出表名 5.查詢first_name的第三個字母是t的員工編號，first_name，電話號碼，部門編號，部門名稱 738 實驗五 PL/SQL編程 【實驗?zāi)康摹?/p> 1.熟悉PL/SQL的數(shù)據(jù)類型和書寫規(guī)則 2.熟悉控制結(jié)構(gòu)和游標(biāo)的使用 3.編寫和運(yùn)行函數(shù)、過程和觸發(fā)器 【實驗內(nèi)容】 編寫腳本文件，調(diào)試運(yùn)行腳本文件，并記錄結(jié)果。 1.在SQL*Plus中編寫一個PL/SQL塊，功能用于打印學(xué)生信息 在DECLARE部分完成： (2)建立學(xué)生信息記錄類型stu_record_type，包括學(xué)號，姓名，性別，籍貫，學(xué)習(xí)成績和活動成績。均為可變長字符類型(3)編寫本地子過程：學(xué)生信息打印過程PrintStuRecord，把(1)中定義的記錄類型作為參數(shù)(4)定義學(xué)生信息記錄變量stu_record 在BEGIN…END部分完成： (1)為stu_record變量的各個元素賦值如下： 學(xué)號：‘2001001’ 姓名：’李新’ 性別：‘m’ 籍貫：‘黑龍江省哈爾濱市’ 學(xué)習(xí)成績：‘Excellent’ 0 (1)建立學(xué)生信息記錄類型stu_record_type，包括學(xué)號，姓名，性別，籍貫，學(xué)習(xí)成績和活動成績。均為可變長字符類型 set serveroutput on declare type stu_record_type is record （s_no varchar2(8), s_name varchar2(10), s_sex varchar2(2), s_address varchar2(20), s_studyscore varchar2(10), s_activescore varchar2(10));(2)編寫本地子過程：學(xué)生信息打印過程PrintStuRecord，把中定義的記錄類型作為參數(shù) procedure PrintStuRecord as begin dbms_output.put_line('學(xué)號:'|| stu_record.s_no);dbms_output.put_line('姓名 :'|| stu_record.s_name);dbms_output.put_line('性別:'|| stu_record.s_sex);dbms_output.put_line('籍貫:'|| stu_record.s_address);dbms_output.put_line('學(xué)習(xí)成績:'|| stu_record.s_studyscore);dbms_output.put_line('活動成績:'|| stu_record.s_activescore);end PrintStuRecord;(3)定義學(xué)生信息記錄變量stu_record stu_record stu_record_type;在BEGIN…END部分完成： (4)為stu_record變量的各個元素賦值如下： 學(xué)號：‘2001001’ 姓名：’李新’ 性別：‘m’ 籍貫：‘黑龍江省哈爾濱市’ 2.建立對bookinfo表的DML觸發(fā)器，一旦bookinfo表發(fā)生了任何變化，立即觸發(fā)，對bookinfo表的數(shù)據(jù)進(jìn)行統(tǒng)計，結(jié)果存儲在數(shù)據(jù)統(tǒng)計表中 (1)如果沒有則建立bookinfo表，選擇建立在scott用戶下，表結(jié)構(gòu)為(bookno varchar2(36)Primary key, bookname varchar2(40)not null, authorname varchar2(10)not null, publishtime date, bookprice float) create table bookinfo(bookno varchar2(36)Primary key，4 Selectcount(bookno),count(distinct authorname)from bookinfo;end;/ (4)在bookinfo表中插入、刪除和更新信息，再查看major_stats表中數(shù)據(jù)的變化 <1>插入 647 實驗六 模式對象管理與安全管理 【實驗?zāi)康摹?/p> 1.了解模式對象的類型 2.掌握在OEM中操作模式對象的方法 3.掌握命令方式建立表、視圖、索引等常見對象的方法 4.熟悉Oracle中權(quán)限分類和設(shè)置，理解系統(tǒng)提供用戶的角色和權(quán)限 5.熟練使用建立用戶、角色，為用戶授權(quán)、授予角色的命令 【實驗內(nèi)容】 1.創(chuàng)建一個TESTUSET用戶，密碼為test，默認(rèn)表空間為user表空間 2.創(chuàng)建用戶后為其授予登錄數(shù)據(jù)庫和創(chuàng)建數(shù)據(jù)庫對象的權(quán)限 3.用TESTUSER用戶登錄數(shù)據(jù)庫 4.創(chuàng)建學(xué)生信息表(學(xué)號，姓名，性別，入學(xué)成績)，定義主鍵和輸入數(shù)據(jù)(至少5條數(shù)據(jù))，提交添加的數(shù)據(jù)。并完成下面的題目： (1)再繼續(xù)添加2條數(shù)據(jù)，設(shè)置一個保存點savepoint，再添加1條數(shù)據(jù)，執(zhí)行回退到保存點的回退命令。查看此時表中數(shù)據(jù)(2)查詢?nèi)雽W(xué)成績大于480的學(xué)生信息(3)建立男生信息視圖 (4)在“成績”字段上建立B-樹索引 5.創(chuàng)建一個表簇，名為empl_dep，容納empl表和dep表，有公第四篇：ORACLE數(shù)據(jù)庫學(xué)習(xí)總結(jié)
第五篇：Oracle數(shù)據(jù)庫實驗報告
點擊咨詢 