OracleSQL基础知识论述

1、1.Oracle SQL 基础Welcome!Rainny Zhong138265865711.1.Oracle SQL简介Dr.E.F.Coddpublishedthe paper,ARelational Model of Datafor Large SharedData Banks,inJune1970 in theAssociationofComputerMachinery(ACM)journal,CommunicationsoftheACM. Coddsmodelisnowacceptedasthe definitivemodelfor relationaldatabasemanage

2、mentsystems (RDBMS). Thelanguage,Structured EnglishQueryLanguage(SEQUEL) wasdevelopedbyIBMCorporation,Inc.,touse Coddsmodel.SEQUEL later becameSQL(still pronouncedsequel).In1979,Relational Software,Inc. (nowOracle Corporation)introduced thefirstcommerciallyavailable implementation of SQL.Today, SQLi

3、sacceptedasthestandardRDBMSlanguage.SQLStandardsOracleCorporationstrivestocomplywithindustry-acceptedstandardsand participatesactivelyinSQLstandards committees. Industry-accepted committeesare theAmericanNationalStandardsInstitute (ANSI)andthe InternationalStandards Organization(ISO),whichisaffiliat

4、edwith theInternational ElectrotechnicalCommission(IEC).BothANSI andtheISO/IEChave accepted SQLasthestandardlanguagefor relationaldatabases.When anew SQLstandardissimultaneouslypublishedbytheseorganizations,the names of thestandardsconformtoconventionsused by theorganization, butthestandards aretech

5、nicallyidentical.Thelatest SQLstandardwasadoptedinJuly 1999and is often calledSQL:99. Theformalnamesofthisstandardare:ANSI X3.135-1999,DatabaseLanguageSQL,Parts1(Framework),2 (Foundation),and5(Bindings)ISO/IEC 9075:1999,Database Language SQL, Parts 1(Framework),2(Foundation),and 5(Bindings)1.1.1.Ora

6、cleSQL的处理过过程1.1.1.OracleSQL的处理过过程处理SQL语句处理理查询： 语法法分析： 搜索索同一语语句 检查查语法、对象名名和权限限 锁定定语法分分析过程程中使用用的对象象 创建建和存储储执行计计划 绑定定：获取取变量值值 执行行：处理理语句 提取取：将结结果行返返回用户户进程处理DML语句： 语法法分析：与处理理查询时时的语法法分析阶阶段相同同。 绑定定：与处处理查询询时的绑绑定阶段段相同。 执行行：如果果数据库库缓冲区区高速缓缓存中不不存在某某些数据据块和还还原块，服务器器进程就就从数据据文件将将它们读读入数据据库缓冲冲区高速速缓存。服务器器进程锁锁定要进进行修改改的

7、行。还原块块用于存存储数据据的前像像，以便便在需要要时回退退DML语句。数据据块记录录数据的的新值。服务务器进程程将数据据的前像像记录到到回退块块中，并并更新数数据块。这两种种更改都都是在数数据库缓缓冲区高高速缓存存中进行行的。数数据库缓缓冲区高高速缓存存中所有有已更改改的块都都标记为为灰数据据缓冲区区，即与与磁盘中中相应的的块不同同的缓冲冲区。DELETE或INSERT命令的处处理使用用类似的的步骤。DELETE命令的前前像包含含已删除除行中的的列值，而INSERT命令的前前像中包包含行的的位置信信息。处理DDL语句：DDL（数据定义义语言）语句的的执行与与DML（数据操纵纵语言）语句和和查

8、询的的执行不不尽相同同，因为为成功执执行DDL语句需要要对数据据字典具具有写权权限。对对于这些些语句，语法分分析阶段段实际上上包括分分析、数数据字典典查找和和执行。事务处处理管理理SQL语句、会会话管理理SQL语句和系系统管理理SQL语句在语语法分析析和执行行阶段处处理。要要重新执执行这些些语句，再次进进入执行行阶段即即可。1.2.Oracle SQL基本元素素DatatypesLiteralsFormatModelsNullsPseudocolumns1.2.1.OracleSQL数据类型型OracleBuilt-inDatatypesANSI,DB2, andSQL/DSDatatypes

9、User-DefinedTypesOracle-SuppliedTypes1.2.1.2.Oracle内置数据据类型1.2.1.2.Oracle内置的数数据类型型概览CharacterDatatypes-CHAR Datatype-NCHARDatatype-NVARCHAR2Datatype-VARCHAR2DatatypeNUMBERDatatypeLONG DatatypeDatetimeandIntervalDatatypes-DATE-TIMESTAMP-TIMESTAMPWITHTIME ZONE-TIMESTAMPWITHLOCALTIME ZONERAWand LONGRAW

10、DatatypesLOB-BLOB-CLOB-NCLOBROWIDDatatypeBFILEDatatype1.2.2.OracleSQL伪列CURRVAL andNEXTVAL-sequence.CURRVAL-sequence.NEXTVALLEVELSELECTemployee_id, last_nameFROM employeesWHERE(employee_id, LEVEL)IN(SELECT employee_id,2 FROMemployees)STARTWITH employee_id =2CONNECT BY PRIOR employee_id =manager_id;RO

11、WIDSELECTROWID, last_nameFROM employeesWHEREdepartment_id =20;ROWNUMSELECT*FROM employeesWHEREROWNUM ?/rdbms/admin/utlexcpt1Statementprocessed.SQL DESCRIBE exceptions注：utlexcpt1.sql脚本的确确切名称称和位置置视操作作系统而而定。有有关详细细信息，请参阅阅专用于于该操作作系统的的Oracle文档。1.3.12.获取约束束信息1.4.Oracle SQL操作符AboutSQLOperatorsArithmetic Ope

12、ratorsConcatenation OperatorSetOperatorsUser-DefinedOperators1.4.1.集合操作作符UnionUnionallIntersectMinus1.4.1.集合操作作符-OE/OE-UNIONselectlocation_id,department_nameDepartment,to_char(null) Warehousefromdepartmentsunionselectlocation_id,to_char(null) Department,warehouse_namefromwarehouses;-UNION ALLSELECTl

13、ocation_idFROMlocationsUNIONALLSELECTlocation_idFROMdepartments;-INTERSECTSELECTproduct_idFROM inventoriesINTERSECTSELECTproduct_idFROM order_items;-MINUSSELECTproduct_idFROM inventoriesMINUSSELECTproduct_idFROM order_items;1.4.1.集合操作作符-OE/OE-UNIONselectlocation_id,department_nameDepartment,to_char(

14、null) Warehousefromdepartmentsunionselectlocation_id,to_char(null) Department,warehouse_namefromwarehouses;-UNION ALLSELECTlocation_idFROMlocationsUNIONALLSELECTlocation_idFROMdepartments;-INTERSECTSELECTproduct_idFROM inventoriesINTERSECTSELECTproduct_idFROM order_items;-MINUSSELECTproduct_idFROM i

15、nventoriesMINUSSELECTproduct_idFROM order_items;1.5.表达式AboutSQLExpressionsSimpleExpressionsCompoundExpressionsCASE ExpressionsCURSORExpressionsDatetimeExpressionsFunctionExpressionsINTERVALExpressionsObjectAccess ExpressionsScalarSubqueryExpressionsType Constructor ExpressionsVariableExpressionsExpr

16、ession Lists1.6.条件AboutSQLConditionsComparison ConditionsLogical ConditionsMembership ConditionsRangeConditionsNull ConditionsEXISTSConditionsLIKE Conditions1.7.常用的SQLDDL子句allocate_extent_clauseconstraintsdeallocate_unused_clausefile_specificationlogging_clauseparallel_clausephysical_attributes_clau

17、sestorage_clause1.7.1.范围分配配子句使用在:altertable,alterindex1.7.2.释放未使使用空间间的子句句使用在:altertable,alterindex1.7.3.文件详述述子句使用在:createtablespace,createtemporary tablespaceAutoextend_clause:1.7.4.物理属性性子句使用在:createtable,create index1.7.5.存储子句句使用在:createtablespace,createtable,create index1.7.6.创建表空空间1.7.6.创建表空空间Ext

18、ent_management_clause:=Segment_management_clause:=(只有永久久的,本本地管理理的表空空间才有有关的)1.7.7.创建临时时表空间间create_temporary_tablespace:=temp_tablespace_extent:=1.7.8.创建表1.7.9.创建索引引Table_index_clause:=1.8. SQL查询和子子查询AboutQueries andSubqueriesCreatingSimpleQueriesHierarchicalQueriesTheUNIONALL, INTERSECT,MINUSOperator

19、sSorting Query ResultsJoinsUsingSubqueriesUnnestingofNested SubqueriesSelectingfromtheDUALTableDistributedQueries1.8.1.连接Ajoinisa query thatcombinesrowsfrom twoormore tables,views, or materializedviews.Oracle performs ajoinwhenevermultipletablesappear in thequerys FROMclause.Thequerysselectlistcanse

20、lect anycolumns fromany of these tables.Ifany twoofthesetableshavea columnname in common,thenyoumustqualify allreferences to these columnsthroughoutthequerywithtablenamestoavoidambiguity.Join condition:Most joinqueriescontain WHERE clauseconditions thatcomparetwocolumns,eachfrom adifferent table.Suc

21、h acondition is calleda joincondition.Toexecute ajoin,Oracle combines pairs of rows, eachcontainingonerow fromeachtable,for which thejoin conditionevaluatestoTRUE.The columnsinthe joinconditionsneed notalso appearintheselect list.1.8.1.连接(1)EquijoinsSELECTlast_name,job_id, departments.department_id,

22、 department_nameFROM employees,departmentsWHEREemployees.department_id=departments.department_id;(2)Self JoinsSELECTe1.last_name| works for|e2.last_nameEmployeesandTheirManagers FROMemployees e1,employeese2WHEREe1.manager_id= e2.employee_id ANDe1.last_nameLIKER%;(3)CartesianProducts(4)InnerJoins(5)O

23、uterJoinsleft outer joinSELECTd.department_id,e.last_nameFROM departments dLEFTOUTERJOIN employeese ON d.department_id= e.department_idORDERBYd.department_id;rightouterjoinSELECTd.department_id,e.last_nameFROM departments dRIGHTOUTERJOINemployeeseONd.department_id=e.department_idORDERBYd.department_id;full outer joinSELECTd.department_id as d_dept_id,e.department_id as e_dept_id,e.last_nameFROMdepartmentsd FULLOUTERJOINemployeeseONd.department_id=e.department_idORDERBYd.department_id;1.8.2.子查询A subquery answersmultiple-partquestions. For