1.簡(jiǎn)介
數(shù)據(jù)存儲(chǔ)有哪些方式？電子表格，紙質(zhì)文件，數(shù)據(jù)庫。
那么究竟什么是關(guān)系型數(shù)據(jù)庫？
目前對(duì)數(shù)據(jù)庫的分類主要是關(guān)系型和非關(guān)系型兩種，關(guān)系型的主要代表有oracle，db2，mysql，sqlserver等，非關(guān)系型數(shù)據(jù)庫也稱作nosql，
主要有mongodb,hbase等等。關(guān)系型數(shù)據(jù)庫主要是以二維表的方式存數(shù)數(shù)據(jù)的，這是關(guān)系數(shù)據(jù)庫最顯著的特征，
什么是二維表呢？
Excel表格就是二維表，由行和列兩個(gè)維度所組成，想想excel表格，你就會(huì)知道數(shù)據(jù)庫里面的表是什么樣子的，道理是一模一樣的。
而非關(guān)系型數(shù)據(jù)庫的數(shù)據(jù)組織方式就五花八門了，有星型的，網(wǎng)狀的等等，他們都統(tǒng)稱為非關(guān)系型數(shù)據(jù)庫，也叫nosql。
關(guān)系型數(shù)據(jù)庫需要一組操作符，實(shí)際上就是需要一套命令，或者說是語言，也就是我們現(xiàn)在要學(xué)習(xí)的sql，它是我們和數(shù)據(jù)庫進(jìn)行溝通的工具。
2.Structured Query Language 結(jié)構(gòu)化查詢語言
（1）包含4種類型語句：
數(shù)據(jù)操縱語言DML-Data Manipulation Language SELECT ,INSERT, UPDATE, DELETE
數(shù)據(jù)定義語言DDL-Data Definition Language CREATE, ALTER, DROP
數(shù)據(jù)控制語言DCL-Data Control Language? GRANT REVOKE
事物控制語句TCL-Transacation Contrl Language COMMIT , ROLLBACK
（2）如何書寫sql
大小寫不敏感,但單引和雙引內(nèi)的大小寫是敏感的.
關(guān)鍵字不能縮寫select不能寫成sel
字符串用單引 比如 ename='SEKER'
列的別名含特殊字符用雙引
可跨行,但不要將關(guān)鍵字和單引的內(nèi)容跨行.
跨行是為了可讀性,一般我們都把select子句和from子句分行寫.
不要在自定義參數(shù)部分使用sql的關(guān)鍵字。
3.SQL語法學(xué)習(xí)
（1）整個(gè)學(xué)習(xí)SQL過程是使用SCOTT用戶的表來學(xué)習(xí)的 默認(rèn)scott是被鎖定的 解鎖的方法
原始狀態(tài)下，這個(gè)用戶是被鎖定的，我們需要解鎖這個(gè)用戶
SQL> conn / as sysdba
SQL> alter user scott account unlock identified by tiger;
通過sys解鎖scott用戶 并將scott的密碼設(shè)置成tiger
SQL> conn scott/tiger
在以后的學(xué)習(xí)過程中 scott的表經(jīng)常被修改 如果想將scott環(huán)境恢復(fù)默認(rèn) 使用系統(tǒng)自帶腳本即可
SQL> show user
USER is "SYS"
SQL> @?/rdbms/admin/utlsampl.sql
如果是windows版本則是 @?\rdbms\admin\utlsampl.sql
@是加載OS中的SQL保存文件
?是$ORACLE_HOME的替代
執(zhí)行完腳本 會(huì)自動(dòng)退出 重新登錄 再對(duì)scott解鎖即可
（2）一個(gè)完整的SQL命令叫語句(statement),每個(gè)關(guān)鍵字和后面跟著的選項(xiàng)叫子句(clause)
select 指定查詢的列
from? 指定查詢的表
where? 過濾的條件
order by 排序的列
DESC|ASC 排序方法
連接到scott用戶
1.scott用戶擁有哪些表？
SQL>select * from tab;
查詢表中所有行所有列
SQL>select * from dept;
SQL>select * from emp;
如果屏幕顯示的內(nèi)容串行 是因?yàn)槟J(rèn)的顯示的行長(zhǎng)度是80字節(jié) 而選出的內(nèi)容超出了80字節(jié) 可以修正一下
SQL> set linesize 100
如果有多個(gè)列標(biāo)題 是一頁內(nèi)行數(shù)默認(rèn)14行 也可以修正
SQL> set pagesize 1000
這個(gè)修改只是內(nèi)存中的 可以將命令保存住到文件 實(shí)現(xiàn)永久配置
SQL> !ls $ORACLE_HOME/sqlplus/admin/g*
/u01/oracle/product/10.2.0/sqlplus/admin/glogin.sql
2.描述表結(jié)構(gòu)
desc TABLE_NAME
SCOTT@ora10g> desc emp
Name ? ? ? Null? Type
----------------------------- -------- --------------------
EMPNO ? ? ? NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SCOTT@ora10g> desc dept
SQL@ora10g> desc SALGRADE
desc描述表的結(jié)構(gòu) 所謂表的結(jié)構(gòu):就是表有多少列 列的名稱和類型及約束(非空)
desc emp
emp表(員工表)的結(jié)構(gòu)介紹:
? ? ? ? ? emp表(員工表)的結(jié)構(gòu)介紹:
??????????empno? 員工工號(hào) 列為整數(shù),最大長(zhǎng)度為4位.
?????????? ename? 員工名字 列為變長(zhǎng)字符型,最大長(zhǎng)度10個(gè)字符
?????????? job? ? 出任職位 列為變長(zhǎng)字符型,最大長(zhǎng)度9個(gè)字符
?????????? mgr? ? 所屬領(lǐng)導(dǎo)工號(hào) 列為整數(shù),最大長(zhǎng)度為4位
????????????hiredate入職日期 列為日期類型
??????????? sal? ? 工資 列為浮點(diǎn)數(shù),最大長(zhǎng)度為7位,其中包含2位小數(shù)
????????????comm? ? 獎(jiǎng)金 列為浮點(diǎn)數(shù),最大長(zhǎng)度為7為,其中包含2位小數(shù)
???????????? deptno? 部門號(hào) 列為整數(shù),最大長(zhǎng)度為兩位
desc dept
dept表(部門表)的結(jié)構(gòu)介紹
? ? ? ? ? ? ? deptno? 部門號(hào) 列為整數(shù),最大長(zhǎng)度為兩位
? ? ? ? ? ? ? dname? 部門名字 列為變長(zhǎng)字符型,最大長(zhǎng)度為14個(gè)字符
? ? ? ? ? ? ? loc? ? 部門所在地理位置 列為變長(zhǎng)字符型,最大長(zhǎng)度為13個(gè)字符
DESC SALGRADE
SALGRADE表(薪水等級(jí)表)的結(jié)構(gòu)介紹 三列都是數(shù)值型
? ? ? ? ? ? ? ? GRADE? 薪水等級(jí)
? ? ? ? ? ? ? ? LOSAL? 所在等級(jí)中薪水底線
? ? ? ? ? ? ? ? HISAL 所在等級(jí)中薪水上限
3.查詢表中指定的列
SQL>select ename,sal From emp;
select ename,sal from emp;
錯(cuò)誤語法: select *,sal from emp; 星號(hào)不可以與單列同時(shí)存在
4.在sql中使用算術(shù)表達(dá)式
select ename,sal*12 from emp;
select ename,(500+sal)*12 from emp;
算術(shù)運(yùn)算符優(yōu)先級(jí):
先乘除后加減,同優(yōu)先級(jí)自左至右
小括號(hào)提高優(yōu)先級(jí),多重括號(hào)則自內(nèi)而外
5.在查詢中為列命名別名
select ename,sal*12 as annual_salary from emp;
select ename,sal*12 annual_salary from emp;
6.在別名中使用特殊字符 要用雙引號(hào)
select ename,sal*12 "annual salary" from emp;
7.表別名 對(duì)emp表取了個(gè)簡(jiǎn)單別名e? 這樣就可以在引用表名時(shí)簡(jiǎn)化輸入
select ename,sal from emp e;
同時(shí)也解決了星號(hào)和列同時(shí)出現(xiàn)的語法錯(cuò)誤 別名的本質(zhì)就是將非法的內(nèi)容合法化
select e.*,sal from emp e;
8.連接操作符
select ename,job from emp;
select ename||' is a '||job from emp;
SQL> select ename||q'['s sal is]'||sal from emp;
9.去重復(fù)值
select deptno from emp;
select distinct deptno from emp;
多列去重
select distinct deptno,job from emp;
10.日期的顯示格式
select hiredate from emp;
默認(rèn)的日期格式是 DD-MON-RR
修改系統(tǒng)參數(shù)
alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
需要sysdba權(quán)限，靜態(tài)參數(shù)，需要重啟數(shù)據(jù)庫生效
修改會(huì)話參數(shù)
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
11. 虛表
虛表 oracle自動(dòng)創(chuàng)建的一張表 可以理解為是一張假想表 目的是為了使select語義完成
借助虛表 我們可以完成很多事
查看當(dāng)前用戶
select user from dual;
查看當(dāng)前時(shí)間(OS時(shí)間)
select sysdate from dual;
計(jì)算
select 9+8 from dual;
獲取隨機(jī)數(shù)
select dbms_random.random from dual;
等等...以后還有很多查詢需要借助虛表完成.
12.過濾條件
select empno,ename,sal from emp where sal >= 1500;
WHERE是關(guān)鍵字 后面跟限制條件
條件由列名,字符串,算術(shù)表達(dá)式,常量和比較運(yùn)算符組成;
比較運(yùn)算符:
> >= < <= = <> !=
邏輯運(yùn)算符:
and? or? not
oracle提供的比較運(yùn)算符
between and??????? in???????????? like????????????????? is null
數(shù)值比較 直接寫數(shù)值
SQL> select ename,sal from emp where sal >= 800 and sal <= 1100;
SQL> select ename,sal from emp where sal between 800 and 1100;
字符串比較 字符串要加單引號(hào) 默認(rèn)存儲(chǔ)模式就是大寫模式
SQL> select ename,sal from emp where ename='KING';
時(shí)間格式比較
日期區(qū)間表示法
先修改日期顯示為我們習(xí)慣的格式 否則寫系統(tǒng)默認(rèn)的時(shí)間格式 DD-MON-RR
alter session set NLS_date_format='YYYY-MM-DD HH24:MI:SS';
select ename,hiredate from emp where hiredate between '1981-01-01' and '1981-05-31';
13.oracle的與and 或or? 非not
and(與)
select ename,sal from emp where sal<=1300 and sal >=900;
or(或)
select ename,sal from emp where sal<=1000 or sal >=4000;
not(非)
select ename,sal from emp where not sal >=4000;
與 ==> 兩端都為真 返回真 若有一端或兩端為假則為假
或 ==> 若有一端或兩端為真則為真 兩端都為假 返回假
但oracle中又引入了NULL
AND運(yùn)算表
T and F = F ?? ?T and T = T ? ? ? T and NULL is NULL
F and F = F ?? ?F and T = F ? ? ? F and NULL = F
NULL and F = F ? ?NULL and T is NULL? ? NULL and NULL is NULL
idle> select ename,sal from emp where sal >2000 and sal <3000;
OR運(yùn)算表
T or T = T ? T or F = T200 ? ? ? T or NULL = T
F or T = T ?? F or F = F ? ? ? F or NULL IS NULL
NULL or T = T ?? NULL OR F is NULL? ? NULL OR NULL IS NULL
idle> select ename,sal,comm from emp where sal = 1250 or comm is not null;
14.oracle提供的運(yùn)算符
like???? between and???? in??? not like
is not null???? not between and
運(yùn)算符的優(yōu)先級(jí)不用記憶 想提高優(yōu)先級(jí)加小括號(hào)即可
SQL> select *from scott.emp where sal>2000 and deptno=20 or deptno=30;
SQL> select *from scott.emp where sal>2000 and (deptno=20 or deptno=30);
用and or 來代替 between and 和 in
idle> select empno,ename,sal from emp where sal between 1000 and 1300;
idle> select empno,ename,sal from emp where sal >=1000 and sal <= 1300;
idle> select empno,ename,sal from emp where ename in ('SCOTT','KING','ADAMS');
idle> select empno,ename,sal from emp where ename = 'SCOTT' or ename = 'KING' or ename = 'ADAMS';
like 通配符: % 任意長(zhǎng)度(包括零長(zhǎng)度)通配符
_ 單個(gè)字符通配符
部門名像 SAL開頭的
select ename,job from emp where job like 'SAL%';
任意一個(gè)字符后跟A之后任意字符串的名字
select ename,job from emp where ename like '_A%';
取反
select ename,job from emp where ename not like '_A%';
like中使用轉(zhuǎn)義
如果查詢的字符串包含_或%字面字符串則需要轉(zhuǎn)義
轉(zhuǎn)義符號(hào)需要在子句中用escape指明
select? * from dept_tmp where dname like 'IT_%';
select? * from dept_tmp where dname like 'IT\_%' escape '\';
15.order by 排序輸出
排序一定要放在sql語句的最后
排序格式化了輸出 確給SQL執(zhí)行增加了負(fù)擔(dān)
默認(rèn)是從小到大(升序) ASC; 逆序(降序)DESC
select ename,sal from emp where sal < 1500 order by sal;
select ename,sal from emp where sal < 1500 order by sal asc;
select ename,sal from emp where sal < 1500 order by sal desc;
order by中使用表達(dá)式
select ename,sal*12 from emp where sal < 1500 order by sal*12;
order by中使用別名
select ename,sal*12 "abc" from emp where sal < 1500 order by "abc";
order by中使用列號(hào) 列號(hào)必須是select子句中已經(jīng)選擇的列的順序號(hào)
select ename,sal*12 "abc" from emp where sal < 1500 order by 2;
order by中使用多列 先按職位A-Z的順序 再按年薪小到大的順序
select ename,job,sal*12 "abc" from emp where sal < 1500 order by 2,3;
order by中使用select子句中未選擇的列
select ename,job,sal*12 "abc" from emp where sal < 1500 order by deptno;
4.單行函數(shù)
什么是函數(shù)?
通俗的講 任何東西,只要它能接收輸入,對(duì)輸入進(jìn)行加工并產(chǎn)生輸出,它就可以被稱之為函數(shù)
例如:牛是函數(shù),它吃的是草(輸入),擠出的是奶(輸出)
函數(shù)是一種程序設(shè)計(jì)結(jié)構(gòu),它可以有一個(gè)或多個(gè)輸入,但只能有一個(gè)輸出.
函數(shù)只有一個(gè)出口,使用函數(shù)組成的程序很容易調(diào)試,也很容易被重用
1.字符類型的函數(shù)
字符型
ASCII????? CHR??????? LOWER??????? UPPER??????????? INITCAP? CONCAT????SUBSTR??????
?LENGTH?? INSTR?? TRIM??? dump?? lpad?? rpad????? REPLACE
ASCII('字符')
返回字符的ASCII碼值
SCOTT>>select ascii('a') from dual;
ASCII('a')
----------
??????? 97
CHR('n')
返回n的字符值 n是ASCII碼數(shù)
SCOTT>>select chr(97) from dual;
C
-
a
但是求單引號(hào)的ASCII碼寫法很特殊 兩個(gè)單引帶表一個(gè)單引
SCOTT>>select ascii(''') from dual;
ERROR:
ORA-01756: quoted string not properly terminated
SCOTT>>select ascii('''') from dual;
ASCII('''')
-----------
39
LOWER(列名|表達(dá)式)
小寫轉(zhuǎn)換
idle> select lower('ABC') from dual;
LOW
---
abc
UPPER(列名|表達(dá)式)
大寫轉(zhuǎn)換
SCOTT>>select upper('abc')from dual;
UPP
---
ABC
INITCAP(列名|表達(dá)式)
每個(gè)詞的詞頭大寫 其他小寫
SCOTT>>select initcap('abc xyz qwe')from dual;
INITCAP('AB
-----------
Abc Xyz Qwe
CONCAT(列名|表達(dá)式,列名|表達(dá)式)
將第一個(gè)字符串和第二個(gè)字符串連接
SQL> select concat('abc','xyz') from dual;
CONCAT
------
abcxyz
該合數(shù)與||功能一樣，但是我們常用的是后者
SQL> select 'abc'||'xyz' from dual;
'ABC'|
------
abcxyz
SUBSTR(列名|表達(dá)式,m,[n])
返回指定子串,該子串是從第m個(gè)字符開始,其長(zhǎng)度為n,不指定n值則從m到最后
SQL> select substr('abcdefg',4,3)from dual;
SUB
---
def
SQL> select substr('abcdefg',4)from dual;
SUBS
----
defg
LENGTH(列名|表達(dá)式)
返回字符串的長(zhǎng)度
SQL> select length('abcdef')from dual;
LENGTH('ABCDEF')
----------------
?????????????? 6
LENGTHB(列名|表達(dá)式)
返回字符串所占用的字節(jié)數(shù)
INSTR (列名|表達(dá)式,'字符串',[m],[n])
從表達(dá)式或列中搜索給定的字符串的所處位置,m代表從第幾個(gè)字符開始搜,n代表要搜索的字符第幾次出現(xiàn). m和n默認(rèn)都是1
SQL> select instr('abcdddd','d') from dual;
INSTR('ABCDDDD','D')
--------------------
??????????????????? 4
SQL> select instr('abcdddd','d',5) from dual;
INSTR('ABCDDDD','D',5)
----------------------
????????????????????? 5
SQL> select instr('abcdddd','d',5,2) from dual;
INSTR('ABCDDDD','D',5,2)
------------------------
????????????????????? 6
TRIM([leading|]trailing|both 要去掉的字符 FROM 源字符串)
從源字符串中去掉指定的字符 可以用leading tailing來修飾去掉的字符串是在開頭或結(jié)尾,默認(rèn)是兩者都
默認(rèn)截取的是空格，生產(chǎn)上有時(shí)候有的字段會(huì)產(chǎn)生空格，這個(gè)會(huì)給應(yīng)用帶來很大的麻煩，用這個(gè)函數(shù)就
可以很容易的，除去字段兩頭的空格，是非常實(shí)用的。
SQL> select trim(leading 'a' from 'aaabbbbbaaaa')from dual;
TRIM(LEAD
---------
bbbbbaaaa
SQL> select trim(trailing 'a' from 'aaabbbbbaaaa')from dual;
TRIM(TRA
--------
aaabbbbb
SQL> select trim(both 'a' from 'aaabbbbbaaaa')from dual;
TRIM(
-----
bbbbb
SQL> select trim('a' from 'aaabbbbbaaaa')from dual;
TRIM(
-----
bbbbb
SQL> select trim('? aaabbbbbaaaa? ')from dual;
TRIM('AAABBB
------------
aaabbbbbaaaa
左補(bǔ)齊lpad 右補(bǔ)齊rpad
將不足20個(gè)字符的位置用指定符號(hào)填充.
SQL> select lpad('abc',20,'-'),rpad('abc',20,'-') from dual;
LPAD('ABC',20,'-')? RPAD('ABC',20,'-')
-------------------- --------------------
-----------------abc abc-----------------
REPLACE(源字符串,OLD字符串,NEW字符串)
從源字符串中找到搜索的old字符串,替換成new字符串
SQL> select replace('abcdefxyz','def','DEF')from dual;
REPLACE('
---------
abcDEFxyz
dump('str'[,FMT[,S,E]])
用于轉(zhuǎn)換進(jìn)制格式
str 被轉(zhuǎn)換的字符串
FMT 格式
默認(rèn)是十進(jìn)制的
8:用八進(jìn)制方式顯示
16：使用16進(jìn)制方式顯示
1016:把數(shù)據(jù)庫當(dāng)前字符集顯示出來
S? str的開始字符位置
E? S開始的后續(xù)結(jié)束字符位置
SQL> select dump('abcd') from dual;
DUMP('ABCD')
--------------------------
Typ=96 Len=4: 97,98,99,100
SQL>
SQL>
SQL> select dump('abcd',2) from dual;
DUMP('ABCD',2)
--------------------------
Typ=96 Len=4: 97,98,99,100
SQL> select dump('abcd',10) from dual;
DUMP('ABCD',10)
--------------------------
Typ=96 Len=4: 97,98,99,100
SQL> select dump('abcd',8) from dual;
DUMP('ABCD',8)
-----------------------------
Typ=96 Len=4: 141,142,143,144
SQL> select dump('abcd',16) from dual;
DUMP('ABCD',16)
-------------------------
Typ=96 Len=4: 61,62,63,64
SQL> select dump('abcd',1010) from dual;
DUMP('ABCD',1010)
------------------------------------------------
Typ=96 Len=4 CharacterSet=ZHS16GBK: 97,98,99,100
SQL> select dump('戴')from dual;
DUMP('戴')
---------------------
Typ=96 Len=2: 180,247
SQL> select 180*power(256,1)+247 from dual;
180*256+247
-----------
46327
SQL> select chr(46327) from dual;
CH
--
戴
SQL> select dump(987654321) from dual;
DUMP(987654321)
-------------------------------
Typ=2 Len=6: 197,10,88,66,44,22
197-193=4
10-1=9*power(100,(4-0))=900000000
88-1=87*power(100,(4-1))=87000000
66-1=65*power(100,(4-2))=650000
44-1=43*power(100,(4-3))=4300
22-1=21*power(100,(4-4))=21
2.數(shù)值型函數(shù)
????????? ?ROUND
?????????? TRUNC
??????????? MOD
??????????? CEIL
????????????power
??????????? greatest
????????????least
ROUND(列名|表達(dá)式,n)
四舍五入到小數(shù)點(diǎn)后的n位
SQL> select round(456.789,0),round(456.789,1),round(456.789,-1)from dual;
ROUND(456.789,0) ROUND(456.789,1) ROUND(456.789,-1)
----------------???????????? ????? ?----------------?????????????????? -----------------
????????? 457 ? ?????????????????????????? 456.8???????????????????? ?????? 460
TRUNC(列名|表達(dá)式,n)
截取到小數(shù)點(diǎn)后的n位
SQL> select trunc(456.789,0),trunc(456.789,1),trunc(456.789,-1) from dual;
TRUNC(456.789,0) TRUNC(456.789,1) TRUNC(456.789,-1)
?????----------------?????????????? ?----------------??????????????-----------------
??????????456 ? ??????????????????????? ?456.7 ????????????????????????? 450
MOD(m,n)
求m除以n的余數(shù)
SQL> select mod(10,3) from dual;
MOD(10,3)
? ----------
????? ?1
SQL> select mod(3,10) from dual;
MOD(3,10)
?? ----------
?????? 3
CEIL 取整 向上補(bǔ)1? 和trunc相反
SQL> select ceil(456.001)from dual;
CEIL(456.001)
??? -------------
?????????457
power(底數(shù),指數(shù)) 求次方
SQL> select power(10,3) from dual;
POWER(10,3)
?? -----------
????? 1000
greatest(expr1,expr2.......) 求出所列出的表達(dá)式或者值中的最大值
SQL> select greatest(1,2,3,4)from dual;
GREATEST(1,2,3,4)
????? -----------------
????????????? 4
least(expr1,expr2.......) 求出所列出的表達(dá)式或者值中的最小值
SQL> select least(1,2,3,4)from dual;
LEAST(1,2,3,4)
??? --------------
?????????? 1
3.日期類型的函數(shù)
修改當(dāng)前會(huì)話的日期顯示格式
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
使用函數(shù)查看系統(tǒng)當(dāng)前時(shí)間，這個(gè)時(shí)間來自于操作系統(tǒng)，數(shù)據(jù)庫里面是不存儲(chǔ)當(dāng)前時(shí)間的
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-05-20 13:35:57
日期的運(yùn)算
默認(rèn)情況下，參與運(yùn)算的單位是‘天’
SQL> select sysdate-10 from dual;
SYSDATE-10
-------------------
2016-05-10 13:39:09
如果想讓小時(shí)參與運(yùn)算，可以處以24
SQL> select sysdate-10/24 from dual;
SYSDATE-10/24
-------------------
2016-05-20 03:48:42
以此類推，分鐘，秒都可以這樣計(jì)算
與日期有關(guān)的函數(shù)
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
ROUND 和 TRUNC 對(duì)日期的取舍
MONTHS_BEWTEEN(日期1,日期2)
計(jì)算兩個(gè)日期間所相差的月份
日期1大于日期2返回正數(shù)，日期1小于日期2返回負(fù)數(shù)
SQL> select months_between('2016-07-21','2016-01-01') from dual;
MONTHS_BETWEEN('2016-07-21','2016-01-01')
?????? -----------------------------------------
??????????????? ? 6.64516129
ADD_MONTHS(日期,n)
把n個(gè)月加到日期上
SQL> select add_months(sysdate,5)from dual;
ADD_MONTHS(SYSDATE,
????? ? -------------------
?????2016-10-20 13:58:57
NEXT_DAY(日期,星期)
從給定日期算起，下一個(gè)指定的星期幾是幾號(hào)
SQL> select next_day(sysdate,'MONDAY')FROM DUAL;
NEXT_DAY(SYSDATE,'M
-------------------
2016-05-23 14:01:19
SQL> select next_day('2016-05-12','MONDAY')FROM DUAL;
NEXT_DAY('2016-05-1
-------------------
2016-05-16 00:00:00
LAST_DAY(日期)
返回該日期的所在月的最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-------------------
2016-05-31 14:03:17
我們修改一下日期顯示的格式
ROUND(date,'[day|month|year]') 和 trunc(date,'[day|month|year]') 應(yīng)用于日期型數(shù)據(jù)
數(shù)字的進(jìn)位和截取是以小數(shù)點(diǎn)為中心,而日期的進(jìn)位和截取是以年月日時(shí)分秒為中心
round 截取月份以16號(hào)為分割，16號(hào)及其以后的日期都入到下一個(gè)月
16號(hào)之前的日期都返回月初的日期
SQL> select round(to_date('2016-05-20'),'month')from dual;
ROUND(TO_DATE('2016
-------------------
2016-06-01 00:00:00
年的四舍五入要所輸入的月份，六月及其以前的日期，都返回年初，六月之后的日期返回下一年年初
SQL> select round(to_date('2016-06-16'),'year')from dual;
ROUND(TO_DATE('2016
-------------------
2016-01-01 00:00:00
以周三中午12點(diǎn)為分界線，周三之前返回本周日的0:00,周三之后的日期，返回下周日的0:00
西方以周日為一周的開始
SQL> select round(to_date('2016-07-20 11:01:01'),'day') from dual;
ROUND(TO_DATE('2016
-------------------
2016-07-17 00:00:00
SQL> select round(to_date('2016-07-21 11:01:01'),'day') from dual;
ROUND(TO_DATE('2016
-------------------
2016-07-24 00:00:00
4.類型轉(zhuǎn)換函數(shù)
不同數(shù)據(jù)類型之間的轉(zhuǎn)換
在賦值語句中數(shù)據(jù)類型會(huì)隱式轉(zhuǎn)換,隱式轉(zhuǎn)換應(yīng)當(dāng)盡量避免,因?yàn)閷懗鰜淼腟QL難理解,隨著時(shí)間流逝自己都很難理解
特別是嵌入到大型程序中時(shí),另外,oracle升級(jí)會(huì)修改隱式轉(zhuǎn)換的規(guī)則,這會(huì)使程序移植遇到問題.
尤其是索引列不能使用隱式轉(zhuǎn)換 那樣就不走索引了
賦值語句中oracle的自動(dòng)轉(zhuǎn)換規(guī)則:
1.將變長(zhǎng)字符型(varchar2)或定長(zhǎng)字符型(char)轉(zhuǎn)換成數(shù)值型(number)
2.將變長(zhǎng)字符型(varchar2)或定長(zhǎng)字符型(char)轉(zhuǎn)換成日期型(date)
3.將數(shù)值型(number)轉(zhuǎn)換成變長(zhǎng)字符型(varchar2)
4.將日期型(date)轉(zhuǎn)換成變長(zhǎng)字符型(varchar2)
表達(dá)式中oracle的自動(dòng)轉(zhuǎn)換規(guī)則:
1.將變長(zhǎng)字符型(varchar2)或定長(zhǎng)字符型(char)轉(zhuǎn)換成數(shù)值型(number)
2.將變長(zhǎng)字符型(varchar2)或定長(zhǎng)字符型(char)轉(zhuǎn)換成日期型(date)
轉(zhuǎn)換成功的條件:
在將字符型數(shù)據(jù)轉(zhuǎn)換成數(shù)字型時(shí),要保證字符型數(shù)據(jù)為有效的數(shù).
在將字符型數(shù)據(jù)轉(zhuǎn)換成日期型時(shí),要保證字符型數(shù)據(jù)為有效的日期.
SQL> select ename,sal from emp where sal>'4000';
ENAME ? SAL
---------- ----------
KING 5000
以上例子中oracle自動(dòng)將字符型的數(shù)據(jù)轉(zhuǎn)換成了數(shù)值型
SQL> select ename,hiredate from emp where hiredate='1981-11-17';
ENAME ? HIREDATE
---------- -------------------
KING ? 1981-11-17 00:00:00
以上例子中oracle自動(dòng)將自負(fù)類型轉(zhuǎn)換為了日期類型
注意：不能直接轉(zhuǎn)換的情況
數(shù)值型和日期型之間不能直接轉(zhuǎn)換,必須將其中之一先轉(zhuǎn)換為字符型,之后再轉(zhuǎn)換為另一種類型
顯示轉(zhuǎn)換函數(shù):
to_char
to_number
to_date
to_char? 兩種情況：
1.to_char(數(shù)字) 目的是將數(shù)字格式化貨幣顯示格式
2.to_char(日期) 目的是將日期格式化需要的格式
避免隱式轉(zhuǎn)換
to_number 不常用，因?yàn)閿?shù)值可以直接輸入
to_date? 很常用，因?yàn)槿掌谑遣豢梢暂斎?只能轉(zhuǎn)
to_char(日期,'fmt')
將日期型數(shù)據(jù)轉(zhuǎn)換成字變長(zhǎng)字符串.fmt為日期格式
日期格式必須用單引號(hào)擴(kuò)起來
常用的時(shí)間格式:
YYYY? 數(shù)字年???????? YEAR? 英文年
MM? ? 數(shù)字月??????????? MONTH 英文月
DY? ? 縮寫的星期?????? DAY? 完整的星期
DD? ? 數(shù)字日期????????????hh? ? 12小時(shí)
HH24? 24小時(shí)???????????? MI? ? 分鐘
SS? ? 秒
SQL> select ename,
to_char(hiredate,'yyyy-mm-dd year month day dy') hiredate
from emp where ename='SCOTT';
ENAME ? HIREDATE
---------- --------------------------------------------------------------------------------
SCOTT ? 1987-04-19 nineteen eighty-seven april? ? sunday sun
以上例子中scott的入職日期被以各種維度顯示出來了
to_char(數(shù)字,'fmt')
將數(shù)字轉(zhuǎn)換成變長(zhǎng)字符串.fmt為數(shù)字格式
9 一個(gè)數(shù)字? 給定的9個(gè)數(shù)不足 會(huì)顯示#
0 顯示前導(dǎo)0
$ 美元符號(hào)
L 本地貨幣符號(hào)
. 小數(shù)點(diǎn)
, 千位符
SQL> select to_char(sal,'L99999')from emp where ename='SCOTT';
TO_CHAR(SAL,'L99999')
--------------------------------
￥3000
SQL> select to_char(sal,'L99999','nls_currency=rmb')from emp where ename='SCOTT';
TO_CHAR(SAL,'L99999','NLS_CURREN
--------------------------------
rmb3000
to_char 還可以將10進(jìn)制數(shù)轉(zhuǎn)換成16進(jìn)制數(shù)
SQL> select to_char(97,'xxxx') from dual;
TO_CHAR(97
----------
61
to_number('字符串','數(shù)字格式')
這個(gè)數(shù)字格式體現(xiàn)的是前面字符串的格式
字符串一定要符合數(shù)字的格式
SQL> select to_number('$123.456','$99999.999')from dual;
TO_NUMBER('$123.456','$99999.999')
----------------------------------
123.456
to_number 也可以將16進(jìn)制轉(zhuǎn)換成10進(jìn)制
SQL> select to_number('a','xxxxx')from dual;
TO_NUMBER('A','XXXXX')
----------------------
10
SQL> select to_number(61,'xxxxx')from dual;
TO_NUMBER(61,'XXXXX')
---------------------
97
TO_DATE('字符串','日期格式')
字符串一定要符合日期格式
SQL> select to_date('20-MAY-16','DD-MON-RR')from dual;
TO_DATE('20-MAY-16'
-------------------
2016-05-20 00:00:00
簡(jiǎn)化輸入的操作，必須使用yyyy-mm-dd格式，并且只能精確到天
SQL> select date'2016-07-22' from dual;
DATE'2016-07-22'
-------------------
2016-07-22 00:00:00
5.NULL值處理函數(shù)
null值
是一個(gè)很特別的值,既不是0也不是空格.它的值是沒有定義,不確定的未知值
比如一個(gè)案件的追蹤表,警方在對(duì)犯罪分子一無所知,但在犯罪分子性別一欄不是男就是女,
只是此時(shí)還不確定
就可以把性別欄設(shè)置為未知,當(dāng)案件偵破到一定程度,警方知道了犯罪分子的性別,
既從未知變成了已知.也就是由NULL變成男或女
SQL> select empno,ename,sal,comm from emp where ename in('SCOTT','WARD');
EMPNO ENAME ? ? SAL? ? ? COMM
---------- ---------- ---------- ----------
7521 WARD ? ? 1250 500
7788 SCOTT ? ? 3000
以上輸出內(nèi)容的獎(jiǎng)金一列存在空值，如果我們統(tǒng)計(jì)總收入的話，那么就會(huì)出現(xiàn)下面的情況
獎(jiǎng)金為null的員工最終的工資還是null，這是不合理的
SQL> select empno,ename,sal,comm,sal+comm total_sal from emp where ename in('SCOTT','WARD');
EMPNO ENAME ? ? SAL? ? ? COMM? TOTAL_SAL
---------- ---------- ---------- ---------- ----------
7521 WARD ? ? 1250 500 ? 1750
7788 SCOTT ? ? 3000
SQL> select ename,sal from emp where comm=null;
no rows selected
想要找到獎(jiǎng)金為null的員工信息，卻沒有任何結(jié)果
這是由null的特殊性決定的，null不參與運(yùn)算，因此只能用is null來描述它
SQL> select ename,sal from emp where comm is null;
ENAME ? SAL
---------- ----------
SMITH ? 800
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
ADAMS 1100
JAMES ? 950
FORD 3000
MILLER 1300
SQL> select ename,sal,comm from emp where comm is not null;
ENAME ? SAL ? ? COMM
---------- ---------- ----------
ALLEN 1600 ? ? 300
WARD 1250 ? ? 500
MARTIN 1250 ? ? 1400
TURNER 1500 ? ? ? 0
要使null能夠參與運(yùn)算，需要用函數(shù)來將null值進(jìn)行轉(zhuǎn)化，相關(guān)函數(shù)有
NVL
NVL2
NULLIF
COALESCE
NVL(表達(dá)式1,表達(dá)式2)
如果表達(dá)式1是NULL,則返回表達(dá)式2
如果表達(dá)式1非NULL,則返回表達(dá)式1
表達(dá)式1和表達(dá)式2可以是數(shù)字,字符串,日期格式,1和2的數(shù)據(jù)類型必須一致
SQL> select ename,sal,comm,sal+nvl(comm,0)from emp where ename in('SCOTT','WARD');
ENAME ? SAL ? ? COMM SAL+NVL(COMM,0)
---------- ---------- ---------- ---------------
WARD 1250 ? ? 500 ? ? 1750
SCOTT 3000 ? ? 3000
NVL2(expr1,expr2,expr3)
如果expr1為空,則返回expr3,否則返回expr2
SQL> select ename,sal,comm,sal+nvl2(comm,comm,0)from emp where ename in('SCOTT','WARD');
ENAME ? SAL ? ? COMM SAL+NVL2(COMM,COMM,0)
---------- ---------- ---------- ---------------------
WARD 1250 ? ? 500 ? 1750
SCOTT 3000 ? 3000
NULLIF(expr1,expr2)
如果expr1和expr2相同,則返回空,否則返回expr1
SQL> select empno,ename,NULLIF(ename,'SCOTT')FROM EMP where ename in ('SCOTT','KING');
EMPNO ENAME? ? ? NULLIF(ENA
---------- ---------- ----------
7788 SCOTT
7839 KING? ? ? KING
空值的排序 升序會(huì)排在最后 降序排在最前
SQL> select empno,ename,sal,comm from emp where ename in('SCOTT','WARD') ORDER BY COMM;
EMPNO ENAME ? ? SAL? ? ? COMM
---------- ---------- ---------- ----------
7521 WARD ? ? 1250 500
7788 SCOTT ? ? 3000
SQL> select empno,ename,sal,comm from emp where ename in('SCOTT','WARD') ORDER BY COMM DESC;
EMPNO ENAME ? ? SAL? ? ? COMM
---------- ---------- ---------- ----------
7788 SCOTT ? ? 3000
7521 WARD ? ? 1250 500
COALESCE(expr1,expr2,expr3,...)
返回第一個(gè)非空的表達(dá)式
SQL> select coalesce('','','a','','','b','','')from dual;
C
-
a
SQL> select coalesce('','','','','','b','','')from dual;
C
-
b
emp表中有獎(jiǎng)金的展示獎(jiǎng)金，沒有獎(jiǎng)金的展示工資
6.分支函數(shù)
分支函數(shù)
decode函數(shù)
decode(expr,search1,result1,search2,result2......,default)
這個(gè)函數(shù)是返回一個(gè)表達(dá)式在各種可能取值的情況下的值
例如，expr滿足search1,則返回result1，滿足search2則返回result2，以此類推，
最終如果沒有滿足任何比對(duì)條件，則返回default值
SQL> select empno,ename,deptno,decode(deptno,10,'AAA',20,'BBB','CCC') FROM EMP;
EMPNO ENAME ? DEPTNO DEC
---------- ---------- ---------- ---
7369 SMITH ? ? ? 20 BBB
7499 ALLEN ? ? ? 30 CCC
7521 WARD ? ? ? 30 CCC
7566 JONES ? ? ? 20 BBB
7654 MARTIN ? ? ? 30 CCC
7698 BLAKE ? ? ? 30 CCC
7782 CLARK ? ? ? 10 AAA
7788 SCOTT ? ? ? 20 BBB
7839 KING ? ? ? 10 AAA
7844 TURNER ? ? ? 30 CCC
7876 ADAMS ? ? ? 20 BBB
7900 JAMES ? ? ? 30 CCC
7902 FORD ? ? ? 20 BBB
7934 MILLER ? ? ? 10 AAA
練習(xí):
按部分編號(hào)漲工資(只打印) 10號(hào)部門漲10% 20號(hào)部分漲20% 其他部分漲30%
SQL> select ename,sal deptno,case when deptno=10 then sal+sal*0.1
2? when deptno=20 then sal+sal*0.2
3? else sal+sal*0.3 end up_sal from emp;
case when 子句
case when 子句分為簡(jiǎn)單和搜索兩種
簡(jiǎn)單case when子句的語法：
case expr when comparation_expr then return_expr...... else else_expr end
簡(jiǎn)單的case when子句可以實(shí)現(xiàn)等值比較，與decode一樣
SQL> select ename,sal,case deptno when 10 then 'AAA' when 20 then 'BBB' else 'CCC' end from emp;
ENAME ? SAL CAS
---------- ---------- ---
SMITH ? 800 BBB
ALLEN 1600 CCC
WARD 1250 CCC
JONES 2975 BBB
MARTIN 1250 CCC
BLAKE 2850 CCC
CLARK 2450 AAA
SCOTT 3000 BBB
KING 5000 AAA
TURNER 1500 CCC
ADAMS 1100 BBB
JAMES ? 950 CCC
FORD 3000 BBB
MILLER 1300 AAA
14 rows selected.
搜索case when可以實(shí)現(xiàn)不等值的比較
case? when condation then return_expr...... else else_expr end
SQL> select ename,sal,case
when sal<1000 then sal+1
when sal>=1000 and sal<2000 then sal+2
when sal>=2000 then sal+3
else sal+4 end up_sal
from emp
ENAME ? SAL ? UP_SAL
---------- ---------- ----------
SMITH ? 800 ? ? 801
ALLEN 1600 ? ? 1602
WARD 1250 ? ? 1252
JONES 2975 ? ? 2978
MARTIN 1250 ? ? 1252
BLAKE 2850 ? ? 2853
CLARK 2450 ? ? 2453
SCOTT 3000 ? ? 3003
KING 5000 ? ? 5003
TURNER 1500 ? ? 1502
ADAMS 1100 ? ? 1102
JAMES ? 950 ? ? 951
FORD 3000 ? ? 3003
MILLER 1300 ? ? 1302
14 rows selected.
7.聚集函數(shù)
多行函數(shù) 聚集函數(shù)
常用聚集函數(shù)
是對(duì)一組或一批數(shù)據(jù)進(jìn)行綜合操作后返回一個(gè)結(jié)果
count 行總數(shù)
avg ? ? ? ? 平均數(shù)
sum 列值的和
max 最大值
min ? ? ? ? 最小值
count([{distinct|all} '列名'|*) 為列值時(shí)空不在統(tǒng)計(jì)之內(nèi)，為*時(shí)包含空行和重復(fù)行
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL>
SQL>
SQL> select count(comm)from emp;
COUNT(COMM)
-----------
4
SQL> select count(distinct deptno)from emp;
COUNT(DISTINCTDEPTNO)
---------------------
3
SQL> select count(deptno)from emp;
COUNT(DEPTNO)
-------------
14
SQL> select max(sal),avg(sal),min(sal),count(sal) from emp;
MAX(SAL)? AVG(SAL) MIN(SAL) COUNT(SAL)
---------- ---------- ---------- ----------
5000 2073.21429 ? ? 800 14
上面執(zhí)行的聚集函數(shù)都是對(duì)所有記錄統(tǒng)計(jì)，一次只返回一行記錄
如果想分組統(tǒng)計(jì)(比如統(tǒng)計(jì)部門的平均值)需要使用group by
為了限制分組統(tǒng)計(jì)的結(jié)果需要使用having過濾
GROUP BY 分組統(tǒng)計(jì)? 9I要排序 10G不排序
求出每個(gè)部門的平均工資
SQL> select deptno,avg(sal) from emp group by deptno;
DEPTNO? AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
還可以按照給定字段進(jìn)行排序
SQL> select deptno,avg(sal) from emp group by deptno order by deptno;
DEPTNO? AVG(SAL)
---------- ----------
10 2916.66667
20 2175
30 1566.66667
分組排序可以使用未被選擇的列
SQL> select avg(sal) from emp group by deptno order by deptno;
AVG(SAL)
----------
2916.66667
2175
1566.66667
如果在查詢中使用了分組函數(shù),任何不在分組函數(shù)中的列或表達(dá)式必須在group by子句中
SQL> select deptno,avg(sal) from emp;
select deptno,avg(sal) from emp
*
ERROR at line 1:
ORA-00937: not a single-group group function
group by 的過濾
group by的過濾使用having子句，放在group by 子句的后面，
和where子句不同，having子句是在分組計(jì)算完成后進(jìn)行的過濾，而where
子句是在分組計(jì)算前做的過濾,where 條件里面只能出現(xiàn)單行處理函數(shù)，而having
子句可以出現(xiàn)聚集函數(shù)
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
DEPTNO? AVG(SAL)
---------- ----------
20 2175
10 2916.66667
分組函數(shù)的注意事項(xiàng):
1.分組函數(shù)只能出現(xiàn)在選擇列,order by,having子句中
2.分組函數(shù)會(huì)忽略NULL 除了count(*)
3.分組函數(shù)中可以使用ALL或distinct;ALL是默認(rèn)值,統(tǒng)計(jì)所有.加上distinct則只統(tǒng)計(jì)不同
4.如果選擇的列里有普通列,表達(dá)式和分組列 那么普通列和表達(dá)式都必須出現(xiàn)在group by中
行轉(zhuǎn)列
create table t4(id int,name varchar2(10),subject varchar2(20),grade number);
insert into t4 values(1,'ZORRO','語文',70);
insert into t4 values(2,'ZORRO','數(shù)學(xué)',80);
insert into t4 values(3,'ZORRO','英語',75);
insert into t4 values(4,'SEKER','語文',65);
insert into t4 values(5,'SEKER','數(shù)學(xué)',75);
insert into t4 values(6,'SEKER','英語',60);
insert into t4 values(7,'BLUES','語文',60);
insert into t4 values(8,'BLUES','數(shù)學(xué)',90);
insert into t4 values(9,'PG','數(shù)學(xué)',80);
insert into t4 values(10,'PG','英語',90);
commit;
SQL> select * from t4;
ID NAME? ? ? SUBOBJECT GRADE
---------- ---------- -------------------- ----------
1 ZORRO? ? ? 語文? ? ? ? ? ? ? ? ? ? ? ? 70
2 ZORRO? ? ? 數(shù)學(xué)? ? ? ? ? ? ? ? ? ? ? ? 80
3 ZORRO? ? ? 英語? ? ? ? ? ? ? ? ? ? ? ? 75
4 SEKER? ? ? 語文? ? ? ? ? ? ? ? ? ? ? ? 65
5 SEKER? ? ? 數(shù)學(xué)? ? ? ? ? ? ? ? ? ? ? ? 75
6 SEKER? ? ? 英語? ? ? ? ? ? ? ? ? ? ? ? 60
7 BLUES? ? ? 語文? ? ? ? ? ? ? ? ? ? ? ? 60
8 BLUES? ? ? 數(shù)學(xué)? ? ? ? ? ? ? ? ? ? ? ? 90
9 PG? ? ? ? 數(shù)學(xué)? ? ? ? ? ? ? ? ? ? ? ? 80
10 PG? ? ? ? 英語? ? ? ? ? ? ? ? ? ? ? ? 90
10 rows selected.
SQL> select name,sum(case when SUBJECT='語文' then GRADE else 0 end) "語文",sum(case when SUBJECT='數(shù)學(xué)' then GRADE else 0 end) "數(shù)學(xué)",sum(case when SUBJECT='英語' then GRADE else 0 end) "英語" from t5 group by name;
NAME? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 語文? ? ? 數(shù)學(xué)? ? ? 英語
-------------------------------------------------- ---------- ---------- ----------
SEKER? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 65? ? ? ? 75? ? ? ? 60
BLUES? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?60? ? ? ? 90? ? ? ? ? 0
PG? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?????? 0? ? ? ? 80? ? ? ? 80
ZORRO? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 70? ? ? ? 80? ? ? ? 75
