Oracle学习笔记01

Oracle学习笔记01

declare
--说明部分
begin
--程序
	dbms_output.put_line('hello plsql');
end;

–当前用户

show user

–当前用户下的表

select * from tab;

–员工表的结构

desc emp;

–查询所有员工信息

select * from emp;

–设置行宽

set linesize 120;

–通过列名查询

select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;

–sql的优化原则：尽量使用列名

–查询员工信息：员工号、姓名、月薪

select empno,ename,sal from emp;

–查询员工信息：员工号、姓名、月薪、年薪

select empno,ename,sal,12*sal from emp;

–查询员工信息：员工号、姓名、月薪、年薪、奖金、年收入

select empno,ename,sal,12*sal,comm,(12*sal+comm) from emp

–sql中的null

1.包含null的表达式都为null

2.null永远!=null

3.如果集合中含有null，不能使用not in 但可以使用in

4.null的排序问题，null值最大

5.组函数会自动滤空

–nvl(a,b)滤空函数

​ --nvl2(arg1,arg2,arg3)当arg1不为null时，则函数返回arg2，arg1为null则返回arg3

​ --查看每个人绩效情况

select ename,sal,nvl2(comm,'没有绩效','有绩效') from emp;

–null永远!=null

–查询奖金为null的员工

--错误写法
select * from emp where comm=null;
--未选定行

--正确写法
select * from emp where comm is null;

–加别名

select empno as "员工号",ename "姓名",sal 月薪 from emp;

–distinct 去掉重复记录

select distinct deptno from emp;
select distinct job from emp;

–distinct作用于后面的所有的列，只要组合起来不重复就行了

select distinct deptno,job from emp;

–concat函数

select concat('Hello','Oracle') from emp;

–dual是Oracle中非常特殊的一张表，叫伪表

select concat('Hello','Oracle') from dual;

–连接符||

select "Hello"||"world" from dual;
--查询员工的信息：***的薪水是***
select ename||'的薪水是'||sal from emp;

–查询10号部门的员工

select * from emp where deptno=10;

–字符串严格区分大小写

–查询名叫KING的员工

--错误写法
select * from emp where ename='King';
--未选定行
--正确写法
select * from emp where ename='KING';

–Oracle严格区分大小写，MySQL不区分

–日期格式敏感

–查询入职日期是17-11月-81的员工

select * from emp where hiredate='17-11月-81'

–修改日期格式

--查看
select * from v$nls_parameters;
--修改
alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

–between…and

–查询薪水1000-2000之间的员工

select *
from emp
where sal between 1000 and 2000;

–in在集合中

–查询10和20号部门的员工

select *
from emp
where deptno in (10,20);

–查询不是10和20号部门的员工

select *
from emp
where deptno not in (10,20);

–null值，如果集合中含有null，不能使用not in 但可以使用in

select *
from emp
where deptno not in (10,20,null);
--未选定行

–like模糊查询

–查询名字以S开头的员工

select *
from emp
where ename like 'S%';

–查询名字是四个字的员工

select *
from emp
where ename like'____';
--四个下划线

–查询名字中含有下划线的员工

--错误写法
select *
from emp 
where ename like '%_%';
--正确写法
select *
from emp
where ename like '%\_%' escape '\';

–order by

–查询员工信息，按月薪排序

select * from emp order by sal;

–order by 后面+列，表达式，别名，序号

select empno,ename,sal,sal*12
from emp
order by sal*12 desc;--降序

select empno,ename,sal,sal*12 年薪
from emp
order by 4 desc;--按年薪降序

–多个列也可以排序

–order by 作用于后面所有的列，先按照第一个列排序，再后面的列排序

–desc只作用于离他最近的列

select * from emp order by deptno,sal desc;

–查询员工信息，按照奖金排序

–null值最大

select * from emp order by comm desc;
--解决null值最大
select * from emp order by nvl(comm,0) desc;
select * from emp order by comm desc nulls last;

–字符函数

–upper转化为大写，lower转化为小写,initcap将字符串首字母转化为大写，其他为小写

select lower('Hello Oracle'), upper('Hello Oracle'),initcap('hello world') from dual;

–substr

–substr(a,b)从a中，第b位开始取

select substr('Hello Oracle',4) from dual;
--lo Oracle

–substr(a,b,c)从a中，第b位开始取，取c位

select substr('Hello World',4,3) from dual
--lo 

–length字符数 lengthb字节数

select length('成都') 字符,lengthb('成都') 字节 from dual;
字符		字节
2		4

–instr(a,b)

–在a中，查找b的位置

select instr('hello oracle','||') from dual;

–replace

select replace('Hello world','l','*') from dual;
--用*代替l

–数值函数 round trunc

–round四舍五入

select round(45.926,2),round(45.926,1),round(45.926,0),round(45.926,-1),round(45.926,-2) from dual

–trunc截取

select trunc(45.926,2),trunc(45.926,1),trunc(45.926,0),trunc(45.926,-1),trunc(45.926,-2) from dual;

–日期函数

–当前时间

select sysdate from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

–昨天 今天 明天

select (sysdate-1),sysdate,sysdate+1 from dual;

–计算员工的工龄：天 星期 月 年

select ename,hiredate,(sysdate-hiredate),(sysdate-hiredate/7),(sysdate-hiredate)/30,(sysdate-hiredate)/365 from emp;

–months_between

select ename,hiredate,(sysdate-hiredate)/30,months_between(sysdate,hiredate) from emp;
--两个结果有出入，因为有的月份不等于30天

–5个月后

select add_months(sysdate,5) from dual;

–这个月最后一天last_day

select last_day(sysdate) from dual;

–next_day

–下一个星期三

select next_day(sysdate,'星期三') from dual;

–转换函数to_char

–2022-04-26 15：43：30今天是星期三

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;

–查询员工的薪水：两位小数，千位符，本地货币代码

select to_char(sal,'L9,999.99') from emp;

–case when , decode

–查询员工涨后工资

–总裁1000，经理800，其他400

--写法一
select ename,job,sal 涨前工资,
case job when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal+400
end 涨后工资
from emp;
--写法二
select ename,job,sal 涨前工资,
decode(job,'PRESIDENT', sal+1000,
      'MANAGER',sal+800,
      sal+400) 涨后工资
from emp;

–多行函数

–工资总额sum

select sum(sal) from emp;

–人数count

select count(*) from emp;

–平均工资avg

select sum(sal)/count(*),avg(sal) from emp;

–平均奖金

select sum(comm)/count(*),sum(comm)/count(comm),avg(comm) from emp;

select count(*),count(comm) from emp;

select count(*),count(nvl(comm,0)) from emp;

–组函数会自动滤空