从零开始学Oracle(一至五)

--以下内容转载于http://tech.ddvip.com

--从零开始学Oracle—环境配置（一）

一、开始前的准备

　　1.启动Oracle

　　 启动监听服务：OracleOraHome90TNSListener

　　 启动实例服务：OracleServiceOrac

　　2.Oracle系统用户

　　 Oracle三大系统用户

　　 默认用:system/welcome

　　超级用户:sys/welcome　　 权限最大

　　 普通用户:scott/tiger

　　3.登录Oracle的方式

　　 Windows窗口下的sqlplus的操作　　 　运行->sqlplusw.exe　

　　 DOS下的sqlplus的操作　　　　　　　　　　　　　　　运行->　sqlplus.exe　　　　

　　 登录Oracle但不连接数据库　　　　　　　　　　 运行->sqlplusw /nolog

　　 connect 用户名/密码@主机连接字符串　 连接到指定用户

　　4.Oracle常用操作

show user 查看当前用户　　 select * from tab where tabtype='TABLE'　 查看当前用户下的表
desc dept　　　　　　　　　　　　 察看表dept的结构
quit/exit　　　　　　　　　　　　 退出
clear screen　　　　　　　　　 清除屏幕
set linesize 200　　　　　 设置一行显示200个字符
set pagesize 20　　　　　　 设置每页显示20行
spool 文件名 (spool c:abc.txt) 作日志文件命令　　　　　　　　　spool off
alter session set nls_date_format = 'yyyy-mm-dd'; 改日期的默认格式
conn /as sysdba　　　　 select * from v$version;查看当前Oracle的版本
conn system/welcome select * from v$tablespace; 查看表空间

　　5.表空间　 用户 表三者的关系

　　 一个用户可以使用一个或多个表空间，一个表空间也可以供多个用户使用　
用户和表空间没有隶属关系。

　　 表空是一个用来管理数据存储逻辑概念，表空间只是和数据文件发生关系，数据文件是物理的，

　　 一个表空间可以包含多个数据文件，而一个数据文件只能隶属一个表空间

　　二、常规操作

　　1.DDL语句(数据定义语言) Data Define Language

　　 create　 　alter　　 　drop　 　truncate 开头的语句 truncate table <表名>

　　 特点:<1>建立和修改数据对象

　　 　<2>建立和修改直接存入库中,直接生效

　　创建表空间：Create tablespace Product　 datafile 'D:testaa.DBF' size 100M

　　删除表空间：Drop tablespace Product

　　创建用户: Create user zhangsan identified by "welcome" default tablespace Product

　　修改用户: Alter　 user zhangsan identified by "Hello"

　　授予用户连接的权限: 　grant connect to zhangsan;

　　撤销用户连接权限：　　 revoke connect　from zhangsan;

　　授予用户在表空间创建对象的权限　　

grant unlimited tablespace to zhangsan;
grant resource　 to zhangsan;　　

　　系统特权:unlimited tablespace表示用户可以在任意表空间中创建对象，且此权限只能授予用户而不能授予角色

　　resource角色:给用户RESOUCE权限后，用户就拥有在所有表空间上建表权限

　　授予用户zhangsan对Emp表增、删、查、改的权限

grant　 select　 on scott.empa to y2;
　　　　　　　　　 delete
　　　　　　　　　 update
　　　　　　　　　 insert
　　　　　　　　　 all　 （=select,delete,update,insert)
with grant option 授予用户权限，则接受权限的用户可以将此权限授予其他用户.

　　（1）.建表,并指定表空间

　　示例： Create table tab1
　　　　　　　　　 (
　　　　　　　　　　　　 no number(4),
　　　　　　　　　　　　 name varchar2(10)
　　　　　　　　　 )tablespace Product;

　　学生表　 Student

　　　　　　　Create table student
　　　　　　 (　
　　　　　　　　 xh number(4) primary key, --学号
　　　　　　　　 name varchar2(10) not null, --姓名
　　　　　　　　 sex　 char(2)　 check (sex in ('男','女')),--性别
　　　　　　　　 birthday date,--生日
　　　　　　　　 sal number(7,2), --奖学金
　　　　　　　　 classid number(2) references class(classid) --班级　　　　　　　　　
　　　　　　 );　班级表class
　　　　　　　　 Create table class
　　　　　　　　 (
　 　　　　　　　 classid number(2), --班级编号
　　　　　　　　　 cname varchar2(20) --班级名字
　　　　　　　　 );

　　 添加字段(学生所在班级classid)

　　 alter table student add (classid number(2));

　　 修改字段的长度

　　 alter table student modify (xm varchar2(12)) ;

　　 修改字段的类型(不能有记录的)

　　 alter table student modify (xh varchar2(5));

　　 删除一个字段

　　 alter table student drop column sal;
删除表

　　 drop table student;

　　 表的名字修改

　　 rename student to stu;

　　 字段如何改名字

　　 --先删除

　　 a)alter table student drop column sal;　　　　　　　　

　　 --再添加

　　 b)alter table student add (salary number(7,2));

　　Number数据类型的存储范围

　　精度　　　 存储字节数

1 - 9　　　　　　 5
10-19　　　　 9
20-28　　　 13
29-38　　　 17

--从零开始学Oracle—基本语法解析（二）
 Oracle数据操作语言 DML

　　接上一篇:

　　2. DML语句(数据操作语言) Data Manupilate Language　　　　　

　　 select
　　 insert
　　 delete
　　 update

　　 特点:<1>对数据起作用的

　　 <2> 这些语句的修改是在内存中发生的

　　 要想改动存入库中必须要commit语句

　　查看当前用户的所有权限

select * from session_privs;

　　查看当前用户下的所有表

select * from tab where tabtype='TABLE';

　　3. TCL(事务控制语句) Transaction Control Language

　　 commit;　 提交　 修改保存到数据库中

　　 rollback; 回滚　 取消内存中的改动

　　 savepoint;保存点 分解事务的 把事务变小

　　 DDL语句 会自动提交以前未提交的事务

　　 关闭SQLplus工具 也会自动提交未提交的事务的

　　 事务 -- 就是一个完整的对数据的DML操作

　　 所有事务 都是要明确的提交和回滚的

　　 --转账　

　　　 update 账目表
　　　 set 钱=钱-500
　　　 where 帐号='A';
　　　 update 账目表
　　　 set 钱=钱+500
　　　 where 帐号='B';
　　　 commit;

　　 事务何时存在 DML语句中除select以外都会有事务

　　《《《《《《《注意》》》》》 / 重复运行上一条SQL语句　　

　　 commit;　　　 结束上一个事务 并且开始一个新的事务

　　 update student set sal = null where xh =1000;
 savepoint c111;

　　 insert into student(xh,name,sex) values (1004,'MIKE','男');

　　 rollback to c111; --撤销了插入的数据

　　 rollback;　 --从c111这个点回滚到事务的开始点

　　《SQLPLUS规则》　　

　　 a)DML语句后跟上DDL语句 DML语句的事务会被自动提交

　　 b)exit/quit命令 退出 SQLPLUS环境时也会自动提交事务

　　 点小叉子关闭sqlplus窗口 事务都自动回滚了

　　 c)非法操作是不能提交事务的 ，只能导致事务回滚

　　《4》 DCL语句(数据控制语句) Data Control Language　　　　　　　　　　　　　　　　　　　 grant 授予权限

　　 revoke 撤销权限

　　 权限 select ,insert,delete,update

　　 all (select ,insert,delete,update 总和)

　　 角色 connect （登陆数据库）,resource(建立表和对象)

　　 如何建一个自己的用户？

　　 必须是超级用户才能建用户

　　　　 --连接到超级用户
　　　　 connect system/manager
　　　　 --建立用户名zhangsan 密码m123
　　　　 create user zhangsan identified by m123;
　　　　 --授予必要的权限connect 你能够连接
　　　　　　　　　　　　　　　　　　　 resource 你能建表不受空间的限制,建立对象
　　　　 grant connect,resource to zhangsan;
　　　　 --这个普通用户就建好了 和scott用户的权限是一样的　　　　　
　　　　 grant DBA to zhangsan; --张三的权限和System一样

　　 --改张三的密码

　　 <<1>> 自己改自己的密码

　　　　　　　 connect zhangsan/m123

　　 密码改为了mm1　　

　　　　　　　 alter user zhangsan identified by mm1;

　　 <<2>> 超级用户来改

　　　　　　　 connect system/manager
　　　　　　　 alter user zhangsan identified by mm1;　
----从零开始学Oracle-—约束(三)
给表添加约束

　　 主键约束 --　 每个表要有主键，唯一的标识一行数据

　　 非空约束

　　 唯一性约束

　　 外键约束

　　 检查约束

　　查看一个表的约束：

　　　　　 select constraint_name,constraint_type
　　　　　 from user_constraints
　　　　　　where table_name = '表名'

　　查看约束作用于那个字段：

　　　　 select * from user_cons_columns
　　　 　where CONSTRAINT_NAME='约束名';

　　1.建立表的同时使用约束

　　　　　　　 create table student( --学生表
　　　　　　　　　　 xh number(4) primary key, --学号主键
　　　　　　　　　　 xm varchar2(10) not null, --姓名不能为空
　　　　　　　　　　 sex char(2)　 check (sex in ('男','女')), --性别
　　　　　　　　　　 birthday date unique, --日期
　　　　　　　　　　 sal number(7,2) check (sal between 500 and 1000),--奖学金 sal >=500 and sal <=1000
　　　　　　　　　　 classid number(2) references cla(id)
　　　　　　　 );　 --必须要先有cla表才对
　　　　　　　　　　　 --一定先建立班级cla表

　　2.建立约束的同时给约束指定名字，便于删除

　　　　　　　 create table stu( --学生表
　　　　　　　　　 xh number(4) constraint pk_stu primary key, --学号是主键
　　　　　　　　　 xm varchar2(20) constraint nn_stu not null, --姓名非空
　　　　　　　　　 age number(2) constraint ck_stu check (age between 10 and 90),
　　　　　　　　　 birthday date,
　　　　　　　　　 shenfenzheng number(18) constraint uq_stu unique, --身份证唯一　
　　　　　　　　　 classid number(2) constraint fk_stu references cla(id) -- 班级编号外键
　　　　　　　　　　 --(引用的一定是另外表的主键或唯一性约束的字段)
　　　　　　　　 );

　　3.建完表后加约束

　　添加主键约束：alter table student add constraint pk_stu primary key (xh);

　　添加非空约束：alter table student modify (xm not null);

　　检查约束：

alter table student add check(sex in ('男','女'));
alter table student add constraint ck_sal check(sal between 500 and 1000));
添加外键约束:　　　 alter table student add constraint fk_stu　 foreign key (classid) references cla(id);
添加唯一约束：　 alter table student add constraint　 uq_sfz unique(shenfenzheng);

　　4.删除约束：

　 格式：alter table 表名 drop constraint 约束名
　　　 　alter table student drop constraint　 fk_stu;
---从零开始学Oracle—内建对象(五)

1.　　 序列

　　查询当前用户下的序列：select * from user_sequences

　　定义：用来生成唯一、连续的整数的数据库对象，序列通常用来自动生成主键或唯一键的值、可升、可降序排列.

　　语法：　　　 Create　 sequence 序列名称　　 //从1开始 每次自动增加1 没有最大值

　　 [star with 值]　 指定要生成的第一个序列号，对于升序默认值为最小值、反之...

　　 [increment　 by 值]　　 间隔数

　　 [maxvalue　 值|nomaxvalue]　 序列最大值,如果指定为nomaxvalue，则升序最大值为10的27次方，降序-1.

　　 [minvalue　　　 值|nominvalue]　 序列最小值,-----------------------升序为1，降序为10的负26次方

　　 [cycle|nocycle]　　 cycle指序列到达最大或最小时，将从头开始生成值　 nocycle值不在生成

　　 [cache　 值|nocache]　 预先分配一组序列号，将其保存在内存中. 默认缓存20个序列

　　访问序列方法：　 currval 和nextval　　 --当前序列 select 序列名.currval from dual

　　更改序列：Alter sequence 序列名

　　删除序列：drop sequence 序列名

　　注意-------------序列使用时需要先用nextval输出完以后再来使用

　　使用：

　 1.和表关联作为表的字段的值
　　　　 a) create table student(
　　　　　　　　　 xh number(4) primary key, --学号
　　　　　　　　　 xm varchar2(20) not null --姓名
　　　　　　　 );

　　　　　　　 要求:学号字段 从1000开始每次增加4 最大值9999
　　　　　　　　 --建立序列
　　　　　　　　　　 create sequence xh_seq
　　　　　　　　　　　　　　　　　 start with 1000 --从1000开始
　　　　　　　　　　　　　　　　　 increment by 4　 --每次增加4
　　　　　　　　　　　　　　　　　 maxvalue 9999　　 --最大值 9999
　　　　　　　　　　　　　　　　　 ;
　　　　　　　　 --使用SQL语句关联
　　　　　　　　 insert into student values
　　　　　　　　　　　　 (xh_seq.nextval,'Mike');
　　　　　　　　 insert into student values
　　　　　　　　　　　　 (xh_seq.nextval,'John');
　　　　　　　　 insert into student values
　　　　　　　　　　　　 (xh_seq.nextval,'Rose');
　　　　
　　　　　 --特点:能产生唯一的值,但是不能保证值在表中的连续性
　
　　 b) create table teacher(
　　　　　　　　　　 teacherxh varchar2(10) primary key,
　　　　　　　　　　 teachername varchar2(20)
　　　　　　　　　 );
　　　　 要求:教师的编号的格式是TH00001
　　　　　　　　　　　　　　　　　　　　　　　　 TH00002
　　　　　　　　　　　　　　　　　　　　　　　　　 ....
　　　　　
　　 --建立序列
　　　　　　　 create sequence teacher_seq
　　　　　　　　　　　　　 maxvalue 99999;
　　 --
　　　　 insert into teacher values ('TH'||
　　　　　　 ltrim(to_char(teacher_seq.nextval,'00000')),
　　　　　　 '张三');
　　　　　　 insert into teacher values ('TH'||
　　　　　　 ltrim(to_char(teacher_seq.nextval,'00000')),
　　　　　　 '李');
　　　　　 insert into teacher values ('TH'||
　　　　　　 ltrim(to_char(teacher_seq.nextval,'00000')),
　　　　　　 '王');
2.视图

　　查看当前用户下的视图:select view_name from user_views;

　　作用:隐藏数据,增加系统的安全性

语法：Create [or replace] [Force|noForce] veiw 视图名 [(别名1，别名2...)]
　　　　　　　　 AS 子查询
　　　　　　　　 [with check option [constraint 约束名]]
　　　　　　　　 [with read only]　 视图是只读的

　　其中：or replace 表示替代已经存在的视图

　　 force 表示不管基表是否存在,创建视图

　　 noforce 表示只有基表存在时，才创建视图.

　　 别名是为子查询中选中的列新定义的名字，替代查询表中原有的列名。

　　 with check option 对视图操作时必须满足子查询的约束条件。后面的约束名是该约束条件的名字

　　 删除视图者需要是视图的建立者或者拥有DROP ANY VIEW权限。视图的删除不影响基表，不会丢失数据

　　对视图操作需满足以下条件：

　　　　　　　　　 <1>建立视图的select语句必须是简单的select语句
　　　　　　　　　　　　　　 简单:不能是多表的查询
　　　　　　　　　　　　　　　　　　　 不能有分组函数
　　　　　　　　　　 <2>建立视图的时候不能带with readonly关键字

　　例：

create or replace view emp_v2
　as --select语句
　 select empno,ename,sal from emp
　 with read only;　 --只读视图

　　3.同义词

　　查看用户创建的同义词:select * from user_synonum;

　　定义:数据库对象的一个别名
用途：1.简化SQL

　　 2.隐藏对象名称和所有者

　　 3.为分布式数据库的远程对象提供了位置透明度

　　 4.提供对象的公共访问

　　注意:使用同义词前需要确保用户得到访问同义词的权限

　　同义词分为：

　　1.私有同义词　 --只能被当前用户模式访问　 要在自身模式下创建须 Create synonum 权限

　　2.共有同义词　 --须Create any synonym权限

　　语法： Create [or replace] [public] synonym 同义词名称 for 要创建同义词对象的名称

　　4.索引

　　作用：加快查询　 索引一定是建立在表上的.

　　语法：Create Index 索引名 on 表名(列名)

　　创建唯一索引: Create unique Index 索引名 on 表名(列名)

　　创建位图索引: Create bitmap Index 索引名 on 表名(列名)

　　数据的不同值对于记录的行数来说 是个很小的数　 这种字段适合使用位图索引 它比普通索引效率高

　　不能用索引的条件:

　　<1>规则1:索引的字段不能参与运算

　　<2>规则2:索引的字段上不能使用函数　

　　5.簇

　　定义：用于多表联合查询，提高数据查询效率

1.簇的创建必须在创建表结构时指定
2.如果使用簇，则两个表必须要有主外键关系

　　簇的使用步骤：

1.创建簇　　　 Create cluster 簇名称(字段名 数据类型);
2.创建主键表使用簇
Create table depa (
　　　 deptno number(2) primary key,
　　　 dname varchar2(20)
　) cluster 簇名（deptno);
3.创建外键表使用簇
Create table empa(
　　 empno number(4) primary key,
　　 ename varchar2(20)
　　 ) cluster 簇名(empno)
4.在簇上建立索引
Create Index　 索引名 on cluster 簇名