SQLite中SQL语言基础语法解析

SQLite 概述

SQLite 是一款轻型的数据库，它是遵守 ACID 的关系型数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持 Windows/Linux/Unix 等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java 等，还有ODBC接口，同样比起 Mysql、PostgreSQL 这两款开源的世界著名数据库管理系统来讲，它的处理速度比它们都快。

SQLite 由以下几个组件组成：SQL 编译器、内核、后端以及附件。SQLite 通过利用虚拟机和虚拟数据库引擎（VDBE），使调试、修改和扩展 SQLite 的内核变得更加方便。

SQL 语言简介

SQL（Structured Query Language）即结构化查询语言，是用于数据库查询和编程的标准语言。SQL 语言主要用于数据库的创建、数据的插入、查询、更新和删除等操作。在 SQLite 中，SQL 语言同样是核心操作语言，通过它我们可以与 SQLite 数据库进行交互。SQL 语言具有以下特点：

1. 非过程化：用户只需提出“做什么”，而不必指明“怎么做”，语句的操作过程由系统自动完成。
2. 统一语法：SQL 语言集数据定义语言（DDL）、数据操作语言（DML）、数据控制语言（DCL）的功能于一体，可以独立完成数据库生命周期中的全部活动。
3. 面向集合操作：SQL 语言采用集合操作方式，不仅查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。

SQLite 数据类型

SQLite 使用的是动态类型系统，这意味着一个值的数据类型是和值本身相关联，而不是和它的容器相关联。不过，SQLite 也支持一些常见的数据类型：

1. NULL：空值，代表一个不存在的值。
2. INTEGER：有符号整数，根据值的大小可以存储在 1、2、3、4、6 或 8 字节中。
3. REAL：浮点值，存储为 8 字节的 IEEE 浮点数字。
4. TEXT：文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
5. BLOB：二进制大对象，可以存储任何数据，完全根据输入存储。

虽然 SQLite 是动态类型，但在创建表时仍然可以指定数据类型，这有助于提高数据的规范性和可读性。例如：

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

在上述示例中，id 列指定为 INTEGER 类型，name 和 email 列指定为 TEXT 类型，age 列指定为 INTEGER 类型。

数据库和表的操作

创建数据库

在 SQLite 中，创建数据库非常简单，当使用 SQLite 命令行工具连接到一个不存在的数据库文件时，SQLite 会自动创建该数据库文件。例如，在命令行中使用以下命令连接到 test.db 数据库，如果 test.db 不存在，则会创建它：

sqlite3 test.db

创建表

使用 CREATE TABLE 语句来创建表。语法如下：

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 data_type [constraint],
    column2 data_type [constraint],
  ...
    [constraint]
);

IF NOT EXISTS 是可选的，用于在表不存在时才创建表，避免重复创建导致的错误。column1、column2 等是表的列名，data_type 是列的数据类型，constraint 是列约束或表约束。

例如，创建一个 employees 表：

CREATE TABLE IF NOT EXISTS employees (
    employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    salary REAL
);

在这个例子中，employee_id 列是主键，并且使用 AUTOINCREMENT 关键字使其自动递增。first_name 和 last_name 列被定义为 TEXT 类型且不能为空，salary 列是 REAL 类型，可存储工资信息。

修改表

使用 ALTER TABLE 语句修改表结构。目前 SQLite 支持的 ALTER TABLE 操作有限，主要用于添加新列或重命名表。

• 添加新列：

ALTER TABLE table_name
ADD COLUMN new_column_name data_type [constraint];

例如，向 employees 表添加一个 department 列：

ALTER TABLE employees
ADD COLUMN department TEXT;

• 重命名表：

ALTER TABLE old_table_name
RENAME TO new_table_name;

例如，将 employees 表重命名为 staff：

ALTER TABLE employees
RENAME TO staff;

删除表

使用 DROP TABLE 语句删除表。语法如下：

DROP TABLE [IF EXISTS] table_name;

IF EXISTS 同样是可选的，用于避免在表不存在时抛出错误。例如，删除 staff 表：

DROP TABLE IF EXISTS staff;

数据插入操作

使用 INSERT INTO 语句向表中插入数据。有几种常见的语法形式：

1. 插入所有列的值：

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

例如，向 employees 表插入一条员工记录：

INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', 5000.0);

2. 插入部分列的值：

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

例如，插入一条只包含姓名和工资的员工记录，employee_id 会自动递增，department 列会为 NULL：

INSERT INTO employees (first_name, last_name, salary)
VALUES ('Jane', 'Smith', 6000.0);

3. 插入多条记录：

INSERT INTO table_name (column1, column2, column3)
VALUES (value1_1, value1_2, value1_3),
       (value2_1, value2_2, value2_3),
       (value3_1, value3_2, value3_3);

例如，一次性插入多条员工记录：

INSERT INTO employees (first_name, last_name, salary)
VALUES ('Alice', 'Johnson', 4500.0),
       ('Bob', 'Williams', 5500.0),
       ('Charlie', 'Brown', 5200.0);

数据查询操作

基本查询

SELECT 语句用于从数据库中查询数据。基本语法如下：

SELECT column1, column2
FROM table_name;

例如，从 employees 表中查询所有员工的 first_name 和 last_name：

SELECT first_name, last_name
FROM employees;

要查询所有列，可以使用通配符 *：

SELECT *
FROM employees;

条件查询

使用 WHERE 子句可以添加查询条件，只返回满足条件的数据。语法如下：

SELECT column1, column2
FROM table_name
WHERE condition;

condition 是一个布尔表达式，可以使用比较运算符（如 =, <, >, <=, >=, <>）、逻辑运算符（如 AND, OR, NOT）等。

例如，查询工资大于 5000 的员工：

SELECT *
FROM employees
WHERE salary > 5000;

查询 department 为 'HR' 且工资大于 5500 的员工：

SELECT *
FROM employees
WHERE department = 'HR' AND salary > 5500;

排序查询

使用 ORDER BY 子句对查询结果进行排序。语法如下：

SELECT column1, column2
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC];

ASC 表示升序（默认），DESC 表示降序。

例如，按工资降序查询员工：

SELECT *
FROM employees
ORDER BY salary DESC;

先按 department 升序，再按 salary 降序查询员工：

SELECT *
FROM employees
ORDER BY department ASC, salary DESC;

聚合查询

SQLite 提供了一些聚合函数，如 COUNT, SUM, AVG, MIN, MAX 等，用于对数据进行统计。

• COUNT：统计行数。例如，统计员工总数：

SELECT COUNT(*)
FROM employees;

统计工资大于 5000 的员工数：

SELECT COUNT(*)
FROM employees
WHERE salary > 5000;

• SUM：计算总和。例如，计算所有员工的工资总和：

SELECT SUM(salary)
FROM employees;

• AVG：计算平均值。例如，计算员工的平均工资：

SELECT AVG(salary)
FROM employees;

• MIN 和 MAX：获取最小值和最大值。例如，获取员工工资的最小值和最大值：

SELECT MIN(salary), MAX(salary)
FROM employees;

分组查询

使用 GROUP BY 子句可以按指定列对数据进行分组，通常与聚合函数一起使用。语法如下：

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

例如，按 department 分组统计每个部门的员工数：

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

还可以使用 HAVING 子句对分组结果进行过滤，它与 WHERE 子句类似，但 WHERE 用于过滤行，而 HAVING 用于过滤分组。例如，查询员工数大于 2 的部门：

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

数据更新操作

使用 UPDATE 语句更新表中的数据。语法如下：

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

例如，将 employees 表中 employee_id 为 1 的员工的工资增加 500：

UPDATE employees
SET salary = salary + 500
WHERE employee_id = 1;

将 department 为 'HR' 的所有员工的工资提高 10%：

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'HR';

数据删除操作

使用 DELETE FROM 语句删除表中的数据。语法如下：

DELETE FROM table_name
WHERE condition;

例如，删除 employee_id 为 5 的员工记录：

DELETE FROM employees
WHERE employee_id = 5;

删除 department 为 'Marketing' 的所有员工记录：

DELETE FROM employees
WHERE department = 'Marketing';

如果不指定 WHERE 子句，将删除表中的所有数据，但表结构仍然保留：

DELETE FROM employees;

连接操作

在关系型数据库中，连接操作用于将多个表中的数据关联起来。SQLite 支持几种常见的连接类型：

内连接（INNER JOIN）

内连接返回两个表中满足连接条件的所有行。语法如下：

SELECT column1, column2
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

例如，有 orders 表和 customers 表，orders 表中有 customer_id 列关联到 customers 表的 customer_id 列，查询每个订单对应的客户信息：

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;

左连接（LEFT JOIN）

左连接返回左表（table1）中的所有行，以及右表（table2）中满足连接条件的行。如果右表中没有匹配的行，则结果集中对应列的值为 NULL。语法如下：

SELECT column1, column2
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

例如，查询所有客户及其订单信息，即使客户没有订单：

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

右连接（RIGHT JOIN）

右连接返回右表中的所有行，以及左表中满足连接条件的行。如果左表中没有匹配的行，则结果集中对应列的值为 NULL。语法如下：

SELECT column1, column2
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

在 SQLite 中，没有直接的 RIGHT JOIN 关键字，但可以通过 LEFT JOIN 来实现相同的效果，只需交换两个表的位置即可：

SELECT column1, column2
FROM table2
LEFT JOIN table1
ON table2.common_column = table1.common_column;

全连接（FULL OUTER JOIN）

全连接返回左表和右表中的所有行。如果某行在另一个表中没有匹配的行，则结果集中对应列的值为 NULL。SQLite 本身不直接支持 FULL OUTER JOIN，但可以通过 UNION 结合 LEFT JOIN 和 RIGHT JOIN 来模拟：

SELECT column1, column2
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column
UNION
SELECT column1, column2
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

子查询

子查询是在另一个查询内部的查询。子查询可以用于 SELECT, WHERE, HAVING 等子句中。

例如，查询工资高于平均工资的员工：

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

在这个例子中，子查询 (SELECT AVG(salary) FROM employees) 先计算出平均工资，然后主查询使用这个结果来筛选工资高于平均工资的员工。

子查询还可以用于多表查询中。例如，有 orders 表和 customers 表，查询有订单的客户信息：

SELECT *
FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

这里子查询 (SELECT DISTINCT customer_id FROM orders) 找出所有有订单的客户 customer_id，主查询根据这些 customer_id 从 customers 表中获取客户信息。

事务处理

SQLite 支持事务处理，事务是一个不可分割的工作单元，要么全部执行成功，要么全部执行失败。事务处理可以确保数据的一致性和完整性。

• 开始事务：使用 BEGIN 或 BEGIN TRANSACTION 语句开始一个事务。
• 提交事务：使用 COMMIT 语句提交事务，将事务中所有的修改永久保存到数据库。
• 回滚事务：使用 ROLLBACK 语句回滚事务，撤销事务中所有的修改，将数据库恢复到事务开始前的状态。

例如，假设要从一个账户向另一个账户转账：

BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
COMMIT;

如果在执行过程中出现错误，可以使用 ROLLBACK 回滚事务：

BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
-- 假设这里出现错误
ROLLBACK;

索引

索引是一种特殊的数据结构，它可以加快数据的查询速度。在 SQLite 中，可以使用 CREATE INDEX 语句创建索引。

创建普通索引

CREATE INDEX index_name
ON table_name (column1, column2);

例如，为 employees 表的 last_name 列创建索引：

CREATE INDEX idx_last_name
ON employees (last_name);

创建唯一索引

唯一索引确保索引列中的值是唯一的。语法如下：

CREATE UNIQUE INDEX index_name
ON table_name (column1);

例如，为 employees 表的 email 列创建唯一索引，确保每个员工的 email 唯一：

CREATE UNIQUE INDEX idx_email
ON employees (email);

索引虽然可以加快查询速度，但也会增加数据插入、更新和删除的开销，因为每次数据修改时，索引也需要相应更新。所以在创建索引时需要权衡利弊，只在经常用于查询条件的列上创建索引。

视图

视图是一个虚拟表，它基于一个或多个实际表的查询结果。视图不实际存储数据，而是在查询视图时动态生成结果。

使用 CREATE VIEW 语句创建视图。例如，创建一个视图显示工资高于 5000 的员工信息：

CREATE VIEW high_paid_employees AS
SELECT *
FROM employees
WHERE salary > 5000;

之后可以像查询普通表一样查询视图：

SELECT *
FROM high_paid_employees;

视图可以简化复杂的查询，同时提供一定的数据安全性，因为可以限制用户对某些列或行的访问。可以使用 DROP VIEW 语句删除视图：

DROP VIEW high_paid_employees;

总结

通过对 SQLite 中 SQL 语言基础语法的详细解析，我们了解了从数据库和表的创建、数据的增删改查，到复杂的连接操作、子查询、事务处理、索引以及视图等方面的知识。这些基础语法是使用 SQLite 进行数据库开发和管理的关键，在实际应用中，我们需要根据具体的需求合理运用这些语法，以构建高效、可靠的数据库应用程序。在使用过程中，要注意数据类型的匹配、查询条件的准确性以及事务和索引的合理使用，以提高数据库的性能和数据的完整性。