SQL
SQL(i/ˈɛs kjuː ˈɛl/[5]或
i/ˈsiːkwəl/[6],Structured Query Language,結構化查詢語言[7][8][9][10])是一種特定目的程式語言,用於管理關聯式資料庫管理系統(RDBMS),或在關係流數據管理系統(RDSMS)中進行流處理。
編程範型 | 聲明式 |
---|---|
設計者 | 唐納德·錢柏林 雷蒙德·博伊斯 |
實作者 | 國際標準化組織/國際電工委員會 |
釋出時間 | 1974 |
目前版本 |
|
型態系統 | 類型系統, 強型別 |
作業系統 | 跨平台 |
網站 | www |
主要實作產品 | |
詳見資料庫系統列表 | |
衍生副語言 | |
SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2006, SQL:2008, SQL:2011 | |
啟發語言 | |
Datalog | |
影響語言 | |
CQL, 語言整合查詢, SOQL, Windows PowerShell,[2] JPQL, 面向Java對象查詢 | |
副檔名 |
.sql |
---|---|
網路媒體型式 | |
開發者 | ISO/IEC |
首次發佈 | 1986年 |
最新版本 | SQL:2016 2016年 |
格式類型 | Database |
標準 | ISO/IEC 9075 |
免費格式? | 是 |
網站 | www |
SQL基於關係代數和元組關係演算,包括一個數據定義語言和數據操縱語言。SQL的範圍包括數據插入、查詢、更新和刪除,資料庫模式建立和修改,以及數據訪問控制。儘管SQL經常被描述為,而且很大程度上是一種聲明式編程(4GL),但是其也含有程序式編程的元素。
SQL是對埃德加·科德的關係模型的第一個商業化語言實現,這一模型在其1970年的一篇具有影響力的論文《一個對於大型共用型資料庫的關係模型》[11]中被描述。儘管SQL並非完全按照科德的關係模型設計,但其依然成為最為廣泛運用的資料庫語言。 [12][13]
SQL在1986年成為美國國家標準學會(ANSI)的一項標準,在1987年成為國際標準化組織(ISO)標準[14]。此後,這一標準經過了一系列的增訂,加入了大量新特性。雖然有這一標準的存在,但大部分的SQL代碼在不同的資料庫系統中並不具有完全的跨平台性。
歷史
編輯在1970年代初,由IBM研究院下屬愛曼登研究中心的埃德加·科德發表將資料組成表格的應用原則(Codd's Relational Algebra)。1974年,同一實驗室的唐納德·錢柏林和雷蒙德·博伊斯參考了科德的模型後,在研製關聯式資料庫管理系統System R中,開發出了一套規範語言SEQUEL(Structured English Query Language,結構化英語查詢語言),並在1976年11月的《IBM研究與開發雜誌》上公佈新版本的SQL(叫SEQUEL/2)。1980年改名為SQL。
1979年,甲骨文公司(當時名為關係式軟件公司)首先提供商用的SQL,IBM公司在DB2和SQL/DS資料庫系統中也實現了SQL。
1986年10月,美國ANSI採用SQL作為關聯式資料庫管理系統的標準語言(ANSI X3. 135-1986),後為國際標準化組織(ISO)採納為國際標準。
1989年,美國ANSI採納在ANSI X3.135-1989報告中定義的關聯式資料庫管理系統的SQL標準語言,稱為ANSI SQL 89,該標準替代ANSI X3.135-1986版本。該標準為下列組織所採納:
- 國際標準化組織,為ISO 9075-1989報告《Database Language SQL With Integrity Enhancement》
- 美國聯邦政府,發佈在《The Federal Information Processing Standard Publication(FIPS PUB)127》
目前,所有主要的關聯式資料庫管理系統支援某些形式的SQL,大部分資料庫至少遵守ANSI SQL89標準。
ANSI SQL92標準在交叉連接(cross join)和內部連接之上,新增加了外部連接,並支援在FROM子句中寫連接表達式。支援集合的並運算、交運算。支援Case (SQL)表達式。支援CHECK約束。建立臨時表。支援cursor。支援事務隔離。
語法
編輯SQL語言分成了幾種要素,包括:
- 子句,是陳述式和查詢的組成成分。(在某些情況下,這些都是可選的。)[15]
- 表達式,可以產生任何純量值,或由列和行組成的資料庫表
- 謂詞,給需要評估的SQL三值邏輯(3VL)(true/false/unknown)或布林真值指定條件,並限制陳述式和查詢的效果,或改變程式流程。
- 查詢,基於特定條件檢索數據。這是SQL的一個重要組成部分。
- 陳述式,可以持久地影響綱要和數據,也可以控制資料庫事務、程式流程、連接、對談或診斷。
- SQL陳述式也包括分號(";")陳述式終結符。儘管並不是每個平台都必需,但它是作為SQL語法的標準部分定義的。
- 無意義的空白在SQL陳述式和查詢中一般會被忽略,更容易格式化SQL代碼便於閱讀。
語言特點
編輯SQL是進階的非過程化編程語言,它允許用戶在高層數據結構上工作。它不要求用戶指定對數據的存放方法,也不需要用戶了解其具體的數據存放方式。而它的介面,能使具有底層結構完全不同的資料庫系統和不同資料庫之間,使用相同的SQL作為數據的輸入與管理。它以記錄專案〔records〕的合集(set)〔項集,record set〕作為操縱對象,所有SQL陳述式接受項集作為輸入,回送出的項集作為輸出,這種項集特性允許一條SQL陳述式的輸出作為另一條SQL陳述式的輸入,所以SQL陳述式可以巢狀,這使它擁有極大的靈活性和強大的功能。在多數情況下,在其他程式語言中需要用一大段程式才可實踐的一個單獨事件,而其在SQL上只需要一個陳述式就可以被表達出來。這也意味着用SQL可以寫出非常複雜的陳述式,在不特別考慮效能下。
SQL包含四個部分:
SQL函數
編輯函數 | 描述 |
---|---|
AVG | 平均值 |
COUNT | 計數(不含Null) |
FIRST | 第一個記錄的值 |
MAX | 最大值 |
MIN | 最小值 |
STDEV | 樣本標準差 |
STDEVP | 總體標準差 |
SUM | 求和 |
VAR | 樣本方差 |
VARP | 總體方差 |
UCASE | 轉化為全大寫字母 |
LCASE | 轉化為全小寫字母 |
MID | 取中值 |
LEN | 計算字串長度 |
INSTR | 獲得子字串在母字串的起始位置 |
LEFT | 取字串左邊子串 |
RIGHT | 取字串右邊子串 |
ROUND | 數值四捨五入取整 |
MOD | 取餘 |
NOW | 獲得當前時間的值 |
FORMAT | 字串格式化 |
DATEDIFF | 獲得兩個時間的差值 |
互操作性和標準化
編輯供應商之間的SQL實現不相容,不一定完全遵循標準。各種資料庫的SQL方言通常不可移植,特別是在日期時間語法、字串連接、NULL
、比較的大小寫敏感方面。只有PostgreSQL與Mimer SQL努力遵從標準。
標準化歷史
編輯SQL在1986年被ANSI標準化,1987年被ISO標準化。由ISO/IEC JTC 1, Information technology, Subcommittee SC 32, Data management and interchange維護。標準名稱通常為如下模式:ISO/IEC 9075-n:yyyy Part n: title。
年份 | 名字 | 別名 | 註釋 |
---|---|---|---|
1986 | SQL-86 | SQL-87 | ANSI首次標準化 |
1989 | SQL-89 | FIPS 127-1 | 小修改,增加了integrity constraint |
1992 | SQL-92 | SQL2, FIPS 127-2 | 大修改,成為現代SQL的基礎 |
1999 | SQL:1999 | SQL3 | 增加了正則表達式匹配、遞歸查詢(傳遞閉包)、資料庫觸發器、程序式與控制流陳述式、非純量類型(arrays)、物件導向特性。在Java中嵌入SQL(SQL/OLB)及其逆(SQL/JRT) |
2003 | SQL:2003 | 增加XML相關特性(SQL/XML)、window functions、標準化sequences、自動產生值的列。對SQL:1999的新特性重新描述其內涵。 | |
2006 | SQL:2006 | 匯入/匯出XML數據與SQL資料庫。XQuery | |
2008 | SQL:2008 | 在cursor之外的ORDER BY陳述式。INSTEAD OF觸發器。TRUNCATE陳述式。FETCH子句 | |
2011 | SQL:2011 | 增加時態數據(PERIOD FOR)。增強了window functions與FETCH子句 | |
2016 | SQL:2016 | 增加行模式匹配、多型表函數、JSON。 | |
2019 | SQL:2019 | 增加了第15部分,多維陣列(MDarray類型和運算子)。 |
當前標準
編輯該標準通常用以下模式表示:ISO/IEC 9075-n:yyyy 第n部分:標題,或者簡稱為ISO/IEC 9075。
ISO / IEC 9075補充了ISO / IEC 13249:SQL多媒體和應用程式包(SQL/MM),該程式包定義了基於SQL的介面和包,給諸如影片,音頻和空間數據之類的廣泛的應用程式。感興趣的各方可以從ISO、IEC或ANSI購買SQL標準文件。SQL:2008的草稿可作為zip存檔免費獲得。
SQL標準剖析
編輯SQL標準包含10部分:
- ISO/IEC 9075-1:2016 Part 1: Framework (SQL/Framework). 提供邏輯概念
- ISO/IEC 9075-2:2016 Part 2: Foundation (SQL/Foundation). 包含語言的主要內容,強制與可選特性。
- ISO/IEC 9075-3:2016 Part 3: Call-Level Interface (SQL/CLI). 定義了介面成分(structures, procedures, variable bindings) 用於編寫能執行SQL的應用程式的語言:Ada,C/C++,COBOL,Fortran,MUMPS,Pascal,PL/I。對於Java語言見標準第10部分。ODBC是一個著名的SQL/CLI的超集。這部分標準主要包含強制的特性。
- ISO/IEC 9075-4:2016 Part 4: Persistent stored modules (SQL/PSM)。SQL的程序式擴充,包括控制流、條件處理、陳述式條件signals與resignals、cursors、本地變數、表達式賦值到變數與參數。此外,SQL/PSM形式化聲明與維護了持續性(persistent)資料庫語言常式(例如儲存程序)。這部分標準主要包含可選的特性。
- Part-6(頁面存檔備份,存於互聯網檔案館): Support for JavaScript Object Notation (JSON). 2017年首次整合JSON資料類型到SQL標準。
- ISO/IEC 9075-9:2016 Part 9: Management of External Data (SQL/MED). 這部分標準主要包含可選的特性。
- ISO/IEC 9075-10:2016 Part 10: Object language bindings (SQL/OLB)。定義了SQLJ,SQL嵌入到Java,保證了SQLJ應用程式二進制可移植。這部分標準主要包含可選的特性。
- ISO/IEC 9075-11:2016 Part 11: Information and definition schemas (SQL/Schemata). 定義了Information Schema與Definition Schema,提供了常用工具集使得SQL資料庫與對象自描述。這些工具包括SQL object identifier、structure與integrity constraints、security與authorization specifications, features與packages。這部分標準主要包含強制與可選的特性。
- ISO/IEC 9075-13:2016 Part 13: SQL Routines and types using the Java TM programming language (SQL/JRT). SQL應用程式呼叫靜態方法作為子程式的能力('Java-in-the-database');Java類別作為SQL結構化用戶定義類型。這部分標準主要包含可選的特性。
- ISO/IEC 9075-14:2016 Part 14: XML-Related Specifications (SQL/XML). 這部分標準主要包含可選的特性。
- ISO/IEC 9075-15:2019 Part 15: 多維陣列(SQL/MDA)。 它為SQL指定了多維陣列類型(MDarray),以及對MDarray,MDarray切片,MDarray單元和相關功能的操作。 標準的這一部分僅包含可選功能。
ISO / IEC標準的擴充
編輯ISO/IEC 9075被ISO/IEC 13249: SQL Multimedia and Application Packages (SQL/MM)補充。後者定義了基於SQL的音影片、空間數據的介面與包。包括:
- ISO/IEC 13249-1:2016 Part 1: Framework
- ISO/IEC 13249-2:2003 Part 2: Full-Text
- ISO/IEC 13249-3:2016 Part 3: Spatial
- ISO/IEC 13249-5:2003 Part 5: Still image
- ISO/IEC 13249-6:2006 Part 6: Data mining
- ISO/IEC 13249-7:2013 Part 7: History
- ISO/IEC 13249-8:xxxx Part 8: Metadata Registry Access MRA (work in progress)
以SQL為基礎的其他延伸語言
編輯- 微軟MS SQL-Server,以及Sybase Adaptive Server系列資料庫所用的SQL
安全問題
編輯由於SQL指令在部份進階使用時,語法會依照特定條件來變換,而且若是表格中的欄位過多時,許多開發人員都會習慣以字串組立的方式建立SQL指令,而且又使用系統管理員級的帳戶連到資料庫,因此讓黑客有機會利用SQL的組立方式進行攻擊,像是在指令中添加部份刺探性或破壞性的指令(例如DROP TABLE
、DROP DATABASE
或是DELETE * FROM myTable
等具破壞性的指令),讓資料庫的資料或實體伺服器被破壞,導致服務中斷或是系統癱瘓等後果,或是以邏輯的漏洞,在密碼欄寫入特殊字串(例如' or '1'='1),讓該字串形成的邏輯判斷永遠為真,或直接取得非查詢條件的資料,而不需要知道密碼,以達成非法登入系統的目的,此種攻擊手法稱為SQL注入(SQL injection)。
目前實務上較有效的防禦方法,就是全面改用參數化查詢。
參考文獻
編輯- ^ SQL:2023 is out. 2023年6月1日 [2023年6月26日].
- ^ Paul, Ryan. A guided tour of the Microsoft Command Shell. Ars Technica. [10 April 2011]. (原始內容存檔於2018-12-25).
- ^ Media Type registration for application/sql. Internet Assigned Numbers Authority. 10 April 2013 [10 April 2013]. (原始內容存檔於2018-01-24).
- ^ The application/sql Media Type, RFC 6922. Internet Engineering Task Force: 3. April 2013 [10 April 2013]. (原始內容存檔於2016-10-18).
- ^ Beaulieu, Alan. Mary E Treseler , 編. Learning SQL 2nd. Sebastapol, CA, USA: O'Reilly. April 2009. ISBN 978-0-596-52083-0.
- ^ SQL, n.. Oxford English Dictionary. Oxford University Press. [2014-11-27].
- ^ Encyclopedia Britannica. SQL. [2013-04-02]. (原始內容存檔於2018-12-25).
- ^ From Oxford Dictionaries: "Definition of SQL - abbreviation, Structured Query Language, an international standard for database manipulation." Oxford Dictionaries. SQL. [2017-10-10]. (原始內容存檔於2016-08-03).
- ^ IBM. SQL Guide.[永久失效連結]
- ^ From Microsoft: "Structured Query Language, invented at IBM in the 1970s. It is more commonly known by its acronym, SQL .." Microsoft. Structured Query Language (SQL). [2017-10-10]. (原始內容存檔於2016-03-12).
- ^ Codd, Edgar F. A Relational Model of Data for Large Shared Data Banks. Communications of the ACM (Association for Computing Machinery). June 1970, 13 (6): 377–87 [2007-06-09]. doi:10.1145/362384.362685. (原始內容存檔於2007-06-12).
- ^ Chapple, Mike. SQL Fundamentals. Databases. About.com. [2009-01-28]. (原始內容存檔於2009-02-22).
- ^ Structured Query Language (SQL). International Business Machines. October 27, 2006 [2007-06-10].
- ^ ISO/IEC 9075-1:2008: Information technology – Database languages – SQL – Part 1: Framework (SQL/Framework). [2017-10-10]. (原始內容存檔於2012-02-03).
- ^ ANSI/ISO/IEC International Standard (IS). Database Language SQL—Part 2: Foundation (SQL/Foundation). 1999.
參見
編輯外部連結
編輯