1.簡介
數(shù)據(jù)存儲有哪些方式？電子表格普办，紙質(zhì)文件工扎，數(shù)據(jù)庫。
那么究竟什么是關(guān)系型數(shù)據(jù)庫衔蹲？
目前對數(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表格就是二維表，由行和列兩個維度所組成巨税，想想excel表格蟋定，你就會知道數(shù)據(jù)庫里面的表是什么樣子的，道理是一模一樣的草添。
而非關(guān)系型數(shù)據(jù)庫的數(shù)據(jù)組織方式就五花八門了驶兜，有星型的，網(wǎng)狀的等等果元，他們都統(tǒng)稱為非關(guān)系型數(shù)據(jù)庫促王，也叫nosql犀盟。
關(guān)系型數(shù)據(jù)庫需要一組操作符而晒，實際上就是需要一套命令，或者說是語言阅畴，也就是我們現(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）整個學(xué)習(xí)SQL過程是使用SCOTT用戶的表來學(xué)習(xí)的 默認(rèn)scott是被鎖定的 解鎖的方法
原始狀態(tài)下监署，這個用戶是被鎖定的，我們需要解鎖這個用戶
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í)行完腳本 會自動退出 重新登錄 再對scott解鎖即可
（2）一個完整的SQL命令叫語句(statement),每個關(guān)鍵字和后面跟著的選項叫子句(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)容串行 是因為默認(rèn)的顯示的行長度是80字節(jié) 而選出的內(nèi)容超出了80字節(jié) 可以修正一下
SQL> set linesize 100
如果有多個列標(biāo)題 是一頁內(nèi)行數(shù)默認(rèn)14行 也可以修正
SQL> set pagesize 1000
這個修改只是內(nèi)存中的 可以將命令保存住到文件 實現(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? 員工工號 列為整數(shù),最大長度為4位.
?????????? ename? 員工名字 列為變長字符型,最大長度10個字符
?????????? job? ? 出任職位 列為變長字符型,最大長度9個字符
?????????? mgr? ? 所屬領(lǐng)導(dǎo)工號 列為整數(shù),最大長度為4位
????????????hiredate入職日期 列為日期類型
??????????? sal? ? 工資 列為浮點數(shù),最大長度為7位,其中包含2位小數(shù)
????????????comm? ? 獎金 列為浮點數(shù),最大長度為7為,其中包含2位小數(shù)
???????????? deptno? 部門號 列為整數(shù),最大長度為兩位
desc dept
dept表(部門表)的結(jié)構(gòu)介紹
? ? ? ? ? ? ? deptno? 部門號 列為整數(shù),最大長度為兩位
? ? ? ? ? ? ? dname? 部門名字 列為變長字符型,最大長度為14個字符
? ? ? ? ? ? ? loc? ? 部門所在地理位置 列為變長字符型,最大長度為13個字符
DESC SALGRADE
SALGRADE表(薪水等級表)的結(jié)構(gòu)介紹 三列都是數(shù)值型
? ? ? ? ? ? ? ? GRADE? 薪水等級
? ? ? ? ? ? ? ? LOSAL? 所在等級中薪水底線
? ? ? ? ? ? ? ? HISAL 所在等級中薪水上限
3.查詢表中指定的列
SQL>select ename,sal From emp;
select ename,sal from emp;
錯誤語法: select *,sal from emp; 星號不可以與單列同時存在
4.在sql中使用算術(shù)表達(dá)式
select ename,sal*12 from emp;
select ename,(500+sal)*12 from emp;
算術(shù)運算符優(yōu)先級:
先乘除后加減,同優(yōu)先級自左至右
小括號提高優(yōu)先級,多重括號則自內(nèi)而外
5.在查詢中為列命名別名
select ename,sal*12 as annual_salary from emp;
select ename,sal*12 annual_salary from emp;
6.在別名中使用特殊字符 要用雙引號
select ename,sal*12 "annual salary" from emp;
7.表別名 對emp表取了個簡單別名e? 這樣就可以在引用表名時簡化輸入
select ename,sal from emp e;
同時也解決了星號和列同時出現(xiàn)的語法錯誤 別名的本質(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ù)庫生效
修改會話參數(shù)
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
11. 虛表
虛表 oracle自動創(chuàng)建的一張表 可以理解為是一張假想表 目的是為了使select語義完成
借助虛表 我們可以完成很多事
查看當(dāng)前用戶
select user from dual;
查看當(dāng)前時間(OS時間)
select sysdate from dual;
計算
select 9+8 from dual;
獲取隨機數(shù)
select dbms_random.random from dual;
等等...以后還有很多查詢需要借助虛表完成.
12.過濾條件
select empno,ename,sal from emp where sal >= 1500;
WHERE是關(guān)鍵字 后面跟限制條件
條件由列名,字符串,算術(shù)表達(dá)式,常量和比較運算符組成;
比較運算符:
> >= < <= = <> !=
邏輯運算符:
and? or? not
oracle提供的比較運算符
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;
字符串比較 字符串要加單引號 默認(rèn)存儲模式就是大寫模式
SQL> select ename,sal from emp where ename='KING';
時間格式比較
日期區(qū)間表示法
先修改日期顯示為我們習(xí)慣的格式 否則寫系統(tǒng)默認(rèn)的時間格式 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運算表
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運算表
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提供的運算符
like???? between and???? in??? not like
is not null???? not between and
運算符的優(yōu)先級不用記憶 想提高優(yōu)先級加小括號即可
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 通配符: % 任意長度(包括零長度)通配符
_ 單個字符通配符
部門名像 SAL開頭的
select ename,job from emp where job like 'SAL%';
任意一個字符后跟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)義符號需要在子句中用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中使用列號 列號必須是select子句中已經(jīng)選擇的列的順序號
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ù)?
通俗的講 任何東西,只要它能接收輸入,對輸入進(jìn)行加工并產(chǎn)生輸出,它就可以被稱之為函數(shù)
例如:牛是函數(shù),它吃的是草(輸入),擠出的是奶(輸出)
函數(shù)是一種程序設(shè)計結(jié)構(gòu),它可以有一個或多個輸入,但只能有一個輸出.
函數(shù)只有一個出口,使用函數(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
但是求單引號的ASCII碼寫法很特殊 兩個單引帶表一個單引
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á)式)
每個詞的詞頭大寫 其他小寫
SCOTT>>select initcap('abc xyz qwe')from dual;
INITCAP('AB
-----------
Abc Xyz Qwe
CONCAT(列名|表達(dá)式,列名|表達(dá)式)
將第一個字符串和第二個字符串連接
SQL> select concat('abc','xyz') from dual;
CONCAT
------
abcxyz
該合數(shù)與||功能一樣春塌，但是我們常用的是后者
SQL> select 'abc'||'xyz' from dual;
'ABC'|
------
abcxyz
SUBSTR(列名|表達(dá)式,m,[n])
返回指定子串,該子串是從第m個字符開始,其長度為n,不指定n值則從m到最后
SQL> select substr('abcdefg',4,3)from dual;
SUB
---
def
SQL> select substr('abcdefg',4)from dual;
SUBS
----
defg
LENGTH(列名|表達(dá)式)
返回字符串的長度
SQL> select length('abcdef')from dual;
LENGTH('ABCDEF')
----------------
?????????????? 6
LENGTHB(列名|表達(dá)式)
返回字符串所占用的字節(jié)數(shù)
INSTR (列名|表達(dá)式,'字符串',[m],[n])
從表達(dá)式或列中搜索給定的字符串的所處位置,m代表從第幾個字符開始搜,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)上有時候有的字段會產(chǎn)生空格簇捍，這個會給應(yīng)用帶來很大的麻煩，用這個函數(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
左補齊lpad 右補齊rpad
將不足20個字符的位置用指定符號填充.
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ù)點后的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ù)點后的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 取整 向上補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)前會話的日期顯示格式
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
使用函數(shù)查看系統(tǒng)當(dāng)前時間锅必，這個時間來自于操作系統(tǒng)事格，數(shù)據(jù)庫里面是不存儲當(dāng)前時間的
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-05-20 13:35:57
日期的運算
默認(rèn)情況下，參與運算的單位是‘天’
SQL> select sysdate-10 from dual;
SYSDATE-10
-------------------
2016-05-10 13:39:09
如果想讓小時參與運算搞隐，可以處以24
SQL> select sysdate-10/24 from dual;
SYSDATE-10/24
-------------------
2016-05-20 03:48:42
以此類推驹愚，分鐘，秒都可以這樣計算
與日期有關(guān)的函數(shù)
MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
ROUND 和 TRUNC 對日期的取舍
MONTHS_BEWTEEN(日期1,日期2)
計算兩個日期間所相差的月份
日期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個月加到日期上
SQL> select add_months(sysdate,5)from dual;
ADD_MONTHS(SYSDATE,
????? ? -------------------
?????2016-10-20 13:58:57
NEXT_DAY(日期,星期)
從給定日期算起么鹤，下一個指定的星期幾是幾號
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ù)點為中心,而日期的進(jìn)位和截取是以年月日時分秒為中心
round 截取月份以16號為分割，16號及其以后的日期都入到下一個月
16號之前的日期都返回月初的日期
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點為分界線余佛，周三之前返回本周日的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ù)類型會隱式轉(zhuǎn)換,隱式轉(zhuǎn)換應(yīng)當(dāng)盡量避免,因為寫出來的SQL難理解,隨著時間流逝自己都很難理解
特別是嵌入到大型程序中時,另外,oracle升級會修改隱式轉(zhuǎn)換的規(guī)則,這會使程序移植遇到問題.
尤其是索引列不能使用隱式轉(zhuǎn)換 那樣就不走索引了
賦值語句中oracle的自動轉(zhuǎn)換規(guī)則:
1.將變長字符型(varchar2)或定長字符型(char)轉(zhuǎn)換成數(shù)值型(number)
2.將變長字符型(varchar2)或定長字符型(char)轉(zhuǎn)換成日期型(date)
3.將數(shù)值型(number)轉(zhuǎn)換成變長字符型(varchar2)
4.將日期型(date)轉(zhuǎn)換成變長字符型(varchar2)
表達(dá)式中oracle的自動轉(zhuǎn)換規(guī)則:
1.將變長字符型(varchar2)或定長字符型(char)轉(zhuǎn)換成數(shù)值型(number)
2.將變長字符型(varchar2)或定長字符型(char)轉(zhuǎn)換成日期型(date)
轉(zhuǎn)換成功的條件:
在將字符型數(shù)據(jù)轉(zhuǎn)換成數(shù)字型時,要保證字符型數(shù)據(jù)為有效的數(shù).
在將字符型數(shù)據(jù)轉(zhuǎn)換成日期型時,要保證字符型數(shù)據(jù)為有效的日期.
SQL> select ename,sal from emp where sal>'4000';
ENAME ? SAL
---------- ----------
KING 5000
以上例子中oracle自動將字符型的數(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自動將自負(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 不常用，因為數(shù)值可以直接輸入
to_date? 很常用辉巡，因為日期是不可以輸入 只能轉(zhuǎn)
to_char(日期,'fmt')
將日期型數(shù)據(jù)轉(zhuǎn)換成字變長字符串.fmt為日期格式
日期格式必須用單引號擴起來
常用的時間格式:
YYYY? 數(shù)字年???????? YEAR? 英文年
MM? ? 數(shù)字月??????????? MONTH 英文月
DY? ? 縮寫的星期?????? DAY? 完整的星期
DD? ? 數(shù)字日期????????????hh? ? 12小時
HH24? 24小時???????????? 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)換成變長字符串.fmt為數(shù)字格式
9 一個數(shù)字? 給定的9個數(shù)不足 會顯示#
0 顯示前導(dǎo)0
$ 美元符號
L 本地貨幣符號
. 小數(shù)點
, 千位符
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ù)字格式')
這個數(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
簡化輸入的操作恨憎，必須使用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值
是一個很特別的值,既不是0也不是空格.它的值是沒有定義,不確定的未知值
比如一個案件的追蹤表,警方在對犯罪分子一無所知,但在犯罪分子性別一欄不是男就是女,
只是此時還不確定
就可以把性別欄設(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)容的獎金一列存在空值郊楣，如果我們統(tǒng)計總收入的話憔恳，那么就會出現(xiàn)下面的情況
獎金為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
想要找到獎金為null的員工信息净蚤，卻沒有任何結(jié)果
這是由null的特殊性決定的钥组，null不參與運算，因此只能用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能夠參與運算今瀑，需要用函數(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
空值的排序 升序會排在最后 降序排在最前
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,...)
返回第一個非空的表達(dá)式
SQL> select coalesce('','','a','','','b','','')from dual;
C
-
a
SQL> select coalesce('','','','','','b','','')from dual;
C
-
b
emp表中有獎金的展示獎金，沒有獎金的展示工資
6.分支函數(shù)
分支函數(shù)
decode函數(shù)
decode(expr,search1,result1,search2,result2......,default)
這個函數(shù)是返回一個表達(dá)式在各種可能取值的情況下的值
例如橘荠，expr滿足search1,則返回result1屿附，滿足search2則返回result2，以此類推哥童，
最終如果沒有滿足任何比對條件挺份，則返回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í):
按部分編號漲工資(只打印) 10號部門漲10% 20號部分漲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 子句分為簡單和搜索兩種
簡單case when子句的語法：
case expr when comparation_expr then return_expr...... else else_expr end
簡單的case when子句可以實現(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可以實現(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ù)
是對一組或一批數(shù)據(jù)進(jìn)行綜合操作后返回一個結(jié)果
count 行總數(shù)
avg ? ? ? ? 平均數(shù)
sum 列值的和
max 最大值
min ? ? ? ? 最小值
count([{distinct|all} '列名'|*) 為列值時空不在統(tǒng)計之內(nèi)贮懈，為*時包含空行和重復(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ù)都是對所有記錄統(tǒng)計匀泊，一次只返回一行記錄
如果想分組統(tǒng)計(比如統(tǒng)計部門的平均值)需要使用group by
為了限制分組統(tǒng)計的結(jié)果需要使用having過濾
GROUP BY 分組統(tǒng)計? 9I要排序 10G不排序
求出每個部門的平均工資
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ìn)行的過濾型宙，而where
子句是在分組計算前做的過濾,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ù)的注意事項:
1.分組函數(shù)只能出現(xiàn)在選擇列,order by,having子句中
2.分組函數(shù)會忽略NULL 除了count(*)
3.分組函數(shù)中可以使用ALL或distinct;ALL是默認(rèn)值,統(tǒng)計所有.加上distinct則只統(tǒng)計不同
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
