Oracle初步

--运行sql脚本
start d:\sql\test.sql;

--截取屏幕内容
spool d:\sql\test_bak.sql;
select * from emp;
spool off;

--导出表(要进到oracle安装目录下面的bin目录下面进行导出)
exp userid=scott/tiger@ORA1 tables=(emp) file=d:\emp_bak.dmp;
--导出表结构
exp userid=scott/tiger@ORA1 tables=(emp) file=d:\emp_bak.dmp rows=n;
--导出表结构
exp userid=scott/tiger@ORA1 tables=(emp) file=d:\emp_bak.dmp direct=y;

--导出方案
exp userid=scott/tiger@ORA1 owner=scott file=d:\emp_bak.dmp

--导出数据库
exp userid=system/manager@ORA1 full=y inctype=complete file=d:\ORA1.dmp

--导入表
imp userid=scott/tiger@ORA1 tables=(emp) file=d:\emp_bak.dmp;

--查看当前用户拥有哪些表
conn scott/tiger;
select table_name from user_tables;
--查看当前用户可以访问到的表
select table_name from all_tables;

--查看'SCOTT'具有什么角色
select * from dba_role_privs where grantee='SCOTT';
--查看一个角色包含的系统权限
select * from dba_sys_privs where grantee='CONNECT';
--查看一个角色包含的对象权限
select * from dba_tab_privs where grantee='CONNECT';

用户管理

--创建用户
conn system/apq;
create user bob identified by a123;
grant connect to bob;
grant resource to bob; --可以建表
--让bob可以查询scott的emp表
conn scott/tiger;
grant select on emp to bob;
--收回bob的select权限
revoke select on emp from bob;

--希望bob可以继续传递权限
conn scott/tiger;
grant select on emp to bob with grant option;
conn bob/a123;
grant select on scott.emp to lucy;
conn lucy/a123;
select * from scott.emp;

--密码输入错误超过三次锁两天
create profile lock_account limit failed_login_attempts 3 password_lock_time 1;
alter user lucy profile lock_account;
--解锁
alter user lucy account unlock;

--删除用户
drop user lucy cascade;

表管理

--添加一个字段   
alter table emp add(id number(2));   
--修改字段(不能有数据)   
alter table emp modify(id number(4));   
--删除一个字段   
alter table emp drop column id;   
--修改表的名字   
rename emp to employee;   
--删除表   
drop table emp;   
--改变日期默认格式   
alter session set nls_date_format='yyyy-mm-dd';   
insert into student(id, name, birthday) values(2, 'bob', '2001-01-02');   
--删除表数据   
delete from student;   
truncate table student;(速度快，不记录日志) 

表查询

--取消重复行   
select distinct deptno, job from emp;   
--查询emp表员工年工资   
select ename, sal, comm, sal*12+nvl(comm,0)*12 "年工资" from emp;   
--查询入职时间>1982-01-01的员工   
select ename, hiredate from emp where hiredate>'1-1月-1982';   
--低于平均工资的员工加上10%   
update emp set sal=sal+sal/10 where sal<(select avg(sal) from emp);   
--查询每个部门的平均工资   
select avg(sal), deptno from emp group by deptno;   
--查询平均工资大于2000按部门编号分组   
select avg(sal) average, deptno from emp group by deptno having avg(sal)>2000 order by avg(sal) desc;  

多表查询

--显示雇员名字、部门名
select a1.ename, a2.dname from emp a1, dept a2 where a1.deptno=a2.deptno;
--显示'FORD'的老板(自连接查询)
select worker.ename, boss.ename from emp worker, emp boss where worker.mgr=boss.empno and worker.ename='FORD';

子查询

--查询和'SMITH'部门相同的员工信息(单行子查询)
select * from emp where deptno=(select deptno from emp where ename='SMITH');
--查询和部门10工作相同的员工信息(多行子查询)
select * from emp where job in (select distinct job from emp where deptno=10);
--查询和'SMITH'部门相同并且工作也相同的员工信息
select * from emp where (deptno, job)=(select deptno, job from emp where ename='SMITH');
--查询高于自己部门平均工资的员工信息
select a1.ename, a1.sal, a1.deptno, a2.avgsal from emp a1, (select deptno, avg(sal) avgsal from emp group by deptno) a2 where a1.deptno=a2.deptno and a1.sal>a2.avgsal;
--使用子查询更新数据(希望'SCOTT'的job,sal和'SMITH'的一样)
update emp_bak set (job, sal) = (select job, sal from emp_bak where name='SMITH') where name='SCOTT';

--导表
create table emp_bak (id, name) as select empno, ename from emp;
--只导入部分数据
create table emp_bak (id number(6), name varchar2(20), deptno number(10));
insert into emp_bak (id, name, deptno) select empno, ename, deptno from emp where deptno=10;

Oracle分页查询

--显示行号   
select a1.*, rownum rn from (select * from emp) a1;   
--显示1~10条记录   
select a1.*, rownum rn from (select * from emp) a1 where rownum<=10;   
--显示5~10条记录   
select * from (select a1.*, rownum rn from (select * from emp) a1 where rownum<=10) where rn>=5;  

合并查询

--union(两个表重复的去掉)   
select ename, sal, job from emp where sal>2500 union select ename, sal, job from emp where job='MANAGER';   
--union all(不去掉重复的)   
--intersect(两个表都有的)   
--minus(用大的减小的)  

Oracle常用函数

--round四舍五入的取   
select round(comm, 2) from emp where ename='BOB';   
--floor 向下取整 55.01 55   
select floor(comm) from emp where ename='BOB';   
--ceil 向上取整 55.01 56   
select ceil(comm) from emp where ename='BOB';   
  
--to_char   
select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss'), to_char(sal, 'L99999.99') from emp;