SQLite教程1-有解無憂

0簡介

在這個SQLite初學者教程中，我們用詳細的例子涵蓋了所有關于SQLite的基礎到高級主題，本課程將通過簡單易懂的主題和例子幫助你學習SQLite，

什么是SQLite？

SQLite是一個開源的、嵌入式的、關系型的資料庫管理系統，大約設計于2000年，它是一個輕量級的資料庫，零配置，沒有服務器或安裝的要求，盡管它很簡單，但它充滿了資料庫管理系統的流行功能，

SQLite教程綱要

?? 第一課下載和安裝SQLite - 如何在Windows上下載和安裝SQLite
?? 第2課 SQLite資料庫--如何創建、打開、備份和洗掉檔案
?? 第3課 SQLite創建、修改、洗掉表--通過實體學習
?? 第4課 SQLite主鍵和外鍵--通過實體學習
?? 第5課 SQLite資料型別--SQLite中的資料型別及示例
?? 第六課 SQLite查詢--Select, Where, LIMIT, OFFSET, Count, Group By
?? 第7課 SQLite Join - 自然的左外鏈、內鏈、交叉鏈與表實體
?? 第8課 SQLite INSERT, UPDATE, DELETE查詢 - 通過實體學習
?? 第9課 SQLite索引、觸發器和視圖--結合實體學習
?? 第10課 SQLite字串函式 - REPLACE, SUBSTR, TRIM, ROUND (舉例)
?? 第11課 SQLite面試問題--20大SQLite面試問題及答案

SQLite的主要特點

與其他資料庫管理系統（如SQL Server或Oracle）相比，SQLite是非常輕量級的（它的大小小于500Kb），
SQLite不是客戶端-服務器資料庫管理系統，它是一個記憶體庫，你可以直接呼叫和使用，不需要安裝，也不需要配置，
典型的SQLite資料庫包含在計算機磁盤存盤的檔案中，所有的資料庫物件（表、視圖、觸發器等）都包含在該檔案中，不需要專門的服務器，

什么時候使用SQLite？

如果你正在為電視、移動電話、照相機、家用電子設備等設備開發嵌入式軟體，那么SQLite是一個不錯的選擇，
SQLite可以處理中低流量的HTTP請求，并管理網站的復雜會話資訊
當你需要存盤檔案的歸檔時，SQLite可以產生更小的歸檔，并且比普通的ZIP歸檔包含更少的元資料，
如果你想在應用程式中對資料進行處理，你可以使用SQLite作為一個臨時資料集，你可以將資料加載到SQLite記憶體資料庫中，并執行所需的查詢，你可以以你想在你的應用程式中顯示的格式提取資料，
提供了簡單而有效的方法來處理使用記憶體變數，例如，你正在開發一個程式，你必須對一些記錄進行計算，你可以創建一個SQLite資料庫并在那里插入記錄，只需一個查詢，你就可以選擇記錄并進行計算，
當你需要一個用于學習和培訓的資料庫系統時，SQLite是一個很好的選擇，正如我們前面解釋的，不需要安裝或配置，在你的電腦中復制SQLite庫，你就可以開始學習了，

為什么使用SQLite？

它是免費的，SQLite開放原始碼，使用它不需要商業許可，
SQLite是跨平臺的資料庫管理系統，它可以在廣泛的平臺上使用，如Windows、Mac OS、Linux和Unix，它還可以在很多嵌入式作業系統上使用，如Symbian和Windows CE，
SQLite提供了一種高效的資料存盤方式，列的長度是可變的，并不固定，因此，SQLite將只分配一個欄位需要的空間，例如，如果你有一個varchar(200)列，而你在上面放了一個10個字符的長度值，那么SQLite將只為這個值分配20個字符的空間，而不是整個200個空間，
廣泛的SQLite APIs - SQLite為廣泛的編程語言提供API，例如.Net語言（Visual Basic、C#）、PHP、Java、Objective C、Python和許多其他編程語言，
SQLite是非常靈活的，
- SQLite變數是動態型別的，也就是說，在變數被賦值之前，變數的型別是不確定的，而不是在宣告時定義，
- INSERT ON CONFLICT REPLACE陳述句，通過這個陳述句，你可以告訴SQLite嘗試對一個表進行插入，如果發現具有相同主鍵的行，那么就用插入的值更新它們，
- 使用SQLite，你可以同時在同一個會話中處理多個資料庫，只要連接這些資料庫，然后你就可以同時訪問所有資料庫的物件（表、視圖等），

SQLite的限制和不支持的功能

SQLite不支持RIGHT OUTER JOIN和FULL OUTER JOIN，它只支持 LEFT OUTER JOIN，
ALTER表陳述句的限制：在SQLite中使用ALTER表陳述句，你只能添加一列或重命名一個表（我們將在下面的教程中看到），但是，你不能做以下事情：
- ALTER列，
- DROP列，
- 增加一個約束條件，
視圖是只讀的 - 你不能在視圖中寫入INSERT、DELETE或UPDATE陳述句，然而，你可以在視圖上創建一個觸發器，并在其中進行INSERT、DELETE或UPDATE陳述句，
在QSLite中沒有實作GRANT和REVOKE命令，在SQLite中只實作了普通的檔案訪問權限，這是因為SQLite對磁盤檔案進行讀寫，與其他資料庫管理系統不同，
觸發器 - 正如我們在接下來的教程中看到的，SQLite只支持FOR EACH ROW觸發器，不支持FOR EACH STATEMENT觸發器，

安裝

下載并安裝SQLite包安裝器

從SQLite官方網站：https://www.sqlite.org/index.html

https://www.sqlite.org/2023/sqlite-tools-win32-x86-3420000.zip

解壓后執行sqlite3.exe即可

SQLite Studio

有很多SQLite管理工具可以使SQLite資料庫的作業更容易，這些工具不是用命令列來創建和管理資料庫，而是提供一套GUI工具，讓你創建和管理資料庫，

https://www2.sqlite.org/cvstrac/wiki?p=ManagementTools列出了幾十個這樣的工具；推薦使用SQLite Studio，

SQLite Studio：它是一個不需要安裝的便攜式工具，它同時支持SQLite3和SQLite2，你可以輕松地將資料匯入和匯出為各種格式，如CSV、HTML、PDF、JSON，它是開源的，支持Unicode，

官網：https://sqlitestudio.pl/ 下載安裝即可，

樣本資料庫

編輯檔案：TutorialsSampleDB.sql

CREATE TABLE [Departments] (  
    [DepartmentId] INTEGER  NOT NULL PRIMARY KEY,  
    [DepartmentName] NVARCHAR(50)  NULL  
);  
INSERT INTO Departments VALUES(1, 'IT');
INSERT INTO Departments VALUES(2, 'Physics');
INSERT INTO Departments VALUES(3, 'Arts');
INSERT INTO Departments VALUES(4, 'Math');

CREATE TABLE [Students] (  
    [StudentId] INTEGER  PRIMARY KEY NOT NULL,  
    [StudentName] NVARCHAR(50) NOT NULL,  
    [DepartmentId] INTEGER  NULL, 
    [DateOfBirth] DATE NULL,
    FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
);  
INSERT INTO Students VALUES(1, 'Michael', 1, '1998-10-12');
INSERT INTO Students VALUES(2, 'John', 1, '1998-10-12');
INSERT INTO Students VALUES(3, 'Jack', 1, '1998-10-12');
INSERT INTO Students VALUES(4, 'Sara', 2, '1998-10-12');
INSERT INTO Students VALUES(5, 'Sally', 2, '1998-10-12');
INSERT INTO Students VALUES(6, 'Jena', NULL, '1998-10-12');
INSERT INTO Students VALUES(7, 'Nancy', 2, '1998-10-12');
INSERT INTO Students VALUES(8, 'Adam', 3, '1998-10-12');
INSERT INTO Students VALUES(9, 'Stevens', 3, '1998-10-12');
INSERT INTO Students VALUES(10, 'George', NULL, '1998-10-12');

CREATE TABLE [Tests] (
    [TestId] INTEGER NOT NULL PRIMARY KEY,
    [TestName] NVARCHAR(50) NOT NULL,
    [TestDate] DATE NULL
);
INSERT INTO [Tests] VALUES(1, 'Mid Term IT Exam', '2015-10-18');
INSERT INTO [Tests] VALUES(2, 'Mid Term Physics Exam', '2015-10-23');
INSERT INTO [Tests] VALUES(3, 'Mid Term Arts Exam', '2015-10-10');
INSERT INTO [Tests] VALUES(4, 'Mid Term Math Exam', '2015-10-15');

CREATE TABLE [Marks] (  
    [MarkId] INTEGER NOT NULL PRIMARY KEY,
    [TestId] INTEGER NOT NULL,
    [StudentId] INTEGER  NOT NULL,  
    [Mark] INTEGER  NULL,
    FOREIGN KEY(StudentId) REFERENCES Students(StudentId),
    FOREIGN KEY(TestId) REFERENCES Tests(TestId) 
);  

INSERT INTO Marks VALUES(1, 1, 1, 18);
INSERT INTO Marks VALUES(2, 1, 2, 20);
INSERT INTO Marks VALUES(3, 1, 3, 16);
INSERT INTO Marks VALUES(4, 2, 4, 19);
INSERT INTO Marks VALUES(5, 2, 5, 14);
INSERT INTO Marks VALUES(6, 2, 7, 20);
INSERT INTO Marks VALUES(7, 3, 8, 20);
INSERT INTO Marks VALUES(8, 3, 9, 20);

匯入：

sqlite3 TutorialsSampleDB.db < d:\TutorialsSampleDB.sql

可以看到生成了TutorialsSampleDB.db，

使用SQLite Studio可以打開資料庫：

2資料庫檔案操作

創建

sqlite3 SchoolDB.db
sqlite> .databases
main: D:\program\sqlite-tools-win32-x86-3420000\test.db r/w
sqlite> .open test2.db #  若不存在則創建
>sqlite3 TutorialsSampleDB.db
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
sqlite> .help # 幫助
.archive ...             Manage SQL archives
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
.binary on|off           Turn binary output on or off.  Default OFF
.cd DIRECTORY            Change the working directory to DIRECTORY
.changes on|off          Show number of rows changed by SQL
.check GLOB              Fail if output since .testcase does not match
.clone NEWDB             Clone data into NEWDB from the existing database
.connection [close] [#]  Open or close an auxiliary database connection
.databases               List names and files of attached databases
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.dump ?OBJECTS?          Render database content as SQL
.echo on|off             Turn command echo on or off
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
.excel                   Display the output of next command in spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto?   Change the EXPLAIN formatting mode.  Default: auto
.filectrl CMD ...        Run various sqlite3_file_control() operations
.fullschema ?--indent?   Show schema and the content of sqlite_stat tables
.headers on|off          Turn display of headers on or off
.help ?-all? ?PATTERN?   Show help text for PATTERN
.import FILE TABLE       Import data from FILE into TABLE
.indexes ?TABLE?         Show names of indexes
.limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
.lint OPTIONS            Report potential schema issues.
.load FILE ?ENTRY?       Load an extension library
.log FILE|on|off         Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?OPTIONS?     Set output mode
.nonce STRING            Suspend safe mode for one command if nonce matches
.nullvalue STRING        Use STRING in place of NULL values
.once ?OPTIONS? ?FILE?   Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.parameter CMD ...       Manage SQL parameter bindings
.print STRING...         Print literal STRING
.progress N              Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE    Replace the standard prompts
.quit                    Stop interpreting input stream, exit if primary.
.read FILE               Read input from FILE or command output
.recover                 Recover as much data as possible from corrupt db.
.restore ?DB? FILE       Restore content of DB (default "main") from FILE
.save ?OPTIONS? FILE     Write database to FILE (an alias for .backup ...)
.scanstats on|off|est    Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
.separator COL ?ROW?     Change the column and row separators
.session ?NAME? CMD ...  Create or control sessions
.sha3sum ...             Compute a SHA3 hash of database content
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current values for various settings
.stats ?ARG?             Show stats or turn stats on or off
.system CMD ARGS...      Run CMD ARGS... in a system shell
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.timeout MS              Try opening locked tables for MS milliseconds
.timer on|off            Turn SQL timer on or off
.trace ?OPTIONS?         Output each SQL statement as it is run
.version                 Show source, library and compiler versions
.vfsinfo ?AUX?           Information about the top-level VFS
.vfslist                 List all available VFSes
.vfsname ?AUX?           Print the name of the VFS stack
.width NUM1 NUM2 ...     Set minimum column widths for columnar output
sqlite> .tables # 查看表
Departments  Marks        Students     Tests
sqlite> .backup bak.db # 備份

注意資料庫名區分大小寫，沒有DROP命令，直接洗掉檔案即可洗掉資料庫，

3表操作

創建表

CREATE TABLE table_name(
column1 datatype,
column1 datatype
);

洗掉表

DROP TABLE table_name;

修改表

ALTER TABLE table_name RENAME TO table_name2;

添加列

ALTER TABLE table_name ADD COLUMN Age INT;

可以使用 “.schema”查看欄位， “.tables” 查看表，

INSERT

INSERT INTO Tablename(colname1, colname2, ….) VALUES(valu1, value2, ….);
INSERT INTO Tablename VALUES(value1, value2, ….); # 忽略列名
INSERT INTO Tablename DEFAULT VALUES; # 默認值插入，可能會報錯，

4列約束

列約束對插入到列上的值執行約束和規則，以驗證插入的資料，列的約束是在創建表時在列定義中定義的，

SQLite主鍵

主鍵列上的所有值都應該是唯一的，并且不為空，

主鍵可以只應用于一個列，也可以應用于列的組合，在后一種情況下，列的值的組合對所有表的行都應該是唯一的，

語法：

ColumnName INTEGER NOT NULL PRIMARY KEY; # 在列本身的定義中
PRIMARY KEY(ColumnName)； # 單獨的定義：
PRIMARY KEY(ColumnName1, ColumnName2)；

非空約束

SQLite非空約束可以防止列有空值：

ColumnName INTEGER  NOT NULL;

默認約束

ColumnName INTEGER DEFAULT 0;

如果你寫一個插入陳述句，而你沒有為該列指定任何值，那么該列的值將為0，

SQLite UNIQUE 約束

防止列的所有值中出現重復的值，

例如：

EmployeeId INTEGER NOT NULL UNIQUE;

這將強制要求 "EmployeeId "的值是唯一的，不允許有重復的值，注意，這只適用于 "EmployeeId "列的值，

SQLite CHECK 約束

SQLite檢查約束是檢查插入值的條件，如果該值不符合條件將不會被插入，

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

你不能在Quantity 列中插個小于10的值，

SQLite外鍵

SQLite外鍵驗證表中的值是否存在于另一個表中，

在處理多個表的時候，當有兩個表相互之間有一個共同的列時，如果你想確保在其中表中插入的值必須存在于另一個表的列中，那么你應該在這個共同的列上使用 "外鍵約束"，

在這種情況下，當你試圖在該列上插入值時，那么外鍵將確保插入的值存在于該表的列中，

請注意，在SQLite中，外鍵約束不是默認啟用的，你必須首先通過運行下面的命令來啟用它們：

PRAGMA foreign_keys = ON;

從3.6.19版本開始，外鍵約束被引入SQLite中，

SQLite外鍵的例子
假設我們有兩個表：Students 和Departments，

Students 表有學生的串列，Departments表有部門的串列，每個學生都屬于一個部門，也就是說，每個學生都有一個departmentId列，

現在，我們將看到外鍵約束是如何幫助確保學生表中的部門ID值必須存在于部門表中的，

CREATE TABLE [Departments] (
	[DepartmentId] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
	[DepartmentName] NVARCHAR(50)  NULL
);
CREATE TABLE [Students] (
	[StudentId] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
	[StudentName] NVARCHAR(50)  NULL,
	[DepartmentId] INTEGER  NOT NULL,
	[DateOfBirth] DATE  NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
);

為了檢查外鍵約束如何防止在一個與另一個表有關系的表中插入未定義的元素或值，我們將看看下面的例子，

讓我們在部門表中插入兩個部門 "IT "和 "藝術"，如下所示：

INSERT INTO Departments VALUES(1, 'IT');
INSERT INTO Departments VALUES(2, 'Arts');

然后嘗試插入一個新的學生，這個學生的departmentId在department表中并不存在：

INSERT INTO Students(StudentName,DepartmentId) VALUES('John', 5);

該行不會被插入，你會得到錯誤“ FOREIGN KEY constraint failed”

5資料型別

與其他資料庫管理系統相比，SQLite中的資料型別是不同的，在SQLite中，你可以正常宣告資料型別，但你仍然可以在任何資料型別中存盤任何值，

SQLite的型別較少，沒有資料型別，你可以在任何列中存盤任何你喜歡的資料型別，這就是所謂的動態型別，

在靜態型別中，像在其他資料庫管理系統中，如果你宣告了一個資料型別為整數的列，你只能插入資料型別為整數的值，然而，在像SQLite這樣的動態型別中，列的型別是由插入的值決定的，然后SQLite會根據其型別來存盤該值，

SQLite存盤類

在SQLite中，根據值的型別有不同的存盤方法，這些不同的存盤方法在SQLite中稱為存盤類，

以下是SQLite中可用的存盤類：

NULL - 這個存盤類是用來存盤任何NULL值的，
INTEGER - 任何數字值都被存盤為有符號的整數值（它可以容納正負整數值），在SQLite中，INTEGER值被存盤在1、2、3、4、6或8位元組的存盤空間中，這取決于數字的值，
REAL - 這個存盤類用于存盤浮點值，它們被存盤在8位元組的存盤空間中，
TEXT - 存盤文本字串，它也支持不同的編碼，如UTF-8, UTF-16 BE, 或 UTF-26LE，
BLOB - 用于存盤大檔案，如影像或文本檔案，該值被存盤為與輸入值相同的位元組陣列，

SQLite親和型別

型別親和性是指在列中存盤資料的推薦型別，然而，你仍然可以按照你的意愿存盤任何型別的資料，這些型別是推薦的，不是必須的，

在SQLite中引入這些型別是為了最大限度地提高SQLite和其他資料庫管理系統之間的兼容性，

在SQLite資料庫中宣告的任何列都會根據其宣告的資料型別分配一個型別親和力，下面是SQLite中型別親和性的提升：

TEXT，
NUMERIC
INTEGER
REAL
BLOB

下面是SQLite如何從其宣告的資料型別中確定列的親和性：

如果宣告的型別包含字串 "INT"，則分配INTEGER親和力，
如果列的資料型別包含以下字串 "TEXT"，"CHAR "或 "CLOB "之一，則分配TEXT親和力，例如，VARCHAR型別將被分配為TEXT親和力，
如果列沒有指定型別或者資料型別是BLOB，則分配BLOB親和力，
如果型別包含以下字串之一 "DOUB"，"REAL "或 "FLOAT"，則分配REAL親和力，
對于任何其他資料型別，NUMERIC親和力被分配，

在SQLite中存盤資料型別的例子：

用SQLite integer存盤數字：

任何資料型別的列包含 "INT "字樣，它將被分配一個INTEGER型別的親和力，它將被存盤在一個INTEGER存盤類中，

所有下列資料型別都被分配為INTEGER型別的親和力：

int, integer, bigint.
int2, int4, int8.
tinyint, smallint, medium int.

在SQLite中的INTEGER型別親和力可以容納任何指定的整數（正數或負數），從1位元組到最大8位元組，

用SQLite REAL存盤數字：

REAL數字是具有雙浮點精度的數字，SQLite將實數存盤為8位元組的陣列，下面是SQLite中可以用來存盤實數的資料型別串列：

REAL
DOUBLE
DOUBLE PRECISION
FLOAT

用SQLite BLOB存盤大資料：

只有一種方法可以將大檔案存盤到SQLite資料庫中，那就是使用BLOB資料型別，這種資料型別用于存盤大檔案，如影像、檔案（任何型別）等，檔案被轉換為位元組陣列，然后以與輸入檔案相同的大小存盤，

存盤SQLite布爾運算：

SQLite沒有單獨的BOOLEAN存盤類，然而，BOOLEAN值被存盤為INTEGERS，其值為0（為假）或1（為真），

存盤SQLite的日期和時間：

你可以使用以下資料型別之一在SQLite中宣告日期或日期時間：

DATE
DATETIME
TIMESTAMP
TIME
注意，在SQLite中沒有單獨的DATE或DATETIME存盤類，相反，任何用前述資料型別之一宣告的值都會根據插入值的日期格式存盤在一個存盤類中，如下所示：
TEXT - 如果你插入的日期值是ISO8601字串的格式（"YYYY-MM-DD HH:MM:SS.SSS"），
REAL-- 如果你在朱利安日數字中插入日期值，即公元前4714年11月24日格林威治正午后的天數，那么日期值將被存盤為REAL，
INTEGER作為Unix時間，自1970-01-01 00:00:00 UTC以來的秒數，

釘釘或微信號: pythontesting 微信公眾號：pythontesting

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/554198.html

標籤：其它

上一篇：03_事務隔離

下一篇：返回列表