Oracle学习-编程阁

1.用库

Oracle数据库由数据文件、控制文件、日志文件组成。直接以文件的形式存放在磁盘当中。直接使用start脚本，内部的运行原理如下

sqlplus / as sysdba #连接 startup #启动

（1）nomount阶段

读取参数文件，分配内存(SGA+PGA)，启动后台进程，生成实例

startup nomount;

状态:STARTED

（2）mount阶段

读取控制文件，获取文件的位置，记录数据库状态，维护数据库的一致性

alter database mount;

状态:MOUNTED

（3）OPEN阶段

检查三大文件(参数文件、数据文件、在线日志文件)，检查数据库一致性

alter database open;

状态:OPEN

自己验证一致性的方法

select FILE# ,CHECKPOINT_CHANGE# from v$datafile;

（4）启动监听

！lsnrctl start alter system register; #注册加速监听

（5）关库

！lsnrctl stop #关监听 shutdown immediate #关库

2.下载操作工具PLSQLDeveloper

下载地址（官方）请稍候…https://www.allroundautomations.com/下载后连接不上可以配置一下oracle instant client：

在菜单栏找到Configure下的Preferences，有的版本Preferences是在Tools下。打开Preferences配置界面后，打开Connection相关配置项，可以找到Oracle Home和OCI library 两个配置项，一个放oracle instant client的文件位置，另一个选择dll。

3.Oracle目录

3.1admin 目录

记录Oracle实例的配置，运行日志等文件。每个实例一个目录。SID:System IDentifier 的缩写，是Oracle实例的唯一标记。在Oracle中一个实例只能操作一个数据库。如果安装多个库那么就会有多个实例，我们可以通过实例SID来区分。由于Oracle中一个实例只能操作一个数据库的原因oracle中也会使用SID来作为库的名称。

3.2cfgtoollogs 目录

下面子目录分别存放当运行dbca，emca，netca等图形化配置程序时的log。

3.3checkpoints 目录

存放检查点文件

3.4diag 目录

Oraclel1g新添加的一个重组目录。其中的子目录，基本上Oracle每个组件都有了自己单独的目录，在Oracle10g中我们一直诟病的log文件散放在四处的问题终于得到解决，无论是asm还是crs还是rdbms，所有组件需要被用来诊断的log文件都存放在了这个新的目录下。

3.5flash recovery_area(闪回区)目录

闪回区：分配一个特定的目录位置来存放一些特定的恢复文件，用于集中和简化管理数据库恢复工作。闪回区可存储完全的数据文件备份、增量备份、数据文件副本、当前的控制文件、备份的控制文件、file文件、快照控制文件、联机日志文件、归档日志、块跟踪文件、闪回日志。

3.6oradata 目录

存放数据文件。

3.7product 目录

OraclgRDBMS 的软件存放目录。RDBMS 即关系数据库管理系统(Relational Database Management System)。

4.Oracle的表空间

4.1永久表空间

表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表，所以称作表空间。

create tablespace永久表空间名称 datafile'永久表空间物理文件位置’size 15Mautoextend on next 10M permanent online;

4.2临时表空间

Oracle临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。重启数据库可以释放临时表空间。

5.Oracle角色权限

5.1对象权限(Object privileges)

对象权限是指在指定的表,视图。序列上制定执行动作的权限或权利。

5.2角色权限(Role privileges)

角色是可以授予用户的相关权限的组，该方法使权限的授予.撒回更加容易管理。

5.3 系统权限(System privileges)

为用户分配创建表、创建用户、创建视图、创建存储过程等权限。

6.Oracle命令学习使用

6.1用户管理（高频 DBA 命令）

创建新用户

-- 用户名test，密码123456，默认表空间users，临时表空间temp create user test identified by "123456" default tablespace users temporary tablespace temp;

赋权限（必配）

-- 登录权限 grant create session to test; -- 建表、视图、序列等常用开发权限 grant create table,create view,create sequence to test; -- DBA超级权限（慎用） grant dba to test;

修改密码、锁定 / 解锁用户

-- 修改密码 alter user test identified by "新密码"; -- 锁定用户 alter user test account lock; -- 解锁用户 alter user test account unlock;

删除用户（连带所有表）

drop user test cascade;

6.2表空间操作

查看所有表空间

select tablespace_name,file_name,bytes/1024/1024 size_m from dba_data_files;

新建永久表空间

create tablespace ts_test datafile '/u01/app/oracle/oradata/ORCL/ts_test01.dbf' size 100m autoextend on next 50m maxsize unlimited;

扩展表空间大小

alter database datafile '/u01/app/oracle/oradata/ORCL/ts_test01.dbf' resize 500m;

6.3表常用 DDL/DML 操作

建表

create table student( id number primary key, name varchar2(50), age number );

增删改查

-- 插入 insert into student(id,name,age) values(1,'张三',20); commit; -- oracle必须手动提交事务 -- 查询 select * from student; -- 更新 update student set age=21 where id=1; commit; -- 删除 delete from student where id=1; commit; -- 清空全表（不记录日志，不可回滚） truncate table student;

修改表结构

-- 新增字段 alter table student add email varchar2(100); -- 修改字段长度 alter table student modify email varchar2(150); -- 删除字段 alter table student drop column email; -- 重命名表 rename student to stu;

删除表

drop table student purge; -- purge直接彻底删除，不进回收站

6.4索引、序列、视图

-- 创建普通索引 create index idx_stu_name on student(name); -- 删除索引 drop index idx_stu_name;

自增序列（Oracle 无 auto_increment，用 sequence）

-- 创建序列 create sequence seq_stu_id start with 1 increment by 1; -- 插入取值 insert into student(id,name) values(seq_stu_id.nextval,'李四'); -- 查看当前值 select seq_stu_id.currval from dual;

视图

create view v_stu as select id,name from student; select * from v_stu; drop view v_stu;

6.5常用查询字典（DBA 必备）

-- 查看所有用户 select username from dba_users; -- 查看当前登录用户 select user from dual; -- 查看当前用户所有表 select table_name from user_tables; -- 查看表结构 desc student; -- 查看会话连接数 select count(*) from v$session; -- 查看数据库实例名、版本 select instance_name,version from v$instance; -- 查看监听端口、服务名 select value from v$parameter where name='service_names';

6.6事务、回滚

-- 提交 commit; -- 回滚所有未提交DML rollback; -- 保存点局部回滚 savepoint sp1; rollback to sp1;

6.7导出导入（expdp/impdp 数据泵，10g + 推荐）

导出整用户数据

expdp system/123456@ORCL schemas=test dumpfile=test.dmp directory=DATA_PUMP_DIR logfile=exp.log

导入用户数据

impdp system/123456@ORCL schemas=test dumpfile=test.dmp directory=DATA_PUMP_DIR logfile=imp.log

6.8多表内连接 / 左外连接（最常用）

内连接：查询员工 + 所属部门名称

SELECT e.emp_id, e.emp_name, e.salary, d.dept_name FROM emp e INNER JOIN dept d ON e.dept_id = d.dept_id;

左连接：保留所有员工，无部门也展示

SELECT e.emp_id, e.emp_name, NVL(d.dept_name, '无所属部门') dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.dept_id;

6.9子查询（单行 / 多行 IN、标量子查询）

标量子查询：查询工资高于平均工资的员工

SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);

IN 多行子查询：查询「市场部、技术部」所有员工

SELECT * FROM emp WHERE dept_id IN ( SELECT dept_id FROM dept WHERE dept_name IN ('技术部','市场部') );

EXISTS 高效关联子查询（大数据推荐，不走全表扫描）

查询存在员工的部门

SELECT d.dept_id, d.dept_name FROM dept d WHERE EXISTS ( SELECT 1 FROM emp e WHERE e.dept_id = d.dept_id );

NOT EXISTS：查询没有任何员工的空部门

SELECT * FROM dept d WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE e.dept_id=d.dept_id);

6.10分组聚合 + HAVING 分组后过滤

需求：统计每个部门人数、平均薪资，只筛选人数≥3 的部门

SELECT d.dept_name, COUNT(e.emp_id) emp_count, ROUND(AVG(e.salary),2) avg_sal, SUM(e.salary) total_sal FROM dept d LEFT JOIN emp e ON d.dept_id=e.dept_id GROUP BY d.dept_name HAVING COUNT(e.emp_id) >= 3 ORDER BY avg_sal DESC;

WHERE 过滤行；HAVING 过滤分组结果，不能混用。

6.11Oracle 分页查询（ROWNUM 经典两层嵌套）

需求：第 6~10 行数据

SELECT t.* FROM ( SELECT ROWNUM rn, emp_id, emp_name, salary FROM emp WHERE ROWNUM <= 10 ) t WHERE t.rn >= 6;

6.12CASE 条件分支（多条件统计）

给薪资分级，并统计每个等级人数

SELECT CASE WHEN salary >= 15000 THEN '高薪' WHEN salary >= 8000 THEN '中薪' ELSE '低薪' END sal_level, COUNT(1) user_num FROM emp GROUP BY CASE WHEN salary >= 15000 THEN '高薪' WHEN salary >= 8000 THEN '中薪' ELSE '低薪' END;

6.13行转列（PIVOT 经典报表统计）

按部门，统计每个部门不同薪资等级人数

SELECT * FROM ( SELECT d.dept_name, CASE WHEN e.salary>=15000 THEN '高薪' WHEN e.salary>=8000 THEN '中薪' ELSE '低薪' END sal_level FROM emp e LEFT JOIN dept d ON e.dept_id=d.dept_id ) PIVOT ( COUNT(1) FOR sal_level IN ('高薪' high, '中薪' mid, '低薪' low) );

6.14开窗函数（ROW_NUMBER/RANK，排名、分组内序号）

每个部门内部薪资排名

SELECT emp_id, emp_name, dept_id, salary, ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) rn, RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) rk FROM emp;

ROW_NUMBER()：同薪资序号连续不重复
RANK()：同薪资并列排名，序号跳号

6.15 取每个部门薪资最高的 1 个人

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) rn FROM emp ) t WHERE t.rn = 1;

6.16关联更新（多表联动 UPDATE）

根据部门表地址 loc，批量更新员工表备注字段

UPDATE emp e SET e.remark = (SELECT d.loc FROM dept d WHERE d.dept_id=e.dept_id) WHERE EXISTS(SELECT 1 FROM dept d WHERE d.dept_id=e.dept_id);

6.17多条件模糊查询 + 多排序

SELECT * FROM emp WHERE emp_name LIKE '%张%' AND salary BETWEEN 5000 AND 20000 AND hire_date >= TO_DATE('2022-01-01','yyyy-mm-dd') ORDER BY dept_id ASC, salary DESC;

6.18集合运算 UNION / UNION ALL / MINUS

-- 合并两个结果集，自动去重 SELECT emp_name FROM emp WHERE dept_id=1 UNION SELECT emp_name FROM emp WHERE dept_id=2; -- 直接拼接，不去重（性能更快，推荐） SELECT emp_name FROM emp WHERE dept_id=1 UNION ALL SELECT emp_name FROM emp WHERE dept_id=2; -- 差集：只在第一张表存在、第二张没有的数据 SELECT emp_id FROM emp WHERE dept_id=1 MINUS SELECT emp_id FROM emp WHERE salary<8000;

6.19常用函数嵌套综合示例

SELECT emp_id, UPPER(emp_name) emp_name_upper, TO_CHAR(hire_date,'yyyy-mm-dd') hire_dt, TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date)/12) work_year, NVL(bonus,0) bonus, salary + NVL(bonus,0) total_income FROM emp WHERE TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date)/12) >= 3;