In managing and manipulating databases using MySQL, operators play an important role in performing various calculations, comparisons, and condition evaluations. The following are the types of operators found in MySQL syntax along with their explanations:
Arithmetic Operators
Arithmetic operators are used to perform basic mathematical calculations on values (operands).
- Addition ( + ): An operator used to add the values of both operands. For example, 30 + 20 results in 50.
- Subtraction ( – ): An operator used to subtract the right operand from the left operand. For example, 30 – 10 results in 20.
- Multiplication ( * ): An operator used to multiply both operands. For example, 3 * 3 results in 9.
- Division ( / ): An operator used to divide the left operand by the right operand. For example, 20 / 2 results in 10.
- Modulus ( % ): An operator used to find the remainder of division of the left operand by the right operand. For example, 10 % 2 results in 0.
Comparison Operators
Comparison operators are used to compare two values. The result of this operation is a boolean value (true or false).
- ( = ): Compares whether both operands are equal. If equal, it returns true. For example, 10 = 10 returns true.
- ( != ): Compares whether the operands are not equal. If not equal, it returns true. For example, 5 != 10 returns true.
- ( <> ): Functions the same as !=, comparing whether the operands are not equal. If not equal, it returns true. For example, 5 <> 10 returns true.
- ( > ): Compares whether the left operand is greater than the right operand. If greater, it returns true. For example, 10 > 5 returns true.
- ( < ): Compares whether the left operand is less than the right operand. If less, it returns true. For example, 5 < 10 returns true.
- ( >= ): Compares whether the left operand is greater than or equal to the right operand. If greater than or equal, it returns true. For example, 10 >= 10 returns true.
- ( <= ): Compares whether the left operand is less than or equal to the right operand. If less than or equal, it returns true. For example, 5 <= 10 returns true.
- ( !< ): Compares whether the left operand is not less than the right operand. If not less, it returns true. For example, 10 !< 5 returns true.
- ( !> ): Compares whether the left operand is not greater than the right operand. If not greater, it returns true. For example, 9 !> 10 returns true.
Logical Operators
Logical operators are used to combine multiple conditions or perform logical evaluations in certain clauses.
- ALL: Compares a value with all values in another set of values.
- AND: Combines two or more boolean expressions. Returns true if all conditions are met.
- ANY: Compares a value with any applicable value in a list according to the condition.
- BETWEEN: Searches for values within a specified minimum and maximum range.
- EXISTS: Checks for the existence of a specific row in a table that meets certain criteria.
- IN: Compares a value with a list of specified literal values.
- LIKE: Compares a value with similar values using wildcard operators.
- NOT: Reverses the meaning of a logical operator being used (negation).
- OR: Adds a condition to a clause and returns true if one or both conditions are true.
- IS NULL: Compares a value to check whether it is empty (NULL).
- UNIQUE: Checks for the uniqueness of each row in a table that has no duplicates.
Using the BETWEEN Operator
The BETWEEN operator is used to search for values within a specified range. The range consists of a minimum and maximum value to be displayed. You can also combine the BETWEEN operator with other comparison operators.
Basic Syntax:
SQL
SELECT * FROM table_name WHERE column_name BETWEEN min_value AND max_value;
Example Usage:
SQL
SELECT * FROM list_HP WHERE price BETWEEN 1000000 AND 5000000;
Meaning: This command will display data from the list_HP table in the price column that has values between 1,000,000 and 5,000,000.
Using the LIKE Clause
The LIKE clause is an operator used to perform a search by comparing a value with a search pattern. This clause can use wildcard operators as substitute characters, such as:
- ( _ ): A wildcard that matches exactly one single character.
- ( % ): A wildcard that matches zero to unlimited characters.
Basic Syntax:
SQL
SELECT column_to_display FROM table_name WHERE search_column LIKE ‘search_keyword’;
Example Usage Without Wildcard:
SQL
SELECT * FROM students WHERE student_name LIKE ‘Wilantara’;
Meaning: Displays data from the students table where the student_name column is exactly “Wilantara”.
Example Usage With Wildcard:
SQL
SELECT * FROM students WHERE student_name LIKE ‘W%’;
Meaning: Displays data from the students table where the student_name column starts with the letter “W” followed by any characters after it.
