Software programmers should have a practice of command-line tools. Though it feels old, sometimes it’s necessary to use the command prompt. I experienced this when I wanted to export a large database. For some reason, I was unable to export it through a GUI tool. And I succeeded in it by executing MySQL queries on the command prompt. You may also want to learn command prompt to interact with the MySQL database.
In this tutorial, we study how to run MySQL queries on the command prompt. For the sake of a tutorial, I will show it using Windows 10 Command Prompt. But feel free to use the same technique if you are on a different operating system.
To get started, you need to have MySQL installed on your system. I am using PHP as my programming language and I have XAMPP software installed on my machine. The XAMPP comes with MySQL bundled in its package. In your case, you might be using other software which doesn’t actually matter. All you need to have MySQL installed.
Connect MySQL Server on Command Prompt
For interacting with the MySQL database, you first need to connect a MySQL server. After a connection is established, you will able to run MySQL queries on the database.
For connecting the MySQL server, open your command prompt. Windows users can do it by
- Click on a Start icon.
- Type
cmdorcommandin the search box. - Click on Open under the Command Prompt App
Once the command prompt is opened, navigate to the directory where you have your MySQL executable files. In my case, the path is – D:\xampp\mysql\bin.
Adjust this path as per your system. And if you have set this MySQL path into your Environment variables then you don’t need to navigate to this bin directory.
Now you can connect to the MySQL server using the command which has passed your MySQL user name explicitly.
mysql -u root -p
Press the Enter after writing this command. It will ask for a password. Type the password. If your password is empty then just hit the Enter. Most probably on a local server, we don’t set a MySQL password.
If the MySQL server is connected, you would see a welcome message like the below screenshot.
You might be confused by the word MariaDB in the screenshot. MariaDB is a fork of the MySQL relational database management system. It is an improved version of MySQL. The XAMPP software uses MariaDB that’s why it’s showing in the welcome message.
We are now entered in the MySQL environment and can run the MySQL queries.
Run MySQL Queries in Command Prompt
In order to execute your MySQL statements you first need to choose the database. Your SQL statements will be executed against the chosen database.
To see a list of databases run SHOW DATABASES statement. It will give a list of all your MySQL databases.
Next, to choose the desired database you have to write a USE DATABASE_NAME statement.
For instance, I am choosing the ‘wp’ database so my SQL statement is USE wp.
After this, you can run your MySQL queries on your chosen database. As an example, I am writing a SELECT query that gives me output as shown in the screenshot below.
Just like the above query you can run other MySQL queries on the command prompt. Whenever you want to come out of the MySQL environment type exit and hit Enter.
Related Articles
- How to Take Backup of Laravel Application
- Backup Laravel Application on Amazon S3
- How to Take Backup of MySQL Database using PHP
If you liked this article, then please subscribe to our YouTube Channel for video tutorials.