MySQL Installation

Uninstalling MySQL

Before installing MySQL, it's important to properly uninstall any previous versions:

1. Stop the MySQL service through the Task Manager
2. Uninstall MySQL via Control Panel or using the installer's uninstall option
3. Clean up residual files in installation and data directories
4. (Optional) Clean registry entries if issues persist
5. Remove MySQL-related environment variables

Downloading and Installing MySQL

MySQL offers several versions:

• MySQL Community Server (open source, free)
• MySQL Enterprise Edition (commercial)
• MySQL Cluster (for high availability)
• MySQL Cluster CGE (advanced cluster edition)

The latest stable version is 8.0.27. For this guide, we'll use version 8.0.26.

Installation Steps

1. Download the MySQL installer from the official website
2. Run the installer and choose "Custom" installation type
3. Select MySQL Server and other desired components
4. Configure installation paths (avoid using Chinese characters)
5. Execute the installation

Post-Installation Configuration

1. Set server type (Development Machine, Server Machine, or Dedicated Machine)
2. Configure authentication method
3. Set root password
4. Configure service name and startup options
5. Complete the configuration

Environment Variables

Add the MySQL bin directory to your system's PATH variable to enable command-line access:

C:\Program Files\MySQL\MySQL Server 8.0\bin

MySQL Operators

Arithmetic Operators

MySQL supports standard arithmetic operations:

• Addition (+)
• Subtraction (-)
• Multiplication (*)
• Division (/)
• Modulo (%)

Example:

SELECT 100 + 50, 100 - 30, 100 * 2, 100 / 3, 100 % 3;

Comparison Operators

Comparison operators evaluate conditions and return 1 (true), 0 (false), or NULL:

• Equal (=)
• Not equal (<> or !=)
• Greater than (>)
• Less than (<)
• Greater than or equal (>=)
• Less than or equal (<=)
• Safe equal (<=>) - handles NULL values
• IS NULL / IS NOT NULL
• BETWEEN ... AND
• IN / NOT IN
• LIKE / NOT LIKE

Example:

SELECT * FROM products WHERE price > 100 AND category_id IN (1, 3, 5);

Logical Operators

Logical operators combine multiple conditions:

• AND - all conditions must be true
• OR - at least one condition must be true
• NOT - negates a condition
• XOR - exclusive OR

Example:

SELECT * FROM employees WHERE (department_id = 10 OR salary > 5000) AND hire_date > '2020-01-01';

Bitwise Operators

Bitwise operators perform operations on binary representations of values:

• Bitwise AND (&)
• Bitwise OR (|)
• Bitwise XOR (^)
• Bitwise NOT (~)
• Bitwise shift left (<<)
• Bitwise shift right (>>)

Example:

SELECT 12 & 5, 12 | 5, 12 ^ 5, ~12, 12 << 2, 12 >> 2;

Sorting Data

Basic Sorting

Use the ORDER BY clause to sort query results:

• ASC - ascending order (default)
• DESC - descending order

Example:

SELECT employee_id, last_name, hire_date FROM employees ORDER BY hire_date DESC;

Multi-Column Sorting

When sorting by multiple columns, the first sort takes priority, then the second, and so on:

SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;

Sorting by Expressions

You can sort by calculated expressions:

SELECT employee_id, last_name, salary*12 annual_salary FROM employees ORDER BY annual_salary DESC;

Pagination

Using LIMIT for Pagination

The LIMIT clause restricts the number of rows returned:

SELECT * FROM products LIMIT 0, 10;  -- First 10 records

SELECT * FROM products LIMIT 10, 10; -- Records 11-20

In MySQL 8.0+, you can use the following syntax:

SELECT * FROM products LIMIT 10 OFFSET 20; -- Records 21-30

Pagination Formula

To implement pagination, use this formula:

SELECT * FROM table LIMIT (currentPage - 1) * pageSize, pageSize;

Example for page 3 with 10 records per page:

SELECT * FROM products LIMIT 20, 10;

Performance Considerations

Using LIMIT improves performance by:

• Reducing network transfer of data
• Limiting the result set size
• Allowing early termination of queries when possible

For example, if you only need one record, use LIMIT 1:

SELECT * FROM users WHERE email = 'example@test.com' LIMIT 1;