查詢語言

计算机语言用于查询到数据库和信息系统

查询语言数据查询语言(Data Query Language, DQL)是用于从数据库信息系统中查询数据计算机语言。例如SQL语言是查询语言里比较知名的一种。

查詢語言範例编辑

数据查询语言编辑

數據查詢語言泛指向資料庫信息系统查詢的各種编程语言。数据查询语言必须要能表达所有关系代数所能表达的查询,这样才被称为关系完整的(英語:Relational complete)。[1]

DQL的主要功能是查询数据,本身核心指令为SELECT,为了进行精细的查询,加入了各类辅助指令。SELECT是查询的指令,例如:

SELECT p.Name, sod.SalesOrderID
FROM Production.Product p
INNER JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID
ORDER BY p.Name

扁平化数据分析查询语言编辑

Flat-QL是一种类SQL 查询语言,但其对使用者屏蔽了关系代数,以及复杂的结构,使得数据分析更容易与各种编程语言进行结合,虽然它不能称为关系完整的的数据查询语言,但它适用于绝大多数数据分析场景,能够有效提高编程效率。

-- 扁平化查询语言示例
SELECT Store.city                   AS "City",
       COUNT(Product.name)          AS "Count Of Product",
       SUM(Sales.sales)             AS "Total Sales",
       AVG(Sales.quantityPurchased) AS "Average Of Quantity Purchased"
FROM sales
WHERE Store.city IN ('New York', 'Chicago')
ORDER BY "City" LIMIT 10 OFFSET 1
-- 编译后的SQL
SELECT "qu_0"."co_1" AS "City",
       "qu_0"."co_3" AS "Count Of Product",
       "qu_1"."co_7" AS "Total Sales",
       "qu_1"."co_8" AS "Average Of Quantity Purchased"
FROM (SELECT "qu_2"."co_1" AS "co_1", SUM("qu_3"."co_2") AS "co_3"
      FROM (SELECT "ta_0"."id" AS "co_0", "ta_1"."city" AS "co_1"
            FROM "db"."sales" AS "ta_2"
                     INNER JOIN "db"."products" AS "ta_0" ON "ta_0"."id" = "ta_2"."productId"
                     INNER JOIN "db"."stores" AS "ta_1" ON "ta_1"."id" = "ta_2"."storeId"
            WHERE "ta_1"."city" IN ('New York', 'Chicago')
            GROUP BY "co_0", "co_1") AS "qu_2"
               LEFT JOIN (SELECT "ta_0"."id" AS "co_0", COUNT("ta_0"."name") AS "co_2"
                          FROM "db"."products" AS "ta_0"
                          GROUP BY "co_0") AS "qu_3" ON "qu_2"."co_0" = "qu_3"."co_0"
      GROUP BY "co_1") AS "qu_0"
         INNER JOIN (SELECT "qu_4"."co_1" AS "co_1", SUM("qu_5"."co_5") AS "co_7", AVG("qu_5"."co_6") AS "co_8"
                     FROM (SELECT "ta_2"."id" AS "co_4", "ta_1"."city" AS "co_1"
                           FROM "db"."sales" AS "ta_2"
                                    INNER JOIN "db"."stores" AS "ta_1" ON "ta_1"."id" = "ta_2"."storeId"
                           WHERE "ta_1"."city" IN ('New York', 'Chicago')
                           GROUP BY "co_4", "co_1") AS "qu_4"
                              LEFT JOIN (SELECT "ta_2"."id"                     AS "co_4",
                                                SUM("ta_2"."sales")             AS "co_5",
                                                AVG("ta_2"."quantityPurchased") AS "co_6"
                                         FROM "db"."sales" AS "ta_2"
                                         GROUP BY "co_4") AS "qu_5" ON "qu_4"."co_4" = "qu_5"."co_4"
                     GROUP BY "co_1") AS "qu_1" ON "qu_0"."co_1" = "qu_1"."co_1"
ORDER BY "City" ASC LIMIT 10 OFFSET 1

引用编辑

  1. ^ Carlo Zaniolo. Advanced Database Systems. Morgan Kaufmann Publishers. 1997: 183. ISBN 1-55860-443-X. 

參考資料编辑

  1. Flat-QL 项目网站
  2. 完整的SQL中文參考網站
  3. MySQL SQL Syntax页面存档备份,存于互联网档案馆
  4. Oracle® Database SQL Language Reference[永久失效連結]
  5. Transact-SQL Reference页面存档备份,存于互联网档案馆
  6. PostgreSQL SQL Commands页面存档备份,存于互联网档案馆
  7. 一种新的查询语言:OttoQL Reference