现在的位置: 首页 > 数据库 > 正文

Oracle从入门到精通[秦靖]复读笔记

2014年11月03日 数据库 ⁄ 共 6915字 暂无评论 ⁄ 阅读 1,078 次
文章目录

Oracle的安装

在Window环境下,Oracle安装所需要的物理内存(ARM)至少1G以上、虚拟内存是物理内存的2倍左右,占用约5G的硬盘空间,处理器至少550MHz以上。

数据库基础

E-R(Entity-Relationship)图又叫实体-联系图,是描述现实世界的概念模型。构成E-R图的基本要素是实体、属性和联系。

实体是客观存在并可以相互区别的事物,既可以是人、物,也可以是抽象的概念。在E-R图中实体用矩形表示,内写实体名。

属性是实体所具有的某一特性,在E-R图中用椭圆表示,并用无向边将其与相应的实体连接起来。

联系在信息世界中反映实体内部或实体之间的联系,实体内部指组成实体的各属性之间的联系,实体之间指不同实体集之间的联系。在E-R图中用菱形表示,菱形框内写是联系名,并用无向边分别与有关实体连接起来,并同时在无向边旁标明联系的类型。

实体间联系的类型有3种,分别是一对一、一对多和多对多,在E-R图反映为1:1、1:n和m:n。一对一指两个实体集间(表),每个实体(行)至多与另个实体有一个联系,如一个学生只能有一个座位;一对多指两个实体集A和B间,A中实体仅对应B中一个实体,而B中一个实体则可对应A中多个实体,如一个学生只能属于一个班级,而一个班级则有多个学生;多对多指两个实体集间每个实体集的实体都可跟另外一个实体集中实体产生多个对应关系,如顾客和商品之间,一个顾客可买多个商品,一个商品可以被多个顾客购买。

一般先绘制实体属性图,再绘制实体联系图(E-R)。

MERGE语句语法

MERGE INTO table_name1

USING table_name2

ON (condition)

WHEN MATCHED THEN update_dml

WHEN NOT MATCHED THEN insert_dml;

SELECT中子句顺序:from和where都在数据库核心中执行,故必须在前面。而group by和order by则是得到结果集后的排序操作,需要在外核中执行,而Order by则是最后执行的。

排序时NULL:在默认的升序排列中,NULL值位于最后,即默认NULL为最大值;如果是DESC降序排列时,NULL将被放在最前面。不过,也可以在语句中指定,可以在Order By之后加NULLS FIRST或NULLS LAST指定NULL值出现位置。

单值函数

  • ROUND(column1,n),对值取n位小数四舍五入,若n为小数则仅截取整数部分;若为负数则向小数点前n位进行四舍五入。
  • TRUNC(column1,n),对值直接截取n位小数,不进行四舍五入,其他与ROUND相同。
  • SUBSTR(str,star_num[,length]),截取str中一部分,从star_num位置开始(第一个字符位置为1),若为负数则表示从字符串后往前数star_num个字符。另外,该函数可扩展为SUBSTRB()以字节为单位、SUBSTRC()以unicode字符为单位等。
  • INSTR(str,search_str[,star_num[,times]]),在str字符串中查找字符串search_str,从第star_num个字符开始查询(默认为1),查找第times次(默认1)出现的字符位置(第一个字符就是1),star_num为负则从后开始为起始位置。另外,该函数也可扩展为INSTRB()等。
  • INITCAP(str),将str字符串中所有单词的第一个字母转为大写,注意不是整个字符串的第一个,而是所有单词的。
  • REPLACE(str,search_str[,replace_str]),在str字符串中搜索search_str,并将之替换为replace_str,若replace_str省略则表示从str中删除search_str。
  • TRIM([mode] [char FROM] str),在str中指定mode下删除字符char,其中mode可选项有leading前删除、trailing后删除和both前后删除,char是指定删除的字符(默认为空格)。
  • LTRIM/RTRIM(str[,set_char]),从字符串str左/右删除出现在set_char中的字符(注意,是字符,不是字符串!),如果未设置则默认空格。
  • SYSDATE/CURRENT_DATE,该函数没有参数,分别返回系统/会话的当前日期,一般配合TO_CHAR()函数返回指定格式的字符型日期,一般情况下两者相同,但如果修改了会话时间则会不同。
  • SYSTIMESTAMP,同sysdate类似,不过它返回的系统时间还包括时区信息,也会精确到微秒。
  • ADD_MONTHS(date,add_num),返回指定日期加上add_num个月后的日期,若add_num是负数则表示之前的日期。需要注意的是,date数据类型必须日期,而不能是字符。
  • DBTIMEZONE/SESSIONTIMEZONE,无参数,分别返回数据库/会话的时区,一般相同,但修改了会话时区后会不同。
  • LAST_DAY(date),返回指定日期date对应月份的最后一天。
  • NEXT_DAY(date,week_num),返回当前日期之后一周内(并不是下一周)指定星期week_num的日期。
  • NEW_TIME(date,timezone1,timezone2),返回时间date在时区timezone1转换到timezone2后的时间。
  • CAST(expr as type_name),强制将exp转换为type_name所定义的数据类型。
  • COALESCE(expr),返回列表中第一个不为NULL的表达式,若都为NULL则返回NULL。
  • LNNVL(condition),主要处理简单条件无法处理的NULL值,只能用于WHERE子句,如果条件为真则返回假及NULL(unknown),即相当于取反再加上NULL值,如lnnvl(a>10)返回a<=10及a is null。
  • NVL(expr1,expr2),如果expr1为NULL值则返回expr2,否则还返回expr1,要求两个表达式(列)数据类型必须一致或可以让系统进行隐式转换。
  • USER,无参数,返回当前会话的登录名,如select user from dual;。
  • DECODE(expr,search,result[,search,result]…[,default]),相当于CASE表达式,在判断比较值时可以给expr加sign()函数,然后search值1和-1。

PL/SQL基础

PL/SQL支持SQL的所有数据类型,并且在此基础上扩展了新的数据类型,也支持SQL的函数以及运算符,还可以存储在Oracle服务器中。

优点:

  • 连接数据库很耗费资源,而SQL语句则需要每次连接,会浪费大量资源在网络连接上;PL/SQL可将多语句合成语句块只需要一次连接,大大节省了网络资源开销。
  • 程序模块化,增加程序复用率及使用难度。
  • 可采用逻辑循环控制程序流程。
  • 处理异常及错误而不中断程序运行。

变量声明语法:var_name datatype [not null] [:=|default value];

数据类型分类:

  • 标量类型变量:单一类型,不存在组合,如数值、字符、日期、布尔等。VARCHAR2,在作为字段存储时最长4000字节,作为变量最长32767字节。日期类型有,DATE和TIMESTAMP。
  • 复合类型变量:由几种单一类型组合而成的一个结构体,可存储多个值。常用的有记录类型(使用之前要声明记录或使用%ROWTYPE引用表中数据类型,使用时跟普通变量类似直接放在字段后即可)、索引表类型(类似数组,可通过键值查询值,这个值可以是单个值也可以是一行数据,此时相当于值是一个数组。通常使用binary_integer作为索引值,也可使用字符串)和VARRAY数组(下标从1开始,适合数据较少时使用,最多可在初始化时使用100个)。
  • 引用类型变量:使用一个其他数据项的引用。

【逻辑判断

if … then …、else …、elseif …、end if等都是独立的语句,结束后要使用分号。

CASE语句和CASE表达式的区别:

  • 语句以end case结尾且加分号,而表达式以end结尾无分号;
  • 语句各when语句间加分号,而表达式无分号;
  • 语句的when之后是一个执行语句或函数,而表达式when之后是一个表达式或特定值;
  • 语句中else是可选项,但当when条件中未找到任何匹配项时会报case_not_found的异常(语句更严谨且具有逻辑保护性),而case表达式在没有匹配项又没有else时返回NULL;

【循环】

  • 循环语句之前可以通过<<…>>来给语句加标签命名,这样在END时就可以后跟该命名。
  • LOOP…END LOOP循环需要与条件控制语句一起使用,否则会出现死循环。如与IF…THEN…EXIT或EXIT WHEN(首选)。
  • 在FOR循环中,for index_name in lower_bound .. upper_bound loop语句中,index_name是循环计数器,不是变量,所以不需要声明,也不能赋值。

【PL/SQL中的DML和DDL

  • 在PL/SQL中,使用select主要用于给变量赋值,若结果集为空值则报NO_DATA_FOUND的异常,若结果集不只一个而变量只是个标量变量会报TOO_MANY_ROWS的异常。
  • PL/SQL中,使用DDL需要使用动态SQL,即将SQL语句赋值给变量,然后通过EXECUTE IMMEDIATE命令来执行。该命令替代了DBMS_SQL包,性能有所提高,但依然不建议过程中使用该命令。

【PL/SQL中的函数】

  • 创建函数时要声明入参和出参,入&出参使用var_name {IN|OUT|IN OUT} data_type形式,出参使用RETURN data_type形式。IN仅针对入参,当需要得到函数内多个数据可采用OUT,在函数调用以后使用DBMS_OUTPUT包进行输出。
  • 查看函数可以在数据字典USER_PROCEDURES中查看其属性,在USER_SOURCE中查看其源脚本,这两个数据字典属于视图。

【PL/SQL中的游标】

  • 游标是数据的缓存区,可以让用户像操作数据一样操作查询出来的数据集,提供了一种从集合性质的结果中提取单条记录的手段。
  • 游标中通常使用FOR循环,如FOR var_name in cur_name,其中变量var_name不需要声明,它默认使用游标的数据类型。
  • 游标属性,在游标后可跟属性作为一种布尔表达式或值,如cur_name%FOUND可判断游标中值是否遍历完,cur_name%ROWCOUNT累计到目前为止使用FETCH提取数据的行数。
  • 每当运行SELECT或DML语句时,PL/SQL会打开一个隐式游标,它不受用户控制,默认名称是“SQL”,属性值是最近一个执行SQL的语句。

PL/SQL中的异常

预定义异常:Oracle提供25种命名的预定义异常,如NO_DATA_FOUND、ZERO_DIVIDE(除数为0)、STORAGE_ERROR(内存溢出)等,在异常处理部分可直接通过这些命名来捕获并处理。

非预定义异常:发生错误有错误编号和描述,但该错误并没有命名,所以不能直接捕获,需要人为的为异常添加名称才能在异常处理部分捕获并处理。为非预定义异常命名需要先声明异常(数据类型是EXCEPTION),再把名称和异常编号关联(如使用PRAGMA EXCEPTION_INIT(name,num))。除了命名外,预定义异常和非预定义异常没有任何区别。

自定义异常:用来处理与实际业务相关的非程序错误,使用时要先声明异常关联异常(数据类型是EXCEPTION,关联异常使用PRAGEMA EXCEPTION_INIT(name,num),自定义错误号num的范围是-20999~-20000之间的负整数),然后在过程中使用RAISE显式抛出,最后就可以在异常处理块中进行处理。

事务和锁

事务

  • 事务是一组包含一条或多条语句的逻辑单元,每个事务都是一个原子单位。
  • 事务四属性:原子性(逻辑工作单位整体要么执行要么不执行)、一致性(事务执行前数据库不会有任何改变)、分离性(并发事务间不干扰)、持久性(一旦事务提交将是永久性修改)。

参见“Oracle锁分类简介”。

SQL*Plus

连接数据库

  • 连接默认数据库:运行cmd打开命令窗口,输入命令“sqlplus user/password”即可连接默认数据库,或可以使用非登录方式,输入命令“sqlplus /nolog”。
  • 若已登录其他数据库,想切换数据库可以使用命令“connect user/password @db_name”;若未登录则可直接在登录sqlplus时指定,使用命令“sqlplus user/password @db_name”。

常用命令

  • 重复执行上条语句,直接按键盘上“/”即可;
  • 在上条语句上追加文本,使用命令“append text”,其中,text是要追加的文本,需要注意的是,如果需要跟之前的语句之间有空格则text中文本开始之前要先有个空格,运行命令后会出现追加后的语句,该语句会成为缓冲区最近一条语句,所以直接按键盘上“/”即可执行该语句;
  • 替换上条语句中部分文件,使用命令“change /old_text/new_text”,运行该命令后得到替换后的语句,用“/”可执行该语句;
  • 删除缓冲区全部语句,使用命令“CLEAR BUFFER”;
  • 显示缓冲区内容,使用命令LIST;
  • 打开含有缓冲区内容的记事本,使用命令“ed”;
  • 保存缓冲区内容,使用命令“SAVE filename
  • 运行缓冲区内容也可使用命令“RUN”;
  • 运行文件,使用命令“START filename”,其中,文件件可加扩展名也可不加,默认为.sql;

格式化输出

  • 设置列别名,可以select语句执行前执行命令“COLUMN oldname HEADIND newname”;
  • 格式化数据显示格式,使用命令“COLUMN column_name FORMAT dataformat”,常用格式如9(代表一个数字字符)、0(指定位无值则显0)、$(美元符号)、MI(负号)、,(千分位)等;
  • 设置每页显示行数,使用命令“SET PAGESIZE n”可设置每页显示的行数,n默认为14,同时可以使用命令“SET NEWPAGE n”设置页间空格数;查看每页显示行数及页间空格数可分别使用命令“SHOW PAGESIZE”和“SHOW NEWPAGE”;
  • 设置每行显示字符数,使用命令“SET LINESIZE n”,n默认为80,显示当前字符数使用命令“SHOW LINESIZE”;
  • 显示查询所用时间,使用命令“SET TIMING ON”,若取消显示则把ON换为OFF;
  • 设置查询结果不显示标题,使用命令“SET HEADING OFF”,显示则把OFF换为ON;
  • 设置查询结果不显示“已选择行数…”,使用命令“SET FEEDBACK OFF”,否则使用ON;

查询结果保存

使用命令:

SPOOL path.filename

SPOOL OFF

需要注意的是,只有执行完SPOOL OFF之后,才把查询结果真正写入到指定文件中。

默认输出文件中是有查询语句的,若要省略可使用命令“SET TERM OFF”。

设置表标题

在制作报表时有时需要给表写大标头,可使用命令“TTITLE title”;

控制文件和日志文件

控制文件(Control File)是一个二进制文件,主要用来存放数据库的名字、数据文件的位置等信息;日志文件(Log File)主要用来存放数据库中数据变化的操作。没有控制文件数据库就无法启动,没有日志文件数据库的信息就无法完全恢复。

一个控制文件只属于一个数据库,它在创建数据库时自动创建,当数据库信息改变时也自动随之改变。它不能被手动修改,只能由Oracle本身修改。

日志文件分为重做日志文件和归档日志文件两种,重做日志文件(Redo Log File)主要记录数据库操作过程,用于恢复数据库。在非归档模式下,如果重做日志写满则把前面的日志文件覆盖掉。

查看控制文件和日志文件

查看控制文件结构使用命令“desc v$controlfile”,查看日志文件结构使用命令“desc v$logfile”。而相看其相应的内容,则可直接使用select语句,如:

1)查看当前是否为归档模式:

首先可以先看一下数据字典,使用命令“desc v$database”,可以看到有字段name(数据库名)和log_mod(当前日志模式);然后,我们就可以直接select查看了,“select name,log_mod from v$database;”。

2)查看控制文件存放位置:

select name,status from v$controlfile;

控制文件的扩展名是.ctl,其大小取决于创建数据库时所提供的参数信息。

表空间管理见“Oracle表空间基础”。

用户管理见“用户管理基础”。

声明:该笔记转载自《Oracle从入门到精通》,仅用于个人学习,若侵犯到您的版权敬请告知!

» 声明:本站文章源于个人经验总结或书籍、互联网转载,内容仅用于个人学习,请勿转载,否则后果自负!

给我留言

留言无头像?