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

顯示具有 20_SQL資料定義(DDL) 標籤的文章。顯示所有文章

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

SQL : CREATE TABLE ... AS SELECT ...

SQL : CREATE TABLE ... AS SELECT ... 使用SELECT的結果建立資料表，並將條件過濾後的查詢結果，匯入新建立的資料表中。

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

取得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分頁中輸入所要執行的指令

CREATE TABLE Album_20190920 as 
  SELECT AlbumId, Title FROM Album WHERE Title like 'A%';

執行SQL指令：(F9) Execute SQL
Status : 確認SQL指令執行無誤
確認資料表Album_20190920已建立，
包含兩個資料欄位：AlbumId, Title，
只匯入A開頭的資料
注意：查看DLL分頁
PRIMARY KEY 等限制條件(Constraints)、結構定義的內容，不會被匯入。
資料型態：INTEGER → 被轉換成 INT，NVARCHAR(160) →被轉換成 TEXT

2021-01-23

自行輸入CREATE TABLE的指令，在對CREATE TABLE指令，還不是很清楚孰悉的情況下，是有一些難度的，SQLite Studio這時候，可以發揮極佳的輔助功能，輕鬆地幫忙使用者CREATE TABLE。
完成資料表建立後，還可以檢視取得CREATE這個TABLE的SQL指令內容。

這裡CREATE TABLE2的目標，是要完成一個像下圖內容的資料表：
資料表名稱：Album，包含三個資料欄位：AlbumId, Title, ArtistId，... 詳細資料如下表 ...

選取要執行這段指令的資料庫，可以參閱：

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

Structure → Create a table
或點選工具列上的『Create a table』
輸入表格名稱(Table name)：Album
按下『Add Column(Ins)』鈕
新增資料欄位：
以AlbumId為例，輸入 Column name，選取Data type ，
限制(Constraints)定義的選項，有：Primary Key, Foreign Key, Unique, Check conditions, Not NULL, Collate, Default等，每一個選項都可以按『Configure』鈕，進行進一步的設定。
新增資料欄位：
Column name : ArtistId 的 Foreign Key 設定
Commit structure change，儲存表格新增。

切換到DDL分頁，查看剛剛透過經由程式頁面操作所得到的DDL SQL指令碼

CREATE TABLE Album (
    AlbumId  INTEGER        CONSTRAINT PK_Album PRIMARY KEY
                            NOT NULL
                            DEFAULT NULL,
    Title    NVARCHAR (160) NOT NULL
                            DEFAULT NULL,
    ArtistId INTEGER        REFERENCES Artist (ArtistId) ON DELETE NO ACTION
                                                         ON UPDATE NO ACTION
                            NOT NULL
                            DEFAULT NULL
);

SQL : CREATE TABLE IF NOT EXISTS table_name

在CREATE TABLE 之前，先確認目前連線的資料庫，確定不存在所要CREATE的資料表，這在透過程式管理的資料庫管控上，可以避免程式coding的複雜度，減少例外狀況的排出。
簡單的加上 IF NOT EXISTS 即可輕鬆地達到事先檢查的目的。

CREATE TABLE IF NOT EXISTS Artist (
    ArtistId INTEGER        NOT NULL,
    Name     NVARCHAR (120),
    CONSTRAINT PK_Artist PRIMARY KEY (ArtistId)
);

在SQLite Studio執行這個CREATE TABLE IF NOT EXISTS指令：

選取要執行這段指令的資料庫，可以參閱：

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

Tools → Open SQL Editor
在Query分頁中輸入所要執行的指令
(F9) Execute SQL
Status : 確認SQL指令執行無誤

SQL : CREATE TABLE語法(Syntax)格式

SQL : CREATE TABLE語法(Syntax)格式：

CREATE TABLE [IF NOT EXISTS] 資料表名稱 (
欄位名稱 [資料型態] [NULL | NULL] [AUTO_INCREMENT] [DEFAULT 預設值] [定義整合限制] ,
...,
PRIMARY KEY (欄位名稱, ... )
UNIQUE (欄位名稱, ... )
FOREIGN KEY (欄位名稱, ... ) REFERENCES 資料表(欄位名稱, ... )
[ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
[ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
CHECK(限制的檢查條件)
);

SQL程式碼採用自由格式，不限制一行只接受多少個字元，也不限制如何斷行。
[IF NOT EXISTS] ：先確認資料表不存在，再予CREATE；可以不使用這個判斷選項。
PRIMARY KEY：用來定義某一或某些欄位為主鍵，不可為空值
UNIQUE：用來定義某一或某些欄位具有唯一的索引值，可以有空值
FOREIGN KEY：用來定義某一或某些欄位為外部鍵
REFERENCES 資料表(欄位名稱, ... ) ：外鍵所要參考的資料表、資料欄位。
[NULL | NOT NULL]：可以為空值(NULL)、不可為空值(NOT NULL)選其中一項，或都不選。
[AUTO_INCREMENT]：當資料型態宣告為INT整數時，如果使用[AUTO_INCREMENT]選項，當新增一筆資料時，該欄位資料，會自動加一作為該欄位的資料值。
[ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]：可使用或不使用ON DELETE，但選用後，必須選用{NO ACTION | CASCADE | SET DEFAULT | SET NULL}的其中一項。
CHECK 用來額外的檢查條件

CREATE TABLE的SQL範例：

CREATE TABLE [Artist]
(
    [ArtistId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Name] NVARCHAR(120)
);

CREATE TABLE [Album]
(
    [AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Title] NVARCHAR(160)  NOT NULL,
    [ArtistId] INTEGER  NOT NULL,
    FOREIGN KEY ([ArtistId]) REFERENCES [Artist] ([ArtistId]) 
        ON DELETE NO ACTION ON UPDATE NO ACTION
);

參考資料：

SQL As Understood By SQLite : CREATE TABLE
https://sqlite.org/lang_createtable.html
CREATE TABLE (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/statements/create-table-transact-sql?view=sql-server-2017

訂閱：文章 (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巨集