MySQL

MySQL • Using Databases • with PHP or Perl Scripts:

Objectives • Advantages of using databases to store Web data • How to prepare a MySQL database for use with Perl • How to store, retrieve, and update data in a MySQL database

Data Driven Web Pages • Approach 1 - Global variables • Disadvantage: • Need to change program if data change • Programs downstream need to change too • Approach 2 - File • Approach 3 - Database

To store data between executions of scripts: • You may use files: • store customer data • Login and password • store page hit counts • remember end-user preferences • store product inventory • Consumer survey • Simple data structure

What is a database? • A set of data organized into one or more computer files. • Text file: A type of database • Using files for product inventory • A file, password.txt, to keep passwords • Generally the term is reserved for more formal database systems like Access, Oracle or MySQL.

Advantages of Scripts Accessing Databases Over Scripts Using Files • Faster access • DB performs the search (random access) vs. Sequential search - a line at a time in file • Better concurrent access • Easier changes to data and scripts • Need not know data file format • Increased security • Perl scripts using files require access permissions set for all • DB uses separate ID and password to access WHH

Comparison: file vs Database

Relational Database? • Relational databases store data in tables (usually more than one) with defined relationships between the tables.

Relational Database Model • Database: collection of tables • Table: collection of similar records • Record: collection of values: Faculty table

Relationship in Sample Database Faculty

Structured Query Language (SQL) • Language for extracting/modifying data • Every table has a name • Every field/column has a name SHOW tables; SELECT * FROM Faculty;

SQL Select Statement • Queries a database (read-only access) • Returns a set of records • What are first names of faculty? SELECT Firstname FROM Faculty; • What classrooms are courses in? SELECT Num, Bdg, Room FROM Courses;

where clause: focus the query • When does comp sci 310 meet? SELECT Time FROM Courses WHERE Num = 310; • What is Huen's teaching schedule? SELECT Num, Time FROM Courses WHERE Instructor = ‘Huen‘;

Boolean Operators in WHERE clause • What upper level courses is Huen teaching? SELECT Num FROM Courses WHERE Instructor = ‘Huen' AND Num >= 300; • Note: Single quote for character strings. Hence Instructor = ‘O’’Hare’ • What third-floor Halsey classrooms does the CS department teach in? SELECT Room FROM Courses WHERE Bdg='HS' AND Room >= 300 AND Room < 400;

Queries over Multiple Tables • If two tables appear in FROM clause, DBMS generates cartesian product • Use WHERE clause to "join" tables • What are instructors’ first names for each course? SELECT Num, Firstname FROM Courses, Faculty WHERE Courses.Instructor = Faculty.Lastname

Duplicate Records • Do not remove duplicates (default) SELECT ALL Instructor FROM Courses; SELECT Instructor FROM Courses; • Explicitly remove duplicates SELECT DISTINCT Instructor FROM Courses;

Tuple Variables • Who shares classrooms with Huen? • Need to use the courses table twice. SELECT DISTINCT Instructor FROM Courses, Courses WHERE Bdg = Bdg AND Room = Room AND Instructor = ‘Huen’; • Help, I'm confused! (…and so is the DBMS)

Tuple Variables • We must differentiate between two uses of the same table SELECT DISTINCT x.instructor FROM Courses x, Courses y WHERE x.Bdg = y.Bdg AND x.Room = y.Room AND y.Instructor = ‘Huen’;

Counting • Count the number of courses SELECT COUNT(*) FROM Courses • Count the number of courses in Halsey SELECT COUNT(*) FROM Courses WHERE Bdg = ‘HS’; • Count the number of rooms used SELECT COUNT(DISTINCT Room) FROM Courses;

GROUP BY • Criteria for grouping records • How many times is each room used? SELECT Room, COUNT(*) FROM Courses GROUP BY Room; • How many courses is each room used for? SELECT Room, COUNT( DISTINCT Num) FROM Courses GROUP BY Room;

ORDER BY • Criteria for ordering (sorting) records • Get the faculty members in descending order of last name SELECT LastName, FirstName FROM Faculty ORDER BY LastName DESC; • Multiple fields can be sorted too SELECT LastName, FirstName FROM Faculty ORDER BY LastName, FirstName;

Other Aggregate Functions • MAX • MIN • SUM All used similar to COUNT • What is the lowest numbered course? SELECT MIN(Num) FROM Courses;

Create a table CREATE TABLE Faculty( Lastname VARCHAR(10) NOT NULL PRIMARY KEY, Firstname VARCHAR(12), Bdg VARCHAR(10), room INT );

Insert a row INSERT INTO Faculty VALUES (‘Huen’, ‘Wing’, ‘HS’, 221); • Better practice: More verbose and specific to avoid data mismatch INSERT INTO Faculty (FirstName, LastName, Bdg, Room) VALUES (‘Wing’, ‘Huen’, ‘HS’, 221);

UPDATING A RECORD UPDATE Tablename SETfieldName1 = value1, …, fieldNameN = valueN WHERE criteria; UPDATE Faculty SET Room = 221 WHERE LastName = ‘Huen’ AND FirstName = ‘Wing’;

DELETE FROM statement • DELETE FROM TableName WHERE criteria DELETE FROM Faculty WHERE LastName = ‘Wing’ AND FirstName = ‘Huen’

PHP PHP DB functions

1. From MySQL Console Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.1.36-community-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

1. PHP to connect to MySQL • MySQL must be running • Need username and password • username = "cs346“ • password = "cs346_password“ • SYNTAX: mysql_connect(“host", “username", “password") • mysql_connect("localhost", "cs346", "cs346_password")

<?php $connection = mysql_connect("localhost", "cs346", "cs346_password") or die(mysql_error()); if ($connection) { $msg = "Connection to MySQL successful!"; } ?> <html> <head> <title>MySQL Connection</title> </head> <body> <?php echo "$msg"; ?> </body> </html>

Successful connection

Username or password does not match

2. PHP listing databases

Some useful functions • mysql_list_dbs() - used to list the databases on a MySQL server • mysql_num_rows() - returns the number of rows in a result set • mysql_tablename() - Though the name implies tablename only, it extracts names of tables, or databases from a result set

<?php $connection = @mysql_connect("localhost", "cs346", "cs346_password") or die(mysql_error()); $dbs = @mysql_list_dbs($connection)or die(mysql_error()); $db_list ="<ul>"; $i =0; while ($i < mysql_num_rows($dbs)){ $db_names[$i] = mysql_tablename($dbs,$i); /* mysql_tablename(list_as_table, row_index) */ $db_list .= "<li>$db_names[$i]</li>"; // concatentation $i++; } $db_list .="</ul>"; ?>

<html> <head> <title>MySQL Databases</title> </head> <body> <p><strong>Databases on localhost</strong>:</p> <?php echo "$db_list"; ?> </body> </html>

mysql> use cs346; Database changed mysql> show tables; +-----------------+ | Tables_in_cs346 | +-----------------+ | movies | +-----------------+ 1 row in set (0.28 sec) mysql>

3. List tables - PHP • MySQL Console: • use <database_name>; • Show tables; • One DB at a time • PHP: • mysql_list_tables function • Use nested while loops • See 11-2db_listtables.php

4. PHP to create DB • $sql_string = “create database testDB2”; • $connection = @mysql_connect("localhost",“username", “password") or die(mysql_error()); • mysql_query($sql_string,$connection) or die(mysql_error());

What happened?

Perhaps cs346 has no permission to create mysql> select user, select_priv, insert_priv, create_priv from user; +-------+-------------+-------------+-------------+ | user | select_priv | insert_priv | create_priv | +-------+-------------+-------------+-------------+ | root | Y | Y | Y | | root | Y | Y | Y | | huen | Y | Y | Y | | cs346 | Y | Y | N | +-------+-------------+-------------+-------------+ 4 rows in set (0.00 sec)

Admin needs to grant permissions mysql> GRANT CREATE on *.* TO 'cs346'@localhost; Query OK, 0 rows affected (0.00 sec) mysql> select user, select_priv, insert_priv, create_priv from user; +-------+-------------+-------------+-------------+ | user | select_priv | insert_priv | create_priv | +-------+-------------+-------------+-------------+ | root | Y | Y | Y | | root | Y | Y | Y | | huen | Y | Y | Y | | cs346 | Y | Y | Y | +-------+-------------+-------------+-------------+ 4 rows in set (0.00 sec) mysql>

Now test again

MySQL

MySQL

Presentation Transcript

MySQL

mysql

MySQL

MySQL

MySQL

MySQL

MySQL

MySQL

MySQL