revised 9 February 2019
slighly edited 8 March 2019
| CS 199 | Willamette University | Spring, 2019 |
|
Programming in PHP, Databases with MySQL, and Web Applications |
||
The previous lab began an exploration of databases by considering how one could access a database and then use a SELECT query to extract data from a database. This lab considers how data in a table might be expanded, modified, or deleted.
The INSERT statement adds new records to the database. The simplest version of this statement specifies each field, in the order specified in the database. Thus, the following statement would add Haiyan Cheng's record to the faculty table:
INSERT INTO faculty VALUES
(NULL, "Haiyan", "Cheng", "Computer Science",
"hcheng@willamette.edu", "375-5339", 207);
As this example illustrates, it is common for keywords in an SQL statement to be capitalized, although this is not necessary.
In this version of the INSERT statement, information is given for all fields of each record. In the case of the facultyID number (which is assigned a unique number with each insertion), NULL is specified.
An alternative syntax first indicates the fields names in parentheses and then lists the corresponding values—again in parentheses. In this format, insertion of the same record might be:
INSERT INTO faculty (first, last, dept, email, phone, office)
VALUES ("Haiyan", "Cheng", "Computer Science",
"hcheng@willamette.edu", "375-5339", 207);
Using this approach yields file populate-faculty-table.sql to add all records to the faculty table lists, with a separate insert statement for each record.
As a third alternative when inserting records, we could include the facultyID number explicitly, using a statement:
INSERT INTO faculty VALUES
(1, "Haiyan", "Cheng", "Computer Science",
"hcheng@willamette.edu", "375-5339", 207);
However, since id is defined with auto_increment, we can leave that field as NULL, in which case the database will determine an appropriate value; more about auto_increment shortly.
In yet another variation of the insert statement, we specify explicitly which value goes with which field:
INSERT INTO faculty
set first = "Haiyan",
last = "Cheng",
dept = "Computer Science",
email = "hcheng@willamette.edu",
phone= "375-5339",
office= 207;
Although this form is somewhat wordy, it clearly identifies exactly which value goes where within a record. Again, since no facultyID is given, the auto_increment attribute will assign a new record number to this field.
Insert 3 records of your choosing into the database. Use several variations of the insert statement to perform this task.
The Delete statement removes records from the database.
In using Delete be sure to limit the scope of your work, since
this statement has the potential to remove all records from the
database.
The following statement removes all records for those with the last name "Walker" from the database:
DELETE FROM faculty WHERE last="Walker";
Delete exactly one of your newly-inserted records from the database.
The Update statement changes fields within records.
As with Delete be sure to limit the scope of a Update,
since this statement has the potential to change all records in the
database.
The following statement updates Walker's e-mail and office information from the faculty table:
UPDATE faculty
SET email="hwalker.willamette.edu",
office="Racquetball Court"
WHERE last=Walker";
Change one of your remaining two newly-created records, perhaps changing a first name and phone extension.
The UPDATE statement changes fields within records.
As with DELETE, be sure to limit the scope of an
UPDATE command, since this statement has the potential to change all
records in the database table.
The following statement updates Walker's email and office information in the faculty field:
UPDATE faculty
SET email='walker@grinnell.edu",
phone="641-269-4208"
WHERE last="Walker";
Modify the telephone numbers of 3 faculty in the current faculty table, adding the area code to their current number. (You will need to use three UPDATE statements, each specifying the complete, extended number for each faculty member.
The creation of a table involves a specification of the various fields for that table. For the current directory example, this might be done with the following statement:
CREATE TABLE faculty ( facultyID int (10) auto_increment, first varchar (30) not null, last varchar (30) not null, dept varchar (30) not null, email varchar (40) not null, phone varchar (30) not null, office int (10) unsigned not null, primary key (facultyID) );
This SQL statement establishes a new table, called faculty. This table has 7 fields:
Further, the specification indicates that none of these fields can be null — that is, each of these fields must contain data, and cannot be omitted from a record.
The facultyID field has two additional properties. First, it is a "primary key", meaning that the value in this field must be unique; two records are not allowed to have the same facultyID. In this case, the primary key is a single field, although in other tables a primary key could involve a combination of fields that together would identify a record uniquely. Second, when information is inserted into this table, one could specify the facultyID explicitly, but one also could ask the database to take then next highest integer value for the id field; if this field is not specified, the database will auto_increment the highest field value to maintain a new, unique integer for this field.
Develop, populate, and modify a database table for a (hypothetical CD collection).
Looking ahead to later labs in this course, we will need to work with a table of courses as a companion to the faculty table. (More about this in a week or so.)
For courses offered Spring 2019 by the Departments of Computer Science and Mathematics, a course table has been created with the following fields:
Use a SELECT query to view the entire table.
|
created 6 February 2019 revised 9 February 2019 slighly edited 8 March 2019 |
|