1 / 30

Web Design:

Web Design:. Fall 2010 Mondays 7-9pm 200 Sutardja -Dai Hall. Basic to Advanced Techniques. Databases & SQL. Lecture Code:. Quiz Lecture Lab. Today’s Agenda. Announcements. Final Project Specs up this week. What is MySQL?. Client Side. Server Side.

glenna
Download Presentation

Web Design:

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. Web Design: Fall 2010 Mondays 7-9pm 200 Sutardja-Dai Hall Basic to Advanced Techniques Databases & SQL Lecture Code:

  2. Quiz Lecture Lab Today’s Agenda

  3. Announcements Final Project Specs up this week

  4. What is MySQL? Client Side Server Side Web Design:Basic to Advanced Techniques • Web Server • Serve Website • Send HTML, CSS, and JavaScript files • Send images • Interprets and executes PHP code • PHP code calls/talks to Database • Web Browser • HTTP Request (visit website) • Interpret and render received files • JavaScript Execution

  5. What is MySQL? MySQL is a brand of database software Relational database management system (RDMS) Named after original developer’s daughter: My Closely used with PHP, and partial reason for success Used by Google, Wikipedia, Facebook, YouTube…

  6. Other Systems Notice “SQL”. Pronounced “sequel”. MySQL PostgreSQL SQLite Microsoft SQL Server

  7. Where do Databases Fit In?

  8. Where do Databases Fit In? Server Client . Web Design:Basic to Advanced Techniques

  9. Web Server Communication • Open URL http://jonathanmui.aw-industries.com/mylife.php • Web Server receives request and determines it is a php file, so it will send processing to the PHP interpreter. • Interpreter looks up File System • File system returns the corresponding file • Interpreter looks up database • Database returns result set • PHP Interpreter does work on the data obtained, then translates all that into HTML • Web Server serves you the HTML 1 2 3 4 5 6 7 8 9 10 11 <?php print("Family & Friends"); $album_files = scandir("Pics"); for($index = 2; $album_files[$index]; $index++){?> <img src=<?php print("/Pics/".urlencode($album_files[$index]));?>> <?php } ?>

  10. Use Cases • Directory Services • User authentication • Banking • Reservations • Browser cache • Many, many things! Almost all web applications use some sort of database technology What about our Cal student information? Our grades?

  11. SQL • Structured Query Language • Databases are not just a repository of information • Can ask the database questions about the data • How many students are enrolled in the DeCal? • What is their average attendance rate? • How many have turned in all assignments? • Which student is doing the best in the course? • Does a user with this password exist? • Need to maintain the data • Create, Read, Update, Delete

  12. CRUD • Create • "INSERT INTO `products` (`name` ,`price`) VALUES (‘Tonka truck’, ‘13.00’)" • Read • "SELECT `index`, `name` FROM `products`" • Update • “UPDATE `products` SET `price` = 20.00 WHERE `name` = ‘Tonka truck’” • Delete • “DELETE FROM `products` WHERE `name` = ‘Tonka truck’”

  13. How is Data Stored in a Database? A unique “Primary Key” Integer Varchar Decimal Products

  14. How is Data Stored in a Database? • A: In Relational Tables • Each table has a name • Columns have labels • Each column stores a different type of data • An entry in a database appears as a ROW • Each column in the row has a value • All rows have a key – a unique identifier • Typically an integer

  15. Data Types Integer Double Float Varchar Longtext Boolean …

  16. Relationships Foreign Key Girlfriends Boyfriends Let’s model…relationships: Girlfriend and Boyfriend

  17. Some Terminology • Primary Key • A primary key is used to uniquely identify each row in a table. • It can either be part of the actual record itself , or it can be an artificial field (one that has nothing to do with the actual record). • A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key • Foreign Key • A field (or fields) that points to the primary key of another table. • The purpose of the foreign key is to ensure referential integrity of the data References: http://www.1keydata.com/sql/

  18. Boyfriend(s) Girlfriends Boyfriends 2 Portia seduces Billy…

  19. Girlfriend(s) Girlfriends Boyfriends Bob picks up the slack…

  20. Better Relationship Modeling Girlfriends Boyfriends Relationships Portia and Kensington Portia and Billy Jane and Bob Jessica and Bob

  21. Relationship Types Girlfriends Boyfriends Girlfriends Boyfriends Relationships “One to Many” “Many to Many”

  22. phpMyAdmin

  23. CRUD • Create • INSERT INTO <table> (<field>) VALUES (<value>) • Read • SELECT <fields> FROM <table(s)> WHERE <condition> • Update • UPDATE <table> SET <field> = <value> WHERE <condition> • Delete • DELETE FROM <table> WHERE <condition>

  24. SQL Examples Students How do we find the name of all students with an A? How do we delete Alex’s row? How do we edit Amber’s grade from an F to an A? How do we add Alex with an A? SELECT Name FROM Students WHERE Grade = A DELETE FROM Students WHERE S_ID = 555572 UPDATE Students SET Grade = A WHERE S_ID = 555568 INSERT INTO Students (Name, Grade) VALUES (‘Alex’, ‘A’)

  25. Index • These allow the SQL query to search the tables faster • Syntax • CREATE INDEX “<index_name>” ON “<table_name>” (column_name) • CREATE INDEX “StudentNames” ON “Students” (name)

  26. Join Allows us to create relationships on the fly. Simply selecting multiple tables SELECT * FROM <table 1, table 2,…> WHERE <condition> SELECT * FROM Girlfriends, Boyfriends WHERE Girlfriends.G_ID = Boyfriends.G_ID AND Girlfriends.G_ID = 1

  27. PHP and MySQL Print titles from all the entries in our blogs table

  28. PHP and MySQL Get contents of a blog with a requested title View.php?title=Octopi and Unicorns

  29. PHP and MySQL Insert new entry into blogs table

  30. PHP and MySQL

More Related