Getting Started with a MySQL Database

This laboratory Exercise provides practice working directly with a MySQL database.

Introduction

A database is a software system that facilitates the efficient storage, retrieval, modification, and deletion of data. Although the general field of databases has evolved considerably over the years, most modern database systems organize data into tables. Databases based on tables are called relational databases, and most contemporary database packages follow this model. Database systems typically support sophisticated operations that allow a user to combine information from multiple tables and select items that meet specified complex criteria.

This course will use the MySQL database package, a widely-used, open source package that may be obtained from www.mysql.com. For CS 199, MySQL resides on server cs-199.willamette.edu. Access is available in two ways:

• Direct interactions are available on campus via a Terminal window.

  • In this framework, a user logs into a specific MySQL database.
  • The user issues a sequence of commands to retrieve, modify, or insert data.

• Access is available over the Web, via a browser/Web server interaction.

  • In this framework, a browser identifies a program (often written in PHP) to handle processing.
  • The program logs into a MySQL database, interacts with the database as part of processing, and prints desired results to a Web page (to be returned to the browser).

Accessing a MySQL Database Directly, with a Terminal Window

Typically, working directly with MySQL (with a Terminal window) eliminates some programming steps and thus is somewhat simpler than using PHP within a Web program. Thus, the first several database-related labs for this course utilize a Terminal window. After 1-2 weeks with MySQL in this environment, later labs will explore Web access via PHP.

1. Direct access to MySQL requires using a Terminal window to gain access to the Web server cs-199.willamette.edu. This requires logging in using ssh and moving to a proper directory (e.g., the shared directory on the cs-199 server). Details for logging in and identifying a proper directory are described here.

As is typical of many database packages, MySQL can maintain may separate databases. On the CS-199 server, MySQL maintains over a dozen separate databases, including the following:

• Databases group1, group2, group3, group4, and group5 for group work, when students work with a partner in lab. As part of the assignment of students into pairs, each pair is assigned a specific group for that week. Both pairs and group numbers may change from one week to another.

• Databases are established for each student, named according to their Willamette username. (The database for me is hwalker.)

MySQL allows access to databases, based on an account name and a password. For this course, the account names correspond exactly to the database names. Thus, account group1 is allowed to access database group1, and account hwalker can access the hwalker database.

In addition, when the cs-199 server responds to Web page requests, MySQL also grants permissions for that server to access any of the databases.

More generally, MySQL allows extensive control over what account can access which databases, but the account/database relationship is quite simple for this course.

For this course, passwords for both the groups and individual databases will be distributed in class.

2. Start up MySQL with the command:

   
      mysql -p -u acountname
   

   The -p indicates that you will supply a password for the account that you have selected. Use the MySQL password distributed in class.

3. Once logged in, check what databases you are allowed to use with the command:

   show databases;
   

   Likely only one database will be identified, but it worthwhile checking. (If 0 or more than 1 databases are listed, please consult the instructor!)

4. Once you determine which database you want to work with, indicate that choice with the command:

   use databaseName;
   

5. As with the PHP programming language, many MySQL commands end with a semicolon. Try typing the "use" command without the semicolon at the end, and describe what happens.

Examining the faculty table

Within a relational database, the table is the basic organizational unit. Data are organized into rows, called records. Columns indicate a type of information; and each column is given a title, called a field name.

For example, in the current database, table faculty contains the listing of faculty for the Departments of Computer Science and Mathematics for spring, 2019. The first five records for this table are:

+-----------+---------+------------+------------------+-------------------------+----------+--------+
| facultyID | first   | last       | dept             | email                   | phone    | office |
+-----------+---------+------------+------------------+-------------------------+----------+--------+
|         1 | Haiyan  | Cheng      | Computer Science | hcheng@willamette.edu   | 375-5339 |    207 |
|         2 | Jim     | Levenick   | Computer Science | levenick@willamette.edu | 370-6486 |    206 |
|         3 | Fritz   | Ruehr      | Computer Science | fruehr@willamette.edu   | 370-6165 |    208 |
|         4 | Henry   | Walker     | Computer Science | walker@cs.grinnell.edu  | 375-5314 |    210 |
|         5 | Peter   | Otto       | Mathematics      | potto@willamette.edu    | 370-6487 |    214 |

In this table, the first row shows the field names. Each subsequent row provides a separate directory entry, beginning with a unique facultyID number. Thus, the first record provides information for Haiyan Cheng, whose office is 207 and whose telephone number is 375-5339. Prof. Cheng's facultyID in this record is the integer 1.

SELECT: Accessing Existing Tables and Data

Working with a database proceeds by issuing a sequence of commands, just as programming in PHP requires a sequence of statements. When working with databases, a single command is called a query, and over the years experiences have yielded a common format for many database queries. Collecting the form of these commands together yields the Standard Query Language or SQL which is used in the MySQL database and in many other database packages around the world. (Of course, even the name MySQL reflects the idea that this database package might be considered as one's own system that uses the SQL language.)

6. Issue the following commands to get an overview of this database, its faculty table, and several versions of the SELECT statement.:

   
      show tables;
      describe faculty;
      SELECT * FROM faculty;
      SELECT last, email FROM faculty WHERE dept = "Computer Science";
      SELECT faculty.* FROM faculty WHERE last="Walker" AND first="Henry";
   

   In each case, record the results obtained.

   As this example illustrates, one can retrieve all fields with a query starting SELECT *. To retrieve specific fields, must indicate which fields you want, with the field names separated by commas (e.g., SELECT last, email).

7. It is common to capitalize keywords in SQL queries, but to what extent is capitalization necessary?

   1. In a SELECT statement, does the keyword SELECT have to be capitalized?
   2. In a SELECT statement, can you change the capitalization of the table faculty (e.g., try an initial capital: Faculty)?
   3. In a SELECT statement, can you change the capitalization of a field (e.g., try an initial capital for Dept)?

8. What results are obtained with these queries:

      SELECT faculty.* FROM faculty WHERE last="Walker" AND first="Henry";
      SELECT faculty.* from faculty WHERE office < 213;
      SELECT faculty.* from faculty WHERE office < 213 ORDER BY email;
      SELECT faculty.* from faculty WHERE office < 213 ORDER BY email ASC;
      SELECT faculty.* from faculty WHERE office < 213 ORDER BY email DESC;
   

9. Using the textbook for reference, develop queries to retrieve the following:

   1. Find all faculty whose telephone extension is 370-6165.
   2. Find all faculty whose email address is greater than or equal to potto@willamette.edu
   3. Find all faculty whose office numbers are between 210 and 214 (inclusive).
   4. List all faculty in the department of computer science with the last names in descending alphabetical order.

created 6 February 2019 revised 6 February 2019