Oracle筆記1-資料庫概念

標籤：

資料庫:

基本的概念:
資料庫管理系統(Database Management System,DBMS):管理資料的一個軟體系統
關係型資料庫管理系統(RDBMS)
資料庫(Database):存放資料的磁碟,是RDBMS的一部分

市面上常見的資料庫管理系統有哪些?
ORACLE,Mysql,Sql Server, Sqlite(行動數據庫), DB2, Access, ,sybase,h2...

SQL(Structure Query Language)結構化查詢語言 (SQL),
它是操作資料庫管理系統的一個標準:主要對資料庫進行CRUD的操作,
資料庫的運算都是通過SQL來完成的;
create:建立
delete:刪除
update:修改
retrieve:檢索

市面上的主流資料庫都遵守SQL規範:

注:不同的資料庫對SQL的支援又有些不同;

sql根據命令功能的不同分為5個部分:
1.DCL(Data Control Language)資料控制語句:grant,revoke

2.DDL(Data Definition Language)資料定義語句:
create,alter,drop,truncate,comment on,rename to...

3.DML(Data Manipulating Language)資料操縱語句:insert,update,delete

4.DQL(Date Query Language)資料查詢語句:select

5.DTL(Data Transaction Language)資料事務語句:commit,rollback,savepoint
-----------------------------------------------------------------------------
Oraclewww.oracle.com

...8i,9i,10g,11g,12c

安裝注意點: 目錄中不能由中文,建議把防火牆和360一些安全軟體關閉;

oracle 資料庫有服務端和用戶端組成:

服務端:由一堆的資料庫後台進程與監聽器以及資料庫所組成
最重要的兩個服務：
OracleServiceXE,Oracle資料庫主服務(XE表示當前資料庫的執行個體名,如果是企業版,則
執行個體名為Orcl)
OracleXETNSListener,Oracle資料庫監聽器,用來監聽第三方軟體對資料庫的串連

用戶端:有多種
1.Oracle內建一個用戶端:sqlplus -- 命令列工具
啟動:運行cmd 開啟cmd命令視窗輸入sqlplus
2.Navicat第三方,圖形介面
ctrl+q開啟查詢
ctrl+r運行所有語句
ctrl+shift+r運行選中語句

3.PL/SQL Developer第三方,圖形介面

4.Sql Developer,有oracle自己開發的使用的是java語言,需要JDK的支援

5.在ORACLE9i~10g中，提供了一個基於瀏覽器訪問的服務 isqlplus
也就是說，不需要專門的用戶端軟體了。通過瀏覽器就可以訪問,
目前已經廢除.
如：開啟瀏覽器，輸入：http://127.0.0.1:8080/apex

-----------------------------------------------------------------------------

建立使用者2步驟:
1.建立一個(普通)使用者:
文法:create user 使用者名稱 identified by 密碼;

例如:
create user jsd1510 identified by jsd1510;

2.授權給一個使用者:
角色:oracle中內建了3個角色:
DBA: 系統管理許可權;
connect: 建立會話串連許可權;
resource: 操作基本資料庫物件的許可權;

文法:grant 許可權,許可權... to 使用者名稱;

例如:給JSD1510帳號賦予串連,基本操作許可權
grant connect,resource to jsd1510;

修改密碼: alter user 使用者名稱 identified by 新密碼;

刪除使用者: drop user 使用者名稱;(刪除時注意許可權問題)

賬戶解鎖: alter user 使用者名稱 account unlock;

-----------------------------------------------------------------------------

sqlplus中常用命令:

1.切換使用者:
conn 使用者名稱/密碼;

2.顯示當前登入的使用者名稱:
show user;

3.執行資料庫指令碼:
start 或者 @ *.sql(可以直接把指令碼拖到命令視窗中)

例如:start D:/oralce_cn.sql;

資料庫指令碼:用於建立資料庫物件的語句集合
目的:在資料庫中建立儲存資料的表格並且在表格中初始化資料

4.查看錶的結構:這是sqlplus的命令,不是sql的命令
desc 表的名字;

5.清屏:
clear screen;

6.回憶記錄:
方向鍵上下

7.編輯多行sql語句:
edit

-------------------------------------------------------------------------
基本查詢:

表格(table):它是由行(rows)和列(column)組成的二維空間
它是資料庫存貯資料的基本單位,將來我們的資料就是存貯在表中;

查詢的本質:
1.要查什麼
2.去哪裡查

文法: select 列名,列名,... from 表格名;

如果查詢的是所有資訊,則可以使用*代替所有列
select * from 表名;

例如:
-- 找出員工的所有的資訊;
select * from s_emp;

-- 從s_emp表中查詢出員工的名字,薪水,職位;
select first_name,salary,title from s_emp;

-- 從s_emp表中查出員工的姓氏,名字;
select first_name,last_name from s_emp;

-- 找出本公司的所有的職稱(title);

排重: distinct
select distinct title from s_emp;

select * from s_title;

-- 找出所有員工的姓名、工資、入職日期
select first_name,salary,start_date from s_emp;

-- 找出所有的客戶名及他的電話號碼
select name,phone from s_customer;

-- 找出員工姓名及他的職稱
select first_name,title from s_emp;

-- 找出每個訂單的費用、支付方式、出貨日期
select total,payment_type,date_shipped from s_ord;

--------------------------------------------------------------------------

列的資料類型:
1.數字類型,可以做所有的數學運算
number number(4)代表整數 最大能存9999
number(7,2)代表double類型 整數長度為5 小數位為2.

2.字元型,可以做拼接的運算
注:字串在資料庫中用‘‘;
varchar2(20) 代表字串最長為20;
char(20) 代表定長20;

3.日期類型,可以做加減運算
Date年月日時分秒
Time時分秒
Timestamp年月日時分秒,還有小數位,如3.2秒

4.大資料類型
clob character large object 大字元型對象 最大可存4G
blob binary large object大二進位對象 最大可存4G

注:大資料類型不支援查看結果

如:
sql具有運算的能力
--從員工表中查詢出員工名字和他的年薪
select first_name,salary*12 from s_emp;
select first_name 姓名,salary*12 年薪 from s_emp;
select first_name "姓 名",salary*12 "年 薪" from s_emp;

注意:Oracle可以給查詢的列取別名,別名可以直接書寫,也可以用""修飾,
""還可以控制格式以及區分大小寫

sql具有字串拼接能力
-- 從s_emp表中查出員工的姓名;
select first_name||‘ ‘||last_name 全名 from s_emp;

--字串拼接(***是**入職的,工資是***,職稱是***)
select first_name||‘ ‘||last_name||‘是‘||start_date||‘入職的,工資是‘||
salary||‘,職稱是‘||title 員工概述 from s_emp;

select first_name||‘ ‘||last_name||‘是‘||to_char(start_date,‘yyyy-mm-dd‘)||‘入職的,工資是‘||
salary||‘,職稱是‘||title 員工概述 from s_emp;

-----------------------------------------------------------------------------

空值置換函數

--查詢所有員工的名字,年薪(不考慮提成)
select first_name,salary*12 from s_emp;

--查詢所有員工的名字,年薪(考慮提成)
--錯誤
select first_name,salary *12 * (1 + commission_pct/100) from s_emp;

原因:
null和所有的值進行運算最後都為null;

空值置換函數nvl(commission_pct/100,0)
如果commission_pct/100的值為null 則運算式的值為0
如果commission_pct/100的值不為null 則運算式的值為commission_pct/100

--正確
select first_name,salary * 12 * (1 + nvl(commission_pct/100,0)) from s_emp;

注:nvl()中的兩個值要為同一資料類型

---------------------------------------------------------------------------

條件查詢:

where字句用來過濾查詢的資料,它對字面量大小寫是敏感的
出現在where後面的運算子有:

1.比較子:
> , < , >= , <= , = , != ^= <>

--查出在41部門的員工名字,工資;
select first_name,salary from s_emp where dept_id=41;

--查出工資高於1500的員工的資訊;
select * from s_emp where salary>1500;

--找出工資大於1200元的員工全名、工資、職稱
select first_name||‘ ‘||last_name,salary,title from s_emp
where salary>1200;

2.邏輯運算子:
and,or,not

--查出41部門工資高於1200的員工名字,工資;
select first_name,salary from s_emp where dept_id=41 and salary>1200;

--查出在41,50,42部門的員工名,薪水;
select first_name,salary from s_emp
where dept_id=41 or dept_id=42 or dept_id=50;

3.其他運算子:
in(list)list是一個列表，多個值使用逗號隔開
not in()
between ? and ?在指定的範圍之內，是全閉空間
is null
is not null

模糊比對:like

萬用字元:
_ 通配任意單個字元
% 通配任意多個字元

--查出在41,42,50部門的員工名,薪水;
select first_name,salary from s_emp where dept_id in(41,42,50);

--找出工資在1200到1500之間的員工名(包括兩端,不包括兩端2個答案)
select first_name,salary from s_emp where salary between 1200 and 1500;
select first_name,salary from s_emp where salary>1200 and salary<1500;

--找出工資大於1500並且沒有提成的員工;
select * from s_emp where salary>1500 and commission_pct is null;

注意:查詢資料時條件是否為null,我們使用關鍵字is或者is not null,千萬不能使用=/!=

--查出名字是以M打頭的員工;
select * from s_emp where first_name like ‘M%‘;

--查出姓名中第三個字母是e的員工;
select * from s_emp where first_name like ‘__e%‘;

如果我們要通配的字元中本身就有_或%,則需要通過關鍵字escape來轉義;
select * from s_emp where first_name like ‘\_t%‘ escape ‘\‘;

-- 找出沒有提成率的員工
select * from s_emp where commission_pct is null;

-- 找出有提成率的員工
select * from s_emp where commission_pct is not null;

-- 找出費用超過10000元的訂單編號及支付方式
select id,payment_type from s_ord where total>10000;

-- 找出工資在950(含)至1200(含)元的員工姓名、職稱
select first_name,title from s_emp where salary between 950 and 1200;

-- 找出名字中含有字母a的員工
select * from s_emp where first_name like ‘%a%‘;

-- 找出名字中第二個字母是a的員工
select * from s_emp where first_name like ‘_a%‘;

-- 找出2月份入職的員工名、入職時間、工資
select first_name,start_date,salary from s_emp
where start_date like ‘%2月%‘;

select first_name,start_date,salary from s_emp
where to_char(start_date,‘mm‘)=2;

-- 查詢出職稱是Stock Clerk的員工全名、工資，並按工資的降序排序
select first_name||‘ ‘||last_name,salary from s_emp
where title=‘Stock Clerk‘ order by salary desc;

--------------------------------------------------------------------------

排序字句:
order by 列名 asc(升序,預設可不寫) | desc (降序)

--找出6月份入職的員工全名,工資,職稱,並按工資降序排序
select first_name||‘ ‘||last_name,salary,title from s_emp
where to_char(start_date,‘mm‘)=6 order by salary desc;

--查詢出職稱中帶VP的員工名字,工資,並按工資的降序排序
select first_name,salary,title from s_emp where title like ‘%VP%‘
order by salary desc;

--查詢出年薪低於25000的員工名,職稱,並按年薪升序排序
select first_name,title,salary*12*(1+nvl(commission_pct/100,0)) 年薪
from s_emp
where salary*12*(1+nvl(commission_pct/100,0))<25000
order by 3;

注意:order by 3表示按照查詢結果的第3列進行排序,
即,order by後面可以根據列名(別名)或者列號進行排序.

--------------------------------------------------------------------------

dual是一張虛擬表格,沒有任何的意義,只是為了充當構建完整的select語句

如:查詢當前資料庫系統時間
select sysdate from dual;

單行函數(single function):
多行函數(組函數)

oracle中提供了大量的函數
處理方式的不同分為:
1.數字函數:
round(列|值|運算式,有效位元); 四捨五入
trunc(列|值|運算式,有效位元); 直接截取
mod(列|值,列|值); 求模(餘)
abs(列|值) 求絕對值

select round(3.1415926,3) from dual;
select trunc(3.1415926,3) from dual;
select mod(10,3) from dual;

--查詢出訂單表中總金額(取整)
select id,round(total,0) from s_ord;

2.字元函數
length(列|值|運算式); 求長度
--擷取員工表中每個員工的姓氏長度
select first_name,length(first_name) from s_emp;

--查看‘HelloWorld‘字串的長度
select length(‘HelloWorld‘) from dual;

upper(列|值|運算式); 轉大寫字母
lower(列|值|運算式); 轉小寫字母
initcap(列|值|運算式); 每個單字首大寫

select name,score from s_student where lower(name)=‘tom‘;

select upper(‘abc‘) from dual;
select lower(‘ABC‘) from dual;
select initcap(‘hello world‘) from dual;

lpad(列|值,寬度,填充字元); 從左邊補滿寬度個填充字元
rpad(列|值,寬度,填充字元); 從右邊補滿寬度個填充字元
select rpad(‘abc‘,10,‘de‘) from dual;//abcdededed
select lpad(‘abc‘,10,‘de‘) from dual;//dedededabc

ltrim(列|值,截取字元); 從左邊截取字元
rtrim(列|值,截取字元); 從右邊截取字元
select rtrim(‘abcdededededede‘,‘de‘) from dual;//abc
select rtrim(‘abcdededeededeee‘,‘de‘) from dual;//abc
select rtrim(‘abcdededeadedede‘,‘de‘) from dual;//abcdededea

replace(列|值,被置換的字元,置換的字元)置換指定字元
select replace(‘hello world!‘,‘o‘,‘*‘) from dual;

translate(參數一,參數二,參數三)轉換指定字元
參數一:要處理的內容,列|值.
參數二:需要檢索的內容
參數三:與檢索內容一一對應替換的內容

select translate(‘I l6o9ve y8o7u!‘,
‘abcdefghijklmnopqrstuvwxyz0123456789‘,
‘abcdefghijklmnopqrstuvwxyz‘)
from dual;

select replace(‘abcdededeadedede‘,‘de‘,‘‘) from dual;//abca
或者
select translate(‘abcdededeadedede‘,‘abcde‘,‘abc‘) from dual;//abca

instr()尋找指定字串在長字串中所在的位置
select instr(‘go,go,quickly!‘,‘go‘) from dual;//1
select instr(‘go,go,quickly!‘,‘go‘,2) from dual;//4
select instr(‘go,go,quickly!‘,‘go‘,-1) from dual;//4
select instr(‘go,go,quickly!‘,‘go‘,-1,2) from dual;//1

注意:資料庫字串下標從1開始計數!!!

substr()
select substr(‘hello world!‘, 3) from dual;//llo world!
select substr(‘hello world!‘,3,4) from dual;//llo
//從3號下標開始切割,保留4個字元

decode()
a.類似一個三目運算子
比較第一和第二個參數,如果相等取第三個參數,如果不相等取第四個參數
select decode(‘A‘,‘A‘,‘B‘,‘C‘) from dual;

b.if else if else

case when 可以把它看成switch功能:
select name "部門名",
case region_id
when 1 then ‘北美‘
when 2 then ‘南美‘
when 3 then ‘中東/非洲‘
when 4 then ‘亞洲‘
when 5 then ‘歐洲‘
else ‘未知地區‘
end "地區名"
from s_dept;

練習:
--查出客戶表(s_customer)中phone列最後一個-線後面的部分;
select phone,substr(phone,instr(phone,‘-‘,-1)+1) from s_customer;

--把圖片表(s_image)中檔案名稱(filename)列中尾碼給查出來;
select distinct substr(filename,instr(filename,‘.‘,-1)+1) from s_image;

--類比向銀行中只顯示姓名的第一個字元(奧巴馬變成奧**):
select first_name,rpad(substr(first_name,1,1),length(first_name),‘*‘)
from s_emp;

select first_name,
replace(first_name,substr(first_name,2),rpad(‘*‘,length(substr(first_name,2))-1,‘*‘))
from s_emp;

--找出名字長度超過5的員工
select * from s_emp where length(first_name)>5;

--找出職稱是stock clerk的員工
select * from s_emp where lower(title)=lower(‘stock clerk‘);

--請把員工的工資分為3等,超過2000元的為高等在1500和2000之間的為中等
低於1500的為低等
select first_name 員工名,
case
when salary<1500 then ‘低等‘
when salary>=1500 and salary<2000 then ‘中等‘
when salary>=2000 then ‘高等‘
end 工資等級
from s_emp;

注意:case 後面是否出現列名,取決於when後麵條件中是否出現比較的列,
如果when後面已經出現列名,則case後面務必不能寫列名,
如果when後面是值,那麼必須在case後面指定列名,否則無法確定所給定的值
真正的含義.

--輸出每個訂單編號及支付方式，支付方式要麼是現金，
要麼是信用卡，否則就是未知
select id,
decode(payment_type,‘CASH‘,‘現金‘,‘CREDIT‘,‘信用卡‘,‘未知‘)
from s_ord;

--輸出地區表(s_region)中每個地區的對應的中文
select name,
decode(name,‘North America‘,‘北美‘,‘South America‘,‘南美‘,‘Africa / Middle East‘,‘非洲/中東‘,‘Asia‘,‘亞洲‘,‘Europe‘,‘歐洲‘,‘未知地區‘)
from s_region;

3.日期函數

oracle 預設的日期格式為 dd-MON-yy

dd表示2位元的日
mon/month代表月份,如:6月
mm代表2位元的月,如:06
yyyy代表4位元年份
HH24/HH代表小時
mi代表分鐘
ss代表秒
fm表示去掉前面的0,如:fm 06,以後值6
day表示星期幾

日期函數:
sysdate當前日期
months_between(date1,date2)2個日期之間的月數
add_months(date1,n)在date1的基礎上加上幾個月
next_day(date1,‘星期幾‘)在date1的基礎上下個星期幾是什麼時候
last_day(date)date日期所在月的最後一天是什麼時候

例如:
--查詢2014年11月1號和2015年12月9號之間的間隔月數
select
months_between
(
to_date(‘2015-12-9‘,‘yyyy-mm-dd‘),to_date(‘2014-11-1‘,‘yyyy-mm-dd‘)
) from dual;

--半年後的日期是?
select add_months(sysdate,6) from dual;

--半年前的日期是?
select add_months(sysdate,-6) from dual;

--查詢當前日期下一個星期五是幾號?
select next_day(sysdate,‘星期五‘) from dual;

--查詢當前月的最後一天是什麼時候?
select last_day(sysdate) from dual;

練習:
--找出員工的工作月數
select months_between(sysdate,start_date) from s_emp;

--查詢員工的工作天數
select first_name,sysdate-start_date from s_emp;

注意:
Oracle中日期類型是可以進行減法運算的,結果是兩個日期的天數!!!

--計算一年前,當前,一年後的時間
select add_months(sysdate,-12),sysdate,add_months(sysdate,12) from dual;

--當前日期前六個月的最後一天
select last_day(add_months(sysdate,-6)) from dual;

補充:
修改當前會話語言環境:
alter session set nls_language = ‘american‘;//改為英文
alter session set nls_language = ‘simplified chinese‘;//改為簡體中文

修改當前會話日期的格式:
alter session set nls_date_format=‘yyyy-mm-dd HH24:mi:ss‘;

4.轉換函式

to_number(char), 把字元轉換成數字

to_date(char[,fmt]),
把字串轉換成日期,如果不傳fmt參數,則採用預設格式(DD-MON-YY)來解析

to_char把數字或日期格式化為字串
1.to_char(number,fmt); 格式化數字
2.to_char(date, fmt); 格式化日期

把數字格式化為字串的時候可以用的一些符號:
9 代表任意的資料
L 代表本地的貨幣符號
$ 代表美元
0 代表0
. 代表.
, 代表,

--把123字串轉換成123數字
select to_number(‘123‘) from dual;

--把1986-04-13字串按照模板yyyy-mm-dd轉換成日期
select to_date(‘1986-04-13‘,‘yyyy-mm-dd‘) from dual;

--格式化輸出員工的工資($1,500.00)
select to_char(salary,‘L999,999,999,999.99‘) from s_emp;

練習:
--把員工的入職日期格式化為年/月/日
select first_name,to_char(start_date,‘yyyy/mm/dd‘) from s_emp;

--找出5月份入職的員工
select * from s_emp where to_char(start_date,‘fmmm‘)=5;

--找出當月入職的員工
select * from s_emp where to_char(start_date,‘fmmm‘)=to_char(sysdate,‘fmmm‘);

--查詢出員工的姓名,入職日期,並按日的升序排序
select first_name,start_date from s_emp
order by to_number(to_char(start_date,‘fmdd‘));

--找出每個員工的名字和它的薪水(如:$2,500.00)
select first_name,to_char(salary,‘$999,999,999,999.99‘) from s_emp;

Oracle筆記1-資料庫概念