Hannibal@sql-bod-idv-tw SQL語法教學及範例

2021-01-26

SQL : INSERT INTO

SQL : INSERT INTO 將資料插到資料表內

目標：

使用CREATE TABLE ... AS SELECT ...，複製資料表MediaType的結構及MediaTypeId=1的這筆資料，到資料表MediaType2。
使用 INSERT INTO table (field1, field2, ...) VALUES (value1, value2, ...); 插入1筆資料。
使用 INSERT INTO table VALUES (value1, value2, ...); 插入2筆資料。
使用 INSERT INTO table (field1, field2, ...) SELECT fielda, fieldb, ... FROM table_a WHERE ...; 插入1筆資料。

作法：

測試環境的資料庫，可以參閱以下網址連結來建立：

取得SQLite版本的Chinook範例資料庫 https://sql.bod.idv.tw/2021/01/sqlitechinook.html
用SQLiteStudio建立SQL學習環境 https://sql.bod.idv.tw/2021/01/sqlitestudiosql.html

選取要作業的資料庫對象(TestWind)，開啟(SQL Editor)：Tools → Open SQL Editor

在Query分頁中輸入所要建立測試資料表MediaType2及插入資料的執行指令

CREATE TABLE MediaType2 AS
    SELECT MediaTypeId, Name 
    FROM MediaType
    WHERE MediaTypeId=1;

INSERT INTO MediaType2 (MediaTypeId,Name) 
    VALUES (2,'Protected AAC audio file');

INSERT INTO MediaType2 
    VALUES (3,'Protected MPEG-4 video file');

INSERT INTO MediaType2 
    SELECT MediaTypeId,Name FROM MediaType 
    WHERE MediaTypeId=4 OR MediaTypeId=5;

執行SQL指令：(F9) Execute SQL (選取要執行的部分SQL指令，按F9，會只執行選取的部分)
先執行 CREATE TABLE MediaType2，再執行一次INSERT INTO MediaType2 ...
Status : 確認SQL指令執行無誤
查看執行結果：

SQL : WITH RECURSIVE ... REPLACE INTO ...

SQL : WITH RECURSIVE ... REPLACE INTO ...
INSERT INTO 不會做資料插入前的檢查，發生錯誤就中斷程式。
在程式開發的過程，時常會遇到一個情境：
如果資料不存在，就插入一筆資料；如果資料已經存在，就更新這一筆資料。
INSERT INTO 衍申的語法：REPLACE INTO 剛好可以滿足這個需求，但資料表必須有主鍵PRIMARY KEY，資料會透過所提供的PRIMARY KEY鍵值，來決定要增加一筆資料？還是更新這筆資料？

目標：

延續上一篇：SQL : WITH RECURSIVE ... INSERT INTO ... https://sql.bod.idv.tw/2021/01/sql-with-recursive-insert-into.html ，這完成INSERT INTO 相關的資料後，這這裡繼續做REPLACE INTO的動作。
在 tabWith 已 INSERT INTO : (1,'a'), (2,'aa'), (3,'aaa'), (4,'aaaa'), (5,'aaaaa') 的情況下，要再繼續REPLACE INTO : (4,'b'), (5,'bb'), (6,'bbb'), (7,'bbbb'), (8,'bbbbb')

作法：

測試環境的資料庫，可以參閱以下網址連結來建立：

取得SQLite版本的Chinook範例資料庫 https://sql.bod.idv.tw/2021/01/sqlitechinook.html
用SQLiteStudio建立SQL學習環境 https://sql.bod.idv.tw/2021/01/sqlitestudiosql.html
確認已完成 SQL : WITH RECURSIVE ... INSERT INTO ... https://sql.bod.idv.tw/2021/01/sql-with-recursive-insert-into.html

選取要作業的資料庫對象(TestWind)，開啟(SQL Editor)：Tools → Open SQL Editor

在Query分頁中輸入SQL的執行指令

WITH  RECURSIVE cte AS (
    SELECT 4 AS c1, 'b' AS c2
    UNION ALL
    SELECT c1 + 1, c2 || 'b' 
    FROM cte
    WHERE c1 < 8
) 
REPLACE INTO tabWith (score, grade) SELECT c1, c2 FROM cte;

執行SQL指令：(F9) Execute SQL，Status : 確認SQL指令 WITH RECURSIVE ... REPLACE INTO ... 執行無誤
查看執行結果
score=4, (4,'aaaa') 已更新為 (4, 'b')
score=5, (5,'aaaaa') 已更新為 (5, 'bb')
已插入 (6,'bbb'), (7,'bbbb'), (8,'bbbbb')

SQL : WITH RECURSIVE ... INSERT INTO ...

SQL : WITH RECURSIVE ... INSERT INTO ...

目標：

先建立一個空資料表格tabWith，有兩個資料欄位 score INT PRIMARY KEY, grade NVARCHAR(120)
用WITH RECURSIVE的方式，插入五筆資料：(1,'a'), (2,'aa'), (3,'aaa'), (4,'aaaa'), (5,'aaaaa')

作法：

測試環境的資料庫，可以參閱以下網址連結來建立：

取得SQLite版本的Chinook範例資料庫 https://sql.bod.idv.tw/2021/01/sqlitechinook.html
用SQLiteStudio建立SQL學習環境 https://sql.bod.idv.tw/2021/01/sqlitestudiosql.html

選取要作業的資料庫對象(TestWind)，開啟(SQL Editor)：Tools → Open SQL Editor

在Query分頁中輸入所要建立測試資料表tabWith的執行指令

CREATE TABLE tabWith (
    score   INTEGER        NOT NULL,
    grade   NVARCHAR(120),
    PRIMARY KEY (score)
);

執行SQL指令：(F9) Execute SQL
Status : 確認SQL指令 CREATE TABLE tabWith 執行無誤

用WITH RECURSIVE的方式，插入五筆資料：(1,'a'), (2,'aa'), (3,'aaa'), (4,'aaaa'), (5,'aaaaa')
在Query分頁中輸入WITH RECURSIVE INSERT INTO的SQL指令

WITH  RECURSIVE cte AS (
    SELECT 1 AS c1, 'a' AS c2
    UNION ALL
    SELECT c1 + 1, c2 || 'a' 
    FROM cte
    WHERE c1 < 5
) 
INSERT INTO tabWith (score, grade) SELECT c1, c2 FROM cte;

執行SQL指令：(F9) Execute SQL，Status : 確認SQL指令WITH RECURSIVE INSERT INTO 執行無誤

SQL : INSERT INTO 語法(Syntax)格式

SQL : INSERT INTO 的語法(Syntax)，有三個基本的語法：

將指定的資料值插入資料表格內：

INSERT INTO 資料表名稱 [(資料欄位名稱, ...)] VALUES (資料欄位值, ...);
將SELECT執行結果插入資料表格內：

INSERT INTO 資料表名稱 [(資料欄位名稱, ...)] SELECT執行結果;
以資料表結構定義時所設定的預設值插入資料表格內：

INSERT INTO 資料表名稱 [(資料欄位名稱, ...)]DEFAULT VALUES;

INSERT INTO的語法格式：

[ WITH [ RECURSIVE ] cte-table-name AS (select-stmt), ...]
{INSERT | REPLACE | INSERT OR REPLACE | insert-type ... } INTO
資料表名稱 [AS 別名] [( 資料欄位名稱,...)] { [VALUES (資料值or運算式,) | select-stmt | DEFAULT VALUES}
ON CONFLICT [(索引欄位, ... ) | [WHERE 條件式] ]
DO { NOTHING | UPDATE SET 欄位名稱=運算式, ... [ WHERE 條件式 ]}

cte : common table expression
select-stmt : select statements
insert-type : INSERT, REPLACE, INSERT OR REPLACE, INSERT OR ROLLBACK, INSERT OR ABORT, INSERT OR FAIL, INSERT OR IGNORE
如果主鍵的對應值存在，就REPLACE指定的資料值；如果主鍵的對應值不存在，就插入這筆資料。有效的利用這個語法規則，可以大大的降低程式設計的複雜度。

參考資料：

SQL As Understood By SQLite : INSERT https://sqlite.org/lang_insert.html
PostgreSQL: Documentation: 11: INSERT https://www.postgresql.org/docs/11/sql-insert.html

2021-01-24

SQL : ALTER TABLE

SQL : ALTER TABLE
ALTER TABLE 修改資料表的名稱、資料欄位名稱，或新增資料欄位

ALTER TABLE 的 SQL語法(Syntax)格式：

ALTER TABLE 資料表名稱
{RENAME TO 新資料表名稱 | RENAME [COLUMN] 資料欄位名稱 TO 資料欄位新名稱 | ADD [COLUMN] 資料欄位定義};

ALTER TABLE 可以修改資料表名稱、資料欄位名稱，增加資料欄位等。
SQLite 3.25.0 起，將原先必須使用設定調整的作法(PRAGMA legacy_alter_table = ON 或 sqlite3_db_config() 的 SQLITE_DBCONFIG_LEGACY_ALTER_TABLE選項)才可以啟用ALTER TABLE的功能，改為可以直接使用的指令。
目前最新的SQLite版本 3.34.1(2021.01.20。

以下將說明，修改Emp_Id_Name資料表結構：
資料表名稱：Emp_Id_Name → EmpIdName
資料欄位名稱：EmployeeId → EmpId
增加資料欄位：IDCardNo CHAR(10)

ALTER TABLE SQL指令的使用：

測試環境的資料庫，可以參閱以下網址連結來建立：

取得SQLite版本的Chinook範例資料庫 https://sql.bod.idv.tw/2021/01/sqlitechinook.html
用SQLiteStudio建立SQL學習環境 https://sql.bod.idv.tw/2021/01/sqlitestudiosql.html

選取要作業的資料庫對象(TestWind)，開啟(SQL Editor)：Tools → Open SQL Editor

Emp_Id_Name資料表，可以透過以下指令製造：

CREATE TABLE Emp_Id_Name AS 
  SELECT EmployeeId, LastName, FirstName FROM Employee;

先修改資料表名稱，在Query分頁中輸入所要執行的指令
```
 ALTER TABLE Emp_Id_Name RENAME TO EmpIdName;
```
執行SQL指令：(F9) Execute SQL，Status : 確認SQL指令執行無誤
因目前 SQLite Studio 的版本 v3.2.1，是基於SQLite 3.24.0開發的，如前所述SQLite 3.25.0後，在ALTER TABLE上以加強功能上的實作，所以接下來用sqlite3 3.29.0(或更新的版本)來完成資料欄位的修改新增。
回SQLiteStudio查看一下，確認完成修改
SQL Features That SQLite Does Not Implement (https://sqlite.org/omitted.html)
SQLite雖然已提供幾乎所的功能特性，但並沒有實作標準SQL的每一項功能特性，以ALTER TABLE的功能，僅提供：RENAME TABLE, ADD COLUMN, 及 RENAME COLUMN的支援，DROP COLUMN, ALTER COLUMN, 及 CONSTRAINT則不在功能支援的範圍內。

參考資料：
SQL As Understood By SQLite : ALTER TABLE https://sqlite.org/lang_altertable.html

SQL : DROP TABLE

SQL : DROP TABLE
DROP TABLE 除了會清除資料表內的資料，也會將這個資料表在資料庫中的結構定義資料一併清除，整個資料表都清光光。

DROP TABLE的語法(Syntax)格式：

DROP TABLE [IF EXISTS] 資料表名稱;

可以增加 [IF EXISTS] 檢查，確認資料表存在，再予清除。
DELETE 跟 DROP 不一樣，DELETE只清除資料，不清除資料表的結構定義。

以下在測試資料庫下執行，預計刪除TestWind資料庫下的Emp_Id_Name資料表：

測試環境的資料庫，可以參閱以下網址連結來建立：

取得SQLite版本的Chinook範例資料庫 https://sql.bod.idv.tw/2021/01/sqlitechinook.html
用SQLiteStudio建立SQL學習環境 https://sql.bod.idv.tw/201/01/sqlitestudiosql.html

選取要作業的資料庫對象(TestWind)，開啟(SQL Editor)：Tools → Open SQL Editor

Emp_Id_Name資料表，可以透過以下指令製造：

CREATE TABLE Emp_Id_Name AS 
  SELECT EmployeeId, LastName, FirstName FROM Employee;

在Query分頁中輸入所要執行的指令
```
DROP TABLE Emp_Id_Name;
```
執行SQL指令：(F9) Execute SQL
Status : 確認SQL指令執行無誤

參考資料：
SQL As Understood By SQLite : DROP TABLE https://sqlite.org/lang_droptable.html

訂閱：文章 (Atom)

	◎	常見的SQL標準
	◎	常見的關聯式資料庫
	◎	SQLite管理工具SQLite-tools
	◎	SQLite管理工具DB Browser for SQLite
	◎	SQLite管理工具SQLite Studio
	◎	範例資料庫(sample database)
	◎	取得SQLite版本的Chinook範例資料庫
	★	SQLite3的資料類型
	◎	SQL指令及主要分類(DDL, DML, DCL, TCL)
	◎	用SQLiteStudio建立SQL學習環境

	★	SQL : CREATE TABLE語法(Syntax)格式
	◎	SQL : CREATE TABLE IF NOT EXISTS table_name
	◎	用SQLite Studio來CREATE TABLE
	◎	SQL : CREATE TABLE ... AS SELECT ...
	◎	SQL : DROP TABLE
	◎	SQL : ALTER TABLE

	★	SQL : INSERT INTO語法(Syntax)格式
	◆	SQL : WITH RECURSIVE ... INSERT INTO ...
	◆	SQL : WITH RECURSIVE ... REPLACE INTO ...
	◎	SQL : INSERT INTO

	◎	SQLite管理工具SQLite Studio
	◎	取得SQLite版本的Chinook範例資料庫
	★	SQLite3的資料類型
	◎	用SQLiteStudio建立SQL學習環境
	◎	用SQLite Studio來CREATE TABLE
	◎	用SQLiteStudio跨資料庫複製或搬移資料表內容

	◎Tips
	◎Base資料庫
	◎Calc試算表
	◎Draw繪圖
	◎Impress簡報
	◎Writer文件
	◎Basic巨集