Oracle学习总结1--基础

一、 cmd下登录oracle服务器

 

sqlplus scott/tiger@//192.168.241.128:1521/orc

 

 

二、基本操作

　　1、解决sqlplus不支持中文的设置：

　　　　增加环境变量，如下:

　　　　设置NLS_LANG为SIMPLIFIED CHINESE_CHINA.ZHS16GBK

 

　　2、查看当前用户：show user

 

　　3、查看当前用户下的所有表：select * from tab;

 

　　4、查看表结构：desc emp

 

　　5、清屏：host cls

 

　　6、设置行宽：set linesize 120

        　　 查看行宽：show linesize

　　　　　设置每页行数：set pagesize 20

 

　　7、设置列宽：

　　　　SQL> col ename for a8   设置ename列为8个字符串大小

　　　　SQL> col sal for 9999      设置sal列为4为数字大小

 

　　8、修改语句错误：

　　　　a)先找到行号，b)使用c /要改的内容/改好的内容

　　　　b)用ed命令

 

　　9、 dual是伪表，当查询与所有表无关时候，用它

 

　　10、Oracle中的转义字符：需要自定义

　　　　select * from emp where ename like '%\_%' escape '\'

　　11、 修改默认日期格式

         （查看系统参数：select * from v$nls_parameters;）

 

         　alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

　　　　alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';

         　alter session set NLS_DATE_FORMAT='DD-MON-RR';（默认格式）

　　12、 根据条件修改报表。

　　　　原生case ... when …then..when..then..else..end

　　　　函数：decode

 　　

　　13、开启和关闭sql语句执行时间命令：set timing on（off）

　　14、开启和关闭输出显示：set serveroutput on（off）

三、SQL优化的原则：

         1、尽量使用列名

         2、where解析顺序: 右 ---> 左

         　　And连接 右侧放容易是假的

          　　Or连接 右侧放容易是真的

         3、尽量使用where而不是having

　　　4、子查询和多表查询都能实现，尽量使用多表查询

　　　5、尽量不要使用集合运算

 

四、SQL中的null规则

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

         2、null永远不等于null。要用 is

         3、如果集合中含有null，不能使用not in—必为空；但可以使用in—可筛选

         4、null在order by排序中，最大

         5、组函数会自动滤空；

五、关键字：

　　　　distinct：不重复

　　　　between and：前后都包含，小值在前，大值在后

　　　　in: 在集合中，注意，这里是集合，不是区间！

　　　　not in:

　　　　escape:定义转义字符

　　　　order by：根据某列排序（可以由数字指定列）

　　　　　　asc：默认升序

　　　　　　desc：降序

　　　　　　nulls last：忽略值为null的列的大小，放到最后

　　　　group by：

　　　　　　单列分组 select deptno,avg(sal) from emp group by deptno;

　　　　　　多列分组：select deptno,job,avg(sal) from emp group by deptno,job;（按组合分组）

                  多列分组规则：前面出现的列必须要由后面分组，

　　　　　　　　　　　　　　　　　　而后面分组的列，不一定前面都要查询

　　　　having：having子句用来筛选分组查询的结果，而且having 后一般跟聚合函数，不能是别名

　　　　rollup:      快速生成报表

                  以下三句的结果合成一句：

select depno,job,sum(sal)

from emp

group by depno,job

order by depno;

select deptno,sum(sal)

from emp

group by deptno

order by deptno;

select sum(sal)

 from emp;

 

合成：

select deptno,job,sum(sal)

from emp

group by rollup(deptno,job);

 

设置报表格式： deptno skip 2（表示deptno去重，每个deptno后隔两行）

取消格式设置：break on null;

 

六、函数

　　1、单行函数：

 

　　　　concat函数：可以||替代

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

　　　　select 'Hello'||'  World' 字符串 from dual;

 

　　　　lower('Hello World') 转小写

　　　　upper('Hello World') 转大写

　　　　initcap('hello world') 首字母大写

　　　　substr(a,b) 从a中，第b位开始取子串（角标从1计算）

　　　　length(‘北京’) 字符数 2

　　　　lengthb(‘北京’) 字节数4

　　　　instr(a,b) 在a中查找b第一次出现的位置

　　　　lpad(a,数量，填充字符)       左填充 lpad('abcd',10,'*') 这里的10指的总字节数。

　　　　rpad(a,数量，填充字符)       右填充 rpad('abcd',10,'*')

　　　　trim('H' from 'Hello WorldH')去掉前后指定的字符

　　　　replace('Hello World','H','h')

　　　　round(45.926,2) 四舍五入

　　　　trunc(45.926,2) 截断

　　　　sysdate：当前时间

         　　　　昨天，今天，明天的表示sysdate加减1

　　　　to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')  日期按某格式转化为字符串

　　　　to_date('2017-6-9','yyyy-dd-mm')

　　　　　　互相转换

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

　　　　　　select to_date('2017-6-9 10:42:15今天是星期五','yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;

 

　　　　months_between(sysdate,hiredate) 相隔多少月 （后，前）

　　　　add_months (sysdate,53)

　　　　last_day(sysdate) 本月最后一天的日期

　　　　next_day(sysdate,'星期四') 下一个星期四的日期

 

　　　　nvl(a,b) （滤空）(判断是否为空的列名,如果为空的设置的值)

　　　　nvl2(a,b,c) 当a=null的时候，返回c；否则返回b

 

　　　　nullif当a=b的时候，返回null；否则返回a

　　　　coalesce(comm,sal)从左到右 找到第一个不为null的值

 

　　　　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;

 

2、多行函数：（组函数）

　　count

　　sum

　　avg

 

七、查询

　　1、多表查询

　　　　笛卡尔积：两表相乘

　　　　

　　　　等值连接:

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

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

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

select d.deptno,d.dname,count(e.empno)

from dept d,emp e

where d.deptno=e.deptno(+)

group by d.deptno,d.dname

order by d.deptno

　　　　

　　　　 自连接: 通过表的别名，将同一张表视为多张表

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

select e1.ename,e2.ename

from emp e1,emp e2

where e1.mgr=e2.empno;

 

　　　　  补充：--层次查询，自连接不适合大表查询，因为笛卡尔积太大

 

select level,empno,ename,mgr

from emp

connect by prior empno=mgr

start with mgr is null

order by level;

 

　　　　2、子查询

　　　　　　子查询所要解决的问题：不能一步求解

　　　　　　（1）查询工资比SCOTT高的员工信息

　　　　　　　　子查询：

select *
from emp
where sal>(select sal
           from emp
           where ename='SCOTT')

　　　　　　　　不用子查询：

select e1.*
from emp e1,emp e2
where e1.sal>e2.sal and e2.ename='SCOTT'

 　　　　子查询规律总结：

1、括号
2、合理的书写风格
3、可以在主查询的where select having from 后面使用子查询
4、不可以在group by使用子查询
5、强调from后面的子查询
6、主查询和子查询可以不是同一张表；只有子查询返回的结果 主查询可以使用 即可
7、一般不在子查询中排序；但在top-n分析问题中 必须对子查询排序
8、一般先执行子查询，再执行主查询；但相关子查询例外
9、单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
10、子查询中的null

 

　　　　　　select后面使用子查询

select empno,ename,sal,(select comm
                        from emp
                        where ename='SCOTT') everybodyComm
from emp;

　　　　　　where后面的子查询：单列

　　　　　　（2）--查询部门名称是SALES的员工

　　　　　　子查询：

select *
from emp
where deptno=(select deptno
              from dept
              where dname='SALES')

　　　　　　多表查询：

select e.*
from emp e,dept d
where e.deptno=d.deptno and d.dname='SALES'

　　　　　　总结：SQL优化3--子查询和多表查询都能实现，尽量使用多表查询

　　　　where后面的子查询：多列（in,any,all）

　　　　　　（3）--查询部门名称是SALES和ACCOUNTING的员工

　　　　　　子查询：

select *
from emp
where deptno in(select deptno
                   from dept
                   where dname='SALES' or dname='ACCOUNTING');

　　　　　　多表查询：

select e.*
from emp e,dept d
where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING')

　　　　　　（4）--查询工资比30号部门任意一个员工高的员工信息

select *
from emp
where sal>any(select sal from emp where deptno=30);

或者

select *
from emp
where sal>(select min(sal) from emp where deptno=30)

 

　　　　　　（5）--查询工资比30号部门所有员工高的员工信息

select *
from emp
where sal>all(select sal from emp where deptno=30)

或者

select *
from emp
where sal>(select max(sal) from emp where deptno=30);

　　　　多行子查询中的null处理：

　　　　　　（6）--查询不是老板的员工信息**

select *
from emp
where empno not in(select mgr from emp where mgr is not null)

 

　　　　from后面的子查询，看做单独的一张表

 　　　　　　（7）--找到员工表中最高的前三名（伪列：rownum）

　　　　　　

引入：发现rownum是在引用表的时候，序列已定的。

select *
from emp
order by sal desc

导出正序的序列。

select rownum,empno,ename,job,sal
from (select * from emp order by sal desc);

取前三个：

select rownum,empno,ename,job,sal
from (select * from emp order by sal desc)
where rownum<=3

 

rownum 延伸，oracle分页
select *
from(select rownum r,el.*
    from (select * from emp order by sal) el
    where r<=8)
where r>=5;


注意rownum伪列的特点是：自己家的rownum只可以做< <=操作
                                        查出来的别人家的rownum才可以做> >=操作

 

 

　　　　　　（8）找出薪水大于本部门平均薪水的员工

关键在于每个员工比较的平均工资不同，引入第二张表：平均工资表，多表查询

select *
from emp,(select deptno,avg(sal) avgsal from emp group by deptno) da
where emp.deptno=da.deptno and emp.sal>da.avgsal

相关子查询：
select *
from emp e
where e.sal>(select avg(sal) from emp where deptno=e.deptno);

 

　　　　　　（9）查询员工总数，每年员工入职人数

关键在于这句
select sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) from emp;

全部：
select count(*),sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
from emp

注意，数字别名必须用双引号，单引号不行

 

　　　　　　（10）行转列：wm_concat

select deptno,wm_concat(empno) empnolist,wm_concat(ename) enamelist
from emp
group by deptno

col empnolist for a30
col enamelist for a60

八 集合运算

　　注意的问题：
　　　　1、参与运算的各个集合必须列数相同 且类型一致
　　　　2、采用第一个集合作为最后的表头
　　　　3、order by永远在最后
　　　　4、括号

　　可以用to_num(null) to_char(null)等去补齐列数

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

这句可以由下面多句合成

select deptno,job,sum(sal) from emp group by deptno,job
union
select deptno,to_char(null),sum(sal) from emp group by deptno
union
select to_number(null),to_char(null),sum(sal) from emp;

　

　　SQL优化 4、 尽量不要使用集合运算