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';

[size=medium]用户管理[/size]

--创建用户
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;

[size=medium]表管理[/size]

--添加一个字段
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;(速度快，不记录日志)

[size=medium]表查询[/size]

--取消重复行
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;

[size=medium]多表查询[/size]

--显示雇员名字、部门名
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';

[size=medium]子查询[/size]

--查询和'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;

[size=medium]Oracle分页查询[/size]

--显示行号
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;

[size=medium]合并查询[/size]

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

[size=medium]Oracle常用函数[/size]

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

[size=medium]表空间[/size]

--创建表空间
create tablespace sp001 datafile 'd:\sp001.dbf' size 10m uniform size 128k;
--使用表空间
create table t1 (deptno number(2), name varchar2(20), loc varchar(20)) tablespace sp001;
--使表空间脱机
alter tablespace sp001 offline;
--使表空间联机
alter tablespace sp001 online;
--设置只读表空间
alter tablespace sp001 read only;
--可读可写
alter tablespace sp001 read write;
--删除表空间
drop tablespace sp001 including contents and datafiles;
--扩展表空间
  --1.增加数据文件
  alter tablespace sp001 add datafile 'd:\sp002.dbf' size 200m;
  --2.重新设置大小(不要超过500m)
  alter tablespace sp001 'd:\sp001.dbf' resize 200m;
  --3.设置文件的自动增长
  alter tablespace sp001 'd:\sp001.dbf' autoextend on next 10m maxsize 500m;

[size=medium]完整性约束[/size]

--添加not null约束
alter table goods modify goodsName not null;
--添加unique约束
alter table customer add constraint cardunique unique(cardId);
--添加check约束
alter table customer add constraint addresscheck check (address in ('海淀', '西城'));
--删除约束
alter table 表名 drop constraint 约束名称
--删除主键
alter table 表名 drop primary key cascade;

[size=medium]索引[/size]

--创建索引
  --单列索引
  create index 索引名 on 表名(列名);
  --复合索引
  create index 索引名 on 表名(列名1, 列名2);
  create index 索引名 on 表名(列名2, 列名1);