Complete Guide to Install and Use MySQL in 2025

MySQL has established itself as one of the most popular and reliable database management systems in the realm of web development and data management. In this guide, we will detail the necessary steps to install and start using MySQL in 2025, along with basic concepts and functions that will help new users familiarize themselves with the environment.

MySQL Installation

What is MySQL Workbench?

MySQL Workbench is a visual tool for database architects, developers, and database administrators (DBAs). This application provides data modeling capabilities, SQL development, and comprehensive tools for server administration, user management, backups, and many more functions.

Installation on Windows and macOS

1. Download from: MySQL Installer.
2. Run the installer and select "Developer Default."
3. Configure a root password when prompted.
4. Install MySQL Workbench (optional, but useful as a GUI).

Installation on Linux (Ubuntu)

To install MySQL and create a user, follow these steps:

Step 1: Update the package index

sudo apt update

Step 2: Install MySQL Server

sudo apt install mysql-server

Step 3: Secure the installation

sudo mysql_secure_installation

Select your options (accept most).

Step 4: Create a user 'harry'@'localhost'

Log into MySQL:

sudo mysql

Run the following SQL commands:

CREATE USER 'harry'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'harry'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

Step 5: Test the login

mysql -u harry -p

Enter the password: password.

Note: Make sure to replace 'password' with a secure password of your choice in production environments.

Introduction to MySQL

What is a database?

A database is a container that stores related data in an organized manner. In MySQL, a database contains one or more tables.

You can think of a database as:

• Folder analogy:
  • A database is like a folder.
  • Each table is a file within that folder.
  • The rows in the table are like the content within each file.
• Excel analogy:
  • A database is like an Excel workbook.
  • Each table is a separate sheet within that workbook.
  • Each row in the table is like a row in Excel.

Getting Started with MySQL

Step 1: Create a database

CREATE DATABASE startersql;

Then, to use it, you can:

• Right-click on it in MySQL Workbench and select "Set as Default Schema."
• Or use the following SQL command:

USE startersql;

Step 2: Create a table

Now you will create a simple users table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    gender ENUM('Male', 'Female', 'Other'),
    date_of_birth DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This table will store basic information about users.

Step 3: Drop the database

You can drop the entire database (and all its tables) using:

DROP DATABASE startersql;

Be careful: this will delete everything in that database.

Data Types in MySQL

• INT: Integer type, used for whole numbers.
• VARCHAR(100): Variable-length string, up to 100 characters.
• ENUM: String object with a value chosen from a list of permitted values.
• DATE: Stores date values.
• TIMESTAMP: Stores date and time, automatically set to the current date and time when creating a row.
• BOOLEAN: Stores TRUE or FALSE values, often used for indicators.
• DECIMAL(10, 2): Stores exact numeric values, useful for financial data.

Constraints in MySQL

• AUTO_INCREMENT: Automatically generates a unique number for each row.
• PRIMARY KEY: Uniquely identifies each row in the table.
• NOT NULL: Ensures that a column cannot have a NULL value.
• UNIQUE: Ensures that all values in a column are different.
• DEFAULT: Sets a default value for a column if no value is provided.

Working with Tables in MySQL

Selecting Data from a Table

Select all columns

SELECT * FROM users;

This retrieves all columns and rows from the users table.

Select specific columns

SELECT name, email FROM users;

This retrieves only the name and email columns from all rows.

Renaming a Table

To rename an existing table:

RENAME TABLE users TO customers;

To rename it back:

RENAME TABLE customers TO users;

Altering a Table

You can use ALTER TABLE to modify an existing table.

• Add a column

ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

• Drop a column

ALTER TABLE users DROP COLUMN is_active;

• Modify a column's type

ALTER TABLE users MODIFY COLUMN name VARCHAR(150);

• Move a column to the first position

To move a column (e.g., email) to the first position:

ALTER TABLE users MODIFY COLUMN email VARCHAR(100) FIRST;

To move a column after another column:

ALTER TABLE users MODIFY COLUMN gender ENUM('Male', 'Female', 'Other') AFTER name;

Inserting Data into MySQL Tables

To add data to a table, we use the INSERT INTO command.

Insert without specifying column names

INSERT INTO users VALUES
(1, 'Alice', '[email protected]', 'Female', '1995-05-14', DEFAULT);

Note: This is not recommended if the table structure may change.

Insert specifying column names

INSERT INTO users (name, email, gender, date_of_birth) VALUES
('Bob', '[email protected]', 'Male', '1990-11-23');

Or for multiple rows:

INSERT INTO users (name, email, gender, date_of_birth) VALUES
('Bob', '[email protected]', 'Male', '1990-11-23'),
('Charlie', '[email protected]', 'Other', '1988-02-17');

Querying Data in MySQL with SELECT

The SELECT command is used to query data from a table.

Basic syntax

SELECT column1, column2 FROM table_name;

Filtering Rows with WHERE

• Equal to

SELECT * FROM users WHERE gender = 'Male';

• Not equal to

SELECT * FROM users WHERE gender != 'Female';

• Greater than / less than

SELECT * FROM users WHERE date_of_birth < '1995-01-01';

Updating Existing Data

The UPDATE command is used to change values in one or more rows.

Basic syntax

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Deleting Data from a Table

The DELETE command is used to remove rows from a table.

Basic syntax

DELETE FROM table_name
WHERE condition;

Best Practices

• Always use WHERE unless you intend to update or delete everything.
• Consider running a SELECT with the same WHERE clause first to confirm what will be affected.
• Always back up important data before performing destructive operations.

Conclusion

MySQL is a powerful tool that, when used correctly, can facilitate efficient data management. From the initial installation to querying and transactions, understanding these basic concepts will provide a solid foundation for exploring more about MySQL.

To learn more about topics related to databases and technology, you are invited to explore more on the blog.