Fall Semester 2002


Lab Notes Five: Using mySQL

So far we have investigated CGI and we realized that keeping state is somewhat tricky. We have been exploring strategies of keeping state on the client side. Sending data to the client presents several security problems, mostly because data sent over to the client becomes available and can be changed. So investigating strategies for keeping state on the server side is worthwhile. We will be using mySQL for all our server side state maintaining techniques.

The software mentioned, mySQL, is a relational database management system. (You can read more about it here). Being relational means that data is stored in it in tables. Each table contains a number of columns. Columns have names, data types, and store values (of that particular data type). We won't do much modeling at first. We will start by using one table with two columns. We just want to be clear on how things work. Later we wil revisit this topic and address issues of modeling. For now we work with only one table. Details below.

The software is already installed on burrowww. You only need to set your environment to make it accessible. Should you want to use mySQL on your own computer you'd have to install it. (Same goes for Perl, and Apache. In this class we install Apache, but we do not install Perl which, like mySQL is installed for us already. Many other things are already installed, as you will see).

Make sure that your PATH contains the mySQL location.

burrowww.cs.indiana.edu% echo $PATH
/u/dgerman/bin:/home/user1/mysql/bin:/usr/local/gnu/bin:/usr/bin:/usr/local/bin:/usr/sbin:/usr/ucb:/usr/bin/X11:/usr/openwin/bin:/usr/dt/bin:/opt/SUNWspro/bin:/usr/ccs/bin:/usr/local/gnu/bin
If it's not, you need to change your .cshrc file.

burrowww.cs.indiana.edu% grep -i mysql ~/.cshrc
        /home/user1/mysql/bin \
To start mySQL you need to specify a username and a password. Notice that we all will log in as the same user (a348) and using the same password. We will be one and the same person. That means we need to make sure we work in such a way that prevents unwanted interference. Conventions to be used towards this aim are listed below, and you are encouraged to make use of them.

burrowww.cs.indiana.edu% mysql -ua348 -pa348AG
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 78 to server version: 3.23.27-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer
Once in you want to make sure you can get out. (Remember vi). So that's how you do it.
mysql> exit
Bye
So we go back in, and this type we don't type the password on the command line any longer.

burrowww.cs.indiana.edu% mysql -ua348 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 79 to server version: 3.23.27-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer
Here are a few commands that you can use.

mysql> show databases;
+----------+
| Database |
+----------+
| a348     |
| bassoon  |
| classes  |
| mysql    |
| proj     |
| test     |
+----------+
6 rows in set (0.00 sec)
Our database is a348, only. Nothing else is available.

mysql> create database dgerman;
ERROR 1044: Access denied for user: 'a348@localhost' to database 'dgerman'
mysql> select database();
+------------+
| database() |
+------------+
|            |
+------------+
1 row in set (0.01 sec)
You can't even create a new one. But you can (and should use) a348, the database for this class.

mysql> use a348
Database changed
How do you see that?
mysql> select database;
ERROR 1064: You have an error in your SQL syntax near '' at line 1
You have just seen an error message.

Likely it won't be the last, so always remain patient, and confident.

mysql> select database();
+------------+
| database() |
+------------+
| a348       |
+------------+
1 row in set (0.00 sec)
This shows that the database has been selected.
mysql> show tables;
Empty set (0.01 sec)
That was a long time ago, so likely you will see many more today.

Now let's create a table. Note the naming conventions.

mysql> create table dgerman_student (
    ->   name        varchar(20)      not null, 
    ->   gender      enum ('f', 'm')  not null,
    ->   student_id  int unsigned     not null  auto_increment primary key
    -> );
Query OK, 0 rows affected (0.00 sec)
Create tables whose names start with your username followed by an underscore.

In my case that means dgerman_. Then I wrote the name of the table (student).

(This is just a convention, but please follow it.)

So we have created a table with three columns:

Do the same, and get ready to enter some data.

mysql> show tables like '%dgerman%';
+-----------------+
| Tables_in_a348  |
+-----------------+
| dgerman_student |
+-----------------+
1 row in set (0.00 sec)
The % acts as a wildcard here.

You can see that your table has been created and you can ask for info about it, too.

mysql> describe dgerman_student;
+------------+------------------+------+-----+---------+----------------+---------------------------------+
| Field      | Type             | Null | Key | Default | Extra          | Privileges                      |
+------------+------------------+------+-----+---------+----------------+---------------------------------+
| name       | varchar(20)      |      |     |         |                | select,insert,update,references |
| gender     | enum('f','m')    |      |     | f       |                | select,insert,update,references |
| student_id | int(10) unsigned |      | PRI | NULL    | auto_increment | select,insert,update,references |
+------------+------------------+------+-----+---------+----------------+---------------------------------+
3 rows in set (0.00 sec)
So we just defined the structure of the tables.

When we review it, nothing unusual shows up.

The structure is (emtpy and) ready to go.

mysql> select * from dgerman_student;
Empty set (0.00 sec)
That's how we extract data, but how do we enter data?
mysql> insert into dgerman_student values 
    ->   ('Abby', 'f', NULL), 
    ->   ('Kyle', 'm', NULL); 
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
We use insert, and carefully specify all elements to be entered.

mysql> select * from dgerman_student;
+------+--------+------------+
| name | gender | student_id |
+------+--------+------------+
| Abby | f      |          1 |
| Kyle | m      |          2 |
+------+--------+------------+
2 rows in set (0.01 sec)
Now when we query it again we find the data there.

How do we delete data?

mysql> delete from dgerman_student;
Query OK, 0 rows affected (0.02 sec)
That sort of wipes out everything.

Only do that if you want to clean everything.

mysql> select * from dgerman_student;
Empty set (0.00 sec)
Nothing left, as anticipated.
mysql> show tables;
+-----------------+
| Tables_in_a348  |
+-----------------+
| dgerman_student |
+-----------------+
1 row in set (0.00 sec)
The table is still there, but we can get rid of it too, if we want.
mysql> drop table dgerman_student;
Query OK, 0 rows affected (0.00 sec)
And if we look for it, it no longer is found.

mysql> show tables;
Empty set (0.00 sec)

mysql> exit
Bye
burrowww.cs.indiana.edu% exit
burrowww.cs.indiana.edu% 
So that's the end of the tutorial.

Remember that you go in as one and the same user so please

A348/A548 LAB ASSIGNMENT FIVE

You are to create a table like the one above.

  1. The name is as described above, and the structure is the same.

  2. Populate the table with some data (at least three records).

  3. Show this to your lab instructor next week, in lab.


Last updated: Sep 23, 2002 by Adrian German for A348/A548