文章目錄

• 1. 管理數據
• • 1.1 插入數據
  • 1.2 多行插入
  • 1.3 測試外鍵
  • 1.4 刪除數據
  • 1.5 更新數據
  • 1.6 刪除表格
• 參考資料

1. 管理數據

在上一篇文章中，我們介紹了如何創建一個數據庫，充分考慮了表格設計，列限制以及表的關系。而在本文中，我們將學習 INSERT（插入）、DELETE（刪除）和 UPDATE（更新）記錄，事實上，這些操作比 SELECT 語句更簡單。

1.1 插入數據

在一個關系數據庫中，數據只有在接收到記錄時才會產生。INSERT 正是將記錄插入數據庫的語句，你可以選擇只選擇某幾列來添加數據，然后讓其他的保留缺失值 null 或者默認值。

首先我們將會在上一篇文章建立的 SurgeTech 數據庫中插入數據，首先導入數據庫：

• 使用Jupyter Notebook 運行 SQL 語句需安裝 ipython-sql

• %sql 以及 %%sql 為在 Notebook 中運行 SQL 語句，在 SQLite 命令行或 SQLite Stiduo 中不需要 %sql 或 %%sql

載入 SQL 以及連接 SQLite：

%load_ext sql %sql sqlite:///DataBase/surgetech_conference2.db 'Connected: @DataBase/surgetech_conference2.db' 圖1 SurgeTech Conference 數據庫 Schema

你可以將你的名字插入到數據庫中：

%%sql INSERT INTO attendee (first_name, last_name) VALUES('Thomas', 'Nield') * sqlite:///DataBase/surgetech_conference2.db 1 rows affected.

可以看到，我們已經將 ‘Thomas’，‘Nield’ 分別插入到了 FIRST_NAME 和 LAST_NAME，而由于 ATTENDEE_ID 設置了 ‘AUTOINCREMENT’（詳見上篇文章），因此自動編號為 1 ，而 VIP 由于默認值為 0 ，因此也具有數值，設下的 PHONE 和 EMAIL 則仍為缺失值。

事實上，在 SQLite 中設置 AUTOINCREMENT 是沒有必要的，只是在如 MySQL 等其他平臺才需要。因為在 SQLite 中，只要一列數據是整形并且為主鍵，則當有新的記錄時就會自動編號

%%sql SELECT * from attendee attendee_idfirst_namelast_namephoneemailvip

1	Thomas	Nield	None	None	0

1.2 多行插入

如果你有一大串數據需要插入，但卻不想多次輸入時，可以在 values 后面多次重復，并且將每一行數據用逗號分隔開：

%%sql INSERT INTO attendee (first_name, last_name, phone, email, vip) VALUES ('Jon', 'Skeeter',4802185842,'john.skeeter@rex.net', 1), ('Sam','Scala',2156783401,'sam.scala@gmail.com', 0), ('Brittany','Fisher',5932857296,'brittany.fisher@outlook.com', 0) * sqlite:///DataBase/surgetech_conference2.db 3 rows affected.

用這種方式插入數據將會更有效率一點，尤其是當你有成百上千條數據時。如果要在 Jave 或者 Python 中向數據庫插入表格數據的話，也應該用這種方式，而不是多次循環 insert 語句。

當你需要將數據從一個表格轉移到另外一個表格時，也可以利用 SELECT 查詢的結果插入到數據庫中，只要保證 SELECT 語句的列和數據類型于 INSERT 語句相同即可：

insert into ATTENDEE (first_name, last_name, phone, email) select first_name, last_name, phone, email from SOME_OTHER_TABLE

1.3 測試外鍵

為了防止出現孤兒數據（Orphaned record），我們在上篇文章創建數據庫時為多個表添加了外鍵限制，現在我們可以測試一下這些限制是否起作用了。

當前 ATTENDEE 表中只有 ATTENDEE_ID 為 1～4 的數據，為了測試外鍵限制是否起作用，我們可以試著在 COMPANY 表中添加 PRIMARY_CONTACT_ID 為 5 的數據：

%%sql SELECT * FROM attendee * sqlite:///DataBase/surgetech_conference2.db Done. attendee_idfirst_namelast_namephoneemailvip

1	Thomas	Nield	None	None	0
2	Jon	Skeeter	4802185842	john.skeeter@rex.net	1
3	Sam	Scala	2156783401	sam.scala@gmail.com	0
4	Brittany	Fisher	5932857296	brittany.fisher@outlook.com	0

從 SQLite 3.6.19 開始支持 外鍵約束

SQLite 默認沒有打開外鍵約束，若需要使用外鍵約束，需要設置 'PRAGMA foreign_keys = ON，如果沒有開啟，即使原數據庫中支持外間約束，在添加數據時也不會進行約束

%%sql PRAGMA foreign_keys = ON; INSERT INTO company (name, description, primary_contact_id) VALUES ('RexApp Solutions', 'A mobile app delivery service', 5) IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY constraint failed [SQL: INSERT INTO company (name, description, primary_contact_id) VALUES ('RexApp Solutions', 'A mobile app delivery service', 5)] (Background on this error at: http://sqlalche.me/e/gkpj)

可以看到，由于外鍵約束的存在，因此時無法向表 COMPANY 添加 PRIMARY_CONTACT_ID 為 5 的數據的，再試試 PRIMARY_CONTACT_ID 為 3:

%%sql INSERT INTO company (name, description, primary_contact_id) VALUES ('RexApp Solutions', 'A mobile app delivery service', 3) * sqlite:///DataBase/surgetech_conference2.db 1 rows affected.

可以看到成功添加了。

1.4 刪除數據

DELETE 語句既簡單又危險，你可以使用一行命令將一整個表的數據刪除：

%%sql DELETE FROM presentation_attendance * sqlite:///DataBase/surgetech_conference2.db 0 rows affected.

然而由于外鍵約束，你無法刪除任何父表：

%%sql DELETE FROM attendee IntegrityError: (sqlite3.IntegrityError) FOREIGN KEY constraint failed [SQL: DELETE FROM attendee] (Background on this error at: http://sqlalche.me/e/gkpj)

在使用 DELETE 語句時，可以同時使用 WHERE 子句來篩選需要刪除的數據，如果你想要刪除那些沒有手機號碼和電子郵箱的數據，你可以：

%%sql PRAGMA foreign_keys = OFF; -- 關閉外鍵約束 DELETE FROM attendee WHERE phone IS null AND email IS null; * sqlite:///DataBase/surgetech_conference2.db Done. 1 rows affected.

在 SQLite，使用 DELETE FROM attendee 來刪除表中的所有數據，而在 MySQL 等其他平臺中，習慣用 TRUNCATE TABLE attendee 來刪除 attendee 表的數據

1.5 更新數據

終于，我們介紹到了 UPDATE 語句了，UPDATE 語句可以修改現有的記錄，如果我們想要將郵箱修改為大寫的話，可以使用 UPPER() 函數：

%%sql UPDATE attendee SET email = UPPER(email) * sqlite:///DataBase/surgetech_conference2.db 3 rows affected.

我們也可以同時修改多個列，只需要在 set 表達式后面依次添加逗號就可以了，如同時將 first_name 和 last_name 改成大寫：

%%sql UPDATE attendee SET first_name = UPPER(first_name), last_name = UPPER(last_name) * sqlite:///DataBase/surgetech_conference2.db 3 rows affected.

你也同樣可以在 UPDATE 語句中使用 WHERE 子句來篩選數據，如將 attendee_id 為 3 和 4 的參會者設為 VIP：

%%sql UPDATE attendee SET vip = 1 WHERE attendee_id IN (3,4) * sqlite:///DataBase/surgetech_conference2.db 2 rows affected.

1.6 刪除表格

可能有時候你想要將某個表格從數據庫中刪去，這是你只要使用 DROP TABLE 加上你想要刪除的表就可以了（注意這將會永久的將表刪去）：

DROP TABLE MY_UNWANTED_TABLE

參考資料

[1] Thomas Nield.Getting Started with SQL[M].US: O’Reilly, 2016: 91-95

相關文章：

SQL | 目錄
SQLite | SQLite 與 Pandas 比較篇之一
SQLite | Select 語句
SQLite | Where 子句
SQLite | Group by 與 Order by 子句
SQLite | CASE 子句
SQLite | Join 語句
SQLite | 數據庫設計與 Creat Table 語句

總結

以上是生活随笔為你收集整理的SQLite | Insert、Delete、Updata 与 Drop 语句的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。