oracle學習筆記day1

oracle資料庫
前言 oracle sql
lesson1 Selecting Rows
lesson2 Sorting & Limiting Selected Rows
lesson3 Single Row Functions
lesson4 Displaying Data from Multiple Tables
lesson5 Group Function
lesson6 Subqueries
lesson7 Overview of Data Modeling and Database Design
lesson8 Creating Tables
lesson9 Manipulating Data(DML)
lesson10 Altering Tables and Constraints
lesson11 Creating Sequences
lesson12 Creating View
lesson13 Creating Indexes

前言:
1.一個認知
認知什麼是oracle？
oracle：商業運用第一的關係型資料庫
實質：關係型資料庫
瞭解oracle資料庫發展歷史
2.二個概念
資料庫：數據存儲的倉庫
關係型資料庫：資料庫中保存的對象之間可以存在一定的關聯關係，並非完全獨立。主要反映到以後學習的主外鍵.
3.三個名詞
sql:結構化的查詢語句，操作oracle資料庫的語言
sqlplus:oracle軟體自帶的可以輸入sql，且將sql執行結果顯示的終端
pl/sql:程式化的sql語句，在sql語句的基礎上加入一定的邏輯操作，如if for...,使之成為一個sql塊，完成一定的功能
4.四種對象
table：表格，由行和列組成，列又稱欄位，每一行內容為表格的一條完整的數據。
view: 視圖，一張表或者多張表的部分或者完整的映射，好比表格照鏡子，鏡子裡面的虛像就是view
除去常見的table和view兩種對象以外，oracle資料庫還支持如下四種對象
sequence：序列
index：索引，提高數據的訪問效率
synonym：同義，方便對象的操作
program unit：程式單元，pl/sql操作的對象
5.五種分類
sql的五大分類:
Data retrieval：數據查詢
select
DML：數據操縱語言（行級操作語言）：操作的是表格當中一條一條的數據
insert update delete
DDL：數據定義語言（表級操作語言）：操作的內容為表格（對象）
create alter drop truncate rename
transaction control：事務控制
commit rollback savepoint
DCL：數據控制語言
grant revoke


delete，truncate區別：
delete： 刪除表中的一條或者多條記錄，該操作需要提交事務
truncate：清空表格,該操作不需要提交事務

oracle資料庫環境準備:
1.安裝oracle資料庫
1.1 最好預設按照到C盤
1.2 安裝過程中有一步讓輸入一個密碼,建議使用oracle作為密碼,方便記憶
1.3 安裝完成之後,打開系統服務,查看服務是否已經正常啟動,具體情況參照文檔:oracle系統服務.txt
1.4 打開cmd,輸入sqlplus,然後回車查看是否能執行該命令
1.5 如果不能執行,則需要把安裝目錄裡面的BIN裡面配置到系統環境變了path中,然後重新打開一個cmd視窗即可

2.登錄oracle自帶的管理系統,新建一個屬於自己的賬號.
參照文檔:oracle系統服務.txt

3.用戶創建好之後,使用sqlplus命令登錄到oracle資料庫中,然後把之後要使用到的表及其數據導入到資料庫中.
參照文檔:導入數據.txt

4.瞭解導入的三張表以及相互關係
s_emp 員工表
s_dept 部門表
s_region 地區表

5.之後登錄或者操作資料庫,就可以使用這個新創建的用戶了

====================================================================

第一章：select語句，數據查詢操作
1.使用select語句查詢某張表的所有數據內容
語法：
select [distinct] *{col_name1,col_name2,..}
from tb_name;
註意：語法中出現的中括弧[]，表示該部分可有可無
*：表示所有列，僅僅作為測試和學習使用，在企業用語中不出現，因為效率低下且可讀性差
col_name1：列名，將需要查閱的數據欄位列舉出來，可以查看多列值，列名之間用,進行分割即可
s_emp ：員工信息表
s_dept：員工部門表
需求：查看s_dept表中的所有記錄
select *
from s_dept;

select id,name,region_id
from s_dept;
練習：查看s_dept表中的所有記錄的id和name
select id,name
from s_dept;

練習：查看所有員工的id，名字(last_name)和薪資(salary)
select id,last_name,salary
from s_emp;

2.select語句可以對指定的列的所有值進行算術運算。
語法：
select col_name 運算符 數字
from tb_name;
需求：查看每個員工的員工id，名字和年薪。
select id,last_name,salary*12
from s_emp;
註意：select語句永遠不對原始數據進行修改。
練習：查看每個員工的員工id，名字和月薪漲100以後的年薪
select id,last_name,(salary+100)*12
from s_emp;
3.給查詢的列起別名
語法：
select old_column [as] new_column_name
from tb_name;
需求：查看員工的員工id，名字和年薪,年薪列名為annual
select id,last_name,salary*12 as annual
from s_emp;

4.使用||可以使得多列的值或者列和特殊的字元串合併到一個列進行顯示
語法：
select col_name||'spe_char'||col_name
from tb_name
'spe_char':如果一個列的值要跟特殊的字元串連接顯示，使用該語法。
需求：查看員工的員工id，全名
select id,first_name||last_name
from s_emp;
練習：查看員工的員工id，全名和職位名稱，全名和職位名稱合併成一列顯示，且格式為：姓 名，職位名稱
select id,first_name||' '||last_name||','||title as name
from s_emp;

5.對null值得替換運算
nvl()函數
語法：
select nvl(col_name,change_value)
from tb_name;
需求：查看所有員工的員工id，名字和提成，如果提成為空，顯示成0
select id,last_name,nvl(commission_pct,0) commission_pct
from s_emp;
6.使用distinct關鍵詞，可以將顯示中重覆的記錄只顯示一條
語法：
select distinct col_name,col_name...
from tb_name;

註意1：distinct關鍵詞只能放在select關鍵詞後面
如：select id,distinct title
from s_emp;
該語句語法錯!!!!!
註意2：如果distinct關鍵詞後面如果出現多列，表示多列聯合去重，即多列的值都相同的時候才會認為是重覆的記錄。
test表：
id id2
1 2
1 3
2 4
3 4
3 4
select distinct id,id2
from test;
顯示結果為：
id id2
1 2
1 3
2 4
3 4
需求：查看所有員工的職位名稱和部門id，同職位同部門的只顯示一次
select distinct title,dept_id
from s_emp;

7.sqlplus命令
sqlplus 登錄之後,可以使用buff(緩存)來存儲/執行/修改要執行的sql語句
這裡的buff的特點:
1.buff中只能存儲一條sql語句(但是這條sql語句可能有很多行)
2.每次放入新的sql語句,會把之前的覆蓋掉
3.每次執行sql語句,都會把這個sql語句放到buff裡面

l 查看緩存中的sql語句
a 在[定位]的那一行後面追加新的內容
i 在[定位]的那一行下麵插入新的一行
c 替換[定位]的那一行中的某些字元串
c/老的字元串/新的字元串
del 刪除[定位]的那一行內容
n 後面加內容可以重寫這一行
! 後面接終端命令 !clear:清屏 windows中使用$符號 例如:$cls
/ 執行緩存sql命令

clear buffer:清空當前緩存的命令

save test.sql buff中的sql語句保存在test.sql文件中
get test.sql 把test.sql中的內容在載入到buff中,但是沒有運行
start test.sql 把test.sql中的內容在載入到buff中並且執行
@test.sql 把test.sql中的內容在載入到buff中並且執行
edit file_name 使用系統預設編輯器去編輯文件

spool file_name 將接下來的sql語句以及sql的運行結果保存到文件中
sql1
result1
sql2
result2
...
spool off 關閉spool功能
exit：退出

8.select id,last_name,first_name, salary, dept_id
from s_emp
Where rownum <=10;

結果不好看，通過column使我們的顯示界面好看。

COLUMN last_name FORMAT a15;
可以簡寫為:
col last_name for a15;
COLUMN first_name FORMAT a15;

第二章：排序和限制查詢
1.排序:所謂排序，就是根據某個欄位的值按照升序或者降序的情況將記錄查詢出來
語法：
select col_name,...
from tb_name
order by col_name [asc|desc],...
註意：1.排序使用order by字句，該子句只對查詢記錄顯示調整，並不改變查詢結果，所以執行權最低，即最後執行。
2.排序關鍵詞：
asc：升序（預設，預設的意思是不加關鍵詞的時候預設為生序排序）
desc：降序
3.如果有多個列排序，後面的列排序的前提是前面的列排好序以後有重覆（相同）的值。

例子：
id id2
1 2
2 3
3 4
4 1
4 2

語句：
select id,id2
from test
order by id,id2 desc;

結果：
id id2
1 2
2 3
3 4
4 2
4 1
註意：先排第一列，如果第一列有重覆的值再排第二列，以此類推
需求：查看員工的id，名字和薪資，按照薪資的降序排序顯示。

2.限制查詢，即指定查詢條件進行查詢
語法：
select col_name,...
from tb_name
where col_name 比較操作表達式
邏輯操作符
col_name 比較操作表達式
...
註意：
1.限制查詢條件，使用where子句
2.條件可以多個，使用邏輯操作符和()進行條件的邏輯整合
3.where子句的優先順序別最高
4.比較操作表達式由操作符和值組成
常見的操作：
1》邏輯比較操作符
= > < >= <= !=
2》不等於:三個都表示不等於的意思(經常用的是!=)
!= <> ^=

需求：查看員工工資小於1000的員工id和名字
select id,last_name,salary
from s_emp
where salary < 1000;

2》sql比較操作符
between and：在什麼範圍之內
需求：查看員工工資在700 到 1500之間的員工id，和名字
select id,last_name,salary
from s_emp
where salary between 700 and 1500;
in(list):在一個列表中
需求：查看員工號1,3,5,7,9員工的工資
select id,last_name,salary
from s_emp
where id in (1,3,5,7,9);
like:模糊查詢，即值不是精確的值的時候使用
通配符，即可以代替任何內容的符號
% ：通配0到多個字元
_ : 當且僅當通配一個字元

轉義字元：
預設為\,可以指定 指定的時候用escape 符號指明即可,轉義字元只能轉義後面的一個字元
需求：查看員工名字以C字母開頭的員工的id，工資。
select id,last_name,salary
from s_emp
where last_name like 'C%';
練習：查看員工名字長度不小於5，且第四個字母為n字母的員工id和工資
select id,last_name,salary
from s_emp
where last_name like '___n_%';
需求：查看員工名字中包換一個_的員工id和工資
select id,last_name,salary
from s_emp
where last_name like '%\_%' escape '\';

is null：對null值操作特定義的操作符，不能使用=
需求：查看員工提成為為空的員工的id和名字
select id,last_name,commission_pct
from s_emp
where commission_pct is null;
3.邏輯操作符
當條件有多個的時候使用
and：且邏輯
or：或邏輯
註意：and邏輯比or邏輯要高
not：非邏輯
需求：查看員工部門id為41且職位名稱為Stock Clerk（存庫管理員）的員工id和名字
select id,last_name,dept_id,title
from s_emp
where dept_id = 41
and
title = 'Stock Clerk';
練習：查看員工部門為41 或者 44號部門 且工資大於1000的員工id和名字
select id,last_name,dept_id,title
from s_emp
where salary > 1000
and
(dept_id = 41
or
dept_id = 44);
查看員工部門為41且工資大於1000 或者 44號部門的員工id和名字
select id,last_name,dept_id,title
from s_emp
where salary > 1000
and
dept_id = 41
or
dept_id = 44;