oracle初级锦集

这篇具有很好参考价值的文章主要介绍了oracle初级锦集。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

【版权声明】未经博主同意,谢绝转载!(请尊重原创,博主保留追究权)
https://blog.csdn.net/m0_69908381/article/details/131040299
出自【进步*于辰的博客】

1、其他知识点链接

  1. 细节、经验;
  2. 索引;

2、伪列

2.1 rownum

参考笔记一,P18.3/4、P19.5。

2.1.1 概述

rownum是虚拟字段,不真实存储,在返回结果集时生成,“宏观”作用类似序号。
在每次查询时,从1开始给结果集编号。常与<<=连用。而与>>=连用时,由于rownum是在查询记录时逐个生成,并迭代。因此,若rownum的判断在第1行记录时就不满足,则无法返回记录,即未迭代,rownum始终为1,最终结果集中无任何记录,与between...and...连用时同理。

注意: \color{red}{注意:} 注意:
无论任何SQL语句,执行顺序都是:from → where → group by → having → select → order by

由于rownum是伴随select生成,故与order by连用时,会导致rownum混乱。故通常是多层嵌套,先进行排序,再使用rownum进行筛选。(如下)

扩展: \color{green}{扩展:} 扩展:
在Oracle中,常言的top-n查询其实是在rownum编号后,使用rownum进行判断,从而获取前n条记录的查询方法。(如下)

2.1.2 示例

数据表:emp(no, ..., sal)
需求:查询工资6 ~ 10名的员工的所有信息。

写法一:

select e2.*
from (select e1.*, rownum rn
	  from (select * from emp order by sal desc) e1) e2
where rn between 6 and 10

前2层仅对结果集进行一次排序,不做筛选,在第3层时,才进行筛选。rnrownum的别名,由于rn属于第2层的结果集,非rownum,故已固定,因此可以直接使用rn between 6 and 10筛选出第6 ~ 10名。

写法二。

select e2.*
from (select e1.*, rownum rn
	  from (select * from emp order by sal desc) e1
	  where rownum <= 10) e2
where rn > 5

在第2层时就进行结果集筛选,rownum初始为1,满足rownum <= 10,则返回记录,同时rownum迭代,如此反复直到条件不满足,这样就查询出前10条记录(工资最高的前10名员工)。此时rownum固定,别名是rn,第3层可以直接使用rn筛选出第6 ~ 10名。

补充说明: \color{red}{补充说明:} 补充说明:
两种写法在第1层时,都得到根据工资降序排序后的所有员工信息。
写法二较写法一,效率高很多。
因为写法一是在第3层才进行结果集筛选,由于条件是rn between 6 and 10,使用的是rnrn属于结果集,已固定。因此在筛选时,会遍历根据工资降序排序后的所有员工信息。
而写法二,在第2层时,是通过rownum <= 10进行筛选,由于rownum的生成机制,第2层仅遍历前10条记录(工资最高的前10名员工)。因此,第3层仅遍历10条员工信息。

2.2 nextval、currval

参考笔记一,P23.18。

2.2.1 序列

这两个伪列基于 序列 \color{green}{序列} 序列,我暂未对序列的相关理论进行整理,大家可以查阅这篇博文《Oracle数据库序列》(转发)。

“序列”是一种按照一定规则自动增加或减少数字的数据库对象,主要用于主键(新增时填充主键)。创建示例:

create sequence swq_emp_empId
increment by 1
start with 1000
nocycle
cache 20

seq_emp是序列名;increment是递增值,默认值为1start是初始值;nocycle表示不循环;cache 20表示进行缓存,缓存大小为20

大家也可以使用图形化界面操作:
oracle初级锦集

2.2.2 概述

nextval是序列的下一个值,currval是序列的当前值。

使用位置:

  1. select子句中,不包括子查询的select子句;
  2. insert 语句的select子句或values子句中;
  3. update 语句的set子句中。

不能使用位置:

  1. 包含distinctgroup byhavingorder by的视图SQL语句的select子句中;
  2. select、update、delete的子查询中;
  3. 包含defaultcreate tablealter table语句中。

操作,

--修改序列--
alter sequence 序列名 ...;// 后面格式与创建语句相同
// 注:后面语句中没有start with,并且修改的值不能少于当前值

2.2.3 示例

insert into emps values(swq_emp_empId.nextval, '张三', 7500.00, 10);

update emps
set sal = 10000.00
where emp_id = swq_emp_empId.currval;

必须先获取nextval,才能使用currval

4、约束

参考笔记一,P22.18~22。

“约束”是一种对数据表字段存储数据的限制,分为主键(primary key)、唯一键(unique)、外键(foreign key)、check约束、not null约束。

4.1 操作命令

--添加约束--
alter table 表名 add constraint 约束名 约束类型(字段);

--删除约束--
alter table 表名 drop constraint 约束名;

--启用/禁用约束--
alter table 表名 enable/disable constraint 约束名;

注:

  1. 添加外键:alter table 表名 add constraint 约束名 约束类型(字段1) reference 主表名(字段2)
  2. 添加not null约束:alter table 表名 modify(字段 constraint 约束名 not null)。因为not null约束仅能定义于字段,即列约束,故不能使用...add constraint...命令添加;

4.2 数据字典

--当前用户旗下所有数据表的所有约束--
user_constraints
--当前用户旗下所有数据表的所有约束所属的字段名--
user_cons_columns

4.3 外键约束

外键会将两个数据表进行关联,进而对子表相应字段的数据进行限制。这里就有个问题:若主表记录被删除,那么子表中相应数据该何去何从?

从上述可知创建外键的命令格式,若在其后增加一条子句,可以决定这些数据的去向,有以下4种子句:

  1. on delete cascade:表示子表中相应数据将连同删除;
  2. on delete set null:表示子表中相应数据将被置空(null);
  3. Restrict:表示若子表中存在相应数据,则提示主表记录不能删除;
  4. on delete no action:表示子表不受影响。

4.4 check 约束

此约束用于限制数据范围(多用于数值字段),其内不允许使用伪列,如:rownum

可以调用sysdateuid()user()userenv()

5、数据类型

5.1 char族

Oracle中char族数据类型与MySQL中相同,大家可查阅博文《MySQL知识点锦集》的第3项,在此不作赘述。

5.2 number(a, b)

参考笔记一,P20.13。

此数据类型为数字,可存储小数。其中,a 是数字位数(包括小数),b 是精确小数位数。

规则:

  1. b > 0,表示精确b位小数,并四舍五入;
  2. b < 0,表示精确到小数点左b位,并四舍五入,故只能存储整数。如:number(5, 3),可存储五位数整数,个位和十位都为0
  3. b = 0,则只能存储整数;
  4. a < b,则只能存储-1 ~ 00 ~ 1的小数,且小数点右b - a位及其后都必须是0(前后四舍五入都要满足),即精确b位小数。

6、内置函数

6.1 单行函数

参考笔记一,P15.3、P39.1。

摘要 参数说明 返回值类型/返回值 说明
substr(s, a, b) a-开始索引,可为负值;b-截取长度 截取。截取方向始终向右
round(a, b) b-精确位数 四舍五入。若b < 0,则向左精确,故round(a)等价于round(a, 0)
upper(s) 转大写
lower(s) 转小写
initcap(s) 首字母大写
length(s) 返回长度
concat(a, b) 拼接,类似||
trunc(s) 截取,类似round(a)
sysdate 获取系统时间
instr(s1, s2[, a][, b]) s2-查找字符,a-开始索引,b-第几个 查找。instr(s1, s2)等价于instr(s1, s2, 1, 1)
lpad/rpad(s1, n, s2) 左 / 右填充。表示将s1s2向左 / 向右填充成长度为n的字符串
replace(s1, s2, s3) 替换。表示将s1中的s2s3替换
trim(s) 去除前后空格
trim(leading/trailing/both/无 a from b) 去除 b 中开头 / 结尾 / 开头和结尾 / 开头以及结尾的 a
mod(a, b) 等同于a%b,余数符号跟 a

6.1.1 lpad/rpad(s1, n, s2)

示例。

select lpad(rpad('csdn', 7, '#'), 10, '*') result from dual

结果:
oracle初级锦集
n < s1.length,则无论lpad/rpad(),结果都只显示s1的前n个字符(从左往右)。

6.1.2 instr(s1, s2[, a][, b])

示例。

select instr(s1, s2, -2, 3) from dual;

表示在s1中,从倒数第2个开始,向左查找第3s2的位置。注意:无论a的正负,返回的都是绝对位置。

6.2 非空判断函数

参考笔记一,P18.1。

摘要 参数说明 返回值类型/返回值 说明
nvl(a, b) anull,返回 b,否则返回 a
nvl2(a, b, c) a不为null,返回 b,否则返回 c
nullif(a, b) 比较 a、b,若a = b,返回null,否则返回 a
case xx when w1 then c1 when w2 then c2 else c3 end xx = w1w1true,返回 c1;若xx = w2w2true,返回 c2;否则返回 c3
decode(xx, w1, c1, w2, c2, c3) 作用同case()

6.3 日期函数

参考笔记一,P15.5。

摘要 参数说明 返回值类型/返回值 说明
months_between(d1, d2) 返回d1d2相差的自然月数
add_months(d, n) 增加月数
next_day(d, '星期一') 返回 d 后的第1个星期一
last_day(d) 返回 d 当月的最后1天
round(d, 'dd') day四舍五入。'dd'是格式码,其他格式码:'CC'→ 世纪,'YY'→ 年,'mm'→ 月,'hh24'→ 小时,'mi'→ 分钟,'ss'→ 秒。其中,round(d, 'dd')等价于round(d)
trunc(d, 'dd') round()trunc(d, 'dd')等价于trunc(d)
extract(day from d) 获取 d 的天数。day是标识符,表示“天”。其他标识符:'year'→ 年,'month'→ 月,

7、视图(view)

推荐一篇博文《Oracle视图详解》(转发)。
参考笔记一,P22.24、P23.15~17。

7.1 概述

view可认为是一种绑定了SQL语句的原表“副本”,故当查询视图时,都会重新执行一次SQL语句查询原表,且修改视图记录等同于修改原表记录。

不过,若视图记录是由原表记录经处理后生成,即SQL语句中包含“数据处理”子句或条件时,不允许变动视图记录。“数据处理”子句或条件有:

  1. group函数;
  2. group by;
  3. distinct;
  4. 使用表达式定义的字段;
  5. 伪列rownum
  6. 原表在视图中未选择(未select)的字段的所有数据为非空且无默认值。

当然,并非只要SQL语句中包含了这6个子句或条件,就无法对视图进行全部DML。规则如下:

  1. 删除视图记录。要求SQL语句中不能包含前3个子句或条件。
  2. 修改视图记录。要求SQL语句中不能包含前5个子句或条件。
  3. 新增视图记录。要求SQL语句中不能包含所有子句或条件。

我们也可以干脆在SQL语句末添加with read only子句禁用视图DML。

7.2 数据字典

--当前用户旗下所有视图--
user_views
--显示视图中哪些字段允许DML--
user_updatable_columns

7.3 一个问题

假若SQL语句是:

create view v_emp_1(id, name, sal)
as
select emp_id, emp_name, sal
from emps
where dept_no = 10;

这里dept_no是外键,该视图记录为员工表中隶属部门10的所有员工信息。

现在,我把10改成20。前言道,查询视图是对原表的再次查询,这样修改后,该视图记录就变为部门20的所有员工信息。可是,view是原表的“副本”,既然创建视图时部门编号为10,说明当时我设定该视图的作用是用于快速查询部门10的所有员工信息,那么又怎能允许后续随意修改查询条件(dept_no = 10)。

因此,可以在SQL语句末添加with check option constraint 约束名子句,设置一个约束限制这种修改。

最后

暂不言。

本文持续更新中。。。文章来源地址https://www.toymoban.com/news/detail-501695.html

到了这里,关于oracle初级锦集的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • vue uniapp 同意验证码滑块验证

    发送验证码以及登录的时候会做验证,防止机刷等 效果图 字段 类型 默认值 说明 show Boolean false 是否显示验证码弹框 canvasWidth Number 310 主图区域的宽度 canvasHeight Number 160 主图区域的高度 imgs Array null 自定义图片,见下方例子 successText String 验证通过 验证成功时的提示文字 f

    2024年02月11日
    浏览(37)
  • 小程序如何设置用户同意服务协议并上传头像和昵称

    为了保护用户权益和提供更好的用户体验,设置一些必填项和必读协议是非常必要的。首先,用户必须阅读服务协议。服务协议是明确规定用户和商家之间权益和义务的文件。通过要求用户在下单前必须同意协议,可以确保用户在使用服务之前了解并同意相应的条款和规定。

    2024年02月05日
    浏览(37)
  • 【python知识点】锦集

    【版权声明】未经博主同意,谢绝转载!(请尊重原创,博主保留追究权) https://blog.csdn.net/m0_69908381/article/details/132368704 出自【进步*于辰的博客】 注:本文可能不适合 0-Python 基础的博友,因为对于各类知识点,我阐述的宗旨是“ 阐明使用细节 ”,而不是基础知识。 细节、

    2024年02月12日
    浏览(44)
  • Hive | 报错锦集

    大家好!我是初心,希望我们一路走来能坚守初心!🔥 今天跟大家分享的文章是 大数据中的Hive报错锦集分享,以此记录我在搭建大数据平台可视化展示中遇到的部分错误 ,希望能帮助到大家!本篇文章收录于 初心 的 Hive 专栏。 🏠 个人主页:初心%个人主页 🧑 个人简介

    2024年02月08日
    浏览(36)
  • C++ 常见错误锦集

    1、LNK1158错误:无法运行“rc.exe” 解决方法: 项目属性-常规-平台工具集里,选择带有XP的平台工具集(Visual Studio 2013 - Windows XP (v120_xp)); 2、fatal error: winapifamily.h: No such file or directory     (错误,找不到winapifamily.h文件) 解决方法: winapifamily.h是在win8中才有的头文件,如

    2024年02月07日
    浏览(34)
  • Unity 性能优化锦集

    Unity作为一款主流的游戏开发引擎,不仅提供了强大的编辑器和开发工具,还可以让开发者轻松地实现高质量的3D游戏。但是,随着游戏规模的不断扩大和玩家需求的增加,游戏的性能问题也变得越来越重要。因此,在使用Unity进行游戏开发时,我们需要注意性能优化方面的一

    2024年02月16日
    浏览(42)
  • goland+solidty报错锦集

    本文已参与「新人创作礼」活动,一起开启掘金创作之路。 1、write tcp 127.0.0.1:8888-127.0.0.1:7861: wsasend: An established connection was aborted by the software in your host machine.(已建立的连接被您的主机中的软件终止。) 原因: 个人判断是以为数据库超时时间设置过短,连接就被数据库掐断了

    2024年02月05日
    浏览(37)
  • Qt实现思维导图锦集

    序号 简述 文章导航 1 思维导图树形结构、不重叠且均匀分布、支持折叠和展开 核心树 2 菜单按钮风格、菜单提示风格、侧滑菜单、侧滑功能窗口 UI设计 3 支持JPEG、PNG、XML、JSON、PDF、SVG格式文件 数据导入导出 4 支持撤销回撤功能、显示节点操作流程、点击可跳转历史 撤销回

    2024年02月14日
    浏览(47)
  • 八大排序算法之快速排序(上篇)(未经优化的快排)

    目录 一.关于快速排序的总体算法思想 1.冒泡排序(交换排序) (以排升序为例) 2.快速排序的总体思想简介(以排升序为例)  二.快速排序单趟排序的算法接口设计(以排升序为例) 单趟排序实现的方法一:hoare版本(左右指针法) 代码实现:  单趟排序实现的方法二:挖坑法  代码实现

    2023年04月08日
    浏览(41)
  • 【Hadoop】 | 搭建HA之报错锦集

    大家好!我是初心,希望我们一路走来能坚守初心! 今天跟大家分享的文章是 Hadoop搭建过程中的报错锦集 ,希望能帮助到大家!本篇文章收录于 初心 的 Hadoop 专栏。 🏠 个人主页:初心%个人主页 🧑 个人简介:大家好,我是初心,和大家共同努力 💕欢迎大家:这里是CS

    2024年02月09日
    浏览(46)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包