Logo

MySQL Commands Cheat Sheet: The Ultimate Guide

Try this guide with our instant dedicated server for as low as 40 Euros

MySQL Commands

MySQL provides a dependable data storage and manipulation foundation for web apps, data warehousing, and other database-driven operations.

Knowing the basics of common MySQL statements and operations is essential for all users who work with projects that require database development and management. These statements allow users to manage databases, database tables, and data efficiently, ensuring smooth operation and optimal performance.

Our MySQL commands cheat sheet presents a quick reference for commonly used statements and functions. The core idea is to boost productivity, minimize errors, and serve as a learning resource and a quick reference guide.

By understanding and utilizing this cheat sheet, users can efficiently streamline database operations, perform essential tasks like backups and user management, and troubleshoot problems.

Table of Contents

  1. Popular MySQL Functions
    1. Connect to a MySQL Database
    2. Database Operations
    3. Data Manipulation
    4. Table Structure Modification
    5. Indexes and Keys
    6. User Management
    7. Backup and Restore
    8. Performance and Optimization
    9. Query Optimization
    10. Maintenance and Troubleshooting
    11. Monitoring
    12. Troubleshoot Common Issues
    13. Performance Tuning
  2. Conclusion
  3. FAQs

Popular MySQL Functions

The following sections discuss some of the commonly used MySQL statements for managing various aspects of MySQL operations.

Connect to a MySQL Database

Connecting to a MySQL database is the initial step in performing database operations. This involves logging into the MySQL server with appropriate credentials and accessing the database and tables.

Let’s discuss several common commands and statements for connecting to MySQL.

Function #1: Log in

To log in to a MySQL server, run this command in the terminal:

> mysql -u [username] -p

Function #2: Select a Database

If you have multiple databases on your server, we recommend using the USE statement in the following syntax:

mysql> USE [database_name];

Function #3: Log Out

When you are finished working with the database, we recommend logging off to close the connection and ensure a clean exit from the database environment.

To log out from the MySQL server, execute the exit statement.

mysql> exit

Function #3 Log Out

Database Operations

MySQL database operations involve creating, selecting, displaying, and deleting databases.

These fundamental statements help users manage database tables efficiently.

Let’s go through some of the most common statements that you can use for efficient database operations.

Function #4: Create a Database

The most fundamental operation is to create a new database. We suggest using the CREATE DATABASE statement followed by the desired database name.

mysql> CREATE DATABASE [database_name];

Function #4 Create a Database

Function #5: Select a Database

In most cases, you have multiple databases on your MySQL server. You need to make sure you have selected the right database before you can carry out further commands and statements.

You can select the database using the USE statement.

mysql> USE [database_name];

Function #5: Select a Database

Function #6: Display Databases

It is always a good idea to display the list of all databases on the MySQL server. We recommend using the SHOW DATABASES statement.

mysql> SHOW DATABASES;

Function #6: Display Databases

Function #7: Drop a Database

The idea of deleting a database is called “dropping” it. It is very important to use caution because the action is irreversible in most instances.

You can use the DROP DATABASE statement followed by the database name to delete an existing database. The syntax of this statement is as follows:

mysql> DROP DATABASE [database_name];

Data Manipulation

Data manipulation operations in MySQL involve inserting, selecting, updating, and deleting data within tables. These operations are crucial for managing and interacting with the data stored in your databases.

Here’s an overview of the most common data manipulation operation.

Function #8: Insert Data

To insert new records into a table, use the INSERT INTO statement followed by the table name, column names, and the values to be inserted.

mysql> INSERT INTO [table_name] ([column1], [column2], ...)

VALUES ([value1], [value2], ...);

Function #9: Select Data

To retrieve data from a table, use the SELECT statement followed by the columns you want to retrieve and the table name. Additionally, you can use the WHERE clause to add conditions to filter the results.

mysql> SELECT [column1], [column2], ...

FROM [table_name]

WHERE [condition];

Function #10: Update Data

We recommend the UPDATE statement followed by the table name, the column to be updated, and the new value to update existing records in the table.

You can also use the WHERE clause to specify which records to update.

mysql> UPDATE [table_name]

SET [column1] = [new_value1], [column2] = [new_value2]

WHERE [condition];

Function #11: Delete Data

You can delete records from a table with the DELETE FROM statement followed by the table name and the WHERE clause to specify which records to delete.

mysql> DELETE FROM [table_name] WHERE [condition];

Table Structure Modification

Modifying the table structure in MySQL is essential for adapting your database schema to changing requirements.

Common operations in this context include adding, modifying, and dropping columns using the ALTER TABLE statement.

Let’s go through some of these table modification operations.

Function #12: Add a Column

You can use the ADD clause with the ALTER TABLE statement to add a new column to an existing table:

mysql> ALTER TABLE [table_name]

ADD [column_name] [data_type] [constraints];

Function #13: Modify a Column

We recommend using the MODIFY statement to change the structure of an existing column. This operation can include changing the data type or constraints of the column.

mysql> ALTER TABLE [table_name]

MODIFY [column_name] [new_data_type] [new_constraints];

Function #14: Drop a Column

Like databases, you can delete a column from a database with the DROP clause. The syntax of this statement will be as follows:

mysql> ALTER TABLE [table_name]

DROP COLUMN [column_name];

Function #15: Rename a Column

We recommend using the CHANGE statement to rename an existing column in a database.

Note that you must specify the current column name followed by the new column name and the data type.

mysql> ALTER TABLE [table_name]

CHANGE [old_column_name] [new_column_name] [data_type] [constraints];

Function #16: Rename a Table

To rename an existing table, we recommend the RENAME TO clause with the ALTER TABLE statement:

mysql> ALTER TABLE [old_table_name]

RENAME TO [new_table_name];

Indexes and Keys

Indexes and keys are critical for optimizing query performance and ensuring data integrity in MySQL databases.

Indexes improve the speed of data retrieval operations, while keys (primary and foreign) enforce unique constraints and relationships between tables.

Here’s an overview of creating, showing, and dropping indexes and keys.

Function #17: Create an Index

You can use the CREATE INDEX statement followed by the index name, table name, and columns to be indexed to create an index for a table. The syntax of this statement is as follows:

mysql> CREATE INDEX [index_name] ON [table_name] ([column1], [column2], ...);

Function #18: Create a Primary Key

We recommend creating a primary key during table creation with the PRIMARY KEY clause. Additionally, you can also add a primary key to an existing table using ALTER TABLE. The syntax of the statement is as follows:

mysql> ALTER TABLE [table_name]

ADD PRIMARY KEY ([column1], [column2], ...);

Function #19: Create a Foreign Key

Creating foreign keys is crucial for maintaining data integrity, accurately modeling relationships, and improving query performance in a relational database system.

To create a foreign key, use the FOREIGN KEY clause that establishes a relationship between tables. We recommend the following statement syntax in this scenario:

mysql> ALTER TABLE [child_table]

ADD CONSTRAINT [fk_name] FOREIGN KEY ([column1], [column2], ...)

REFERENCES [parent_table] ([column1], [column2], ...);

Function #20: Display Indexes

You can use the SHOW INDEX statement to display the indexes on a table. The statement syntax in this context is as follows:

mysql> SHOW INDEX FROM [table_name];

Function #21: Drop an Index

You can use the DROP INDEX statement followed by the index name and table name to remove an index from a table.

mysql> DROP INDEX [index_name] ON [table_name];

Function #22: Drop a Primary Key

To remove a primary key from a table, use the DROP PRIMARY KEY clause in the ALTER TABLE statement.

mysql> ALTER TABLE [table_name] DROP PRIMARY KEY;

Function #23: Drop a Foreign Key

We recommend using the DROP FOREIGN KEY clause in the ALTER TABLE statement to remove a foreign key from a database.

mysql> ALTER TABLE [table_name] DROP FOREIGN KEY [fk_name];

User Management

User management in MySQL is essential for database security and access control. This involves creating users, granting and revoking privileges, and deleting users.

Here’s a detailed overview of each operation with relevant syntax:

Function #24: Create a User

You can create a new user with the CREATE USER statement followed by the username, host, and password. The sybntax is as follows:

mysql> CREATE USER '[username]'@'[host]' IDENTIFIED BY '[password]';

For instance, to create a user john_doe and password securepassword, execute the following statement:

mysql> CREATE USER 'john_doe'@'localhost' IDENTIFIED BY '[password]';

Function #24: Create a User

Function #25: Grant Appropriate Privileges

Before discussing how to grant privileges, let us understand some common privileges database administrators can grant to users.

Grant Appropriate Privileges

Administrators use the GRANT statement followed by the specific privileges, database, table, username, and host to grant specific privileges to a user. The syntax of this statement is:

mysql> GRANT [privileges] ON [database_name].[table_name] TO '[username]'@'[host]';

For instance, to grant SELECT and INSERT privileges on all tables in my_database to john_doe, execute the following statement:

mysql> GRANT SELECT, INSERT ON my_database.* TO 'john_doe'@'localhost';

Function #25: Grant Appropriate Privileges

Function #26: Revoke Privileges

Alternatively, administrators can revoke privileges granted to a user. For this, we recommend the REVOKE statement followed by the privileges, the database and table, and the username and host.

mysql> REVOKE [privileges] ON [database_name].[table_name] FROM '[username]'@'[host]';

For instance, to revoke the INSERT privilege on all tables in my_database from john_doe, execute:

mysql> REVOKE INSERT ON my_database.* FROM 'john_doe'@'localhost';

Function #26: Revoke Privileges

Function #27: Drop a User

Admins can delete a user account from a database. Note that this action removes all associated privileges. We recommend the DROP USER statement followed by the username and host in this scenario:

mysql> DROP USER '[username]'@'[host]';

For instance, the following statement to delete the user john_doe from the database.

mysql> DROP USER 'john_doe'@'localhost';

Function #27: Drop a User

Function #28: List Users

Admins soften need to list down all users so that they can select specific user account(s) for further operations. In these scenarios, you can query the mysql.user table to find the users of MySQL servers.

mysql> SELECT user, host FROM mysql.user;

Function #28: List Users

This statement retrieves a list of all users and their associated hosts.

Function #29: Change User Password

We recommend the ALTER USER statement to change a user’s password. The statement in this scenario will be as follows:

mysql> ALTER USER '[username]'@'[host]' IDENTIFIED BY '[new_password]';

For instance, to create a new user password for the user john_doe, run this statement:

mysql> ALTER USER 'john_doe'@'localhost' IDENTIFIED BY 'newsecurepassword';

Function #29: Change User Password

Backup and Restore

Backup and restore operations are critical for data protection and recovery in MySQL. Regular backups help safeguard against data loss due to hardware failures, software issues, or human error.

Here’s an overview of the basic commands and procedures for backing up and restoring MySQL databases.

The basic command syntax to backup a database is as follows:

# mysqldump -u [username] -p [database_name] > [backup_file.sql]

For instance, to backup the my_database database to a file named my_database_backup.sql, run:

# mysqldump -u root -p my_database > my_database_backup.sql

Here,

-u: Specifies the username

-p: Prompts for the password

Function #30: Back Up All Databases

We recommend using the –all-databases option with the mysqldump command to back up all databases on a MySQL server. The complete syntax is as follows:

# mysqldump -u [username] -p --all-databases > [backup_file.sql]

For instance, the following command backs up all databases on the server to a file named all_databases_backup.sql.

# mysqldump -u root -p --all-databases > all_databases_backup.sql

Function #31: Restore a Database

If you have a backup archive, you can restore a database by executing the following command syntax that uses the backup archive:

# mysql -u [username] -p [database_name] < [backup_file.sql]

For instance, consider the following command to restore the my_database database from the my_database_backup.sql file:

# mysql -u root -p my_database < my_database_backup.sql

Function #33: Restore All Databases

We recommend using the same mysql command without specifying a database name to restore all databases from a backup file created with the –all-databases option:

# mysql -u [username] -p < [backup_file.sql]

For instance, run the following command to restore all databases from the all_databases_backup.sql file:

# mysql -u root -p < all_databases_backup.sql

Function #34: Backup and Restore with Compression

To save storage space, compress the backup file using gzip during the backup process and decompress it before restoration.

Initially, back up the file using the command:

# mysqldump -u [username] -p [database_name] | gzip > [backup_file.sql.gz]

Next, restore from compress backup.

# gunzip < [backup_file.sql.gz] | mysql -u [username] -p [database_name]

Performance and Optimization

Optimizing MySQL performance involves combining database design, indexing, query optimization, and configuration tuning.

The following section discusses the essential techniques and examples to ensure the MySQL database performs efficiently.

Function #35: Indexing

Indexes are crucial for accelerating data retrieval operations. Query execution time can be greatly decreased with proper indexing.

To establish indexes on columns commonly used in WHERE, JOIN, and ORDER BY clauses, use the establish INDEX statement.

CREATE INDEX idx_username ON users (username);

Next, utilize EXPLAIN to analyze how MySQL executes queries and verify if indexes are being used effectively.

EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

Query Optimization

Writing efficient queries is key to optimizing performance.

Function #36: Select Only Required Column

By default, the SELECT * statement retrieves all columns from a table. This can be inefficient, especially for large tables, as it transfers unnecessary data.

To improve performance, explicitly specify the exact columns you need in your query. This reduces the data transferred between the database server and your application.

mysql> SELECT username, email FROM users WHERE id = 1;

Function #37: Optimize Joins

Joins are used to combine data from multiple tables based on a shared relationship. However, inefficient joins can significantly impact performance.

Ensure that the columns involved in the join operation have indexes created on them. Indexes allow MySQL to quickly locate relevant rows in each table, speeding up the join process.

mysql> SELECT orders.id, users.username

FROM orders

JOIN users ON orders.user_id = users.id;

Function #38: Limit Data Retrieval

When querying large datasets, retrieving all rows might not be necessary.

Use the LIMIT clause to restrict the number of rows returned by a query. This is particularly beneficial for pagination or retrieving only a specific number of recent entries.

mysql> SELECT * FROM orders WHERE status = 'pending' LIMIT 10;

By following these practices, you can write more efficient MySQL queries that execute faster and contribute to an overall better-performing database system.

Maintenance and Troubleshooting

Effective maintenance and troubleshooting are crucial for the smooth operation of MySQL databases. This involves regular maintenance tasks, monitoring, and resolving common issues.

Some of the essential maintenance practices, troubleshooting tips, and examples are:

Function #39: Backups

Regularly creating database backups using mysqldump safeguards the data in case of server failures, accidental deletions, or other unforeseen circumstances.

# mysqldump -u root -p my_database > my_database_backup.sql

Function #40: Optimize Tables

The OPTIMISE TABLE statement helps defragment tables, which can improve query execution speed by optimizing data storage.

mysql> OPTIMIZE TABLE my_table;

Function #41: Check and Repair Tables

Regularly checking and repairing tables using CHECK TABLE and REPAIR TABLE identifies and fixes potential inconsistencies or corruption issues within the tables.

mysql> CHECK TABLE my_table;

REPAIR TABLE my_table;

Monitoring

Continuously monitoring MySQL servers helps detect potential problems before they significantly impact performance.

Function #42: MySQL Status

Use the SHOW STATUS statement to gain insights into various server aspects, such as current connections, thread usage, and overall server health.

mysql> SHOW STATUS LIKE 'Threads%';

Troubleshoot Common Issues

Recognizing and resolving common issues promptly is essential for maintaining database health.

Function #43: Connection Issues

If users cannot connect, check the MySQL service status using sudo systemctl status mysql and verify user privileges using SELECT user, host FROM mysql.user WHERE user=’username’.

# sudo systemctl status mysql

mysql> SELECT user, host FROM mysql.user WHERE user='username';

Function #44: Performance Issues

Identify slow queries through the slow query log and optimize them for better performance.

Use EXPLAIN SELECT * FROM my_table WHERE condition; to analyze how MySQL executes a query and identify potential bottlenecks.

mysql> EXPLAIN SELECT * FROM my_table WHERE condition;

Function #45: Storage Issues

If you’re running out of storage space, consider cleaning up old data and logs. Partitioning large tables can also improve manageability.

You can use DELETE FROM my_table WHERE date < ‘2020-01-01’; to delete old data before the first Jan 2020.

mysql> DELETE FROM my_table WHERE date < '2020-01-01';

Function #46: Corruption Issues

If a table is corrupted, attempt recovery using the REPAIR TABLE statement. The syntax of this statement is as follows:

mysql> REPAIR TABLE my_table;

Performance Tuning

Performance tuning refers to the process of optimizing a database to improve its efficiency, speed, and resource utilization.

Function #47: Buffer Pool Size

The InnoDB buffer pool is a memory area within the MySQL server that caches frequently accessed data from tables. When a query needs data, the engine first checks the buffer pool. If the data is present, the system retrieves it much faster from the buffer than from the disk storage. We recommend running the following command to modify the InnoDB buffer pool size:

mysql> innodb_buffer_pool_size=4G

Function #48: Query Cache

The query cache stores the results of previously executed queries.

When you rerun a query, MySQL can potentially retrieve the results from the cache instead of re-executing the query. As a result, users see a significant increase in performance.

We recommend running this statement to enable the query cache. This statement sets the local variable to ON. The next statement sets an initial size for the cache.

mysql> query_cache_type=1

query_cache_size=256M

Conclusion

Mastering MySQL commands is essential for efficient database management and manipulation. This comprehensive guide provides various SQL commands and statements, from basic operations to advanced functionalities.

Understanding how to connect to the MySQL command-line client, perform database operations, manipulate data, and manage users ensures a robust foundation for any database administrator or developer.

Whether you’re creating indexes, modifying table structures, or fine-tuning performance, these commands are indispensable tools in your MySQL toolkit.

By familiarizing yourself with this quick reference, you can confidently navigate the MySQL command-line client, optimize your database operations, and ensure smooth and efficient data management.

FAQs

Q. What are the different types of statements in MySQL?

MySQL statements can be categorized into several types, including Data Definition Language (DDL) statements (e.g., CREATE, ALTER, DROP), Data Manipulation Language (DML) statements (e.g., SELECT, INSERT, UPDATE, DELETE), Data Control Language (DCL) statements (e.g., GRANT, REVOKE), and administrative statements (e.g., SHOW, DESCRIBE, USE).

Q. How do I use aggregate functions in MySQL?

In MySQL, aggregate functions calculate a single value after applying a set of values. Common aggregate functions include COUNT(), SUM(), AVG(), MAX(), and MIN(). For example:

SELECT COUNT(*) FROM employees;

SELECT AVG(salary) FROM employees;

Q. What are the DROP FUNCTION statements, and how do I use it?

The DROP FUNCTION statements delete a stored function from the database. To drop a function named calculate_bonus, use: DROP FUNCTION calculate_bonus;

Q. What are some essential administrative statements in MySQL?

Administrative statements help manage the database server and its objects. Some essential statements include SHOW DATABASES, SHOW TABLES, DESCRIBE table_name, and USE database_name. For example:

SHOW DATABASES;

DESCRIBE employees;

USE my_database;

Q. Can you provide a list of basic MySQL statements for beginners?

Certainly! Here are some basic MySQL statements:

  • CREATE DATABASE database_name;
  • USE database_name;
  • CREATE TABLE table_name (column_definitions);
  • INSERT INTO table_name (columns) VALUES (values);
  • SELECT * FROM table_name;
  • UPDATE table_name SET column = value WHERE condition;
  • DELETE FROM table_name WHERE condition;

Q. How do I update a table in MySQL?

To update data in a table, use the UPDATE statements followed by the table name, SET clause, and a WHERE clause to specify the condition. For example:

UPDATE employees SET salary = 60000 WHERE employee_id = 1;

Q. What are some advanced SQL statements in MySQL?

Advanced SQL statements in MySQL include complex joins, subqueries, stored procedures, triggers, and views. Examples include:

— Subquery

SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = ‘Sales’);

— Stored Procedure

CREATE PROCEDURE GetEmployeeCount() BEGIN SELECT COUNT(*) FROM employees; END;

— Trigger

CREATE TRIGGER before_employee_insert BEFORE INSERT ON employees FOR EACH ROW SET new.created_at = NOW();

Q. How do I use the command-line prompt to interact with MySQL?

The MySQL command-line client allows you to interact with the database server directly. To start, open your terminal and type: mysql -u username -p

Then, enter your password. You can now execute MySQL commands at the prompt.

Q. What does the DROP statements do in MySQL?

The DROP statements deletes databases, tables, or other objects. For example, to drop a table named employees: DROP TABLE employees;

To drop a database named my_database: DROP DATABASE my_database;

Q. What are aggregate functions, and how are they useful?

Aggregate functions perform calculations on multiple table rows and return a single result. They are useful for summarizing data. Examples include:

SELECT SUM(salary) FROM employees;

SELECT MAX(salary) FROM employees;

SELECT MIN(salary) FROM employees;

SELECT COUNT(*) FROM employees;

These functions help in generating reports and insights from data.

Try this guide with our instant dedicated server for as low as 40 Euros