Guide to Object-oriented Programming With Java (Buffalo State University Version)

1. Login to your bscacad3.buffalostate.edu account using Putty (on Windows) or ssh (on MacOS) using your BSC Username and Password. Your mysql-password is your Banner ID beginning with a capital B.
2. Edit your .bashrc file using the vi editor (see the Washington University site for more information about Using VI) to setup the mysql alias:
   1. At the Unix $ prompt type: vi .bashrc
      1. Scroll to the end of the file
      2. Press SHIFT-A to 'append at the end of the this line'
         1. Press the ENTER key to go to the next line
         2. Type this line:
            
            alias mysql='mysql -uyour-bsc-userid -pyour-mysql-password -D your-bsc-userid'
            
            
         3. Press CONTROL-[ to exit the append mode
      3. To save your .bashrc file, press: :wq
3. At the Unix $ prompt type: source .bashrc

1. At the Unix $ prompt type: mysql
2. At the mysql> prompt type: show tables;
3. There should not be any tables listed yet unless you used mysql for another course.
4. To create a table, at the mysql> prompt type:
   
   CREATE TABLE testTable (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, firstName VARCHAR (25), lastName VARCHAR(25));
   
   
5. At the mysql> prompt type: describe testTable;
6. To add data to your table, type:
   
   INSERT INTO testTable (firstName, lastName) VALUES ("your first name", "your lastname");
   
   
7. To view the data in your table type:
   
   SELECT * FROM testTable ORDER BY lastName, firstName;
   
   
8. To exit mysql, at the mysql> prompt type: exit
9. You should now be back at the Unix $ prompt.

mysql> DESCRIBE booksTable;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| bookID      | int(9)       | NO   | PRI | NULL    | auto_increment |
| bookTitle   | varchar(100) | YES  |     | NULL    |                |
| authorFname | varchar(20)  | YES  |     | NULL    |                |
| authorLname | varchar(20)  | YES  |     | NULL    |                |
| bookISBN    | varchar(14)  | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM booksTable;
+--------+-----------------------------------+--------------+-------------+----------------+
| bookID | bookTitle                         | authorFname  | authorLname | bookISBN       |
+--------+-----------------------------------+--------------+-------------+----------------+
|      1 | Tarantula                         | Bob          | Dylan       | 9714-0743230414 |
|      2 | 19134                              | George       | Orwell      | 9714-0451524935 |
|      3 | The Case-Book of Sherlock Holmes  | Arthur Conan | Doyle       | 9714-0199555642 |
+--------+-----------------------------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM booksTable WHERE authorLname = "Dylan";
+--------+-----------+-------------+-------------+----------------+
| bookID | bookTitle | authorFname | authorLname | bookISBN       |
+--------+-----------+-------------+-------------+----------------+
|      1 | Tarantula | Bob         | Dylan       | 9714-0743230414 |
+--------+-----------+-------------+-------------+----------------+
1 row in set (0.00 sec)