Transcript Chapter 3 Introduction to relational databases and MySQL Part 2 Murach's PHP and MySQL, C3 © 2010, Mike Murach & Associates, Inc. Slide 1

Chapter 3
Introduction
to relational databases
and MySQL
Part 2
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 1
Test 2: Friday, Oct 18
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 2
More on SQL
 SQL is a HLL for working with databases
 SQL statements express WHAT we want to do to a
database, not HOW we will do it
 SQL is declarative rather than procedural
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 3
Review of SQL so far
 The Retrieve in CRUD is one with the SELECT in SQL
 The Create in CRUD is done with the INSERT in SQL
 The Update in CRUD is done with the UPDATE in SQL
 The Delete in CRUD is done with the DELETE in SQL
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 4
The syntax for the INSERT statement
INSERT INTO table-name [(column-list)]
VALUES (value-list)
A statement that adds one row to a table
INSERT INTO products
(categoryID, productCode, productName, listPrice)
VALUES
(1, 'tele', 'Fender Telecaster', 599.00)
A statement that uses the MySQL NOW function to
get the current date
INSERT INTO orders (customerID, orderDate)
VALUES (1, NOW())
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 5
The syntax for the UPDATE statement
UPDATE table-name
SET expression-1 [, expression-2] ...
WHERE selection-criteria
A statement that updates a column in one row
UPDATE products
SET productName =
'Ludwig 5-Piece Kit with Zildjian Cymbals'
WHERE productCode = 'ludwig'
A statement that updates multiple rows
UPDATE products
SET listPrice = 299
WHERE categoryID = 1
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 6
The syntax for the DELETE statement
DELETE FROM table-name
WHERE selection-criteria
A statement that deletes one row from a table
DELETE FROM products
WHERE productID = 1
A statement that deletes multiple rows
DELETE FROM products
WHERE listPrice > 200
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 7
The SQL statements so far belong to the DML part
of SQL
SQL has a DDL part with statements that allow the
creation of databases and tables
It also has a part that provides for providing
secure access to databases
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 8
Creating a user with limited privileges
on one table
GRANT SELECT
ON my_guitar_shop1.products
TO mgs_tester@localhost
IDENTIFIED BY 'pa55word'
Creating a user with limited privileges
on all tables
GRANT SELECT, INSERT, DELETE, UPDATE
ON my_guitar_shop1.*
TO mgs_user@localhost
IDENTIFIED BY 'pa55word'
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 9
Two ways of running SQL programs
 With a command line console
 With a GUI interface
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 10
Using MySQL via a command-line client
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 11
Using MySQL via phpMyAdmin a web-based GUI
client
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 12
Running a SQL script that creates a database
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 13
How to import and run a SQL script
1. Click the Import tab, go to the “File to Import” section, click the
Browse button, and select the file that contains the script.
2. Click the Go button. This runs the script that’s in the file.
The script for creating the databases for this book
\xampp\htdocs\book_apps\_create_db\create_db.sql
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 14
MySQL Workbench is another MySQL GUI
interface

Lets you create and edit diagrams.

Lets you define the tables, columns, and indexes for a database.

Lets you define the relationships between the tables in a database.

Lets you generate a diagram from a SQL creation script.

Lets you generate a SQL creation script from a diagram.
Murach's PHP and MySQL, C16
© 2010, Mike Murach & Associates, Inc.
Slide 15
The Home page for MySQL Workbench
Murach's PHP and MySQL, C16
© 2010, Mike Murach & Associates, Inc.
Slide 16
How to install MySQL Workbench
1. Go to the MySQL Workbench web site at:
http://wb.mysql.com/
2. Download the version for your system.
Run the installer or setup file and respond to the prompts.
Murach's PHP and MySQL, C3
© 2010, Mike Murach & Associates, Inc.
Slide 17