LIS 558: Database Management Systems

1
LIS 558 Database Management Systems
November 9, 2006

• Introduction to MySQL Part 2
• Margaret Kipp
• mkipp_at_uwo.ca

Faculty of Information Studies, University of
Western Ontario
2
Outline

• Writing SQL scripts and running them
• SELECT, INSERT, etc.
• Views
• Transactions
• Creating stored procedures
• Using cursors and triggers

3
SQL in Scripts

• a SQL script consists of a text file with
  multiple SQL statements in a row
• they can be separate statements or linked into
  transactions
• a SQL script file should have the extension .sql

4
How to use script files?

• The command for running scripts in MySQL is
  SOURCE
• e.g. SOURCE simplescript.sql
• the script will be run as if you had typed the
  commands at the MySQL command line

5
Why use script files?

• quickly set up a database
• software products built on a database management
  system will come with scripts to set up the
  necessary databases
• recreate the database in the event of a crash
• DBMS often offer backup commands that create a
  SQL script which can recreate the structure and
  data in a database

6
Example Script File

• This script file creates the database for the
  Summer Reading Programme
• http//publish.uwo.ca/mkipp/teaching/558/summerre
  adingprogramme.sql
• Look for familiar commands
• the script contains create table, view and insert
  commands
• this script creates some useful views and enters
  data into the ReadingLevel table, a lookup table

7
A Script File to Insert Data

• This script file inserts all the test data into
  the SummerReading database
• http//publish.uwo.ca/mkipp/teaching/558/datafors
  ummerreading.sql
• Look for familiar commands
• Note that this script does not use transactions,
  but for a production database it probably should

8
A Script file to Calculate Points

• This script file calculates how many points each
  child has accumulated so far
• http//publish.uwo.ca/mkipp/teaching/558/sr-calct
  otals.sql
• Three commands create table, insert select,
  update
• the first command creates a temporary table the
  second populates it with totals the third copies
  the totals into the Child table

9
A Simple SQL Script

• exit mysql and create a new file in pico
• pico simplescript.sql
• create a view to display a list of all children's
  names and the titles of books they have read
• type in a series of insert commands to enter a
  new branch and two new children into the database
• to save type CTRL-O

10
Simple SQL Script (cont.)

• run the script in mysql (source simplescript.sql)
• reopen the script (pico simplescript.sql) and add
  transactions to it
• start a transaction at the beginning and add a
  savepoint after the first two INSERTs
• see what happens when you rollback or commit,
  what does the savepoint do?

11
Stored Procedures

• sometimes you need to do calculations and store
  an intermediary result
• this is not generally possible in standard SQL
  without using a temporary table or a subquery
• a stored procedure is a little piece of code (a
  macro) that performs a specific set of operations
  and then returns a value or performs an action

12
Example Stored Procedure

• delimiter //
• CREATE PROCEDURE winners ()
• BEGIN
• SELECT Max(SumOfPoints), LibraryCard,
  ReadingLevel, Name
• FROM TotalPoints
• GROUP BY ReadingLevel
• END //
• delimiter

13
Example (cont.)

• to run a stored procedure, use the CALL command
• CALL winners()
• this will run the procedure and display any
  results

14
About the example

• the delimiter command tells the mysql command
  line where the stored procedure really ends
• normally you would use a semi-colon after END,
  but there is already a semi-colon after
  ReadingLevel and there can only be one in an
  entire statement
• the CREATE PROCEDURE command creates the
  procedure, giving it a name

15
Example (cont.)

• BEGIN and END are like bookends marking the
  beginning and end of the procedure
• between BEGIN and END are the commands that do
  all the work, in this case a SELECT statement
• other commands can go in the middle too

16
Another Example

• CREATE PROCEDURE calculateChildTotal (
• IN childname VARCHAR(20),
• OUT childpoints INT)
• BEGIN
• SELECT Sum(Book.Points)
• FROM Child INNER JOIN (ChildAndBook INNER JOIN
  Book ON Book.IDChildAndBook.bookid) ON
  Child.LibraryCardChildAndBook.childid
• WHERE Child.Name childname
• INTO childpoints
• END

17
Example (cont.)

• note the similarities between this procedure and
  the last (create procedure, begin, end, select
  statement)
• differences this procedure takes a child's name
  and returns the points in a variable
• call it like this
• CALL calculateChildTotal("Lisa Samson", _at_points)
• to see the value of the variable type
• SELECT _at_points

18
Example (cont.)

• The major differences between the two procedures
  are the parameters after the procedure name and
  the INTO clause in the SELECT statement
• The parameters are variables. A variable stores
  information.
• The INTO clause puts the result of the SELECT
  statement into the childpoints variable
• The childname variable passes the child's name to
  the procedure.

19
Writing a Stored Procedure

• use this syntax shell and fill it in
• DELIMITER //
• CREATE PROCEDURE ltnamegt (
• IN variable1 datatype1
• OUT variable2 datatype2)
• BEGIN
• ltput your SQL statements heregt
• END //
• DELIMITER

20
Stored Procedure Exercise

• write a stored procedure to display the list of
  books read by a given child
• the procedure will need one parameter for the
  child's name
• the procedure will use a select statement to
  display the list of books

21
Cursors

• a cursor retrieves all the results from the
  SELECT statement
• the results (result set) is stored and can be
  scrolled through (backwards or forwards)
• cursors are used by interactive database
  applications (e.g. websites based on databases)

22
Working With Cursors

• Step 1 Declare the cursor (give it a name and
  associate it with a SELECT statement)
• Step 2 Open the cursor for use (like opening a
  database)
• Step 3 populate the cursor with data
• Step 4 close the cursor

23
A Simple Cursor

• CREATE PROCEDURE readinglist()
• BEGIN -- does not retrieve any data!
• -- declare the cursor
• DECLARE booksinlist CURSOR FOR
• SELECT Book.Title FROM Book
• OPEN booksinlist -- open the cursor
• CLOSE booksinlist -- close the cursor
• END

24
A Simple Cursor (cont.)

• the procedure readinglist() creates a cursor then
  opens and closes it
• it does nothing else
• normally there would be additional code in
  between the open and close statements

25
A Cursor That Retrieves Data

• CREATE PROCEDURE readinglistsingle()
• BEGIN
• DECLARE book VARCHAR(50) -- declare local
  variables
• DECLARE booksinlist CURSOR FOR
• SELECT Book.Title FROM Book -- declare the
  cursor
• OPEN booksinlist -- open the cursor
• FETCH booksinlist INTO book
• SELECT book -- get a book from Book table and
  display
• CLOSE booksinlist -- close the cursor
• END

26
A Cursor That Does Something with Data

• See the following two examples which expand on
  the previous example
• http//publish.uwo.ca/mkipp/teaching/558/readingl
  ist.sql
• this example retrieves all book titles and
  displays them
• http//publish.uwo.ca/mkipp/teaching/558/readingl
  isttest.sql
• this examples retrieves all book titles but only
  displays the ones that start with The

27
Another Example

• This example calculates the total points so far
  for each child and updates the child table
• http//publish.uwo.ca/mkipp/teaching/558/booksrea
  dbyeachchild.sql
• This approach does not require a temporary table!

28
Cursor Exercise

• create a procedure that declares a cursor
• the cursor should select the branch name and
  contact person from the branch table
• display each branch name and the contact person,
  separated by a hyphen
• use the contatenation syntax we used before (e.g.
  lastname "-" firstname)

29
Triggers

• Triggers are little bits of code that run when a
  query tries to modify the data in a table
• a trigger can run before or after the query
• a trigger can be run for UPDATES, INSERTS or
  DELETES
• a trigger runs automatically once it is created
  unlike a stored procedure or script which must be
  run

30
Why Use Triggers?

• check that data is valid since MySQL does not
  support data validation in the CREATE TABLE
  command
• print out messages after data entry
• update other data when something is modified
• e.g. update Child.Total every time a child reads
  a new book

31
How to Create a Trigger

• assign the trigger a unique name
• list the table the trigger is attached to
  (triggers are related to tables)
• tell when the trigger will run (BEFORE OR AFTER)
• list the action the trigger should respond to
  (UPDATE, DELETE, INSERT)
• list the action or actions the trigger will
  perform

32
Example Trigger

• -- this trigger formats phone numbers
• CREATE TRIGGER formatphonenumber
• BEFORE INSERT ON Branch
• FOR EACH ROW
• BEGIN
• IF NEW.Phone NOT LIKE '519' THEN
• SET NEW.Phone CONCAT('519-',NEW.Phone)
• END IF
• END

33
Another Example

• This example handles the update of the Total
  column in the Child table using an INSERT TRIGGER
  on the ChildAndBook table, this trigger runs
  AFTER insert
• http//publish.uwo.ca/mkipp/teaching/558/childrea
  dbook.sql
• the trigger selects the current points and new
  points before doing the update

34
Trigger Exercise

• write a trigger to insert the current date when a
  row is inserted into the ChildAndBook table
• use the following SQL statement for the insert
• INSERT INTO ChildAndBook (ID, ReadOn)
• VALUES (NEW.ID, Now())
• should this be a before or after trigger?