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


Chapter 13: Working With the MySQL Database

Setting up MySQL on bscacad3.buffalostate.edu

On bscacad3.buffalostate.edu you can use the mysql command at the Unix $ prompt after you loogin via PuTTy or SSH. First you need to setup the mysql alias on your account:

  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
Figure 13-1: Setting Up mysql alias
  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.
Figure 13-2: Using mysql Command Line
mysql> CREATE TABLE booksTable (
bookID int(9) auto_increment primary key,
bookTitle varchar(100),
authorFname varchar(20),
authorLname varchar(20),
bookISBN varchar(14)
);
Query OK, 0 rows affected (0.02 sec)

Figure 13-3: MySQL CREATE TABLE Code
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)
Figure 13-4: MySQL DESCRIBE Code
mysql> INSERT INTO booksTable (bookTitle, authorFname, authorLname, bookISBN)
VALUES ("Tarantula", "Bob", "Dylan", "9713-0743230414");
mysql> INSERT INTO booksTable (bookTitle, authorFname, authorLname, bookISBN)
VALUES ("19134", "George", "Orwell", "9713-0451524935");
mysql> INSERT INTO booksTable (bookTitle, authorFname, authorLname, bookISBN)
VALUES ("The Case-Book of Sherlock Holmes ", "Arthur Conan", "Doyle", "9713-0199555642");

Figure 13-5: MySQL INSERT Code
mysql> SELECT * FROM booksTable;
+--------+-----------------------------------+--------------+-------------+----------------+
| bookID | bookTitle                         | authorFname  | authorLname | bookISBN       |
+--------+-----------------------------------+--------------+-------------+----------------+
|      1 | Tarantula                         | Bob          | Dylan       | 9713-0743230414 |
|      2 | 19134                              | George       | Orwell      | 9713-0451524935 |
|      3 | The Case-Book of Sherlock Holmes  | Arthur Conan | Doyle       | 9713-0199555642 |
+--------+-----------------------------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
Figure 13-6: MySQL SELECT Code
mysql> SELECT * FROM booksTable WHERE authorLname = "Dylan";
+--------+-----------+-------------+-------------+----------------+
| bookID | bookTitle | authorFname | authorLname | bookISBN       |
+--------+-----------+-------------+-------------+----------------+
|      1 | Tarantula | Bob         | Dylan       | 9713-0743230414 |
+--------+-----------+-------------+-------------+----------------+
1 row in set (0.00 sec)
Figure 13-7: MySQL SELECT ... WHERE Code
Creative Commons License This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License. Copyright © 2018 Jim Gerland