DeveloperBreeze

MySQL Cheatsheet: Comprehensive Guide with Examples

mysql
Database Management Transactions Backend development MySQL commands database operations data querying backup and restore user management MySQL cheatsheet

Introduction

MySQL is a popular open-source relational database management system used by developers and businesses worldwide. It supports a wide range of operations that allow you to create, manage, and manipulate data efficiently. This comprehensive cheatsheet covers essential MySQL commands and concepts, complete with examples presented in HTML tables for easy reference.

1. Database Operations

1.1 Creating and Dropping Databases

CommandDescriptionExample
CREATE DATABASE <database_name>;Creates a new databaseCREATE DATABASE mydatabase;
DROP DATABASE <database_name>;Drops (deletes) a databaseDROP DATABASE mydatabase;
SHOW DATABASES;Lists all databases on the MySQL serverSHOW DATABASES;
USE <database_name>;Selects a database to useUSE mydatabase;

2. Table Operations

2.1 Creating, Dropping, and Altering Tables

CommandDescriptionExample
CREATE TABLE <table_name> (<column_definitions>);Creates a new tableCREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));
DROP TABLE <table_name>;Drops (deletes) a tableDROP TABLE users;
ALTER TABLE <table_name> ADD <column_definition>;Adds a new column to an existing tableALTER TABLE users ADD email VARCHAR(100);
ALTER TABLE <table_name> DROP COLUMN <column_name>;Drops a column from an existing tableALTER TABLE users DROP COLUMN email;

3. Inserting, Updating, and Deleting Data

3.1 Insert Data

CommandDescriptionExample
INSERT INTO <table_name> (<columns>) VALUES (<values>);Inserts new data into a tableINSERT INTO users (id, name) VALUES (1, 'John Doe');

3.2 Update Data

CommandDescriptionExample
UPDATE <table_name> SET <column_name>=<value> WHERE <condition>;Updates existing data in a tableUPDATE users SET name='Jane Doe' WHERE id=1;

3.3 Delete Data

CommandDescriptionExample
DELETE FROM <table_name> WHERE <condition>;Deletes data from a tableDELETE FROM users WHERE id=1;
DELETE FROM <table_name>;Deletes all data from a tableDELETE FROM users;

4. Querying Data

4.1 Basic SELECT Queries

CommandDescriptionExample
SELECT * FROM <table_name>;Selects all columns from a tableSELECT * FROM users;
SELECT <columns> FROM <table_name>;Selects specific columns from a tableSELECT name FROM users;
SELECT * FROM <table_name> WHERE <condition>;Selects rows based on a conditionSELECT * FROM users WHERE id=1;

4.2 Using Aggregate Functions

FunctionDescriptionExample
COUNT()Returns the number of rows that match the querySELECT COUNT(*) FROM users;
SUM()Returns the sum of a numeric columnSELECT SUM(age) FROM users;
AVG()Returns the average value of a numeric columnSELECT AVG(age) FROM users;
MAX()Returns the maximum value in a columnSELECT MAX(age) FROM users;
MIN()Returns the minimum value in a columnSELECT MIN(age) FROM users;

5. Joins

5.1 Inner Join

CommandDescriptionExample
SELECT * FROM <table1> INNER JOIN <table2> ON <table1.column>=<table2.column>;Returns records that have matching values in both tablesSELECT users.name, orders.amount FROM users INNER JOIN orders ON users.id=orders.user_id;

5.2 Left Join

CommandDescriptionExample
SELECT * FROM <table1> LEFT JOIN <table2> ON <table1.column>=<table2.column>;Returns all records from the left table and matched records from the right tableSELECT users.name, orders.amount FROM users LEFT JOIN orders ON users.id=orders.user_id;

5.3 Right Join

CommandDescriptionExample
SELECT * FROM <table1> RIGHT JOIN <table2> ON <table1.column>=<table2.column>;Returns all records from the right table and matched records from the left tableSELECT users.name, orders.amount FROM users RIGHT JOIN orders ON users.id=orders.user_id;

5.4 Full Join

CommandDescriptionExample
SELECT * FROM <table1> FULL OUTER JOIN <table2> ON <table1.column>=<table2.column>;Returns all records when there is a match in either left or right tableSELECT users.name, orders.amount FROM users FULL OUTER JOIN orders ON users.id=orders.user_id;

6. Indexes

6.1 Creating and Dropping Indexes

CommandDescriptionExample
CREATE INDEX <index_name> ON <table_name>(<column_name>);Creates an index on a table columnCREATE INDEX idx_user_name ON users(name);
DROP INDEX <index_name> ON <table_name>;Drops an index from a tableDROP INDEX idx_user_name ON users;

7. Transactions

7.1 Starting, Committing, and Rolling Back Transactions

CommandDescriptionExample
START TRANSACTION;Begins a new transactionSTART TRANSACTION;
COMMIT;Saves all changes made in the transactionCOMMIT;
ROLLBACK;Reverts all changes made in the transactionROLLBACK;

8. User Management

8.1 Creating and Managing Users

CommandDescriptionExample
CREATE USER '<username>'@'<host>' IDENTIFIED BY '<password>';Creates a new MySQL userCREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';
GRANT ALL PRIVILEGES ON <database_name>.* TO '<username>'@'<host>';Grants all privileges to a user on a specific databaseGRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser'@'localhost';
REVOKE <privilege> ON <database_name>.* FROM '<username>'@'<host>';Revokes a specific privilege from a userREVOKE SELECT ON mydatabase.* FROM 'newuser'@'localhost';
DROP USER '<username>'@'<host>';Deletes a MySQL userDROP USER 'newuser'@'localhost';

9. Backup and Restore

9.1 Backup Database

CommandDescriptionExample
mysqldump -u <username> -p <database_name> > <backup_file>.sqlBacks up a database to a .sql filemysqldump -u root -p mydatabase > backup.sql

9.2 Restore Database

CommandDescriptionExample
mysql -u <username> -p <database_name> < <backup_file>.sqlRestores a database from a .sql filemysql -u root -p mydatabase < backup.sql

Conclusion

This MySQL cheatsheet provides a comprehensive overview of the most commonly used MySQL commands, complete with examples to help you quickly find the information you need. Whether you're creating and managing databases, writing queries, or handling transactions, this guide serves as a quick reference to help you work more efficiently with MySQL.

Related Posts

More content you might like

Tutorial

Connecting a Node.js Application to an SQLite Database Using sqlite3

Create a JavaScript file named app.js in your project directory and open it in your preferred code editor.

touch app.js

Oct 24, 2024
Read More
Tutorial
mysql

Mastering MySQL Data Management – Backups, Restorations, and Table Operations

Once you have a backup, you can restore it to your database using the mysql command with the SOURCE keyword.

Command to Restore from a Backup:

Aug 20, 2024
Read More
Tutorial
javascript php

Integrating Laravel and React with Vite: Using Databases and PHP in a Full-Stack Project

In the Post model (app/Models/Post.php), define the fields that can be mass assigned:

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    use HasFactory;

    protected $fillable = ['title', 'body'];
}

Aug 14, 2024
Read More
Tutorial
go

Building a RESTful API with Go and Gorilla Mux 

func deleteBook(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "application/json")
	params := mux.Vars(r)
	for index, item := range books {
		if item.ID == params["id"] {
			books = append(books[:index], books[index+1:]...)
			break
		}
	}
	json.NewEncoder(w).Encode(books)
}

Start the server by running the following command in your terminal:

Aug 12, 2024
Read More

Discussion 0

Please sign in to join the discussion.

No comments yet. Be the first to share your thoughts!