oracle视频学习笔记

 ORACLE 好的学习资料_网友摘录
ORACLE PL—SQL ： http://www.cnblogs.com/huyong/tag/ORACLE/
 
学习资料:http://www.cnblogs.com/huyong/archive/2012/07/30/2614563.html
 
Oracle的[物理结构]主要有三种文件。
.dbf数据文件，是用于存储数据库数据的文件，例如表中的记录，索引，数据字典信息等，可以通过系统数据字典
DBA_DATA_FILES查看相关信息。与逻辑角度的表空间（并不是真正的文件）对应，一个表空间可以有多个数据文件，
但一个数据文件只能属于一个表空间。
.log重做日志文件，用于记录对数据库的修改信息（查询操作不会产生）。日志文件是备份与恢复的重要手段。
.ctl控制文件，用于描述数据库的物理结构。存放有数据文件和日志文件等信息。
.ora参数文件，记录数据库名，控制文件路径、进程等信息。
上面种文件任意一种出错，数据库都可能不能正常运行。参数文件可以修改数据库某些参数，但是需要重启数据库才生效。
 
  安装oracle:(共有216个组件)
  oracle安装会自动地生成两个 sys用户和system用户:
  1): sys 用户是超级用户, 具有最高的权限,具有sysdba角色, 有create  database的权限;
      该用户默认的密码是change_on_install;
  2)：system 用户是管理操作员,权限很大，具有sysoper角色，没有create database的权限;
      该用户的默认密码是manager;
  3): 一般来讲,对数据库维护,使用system用户登录就可以了.
   
     启动Oracle时启动 oracle数据库实例，单个数据库(eg:ORACL)
     Oracle数据对象
  
   4) oracle管理工具介绍:
      oracle的登录:cmd--->sqlplusw (界面一) sqlplus/nolog
      oracle的登录:cmd--->sqlplus  (dos界面)
    5) pl/sql developer 属于第三方软件,主要用于开发,测试,优化oralce pl/sql
      的存储过程比如:触发器,此软件oracle不带,需要单独安装;
   
    6) 链接命令:
       conn 用法,conn 用户名/密码@网络服务名 【as sysdba/sysoper】
       当用特殊用户身份连接时候,必须带上as sysdba 或是as sysoper
    7)scoot用户管理四张表 bouns dept emp salgrade
    8) pl/sql developer对于oracle 如myeclipse对于java
    9) oracle取出数据相对较繁琐,没有top函数,没有自动增长列
  
 
 show user;(显示当前用户名)
 conn / as sysdba;
 conn system/manager
 conn scott/tiger
 conn sys/change_on_install
 修改密码: passw 回车 --->输入旧密码---->输入新密码
 exit; clear screen;
 例如在d盘有一个 aa.sql文件;
 start d:\aa.sql; (运行外部脚本)
 edit d:\aa.sql; (编辑外部脚本)
 spool 说明: 该命令可以将sql*plus屏幕上的内容输出到指定文件中去
 spool d:\aa.sql 并且输入spool off
 ***很重要知识点(截取屏幕上的内容):
  (1) spool d:\aa.sql;
  (2)  select * from inf;
  (3) spool off;
select *  from emp where ename='&name';
set linesize 120; //设置每行最多可以显示的字符,默认是80;
set pagesize 120;  //设置每页显示的行目数,默认是14(分页显示数据,方便打印报表)
create user rufus identified by rufus_123; (由有权限的用户创建!)
密码只能是以字母开头;
系统管理员修改别人密码 ---->password 用户; ---->新密码
drop 用户;
在删除用户的时候，如果要删除的用户已经创建了表,那么需要在删除时候,带一个参数cascade
drop rufus cascade;
你这是写的什么啊，删除的用户还可以被赋权限啊 
drop user user1cascade; 
grant connect,resource,dba to user1；
可以这样删除：
drop tablespace XXXX INCLUDING CONTENTS; 
drop user XXXX cascade;
新用户被创建的时候是孤零零的,没有任何权限,甚至连登陆数据库的权限都没有,需要为其指定相应
的权限,给一个用户赋予权限用grant,回收权限revoke; (system,sys,dba)
grant conn to rufus;
grant resource to rufus;
grant dba to rufus;
用户 != 角色;
conn system/manager = conn / as sysdba;

权限:
把权限赋给角色/把权限赋给用户,把角色赋给用户;
系统权限: 所谓系统权限,用户对数据库的相关权限(connect  resource dba);
对象权限: 用户对其他用户的数据对象操作的权限(select insert update delete all);
grant select on emp to rufus; (system, dba, scott, sys 给别人授权)
select * from scott.emp;(查看别人的表,要指明表是哪一个用户的表)称为:方案;
select * from emp;(出错,因为自己没有这张表,是别人赋予你查的表,要指明主人)

Oracle 的组织单位是用户,一个数据库多个用户,用户的关系自主联系(很符合公司的人际关系网);
grant update on emp to rufus; (system, dba, scott, sys 给别人授权)
grant all on emp to rufus; (对该表的所有的对象权限都赋给Rufus)
收回所赋予出去的权限, revoke select on emp from rufus;

权限的传递 ===对权限的维护;
scott给Rufus 对象权限, 希望rufus可以把这个权限继续给别的用户:
grant select on emp to rufus with grant option; (给grant系统权限同时赋予)
grant select on scott.emp to alice with grant option;

如果是系统权限时候:
system给Rufus权限时候:
grant connect to rufus with admin option;
如果 scott-->rufus--->alice; 如果Scott收回给Rufus的权限会出现的情况?  ///(受到株连)
///

使用profile管理用户口令:
概述:profile是口令限制,资源限制的命令集合,当建立数据库时候,oracle会自动建立名为default
的profile.当建立用户没有指定profile选项,那么Oracle就会将default分配给用户;
1) 账号锁定:
指定该账号登录时最多可以输入密码的次数,也可以指定用户锁定的时间(天)一般用dba的身份去执行该命令
 eg: 指定tea用户最多可以试登录3次，锁定时间为2天:
2)创建一个规则loke_accout;
 create profile loke_accout limit failed_login_attemps 3 password_lock_time 2;
3)该loke_accout规程对哪个用户作用;
 alter user tea profile lock_accout;
4) 被锁定的用户解锁
 alter user tea account unlock;  (一般以dba身份)

a)终止口令
  为了让用户定期修改密码可以使用终止口令的指令来完成,同样这个命令也需要
  dba身份来操作;
  eg: 给前面创建的用户tea创建一个Profile文件，要求用户每隔10天要修改自己
  的登录密码,宽限为2天;
create profile myprofile limit password_life_time 10 password_grace_time 2;
alter user tea profile myprofile;
 

  三个系统内置的角色,三个系统内置的角色名,他们不具备对象权限:
  connect  --连接
  resource --不受限制使用DB的资源
  dba      --管理
  set wrap off  --设置不转行
  l 或 list 显示缓存区的SQL语句
  / --表示执行缓存区的SQL语句

--创建表空间
create tablespace wh datafile 'E:\oracle\product\10.1.0\oradata\orcl\wh01.dbf'
size 10m autoextend on maxsize 100m;
--向表空间添加数组文件
 alter tablespace wh add datafile 'E:\oracle\product\10.1.0\oradata\orcl\wh02.dbf'
 size 5m;
--如何创建临时表空间
 create temporary tablespace mytemp tempfile 
'E:\oracle\product\10.1.0\oradata\orcl\mytemp01.dbf'
size 10m autoextend on maxsize 100m;
select to_char(sysdate,'yyyy-mm-dd hh24:mi;ss') from dual;
to_char 函数带两个参数;
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
oracle 表的管理:
 char  对于指导定长的就用这个,访问效率高
 varchat2(20)    变长,最大字符数4000字符
 clob(character large object) 字符型大对象,最大为4G;
 时间(效率)与效率不可调和
 数字型:
 number 表示任意大小;
 number(5,2) 表示一个小数有5个有效数字,2位小数
 范围-999.99 -- 999.99
 number(5) //数字类型就一个,很方便记忆
 表示一个五位整数,范围-99999-99999
 date 包括年月日和时分秒
 timestamp这是oracle9i对date数据类型的扩展
 blob 二进制数据 可以存放图片/声音 4
 
create table student(
xh number(4),  --学号
xm varchar2(20), --姓名
sex char(2),   ---性别，为了效率就用char,不是男就是女
birthday date,  ---出生日期
sal number(7,2) ); ---奖学
create table student(
xh number(4),  
xm varchar2(20), 
sex char(2),  
birthday date,  
sal number(7,2) );

ORACLE中数据字典视图分为3大类,     用前缀区别，分别为：USER，ALL 和 DBA，许多数据字典视图包含相似的信息。
USER_*:有关用户所拥有的对象信息，即用户自己创建的对象信息
ALL_*：有关用户可以访问的对象的信息，即用户自己创建的对象的信息加上其他用户创建的对象但该用户有权访问的信息
DBA_*：有关整个数据库中对象的信息 

*******最频繁的语句: show user /desc emp;
查看当前用户拥有哪些数据表: select table_name from user_tables;
查看数据库中某个用户拥有哪些表: select table_name from all_tables where owner='SCOTT';
查看某个用户中的具体一张表中所有字段需要all_tab_columns
select table_name,column_name,data_type from all_tab_columns where owner='SCOTT' and
table_name='DEPT';  =desc dept;
建好的表如何修改:
 添加一个字段:
 alter table student add(classid number(2));
 修改字段的长度:
 alter table student modify(xm varchar2(30));
 修改字段的类型/或是名字(不能是数据)
 alter table student modify(xm char(30));
 删除一个字段
 
 alter table student drop column sal;
 修改表的名字:
 rename student to stu;
 删除表
 drop table student;
 oracle中默认的日期格式 'DD-MON-YY' dd 日子 mon月子 yy(两位的) 年
 '09-8月-99' (很奇怪,'月'必须带上)
 当然可以通过修改数据字典表,改变默认的日期格式
 alter session set nls_date_format ='yyyy-mm-dd';
 =native Language Support，本地语言支持
 修改后,可以使用我们熟悉的格式添加日期类型:
 insert into student values('A002','mike','male','1989-05-25',10);
 插入为空的值 null;
 查看某个字段为空的所有记录数;
 select * from student where bithday is null;
 select * from student where bithday is not null;
 update student set sex='female' where xid='a001';
 
 update student set name='rufus', birthday='1989-04-25' where xid='a002';
 把所有男性工人工作变为一半;
 update student set sal=sal/2 where sex ='male';
 update student set sal=sal/2 where sex is null;
 删除数据
 delete from student;删除所有数据,表结构还在,写日志,可以恢复,速度慢
 // savepoint aa ---->delete from student--->rollback to aa;
 drop table student; 删除表的结构和数据(表的结构都没了,肯定无法恢复);
 delete from student where xh='a001'; 删除一条指定数据;
 
 truncate table student; 删除表中所有记录,表结构还在,不写日志,无法找回删除的记录,速度慢
 日志是数据库的内部文件,在后台默默无闻的工作,我们善于利用它就可以了,它如何工作不用管
 
 

 每次会话是有默认的时间的:
 desc dept;
 select * from dept;
 select ename, sal, job, deptno from emp;
 select distinct deptno, job from emp;
 set timing on; //打开显示操作时间开关
 指数倍增长赋值
 insert into users(userid,username,userpass) select * from users;
 orcle对引号内的内容区分大小写;
 使用算术表达式
 使用列的别名
 select ename "姓名", sal*12 as "年收入" from emp;
 select sal*12 + comm*13 "年工资",ename,comm from emp;
 select sal*12 + nvl(comm,0)*13 "年工资",ename,comm from emp;
 参与计算的字段有一个为空结果就为空(如何解决此问题?)
 如何处理null值
 使用nvl函数还处理
 nvl(comm,0) //如果从数据库中查出comm为null,就把0赋值给该字段,不为null该是什么就是什么;
 如何连接字符串:
 select ename || 'is a' || job from emp;
------------------------------------------------
考点知识:
 nvl 函数是用于将空值转换为一个替换值的,由于空值自身的特点不能参与运算
 用nvl可以对空值处理;
 nvl(expr1,expr2);
 nvl(expr1,expr2,expr); //功能和三目运算法一样
 如何查看1982后入职的员工?(时间格式的数据是可以比较大小的,默认的时间格式)
 select ename,hiredate from emp where hiredate>'01-1月-1982';
 
  SQL> select sysdate from dual;
  SYSDATE
  --------------
  11-10月-11
  记住默认的时间格式就是了,日月年,从小到大,每个两位;
  select ename emp where sal>=2000 and sal<=2500;
  select ename from emp where sal between 2000 and 2500;
  select ename,sal from emp where ename like 'S%';
 
  select ename,sal from emp where ename like '__o%'; (统配任意一个字符)
  select ename,sal from emp where empno in(123,234);
  显示没有上级的人员(其实质训练如何寻找某个字段为空的数据记录)
  select * from emp where mgr is null; (mgr为上级管理人员)
  万万不可写 select * from emp where mgr=null;
   使用逻辑符号:
  查询工资高于500或是岗位为manager的雇员,同时还要满足他们的姓名首写字母大写为J;
  select * from emp where (sal>500 or job ='manager') and ename like 'J%';
   由or组合的条件我们往往都可以用(),来组装条件;
  select * from emp sal order by sal desc/asc(默认的);
  按部门编号的降序，销售升序排列
  select * from emp order by deptno (asc), sal desc; (多个条件用逗号排序)
   按照别名排序
  select ename,sal*12 "年薪" from emp oder by "年薪" asc;
   数据分组------>max,min,avg,sum,count;
   查询销售量最大的人的信息(子查询);由里而外的执行顺序;
   select ename ,sal from emp where sal=(select max(sal) from emp);
   不分组，普通查询和聚合查询不能一起查询.
   select 1 ,ename from emp;
   分组的字段一定要出现在查询字段名汇总(不然分组没有意思);
   select avg(sal),max(sal),deptno from emp group by deptno;
  对分组的结果筛选having;
  select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;
  对数据分组的总结:
  分组函数只能出现在选择列表,having,order by 子句中;
 如果在select 语句中同时包含group by,having,order by 顺序出现group by,having,order by
 select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal);
 
 

在操作数据库的时候我们常常会遇到这样的问题，表建好了，突然发现不完整:
要添加列，要修改列，要删除列: 如何做:

如何修改已经建好的列: aleter table mytable modify(address varchar2(50));
如何添加新的一列: alter table mytable add(phone varchar(20));
如何删除旧的一列: alter table mytable drop column phone;
如何删除表中的所用插入的数据:truncate table mytable;
oracle 中利用现有的表创建新表：
create table newmytable as select * from mytable ;
以下代利用现有的表创建新表,没有任何记录.
create table newmytable as select * from mytable where 1=2;
在sqlserver中利用旧表创建新表:
select * into newtable from oldtable;
如果不想导入记录在,只想生成表的结构:
select * into newtable from oldtable where 1=2;

看用户建立的表   : 
select table_name from user_tables;  //当前用户的表 , 当前用户的前缀——user_ 
   
select table_name from all_tables;  //所有用户的表  ,
   
select table_name from dba_tables;  //包括系统表
用户如何有效的利用oracle数据字典：
数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等
 
下面按类别列出一些ORACLE用户常用数据字典的查询使用方法:
用户:
查看当前用户的缺省的表空间:
select username,default_tablespace from user_users;
查看当前用户的角色
select * from user_role_privs;
查看当前用户的系统权限和表级权限
select * from user_sys_privs;
select * from user_tab_privs;
参看当前用户创建的序列号
select * from user_sequences;
查看当前用户所创建的序列名
select sequence_name from user_sequences;

数据字典视图主要可分为三类
dba - 所有方案包含的对象信息
all - 用户可以访问的对象信息
user -用户方案的对象信息
举例
--查看当前用户拥有的所有表的名字
select table_name from user_tables;
--查看当前用户可以访问的所有表的名字
select table_name from all_tables;
--查看当前用户拥有的所有对象的类型
select distinct object_type from user_objects;
--查看所有用户拥有的所有对象的类型
select table_name from dba_tables;
===========================================================================

group by 有一个原则，就是select后面的所用列中，没有使用聚合函数的
列，必须出现在group by 后面(重要)(就如三垂线定理之于立体几何);
例如:
  A     B
  1     abc
  1     bcd
  1     asdfg

一般的错误就是如下查询语句:
select A,B from table group by A;

  A     B
       abc
  1     bcd
       asdfg
右边3条如何变成一条，所以需要用到聚合函数，如下正确做法:
 select A,count(B) as 数量 from table group by A;
 得到的结果就是：
 A    数量
 1    3
所谓分组:依据条件分组，条件列出，不相关的聚合;
having
 
where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉
即在分组之前过滤数据，条件不能包含聚组函数，使用where条件显示特定的行；

在介绍GROUP BY 和 HAVING 子句前，我们必需先讲讲sql语言中一种特殊的函数：
聚合函数，例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们
一般作用在多条记录上。
SELECT SUM(population) FROM bbc
这里的SUM作用在所有返回记录的population字段上，结果就是该查询只返回
一个结果，即所有国家的总人口数。通过使用GROUP BY 子句，可以让SUM 和 COUNT
 这些函数对属于一组的数据起作用。当你指定 GROUP BY region 时， 属于同一个
region（地区）的一组数据将只能返回一行值，也就是说，表中所有除region（地区）
外的字段，只能通过 SUM, COUNT等聚合函数运算后返回一个值。
HAVING子句可以让我们筛选成组后的各组数据，WHERE子句在聚合前先筛选记录．
也就是说作用在GROUP BY 子句和HAVING子句前．而 HAVING子句在聚合后对组记录进行筛选。
让我们还是通过具体的实例来理解GROUP BY 和 HAVING 子句，还采用第三节介绍的bbc表。
　　SQL实例：
 
SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region

先以region把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用
聚合函数对每组中的不同字段（一或多条记录）作运算。
显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。

SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000
在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。
相反，HAVING子句可以让我们筛选成组后的各组数据．

group by 有一个原则，就是select后面的所用列中，没有使用聚合函数的
列，必须出现在group by 后面(重要)(就如三垂线定理之于立体几何);
(对组内的非分组依据字段进行聚合，只有这样才能保证每组的结果的唯一性);
 

笛卡尔积(全连接 排列组合);
多表查询的条件是 至少不能少于表的个数 -1;
多表为了书写方便往往为表起一个别名;
select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno =a2.deptno;

*****************重点理解的查询方式***********
显示各个员工的姓名,工资,以其工资的级别
select a1.ename,a1.salary,a2.grade from emp a1,salgrade a2 where a1.sal between a2.lowsal and a2.hisal;
 显示雇员名,雇员工资及所在部门的名字,并按照部门排序
 select a1.ename,a2.dname,a2.dname from emp a1,dept a2 where a1.deptno =a2.deptno order by a1.deptno;

自连接,自连接是指在同一张表的链接查询:
显示某个员工的上级领导的名字?
 select work.ename,boss.ename from emp worker,emp boss  where worker.mgr =boss.empno;
 select work.ename,boss.ename from emp worker,emp boss  where worker.mgr =boss.empno and worker.ename='FORD';
什么是子查询:
子查询是指嵌入在其他SQL语句中的select语句,也叫嵌套查询;
单行子查询:
单行子查询是指只返回一行数据的子查询语句
多行子查询:
多行子查询是指返回多行数据的子查询
------------------------------------------------------------------
select deptno from emp where ename='SMITH';
select * from emp where deptno =(select deptno from emp where ename='SMITH');
从左到右的扫描**
 在多行子查询中使用all 操作符    all // any
 如何显示工资比部门30的所有员工的工资高的员工的姓名，工资和部门号;
select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);
在多行子查询中使用any操作符;
如何显示工资比部门30的任意一个员工的工资高的员工的姓名,工资和部门号
select ename,sal,dept from emp where sal> any(select sal from emp where deptno=30);

多列子查询
请思考如何查询与Smith的部门和岗位完全相同的所有雇员;

select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
如何显示高于自己部门平均工资的员工的信息?

select deptno,avg(sal) mysal from emp group by deptno; //每个部门的平均工资
把查询出来的整个结果看做是一张表:

select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,(select deptno,avg(sal) mysal from
emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;
在from子句中使用子查询:
这里需要说明的当在from子句中使用子查询时候,该子查询会被当做一个视图来对待
因此叫做内嵌视图,当在from子句中使用子查询时必须给子查询指定别名;
--------------------------------------------------------------------------------
分页查询,按雇员的id号升序取出
 1 . 用rownum 分页
      Oracle分配的行号,
 
SQL> select * from ( select a1.* ,rownum rn from (select * from emp) a1 where rownum<=10)
  2  where rn>=6;
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ---------
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30          6
      7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10          7
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000                    20          8
      7839 KING       PRESIDENT            17-11月-81           5000                    10          9
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30         10
SQL> 设计到内嵌表和子查询的理解问题,第一张表的分配的行号是rownum,第一张内嵌视图表分配的行号是rn,在内嵌表的rownum上取>=6;
     每次子查询都得到一张不同的视图(不同的内嵌表,用不同的别名标示他们)

-----指定查询列的时候,只需要修改里层的子查询就可以了
SQL> select * from ( select a1.* ,rownum rn from (select ename from emp) a1 where rownum<=10)
  2  where rn>=6;
-----排序也只需要在里层的查询语句变化下
SQL> select * from ( select a1.* ,rownum rn from (select ename from emp order by sal desc) a1 where rownum<=10)
  2  where rn>=6;
**************************很重要的语句************
-----显示4--9条记录
-----用查询结果创建新表: as = like (像,就是借用表结构同时存入取出的数据)
create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;

合并查询:
 有时在实际应用中,为了合并多个select语句的结果,可以使用集合操作符号
union,union all,intersect,minus;
1) union该操作符号用于取得两个结果集的并集,当使用该操作符时,会自动去掉结果集中重复行(union all 不取消重复行)
select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='manager'
2) 一点集合知识,两个集合的差集合
select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job='manager'
 
 
 
连接数据库需要几个参数,大家都应该知道,连接数据库需要ip地址,端口号,用户名,密码,数据库名称,最后一
个就是数据库角色,也就是"连接为"这个选项,没有这个选项无法连接数据库.没有安装Oracle的话,这里就会
少一个选项"连接为",如果您安装了Oracle客户端那就请忽略该经验,因为PLSQL工具会自动从系统注册表
找有没有Oracle的配置信息,点击工具→首选项弹出如下第二图,根据红色框中的位置您会发现中间有个
Oracle主目录,其中第①个是Oracle主目录,第②个是Oracle参数配置信息目录(需要一个配置文件路劲)
,这时您需要下载一个配置文件