LIS 558: Database Management Systems - PowerPoint PPT Presentation

Loading...

PPT – LIS 558: Database Management Systems PowerPoint presentation | free to view - id: 905fa-NWEzY



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

LIS 558: Database Management Systems

Description:

use a secure shell client to create a text or graphical connection to a Unix server ... this command does not create tables, this must be done separately ... – PowerPoint PPT presentation

Number of Views:69
Avg rating:3.0/5.0
Slides: 49
Provided by: publi2
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: 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
About PowerShow.com