转换函数 to_char、to_number。to_date
数字函数 round 、trunc、mod
日期函数 months_between 、add_months、last_day、next_day、
通用函数 nvl、coalesce、decde
字符串函数 upper、lower、initcap、length、trim、replace
让日期按指定格式显示
select to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') now from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now from dual;
select to_char(sysdate,' hh12:mi:ss') now from dual;
让日期按照全英文显示
select to_char(sysdate,' year month dd day dy hh:mi:ss pm') now from dual;
按照指定格式插入日期格式的数据，但还是和原来显示的效果是一样的
insert into emp_ck values(1012,’rose’,’Programmer’,5500,400,to_date(‘2011-10-09’,’yyyy-mm-dd’),1002,10);
coalesce函数(奖金，薪水*0.5，100)
作用：返回第一个非空参数
建议最后一个参数设为常量
参数可以是很多个
select name,salary,bonus,coalesce(bonus,salary*0.5,100) sal from emp_ck;
本语句的意思就是，把没有奖金的人的工资的一半当做奖金。
case and的形式
select name,job,salary,
case job when 'Analyst'
then salary*1.15
when 'Manager'
then salary*1.10
when 'clerk'
then salary*1.05
else salary
end new_salary
from emp_ck;
本语句的意思是给每个职位加薪水用
还可以用oracle的函数decode
decode(列名，匹配条件1，取值1，
匹配条件2，取值2，
。。。。。
默认值)
select name,job,salary,decode(job,'analyst',salary*1.15,
'manager',salary*1.10,
'clerk',salary*1.05,
salary) newsalary
from emp_ck;
补齐函数
左补齐 lpad（字段，希望显示的位数，‘需要补的字符或数字’）
select salary,lpad(salary,7,'$') newsal from emp_ck;
select salary,lpad(salary,7,0) newsal from emp_ck;
右补齐 rpad（字段，希望显示的位数，‘需要补的字符或数字’）
select salary,rpad(salary,7,'$') newsal from emp_ck;
select salary,rpad(salary,7,0) newsal from emp_ck;
日期函数
两个月份之间间隔数
months_between(日期1，日期2)
select months_between(to_date('20110910','yyyymmdd'),to_date('20111009','yyyymmdd')) from dual;
增加月数
add_months(指定的日期，指定的月数)
select sysdate,add_months(sysdate,10) late from dual;
当月的最后一天
last_day(指定的日期)
select last_day(sysdate) from dual;
下一个周几
next_day(指定的日期，指定是星期几)
select next_day(sysdate,'sat') from dual;
日期格式指定
to_date(日期的形式，指定的日期格式)
select to_date('20111009','yyyymmdd') from dual;
select to_date('20111009','yyyy-mm-dd') from dual;
把字符串转换成数字
to_number(字符串)
select to_number('20111009') from dual;
把指定元素转换成字符串
to_char(指定元素，指定格式的字符串(要转换成数字用0表示，其他格式用其他
形式))
select to_char(1234,'0000') from dual;
数字的四舍五入
round(要进行处理的数字，保留几位小数)
select round(1029321.345,0) from dual;
trunc(要处理的数字，要截取小数点的长度)
他不进行四舍五入。只按要求指定截取长度
select trunc(1029321.345,0) from dual;
取模
mod(要取模的数字，对几取模)
select mod(100,3) from dual;
这个的用法是，如果“要查询的字段”是NULL ，那么就把“指定的元素” 放在为NULL的上面
nvl(要查询的字段，指定的元素)
select nvl(salary,1) F,nvl(salary,0) E from emp_ck;
字符串变大写
upper(指定的字符串)
select upper('abc') from dual;
字符串变小写
lower(指定的字符串)
select lower('ABC') from dual;
指定的字符串首字母大写
initcap(指定的字符串)
select initcap('helloworld') from dual;
长度的显示
length(指定的元素)
select length('helloworld') from dual;
select length(1234567890) from dual;
替换函数
replace(字符串，要替换的字母，要换成的字符)
select replace('aaaa','a','b') from dual;
去除字符串的空格的函数
trim(指定的字符串)(trim 有两种一个是左去除空格Ltrim一个是右去除空格Rtrim,也可以默认trim两边都去除空格)
select trim(' HelloWorld! ! ') Trim from dual;
select Ltrim(' HelloWorld! ! ') Trim from dual;
select Rtrim(' HelloWorld! ! ') Trim from dual;
查找在一个字符串中有指定的字符串的位置
instr(字符串，指定在字符串中出现的字符或字符串，开始查找的起点，第几次出现的次数)
得到在string1中包含string2的位置. string1时从左边开始检查的,开始的位置为a,如果a是一个负数,那么string1是从右边开始进行扫描的.第b次出现的位置将被返回. a和b都缺省设置为1,这将会返回在string1中第一次出现string2的位置.如果string2在a和b的规定下没有找到,那么返回0.位置的计算是相对于string1的开始位置的,不管
select instr('123123123123','2',1,1) from dual;
select count(*) from emp_ck where name=''
按指定的长度截取字符串
substr(指定字符串，指定字符的位置从1开始(如果为负责从右边开始)，指定截取长度)
select substr('abcabcabc',2,1) from dual;
过滤分组结果：having短语平均薪水在5000以上的部门
select deptno,nvl(salary,0) from emp_ck;
select deptno,avg(nvl(salary,0)) from emp_ck group by deptno having avg(nvl(salary,0))>5000;
每个部门每个职位有多少人
select deptno,job,count(*) People from emp_ck group by deptno,job order by deptno,job;
子查询
子查询的all用法
select name,salary from emp_ck where salary >all(select salary from emp_ck where deptno =10 and salary is not null);
select name,salary from emp_ck where salary >any(select salary from emp_ck where deptno =10 and salary is not null);
select deptno,dname from dept_ck where deptno in
(10,20,30);
多列子查询in的用法
select deptno,dname from dept_ck where deptno in (select deptno from emp_ck where job ='Manager');
not in 的用法
select deptno,dname from dept_ck where deptno not in (select deptno from emp_ck where job ='Manager');
在子查询中如果返回一条数据可以用=进行连接
如果返回多条数据用in进行连接
那个部门的人数大于3个
select deptno,count(*) from emp_ck group by deptno having count(*) >3;
用子查询做，
select deptno,count(*) from emp_ck group by deptno having count(*) >(select deptno from emp_ck where deptno = 20);
select deptno,avg(nvl(salary,0)) from emp_ck group by deptno;
哪个部门的平均薪水比部门20的平均薪水高
select deptno,avg(nvl(salary,0)) from emp_ck group by deptno having avg(nvl(salary,0))>(select avg(nvl(salary,0)) from emp_ck where deptno=20);
研发部都有哪些职位？
select job from emp_ck where deptno = (select deptno from dept_ck where dname = '研发部');
这样查询用重复数据产生，所以要用下面的查询方式，加去重操作，distinct
select distinct job from emp_ck where deptno = (select deptno from dept_ckwhere dname = '研发部');
select deptno,avg(nvl(salary,0)) from emp_ck group by deptno;
所在部门的平均薪水大于7000的员工名字，薪水，职位，部门号
select name,avg(nvl(salary,0)) from emp_ck having avg(nvl(salary,0))>7000 group by name;
create table student(sid int(4),sname varchar(8))
insert into student values(102,'jerry');
create table course(cid int(2),cname varchar(8));
insert into course values(1,'java');
create table student_course(sid int(4),cid int(4));
insert into student_course values(101,1);
查询每个经理的员工的最低工资，没有经理的不算在内
SELECT e.mgr,https://www.doczj.com/doc/298387648.html,,min(e.salary) from emp_ck e JOIN emp_ck x on e.mgr = x.id
WHERE e.mgr is not null
GROUP BY e.mgr,https://www.doczj.com/doc/298387648.html,;
查询所有部门的部门编号，部门名字，所在位置，员工数量和薪水平均值
10 研发部 北京 6 6850
20 人事部 上海 3 8000
select d.deptno,d.dname,e.avgSalary from dept_ck d
join (select deptno,count(*),avg(nvl(salary,0)) avgSalary from emp_ck group by deptno) e on d.deptno = e.deptno;
union 去重并集 union all不去重，并集
union 排序了，union all没有排序
在用union 的时候，两个查询的字段必须字段一致，查询字段顺序一致
select salary,name,deptno from emp_ck where deptno =10 union select salary,name,deptno from emp_ck where salary>8000;
交集
select salary,name,deptno from emp_ck where deptno =10 intersect
select salary,name,deptno from emp_ck where salary>8000;
A--B minus:减法
select salary,name,deptno from emp_ck where deptno =10 minus
select salary,name,deptno from emp_ck where salary>8000;
哪些员工的薪水低与本部门的平均薪水
select name,salary,deptno from emp_ck x where salary < (select avg(nvl(salary
,0))
from emp_ck where deptno=x.deptno);
哪些员工不是别人的经理
注意在第二个子查询时，只为了得到条件，所以在select后面可以写数字做代替
select id,name,deptno from emp_ck x where not exists (select 'a' from emp_ck where mgr = x.id);
哪些部门没有员工
select d.dept,d.dname from dept_ck d left outer join emp_ck e on d.deptno=e.deptno where e.id is null
或者
select deptno from dept_ck
minus
select distinct deptno from emp_ck;
或者
select d.deptno,d.dname from dept_ck d
where not exists (select 1 from emp_ck where deptno = d.deptno);
对于已经被删除的表的恢复，但是时间不能太长，对于数据量大的就必须尽快
flashback table emp_ck to before drop
设置自动提交
set autocommit on;
//DCL
grant (赋予权限)/revoke(撤销权限)
grant select on xxxyyy to openlab;
某账户取消openlab账户对xxxyyy表的查询权限
revoke select on xxxyyy from openlab;
select constraint_name,constraint_type from user_constraints where table_name ='student_ck';
查询后得到的显示其中字母代表的含义
C not null
P PK
U Unique
R FK
oracle自动创建自增长序列，每次加1
create sequence myseq_ck;
select myseq_ck.nextval from dual;
oracle自动创建自增长序列，每次加1往表里增加数据
create table oracle_table_ck(id number(2),name varchar2(5));
insert into oracle_table_ck(myseq_ck.nextval,'tim');
从1000为起点进行自增长步进是2
create sequence myseq_ck2
start with 1000
increment by 2;
insert into oracle_table_ck(id,name)
values(myseq_ck2.nextval,'hh');
rownum的用法
把一个查询结果做视图，通过rownum在查询
select rownum,id,name,salary
from
(select id,name,salary from emp_ck order by salary)
where rownum<=3;
查找最大N条数据时注意去掉空值
select rownum,name,id,hiredate
from (select id,name,hiredate from emp_ck
order by hiredate)
where rownum <=3;
薪水倒序排名在第5-第10员工
select rn,id,name,salary from
(select rownum rn,id,name,salary from
(select id,name,salary from emp_ck where salary is not null order by salary desc)
)
where rn<10 and rn>=5;