Skip to main content

MySQL Tutorial

MySQL is a widely-used open-source relational database management system (RDBMS). This tutorial covers the basics of using MySQL, including setup, configuration, and common SQL commands for managing databases and data.

Prerequisites​

  1. MySQL Installation: Ensure MySQL is installed on your system. Verify the installation with:

    mysql --version

    If it’s not installed, use your package manager, for example on Ubuntu:

    sudo apt update
    sudo apt install mysql-server

  2. Basic Knowledge of SQL: Familiarity with SQL basics will be helpful.

1. Starting and Stopping MySQL​

To start the MySQL service:

sudo systemctl start mysql

To stop the MySQL service:

sudo systemctl stop mysql

To enable MySQL to start on boot:

sudo systemctl enable mysql

2. Logging into MySQL​

To log in to the MySQL client as the root user:

sudo mysql -u root -p

Enter your password when prompted.

3. Basic SQL Commands in MySQL​

Creating a Database​

To create a new database:

CREATE DATABASE example_db;

Selecting a Database​

To use a specific database:

USE example_db;

Creating a Table​

To create a table named users with some basic fields:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Inserting Data​

To insert data into the users table:

INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

Querying Data​

To retrieve all records from the users table:

SELECT * FROM users;

Updating Data​

To update a user’s name in the users table:

UPDATE users SET name = 'Jane Doe' WHERE id = 1;

Deleting Data​

To delete a user from the users table:

DELETE FROM users WHERE id = 1;

4. User Management in MySQL​

Creating a New User​

To create a new user with a password:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Granting Privileges​

To grant all privileges on a specific database:

GRANT ALL PRIVILEGES ON example_db.* TO 'newuser'@'localhost';

Revoking Privileges​

To revoke privileges from a user:

REVOKE ALL PRIVILEGES ON example_db.* FROM 'newuser'@'localhost';

Deleting a User​

To delete a user:

DROP USER 'newuser'@'localhost';

5. Backing Up and Restoring Databases​

Backing Up a Database​

To back up a MySQL database using mysqldump:

mysqldump -u root -p example_db > example_db_backup.sql

Restoring a Database​

To restore a MySQL database from a backup file:

mysql -u root -p example_db < example_db_backup.sql

6. Useful MySQL Commands​

Show Databases​

To list all databases:

SHOW DATABASES;

Show Tables​

To list all tables in the selected database:

SHOW TABLES;

Describe Table Structure​

To show the structure of a table:

DESCRIBE users;

Exiting the MySQL Client​

To exit the MySQL client:

EXIT;

Summary​

This tutorial introduced the basics of using MySQL, including:

  1. Starting and stopping the MySQL service.
  2. Creating and managing databases and tables.
  3. Performing CRUD (Create, Read, Update, Delete) operations.
  4. Managing users and privileges.
  5. Backing up and restoring databases.

MySQL is a powerful and versatile database system, well-suited for both small and large-scale applications. Experiment with SQL commands to get comfortable with database operations in MySQL.

Content Review​

The content in this repository has been reviewed by chevp. Chevp is dedicated to ensuring that the information provided is accurate, relevant, and up-to-date, helping users to learn and implement programming skills effectively.

About the Reviewer​

For more insights and contributions, visit chevp's GitHub profile: chevp's GitHub Profile.