Oracle培训教材.doc

第一章Oracle数据库简介Oracle Database 10g 是美国Oracle公司提供都一款数据库软件，该系统可以存储大量的、共享的、互相关联的数据，并且提供了对这些数据进行高效检索的机制。1.1 Oracle Database 10g的体系构架Oracle Database 10g 体系主要由文件系统、背景进程、存储模式等几个方面组成。文件系统文件系统包括可执行文件、参数文件、控制文件、数据文件、联机重做日志文件、输出文件和临时文件等。可执行文件时系统的核心。因为数据文件只能被Oracle处理程序识别，所以离开了处理程序的可执行文件载体，系统就变得毫无价值。参数文件用户在启动实例时配置数据库，确定Oracle Database 10g的运行环境。当创建数据库时，用户可以运行参数文件，该文件规定了数据库中所使用的各种设置值。这些设置暴扣数据库实例名称、数据库主要文件的位置，以及实例所使用的主要内存区域的大小等。该文件的名称通常为init.ora。例如数据库实例名称是sales，则其参数文件名称是initsales.ora。控制文件时Oracle服务器在启动期间用来标识物理文件和数据库结构的二进制文件。控制文件提供了建立新实例时所需要的文件目录。数据文件用来存放用户的数据。联机重做日志文件对Oracle数据库至关重要，他们是数据库的事物日志。联机重做日志文件仅用于恢复数据库。输出文件影响数据库的可恢复性。输出文件由Oracle自带的数据程序生成。它是在一个给定的时间点上，对数据库的数据和结构的备份。在Oracle系统中，临时文件是一个特殊类型的数据文件类型。当内存不足于在RAM中保存一个大规模排序操作的中间结果或结果集是，Oracle使用临时文件来保存它们。1.2 常用数据类型 varchar2varchar2允许存储从计算机键盘上输入的任何字符。他是一种变长度的数据类型，最大长度是4000。如果在创建表时，定义了一个长度为20的varchar2类型的列，那么当保存的字符长度小于20，就按实际长度保存该字段，如果字符长度超过20，则系统将报错。numbernumber是用来存储整数或者小数数据。date和timestampdate数据类型用户存储日期和时间信息。在默认情况下，date数据类型只显示日期数据，而不显示时间类型。可以使用to_char()函数设置date数据类型，以便显示时间。Timestamp数据类型也是一种存储日期和时间信息的数据类型。Timestamp数据类型与date数据类型相比，timestamp显示的时间更加精确。1.3 表表由行和列组成，具有二维结构。表中的每一行数据被称为一行记录，每一个列都有一个表中唯一的列名。表中行和列的交叉点被成为字段。表是数据保存的逻辑对象。我们绝大多数的数据查询都是针对表的操作。1.4 用户权限 Oracle默认的角色包括DBA、connect、resource、select_catalog_role、OLAP_DBA、scheduler_admin、exp_full_database和imp_full_database等。 DBA可以执行数据库中的所有操作。 Connect 可以执行数据连接等操作。 Resource 可以执行使用数据库资源等操作。 Select_catalog_role 可以执行select操作。 OLAP_DBA可以执行与表、视图有关的操作。 Scheduler_admin 可以执行作业管理等操作。 Exp_full_database 可以执行引出数据库等操作。 Imp_full_database 可以执行引入数据库等操作。对象权限：使用指定对象的权限。包括select、insert、update、delete、execute。系统权限： 可以执行数据库中的某些操作的权限叫系统权限。包括create table、create any table、drop table、drop any table、create procedure、execute any procedure、create sequence、create sequence、create session、create synonym、create user、drop user、create view、create trigger。第二章 表2.1 创建表创建表的语法create global temporary table table_name (column_name type constraint constraint_definition default default_exp,column_name_type constraint constraint_definition defalut default_exp)on commit delete|preserve rowtablespace tablespace_name;例子1：create table pamenieds.employees (employee_id integer not null constraint pk_employee_id primary key,employee_name varchar(20) not null,gender varchar2 (1) constraint ck_employee_gender check (gender in (M,F),birthdate date,position varchar2 (12),tel_phone varchar2 (20),) tablespace tbs_data_01;其中 parmenides是用户名称， tablespace是用户表空间，如果不指定表空间，该表将会被放置在当前用户默认的表空间。Oracle是一个关系型数据库。表保存的对象一般是实体或实体间的关系。例子2：create global temporary table temp_salary ( month varchar2 (20) , employee_id varchar2(20) , work_days number(2) )on commit preserve rowtemp_tablespace;关键字preserve 表示表中的数据在当前会话内有效。比如当创建了一个临时表，向该表中插入了数据，当会话未结束时，该表中的数据是有效的。当你用disconnect命令断开会话，或者用quit命令退出当前的sql_plus后。在重新连接，你会发现该表中已经没有数据了。关键字delete 表示临时表中的数据在事务结束时被删除。值得注意的是，在创建表后，不管你输入的表名是否是大写或小写，Oracle会自动的把表名装换为全大写的。所以我们在查询表名的时候要注意。2.2 修改表结构当表创建后，如果表结构不合理，可以修改表的结构。增加列 Alter table table_name add column_name type;如果修改表结构时表中已存在数据，那么对于所有记录上新增列的字段值都为null值。更新列 Alter table table_name modify column_name type;表更新时要注意，一般只有在表中有数据的情况下，只能把数据的长度从低向高改变，不能从高向低改变。 数据类型也必须为兼用的数据类型。删除列 Alter table table_name drop column column_name; 每次只能删除表中的一列。2.3 约束约束是保障表中数据完整性和一致性的一种方式。约束可以限制列的取值范围，强制列的取值来自合理的范围。Oracle Database 10g系统中，约束的类型包括非空约束、主键约束、唯一性约束、外键约束、检查约束和缺省约束等。约束既可以在使用create table 语句定义表时直接定义，也可以再定义表之后使用 alter table语句更新表时再定义。非空约束Alter table table_name modify column_name not null | null就是限制必须为某个列提供值，既该字段的值不能为null。主键约束Alter table table_name add conatraint_name primary key ( column_name,column_name);主键约束就是唯一地确定表中的每一行数据。在一个表中， 只能有一个主键约束。主键约束既可以是有由一个列组成，也可以由两个以上的列组成。主键由一个列组成该主键被称为行级约束。主键约束由一个以上的组成，则该主键约束被称为表级约束。唯一性约束 alter table table_name add constraint constraint_name unique ( column_name , column_name );唯一性约束强调所在的列不允许有相同的值。与主键定义相比，它允许列为null。外键约束Alter table table_name add constaint constraint_name foreign key ( column_name ) references ref_table_name ( ref_column_name ) on delete cascade|set null on updatecascade 外键是指引用另外一个表或本表中的某个列或某几个列。 被引用的列应该是主键约束列，或是唯一性的约束列。检查约束 Alter table table_name add constraint constraint_name check ( expression )。 检查约束指标中的某一个列或一组列的取值应该满足检查约束指定的条件。缺省约束 Alter table table_name modify column_name type default (constant_expression )缺省约束是指当在插入操作过程中没有为某个列提供数据时，系统会自动将缺省约束的值插入到该列中。2.4 注释表 Comment on table table_name is table_comment; Comment on column table_name.column_name is column_comment;注释表就是为表和表中的列添加文字说明。以便帮组用户在使用表或表中的列时理解表的结构和内容。2.5 删除表 Drop table table_name; 在数据库中如果某个表不要了，那么就可以删除掉该表。删除表必须要有该表的删除权限。 如果该表上有约束引用，有时需要删除该约束后才能删除该表。第三章 常用sqlplus命令当我们连上数据库后，输入SQL语句时，该语句被存在了SQL缓冲区中（一个内存区）。这个SQL缓冲区很小，只能存一个SQL语句，既当下一条SQL语句输入时，原来在缓冲区中的SQL语句被覆盖掉。SQL*PLUS提供了一些常用的命令。与SQL语句不同的是SQL*PLUS的命令是可以缩写。下面就简单地介绍一些常用的SQL*PLUS命令。DESCRIBE 命令一般我们在操作表之前总是想知道表的结构。我们可以使用DESCRIBE 命令来完成。例如下例用来DESCRIBE显示emp表的结构。SQL DESC emp名称 是否为空 类型 - - - EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) 一个表的结构就是该表中包含了多少个列，每一列的数据类型和它的最大长度， 以及该列是否可以为空（NULL）。DESCRIBE 命令也许是最经常使用的SQL*PLUS命令。一般有经验的开发人员（程序员）在使用SQL语句开发程序之前，都要使用DESCRIBE 命令来查看一下SQL语句要操作的表的结构，因为一旦开发人员清楚了所操作的表的结构，可以明显地减少程序出错的概率。SET LINESIZE 80| n 命令使用Linesize命令可以设置一行数据可以容纳的字符数量。Linesize的默认值为80，适当的调整linesize的值可以使输出显示更为直观。例子：Set Feedback n | on | offFeedback命令是设置SQL*PLUS在查询结果集的末尾输出一行，以便指出查询中返回的数据行数量。Feedback在默认情况下是6，表示在返回的数据行的数量是6或更多的时候显示返回的数据行的数量。如果使用on关键字，表示总是现实提示信息；如果使用off关键字，则表示不显示提示信息。L（LIST）命令 和n text命令该命令被用来显示SQL缓冲区中的内容。可以使用L（LIST）命令来显示您刚刚输入的SQL语句。SELECT empno, ename, job, sal 2 FROM dept 3 WHERE sal = 1500 4* ORDER BY job, sal DESC 之后您可以使用 n text命令来修改出错的部分，其中n为在SQL缓冲区中的SQL语句的行号，text为要替代原来出错部分的SQL语句。因为从L（LIST）命令的显示得知是第二行出了错，所以您现在输入以下的命令来修改所犯的错误（例3-7）。例3-7SQL 2 FROM emp之后再使用如下的L（LIST）命令来显示SQL缓冲区中的内容以检查您的修改是否正确。SQL L例3-8结果 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal = 1500 4* ORDER BY job, sal DESC /（RUN）命令/ (run)命令是用来执行缓冲区里的命令。用L确认缓冲区内的sql语句后，可以输入/ 或 run命令，让系统执行命令。脚本文件我们经常会重用很多sql代码或者程序块，这样我们可以把这部分代码保存在脚本文件中。当要执行脚本时，我们可以用 start或 命令 + 调用脚本文件的路径和脚本名称。例如： SQL D:oracleadminbinh_books.sqlSQL start b_books.sql当然我们也可以用 save 命令把刚编写的脚本保存到脚本文件中。用Edit命令可以编辑已存在的脚本。SPOOL命令使用spool命令可以把查询结果保存在一个文件中。spool file_name create | replace | append | off;SQL SPOOL D:SQLOUTPUTSQL SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal = 1500 4 ORDER BY job, sal DESC;SQL SPOOL OFF;这样我们就可以从D:SQLOUTPUT文件中看到命令的执行结果。小窍门：我们在UNIX环境下使用sqlplus登陆数据库时，sqlplus的环境变量是由DBA统一设定的，一般情况下满足自己的个性需求。其实我们可以再登陆数据库时自己设置环境变量。UNIX系统环境下，Oracle系统会在打开sqlplus时会寻找当前目录下是否有./login.sql文件，如果存在该文件，系统就会自动执行该文件中的命令。所以我们可以在login.sql中设置自己个性环境。例如：$ cat login.sql/该命令是用来设置UNIX环境下该session的查询并行度。alter session enable parallel dml;alter session force paraller query paraller 10;alter session force paraller dml paraller 10;set line 300;set pagesize 200;set feedback on;当前的sqlplus的环境可以用show all可以查看默认的设置。第四章 检索数据检索数据是数据库本身存在的意义所在。而检索数据的方法也是最灵活多变的，根据数据结构的和数据存储情况数据检索的sql语句可以灵活多变。编写稳定高效的sql语句是一个数据开发人员的能力体现。4.1 简单查询及常用函数使用select语句检索数据的最基本语法形式如下：Select all|distinct ,目标列表达式 FROM ,Where group by having order by ASC|DESCSelect column_list from table_name;实例：学生-课程 关系学生表： student(Sno, Sname, Ssex, Sage, Sdept)课程表： Course(Cno, Cname, Cpno, Ccredit)学生选课表: SC (Sno, Cno, Grade)单表查询查询表中的指定列Select Sno, Sname from student; 查询经过计算的列Select Sname, (2009 Sage) From student;目标表达式还可以式字符串常量、函数等。Select 巴门尼德 , Sname , (2009 Sage) From student;在查询满足条件的记录，我们可以通过where字句来实现。Select column_list from table_name where conditions;where 常用查询条件比较： =, , =, =, !=, , !, ! ( select birthdate from authorswhere name = Richard );使用子查询时应遵循以下一些原则。1.子查询必须在括号中2.子查询中部能包含order字句3.子查询最多嵌套255层。在一般情况下子查询执行顺序为外查询执行一行，内查询执行一遍。所以效率比较差。一般都可以用连接查询来代替。4.3 连接查询笛卡尔积笛卡尔积是指不带连接谓词的连接，即没有连接字段。他的记录结果为关联表记录的乘积。在集合运算中，集合之间的乘积也被称为笛卡尔积。在关系型数据库中，笛卡尔的记录集的记录数目等于各个表记录数的乘积。当从关系型数据库中的两个表中检索数据时，如果没有指定条件，那么这种检索结果就是笛卡尔积。例子：Select title_chinese, b.book_type_id, t.book_typ_id, book_type_name from books b , book_type t;假设books表有12条数据，book_type表中6条数据，那么查询结果就有72条数据。理论上这样的笛卡尔积在实际生产中是没有意义的，在现场开发环境也是不允许的。对大数据量的表，这样的笛卡尔运算是灾难性的。内连接内连接是指满足连接条件的连接操作。也就是说，在内连接的结果中，都是满足连接条件的数据。可以说内连接是笛卡尔集合中满足连接条件的子集。 Select column_list from table_name_1 inner join table_name_2 on join_condition;例子：Select title_chinese, b.book_type_id, t.book_typ_id, book_type_nameFrom books b Inner join book_type tOn b.book_type_id = t.book_type_id;外连接 在连接过程中，如果某个表中的数据不满足条件，也希望出现在检束结果中。那么可以用外连接。 外连接可以分为左外连接，右连接，全连接。Select column_listFrom table_name_1 left | right | full join table_name_2 on join_condition;外连接是在实际工作环境中用得最多的查询模式。例子：Select * from table_a;Id A_col - Id_1 a Id_2 b Id_3 cselect * from table_b ; id b_col- Id_1 1 Id_3 3 Id_1 2 Select a.id, a.a_col, b.b_col From table_a a Left join table_b b on a.id = b.idorder by a.id asc, a.acol asc, b.b_col asc; Id_1 a_col b_col-Id_1 a 1Id_1 a 2Id_2 b Id_3 c 3从结果里可以看出table_a的所有记录都会出现在最后结果中，如果table_b中如果存在对应记录，那么对应记录才会出现在最终结果中。row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的). 与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码 row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序） rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内） dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的). 与rownum的区别在于：使用rownum进行排序