Download
slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
MySQL PowerPoint Presentation

MySQL

189 Views Download Presentation
Download Presentation

MySQL

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