3 - MySQL Introduction

A general introduction to MySQL and how we can use it.

Transcript

1. PHP101 A concise course in PHP web development.
2. Course Overview 1. PHP Overview & Basics 2. Functions 3. Loops 4. MySQL and data-rich websites 5. Security and Good Practice 6. Working with JSON, XML and APIs 7. Object-Oriented PHP Development 8. Conclusion
3. Introduction to MySQL • SQL is an acronym of Structured Query Language. • MySQL is an open-source implementation of SQL. • Allows us to build and execute queries on a database. • Useful for building data-rich and persistent applications.
4. Basic SQL Terms • Database:
 A collection of tables which may, or may not relate to one another in some way. • Table:
 A storage mechanism for holding structured data, containing fields that do relate to one another. • Field:
 A single piece of data relating to each record in a table. Can be used to store multiple data types. • Record:
 A single entry in a table, containing data stored in the record’s fields.
5. Creating Databases in MySQL • The easiest way to create a database, and its respective tables is using a GUI or web interface, such as phpMyAdmin. • phpMyAdmin allows us to build and define our tables using an interface similar to Microsoft Access. • When we create a database, we must also create a user account with specific privileges on the database. • Once we have created a database, we then need to create table which exists within it and actually store our data.
6. Creating Tables in MySQL • Functions are affected by variable scope. • Things functions CAN access: • Other functions • Function parameters • Variables defined as global   • Things functions CANNOT access: • Variables defined outside of the function (unless we declare them as global).
7. Creating Tables in MySQL • Tables can have an unlimited number of fields, and MySQL will easily handle tables with > 10,000,000 records if the table is well designed. • When creating our tables, we need to think about redundant data and data duplication. • A well designed database will have minimal data duplication, because this causes more space and slower response times with queries.
8. Table Relationships • In database design, we have several relationship patterns which we need to bear in mind. • One-to-One Relationship:
 One record in a single table relates to a single record in another table. • One-to-Many Relationship:
 One record in a single table can relate to multiple records in another table. • Many-to-Many Relationship:
 Multiple records in one table relate to multiple records in another table.
9. • In a well-designed database, we should 
 have no many-to-many relationships. • Use a link table instead
10. MySQL Field Datatypes • MySQL has numerous data types we can use: • Numerical Types:
 tinyint - integer between -128 and 127
 smallint - integer between -32,768 and 32,767
 mediumint - integer between -8,388,608 and 8,388,607
 int - integer -/+2.147E+9
 bigint - integer -/+9.223E+18
 float - number with <= 24 decimal places
 double - number with 25 - 53 decimal places
 decimal - same as float / double, but stored as a string!
 bit - binary integer
11. MySQL Field Datatypes • Text Types:
 char - string with maximum char length of 255
 varchar - string with maximum char length of 65,535
 tinytext - string with 0-255 characters
 text - string with 0-65,535 characters
 mediumtext - string with 0-16,777,215 characters
 longtext - string with 0-4,294,967,295 characters
12. MySQL Field Datatypes • Date / Time Types:
 date - date in format YYYY-­‐MM-­‐DD
 time - time in format HH:MM:SS
 datetime - time and date in format YYYY-­‐MM-­‐DD  HH:MM:SS
 timestamp - integer of seconds since Unix epoch
 year - 4-digit year in format YYYY
13. Primary and Foreign Keys • Primary keys offer an easy way to identify records in a table. • Foreign keys allow us to identify relating rows in different tables. • Primary keys must be unique. • Primary keys should generally be integers. • We can define Primary Keys as auto-incrementing fields which means we don’t need to specify them when inserting data.
14. Indexes • Indexes make searching and SELECT queries faster • Essentially, creates a lookup (or index) of the table • By default, Primary Keys are always indexes • We can add more indexes, and should add them to fields which we will search regularly.
15. Query Types • SELECT
 Will retrieve the specified fields from the table • UPDATE
 Will update the specified fields with the given values in the table • INSERT
 Will add new records to the specified table • DELETE
 Will delete the matched records from the specified table.
16. Using SELECT Select every field of every
 record in users:
 
 SELECT  *  FROM  `users`   Select only some field of a 
 specific record:
 
 SELECT  `firstname`,`email`  
 FROM  `users`
 WHERE  `id`  =  1 users
17. Using UPDATE Update a single field:
 
 UPDATE  `users`
 SET  `email`  =  'foo@bar.com'
 WHERE  `id`  =  1 users
18. Using INSERT Insert a single record:
 
 INSERT  INTO  `users`
 (`firstname`,`lastname`,`email`)
 VALUES
 (‘Ben’,’Major','ben@foo.com') users
19. Using DELETE Delete a single record:
 
 DELETE  FROM  `users
 WHERE  `id`  =  1 users