【Oracle】笔记

廖_小凡
95 阅读1分钟

纯文字阐述，内容比较干。并且，由于考虑到时间长了恐有所遗漏，便即兴记录，并没有对内容进行筛选、排序。因此，大家在阅读时可以直接Ctrl + F进行检索。

细节积累

  1. 字符串前有一个' '（空字符），故首字母的位置是1，不是0。
  2. 使用group by时，select中出现的字段必须在group by中存在。
  3. 查询实例：show parameter name。（注：当前用户必须至少拥有sysdba权限）
  4. 插入时，若要插入特殊字符'&'，不能直接放入字符串中，因为&是变量修饰符，只能通过字符串拼接实现；
  5. 数据表存储于用户的数据群中，会占据存储空间。delete是 DML，删除记录后并不释放空间，可回滚；truncate是 DDL，执行后释放空间，不可回滚。
  6. 表约束和列约束相同，只是定义位置不同。列约束定义于列后，表约束定义于所有列后。
  7. 日期运算： (1)+ 1：增加一天；(2)+ 1/24：增加一小时，都会进位。
  8. 查询对象结构：desc 对象名。 （对象如：数据表）
  9. 查看Oracle版本方法：(1)登录时（cmd）显示；(2)sqlplus -v；(3)select * from v$version。
  10. 查询下编辑：在查阅语句后加for update。
  11. .5等同于0.5。
  12. 查询clob类型字段：dbms_lob.substr(字段)。

二、数据表

2.1 配置相关命令

# 修改表名
1、alter table old_name rename to new_name;
2、rename old_name to new_name;

# 修改字段名
alter table 表名 rename column 旧名 to 新名;

# 设置默认值
1、alter table 表名 modify(字段 default 值); # 影响的是新增值，不会改变已存储值
# 若字段之前无默认值，则执行命令前必须先手动为所有行赋初始值
2、alter table 表名 set 字段 = default;		# 可将所有已存储数据重置为 default

# 删除表，同时清除约束
drop table 表 cascade constraint;

2.2 修改字段的规定

  1. 若修改数据类型，则此列中已存储数据必须全为null（不是字符串'null'，而是指“空”，无数据）。
  2. 若此类的数据类型为数值类型，(1)减少长度，同上；(2)增大长度，无限制。
  3. 若此类的数据类型为字符类型，减少长度时，只要修改后的长度足够容纳已存储数据，无限制。

三、escape

Oracle语法规定，通配符%表示任意数量的任何字符，_表示单个字符。如：like '%%%'将匹配所有行，like '%_%'同样。

若需求是匹配包含字符_的所有行，仅使用like无法实现，escape用于解决这类问题。

如：

like '%a_%' escape 'a'

简单而言，escape的作用是指定转义符（必须是单个字符）。

很多语言将\作为转义符，Oracle不是（只是将\视为一个字符），即like '%\_%'匹配的是包含字符\的所有行。

四、数据字典dba_profiles

参考笔记二，P24.4。

此字典记录了口令和资源限制的配置信息，如：登录允许密码错误次数、密码过期时间。如下：（当前用户身份至少是sysdba才能访问，当前用户为基础数据库管理员sys）

在这里插入图片描述

修改此表数据不能使用update，而是需要在命令行完成。

示例。

alter profile default limit failed_login_attempts 3;

将登录允许密码错误次数设置为3次。

五、集合运算

参考笔记二，P58.3。

5.1 概述

常见的集合运算有4种：

  1. union：联合。相当于“并集”，去重，按照第一列升序排序；若每个查询都有 null（空记录），只保留一个。
  2. union all：完全联合。不去重，默认不排序，并保留所有 null。由于不去重，故效率高于union。
  3. intersect：交集。
  4. minus：差集。假设运算是A minus B（A、B是select查询），等价于A - A intersect B。

集合运算条件：

  1. 所有select查询（在后称为“子查询”）的列数必须相同并类型一一对应，列名无需相同；
  2. 不允许“子查询”使用order by，只能联合后使用（即只能在最后使用）；
  3. 以第一个“子查询”的列作为运算结果的列；
  4. 去重依据：在“子查询”内、“子查询”间，根据不同记录之间所有列的数据是否相同判断（一一对应比较），不考虑列名（包括相同列名）。

5.2 示例

示例数据。

在这里插入图片描述

在这里插入图片描述

运算示例。

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

六、in

in与not in我们都很熟悉，不知道大家有没有注意一个细节，就是明明存在满足in/not in的记录，可结果集为空。为何如此？（注：null 是空，指“无数据”，不是'null'或'NULL'）

规则：

  1. 任何类型数据与 null 运算都为空。
  2. 包含空值的连接字符串||，同与空字符串''连接。

我先将所有情况列举出来，再说明原因，在此规定格式：

where A B C

A 是字段，B 是in/not in，C 是条件列表。

八种情况：

  1. A 中存在空记录，B 是in，C 有null。结果：正常匹配，但 null 行无法显示。（正常匹配非 null 记录）
  2. A 中存在空记录，B 是in，C 无null。结果：正常匹配。
  3. A 中存在空记录，B 是not in，C 有null。结果：无记录。
  4. A 中存在空记录，B 是not in，C 无null。结果：正常匹配非 null 记录。
  5. A 中不存在空记录，B 是in，C 有null。结果：正常匹配。
  6. A 中不存在空记录，B 是in，C 无null。结果：正常匹配。
  7. A 不存在空记录，B 是not in，C 有null。结果：无记录。
  8. A 中不存在空记录，B 是not in，C 无null。结果：正常匹配。

原因：

  1. in相当于=，not in相当于!=。
  2. 表示“为空”的格式是is null，“不为空”是is not null。
  3. 满足in的条件是“只要有一个匹配”，满足not in的条件是“必须所有都不匹配”。
  4. 无论in还是not null，都是逐条记录进行比较。

总结：只有当 “使用not null” 和 “存在空记录” 这两个条件都满足时，才会无记录。因此，在查询时应先清空 null（where xx is not null）。。

PS：大家基于这四点，套用到以上八种情况，就知道为何“无记录”了。

八、类型转换

8.1 隐式转换

  1. 插入、更新时，转换为字段的类型，如：Integer → number；
  2. 查询时，转换为属性的类型，如：number → int；
  3. 字符与数字比较时，转换为数值类型；
  4. 字符与日期比较时，转换为日期类型；
  5. 用||连接时，转换为字符类型。

8.2 显式转换

如下图：

在这里插入图片描述

图解：

  • A：to_char(d, 格式码)。

格式码： yyyy→ 年，year→ 年份英文全称，month→ 月份，MM/mm→ 月，day/dy→ 星期，dd→ 日，hh24/HH24→ 小时，mi→ 分钟，ss→ 秒，am/pm→ 朝夕。

示例：

在这里插入图片描述

  • B：to_number(c)

规定： c必须是数字型字符串才可以转换。

示例：

在这里插入图片描述

  • C：to_char(n, 格式码)；

格式码： 9（数字）→ 保持数字原状；0（数字）→ 若n的位数小于格式码的位数时，用0代替；$→ 美元；L（字母）→ 本地货币；.（小数点）→ 保持本身。

示例：

1、格式码：'9999.99'。

在这里插入图片描述

2、格式码：'0000.00'。

在这里插入图片描述

3、格式码：'$9999.99'或'$0000.00'。

在这里插入图片描述

4、格式码：'L9999.99'。

在这里插入图片描述

特例： 若格式码的长度大于数字的长度，则显示##...。

示例：

在这里插入图片描述

  • D：to_date(c, 格式码)。

同样遵循转换A中的格式码。（后续补充）

最后

如果大家想要了解一些Oracle知识点，可查阅博文《【Oracle】知识点》。

本文持续更新中。。。

avatar
文章
阅读
粉丝