Oracle复习笔记(四)
，事务提交后，对数据库的影响是永久的，无法回滚
Oracle中的事务
1）1次连接=1次会话session=1个事务
2）一旦遇到commit或rollback，则提交或回滚该会话中所有未提交的操作
3）对于DDL语句（create/alter/drop），执行成功后相当于执行了commit
--事务提交
commit;
--设置事务回滚点
savepoint p1;
--事务回滚
rollback;
rollback to p1;
--删除表所有数据(不允许回滚)
truncate table tb;
/*----------------------------------------行级锁----------------------------------------*/
1）Oracle自动为 insert/delete/update/select...for update 操作应用行级锁
一旦数据被锁住,就不允许其他会话进行操作,直到commit或rollback.
2）用于锁定查询结果集 select * from emp for update;
3）用于检测数据是否被锁定,以及限制用户等待的时间.
select * from emp for update nowait; --不等待
select * from emp for update wait 10; --等待10S
/*----------------------------------------表空间操作----------------------------------------*/
--授权创建表空间系系统权限
grant create tablespace to test2;
--创建表空间
create tablespace myspace
datafile 'd:\mydata.dbf'
size 20m
autoextend on
next 3m
maxsize 100m;
--扩展表空间
alter tablespace myspace add datafile 'd:\mydata2.dbf' size 30m;
alter database datafile 'd:\mydata.dbf' resize 35m;
alter database datafile 'd:\mydata2.dbf' autoextend on next 3m maxsize 100m;
--删除表空间(including contents and datafiles物理文件会一起删除)
drop tablespace myspace including contents and datafiles;
/*----------------------------------------并集、交集、减集----------------------------------------*/
/*union并集(或的关系,不包含重复记录),union all包含重复记录 */
select * from emp where sal > 2500 union select * from emp where job = 'MANAGER';
/*intersect交集(与的关系)*/
select * from emp where sal > 2500 intersect select * from emp where job = 'MANAGER';
/*munus减集 */
select * from emp where sal >
2500 minus select * from emp where ename = 'MANAGER';
/*----------------------------------------常用函数----------------------------------------*/
--分组函数：针对每组数据返回1个结果
select count(*) 总人数,max(sal) 最高工资,min(sal) 最低工资,sum(sal) 工资总和,avg(sal) 平均工资 from emp;
select count(*) 公司总人数,count(mgr) 有上级的人数 from emp;
select deptno,avg(sal) ageSal from emp group by deptno having avg(sal)>=2000 order by avg(sal) desc;
--日期函数：针对每行数据返回1个结果
select to_date('1-5月-13') - sysdate 天 from dual;
select ename,months_between(sysdate,hiredate)/12 "工龄(年)" from emp;
select ename,hiredate 入职时间,add_months(hiredate,3) 转正日期 from emp;
select * from emp where hiredate = last_day(hiredate);
select * from emp where extract(month from hiredate) = 5;
--字符函数
select length('oracle') from dual;
select lengthb('你好') from dual;
select substr('hello@sina.com',7,4) from dual;
select instr('oracle','a') from dual;
select substr('&email',1,instr('&email','@')-1) 用户名 from dual;
select replace('ddl','dl','ml') from dual;
select length('abcabca') - length(replace('abcabca','a')) from dual;
select chr(65) from dual;
select ascii('A') from dual;
select lpad('A',5,0),rpad('A',5,0) from dual;
--数学函数
select ceil(9.1) from dual;
select floor(9.1) from dual;
select round(1.5),round(1.55,1),round(155,-1) from dual;
select trunc(155.55) from dual;
--分析函数(用于数据的排名统计)
select ename,sal,
row_number() over(order by sal desc) row_number,
rank() over(order by sal desc) rank,
dense_rank() over(order by sal desc) dense_rank
from emp;
--转换函数
select to_char(sysdate,'yyyy-MM-dd hh24:mm:ss day') from dual;
select to_char(sysdate,'yyyy"年"fmmm"月"dd"日"') from dual;
select to_date('2013-5-1 15:33:40','yyyy-mm-dd hh24:mi:ss') from dual;
select to_number('00012