To use the mysql client utility interactively, take the following steps:
1.
If necessary, open a shell for your operating system:If you’re using a graphical user interface (GUI) such as GNOME or KDW in a Linux environ-ment, use a Terminal window.
If you’re using Linux without a GUI, use the shell’s command prompt.
2.
Next, launch the mysql client utility in interactive mode. If you’re using a Linux RPM installation of MySQL, enter the following command:/usr/bin/mysql mysql -u root
If you’re using a Linux tar installation of MySQL, enter the following command:
/usr/local/mysql/bin/mysql mysql -u root
If you’re using a Windows installation of MySQL, enter the following command:
c:\program files\mysql\mysql server <version>\bin\mysql mysql -u root
You receive a message that welcomes you to the MySQL monitor and provides information about command termination, the MySQL connection, and getting help. In addition, the com-mand prompt displays as follows:
mysql>
At the command prompt, you simply type SQL commands as you would type in commands in any Linux or Windows shell. When sending commands to MySQL, you must terminate your commands with a semi-colon, unless those commands are specific to the mysql utility, such as the exit or quit command.
3.
Now, try out a couple of mysql-specific commands. In the first one, you list the commands that are specific to the mysql client. Execute the following command:help
When you execute the help command, mysql returns a list of available commands, as shown in the following results:
For the complete MySQL Manual online visit:
http://www.mysql.com/documentation
For info on technical support from MySQL developers visit:
http://www.mysql.com/support
For info on MySQL books, utilities, consultants, etc. visit:
http://www.mysql.com/portal
List of all MySQL commands:
Note that all text commands must be first on line and end with ‘;’
? (\?) Synonym for `help’.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set query delimiter.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
notee (\t) Don’t write into outfile.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute a SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
For server side help, type ‘help all’
The list of commands available to mysql can vary depending on version and operating system.
Be sure to review the list that appears on your system.
4.
Next, type in a SELECTstatement, but don’t include the terminator (semi-colon). Type the fol-lowing at the command prompt and then press Enter:SELECT host, user FROM user
Because you didn’t include the terminator, mysql interprets this to mean that you have not com-pleted the statement. As a result, the command prompt changes to the following:
->
At this point, you can complete the statement, add a semi-colon, or clear it to start over.
5.
If you decide that you don’t want to complete this statement, use the following command to clear it:\c
The clear command tells mysql to disregard the statement and return you to the command prompt.
6.
Another mysql command that you use quite often is the use command, which specifies the name of the database in which you’ll be working. For example, you would change to the test database by using the use command:use test
After you execute this command, you receive a message telling you that the database has been changed, and you’re returned to the command prompt.
7.
Finally, to exit the mysql utility, you can simply execute the following command:quit
You could have also used the exit command or the \qcommand to exit mysql. Once you exe-cute one of these commands, you’re returned to your shell’s command prompt.
How It Works
Once you’re in your operating system’s shell, you can launch the mysql client utility along with the options necessary to operate the utility. In the preceding exercise, you included two options, as shown in the fol-lowing code:
/usr/bin/mysql mysql -u root
The first mysql refers to the client utility. If you want, you can launch the utility without specifying any options; however, if you do specify options, they follow the name of the utility. For example, the second mysql is an option that provides the name of a specific database, which in this case is the mysql
admin-database that currently exists in your MySQL environment. By specifying the mysql admin-database, you’re telling mysql to operate in the context of that database. Once in the mysql shell, you can specify another database if you desire.
The second option used in the command is the user option: -u root. The option specifies that the mysql utility should interact with MySQL in the context of the root user account. Whatever privileges are granted to the specified user account are available during that mysql client session.
Because you haven’t yet created any user accounts or modified the existing accounts, it isn’t necessary to specify the root user. We used it here simply to demonstrate how that option is specified. Once you’ve created additional user accounts and assigned privileges to those accounts, you can log into the mysql client in the context of one of those accounts.
After you logged into the mysql client utility, you were able to execute mysql commands, such as help,
\c, use, and quit. These commands are specific to the mysql utility and, as a result, do not require that you terminate them with a semi-colon, as would be the case with an SQL statement. You can use a termi-nator, however, and the commands will run as they would without it. In addition, if you are executing several commands consecutively, as would be the case when creating a batch file, you must use the ter-minator so MySQL recognizes the end of one statement and the beginning of the next.