Oracle学习小记 – 夜与凡

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	数据类型	描述
1	Varchar,varchar2	表示一个字符串
2	NUMBER	NUMBER(m,n)表示一个小数，总长度是m,小数是n,整数是m-n
3	DATA	表示日期类型
4	CLOB	大对象，表示哒文本数据类型，可存4G
5	BLOB	大对象，表示二进制数据，可存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来修饰

view comments - NOTHING

Comments | NOTHING

no comment

Warning: Undefined variable $return_smiles in /www/wwwroot/blog.yeyufan.cn/wp-content/themes/Aurore/functions.php on line 1096

Cancel Reply

Markdown Supported while Forbidden

You are a surprise that I will only meet once in my life ...

戳我试试 OωO 嘿嘿嘿ヾ(≧∇≦*)ゝ

bilibili~	(=・ω・=)	Tieba