MySQL 8: How to select a database to work with command line

Introduction

MySQL is a prevalent open-source relational database management system. MySQL 8, the latest version at the cutoff of our knowledge, provides enhanced performance and security features. One fundamental task when working with MySQL is selecting a database via the command-line interface (CLI). This tutorial guides you through the steps to select and interact with a MySQL database using the CLI. We cover basic commands and work our way up to more advanced techniques, illustrating outputs and providing best practices.

Prerequisites

Before we dive into the commands, ensure that you have the following:

• MySQL 8 installed on your system.
• Access to the command-line interface (Terminal on macOS and Linux, Command Prompt, or PowerShell on Windows).
• Basic understanding of SQL (Structured Query Language).
• An existing database and user with privileges to access it.

Accessing the MySQL Shell

To begin, you’ll need to open MySQL Shell. The shell is used to execute SQL queries, administration tasks, and for database management. Launch the shell by opening your command line interface and entering the following command:

mysql -u username -p

Replace username with your MySQL user. After hitting Enter, you’ll be prompted for your password. Enter it, and you should be logged into MySQL.

Selecting a Database

Once you’re in the shell, you can view all available databases with:

SHOW DATABASES;

This command will list the databases you have access to. To select a database to work with, use the USE command followed by the database name:

USE database_name;

Be sure to replace database_name with the actual name of the database you wish to work with. If the database exists and you have sufficient privileges, the output should look like:

Database changed

If the database does not exist or you lack privileges, an error will be returned.

Basic Data Retrieval

With the database selected, you might want to start by retrieving data from it. A basic SELECT query can be used:

SELECT * FROM table_name;

This command selects all columns from a specific table within the active database. You should replace table_name with the name of the table you want to query:

mysql> SELECT * FROM employees;
+----+------------+------------+
| id | first_name | last_name  |
+----+------------+------------+
|  1 | John       | Doe        |
|  2 | Jane       | Smith      |
+----+------------+------------+
2 rows in set (0.00 sec)

Advanced Database Operations

After grasping the basics, more complex operations like joining tables or complex filter clauses can be employed. For example:

SELECT e.first_name, e.last_name, d.department_name FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 50000;

The above query retrieves employees’ names and their department names with salaries over 50,000.

Transaction Management

MySQL enables transaction control, which is crucial in managing how changes to the data are made and ensuring data integrity. Below are some essential transaction commands:

• BEGIN; or START TRANSACTION; initiates a new transaction.
• COMMIT; saves the changes made during the transaction.
• ROLLBACK; undoes changes made in the current transaction.

Remember, these commands only apply to storage engines that support transactions, such as InnoDB.

Scripting with MySQL Command Line

For more advanced users, MySQL allows running scripts directly from the command line. This can be done by redirecting the input to MySQL from a file containing SQL statements:

mysql -u username -p database_name < script.sql

Ensure script.sql contains valid SQL statements and is located in the correct directory.

Troubleshooting

Sometimes, things may not work as expected. A common error is permission-related, which can be resolved by verifying user privileges. Another frequent issue is typo-related; ensuring correct spelling of database and table names can save a lot of debugging time.

Conclusion

In this tutorial, we discussed how to select and work with databases in MySQL 8 using the command line. We started with fundamental operations and explored more complex functionalities, with examples to demonstrate each command. With these tools in hand, interacting with MySQL databases at the command line should be manageable and efficient.