After completing this in-class exercise, you should feel more comfortable with the database concepts and the SQL commands we discussed during the last class lecture. These topics include:
We will also be looking at a few new SQL command line instructions.
I have already created an empty database for you. It uses your z-account username as its name.
As discussed in class last Thursday, we will have access to MySQL through the CSCI Linux server einstein.etsu.edu. To log onto Einstein, you will be using the terminal simulation program Putty. All of the laboratory machines should have putty.exe installed. You'll find it under "Internet Tools." Opening Putty should present you with a window like that shown below.
Use the following steps to set up your connection to Einstein.
After you've successfully used Putty to open a connection to Einstein, you should see a text window with a prompt like "login as:" at the top of the window. At the prompt, enter your user name (z-name), then press Enter. You will then be prompted for your password. Pressing Enter should log you onto Einstein.
If you have not done so already, change your Einstein password. To change your Einstein password, type "passwd" at the Einstein prompt and follow the directions. (It will ask for your old password, then your new password, then a confirmation of your new password.) Einstein is very picky about passwords. It cannot be a word from the dictionary, and it's best if the password has numbers and punctuation in it although I don't know that it requires these things. I cannot change Einstein passwords, so be sure to remember your password.
Once you've logged onto Einstein, you should have a prompt that looks like: [zabc123@einstein ~]$. At this prompt, type:
mysql -u zabc123 -p
The -u switch indicates that the string immediately following the switch will be your username. The -p switch indicates a password is needed to log on. (You can put the password as a text string immediately following the -p if you wish. If the password is left out, it will automatically prompt you.)
If you have not done so already, change your MySQL password. To change your MySQL password, type the following command at the MySQL prompt inserting your new password for "new_pw".
SET PASSWORD = PASSWORD('new_pw');
First, let's see what resources you have access to. The command show can be used to dump to the screen the resources available to you. For example, entering the command "show databases;" lists all databases your account has access to.
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| test |
| zabc123 |
+----------+
2 rows in set (0.00 sec)
mysql>
Notice the line ends in a semi-colon. If you leave this semi-colon out, the command will not execute and you will get a prompt that looks like "->". If this happens, simply enter a semi-colon at the prompt and press Enter.
mysql> SHOW DATABASES
-> ;
In order to access the tables and properties of a database, we must use the use command. The use command has the format " use databasename", and when executed, changes the current database to the one named. At the MySQL prompt, type the following where zabc123 is replaced with your username, i.e., the database I created for you in your account:
mysql> USE zabc123;
Database changed
If the database exists and your syntax is correct, MySQL will respond with "Database changed." At this point, you have access to an empty database. We can verify this using the show command again. Typing "show tables;" should reveal that there are not tables in your database at this point.
mysql> SHOW TABLES;
Empty set (0.00 sec)
Although you do not have the privileges on Einstein to create databases, databases can be created from the command line using the create command. The syntax is:
CREATE DATABASE databasename;
A database without tables is not very useful. Let's create a table and populate it. The create command can be used to generate tables within the current database. The syntax is similar to that for creating a database except that the keyword "TABLE" is used and there are arguments passed to the database identifying the fields. The syntax is:
CREATE TABLE tablename (field1 data_type [NULL/NOT NULL], field2 data_type [NULL/NOT NULL], ...);
The data types referred to in the above syntax should correspond with a data type as defined in the MySQL Reference Manual on data types.
The first table we are going to create is a simple table that keeps track of a student's name, ID, and z-account. Since names are variable length strings, we'll use the data type VARCHAR(30). Student ID's are always numeric, and although they may have leading zeros and such, we'll still use an INT field. Finally, the ZACCT is a string of 5 to 7 characters, so we'll use a data type of VARCHAR(7). The command below will define our table named "students" for us:
mysql> CREATE TABLE students (NAME VARCHAR(30) NOT NULL, ID INT NOT NULL, ZACCT VARCHAR(7));
Query OK, 0 rows affected (0.01 sec)
Using "SHOW TABLES," we should see that we now have a table defined.
mysql> SHOW TABLES;
+-------------------+
| Tables_in_zabc123 |
+-------------------+
| students |
+-------------------+
1 row in set (0.01 sec)
We can now use the command describe to give the schema for the table.
mysql> DESCRIBE students;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| NAME | varchar(30) | | | | |
| ID | int(11) | | | 0 | |
| ZACCT | varchar(7) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Now let's put some data in it. During the last lecture, we learned about adding a record (INSERT), removing a record (DELETE), and modifying a record (UPDATE). Let's begin by adding some records. Remember that the syntax for the INSERT command is:
INSERT INTO tablename (fieldname [, fieldnames]) VALUES (value [, values]);
You can leave out the (fieldname [, fieldnames]) portion of the command if you intend to define all of the fields of the record in the order they appear in the table. The command below inserts a record with all of the fields defined. Enter it at your command line to insert a record into your new table.
mysql> INSERT INTO students VALUES ('Theo LeSieg', 10158473, 'ztls999');
Query OK, 1 row affected (0.00 sec)
As suggested above, we can also insert records that do not have all of the fields defined. Use the command shown below to insert a record without the z-account field.
mysql> INSERT INTO students (NAME, ID) VALUES ('H.A. Ray', 10110594);
Query OK, 1 row affected (0.00 sec)
Just so that we can have some data to display, enter the following commands at your command line prompt.
INSERT INTO students VALUES ('Maurice Sendak', 10148764, 'zmas123');
INSERT INTO students VALUES ('Jean De Brunhoff', 10129486, 'zjdb123');
Now that we've entered some records, let's see if we can find them. Remember that to generate an output from a table, we will use the select command. The syntax of the select command is:
SELECT *| fieldname [, fieldnames] FROM tablename [, tablenames] WHERE fieldname=value ORDER BY fieldname [, fieldnames]
Let's begin by requesting all records from students be displayed. This is the purpose of the '*' in the select command. Enter the command "SELECT * FROM students" at your command prompt. The result should be similar to that shown below:
mysql> SELECT * FROM students;
+------------------+----------+---------+
| NAME | ID | ZACCT |
+------------------+----------+---------+
| Theo LeSieg | 10158473 | ztls999 |
| H.A. Ray | 10110594 | NULL |
| Maurice Sendak | 10148764 | zmas123 |
| Jean De Brunhoff | 10129486 | zjdb123 |
+------------------+----------+---------+
4 rows in set (0.00 sec)
Notice that since we did not give a field value for ZACCT in the H.A. Ray record, its value comes up as NULL.
Now lets' see if we can display only specific fields of the records. Enter the command "SELECT NAME FROM students". You should get the following result.
mysql> SELECT NAME FROM students;
+------------------+
| NAME |
+------------------+
| Theo LeSieg |
| H.A. Ray |
| Maurice Sendak |
| Jean De Brunhoff |
+------------------+
4 rows in set (0.00 sec)
We can also display records with specific settings for their fields. For example, "SELECT * FROM students WHERE ID=10110594" should result in only H.A. Ray's record.
mysql> SELECT * FROM students WHERE ID=10110594;
+----------+----------+-------+
| NAME | ID | ZACCT |
+----------+----------+-------+
| H.A. Ray | 10110594 | NULL |
+----------+----------+-------+
1 row in set (0.00 sec)
We can also use conditional statements in a SELECT command. The following command for example requests all students in the database with an ID greater than 10140000.
mysql> SELECT NAME FROM students where ID > 10140000;
+----------------+
| NAME |
+----------------+
| Theo LeSieg |
| Maurice Sendak |
+----------------+
2 rows in set (0.00 sec)
If you recall from the last lecture, we covered two additional commands regarding the handling of records in a table: update and delete. The update command was used to modify and existing record, and it had the following syntax:
UPDATE tablename SET fieldname=value WHERE fieldname=value;
For example, if we wanted to provide a ZACCT name for H.A. Ray, we could use the following command:
UPDATE students SET ZACCT='zhar333' WHERE ID=10110594;
Notice the importance of having the primary key of ID. Now if we do a select * on students, we get:
+------------------+----------+---------+
| NAME | ID | ZACCT |
+------------------+----------+---------+
| Theo LeSieg | 10158473 | ztls999 |
| H.A. Ray | 10110594 | zhar333 |
| Maurice Sendak | 10148764 | zmas123 |
| Jean De Brunhoff | 10129486 | zjdb123 |
+------------------+----------+---------+
4 rows in set (0.00 sec)
You can also delete records using the delete command. Remember that the syntax for the delete command is:
DELETE FROM tablename WHERE fieldname=value;
We can use this command to delete the Jean De Brunhoff entry.
DELETE FROM students WHERE ID=10129486;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM students;
+----------------+----------+---------+
| NAME | ID | ZACCT |
+----------------+----------+---------+
| Theo LeSieg | 10158473 | ztls999 |
| H.A. Ray | 10110594 | zhar333 |
| Maurice Sendak | 10148764 | zmas123 |
+----------------+----------+---------+
3 rows in set (0.00 sec)
If you no longer want to have a table contained in your database, you can use the command drop to remove it. The syntax is "DROP TABLE tablename;" without the quotation marks. Since the table we created in the previous steps will not be used anymore, (it was just a learning exercise) you can drop it with the following command.
DROP TABLE students;
After executing this command, executing the command "SHOW TABLES" should result in the empty set being returned.
In order to reinforce what we've already discussed, you will be using the commands discussed earlier to create another table and populate it with records. Begin by using the CREATE TABLE command to create a table named "mylibrary" which contains the following fields:
| Field Name | Data Type |
| TITLE | VARCHAR(50) |
| AUTHOR | VARCHAR(35) |
| PUB_YEAR | YEAR(4) |
| PRICE | DECIMAL(6,2) |
| INDX | CHAR(7) |
| ON_SHELF | BOOLEAN |
Now we are going to begin populating the database. I'll give you the syntax for the first entry. If you set up your table properly, you shouldn't have a problem inserting this data.
INSERT INTO mylibrary VALUES ('Catcher in the Rye, The', 'J.D. Salinger', 1951, 6.99, 'F-SAL00', 1);
Now use the INSERT command to insert the following data.
| TITLE | AUTHOR | PUB_YEAR | PRICE | INDX | ON_SHELF |
| Animal Farm | George Orwell | 1946 | 7.95 | F-ORW | 1 |
| To Kill a Mockingbird | Harper Lee | 1960 | 6.99 | F-LEE00 | 1 |
| Lord of the Flies | William Golding | 1954 | 7.95 | F-GOL00 | 1 |
| Of Mice and Men | John Steinbeck | 1939 | 8.34 | F-STE00 | 1 |
Wow, was that a pain or what trying to insert all of those names from above, especially when you're using a terminal where cut-n-paste is not supported or produces odd results. What would be nice is to create a text file of commands that we could run at once. For example, the following set of commands, if executed, would add fourteen more books to our library database.
INSERT INTO mylibrary VALUES ('One Flew Over the Cuckoos Nest', 'Ken Kesey', 1963, 7.99, 'F-KES00', 1);
INSERT INTO mylibrary VALUES ('Fahrenheit 451', 'Ray Bradbury', 1953, 6.99, 'F-BRA00', 1);
INSERT INTO mylibrary VALUES ('Road Ahead, The', 'Bill Gates', 1996, 14.99, 'T-GAT00', 1);
INSERT INTO mylibrary VALUES ('Computer Organization', 'Carl Hamacher', 2001, 132.81, 'T-HAM00', 1);
INSERT INTO mylibrary VALUES ('Seven Habits of Highly Effective People, The', 'Stephen R. Covey', 1989, 10.20, 'P-COV00', 1);
INSERT INTO mylibrary VALUES ('How to Win Friends and Influence People', 'Dale Carnegie', 1990, 7.99, 'P-CAR00', 1);
INSERT INTO mylibrary VALUES ('Linux Bible, 2005 Edition', 'Christopher Negus', 2005, 26.39, 'T-NEG00', 1);
INSERT INTO mylibrary VALUES ('Linux For Dummies, 6th Edition', 'Dee-Ann LeBlanc', 2005, 20.79, 'T-LEB00', 1);
INSERT INTO mylibrary VALUES ('Carrie', 'Stephen King', 1974, 6.95, 'F-KIN00', 1);
INSERT INTO mylibrary VALUES ('Jaws', 'Peter Benchley', 1974, 6.99, 'F-BEN00', 1);
INSERT INTO mylibrary VALUES ('158-Pound Marriage, The', 'John Irving', 1973, 6.99, 'F-IRV00', 1);
INSERT INTO mylibrary VALUES ('World According to Garp, The', 'John Irving', 1978, 7.99, 'F-IRV01', 1);
INSERT INTO mylibrary VALUES ('Hobbit, The', 'J.R.R. Tolkien', 1965, 7.99, 'F-TOL00', 1);
INSERT INTO mylibrary VALUES ('Bag of Bones', 'Stephen King', 1999, 7.99, 'F-KIN01', 1);
Copy the above series of commands into a text editor such as Notepad. You must use a text editor here as the control characters used by a word processor will corrupt the commands we are trying to execute. Save this text file locally with the file extension ".sql" (without the quotation marks).
Once you have saved the file locally, we can upload it to your Einstein account. Using WS-FTP or any other FTP program, connect to einstein.etsu.edu. Using your z-account name for a user name and your einstein password, you should be able to connect to the server.
Transfer the SQL script file (the one you saved with the ".sql" file extension) to your home directory on Einstein. This directory should be identified as /home/zabc123 in the WS-FTP window.
Now that we have the script loaded on Einstein, we should only have to execute it. Go back to Putty and your MySQL prompt. Type the following command where "mysqlscript" is replaced with the name you saved your sql script under.
\. mysqlscript.sql
You will not see your script commands, but you should see the output for each command from MySQL. If you want to, you can store scripts in sub-folders on Einstein. MySQL accepts standard Linux-formatted directory references in its file names.
Now do a select command to verify that all of the books were recorded in the library. Including the five you added in Part 7, you should have a total of 19 records or rows.
For the last part of this exercise, I want you to develop a script that creates a table called "favoritelinks" with fields for a description (DESCR), location (URL), and date and time last visited (LASTVISIT). Add insert commands to populate it with at least four web sites.
As far as this laboratory exercise is concerned, you are through. To exit MySQL, type "exit" and press Enter. To log off of Einstein, type "logout" and press Enter.
Developed by David Tarnoff for his students in CSCI 2910 at ETSU