SQLite的基本应用

1 在命令行中使用SQLite

  1. 创建数据库：打开命令行，输入sqlite3命令，后面跟上数据库文件的名称。
1
PS C:\Users\.PORJECT\SQLITE> sqlite3 test.db
  1. 创建表：在sqlite命令行中输入CREATE TABLE命令，后面跟上表的名称和字段。
1
2
3
4
5
sqlite> CREATE TABLE employees (
(x1...>     id INTEGER PRIMARY KEY,
(x1...>     name TEXT NOT NULL,
(x1...>     department TEXT NOT NULL
(x1...> );

这段代码是用来在SQLite数据库中创建一个名为“employees”的表。以下是对这段代码的详细解释：

  • CREATE TABLE employees (：这是创建表的命令，employees是表的名称。表名一般用作标识数据库中存储的数据。

  • id INTEGER PRIMARY KEY,：这行定义了一个名为id的列，其数据类型为INTEGER（整数）。PRIMARY KEY配置为主键，这通常用作每个记录的唯一标识符。

  • name TEXT NOT NULL,：这一行定义了一个名为name的列，其数据类型为TEXT（文本）。NOT NULL约束表示该列的值不能为空，必须提供一个有效的姓名。

  • department TEXT NOT NULL：这行定义了一个名为department的列，同样数据类型为TEXT。NOT NULL约束要求该列的数据值也不能为NULL，即必须指定该员工所属的部门。

  • );：此符号表示表创建命令的结束。

总的来说，这段代码用于创建一个用于存储员工信息的表，包括员工的ID、姓名和部门信息。

  1. 插入数据：在sqlite命令行中输入INSERT INTO命令，后面跟上表的名称和字段的值。

    1
    2
    3
    sqlite> INSERT INTO employees (id, name, department) VALUES (1, 'Alice Smith', 'HR');
    sqlite> INSERT INTO employees (id, name, department) VALUES (2, 'Bob Johnson', 'IT');
    sqlite> INSERT INTO employees (id, name, department) VALUES (3, 'Charlie Brown', 'Sales');

  2. 查询数据：在sqlite命令行中输入SELECT命令，后面跟上查询的条件。

1
sqlite> SELECT * FROM employees;

输出：

1
2
3
1|Alice Smith|HR
2|Bob Johnson|IT
3|Charlie Brown|Sales
  1. 添加列：在sqlite命令行中输入ALTER TABLE命令，后面跟上表的名称和字段的定义。
1
2
3
4
sqlite> ALTER TABLE employees ADD COLUMN salary INTEGER;
sqlite> ALTER TABLE employees ADD COLUMN bonus INTEGER;
sqlite> ALTER TABLE employees ADD COLUMN summary_salary INTEGER;

这段代码是用来在SQLite数据库中向已存在的表中添加一列。具体解释如下：

  • ALTER TABLE employees：这是用来修改已有表结构的命令，employees是被修改的表的名称。

  • ADD COLUMN salary INTEGER;：这部分表示要向employees表中添加一个新列。salary是新列的名称，其数据类型为INTEGER（整数），用于存储员工的薪资信息。

整条命令的作用是，为employees表添加一个名为salary的整数类型列。这允许在未来的操作中为每位员工记录薪资数据。

  1. 更新数据：在sqlite命令行中输入UPDATE命令，后面跟上表的名称、更新的条件和更新的字段。
1
2
3
4
5
6
UPDATE employees SET salary = 50000 WHERE id = 1;
UPDATE employees SET salary = 40000 WHERE id = 2;
UPDATE employees SET salary = 60000 WHERE id = 3;
UPDATE employees SET bonus = 5000 WHERE id = 1;
UPDATE employees SET bonus = 10000 WHERE id = 2;
UPDATE employees SET bonus = 7000 WHERE id = 3;

这段代码是用来更新employees表中id为1的员工的薪资信息。具体解释如下：

  • UPDATE employees：这是用来更新表中的数据命令，employees是被更新的表的名称。

  • SET salary = 50000：这部分表示要更新employees表中id为1的员工的薪资信息。SET关键字后面跟着要更新的字段和值。

  • WHERE id = 1：这部分表示更新条件，即只更新id为1的员工的薪资信息。WHERE关键字后面跟着更新条件。

总的来说，这段代码用于更新employees表中id为1的员工的薪资信息为50000。

  1. 计算数据：在sqlite命令行中输入SELECT命令，后面跟上计算的表达式。
1
sqlite> SELECT id, name, department, salary, bonus, salary + bonus AS summary_salary FROM employees;

这段代码是用来从employees表中查询数据的SQL命令。具体解释如下：

  • SELECT关键字：用于指定要查询的列。这里选择了多个字段。

  • id, name, department, salary, bonus：这些是要查询的字段，分别表示员工的ID、姓名、部门、薪资和奖金。

  • salary + bonus AS summary_salary：这里计算薪资和奖金的总和。AS summary_salary表示将计算结果命名为summary_salary，这样在查询结果中可以用这个名称引用该计算列。

  • FROM employees;：指定数据来源于employees表。

整条命令的作用是查询employees表中所有员工的ID、姓名、部门、薪资、奖金，以及薪资和奖金的总和（存储为summary_salary），并返回这些信息。

  1. 退出sqlite：在sqlite命令行中输入.quit命令。
1
sqlite> .quit

输出：

1
2
3
4
5
6
7
8
9
10
11

PS C:\Users\.PORJECT\SQLITE> ls


    目录: C:\Users\.PORJECT\SQLITE


Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a----         2024/9/16     11:34           8192 test.db

以上仅为SQLite的数据库在命令行中的基本使用方法。SQL作为关系型数据库的查询语言，其功能远不止于此。更多高级功能请参考官方文档或参考SQLite的进阶应用。

2 使用Python、C等语言操作SQLite数据库

2.1 Python操作SQLite数据库

使用 Python 操作 SQLite 数据库是一个简单的过程。Python 的标准库中包含了一个名为 sqlite3 的模块，它提供了访问 SQLite 数据库的接口。下面是一些基本步骤和示例代码来演示如何使用 Python 连接到 SQLite 数据库，并执行一些基本的操作。

  1. 导入 sqlite3 模块
1
import sqlite3
  1. 连接到数据库

如果指定的数据库文件不存在，SQLite 将会自动创建一个空的数据库文件。你可以通过提供一个文件名来连接到一个数据库文件，或者使用 ':memory:' 来将数据库存储在内存中。

1
conn = sqlite3.connect('example.db')
  1. 创建一个游标对象

游标是数据库内部数据的一个位置指示器，允许我们执行 SQL 命令并获取结果。

1
cursor = conn.cursor()
  1. 执行 SQL 命令

使用游标对象执行 SQL 命令。例如，创建一个表：

1
2
3
4
cursor.execute('''
CREATE TABLE stocks
    (date text, trans text, symbol text, qty real, price real)
''')
  1. 插入数据

可以使用 INSERT 语句来向表中插入数据：

1
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

或者批量插入数据：

1
2
3
4
5
purchases = [('2006-03-28', 'BUY', 'IBM', 500, 45.00),
             ('2006-04-05', 'BUY', 'MSOFT', 100, 72.00),
             ('2006-04-06', 'SELL', 'IBM', 50, 53.00),
            ]
cursor.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)
  1. 提交事务

在对数据库进行更改之后，需要提交事务来保存更改。

1
conn.commit()
  1. 查询数据

使用 SELECT 语句来查询数据：

1
2
cursor.execute('SELECT * FROM stocks WHERE symbol=?', ('RHAT',))
print(cursor.fetchone())

或者使用 fetchall() 获取所有结果：

1
2
cursor.execute('SELECT * FROM stocks')
print(cursor.fetchall())
  1. 关闭连接

最后，别忘了关闭数据库连接：

1
conn.close()

以上就是使用 Python 和 SQLite3 模块进行基本数据库操作的方法。记得每次操作完数据库后都要关闭连接，防止资源泄露。