垦荒学园
  • 中文

      • MySQL WHERE子句

      概述

      WHERE子句根据指定的条件过滤记录。它只提取满足指定条件的记录，使查询更精确、更高效。

      WHERE语法

      比较运算符

      基本比较

      -- Equal

-- Not equal

-- Greater than

-- Greater than or equal

-- Less than

-- Less than or equal

      范围运算符

      -- Between (inclusive)

-- Date range

-- NOT BETWEEN

      列表运算符

      -- In list

-- With subquery

-- NOT IN

      逻辑运算符

      AND运算符

      -- Multiple conditions (all must be true)

-- Multiple AND conditions

-- Combine with other operators

      OR运算符

      -- Any condition true

-- Multiple OR conditions

      AND与OR组合

      -- AND has higher precedence

-- Parentheses for clarity

      NOT运算符

      -- NOT equal

-- NOT IN

-- NOT BETWEEN

-- NOT LIKE

-- NOT NULL

-- Complex NOT

      NULL条件

      IS NULL / IS NULL

      -- Find NULL values

-- Find missing relationships

-- Find incomplete records

      IS NOT NULL / IS NOT NULL

      -- Exclude NULL values

-- Only completed records

      模式匹配

      LIKE / LIKE

      -- Starts with

-- Ends with

-- Contains

-- Single character wildcard

-- Multiple wildcards

      NOT LIKE / NOT LIKE

      -- Does not start with

-- Does not contain

      大小写不敏感

      -- MySQL LIKE is case-insensitive by default

-- For case-sensitive comparison

      正则表达式

      -- Starts with

-- Ends with

-- Contains any of characters

-- Multiple options

-- Complex patterns

-- NOT REGEXP

      字符串比较

      -- Exact match

-- Case-insensitive comparison

-- Length comparison

-- String functions in WHERE

-- Concatenation comparison

      日期时间过滤

      -- Specific date

-- Date range

-- Time comparison

-- Year filter

-- Month filter

-- Day of week

-- Date functions

      数值过滤

      -- Aggregate functions in WHERE (using HAVING)

-- Subquery with aggregate

-- Mathematical operations

      组合条件

      -- Complex query

-- Using CASE in WHERE (advanced)

      性能考虑

      索引使用

      -- Use indexed columns in WHERE

-- Function on column prevents index use

-- Rewrite to use index

      查询优化

      -- Put most selective condition first

-- Avoid SELECT *

-- Use LIMIT for testing

      故障排除

      常见问题

      -- Unexpected results
-- Check: Operator precedence, parentheses

-- No results returned
-- Check: NULL comparisons (use IS NULL, not = NULL)

-- Case sensitivity
-- Check: String comparisons, collation settings

-- Date format
-- Check: Date format matches MySQL expectations (YYYY-MM-DD)

      调试

      -- Add EXPLAIN to see query plan

-- Test conditions separately

-- Check data types

      小结

      WHERE子句过滤包括：

      比较运算符 范围运算符 逻辑运算符 NULL处理 模式匹配 Time**: Date functions / 日期时间 性能优化

      上一个：查询数据

      下一个：UPDATE