轉(zhuǎn)換函數(shù) 數(shù)值轉(zhuǎn)字符 字符轉(zhuǎn)數(shù)值 日期
字符轉(zhuǎn)數(shù)值 to_number(str)
SELECT 100+'10' from dual; 110 默認已經(jīng)幫我們轉(zhuǎn)換
SELECT 100+to_number('10') from dual; 110
數(shù)值轉(zhuǎn)字符
SELECT to_char(sal,'$99.99 ')from emp;
日期轉(zhuǎn)字符 to_char()
selectt to_char(sysdate,'yyy-mm-dd hh:mi:ss') from dual;
只想要年
SELECT to_char(SYSDATE，'yyyy')from dual;2017
只想要日
SELECT to_char(sysdate,'d')from dual;
SELECT to_char(sysdate,'dd')from dual;
SELECT to_char(sysdate,'ddd')from dual;
SELECT to_char(sysdate,'day') from dual; monday
select to_char(sysdate,'dy')from dual;mon 星期的簡寫

字符轉(zhuǎn)日期
select to date('2017-04-10','yyyy-mm-dd') from dual;
查詢1981年--1985年入職的員工信息
select * from emp where hiredate between to_date('1981','yyyy') and to_date('1985','yyyy');
通用函數(shù) nvl(參數(shù)1，參數(shù)2)如果參數(shù)1=nulL 就返回參數(shù)2
nvl2(參數(shù)1，參數(shù)2，參數(shù)3) 如果參數(shù)1=null ,就返回參數(shù)3
select nv12(null,25,5) from dual; 5
select nv12(1,5,6) from dual 5
nul1if(參數(shù)1，參數(shù)2) 如果參數(shù)1=參數(shù)2 就返回null
coalesce: 返回第一個不為null的值
SELECT nullif(5,6) from dual;5
SELECT nullif(5,5) from dual;null
SELECT coalesce(null,null,3,5.6)from dual;
條件表達式
case字段：
when 值1 then 值
when 值2 then 值
else
默認值
end
給表中姓名取一個中文名
select
case ename
when 'smith' then '劉備小二'
when 'allen' then '諸葛村夫'
ELSE
'路人甲'
end
from emp;
case .. when 通用的寫法 mysql 和oracle 中都可以使用

Oracle 特有的寫法： decode(字段，if1,then1,if2,then2,else1)
給表中姓名取一個中文名
SELECT decode(ename,'smith','劉備小二','allen','諸葛村夫,)from emp
分組表達式 group by
select 分組的條件，分組之后的操作 from 表名 group by分組的條件 having 條件過濾
分組統(tǒng)計所有部門的平均工資，找出平均工資大于1500的部門
select deptno,avg(sal) from emp group by deptno;
過濾出大于2000的
select deptno,avg(sal) from emp group by deptno having avg(sal)2000;
select deptno,avg(sal) from emp group by deptno having avg(sal)2000;
sql的編寫順序
select from where groupby having orderby
sql的執(zhí)行順序
from where groupby having select orderbv
where 和 having 區(qū)別
where 后面不能接聚合函數(shù)，可以接單行函數(shù)
having 是在groupby之后只想，可以接聚合函數(shù)
select * from bonus;
select * from salgrade;
多表查詢“” 迪卡爾積 實際上市兩、張表的乘積,但是實際開發(fā)中沒有太大意義

格式 select * from 表1，表2
select * from emp e1,dept d1 where e1.deptno=d1.deptno;
內(nèi)連接 隱士內(nèi)連接
等值內(nèi)連接： where e1.deptno=d1.deptno;
不等值內(nèi)連接： where e1.deptno<>d1.deptno;
自連接： 自己連接自己
顯示內(nèi)連接

查詢員工編號，姓名，員工部門名稱經(jīng)理的編號，姓名
select e1.empno,e1.ename,e1.mgr from emp e1,emp m1 where e1.mgr=m1.empno;
查詢員工編號，員工姓名，員工的部門，員工的部門名稱，經(jīng)理的編號，姓名
e1.deptno=d1.deptno
select e1.empno,e1.ename,e1.mgr from emp e1,emp m1 where e1.mgr=m1.empno;
查詢員工編號，員工姓名，員工的部門名稱，經(jīng)理的編號，經(jīng)理的姓名，經(jīng)理的部門名稱
select e1.empno, case e1.ename
when 1 then '一級'
when 2 then '二級'
else '五級'
end "等級"
,d1.dname,e1.mgr,m1.ename,d2.dname from emp e1 ,emp m1,dept d1,
dept d2, salgrade s1 where e1.mgr=m1.empno and e1.deptno=d1.deptno and m1.deptno =d2.deptno,
and e1.sal between s1.losal and s1.hisal
select * from salgrade;
查詢員工姓名和員工部門所處的位置
select e1.ename,d1.loc from emp e1,dept d1 where e1.deptno=d1.deptno

顯示內(nèi)連接
select * from 表1 inner join 表2 inner可以省略
select * from emp e1 inner join dept d1 on e1.deptno=d1.deptno
外連接 左外連接
坐表中所有的記錄，如果右表 沒有對應(yīng)的記錄，就顯示空
右外連接
left outer join 右外連接 right outer join outer 關(guān)鍵字可以省略
select * from emp e1 left outer join dept d1 on e1.deptno=d1.deptno;

insert into emp(empno,ename) values (9527,'huaan')
select * from emp e1 right outer join dept d1 on e1.deptno=d1.deptno
oracle 中的外連接：(+)實際上是如果沒有對應(yīng)的記錄就加上空值
select * from emp e1,dept d1 where e1.deptno =d1.deptno(+);
select * from emp e1,dept d1 where e1.deptno(+)=d1.deptno;
子查詢 查詢語句中嵌套查詢語句
查詢員工最高工資的員工信息
用來解決復(fù)雜的查詢語句
單行子查詢： 》 》= = 《 《= <> !=

多行子查詢
1.查詢出最高工資
select max(sal) from emp; 5000
2.工資等于最高工資
select * from emp where sal=(elect max(sal) from emp);
查詢出比雇員7654的工資高，同時和7788從事相同的工作的員工信息
雇員7654的工資1250
select sal from emp where empno=7654;
7788從事的工作
select job from emp where empno=7788;
兩個條件合并
select * from emp where sal>1250 and job='anlyst';

select * from emp where sal>(select sal from emp where empno=7654) and job=(select * from emp where sal>1250 );

查詢每個部門最低工資的員工信息和他所在的部門信息
知道每個部門的最低工資 分組統(tǒng)計
select deptno min(sal) from emp groupby deptno
員工工資等于他所處部門的最低工資
select * from emp e1,
(select deptno min(sal) from emp groupby deptno)t1
where e1.deptn=t1.deptno and e1.sal=t1.minsal;
查詢部門相關(guān)信息
select * from emp e1,
(select deptno min(sal) from emp groupby deptno)t1,dept d1
where e1.deptn=t1.deptno and e1.sal=t1.minsal and e1.deptnp=d1.deptno;
查詢不是領(lǐng)導(dǎo)的信息
select * from emp where empno in (select mgr from emp);
select * from emp where empno <>all (select mgr from emp);
正確的寫法
select * from emp where empno in (select mgr from emp where mgr is not null);
通常情況下，數(shù)據(jù)庫中不要出現(xiàn)null 最好的做法加上notnull
null值并不代表不占空間 char(100)null 100個字符
exists（查詢語句）：存在的意思
當作布爾值來處理：當查詢語句有結(jié)果的時候就是返回true否則返回的事false
數(shù)據(jù)量比較大的時候是比較高效的
select * from emp where exists(select * from emp where deptno =1234567);
select * from emp where 3=4;
select * from emp where exists(select * from emp where deptno=20);
查詢有員工的部門信息
select * from dept d1 where exists(select * from emp e1 where e1.deptno=d1.deptno);
找到員工工資最高的前三名(降序排序)
select * from emp order by sal desc;
rownum:偽列，系統(tǒng)自動生成的一列，用來表示行號的
rownum 是oracle中特有的用來表示行號的，默認值1起始值是1，在查詢出結(jié)果之后，再添加1
select rownum, e1.* from emp e1;
查詢rownum大于2的所有記錄,默認起始值是1 沒有任何記錄
rownum最好不要于號判斷，可以做小于好判斷
select rownum,* rom emp e1 where rownum>2;
查詢rownum大于等于1的所有記錄
select rownum,* rom emp e1 where rownum>=1;
查詢rownum<6的所有記錄
select rownum,e1.* from emp e1 where rownum<6
rownum排序
select rownum,e1.* from emp e1 order by sal;
sql執(zhí)行順序
from where groupby having select rownum order by
找到員工表中工資最高的前三名
select 31.* from emp e1 order by sal de
將上面的結(jié)果當做一張表處理，再查詢
select rownum t1.* from (select 31.* from emp e1 order by sal de) t1;
只要顯示前三條記錄
select rownum t1.* from (select 31.* from emp e1 order by sal de) t1 where rownum<4;
找到員工中薪水大于本部門平均薪水的員工
所有部門的平均薪水 分組統(tǒng)計
select deptno,avg(sal) from emp group by deptno;
員工工資>本部門平均工資
select * from emp e1,(select deptno,avg(sal)t1rom emp group by deptno;)t1
where e1.deptno=t1.deptno and e1.sal> t1.avgsal;
關(guān)聯(lián)子查詢，非關(guān)聯(lián)子查詢
select * from emp e where sal >(select avg(sal) from emp e2 group by deptno) t1
having e.deptno =e2.deptno);
統(tǒng)計每年入職的員工個數(shù)
select hiredate from emp;
只顯示年
select to_char (hiredate，'yyyy') from emp;
分組統(tǒng)計
select to_char (hiredate，'yyyy'yycount(1 )ccm emp group by to_char(hiredate,'yyyy')
將1980年豎起來
select sum(
case yy
when '1987' then cc end )1987"
rom
(select to_char (hiredate，'yyyy'yycount(1 )ccm emp group by to_char(hiredate,'yyyy') )
將1980年豎起來 并且取一個別名 1987
去除行記錄中的空值
統(tǒng)計員工的總數(shù)
select sum (cc) "total" from
將1987和total合并在一起
顯示所有年份的結(jié)果
rowid 偽列 每行記錄所存放的真實物理地址
rownum:行號，每查詢出記錄之后，就會添加一個行號
select rowid,e.* from emp e;
去除表中重復(fù)記錄
creat table p(name varchar2(10) );
insert into p values('黃偉福');
select * from emp;
刪除表中重復(fù)記錄，只保留了rowid最小的那行記錄
select rowid,p.* from emp;
select distinct * from p;
delete from p where
select min(rowid) from p2 where p1.name =p2.name
delete from p1 where p1.name=p2.name
delete from p1 where p1.rowid>(select min(rowid) from p2 where p1.name =p2.name)
rownum:分頁查詢
查詢第6條-第10條記錄
在oracle中只能使用查詢來做分頁查詢

select rownum,emp.* from emp;

select rownum,emp.* from emp;
select rownum hanghao ,emp.* from emp;
select * from (select rownum hanghao ,emp.* from emp;) tt where tt.hanghao between 6 and 10;
集合運算：并集 將兩個查詢結(jié)果進行合并
交集
差集
工資大于1500 或者20號部門下的員工
select * from emp where sal>1500 or deptno=20;
工資大于1500
select * from emp where sal >1500
20號部門下的員工
select * from emp where deptn0 =20;
并集運算 union union all
select * from emp where sal >1500
union
select * from emp where deptn0 =20; 9條

select * from emp where sal >1500
union all
select * from emp where deptn0 =20; 12條
union：去除重復(fù)的，并且排序
union all:不會去除重復(fù)的
所有的查詢結(jié)果可能不是來自同一張表
基本信息，詳細信息
emp 2000年
2017年 手機 詳細信息 emp2017
差集運算：兩個結(jié)果相減
查詢1981年入職員工（不包括總裁和經(jīng)理）
select * from emp where to_char(hiredate,'yyyy')=1981;
總裁和經(jīng)理
select * from emp where job='president' or 'manager';
select * from emp where to_char(hiredate,'yyyy')=1981;
minus
select * from emp where job='president' or 'manager';

select * from emp where to_char(hiredate,'yyyy')=1981;
minus
select * from emp where job='president' or 'manager';
集合運算中的注意事項
列的類型要一致
按照順序?qū)?br> 列的數(shù)量要一致
如果不足，用空值填充
select * from emp where to_char(hiredate,'yyyy')=1981;
union
select * from emp where job='president' or 'manager';

select ename,sal,deptno from emp where sal>1500
union
select ename,sal, from emp where deptno=20;