OCP课程基础总结一

基本命令

PLSQL
在菜单help--support Info中可以看到 
1-TNS File的配置路径 D:\app\administrator\product\11.2.0\client_1\Network\Admin\tnsnames.ora
orcl = 
  (DESCRIPTION = 
      (ADDRESS_LIST  =
    (ADDRESS = (PROTOCOL  = TCP) (HOST =10.8.1.1) (PORT=1521)
  )
   (CONNECT_DATA = 
      (SERVICE_NAME = orcl)
 )
 )
2-安装plsql后还需要安装oracle的客户端配置tns，才可以用plsql登陆
3-固定tool--windows list的方法是，在window--save layout 
su  - oracle 
sqlplus / as sysdba
开启：startup mount;  关闭：shutdown  immediate;
sqlplus / as sysdba ---------以sys登陆          超级用户（sysdba）
alter user 用户名 account unlock; --------- 解除锁定(必须带“;”号)
alter user 用户名 identified 密码; -------------修改密码
alter database open; 开启数据库操作
alter user scott account unlock;
alter user scott identified by oracle_4U 
conn scott/oracle_4U;
show parameter name; 查看实例名称 
lsnrctl start;  登陆linux服务器开启监听
lsnrctl status;  查看监听状态
在PLSQL Developer这款工具里自带有查看sql历史的快捷键：ctrl+E
oracle有三个默认的用户名和密码~
1.用户名:sys密码:change_on_install
2.用户名:system密码:manager
3.用户名:scott密码:tiger

基本的select查询语句

select * from ename,(sal+200)*1.2 as salnew from emp; 通过别名
select * from emp where sal between 2000 and 3000; 通过between和and
select * from emp where sal in (2000,3000,5000); 通过in 锁定范围信息
select ename,sal from emp where ename like 'A%';  模式匹配
select ename,sal from emp where ename like '_A%'; like的模式匹配
select * from emp where comm is null;  使用null 条件
select * from emp where comm is not null; 使用null 条件
select ename,sal,deptno form emp where sal >1500 or ename like '%A%' or 
deptno in(20,30); 使用or运算符
select ename,sal from emp order by sal; 使用order by 子句可对检索进行排序，ASC 升序(默认是升序)  DESC 降序 
select ename,sal from emp order by sal desc; 降序来排列
select ename,sal,deptno from emp order by 3,2; 通过第三行，第二行来排序顺序
select  distinct deptno from emp; 使用distinct来去重 
select  count(distinct deptno) as new  from emp;
select deptno,job,avg(sal) from emp group bu deptno,job order by deptno; 分组并且按照deptno进行相关的排序

基本函数查询语句

select  lower('SQL COURSER') from dual; 将大写转换为小写
select upper('sql courser') from dual;          将小写转换为大写
select initcap('SQL Course') from dual;   将每个字母的第一个字母变成大写
select round(123.45678,2) test from dual;  取小数点后两位
select sum(sal),avg(sal),min(sal),max(sal) from emp;  总和最大平均最小
select count(*)  from emp; 查看所有的emp的数量大小 
select count(comm)  from emp; 查看emp中comm的个数是多少

条件查询语句

1-使用 case when 来判断刷选
select ename,deptno,sal case deptno when 10 then sal*1.1 when 20 then sal*1.2 else  sal end newsal from emp;   newsal作为新的资金储存  
2-使用 where 或者使用 having：   
WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。   
HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。
select deptno,job,max(sal) from emp group by deptno,job having max(sal) > 2000 order by max(sal) 
select deptno,job,max(sal) from emp where deptno=30 group by deptno,job;

使用联接显示多个表中的数据

内联接
自然连接是在两张表中寻找那些数据类型和列名都相同的字段，
然后自动地将他们连接起来，并返回所有符合条件按的结果
select * from emp;
select * from dept;
oracle使用using关键字
sql/92标准可以使用using关键字来简化连接查询，但是只是在查询满足下面两个条件时，才能使
用using关键字进行简化。
1.查询必须是等值连接。
2.等值连接中的列必须具有相同的名称和数据类型。
select  ename,deptno from dept natural join emp;
select ename,deptno from dept join emp using(deptno);
在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。
select e.ename,e.sal,d.deptno from emp e join dept d on (e.deptno=d.deptno)
select w.enam,m.ename from emp w join emp m on(w.mgr=m.empno);
外联接
Oracle  外连接(OUTER JOIN)包括以下：
左外连接(左边的表不加限制)
右外连接(右边的表不加限制)
全外连接(左右两表都不加限制)
create table deptnew as select * from emp;
insert into empnew(ename,deptno) values('HANFEI',50)
select e.ename,e.deptno from empnew e left join deptnew1 d on(e.deptno=d.deptno) ;左联接 左表empnew的值不变匹配右表合适的
select e.ename,e.deptno from empnew e right join deptnew d on(e.deptno=d.deptno); 右联接 右表deptnew的值不变匹配左表合适的
全联接 full join 
select e.ename,e.deptno,d.dname from empnew e  full  join deptnew d on(e.deptno=d.deptno); 全联接 
笛卡尔积  交叉联接 列数不变，行数相乘
select e.ename,d.deptno from dept d cross join emp e; 

使用子查询来解决查询

执行单行子查询·
select ename,sal from emp where sal>=(select sal from emp where ename='SCOTT');
select  ename,sal from emp where sal>=(select avg(sal) from emp);
select ename,sal,deptno from emp where sal>=(select avg(sal) from emp)  and deptno=(select deptno from emp where ename='SCOTT');
select deptno,min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=30);
 

使用子查询来解决查询

• 定义子查询
• 了解子查询的类型
• 编写单行和多行子查询

使用any运算符
>any大于查询出来的任一返回值（大于最小值）
<any小于查询出来的任一返回值（小于最大值）
=any和in相同
<>any和！=any相同，即返回不等于查询任一值
select ename,sal,deptno from emp where sal > any(select sal from emp where deptno=30);
select ename,sal,deptno from emp where sal < any(select sal from emp where deptno=30);
>all大于查询出来的最大返回值（大于最大值）
<ally小于查询出来的最小返回值（小于最小值）
=all等于查询结果中的所有值
<>和！=不等于查询结果中的所有值
select ename,sal,deptno from emp where sal<all(select sal from emp where deptno=30);
select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);
select e.name from emp e where e.empno not in (select m.empno from emp m);

使用集合运算符

• 描述集合运算符
• 使用集合运算符将多个查询组成一个查询
• 控制返回行的顺序

create table dept1 as select * from dept;
create table dept2(dno number(2),dnm varchar2(14),address varchar2(13));
insert into dept1 values(50,'TEACH','SHANCHAT')
Insert into dept2(dno,dnm,address)  select deptno,dname,loc from dept;
insert into dept2 values(60,'sale','beijing')
select * from dept1
使用Union 和 Union all 集合运算符
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
select deptno,dname from dept1 union select dno,dname from dept2;
select deptno,dname from dept1 union all select  dno,dnm from dept2;
使用intersect运算符
UNION 是联集，而 INTERSECT 是交集
select deptno,dname from dept1 intersect select dno,dnm from dept2;
使用minus模式是差集合的模式 
select deptno,dname from dept1 minus select dno,dnm from dept2;
select deptno,dname,loc from dept1 minus select dno,dnm,to_char(null)
from dept2;

处理数据

• 在表中插入行
• 更新表中的行
• 从表中删除行
• 控制事务处理

drop table  表名；
drop table  commodity；
注意：
    1.用drop删除表数据，不但会删除表中的数据，连表结构也被删除了！

truncate table 表名；
truncate  table commodity；
注意：
    1.用truncate删除表数据，只是删除表中的数据，表结构不会被删除！
    2.删除整个表的数据时,过程是系统一次性删除数据,效率比较高
    3.truncate删除释放空间

delete  表名；
delete  commodity；
注意：
    1.用delete删除表数据，只是删除表中的数据，表结构不会被删除！
    2.虽然也是删除整个表的数据,但是过程是系统一行一行地删,效率比truncate低
    3.delete删除是不释放空间的

区别：
TRUNCATE 只能对TABLE；    
DELETE可以是table和view

create table deptnew as select * from dept;
select * from deptnew;
insert into deptnew values(50,'TEACH,'SH');
insert into deptnew(deptno,loc,dname) values(60,'BJ','SALE');
insert into deptnew(deptno,dname) values(70,'abc')  带有空值的行或者是
insert into deptnew(deptno,dname,loc) values(80,'xyz',null) 或者这种方法
insert into empnew(ename,hiredate,sal) values('HANFEI',sysdate,2500);
从其他表中复制行
create table d30(name char(20),sal int); 表名为d30，列为name和sal 类型为char和int
insert into d30(name,sal) select ename,sal from emp where deptno=30;
更改表中的数据
update empnew  set sal=3000 where ename='SCOTT'; 注意区分大小写才可以
update empnew set deptno=(select deptno from empnew where ename='SCOTT'),sal=(select sal from empnew where ename='SCOTT') where ename='HANFEI'
select * from d30;
delete from d30 where sal=1250; 从表中删除行
delete d30 删除所有的行 
detele empnew where deptno=(selete deptno from empnew where ename='SCOTT');
selete * from empnew;
事务处理
使用plsql 中增删改，涉及到数据的操作，如果通过SQL window进行操作的，一定要加上commit;
才可以真正的写到库里面
通过 command windows 来进行操作 
create table test as select * from dept;
select * from test;
commit;
delete test  where deptno=40;
select * from test;
rollback;
select * from test;
commit;  
可以看到之前删除的表又恢复回来了
或者设立 savepoint点进行恢复
delete test where deptno=40;
select * from test;
savepoint a;
delete test where deptno=30;
select * from test;
savepoint b;
rollback to a;
select * from test;

数据库备份

• 从物理与逻辑的角度来分类：

从物理与逻辑的，备份可以分为物理备份和逻辑备份。
物理备份：对数据库操作系统的物理文件（数据文件，控制文件和日志文件）的备份。物理备份又可以分为脱机备份（冷备份）和联机备份（热备份），前者是在关闭数据库的时候进行的，后者是以归档日志的方式对运行的数据库进行备份。可以使用oracle的恢复管理器（RMAN）或操作系统命令进行数据库的物理备份。
逻辑备份：对数据库逻辑组件（如表和存储过程等数据库对象）的备份。逻辑备份的手段很多，如传统的EXP，数据泵（EXPDP），数据库闪回技术等第三方工具，都可以进行数据库的逻辑备份

• 从数据库的备份角度分类：

从数据库的备份角度，备份可以分为完全备份和增量备份和差异备份
完全备份：每次对数据库进行完整备份，当发生数据丢失的灾难时，完全备份无需依赖其他信息即可实现100%的数据恢复，其恢复时间最短且操作最方便。
增量备份：只有那些在上次完全备份或增量备份后被修改的文件才会被备份。优点是备份数据量小，需要的时间短，缺点是恢复的时候需要依赖以前备份记录，出问题的风险较大。
差异备份：备份那些自从上次完全备份之后被修改过的文件。从差异备份中恢复数据的时间较短，因此只需要两份数据---最后一次完整备份和最后一次差异备份，缺点是每次备份需要的时间较长。

• 备份方式

1、Rman(物理备份）：针对 数据库，表空间，数据文件，数据块，这种方案备份的速度很慢。通常在备份数据时候不建议备份在线日志文件，其他文件都可以备份
2、exp,expdp(逻辑备份）：针对 用户，数据库对象（表，分区...），这种备份方案对数据的完整性保证不是很好

• 备份命令

1-rman
一、首先做数据库的归档操作（只需归档一次）：
1、sqlplus sys/密码 as sysdba; 
2、关闭数据库，开启成mount模式： 
shutdown immediate; 
startup mount; 
3、开启日志归档，并且开库后启用自动归档： 
alter database archivelog; 
alter database open; 
alter system archive log start; 
4、可以查看归档路径：show parameter DB_RECOVERY_FILE_DEST; 
5、查看归档状态：archive log list 
6、归档状态中archivelog：有enable就说明设置成功 
7、Exit；退出sys模式 
8、默认的存档空间是2G； 
查看存档空间大小：show parameter db_recovery_file_dest; 
修改大小：alter system set db_recovery_file_dest_size=100G; 
二、注意：已经设置好归档后每次备份从这里开始。 
Rman； 
connect target sys/密码 
backup database; 
2-export
备份：export  用户名/密码 file=用户名.dmp  导出该用户下的所有表
恢复： import  用户名/密码 file=用户名.dmp full=y(表示全部导入）
 import  用户名/密码 file=用户名.dmp tables=tt(表示导入tt表）