oracle初学者笔记

永久表创建

 permanent永久表空间关键字

创建用户

创建用户未赋予权限 

分配链接权限

分配系统权限

 创建表

追加字段 

修改字段

修改字段名

删除字段名

删除表

 

 插入数据

修改语句

删除语句 

删除一行

删除所有数据并且不需要commit

 比较truncate和delete实现数据删除？

1.delete删除的数据可以rollback；

2.delete删除可能产生碎片，并且不释放空间；

3.truncate是先摧毁表结构，再重建表结构。

简单查询语句

1.精确查询

2.模糊查询

3.and运算符

 4.or运算符

 5.and和or运算符混合使用

 6.范围查询

 7.空值查询

8.去重复操作

9.排序

10.基于伪列的查询 

rowid本身就是物理地址，具有唯一性，可不用创建主键

 11.聚合统计

1）求和sum

 

2）平均avg

 3）最大值

4）最小值

5）统计个数

 6）分组统计（select后一定是分组聚合的条件或者是聚合函数）

 

 7）分组后条件查询having

 多表连接查询

1.内连接查询

需求：查询显示业主编号，业主名称，业主类型名称

需求：查询显示业主编号，业主名称，地址和业主类型 

需求：查询显示业主编号，业主名称，地址，所属区域，业主分类

需求：查询显示业主编号，业主名称，地址，所属区域，收费员，业主分类 

 2.外连接-左外连接

需求：查询业主的账务记录，显示业主编号，名称，年，月，金额。如果业主没有帐务记录也要列出姓名(使用SQL1999标准) 

oracle的语法

 3.外连接-右外连接

使用SQL1999标准

 oracle的语法

子查询（嵌套查询）

1.where 子句中的子查询

1）单行子查询

只返回一条记录

单行操作符

需求：查询2012年1月用水量大于平均值的台账记录

2）多行子查询-in运算符

需求：查询地址编号为1、3、4的业主记录

需求：查询地址含有‘花园’的业主信息

需求：查询地址不含有‘花园’的业主信息

 2.from子句中的子查询

需求：查询显示业主编号，业主名称，业主类型名称，条件为业主类型为’居民‘，使用子查询实现

 3.select子句中的子查询

需求：列出业主信息，包括ID，名称，所属地址

 需求:列出业主信息，包括ID，名称，所属地址，所属区域

分页查询 

1.简单分页

 2.基于排序的分页(rownum和排序优先级是生成rownum在排序)

字符函数

1.length()求字符串长度

 2.substr(源字符串，从第几位，截取字符数) 求字符串子串

3.concat() 字符串拼接

 

 数字函数

1.round() 四舍五入函数 

2.trunc()数字截取

3.mod()取模 

日期函数 

1.add_months()加月函数

2.last_day()求所在月最后一天

3.trunc()日期截取 （把时间截取掉）

 

按月截取 

按年截取

按小时截取 

 

按分钟截取

 

 转换函数

1.数字转字符串to_char()

2.日期转字符串to_char() 

3.字符串转日期to_date()

4.字符串转数字

空值处理函数NVL

 需求：显示价格表中业主类型ID为1的价格记录，如果上限值为NULL，则显示99999999999

 

空值处理函数NVL2

条件取值 decode()

 条件取值 case when then

 分析函数

第一种：值相同，排名相同，序号跳跃

 第二种：值相同，排名相同，序号连续

第三种：序号连续，无论值是否相等

 用分析函数做分页

 集合运算
1.并集

去重

不去重

2.交集

 

3.差集 

 用minus实现分页

 

 视图

使用视图的优点：

1.简化数据操作：视图可以简化用户处理数据的方式。

2.着重于特定数据：不必要的数据或敏感数据可以不出现在视图中。

3.视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。

4.提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口。

创建简单视图

需求：创建视图：业主类型为1的业主信息

查询视图 

修改视图

 带检查约束的视图

需求：根据地址表创建视图，内容为区域为2的记录

 

 只读视图的创建与使用

需求：将上边的视图变为只读

 带错误的视图创建

复杂视图创建 -多表关联 

创建聚合统计的复杂视图

 物化视图

什么是物化视图？

        视图是一个虚拟表（也可以认为是一条语句），基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化视图）。

        物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的。

创建手动刷新物化视图

需求：查询地址ID，地址名称和所属区域名称

手动刷新语句

 创建自动刷新物化视图

创建不生成数据的物化视图 

 创建增量更新的物化视图

序列

创建序列

查找序列的下一个值 

获取序列当前值

索引

什么是索引

        索引的用于加速数据存取的数据对象。合理的使用索引可以大大降低I/O次数，从而提高数据访问性能。

        索引是需要占据存储空间的，也可以理解为是一种特殊的数据。形式类似于树，而树的节点存储的就是每条记录的物理地址，也就是伪列。

创建普通索引 

创建唯一索引

需求：在业主表的水表编号一列创建唯一索引

 创建复合索引

  变量声明

 select into 赋值

属性类型（引用型 表名.列名%type）

属性类型（记录型 表%rowtype）

 异常

 判断语句

无条件循环

有条件循环

 for循环

 游标

什么是游标？

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。我们可以把游标理解为PL/SQL中的结果集。

简单游标

需求：打印业主类型为1价格表

 复杂游标

 for循环游标

 

 存储函数

什么是存储函数

存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用PL/SQL进行逻辑的处理。 

创建存储函数

 查询存储函数

存储过程

存储过程是被命名的PL/SQL块，存储与数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。

        存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：

1.存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值；

2.存储函数可以在select语句中直接使用，而存储过程不能。过程多数是别应用程序调用；

3.存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。

触发器

什么是触发器？

数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句在指定的表上发出时，oracle自动地执行触发器中定义的语句序列。

触发器可用于

1.数据确认

2.实施复杂的安全性检查

3.做审计，跟踪表上所作的数据操作等

4.数据的备份和同步

​​​​​​​