Oracle相关知识点

一.oracle的命令
1.登录
sqlplus 用户名/密码@ip地址:1521/orcl [as sysdba]
2.查看当前连接数据库的用户：
使用show user查看当前的用户
3.用户的切换
conn 用户名/密码 [as sysdba]
4.查看用户下的表
set linesize 300; 每行展示300个字符
col 列名 for a[数字]，某一列占几个字符
5.查看表的结构
desc 表名；
6.修改sql中错误的单词
–c命令 change
例：select empno,ename,sal
form emp
2
c /form/from
/ (执行这条语句)
7.编辑sql语句 ed;
8.去掉重复
select distinct deptno,job from emp;
———disctinct 作用于后面所有的列
9.连接符 ” ||” concat
select concat (‘hello’ , ‘world’) from dual;
select ‘hello’ || ’ world’from dual;
–dual表 ：伪表
10.字符串
10.1字符串可以使select列表中的一个字符，数字，日期。
10.2日期和字符只能在单引号中出现，双引号表示列的别名。
10.3 每当返回一行时，字符串被输出一次。
11.清屏
host cls
12.设置页数
set pagesize 20;
二.过滤和排序
1.字符串大小写敏感
2.日期格式敏感 默认的日期格式 DD-MON-RR
3.修改日期格式：select *from v$nls_parameters;

    v$nls_parameters：数据字典。

alter session|system set NLS_DATE_FORMAT=’yyyy-mm-dd’;
4.模糊查询
“%”：可以匹配任意长度的内容；
“_”:可以匹配一个长度的内容。
转义字符：escape标识符
例：select *from emp where ename like ‘%_%’ escape ‘\’;
5.order by 作用于后面所有的列;desc 只作用于离他最近的列。
四.单行函数
1.substr(a,b) 从a中，第b位开始取
2.substr(a,b,c) 从a中，第b位开始取，取c位
3.length 字符数 lengthb 字节数
4.instr(a,b) 查找字符串b在a中的位置，返回索引值。
5.lpad 左填充 rpad 右填充
select lpad(‘abcd’,10,’&’) 左，rpad(‘abcd’,10,’&’) 右 from dual;

6.trim 去掉前后指定的字符
select trim(‘H’ from ‘‘Hello World’) from dual;
7.replace 替换
8.格式化时间
select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual;
9.months_between（sysdate,hiredate） 相差的月数
10.next_Day（sysdate，‘星期六’）
应用：每个星期一自动备份表中的数据。

11.滤空函数 nvl(comm,0）
nvl2(a,b,c) 当a=null 的时候，返回c，否则返回b;
nullif(a,b) 当a=b的时候，返回null;否则返回a.
coalesce（a,b,c…..） 从左到右知道到第一个不为null的值。
12.case表达式
select empno,ename,job,sal 涨前,
case job when ‘PRESIDENT’ then sal+1000
when ‘MANAGER’ then sal+500
else sal
+400
end 涨后
from emp;
13.decode函数
select empno,ename,job,sal 涨前，
decode(job,’president’，sal+1000，
‘manager’,sal+800,
sal+400) 涨后
from emp;

含义解释：
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　……
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

14.单行函数可以嵌套，嵌套函数的执行顺序是由内到外。
五.分组函数，又称多行函数
常用组函数 AVG， COUNT， MAX ，MIN ，SUM
使用GROUP BY 子句将表中的数据分成若干组，
先按照第一个列分组，再按照第二个列分组，以此类推。
**********1.在select列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中。
2.包含在GROUP BY子句中的列不必包含在select 列表中。

where和having的区别：where后面不能使用多行函数

3.group by 语句的增强：
group by rollup(a,b) =
group by a,b
+ group by a
+ 没有分组
break on deptno skip 2
break on null;
六.多表查询
0.表的别名
0.0使用别名可以简化查询
0.1使用表明前缀可以提高执行效率
0.2如果使用了表的别名，则不能再使用表的真名。
0.3连接N张表至少有N-1个连接条件。
1.等值连接
select empno,ename,sal,dname from emp,dept where emp.deptno=dept.deptno;
2.不等值连接
select e.empno,e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal
;
3.外连接
//希望：对于某些不成立的记录（40号部门），任然希望包含在最后的结果中
3.1 左外连接：当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含在最后的结果中
写法：where e.deptno=d.deptno(+)
3.2 右外连接：当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;
4.–自连接：通过表的别名，将同一张表视为多张表
select e.ename 员工姓名,b.ename 老板姓名
from emp e,emp b
where e.mgr=b.empno;
5.层次查询
自连接：不适合操作大表
select level,empno,ename,mgr
from emp
connect by prior empno=mgr
start with mgr is null
order by 1;

七.SQL优化的原则
1.尽量使用列名；
2. where解析的顺序：从右向左。
3.尽量使用where
4.尽量使用多表查询
5.当union和union all都可以时，尽量使用union all
union = union all +distinct
6.尽量不要使用集合运算
八.SQL中的null值
1. 包含null的表达式都为null
滤空函数 nvl(comm,0）
2. null 永远！= null
select * from emp where comm is null;
3.如果集合中含有null，不能使用not in；但可以使用in.
4.select * from emp order by comm desc nulls last;
5.组函数（多行函数）自动滤空；嵌套滤空函数nvl来屏蔽他的滤空功能