SQLite | Insert、Delete、Updata 与 Drop 语句

文章目录

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
1ThomasNieldNoneNone0

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
1ThomasNieldNoneNone0
2JonSkeeter4802185842john.skeeter@rex.net1
3SamScala2156783401sam.scala@gmail.com0
4BrittanyFisher5932857296brittany.fisher@outlook.com0

从 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 语句