本文最后更新于2023年5月23日，已超过 1 年没更新！内容可能已失效，请自行测试。

Oracle 简介

Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系型数据库管理系统。

创始人 Larry Ellison

Oracle 安装

超详细的安装过程

若电脑存在旧版本,需要更换版本是,需要完全卸载Oracle进行重装

可参考完全卸载Oracle

Oracle 体系结构

数据库

Oracle数据库是数据的物理存储，包括数据文件ORA或者DBF，控制文件，练级日志，参数文件。其实Oracle数据库的概念和其他的有点不一样，这里的是一个操作系统只有一个库，看作Oracle就只有一个大数据库

实例

一个Oracle实例，有一系列的后台进程和内存结构组成。一个数据库可以有N个实例

用户

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

表空间

表空间是数据库的逻辑存储设备, 它把数据库信息组织成物理存储空间.

表空间由数据文件组成.用户的各种模式对象(如表, 索引, 过程, 触发器等) 都是放在表空间中. 对每个数据库用户, 都可以设置一个默认表空间. 当用户创建一个新的数据库对象(如表), 并且不明确地为此对象指定表空间时, Oracle 会把所创建的这个新数据库对象存放到用户默认的表空间中

如果不给用户指定默认表空间, 则用户的默认表空间为 USERS 表空间.

数据文件

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

注：表的数据由用户放入某一个表空间中的，而这个表空间会随机把这些表数据放到一个或多个数据文件中

SQL语句

SQL语句分为以下三种类型

  • DML: Data Manipulation Language 数据操纵语言
  • DDL:  Data Definition Language 数据定义语言
  • DCL:  Data Control Language 数据控制语言

DML

DML用于查询与修改数据记录，包括如下SQL语句

  • INSERT：添加数据到数据库中
  • UPDATE：修改数据库中的数据
  • DELETE：删除数据库中的数据
  • SELECT：选择（查询）数据

DDL

DDL用于定义数据库的结构，包括如下SQL语句

  • CREATE TABLE：创建表
  • ALTER  TABLE：更改表
  • DROP TABLE：删除表
  • CREATE INDEX：建索引
  • DROP INDEX：删索引

DCL

DCL用来控制数据库的访问，包括如下SQL语句

  • GRANT：授予访问权限
  • REVOKE：撤销访问权限
  • COMMIT：提交事务处理
  • ROLLBACK：事务处理回退
  • SAVEPOINT：设置保存点
  • LOCK：对数据库的特定部分进行锁定

创建表空间

--创建表空间
CREATE TABLESPACE YEYUFAN
DATAFILE 'D:\SOFT\ORACLE\YEYFUAN.dbf'
size 100m
autoextend on
next 10m;
--删除表空间
DROP TABLESPACE YEYUFAN;

创建用户以及赋权

--创建用户
CREATE USER YEYUFAN
identified by YEYUFAN
default tablespace YEYUFAN;
--Oracle数据库中常用角色
connect --连接角色，基本角色
resource --开发者角色
dba--超级管理员角色
--给用户授权超级管理员角色
GRANT DBA TO YEYUFAN;

Oracle数据类型

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

表的管理

建表

--创建一个person表
CREATE TABLE person(
       pid NUMBER(20),
       pname VARCHAR2(10)
);

修改表结构

--添加一列
ALTER TABLE person ADD gender NUMBER(1);
--修改列类型
ALTER TABLE person MODIFY gender CHAR(1);
--修改列名称
ALTER TABLE person RENAME COLUMN gender TO sex;
--删除一列
ALTER TABLE person DROP COLUMN sex;

数据增删改

--添加一条记录
SELECT * FROM PERSON;
INSERT INTO  person (pid,pname) VALUES (1,'小明');
INSERT INTO  person (pid,pname) VALUES (2,'小白');
--添加多条记录
INSERT ALL
INTO person(pid,pname) values(3,'张三')
INTO person(pid,pname) values(4,'李四')
select 1 from dual;
--修改一条记录
UPDATE person 
SET pname ='小马' WHERE pid=1;
--删除表中记录
DELETE FROM person;
--删除表
DROP TABLE person;
--删除表结构
TRUNCATE TABLE person;
--先删除表，再创建表，效果等同于删除表中全部记录
--数据量大的情况下，尤其在表中带有索引的情况下，效率高
--索引提高查询效率，但是影响增删改效率

序列

  • 序列:不属于任何一张表，但是可以逻辑与表做绑定
  • 序列:默认从1开始，依次递增，主要用来给主键赋值使用
  • dual:虚表，只是为了补全语法，无意义

scott用户下的表结构

  • DEPT
  • EMP
  • BONUS
  • SALGRADE

单行函数应用

字符函数

SELECT UPPER('yes')  FROM    dual;
SELECT LOWER('YES')  FROM    dual;

数值函数

SELECT round(26.14,2)  FROM    dual;--四舍五入，后面的参数表示保留的数字
SELECT trunc(26.14,2)  FROM    dual;--直接截取，不看后面位数的数字
SELECT MOD(10,3)  FROM    dual; --求余数

日期函数 

--查询出emp表中所有员工入职距离现在几天
SELECT sysdate-e.hiredate 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((sysdate-e.hiredate)/7) FROM   emp e;

转换函数 

SELECT to_char(SYSDATE,'fm yyyy-mm-dd hh24:mi:ss') FROM dual;   
--字符串转日期
SELECT to_date('2020-3-13 17:41:34','fm yyyy-mm-dd hh24:mi:ss') FROM dual;

通用函数

--算出emp表中所有员工的年薪
--奖金里面有null值,null和任何数值做运算，都是null值
SELECT e.sal*12+NVL(e.comm,0) FROM  emp e;

条件表达式

条件表达式的通用写法

--给emp表中员工取中文名
SELECT e.ename,
       CASE e.ename
         WHEN  'SMITH' THEN '小明'
         WHEN  'ALLEN' THEN '小白'
         WHEN  'WARD' THEN '小花'
           ELSE  '无名'
             END
FROM  emp e;
--判断emp表中员工工资，若高于3000，显示高收入，高于1500低于3000显示中等收入，其余显示低收入
SELECT e.sal，
       CASE 
         WHEN e.sal>3000 THEN '高收入'
         WHEN e.sal>1500 THEN '中等收入'
           ELSE '低收入'
           END
FROM emp e;

条件表达式Oracle专用写法

SELECT e.ename,
       DECODE( e.ename,
           'SMITH' , '小明',
           'ALLEN' , '小白',
           'WARD',  '小花',
             '无名')中文名
FROM  emp e;
--Oracle除了起别名，都用单引号

多行函数（聚合函数）

SELECT COUNT(1) FROM emp;--查询总数量
SELECT SUM(sal) FROM emp;--工资总和
SELECT MAX(sal) FROM emp;--最大工资
SELECT MIN(sal) FROM emp;--最低工资
SELECT AVG(sal) FROM emp;--平均工资

分组统计

--分组查询
---查询出每个部门的平均工资
--分组查询中，出现在group by 后面的原始列，才能出现在select后面
--没有出现在group by后面的列，想在selecet后面出现，必须加上聚合函数
--聚合函数有一个特性，可以把多行记录变成一个值
SELECT e.deptno,AVG(e.sal)
FROM   emp e
GROUP BY e.deptno;
--查询平均工资高于2000的部门信息
SELECT e.deptno,AVG(e.sal) asal
FROM   emp e
GROUP BY e.deptno
HAVING AVG(e.sal)>2000;
--所有条件都不能使用别名来判断
       
--查询出每个部门工资高于800的员工的平均工资
SELECT e.deptno,AVG(e.sal) asal
FROM   emp e
WHERE  e.sal>800
GROUP BY e.deptno;
--Where是过滤分组前的数据，having是过滤分组后的数据
--表现形式：where必须在group之前，having在group by 之后
--查询平均工资高于2000的部门
SELECT e.deptno,AVG(e.sal) asal
FROM   emp e
GROUP BY e.deptno
HAVING AVG(e.sal)>2000;

多表查询

--多表查询中的一些概念
--笛卡尔积
SELECT COUNT(1) FROM  emp;  --12
SELECT COUNT(1) FROM  dept; --4 
SELECT * FROM  emp e,dept d;  --48
--等值连接
SELECT * FROM  emp e,dept d   WHERE e.deptno=d.deptno;   
--内连接
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;
--
SELECT * FROM emp e,dept d
WHERE  e.deptno = d.deptno(+);
--查询出员工姓名，员工领导姓名
--自连接，站在不同的角度把一张表看成多张表
SELECT e1.ename,e2.ename
FROM emp e1,emp e2
WHERE  e1.mgr=e2.empno
--查询员工姓名，员工部门名称，员工领导姓名，员工领导部门名称
SELECT e1.ename,d1.dname,e2.ename,d2.dname
FROM emp e1,emp e2，dept d1,dept d2
WHERE  e1.mgr=e2.empno
AND e1.deptno=d1.deptno
AND e2.deptno=d2.deptno;

子查询

--子查询
--子查询返回一个值
--查询出工资和scott一样的员工信息
SELECT * FROM emp WHERE sal 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与分页查询

--oracle分页查询
--rownume行号：当我们做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,t.* FROM  (
SELECT * FROM emp e ORDER BY e.sal DESC) t
WHERE rownum<11)tt
WHERE rn>5;

视图

  • 视图就是提供一个查询的窗口
  • 创建视图必须要有dba权限
  • 视图可以屏蔽一些铭感字段
  • 保证总部和分部数据及时统一
--查询语句创建表
CREATE TABLE emp AS SELECT * FROM  scott.emp;
SELECT * FROM    emp;
--创建视图
CREATE VIEW v_emp AS SELECT ename,job FROM emp;
--查询视图
SELECT * FROM   v_emp;
--修改视图[不推荐]
UPDATE v_emp SET job ='CLERK'   WHERE ename='ALLEN';
COMMIT;
--创建只读视图
CREATE VIEW v_emp1 AS SELECT ename,job FROM emp WITH READ ONLY;

索引

索引就是在表的列上构建一个二叉树，达到提高查询效率的目的，但是会影响增删改的效率

--单列索引
CREATE INDEX idx_ename ON emp(ename);
--单列索引触发规则，条件必须是索引列中的原始值
SELECT * FROM emp WHERE ename='SCOTT';    
--单行函数，模糊查询，都会影响索引的触发
--复合索引
CREATE INDEX idx_enamejob ON emp(ename,job);
--复合索引中第一列为优先检索列
--如果要触发复合索引，必须包含有优先检索列中的原始值
SELECT * FROM emp WHERE ename='SCOTT' AND  job='CLERK';--触发复合索引
SELECT * FROM emp WHERE ename='SCOTT' or  job='CLERK';--不触发索引
SELECT * FROM emp WHERE ename='SCOTT';--触发单列索引

PL/SQL编程语言

PL/SQL编程语言：对SQL语言的扩展，使SQL语言有过程化编程的特性

  • PL/SQL编程语言比一般的过程化编程语言更加灵活高效
  • PL/SQL编程语言主要用来编写存储过程和存储函数

声明方法

--赋值操作可以用:= 也可以使用into查询语句赋值
DECLARE
       i NUMBER(2) :=10;
       s VARCHAR(10) :='小明';
       ena emp.ename%TYPE;--引用型变量
       emprow emp%ROWTYPE;--记录型变量
BEGIN
  dbms_output.put_line(i);
  dbms_output.put_line(s);
  SELECT ename INTO ena FROM emp WHERE empno=7369;
  dbms_output.put_line(ena);
  SELECT * INTO emprow FROM emp WHERE empno=7369;
  dbms_output.put_line(emprow.ename ||' 的工作为:'||emprow.job);
END;

PL/SQL中的if判断

--输入小于18的数字，输出未成年
--输入大于18小于40的数字，输出中年人
--输入大于40的数字，输出老年人
DECLARE
  i NUMBER(3) :=&ii;
BEGIN
  IF i<18 THEN
    dbms_output.put_line('未成年');
    ELSIF i<40 THEN
    dbms_output.put_line('中年人');
    ELSE
    dbms_output.put_line('老年人');
    END IF;
END;

PL/SQL中loop循环

while循环

DECLARE
i NUMBER(2) :=1;
BEGIN
  WHILE i<11 LOOP
    dbms_output.put_line(i);
    i :=i+1;
  END LOOP;
END;

exit循环

DECLARE
i NUMBER(2) :=1;
BEGIN
  LOOP
    EXIT WHEN i>10;
    dbms_output.put_line(i);
    i :=i+1;
  END LOOP;
END;

for循环

DECLARE

BEGIN
  FOR i IN 1..10 LOOP
    dbms_output.put_line(i);
    END LOOP;
END;

PL/SQL中的游标

游标：可以存放多个对象，多行记录

--输出emp表中所有员工的姓名
DECLARE 
CURSOR c1 IS SELECT * FROM emp;
emprow emp%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO emprow;
    EXIT WHEN c1%NOTFOUND;
    dbms_output.put_line(emprow.ename);
  END LOOP;
  CLOSE c1;
END;
--给指定部门员工涨工资
DECLARE
CURSOR c2(eno emp.deptno%TYPE) IS SELECT empno FROM emp  WHERE deptno=eno;
en emp.empno%TYPE;
BEGIN
  OPEN c2(10);
  LOOP
    FETCH c2 INTO en;
    EXIT WHEN c2%NOTFOUND;
    UPDATE emp SET sal=sal+100 WHERE empno=en;
    COMMIT;
    END LOOP;
  CLOSE c2;
END;

存储过程

存储过程：提前编译好的一段PL/SQL语言，放置在数据库， 可直接调用，一般都是固定步骤的业务 

Create [or replace] PROCEDURE 过程名 [（参数名 in/out 数据类型）]
AS
BEGIN
       PLSQL子程序体;
End;
--给指定员工涨100工资
CREATE OR REPLACE PROCEDURE p1(eno emp.empno%type)
AS
BEGIN
  UPDATE emp SET  sal =sal+100 WHERE    empno =eno;
  COMMIT;
END;

--测试p1
DECLARE
BEGIN
  p1(7369);
END;


SELECT * FROM  emp WHERE empno ='7369';

存储函数

CREATE OR REPLACE FUNCTION 函数名(NAME IN TYPE，NAME IN TYPE) RETURN 数据类型 IS
结果变量 数据类型;
BEGIN
  RETURN(结果变量);
END 函数名;

存储过程和存储函数的区别：在于函数可以有一个返回值，过程没有

--通过存储函数实现计算指定员工的年薪
--存储过程和存储函数的参数都不能带长度
CREATE OR REPLACE FUNCTION f_sal(eno emp.empno%TYPE) RETURN NUMBER
IS
       s NUMBER(10);
BEGIN
  SELECT sal*12+NVL(comm,0）INTO s FROM  emp WHERE empno =eno;
  RETURN  s; 
END;
--测试存储函数f_sal
--存储函数调用时，返回值需要接收
DECLARE 
  s NUMBER(10);
BEGIN
  s:= f_sal(7369);
  dbms_output.put_line(s);
END;

--out类型参数如何使用
--使用存储过程来算年薪
CREATE OR REPLACE PROCEDURE p_yearsal(eno emp.empno%TYPE,yearsal OUT number)
IS
       s NUMBER(10);
       c emp.comm%TYPE;
BEGIN
    SELECT sal*12,NVL(comm,0) INTO s,c FROM emp WHERE empno =eno;
    yearsal :=s+c;
END;
--测试存储过程p_yearsql
DECLARE
    yearsal NUMBER(10);
BEGIN
  p_yearsal(7369,yearsal);
  dbms_output.put_line(yearsal);
END;
--IN和OUT类型参数的区别是什么？
--凡是涉及到into查询语句赋值或者:=赋值操作的参数，必须使用OUT来修饰

=

一沙一世界，一花一天堂。君掌盛无边，刹那成永恒。