MySQL Documentation

Welcome to the MySQL Documentation! This guide is designed to help you master MySQL, including installation, basic commands, advanced queries, and administration tasks.

Getting Started

Installation

Ubuntu:

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql
sudo systemctl enable mysql

macOS (Homebrew):

brew install mysql
brew services start mysql

Windows:
Download the MySQL Installer from the official website.
Follow the installation wizard and set the root password.

Verifying Installation

To confirm MySQL is installed:

mysql --version

Basic Commands

Starting and Stopping MySQL Service

Start MySQL:
```
sudo systemctl start mysql
```
Stop MySQL:
```
sudo systemctl stop mysql
```
Restart MySQL:
```
sudo systemctl restart mysql
```

Connecting to MySQL

To connect as the root user:

mysql -u root -p

- -u: Specifies the username. - -p: Prompts for the password.

Database Operations

Create a Database

CREATE DATABASE database_name;

- Example:

CREATE DATABASE test_db;

Show Databases

SHOW DATABASES;

Select a Database

USE database_name;

Drop a Database

DROP DATABASE database_name;

Table Operations

Create a Table

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

- Example:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

Show Tables

SHOW TABLES;

Describe a Table

DESCRIBE table_name;

Drop a Table

DROP TABLE table_name;

CRUD Operations

Insert Data

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

- Example:

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

Select Data

SELECT column1, column2 FROM table_name;

- Example:

SELECT * FROM users;

Update Data

UPDATE table_name SET column1 = value1 WHERE condition;

- Example:

UPDATE users SET email = 'new_email@example.com' WHERE id = 1;

Delete Data

DELETE FROM table_name WHERE condition;

- Example:

DELETE FROM users WHERE id = 1;

User Management

Create a User

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

Grant Privileges

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

Show User Privileges

SHOW GRANTS FOR 'username'@'localhost';

Revoke Privileges

REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'localhost';

Drop a User

DROP USER 'username'@'localhost';

Backup and Restore

Backup a Database

mysqldump -u username -p database_name > backup.sql

Restore a Database

mysql -u username -p database_name < backup.sql

Common Administrative Tasks

Reset Root Password

Stop MySQL:
```
sudo systemctl stop mysql
```
Start MySQL in Safe Mode:
```
sudo mysqld_safe --skip-grant-tables &
```
Connect to MySQL:
```
mysql -u root
```

Reset the Password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Restart MySQL:
```
sudo systemctl restart mysql
```

Check MySQL Status

sudo systemctl status mysql

Advanced Queries

Joins

Inner Join:

SELECT * FROM table1
INNER JOIN table2 ON table1.id = table2.table1_id;

Left Join:

SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.table1_id;

Right Join:

SELECT * FROM table1
RIGHT JOIN table2 ON table1.id = table2.table1_id;

Indexing and Optimization

Create an Index

CREATE INDEX index_name ON table_name (column_name);

Drop an Index

DROP INDEX index_name ON table_name;

Troubleshooting

View Error Logs

On Linux:
```
sudo tail -f /var/log/mysql/error.log
```

Check for Running Queries

SHOW FULL PROCESSLIST;

Resources

This documentation is structured to provide step-by-step instructions for MySQL usage and administration tasks. For a complete guide, refer to the official documentation. ```