Introduction to MySQL & Discussion.
clements89
lab_-intro_to_mysql.pdf
1
Introduction to MySQL
Overview
This lab walks you through using MySQL. MySQL is a relational database that can be used as part of Web
and other applications. This lab serves as a primer for using MySQL and will serve as a foundation when
we discuss SQL injection attacks and possible mitigations.
Learning Outcomes:
At the completion of the lab you should be able to:
1. Connect to a MySQL database and show the tables within the Ubuntu virtual machine
2. Create MySQL tables containing popular data types and constraints
3. Insert, update and delete data from MySQL database tables
4. Create and execute SQL Select statements and simple joins on MySQL tables
Lab Submission Requirements:
After completing this lab, you will submit a word (or PDF) document that meets all of the requirements in
the description at the end of this document. In addition, your MySQL file should be submitted. You can
submit multiple files in a zip file.
Virtual Machine Account Information
Your Virtual Machine has been preconfigured with all of the software you will need for this class. The
default username and password are:
Username : umucsdev
Password: umuc$d8v
MySQL Username: sdev_owner
MySQL password: sdev300
MySQL database: sdev
Part 1 – Connect to a MySQL database and show the tables within the Ubuntu virtual machine
The Virtual Machine already has MySQL installed. A MySQL username has also been created along with a
database to use for your applications and testing. Although there are SQL editors available, for
simplicity, we will use gedit to create the MySQL scripts. To run the scripts we will just copy and paste
from the editor to the MySQL prompt.
1. Assuming you have already launched and logged into your SDEV32Bit Virtual Machine (VM)
from the Oracle VirtualBox, open up the terminal by clicking on the terminal icon.
2
2. To start the MySQL database type the following the terminal prompt:
mysql -u sdev_owner -p
When prompted for the password enter sdev300
3
3. To display the available databases type the following at the mysql prompt:
show databases;
4. The database we will be using for this course is sdev. To use this database, type the following at
the mysql prompt:
use sdev;
4
5. To display the current tables in the sdev database, type the following command at the mysql
prompt:
show tables;
You may already have some tables in your database. If so, the names of those tables would be
displayed. If not, you would see Empty set as illustrated above.
6. To exit the MySQL application. Type exit at the mysql prompt:
5
Part 2 Create MySQL tables containing popular data types and constraints
The reading for this week covered the foundations for creating and dropping tables using a variety of data types and constraints. In this exercise we will create three tables along that could be used to represent a very simple student and course registration system. The tables all have primary keys. One table provides foreign keys to the other two tables. When creating SQL commands to be executed in MySQL, it is always recommended to prepare them in a text editor and then either run the script or copy and paste into the MySQL application. Since this isn’t a course in database design, we will just copy and paste from the gedit text editor.
1. Create a folder named sql to hold your sql scripts in the home/umucsdev directory. Copy and paste the following SQL code into a file named CreateTables.sql
use sdev;
// Create a student table
CREATE TABLE Students (
PSUsername varchar(30) primary key,
FirstName varchar(30),
LastName varchar(30),
EMail varchar(60)
);
CREATE TABLE Courses(
CourseID int primary key,
CourseDisc varchar(4),
CourseNum varchar(4),
CourseTitle varchar(75)
);
CREATE TABLE StudentCourses (
StudentCourseID int primary key,
CourseID int references Courses(CourseID),
PSUsername varchar(30) references Students(PSUsername)
);
6
2. Open a terminal and launch mysql using the following command:
mysql -u sdev_owner -p
When prompted type the sdev password
7
3. At the mysql prompt begin copying and pasting the SQL code. You can copy it all at once.
4. The Query OK output from MySQL is an indicator your SQL execution was successful. In addition
your can type the following at the mysql prompt to show the tables:
show tables;
5. You can also describe each table:
8
desc Courses;
desc StudentCourses;
desc Students;
On the Linux Operating system, MySQL table data and names are case sensitive. Hence (Students is not the same as students or STUDENTS). This is critical when you start running your queries for your applications. You should experiment in MySQL by creating one or two of your own tables. Be sure to use several data types and add a primary key for each table and other constraints as needed.
Part 3 Insert, update and delete data from MySQL database tables
Once tables have been created your can insert records and then update the record or even delete the
record. This exercise discusses how to use MySQL to populate and modify the records in your database.
We will once again, create the database scripts using the gedit text editor.
1. Copy and paste the following SQL code into a file named InsertUpdateDeleteTables.sql
-- Insert students
insert into Students
values ('jsmith', 'John','Smith','[email protected]');
insert into Students
9
values ('mjones', 'Mary','Jones','[email protected]');
insert into Students
values ('jparsons', 'Jeff','Parsons','[email protected]');
-- Insert Courses
insert into Courses
values (1, 'SDEV','300','Secure Web Development');
insert into Courses
values (2, 'SDEV','360','Secure Software LifeCycle');
-- Insert student courses
insert into StudentCourses
values (1,1,'jsmith');
insert into StudentCourses
values (2,1,'mjones');
-- Update the Student data
update Students set Email = '[email protected]'
where PSUsername = 'jsmith';
update Students set Email = '[email protected]'
where PSUsername = 'mjones';
update Students set Email = '[email protected]'
where PSUsername = 'jparsons';
-- delete the Parsons record
delete from Students
where PSUsername = 'jparsons';
10
2. Start MySQL on your virtual machine, login as sdev_owner and then be sure to use the sdev database.
3. Copy and paste the SQL statements into the mysql prompt and your data will be inserted and modified.
As you review the script and experiment by inserting, updating and deleting your own unique records, you should note following:
11
a. The primary key is a critical element and used to find records to update as well as delete. Notice the where clause is based on the primary key. When you build your own statements, be sure to consider this model.
b. The update statement uses the set clause. If you wanted to update multiple columns you would just use a comma between each new update. (e.g. set Email=’NewEmail, lastname=’NewLastname’)
c. Be cautious with both deletes and updates. Be sure you use where clauses to filter to the specific rows or rows you want to modify or delete. Disastrous consequences could occur if you don’t take caution here. For example, if you don’t put the where clause for the updates, you could potentially change every record with your new data.
Part 4 Create and execute SQL Select statements and simple joins on MySQL tables
Once tables have been created and data populated, you can query the tables using the Select
statement. The Select statement has many clauses, the examples below will emphasis the where and
order by clauses.
1. Copy and paste the following SQL code into a file named QueryTables.sql
-- Select all records and columns
select * from Students;
select * from Courses;
select * from StudentCourses;
-- Use the Where clause
select Email from Students
where PSUsername = 'jsmith';
select * from Courses
where CourseDisc Like ('SD%');
select PSUsername from StudentCourses
where CourseID = 1;
-- Order by
select * from Students
Order by LastName;
select * from StudentCourses
order by CourseID;
-- Joins to get more details
select A.PSUsername, CourseDisc from
StudentCourses A, Courses B, Students C
where A.PSUsername =C.PSUsername
and A.CourseID = B.CourseID;
select A.PSUsername, CourseDisc,CourseNum, CourseTitle from
StudentCourses A, Courses B, Students C
12
where A.PSUsername =C.PSUsername
and A.CourseID = B.CourseID
order by B.CourseID,A.PSUsername;
2. Start MySQL on your virtual machine, login as sdev_owner and then be sure to use the sdev database.
3. Copy and paste from the QueryTables.sql file to your mysql prompt. Executing one or two queries at a time is recommended so you can experiment and analyze the results for each query.
13
As you experiment and analyze the SQL statements and results note the following:
a. Where clauses are critical to filtering and returning the exact rows you want. b. If you use the Like clause you can also use the wild card character (‘%’) to provide results for any
character. c. You can use the Order by clause with multiple columns by using a comma between each
column. d. Joins can be tricky. For this course, we will work to keep no more than 3 table joins. Notice the
pattern you need to join each table on the column that has the identical column. (e.g. PSUsername is found in both Students and StudentCourses). You may need to reference the
14
alias (e.g. A or B in this case) to remove redundant namings. This is why we had to use order by B.CourseID, A.PSUsername. If we just used CourseID or PSUsername, the query would not know which table column to use. Joins Query statements may take some extra practice to become comfortable with.
Lab submission details:
As part of the submission for this Lab, you will design your own tables and populate them with data
based on the following requirements. For each of the requirements, be sure to save the specific SQL
statements that you used. Please label each SQL statement corresponding to the numbered
requirements below:
1. Create a table named Faculty to store FacultyID( Primary key), FirstName, LastName, Email, Date
of birth and number of courses taught to date. You should select the appropriate data types and
constraints for the table.
2. Create a table named Courses to store CourseID (Primary key), Discipline Name (e.g. SDEV),
Course Number (e.g. 300), Number of Credits (e.g. 3), Year first offered (e.g. 2010) and Course
Title. You should select the appropriate data types and constraints for the table.
3. Create a table named FacultyCourses to store the Faculty and the Courses they have taught. You
should design the table based on the Faculty and Courses tables you previously created.
4. Use Insert statements to populate at least 10 faculty records, 5 Course records, and 25
FacultyCourses records
5. Use update statements to update all Courses to 4 credits
6. Use update statements to update any Faculty who have taught more than 4 courses to modify
the number to 5 courses taught
7. Delete any Faculty record whose LastName starts with the letter ‘Z’
8. Delete any Course record that was first offered in 1999
9. Use select statements to display all records in all 3 tables. Order by the Faculty lastname, and
Course title as appropriate. Note you should use 3 separate select statements to satisfy this
requirement.
10. Use Select statements to display all Faculty who have not taught any courses
11. Use Select statements to display all Courses offered before 1984
12. Use Select and appropriate joins to display all columns from the Faculty and Course tables for
each Faculty and Course in the FacultyCourse table. Note: this will be a 3-table join.
Create screen shots showing the successful running of each your scripts.
For your deliverables, you should submit a zip file containing your word document (or PDF file) with
screen shots of the application running successfully along with your SQL script file. You do not need
separate files for each script. You can include them in one SQL script.
Include your full name, class number and section and date in the document.
