App Inventor 2 SQLite数据库教程 - 本地数据存储与查询

当 Screen1.初始化
  调用 SQLite1.打开数据库("myapp.db")
  
  ' 创建用户表
  设 建表SQL = "CREATE TABLE IF NOT EXISTS users (" &
    "id INTEGER PRIMARY KEY AUTOINCREMENT, " &
    "name TEXT NOT NULL, " &
    "phone TEXT, " &
    "email TEXT, " &
    "created_at DATETIME DEFAULT CURRENT_TIMESTAMP)"
  调用 SQLite1.执行SQL(建表SQL)

当 按钮_添加用户.被点击
  设 SQL = "INSERT INTO users (name, phone, email) VALUES ('" &
    输入框_姓名.文本 & "', '" &
    输入框_电话.文本 & "', '" &
    输入框_邮箱.文本 & "')"
  调用 SQLite1.执行SQL(SQL)
  标签_状态.文本 = "✅ 用户已添加"

当 按钮_安全添加.被点击
  设 SQL = "INSERT INTO users (name, phone, email) VALUES (?, ?, ?)"
  调用 SQLite1.执行参数化SQL(SQL, 
    [输入框_姓名.文本, 输入框_电话.文本, 输入框_邮箱.文本])

当 按钮_查询全部.被点击
  调用 SQLite1.查询SQL("SELECT * FROM users ORDER BY created_at DESC")

当 SQLite1.查询结果(结果)
  ' 结果是列表，每项是一个字典
  设 显示 = ""
  设 i = 1
  当 i ≤ 列表长度(结果)
    设 记录 = 列表第i项(结果)
    设 显示 = 显示 & 
      从字典 记录 获取 "name" & " | " &
      从字典 记录 获取 "phone" & " | " &
      从字典 记录 获取 "email" & "\n"
    设 i = i + 1
  标签_用户列表.文本 = 显示

当 按钮_搜索.被点击
  设 SQL = "SELECT * FROM users WHERE name LIKE '%" & 输入框_搜索.文本 & "%'"
  调用 SQLite1.查询SQL(SQL)

当 按钮_更新.被点击
  设 SQL = "UPDATE users SET phone = '" & 输入框_新电话.文本 & 
    "' WHERE name = '" & 输入框_姓名.文本 & "'"
  调用 SQLite1.执行SQL(SQL)

当 按钮_删除.被点击
  设 SQL = "DELETE FROM users WHERE id = " & 选中ID
  调用 SQLite1.执行SQL(SQL)

当 Screen1.初始化
  调用 SQLite1.打开数据库("school.db")
  
  调用 SQLite1.执行SQL(
    "CREATE TABLE IF NOT EXISTS students (" &
    "id INTEGER PRIMARY KEY AUTOINCREMENT, " &
    "name TEXT NOT NULL, " &
    "class TEXT, " &
    "student_no TEXT UNIQUE)")
  
  调用 SQLite1.执行SQL(
    "CREATE TABLE IF NOT EXISTS scores (" &
    "id INTEGER PRIMARY KEY AUTOINCREMENT, " &
    "student_id INTEGER, " &
    "subject TEXT, " &
    "score REAL, " &
    "exam_date TEXT, " &
    "FOREIGN KEY(student_id) REFERENCES students(id))")

当 按钮_录入.被点击
  ' 先查找学生ID
  设 查询SQL = "SELECT id FROM students WHERE name = '" & 输入框_学生.文本 & "'"
  调用 SQLite1.查询SQL(查询SQL)

当 SQLite1.查询结果(结果)
  如果 列表长度(结果) > 0
    设 学生ID = 从字典 列表第1项(结果) 获取 "id"
    设 录入SQL = "INSERT INTO scores (student_id, subject, score, exam_date) VALUES (" &
      学生ID & ", '" & 下拉框_科目.选中项 & "', " & 
      输入框_分数.文本 & ", '" &
      Clock1.格式化日期时间(Clock1.当前时间(), "yyyy-MM-dd") & "')"
    调用 SQLite1.执行SQL(录入SQL)
    标签_状态.文本 = "✅ 成绩已录入"

' 查询某学生所有成绩
设 SQL = "SELECT s.name, sc.subject, sc.score, sc.exam_date " &
  "FROM scores sc JOIN students s ON sc.student_id = s.id " &
  "WHERE s.name = '" & 输入框_学生.文本 & "' " &
  "ORDER BY sc.exam_date DESC"

' 查询某科目平均分
设 SQL = "SELECT subject, AVG(score) as avg_score, MAX(score) as max_score, " &
  "MIN(score) as min_score, COUNT(*) as count " &
  "FROM scores WHERE subject = '" & 下拉框_科目.选中项 & "' " &
  "GROUP BY subject"

' 班级成绩排名
设 SQL = "SELECT s.name, s.class, SUM(sc.score) as total " &
  "FROM scores sc JOIN students s ON sc.student_id = s.id " &
  "GROUP BY sc.student_id ORDER BY total DESC"

当 SQLite1.查询结果(结果)
  设 显示列表 = 创建空列表
  设 i = 1
  当 i ≤ 列表长度(结果)
    设 记录 = 列表第i项(结果)
    设 显示文本 = 从字典 记录 获取 "name" & " | " &
      从字典 记录 获取 "subject" & " | " &
      从字典 记录 获取 "score" & "分"
    设 显示列表 = 添加列表项(显示列表, 显示文本)
    设 i = i + 1
  设 ListView1.元素 = 显示列表

当 按钮_导出.被点击
  调用 SQLite1.查询SQL("SELECT * FROM users")
  
当 SQLite1.查询结果(结果)
  设 CSV = "id,name,phone,email\n"
  设 i = 1
  当 i ≤ 列表长度(结果)
    设 r = 列表第i项(结果)
    设 CSV = CSV & 从字典r获取"id" & "," & 
      从字典r获取"name" & "," &
      从字典r获取"phone" & "," &
      从字典r获取"email" & "\n"
    设 i = i + 1
  调用 File1.保存文件(CSV, "/sdcard/MyApp/export.csv")