从零开始学Oracle(一至五)

素颜马尾好姑娘i 2022-09-26 14:52 189阅读 0赞

\--以下内容转载于[http://tech.ddvip.com][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 簇名

[http_tech.ddvip.com]: http://tech.ddvip.com