MySQL Command-line

MySQL comes with a command-line interface that let you run MySQL commands and SQL queries. While there are graphical tools like phpMyAdmin and MySQL Workbench, command line interface will come in handy when you manage more databases and when you get more familiar with MySQL.

MySQL Path

You should be able to run mysql and other command-line utilities like mysqladmin and mysqldump (discussed later) from any folder. If these utilities are not accessible from any directory, add the path to the utilities to a global path variable in your operating system.
If you have installed XAMPP in Windows, the path would be the bin folder under mysql folder. For example, if you have installed XAMPP under C drive, path would be C:\xampp\mysql\bin. You can verify the path by making sure the utilities are available in the chosen path.
Add this path to the path environment variable in Windows, and then you will be able to access the utilities from any folder.

Logging into MySQL

You can log in to MySQL as root user (generally the user with all the privileges) by typing the command below.

mysql -u root -p

The command above will prompt you to enter the password for user root. If your MySQL installation is new and you haven’t changed the root password, most of the time the root password is blank (just press the Enter key).
After that, you would see a prompt like below that lets you type commands. Prior to the prompt, you would see few instructions and the MySQL version number


Executing Commands

All SQL commands you type at the MySQL prompt should have a semicolon (;) at their ends. The commands will not run till you enter a semicolon (It’s possible to use \G instead of semicolon as explained below).
In addition to the SQL commands, MySQL has its own set of commands. To see these commands, type help at the MySQL prompt as below. These commands aren’t required to have a semicolon at the end. After typing a command, hit Enter key to execute the command.

mysql> help

Command-line Pretty Output

If you find the output of a certain SQL command difficult to read, try \G in place of the semicolon as shown in the following example. This will display the output in a vertical format and remove surrounding dashed lines.

mysql> SHOW TABLE STATUS FROM company_db \G

Multiple Line Commands

To achieve clarity, you can span a command over multiple lines. Just hit the Enter key after each line, and MySQL will prompt an arrow indicating a new line. The following is a multiple line SQL command to create a data table.

mysql> create table `employee` (
-> `id` int(10),
-> `first_name` varchar(40),
-> `last_name` varchar(40),
-> `age` tinyint(3),
-> `joined_date` date,
-> `records` text,
-> primary key (`id`)
-> );

How to Clear Command-line

If you are on a Mac OS or a Linux command-line, you can use Ctrl+L for clearing the screen and Ctrl+U for clearing the current line.

Logging out of MySQL

Use the exit command to log out of MySQL.

mysql> exit

Video Tutorial for how to use mysql through cmd

Recommended Tutorials for you


Anuj kumar

This is Anuj Kumar. I’m a professional web developer with 5+ years of experience. I write blogs in my free time. I love to learn new technologies and share with others. I founded PHPGurukul in September 2015. The main aim of this website to provide PHP, Jquery, MySQL, PHP Oops and other web development tutorials. .

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Check Also
Back to top button