How To Use MySQL / MariaDB From Command Line

MySQL and MariaDB are two widely used open-source relational database management systems that are valuable for organizing and handling data and information. They serve as essential tools for both businesses and developers due to their versatility and functionality.

Although graphical user interfaces (GUIs) offer a more intuitive approach for some users, the command line interface (CLI) provides greater control and flexibility when working with databases. By leveraging the command line interface, users can execute commands swiftly and efficiently, enabling easier management of databases and their contents.

In this blog post, we will delve into a comprehensive guide that explores the effective usage of MySQL and MariaDB through the command line interface. The guide will encompass crucial topics, including installation procedures, accessing the CLI, creating and modifying databases, as well as inserting and retrieving data, among others. By the time you finish reading this blog post, you will possess a deeper understanding of how to utilize MySQL and MariaDB effectively through the command line interface.

Installing on different operating systems

MySQL and MariaDB can be installed on different operating systems such as Windows, macOS, and various Linux distributions. Here’s a brief overview of how to install them on each OS:

Windows

• Download the MySQL Community Server from the official website
• Run the installer and follow the prompts to install the server
• During the installation process, set a root password for MySQL
• After installation, MySQL should be running as a Windows service

macOS

• Install Homebrew package manager if not already installed
• Open Terminal and enter “brew install mysql” to install MySQL
• Once the installation is complete, run “brew services start mysql” to start the MySQL service
• Set a root password for MySQL using the command “mysql_secure_installation”

Linux (Ubuntu)

• Open Terminal and enter “sudo apt-get update” to update the package manager
• Enter “sudo apt-get install mysql-server” to install MySQL
• During the installation process, set a root password for MySQL
• After installation, MySQL should be running as a Linux service

Installing MySQL / MariaDB on CentOS 7

Here’s a more detailed step-by-step guide for installing MySQL / MariaDB on CentOS 7:

• Update the package manager by running the following command:

sudo yum update

• Install MySQL / MariaDB using the following command:

sudo yum install mariadb-server

• Start the MariaDB service by running the following command:

sudo systemctl start mariadb

• Run the following command to enable the MariaDB service to start automatically on system boot:

sudo systemctl enable mariadb

• Secure your MariaDB installation by running the following command:

sudo mysql_secure_installation

• You will be prompted to set a root password for MariaDB. Enter a strong password and remember it.
• During the secure installation process, you will be asked to answer a series of questions to improve the security of your MariaDB installation. Follow the prompts and answer the questions as appropriate.

That’s it! You should now have MariaDB installed and running on your CentOS 7 system. You can test your installation by logging into MariaDB using the following command:

sudo mysql -u root -p

Accessing the MySQL / MariaDB command line interface

To access the MySQL / MariaDB command line interface, you need to open a terminal or command prompt and enter the appropriate command. Here are the steps to access the command line interface:

1. Open a terminal or command prompt on your computer.
2. Enter the command to start the MySQL / MariaDB command line interface, depending on the installation type:

For MySQL on Windows, enter the following command and press Enter:

mysql -u root -p

You will be prompted to enter the root password you set during installation.

For MySQL on Linux or macOS, enter the following command and press Enter

mysql -u root -p

Again, you will be prompted to enter the root password you set during installation.

For MariaDB on all operating systems, enter the following command and press Enter:

mysql -u root -p

Once again, you will be prompted to enter the root password you set during installation.

Once you have successfully entered the correct password, you will see a prompt that looks like this:

MariaDB [(none)]>

This prompt indicates that you are now in the MySQL / MariaDB command line interface and can start executing commands.

That’s it! You are now ready to use the MySQL / MariaDB command line interface to manage your databases and perform various operations.

Creating a Database

To create a new database using the command line interface, you can use the CREATE DATABASE command followed by the name of the database. Here are the steps:

• Open the MySQL / MariaDB command line interface.
• Enter the following command to create a new database:

CREATE DATABASE database_name;

Replace database_name with the name you want to give your new database.

• Once you have executed the command, you should see a message indicating that the database was created successfully.

Here are some examples of creating databases using different naming conventions and parameters:

Create a database named “my_database”:

CREATE DATABASE my_database;

Create a database with a specific character set and collation:

CREATE DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Creating Tables

To create a new table within a database, you can use the CREATE TABLE command followed by the table name and the list of columns and their data types. Here are the steps:

• Open the MySQL / MariaDB command line interface.
• Select the database in which you want to create the table using the following command:

USE database_name;

Replace database_name with the name of the database you want to use.

• Enter the following command to create a new table:

CREATE TABLE table_name (

    column1 datatype1,

    column2 datatype2,

    …

);

Replace table_name, column1, datatype1, etc., with the names of your table and its columns and data types.

• Once you have executed the command, you should see a message indicating that the table was created successfully.

Here are some examples of creating tables using different data types and constraints:

Create a table with columns for “id”, “name”, and “age”:

CREATE TABLE my_table (

    id INT PRIMARY KEY,

    name VARCHAR(50),

    age INT

);

• Create a table with columns for “id”, “name”, and “email”, where “id” is an auto-incrementing primary key:

CREATE TABLE my_table (

    id INT PRIMARY KEY AUTO_INCREMENT,

    name VARCHAR(50),

    email VARCHAR(100)

);

Inserting and Modifying Data

To insert new data into a table, you can use the INSERT INTO command followed by the name of the table and the values you want to insert. Here are the steps:

• Open the MySQL / MariaDB command line interface.
• Select the database in which the table you want to insert data into is located using the following command:

USE database_name;

Replace database_name with the name of the database.

• Enter the following command to insert data into the table:

INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …);

Replace table_name, column1, column2, etc., with the name of the table and its columns, and value1, value2, etc., with the values you want to insert.

• Once you have executed the command, you should see a message indicating that the data was inserted successfully.

To modify existing data in a table, you can use the UPDATE command followed by the name of the table, the column you want to modify, and the new value. Here are the steps:

• Open the MySQL / MariaDB command line interface.
• Select the database in which the table you want to modify data in is located using the following command:

USE database_name;

Replace database_name with the name of the database.

Enter the following command to modify data in the table:

UPDATE table_name SET column_name = new_value WHERE condition;

Replace table_name, column_name, new_value, and condition with the name of the table, the column you want to modify, the new value you want to set, and the condition that specifies which rows you want to modify.

Once you have executed the command, you should see a message indicating that the data was modified successfully.

Conclusion

To conclude, leveraging the command line interface for managing MySQL and MariaDB databases proves to be a powerful tool for developers and system administrators alike. By diligently following the steps outlined in this blog post, you have gained a comprehensive understanding of crucial aspects such as the installation process for MySQL/MariaDB, accessing the command line interface, creating databases and tables, inserting and modifying data, retrieving data, deleting data, and dropping tables. Armed with this knowledge, you are well-equipped to embark on database building and management endeavors for your own projects or your organization.