Introduction

This article aims to provide you with a cheat sheet for the most common operations in MySQL.

Connecting to MySQL

To start using MySQL, you need to connect to the MySQL server. In its simplest form, you can connect to the MySQL server using the following command:

$ mysql -u <user> -p

Here, <user> is the username you've set up for MySQL. The -p flag prompts you for the password associated with the user.

Specific Database Connection

If you wish to directly connect to a specific database (<db_name>), use the following command:

$ mysql -u <user> -p <db_name>

The database name (<db_name>) is an optional parameter. If provided, you will connect directly to that database.

Remote Connection

In case you need to connect to a remote MySQL server, use the -h and -P flags to specify the host and port respectively:

$ mysql -h <host> -P <port> -u <user> -p

Here, <host> represents the hostname or IP address of the MySQL server, and <port> is the port number where the MySQL server is listening.

Remote Connection with Specific Database

Similarly, you can connect to a remote MySQL server and directly to a specific database with this command:

$ mysql -h <host> -u <user> -p <db_name>

Database Operations

Once you're connected to the MySQL server, you can perform various operations on databases. These operations include creating a database, listing all available databases, and switching or connecting to a specific database.

Creating a Database

To create a new database in MySQL, you use the CREATE DATABASE statement followed by the name of the database. For example, to create a database named db, you would use the following command:

CREATE DATABASE db;

After executing this command, a new database named db will be created.

Listing all Databases

To view a list of all databases available on the MySQL server, you can use the SHOW DATABASES; command:

SHOW DATABASES;

This will return a list of all databases that the currently logged-in user has permissions to see.

Switching to a Specific Database

To switch to a specific database, you can use the USE statement followed by the name of the database. For example, to switch to a database named db, use the following command:

USE db;

This command will switch the context to the db database. All subsequent commands will be executed against this database until you switch to another database or terminate the connection.

Connecting to a Specific Database

In some cases, you might want to connect to a specific database using the CONNECT statement:

CONNECT db;

The CONNECT statement is similar to the USE statement, with the main difference being that CONNECT also checks the user's privileges on the specified database, while USE does not.

Table Operations

After switching to the appropriate database, you can perform various operations on the tables within it. These operations include listing all tables, showing the fields of a specific table, and displaying the structure of a table.

Listing Tables for Current Database

To list all tables in the currently selected database, use the SHOW TABLES; command:

SHOW TABLES;

This command will return a list of all tables in the current database.

Listing Fields for a Table

To show the fields (or columns) of a specific table, use the SHOW FIELDS FROM command followed by the name of the table. For example, to show the fields of a table named t, use the following command:

SHOW FIELDS FROM t;

This will display a list of all the fields, their types, and other properties for the specified table.

Displaying Table Structure

If you need to inspect the structure of a table, including its fields, types, and other details, use the DESC (short for DESCRIBE) command:

DESC t;

This command will return the structure of the table t, including column names, data types, whether NULL is permitted, default values, and other information. It's an invaluable tool for quickly understanding the structure and constraints of a table.