您的位置 首页 > 数码极客

oracle8.0.5如何使用

Oracle数据库的安装

我安装的Oracle数据库版本为Oracle Database 12c Release 2

具体安装教程可以参考 百度经验

操作系统为安装在虚拟机里面的Windows Server 2019 DataCenter

客户端我是用的是PLSQL Developer 13。官网 激活码

Oracle体系结构

数据库

Oracle 数据库是数据的物理存储。其实 Oracle 数据库的概念和其它数据库不一样,这里的数据库是一个操作系统 只有一个库。可以看作是 Oracle 就只有一个大数据库。

实例

一个 Oracle 实例(Oracle Instance)有一系列的后台进程(Backguound Processes)和内存结构 (Memory Structures)组成。一个数据库可以有 n 个实例。

12c 版本已经能一个实例有多个数据库了。

用户

用户是在实例下建立的。不同实例可以建相同名字的用户。

在Oracle数据库中用户是管理表的基本单位,对应MySQL中,某某库拥有几张表,而在Oracle中为:某某用户有几张表。

表空间

表空间是 Oracle 对物理数据库上相关数据文件(ORA 或者 DBF 文件)的逻辑映射。一个数 据库在逻辑上被划分成一到若干个表空间,每个表空间包含了在逻辑上相关联的一组结构。每 个数据库至少有一个表空间(称之为 system 表空间)。

每个表空间由同一磁盘上的一个或多个文件组成,这些文件叫数据文件(datafile)。一个数据文件 只能属于一个表空间。

数据文件(dbf、ora)

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的,真正是在某一个 或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成,一个数据文件只能属于 一个表空间。一旦数据文件被加入到某个表空间后,就不能删除这个文件,如果要删除某个数 据文件,只能删除其所属于的表空间才行。

Oracle的一些特殊机制

最开始的学习的时候,这些细节就让我很头疼

  1. Oracle中,字段名,表名,用户名等 默认都是大写的。
  2. 比如:我用Navicat创建了个用户 sky03 ,登录的时候用 sky03,提示用户名/密码无效,其实Navicat在创建表或用户的时候,会自动加上双引号"sky03"(强制使用原大小写),真正的用户名为sky03。而Oracle会自动把不加双引号的 表或用户名 转大写,也就是说我登录的时候的用户名为SKY03,所以登录的时候加上双引号就行了"sky03"。所以为了以后使用方便,以后都用大写。
  3. 与MySQL不同的是,Oracle的事务是手动提交的,所以增删改查后都要手动提交事务(commit),不然都是脏数据!
  4. Oracle数据库开启,并且存在会话的时候,不要直接关机,应该先把会话关掉,再关掉数据库(也可以不关数据库),再关机。不然的话,下次启动登录会报错:Oracle ORA-01033: ORACLE initialization or shutdown in progress 。

创建表空间

表空间:ORACLE 数据库的逻辑单元。 数据库---表空间 一个表空间可以与多个数据 文件(物理结构)关联

一个数据库下可以建立多个表空间,一个表空间可以建立多个用户、一个用户下可以建立 多个表。

创建表空间的语句:

create tablespace "sky03" datafile 'c:\; size 100m autoextend on next 10m;

sky03:为表空间名称

datafile:指定表空间对应的数据文件

size:后定义的是表空间的初始大小

autoextend on:自动增长 ,当表空间存储都占满时,自动增长

next:后指定的是一次自动增长的大小。

最后会在安装Oracle数据库的电脑上创建c:\此文件

删除表空间的语句为:

drop tablespace "sky03"

创建用户

创建用户

创建用户语句:

CREATE USER "SKY03" IDENTIFIED BY "admin" DEFAULT TABLESPACE "sky03"

CREATE USER:创建用户,后面是用户名

IDENTIFIED BY:后边是用户的密码

DEFAULT TABLESPACE:后边是表空间名称

Oracle 数据库与其它数据库产品的区别在于,表和其它的数据库对象都是存储在用户 下的。

这里有个东西要注意,就是如果创建的用户名里有小写字母,登录时就要在用户名上加双引号,Oracle会自动将不带双引号的用户名转大写,例如:

创建的用户名为sky03,登录时填写用户名就必须为"sky03",不然就会提示用户名/口令无效,如果用户名为SKY03,登录时就可以直接填写SKY03或者sky03,密码是区分大小写的!

密码为特殊字符时,创建用户时密码需要加双引号,登陆时也需要把密码用双引号括起来。

给用户分配角色

新创建的用户没有角色身份,登陆后会提示

Oracle 中已存在三个重要的角色:connect 角色,resource 角色,DBA 角色。

  • CONNECT 角色: --是授予最终用户的典型权利,最基本的
  • ALTER SESSION --修改会话
  • CREATE CLUSTER --建立聚簇
  • CREATE DATABASE LINK --建立数据库链接
  • CREATE SEQUENCE --建立序列
  • CREATE SESSION --建立会话
  • CREATE SYNONYM --建立同义词
  • CREATE VIEW --建立视图
  • RESOURCE 角色: --是授予开发人员的
  • CREATE CLUSTER --建立聚簇
  • CREATE PROCEDURE --建立过程
  • CREATE SEQUENCE --建立序列
  • CREATE TABLE --建表
  • CREATE TRIGGER --建立触发器
  • CREATE TYPE --建立类型
  • DBA 角色: 拥有全部特权,是系统最高权限,只有 DBA 才可以创建数据库结构,并且系统 权限也需要 DBA 授出,且 DBA 用户可以操作全体用户的任意基表,包括删除

授权语句:

GRANT "DBA" TO "SKY03"

之后就可以用该用户名登录了!

Oracle数据类型

No数据类型描述1Varchar, Varchar2表示一个字符串2NUMBERNUMBER(n)表示一个整数,长度是 nNUMBER(m,n):表示一个小数,总长度是 m,小 数是 n,整数是 m-n3DATA表示日期类型4CLOB大对象,表示大文本数据类型,可存 4G5BLOB大对象,表示二进制数据,可存 4G

  • Varchar2:常用,它会自动截取掉多余的字符长度

表的管理

SQL的注释

SQL的单行注释为 --

多行注释为 /* ... */

建表

语法:

Create table "用户名"."表名"(

字段 1 数据类型 [default 默认值],

字段 2 数据类型 [default 默认值],

... 字段 n 数据类型 [default 默认值]

);

注意:"用户名"."表名",如果所使用的"用户名"已经是当前已经登录的用户,则可以省略不写,即:"表名"

范例:创建 person 表

CREATE TABLE "SKY03"."person" ( "pid" NUMBER(10) NOT NULL , "name" VARCHAR2(10) , "gender" NUMBER(1) DEFAULT 1 , "birthday" DATE ); ​ INSERT INTO "SKY03"."person"("pid", "name", "gender", "birthday") VALUES ('1', '张三', '1', TO_DATE('2019-04-21 15:42:24', 'SYYYY-MM-DD HH24:MI:SS'));

删表

三个删除

  • 删除表中全部记录
DELETE FROM "person";
  • 删除表结构
DROP TABLE "person";
  • 先删除表,再次创建表。效果等同于 删除表中全部记录 。
  • 在数据量大的情况下,尤其在表中带有索引的情况下,该操作效率高。
  • 索引可以提高查询效率,但是会影响增删改效率
TRUNCATE TABLE "person";

表结构的增删改(字段)

在 sql 中使用 alter 可以修改表

  • 添加字段:ALTER TABLE 表名称 ADD (字段 1 类型 [DEFAULT 默认值],字段 2 类型 [DEFAULT 默认值]...)
  • 修改字段:ALTER TABLE 表名称 MODIFY (字段 1 类型 [DEFAULT 默认值],字段 2 类型 [DEFAULT 默认值]...)
  • 修改字段名:ALTER TABLE 表名称 RENAME COLUMN 字段1 TO 字段 2
  • 删除字段:ALTER TABLE 表名称 DROP COLUMN 字段

添加字段:

ALTER TABLE "SKY03"."person" ADD ("address" varchar2(10) );

修改字段属性:

ALTER TABLE "SKY03"."person" MODIFY ("birthday" VARCHAR2(7) );

修改字段名:

ALTER TABLE "SKY03"."person" RENAME COLUMN "pid" TO "id" ;

删除字段:

ALTER TABLE "SKY03"."person" DROP COLUMN "pid" ;

表数据的增删改(记录)

INSERT(增)

标准写法:

INSERT INTO 表名[(列名 1,列名 2,...)]VALUES(值 1,值 2,...)

简单写法(不建议):

INSERT INTO 表名 VALUES(值 1,值 2,...)

注意:使用简单的写法必须按照表中的字段的顺序来插入值,而且如果有为空的字段使用 null

另外,Oracle默认是手动提交事务的,所以此时插入的数据为脏数据,点下rollback,数据就没了,所以以后增删改都要提交事务。

MySQL中默认是自动提交事务,所以不用手动提交。

java中connection对象是默认自动提交事务的。

提交:commit

回滚:rollback

INSERT INTO "SKY03"."person"("pid", "name", "gender", "birthday") VALUES ('1', '张三', '1', TO_DATE('2019-04-21 15:42:24', 'SYYYY-MM-DD HH24:MI:SS')); COMMIT;

DELETE(删)

语法 :

DELETE FROM 表名 WHERE 删除条件;

在删除语句中如果不指定删除条件的话就会删除所有的数据

UPDATE(改)

全部修改:UPDATE 表名 SET 字段 1=值 1,字段 2=值 2,....

局部修改:UPDATE 表名 SET 字段 1=值 1,字段 2=值 2,....WHERE 修改条件;

序列 (SEQUENCE)

在MySQL中主键一般都是自增、唯一、非空的属性,在Oracle中也一样。MySQL用auto_increment完成以上需求,Oracle用的就是序列(SEQUENCE)。

序列是不属于任何一张表,但是可以和表做绑定。

  1. 创建序列
  • 语法:
  • CREATE SEQUENCE 序列名 [相关参数]
-- s代表序列,person代表是person表的序列 CREATE SEQUENCE s_person;
  • 参数:(一般不用,了解)
  • INCREMENT BY :序列变化的步进,负值表示递减。(默认1)
  • START WITH:序列的初始值 。(默认1)
  • MAXVALUE:序列可生成的最大值。(默认不限制最大值,NOMAXVALUE)
  • MINVALUE:序列可生成的最小值。(默认不限制最小值,NOMINVALUE)
  • CYCLE:用于定义当序列产生的值达到限制值后是否循环(NOCYCLE:不循环,CYCLE:循环)。
  • CACHE:表示缓存序列的个数,数据库异常终止可能会导致序列中断不连续的情况,默认值为20,如果不使用缓存可设置NOCACHE
  1. 修改或删除序列
  • 使用 alter 命令进行修改
  • 使用 drop 命令删除
  1. 序列的使用
  • currval 表示序列的当前值,刚建好的序列没有当前值,必须先执行一次nextval 才能获取到值,否则会报错!
  • nextval 表示序列的下一个值。新序列首次使用时获取的是该序列的初始值,从第二次使用时开始按照设置的步进递增。
  • 查询序列的值:select s_person.[currval/nextval] from dual;
  • 其中dual为虚表,只是为了补全语法,没有任何意义,因为Oracle中查询语句必须带有from关键词,所以采用虚表。
  • SQL语句中使用:insert into table (id) values )

Scott用户

简单来说,Scott用户是Oracle专门给新手练习用的账户,自带的有一些表。但是12c版本的数据库没有Scott账户,但是有Scott账户的SQL文件,可以恢复。具体教程 点这里。

函数

数据库的增删改查,难的就是查,尤其是多表查询,当涉及到复杂查询,就必须使用函数。

Oracle中的函数分为 单行函数 和 多行函数

单行函数

作用于一行,返回一个值

  1. 字符函数
  • upper('yes') 字符小写转大写
select upper('yes') from dual; -- 结果是 YES
  • lower('YES') 字符大写转小写
select upper('YES') from dual; -- 结果是 yes
  1. 数值函数
  • round() 四舍五入
-- 第二个参数:正数,保留小数点后几位;负数,保留小数点前几位;0或不写,不保留小数。 select round) from dual; -- 26.2 select round) from dual; -- 30 select round) from dual; -- 0 select round) from dual; -- 100
  • trunc() 截取数字
-- 第二个参数:正数,保留小数点后几位,其他舍去;负数,保留小数点前几位,其他舍去;0或不写,舍去小数。 select trunc) from dual; -- 26.1 select trunc) from dual; -- 20 select trunc) from dual; -- 0
  • mod() 求余
-- 第一个参数对第二个参数求余 select mod) from dual; -- 0.16 select mod(10,3) from dual; -- 1
  1. 日期函数
  • sysdate 表示当前系统时间
--查询出emp表中所有员工入职距离现在几天。 最后的e是emp的别名 select from emp e; --算出明天此刻 select sysdate+1 from dual; --查询出emp表中所有员工入职距离现在几月。 select months_between(sysdate,e.hiredate) from emp e; --查询出emp表中所有员工入职距离现在几年。 select months_between(sysdate,e.hiredate)/12 from emp e; --查询出emp表中所有员工入职距离现在几周。 select round(()/7) from emp e;
  1. 转换函数
  • to_char() 日期转字符串
select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual; -- 2019-04-22 11:49:43 select to_char(sysdate, 'fm yyyy-mm-dd hh:mi:ss') from dual; -- 2019-4-22 11:49:14 (月日不带0) select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual; -- 2019-4-22 23:50:10 (24小时制)
  • to_date() 字符串转日期
select to_date('2018-6-7 16:39:50', 'fm yyyy-mm-dd hh24:mi:ss') from dual; -- 2018/6/7 16:39:50 注意结果是日期类型的
  1. 通用函数
  • nvl(参数1,参数2) 如果参1为null值,就返回参2,如果参1不为null,则返回参1
--算出emp表中所有员工的年薪。 --奖金里面有null值,如果null值和任意数字做算术运算,结果都是null。 select e.sal*12+nvl, 0) from emp e;

通用函数

通用函数,在MySQL和Oracle里通用!

  • when then 条件表达式
  • 用法一:

---给emp表中员工起中文名 ---等值判断 select e.ename, case e.ename when 'SMITH' then '曹贼' when 'ALLEN' then '大耳贼' when 'WARD' then '诸葛小儿' else '无名' end from emp e; -- 结果:emp表的员工名前三个会自动改掉,其他的走else


  • 用法二:

---给emp表中员工起中文名 ---等值判断 select e.ename, case e.ename when 'SMITH' then '曹贼' when 'ALLEN' then '大耳贼' when 'WARD' then '诸葛小儿' --else '无名' end from emp e; -- 结果:没有指定名字的,全部变成null值 -- 一般可以用于批量将记录的某字段变为null值 -- 其实这里的when后相当于 e.ename='SMITH'


  • 用法三:

---判断emp表中员工工资,如果高于3000显示高收入,如果高于1500低于3000显示中等收入, -----其余显示低收入 -----范围判断 select e.sal, case when e.sal>3000 then '高收入' when e.sal>1500 then '中等收入' else '低收入' end from emp e;


  • decode() Oracle专用表达式,作用和 when then 一样
----oracle专用条件表达式 ----oracle中除了起别名,都用单引号。 select e.ename, decode, 'SMITH', '曹贼', 'ALLEN', '大耳贼', 'WARD', '诸葛小儿', '无名') "中文名" --双引号可以去掉,但是不能用单引号 from emp e;


  • 我们开发人员讲究代码可重用,最好使用通用函数,MySQL和Oracle都能用!

多行函数(聚合函数)

作用于多行,返回一个值

  1. count() 统计记录数
select count(1) from emp;---查询总数量 -- 有时候写的是count(*),其实底层走的还是count(1) -- count(1)的意思是count(第一个字段)
  1. sum() 求和函数
select sum(sal) from emp;---工资总和
  1. max() 最大值
select max(sal) from emp;---最大工资
  1. min() 最小值
select min(sal) from emp;---最低工资
  1. avg() 平均值
select avg(sal) from emp;---平均工资

查询

分组查询

关键字 group by

语法:SELECT 分组字段 [聚合函数] FROM 表名 {GROUP BY 分组字段}

select e.sex, avg) --, e.ename from emp e group by e.sex; -- 整个语句意思是 以性别为组,统计男女的平均年龄
  • 分组查询中,出现在group by后面的原始列,才能出现在select后面
  • 没有出现在group by后面的列,想在select后面,必须加上聚合函数。

  • 不能使用别名当条件
  • 因为一般条件的执行顺序高于查询
  • 例如: where的执行优先级高于select,所以s是无效的
select ename, sal s from emp where s>1500; --不能执行
  • 正确:
select ename, sal s from emp where sal>1500;
  • where和having的区别
  • 查询出平均工资高于2000的部门信息

select e.deptno, avg) from emp e group by e.deptno having avg)>2000;
  • 查询出每个部门工资高于800的员工的平均工资

select e.deptno, avg) asal from emp e where e.sal>800 group by e.deptno;
  • where是过滤分组前的数据,having是过滤分组后的数据。
  • 表现形式:where必须在group by之前,having是在group by之后。
  • 先查询出每个部门工资高于800的员工的平均工资,然后再查询出平均工资高于2000的部门:
select e.deptno, avg) asal from emp e where e.sal>800 group by e.deptno having avg)>2000;

多表查询

多表查询中的一些概念

  • 笛卡尔积
select * from emp e, dept d;
  • emp有14条记录,dept有4条记录,查询出来的结果为14*4=56条记录,其中大部分没有意义
  • 等值连接
select * from emp e, dept d where e.deptno=d.deptno; -- 查询出主键相等的信息 14条信息
  • 内连接(老版的等值连接)
select * from emp e inner join dept d on e.deptno = d.deptno;
  • 外连接(左连接和右连接)
--查询出所有部门,以及部门下的员工信息 select * from emp e right join dept d on e.deptno=d.deptno; ---查询所有员工信息,以及员工所属部门 select * from emp e left join dept d on e.deptno=d.deptno;
  • oracle中专用外连接
select * from emp e, dept d where e.deptno(+) = d.deptno;
  • 自连接:其实就是站在不同的角度把一张表看成多张表。
-- 查询出员工姓名,员工领导姓名,员工和领导都在emp表 select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

子查询

在一个查询的内部还包括另一个查询,则此查询称为子查询。

Sql的任何位置都可以加入子查询。

  • 单列子查询,返回一列的一个内容
---查询出工资和SCOTT一样的员工信息 select * from emp where sal in --如果确定是ename是惟一的,in可以换成= (select sal from emp where ename = 'SCOTT');
  • 单行子查询,返回多个列,有可能是一个完整的记录
---查询出工资和10号部门任意员工一样的员工信息 select * from emp where sal in (select sal from emp where deptno = 10);
  • 多行子查询,返回多条记录
---查询出每个部门最低工资,和最低工资员工姓名,和该员工所在部门名称 ---1,先查询出每个部门最低工资 select deptno, min(sal) msal from emp group by deptno; ---2,三表联查,得到最终结果。 select t.deptno, t.msal, e.ename, d.dname from (select deptno, min(sal) msal from emp group by deptno) t, emp e, dept d where t.deptno = e.deptno and t.msal = e.sal and e.deptno = d.deptno;

分页查询

---rownum行号:当我们做select操作的时候, --每查询出一行记录,就会在该行上加上一个行号, --行号从1开始,依次递增,不能跳着走。 ----排序操作会影响rownum的顺序 select rownum, e.* from emp e order by e.sal desc ----如果涉及到排序,但是还要使用rownum的话,我们可以再次嵌套查询。 select rownum, t.* from( select rownum, e.* from emp e order by e.sal desc) t; ----emp表工资倒叙排列后,每页五条记录,查询第二页。 ----rownum行号不能写上大于一个正数。 select * from( select rownum rn, tt.* from( select * from emp order by sal desc ) tt where rownum<11 ) where rn>5

视图

视图的概念:视图就是封装了一条复杂查询的语句,提供一个查询的窗口,所有数据来自于原表。

  1. 创建视图

创建视图必须有DBA权限

  1. 语法1:CREATE VIEW 视图名称 AS 子查询
create view v_emp as select ename, job from emp;
  1. 语法2:CREATE OR REPLACE VIEW 视图名称 AS 子查询
  2. 如果视图已经存在我们可以使用语法2来创建视图,这样已有的视图会被覆盖。
  3. 查询视图
  4. 跟普通查询没什么区别:
select * from v_emp;
  1. 修改视图
  2. 跟普通update没什么区别,视图修改的是原表的数据,不推介修改视图
update v_emp set job='CLERK' where ename='ALLEN'; commit;
  1. 创建只读视图
  2. 为了防止修改视图还可以创建只读视图
create view v_emp1 as select ename, job from emp with read only;
  1. 视图的作用
  2. 视图可以屏蔽掉一些敏感字段。
  3. 保证总部和分部数据及时统一。

索引

  • 概念
  • 索引就是在表的列上构建一个二叉树。
  • 达到大幅度提高查询效率的目的,但是索引 会影响增删改的效率。
  • 索引分为 单列索引 和 复合索引
  • 单列索引
  • 创建单列索引
  • 语法:CREATE index 索引名 on 表名(列名)
create index idx_ename on emp(ename);
  • 单列索引触发规则
  • 查询条件必须是索引列中的原始值。
  • 单行函数,模糊查询,都会影响索引的触发。
  • 复合索引
  • 创建复合索引
create index idx_enamejob on emp(ename, job);
  • 复合索引触发规则
  • 复合索引中第一列为优先检索列。
  • 触发复合索引,必须包含有优先检索列中的原始值。
  • 示例:
  • 假设以上单列索引和复合索引同时存在

select * from emp where ename='SCOTT' and job='xx'; ---触发复合索引 select * from emp where ename='SCOTT'; ---触发单列索引。 select * from emp where ename='SCOTT' or job='xx'; ---这个相当于两条查询语句,一个条件为or的前者,触发单列索引;一个条件为or的后者,不触发任何索引。 ---or到一起,就是不触发索引

PL/SQL编程语言

  • pl/sql编程语言是对sql语言的扩展,使得sql语言具有过程化编程的特性。
  • pl/sql编程语言比一般的过程化编程语言,更加灵活高效。
  • pl/sql编程语言主要用来编写存储过程和存储函数等。

基本语法

declare 说明部分 (变量说明,游标申明,例外说明 〕 begin 语句序列 (DML语句〕… exception 例外处理语句 End;

示例:

  • 赋值操作可以使用:=也可以使用into查询语句赋值
declare i number(2) := 10; -- `:=`是赋值的意思,相当于Java里的 `=` s varchar2(10) := '小明'; ena emp.ename%type;---引用型变量 emprow emp%rowtype;---记录型变量 begin dbm(i); --输出变量 dbm(s); select ename into ena from emp where empno = 7788; dbm(ena); select * into emprow from emp where empno = 7788; dbm || '的工作为:' || em); -- 双竖线`||`是连接字符串的符号 end;

if判断语句

需求:

  1. 输入小于18的数字,输出未成年
  2. 输入大于18小于40的数字,输出中年人
  3. 输入大于40的数字,输出老年人

语法:

declare i number(3) := &i; -- &i表示键盘输入,&后可以跟任何变量 begin if i<18 then dbm('未成年'); elsif i<40 then -- elsif相当于else if dbm('中年人'); else dbm('老年人'); end if; --if语句结束再加分号; end;

loop循环

需求: 用三种方式输出1到10是个数字

  • while循环
declare i number(2) := 1; begin while i<11 loop i小于11,就一直循环 dbm(i); i := i+1; end loop; end;
  • exit循环
declare i number(2) := 1; begin loop exit when i>10; -- 当i大于10的时候推出循环 dbm(i); i := i+1; end loop; end;
  • for循环
declare begin for i in 1..10 loop -- 变量i 从1循环到10结束 dbm(i); end loop; end;

游标

游标可以存放多个对象,多行记录。

需求:输出emp表中所有员工的姓名

declare cursor c1 is select * from emp; emprow emp%rowtype; begin open c1; loop fetch c1 into emprow; exit when c1%notfound; dbm); end loop; close c1; end;

责任编辑: 鲁达

1.内容基于多重复合算法人工智能语言模型创作,旨在以深度学习研究为目的传播信息知识,内容观点与本网站无关,反馈举报请
2.仅供读者参考,本网站未对该内容进行证实,对其原创性、真实性、完整性、及时性不作任何保证;
3.本站属于非营利性站点无毒无广告,请读者放心使用!

“oracle8.0.5如何使用”边界阅读