跳转到内容

XQuery/XQuery 从 SQL

Add links
来自维基教科书，开放世界中的开放书籍

Scott/Tiger 示例

[编辑 | 编辑源代码]

一个广泛用于教授关系数据库的经典数据库，涉及员工、部门和薪资等级。在 Oracle 培训资料中，它以演示用户名和密码 Scott/Tiger 而闻名。

这三个表被转换为 XML（通过 Excel 的 XML 附加程序 XQuery/Excel 和 XML），如下所示

员工 XML 表格 MySQL

部门 XML 表格 MySQL

薪资等级 XML 表格 MySQL

Oracle SQL 文件的 MySQL 端口可以在这里找到 这里.

执行环境

[编辑 | 编辑源代码]

eXist 演示服务器用于 XQuery 示例。这些示例以纯 XML 或转换为表格格式返回。

等效的 SQL 查询在 w:MySQL 服务器上执行，该服务器也位于 布里斯托尔西英格兰大学

基本查询

[编辑 | 编辑源代码]

计数记录

[编辑 | 编辑源代码]
任务：有多少员工？
[编辑 | 编辑源代码]

SQL: select count(*) from Emp; MySQL

XQuery: count(//Emp) XML

任务：有多少个部门？
[编辑 | 编辑源代码]

SQL: select count(*) from dept MySQL

XQuery: count(//Dept) XML

选择记录

[编辑 | 编辑源代码]

任务：显示所有薪资大于 1000 的员工

[编辑 | 编辑源代码]

SQL: select * from emp where sal > 1000; MySQL

XQuery: //Emp[Sal>1000] XML 表格

任务：显示所有薪资大于 1000 且小于 2000 的员工

[编辑 | 编辑源代码]

SQL: select * from emp where sal between 1000 and 2000; MySQL

XQuery: //Emp[Sal>1000][Sal<2000] XML 表格

这里，连续的过滤条件取代了“between”隐含的与条件。

尽管 XQuery 中没有“between”函数，但编写一个很简单

declare function local:between($value as xs:decimal, $min as xs:decimal, $max as xs:decimal) as xs:boolean {
  $value >= $min and $value <= $max
};

这简化了查询为 //Emp[local:between(Sal,1000,2000)] XML 表格

并且具有将 Sal 转换为数字现在在函数签名中隐含的优点。

任务：显示所有没有佣金的员工

[编辑 | 编辑源代码]

SQL: select * from emp where comm is null; MySQL

XQuery: //Emp[empty(Comm/text())] XML 表格

请注意，empty(Comm) 不够，因为这只有在元素本身不存在时才为真，在本示例 XML 中它并不存在。

XQuery: //Emp[empty(Comm)] XML

任务: 选择前 5 名员工

[edit | edit source]

SQL: select * from emp limit 5; MySQL

XQuery: //Emp[position() <=5]

XML 表格

选择列

[edit | edit source]

列出员工姓名和薪资

[edit | edit source]

SQL: Select ename,sal from emp MySQL

令人惊讶的是，XPath 不支持仅选择节点中子节点的子集（修剪）。

//Emp/(Ename,Sal) XML 检索了所需的元素，但父 Emp 节点已丢失。

//Emp/(Ename|Sal) XML 更好，因为它按顺序保持元素，但它不会返回仅包含 Ename 和 Sal 子节点的 Emp 节点，如所要求的那样。

//Emp/*[name(.) = ("Ename","Sal")] XML 使用元素名称的反射。


XQuery

    for $emp in //Emp 
    return 
       <Emp>
         {$emp/(Ename|Sal)}
       </Emp>

XML 表格

这里使用 XQuery FLWOR 表达式从原始元素创建新的 EMP 元素。

计算值

[edit | edit source]

计算年薪

[edit | edit source]

任务: 计算所有员工的年薪。年薪按月薪的 12 倍加上佣金计算。由于佣金可能是空值，因此必须用合适的数值替换。

SQL: select 12 * sal + ifnull(comm,0) from emp; MySQL

XQuery: //Emp/(12*number(Sal)+(if(exists(Comm/text())) then number(Comm) else 0)) XML


SQL 函数 COALESCE 与 IFNULL 相同，但它接受多个参数。

SQL: select 12 * sal + coalesce(comm,0) from emp; MySQL

XQuery: //Emp/(12*number(Sal)+ number((Comm/text(),0)[1])) XML

在这个简单的例子中，由于缺少用于承载项目类型的信息的模式，因此需要显式地将字符串转换为数字。

注意 XQuery 习惯用法

 (Comm/text(),0)[1]

计算序列中的第一个非空项目，与 COALESCE 相对应。

选择和创建列

[edit | edit source]

任务: 列出员工姓名及其年薪。

SQL: select ename, 12 * sal + ifnull(comm,0) as "Annual Salary" from emp; MySQL

XQuery

for $emp in //Emp 
return <Emp>
         {$emp/Ename}
         <AnnualSalary>
            {12*number($emp/Sal)+ 
             (if (exists($emp/Comm/text())) 
             then number($emp/Comm)
             else 0)
            }
         </AnnualSalary>
       </Emp>

XML 表格

我们再次遇到了树修剪的问题，但现在还增加了嫁接，这也需要显式地构造 XML 节点。


SQL 运算符

[edit | edit source]

IN

[edit | edit source]

任务: 显示所有工作职位为 ANALYST 或 MANAGER 的员工。

SQL: select * from emp where job in ("ANALYST","MANAGER") MySQL


XQuery: //Emp[Job = ("ANALYST","MANAGER")]

XML 表格

NOT IN

[edit | edit source]

任务: 选择所有工作职位不为 'ANALYST' 或 'MANAGER' 的员工。

SQL: select * from emp where job not in ("ANALYST","MANAGER") MySQL

这不起作用

XQuery: //Emp[Job !=("ANALYST","MANAGER")] XML 表格

这里的广义等于始终为真，因为每个人要么不是 ANALYST，要么不是 MANAGER。这可以工作。

XQuery: //Emp[not(Job =("ANALYST","MANAGER"))] XML 表格

不同的值

[edit | edit source]

任务: 显示员工担任的不同职位。

MySQL: select distinct job from emp; MySQL

XQuery: distinct-values(//Emp/Job) XML

模式匹配

[edit | edit source]

任务: 列出所有姓名以 "S" 开头的员工。

MySQL: select * from emp where ename like "S%"; MySQL

XQuery: //Emp[starts-with(Ename,"S")] XML 表格

参见 starts-with()

任务: 列出所有姓名包含 "AR" 的员工。

MySQL: select * from emp where ename like "%AR%"; MySQL

XQuery: //Emp[contains(Ename,"AR")] XML 表格

参见 contains()

任务: 列出所有姓名包含 "ar" 的员工，不区分大小写。

MySQL: select * from emp where ename like "%ar%"; MySQL

SQL 中的 LIKE 不区分大小写，但 fn:contains() 区分大小写，因此需要转换大小写。

XQuery: //Emp[contains(upper-case(Ename),upper-case("ar"))] XML 表格

参见 upper-case()


更复杂的模式需要正则表达式。

MySQL: select * from emp where ename regexp "M.*R"; MySQL

XQuery: //Emp[matches(Ename,"M.*R")] XML 表格

参见 matches()

类似地，SQL 的 REGEXP 不区分大小写，而 XQuery matches() 中的附加标志控制匹配。

MySQL: select * from emp where ename regexp "m.*r"; MySQL

XQuery: //Emp[matches(Ename,"m.*r",'i')] XML 表格

（'i' 使正则表达式匹配不区分大小写。）

表连接

[edit | edit source]

简单的内连接

[edit | edit source]

任务: 查找员工 'SMITH' 所在部门的名称。

SQL 

select dept.dname 
from emp, dept 
where dept.deptno = emp.deptno  
   and ename='SMITH';

MySQL


XPath : //Dept[DeptNo = //Emp[Ename='SMITH']/DeptNo]/Dname

XML

也许 XQuery 中的 FLWOR 表达式更易读


let $dept := //Emp[Ename='SMITH']/DeptNo
return //Dept[DeptNo = $dept ]/Dname

XML

任务：查找所有会计部门员工的姓名

SQL

select emp.ename 
from emp,dept 
where dept.deptno = emp.deptno 
  and dname='Accounting';

MySQL

XPath：//Emp[DeptNo = //Dept[Dname='Accounting']/DeptNo]/Ename

XML

XQuery

let $dept := //Dept[Dname='Accounting']/DeptNo
return //Emp[DeptNo = $dept]/Ename

XML


注意，在这个版本的 eXist 中，等式中操作数的顺序很重要 - 在以后的版本中会修复。

XQuery：//Emp[Dname='Accounting'/DeptNo = //Emp/DeptNo]/Ename


XML

更复杂的内连接

[编辑 | 编辑源代码]

任务：列出每个员工的姓名，以及他们所在部门的名称和位置。

SQL

  select ename, dname,location
    from emp, dept
   where emp.deptno = dept.deptno;

MySQL

如果必须从多个节点中选择元素，XPath 就无能为力，需要使用 XQuery

XQuery

此连接可以写成

  for $emp in //Emp
  for $dept in //Dept
  where $dept/DeptNo= $emp/DeptNo
  return
    <Emp>
      {$emp/Ename}
      {$dept/(Dname|Location)}
    </Emp>

XML 表格

但更常见的是以子选择的形式编写

  for $emp in //Emp
  let $dept := //Dept[DeptNo=$emp/DeptNo]
  return
    <Emp>
      {$emp/Ename}
      {$dept/(Dname|Location)}
    </Emp>

XML 表格

带选择的内连接

[编辑 | 编辑源代码]

任务：列出所有分析师的姓名和部门。

SQL

  select ename, dname
    from emp, dept
   where emp.deptno = dept.deptno
     and job="ANALYST";

MySQL

XQuery

  for $emp in //Emp[Job='ANALYST']
  let $dept := //Dept[DeptNo= $emp/DeptNo]
  return
    <Emp>
      {$emp/Ename}
      {$dept/Dname}
    </Emp>

XML 表格

一对多查询

[编辑 | 编辑源代码]

任务：列出部门以及每个部门的员工数量。

SQL

  select dname,
         (select count(*)
            from emp
           where deptno = dept.deptno
         ) as headcount
    from dept;

MySQL

XQuery

  for $dept in //Dept 
  let $headCount := count(//Emp[DeptNo=$dept/DeptNo])
  return  
    <Dept>
       {$dept/Dname}
       <HeadCount>{$headCount}</HeadCount>
    </Dept>


XML 表格

Theta (不等式) 连接

[编辑 | 编辑源代码]

任务：按升序排列的薪级顺序列出员工姓名和薪级。

薪级由最低和最高工资定义。

SQL

select ename, grade
  from emp, salgrade
 where emp.sal between salgrade.losal and salgrade.hisal;

MySQL

XQuery

for $emp in //Emp
let $grade :=  //SalGrade[number($emp/Sal) > number(LoSal)][number($emp/Sal) < number(HiSal)]/Grade
order by $grade
return
  <Emp>
    {$emp/Ename}
    {$grade}
  </Emp>

XML 表格

递归关系

[编辑 | 编辑源代码]

员工与其经理之间的关系是递归关系。

任务：列出每个员工的姓名以及他们经理的姓名。

SQL

select e.ename, m.ename
  from emp e
  join emp m on e.mgr = m.empno

MySQL

XQuery

for $emp in //Emp
let $manager := //Emp[EmpNo = $emp/MgrNo]
return
  <Emp>
    {$emp/Ename}
    <Manager>{string($manager/Ename)}</Manager>
  </Emp>

XML 表格

XQuery 的结果与 SQL 的结果不太一样。King 没有经理，因此缺席了 SQL 内连接。要在 XQuery 中产生相同的结果，我们需要过滤具有经理的员工

for $emp in //Emp[MgrNo]
let $manager := //Emp[EmpNo = $emp/MgrNo]
where $emp/MgrNo/text()
return
  <Emp>
    {$emp/Ename}
    <Manager>{string($manager/Ename)}</Manager>
  </Emp>

XML 表格

或者，外连接会返回所有员工，包括 King

SQL

select e.ename, m.ename
  from emp e
  left join emp m on e.mgr = m.empno

MySQL

转换为组织树

[编辑 | 编辑源代码]

经理关系定义了一个树状结构，King 是根节点，她的直属下属是她孩子，依此类推。XQuery 中的递归函数可以解决此任务。

XQuery

 declare function local:hierarchy($emp) {
     <Emp name='{$emp/Ename}'>
       <Reports>
       {for $e in  //Emp[MgrNo = $emp/EmpNo]
        return
           local:hierarchy($e)
       }
       </Reports>
     </Emp>
 };
 
 local:hierarchy(//Emp[empty(MgrNo/text())])

XML

转换为部门/员工层次结构

[编辑 | 编辑源代码]

为了导出，可以创建一个单个 XML 文件，其中员工嵌套在部门中。由于 Dept/Emp 关系恰好是一对多，因此可以在不引入冗余或数据丢失的情况下做到这一点。

XQuery

<Company>
  {for $dept in //Dept
  return
    <Department>
      {$dept/*}   
      {for $emp in //Emp[DeptNo = $dept/DeptNo]  
      return $emp
      }
    </Department>
  }
</Company>

XML

使用这种简单的方法，Emp 中的外部键 DeptNo 已被包含，但现在它已变得多余。except 运算符在这里很有用

<Company>
   {for $dept in //Dept
    return
       <Department>
          {$dept/*}   
          {for $emp in //Emp[DeptNo = $dept/DeptNo]  
           return 
              <Employee>
                  {$emp/* except $emp/DeptNo}
              </Employee>
          }
       </Department>
    }
</Company>

XML

注意，这假设没有要复制的属性。如果有，它们将使用 $emp/@* 复制

处理层次数据

[编辑 | 编辑源代码]

此层次数据可以在 XQuery 中直接查询。

员工路径

[编辑 | 编辑源代码]

几乎所有查询都保持不变（除了将元素名称更改为 Employee）。这是因为用于在 Emp.xml 文档中选择 Emp 的路径是 //Emp，现在在合并的文档中是 //Employee。如果使用完整的路径（/EmpList/Emp），则需要将其替换为 /Company/Department/Employee

简单导航

[编辑 | 编辑源代码]

任务：查找员工“Smith”的部门名称。

XQuery：//Employee[Ename='SMITH']/../Dname XML

任务：查找会计部门员工的姓名。

XQuery：//Department[Dname='Accounting']/Employee/Ename XML

部门/员工连接

[编辑 | 编辑源代码]

主要变化在于需要在员工和部门之间进行连接的查询，因为它们已经嵌套在一起，因此变成了向上（从员工到部门）或向下（从部门到员工）导航树。

多对一

[编辑 | 编辑源代码]

列出员工及其所在部门位置的查询（使用单独的文档）为

 for $emp in //Emp
 for $dept in //Dept
  where $dept/DeptNo=$emp/DeptNo
  return
    <Emp>
      {$emp/Ename}
      {$dept/(Dname|Location)}
    </Emp>

XML 表格

对于一个嵌套文档，它变成了

 for $emp in //Employee
 return
    <Employee>
      {$emp/Ename}
      {$emp/../Location}
    </Employee>

XML 表格，使用父级访问向上移动树。

一对多

[编辑 | 编辑源代码]

列出部门和员工数量（在单独的表格中）为

for $dept in //Dept 
let $headCount := count(//Emp[DeptNo=$dept/DeptNo])
return  
    <Dept>
       {$dept/Dname}
       <HeadCount>{$headCount}</HeadCount>
    </Dept>

XML 表格

它变成了

for $dept in //Department
let $headCount := count($dept/Employee)
return  
    <Department>
       {$dept/Dname}
       <HeadCount>{$headCount}</HeadCount>
    </Department>

XML 表格

汇总和分组

[编辑 | 编辑源代码]

汇总数据

[编辑 | 编辑源代码]

任务：显示经理的数量、平均工资（四舍五入）、最低工资和最高工资。

SQL：SELECT count(*), round(avg(sal)), min(sal), max(sal) FROM emp WHERE job='MANAGER'; MySQL

XQuery

   (count(//Emp[Job='MANAGER']),round(avg(//Emp[Job='MANAGER']/Sal)),min(//Emp[Job='MANAGER']/Sal),max( //Emp[Job='MANAGER']/Sal))

XML

最好将 XPath 表达式分解为员工子集

let $managers := //Emp[Job='MANAGER']
return (count($managers),round(avg($managers/Sal)),min($managers/Sal),max($managers/Sal))

XML

最好为计算的各个值添加标签

let $managers := //Emp[Job='MANAGER']
return 
   <Statistics>
     <Count>{count($managers)}</Count>
     <Average>{round(avg($managers/Sal))}</Average>
     <Min>{min($managers/Sal)}</Min>
     <Max>{max($managers/Sal)}</Max>
   </Statistics>

XML

分组

[编辑 | 编辑源代码]

任务：显示每个职位的员工数量、平均工资（四舍五入）、最低工资和最高工资。

SQL：SELECT job, count(*), round(avg(sal)), min(sal), max(sal) FROM emp GROUP BY job; MySQL


在 XQuery 中，分组必须通过遍历各组来完成。每个组都由职位标识，我们可以使用 distinct-values 函数获取所有职位的集合（序列）

for $job in distinct-values(//Emp/Job)
let $employees  := //Emp[Job=$job]
return 
   <Statistics>
         <Job>{$job}</Job>
         <Count>{count($employees )}</Count>
         <Average>{round(avg($employees/Sal))}</Average>
          <Min>{min($employees/Sal)}</Min>
          <Max>{max($employees/Sal)}</Max>
     </Statistics>

XML 表格

层次结构报告

[编辑 | 编辑源代码]

任务：列出部门、部门员工姓名和工资，以及每个部门的总工资。

这将生成嵌套表格。

SQL： ?

XQuery

<Report>
  {
  for $dept in //Dept
  let $subtotal := sum(//Emp[DeptNo = $dept/DeptNo]/Sal)
  return
     <Department>
        {$dept/Dname}
        {for $emp in //Emp[DeptNo = $dept/DeptNo]
         return
            <Emp>
              {$emp/Ename}
              {$emp/Sal}
            </Emp>
         }
         <SubTotal>{$subtotal}</SubTotal>
      </Department>
   }
   <Total>{sum(//Emp/Sal)}</Total>
</Report>

XML


请注意，XQuery 语言的函数特性意味着每个总计必须显式计算，而不是像在命令式语言中那样累积计算。这样一来，公式就明确且独立，因此可以放在报告中的任何位置，例如放在开头而不是结尾。

<Report>
  <Total>{sum(//Emp/Sal)}</Total>
  {
  for $dept in //Dept
  let $subtotal := sum(//Emp[DeptNo = $dept/DeptNo]/Sal)
  return
     <Department>
         <SubTotal>{$subtotal}</SubTotal>
        {$dept/Dname}
        {for $emp in //Emp[DeptNo = $dept/DeptNo]
         return
            <Emp>
              {$emp/Ename}
              {$emp/Sal}
            </Emp>
         }
      </Department>
   }
 </Report>

XML

受限组

[编辑 | 编辑源代码]

任务：显示每个职位的员工数量、平均工资（四舍五入）、最低工资和最高工资，条件是每个组至少要有 2 名员工。

SQL

SELECT job, count(*), round(avg(sal)), min(sal), max(sal)
  FROM emp
 GROUP BY job
HAVING count(*) > 1;

MySQL

XQuery

for $job in distinct-values(//Emp/Job)
let $employees := //Emp[Job=$job]
where count($employees) > 1
return 
   <Statistics>
      <Job>{$job}</Job>
      <Count>{count($employees )}</Count>
      <Average>{round(avg($employees /Sal))}</Average>
      <Min>{min($employees /Sal)}</Min>
      <Max>{max($employees /Sal)}</Max>
   </Statistics>

XML 表格

日期处理

[编辑 | 编辑源代码]

按日期选择

[编辑 | 编辑源代码]

任务：列出所有在本世纪雇用的员工。

SQL：SELECT * from job where hiredate >= '2000-01-01' MySQL

XQuery：//Emp[HireDate >= '2000-01-01']

实际上，由于缺乏定义 HireDate 为 xs:date 的模式，因此此比较是一个字符串比较。

XML 表格

检索自 "https://wikibooks.cn/w/index.php?title=XQuery/XQuery_from_SQL&oldid=3826847"
华夏公益教科书