LIS 558: Database Management Systems

1
LIS 558 Database Management Systems
October 26, 2006

• Introduction to MySQL
• Margaret Kipp
• mkipp_at_uwo.ca

Faculty of Information Studies, University of
Western Ontario
2
Outline

• What is MySQL?
• Connecting to MySQL
• Opening a database
• Working with MySQL
• Using SQL DML and DDL in MySQL
• Introduction to SQL DCL and TCL

3
What is MySQL

• open source relational database management system
• cross platform (Windows, Unix/Linux, Mac)
• client-server database
• has a command line utility and graphical
  interfaces

4
Client-Server Software

• consists of two parts client and server
• can be on the same machine or different machines
• client connects to the server
• e.g. browser connects to another machine to get a
  webpage

5
Accessing MySQL

• Client-Server setup
• user works with client application which connects
  to server
• database is stored on the server
• Note this aspect is hidden in Access, you are
  actually connecting to the Jet database driver
  (the Access database driver) automatically when
  you start the Access application

6
Accessing MySQL

• Two methods
• login to Unix server
• use a secure shell client to create a text or
  graphical connection to a Unix server
• MySQL query browser
• a graphical interface to MySQL

7
How to Connect to MySQL on the server

• Run the secure telnet facility
• SSH Secure Telnet Secure Shell
• Click on Quick Connect
• Host Name gcampbell.fims.uwo.ca
• User Name ltyour user namegt
• You will be prompted for the password ltpasswordgt
• You should see a prompt, this is the Linux
  command line

8
Important Linux Commands

• ls
• show what files are in your user directory
• man
• short for manual, typing "man mysql" will give
  technical information about the mysql command
  (user documentation is on the web)
• pico
• a text editor with similar functionality to
  notepad

9
Working with MySQL

• to connect to the mysql server
• mysql -u userid -p
• replace userid with your e-mail userid
• you will be prompted for the password
• this command runs mysql, and provides your user
  name and a request to be prompted for a password
• from the MySQL command line you can explore the
  databases available through the mysql client

10
MySQL Commands

• SHOW
• the show command can be used to explore the
  database structure
• e.g. SHOW DATABASES
• lists all databases available
• Note each MySQL or SQL statement must be
  terminated with a semicolon ()

11
MySQL Commands (cont.)

• USE ltdatabasegt
• connect to the specified database
• SHOW TABLES
• shows all tables in a database
• SHOW COLUMNS FROM lttablegt
• shows all fields in the listed table
• in MySQL, can also use DESC lttablegt

12
MySQL Commands (cont.)

• SHOW CREATE DATABASE ltdatabasegt
• SHOW CREATE TABLE lttablegt
• displays the MySQL statements used to create the
  selected database or table
• SHOW GRANTS FOR ltusergt
• displays security settings for a specified user

13
MySQL Commands (cont.)

• SHOW ERRORS
• SHOW WARNINGS
• displays server errors or warning messages
• SHOW STATUS
• displays status information about the MySQL server

14
Exploring the Database

• 1. SHOW DATABASES
• lists all the available databases
• 2. USE SummerReading
• opens the summer reading database
• 3. SHOW TABLES
• lists all the tables in our current database
• if you have not opened a database, this command
  will fail

15
Exploring the Database (cont.)

• 1. DESC lttablenamegt
• will return a definition view of the table
• try this on a few tables
• 2. SHOW CREATE DATABASE ltdatabasegt
• shows the commands used to create the database
• 3. SHOW CREATE TABLE lttablegt
• shows the commands used to create the table

16
Exploring the Database (cont.)

• 1. SHOW STATUS
• status information about the mysql server
• 2. SHOW PRIVILEGES
• list possible security privileges for users
• 3. SHOW GRANTS
• to show the privileges for a specific user

17
SQL in MySQL

• MySQL supports most SQL statements including
• permissions and security statements (DCL)
• transaction control (TCL)
• create and alter (DDL)
• select, insert, update, delete, etc. (DML)

18
Fully Qualified Table Names

• as in Access, it is not always necessary to
  specify the table name with the field name when
  working on a single table
• with multiple tables it is necessary
• Syntax tablename.fieldname
• e.g. Child.ReadingLevel
• Child.Name
• Book.Title

19
SELECT in MySQL

• commands are the same as in Access (SELECT, FROM,
  WHERE, GROUP BY, etc)
• some modifiers are different
• Top N gt LIMIT N OFFSET X
• the limit clause goes at the end of the SELECT
  statement, X is the number of rows to skip
• e.g. SELECT FROM Child LIMIT 5 OFFSET 3

20
JOINS

• INNER JOIN
• query results come from rows in Table A that
  match rows in Table B
• LEFT OUTER JOIN
• query results include all selected rows in Table
  A plus the rows in Table B that match
• RIGHT OUTER JOIN
• query results include all selected rows from
  Table B plus the rows in Table A that match

21
JOIN Example

• select all children from the database, plus the
  titles of any books they have read
• SELECT Child.Name, Book.Title
• FROM Child LEFT OUTER JOIN (ChildAndBook INNER
  JOIN Book ON ChildAndBook.bookid Book.ID) ON
  Child.LibraryCard ChildAndBook.childid

22
SELECT Exercises

• select all items from the child table
• select title, readinglevel and points from the
  book table
• select all titles and the names of the children
  who have read them

23
SELECT Exercises (cont.)

• select all book titles from the database, plus
  the names of any children who have read them
• select all book title from the database plus a
  count of how many children have read them

24
INSERT, UPDATE and DELETE in MySQL

• Examples
• UPDATE Branch
• SET Branch.ContactPerson 'Joan Platelet'
• WHERE Branch.ID 1
• DELETE FROM ChildAndBook
• WHERE childid 5 AND bookid 8

25
UPDATE and DELETE Exercises

• what is the SQL for changing a child's place of
  registration (pick a child)
• what is the SQL for changing a child's comments
  on a book (pick a specific book and child)

26
INSERT Exercises

• what is the SQL for signing a child up for the
  summer reading programme?
• what is the SQL for adding a new book to the
  reading list

27
CREATE TABLE in MySQL

• create a table in MySQL
• remember that the SQL datatypes are not the same
  as the Access datatypes
• Example
• CREATE TABLE ChildPointsTemp (
• SumOfPoints INT,
• LibraryCard INT
• ) ENGINEInnoDB

28
SQL Datatypes Conversion Table
29
CREATE Exercises

• write the SQL to create a table for publisher
  information
• this table would be joined to the book table by
  publisher ID (or publisher name)
• also, write the SQL to alter the book table and
  add the publisher column and the foreign key

30
INSERT SELECT in MySQL

• INSERT SELECT is a command used to copy data from
  one table to another
• this command does not create tables, this must be
  done separately
• like a regular INSERT except the VALUES section
  is replaced by SELECT query
• Example
• can be used to calculate totals from one table
  and then insert them into another table's total
  column

31
INSERT SELECT Example

• INSERT INTO ChildPointsTemp (SumOfPoints,
  LibraryCard)
• SELECT Sum(Book.Points), Child.LibraryCard
• FROM ((ReadingLevel INNER JOIN Child ON
  ReadingLevel.ReadingLevelChild.ReadingLevel)
  INNER JOIN Book ON ReadingLevel.ReadingLevelBook.
  ReadingLevel) INNER JOIN ChildAndBook ON
  (Book.IDChildAndBook.bookid) AND
  (Child.LibraryCardChildAndBook.childid)
• GROUP BY Child.LibraryCard, Child.ReadingLevel
• ORDER BY Sum(Book.Points) DESC,
  Child.ReadingLevel

32
INSERT SELECT Exercises

• write an INSERT SELECT statement to enter the
  fact that a child (identified by name) read a
  given book (identified by title) and commented on
  it into the ChildAndBook table
• Note it is perfectly acceptable to add a value
  into the SELECT that is not in the table (need
  this to add a comment)
• e.g. SELECT Child.Name, "is a reader!" FROM
  Child

33
Views

• a view is a temporary table based on a query
• views are stored in the database so they can be
  reused as desired (just like Access queries)
• create a view with the CREATE VIEW command and a
  SELECT statement
• e.g. CREATE VIEW Children AS SELECT FROM Child

34
Views Syntax

• CREATE VIEW ltview namegt AS
• ltSELECT statementgt
• a view consists of a view name and a SELECT query
  (any SELECT query)
• e.g.
• CREATE VIEW EarlyReaderBooks AS SELECT FROM
  Books WHERE Books.ReadingLevel "Early Readers"

35
Views Example

• CREATE VIEW PopularBooks AS
• SELECT Book.Title, Count(ChildAndBook.ReadAgain)
  AS CountOfReadAgain, Book.ReadingLevel
• FROM Child INNER JOIN (Book INNER JOIN
  ChildAndBook ON Book.ID ChildAndBook.bookid) ON
  Child.LibraryCard ChildAndBook.childid
• GROUP BY Book.Title, Book.ReadingLevel,
  ChildAndBook.ReadAgain
• HAVING ChildAndBook.ReadAgain1 AND
  CountOfReadAgain gt 1
• ORDER BY Count(ChildAndBook.ReadAgain) DESC

36
Views (cont.)

• the view PopularBooks returns a list of books
  that have been marked ReadAgain by the children,
  sorted by how often they have been so marked
• this query is quite complex, saving it as a view
  removes the need to write it again
• as a view, this query can be treated like a
  table and used in other queries

37
Views Exercises

• create a view listing the number of children who
  signed up at each branch
• create a view listing the number of books read by
  each child

38
DCL

• DCL or Data Control Language is a subset of SQL
• DCL allows database administrators to control
  access to individual features of the database
  (e.g. permissions to read or modify table)
• Commands
• GRANT
• REVOKE

39
Permissions

• GRANT and REVOKE control levels of permissions
• access to the mysql server
• access to particular databases
• access to particular tables in a database
• access to particular columns or stored procedures
• access can be granted or revoked to columns
  within a table

40
DCL Examples

• GRANT ALL ON summerreading TO ltusergt
• grants all privileges on summerreading, this user
  can select data, modify data, and delete tables
• GRANT SELECT ON summerreading TO ltusergt
• this user can select from any table in
  summerreading, but cannot modify anything

41
DCL Examples (cont.)

• REVOKE ALL ON summerreading FROM ltusergt
• this user can no longer access summerreading
• REVOKE DROP ON summerreading FROM ltusergt
• this user may still have read or modify
  privileges to the data but can no longer drop
  tables, views or indexes

42
Transactions TCL

• TCL or Transaction Control Language controls how
  the DBMS handles multiple statements
• a transaction is a set of SQL commands that are
  all run together
• the important point is that they must all succeed
  or the entire set will fail
• by default SQL commands are saved (committed)
  immediately after being run

43
Why Use Transactions?

• transactions ensure that data integrity is
  maintained
• e.g. if you were inserting a series of values
  into a table and the network went down, then only
  parts of the inserted data would have been
  committed
• transactions ensure that everything succeeds or
  fails as a unit

44
TCL Terminology

• Transaction a set of SQL commands run as a unit,
  they must all succeed or all fail
• Rollback return the database to its previous
  state, undo the SQL commands
• Commit save the database in its new state, save
  the results of the latest SQL
• Savepoint the point at which a new unit of the
  transaction begins (this allows you to break a
  transaction into smaller units)

45
TCL Commands

• START TRANSACTION
• marks the beginning of a transaction
• SAVEPOINT ltsavepoint namegt
• set a savepoint
• ROLLBACK
• undo all SQL commands back to whenever the
  transaction started (or back to the last
  savepoint)
• also ROLLBACK TO ltsavepoint namegt

46
TCL Commands

• COMMIT
• saves all changes made since the beginning of the
  transaction
• by default each individual statement is treated
  like a single transaction and automatically
  committed
• using transactions, DELETE and UPDATE (etc.) can
  be undone

47
Example Using Transactions

• START TRANSACTION
• INSERT INTO Child (Name, ReadingLevel,
  PlaceOfRegistration)
• VALUES ('George Taylor','Early Readers',4)
• INSERT INTO Child (Name, ReadingLevel,
  PlaceOfRegistration)
• VALUES ('Janey Taylor', 'Junior',4)
• COMMIT

48
Using Transactions

• note that the START TRANSACTION command is used
  to start the transaction
• COMMIT saves the data to the database
• if you were unhappy with the INSERTs, you could
  have chosen to ROLLBACK
• normally to remove an INSERTed row, you must
  delete