====== alaSQL 完全教程 ======

alaSQL 是一个轻量级的 JavaScript SQL 数据库，专为浏览器和 Node.js 设计。它支持标准 SQL 语法，同时提供 JSON 文档存储能力，是处理客户端数据的理想选择。

===== 目录 =====

  * [[#第一章：alaSQL简介与环境搭建|第一章：alaSQL简介与环境搭建]]
  * [[#第二章：基础SQL操作|第二章：基础SQL操作]]
  * [[#第三章：高级查询技术|第三章：高级查询技术]]
  * [[#第四章：数据导入导出|第四章：数据导入导出]]
  * [[#第五章：索引与性能优化|第五章：索引与性能优化]]
  * [[#第六章：浏览器集成与应用|第六章：浏览器集成与应用]]
  * [[#第七章：Node.js服务端开发|第七章：Node.js服务端开发]]
  * [[#第八章：与其他框架集成|第八章：与其他框架集成]]
  * [[#第九章：实战项目案例|第九章：实战项目案例]]
  * [[#第十章：最佳实践与常见问题|第十章：最佳实践与常见问题]]

----

===== 第一章：alaSQL简介与环境搭建 =====

==== 1.1 什么是alaSQL ====

alaSQL（AlaSQL）是一个开源的 JavaScript SQL 数据库，具有以下特点：

  * **纯 JavaScript 实现**：无需外部依赖，可直接在浏览器和 Node.js 中运行
  * **标准 SQL 支持**：支持大部分 SQL-99 标准语法
  * **JSON 友好**：原生支持 JavaScript 对象和 JSON 数据
  * **轻量快速**：核心库仅约 300KB，查询性能优异
  * **跨平台**：支持浏览器、Node.js、Web Workers 和 WebSQL

==== 1.2 适用场景 ====

^ 场景 ^ 说明 ^
| 离线 Web 应用 | 在无网络环境下提供数据存储和查询能力 |
| 数据可视化 | 快速处理大量数据用于图表展示 |
| 原型开发 | 快速搭建无需后端的数据演示项目 |
| 客户端缓存 | 存储用户偏好设置、临时数据等 |
| 数据转换 | CSV、Excel、JSON 等格式之间的转换 |
| 教育学习 | 学习 SQL 语法，无需安装数据库服务器 |

==== 1.3 环境搭建 ====

=== 1.3.1 浏览器环境 ===

<code html>
<!-- 通过 CDN 引入 -->
<script src="https://cdn.jsdelivr.net/npm/alasql@4"></script>

<script>
  // 现在可以使用 alasql 对象了
  alasql("CREATE TABLE users (id INT, name STRING)");
  alasql("INSERT INTO users VALUES (1, '张三'), (2, '李四')");
  var result = alasql("SELECT * FROM users");
  console.log(result);
</script>
</code>

=== 1.3.2 Node.js 环境 ===

<code bash>
# 安装 alaSQL
npm install alasql
</code>

<code javascript>
// 在 Node.js 中使用
const alasql = require('alasql');

// 创建内存数据库
const db = new alasql.Database();

db.exec("CREATE TABLE products (id INT, name STRING, price NUMBER)");
db.exec("INSERT INTO products VALUES (1, 'iPhone', 5999), (2, 'iPad', 3999)");

const results = db.exec("SELECT * FROM products WHERE price > 4000");
console.log(results);
</code>

=== 1.3.3 ES6 模块导入 ===

<code javascript>
// 使用 ES6 模块语法
import alasql from 'alasql';

// 或使用命名导入
import * as alasql from 'alasql';

// 异步查询
async function queryData() {
  const res = await alasql.promise('SELECT * FROM ?', [[{a:1}, {a:2}]]);
  console.log(res);
}
</code>

==== 1.4 第一个alaSQL程序 ====

<code javascript>
// 1. 创建表
alasql("CREATE TABLE students (id INT PRIMARY KEY, name STRING, age INT, score NUMBER)");

// 2. 插入数据
alasql("INSERT INTO students VALUES \
  (1, '小明', 18, 85.5), \
  (2, '小红', 19, 92.0), \
  (3, '小刚', 18, 78.5), \
  (4, '小丽', 20, 88.0)");

// 3. 查询数据
const students = alasql("SELECT * FROM students WHERE score >= 80");
console.log("成绩80分以上的学生:", students);

// 4. 聚合查询
const stats = alasql(`
  SELECT 
    COUNT(*) as total,
    AVG(score) as avg_score,
    MAX(score) as max_score,
    MIN(score) as min_score
  FROM students
`);
console.log("成绩统计:", stats);

// 5. 分组查询
const ageStats = alasql(`
  SELECT age, COUNT(*) as count, AVG(score) as avg_score
  FROM students
  GROUP BY age
  ORDER BY age
`);
console.log("按年龄分组统计:", ageStats);
</code>

===== 第二章：基础SQL操作 =====

==== 2.1 数据定义语言（DDL）====

=== 2.1.1 创建表 ===

<code javascript>
// 基础建表
alasql("CREATE TABLE employees ( \
  id INT PRIMARY KEY, \
  name STRING, \
  department STRING, \
  salary NUMBER, \
  hire_date DATE, \
  is_active BOOLEAN DEFAULT true \
)");

// 使用 IF NOT EXISTS
alasql("CREATE TABLE IF NOT EXISTS employees (...)");

// 从查询结果创建表
alasql("CREATE TABLE high_earners AS \
  SELECT * FROM employees WHERE salary > 10000");
</code>

=== 2.1.2 修改表结构 ====

<code javascript>
// 添加列
alasql("ALTER TABLE employees ADD COLUMN email STRING");

// 删除列
alasql("ALTER TABLE employees DROP COLUMN email");

// 重命名列（alaSQL扩展语法）
alasql("ALTER TABLE employees RENAME COLUMN name TO full_name");
</code>

=== 2.1.3 删除表 ===

<code javascript>
// 删除表
alasql("DROP TABLE employees");

// 安全删除（表不存在不报错）
alasql("DROP TABLE IF EXISTS employees");
</code>

==== 2.2 数据操作语言（DML）====

=== 2.2.1 INSERT 插入数据 ===

<code javascript>
// 单行插入
alasql("INSERT INTO employees VALUES (1, '张三', '技术部', 15000, '2023-01-15', true)");

// 多行插入
alasql(`INSERT INTO employees VALUES 
  (2, '李四', '销售部', 12000, '2023-03-20', true),
  (3, '王五', '技术部', 18000, '2022-11-08', true),
  (4, '赵六', '人事部', 10000, '2023-06-01', false)
`);

// 指定列插入
alasql("INSERT INTO employees (id, name, department) VALUES (5, '钱七', '财务部')");

// 从其他表插入
alasql("INSERT INTO employees_backup SELECT * FROM employees WHERE is_active = true");

// 插入对象数组（JavaScript风格）
const newEmployees = [
  {id: 6, name: '孙八', department: '技术部', salary: 16000},
  {id: 7, name: '周九', department: '销售部', salary: 13000}
];
alasql("INSERT INTO employees VALUES ?", [newEmployees]);
</code>

=== 2.2.2 SELECT 查询数据 ====

<code javascript>
// 基础查询
alasql("SELECT * FROM employees");

// 选择特定列
alasql("SELECT name, salary FROM employees");

// 使用别名
alasql("SELECT name AS 姓名, salary AS 工资 FROM employees");

// 去重
alasql("SELECT DISTINCT department FROM employees");

// 条件查询
alasql("SELECT * FROM employees WHERE salary > 12000");
alasql("SELECT * FROM employees WHERE department = '技术部' AND salary >= 15000");

// 范围查询
alasql("SELECT * FROM employees WHERE salary BETWEEN 10000 AND 15000");
alasql("SELECT * FROM employees WHERE department IN ('技术部', '销售部')");

// 模糊查询
alasql("SELECT * FROM employees WHERE name LIKE '张%'");
alasql("SELECT * FROM employees WHERE name LIKE '%三%'");

// 空值判断
alasql("SELECT * FROM employees WHERE email IS NULL");
alasql("SELECT * FROM employees WHERE email IS NOT NULL");

// 排序
alasql("SELECT * FROM employees ORDER BY salary DESC");
alasql("SELECT * FROM employees ORDER BY department ASC, salary DESC");

// 分页（LIMIT 和 OFFSET）
alasql("SELECT * FROM employees LIMIT 10");
alasql("SELECT * FROM employees LIMIT 10 OFFSET 20");
alasql("SELECT * FROM employees LIMIT 20, 10");  // 简写形式
</code>

=== 2.2.3 UPDATE 更新数据 ====

<code javascript>
// 基础更新
alasql("UPDATE employees SET salary = 20000 WHERE id = 1");

// 更新多列
alasql("UPDATE employees SET salary = 22000, department = '架构部' WHERE id = 1");

// 批量更新
alasql("UPDATE employees SET salary = salary * 1.1 WHERE department = '技术部'");

// 使用表达式
alasql("UPDATE employees SET salary = salary + 1000 WHERE hire_date < '2023-01-01'");
</code>

=== 2.2.4 DELETE 删除数据 ====

<code javascript>
// 条件删除
alasql("DELETE FROM employees WHERE id = 5");

// 批量删除
alasql("DELETE FROM employees WHERE is_active = false");

// 删除所有数据（保留表结构）
alasql("DELETE FROM employees");

// 清空表并重置自增ID（alaSQL扩展）
alasql("TRUNCATE TABLE employees");
</code>

==== 2.3 数据类型 ====

^ 类型 ^ 说明 ^ 示例 ^
| STRING / VARCHAR | 字符串 | 'Hello', "World" |
| NUMBER / FLOAT | 浮点数 | 3.14, -0.5 |
| INT / INTEGER | 整数 | 42, -10 |
| BOOLEAN | 布尔值 | true, false |
| DATE | 日期 | '2024-01-15' |
| DATETIME | 日期时间 | '2024-01-15 09:30:00' |
| OBJECT | JavaScript对象 | {a: 1, b: 2} |
| ARRAY | JavaScript数组 | [1, 2, 3] |

===== 第三章：高级查询技术 =====

==== 3.1 聚合函数 ====

<code javascript>
// 统计函数
alasql("SELECT COUNT(*) FROM employees");
alasql("SELECT COUNT(DISTINCT department) FROM employees");

// 数值计算
alasql("SELECT SUM(salary) FROM employees");
alasql("SELECT AVG(salary) FROM employees");
alasql("SELECT MAX(salary) FROM employees");
alasql("SELECT MIN(salary) FROM employees");

// 组合使用
alasql(`
  SELECT 
    department,
    COUNT(*) as emp_count,
    SUM(salary) as total_salary,
    AVG(salary) as avg_salary,
    MAX(salary) as highest_salary,
    MIN(salary) as lowest_salary
  FROM employees
  GROUP BY department
`);

// HAVING 子句（过滤分组结果）
alasql(`
  SELECT department, AVG(salary) as avg_salary
  FROM employees
  GROUP BY department
  HAVING AVG(salary) > 12000
`);
</code>

==== 3.2 多表查询 ====

=== 3.2.1 内连接（INNER JOIN）===

<code javascript>
// 创建部门表
alasql("CREATE TABLE departments (dept_id STRING, dept_name STRING, location STRING)");
alasql(`INSERT INTO departments VALUES 
  ('技术部', '技术研发中心', 'A座3楼'),
  ('销售部', '销售中心', 'B座1楼'),
  ('人事部', '人力资源部', 'A座2楼')
`);

// 内连接查询
const result = alasql(`
  SELECT 
    e.name,
    e.salary,
    d.dept_name,
    d.location
  FROM employees e
  INNER JOIN departments d ON e.department = d.dept_id
`);
console.log(result);
</code>

=== 3.2.2 外连接（OUTER JOIN）===

<code javascript>
// 左外连接（返回左表所有记录）
alasql(`
  SELECT e.name, d.dept_name
  FROM employees e
  LEFT JOIN departments d ON e.department = d.dept_id
`);

// 右外连接
alasql(`
  SELECT e.name, d.dept_name
  FROM employees e
  RIGHT JOIN departments d ON e.department = d.dept_id
`);

// 全外连接
alasql(`
  SELECT e.name, d.dept_name
  FROM employees e
  OUTER JOIN departments d ON e.department = d.dept_id
`);
</code>

=== 3.2.3 交叉连接（CROSS JOIN）===

<code javascript>
// 笛卡尔积
alasql(`
  SELECT e.name, d.dept_name
  FROM employees e
  CROSS JOIN departments d
`);
</code>

==== 3.3 子查询 ====

<code javascript>
// 单行子查询
alasql(`
  SELECT * FROM employees 
  WHERE salary > (SELECT AVG(salary) FROM employees)
`);

// 多行子查询（IN）
alasql(`
  SELECT * FROM employees 
  WHERE department IN (
    SELECT dept_id FROM departments WHERE location LIKE 'A座%'
  )
`);

// 相关子查询（EXISTS）
alasql(`
  SELECT * FROM employees e
  WHERE EXISTS (
    SELECT 1 FROM departments d 
    WHERE d.dept_id = e.department AND d.location = 'A座3楼'
  )
`);

// 子查询作为派生表
alasql(`
  SELECT dept_name, avg_sal
  FROM (
    SELECT department, AVG(salary) as avg_sal
    FROM employees
    GROUP BY department
  ) t
  JOIN departments d ON t.department = d.dept_id
  WHERE avg_sal > 13000
`);
</code>

==== 3.4 窗口函数 ====

<code javascript>
// ROW_NUMBER() - 行号
alasql(`
  SELECT 
    name,
    salary,
    department,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
  FROM employees
`);

// RANK() - 排名（跳跃）
alasql(`
  SELECT 
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) as rank
  FROM employees
`);

// 分区窗口函数
alasql(`
  SELECT 
    name,
    salary,
    department,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
  FROM employees
`);

// LAG/LEAD - 前后行
alasql(`
  SELECT 
    name,
    salary,
    LAG(salary) OVER (ORDER BY salary) as prev_salary,
    LEAD(salary) OVER (ORDER BY salary) as next_salary
  FROM employees
`);
</code>

===== 第四章：数据导入导出 =====

==== 4.1 从JavaScript对象加载数据 ====

<code javascript>
// 直接查询JavaScript数组
const myData = [
  {id: 1, product: '苹果', price: 5.5, quantity: 100},
  {id: 2, product: '香蕉', price: 3.2, quantity: 200},
  {id: 3, product: '橙子', price: 4.8, quantity: 150}
];

const result = alasql("SELECT product, price * quantity as total FROM ?", [myData]);
console.log(result);

// 将数组导入为表
alasql("CREATE TABLE fruits");
alasql("SELECT * INTO fruits FROM ?", [myData]);
</code>

==== 4.2 CSV文件处理 ====

<code javascript>
// 导出为CSV
alasql("SELECT * FROM employees", [], function(data) {
  alasql.exportCsv(data, 'employees.csv');
});

// 从CSV导入（浏览器环境）
alasql("CREATE TABLE csv_data");
alasql('SELECT * FROM CSV("data.csv")', [], function(data) {
  alasql("INSERT INTO csv_data SELECT * FROM ?", [data]);
});

// Node.js 从文件导入CSV
const fs = require('fs');
const csv = fs.readFileSync('data.csv', 'utf8');
alasql("CREATE TABLE products");
alasql('SELECT * FROM CSV(?)', [csv], function(data) {
  alasql("INSERT INTO products SELECT * FROM ?", [data]);
});
</code>

==== 4.3 Excel文件处理 ====

<code javascript>
// 导出为Excel
alasql("SELECT * FROM employees", [], function(data) {
  alasql.exportXls(data, 'employees.xlsx');
});

// 从Excel导入
alasql('SELECT * FROM XLSX("data.xlsx")', [], function(data) {
  console.log(data);
});
</code>

==== 4.4 JSON数据处理 ====

<code javascript>
// 导出为JSON
const jsonData = alasql("SELECT * FROM employees");
console.log(JSON.stringify(jsonData, null, 2));

// 从JSON导入
const jsonStr = '[{"id":1,"name":"张三"},{"id":2,"name":"李四"}]';
const parsed = JSON.parse(jsonStr);
alasql("CREATE TABLE json_table");
alasql("INSERT INTO json_table SELECT * FROM ?", [parsed]);
</code>

===== 第五章：索引与性能优化 =====

==== 5.1 创建索引 ====

<code javascript>
// 创建普通索引
alasql("CREATE INDEX idx_department ON employees(department)");

// 创建唯一索引
alasql("CREATE UNIQUE INDEX idx_email ON employees(email)");

// 复合索引
alasql("CREATE INDEX idx_dept_salary ON employees(department, salary)");

// 删除索引
alasql("DROP INDEX idx_department");
</code>

==== 5.2 查询优化技巧 ====

<code javascript>
// 1. 只查询需要的列
alasql("SELECT id, name FROM employees");  // 优于 SELECT *

// 2. 使用 LIMIT 限制返回行数
alasql("SELECT * FROM employees LIMIT 100");

// 3. 合理使用索引列作为查询条件
alasql("SELECT * FROM employees WHERE department = '技术部'");

// 4. 避免在索引列上使用函数
// 不推荐：alasql("SELECT * FROM employees WHERE UPPER(name) = '张三'")
// 推荐：在应用层处理或在数据入库时统一格式

// 5. 大数据量时使用分页
alasql("SELECT * FROM employees LIMIT 50 OFFSET 0");   // 第1页
alasql("SELECT * FROM employees LIMIT 50 OFFSET 50");  // 第2页
</code>

===== 第六章：浏览器集成与应用 =====

==== 6.1 在HTML中使用alaSQL ====

<code html>
<!DOCTYPE html>
<html>
<head>
  <title>alaSQL 示例</title>
  <script src="https://cdn.jsdelivr.net/npm/alasql@4"></script>
</head>
<body>
  <h1>员工信息查询</h1>
  
  <div>
    <input type="text" id="searchName" placeholder="输入姓名">
    <button onclick="search()">搜索</button>
  </div>
  
  <table id="resultTable" border="1">
    <thead>
      <tr><th>ID</th><th>姓名</th><th>部门</th><th>工资</th></tr>
    </thead>
    <tbody></tbody>
  </table>

  <script>
    // 初始化数据
    alasql("CREATE TABLE employees (id INT, name STRING, dept STRING, salary INT)");
    alasql(`INSERT INTO employees VALUES
      (1, '张三', '技术部', 15000),
      (2, '李四', '销售部', 12000),
      (3, '王五', '技术部', 18000),
      (4, '赵六', '人事部', 10000)
    `);

    function search() {
      const name = document.getElementById('searchName').value;
      const results = alasql("SELECT * FROM employees WHERE name LIKE ?", ['%' + name + '%']);
      
      const tbody = document.querySelector('#resultTable tbody');
      tbody.innerHTML = results.map(row => `
        <tr>
          <td>${row.id}</td>
          <td>${row.name}</td>
          <td>${row.dept}</td>
          <td>${row.salary}</td>
        </tr>
      `).join('');
    }
  </script>
</body>
</html>
</code>

==== 6.2 本地存储持久化 ====

<code javascript>
// 使用 localStorage 持久化数据（浏览器环境）

// 保存数据库到 localStorage
function saveDatabase() {
  const data = alasql("SELECT * FROM employees");
  localStorage.setItem('employees_db', JSON.stringify(data));
}

// 从 localStorage 恢复数据
function loadDatabase() {
  const saved = localStorage.getItem('employees_db');
  if (saved) {
    const data = JSON.parse(saved);
    alasql("CREATE TABLE IF NOT EXISTS employees");
    alasql("DELETE FROM employees");  // 清空现有数据
    alasql("INSERT INTO employees SELECT * FROM ?", [data]);
  }
}

// IndexedDB 持久化（alaSQL内置支持）
alasql("CREATE INDEXEDDB DATABASE IF NOT EXISTS mydb");
alasql("ATTACH INDEXEDDB DATABASE mydb");
alasql("USE mydb");

// 现在创建的表会自动持久化到 IndexedDB
alasql("CREATE TABLE IF NOT EXISTS employees (id INT PRIMARY KEY, name STRING)");
alasql("INSERT INTO employees VALUES (1, '张三')");
// 刷新页面后数据依然存在
</code>

===== 第七章：Node.js服务端开发 =====

==== 7.1 构建REST API ====

<code javascript>
// server.js
const express = require('express');
const alasql = require('alasql');
const app = express();

app.use(express.json());

// 初始化数据库
alasql("CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY, name STRING, email STRING)");

// GET 所有用户
app.get('/api/users', (req, res) => {
  const users = alasql("SELECT * FROM users");
  res.json(users);
});

// GET 单个用户
app.get('/api/users/:id', (req, res) => {
  const user = alasql("SELECT * FROM users WHERE id = ?", [req.params.id]);
  if (user.length === 0) {
    return res.status(404).json({error: '用户不存在'});
  }
  res.json(user[0]);
});

// POST 创建用户
app.post('/api/users', (req, res) => {
  const {id, name, email} = req.body;
  try {
    alasql("INSERT INTO users VALUES (?, ?, ?)", [id, name, email]);
    res.status(201).json({id, name, email});
  } catch (err) {
    res.status(400).json({error: err.message});
  }
});

// PUT 更新用户
app.put('/api/users/:id', (req, res) => {
  const {name, email} = req.body;
  alasql("UPDATE users SET name = ?, email = ? WHERE id = ?", [name, email, req.params.id]);
  res.json({id: req.params.id, name, email});
});

// DELETE 删除用户
app.delete('/api/users/:id', (req, res) => {
  alasql("DELETE FROM users WHERE id = ?", [req.params.id]);
  res.status(204).send();
});

// 复杂查询端点
app.get('/api/stats', (req, res) => {
  const stats = alasql(`
    SELECT 
      COUNT(*) as total_users,
      SUBSTRING(email, INSTR(email, '@') + 1) as domain,
      COUNT(*) as count
    FROM users
    GROUP BY domain
  `);
  res.json(stats);
});

app.listen(3000, () => {
  console.log('Server running on port 3000');
});
</code>

==== 7.2 文件数据库持久化 ====

<code javascript>
const alasql = require('alasql');
const fs = require('fs');

const DB_FILE = './mydatabase.json';

// 加载数据库
function loadDB() {
  if (fs.existsSync(DB_FILE)) {
    const data = JSON.parse(fs.readFileSync(DB_FILE, 'utf8'));
    for (const [tableName, rows] of Object.entries(data)) {
      alasql(`CREATE TABLE IF NOT EXISTS ${tableName}`);
      alasql(`DELETE FROM ${tableName}`);
      if (rows.length > 0) {
        alasql(`INSERT INTO ${tableName} SELECT * FROM ?`, [rows]);
      }
    }
  }
}

// 保存数据库
function saveDB() {
  const tables = alasql("SHOW TABLES");
  const data = {};
  tables.forEach(t => {
    const tableName = t.tableid;
    data[tableName] = alasql(`SELECT * FROM ${tableName}`);
  });
  fs.writeFileSync(DB_FILE, JSON.stringify(data, null, 2));
}

// 使用示例
loadDB();
alasql("CREATE TABLE IF NOT EXISTS products (id INT, name STRING, price NUMBER)");
alasql("INSERT INTO products VALUES (1, '笔记本电脑', 5999)");
saveDB();
</code>

===== 第八章：与其他框架集成 =====

==== 8.1 与Vue.js集成 ====

<code javascript>
// Vue 3 + alaSQL 示例
import {ref, computed} from 'vue';
import alasql from 'alasql';

export function useDatabase() {
  const data = ref([]);
  
  // 初始化表
  alasql("CREATE TABLE IF NOT EXISTS items (id INT, name STRING, value NUMBER)");
  
  const query = (sql, params = []) => {
    return alasql(sql, params);
  };
  
  const insert = (record) => {
    alasql("INSERT INTO items VALUES ?", [record]);
    refresh();
  };
  
  const refresh = () => {
    data.value = alasql("SELECT * FROM items");
  };
  
  // 计算属性：统计数据
  const stats = computed(() => {
    return alasql(`
      SELECT 
        COUNT(*) as count,
        SUM(value) as total,
        AVG(value) as average
      FROM items
    `)[0];
  });
  
  return {data, query, insert, stats, refresh};
}
</code>

==== 8.2 与React集成 ====

<code jsx>
// React Hook for alaSQL
import {useState, useCallback, useMemo} from 'react';
import alasql from 'alasql';

export function useAlaSQL(tableName, initialData = []) {
  const [version, setVersion] = useState(0);
  
  // 初始化表
  useMemo(() => {
    alasql(`CREATE TABLE IF NOT EXISTS ${tableName}`);
    if (initialData.length > 0) {
      alasql(`INSERT INTO ${tableName} SELECT * FROM ?`, [initialData]);
    }
  }, [tableName]);
  
  // 查询
  const query = useCallback((sql, params = []) => {
    return alasql(sql, params);
  }, []);
  
  // 获取所有数据
  const getAll = useCallback(() => {
    return alasql(`SELECT * FROM ${tableName}`);
  }, [tableName, version]);
  
  // 插入
  const insert = useCallback((record) => {
    alasql(`INSERT INTO ${tableName} VALUES ?`, [record]);
    setVersion(v => v + 1);
  }, [tableName]);
  
  // 更新
  const update = useCallback((setClause, whereClause) => {
    alasql(`UPDATE ${tableName} SET ${setClause} WHERE ${whereClause}`);
    setVersion(v => v + 1);
  }, [tableName]);
  
  // 删除
  const remove = useCallback((whereClause) => {
    alasql(`DELETE FROM ${tableName} WHERE ${whereClause}`);
    setVersion(v => v + 1);
  }, [tableName]);
  
  return {query, getAll, insert, update, remove};
}
</code>

===== 第九章：实战项目案例 =====

==== 9.1 项目一：个人记账本 ====

<code javascript>
// 个人记账本应用
class AccountBook {
  constructor() {
    alasql("CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, name STRING, type STRING)");
    alasql("CREATE TABLE IF NOT EXISTS transactions (id INT PRIMARY KEY, date DATE, account_id INT, category STRING, amount NUMBER, note STRING)");
  }
  
  // 添加账户
  addAccount(id, name, type) {
    alasql("INSERT INTO accounts VALUES (?, ?, ?)", [id, name, type]);
  }
  
  // 记录交易
  addTransaction(id, date, accountId, category, amount, note) {
    alasql("INSERT INTO transactions VALUES (?, ?, ?, ?, ?, ?)", [id, date, accountId, category, amount, note]);
  }
  
  // 月度收支统计
  getMonthlyReport(year, month) {
    return alasql(`
      SELECT 
        category,
        SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) as income,
        SUM(CASE WHEN amount < 0 THEN -amount ELSE 0 END) as expense
      FROM transactions
      WHERE YEAR(date) = ? AND MONTH(date) = ?
      GROUP BY category
    `, [year, month]);
  }
  
  // 账户余额
  getAccountBalance(accountId) {
    return alasql("SELECT SUM(amount) as balance FROM transactions WHERE account_id = ?", [accountId])[0].balance || 0;
  }
}

// 使用示例
const book = new AccountBook();
book.addAccount(1, '现金', 'asset');
book.addAccount(2, '银行卡', 'asset');
book.addTransaction(1, '2024-01-15', 1, '工资', 10000, '1月工资');
book.addTransaction(2, '2024-01-16', 1, '餐饮', -150, '午餐');
book.addTransaction(3, '2024-01-17', 2, '交通', -50, '地铁');

console.log(book.getMonthlyReport(2024, 1));
console.log('现金余额:', book.getAccountBalance(1));
</code>

==== 9.2 项目二：数据可视化仪表板 ====

<code javascript>
// 销售数据仪表板
class SalesDashboard {
  constructor() {
    alasql("CREATE TABLE IF NOT EXISTS sales (id INT, product STRING, region STRING, date DATE, amount NUMBER, quantity INT)");
  }
  
  loadData(salesData) {
    alasql("DELETE FROM sales");
    alasql("INSERT INTO sales SELECT * FROM ?", [salesData]);
  }
  
  // 区域销售排名
  getRegionRanking() {
    return alasql(`
      SELECT region, SUM(amount) as total_sales, SUM(quantity) as total_qty
      FROM sales
      GROUP BY region
      ORDER BY total_sales DESC
    `);
  }
  
  // 产品销售趋势（按月）
  getProductTrend(product) {
    return alasql(`
      SELECT 
        YEAR(date) as year,
        MONTH(date) as month,
        SUM(amount) as monthly_sales
      FROM sales
      WHERE product = ?
      GROUP BY YEAR(date), MONTH(date)
      ORDER BY year, month
    `, [product]);
  }
  
  // TOP 10 产品
  getTopProducts(limit = 10) {
    return alasql(`
      SELECT product, SUM(amount) as total_sales
      FROM sales
      GROUP BY product
      ORDER BY total_sales DESC
      LIMIT ?
    `, [limit]);
  }
  
  // 同比增长率
  getYoYGrowth(year) {
    return alasql(`
      SELECT 
        t1.product,
        t1.this_year,
        t2.last_year,
        ROUND((t1.this_year - t2.last_year) / t2.last_year * 100, 2) as growth_rate
      FROM (
        SELECT product, SUM(amount) as this_year
        FROM sales WHERE YEAR(date) = ?
        GROUP BY product
      ) t1
      LEFT JOIN (
        SELECT product, SUM(amount) as last_year
        FROM sales WHERE YEAR(date) = ? - 1
        GROUP BY product
      ) t2 ON t1.product = t2.product
    `, [year, year]);
  }
}
</code>

===== 第十章：最佳实践与常见问题 =====

==== 10.1 最佳实践 ====

  - **合理设计表结构**：根据查询需求设计索引，避免过多的列
  - **批量操作**：尽量使用多行插入代替多次单行插入
  - **参数化查询**：使用参数化查询防止 SQL 注入
  - **及时清理**：不需要的数据及时清理，避免内存占用过高
  - **错误处理**：使用 try-catch 包裹数据库操作

==== 10.2 常见问题 ====

=== Q: alaSQL 支持事务吗？ ===

alaSQL 当前版本对事务的支持有限，主要用于单用户场景。如果需要复杂事务，建议使用 SQLite 或其他数据库。

=== Q: 如何处理大量数据？ ===

对于超过 10 万条记录的数据集，建议：
  - 使用分页查询
  - 添加合适的索引
  - 考虑使用 Web Workers 避免阻塞主线程
  - 数据分片处理

=== Q: 如何在生产环境使用？ ===

  - 浏览器环境：适合离线应用、数据可视化、原型开发
  - Node.js 环境：适合中小型应用、数据转换、测试环境
  - 高并发场景：建议使用 PostgreSQL、MySQL 等传统数据库

=== Q: alaSQL 和 SQL.js 的区别？ ===

^ 特性 ^ alaSQL ^ SQL.js ^
| 架构 | 纯 JavaScript 实现 | SQLite 编译为 WebAssembly |
| 体积 | 约 300KB | 约 1MB+ |
| SQL 兼容性 | 部分 SQL-99 | 完整 SQLite 语法 |
| JSON 支持 | 原生支持 | 需转换 |
| 浏览器支持 | 所有现代浏览器 | 支持 WASM 的浏览器 |

==== 10.3 错误处理 ====

<code javascript>
try {
  alasql("CREATE TABLE test (id INT PRIMARY KEY, name STRING)");
  alasql("INSERT INTO test VALUES (1, 'Alice')");
  alasql("INSERT INTO test VALUES (1, 'Bob')");  // 主键冲突，会报错
} catch (err) {
  console.error('数据库错误:', err.message);
}

// 使用 promise 风格处理异步操作
alasql.promise('SELECT * FROM test')
  .then(result => console.log(result))
  .catch(err => console.error('查询失败:', err));
</code>

----