1 / 90

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:

theo
Download Presentation

MySQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. MySQL • Using Databases • with PHP or Perl Scripts:

  2. 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

  3. 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

  4. 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

  5. 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.

  6. 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

  7. Comparison: file vs Database

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

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

  10. Relationship in Sample Database Faculty

  11. 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;

  12. 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;

  13. 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‘;

  14. 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;

  15. 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

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

  17. 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)

  18. 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’;

  19. 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;

  20. 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;

  21. 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;

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

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

  24. 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);

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

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

  27. PHP PHP DB functions

  28. 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>

  29. 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")

  30. <?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>

  31. Successful connection

  32. Username or password does not match

  33. 2. List DB- From MySQL Console mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | books | | cs346 | | mailinglist | | mysql | | products | +--------------------+ 6 rows in set (0.02 sec) mysql>

  34. 2. PHP listing databases

  35. 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

  36. <?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>"; ?>

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

  38. 3. Show tables - from MySQL Console mysql> use books; Database changed mysql> show tables; +-----------------+ | Tables_in_books | +-----------------+ | authorisbn | | authors | | titles | +-----------------+ 3 rows in set (0.13 sec)

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

  40. 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

  41. 4. Create database – from MySQL Console mysql> create database testDB; Query OK, 1 row affected (0.19 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | books | | cs346 | | mailinglist | | mysql | | products | | testdb | +--------------------+ 7 rows in set (0.01 sec) mysql>

  42. 5. Drop database – from MySQL console mysql> drop database testdb; Query OK, 0 rows affected (0.27 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | books | | cs346 | | mailinglist | | mysql | | products | +--------------------+ 6 rows in set (0.00 sec) mysql>

  43. 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());

  44. What happened?

  45. 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)

  46. 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>

  47. Now test again

More Related