MySQL Basic Tutorial
We begin our study of Web-based, data-driven applications using MySQL and PHP by looking first at the MySQL utility. MySQL is a relational database management system with many of the same features that we have explored in Access and Oracle thus far. Like Oracle, MySQL uses the client/server model. The server that we will be using resides on the departmental Enterprise Web server. We will be using two different clients to communicate with the server: the standard command-line client, running on the Ultras and accessed using secure telnet (Putty), and a Web-based interface called phpMyAdmin. Unlike Oracle and Access, MySQL is an open-source DBMS. That means (among other things) that it is freely distributed, its source code is freely available, and you use it at your own risk. You will find, however, that the documentation is actually quite good, and the system itself very reliable. 1. The command line interface Getting started The first step to using the standard MySQL client, which features a command-line interface much like weve seen with Oracle, is to connect to one of the Ultras using a secure shell (SSH) client. (Putty is provided for this in the lab, and is freely downloadable for your own computer.) From there youll connect to the MySQL server on the Enterprise Web server and change your password. a. Use Putty to connect to ultrax1.furman.edu using your ID and default password. (A demonstration will be given in lab if needed.) If you dont know these, go see Ted to get them. b. At the command line, enter the following to change your MySQL password (which is different from the password that you just used to log in):
mysqladmin -h enterprise P 3828 -u <username> -p password <new password>
The word password is a keyword in this command. You will be prompted for your current password, which is .reset.. c. Next, use your new password to connect to the MySQL server on Enterprise:
mysql -h enterprise P 3828 -u <username> -p
With this command you are specifying the host computer (enterprise) and the fact that you are using a password to log in. Enter the password that you just created when prompted.
CS40: Database Management Systems
page 2
d. Now you are connected to the MySQL server. What you have here is an interface similar to the Oracle SQL Worksheet that weve used previously. An additional command that you should try now is:
mysql> show databases;
This will show all of the databases that currently exist on the server. Make a note of yours. It is one of only two databases that youll be able to work with. (The other one is the test database, which is accessible to everyone.) Then run the command
mysql> use <your DB name>;
This selects your DB as the one to which all following commands will apply. e. Next lets import some data. Use FTP to copy the file BigHitSampleMySQL.txt (in the OUT folder) to your home directory on the Ultra server. Then run the following command in MySQL:
mysql> source BigHitSampleMySQL.txt;
Make sure that youve run the use command first. You might want to use WordPad or another editor to take a look at this file to see what youve just done. As you can see, it simply consists of SQL commands to create and populate several tables for the video store enterprise weve used as a case study in class. Note: As you will soon see, you could also have manually all typed of the SQL commands necessary to build the database in at the command line. However, it is much more convenient to import an external file like we did. This may be useful to you in the future. f. Use the following command to display all of your tables:
mysql> show tables;
and this one to show the schemas of a particular table:
mysql> show columns from <tablename>;
Performing queries Now that weve learned how to connect to the server, and weve constructed a database, the next step is to try some SQL queries. We wont spend too long on this, because the activity is pretty similar to what weve done previously with Oracle. a. Experiment with some video store queries. Use the schema handout from class and some of the queries from the class PowerPoint notes. Note which queries dont seem to work and see if you can figure out why. In some cases the syntax for MySQL will be slightly different from the Access or Oracle syntax. Another difference is that the
CS40: Database Management Systems
page 3
version of MySQL that we have installed doesnt support nested SELECT statements. Make whatever adjustments are necessary. b. Make up a query or two to try. Try to incorporate several different capabilities of the SELECT command. c. To quit the MySQL client, enter quit or exit. 2. The Web interface As mentioned previously, MySQL is an open source system. One of the many utilities that has been written by a MySQL user is a Web interface to the server called phpMyAdmin. Well use that next. a. Point your browser to the following address:
http://cs.furman.edu/phpMyAdmin/
b. You will be prompted for a login ID and password. Your login ID is the same as your Ultra ID. (It doesnt have to be, but it has been set up that way.) Your password is the same as it is when you use the command line interface. (So it is still .reset. if you havent changed it.) c. Im not going to take a lot of time explaining this interface to you. Most of it should make sense by now. Click once on your database to select i...