oracle（sql）基礎篇系列（一）——基礎select語句、常用sql函數、組函數、分組函數，oracle基礎篇

oracle（sql）基礎篇系列（一）——基礎select語句、常用sql函數、組函數、分組函數，oracle基礎篇

 

 

花點時間整理下sql基礎，溫故而知新，也方便復習查看。文章的demo來自oracle自帶的dept,emp,salgrade三張表。解鎖scott用戶，使用scott用戶登錄就可以看到自帶的表。

#使用oracle用戶登錄linux

[oracle@localhost ~]$ sqlplus / as sysdba;

......

SQL> alter user scott account unlock；

四大語句

DQL語句——select

DML語句——insert,upate,delete等（關鍵字後帶表名）

DDL語句——create,alter,drop等（關鍵字後帶table關鍵字再跟表名）

事務控制語句——commit,rollback

基礎select語句

簡單select語句

select * from emp;

 

包含算術表達式的語句

--查詢出來的sal乘以5

select ename , sal*5 from emp;

 

--表中取出2*3的結果

select 2*3 from emp;在emp;

 

--當我們需要顯示一個結果的時候就用系統提供的dual虛表

select 2*3 form dual;

 

獲取系統當前的時間

--sysdate在Oracle中表示當前系統時間

select sysdate from dual;

含有別名的語句

--顯示出來的是salary字段名而不是sal

select ename, sal*12 salary from emp;

 

含有空值

空值表達式

--含有任何null值的數學表達式最後的結果都為null

select ename, sal*5+comm from emp;

空字符串（字符串、字符串連接符）

--含有任何null值的字符串表達式中，null被當作空字符串處理

select empno, ename || 'abc' || mgr from emp;

 

--字符串連接符：||。select語句中用單引號表示字符串

 

distinct消除重復

--用distinct消除結果集中deptno相同的記錄

select distinct deptno from emp;

 

--消除結果集中job和deptno都相同的記錄

select distinct job, deptno from emp;

 

where 條件過濾

等值判斷

select * from emp where empno = 7369;

 

非等值判斷

（1）>,<,>=, <= ,<>

 

--取出所有部門號不是10的雇員的名字和薪水

select ename, sal from emp where deptno <> 10;

 

（2）between..and..

 

--包含最大值和最小值

select ename, sal from emp where sal between 800 and 1500;

 

（3）字符串比較

比較字符串ASCII碼值的比較，先比較第一字母，依次比較每個字母

 

select ename, sal from emp where ename > ‘DBA’;

 

空值處理

--為空

select ename from emp where comm is null;

 

--不為空

select ename from emp where comm is not null;

in語句

--在選定范圍

select ename, sal from emp where sal in (800, 1250, 1500, 2000);

 

--不在選定范圍

select ename, sal from emp where sal not in  (800, 1500, 2000);

日期處理

--Oracle默認的日期格式為：DD-MON-RR

select ename, hiredate from emp where hiredate > ’20-2月-1981’;

 

--to_char()，把日期類型字段轉為指定格式的字符串

--輸出2008-04-04

select to_char(sysdate, ‘YYYY-MM-DD-HH24:MI:SS’) from dual;

 

--to_date()，將日期字符串轉為指定格式的日期

select * from emp where hiredate > to_date(‘1981-03-02’, ‘YYYY-MM-DD’);

and,or,not語句

--and

select deptno, ename, sal from emp where deptno = 10 and sal > 1000;

 

--or

select deptno, ename, sal from emp where deptno = 10 or sal > 1000;

 

--not

select ename, sal from emp where sal not in  (800, 1500, 2000);

like模糊查詢（通配符匹配、單字符匹配、轉義字符）

（1）通配符%，代表0個或者多個字符

 

select ename from emp where ename like ‘%ALL%’; 

 

（2）單個字符-，代表一個字符

 

select ename from emp where ename like ‘_A%’;

 

（3）轉義字符\，統默認的轉義字符是 ‘\’

 

--中間的%不表示通配符，而是表示姓名包含%

select ename from emp where ename like ‘%\%%’；

 

order by排序

asc升序

select ename, sal from emp order by ename asc;

desc降序

select ename, deptno from emp order by ename desc;

 

常用sql函數

lower()——將字符串全部轉成小寫

select lower(ename),sal from emp where lower(ename) like '%a%';

upper()——將字符串全部轉成大寫

select upper(ename),sal from emp where upper(ename) like '%a%';

substr()——截取字符串

--從第2個字符開始截取ename

select substr(ename, 2) from emp;

 

--substr('hello',1,3)，輸出hel，第三個參數可以省略

select substr('hello', 1,3) from dual;

 

 

 

 

chr()——求一個與某個ASCII碼值對應的字符

select chr(65) from dual;

ascii()——求一個字符的ASCII碼值

select ascii(‘A’) from dual;

round()——四捨五入

對參數值進行四捨五入，可以指定四捨五入到小數點後幾位，可以用負數指定小數點前面幾位

--輸出24

select round(23.652) from dual;

 

--輸出23.65

select round(23.652，2) from dual;

 

--輸出20，-1表示對個位數進行四捨五入

select round(23.652，-1) from dual;

 

--輸出0，-2表示對十位數進行四捨五入

select round(23.652，-2) from dual;

to_char()——日期類型轉字符串

把日期類型字段轉為指定格式的字符串，可以精確到時分秒

--輸出2008-04-04

select to_char(sysdate, ‘YYYY-MM-DD’) from dual;

 

--2008-04-04 01:10:21

select to_char(sysdate,'YYYY-MM-DD hh:mm:ss') from dual;

to_date()——字符串轉為日期類型

將日期字符串轉為指定格式的日期

select * from emp where hiredate > to_date(‘1981-03-02’, ‘YYYY-MM-DD’);

 

to_number()——字符串轉數字

將指定的字符串轉換成數字格式,第一個參數為要轉為數字的字符串（一定要確保所轉換字段是可轉換為數字的，否則報“invalid number”），第二個參數為指定的格式

select * from emp where sal > to_number(‘$1,200.00’, ‘$9,999.99’);

nvl()——空值處理

這個函數有兩個參數：

第一參數：字段名或表達式，如果這個參數值為null，就返回第二參數值，否則返回第一參數值。

select ename, sal*12+nvl(comm, 0) from emp;

 

組函數

min()——求最小值

max()——求最大值

avg()——求平均值

sum()——求總和

select sum(sal), avg(sal), max(sal), min(sal) from emp;

count()——求數量

--求記錄數量

select count(*) from emp;

 

--求comm字段中所有非空記錄的數量

select count(comm) from emp;

 

--和distinct一起使用，得到所有唯一值記錄的數量

select count(distinct deptno) from emp;

 

分組函數

對組函數的查詢結果進行分組

group by

使用group by 的規律：出現在select列表中的字段，如果沒有出現在組函數中，則必須出現在group by子句中。

select deptno, avg(sal) from emp group by deptno;

 

--典型錯誤，group by缺少ename

select ename, deptno, max(sal) from emp group by deptno;

 

--求出每個部門中最賺錢的那個人的名字

select ename, deptno, sal from emp where sal in (

select sal from (

select deptno, max(sal) sal from emp group by deptno

))

having

 

having語句的作用是對分組之後的數據進行過濾數據，where 語句的作用是分組之前過濾數據。

--注意不能使用where

select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;

 

綜合練習

從emp這張表裡把平均工資和部門編號列出來，並且過濾掉大寫是A的名字 把剩下的數據按照部門編號進行分組，分組之後的平均薪水必須大於2000，按照部門編號的倒序排列。

分析：分組，過濾，排序；過濾又包含分組前過濾(where)和分組後過濾(having)。編寫順序：分組前過濾(where) > 分組（group by） > 分組後過濾(having) > 排序（order by）

select avg(sal),deptno,ename from emp  where ename not like '%A%' group by deptno,ename having avg(sal) > 2000 order by deptno desc;

 

本文就到此，下一篇我們繼續整理多表連接查詢、子查詢、視圖相關內容。