What's MySQL
Useful Links
Requirements Statements (simplified)
We want to create a database to manage students and their transcripts information. For each student, we need to record the student number and name of the student, and as well as his / her date of birth. Student takes courses in particular semester of a year. Each course has a course id that can be used to unique identify the course within the university. Of course, we need keep track of the grade a student obtained for the course. Each course is offered by one department. For that we need to record the identification number, as well as the descriptive name of each department.
The conceptual design of the university database
The university database in MySQL format
create table department(
deptID int not null,
deptName char(50) not null);
create table course (
cID char(6) not null,
cName char(30) not null,
deptID int not null);
...
insert into department values(100,'Mechanical and Industrial Engineering');
insert into department values(101,'Computer Science');
...
insert into course values('MIE100','Dynamics',100);
insert into course values('MIE453','Bioinformatics Systems',100);
...
3.1 Getting Started
To start MySQL Monitor (client):
mysql -u <username> -p
mysql -h <hostname> -u <username> -p mysql -u <username> -p<password> -D dbname #yes, there is no space between -p and <password>
mysql -u <username> -p<password> -D dbname < filename.sql
To exit MySQL Monitor (client):
quit;
3.2 Creating Database
The first thing to is to create a database in MySQL server.
CREATE DATABASE univ;
If you want to show the databases you just created.
SHOW DATABASES;
You might want to remove the database from MySQL server at some time.
DROP DATABASE [IF EXISTS] univ;
3.3 Creating Table
First thing first, you must select a database explicitly before using it.
USE univ;
Before you can enter data into a MySQL and take advantage of its services, you need to define the structure (i.e., schema) of the database using the Data Definition Language (DDL).
CREATE TABLE student (
sID char(10) not null,
sName char(15) not null,
dob date not null);
If you want to see what has just happened.
SHOW tables; DESCRIBE student;
Notice: MySQL statements are case-insensitive
MySQL Data Types:
| INT - integer FLOAT - Small floating-point number DOUBLE - Double-precision floating-point number CHAR(N) - Text N characters long (N=1..255) VARCHAR(N) - Variable length text up to N characters long TEXT - Text up to 65535 characters long LONGTEXT - Text up to 4294967295 characters long |
3.4 RenamingTable
You can rename the name of the table.
RENAME TABLE student TO students;
3.5 Adding Data
Now we are ready to insert some data into our database using the Data Manipulation Language (DML).
INSERT INTO student VALUES('01','Mike','1986-02-23');
INSERT INTO student VALUES('02','Peter','1985-05-01');
Let's check if everything is fine.
SELECT * FROM student;
To enter a large amount of data, you might want to first store it in a file. In this way you can also reuse your data.
univ.sql contains schema definition and sample data insertion for our university database.
SOURCE univ.sql;
3.5 Querying Database
With the sample data in the database, now we can play with it in a more interesting way using SQL statements.
Q1: Find students who were born before 1985-09-01
SELECT * FROM student WHERE student.dob < '1985-09-01';
Q2: List the course numbers and names of all the MIE courses
SELECT cid, cname FROM course WHERE cid like 'MIE%';
Q3: Find all students who have take at least one Computer Science course;
List the student name along with the course number, name, year and semester
it was taken.
SELECT sname, transcript.cid, cname, year, sem
FROM student, course, transcript
WHERE student.sid = transcript.sid
AND course.cid = transcript.cid
AND transcript.cid like 'CS%';
Q4: List the name, age of all the students, ordered by their age
-- CURDATE() return current date -- DATEDIFF(x, y) return date difference between x and y in days -- FLOOR(x) Returns the largest integer value not greater than x SELECT sname, FLOOR(DATEDIFF(CURDATE(),dob)/356) AS age FROM student ORDER BY age;
Q5: Find students who has failed at least one course
SELECT student.sid AS 'Student Number',
sname AS 'Student Name',
cid AS 'Course Number',
grade AS 'Failing Grade'
FROM student, transcript
WHERE student.sid = transcript.sid
AND transcript.grade < 60;
Q6: Find students who has failed all courses he/she has taken
SELECT DISTINCT s1.sid AS 'Student Number',
s1.sname AS 'Student Name'
FROM student AS s1, transcript AS t1
WHERE s1.sid = t1.sid
AND t1.sid NOT IN
(SELECT t2.sid
FROM transcript AS t2
WHERE t2.grade > 60);
DBI is the DataBase Interface module for Perl.
The DBI module enables you
Database Access Process
DBI & DBD
Database programming In Perl has been made easy thanks to two types of modules: the DBI module and DBD modules.
DBI Module
The DBI module provides a database independent interface for Perl.
DBD Modules
DBD modules provide drivers
Data Source
In order to access a database, three pieces of information are required: data source, user name, password
A data source is specified by the generalized database interface used by the program, the database driver used to communicate with the real database, and the name of the real database.
Database Connection
Before a Perl program can access a database, a database connection (or session) between the program and the database has to be established.
Database Handle
After a database connection is established successfully, a database handle is returned, which have specific DBI functionality.
One of the important subroutine is to "prepare" a SQL statement before execution (see below).
SQL Statement and Statement Handle
SQL statements are represented as strings in Perl.
They have to be "prepared" (i.e., translated into native database representation) before execution.
A prepared statement is accessed through a statement handle, which also has specific DBI functionality.
SQL Statement Execution and Result
A prepared statement has to be excuted in order to take effect.
The result of excution can be retrieved also through the statement handle.
Example: First DBI Program
#! /usr/bin/perl -w
# show_tables - list the tables within the univ database.
# Uses "DBI::dump_results" to display results.
use strict;
# include a collection of DBI database utility routines
use DBI qw( :utils );
# define data source, user name and password as constants
use constant DATABASE => "DBI:mysql:univ";
use constant DB_USER => "lei";
use constant DB_PASS => "lei";
# make a database connection (a database handle is returned)
my $dbh = DBI->connect( DATABASE, DB_USER, DB_PASS )
or die "Connect failed: ", $DBI::errstr, ".\n";
# a MySQL command as a SQL statement to be issued
my $sql = "show tables";
# prepare the SQL statement (a statement handle is returned)
my $sth = $dbh->prepare( $sql );
# execute the prepared SQL statement and show the result
$sth->execute;
print "All talbes in the database:", "\n";
print "===============================", "\n";
print dump_results( $sth ), "\n\n\n";
# prepare and execute another SQL statement
my $sql2 = "select * from student";
my $sth2 = $dbh->prepare( $sql2 );
$sth2->execute;
# retrieve the result
# fetchrow_array retrieve one row from the result table at a time
# and returns an array of elements corresponding the fields in the row
print "Data in the student talbe:", "\n";
print "===============================", "\n";
print "ID Name DoB\n";
print "--------------------------\n";
my @student = ();
while (@student = $sth2->fetchrow_array()) {
print $student[0], " ", $student[1], " ", $student[2],"\n";
}
# remember to release the statement handle and close the database connection
$sth->finish;
$sth2->finish;
$dbh->disconnect;
Preparing a query is a relatively expensive operation.
If you need execute a query many times, it is a good idea the use cached queries.
Example: Cached Queries
#! /usr/bin/perl -w
# Cached Query Example
use strict;
use DBI;
# define data source, user name and password as constants
use constant DATABASE => "DBI:mysql:univ";
use constant DB_USER => "lei";
use constant DB_PASS => "lei";
# make a database connection (a database handle is returned)
my $dbh = DBI->connect( DATABASE, DB_USER, DB_PASS )
or die "Connect failed: ", $DBI::errstr, ".\n";
# get next student ID from keyboard
print "Please enter student ID (q to quit): ";
my $sth;
while(<>) {
# exit the loop if the user enters "q"
chomp();
if ($_ eq 'q') {
print "Bye!\n";
last;
}
# a SQL statement to be issued
my $sql = "select sName from student where sID = $_";
# prepare the SQL statement
# prepared_cached looks to see if the query is the same as last time.
# if so, it retrieves and returns the previous statement handle, instead
# of creating a new one.
$sth = $dbh->prepare_cached( $sql );
# execute the prepared SQL statement and show the result
$sth->execute;
# retrieve the result
my @student = $sth->fetchrow_array();
print "The name of the student is: ";
if (defined $student[0]) {
print $student[0], "\n";
} else {
print "[NO SUCH STUDENT]", "\n";
}
# get next student ID from keyboard
print "\nPlease enter student ID (q to quit): ";
}
$sth->finish;
$dbh->disconnect;
Example: Add a new student record
#! /usr/bin/perl -w
# Insertion Example
use strict;
use DBI;
# define data source, user name and password as constants
use constant DATABASE => "DBI:mysql:univ";
use constant DB_USER => "lei";
use constant DB_PASS => "lei";
# make a database connection (a database handle is returned)
my $dbh = DBI->connect( DATABASE, DB_USER, DB_PASS )
or die "Connect failed: ", $DBI::errstr, ".\n";
# a MySQL command as a SQL statement to be issued
my $sql = "insert into student values('20','Lei','1978-04-08')";
# prepare the SQL statement (a statement handle is returned)
my $sth = $dbh->prepare( $sql );
# execute the prepared SQL statement and show the result
$sth->execute;
# print out the updated student table
my $sql2 = "select * from student";
my $sth2 = $dbh->prepare( $sql2 );
$sth2->execute;
print "ID Name DoB\n";
print "--------------------------\n";
my @student = ();
while (@student = $sth2->fetchrow_array()) {
print $student[0], " ", $student[1], " ", $student[2],"\n";
}
# remember to release the statement handler and close the database connection
$sth->finish;
$sth2->finish;
$dbh->disconnect;
We want to keep databases in consistent state after the update to the databases
The database transaction ensures consistent. A transaction is a set of database queries, with the all-or-none property.
#! /usr/bin/perl -w
# Commit/Rollback Example
use strict;
use DBI qw;
# define data source, user name and password as constants
use constant DATABASE => "DBI:mysql:univ";
use constant DB_USER => "lei";
use constant DB_PASS => "lei";
# make a database connection (a database handle is returned)
# auto commit is turn off, which means an explicit commit statement
# need to be issued before an transaction is committed.
my $dbh = DBI->connect( DATABASE, DB_USER, DB_PASS, {AutoCommit => 0})
or die "Connect failed: ", $DBI::errstr, ".\n";
# a MySQL command as a SQL statement to be issued
my $sql = "insert into student values('20','Lei','1978-04-08')";
# prepare the SQL statement (a statement handle is returned)
my $sth = $dbh->prepare( $sql );
# execute the prepared SQL statement and show the result
$sth->execute;
# print out the student table before commit/rollback
my $sql2 = "select * from student";
my $sth2 = $dbh->prepare( $sql2 );
$sth2->execute;
print "Here is the student table before commit/rollback\n";
print "=================================================\n";
print "ID Name DoB\n";
print "--------------------------\n";
my @student = ();
while (@student = $sth2->fetchrow_array()) {
print $student[0], " ", $student[1], " ", $student[2],"\n";
}
# ask for confirmation
print "One row is to be inserted into the database, accept it?[y/n]\n";
if ( =~ /y/) {
$dbh->commit;
} else {
$dbh->rollback;
}
# print out the student table after insertion
$sth2->execute;
print "Here is the student table after commit/rollback\n";
print "================================================\n";
print "ID Name DoB\n";
print "--------------------------\n";
while (@student = $sth2->fetchrow_array()) {
print $student[0], " ", $student[1], " ", $student[2],"\n";
}
# remember to release the statement handler and close the database connection
$sth->finish;
$sth2->finish;
$dbh->disconnect;