oracle学习总结
1.创建表：
create table IT_EMPLOYEES(
EMPLOYEE_ID NUMERIC(6) NOT NULL UNIQUE,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(10),
JOB_ID VARCHAR2(10),
SALARY NUMBER(8,2),
MANAGER_ID NUMBER(6)
);
2.创建视图：
(1)
create view prog_employees_1
as
select employee_id,first_name,last_name,email,
phone_number,salary,manager_id from it_employees
where job_id='IT_PROG';
(2)
create view prog_employees_1
as
select employee_id,first_name,last_name,email,
phone_number,salary,manager_id from it_employees
where job_id='IT_PROG';
with check option;
（3）删除视图
drop view prog_employees;
3.创建索引
（1）Create 【unique】【cluster】INDEX<索引名> ON<表名>(<列名> <次序>)
例：
create index IT_LASTNAME ON IT_EMPLOYEES(LAST_NAME);
用户可以在查询频率最高的列上建立聚簇索引，由于聚簇索引是将索引和表记录放在一起存储，所以在一个基表上只能建立一个聚簇索引。在建立聚簇索引之后，由于更新索引列数据时会导致表中记录的物理顺序的变更，系统代价较高，因此对于经常更新的列不适宜建立聚簇索引。
（2）删除索引
Drop index <索引名>;
4.Alter
(1) 增加一列
alter table It_Employees add BIRTH_DATE DATE;
（2）修改字段
alter table It_Employees modify manager_id number(8);
（3）删除约束条件
alter table It_Employees drop unique(employee_id);
4.select / group by /having
注意：select子句后面只有两类表达式，统计函数和进行分组的列明。
Having 子句对分组的结果进一步筛选，having子句与分组有关，而where子句与单个行有关。
select deptno,avg(sal),sum(sal),max(sal),min(sal),count(sal) from emp group by deptno;
select deptno,avg(sal),sum(sal),max(sal),min(sal),count(sal),count(*) from emp group by deptno having avg(sal) > 2000;
5.内连接inner join /外连接（左left outer join/右外连接 right outer join/全外连接 full <outer> join ）
区别：内连接进行多表查询时，返回的查询结果集中仅仅包含符合查询条件（where 条件和having条件）和连接条件的行。内连接消除了与另一个表中的任何行不匹配的行，而外连接扩展了内连接的结果集，除返回所有匹配的行外，还返回不匹配的行。
select empno,ename,dname from emp inner join dept on emp.deptno = dept.deptno where job='SALESMAN';
1 7499 ALLEN SALES
2 7654 MARTIN SALES
3 7844 TURNER SALES
4 7521 WARD SALES
5 5555 lipeng OPERATIONS
测试数据：deptno为null的
insert into
emp values(6666,'lipeng','SALESMAN',6666,to_date('2013-04-02','yyyy/mm/dd'),5000.00,1000.00,null);
select empno,ename,dname from emp left outer join dept on emp.deptno = dept.deptno where job='SALESMAN'
1 5555 lipeng OPERATIONS
2 6666 lipeng
3 7499 ALLEN SALES
4 7521 WARD SALES
5 7654 MARTIN SALES
6 7844 TURNER SALES
从结果可以看出：做外连接不仅包含连接相匹配的行，而且还包含左表emp中所有满足where限制的行，而不论是否与右表相匹配。
5.union /union all/intersect/minus
union 将集合中的重复记录滤除，而union all包含两个子结果集重复的行。
select ename,SAL from emp where ename like 'S%' OR ename like 'J%'
union all
select ename,SAL from emp where ename like 'J%' OR ename like 'C%';
6.子查询in/exists/比较运算符
（1）select empno,ename,deptno
from emp where deptno in (select deptno from dept where loc='CHICAGO');
执行顺序：先执行括号内的子查询，然后将查询到的deptno结果跟emp中的deptno进行比较，若列值存在于这些返回值中，则外层查询结果会在结果集中显示该行。
（2）select empno,ename from emp where exists (
select * from dept where emp.deptno = dept.deptno and loc='CHICAGO'
);
（3）查询emp表，将将薪资大于本职位平均薪资的雇员信息显示出来。
select empno,ename,sal,job from emp
where job='SALESMAN' and sal >(
select avg(sal) from emp where job='SALESMAN'
);
7.数据操纵 INSERT/UPDATE/DELETE/TRUNCATE
update emp
set sal =
(select avg(sal) from emp where job='SALESMAN')
where empno=6666;
commit;
delete from emp where empno=5555;
DELETE/TRUNCATE区别：
如果确定要删除表中所有记录时，建议使用TRUNCATE，因为TRUNCATE删除数据时要比DELETE快的多。但是TRUNCATE删除数据后，不能用rollback来恢复数据，但是delete可以用。
Reuse storage/drop storage
Truncate table emp reuse storage;
Reuse storage表示删除记录后保存记录占用的空间。
drop storage 表示删除记录后立刻回收记录占用的空间。
8.授权grant/回收revoke
grant select on emp to sup2db;
revoke select on emp from sup2db;
9.字符类函数
select ASCII('A') big_A,ASCII('a') small_a from dual;
select CHR(65),CHR(97) from dual;
select concat('oracle','11g') oraclename from dual;
select initcap('hello world') name from dual;
select replace('feelblue','blue','yellow') from dual;
日期函数：
select add_months(hiredate,1) from emp;
第七章 oracle数据库管理操作
一．增加安全性
解决的问题：例如，销售经理需要访问数据库中有关该部门员工的信息。但是该经理没有理由访问有关其它部门员工的信息。
(1)以系统管理员的身份进行登录：
conn sys/change_in_stall as sysdba;
（2）激活人力资源示例数据库账户，连接到HR示例数据库
Alter user hr identified by hr account unlock;
Conn hr/hr;
查看hr用户下面的所有表：
（1）连接到hr用户
（2）Select table_name from user_tables;
或者使用 Select * from tab;
(3)为销售经理创建用户ID，用户名salesmanger，密码sales，并将connect权限授予销售经理。
(4) Conn sys/change_in_stall;
grant connect to salesmanger identified by sales;
（4）定义一个查看员工编号和姓名的视图，将数据库中员工等信息隐藏起来。
create view emp_sales
as
select employee_id,first_name,last_name from hr.employees;
}
(5)授予salesmanger查看emp_sales视图的权限
grant select on emp_sales to salesmanger;
（6）让销售经理以用户salesmanger登录数据库后，销售经理可以查看员工的编号和姓名，但是不允许查看员工的工资信息，这样在一定程度保证了数据的安全性。
conn salesmanger/sales
select * from sys.emp_sales;
select * from sys.emp_sales;
二．隐藏数据的复杂性
现有一机场数据库，拥有pilotSkills表和hanger表，
其中表pilotSkills描述了飞行员和他们能够驾驶的飞机信息，表hanger描述了停在飞机棚中的飞机信息。
现在要求查询能够驾驶飞机棚中每一架飞机的飞行员的姓名。
--创建pilotSkills表
create table pilotSkills(
pilot char(15) not null,
plane char(15) not null,
primary key(pilot,plane)
);
create table hanger(
plane char(15) primary key
);
创建视图，实现“找出能够驾驶飞机棚中每一架飞机的飞行员的姓名”
Create view QualifiedPilots(pilot)
As
Select ps.pilot from pilotSkills ps,hanger h
Where ps.plane = h.plane
Group by ps.pilot
Having count(ps.plane) = (select count(plane) from hanger);
select pilot from QualifiedPilots;
三、实现记录的唯一性
--增加主键
Alter table stu add constrait c1 primary key(sno);
--删除主键约束
Alter table stu drop constrait c1;
--创建一个名为ind1的唯一索引
Create unique index ind1 on emp(empno);
当插入一条重复数据的时候会报错。
四．实现数据的完整性
Alter table student modify sname not null;
第八章 数据库用户管理
1.创建一个用户使其具有登陆，连接的系统权限
create user stu identified by stu;
grant create session to stu;
2.用户HR将Employees表的查询、查询、更改表的对象的权限授予stu，那么stu具有了对HR的employees表的select对象权限，但不具备其它对象权限。
conn hr/hr;
grant select,insert,update on employees to stu;
select first_name,last_name,job_id,salary from hr.employees where salary>15000;
3.授权角色
数据字典dba_roles可以了解数据库中全部的角色信息。
select * from dba_roles;
角色connect、resource和DBA主要用于数据库管理。对于数据库管理员分别授予Connnect、resources和DBA角色。
创建角色:
create role access_database;
Create role access_database identified by 123;
(2) 授权
grant create session,create table,create view to access_database;
（3）可将角色授予用户，使用户获得该角色所拥有的所有权限。
grant access_database to scott;
4.修改用户的默认角色
（1）设置用户的角色失效
Alter user scott default role none;
用户角色失效后，该用户中的权限将全部丢失。用户连接数据库权限create session存储于ACCESS_DATEBASE中，当该角色失效后，用户scott就不能登录到数据库中。
（2）设置用户角色生效
Alter user scott default role all;
（3）查看session_roles视图，确认会话所用的角色
connect stu/stu;
select * from session_roles;
结果：
ROLE
------------------------------
CONNECT
RESOURCE
(4)为当前用户启用ACCESS_DATABASE角色。
Set role access_database;
5.回收权限
*逐一回收
Connect sys/change_in_stall as sysdba;
（1）系统权限的回收
收回scott用户的select any dictionary系统权限。
Revoke select any dictionary from scott;
（2）对象权限的回收
Hr用户回收scott对employees表的select对象权限。
Connect hr/hr
Revoke select on employees from scott;
用户HR将基表Employees的所有权限从public用户回收。
Revoke all on employees from public;
6.删除角色
Drop role access_database;
删除用户：
Drop user stu;
7.使用数据库连接
是为了访问远程数据库而创建的数据库通信链路。
链接到指定的用户：
CREATE DATABASE LINK link_name CONNECT TO USER IDENTIFIED BY password USING server_name;
link_name:表示要链接到远程数据库名。
Server_name：表示远程数据库的服务名。
数据库空间管理
1.设置其它表空间初值
例如学生信息库，要把有关学生信息的一些表放到一个表空间中，如果有2000多名学生，则存储学生个人信息最多也不超过100MB的空间，但是为了保险，我们可以设置两个数据文件，每个数据文件的大小均设置为100MB
create Tablespace student_info
datafile 'C:\oracle\product\10.2.0\oradata\orcl\student01.dbf'size 100M, 'C:\oracle\product\10.2.0\oradata\orcl\student02.dbf'size 100M
default storage(
initial 10M
next 10M
minextents 1
maxextents 10
pctincrease 20
)
online;
说明：
Storage 指定表空间的存储参数，这些参数对于数据库的性能影响很大，选着时要慎重。
initial 10M：--表空间student_information初始空间大小为10MB
next 10M： -- 当初始区间填满后，分配第二个区间的大小为10MB
pctincrease 20：--当在填满时，按照20%的增长速率分配区间大小。
minextents 1：-- 初始为该表空间分配1个区间
maxextents 10： --最多为该表空间分配10个区间
2.空间充足的管理
查看表空间使用情况，使用以下方法避免空间的不足：
（1）使用数据字典动态监视
这里使用的数据字典是dba_free_space和user_free_space,可以查看其内容来得到有关表空间的空间信息。
以system的身份登录：
select * from dba_free_space;
（2）向表空间增加数据文件
alter tablespace student_info
add datafile
'C:\oracle\product\10.2.0\oradata\orcl\student03.dbf'
size 2M;
注意：通过上述方法给表空间分配太多的空间并不好，因为这样做无疑造成较大空间的浪费。因此建议做好空间估计，并合理利用空间，无论是对减少资源浪费还是提高系统性能都有好处。
3.解决空间不足的方法
扩充数据库存储空间常用的方法有如下三种：
（1）增加SYSTEM表空间中数据文件的大小
（2）创建新的表空间
（3）创建新的数据文件
（1）增加SYSTEM表空间中数据文件的大小
数据库中的数据其实都是存储在数据文件中的，SYSTEM表空间的数据文件是在创建数据库的时候给定的，并且给其大小给定了一个初值。那么System表空间不够时就可以Alter DataBase命令动态的增加SYSTEM表空间数据文件的大小。
查11g
Alter DATABASE orcl
Datafile 'C:\oracle\product\10.2.0\oradata\orcl\system01.dbf'
resize 750M;
(2)创建新的表空间
表空间其实是一个逻辑概念，它所有数据和结构信息都存储在一个或者多个数据文件当中，当需要扩充数据库存储空间时，可以创建新的表空间并指定它的数据文件，系统就会划出一块磁盘空间给这个表空间.
注意：创建数据库时最好能创建几个私用的表空间，因为SYSTEM表空间是系统表空间，其中存储数据字典和数据库结构等重要信息，他是数据库运行的基础，若是把所有信息都存放在这个表空间里，一方面会迅速占满它的空间，另一方面也加大了出错的可能性。
1. Create tablespace 命令简介
Create tablespace 表空间名
Datafile{
文件名[autoextend {off | on next数值 maxsize数值}]，
}
Mininum extend 数值
Logging | nologging
Default storage{...}
Online | offline
Permanent | temporary;
2.用create tablespace创建表空间
create tablespace test
datafile 'C:\oracle\product\10.2.0\oradata\orcl\test01.dbf' size 2M
default storage(initial 2M
next 2M
minextents 2
maxextents 10
pctincrease 20
)
online;
（3）动态的增加表空间
1.Alter tablespace 命令简介
向表空间中增加数据文件使用的命令：
Alter tablespace 表空间名
Loggin | nologging
Add datafile{数据库文件名 [autoextend],...}
Rename datafile 原文件名 to 新文件名
Coalesce
Default storage
Mininum extend 数值
Online | offline [normal | temporary | immediate | for recover]
[begin | end] backup
Read only | write
Permanent | temporary
说明：
Add datafile：用于增加数据文件，可在联机或者是脱机下增加，但所增加的数据文件不能是其它表空间或者数据库已经使用的，它同样可带autoextend参数选项。
Coalesce：用于所有相连的空间范围合并到相邻较大的范围当中去，这一项不能被其它命令所指定。
[begin | end] backup:用于开始或者结束联机备份表空间的数据文件，在备份过程中用户可以继续访问该表空间，但备份过程中不能将表空间脱机，也不能关闭数据库。
Read only | write：其中Read only表示此表空间的内容是只读的，不能像其中写入任何数据，而read write则可以对此表空间的数据进行读写操作。
向表空间test中增加两个大小为10MB的数据文件
Alter tablespace test
Add datafile
'C:\oracle\product\10.2.0\oradata\orcl\test02.dbf size 10M' ;
