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

顯示具有 60_SQLite Studio應用 標籤的文章。顯示所有文章

2021-01-24

用SQLiteStudio跨資料庫複製或搬移資料表內容

SQLite Studio 資料表的複製、移動功能，可以讓SQLite 的資料庫間，快速地達到資料複製(copy)或搬移(move)的需求，尤其是在測試資料的過程，更能感受到這個功能的妙用。

這個學習範例所需的資料庫環境，可以參閱：

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

來源資料庫：Chinook，有11個資料表，每個資料表都有資料
目的資料庫：TestWind ，沒有資料表、沒有資料
選取Chinook 的 Artist 資料表，按住滑鼠左鍵，拖曳到 TestWind 的 Tables 位置，放開左鍵。
勾選：include data, include indexes, include triggers
點選：Copy選項
Referenced tables的提醒：
SQLite Studio 提醒 Artist 這個資料表被 Albumn, PlaylistTrack, Track, InvoiceLine 等資料 Reference了，提示：要不要一併匯入這些資料表？
這裡選擇：No，只Copy Artist資料表
確認複製成功。

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
);

用SQLiteStudio建立SQL學習環境

SQLiteStudio的取得及其相關功能，可以參閱：
SQLite管理工具SQLite Studio https://sql.bod.idv.tw/2021/01/sqlitesqlite-studio.html
在這網站內的SQL學習，會以Chinook範例資料庫，當資料操作的資料來源。
如何取得Chinook？
https://github.com/lerocha/chinook-database
https://github.com/lerocha/chinook-database/tree/master/ChinookDatabase/DataSources
https://archive.codeplex.com/?p=chinookdatabase
可以參考：取得SQLite版本的Chinook範例資料庫 https://sql.bod.idv.tw/2021/01/sqlitechinook.html
這裡除了準備Chinook Sample Database外，我們將在SQLiteStudio新增建立一個測試資料庫TestWind.sqlite的資料庫。

Databases區塊內按滑鼠右鍵→Database→Add a database
或 Database→Add a database

按『＋』鈕，選擇存放的資料夾位置，輸入資料庫檔名：TestWind.sqlite，存檔。

OK，建立TestWind資料庫。

確認SQLiteStudio的資料庫區塊，已有：Chinook, TestWind
選取TestWind，Connect to the database

2021-01-22

SQLite3的資料類型

SQLite的官網提到，目前大部份的SQL資料庫引擎（除了SQLite之外）都使用靜態、嚴格的資料類型。使用靜態類型時，資料值的數據類型由儲存這項資料的資料欄位型態決定。
SQLite的資料類型，使用更通用的動態類型系統(dynamic type system)。在SQLite中，資料值的資料類型與資料值本身相關聯，而不是與存放資料的資料欄位型態相關聯。 SQLite的動態類型系統向下相容其他資料庫引擎中更常見的靜態類型系統，因為在靜態類型資料庫上工作的SQL語句應該在SQLite中以相同的方式工作。但是，SQLite中的動態類型允許它執行傳統的嚴格類型資料庫中無法實現的操作。
每一個存放在SQLite資料庫中的資料值，都具有下列的資料型態中的一個資料類型：

NULL : 空值。
INTEGER : 整數。是帶有正負值的整數，可能會使用1, 2, 3, 4, 6, 8個位元組(Bytes)來存放資料，實際使用的Bytes數，以存放的值來決定。
REAL : 浮點數值。以 8 Bytes來存放IEEE浮點數。
TEXT : 文字字串值。以資料庫的文字編碼方式：UTF-8, UTF-16BE, UTF-16LE來儲存資料。
BLOB : 二進位大型物件(Binary Large OBject)。
沒有Boolean值的資料儲存型別，SQLite3用0來儲存False(假)，用1來儲存True(真)。
沒有日期(Date)、時間(Time)的資料儲存型別，SQLite內建的日期和時間函數，將日期和時間存儲為TEXT，REAL或INTEGER值。
如果是TEXT，存為ISO8601字符串（“YYYY-MM-DD HH：MM：SS.SSS”）。
如果是REAL，是記錄一個Julian的日期數，是西元前4714年11月24日格林威治中午以來的天數。
如果是INTEGER，是紀錄1970-01-01 00:00:00 UTC以來的秒數(Unix Time)。

近似型別(Type Affinity)
SQLite3資料庫中的每一個資料欄位，都會被指定為下列型別之一的近似型別：TEXT, NUMERIC, INTEGER, REAL, BLOB。
欄位近似型別的決定規則(Determination Of Column Affinity)，規則依序如下：

宣告的資料類型包含字串“INT”，視為INTEGER 的近似型別。
宣告的資料類型包含字串“CHAR”、“CLOB”或“TEXT”，視為TEXT的近似型別。 VARCHAR / NVARCHAR 類型包含字串“CHAR”，視為TEXT的近似型別。
宣告的類型包含字串“BLOB”、或未指定類型，視為BLOB的近似型別。
宣告的類型包含字串“REAL”、“FLOA”或“DOUB”，視為REAL的近似型別。
上述似規則以外，視為NUMERIC的近似型別。

近似型別歸類舉例：

CREATE TABLE宣告或CAST5轉換式	歸類	規則
INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8	INTEGER	i
CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(55), NATIVE CHARACTER(70), NVARCHAR(100), TEXT,CLOB	TEXT	ii
BLOB, no datatype specified	BLOB	iii
REAL, DOUBLE, DOUBLE PRECISION, FLOAT	REAL	iv
NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME	NUMERIC	v

SQLite Studio資料欄位可以選擇的選項：
BIGINT, BLOB, BOOLEAN, CHAR, DATE, DATETIME, DECIMAL, DOUBLE, INTEGER, INT, NONE, NUMERIC, REAL, STRING, TEXT, VARCHAR

參考資料：Datatypes In SQLite Version 3 https://www.sqlite.org/datatype3.html

取得SQLite版本的Chinook範例資料庫

下載取得Chinook範例資料庫：
https://github.com/lerocha/chinook-database/blob/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite

簡化資料庫名稱，把Chinook_Sqlite.sqlite 修改為 Chinook.sqlite
開啟SQLite Studio，以SQLite Studio開啟資料庫Chinook.sqlite ：
把資料庫檔案拖曳到SQLite Studio的區塊內，放開滑鼠，會開啟Database小視窗，按下OK鈕，就可以成功開啟這個資料庫了。

可以透過SQLite Studio查看Chinook.sqlite的資料表、資料欄位、Primary Key、索引(Index)、資料內容、Constraint(限制條件) ...
將資料庫檔案拖曳到Database區塊內後，Database→Connect to the database

Chinook資料庫內，共包含11個資料表：Album, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track，每個資料表內所包含的資料欄位，說明如下：(以Chinook_sqlite.sql的CREATE TABLE來查看資料表的資料欄位內容)

Album :
CREATE TABLE [Album]
(
[AlbumId] INTEGER NOT NULL,
[Title] NVARCHAR(160) NOT NULL,
[ArtistId] INTEGER NOT NULL,
CONSTRAINT [PK_Album] PRIMARY KEY ([AlbumId]),
FOREIGN KEY ([ArtistId]) REFERENCES [Artist] ([ArtistId])
ON DELETE NO ACTION ON UPDATE NO ACTION
);
Artist :
CREATE TABLE [Artist]
(
[ArtistId] INTEGER NOT NULL,
[Name] NVARCHAR(120),
CONSTRAINT [PK_Artist] PRIMARY KEY ([ArtistId])
);
Customer :
CREATE TABLE [Customer]
(
[CustomerId] INTEGER NOT NULL,
[FirstName] NVARCHAR(40) NOT NULL,
[LastName] NVARCHAR(20) NOT NULL,
[Company] NVARCHAR(80),
[Address] NVARCHAR(70),
[City] NVARCHAR(40),
[State] NVARCHAR(40),
[Country] NVARCHAR(40),
[PostalCode] NVARCHAR(10),
[Phone] NVARCHAR(24),
[Fax] NVARCHAR(24),
[Email] NVARCHAR(60) NOT NULL,
[SupportRepId] INTEGER,
CONSTRAINT [PK_Customer] PRIMARY KEY ([CustomerId]),
FOREIGN KEY ([SupportRepId]) REFERENCES [Employee] ([EmployeeId]) ON DELETE NO ACTION ON UPDATE NO ACTION
);
Employee :
CREATE TABLE [Employee]
(
[EmployeeId] INTEGER NOT NULL,
[LastName] NVARCHAR(20) NOT NULL,
[FirstName] NVARCHAR(20) NOT NULL,
[Title] NVARCHAR(30),
[ReportsTo] INTEGER,
[BirthDate] DATETIME,
[HireDate] DATETIME,
[Address] NVARCHAR(70),
[City] NVARCHAR(40),
[State] NVARCHAR(40),
[Country] NVARCHAR(40),
[PostalCode] NVARCHAR(10),
[Phone] NVARCHAR(24),
[Fax] NVARCHAR(24),
[Email] NVARCHAR(60),
CONSTRAINT [PK_Employee] PRIMARY KEY ([EmployeeId]),
FOREIGN KEY ([ReportsTo]) REFERENCES [Employee] ([EmployeeId])
ON DELETE NO ACTION ON UPDATE NO ACTION
);
Genre :
CREATE TABLE [Genre]
(
[GenreId] INTEGER NOT NULL,
[Name] NVARCHAR(120),
CONSTRAINT [PK_Genre] PRIMARY KEY ([GenreId])
);
Invoice :
CREATE TABLE [Invoice]
(
[InvoiceId] INTEGER NOT NULL,
[CustomerId] INTEGER NOT NULL,
[InvoiceDate] DATETIME NOT NULL,
[BillingAddress] NVARCHAR(70),
[BillingCity] NVARCHAR(40),
[BillingState] NVARCHAR(40),
[BillingCountry] NVARCHAR(40),
[BillingPostalCode] NVARCHAR(10),
[Total] NUMERIC(10,2) NOT NULL,
CONSTRAINT [PK_Invoice] PRIMARY KEY ([InvoiceId]),
FOREIGN KEY ([CustomerId]) REFERENCES [Customer] ([CustomerId])
ON DELETE NO ACTION ON UPDATE NO ACTION
);
InvoiceLine :
CREATE TABLE [InvoiceLine]
(
[InvoiceLineId] INTEGER NOT NULL,
[InvoiceId] INTEGER NOT NULL,
[TrackId] INTEGER NOT NULL,
[UnitPrice] NUMERIC(10,2) NOT NULL,
[Quantity] INTEGER NOT NULL,
CONSTRAINT [PK_InvoiceLine] PRIMARY KEY ([InvoiceLineId]),
FOREIGN KEY ([InvoiceId]) REFERENCES [Invoice] ([InvoiceId])
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY ([TrackId]) REFERENCES [Track] ([TrackId])
ON DELETE NO ACTION ON UPDATE NO ACTION
);
MediaType :
CREATE TABLE [MediaType]
(
[MediaTypeId] INTEGER NOT NULL,
[Name] NVARCHAR(120),
CONSTRAINT [PK_MediaType] PRIMARY KEY ([MediaTypeId])
);
Playlist :
CREATE TABLE [Playlist]
(
[PlaylistId] INTEGER NOT NULL,
[Name] NVARCHAR(120),
CONSTRAINT [PK_Playlist] PRIMARY KEY ([PlaylistId])
);
PlaylistTrack :
CREATE TABLE [PlaylistTrack]
(
[PlaylistId] INTEGER NOT NULL,
[TrackId] INTEGER NOT NULL,
CONSTRAINT [PK_PlaylistTrack] PRIMARY KEY ([PlaylistId], [TrackId]),
FOREIGN KEY ([PlaylistId]) REFERENCES [Playlist] ([PlaylistId])
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY ([TrackId]) REFERENCES [Track] ([TrackId])
ON DELETE NO ACTION ON UPDATE NO ACTION
);
Track
CREATE TABLE [Track]
(
[TrackId] INTEGER NOT NULL,
[Name] NVARCHAR(200) NOT NULL,
[AlbumId] INTEGER,
[MediaTypeId] INTEGER NOT NULL,
[GenreId] INTEGER,
[Composer] NVARCHAR(220),
[Milliseconds] INTEGER NOT NULL,
[Bytes] INTEGER,
[UnitPrice] NUMERIC(10,2) NOT NULL,
CONSTRAINT [PK_Track] PRIMARY KEY ([TrackId]),
FOREIGN KEY ([AlbumId]) REFERENCES [Album] ([AlbumId])
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY ([GenreId]) REFERENCES [Genre] ([GenreId])
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY ([MediaTypeId]) REFERENCES [MediaType] ([MediaTypeId])
ON DELETE NO ACTION ON UPDATE NO ACTION
);

2021-01-21

SQLite管理工具SQLite Studio

資料更新：
2019-12-30起，SQLiteStudio的原始碼及程式下載，已經移至GitHub
https://github.com/pawelsalawa/sqlitestudio/releases
如需要舊版的資料：
(3.x.x) : https://www.dropbox.com/sh/ao4nz2qjfsz2yuy/AABwiiss3do7n0wNecuk-uyna?dl=0
(2.x.x) : https://www.dropbox.com/sh/iyilxtepgswpdlm/AADmYlJ4QRYWn_eo9u4fPn0Aa?dl=0

原內容：
之前介紹的SQLite-tools是文字介面的管理程式，還有圖形介面的DB Browser for SQLite，但我最常用的是SQLite Studio，現在就來介紹一下SQLite Studio：

SQLite Studio官方網站：https://sqlitestudio.pl/index.rvt
SQLite Studio下載網誌：https://sqlitestudio.pl/index.rvt?act=download

提供Windows / Linux / MacOSX 安裝版即可攜版
有提供SHA-256，下載後先比對一下再使用，用起來會更放心。

以Windows portable SQLiteStudio-3_2_1.zip為例：(解壓縮後，可以直接使用)

SQLite Studio 主要的功能簡介：

主畫面：
Database→Connect to database
Database→Disconnect from database
Database→Add a database
Database→Edit the database
Database→Remove the database
Database→Export the database
可以匯出的格式：HTML / JSON / PDF / SQL / XML
可以指定文字編碼(text encoding)
Database→Convert database type
以開啟SQLite3為例，可以指定轉換為：SQLite2 / SQLCipher / System.Data.SQLite / WxSQLite3
Database→Vacuum
Database→Integrity check
Database→Refresh selected database schema
Database→Refresh all database schema
Structure→Create a table
Structure→Edit the table
Structure→Delete the table
Structure→Create an index
Structure→Edit the index
Structure→Create a trigger
Structure→Edit the trigger
Structure→Delete the trigger
Structure→Create a view
Structure→Edit the view
Structure→Delete the view
View→Databases
View→Status
View→Database toolbar
View→Structure toolbar
View→Tools
View→Window list
View→View toolbar
View→Tile windows
View→Tile windows horizontally
View→Tile windows vertically
View→Cascade window
View→Close selected windows
View→Close all windows but selected
View→Close all windows
View→Restore recently closed window
View→Rename selected windows
View→Window list
Tools→Open SQL editor
Tools→Open DDL history
Tools→Open SQL functions editor
Tools→Open collations editor
Tools→Import
Tools→Export
Tools→Open configuration dialog
Help→User Manual
Help→SQLite documentation
Help→Open home page
Help→Open forum page
Help→Check for updates
Help→Report a bug
Help→Propose a new feature
Help→Bugs and feature requests
Help→Licence
Help→About

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