《Oracle数据库应用》课件9

第9章Oracle支持的SQL查询虚表和伪列1基本查询2Oracle支持的SQL函数3高级查询4当今的数据处理可分成两大类：联机事务处理OLTP(on-linetransactionprocessing)OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，如银行交易联机分析处理OLAP(On-LineAnalyticalProcessing)OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果OLTPOLAP用户操作人员,低层管理人员决策人员,高级管理人员功能日常操作处理

分析决策DB设计面向应用面向主题数据当前的,最新的细节的，二维的分立的聚集的,多维的集成的,统一的存取读/写数十条记录

读上百万条记录工作单位

简单的事务

复杂的查询用户数

上千个

上百个DB大小

100MB-GB

100GB-TBOLTP与OLAP之间的比较：9.1虚表和伪列Oracle的伪列和虚表虚表(伪表)DUAL该表是为了保证在使用SELECT语句中的语句的完整性而提供的伪列(Pseudocolumn)物理上并不存在，但却想查看的伪造的假列，即：只是在查询时才构造出来比如：CURRVAL/NEXTVAL使用序列号的保留字LEVEL查询数据所对应的级ROWID记录的唯一标识ROWNUM限制查询结果集的数量SQLSERVER不支持伪列/伪表9.2基本查询SELECT语句完成如下运算：投影(Projection)选取指定的列(字段)在SELECT子句中列出所需的列(字段)名选择(Selection)选取指定的行(记录)无条件选取全部行或选WHERE子句条件所限定的行连接(Joining)将多个表的数据连接在一起在FROM子句中列出多个表名然后在WHERE子句中指定连接条件由Join指定参与连接的多个表，然后在ON子句中指定连接条件SELECT语句基本查询一般将各子句单独成行书写，并采用缩进格式选择表中所有列*(星号)或在SELECT子句中列出所有的字段消除重复出现的行：使用DISTINCT限定词要善于使用别名(alias)可重命名表名、列名以改善查询结果的可读性若别名中使用空格或需区分大小写时，应使用双引号两种定义别名方法：别名直接写在列名或列表达式之后用AS定义，将别名放在AS关键字之后字符串和日期常量需用单引号括起来字符串常量区分大小写在SELECT子句中可使用运算符和SQL函数构造列表达式，在取出数据同时进行有关的运算FROM后可以连接的数据对象，即从何处取数据表表分区视图远程数据库结果集查询结果默认的排序方式为升序ASC数值型从小到大日期型按年份从小到大字符型按字母表顺序空值排在最后统计函数对一组查询行返回一个结果值除count(*)外，分组函数均忽略空值若要包括空值，须使用空值转换函数不可用在WHERE子句中用以限定查询的结果，对分组查询结果的限定应使用HAVING子句可有下面两个选项：(1)DISTINCT选项：使分组函数只考虑列表达式中的不同值(2)ALL选项：使分组函数考虑全部值，其中包含重复值主要的统计函数数据分组GROUPBY子句对记录分组并执行相应数据聚集运算在SELECT子句的列名表中的所有非分组函数计算列必须出现在GROUPBY子句中使用HAVING子句对数据分组后的返回结果进行限制HAVING子句应放在GROUPBY子句之后groupby子句需要将表中的数据按照某些字段值分组，然后对每组的数据进行统计，得到多个汇总结果用于分组的表达式里的字段，可以是1个也可是多个使用规则每组只产生一个汇总结果，每组只返回一行select子句中只能有2种类型的表达式：一是出现在groupby子句中的字段或是它的非统计函数表达式；另一种是其他非分组字段的统计函数having子句使用groupby和统计函数对记录分组后，还可使用having对分组后的结果进一步筛选使用规则当查询语句select中同时存在where/groupby/having时，执行顺序为where->groupby->having。其写法如下：selectfromwheregroupbyhaving分页查询查询出来的数据按每页多少条进行显示需要使用伪列ROWNUM使用子查询嵌套的方式语法：SELECT*FROM(SELECTA.*,ROWNUMRNFROM(SELECT*FROMtable)AWHEREROWNUM<=number_high)WHERERN>=number_low;例题：P143-148字符串函数LOWER(char)UPPER(char)INITCAP(char)CONCAT(char1,char2)SUBSTR(char,m[,n])LENGTH(char)INSTR(char1,char2[,m[,n]])LPAD(char1,n[,char2])RPAD(char1,n[,char2])TRIM(leading|trailing|both,trim_charFROMtrim_source)REPLACE(char1,char2[,char3])9.3Oracle支持的SQL函数数学函数ABS(n)CEIL(n)COS(n)COSH(n)EXP(n)LN(n)LOG(m,n)MOD(m,n)POWER(m,n)FLOOR(n)ROUND(m[,n])SIGN(n)SIN(n)SINH(n)SQRT(n)TAN(n)TANH(n)TRUNC(m[,n])日期函数MONTHS_BETWEEN(d1,d2)ADD_MONTHS(d,n)NEXT_DAY(d,s)LAST_DAY(d)ROUND(date,fmt)TRUNC(date,fmt)Oracle的类型转换自动类型转换：字符型和日期型、字符型和数值型间强制类型转换：使用类型转换函数常用的转换函数TO_CHAR(NUMBER|date,'format')TO_NUMBER(char,'format')TO_DATE(char,'format')例题：P149-1511.表的连接等值连接与非等值连接

SELECT...FROMtable1,table2WHEREtable1.column1opertable2.column2;等值连接指参与连接的多个表将连接条件列值相同的记录连接在一起作为查询结果记录返回等值连接，运算符oper为=非等值连接，oper可为!=、>、<、<=、>=等例题：P1529.4高级查询自连接（自身连接）出于某种业务逻辑的需要，将一个表看成两个副本进行关联查询将两个副本赋予不同的别名，使用别名构造连接条件外连接增加空行将不满足连接条件的记录也返回 SELECT... FROMtablealias1,tablealias2 WHERE{alias1.column1(+)=alias2.column2|alias1.column1=alias2.column2(+)};(+)所在位置的另一侧为连接的方向左外连接alias1.column1=alias2.column2(+)说明等号左侧的所有记录均会被显示右外连接alias1.column1(+)=alias2.column2说明等号右侧的所有记录均会被显示SQLServer中用*表示外连接1.leftouterjoin：左外连接=左连接selecte.last_name,e.department_id,d.department_namefromemployeeseleftouterjoindepartmentsd

on(e.department_id=d.department_id);等价于selecte.last_name,e.department_id,d.department_namefromemployeese,departmentsdwheree.department_id=d.department_id(+);结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录，即等号左侧的所有记录均会被显示2.rightouterjion：右外连接=右连接selecte.last_name,e.department_id,d.department_namefromemployeeserightouterjoindepartmentsd

on(e.department_id=d.department_id);等价于selecte.last_name,e.department_id,d.department_name

fromemployeese,departmentsd

wheree.department_id(+)=d.department_id;结果为：所有员工及对应部门的记录，包括没有任何员工的部门记录，即等号右侧的所有记录均会被显示3.fullouterjoin：全外连接selecte.last_name,e.department_id,d.department_namefromemployeesefullouterjoindepartmentsd

on(e.department_id=d.department_id);结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录使用连接时，应注意以下几点：(1)建议在列名之前使用表名前缀以改善运行性能(2)对表使用简短的别名可改善连接性能(3)使用准确的连接条件和WHERE子句条件可显著改善连接性能，不使用无条件的连接(即笛卡儿积)(4)应对同名的列冠以表名前缀以告知数据库该列取自哪个表2.子查询嵌入在其他SQL语句中的SELECT语句分类：简单子查询：用独立的条件返回值相关子查询：子查询WHERE条件中用到了嵌套它的SQL语句的当前记录值单行子查询：仅返回一条记录多行子查询：返回多条记录当子查询出现在WHERE子句中时WHEREexproperator (SELECTselect_list FROMtable );单行比较运算符：>、>=、<、<=、<>、=等多行比较运算符：IN、ANY、ALL、EXISTSANY：某一记录ALL：所有记录ANY/ALL与关系运算符一起使用的含义注：使用EXISTS的执行效率较高，只需返回一个布尔值相关子查询CREATETABLE<新表名>ASSELECT<列名>,<列名>,...

FROM<表名>

WHERE<列名或列表达式><比较运算符>(SELECT<列名>

FROM<表名>

WHERE<条件>);INSERTINTO<表名>(<列名>,<列名>,...)

SELECT<列名>,<列名>,...

FROM<表名>

WHERE<列名或列表达式><比较运算符>(SELECT<列名>

FROM<表名>

WHERE<条件>);UPDATE<表名>SET(<列名>,<列名>,...)=(SELECT<列名>,<列名>,...

FROM<表名>

WHERE<条件>)WHERE<列名或列表达式><比较运算符>(SELECT<列名>

FROM<表名>

WHERE<条件>);DELETEFROM<表名>WHERE<列名或列表达式><比较运算符>(SELECT<列名>

FROM<表名>

WHERE<条件>);SELECT...FROM<表名>WHERE<列名或列表达式><比较运算符>(SELECT<列名>

FROM<表名>

WHERE<条件>);例题：P154-1553.集合操作SELECT语句中的集合运算包括并UNION/交INTERSECT/差MINUS所有集合运算符具有相同的优先级按照从左(上)至右(下)的顺序执行可用括号改变运算的优先级各SELECT语句中的列数和字段类型必须相同作为DBA，要对数据的变化敏感，当查询结果与预期不符时，需查找问题所在对集合运算结果的排序应遵循以下规则：(1)ORDERBY子句只能出现在整个语句的最后，且只能用一次(2)ORDERBY子句使用的列名或别名只能取自第一个SELECT语句(3)ORDERBY子句可使用列的位置序号表示列名(4)默认以第一个SELECT语句的第一列的升序显示并UNIONUNION：返回多个查询中消除重复行以后的结果UNIONALL：返回每个查询语句得到的结果行不消除多个查询得到结果中的重复行在重复数据检查时不忽略空值(NULL)交INTERSECT返回所有查询语句得到的结果中的相同行各查询语句返回结果中有相同行，并不代表各对应基表中就存在相同记录，只是与选取的列有关交运算不忽略空值差MINUS返回在第一个查询语句的结果集中却不在第二个查询语句的结果集中的所有记录行差运算不忽略空值例题：P156UNIONUNIONALLINTERSECTMINUS集合运算要求SELECT语句的列数与列类型要一致。当不一致而又需保持一致时，可用虚列占位。用虚列(dummycolumn)占位，使用类型转换函数满足其他SELECT语句的列对类型的要求例如：SELECTdepartment_id,TO_NUMBER(null)location,hire_dateFROMemployeesUNIONSELECTdepartment_id,location_id,TO_DATE(null)

FROMdepartments;4.TOP-N查询用于获取某列数据中最大或最小的n个值语法：SELECT[column_list],ROWNUMFROM(SELECT[column_list]