alaSQL 完全教程

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

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

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

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

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

1.3.1 浏览器环境

<!-- 通过 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>

1.3.2 Node.js 环境

# 安装 alaSQL
npm install alasql

// 在 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);

1.3.3 ES6 模块导入

// 使用 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);
}

// 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);

2.1.1 创建表

// 基础建表
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");

2.1.2 修改表结构

// 添加列
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");

2.1.3 删除表

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

2.2.1 INSERT 插入数据

// 单行插入
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]);

2.2.2 SELECT 查询数据

// 基础查询
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");  // 简写形式

2.2.3 UPDATE 更新数据

// 基础更新
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'");

2.2.4 DELETE 删除数据

// 条件删除
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");

类型	说明	示例
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]

// 统计函数
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
`);

3.2.1 内连接（INNER JOIN）

// 创建部门表
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);

3.2.2 外连接（OUTER JOIN）

// 左外连接（返回左表所有记录）
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
`);

3.2.3 交叉连接（CROSS JOIN）

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

// 单行子查询
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
`);

// 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
`);

// 直接查询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]);

// 导出为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]);
});

// 导出为Excel
alasql("SELECT * FROM employees", [], function(data) {
  alasql.exportXls(data, 'employees.xlsx');
});
 
// 从Excel导入
alasql('SELECT * FROM XLSX("data.xlsx")', [], function(data) {
  console.log(data);
});

// 导出为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]);

// 创建普通索引
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");

// 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页

<!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>

// 使用 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, '张三')");
// 刷新页面后数据依然存在

// 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');
});

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();

// 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};
}

// 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};
}

// 个人记账本应用
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));

// 销售数据仪表板
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]);
  }
}

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

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 的浏览器

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));

Detach Close

您访问的页面并不存在。如果允许，您可以使用创建该页面按钮来创建它。

alaSQL 完全教程

目录

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

1.1 什么是alaSQL

1.2 适用场景

1.3 环境搭建

1.3.1 浏览器环境

1.3.2 Node.js 环境

1.3.3 ES6 模块导入

1.4 第一个alaSQL程序

第二章：基础SQL操作

2.1 数据定义语言（DDL）

2.1.1 创建表

2.1.2 修改表结构

2.1.3 删除表

2.2 数据操作语言（DML）

2.2.1 INSERT 插入数据

2.2.2 SELECT 查询数据

2.2.3 UPDATE 更新数据

2.2.4 DELETE 删除数据

2.3 数据类型

第三章：高级查询技术

3.1 聚合函数

3.2 多表查询

3.2.1 内连接（INNER JOIN）

3.2.2 外连接（OUTER JOIN）

3.2.3 交叉连接（CROSS JOIN）

3.3 子查询

3.4 窗口函数

第四章：数据导入导出

4.1 从JavaScript对象加载数据

4.2 CSV文件处理

4.3 Excel文件处理

4.4 JSON数据处理

第五章：索引与性能优化

5.1 创建索引

5.2 查询优化技巧

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

6.1 在HTML中使用alaSQL

6.2 本地存储持久化

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

7.1 构建REST API

7.2 文件数据库持久化

第八章：与其他框架集成

8.1 与Vue.js集成

8.2 与React集成

第九章：实战项目案例

9.1 项目一：个人记账本

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

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

10.1 最佳实践

10.2 常见问题

Q: alaSQL 支持事务吗？

Q: 如何处理大量数据？

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

Q: alaSQL 和 SQL.js 的区别？

10.3 错误处理

该主题尚不存在