MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Function Overview

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

MySQL Common Operators Overview

In MySQL, operators are used to perform operations on variables and values. These are some of the most commonly used operators:

Arithmetic Operators: These operators are used to perform mathematical operations like addition (+), subtraction (-), multiplication (*), division (/), and modulus (%).

Example:

SELECT 10 + 20;  -- Returns 30
SELECT 10 - 20;  -- Returns -10
SELECT 10 * 20;  -- Returns 200
SELECT 10 / 20;  -- Returns 0.5
SELECT 10 % 3;   -- Returns 1 (remainder of the division)

Comparison Operators: These operators are used to compare two values. They include equal to (=), not equal to (<> or !=), less than (<), greater than (>), less than or equal to (<=), greater than or equal to (>=).
Example:
```
SELECT * FROM employees WHERE salary > 50000;
```
Logical Operators: These operators are used to combine or negate conditions. They include AND, OR, and NOT.
Example:
```
SELECT * FROM employees WHERE salary > 50000 AND department_id = 1;
```
Bitwise Operators: These operators are used to perform bitwise operations. They include Bitwise AND (&), Bitwise OR (|), Bitwise XOR (^), Bitwise NOT (~), Bitwise Shift Right (>>), and Bitwise Shift Left (<<).
Example:
```
SELECT 6 & 3;  -- Returns 2 (Performs a bitwise AND operation)
```
Compound Operators: These operators are used to perform an operation and assign the result at the same time. They include Add Equal (+=), Subtract Equal (-=), Multiply Equal (*=), Divide Equal (/=), and Modulus Equal (%=).
Example:
```
SET @num = 20;
SET @num /= 2;  -- Sets @num to 10 (divides @num by 2 and assigns the result to @num)
```
Other Operators: MySQL also supports other operators such as the ternary operator (CASE WHEN ... THEN ... ELSE ... END), the NULL-safe equal to operator (<=>), the LIKE operator for pattern matching, the IN operator to check if a value is within a list of values, and the BETWEEN operator to check if a value is within a range of values.
Example:
```
SELECT CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END AS salary_level FROM employees;
SELECT * FROM employees WHERE department_id IN (1, 2, 3);
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 60000;
```

This is just a brief overview. Each category of these operators has its own rules and use cases, and it's important to understand how they work in different contexts.

Arithmetic operators in MySQL:

Arithmetic operators perform mathematical operations on numeric values.

SELECT 5 + 3 AS addition, 5 - 3 AS subtraction, 5 * 3 AS multiplication, 5 / 3 AS division, 5 % 3 AS modulus;

Comparison operators in MySQL:

Comparison operators are used to compare values and return boolean results.

SELECT column1 = column2 AS equal, column1 != column2 AS not_equal, column1 > column2 AS greater_than,
       column1 < column2 AS less_than, column1 >= column2 AS greater_than_or_equal,
       column1 <= column2 AS less_than_or_equal;

Logical operators in MySQL:
- Logical operators perform logical operations and return boolean results.
```
SELECT * FROM table_name WHERE condition1 AND condition2 OR NOT condition3;
```

Bitwise operators in MySQL:

Bitwise operators perform bitwise operations on integer values.

SELECT 5 & 3 AS bitwise_and, 5 | 3 AS bitwise_or, 5 ^ 3 AS bitwise_xor, ~5 AS bitwise_not;

Assignment operators in MySQL:
- Assignment operators are used to assign values to variables.
```
SET @variable = 10;
```
Concatenation operators in MySQL:
- Concatenation operators are used to concatenate strings.
```
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
```
IN and NOT IN operators in MySQL:
- IN and NOT IN operators are used to filter rows based on a set of values.
```
SELECT * FROM products WHERE category_id IN (1, 3, 5);
```

Combining operators in MySQL expressions:

Operators can be combined to create complex expressions.

SELECT * FROM orders WHERE (status = 'shipped' OR status = 'delivered') AND total_amount > 100;