oracle笔记

最新推荐文章于 2022-04-14 21:31:10 发布

Blijiojiodibuliduo

最新推荐文章于 2022-04-14 21:31:10 发布

阅读量275

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/burning1996/article/details/88527783

版权

oracle 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

1) 数据库（Database）：数据库是一种软件产品，用于存放数据，管理数据的存储仓库，是有效组织在一起的数据集合。

2) 常用数据库软件（端口）

大型：Oracle（1521）、DB2（50000）、Sybase
中小型：SqlServer（1433）、MySql（3306）
小型：Access，PostgreSQL，SQLite

3) RDBMS（Relational Database Management System）：关系型数据库管理系统，是数据库软件中用来操纵和管理数据库的部分，用于建立、使用和维护数据库，简称rdbms。

4) SQL（Structured Query Language）：SQL语言是用来在关系数据库上执行数据操作、检索及维护所使用的标准语言，是一个综合的、通用的关系数据库语言。

5) DBA（Database Administrator）

数据库管理员
Oracle默认帐户名：sys 和 system

6) 表（table）

表（table）是数据库存储的基本单元，对应于现实世界中的实体对象，是一个二维结构。
行：row，record（记录）
列：column，field（字段）
表名/列名最长30个字符，不能以数字开头

7) 视图（view）

视图也被称为虚表（虚拟的表），视图对应于一条 select 语句，这条查询语句得到的结果集被赋予一个名字，即视图的名字，以后可以像操作表一样操作这个视图。
使用视图的主要目的是简化复杂查询

8) 索引（index）：索引是用来在数据库中加速表查询的数据库对象，通过快速路径访问方式快速定位数据，可有效较少磁盘 I/O 操作，提高访问性能

9) 序列（sequence）：序列是一种用来生成唯一数字值的数据库对象。序列的值由Oracle程序按递增或递减顺序自动生成，通常用来自动生成表的主键值，是一种高效率获得唯一键值的途径。

10) 存储过程（procedure）：存储过程是一种 PL/SQL 存储程序单元，主要用于在数据库中完成特定的操作或者任务，如果在程序中经常需要执行某个操作，可以基于这些操作建立一个过程，用于简化客户端的开发和维护，以及提高执行性能。

11) 函数（function）：PL/SQL 中函数被用来执行复杂的计算，并返回计算结果。

12) 包（package）

包中一种比较特殊的PL/SQL程序，它并不是一个PL/SQL存储程序块，而是用于将相关的存储过程和函数组织起来，组成PL/SQL存储程序组。
包由两个独立部分组成：包头和包体。

13) 触发器（trigger）

PL/SQL程序中的触发器的结构类似于函数和过程，与函数和过程不同，触发器是在事件发生时隐式的运行的。
相当于Java语言汇总的事件监听器。

14) 同义词（synonym）

同义词是数据库对象的别名，目的是简化SQL查询，以及隐藏数据库对象的信息。

15) 数据类型

数字
- number(n) // 数字（总长n位）
- number(n, m) // 浮点数（总长n位，小数点m位）；例：number(4, 2) => 99.99
字符串 // 每个英文字符占用一个字节，每个中文字符按编码不同占用2-4个字节；如UTF-8:2-4个字节
- char(n) // 定长字符；最长n个字符，放入的数据不足则补空格；可以不指定长度，默认为1；最多2000个英文，1000个汉字（GBK）
- varchar(n) // 变长字符；最长放入n个字符，放入的数据是多少长度就占多大空间
- varchar2(n) // Oracle自己定义的变长字符串；必须指定长度
- long // varchar2加长版，变长字符，最多2G；限制较多，每个表只能有一个，不能作为主键、不能建立索引、不能出现在查询条件中
- clob // 定长或变长字符串，最多4G；Oracle建议开发中使用clob替代long
日期
- date
  
  在数据库中存储固定为7个字节，格式为：
  第1字节：世纪+100
  第2字节：年
  第3字节：月
  第4字节：天
  第5字节：小时+1
  第6字节：分+1
  第7字节：秒+1
null 的值被视作最大
- 空值和任何数据做算术运算，结果都是null
- 空值和字符串做连接操作，结果相当于空值不存在

16) 数据库命令
16.1) 设置的命令仅当前窗口有效，关闭窗口重新连接仍按默认方式显示

clear scr // 清屏
/ // 执行上一条命令，忽略sql plus命令
set pages[ize] 100 // 每100行数据分页显示，0为不分页
set linesize 200 // 设置行长度；显示200字符再换行
col[umn] col_name for[mat] 99/a2 // 设置列宽度；要与列的类型一致
set autocommit on/off; // 打开/关闭（默认）自动提交

16.2) DQL（数据查询语句）

select id from tab_name as 别名 where col_name = 'val'; // select；from；as；where

查询语句执行顺序：
select：少用 * 号，尽量取字段名称（Oracle 在解析过程中，通过查询数据字典将*号依次转换成所有的列名，消耗时间）
from：从后往前、从右到左（数据量较少的表尽量放在后面）
where：自下而上、从右到左（将能过滤掉最大数量记录的条件写在where子句的最右）
group by：从左到右分组（最好在 group by 前使用 where 将不需要的记录过滤掉）
having：消耗资源（尽量避免使用，having 会在检索出所有记录之后才对结果集进行过滤，需要排序等操作）
order by：从左到右（排序，消耗资源）

16.3) DDL（数据定义语句）

create table new_tab_name as select id from old_tab_name;` // 复制表数据+结构（不复制约束条件）
- insert into new_tab_name (select id from tab_name); // 复制表数据+结构
- create table new_tab_name as select id from tab_name where 1<>1; // 复制表结构，不复制数据
drop table tab_name; // 删除表
truncate table tab_name; // 截断表，功能上等同于delete+commit
rename tab_name to tab_name_new; // 更改表名
alter table tab_name add (col_name char(4)); // 增加列，列只能增加在最后，不能插入
- alter table tab_name rename col_name to new_col_name; // 修改列名
- alter table tab_name modify(col_name char(8)); // 修改列的数据类型
- alter table tab_name drop column col_name; // 删除列

16.4) DML（数据操控语句）

insert into tab_name (col_name1, col_name2, …) values (1001, …);
- insert into tab_name values (1001,…); // 可省略col_name，但值必须写齐全；如果有数据没提供，则用null
update tab_name set col_name1 = new_var1, col_name2 = new_var2… where …;
delete [from] tab_name where col_name = val;
- drop table // 删除表结构和数据
- truncate // 删除表数据，保留表结构
- drop 和 truncate // 都不可以回退
- delete // 仅删除数据，可以回退

16.5) TCL（事务控制语句）

commit // 提交数据
- 保存数据的临时空间被释放
- 数据上的锁被释放
- 所有的会话都能看到被改变的结果
rollback [to mark_1]; // 回滚数据；
- 只能看到 savepoint mark_1 语句之前的数据
savepoint mark_1; // 保存书签

16.6) DCL（数据控制语句）

grant select on tab_name to user_name_open; // 现有用户user_name将查看tab_name表的权限赋予user_name_open
- select id from user_name.tab_name; // 现有用户user_name_open查询user_name账户下的tab_name表
revoke select on tab_name from user_name_open; // 现有用户user_name将tab_name表的select权限收回

16.7) 其他、关键字

select id from tab_name order by col_name; // 排序（升序A-Z）
- select id from tab_name order by 1 desc; // 排序（降序Z-A）
- select id from tab_name order by col_name1, col_name2 desc; // col_name1升序，col_name2降序
select distinct col_name from tab_name;// 查找唯一值；必须只能跟在select后；*` 可以用，但是相当于没作用
select col_name from tab_name where col_name [is] not null; // 比较null
select id from tab_name where lower(col_name) [not] in (1001, 'tom'); // in；not in
select id from tab_name where lower(col_name) between 5000 and 8000; // between…and…；[低值，高值]
select count(id) from tab_name where table_name like '_emp%'; // like；_表示单个字符，%表示0到多个字符
and
or
|| / concat(char1, char2) // 连接两个数据
desc tab_name; // 查看表的结构
create table tab_name (sex char(1) default 'M'; // 指定默认值
show user; // 查看用户
show errors; // 显示编译警告和错误
rownum // 行号（Oracle伪列）
rowid // 列id（Oracle伪列）

17) 字符函数（SQL语句大小写不敏感，数据大小写）

upper() // 大写
lower() // 小写
initcap() // 首字母大写
length() // 取长度
select lpad(col_name, 10, '*') from tab_name; // 左补丁
select rpad(col_name, 10, '#') from tab_name; // 右补丁
replace() // 字符替换
trim() // 去除前后的空格
ltrim(c1[, c2]) // 从c1的左边截去c2；没有c2就去除空格
rtrim(c1[, c2]) // 从c1的右边截去c2；没有c2就去除空格
substr(char, [m[, n]]) // 获取字符串的子串，返回char中从m位开始取n个字符；如果m=0则从首字符开始，如果m=负数，则从尾部开始；如果没有设置n或n的长度超过char的长度，则取到字符串末尾；char的首位计数从1开始
instr() // 去

18) 数字函数

select mod(col_name, 2) from tab_name; // 取余
round(123.456, 2) // 四舍五入；=123.46
- round(123.456, -1) // =120
trunc(12.3456, 2) // 截取；=12.34
- trunc(12.3456, -1) // =10
ceil(45.678) // 取大于或等于n的最小整数值；=46
floor(45.678) // 取小于或等于n的最大整数值；=45

19) 日期函数

select sysdate from dual; // sysdate -> 当前系统时间；dual -> 虚表，单行单列
months_between(sysdate, date) // 两个日期之间的月份数
add_months(sysdate, [-]3) // 为参数日期增加指定月份
last_day(sysdate) // 参数时间点所在月份的最后一天
set col_name = to_date('2014-8-22','yyyy-mm-dd')
常用日期格式（sqlplus默认：DD-MON-RR -> 日-月-年）
- yyyy // 四位数字年，如：2011
- year // 全拼的年，如：twenty
- month // 全拼的月，如：november/11月（中文）
- mm // 两位数字的月，如：11
- mon // 简拼的月，如：nov（中文没有简拼）
- dd // 两位数字日
- day // 全拼的星期，如：Tuesday
- dy // 全拼的星期，如：tue
- am // 上午/下午，如：am/pm
- 年（RR）和年（YY）的区别：YY格式是Oracle早期，存在千年虫问题

20) 转换函数

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual; // 把日期转换为字符，按指定格式输出
insert into tab_name values ( to_date('2014-08-28','yyyy-mm-dd') ); // 把字符转换为日期，按指定格式输出
select to_number('$7,912,345.67', '$9,999,999.99') * 10 col_num from dual; // 把字符$7,912,345.67，按指定格式输出，乘以10

21) 单行函数（包含字符函数、数字函数、日期函数、转换函数，还有一些其他常用的函数）

nvl(null, var) // 为null取var值
nvl2(null, var1, var2) // 不为null取var，为null取var2
coalesce(…) // 返回参数列表中第一个非空参数，参数列表中最后一个值通常为常量
- 如：发年终奖金；bonus不是null，年终奖为bonus；bonus是null，年终奖为salary*0.5；bonus和salary都是null，年终奖为100
- select name, bonus, salary coalesce(bonus, salary*0.5, 100) as 年终奖 from tab_name;
select col_name
case col_name when 'val_1' then col_name_1 * 0.1
case col_name when 'val_2' then col_name_1 * 0.2
else col_name_1
end as 别名
from tab_name;
select col_name
decode(col_name, 'val_1', col_name_1 * 0.1, 'val_2', col_name_1 * 0.2, col_name_1 ) as 别名
from tab_name;

22) 组函数（聚合函数）

count() // 统计记录条数
avg() // 求平均值
sum() // 只针对数字类型
max() // 只针对数字类型
min()
- 如果函数中写列名，默认忽略null值
- avg / sum 针对数字的操作
- max / min 对所有数据类型都可以操作

23) 分组查询

group by col_name
- select 后出现的列，凡是没有被组函数包围的列，必须出现在 group by 短语中
- 如果 group by 短语中的列，没有出现在 select 短语中，不会出错，信息不够全

24) 单行比较运算符（都只能和一个值比较）

> < >= <= = <> // 返回一行时使用
selelct col_name from tab_name where col_name > all(select col_name from tab_name where col_name = 'val') // 大于最大值
selelct col_name from tab_name where col_name > any(select col_name from tab_name where col_name = 'val') // 大于最小值
selelct col_name from tab_name where col_name > in(select col_name from tab_name where col_name = 'val') //
>all <all >any <any in // 返回多行时使用

25) having子句

having子句用于对分组后的数据进行过滤
注意区别where是对表中数据的过滤；having是对分组得到的结果数据进一步过滤
select deptno, count(id) from emp_xxx group by deptno having count(id) > (select count(id) from emp_xxx where deptno = 30); // 哪个部门的人数比部门30的人数多

26) 子查询

select ename, salary, job, deptno from emp_xxx where (deptno, salary) in (select deptno, max(salary) from emp_xxx where deptno is not null group by deptno); // 求每个部门拿最高薪水的是谁？
子查询的条件是单列还是多列没关系，关键是要分清返回的是单行还是多行，是单行则用>,<等这些，是多行则用in,all等这些

27) 关联子查询

select ename,salary,deptno from emp_xxx as a where salary < (select avg(nvl(salary, 0)) from emp_xxx where deptno = a.deptno); // 哪些员工的薪水比本部门的平均薪水低？不再和整个部门的平均薪水比较

28) select col_name from tab_name as a where [not] exists (select 1 from tab_name where col_name = a.col_name);

exists关键字判断子查询有没有数据返回，有则为true，没有则为false
exists不关心子查询的结果，使用子查询中select后面写什么都可以，本例中我们写常量1
执行顺序：从主查询开始，把主查询中的col_name数据传入子查询，作为条件中的参数

29) 结果集操作

两个结果集必须结构相同，即列的个数、列的顺序、列的数据类型一致时，才能做集合操作
合集：union 和 union all
select col_name1,col_name2 from tab_name where col_name1=val_1 union select col_name1,col_name2 from tab_name where col_name2>2;
- union去掉重复记录，union all不去重
- union排序，union all不排序
- 满足功能的前提下，优选union all
交集：select col_name from tab_name where col_name1='val' intersect select col_name2 from tab_name where col_name3>val;
差集：select col_name from tab_name where col_name1='val' minus select col_name2 from tab_name where col_name3>val;

30) 内连接

select id from tab_name1 as a join tab_name2 as b on a.col_name = b.col_name;
子表中外键值为null的数据不包含在结果集中
父表中主键值没有被参照的数据不包含在结果集中
tab_name1叫做驱动表，tab_name2叫做匹配表；等值连接方式下，驱动表和匹配表位置可以互换，不影响结果集
执行方式：不论谁做驱动表，都会遍历驱动表，在匹配表中查找匹配数据

31) 外连接

左外连接：tab_name1 left outer join tab_name2 on 条件 // 以左边表为驱动表
右外连接：tab_name1 right outer join tab_name2 on 条件 // 以右边表为驱动表
全外连接：tab_name1 full outer join tab_name2 on 条件 // 驱动表和匹配表可以互换
外连接特征
- 如果驱动表在匹配表中找不到匹配记录，则匹配一行空行
- 外连接的结果集 = 内连接的结果集 + 驱动表在匹配表中匹配不上的记录和空值
- 外连接的本质是驱动表中的数据一个都不能少

32) 总结：

select col_name1, col_name2…, 表达式, 函数,…
from tab_name
where 条件
group by col_name
having 带组函数的条件
order by col_name;

33) sqlplus执行.sql脚本文件

@ c:\test.sql

34) 约束条件

列级约束条件
create table tab_name(
col_name1 number(2) primary key, // 主键约束
col_name2 char(2) not null, // 非空约束，not null只能定义在列级
col_name2 char(2) constraint col_name2_nn not null,
col_name3 varchar(4) unique, // 唯一约束
col_name4 varchar2(8) check(col_name4 in ('F','M')) // 检查约束
);
表级约束条件
create table tab_name(
col_name1 number(2),
col_name2 char(2),
col_name3 varchar(4),
col_name4 varchar2(8),
col_name5 char(1),
constraint tab_name_col_name1_pk primary key(col_name1), // 主键约束
constraint tab_name_col_name3_uk unique(col_name3), // 唯一约束
constraint tab_name_col_name4_ck check(col_name4 in 'F','M','f','m')) // 检查约束
constraint tab_name_col_name5_fk foreign key(col_name5) references tab_name1(id) // 外键约束
);
建表之后创建约束条件，结构清晰，可读性好，方便操作约束条件
alter table tab_name add constraint tab_name_col_name5_fk foreign key(col_name5) references tab_name1(id) on delete set null;
// on delete set null：主表数据被删除，从表关联数据置为null
alter table tab_name add constraint tab_name_col_name5_fk foreign key(col_name5) references tab_name1(id) on delete cascade;
// on delete cascade：级联删除（主表数据删除，从表相关联数据统统删除）
alter table tab_name drop constraint tab_name_col_name5_fk // 删除约束条件

35) 数据字典

user_tables // 用户所有的数据表
user_constraints // 用户所有的约束条件
user_objects // 用户所有的对象（表、视图、索引…）
user_views // 用户视图
all_tables // 用户能访问的数据表（包括自己的和别的用户允许自己访问的）
all_constraints // 用户能访问的约束条件
all_objects // 用户能访问的对象（表、视图、索引…）
数据字典格式如下：
- user_xxx // 用户自己的对象
- all_xxx // 用户能访问的对象
- dba_xxx // 数据库所有的对象
select constraint_name, constraint_type from user_constraints where table_name = 'tab_name'; // 查询tab_name表的约束条件名和类型

36) 视图

create [or replace] view v_view_name as select col_name from tab_name where col_name = val; // 创建[更新]视图
可创建多表关联的复杂视图，但复杂视图不允许DML操作
drop view v_view_name; // 删除视图
select id from v_view_name; // 视图的使用和表相同，且查询到的是实时动态被更改后的数据
insert into v_view_name values(val); // 对简单视图的DML操作可以影响到基表，但不能违反基表的约束条件
create [or replace] view v_view_name [(alias[, alias…])] as select col_name from tab_name where col_name = val [with check option]; // 表示通过视图所做的修改，必须在视图的可见范围内
create [or replace] view v_view_name [(alias[, alias…])] as select col_name from tab_name where col_name = val [with read only]; // 只读；保证视图对应的基表数据不会被非法修改
select object_name from user_objects where object_type='view'; // 在user_objects中查询所有视图名称
select text from user_views where view_name='v_view_name'; // 在user_views中查询指定视图

37) 索引

索引的结构：数据+地址
索引一旦被简历就将被Oracle系统西东维护，查询语句中不用指定使用哪个索引
数据变更频繁的表，索引会影响查询性能
如果数据表有PK/Unique约束，索引会自动创建，除此之外，索引需要手动创建
create index idx_tab_col_name on tab_name(col_name1, col_name2); // 创建索引
alter index idx_tab_col_name rebuild
select idx_tab_col_name from user_indexes where tab_name = 'tab_name'; // 查询索引
drop index idx_tab_col_name; // 删除索引

38) 序列

表可以用序列产生的值作为主键，也可以不用
序列可以为一个或多个表产生主键，也可以不用；建议一个序列为一个表产生主键
序列这种对象在Oracle、db2等数据库中有，在mysql、sql server中没有
create sequence seq_name // 创建
[start with 1 ] // 从1开始（默认）
[increment by 1 ] // 步进为1（默认）
[nomaxvalue] // 不设置最大值
[nocycle] // 一直累加，不循环
[cache 10]; // 缓存序列个数，有助于提高效率，但可能造成跳号
select seq_name.nextval from dual; // 增加序列值，然后返回
select seq_name.currval from dual; // 返回当前值
drop sequence seq_name; // 删除序列

39) 存储过程

-- 存储过程：输入班号，输出最高分的学生名字和总成绩
CREATE OR REPLACE PROCEDURE cal_lxt(
p_class_id IN NUMBER,
p_student_name OUT CHAR,
p_total_score OUT NUMBER
)
IS -- 定义变量
BEGIN -- 程序体
SELECT student_name, total_score
INTO p_student_name, p_total_score
FROM (
SELECT stu.student_name, sum(per.test_score) total_score
FROM t_student_lxt stu JOIN t_performance_lxt per
ON stu.student_id = per.student_id
WHERE stu.class_id = p_class_id
GROUP BY stu.student_name
ORDER BY total_score DESC
) WHERE ROWNUM < 2;
END;
-- 打开输出
SET serveroutput ON;
-- 匿名块，测试过程或函数，调用
DECLARE
p_student_name CHAR(20);
p_total_score NUMBER;
BEGIN
cal_lxt(&no, p_student_name, p_total_score);
dbms_output.put_line(p_student_name);
dbms_output.put_line(p_total_score);
END;

40) 函数

-- 定义函数
CREATE OR REPLACE FUNCTION calculate_lxt(score NUMBER)
RETURN NUMBER
IS
-- 定义变量
i NUMBER; -- 整数
j NUMBER; -- 小数
result NUMBER;
BEGIN
-- 程序体
i := trunc(score);
j := mod(score, 1);
IF j<0.25 THEN
result := 0;
elsif j<0.75 THEN
result := 0.5;
ELSE
result := 1;
END IF;
RETURN i + result;
END;
-- 显示编译警告和错误
show errors;
-- 调用
SELECT name, s1, s2, s3, s4, (s1+s2+s3+s4)/4 AS s, calculate_lxt((s1+s2+s3+s4)/4) AS total_s FROM ielts_lxt;

41) 触发器

-- 触发器
CREATE TABLE emp_his AS SELECT id FROM EMP WHERE 1=2;
CREATE OR REPLACE TRIGGER tr_del_emp
BEFORE DELETE --指定触发时机为删除操作前触发
ON scott.emp
FOR EACH ROW --说明创建的是行级触发器
BEGIN
--将修改前数据插入到日志记录表 del_emp ,以供监督使用。
INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;
DELETE emp WHERE empno=7788;
DROP TABLE emp_his;
DROP TRIGGER del_emp;