Oracle学习（一）

╰半橙微兮° 2022-01-10 03:47 245阅读 0赞

# Oracle学习（一） #

主要学习了基本的select语句、过滤与排序、单行函数、分组函数和多表查询

### 文章目录 ###

*  Oracle学习（一）
 *   *  基本的select语句
     *  过滤与排序
     *  单行函数
     *  多行函数
     *  多表查询
     *  附：一些说明

## 基本的select语句 ##

查看当前用户

show user;

当前用户下的表

select * from tab;

查看员工表的结构

desc emp;

查询所有的员工信息

select * from emp;

设置显示的行宽和列宽

show linesize;--显示行宽
    set linesize 120;--设置行宽为120
    col ename for a8;--设置ename列宽度为a8
    col sal for 9999;--设置sal列宽度为9999

通过列名显示查询

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

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

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

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

--comm可能为空，不能与其他数直接相加
    --滤空函数nvl，相当于mysql中的ifnull函数
    select empno,ename,sal,sal*12,comm,sal*12+nvl(comm,0) from emp;

查询奖金为null的员工

--切记：条件不能用comm = null
    select * from emp where comm is null;

取别名，以下为错误示例

--as可有可无。但别名强烈建议用双引号括起来！！！若没有双引号，别名中间不能有空格或者查询关键词，如：月 薪、月select薪 等等都是错误的
    select empno as "员工号",ename "姓名",sal 月    薪,sal*12,comm,sal*12+nvl(comm,0);

distinct 去掉重复记录

--作用于deptno列
    select distinct deptno from emp;
    --作用于deptno,job这两列
    select distinct deptno,job from emp;

连接符 ||、concat函数，效果是一样的

--dual是伪表
    select 'Hello'||' World' as "字符串" from dual;
    select concat('Hello',' World') as "字符串" from dual;

查询员工信息：xxx的薪水是xxx

select ename||'的薪水是'||sal 信息 from emp;

## 过滤与排序 ##

查询10号部门的员工

select * from emp where deptno=10;

查询名叫KING的员工

--加了引号后，就是字符串，其大小写敏感
    select * from emp where ename='KING'

查询入职日期是17-11月-81的员工，默认的日期格式为DD-MON-RR

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

修改日期格式

--查看当前的日期格式
    select * from v$nls_parameters;
    --session当前会话有效 ，system全局有效
    alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

再次查询入职日期是1981-11-17的员工

select * from emp where hiredate='1981-11-17';

查询薪水1000~2000之间的员工

--between and: 1.含有边界 2.小值在前 大值在后
    select * from emp where sal between 1000 and 2000;

查询10和20号部门的员工

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

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

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

查询名字以S打头的员工

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

查询名字是4个字的员工

select * from emp where ename like '____'

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

--转意字符
    select * from emp where ename like '%\_%' escape '\'

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

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 年薪 desc;
    --序号(select后面列的编号，从1开始)
    select empno,ename,sal,sal*12 年薪 from emp order by 4 desc

多个列的排序

--order by 作用于后面所有的列，先按照第一个列排序，再后面的列
    select * from emp order by deptno asc,sal desc

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

--comm中包含空值，oracle中默认null最大
    select * from emp order by comm desc;
    --可以把null放到最后
    select * from emp order by comm desc nulls last;

## 单行函数 ##

字符“Hello World”转小写、转大写、首字母大写

select lower('Hello World') 转小写,upper('Hello World') 转大写,initcap('hello world') 首字母大写 from dual;

截取字符“Hello World”

--substr(a,b) 从a中，第b位(序号从1开始)开始取 
    select substr('Hello World',4) 子串 from dual;
    --substr(a,b,c) 从a中，第b位(序号从1开始)开始取，取c位
    select substr('Hello World',4,3) 子串 from dual;

求字符“北京”的字符长度和字节长度

--length 字符数 lengthb 字节数
    select length('北京') 字符,lengthb('北京') 字节 from dual;

在“Hello World”中，查找“ll”，返回ll所在位置

select instr('Hello World','ll') 位置 from dual;

将“abcd”用\*填充满10位

--lpad 左填充，rpad 右填充
    select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;

去掉字符串“Hello WorldH”前后的H字符

select trim('H' from 'Hello WorldH') from dual;

将“Hello World”中的“l”替换成“\*”

select replace('Hello World','l','*') from dual;

对数字进行四舍五入

--round(数字,保留小数点位数)
    --结果：45.93、45.9、46、50、0 
    select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,round(45.926,-1) 四,round(45.926,-2) 五 from dual;

对数字进行截断

--trunc(数字,保留小数点位数)
    --结果：45.92、45.9、45、40、0 
    select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,trunc(45.926,-1) 四,trunc(45.926,-2) 五 from dual

获取当前时间

--mysql中用now()函数
    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;

53个月后所在的日期

select add_months(sysdate,53) from dual;

当月的最后一天所在的日期

select last_day(sysdate) from dual;

下一个星期四所在的日期

--必须是“星期几”，不能写“礼拜几”
    select next_day(sysdate,'星期四') from dual;

对当前时间按年、月进行四舍五入

select round(sysdate,'month'),round(sysdate,'year') from dual;

输出“2019-06-20 12:18:12今天是星期四”

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

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

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

计算年薪（包括奖金）

--nvl2(a,b,c) 当a=null的时候，返回c；否则返回b
    select sal*12+nvl2(comm,comm,0) from emp;

比较两个字符，相同则返回null

--nullif(a,b) 当a=b的时候，返回null；否则返回a
    select nullif('abc','abc') 值 from dual; --返回空
    select nullif('abc','abcd') 值 from dual; --返回abc

从左到右 找到第一个不为null的值

--可以用coalesce函数
    select comm,sal,coalesce(comm,sal) "第一个不为null的值" from emp;

给员工涨工资，总裁1000 经理800 其他400

--case方法，适用于mysql、oracle等
    select ename,job,sal as 涨前,
               case job when 'PRESIDENT' then sal+1000
                        when 'MANAGER' then sal+800
                        else sal+400
                end as 涨后
        from emp;
        
    --decode方法，只能用于oracle
    select ename,job,sal as 涨前,
               decode(job,'PRESIDENT',sal+1000,
                          'MANAGER',sal+800,
                                    sal+400) as 涨后
        from emp;

## 多行函数 ##

计算工资总额

select sum(sal) from emp;

计算人数

select count(*) from emp;

计算平均工资

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

计算平均奖金

select sum(comm)/count(*) as 一,sum(comm)/count(comm) as 二,avg(comm) as 三 from emp;

count(\*)和count(列名)的区别

--count(*)计算所有行数，而count(列名)会忽略null所在的行
    select count(*), count(comm) from emp;

要用count(列名)计算所有行

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

每个部门的平均工资

select deptno,avg(sal) from emp group by deptno;

按部门和工资进行分组，计算所有工资

--多个列的分组: 先按照第一个列分组，如果相同，再第二个列分组，以此类推
    select deptno,job,sum(sal) from emp group by deptno,job order by 1;

查询平均工资大于2000的部门

--where和having的区别：where不能使用多行函数
    select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

查询10号部门的平均工资

select deptno,avg(sal) from emp group by deptno having deptno=10;

group by 的增强

select deptno,job,sum(sal) from emp group by rollup(deptno,job);

## 多表查询 ##

查询员工信息：员工号 姓名 月薪 部门名称

--等值连接
    select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;

查询员工信息：员工号 姓名 月薪 工资级别

--不等值连接
    select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;

按部门统计员工信息：部门号 部门名称 人数

--外连接，以部门为主
    /* 左外连接: 当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含在最后的结果中 写法:where e.deptno=d.deptno(+) 右外连接: 当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含在最后的结果中 写法:where e.deptno(+)=d.deptno */
    select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname;

查询员工信息：员工姓名 老板姓名

--自连接，自连接不适合操作大表
    select e.ename 员工姓名,b.ename 老板姓名 from emp e,emp b where e.mgr=b.empno;

查询员工、老板关系信息

--层次查询
    select level,empno,ename,mgr
    from emp
    connect by prior empno=mgr
    start with mgr is null 
    order by 1;

## 附：一些说明 ##

sql中的null：

1.  包含null的（算数）表达式，计算结果都为null
2.  null永远!=null
3.  如果集合中含有null，不能使用not in；但可以使用in
4.  null的排序，有null值的最大
5.  组函数会自动滤空（即：忽略null）

sql优化原则：

1.  尽量使用列名，不要使用\*
2.  where解析顺序: 右 —> 左
3.  用到分组时，能用where尽量使用where