Appendix C: MySQL Command Reference

This appendix lists the MySQL commands you will use most often. These commands will not be used in Perl programs. Instead, these are the commands you will use to perform basic administration of a MySQL database.

The reason for this appendix is that MySQL will likely be the DBMS you use. If you are using another database, refer to its documentation for administration instructions. The latest MySQL documentation is at http://www.mysql.com if you need more complete documentation.

Connecting as a User

There will be many times when you will want to connect to MySQL directly from the command line. Connecting this way can be very helpful when creating queries because you can test them and make sure that they work before coding them in your program.

Connecting as an interactive user can also be very useful when debugging a problem. When debugging, it is usually best to log in as the user that the problem is occurring with. For example, if you have a user named www with a password of supersecret and you are having problems with a program using that account, log in as that user and try to run the same query. If the error has something to do with access permissions, you may be able to catch it and figure out a solution from here. If you log on as the admin user, you would never have seen the access-rights; the admin user has access rights to do anything, so the error does not happen when you are logged in as the admin user.

To connect as a non-admin user: (in this case, the bookuser user)

mysql –u bookuser –p testpass

This logs into mysql as the user bookuser with a password of testpass. The -u stands for username, and the -p stands for password. Specify a host if the MySQL server is on a different system.

mysql –u bookuser –p testpass –h hostname 

If all goes well, you should get something that looks like this:

Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 10 to server version: 3.23.47 Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer. mysql>

This means you are connected to MySQL and are at the MySQL prompt. From here, you can directly enter SQL statements and immediately see the results. All SQL statements entered at the mysql> prompt must be terminated with a semicolon.

For example:

mysql> use bookdatabase; mysql> select * from library;

The above lines tell MySQL to use the database named bookdatabase and then perform a query to select and display all items from the table named library.