逻辑表达式

📄字数 1.9K

👁️阅读量 加载中...

概述

逻辑表达式是用来表示布尔条件的表达式，结果值为TRUE、FALSE或UNKNOW（表示未知）。常用于WHERE、JOIN、HAVING、CASE等子句中。

逻辑表达式分类

• 比较运算符

运算符	说明	示例
=	等于	a = 100
<> / !=	不等于	b <> 1
> / < / >= / <=	大小比较	salary > 3000

• 范围与集合判断

表达式	说明	示例
BETWEEN a AND b	属于闭区间 [a, b]	score BETWEEN 60 AND 90
IN (val1, val2, ...)	属于集合	status IN ('A', 'B')
IS NULL / IS NOT NULL	判断空值	addr IS NOT NULL

• 模糊匹配

表达式	说明	示例
LIKE	匹配	name LIKE '张%'
NOT LIKE	排除匹配	title NOT LIKE '%测试'

• 逻辑运算符

运算符	说明	示例
AND	逻辑与	a > 0 AND b < 10
OR	逻辑或	x = 1 OR y = 2
NOT	逻辑非	NOT (status = 'N')

• 布尔值判断

运算符	说明	示例
IS TRUE/NOT TRUE	TRUE或FALSE判断	flag IS TRUE/NOT TRUE
IS FALSE/NOT FALSE	TRUE或FALSE判断	flag IS FALSE/NOT FALSE

语法格式

• 比较运算符

• 范围与集合判断

• 模糊匹配

• 逻辑运算符

语法格式见逻辑运算符章节。

• 布尔值判断

参数说明：
bool_expr：布尔表达式，即结果为TRUE、FALSE 或 UNKOWN 的逻辑表达式
expr：普通表达式，表示任何可以被求值为一个值（数字、字符串、日期等）的表达式

示例

-- 建表
SQL> CREATE TABLE employee (
            id INT PRIMARY KEY,
            name VARCHAR(50),
            age INT,
            department VARCHAR(50),
            salary DECIMAL(10, 2),
            is_active BOOLEAN,
            bonus DECIMAL(10, 2)
          );

-- 插入数据
SQL> INSERT INTO employee VALUES
     (1, 'Alice', 30, 'HR', 8000.00, TRUE, 1000.00),
     (2, 'Bob', 45, 'Engineering', 12000.00, TRUE, NULL),
     (3, 'Charlie', 28, 'Sales', 6000.00, FALSE, NULL),
     (4, 'Diana', 50, 'HR', 9500.00, TRUE, 2000.00),
     (5, 'Eve', 35, 'Engineering', 10000.00, FALSE, NULL);

-- 使用AND/OR判断多个条件
SQL> SELECT name, department, salary
     FROM employee
     WHERE department = 'HR' AND salary > 9000;

+-------+------------+--------+
| NAME  | DEPARTMENT | SALARY |
+-------+------------+--------+
| Diana | HR         | 9500   |
+-------+------------+--------+

-- 使用IS NULL/IS NOT NULL判断空值
SQL> SELECT name, bonus
     FROM employee
     WHERE bonus IS NULL;

+---------+--------+
|  NAME   | BONUS  |
+---------+--------+
| Bob     | <NULL> |
| Charlie | <NULL> |
| Eve     | <NULL> |
+---------+--------+

-- 使用NOT逻辑
SQL> SELECT name
     FROM employee
     WHERE NOT is_active;

+---------+
|  NAME   |
+---------+
| Charlie |
| Eve     |
+---------+

-- 使用IN判断集合包含关系
SQL> SELECT name, department
     FROM employee
     WHERE department IN ('HR', 'Sales');

+---------+------------+
|  NAME   | DEPARTMENT |
+---------+------------+
| Alice   | HR         |
| Charlie | Sales      |
| Diana   | HR         |
+---------+------------+

-- 使用BETWEEN判断范围
SQL> SELECT name, salary
     FROM employee
     WHERE salary BETWEEN 8000 AND 10000;

+-------+--------+
| NAME  | SALARY |
+-------+--------+
| Alice | 8000   |
| Diana | 9500   |
| Eve   | 10000  |
+-------+--------+

-- 使用LIKE进行模糊匹配
SQL> SELECT name
     FROM employee
     WHERE name LIKE 'A%';

+-------+
| NAME  |
+-------+
| Alice |
+-------+

-- 布尔值判断
SQL> SELECT * FROM employee WHERE is_active IS TRUE;

+----+-------+-----+-------------+--------+-----------+--------+
| ID | NAME  | AGE | DEPARTMENT  | SALARY | IS_ACTIVE | BONUS  |
+----+-------+-----+-------------+--------+-----------+--------+
| 1  | Alice | 30  | HR          | 8000   | T         | 1000   |
| 2  | Bob   | 45  | Engineering | 12000  | T         | <NULL> |
| 4  | Diana | 50  | HR          | 9500   | T         | 2000   |
+----+-------+-----+-------------+--------+-----------+--------+

SQL> SELECT * FROM employee WHERE is_active IS NOT TRUE;

+----+---------+-----+-------------+--------+-----------+--------+
| ID |  NAME   | AGE | DEPARTMENT  | SALARY | IS_ACTIVE | BONUS  |
+----+---------+-----+-------------+--------+-----------+--------+
| 3  | Charlie | 28  | Sales       | 6000   | F         | <NULL> |
| 5  | Eve     | 35  | Engineering | 10000  | F         | <NULL> |
+----+---------+-----+-------------+--------+-----------+--------+

SQL> SELECT * FROM employee WHERE is_active IS NOT FALSE;

+----+-------+-----+-------------+--------+-----------+--------+
| ID | NAME  | AGE | DEPARTMENT  | SALARY | IS_ACTIVE | BONUS  |
+----+-------+-----+-------------+--------+-----------+--------+
| 1  | Alice | 30  | HR          | 8000   | T         | 1000   |
| 2  | Bob   | 45  | Engineering | 12000  | T         | <NULL> |
| 4  | Diana | 50  | HR          | 9500   | T         | 2000   |
+----+-------+-----+-------------+--------+-----------+--------+

使用场景

• 场景一：数据行筛选（WHERE子句），这是逻辑表达式最常见的使用场景，用于指定返回哪些行
• 场景二：联接条件控制（JOIN ON），在多表连接时，逻辑表达式决定匹配哪些行