OracleSQL基础知识论述10981.pptx

《OracleSQL基础知识论述10981.pptx》由会员分享，可在线阅读，更多相关《OracleSQL基础知识论述10981.pptx（57页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1.Oracle SQL 基础基础Welcome!Rainny Zhong138265865711.1.Oracle SQL简介简介Dr.E.F.CoddDr.E.F.Codd published the paper,A Relational Model of Data for Large Shared Data Banks,in June 1970 in the Association of Computer Machinery(ACM)journal,Communications of the ACM.Codds model is now accepted as the definitiv

2、e model for relational database management systems(RDBMS).The language,Structured English Structured English Query LanguageQuery Language(SEQUEL)was developed by IBMIBM Corporation,Inc.,to use Codds model.SEQUEL later became SQL(still pronounced sequel).In 1979,Relational Relational Software,IncSoft

3、ware,Inc.(now Oracle Corporation)introduced the first commercially available implementation of SQL.Today,SQL is accepted as the standard RDBMS language.SQL StandardsSQL StandardsOracle Corporation strives to comply with industry-accepted standards and participates actively in SQL standards committee

4、s.Industry-accepted committees are the American National Standards Institute(ANSI)and the International Standards Organization(ISO),which is affiliated with the International Electrotechnical Commission(IEC).Both ANSI and the ISO/IEC have accepted SQL as the standard language for relational database

5、s.When a new SQL standard is simultaneously published by these organizations,the names of the standards conform to conventions used by the organization,but the standards are technically identical.The latest SQL standard was adopted in July 1999 and is often called SQL:99.The formal names of this sta

6、ndard are:ANSI X3.135-1999,Database Language SQL,Parts 1(Framework),2(Foundation),and 5(Bindings)ISO/IEC 9075:1999,Database Language SQL,Parts 1(Framework),2(Foundation),and 5(Bindings)1.1.1.Oracle SQL的处理过程的处理过程1.1.1.Oracle SQL的处理过程的处理过程处理SQL SQL 语句处理查询：语法分析：搜索同一语句 检查语法、对象名和权限 锁定语法分析过程中使用的对象 创建和存储执行

7、计划 绑定：获取变量值 执行：处理语句 提取：将结果行返回用户进程处理DML DML 语句：语法分析：与处理查询时的语法分析阶段相同。绑定：与处理查询时的绑定阶段相同。执行：如果数据库缓冲区高速缓存中不存在某些数据块和还原块，服务器进程就从数据文件将它们读入数据库缓冲区高速缓存。服务器进程锁定要进行修改的行。还原块用于存储数据的前像，以便在需要时回退DML 语句。数据块记录数据的新值。服务器进程将数据的前像记录到回退块中，并更新数据块。这两种更改都是在数据库缓冲区高速缓存中进行的。数据库缓冲区高速缓存中所有已更改的块都标记为灰数据缓冲区，即与磁盘中相应的块不同的缓冲区。DELETE或INSER

8、T命令的处理使用类似的步骤。DELETE命令的前像包含已删除行中的列值，而INSERT命令的前像中包含行的位置信息。处理DDL DDL 语句：DDL（数据定义语言）语句的执行与DML（数据操纵语言）语句和查询的执行不尽相同，因为成功执行DDL 语句需要对数据字典具有写权限。对于这些语句，语法分析阶段实际上包括分析、数据字典查找和执行。事务处理管理SQL 语句、会话管理SQL 语句和系统管理SQL 语句在语法分析和执行阶段处理。要重新执行这些语句，再次进入执行阶段即可。1.2.Oracle SQL基本元素基本元素DatatypesLiteralsFormat Models NullsPseudo

9、columns 1.2.1.Oracle SQL数据类型数据类型Oracle Built-in DatatypesANSI,DB2,and SQL/DS DatatypesUser-Defined Types Oracle-Supplied Types1.2.1.2.Oracle内置数据类型内置数据类型1.2.1.2.Oracle内置的数据类型概览内置的数据类型概览Character Datatypes-CHAR Datatype-NCHAR Datatype-NVARCHAR2 Datatype-VARCHAR2 Datatype NUMBER DatatypeLONG DatatypeDa

10、tetime and Interval Datatypes-DATE-TIMESTAMP-TIMESTAMP WITH TIME ZONE-TIMESTAMP WITH LOCAL TIME ZONERAW and LONG RAW DatatypesLOB-BLOB-CLOB-NCLOBROWID DatatypeBFILE Datatype1.2.2.Oracle SQL伪列伪列CURRVAL and NEXTVAL-sequence.CURRVAL-sequence.CURRVAL-sequence.NEXTVAL-sequence.NEXTVAL LEVELSELECT employe

11、e_id,last_name FROM employees WHERE(employee_id,LEVEL)IN SELECT employee_id,last_name FROM employees WHERE(employee_id,LEVEL)IN(SELECT employee_id,2 FROM employees)START WITH employee_id=2 CONNECT(SELECT employee_id,2 FROM employees)START WITH employee_id=2 CONNECT BY PRIOR employee_id=manager_id;BY

12、 PRIOR employee_id=manager_id;ROWIDSELECT ROWID,last_name FROM employees WHERE department_id=20;SELECT ROWID,last_name FROM employees WHERE department_id=20;ROWNUMSELECT*FROM employees WHERE ROWNUM 10;SELECT*FROM employees WHERE ROWNUM?/rdbms/admin/utlexcpt1Statement processed.2.SQL DESCRIBE excepti

13、ons注：注：utlexcpt1.sql脚本的确切名称和位置视操作系统而定。有关详细信息，请参阅脚本的确切名称和位置视操作系统而定。有关详细信息，请参阅专用于该操作系统的专用于该操作系统的Oracle文档。文档。1.3.12.获取约束信息获取约束信息1.4.Oracle SQL操作符操作符About SQL OperatorsAbout SQL OperatorsArithmetic OperatorsArithmetic OperatorsConcatenation OperatorConcatenation OperatorSet OperatorsSet OperatorsUser-De

14、fined OperatorsUser-Defined Operators1.4.1.集合操作符集合操作符UnionUnionUnion allUnion allIntersectIntersectMinusMinus1.4.1.集合操作符集合操作符-OE/OEOE/OE-UNION-UNIONselect location_id,select location_id,department_name Department,department_name Department,to_char(null)Warehouse to_char(null)Warehousefrom department

15、sfrom departmentsunionunionselect location_id,select location_id,to_char(null)Department,to_char(null)Department,warehouse_name warehouse_namefrom warehouses;from warehouses;-UNION ALL-UNION ALLSELECT location_id FROM locations SELECT location_id FROM locations UNION ALL UNION ALL SELECT location_id

16、 FROM departments;SELECT location_id FROM departments;-INTERSECT-INTERSECTSELECT product_id FROM inventoriesSELECT product_id FROM inventoriesINTERSECTINTERSECTSELECT product_id FROM order_items;SELECT product_id FROM order_items;-MINUS-MINUSSELECT product_id FROM inventoriesSELECT product_id FROM i

17、nventoriesMINUSMINUSSELECT product_id FROM order_items;SELECT product_id FROM order_items;1.4.1.集合操作符集合操作符-OE/OEOE/OE-UNION-UNIONselect location_id,select location_id,department_name Department,department_name Department,to_char(null)Warehouse to_char(null)Warehousefrom departmentsfrom departmentsun

18、ionunionselect location_id,select location_id,to_char(null)Department,to_char(null)Department,warehouse_name warehouse_namefrom warehouses;from warehouses;-UNION ALL-UNION ALLSELECT location_id FROM locations SELECT location_id FROM locations UNION ALL UNION ALL SELECT location_id FROM departments;S

19、ELECT location_id FROM departments;-INTERSECT-INTERSECTSELECT product_id FROM inventoriesSELECT product_id FROM inventoriesINTERSECTINTERSECTSELECT product_id FROM order_items;SELECT product_id FROM order_items;-MINUS-MINUSSELECT product_id FROM inventoriesSELECT product_id FROM inventoriesMINUSMINU

20、SSELECT product_id FROM order_items;SELECT product_id FROM order_items;1.5.表达式表达式About SQL ExpressionsAbout SQL ExpressionsSimple ExpressionsSimple ExpressionsCompound Expressions Compound Expressions CASE Expressions CASE Expressions CURSOR Expressions CURSOR Expressions Datetime ExpressionsDatetim

21、e ExpressionsFunction Expressions Function Expressions INTERVAL ExpressionsINTERVAL ExpressionsObject Access Expressions Object Access Expressions Scalar Subquery ExpressionsScalar Subquery ExpressionsType Constructor ExpressionsType Constructor ExpressionsVariable ExpressionsVariable ExpressionsExp

22、ression ListsExpression Lists1.6.条件条件About SQL ConditionsAbout SQL ConditionsComparison Conditions Comparison Conditions Logical ConditionsLogical ConditionsMembership ConditionsMembership ConditionsRange ConditionsRange ConditionsNull Conditions Null Conditions EXISTS Conditions EXISTS Conditions L

23、IKE Conditions LIKE Conditions 1.7.常用的常用的SQL DDL子句子句allocate_extent_clauseallocate_extent_clauseconstraintsconstraintsdeallocate_unused_clausedeallocate_unused_clausefile_specificationfile_specificationlogging_clauselogging_clauseparallel_clauseparallel_clausephysical_attributes_clausephysical_attri

24、butes_clausestorage_clausestorage_clause1.7.1.范围分配子句范围分配子句使用在使用在:alter table,alter index1.7.2.释放未使用空间的子句释放未使用空间的子句使用在使用在:alter table,alter index1.7.3.文件详述子句文件详述子句使用在使用在:create tablespace,create temporary tablespaceAutoextend_clause:1.7.4.物理属性子句物理属性子句使用在使用在:create table,create index1.7.5.存储子句存储子句使用在使

25、用在:create tablespace,create table,create index1.7.6.创建表空间创建表空间1.7.6.创建表空间创建表空间Extent_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查询和子查询

26、查询和子查询About Queries and SubqueriesAbout Queries and SubqueriesCreating Simple Queries Creating Simple Queries Hierarchical Queries Hierarchical Queries The UNION ALL,INTERSECT,MINUS The UNION ALL,INTERSECT,MINUS OperatorsOperatorsSorting Query ResultsSorting Query ResultsJoinsJoinsUsing SubqueriesUs

27、ing SubqueriesUnnesting of Nested Subqueries Unnesting of Nested Subqueries Selecting from the DUAL TableSelecting from the DUAL TableDistributed QueriesDistributed Queries1.8.1.连接连接A A joinjoin is a query that combines rows from two or more tables,views,or is a query that combines rows from two or

28、more tables,views,or materialized views.Oracle performs a join whenever multiple tables materialized views.Oracle performs a join whenever multiple tables appear in the querys FROM clause.The querys select list can select appear in the querys FROM clause.The querys select list can select any columns

29、 from any of these tables.If any two of these tables have any columns from any of these tables.If any two of these tables have a column name in common,then you must qualify all references to a column name in common,then you must qualify all references to these columns throughout the query with table

30、 names to avoid these columns throughout the query with table names to avoid ambiguity.ambiguity.Join condition:Join condition:Most join queries contain WHERE clause conditions that compare two Most join queries contain WHERE clause conditions that compare two columns,each from a different table.Suc

31、h a condition is called a join columns,each from a different table.Such a condition is called a join condition.To execute a join,Oracle combines pairs of rows,each condition.To execute a join,Oracle combines pairs of rows,each containing one row from each table,for which the join condition containin

32、g one row from each table,for which the join condition evaluates to TRUE.The columns in the join conditions need not also evaluates to TRUE.The columns in the join conditions need not also appear in the select list.appear in the select list.1.8.1.连接连接(1)EquijoinsSELECT last_name,job_id,departments.d

33、epartment_id,department_name FROM SELECT last_name,job_id,departments.department_id,department_name FROM employees,departments WHERE employees.department_id=employees,departments WHERE employees.department_id=departments.department_id;departments.department_id;(2)Self JoinsSELECT e1.last_name|works

34、for|e2.last_name Employees and Their Managers SELECT e1.last_name|works for|e2.last_name Employees and Their Managers FROM employees e1,employees e2 WHERE e1.manager_id=e2.employee_id AND FROM employees e1,employees e2 WHERE e1.manager_id=e2.employee_id AND e1.last_name LIKE R%;e1.last_name LIKE R%;

35、(3)Cartesian Products(4)Inner Joins(5)Outer Joinsleft outer joinleft outer joinSELECT d.department_id,e.last_name FROM departments d LEFT OUTER JOIN SELECT d.department_id,e.last_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id=e.department_id ORDER BY d.department_id;employees

36、 e ON d.department_id=e.department_id ORDER BY d.department_id;right outer joinright outer joinSELECT d.department_id,e.last_name FROM departments d RIGHT OUTER JOIN SELECT d.department_id,e.last_name FROM departments d RIGHT OUTER JOIN employees e ON d.department_id=e.department_id ORDER BY d.depar

37、tment_id;employees e ON d.department_id=e.department_id ORDER BY d.department_id;full outer joinfull outer joinSELECT d.department_id as d_dept_id,e.department_id as e_dept_id,e.last_name SELECT d.department_id as d_dept_id,e.department_id as e_dept_id,e.last_name FROM departments d FULL OUTER JOIN

38、employees e ON d.department_id=FROM departments d FULL OUTER JOIN employees e ON d.department_id=e.department_id ORDER BY d.department_id;e.department_id ORDER BY d.department_id;1.8.2.子查询子查询 A subquery answers multiple-part questions.For example,to determine who works in Taylors department,you can

39、first use a subquery to determine the department in which Taylor works.You can then answer the original question with the parent SELECT statement.A subquery in the FROM clause of a SELECT statement is also called an inline view.A subquery in the WHERE clause of a SELECT statement is also called a ne

40、sted subquery.Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement.A correlated subquery is evaluated once for each row processed by the parent statement.The parent statement can be a SELECT,UPDATE,or DELETE statement.Correlated subquery Example:SELECT department_id,last_name,salary FROM employees x WHERE salary (SELECT AVG(salary)FROM employees WHERE x.department_id=department_id)ORDER BY department_id;1.8.3.Select语法语法1.8.3.Select语法语法演讲完毕，谢谢观看！