Predicate Operations

MySQL Tutorials - Herong's Tutorial Examples

∟Predicate Operations

This section provides a list of predicate operations supported by MySQL with some examples.

Predicate operations are operations that can be evaluated to give a condition of true or false:

1. "Comparison" Predicates: Compares two values of the same type according to a predefined order. There are 6 comparison operations:

Equal: value = value
Not Equal: value <> value
Less Than: value < value
Greater Than: value > value
Less Than or Equal: value <= value
Greater Than or Equal: value >= value

2. "Between" Predicates: Check a given value against a value range. There are 2 "between" operations:

Between: value BETWEEN value AND value
Not Between: value NOT BETWEEN value AND value

3. "In" Predicates: Check a given value against a list of values. There are 2 "in" operations:

In: value IN (value, value, ..., value)
Not In: value NOT IN (value, value, ..., value)

4. "Like" Predicates: Check a given character string against a character pattern. By default "Like" operations are not case sensitive. There are 2 "Like" operations:

Like: string LIKE pattern
Not Like: string NOT LIKE pattern

"pattern" is a character string with wildcard characters and escape sequences:

"_": Representing one and only one single character.
"%": Representing zero, one and many characters.
"\_": Representing character "_".
"\%": Representing character "%".

Examples:

'abc' LIKE 'Abc' -- true
'3.14' LIKE '_.__' -- true
'3.14159' LIKE '3.%' -- true
'herong_yang' LIKE 'herong\_yang' -- true
'4.75%' LIKE '_.__\%' -- true

5. "Null" Predicates: Check a given value against null value. There are 2 "Null" operations:

Null: value IS NULL
Not Null: value IS NOT NULL

Examples:

1 IS NULL -- false
'a' IS NOT NULL -- true
NULL IS NULL -- true
NULL IS NOT NULL -- false

Table of Contents

About This Book

Introduction of SQL

MySQL Introduction and Installation

Introduction of MySQL Programs

PHP Programs and MySQL Server

Perl Programs and MySQL Servers

Java Programs and MySQL Servers

Datatypes and Data Literals

►Operations and Expressions

What Is Expression

Arithmetic Operations

►Predicate Operations

Collations in Predicate Operations

Date and Time Operations

Examples of Different Types of Operation

Character Strings and Bit Strings

Commonly Used Functions

Table Column Types for Different Types of Values

Using DDL to Create Tables and Indexes

Using DML to Insert, Update and Delete Records

Using SELECT to Query Database

Window Functions for Statistical Analysis

Use Index for Better Performance

Transaction Management and Isolation Levels

Locks Used in MySQL

Defining and Calling Stored Procedures

Variables, Loops and Cursors Used in Stored Procedures

System, User-Defined and Stored Procedure Variables

MySQL Server Administration

Storage Engines in MySQL Server

InnoDB Storage Engine - Primary and Secondary Indexes

Performance Tuning and Optimization

Bulk Changes on Large Tables

MySQL Server on macOS

Installing MySQL Server on Linux

Connection, Performance and Second Instance on Linux

Archived Tutorials

References

Full Version in PDF/EPUB