使用php做的网站,wordpress响应式主题制作教程,重庆网站快速排名优化,衡阳市做网站查询是数据库的基本应用之一#xff0c;oracle中的SQL查询语法#xff0c;一方面遵循了SQL标准#xff0c;另一方面又有自己的独特之处。 从而使得oracle中的SQL查询功能更加强大。接下来将会涉及oracle中的SQL查询语句#xff0c;包括#xff1a; 基本查询#xff1a;主… 查询是数据库的基本应用之一oracle中的SQL查询语法一方面遵循了SQL标准另一方面又有自己的独特之处。 从而使得oracle中的SQL查询功能更加强大。接下来将会涉及oracle中的SQL查询语句包括 基本查询主要讲述查询语句及各种子句的使用 子查询主要讲述如何在查询语句中添加子查询 联合语句主要讲述多个查询语句之间的集合运算 关联语句主要讲述多个表/视图之间的关联关系 层次化查询主要讲述树状结构的查询。 一、基本查询 1. 查询命令select 执行查询的命令为select命令该命令用于在数据源中捕获最终数据。无论查询语句多么复杂最外层的select命令总是最后执行。 具体表现为oracle首先根据from子句获得数据源的所有记录接着oracle扫描所有记录并根据select命令所指定的列获取最终结果。 2. 指定过滤条件----where子句 where子句用于限定from子句所指定的数据源或者各数据源进行运算之后形成的结果集合。因此执行顺序处于from子句之后select命令之前。即使from子句更加复杂其执行顺序仍然如此。 注笛卡尔积--获得的是两个数据表的乘积第一表的每条记录与第二表的每条记录组合所获得结果集。 3. 获取唯一记录----distinct 在查询时会出现重复记录。为了剔除重复记录可以利用distinct关键字。 select distinct a.xxx, b.yyy from table a, tables b where a.a_id b.a_id 其中distinct关键字用于获取结果集中列a.xxxb.yyy的唯一性组合。 4. 分组----group by 子句 在数据库查询中分组是一个非常重要的应用。分组是指将数据表中的所有记录以某个或者某些列为标准划分为一组。例如在一个存储了地区学生的表中以学校为标准可以将所有学生信息划分为多个组。 进行分组查询应该使用group by子句。该子句指定分组标准并将数据源按照该标准进行划分然后循环处理每组数据。 select cd.channel_id, cd.channel_name from intf.ecs_order eo , liuxin.channel_dianqu cd where eo.org_id cd.channel_id group by cd.channel_id, cd.channel_name 其中group by cd.channel_id, cd.channel_name用于对数据源按列 cd.channel_id, cd.channel_name 进行分组。详细步骤如下 1from子句获得数据源2利用where 子句筛选符合条件的记录3利用group by子句进行分组 4对每组进行循环处理获得最终结果----每组的cd.channel_id, cd.channel_name。 5. 过滤分组----having子句 where子句可以过滤from子句所指定的数据源但是对于group by子句所产生的分组无效。为了将分组按照一定条件进行过滤应该使用having子句。 having子句是依附于group by子句存在而存在的。 select cd.channel_id, cd.channel_name from intf.ecs_order eo , liuxin.channel_dianqu cd where eo.org_id cd.channel_id group by cd.channel_id, cd.channel_name having cd.channel_id 100000199 上面例子说明的是ID号大于100000199的记录。 1利用group by子句分组2利用having子句筛选分组----组内所有ID号大于100000199。 3对每组进行循环处理获得最终结果----每组的cd.channel_id, cd.channel_name。 6. 排序----order by子句 order by子句用于排序结果集。order by子句在使用时需要指定排序标准和排序方式。排序标准是指按照结果集中哪个或哪些列进行排序order by有两种排序方式---升序asc也是默认的排序方式和降序desc。 select cd.channel_id, cd.channel_name from intf.ecs_order eo , liuxin.channel_dianqu cd where eo.org_id cd.channel_id order by cd.channel_id desc; 7. order by与group by子句 order by与group by同时存在的时候oracle是怎样执行的呢 oracle首先执行group by子句然后才进行排序操作。 8. order by与distinct order by子句与distinct关键字同时使用时也必须遵循这样的一个规则---order by子句所指定的排序列必须出现在select表达式中。 二、子查询 子查询是指在查询语句的内部嵌入查询以获得临时的结果集。oracle总是自动优化带有子查询的查询语句。如果子查询的数据源与父查询中的数据可以实现连接操作那么将转化为连接操作否则将首先执行子查询然后执行父查询。 内嵌视图也可以看做子查询的一种形式内嵌视图式的子查询出现在from子句中作为父查询的数据源。 本阶段重点讲述子查询的另外两种应用模式用于查询条件和insert into语句。 1、查询条件中的子查询 子查询可以用于查询条件。例子 select * from employees where employee_id in (select employee_id from salary); 其中select employee_id from salary 是子查询。 子查询实际返回一个结果集该结果集仅有有一列并用于查询条件where employee_id in (select employee_id from salary)中。该查询条件要求搜寻表employees中employee_id的值在子查询的结果集中。 2、建表语句中的子查询 子查询的另外一个应用场景用于建表语句中。 例如oracle的内置视图user_objects描述了当前用户所有对象信息现需要创建一个与该视图具有相同结果的空数据表则可以利用如下SQL语句。 create table tmp_user_objects as select * from user_objects where 11; 子查询select * from user_objects where 11 获得的实际是一个空结果集利用该结果集创建数据表时将创建一个空的数据表。 利用语句可浏览desc tmp_user_objects; 3、插入语句中的子查询 同样我们也可以在插入语句中使用子查询这相当于向表中批量插入数据。 例如insert into tmp_user_objects selects * from user_objects where object_type table; slect * from user_objects where objects_type TABLE用于获得视图 user_objects 中object_type 为table的所有记录。 该子查询的所有记录将被插入表tmp_user_objects中。 三、联合语句 联合语句是指对于多个查询获得的结果集进行集合操作。这些集合操作包括union、union all、intersect 和 minus。 这些集合运算都是二元运算运算结果仍然是一个记录集合。 本阶段讲述这几种联合运算的使用。 1、求并集记录唯一----union运算 union运算实际是合并两个结果集中的所有记录并将其中重复记录剔除保证结果集中的记录唯一。 例数据库中存在着两个表a_students和b_students分别存储了参加了a培训班和b培训班的学生信息其数据如下所示。现需要取得a班和b班共有多少学生实际为获取表a_students与表b_students的并集相应的SQL语句如下所示。 select student_id, student_name from a_students union select student_id, student_name from b_students; union 用于对select student_id, student_name from a_students 和 select student_id, student_name from b_students所获得结果集进行并集运算。 但需要注意的是union运算的两个结果集必须具有完全相同的列数并且各列具有相同的数据类型。 select student_id, student_name, student_age from a_students union select student_id, student_name from b_students 上面这个就会报错 oracle将抛出错误ORA-01789query block has incorrect number of result columns表面查询结果的列数非法不能进行并集运算。 2、求并集----union all 运算 union all 运算与union运算都可看做并集运算。但是union all只是将两个运算结果集进行简单整合并不剔除其中的重复数据。这是与union运算的最大区别。 为了统计 例如为了统计A班级和B班级有多少人次参加了培训则可以利用union all代替union来执行上面的SQL语句。 select student_id, student_name from a_students union all select student_id, student_name from b_students; union all 并不删除重复记录因此该SQL语句的执行结果记录较多。同时由于union all运算不删除重复记录因此在执行效率上要高于union操作。因此当对两个结果集已经确定不会存在重复记录时应该使用union all操作以提升效率。 3、求交集---intersect运算 intersect运算是指交集运算。该运算可以获得两个结果集的交集----即同时存在于两个结果集中的记录。 例如在表a_students和表b_students中均存在着学生信息现需要获得既参加了a班又参加了b班的学生姓名。即同时存在于两个表中的学生姓名则可以使用intersect运算。相应的SQL如下 select student_name from a_students intersect select student_name from b_students 4、求差集----minus运算 minus是集合间的减法运算。该运算将返回第一个集合中存在而第二个集合中不存在的记录。 例如现需要获得参加A培训班但是未参加B培训班的学生。此时可以利用minus运算获得存在于表a_students中而不存在于表b_students中的学生姓名。 select student_name from a_students minus select student_name from a_students; minus用于获得两个结果集的差集。注意是返回得哪个表中的 5、联合运算的混合运算 对于这4种集合运算----union运算、union all运算、intersect运算和minus运算oracle允许进行混合运算。在混合运算时这4种运算的优先级是相同的也就是说他们将按照自左至右的顺序依次进行。 例如intersect和union all的混合运算 select student_name from a_students intersect select student_name from a_students union all select student_name from b_students 调整优先级可以使用小括号 四、连接 在大多数查询中所使用的数据源往往有多个。当多个数据源同时使用时这些数据源如何进行组合便成为了一个至关重要的问题。连接即用来指定多个数据源之间的组合关系。默认情况下多个数据源之间使用的是笛卡尔积方式进行组合。除此之外oracle还提供了另外几种特殊的组合方式。这些特殊方式有效地补充了笛卡尔积的不足。 1、自然连接 自然连接顾名思义即无须用户指定任何连接条件只需指定连接的两个数据源。至于两个数据源如何进行数据整合则无须用户操心自然连接所使用的关键字为natural join。其连接原则两个数据源的共有列并且具有相同列值。 例如表employees和表salary都包含了employee_id列。二者可以进行自然连接操作相应的SQL语句如下所示。 select * from employees natural join salary; natural join 用于两个表之间的自然连接。搜寻结果获得的结果集将含有公共列employee_id。这里所说的公共列是指不能为列employee_id指定限定词。例如不能讲列employee_id标识为特定表employees或者salary中的列。详看如下SQL select e.employee_id from employees e natural join salary s; 上面语句将会抛出错误ORA-25155column used in NATURAL join cannot have qualifier select e.employee_id尝试在自然连接的结果集中获得表employees中的列employee_id。此处的错误原因是用于自然连接的列不能指定限定词。 当然对于自然连接之外的其他列可以使用限定词进行修饰。例如 select employee_id, e.employee_name, s.month, s.salary from employees e natural join salary s 2、内连接 自然连接强制使用两个表之间的公共列表作为搜寻条件而且要求公共列的值必须相等这带来了极大的限制因此自然连接并不常用。而内连接突破了这两种约束内连接可以自行指定连接列和连接条件。内连接运算的关键字为inner join. 例如同样为了实现获得员工工资状况这一需求利用内连接的SQL语句如下所示。 selecte.employee_id, e.employee_name, s.month, s.salary from employees e inner join salarys on e.employee_id s.employee_id; 其中select e.employee_id, e.employee_name, s.month, s.salary 用于获取表employees中的employee_id、employee_name和表salary中的month、salary等列与自然连接不同的是此处的employee_id可以使用限定符e进行修饰from employees e inner join salary s 用于将表employees与表salary进行内联操作on e.employee_id s.employee_id用于指定搜寻条件----表employees的列employee_id与表salary的列employee_id具有相同的列值。 另外内连接运算inner join 中的inner关键字可以省略如下所示。 selecte.employee_id, e.employee_name, s.month, s.salary from employees e join salary s on e.employee_id s.employee_id 默认情况下oracle的连接为内连接因此在这里使用了join代替inner join可以实现相同的效果而且写法更为简洁。 对于大多数开发者来说并不习惯使用内连接方式而更习惯于where 条件实现利用where 条件改写本示例语句如下 selecte.employee_id, e.employee_name, s.month, s.salary from employees e,salary s wheree.employee_id s.employee_id; 虽然可以利用where 子句改写内连接的SQL查询但需要注意的是当实现多表关联oracle在执行时还是有区别的对于where子句方式并且from子句中含有多个数据源oracle在进行笛卡尔积运算时会自行优化。例如 select * fromemployees, salary, company, sales wehre ... from 子句中包含了多个数据表而oracle执行的方式并非按照如下顺序employees X salary X company X sales其中X代表进行笛卡尔积运算。而有可能被优化为salary X employees X sales X company因此有时我们对oracle的执行结果感到莫名其妙使用肉眼即可分辨这种执行结果是错误的尽管这种机会微乎其徽。 此时不妨使用内连接来尝试解决该问题改写后的代码为 select * fromemployees join salary on.... join company on join sales on 在这种执行方式下oracle所执行的连接顺序一定是遵循employees X salary X company X sales。 3、外链接 内连接所指定的两个数据源处于平等的地位。而外链接不同外链接总是以一个数据源为基础将另外一个数据源与之进行条件匹配。即使条件不匹配基础数据源中的数据总是出现在结果集中。那么依据哪个数据源作为基础数据源便出现了两种外链接的方式----左外连接和右外连接。因为内连接没有左右之分所以习惯上将左外连接和右外连接简称为左连接和右连接。 3.1 左连接 例子左连接和右连接的运算应该使用left join 和right join.利用内连接获得员工的工资信息但是这并不包括所有员工的工资状况因为有的员工并未出现在工资表salary中为了获得所有员工的工资状况可以利用外连接来实现。 selecte.employee_id, e.employee_name, s.month, s.salary from employeese left join salary s on e.employee_id s.employee_id; 其中from employees e left join salary s 指定数据源为表employees与salry的左连接如果将left join 视作运算符那么左表为employees,右表为salary左连接的意思即以左表----employees为基础表on e.employee_id s.employee_id指定连接条件----表employees的employee_id列与表salary的employee_id具有相同的值。 3.2 右连接 与左连接相反右连接是以运算符右侧的表作为基础表来实现关联。右连接的运算符为right join. 3.3 外连接的简略写法 使用left join和right join无非是为了判断到底以数据源作为基础因此oracle提供了外连接的简略写法----在where条件中奖附属数据源的列使用进行标识从而省略left join right join 及on关键字。 wheree.employee_id s.employee_id() --左连接 wheree.employee_id() s.employee_id --右连接 3.4 完全连接 完全连接实际是一个左连接和右连接的组合也就是说如果两个数据源使用了完全连接那么将首先进行一次左连接然后进行一次右连接最后再删除其中的重复记录即得到完全连接。完全连接应该使用full join 关键字并使用on关键字指定连接条件。 selecte.employee_id, e.employee_name, s.month, s.salary from employees e full join salarys on e.employee_id s.employee_id; 完全连接的执行过程首先执行employees与salary的左连接然后执行二者的右连接最后将两个临时结果集进行union操作。 select e.employee_id, e.employee_name, s.month, s.salary from employees e left join salary s on e.employee_id s.employee_id union select e.employee_id, e.employee_name, s.month, s.salary from employees e right join salary s on e.employee_id s.employee_id; 五、层次化查询 关系型数据库中同一个数据表中的记录具有相同的列因此不同的记录之间存在着平行关系。但是有时候各记录之间也可能存在着“父子”关系。当这些“父子”关系较为复杂时可以将整个表中的数据看做树状结构而基于树状结构数据的查询称为层次化查询。 1、树状模型 树状模型一个很典型的实例为市场信息。不同层级的市场之间存在着父子关系如下图 可以将市场信息存储于表market中相应的表结构及数据如下所示。 在该表中列parent_market_id非常重要。该列存储了当前市场的父级市场ID依据parent_market_id与market_id可以将原本平行结构的市场信息组装为获得树状模型。 2、层次化查询 自9i版本开始oracle提供了层次化查询方案递归获得树状模型的信息层次化查询的语法如下所示 select 列名1、列名2..... from 表名 start with 开始条件 connect by 递归条件 其中start with指定查询的起点即从哪些记录开始查询connect by指定递归条件以获得下一条记录。 例如在表market中搜索市场“亚洲”及其所有市场信息相应的SQL语句及搜寻结果如下所示。 select market_id, market_name from market start with market_name 亚洲 connect by prior market_id parent_market_id start with market_name 亚洲指定查询的起始节点为market_name等于亚洲的记录connect by prior market_id parent_market_id指定如何递归获得下一条记录----前一条记录的market_id等于下一条记录的parent_market_id。 connect by 遵循深度优先的搜索策略对于以上SQL语句其执行顺序如下所示 1start with market_name 亚洲,获得一条记录。该记录为查询的起始结点 2当使用connect by prior parent_market_id market_id进行递归时。prior是指前一条记录即当前记录‘亚洲’。前一条记录的parent_market_id等于下一条的market_id那么将获得3条记录即market_id分别为5.6.7的记录。oracle将利用最先获得记录中国再次进行递归 3connect by prior parent_market_id market_id对于递归操作前一条prior记录为中国因此此次的递归操作会再次获得下级市场信息。 4对于记录北京当oracle再次利用connect by prior market_id parent_market_id尝试获得下一条记录时将无法获得任何记录。这意味着已完成了最“左”端最深层的递归查询。那么oracle将返回上一级记录中国并对第二条记录天津继续进行递归查询。 5以此类推oracle遵循从左至右、深度优先的策略可以递归的获得亚洲及亚洲之下的所有市场信息。 通过以上步骤及查询语句的执行结果可知利用connect by 实现的层次化查询返回的实际是一个结果集该结果集为递归遍历所有经过的每条记录(树中的结点)的集合。 3、层次化查询的相关函数 对于层次化查询最常用的函数为sys_connect_by_path()函数。层次化查询总是以某条记录为起点根据connect by所指定的条件递归获得结果集合。 而sys_connect_by_path()函数则可以对起始至当前记录之间的结果集进行聚合操作。该操作仅限于串联字符串响应的语法如下 sys_connect_by_path(列名分隔符) 其中列名指定将哪个列的值进行串联而分割符则指定字符串串联时的分隔符。 例如为了获得北京至顶级市场的完整路径可以使用如下SQL语句。 select market_id, market_name, sys_connect_by_path(market_name, / ) market_path from market start with market_name 北京 connect by prior parent_market_id market_id; select market_id, market_name, sys_connect_by_path(market_name, / )market_path用于获得层次化查询过程中每条记录的market_id、market_name、起始记录至当前记录的market_name列的字符串串联串联过程中使用/作为分隔符start with market_name 北京 connect by prior parent_market_id market_id指定层次化查询的定义---从market_name等于北京的记录开始按照前一条记录的parent_market_id等于下一条记录的market_id的规则进行递归。 分析查询结果可知市场路径是节节攀升的鉴于此可以利用max()函数获得最完整的路径。 select max(sys_connect_by_path(market_name, /)) market_path from market start with market_name 北京 connect by prior parent_market_id market_id; 结果为/北京/中国/亚洲/全球 其中max()函数可用于字符串类型已获得按字母表顺序排列时处于末位位置的字符串。 4、总结一条记录衍生出多条记录 对于数据表中的某条记录有时需要多条记录为其服务。这就涉及一条记录衍生出多条记录的问题。在oracle中如果要使一条记录衍生出多条记录大致有两种方法一种是通过窗口函数将在后面介绍另一种是通过层次化查询。 对于窗口函数来说数据表中有多条记录是必要的如果一个数据表中仅有一条记录那么无论如何是无法衍生出多条记录的。并且衍生出的记录数目窗口大小也不能大于数据表的记录总数。 针对层次化查询语法所能获得的记录数目则要灵活的多。connect by 只是指定获得下一条记录的条件针对start with所指定的记录如果没有start with则针对所有记录oracle会依次扫描数据表中的记录并以递归的方式进行下去。也就是说connect by完全具备死循环的条件。 1、二进制转十进制 在oracle 9i以后oracle提供了bin_to_num()函数用于将二进制数据转换为数值型例如 select bin_to_num(1, 0, 1) a, bin_to_num(1,0) b from dual; 同样可以利用如下SQL语句进行处理 select sum(data) from ( select substr(101, rownum, 1) * power (2, length(101) - rownum) data from dual connect by rownum length(101)) 在该SQL语句中表dual本来仅有一条记录X但是当利用connect by rownum length(101)获得下一条记录时仍会获得记录X直至获得的记录总数等于3二进制101的总位数。而针对每次获得的记录oracle依次计算各位置对应的实际数字利用2的乘方运算最后将所有数字相加便获得了二进制数字代表的十进制数字。 转载于:https://www.cnblogs.com/iamliuxin/p/5617814.html
