数据库和后端

mac 中有自带的数据库

SQLite教程：在Mac中使用SQLite

一、SQLite简介

SQLite 是一个轻量级的嵌入式数据库，它是无服务器、零配置、事务性的，适合小型到中型应用程序。由于其简单性和自包含，SQLite 常用于开发过程中作为轻量级的数据库选择。

二、在Mac上安装和使用SQLite

Mac 系统通常自带 SQLite，因此无需额外安装。你可以直接在终端中使用以下命令查看是否已经安装：

sqlite3 --version

如果输出 SQLite 的版本号，说明已经安装。否则可以通过 brew 进行安装：

brew install sqlite

三、使用SQLite数据库

创建并打开数据库
打开终端，使用 sqlite3 命令创建或打开一个数据库：
```
sqlite3 mydatabase.db
```
这将会创建（如果文件不存在）或打开名为 mydatabase.db 的数据库。

基本的SQL操作

创建表：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
);

插入数据：

INSERT INTO users (name, age) VALUES ('Alice', 30);

查询数据：
```
SELECT * FROM users;
```

更新数据：

UPDATE users SET age = 31 WHERE name = 'Alice';

删除数据：
```
DELETE FROM users WHERE name = 'Alice';
```

导入和导出数据
- 导出数据库为SQL文件：
```
sqlite3 mydatabase.db .dump > backup.sql
```
- 导入SQL文件到数据库：
```
sqlite3 mydatabase.db < backup.sql
```
退出SQLite命令行
使用 .exit 或按 Ctrl+D 来退出：
```
.exit
```

四、可视化IDE工具

尽管SQLite可以通过终端操作，但可视化工具能够更直观地管理和查询数据。以下是几个适合Mac的SQLite可视化工具：

DB Browser for SQLite
- 免费开源，支持SQLite数据库的创建、设计、查询和数据管理。
- 下载地址：DB Browser for SQLite
SQLiteStudio
- 免费开源，功能丰富的SQLite数据库管理工具，支持拖拽式表格设计和查询构建。
- 下载地址：SQLiteStudio
DataGrip（JetBrains）
- 强大的付费数据库管理工具，支持多种数据库，包括SQLite。
- 下载地址：DataGrip

五、SQLite知识体系框架

数据类型：
SQLite 使用动态类型，它只有五种基础存储类型：
- NULL：空值
- INTEGER：有符号整数
- REAL：浮点数
- TEXT：字符串
- BLOB：二进制数据
约束条件：
SQLite 支持以下约束：
- PRIMARY KEY：主键
- UNIQUE：唯一约束
- NOT NULL：不允许空值
- CHECK：检查约束
- FOREIGN KEY：外键
索引：
索引用于加速查询，可以通过以下语法创建索引：
```
CREATE INDEX idx_name ON users (name);
```
事务处理：
SQLite 支持ACID事务。典型的事务流程如下：
```
BEGIN TRANSACTION;
-- 执行SQL操作
COMMIT; -- 或 ROLLBACK;
```

视图：
视图是基于SQL查询的虚拟表：

CREATE VIEW user_names AS
SELECT name FROM users;

触发器：
触发器是自动执行的数据库操作：

CREATE TRIGGER update_timestamp
AFTER UPDATE ON users
BEGIN
    UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

SQLite扩展：
SQLite支持多种扩展，例如 FTS3/4（全文搜索）， JSON1 扩展用于处理JSON数据。

六 SQLite 常用命令

在使用 SQLite 时，常用的 sqlite3 命令可以帮助你管理和操作数据库。以下是一些常用的 SQLite 命令：

1. 基本数据库操作

打开数据库：
```
sqlite3 mydatabase.db
```
如果数据库不存在，会创建一个新的数据库。
退出SQLite：
```
.exit
```
或者按 Ctrl+D。
显示当前数据库中的表：
```
.tables
```
显示表结构：查看某张表的列及其类型：
```
.schema table_name
```
显示所有表的结构：
```
.schema
```
导出数据库（备份）：导出数据库结构和数据到SQL文件：
```
.output backup.sql
.dump
.exit
```
导入数据库：从SQL文件中导入数据：
```
.read backup.sql
```

2. 数据操作

创建表：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
);

插入数据：

INSERT INTO users (name, age) VALUES ('Alice', 30);

查询数据：
```
SELECT * FROM users;
```

更新数据：

UPDATE users SET age = 31 WHERE name = 'Alice';

删除数据：
```
DELETE FROM users WHERE name = 'Alice';
```

3. 辅助命令

显示数据库中的索引：
```
.indexes
```
开启/关闭列名输出：开启列名输出：
```
.headers on
```
关闭列名输出：
```
.headers off
```
设置分隔符：用于设置查询结果输出时的分隔符：
```
.separator ","
```
显示当前行号：
```
.mode column
```
计时执行SQL语句：
```
.timer on
```
查看数据库文件信息：显示有关数据库的元数据：
```
PRAGMA database_list;
```

4. 事务和备份

开启事务：
```
BEGIN TRANSACTION;
```
提交事务：
```
COMMIT;
```
回滚事务：
```
ROLLBACK;
```

5. 格式化和调试命令

查询结果输出到文件：

.output result.txt
SELECT * FROM users;
.output stdout

调试SQL语句：打印每条 SQL 语句的执行计划：
```
EXPLAIN QUERY PLAN SELECT * FROM users WHERE id = 1;
```
统计数据库的页数和大小：
```
PRAGMA page_count;
PRAGMA page_size;
```

6. 其他有用命令

删除表：
```
DROP TABLE users;
```
删除数据库：直接删除 .db 文件即可，例如：
```
rm mydatabase.db
```
列出所有可用命令：
```
.help
```

七上传文件到数据库

在 SQLite 中，将本地文件的数据上传到数据库通常是通过将文件中的数据导入到表中完成的。常见的文件格式有 CSV、SQL 等。在 macOS 上，你可以使用 sqlite3 命令行工具将本地文件中的数据上传到 SQLite 数据库。

步骤：将 CSV 文件导入 SQLite

准备 CSV 文件：假设你有一个 CSV 文件 data.csv，内容如下：
```
id,name,age
1,Alice,30
2,Bob,25
3,Charlie,35
```
创建目标表：打开 sqlite3 并创建一个数据库和表来接收 CSV 文件中的数据。
```
sqlite3 mydatabase.db
```
在 SQLite 中创建一个与 CSV 文件列匹配的表：
```
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
);
```
导入 CSV 文件：使用 .import 命令将 CSV 文件的数据导入到 SQLite 表中。
```
.mode csv
.import /path/to/data.csv users
```
- 解释：
  - .mode csv：设置 CSV 文件为输入模式。
  - .import /path/to/data.csv users：将指定的 CSV 文件导入到 users 表中。请将 /path/to/data.csv 替换为你的文件路径。
验证导入结果：导入完成后，你可以通过 SQL 查询检查数据是否成功导入：
```
SELECT * FROM users;
```

步骤：将 SQL 文件导入 SQLite

如果你有一个 SQL 文件（例如 backup.sql），并希望将其导入到 SQLite 数据库中，步骤如下：

打开 SQLite 数据库：
```
sqlite3 mydatabase.db
```
导入 SQL 文件：使用 .read 命令将 SQL 文件中的内容导入到数据库：
```
.read /path/to/backup.sql
```
验证导入结果：可以通过查询表数据来验证 SQL 文件的执行是否成功：
```
SELECT * FROM some_table;
```

提示

如果你的 CSV 文件包含标题行（如列名），SQLite 会自动跳过第一行并从第二行开始导入数据。
如果在导入过程中发生错误，确保 CSV 文件的列数与表的列数匹配。
对于大文件，可以考虑分批导入数据，以避免超出系统内存。

通过上述步骤，你可以轻松地将本地文件的数据导入到 SQLite 数据库中进行管理和分析。

后端与数据库交互

创建一个简单的Express服务器：在你的项目文件夹中创建一个 app.js 文件，内容如下：

const express = require('express');
const app = express();
const sqlite3 = require('sqlite3').verbose();
const port = 4000;

app.get('/', (req, res) => {
  res.send('Hello, World!');
});

app.listen(port, () => {
  console.log(`Server running at http://localhost:${port}`);
});

连接SQLite数据库：

安装SQLite 库

npm install sqlite3

在 app.js 文件中，添加与数据库的连接：

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

db.serialize(() => {
  db.run("CREATE TABLE user (id INT, name TEXT)");
  db.run("INSERT INTO user (id, name) VALUES (1, 'John Doe')");
});

app.get('/users', (req, res) => {
  db.all("SELECT * FROM user", [], (err, rows) => {
    if (err) {
      throw err;
    }
    res.json(rows);
  });
});

// 查询 iris 表的所有数据接口
app.get('/iris', (req, res) => {
    const query = 'SELECT * FROM iris';
    
    db.all(query, [], (err, rows) => {
        if (err) {
            res.status(500).json({ error: err.message });
            return;
        }
        res.json({
            message: 'success',
            data: rows
        });
    });
});

// 关闭数据库连接
process.on('SIGINT', () => {
    db.close(() => {
        console.log('Database connection closed.');
        process.exit(0);
    });
});

在终端中运行：

node app.js

打开浏览器，访问 http://localhost:4000/ 查看简单的Hello World。访问 http://localhost:4000/users 来查看数据库中的用户信息。 http://localhost:4000/iris 访问获取的数据集

SQLite 和其他数据库的差别

是的，链接 SQLite 的方式与其他数据库（如 MySQL、PostgreSQL、MongoDB 等）有一些差异。主要差异体现在以下几个方面：

1. 数据库连接方式

SQLite：是一个文件级的数据库，它不需要启动一个单独的数据库服务。连接 SQLite 时，直接使用文件路径连接到数据库，这使得它非常轻量和便携。 javascript const sqlite3 = require('sqlite3').verbose(); const db = new sqlite3.Database('./path/to/your/database.db'); // 直接连接到数据库文件
其他数据库（如 MySQL、PostgreSQL）通常运行在服务端，需要提供连接参数，如主机地址、端口号、用户名、密码等： javascript const mysql = require('mysql'); const connection = mysql.createConnection({ host: 'localhost', user: 'your_username', password: 'your_password', database: 'your_database' });

2. 数据库服务器

SQLite：是一个嵌入式数据库，不需要单独的数据库服务器进程。数据库直接保存在本地文件中。
其他数据库：MySQL、PostgreSQL 等是客户端-服务器（Client-Server）架构，通常在一个服务器进程中运行，需要客户端通过网络协议连接到数据库。

3. 并发处理

SQLite：由于是一个文件级数据库，并发写入操作是受限的，它通过文件锁来避免数据损坏，因此不太适合高并发的场景。
其他数据库：MySQL、PostgreSQL 等设计用于处理多个客户端的并发请求，提供了复杂的并发控制机制，如事务、锁等，适合高并发、高流量的场景。

4. 使用场景

SQLite：通常适合嵌入式应用、小型项目、移动应用或单用户应用，特别是在数据量不大、并发要求不高的情况下。它的优点是零配置、易部署。
其他数据库：如 MySQL、PostgreSQL 等适合大规模、多用户应用，特别是在数据量大、并发高的情况下，适合用于 web 应用的后端系统、大型数据分析项目等。

5. SQL 语法支持

SQLite：支持大部分标准的 SQL 语法，但它并不支持所有的 SQL 特性。例如，SQLite 不支持完整的外键约束、触发器和存储过程的功能有限。
其他数据库：如 MySQL、PostgreSQL 支持更完整的 SQL 特性，例如事务管理、存储过程、复杂的查询优化等。

6. 连接池

SQLite：通常不需要连接池，因为它是单进程模式，所有的操作在同一个文件上进行，且不支持真正的并发写入。
其他数据库：如 MySQL、PostgreSQL 可以通过连接池来管理多个数据库连接，提升并发处理能力和资源利用率。

7. 安装与配置

SQLite：不需要复杂的安装过程，SQLite 通常是一个单一的二进制文件，直接使用即可。数据库文件保存在本地文件系统中。
其他数据库：需要先安装并配置数据库服务，并且通常要配置用户权限、端口、连接池等。

示例对比

SQLite 连接示例：

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./path/to/your/database.db');

// 查询数据
db.all('SELECT * FROM iris', [], (err, rows) => {
    if (err) {
        throw err;
    }
    console.log(rows);
});

db.close();

MySQL 连接示例：

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'your_user',
  password: 'your_password',
  database: 'your_database'
});

connection.connect();

connection.query('SELECT * FROM iris', (error, results, fields) => {
  if (error) throw error;
  console.log(results);
});

connection.end();

简单的数据库和后端

米霖

2024-09-18

数据库和后端

SQLite教程：在Mac中使用SQLite

一、SQLite简介

二、在Mac上安装和使用SQLite

三、使用SQLite数据库

四、可视化IDE工具

五、SQLite知识体系框架

六 SQLite 常用命令

1. 基本数据库操作

2. 数据操作

3. 辅助命令

4. 事务和备份

5. 格式化和调试命令

6. 其他有用命令

七上传文件到数据库

步骤：将 CSV 文件导入 SQLite

步骤：将 SQL 文件导入 SQLite

提示

后端与数据库交互

SQLite 和其他数据库的差别

1. 数据库连接方式

2. 数据库服务器

3. 并发处理

4. 使用场景

5. SQL 语法支持

6. 连接池

7. 安装与配置

示例对比

数据库和后端

SQLite教程：在Mac中使用SQLite

一、SQLite简介

二、在Mac上安装和使用SQLite

三、使用SQLite数据库

四、可视化IDE工具

五、SQLite知识体系框架

六 SQLite 常用命令

1. 基本数据库操作

2. 数据操作

3. 辅助命令

4. 事务和备份

5. 格式化和调试命令

6. 其他有用命令

七 上传文件到数据库

步骤：将 CSV 文件导入 SQLite

步骤：将 SQL 文件导入 SQLite

提示

后端与数据库交互

SQLite 和其他数据库的差别

1. 数据库连接方式

2. 数据库服务器

3. 并发处理

4. 使用场景

5. SQL 语法支持

6. 连接池

7. 安装与配置

示例对比

七上传文件到数据库