oracle的基本使用

一、 oracle介绍[了解]

 ORACLE 数据库系统是美国 ORACLE 公司（甲骨文）提供的以分布式数据库为核心的一组

软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或 B/S 体系结构的数据库之一。比如
SilverStream 就是基于数据库的一种中间件。ORACLE 数据库是目前世界上使用最为广泛的数据
库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，
它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只
要在一种机型上学习了 ORACLE 知识，便能在各种类型的机器上使用它。

二、 Oracle安装[了解]

课上已准备好了一个安装全套 Oracle 软件的 XP 虚拟机，我们直接在虚拟机中学习，如果自
己想把软件安装到自己电脑请参考文档《Oracle 安装.docx》

三、 Oracle体系结构[理解]

数据库
Oracle 数据库是数据的物理存储。这就包括（数据文件 ORA 或者 DBF、控制文件、联机日
志、参数文件）。其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统
只有一个库。可以看作是 Oracle 就只有一个大数据库。
实例
一个 Oracle 实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构
（Memory Structures)组成。一个数据库可以有 n 个实例。
用户
用户是在实例下建立的。不同实例可以建相同名字的用户。
表空间
表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数
据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每
个数据库至少有一个表空间(称之为 system 表空间)。
每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件
只能属于一个表空间。
数据文件（dbf、ora）
数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个
或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于
一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数
据文件，只能删除其所属于的表空间才行。
注：表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到
一个或者多个数据文件中。
由于 oracle 的数据库不是普通的概念，oracle 是有用户和表空间对数据进行管理和存放的。
但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同
一个名字的表！这里区分就是用户了！

四、创建表空间[理解]

表空间？ ORACLE 数据库的逻辑单元。 数据库---表空间 一个表空间可以与多个数据

文件（物理结构）关联
一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立
多个表。

/
--创建表空间
create tablespace itheim
datafile 'd:\itheim.dbf'
size 100m
autoextend on  --自动扩容
next 10m;

--删除表空间
drop tablespace itheim

itcast 为表空间名称
datafile 指定表空间对应的数据文件
size 后定义的是表空间的初始大小
autoextend on 自动增长，当表空间存储都占满时，自动增长
next 后指定的是一次自动增长的大小。

五、用户[理解]

创建用户
--创建用户

create user itheim
identified by itheim
default tablespace itheim

identified by 后边是用户的密码
default tablespace 后边是表空间名称
oracle 数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户
下的。
7. 用户赋权限
新创建的用户没有任何权限，登陆后会提示

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 用户可以操作全体用户的任意基表，包括删除

--给用户授权
--oracle常用角色
connect     --连接角色
resource  --开发者角色
dba --超级管理员角色
--给itheim赋予dba角色
grant dba to itheim

进入 system 用户下给用户赋予 dba 权限，否则无法正常登陆

六、 Oracle 数据类型[ 应用]

No 数据类型描述
1 Varchar， varchar2 表示一个字符串
2 NUMBER NUMBER(n)表示一个整数，长度是 n
NUMBER(m,n):表示一个小数，总长度是 m，小
数是 n，整数是 m-n
3 DATA 表示日期类型
4 CLOB 大对象，表示大文本数据类型，可存 4G
5 BLOB 大对象，表示二进制数据，可存 4G

七、表的管理[应用]

1.1建表

语法：
Create table 表名（
字段 1 数据类型 [default 默认值],
字段 2 数据类型 [default 默认值],
...
字段 n 数据类型 [default 默认值] ）;
范例：创建 person 表
create table person(
 pid number(10),
 name varchar2(10),
 gender number(1) default 1,
 birthday date
);
insert into person(pid, name, gender, birthday)
values(1, '张三', 1, to_date('1999-12-22', 'yyyy-MM-dd'));

2.1表删除

语法：DROP TABLE 表名

3.1表的修改
在 sql 中使用 alter 可以修改表
 添加语法：ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型
[DEFAULT 默认值]...)
 修改语法：ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型
[DEFAULT 默认值]...)
 修改列名: ALTER TABLE 表名称 RENAME 列名 1 TO 列名 2
范例：在 person 表中增加列 address
alter table person add(address varchar2(10));
范例：把 person 表的 address 列的长度修改成 20 长度
alter table person modify(address varchar2(20));

4.1数据库表数据的更新

INSERT（增加）
标准写法：
INSERT INTO 表名[(列名 1，列名 2，...)]VALUES(值 1，值 2，...)
简单写法（不建议）
INSERT INTO 表名 VALUES(值 1，值 2，...)
注意：使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用 null
insert into person
values(2,'李四',1,null,'北京育新');
UPDATE（修改）
全部修改：UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，....
局部修改：UPDATE 表名 SET 列名 1=值 1，列名 2=值 2，....WHERE 修改条件；
全部更新
局部更新
DELETE（删除）
语法 : DELETE FROM 表名 WHERE 删除条件;

在删除语句中如果不指定删除条件的话就会删除所有的数据
因为 oracle 的事务对数据库的变更的处理，我们必须做提交事务才能让数据真正的插入到数
据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据
库。如果事务提交后则不可以再回滚。
提交：commit
回滚：rollback

5.1序列

在很多数据库中都存在一个自动增长的列,如果现在要想在 oracle 中完成自动增长的功能,

则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。
语法：CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];
范例:创建一个 seqpersonid 的序列,验证自动增长的操作
CREATE SEQUENCE seqpersonid;
序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：
nextval :取得序列的下一个内容
currval :取得序列的当前内容
select seqpersonid.nextval from dual;
select seqpersonid.currval from dual;
在插入数据时需要自增的主键中可以这样使用

在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪
一张表使用都可以，但是我们一般都是一张表用一个序列。
序列的管理一般使用工具来管理。

八、 Scott用户下的表结构[了解]

九、单行函数[应用]

1. 字符函数
接收字符输入返回字符或者数值，dual 是伪表

把小写的字符转换成大小的字符
upper('smith')
把大写字符变成小写字符
lower('SMITH')

2. 数值函数
四舍五入函数：ROUND()
默认情况下 ROUND 四舍五入取整，可以自己指定保留的位数。

1.1.1 3.日期函数
Oracle 中提供了很多和日期相关的函数，包括日期的加减，在日期加减时有一些规律
日期 – 数字 = 日期
日期 + 数字 = 日期
日期 – 日期 = 数字

范例：查询雇员的进入公司的周数。
分析：查询雇员进入公司的天数(sysdate – 入职日期)/7就是周数
获得两个时间段中的月数：MONTHS_BETWEEN()
范例：查询所有雇员进入公司的月数

1.1.2 4.转换函数
TO_CHAR:字符串转换函数
范例：查询所有的雇员将将年月日分开，此时可以使用 TO_CHAR 函数来拆分
拆分时需要使用通配符
年：y, 年是四位使用 yyyy
月：m, 月是两位使用 mm
日：d, 日是两位使用 dd

在结果中 10 以下的月前面被被补了前导零，可以使用 fm 去掉前导零
TO_DATE:日期转换函数
TO_DATE 可以把字符串的数据转换成日期类型

1.1.3 5.通用函数
1．空值处理 nvl
范例：查询所有的雇员的年薪

我们发现很多员工的年薪是空的，原因是很多员工的奖金是 null，null 和任何数值计算都是
null，这时我们可以使用 nvl 来处理。

2.Decode 函数
该函数类似 if....else if...esle
语法：DECODE(col/expression, [search1,result1],[search2, result2]....[default])
Col/expression:列名或表达式
Search1，search2...:用于比较的条件
Result1, result2...:返回值
如果 col/expression 和 Searchi 匹配就返回 resulti,否则返回 default 的默认值

范例：查询出所有雇员的职位的中文名

3.case when


CASE expr WHEN comparison_expr1 THEN return_expr1
 [WHEN comparison_expr2 THEN return_expr2
 WHEN comparison_exprn THEN return_exprn 
 ELSE else_expr]
END
select t.empno,
 t.ename,
 case
 when t.job = 'CLERK' then
 '业务员'
 when t.job = 'MANAGER' then
 '经理'
 when t.job = 'ANALYST' then
 '分析员'
 when t.job = 'PRESIDENT' then
 '总裁'
 when t.job = 'SALESMAN' then
 '销售'
北京市昌平区建材城西路金燕龙办公楼一层 电话：400-618-9090
传智播客——专注于 Java、.Net 和 Php、网页平面设计工程师的培训
 else
 '无业'
 end
 from emp

十、多行函数（聚合函数）

1.1.3.1 1.统计记录数 count()
范例：查询出所有员工的记录数

不建议使用 count(*)，可以使用一个具体的列以免影响性能。

1.1.3.2 2.最小值查询 min()
范例：查询出来员工最低工资

1.1.3.3 3.最大值查询 max()
范例：查询出员工的最高工资

1.1.3.4 4.查询平均值 avg()
范例：查询出员工的平均工资

1.1.3.5 5.求和函数 sum()
范例：查询出 20 号部门的员工的工资总和

十一、分组统计

分组统计需要使用 GROUP BY 来分组
语法：语法：SELECT * |列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段} ORDER BY 列名 1 ASC|DESC，列名 2...ASC|DESC
范例：查询每个部门的人数

范例：查询出每个部门的平均工资

如果我们想查询出来部门编号，和部门下的人数

我们发现报了一个 ORA-00937 的错误
注意：

如果使用分组函数，SQL 只可以把 GOURP BY 分组条件字段和分组函数查询出来，不能有其
他字段。
如果使用分组函数，不使用 GROUP BY 只可以查询出来分组函数的值

范例：按部门分组，查询出部门名称和部门的员工数量

范例：查询出部门人数大于 5 人的部门
分析：需要给 count(ename)加条件，此时在本查询中不能使用 where，可以使用HAVING

范例：查询出部门平均工资大于 2000的部门

十二、多表查询[应用]

1.1.4 1.多表连接基本查询
使用一张以上的表做查询就是多表查询
语法： SELECT {DISTINCT} *|列名.. FROM 表名别名，表名 1 别名
{WHERE 限制条件 ORDER BY 排序字段 ASC|DESC...}
范例：查询员工表和部门表

我们发现产生的记录数是 56 条，我们还会发现 emp 表是 14 条，dept 表是 4 条，56 正是 emp
表和 dept 表的记录数的乘积，我们称其为笛卡尔积。
如果多张表进行一起查询而且每张表的数据很大的话笛卡尔积就会变得非常大，对性能造
成影响，想要去掉笛卡尔积我们需要关联查询。
在两张表中我们发现有一个共同的字段是 depno，depno 就是两张表的关联的字段，我们可
以使用这个字段来做限制条件，两张表的关联查询字段一般是其中一张表的主键，另一张表的
外键。

关联之后我们发现数据条数是 14条，不在是 56 条。
多表查询我们可以为每一张表起一个别名

范例：查询出雇员的编号，姓名，部门的编号和名称，地址

1.1.5 2. 外连接（左右连接）

右连接
当我们在做基本连接查询的时候，查询出所有的部门下的员工，我们发现编号为 40 的部门
下没有员工，但是要求把该部门也展示出来，我们发现上面的基本查询是办不到的

使用(+)表示左连接或者右连接，当(+)在左边表的关联条件字段上时是左连接，如果是在右
边表的关联条件字段上就是右连接。

十三、子查询[ 应用]

子查询：在一个查询的内部还包括另一个查询，则此查询称为子查询。
Sql的任何位置都可以加入子查询。
范例：查询比 7654工资高的雇员
分析：查询出 7654员工的工资是多少，把它作为条件

子查询在操作中有三类：
单列子查询：返回的结果是一列的一个内容
单行子查询：返回多个列，有可能是一个完整的记录
多行子查询：返回多条记录
范例：查询出比雇员 7654 的工资高，同时从事和 7788的工作一样的员工

十四、 Rownum 与分页查询[ 应用]

ROWNUM:表示行号，实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现。
范例：查询 emp 表带有 rownum 列
select rownum, t.* from emp t

我们可以根据 rownum 来取结果集的前几行，比如前 5 行

但是我们不能取到中间几行，因为rownum不支持大于号，只支持小于号，如果想实现我们的
需求怎么办呢？答案是使用子查询，也正是oracle分页的做法。
第一种写法：

select *
 from (select rownum rm, a.* from (select * from emp) a where rownum < 11) b where b.rm > 
5
第二种写法：
select * 
from (select rownum r ,emp.* from emp) b
where b.r >5 and b.r <11

posted @ 2020-08-11 10:38 錵開や落幕阅读(338) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

錵開や落幕

oracle的基本使用

一、 oracle介绍[了解]

二、 Oracle安装[了解]

三、 Oracle体系结构[理解]

四、 创建表空间[理解]

五、 用户[理解]

六、 Oracle 数据类型[ 应用]

七、 表的管理[应用]