转换函数 数值转字符 字符转数值 日期
字符转数值 to_number(str)
SELECT 100+'10' from dual; 110 默认已经帮我们转换
SELECT 100+to_number('10') from dual; 110
数值转字符
SELECT to_char(sal,'$99.99 ')from emp;
日期转字符 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 星期的简写
字符转日期
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');
通用函数 nvl(参数1，参数2)如果参数1=nulL 就返回参数2
nvl2(参数1，参数2，参数3) 如果参数1=null ,就返回参数3
select nv12(null,25,5) from dual; 5
select nv12(1,5,6) from dual 5
nul1if(参数1，参数2) 如果参数1=参数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 条件过滤
分组统计所有部门的平均工资，找出平均工资大于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的执行顺序
from where groupby having select orderbv
where 和 having 区别
where 后面不能接聚合函数，可以接单行函数
having 是在groupby之后只想，可以接聚合函数
select * from bonus;
select * from salgrade;
多表查询“” 迪卡尔积 实际上市两、张表的乘积,但是实际开发中没有太大意义
格式 select * from 表1，表2
select * from emp e1,dept d1 where e1.deptno=d1.deptno;
内连接 隐士内连接
等值内连接： where e1.deptno=d1.deptno;
不等值内连接： where e1.deptno<>d1.deptno;
自连接： 自己连接自己
显示内连接
查询员工编号，姓名，员工部门名称经理的编号，姓名
select e1.empno,e1.ename,e1.mgr from emp e1,emp m1 where e1.mgr=m1.empno;
查询员工编号，员工姓名，员工的部门，员工的部门名称，经理的编号，姓名
e1.deptno=d1.deptno
select e1.empno,e1.ename,e1.mgr from emp e1,emp m1 where e1.mgr=m1.empno;
查询员工编号，员工姓名，员工的部门名称，经理的编号，经理的姓名，经理的部门名称
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
显示内连接
select * from 表1 inner join 表2 inner可以省略
select * from emp e1 inner join dept d1 on e1.deptno=d1.deptno
外连接 左外连接
坐表中所有的记录，如果右表 没有对应的记录，就显示空
右外连接
left outer join 右外连接 right outer join outer 关键字可以省略
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 中的外连接：(+)实际上是如果没有对应的记录就加上空值
select * from emp e1,dept d1 where e1.deptno =d1.deptno(+);
select * from emp e1,dept d1 where e1.deptno(+)=d1.deptno;
子查询 查询语句中嵌套查询语句
查询员工最高工资的员工信息
用来解决复杂的查询语句
单行子查询： 》 》= = 《 《= <> !=
多行子查询
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 );
查询每个部门最低工资的员工信息和他所在的部门信息
知道每个部门的最低工资 分组统计
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;
查询部门相关信息
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;
查询不是领导的信息
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);
通常情况下，数据库中不要出现null 最好的做法加上notnull
null值并不代表不占空间 char(100)null 100个字符
exists（查询语句）：存在的意思
当作布尔值来处理：当查询语句有结果的时候就是返回true否则返回的事false
数据量比较大的时候是比较高效的
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:伪列，系统自动生成的一列，用来表示行号的
rownum 是oracle中特有的用来表示行号的，默认值1起始值是1，在查询出结果之后，再添加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执行顺序
from where groupby having select rownum order by
找到员工表中工资最高的前三名
select 31.* from emp e1 order by sal de
将上面的结果当做一张表处理，再查询
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;
找到员工中薪水大于本部门平均薪水的员工
所有部门的平均薪水 分组统计
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;
关联子查询，非关联子查询
select * from emp e where sal >(select avg(sal) from emp e2 group by deptno) t1
having e.deptno =e2.deptno);
统计每年入职的员工个数
select hiredate from emp;
只显示年
select to_char (hiredate，'yyyy') from emp;
分组统计
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
去除行记录中的空值
统计员工的总数
select sum (cc) "total" from
将1987和total合并在一起
显示所有年份的结果
rowid 伪列 每行记录所存放的真实物理地址
rownum:行号，每查询出记录之后，就会添加一个行号
select rowid,e.* from emp e;
去除表中重复记录
creat table p(name varchar2(10) );
insert into p values('黄伟福');
select * from emp;
删除表中重复记录，只保留了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;
集合运算：并集 将两个查询结果进行合并
交集
差集
工资大于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：去除重复的，并且排序
union all:不会去除重复的
所有的查询结果可能不是来自同一张表
基本信息，详细信息
emp 2000年
2017年 手机 详细信息 emp2017
差集运算：两个结果相减
查询1981年入职员工（不包括总裁和经理）
select * from emp where to_char(hiredate,'yyyy')=1981;
总裁和经理
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';
集合运算中的注意事项
列的类型要一致
按照顺序写
列的数量要一致
如果不足，用空值填充
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;
