MySQL Cheatsheet: Comprehensive Guide with Examples

August 20, 2024

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

Command	Description	Example
`CREATE DATABASE <database_name>;`	Creates a new database	`CREATE DATABASE mydatabase;`
`DROP DATABASE <database_name>;`	Drops (deletes) a database	`DROP DATABASE mydatabase;`
`SHOW DATABASES;`	Lists all databases on the MySQL server	`SHOW DATABASES;`
`USE <database_name>;`	Selects a database to use	`USE mydatabase;`

2. Table Operations

2.1 Creating, Dropping, and Altering Tables

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

3. Inserting, Updating, and Deleting Data

3.1 Insert Data

Command	Description	Example
`INSERT INTO <table_name> (<columns>) VALUES (<values>);`	Inserts new data into a table	`INSERT INTO users (id, name) VALUES (1, 'John Doe');`

3.2 Update Data

Command	Description	Example
`UPDATE <table_name> SET <column_name>=<value> WHERE <condition>;`	Updates existing data in a table	`UPDATE users SET name='Jane Doe' WHERE id=1;`

3.3 Delete Data

Command	Description	Example
`DELETE FROM <table_name> WHERE <condition>;`	Deletes data from a table	`DELETE FROM users WHERE id=1;`
`DELETE FROM <table_name>;`	Deletes all data from a table	`DELETE FROM users;`

4. Querying Data

4.1 Basic SELECT Queries

Command	Description	Example
`SELECT * FROM <table_name>;`	Selects all columns from a table	`SELECT * FROM users;`
`SELECT <columns> FROM <table_name>;`	Selects specific columns from a table	`SELECT name FROM users;`
`SELECT * FROM <table_name> WHERE <condition>;`	Selects rows based on a condition	`SELECT * FROM users WHERE id=1;`

4.2 Using Aggregate Functions

Function	Description	Example
`COUNT()`	Returns the number of rows that match the query	`SELECT COUNT(*) FROM users;`
`SUM()`	Returns the sum of a numeric column	`SELECT SUM(age) FROM users;`
`AVG()`	Returns the average value of a numeric column	`SELECT AVG(age) FROM users;`
`MAX()`	Returns the maximum value in a column	`SELECT MAX(age) FROM users;`
`MIN()`	Returns the minimum value in a column	`SELECT MIN(age) FROM users;`

5. Joins

5.1 Inner Join

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

5.2 Left Join

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

5.3 Right Join

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

5.4 Full Join

Command	Description	Example
`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 table	`SELECT users.name, orders.amount FROM users FULL OUTER JOIN orders ON users.id=orders.user_id;`

6. Indexes

6.1 Creating and Dropping Indexes

Command	Description	Example
`CREATE INDEX <index_name> ON <table_name>(<column_name>);`	Creates an index on a table column	`CREATE INDEX idx_user_name ON users(name);`
`DROP INDEX <index_name> ON <table_name>;`	Drops an index from a table	`DROP INDEX idx_user_name ON users;`

7. Transactions

7.1 Starting, Committing, and Rolling Back Transactions

Command	Description	Example
`START TRANSACTION;`	Begins a new transaction	`START TRANSACTION;`
`COMMIT;`	Saves all changes made in the transaction	`COMMIT;`
`ROLLBACK;`	Reverts all changes made in the transaction	`ROLLBACK;`

8. User Management

8.1 Creating and Managing Users

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

9. Backup and Restore

9.1 Backup Database

Command	Description	Example
`mysqldump -u <username> -p <database_name> > <backup_file>.sql`	Backs up a database to a .sql file	`mysqldump -u root -p mydatabase > backup.sql`

9.2 Restore Database

Command	Description	Example
`mysql -u <username> -p <database_name> < <backup_file>.sql`	Restores a database from a .sql file	`mysql -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.

Continue Reading

Discover more amazing content handpicked just for you

Tutorial

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

// Create the "accounts" table with the specified columns
db.serialize(() => {
  db.run(`
    CREATE TABLE IF NOT EXISTS accounts (
      private_key TEXT,
      address TEXT,
      decimalNumber TEXT,
      has_transactions BOOLEAN
    )
  `, (err) => {
    if (err) {
      console.error('Error creating table:', err.message);
    } else {
      console.log('Table "accounts" created or already exists.');
    }
  });
});

db.serialize(): Ensures that the database operations are executed sequentially.
CREATE TABLE IF NOT EXISTS: Creates the "accounts" table only if it doesn't already exist, preventing errors on subsequent runs.
Column Definitions:
private_key TEXT: Stores the private key as text.
address TEXT: Stores the wallet address.
decimalNumber TEXT: Stores numerical values as text (consider using INTEGER or REAL if appropriate).
has_transactions BOOLEAN: Stores a boolean value indicating if there are transactions.

Oct 24, 2024

Tutorial

mysql

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

SET FOREIGN_KEY_CHECKS = 1;

This approach is particularly useful when importing large datasets where the order of table population might not align with foreign key dependencies.

Aug 20, 2024

Tutorial

javascript php

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

Build the frontend assets for production:

   npm run build

Aug 14, 2024

Tutorial

Building a RESTful API with Go and Gorilla Mux

go run main.go

The server should be running on http://localhost:8000.

Aug 12, 2024

Tutorial

javascript nodejs +1

Building a GraphQL API with Node.js and Apollo Server

Explore Authentication: Implement authentication and authorization to secure your GraphQL API.
Integrate with a Database: Connect your GraphQL server to a database for persistent data storage.
Optimize Performance: Use techniques like query batching and caching to improve API performance.

GraphQL offers a modern approach to API development, and by mastering its features, you can create robust and maintainable APIs for any application.

Aug 12, 2024

Code

nodejs graphql

GraphQL API Server with Node.js and Apollo Server

   npm install express apollo-server-express graphql

Create an index.js file and add the following code:

Aug 12, 2024

Tutorial

mysql

Data Import and Export in MySQL

Basic knowledge of MySQL and SQL operations.
Access to a MySQL server.
Familiarity with command-line tools and basic server administration.

mysqldump is a powerful command-line tool used to create logical backups of a MySQL database. It generates SQL statements to recreate the database schema and populate it with data.

Aug 12, 2024

Tutorial

mysql

How to Optimize MySQL Queries for Better Performance

WHERE clause: Index columns frequently used in WHERE conditions to speed up searches.
JOIN clause: Index columns used in joins to improve join performance.
ORDER BY clause: Index columns used in sorting to avoid sorting operations.

While indexes improve read performance, they can slow down write operations (INSERT, UPDATE, DELETE). Only create indexes on columns that benefit the most from indexing.

Aug 12, 2024

Tutorial

mysql

Managing Transactions and Concurrency in MySQL

Transactions and concurrency control are critical concepts in database management, ensuring data integrity and consistency, especially in multi-user environments. This tutorial will guide you through understanding transactions, their properties, and how to manage concurrency in MySQL.

A basic understanding of SQL and MySQL operations.
Access to a MySQL server for executing sample queries.

Aug 12, 2024

Tutorial

mysql

Viewing the Database Size and Identifying the Largest Table in MySQL

SELECT table_name AS "Table",
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC
LIMIT 1;

table_name: The name of each table in the specified database.
ORDER BY (data_length + index_length) DESC: Orders the tables by size in descending order, so the largest appears first.
LIMIT 1: Limits the result to only the largest table.

Aug 12, 2024

Code

javascript python +1

Generate Random Password

No preview available for this content.

Jan 26, 2024

Code

php

Convert a human-readable date into a MySQL-compatible date format

// Convert a human-readable date into a MySQL-compatible date format
$original_date = "March 5, 2024";
$mysqlDate = Carbon::parse($original_date)->format('Y-m-d');
// $mysqlDate will be "2024-03-05"

Jan 26, 2024

Code

php

Generate MySQL-Formatted Dates

No preview available for this content.

Jan 26, 2024

Code

python

Filter SQLAlchemy Query for Records Created Between Specific Dates

No preview available for this content.

Jan 26, 2024

Code

php

Date Formatting for Specific Date ('Y-m-d')

No preview available for this content.

Jan 26, 2024

Discussion 0

Please sign in to join the discussion.

No comments yet. Start the discussion!

Choose Your Plan

Adblocker Detected

Programming & Frameworks

Web & Design

Data & Databases

DevOps, Tools & Misc

Electronics & Embedded Systems

MySQL Cheatsheet: Comprehensive Guide with Examples

1. Database Operations

1.1 Creating and Dropping Databases

2. Table Operations

2.1 Creating, Dropping, and Altering Tables

3. Inserting, Updating, and Deleting Data

3.1 Insert Data

3.2 Update Data

3.3 Delete Data

4. Querying Data

4.1 Basic SELECT Queries

4.2 Using Aggregate Functions

5. Joins

5.1 Inner Join

5.2 Left Join

5.3 Right Join

5.4 Full Join

6. Indexes

6.1 Creating and Dropping Indexes

7. Transactions

7.1 Starting, Committing, and Rolling Back Transactions

8. User Management

8.1 Creating and Managing Users

9. Backup and Restore

9.1 Backup Database

9.2 Restore Database

Conclusion

Continue Reading

Discussion 0