CS425 MySQL Instructions:

A MySQL server has been created on the CS department computing system for use b 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 zhifengc my database name is zhifengc and the username for the database is also zhifengc. 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 bee given one for COS425 work. The login for the account is your Princeton user ID and th password has been set to your CIT password. You can access a CS machine by usin a workstation in one of the public areas in the CS department or by ssh'ing to "portal.cs.princeton.edu".

1. First of all, you should change your password for studentdb using the command:
   bolle:$ mysqladmin -h studentdb -p -u [username] password [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]
   
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];
    You can find more details in th MySQ Manual section on "mysqldump"
11. 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 and CHECK in CREATE TABLE, MySQL actually doesn't enforce the constraints.) This is discused in the manual: Chap 5.