oracle学习笔记分析和总结

TOC\o"1-5"\h\z\o"CurrentDocument"第一章SQL更新数据2\o"CurrentDocument"第二章序列4\o"CurrentDocument"第三章SQL查询6\o"CurrentDocument"第四章ORACLE数据表对象15Selectmanager_namename,statusFromt_managersWherestatus='ACT'UnionallSelectemployee_name,statusFromt_employeesWherestatus=,ACT'Intersect查询Intersect查询用于获得两个结果集的交集。例selectemployee_name,statusFromt_employeesWherestatus='ACT'Intersectselectmanager_name,statusFromt_managersWherestatus='ACT'Minus查询Minus查询可以看做集合间的减法运算，该操作的第一个集合看做被减数，而第二个集合看作减数，那么minus操作将返回第一个结果集中存在，而第二个结果集中不存在的记录例selectemployee_name,statusFromt_employeesWherestatus='ACT'MinusSelectmanager_name,statusFromt_managersWherestatus='ACT'IV.联接联接用于多数据源（表、视图）之间如何组合，以形成最终的数据源。联接对于查询语句有着不可或缺的作用。如果未显示指定联接，那么将获得多个数据源的笛卡尔积。Oracle中主要包括以下几种联接关系。自然联接内联接外联接：左联接外联接：右联接外联接：完全联接自然联接自然联接将两个数据源中具有相同名称的列进行联接。用户不必明确指定执行联接的列。自然联接应该使用naturaljoin关键字。例select*Fromt_employeesnaturaljoint_managers内联接内联接像自然联接不同，需要在from子句中使用联接条件。但是，用户可以自行制定所要联接的各数据源的列。这克服T自然联接要求联接必须同名的限制。例select*Fromt_employeeseinnerjoint_managersmOne.employee_name=m.manager_name内连接可以用where子句来代替外联接一左联接外联接与内联接不同的是，内联接中的两个数据源是并列关系,二者具有平等的地位，而外联接将其中一个数据源指定为基表（或者说为主表）另一个数据源可以看做附表。在最终的数据源中，一定含有基表中的数据，而附表中的数据是否出现,那么依具体的联接条件而定。左联接例子selecte.employeejd,e.employee_name,s.salaryFromt_employeeseLeftouterjoint_salarysOne.employeejd=s.employee_idOrderbye.employee_id（当左侧数据表中的数据在右侧表中没有记录与之对应，将利用null来填补空缺的查询结果）对于左联接，oracle还提供了另外一种简写的方式一一在where子句条件中添加(+)来指定附表Selecte.employeejd,e.employee_name,salaryFromt_employeese,t_salarysWheree.employee_id=s.employee_id(+)Orderbye.employee_id外联接一一右联接右联接与左联接的执行过程非常相似，二者的区别在于基表的选择。右联接应该选择使用right(outer)join关键字，而基表即处于该关键字右侧的数据表selecte.employeejd,e.employee_name,s.salaryFromt_employeeserightouterjoint_salarysOne.employeeJd=s.employee_idOrderbye.employee_id外联接一一完全联接完全联接实际是一个左联接和右联接的组合，即首先执行一个左联接，然后执行一个右联接，最后将两个结果集执行union操作，从而获得最终的数据源。例selecte.employeejd,e.employee_name,s.salaryFromt_employeesefulljoint_salarysOne.employeeJd=s.employee_idOrderbye.employee_id缺点：完全联接开销很大V.层次化查询利用connectby进行层次化查询例select*frommarketStartwithmarket_id=14Connectbypriorparent_market_id=market_idConnect指定的递归条件，可以像where子句中的条件一样，使用and或者or等运算符来指定多个条件例select*frommarketStartwithmarketjd=14Connectbypriorparent_market_id=marketjdandinstr(market_name,洲)=0演示用connectby获得所有中国市场的客户信息。(具体看书)Select*frommarket_customerWheremarketjdin(SelectmarketjdfrommarketStartwithmarket_name='中国，Connectbypriormarketjd=parent_market_id)Sys_connect_by_path()函数的使用(具体看书316页)第四章ORACLE数据表对象与其他数据库不同，oracle数据库的下一层逻辑结构并非数据表，而是表空间；每个数据表都属于唯一的表空间。I.oracle表空间表空间是oracle的开创性理念，他是一个逻辑对象。使用表空间有以下好处：.防止磁盘空间突然耗竭的风险.规划数据更灵活（类似于操作系统的文件夹功能）.提高数据库性能（访问频繁的数据表放入单独的表空间）.提高数据库的安全性II.创立oracle表空间.Oracle表空间是一个逻辑概念，创立是需要指定物理文件，即实际数据分配磁盘空间。表空间的物理文件被称为数据文件（datafile）；与此同时，应同时指定数据文件的初始大小。创立一个名为USER1的表空间，其SQL语句如下：Createtablespaceuserldatafile'f:\database\oracle\user1_data.dbfsize20M•指定数据文件的可扩展性表空间设计理念灵活性的一个方面在于数据文件的可扩展性。当存储在某个数据文件中的数据量超过了其初始大小时，数据文件可以进行自动扩展。要像实现该功能，在创立表空间时应该使用autoextend选项Createtablespaceuser2datafile'f:\database\oracle\user2_data.dbfsize20Mautoextendon要关闭自动扩展，可以将autoextend选项的值设为“off”•指定数据文件的增长幅度数据文件默认每次增长64K,当频繁增长时，可以使用next选项设定数据文件自动增长的幅度例createtablespaceuser3datafile'f:\database\oracle\user3_data.dbfsize20MAutoextendonnext5M数据文件的大小和自动增长的幅度单位只有KB和MB两种。.指定数据文件的最大尺寸数据文件可以自动增长，但是无限制的增长往往带来风险。很多情况下，某台服务器上可能同时运行着多个系统，如邮件服务器和数据库服务器可能为同一台机器。，，因此除非特殊需要，应为每个表空间的数据文件设定最大尺寸。应使用maxsize选项Createtablespaceuser4datafile'f:\datafile\oracle\user4_data.dbfsize20MAutoextendonnext5mmaxsizeunlimited这里unlimited作用是不限制最大尺寸。.查看表空间是否创立成功表空间成功创立后，会在数据库系统表中添加相应的记录，并且创立相应的物理文件。可以通过查看视图dba_data_files中的记录和实际数据文件存在性，来判断表空间是否创立成功Selectfile_name,tablespace_namefromdba_data_filesorderbyfile_name,为一个表空间创立多个数据文件.一个表空间可以有多个数据文件，为一个表空间创立多个数据文件需要指定多个数据文件的完整路径和详细的选项参数。各数据文件之间使用分隔。例selecttablespacemultiple_data_filedatafile'f:\database\tmp\data_1.dbfsizeIM,'f:\database\tmp\data_2.dbfsize5MIII.查看表空间Dba_tablespaces可以用来查看所有表空间的基本信息；而dba_data_files可以用来查看相关数据文件的信息。查看表空间信息，可以利用如下SQL语句：Selecttablespace_name,status,allocation_typefromdba_tablespacesW,修改数据库默认表空间查询每个用户的默认表空间，可以使用如下SQL语句：Selectuser_id,username,default_tablespacefromdba_users修改数据库默认的表空间的SQL语句如下：Alterdatabasedefaulttablespaceuserl.修改表空间名称修改表空间名称使用rename命令Altertablespaceuser2renametouser20表空间重命名并不对数据文件产生影响，需要注意的是，不能对数据的系统表空间进行重命名，例如SYSTEM,SYSAUX等无法进行重命名。.删除表空间删除表空间的命令为droptablespaceo删除表空间有两种方式，一种是仅仅删除其在数据库中的记录，另一种是将记录和数据文件一起删除。仅删除其在数据库中的记录的SQL语句如下：Droptablespaceuser20删除表空间及其数据文件的SQL语句如下：Droptablespaceuser20includingcontentsanddatafiles.创立oracle数据表利用命令创立数据表例createtablet_user(user_idnumbernotnull,user_namevarchar2(20)notnull,user_emailvarchar2(30))利用命令查看表结构Selecttable_name,tablespace_namefromuser_tableswheretable_name=,t_user'视图user_tables可以用来查看所有用户表的基本信息。可以在SQLPlus或PL/SQLDeveloper的[CommandWindow]执行如下SQL语句来查看数据表信息Describetuser;.修改oracle数据表结构利用命令修改数据表结构修改数据表结构的SQL命令为altertable可以利用RENAME选项，对列名进行修改例altertablet_userrenamecolumnuser_emailtoemail列更名时，不会影响数据库中的记录；但注意不能将列名更新为与其他已存在列名相同。可以利用modify选项对列的属性进行修改，以修改USER_NAME的长度为例，相应的SQL语句如下：Altertablet_usermodify(user_namevarchar2(15))Oracle允许一次修改多个列属性。例altertablet_usermodify(user_namevarchar2(15),emailvarchar2(30))使用altertable命令同样可以为表添加一列例altertablet_useradd(remarksvarchar2(50))删除表中的操作使用drop选项第一章SQL更新数据I.插入数据一.Insert语句向表中插入数据.为所有列显示赋值Insertintopeoplevalues(people_seq.nextval,1,'ACT');.同时指定要插入的列名列表和列值列表Insertintotable_name(column1,column2,...)values(value1,value2二.利用子查询批量插入数据此时的SQL语句除了包含insertinto命令之外，还应包含一个查询语句，其语法规那么如下：Insertintotable_nameselect...例：insertintopeopleselectemployeejd,employee_name,statusfromt_employeeswhereemployee_id>=3;n.修改数据Update修改数据一般有以下几种情况：直接修改单列的值，直接修改多列的值、利用where子句限制修改范围和利用视图修改数据。L修改单列的值updatetable_namesetcolum=value例updatepeoplesetstatus='CXL';例altertablet_userdropcolumnremarkAvertable命令除了对列进行修改外，还可以对表本身的属性进行修改。例如，利用rename选项修改表名Altertablet_userrenametot_users（注意，不要轻易修改表名）.删除数据表删除数据表的命令为droptable例droptablet_dropDroptable和delete命令的区别：droptable用于删除数据表，一旦删除，该数据表在数据库中将不再存在，而delete那么用于删除数据表的记录，无论记录有无，数据表仍然是存在的。有时，某些数据表的列被其他表引用，如外键引用，直接使用droptable将无法删除该表。此时，需要使用cascadeconstraints选项。例droptablet_dropcascadeconstraintscascadeconstraints表示删除表时，将一起作用于约束。,备份/恢复数据表数据表的备份和恢复是最常用的数据库操作，数据表的备份主要用于以下场合修改数据表结构之前修改数据表的数据之前删除某个数据表之前利用命令备份/恢复数据表对于oracle数据表的备份/恢复操作，最常用的命令为exp和impoExp命令实现导出操作，imp实现导入操作。使用exp导出表T_USERS的步骤如下Expsystem/abc123@testtables=(T_USERS)file=D:\temp\command\T_USERS.dmp使用imp导入数据表的步骤如下所示：Impsystem/abc123@testfile=D:\temp\command\T_USERS.dmptables=T_USERS注意，tabi6s参数是必需的，而且要与导出时的表名保持一致.临时表Oracle使用createtable命令创立的数据表称为永久表或普通表。在oracle中还有另外一种特殊的数据表临时表。临时表指的是所存储的数据的临时性，临时表虽然一直存在，但是其中的数据会在某种条件下被oracle数据库自动清空。临时表数据清空的条件有两种，一种是事物提交或回滚；二是会话结束。创立临时表应该使用createglobaltemporarytable命令创立名为tmp_users_session的会话级临时表。Createglobaltemporarytabletmp_users_session(userjdint,user_namevarchar2(20),user_emailvarchar2(30))oncommitpreserverows创立事务级临时表例createglobaltemporarytabletmp_users_transaction(user_idint,user_namevarchar2(20),user_emailvarchar2(30))oncommitdeleterowsVII.特殊的表dual第五章约束—.主键约束主键约束可以保存数据完整性，即防止数据表中的两条记录完全相同，通过将主键纳入查询条件，可以到达查询结果最多返回一条记录的目的。.创立主键约束Createtablestudent(studentjdnumberprimarykey,student_namevarchar2(20),student_birthdaydate,student_addressvarchar2(50),student_phonevhar2(20))查看约束Selecttable_name,constraint_name,constraint_type,statusfromuser_constraintswheretable_name="STUDENT'演示获得主键的作用列Selectconstraintname,tablename,columnnamefromuser_cons_columnswhereconstraint_name='SYS_C00512r创立表时显示命名主键Createtablestudent(studentjdnumberconstraintpk_studentprimarykey,student_namevarchar2(20),student_brithdaydate,student_addressvarchar2(50),student_phonevarchar2(20))创立主键的另一种写法Createtablestudent(studentjdnumber,student_namevarchar2(20),student_birthdaydate,student_addressvarchar2(50),student_phonevarchar2(20)constraintpk_studentprimarykey(student_id))在student_name,student_birthday,student_address三列上创立主键约束。Createtablestudent(student_idnumber,student_namevarchar2(20),student_birthdaydate,student_addressvarchar2(50),student_phonevarchar2(20),constraintpk_studentprimarykey(student_name,student_birthday,student_address)).修改表的主键约束为已存在的表创立主键Altertablestudentmodify(studentJdnumberprimarykey)当要添加的主键作用于多个列时，可以通过添加主键命令，而不是通过修改列的属性来添加主键。例altertablestudentaddconstraintpk_studentprimarykey(student_name,student_birthday,student_address)删除主键Altertablestudentdropprimarykey利用删除约束的语法删除主键Altertablestudentdropconstraintpk_student列status标识了约束的可用性演示禁用主键约束Altertablestudentdisableprimarykey启用主键约束的SQL语句如下：Altertablestudentenableprimarykey重命名主键AltertablestudentrenameconstraintSYS_C005145toPK_STUDENT.利用update命令同时修改多列的值。Updatetable_namesetcolum1=value1,colum2=value2updatepeoplesetid=(20000+id),status='ACT";.利用where了,句限制修改范围例updatepeoplesetstatus='CXL'whereid>20010;m.删除数据Delete命令删除表中的某些记录，truncate命令的作用是删除整个数据表。语法规那么deletefromtable_namewhere例deletefrompeoplepwhereexists(select1fromt_employeesewheree.employee_name=)Truncate命令的语法规那么Truncatetabletable_name例truncatetablepeopleDDL不能回滚，DML可以回滚回滚命令:rollback提交命令:commit第二章序列I.创立序歹|J使用createsequence命令例createsequenceemployee_seq序列有两个重要属性cuirval和nextvaLCuirval用于获得序列的当前值，nextval用于获得序列的下一个值。每次调用nextval,都会使序列的当前值增加单位步长（默认步长为l）o序列的调用方法为seq.currval和seq.nextval。但是，需要注意的是，在序列创立之后，应该首先使用seq.nextval,然后才能使用seq.currvalo使用方法：Selectemployee_seq.nextvalfromdualSelectemployee_seq.currvalfromdual向表中插入新的员工信息：Insertintot_employeesvalues（employee_seq.nextval；陆逊\O「ACT）;删除序列：Dropsequenceemployee_seq创立序列，并指定startwith选项：Createsequenceemployee_seqstartwith12n.修改序列属性修改minvalue和maxvalue的值Altersequenceemployee_seqminvalue20;Altersequenceemployee_seqmaxvalue99999;设置maxvalue值为无限Altersequenceemployee_seqnomaxvalue；利用alter命令修改步长为5：Altersequencetest_seqincrementby5；III.修改cycleCycle选项用于指定序列在获得最大值的下一个值时，从头开始获取，这里的头即为minvahie指定的值。修改序列test_seq为可循环Altersequencetest_seqcycle；关闭可循环Altersequencetest_seqnocycle；Cache是序列缓存，其实际意义为，每次利用nextval,并非直接操作序列，而是一次性获取多个值的列表到缓存。使用nextval获得的值，实际是从缓存抓取。抓取的值依赖于序列的cuiral和步长incrementby。缓存的值（在这里，即容量20）必须小于一次循环所能获得的数目。Cache的存在会对其他修改其他选项产生影响。第三章SQL查询I.基本查询例selectemployee_namefromt_employees;当from子句中含有多个数据源时，这些数据源将实现笛卡尔积。Where子句Where子句用于过滤from子句所获得的数据源。例selecte.employee_id,e.employ_name,s.salaryfromt_employeese,t_salarysWheree.employee_id=s.employee_id（当from子句中含有多个数据表或其他数据源时，为各数据表指定别名是一个良好的习惯，即使select语句所要捕获的列不会引起列名的混淆。）利用distinct获得唯一性记录，被distinct限制的既可以是单个列，也可以是多个列的组合。演示利用distinct关键字获得工资表中的所有月份Selectdistinctmonthfromt_salary可以获得员工的工资的唯一性记录，以表现员工的工资变化：Selectdistincte.employee_name,s.salaryFromt_employeee,t_salarysWheree.employeejd=s.employee_id此语句捕获唯一的employee_name和salary的组合。Orderby子句Orderby子句可以将查询结果按照一定的顺序进行排序。Selectdistincte.employee_name,s.salaryFromt_employeee,t_salarysWheree.employee_id=s.employeeJdOrderbys.salary默认进行升序排列。升asc降desc例orderbys.salarydescGroupby子句Groupby子句用于对记录集合进行分组，一旦使用了分组之后，select语句的真实操作目标即为各个分组数据，每次循环处理的也是各个分组，而不再是单条记录。Selecte.employee_name,sum(s.salary)Fromt_employeee,t_salarysWheree