Or copy link
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.
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:
Here’s a more detailed step-by-step guide for installing MySQL / MariaDB on CentOS 7:
sudo yum update
sudo yum install mariadb-server
sudo systemctl start mariadb
sudo systemctl enable mariadb
sudo mysql_secure_installation
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
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:
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
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:
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.
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:
CREATE DATABASE database_name;
Replace database_name with the name you want to give your new database.
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;
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:
USE database_name;
Replace database_name with the name of the database you want to use.
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.
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
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(100)
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:
Replace database_name with the name of the database.
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.
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:
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.
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.
Remote access to MySQL databases is an essential featur...
PostgreSQL, also known as Postgres is a powerful open-s...
SSH stands for Secure Shell is a powerful network proto...
The ability to easily integrate data from several sourc...
MySQL Workbench is a popular graphical tool that simpli...
MySQL is a well-known Open Source SQL Data Management S...
Save my name, email, and website in this browser for the next time I comment.
Δ