「SQL」修訂間的差異

出自ProgWiki
跳至導覽 跳至搜尋
 
(未顯示同一使用者於中間所作的 11 次修訂)
行 1: 行 1:
'''SQL'''(Structured Query Language),{{wikipedia|SQL|lang=en}}
+
'''SQL'''(Structured Query Language)
 +
*{{wikipedia|SQL|lang=en}}
 +
*{{wikibooks|Structured_Query_Language|lang=en}}
 +
*{{wikiversity|SQL|lang=en}}
 +
 
 +
==技術文件==
 +
*ISO/IEC 9075 - ''Information technology — Database languages — SQL''
 +
*[https://www.w3schools.com/sql/ SQL Tutorial - W3Schools]
 +
*[https://troels.arvin.dk/db/rdbms/ Comparison of different SQL implementations]
  
 
==SQL的版本==
 
==SQL的版本==
 
;標準
 
;標準
 +
*SQL-86
 +
*SQL-89
 
*{{wikipedia|SQL-92|lang=en}}
 
*{{wikipedia|SQL-92|lang=en}}
 
*{{wikipedia|SQL:1999|lang=en}}
 
*{{wikipedia|SQL:1999|lang=en}}
行 8: 行 18:
 
*{{wikipedia|SQL:2008|lang=en}}
 
*{{wikipedia|SQL:2008|lang=en}}
 
*{{wikipedia|SQL:2011|lang=en}}
 
*{{wikipedia|SQL:2011|lang=en}}
 +
*{{wikipedia|SQL:2016|lang=en}}
 +
*SQL:2019
  
 
;主要分支版本
 
;主要分支版本
 
*T-SQL,{{wikipedia|Transact-SQL|lang=en}}
 
*T-SQL,{{wikipedia|Transact-SQL|lang=en}}
 +
*:用於:[[MS-SQL|Microsoft SQL Server]]
 
*PL/SQL,{{wikipedia|PL/SQL|lang=en}}
 
*PL/SQL,{{wikipedia|PL/SQL|lang=en}}
 +
*:用於:[[Oracle]]
 
*SQL/PSM,{{wikipedia|SQL/PSM|lang=en}}
 
*SQL/PSM,{{wikipedia|SQL/PSM|lang=en}}
 +
*:用於:[[MySQL]],[[MariaDB]]
 +
*PL/pgSQL,{{wikipedia|PL/pgSQL|lang=en}}
 +
*:用於:[[PostgreSQL]]
  
 
==SQL基本語法==
 
==SQL基本語法==
行 174: 行 191:
  
 
===Oracle===
 
===Oracle===
;使用ROWNUM做分頁
+
====Oracle Type A====
 +
*使用ROWNUM做分頁
 +
*參照:[https://blogs.oracle.com/oraclemagazine/post/on-top-n-and-pagination-queries On Top-n and Pagination Queries]
 
<source lang="oracle8">
 
<source lang="oracle8">
 
SELECT * FROM (
 
SELECT * FROM (
行 184: 行 203:
 
WHERE  ROWNUM >= [每頁長度*(第幾頁-1)+1]
 
WHERE  ROWNUM >= [每頁長度*(第幾頁-1)+1]
 
     AND ROWNUM <= [每頁長度*第幾頁]
 
     AND ROWNUM <= [每頁長度*第幾頁]
 +
</source>
 +
====Oracle Type B====
 +
*使用OFFSET與FETCH NEXT做分頁,據說是Oracle 12c開始可以用的寫法? 
 +
*參照:[https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljoffsetfetch.html The result offset and fetch first clauses]
 +
<source lang="oracle8">
 +
SELECT [欄位1, 欄位2, ...]
 +
    FROM [資料表]
 +
    WHERE [篩選條件]
 +
    ORDER BY [排序欄位] [ASC/DESC]
 +
    OFFSET [每頁長度*(第幾頁-1)] ROWS FETCH NEXT [每頁長度] ROWS ONLY;
 
</source>
 
</source>
  
* 參照:[http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html On Top-n and Pagination Queries]
+
==關聯式查詢==
 
+
*模式1.資料表名稱都寫在from,關聯條件都寫在where
==Join關係==
+
*模式2.join,關聯條件寫在各自的on (join關係見下圖)
 +
*:圖檔出處:[https://www.facebook.com/photo.php?fbid=645876362117204&set=a.199337396771105.43932.199182533453258&type=1&theater Facebook(MSDN 台灣)]
 
[[File:SQL-Joins.png|600px]]
 
[[File:SQL-Joins.png|600px]]
*圖檔出處:[https://www.facebook.com/photo.php?fbid=645876362117204&set=a.199337396771105.43932.199182533453258&type=1&theater Facebook(MSDN 台灣)]
+
*模式3.子查詢 (可用於查詢的欄位或傳給篩選條件)
 +
*:[https://docs.microsoft.com/zh-tw/sql/relational-databases/performance/subqueries?view=sql-server-ver15 子查詢 (SQL Server) - SQL Server | Microsoft Docs ]
  
 
==相關==
 
==相關==
行 212: 行 243:
 
* [http://www.ispirer.com/products SQLWays]
 
* [http://www.ispirer.com/products SQLWays]
  
 +
===在實際運用上,到底該直接用SQL?還是用EF(LINQ)?或是用SQL預存程序?===
 +
*[https://blog.darkthread.net/blog/linq-or-direct-sql/ 閒聊:用 LINQ 還是自己寫 SQL?-黑暗執行緒]
  
 
[[Category:SQL]]
 
[[Category:SQL]]
 
[[Category:資料分頁]]
 
[[Category:資料分頁]]

於 2022年1月12日 (三) 23:12 的最新修訂

SQL(Structured Query Language)

技術文件

SQL的版本

標準
主要分支版本

SQL基本語法

查詢(Select)

基本模式
SELECT [欄位1, 欄位2, ...] 
    FROM [資料表]
    WHERE [篩選條件] 
    ORDER BY [排序欄位] [ASC/DESC]
相同欄位的多個查詢結果串接(也可使用不同來源的資料表)
SELECT [欄位1, 欄位2, ...] 
    FROM [資料表1]
    WHERE [篩選條件1] 
UNION
SELECT [欄位1, 欄位2, ...] 
    FROM [資料表2]
    WHERE [篩選條件2]

插入(Insert)

基本模式
INSERT INTO [資料表] ([欄位1, 欄位2, ...])
    VALUES ([欄位值1, 欄位值2, ...])
插入多筆資料(SQL Server 2008以上, 或是MySQL等才支援)
INSERT INTO [資料表] VALUES 
([欄位值1, 欄位值2, ...])
,([欄位值1, 欄位值2, ...])
,([欄位值1, 欄位值2, ...])
從另一個資料表取資料,插入到另一個資料表之中
INSERT INTO [資料表] ([欄位1, 欄位2, ...])
    SELECT ([欄位1, 欄位2, ...]) FROM [來源資料表] WHERE [篩選條件]

更新(Update)

基本模式
UPDATE [資料表] SET [欄位1 = 欄位值1, 欄位2 = 欄位值2 ...]
    WHERE [篩選條件]
從別的資料表中取出資料,更新到資料表
UPDATE [資料表]
	SET [欄位1=a.欄位1, 欄位2=a.欄位2 ....]
	FROM  [來源資料表] AS a
        WHERE [篩選條件]

刪除(Delete)

基本模式
DELETE FROM [資料表] WHERE [篩選條件]

SQL分頁select

分頁前SQL

SELECT [欄位1, 欄位2, ...] 
    FROM [資料表]
    WHERE [篩選條件] 
    ORDER BY [排序欄位] [ASC/DESC]

MS-SQL

MS-SQL Type A

  • 無排序,在關聯式查詢時,可能導致每次查詢同一分頁的資料時,出來的資料都不一樣。
SELECT [欄位1, 欄位2, ...] FROM [資料表]
    WHERE [主要索引欄位] IN
    (
        SELECT * FROM 
        (
            SELECT TOP [每頁長度*第幾頁] [主要索引欄位]
                FROM [資料表]
                WHERE [篩選條件]     
        )
        as [資料表别名1]
        WHERE [主要索引欄位] NOT IN
        (
            SELECT TOP [每頁長度] [主要索引欄位] 
                FROM [資料表]
                WHERE [篩選條件] 
        )
    )

MS-SQL Type B

  • 有排序,又稱【夾擠式分頁查詢】。MS-SQL 2000以前,常見的分頁用語法。
SELECT * FROM 
(
    SELECT TOP [每頁長度] * FROM 
    (
        SELECT TOP [每頁長度*第幾頁] [欄位1, 欄位2, ...] 
        FROM [資料表]
        WHERE [篩選條件]     
        ORDER BY [排序欄位] [DESC/ASC]
    )
    as [資料表别名1]
    ORDER BY [排序欄位] [ASC/DESC]
)
as [資料表别名2] 
ORDER BY [排序欄位] [DESC/ASC]

MS-SQL Type C

  • MS-SQL 2005以後才有RANK()語法。需特別注意,當RANK()裡的排序條件只有1個欄位,且此欄位的值都一樣時,會導致RankNumber都會變成1,導致分頁失效,需在排序條件中加上一個有唯一值的欄位。
SELECT * FROM 
(
    SELECT rank() over ( ORDER BY [排序欄位] [ASC/DESC] ) as RankNumber, * FROM
    (
        SELECT [欄位1, 欄位2, ...] 
        FROM [資料表]
        WHERE [篩選條件]     
    )
    as [資料表别名1]
) as [資料表别名2]
WHERE RankNumber between [每頁長度*(第幾頁-1)] and [每頁長度*第幾頁]

MS-SQL Type D

SELECT IDENTITY(int,1,1) as RankNumber, [欄位1, 欄位2, ...] 
INTO [#暫存資料表名稱] 
FROM [資料表]
ORDER BY [排序欄位] [ASC/DESC];
 
SELECT * FROM [#暫存資料表名稱]
WHERE RankNumber between [每頁長度*(第幾頁-1)] and [每頁長度*第幾頁];
 
drop table [#暫存資料表名稱];

其他相關

(以SQL預存程序實作SQL-Sever的北風資料庫的分頁範例)

MySQL

SELECT [欄位1, 欄位2, ...] 
        FROM [資料表]
        WHERE [篩選條件]     
        ORDER BY [排序欄位] [ASC/DESC]
        LIMIT [每頁長度] OFFSET [每頁長度*第幾頁]


Oracle

Oracle Type A

SELECT * FROM (
        SELECT [欄位1, 欄位2, ...] 
                FROM [資料表]
                WHERE [篩選條件]
                ORDER BY [排序欄位] [ASC/DESC]
)
WHERE   ROWNUM >= [每頁長度*(第幾頁-1)+1]
    AND ROWNUM <= [每頁長度*第幾頁]

Oracle Type B

SELECT [欄位1, 欄位2, ...] 
    FROM [資料表]
    WHERE [篩選條件]
    ORDER BY [排序欄位] [ASC/DESC]
    OFFSET [每頁長度*(第幾頁-1)] ROWS FETCH NEXT [每頁長度] ROWS ONLY;

關聯式查詢

  • 模式1.資料表名稱都寫在from,關聯條件都寫在where
  • 模式2.join,關聯條件寫在各自的on (join關係見下圖)
    圖檔出處:Facebook(MSDN 台灣)

SQL-Joins.png

相關

Connection Strings

主條目:ConnectionString

SQL資料隱碼

主條目:SQL資料隱碼

FAQ

當Table或欄位的名稱衝到SQL預設的保留字時

  • MS-SQL 或 MS-Access 使用 [] 來將衝突的Table或欄位的名稱包起來。
  • Oracle 或 IBM-DB2 或 PostgreSQL 使用雙引號字元 " 來將衝突的Table或欄位的名稱包起來。
  • MySQL 使用反單引號字元 ` 來將衝突的Table或欄位的名稱包起來。

跨資料庫轉換

相關軟體

在實際運用上,到底該直接用SQL?還是用EF(LINQ)?或是用SQL預存程序?