一、Oracle學習查詢語句的筆記
在Oracle 中是對大小寫 敏感的 也就是區分大小寫
|| 連接or 或者
and 和、 并且
where 條件
between ..最小值 and 最大值(這個關鍵字包含等于)
in 字段 in(值1，值2，值3...值n)
or 關鍵字 例子----select * from emp where (deptno=10 and job ='MANAGER') or (deptno=20 and job='CLERK');
distinct去掉重復的
like |模糊查詢（注意使用通配符的問題 ） 可以在任何地方應用
|主要的通配符有
|···|“%”：可以匹配任意長度的內容
|···|“_”：可以匹配一個長度的內同
例如
select * from emp where ename like '%M%';
<> 不等號
#######基本查詢語句結束#######接下來是排序#######
Ordre by 排序：asc升序（默認的） desc：降序 *放在sql語句的最后
#######排序結束#######接下來是函數#######
一、字符函數
upper() 小寫字母變大寫 ·select upper('smith') from dual; 必須 加上from
·select * from emp where ename =upper('smith');
lower() 大寫字母變小寫 ·select lower('HELLO WORLD') from dual;
initcap() 開頭字母大寫 ·select initcap('HELLO WORLD') from dual;
·select initcap(ename) from emp;
*字符串除了可以使用||來連接。 還可以使用concat();函數來進行連接
·select concat('hellow','world') from dual;
可以進行字符串截取，求字符串長度。進行指定內容替換
·字符串截取：substr(); substr 的截取點是從0或者是1效果都是一樣的（Oracle）
Oracle 中 可以輸入負值 來倒著截取。
·select ename ,substr(ename,-3,3) from emp;
·字符串長度：length();
·內容替換： replace();
_________________范例_______________
select substr('hello',1,3) 截取字符串,
length('hello') 字符串長度,
replace('hello','l','x') 字符串替換
from dual;
________________________________________
二、數值函數
·四舍五入 :round(); 可以指定四舍五入位數select round(789.546,-2) from dual;
負值對整數進行操作。 正值是小數
·截斷小數位 :trunc();
·取余（取模）:mod
三、日期函數
·日期-數字=日期
·日期+數字=日期
·日期-日期=數字（天數）
·months_between();求出指定日期范圍的月數
·add_months();在制定日期加上制定的月數，求出之后的日期
·next_day();下一個的今天是哪一個日期
·last_day();求出給定日期的月最后一天的日期
當前日期 sysdate關鍵字 范例： select sysdate from dual;
四、轉換函數
·to_char(): 轉換成字符串
·通配符：·年：yyyy
·月：mm
·日：dd
·to_number(): 轉換成數字
·to_date(): 轉換成日期
五、通用函數
·nvl(字段,0) 如果字段里面的值是空 就按照0顯示
__________________________ 范例__________________________
select empno,ename,(nvl(sal,0)+nvl(comm,0))*12 from emp;
________________________________________________________
·decode 類似if(){}else{}
__________________________范例 _________________________________
1·select decode(1,1,'內容是1',2,'內容是2',3,'內容是3') from dual;
2·select empno 編號, ename 姓名 , HIREDATE 日期,decode
(
job,'CLERK','業務員','SALESMAN','銷售經理',
'MANAGER','經理','ANALYST','分析員',
'PRESIDENT','總裁'
) 職業
from emp;
_______________________________________________________________
#######函數結束#######接下來是多表查詢#######
1·基礎語句
*在使用多表查詢的時候會出現笛卡爾積，如果表的數據越多，那么笛卡爾積也就會越大。
比如一張表有1W條記錄，就有1W的五次方條記錄。（出現的原因是有關聯字段）
*在多表查詢的時候加入where語句，就能消除笛卡爾積。
一般會為表取別名 別名范例: select * from emp e ,dept d where e.deptno=d.deptno;
***范例（難題）**
問題：求出雇員姓名，工資，部門名稱，工資等級，上級領導名字，領導的工資，領導的工資等級。
__________________________________________________________________________________________
select e.ename 雇員姓名,e.sal 工資,d.dname 部門名稱,
decode(s.grade,'1','第五等工資','2','第四等工資','3','第三等工資','4','第二等工資' ,'5','第五等工資') 工資等級,
m.ename 上級領導名字,m.sal 領導的工資,
decode(ms.grade,'1','第五等工資','2','第四等工資','3','第三等工資','4','第二等工資' ,'5','第五等工資')領導的工資等級
from emp e, dept d ,salgrade s ,emp m,salgrade ms
where e.deptno =d.deptno and
e.sal between s.losal and s.hisal
and e.mgr=m.empno
and m.sal between ms.losal and ms.hisal;
__________________________________________________________________________________________
*注意*
有一個人沒有上級領導，查不出，因為什么呢？因為所有人的最上層x領導是他！
2·左右連接
去除笛卡爾積的時候會讓某些字段不被查出。需要使用左右連接（(+)用這個符號）
例如select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno(+)=d.deptno;
表示右連接，以右邊的表為準，證明：(+)在左邊表示右連接，反之左連接。默認左連接
3·sql1999語法支持（了解即可）
·cross join ： 交叉連接==>產生笛卡爾積
·natural join： 自然連接==>消除笛卡爾積
·using 字句： 直接關聯操作列
_________________________范例_________________________________
·select * from emp e join dept d using(deptno) where deptno=30;
on是自己定義條件·select * from emp e join dept d on(e.deptno=d.deptno) where e.deptno=30;
__________________其結果都是一樣的____________________________
· 左右連接（左右外連接）
·left join
·right join
#######多表查詢結束#######接下來是組函數及分組統計#######
*!重點!*
1· 組函數
·count()： 記錄數
·max()： 最大值\
>不能用在字符串
·min()： 最小值/
·avg()： 平均值
·sum()： 總和
2·分組統計
group by 分組條件
__________________________范例_________________________
·select deptno,count(empno) from emp group by deptno;
·select deptno,avg(sal) from emp group by deptno;
_______________________________________________________
錯誤：_______________________________________________________
1、使用了分組函數，有兩種情況
·使用了group by 并指定了分組條件 會將分組條件一起查詢出來
·沒使用分組，就只能單獨使用分組函數。
2、在使用分組函數的時候，不能出現分組函數和分組條之外的字段
select d.dname ,count(e.empno) from dept d ,emp e
where d.deptno=e.deptno
group by d.dname;
_______________________________________________________
select max(avg(sal))
from emp
group by deptno;
_______________________________________________________
*!注意!*：分組函數值能在分組中使用不能在where中使用,要使用 having 關鍵字
_____________________范例______________________________
select deptno,avg(sal)
from emp
group by deptno having avg(sal)>2000;
_______________________________________________________
#######接下來是多表查詢######接下來是子查詢########
子查詢是指，在以個查詢的內部還包括另外一個查詢。
*!注意!*：所有子查詢都要在()中編寫
子查詢分為以下三類:
· 單列子查詢：返回結果是一個列的一個內容
·單行子查詢，返回多個列，有可能是一條完整的記錄
·多行子查詢，返回多條記錄
________________________ 范例__________________________________________
--select * from emp where sal>(select sal from emp where empno=7654)
and job=(select job fromemp where empno=7788);
----------------------------------------------------
select d.dname,ed.c,ed.a,e.ename
from dept d ,
(select deptno,count(empno) c, avg(sal)a,min(sal)min
from emp
group by deptno) ed ,emp e
where
d.deptno=ed.deptno and e.sal =ed.min;
______________________________________________________________________
子查詢三種符號：
·in ：指定一個查詢范圍
·any ：有三種情況 ，大于小于等于
·=any相當于in
·>any比里面最小的值要大。
·
all比最大的值要大。
·1500;-工資大于1500
-- 能領取獎金的
--select * from emp where COMM is not null;
--能領取獎金的工資大于1500的人
--select * from emp where COMM is not null and sal>1500;
--能領取獎金或者工資大于1500的人
--select * from emp where COMM is not null or sal>1500;
--括號能表示一組條件
--select * from emp where not (sal >1500 and COMM is not null);
--select * from emp where sal >=1500 and sal <=3000;
--select * from emp where sal between 1500 and 3000;
--1981年1月1日到1981年12月31日的員工
--select * from emp where HIREDATE between '01-1月 -81' and '31-12月 -81';
--select * from emp where ename= 'SMITH';
--select * from emp where empno=7369 or empno=7499 or empno =7521;se
--select * from emp where empno in(7369,7499,7521);
--select * from emp where empno not in(7369,7499,7521);
--select * from emp where ename in('SMITH','ALLEN','KING') and COMM is not null;
--select * from emp where ename like '%M%' ;
--select * from emp where HIREDATE like '%81';
--<>不等號 查詢編號不是7369的雇員
--selec t * from emp where empno<>7369;
select * from emp where empno!=7369;
--工資由低到高
--select * from emp order by SAL ;
--降序
--select * from emp order by SAL desc ;
--select * from emp where DEPTNO=10 order by SAL desc ,HIREDATE asc;
######################查詢及排序語句結束###########################
##########函數開始###########
--select upper('smith') from dual;
--select * from emp where ename =upper('smith');
--select lower('HELLO WORLD') from dual;
--select initcap('HELLO WORLD') from dual;
--select initcap(ename) from emp;
--select concat('hellow','world') from dual;
/*select substr('hello',0,3) 截取字符串,
length('hello') 字符串長度,
replace('hello','l','x') 字符串替換
from dual;
*/
--顯示雇員姓名及姓名后三個字符
--select ename ,substr(ename,length(ename)-2) from emp;
--select ename ,substr(ename,-3,3) from emp;
--select round(789.546,-2) from dual;
--select trunc(789.536,2) from dual;
--select mod(10,3) 十除三的模 from dual;
--求當前日期 使用sysdate
--select sysdate from dual;
--select empno,ename,round((sysdate-HIREDATE)/7) 星期數 from emp;
--select empno,ename,round(months_between(sysdate,HIREDATE)) from emp;
--select add_months(sysdate ,4) from emp;
--select next_day(sysdate,'星期二') from dual;
--select last_day(sysdate) from dual;
/*
select empno,ename,to_char(HIREDATE,'yyyy') year ,
to_char(HIREDATE,'mm') month,
to_char(HIREDATE,'dd') day
from emp;
*/
--select empno, ename,to_char(HIREDATE,'fmyyyy-mm-dd')日期 from emp;
--select empno ,ename,to_char(sal,'l99,999') from emp;
--select to_number('123')+to_number('123') from dual ;
--select to_char(to_date('2010-3-21','yyyy-mm-dd'),'fmyyyy-mm-dd')from dual;
--select empno,ename,(nvl(sal,0)+nvl(comm,0))*12 from emp;
--select decode(1,1,'內容是1',2,'內容是2',3,'內容是3') from dual;
/*
select empno 編號, ename 姓名 , HIREDATE 日期,decode
(job,'CLERK','業務員','SALESMAN','銷售經理 ','MANAGER','經理','ANALYST','分析員','PRESIDENT','總裁') 職業
from emp;
##########函數結束###########
##########多表查詢開始###########
*/
--select * from emp e ,dept d where e.deptno=d.deptno;
/*
select e.ename 雇員姓名,e.sal 工資,d.dname 部門名稱,
decode(s.grade,'1','第五等工資','2','第四等工資','3','第三等工資','4','第二等工資' ,'5','第五等工資') 工資等級,
m.ename 上級領導名字,m.sal 領導的工資,
decode(ms.grade,'1','第五等工資','2','第四等工資','3','第三等工資','4','第二等工資' ,'5','第五等工資')領導的工資等級
from emp e, dept d ,salgrade s ,emp m,salgrade ms
where e.deptno =d.deptno and
e.sal between s.losal and s.hisal
and e.mgr=m.empno
and m.sal between ms.losal and ms.hisal;
*/
--select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno(+)=d.deptno;
--select e.empno,e.ename,m.empno,m.ename from emp e,emp m where e.mgr=m.empno(+);
--select * from emp natural join dept;
--select * from emp e join dept d using(deptno) ;
--select * from emp e join dept d on(e.deptno=d.deptno) where e.deptno=30;
--select e.ename,d.deptno,d.loc from emp e right outer join dept d on (e.deptno=d.deptno);
##########組函數和分組開始###########
--select deptno,count(empno) from emp group by deptno;
--select deptno,avg(sal) from emp group by deptno;
--select d.dname ,count(e.empno) from dept d ,emp e where d.deptno=e.deptno group by d.dname;
--select ename, deptno,avg(sal) from emp group by deptno;
/*
select deptno,avg(sal)
from emp
group by deptno having avg(sal)>2000;
*/
/*
select job,sum(sal) su
from emp
where job<>'SALESMAN'
group by job having sum(sal)>5000
order by su;
*/
select max(avg(sal))
from emp
group by deptno;
--select * from emp where sal>(select sal s from emp where empno=7654);
--select * from emp where sal>(select sal from emp where empno=7654) and job=(select job from emp where empno=7788);
--select ename,job,sal from emp where sal = (select min(sal) from emp);
/*
select d.dname,ed.c,ed.a,e.ename
from dept d ,
(select deptno,count(empno) c, avg(sal)a,min(sal)min
from emp
group by deptno) ed ,emp e
where
d.deptno=ed.deptno and e.sal =ed.min;
*/
--select * from emp where sal in (select min(sal) from emp group by deptno);
--select * from emp where sal >any(select min(sal) from emp group by deptno);
posted on 2010-07-06 17:13
kxbin 閱讀(240)
評論(0) 編輯 收藏