MySQL: Comprehensive Guide

1. Installation

Ubuntu/Debian:

sudo apt update

sudo apt install mysql-server

CentOS/RHEL:

sudo yum install mysql-server

Start MySQL Service:

sudo systemctl start mysql

sudo systemctl enable mysql

2. Securing MySQL Installation

sudo mysql_secure_installation

Follow the prompts to set a root password and secure your installation.

3. Connecting to MySQL

mysql -u root -p

Log in to MySQL as the root user.

4. Basic MySQL Commands

Show Databases:

SHOW DATABASES;

Create a Database:

CREATE DATABASE mydatabase;

Use a Database:

USE mydatabase;

Show Tables:

SHOW TABLES;

Create a Table:

        CREATE TABLE users (

            id INT AUTO_INCREMENT PRIMARY KEY,

            username VARCHAR(50),

            password VARCHAR(255)

        );
        

Insert Data:

INSERT INTO users (username, password) VALUES ('admin', 'password123');

Select Data:

SELECT * FROM users;

Update Data:

UPDATE users SET password='newpassword' WHERE username='admin';

Delete Data:

DELETE FROM users WHERE username='admin';

5. User Management

Create a New User:

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

Grant Privileges:

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

Show Grants for a User:

SHOW GRANTS FOR 'newuser'@'localhost';

Revoke Privileges:

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

6. Backup and Restore

Backup a Database:

mysqldump -u root -p mydatabase > mydatabase_backup.sql

Restore a Database:

mysql -u root -p mydatabase < mydatabase_backup.sql

7. Using SQL Queries

Where Clause:

SELECT * FROM users WHERE username='admin';

Order By:

SELECT * FROM users ORDER BY id DESC;

Join Tables:

        SELECT users.username, orders.amount

        FROM users

        JOIN orders ON users.id = orders.user_id;
        

8. Handling Errors

Check Error Codes:

SHOW ERRORS;

Display Last Error:

SELECT LAST_INSERT_ID();

9. Optimizing Performance

Indexing:

CREATE INDEX idx_username ON users (username);

Analyze Table:

ANALYZE TABLE users;

10. Exiting MySQL

EXIT;

Example Scenarios

1. Creating a Database and Table:

        CREATE DATABASE shop;

        USE shop;

        CREATE TABLE products (

            id INT AUTO_INCREMENT PRIMARY KEY,

            name VARCHAR(100),

            price DECIMAL(10, 2)

        );
        

2. Inserting Data into the Table:

        INSERT INTO products (name, price) VALUES ('Laptop', 999.99);

        INSERT INTO products (name, price) VALUES ('Smartphone', 499.99);
        

3. Querying the Table:

SELECT * FROM products;

4. Updating a Record:

UPDATE products SET price = 899.99 WHERE name = 'Laptop';

5. Deleting a Record:

DELETE FROM products WHERE name = 'Smartphone';