Oracle学习总结1

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

 

三、SQL优化的原则：

         1、尽量使用列名

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

         　　And连接 右侧放容易是假的

          　　Or连接 右侧放容易是真的

         3、尽量使用where而不是having

 

四、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;

 

Oracle学习总结1

标签：自连接   ini   经理   tcap   格式   comm   ace   imp   cot