SQLite条件结果处理与Null值处理策略

SQLite条件结果处理

WHERE 子句基础

在SQLite中，WHERE子句是进行条件筛选的关键部分。它允许我们根据特定条件从表中选择符合要求的行。例如，我们有一个名为employees的表，包含id、name、age和salary字段。

-- 创建employees表
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    salary REAL
);

-- 插入一些示例数据
INSERT INTO employees (name, age, salary) VALUES ('Alice', 30, 5000.0);
INSERT INTO employees (name, age, salary) VALUES ('Bob', 25, 4000.0);
INSERT INTO employees (name, age, salary) VALUES ('Charlie', 35, 6000.0);

如果我们只想获取年龄大于30岁的员工信息，可以使用以下WHERE子句：

SELECT * FROM employees WHERE age > 30;

这将返回Charlie的记录，因为只有他的年龄大于30岁。WHERE子句中的条件可以使用各种比较运算符，如=（等于）、!=（不等于）、<（小于）、>（大于）、<=（小于等于）和>=（大于等于）。

多条件组合

实际应用中，我们常常需要多个条件同时满足才能筛选出合适的数据。SQLite支持使用逻辑运算符AND和OR来组合多个条件。

假设我们要获取年龄大于30岁且工资大于5000的员工信息，可以这样写：

SELECT * FROM employees WHERE age > 30 AND salary > 5000;

这里AND表示两个条件都必须满足。如果我们使用OR，比如要获取年龄大于30岁或者工资大于5000的员工信息：

SELECT * FROM employees WHERE age > 30 OR salary > 5000;

这将返回Alice和Charlie的记录，因为Alice工资大于5000，Charlie年龄大于30岁。

我们还可以使用括号来改变逻辑运算的优先级。例如，如果我们想获取（年龄大于30岁且工资大于4500）或者（年龄小于28岁且工资大于3500）的员工信息：

SELECT * FROM employees WHERE ((age > 30 AND salary > 4500) OR (age < 28 AND salary > 3500));

模糊匹配

在处理文本数据时，模糊匹配是非常有用的。SQLite提供了LIKE运算符用于模糊匹配。LIKE通常与通配符%（匹配零个或多个字符）和_（匹配单个字符）一起使用。

假设我们的employees表中有很多员工，我们想找到名字以A开头的员工，可以这样写：

SELECT * FROM employees WHERE name LIKE 'A%';

如果我们想找到名字是三个字符且第二个字符是o的员工，可以使用_通配符：

SELECT * FROM employees WHERE name LIKE '_o_';

使用 IN 运算符

IN运算符用于检查一个值是否在指定的列表中。例如，我们想获取年龄为25岁或者35岁的员工信息：

SELECT * FROM employees WHERE age IN (25, 35);

这比使用多个OR条件更简洁。我们也可以使用NOT IN来获取不在指定列表中的记录，比如获取年龄不在25岁和35岁的员工信息：

SELECT * FROM employees WHERE age NOT IN (25, 35);

条件表达式 CASE

CASE表达式允许我们在SQL查询中进行条件判断和结果返回。它有两种形式：简单CASE表达式和搜索CASE表达式。

简单CASE表达式语法如下：

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE result3
END

例如，我们想根据员工的年龄给员工评级，年龄小于25岁为“Junior”，25到35岁为“Mid - level”，大于35岁为“Senior”，可以这样写：

SELECT name, age,
       CASE
           WHEN age < 25 THEN 'Junior'
           WHEN age BETWEEN 25 AND 35 THEN 'Mid - level'
           ELSE 'Senior'
       END AS rating
FROM employees;

搜索CASE表达式语法如下：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result3
END

例如，我们想根据员工的工资情况进行不同的奖励设置，如果工资大于6000，奖励1000；如果工资大于5000，奖励500；否则奖励100：

SELECT name, salary,
       CASE
           WHEN salary > 6000 THEN 1000
           WHEN salary > 5000 THEN 500
           ELSE 100
       END AS bonus
FROM employees;

SQLite Null值处理策略

Null值的概念

在SQLite中，NULL表示一个缺失的值或者未知的值。它不同于空字符串或者零值。例如，在employees表中，如果某个员工的工资还未确定，我们可以将其salary字段设为NULL。

-- 更新某个员工的工资为NULL
UPDATE employees SET salary = NULL WHERE name = 'Alice';

检测Null值

要检测一个字段是否为NULL，不能使用=运算符，而应该使用IS NULL和IS NOT NULL。例如，要获取工资为NULL的员工信息：

SELECT * FROM employees WHERE salary IS NULL;

要获取工资不为NULL的员工信息：

SELECT * FROM employees WHERE salary IS NOT NULL;

Null值在运算中的表现

当NULL参与算术运算、比较运算或者逻辑运算时，结果通常也是NULL。例如：

SELECT 5 + NULL; -- 结果为NULL
SELECT 'Hello' || NULL; -- 结果为NULL
SELECT NULL > 10; -- 结果为NULL

只有在逻辑运算AND、OR和NOT中，NULL有特殊的处理方式。NULL AND TRUE结果为NULL，因为NULL表示未知，不知道这个未知值与TRUE进行AND运算的结果；NULL OR TRUE结果为TRUE，因为只要有一个为TRUE，OR运算结果就为TRUE；NOT NULL结果为NULL，因为对未知值取反还是未知。

聚合函数与Null值

聚合函数（如SUM、AVG、COUNT、MIN和MAX）在处理包含NULL值的数据时，默认会忽略NULL值。

例如，计算employees表中工资的总和：

SELECT SUM(salary) FROM employees;

这里如果有员工工资为NULL，SUM函数会忽略这些NULL值，只对实际存在的工资值进行求和。

如果我们想计算包含NULL值的记录数，可以使用COUNT(*)，它会统计所有的记录，包括NULL值：

SELECT COUNT(*) FROM employees;

如果只想统计非NULL值的记录数，可以指定具体字段，如：

SELECT COUNT(salary) FROM employees;

处理Null值的函数

SQLite提供了一些函数来处理NULL值。

COALESCE函数用于返回参数列表中的第一个非NULL值。例如，我们想获取员工的工资，如果工资为NULL，则返回0：

SELECT name, COALESCE(salary, 0) AS salary_or_zero
FROM employees;

IFNULL函数与COALESCE函数类似，它接受两个参数，如果第一个参数为NULL，则返回第二个参数：

SELECT name, IFNULL(salary, 0) AS salary_or_zero
FROM employees;

NULLIF函数比较两个表达式，如果它们相等则返回NULL，否则返回第一个表达式。例如，如果我们有一个字段bonus，如果它的值为0，我们想将其设为NULL：

SELECT name, NULLIF(bonus, 0) AS adjusted_bonus
FROM employees;

在连接查询中处理Null值

在进行连接查询（如INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN）时，NULL值也需要特别处理。

以LEFT JOIN为例，假设我们有另一个表departments，包含department_id和department_name字段，并且employees表中有一个department_id字段关联到departments表。

-- 创建departments表
CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT
);

-- 插入一些示例数据
INSERT INTO departments (department_id, department_name) VALUES (1, 'HR');
INSERT INTO departments (department_id, department_name) VALUES (2, 'IT');

-- 进行LEFT JOIN
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

如果某个员工的department_id为NULL，在LEFT JOIN结果中，对应的department_name字段将为NULL。我们可以使用前面提到的处理NULL值的函数来处理这种情况，例如：

SELECT employees.name, COALESCE(departments.department_name, 'Unassigned') AS department
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

这样就将没有部门分配的员工标记为“Unassigned”。

在子查询中处理Null值

子查询中同样会遇到NULL值的问题。例如，假设我们想找到工资高于平均工资的员工，但是如果平均工资计算结果为NULL（比如所有员工工资都为NULL），我们需要正确处理。

-- 找到工资高于平均工资的员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

在这种情况下，如果平均工资为NULL，结果集将为空。我们可以使用COALESCE函数来处理：

SELECT name, salary
FROM employees
WHERE salary > COALESCE((SELECT AVG(salary) FROM employees), 0);

这样即使平均工资为NULL，也会按照与0比较来筛选员工。

防止Null值插入

在设计表时，我们可以通过设置字段的约束来防止NULL值插入。例如，我们在创建employees表时，可以将name字段设为NOT NULL：

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    salary REAL
);

这样如果我们尝试插入一条name为NULL的记录，将会报错。

数据库事务与Null值

在数据库事务中，对NULL值的处理也很重要。例如，如果在一个事务中更新多个记录，其中某些记录的更新可能会导致字段变为NULL。如果事务执行过程中出现错误，整个事务回滚，所有涉及NULL值的更新也会被撤销，数据库状态恢复到事务开始前。

BEGIN;
UPDATE employees SET salary = NULL WHERE age < 28;
-- 假设这里出现错误，比如违反了某个约束
ROLLBACK;

在这种情况下，所有年龄小于28岁的员工工资不会被设为NULL，因为事务回滚了。

通过合理处理条件结果和NULL值，我们能够更有效地管理和查询SQLite数据库中的数据，确保数据的准确性和完整性，满足各种业务需求。无论是简单的条件筛选，还是复杂的多表连接和子查询，对条件和NULL值的深入理解都是至关重要的。在实际应用中，需要根据具体的业务场景，灵活运用上述提到的各种方法和技巧，以达到最佳的数据库操作效果。