SQLitedataoperationtutorial

SQLitedataoperationoverview

dataoperation is SQLitein最basic也 is 最常用 operation之一, including向表in插入 new data, update现 has data and delete不需要 data. 这些operation is datalibraryapplication程序 corefunctions, Master它们 for 于Developmentdatalibraryapplication至关 important .

SQLitedataoperation basiccommands

INSERT: 向表in插入 new data
UPDATE: update表in 现 has data
DELETE: from 表indeletedata
REPLACE: 插入 or replacedata

插入data (INSERT)

INSERT语句用于向表in插入 new 行data.

basic语法

                    -- 插入完整行
INSERT INTO table_name VALUES (value1, value2, ..., valueN);

-- 插入指定列
INSERT INTO table_name (column1, column2, ..., columnN) VALUES (value1, value2, ..., valueN);

-- 插入 many 行
INSERT INTO table_name (column1, column2, ...) VALUES 
(value1a, value2a, ...),
(value1b, value2b, ...),
(value1c, value2c, ...);

-- example
-- 插入完整行
INSERT INTO users VALUES (1, '张三', 'zhangsan@example.com', 25);

-- 插入指定列
INSERT INTO users (name, email, age) VALUES ('李四', 'lisi@example.com', 30);

-- 插入 many 行
INSERT INTO users (name, email, age) VALUES 
('王五', 'wangwu@example.com', 28),
('赵六', 'zhaoliu@example.com', 32),
('孙七', 'sunqi@example.com', 26);
                

usingDEFAULT值

                    -- creation带默认值 表
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL DEFAULT 0.0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 插入时using默认值
INSERT INTO products (name) VALUES ('产品A');
INSERT INTO products (name, price) VALUES ('产品B', 99.99);
                

from other表插入data

                    --  from other表插入data
INSERT INTO table1 (column1, column2, ...)
SELECT column1, column2, ...
FROM table2
WHERE condition;

-- example:  from  old 表向 new 表migrationdata
INSERT INTO users_new (name, email, age)
SELECT name, email, age
FROM users_old
WHERE status = 'active';
                

updatedata (UPDATE)

UPDATE语句用于modify表in 现 has data.

basic语法

                    -- update所 has 行
UPDATE table_name SET column1 = value1, column2 = value2, ...;

-- update满足条件 行
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

-- example
-- update所 has user status
UPDATE users SET status = 'active';

-- updatespecificuser information
UPDATE users
SET name = '张三', age = 26
WHERE id = 1;

-- using表达式update
UPDATE products
SET price = price * 0.9  -- 打9折
WHERE category = 'electronics';
                

using子queryupdate

                    -- using子queryupdate
UPDATE table1
SET column1 = (SELECT column1 FROM table2 WHERE table1.id = table2.id)
WHERE condition;

-- example: 根据部门表update员工表 部门名称
UPDATE employees
SET department_name = (SELECT name FROM departments WHERE employees.department_id = departments.id);
                

update Notes

始终usingWHERE子句限制update范围, 否则会update表in 所 has 行
using主键 or 唯一约束列serving asWHERE条件, 确保只update目标行
updateoperation is 不可逆 , in 执行 before 应确认update条件
for 于 big 型表 updateoperation, 可能会影响performance, 应谨慎执行

deletedata (DELETE)

DELETE语句用于 from 表indeletedata.

basic语法

                    -- delete所 has 行
DELETE FROM table_name;

-- delete满足条件 行
DELETE FROM table_name WHERE condition;

-- example
-- delete所 has user (谨慎using) 
DELETE FROM users;

-- deletespecificuser
DELETE FROM users WHERE id = 1;

-- delete满足条件  many 个user
DELETE FROM users WHERE age < 18 OR status = 'inactive';
                

delete Notes

始终usingWHERE子句限制delete范围, 否则会delete表in 所 has 行
using主键 or 唯一约束列serving asWHERE条件, 确保只delete目标行
deleteoperation is 不可逆 , in 执行 before 应backup important data
for 于 big 型表 deleteoperation, 可能会影响performance, 应谨慎执行
deletedata不会释放disk空间, 需要usingVACUUMcommands回收空间

replacedata (REPLACE)

REPLACE语句 is SQLite scale语法, 用于插入 new data or replace现 has data.

basic语法

                    -- replacedata
REPLACE INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

-- example
-- such as果id for 1 user存 in , 则replace; 否则插入
REPLACE INTO users (id, name, email) VALUES (1, '张三', 'zhangsan_new@example.com');

-- replace many 行
REPLACE INTO users (id, name, email) VALUES 
(2, '李四', 'lisi_new@example.com'),
(3, '王五', 'wangwu_new@example.com');
                

REPLACE working principles

尝试插入 new data
such as果发生唯一约束 or 主键conflict, 则deleteconflict 行
然 after 插入 new data

REPLACE and INSERT OR REPLACE 区别

                    -- REPLACE语句
REPLACE INTO users (id, name) VALUES (1, '张三');

-- INSERT OR REPLACE语句
INSERT OR REPLACE INTO users (id, name) VALUES (1, '张三');

-- 两者效果相同, 都 is SQLite scale语法
                

批量operation

批量operation is 指一次性执行 many 个dataoperation语句, 这 in processing big 量data时非常 important .

usingtransactionfor批量operation

                    -- usingtransactionfor批量插入
BEGIN TRANSACTION;

INSERT INTO users (name, email, age) VALUES ('user1', 'user1@example.com', 25);
INSERT INTO users (name, email, age) VALUES ('user2', 'user2@example.com', 26);
INSERT INTO users (name, email, age) VALUES ('user3', 'user3@example.com', 27);
INSERT INTO users (name, email, age) VALUES ('user4', 'user4@example.com', 28);
INSERT INTO users (name, email, age) VALUES ('user5', 'user5@example.com', 29);

COMMIT;
                

批量updateexample

                    -- 批量updatestatus
BEGIN TRANSACTION;

UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';
UPDATE users SET status = 'active' WHERE last_login >= '2023-01-01';

COMMIT;
                

dataoperationbest practices

遵循以 under best practices可以improvingdataoperation efficiency and security性:

插入operationbest practices

using指定列 INSERT语句, 而不 is 依赖列顺序
for 于 big 量data插入, usingtransaction and 批量插入
利用默认值reducing需要指定列数
for 于自增主键, 不要手动指定值

updateoperationbest practices

始终usingWHERE子句限制update范围
using主键 or 唯一index列serving asWHERE条件
for 于 big 型update, 考虑分批执行
in update before backup important data

deleteoperationbest practices

始终usingWHERE子句限制delete范围
using主键 or 唯一index列serving asWHERE条件
for 于 big 型delete, 考虑usingTRUNCATE TABLE (SQLite不support, 可usingDELETE FROM)
in delete before backup important data
delete after usingVACUUMcommands回收空间

实践case: usermanagementsystemdataoperation

现 in 让我们through一个practicalcase来练习SQLitedataoperation.

caserequirements

creation一个usermanagementsystem, package含以 under functions:

添加 new user
updateuserinformation
deleteuser
批量importuser

solution

                    -- 1. creationuser表
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER,
    status TEXT DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 2. 添加 new user
INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@example.com', 25);
INSERT INTO users (name, email, age) VALUES ('李四', 'lisi@example.com', 30);

-- 3. updateuserinformation
UPDATE users SET age = 26, updated_at = CURRENT_TIMESTAMP WHERE id = 1;
UPDATE users SET status = 'inactive' WHERE email = 'lisi@example.com';

-- 4. deleteuser
DELETE FROM users WHERE id = 2;

-- 5. 批量importuser
BEGIN TRANSACTION;

INSERT INTO users (name, email, age) VALUES ('王五', 'wangwu@example.com', 28);
INSERT INTO users (name, email, age) VALUES ('赵六', 'zhaoliu@example.com', 32);
INSERT INTO users (name, email, age) VALUES ('孙七', 'sunqi@example.com', 26);
INSERT INTO users (name, email, age) VALUES ('周八', 'zhouba@example.com', 35);
INSERT INTO users (name, email, age) VALUES ('吴九', 'wujiu@example.com', 29);

COMMIT;

-- 6. replaceuserinformation (such as果存 in 则update, 不存 in 则插入) 
REPLACE INTO users (id, name, email, age) VALUES (1, '张三', 'zhangsan_new@example.com', 27);

-- 7. queryoperation结果
SELECT * FROM users;
                

互动练习

请completion以 under dataoperation练习:

creation一个产品表 (products) , package含id, name, price, category, stocketc.字段
插入5个产品data
update价格 low 于100 产品, 将价格improving10%
deletelibrary存 for 0 产品
usingREPLACE语句update一个现 has 产品 information
usingtransaction批量插入10个产品data

提示: using合适 dataclass型 and 约束条件.

本课时summarized

in 本课时in, 我们Learning了SQLite dataoperationtechniques:

usingINSERT语句向表in插入 new data, including插入单行, many 行 and from other表插入
usingUPDATE语句update表in 现 has data, includingupdate单行, many 行 and using子queryupdate
usingDELETE语句 from 表indeletedata, includingdelete单行, many 行 and delete所 has data
usingREPLACE语句插入 or replacedata, processing唯一约束conflict
usingtransactionfor批量operation, improvingefficiency and 保证dataconsistency
Learning了dataoperation best practices, includingsecurity性 and performance考虑
throughpracticalcase练习了各种dataoperationtechniques

SQLitedataoperationtutorial

SQLitedataoperationoverview

SQLitedataoperation basiccommands

插入data (INSERT)

basic语法

usingDEFAULT值

from other表插入data

updatedata (UPDATE)

basic语法

using子queryupdate

update Notes

deletedata (DELETE)

basic语法

delete Notes

replacedata (REPLACE)

basic语法

REPLACE working principles

REPLACE and INSERT OR REPLACE 区别

批量operation

usingtransactionfor批量operation

批量updateexample

dataoperationbest practices

插入operationbest practices

updateoperationbest practices

deleteoperationbest practices

实践case: usermanagementsystemdataoperation

caserequirements

solution

互动练习

本课时summarized

推荐tutorial

tutorial导航

coreknowledge点