CS425 MySQL Instructions:

A MySQL server has been created on the CS department computing system for use by COS425 students. The server is named studentdb. For each student in COS 425 a database has been created on the server; this database is named using the student's user name. For example, since my username in the CS department is aslp my database name is aslp and the username for the database is also aslp. The password for your database has been set to be the same as your username. (This should be changed right away as discussed below.) You can access studentdb only if logged onto a CS department computer. Each COS 425 student who did not already have a CS account has been given one for COS425 work. The login for the account is your Princeton user ID and the password has been set to your CIT password. You can access a CS machine by using a workstation in one of the public areas in the CS department or by ssh'ing to "portal.cs.princeton.edu".

There are no logins on the machine studentdb; all access to studentdb is through client programs "mysql", "mysqldump", and "mysqladmin" run on one of the CS machines. The following instructions assume you are logged onto the CS machine named bolle and show the bolle prompt, but any machine in the department will work, as long as it has the mysql client (e.g. rayban).

1. First of all, you should change your password for studentdb using the command:

   bolle:$ mysqladmin -h studentdb -u [username] -p password [new password]

   where: [username] is your user name, and [new password] is your new password.
   This will prompt you to enter your password which initially is just your username.
   Note: It's possible that your initial password is empty. In this case, you just enter RETURN when you are asked for the password.

2. To interact with MySQL, you first connect to the server using the command:
   

   bolle:$ mysql -h studentdb -u [username] -p [database name];
   Enter password: [your password]

   Recall that the database name is the same as your user name. After you enter your password at the prompt, you'll be in the mysql command-line utility.

3. When you finish, you can disconnect:
   mysql> quit

4. In MySQL, you may enter queries(SQL commands):
   mysql> create table professors(name char(40) not null, department char(3), primary key(name));
   Query OK, 0 rows affected (0.05 sec)
   mysql> create table courses(cname char(40) not null, prof char(40), primary key(cname), foreign key prof_ref(prof) references professors(name));
   Query OK, 0 rows affected (0.04 sec)

5. To show tables your created:
   mysql> show tables;

6. To show definition of a table:
   mysql> describe courses;

7. To insert some data:
   mysql> insert into professors values ('A', 'COS');
   Query OK, 1 row affected (0.00 sec)
   mysql> insert into professors values ('B', 'MAT');
   Query OK, 1 row affected (0.00 sec)
   mysql> insert into courses values ('C1', 'A');
   Query OK, 1 row affected (0.00 sec)
   mysql> insert into courses values ('C2', 'B');
   Query OK, 1 row affected (0.00 sec)
   mysql> insert into courses values ('C3', 'A');
   Query OK, 1 row affected (0.00 sec)
8. To query the database:
   select cname, department from courses, professors where prof=name order by cname;

9. Using mysql in Batch Mode:
   Suppose you put all commands in file batch-file. It contains:

     create table professors(name char(40) not null, department char(3), primary key(name));
     create table courses(cname char(40) not null, prof char(40), primary key(cname), foreign key prof_ref(prof) references professors(name));
     show tables;
     describe courses;
     insert into professors values ('A', 'COS');
     insert into professors values ('B', 'MAT');
     insert into courses values ('C1', 'A');
     insert into courses values ('C2', 'B');
     insert into courses values ('C3', 'A');
     

   The following command will let mysql to execute all the commands in batch mode:

   bolle:$ mysql -h studentdb -u [username] -p [database name] < batch-file

   You may redirect the output of mysql to a file to save the result.

10. You can dump the structure and data from your MySQL tables to an ASCII fil using the UNIX shell command mysqldump with output redirected to a file:

    bolle:$ mysqldump -h studentdb -u [username] -p [database name] > [filename];

    In this way you can back up your own database. We recommend you do this periodically. You can find more details in the MySQL Manual section on "mysqldump"

11. The database can be accessed via PHP (unnecessary for the COS 425 assignment):

            <?
                    $machine = "studentdb.cs.princeton.edu";
                    $user = "<username>";
                    $passwd = "$lt;password>";
                    $db = "<dbname>";
    
                    $connect = mysql_connect($machine,$user,$passwd);
                    if(!$connect) {
                            echo "Error: couldn't connect to db!<BR>\n";
                            $error = mysql_error($connect);
                            echo "<LI> $error\n";
                            exit;
                    }
                    mysql_select_db($db,$connect);
    
                    \\ query code here...
            ?>
    

12. Please notice that MySQL doesn't support some features, such as FOREIGN KEY constraints, CHECK constraints, ASSERTION, DOMAIN constraints, TRIGGERS etc. (Although the syntax allows FOREIGN KEY in CREATE TABLE, MySQL actually doesn't enforce the constraint.) This is discused in the manual: "Differences Compared to ANSI SQL92".