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.
1 |
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
1 |
mysql> |
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.
1 |
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.
1 |
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.
1 2 3 4 5 6 7 8 9 |
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.
1 |
mysql> exit |