从零开始学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 簇名