SQLite | Insert、Delete、Updata 与 Drop 语句

文章目录

• 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_id	first_name	last_name	phone	email	vip
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_id	first_name	last_name	phone	email	vip
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