求适配jQuery Mobile+PhoneGap的SQLite基础操作完整脚本

Hey there! Since you’ve got solid experience with MySQL and MS SQL, you’ll pick up SQLite in no time—most of the syntax is identical, with just a few tiny quirks to note. Let’s walk through a complete set of basic operations that fit perfectly with your kid’s exercise score diary app, from creating the database to running those classic queries you need.

1. 创建/打开数据库（命令行操作） #

SQLite is file-based, so opening a database is the same as creating it if it doesn’t exist. Fire up your sqlite3.exe and run:

-- 打开当前目录下的exercise_scores.db（不存在则创建）
.open exercise_scores.db

-- 或者指定完整路径，比如：
-- .open C:\your-project-folder\exercise_scores.db

2. 创建评分记录表 #

First, let’s make a table tailored to your app’s needs. This includes fields for the child’s name, exercise type, score, date, and notes:

CREATE TABLE IF NOT EXISTS exercise_records (
    record_id INTEGER PRIMARY KEY AUTOINCREMENT,  -- 自增主键，唯一标识每条记录
    child_name TEXT NOT NULL,                     -- 儿童姓名（必填）
    exercise_type TEXT NOT NULL,                  -- 锻炼类型（比如跳绳、跳远，必填）
    score INTEGER NOT NULL,                       -- 成绩/评分（必填，比如跳绳次数、跳远厘米数）
    record_date DATE DEFAULT (DATE('now')),       -- 记录日期，默认自动设为当天
    notes TEXT                                    -- 备注（可选，比如表现评价）
);

The IF NOT EXISTS clause lets you run this script multiple times without errors—perfect for testing.

3. 插入记录（新增评分） #

Add single or multiple records with these commands:

插入单条记录 #

INSERT INTO exercise_records (child_name, exercise_type, score, notes)
VALUES ('小明', '跳绳', 120, '今天跳得很认真，比昨天多20个');

批量插入多条记录 #

INSERT INTO exercise_records (child_name, exercise_type, score, notes)
VALUES 
('小红', '跳远', 150, '第一次跳这么远，超棒！'),
('小明', '跳远', 140, '落地有点不稳，下次注意姿势'),
('小红', '跳绳', 105, '速度还可以，继续加油');

4. 更新记录（修改评分或信息） #

Use UPDATE to tweak existing records. Always try to use the record_id (primary key) for accuracy, but you can also filter by other fields:

通过主键更新（最安全，唯一匹配） #

UPDATE exercise_records
SET notes = '补充：当天是体育课测试'
WHERE record_id = 1;

通过其他条件更新 #

UPDATE exercise_records
SET score = 130, notes = '重新测试后成绩更新为130'
WHERE child_name = '小明' AND exercise_type = '跳绳' AND record_date = '2024-05-20';

5. 删除记录 #

Be careful with DELETE—always add a WHERE clause unless you want to empty the entire table!

删除单条记录（用主键） #

DELETE FROM exercise_records
WHERE record_id = 3;

删除某个儿童的所有记录 #

DELETE FROM exercise_records
WHERE child_name = '小红';

清空整个表（谨慎使用！） #

DELETE FROM exercise_records;
-- 若要重置自增主键的计数，加上这条：
DELETE FROM sqlite_sequence WHERE name = 'exercise_records';

6. 经典查询操作 #

Here are the queries you’ll likely use most often for your app:

查询所有记录 #

SELECT * FROM exercise_records;

查询特定锻炼类型的高分记录（比如跳绳成绩>110的） #

SELECT child_name, score, record_date, notes
FROM exercise_records
WHERE exercise_type = '跳绳' AND score > 110
ORDER BY score DESC;  -- 按成绩从高到低排序

查询某个儿童的所有锻炼记录（按日期倒序） #

SELECT exercise_type, score, record_date, notes
FROM exercise_records
WHERE child_name = '小明'
ORDER BY record_date DESC;

统计每个儿童的平均成绩（按锻炼类型分组） #

SELECT child_name, exercise_type, AVG(score) AS average_score
FROM exercise_records
GROUP BY child_name, exercise_type;

查询最近7天的记录 #

SELECT * FROM exercise_records
WHERE record_date >= DATE('now', '-7 days');

命令行实用辅助命令 #

In sqlite3.exe, these commands will make testing easier:

• .tables：List all tables in the current database
• .schema exercise_records：View the structure of your table
• .mode column：Format query results into aligned columns
• .header on：Show column names in query results
• .exit：Quit the SQLite command line

Quick Tip for Your jQuery Mobile + PhoneGap Project #

When you move these operations to your app, you won’t use the sqlite3.exe command line—instead, use the Cordova SQLite plugin (cordova-sqlite-storage). The SQL statements stay exactly the same, but you’ll wrap them in JavaScript code like this:

// 打开数据库
var db = window.sqlitePlugin.openDatabase({name: 'exercise_scores.db', location: 'default'});

// 执行创建表的操作
db.transaction(function(tx) {
  tx.executeSql('CREATE TABLE IF NOT EXISTS exercise_records (...)');
});

Test all your SQL queries in the command line first, then port them to your JS code—it’ll save you time debugging!

内容的提问来源于stack exchange，提问作者Isma