XQuery/XQuery 從 SQL

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 名員工

SQL: select * from emp limit 5; MySQL

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

XML 表格

選擇列

列出員工姓名和薪資

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 元素。

計算值

計算年薪

任務: 計算所有員工的年薪。年薪按月薪的 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 相對應。

選擇和建立列

任務: 列出員工姓名及其年薪。

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 運算子

IN

任務: 顯示所有工作職位為 ANALYST 或 MANAGER 的員工。

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

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

XML 表格

NOT IN

任務: 選擇所有工作職位不為 '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 表格

不同的值

任務: 顯示員工擔任的不同職位。

MySQL: select distinct job from emp; MySQL

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

模式匹配

任務: 列出所有姓名以 "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' 使正則表示式匹配不區分大小寫。）

表連線

簡單的內連線

任務: 查詢員工 '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 表格