MySQL, SQL and phpMyAdmin

Homework

1. SQL Syntax
   • Read: SQL Syntax from w3schools (and the world ends…)
   • Reference:
     • SQL INSERT INTO Statement
     • SQL SELECT Statement
     • SQL WHERE Clause
     • SQL ORDER BY Keyword
     • SQL UPDATE Statement
     • SQL DELETE Statement
     • SQL INNER JOINS
2. Relational Databases
   • Read: Summary - Relational Database Model
   • Read: MariaDB vs MySQL
3. phpMyAdmin
   • Read: How to Manage Databases with phpMyAdmin
   • Read: How to Create and Populate Tables
4. MySQL with PHP and phpMyAdmin
   • Watch: PHP Tutorial #23 - MySQL Introduction by Net Ninja
   • Watch: PHP Tutorial #24 - Setting Up a MySQL Database
   • Watch: PHP Tutorial #25 - Connecting to a Database
   • Watch: PHP Tutorial #26 - Getting Data From a Database
   • Watch: PHP Tutorial #27 - Rendering Data to the Browser
   • Watch: #31 - Getting a Single Record
   • Reference:
     • mysqli_query
     • mysqli

---

Morning reflection

• Initial comments on 262 Assignment 4
  • frontend vs backend javascript
  • HTML vs JSON endpoints
  • simplifying routes

Housekeeping

• CPNT 200 Assignment breakdown

---

1. Relational Data Models

Learning Objectives

• Define the features of a relational data model.
• Understand how to read and diagram a data model using and Entity Relationship Diagram (ERD).
• Demonstrate common relationships found in relational data models:
  • One-to-many
  • Many-to-many
• Define normalization
• Summarize the similarities and differences between MySQL and MariaDB.

Materials

• Relational Terminology
• ERD Diagrams
• ERD Symbols
• aviano-db source repo

Key Takeaways

• Naming convensions for database tables and columns:
  • lowercase
  • spaces replaced with underscores
  • no numbers
  • valid, descriptive names no longer than 64 characters

---

2. phpMyAdmin Tour

Learning Objectives

• Perform common operations using the phpMyAdmin control panel:
  • Create a database.
  • Define a table.
  • Insert data into a table.
  • Perform SQL queries against a database.

Materials

• Activity: Create an animals database

Key Takeaways

• Changing the structure of a database can be difficult after you insert data. Getting it right the first (or second) time can be difficult and the reason NoSQL databases are popular.
• It’s important to not forget your constraints. A key concept of relational data models is maintaining data integrity.

---

3. SQL Queries

Learning Objectives

• Understand the syntax of common SQL queries.
• Perform common CRUD operations on a database using SQL.
  • Create a record with INSERT.
  • Retrieve a record with SELECT.
    • Filter results with WHERE conditions.
    • Sort results with an ORDER clause.
  • Update a record with UPDATE.
    • Target records with WHERE conditions.
  • Combine the results from two tables with INNER JOIN.
• Define a mysql function.

Terminology

Materials

• SQL Syntax Basics
• 18 Useful SQL Functions

Key Takeaways

• Comparisons use =, not == or ===.
• Inserting data using phpMyAdmin can be a pain. You will often create an import script instead. This can be done with your language of choice.
• We rarely delete records in industry. Instead, there will usually be an active field (or similar) that is set to yes or no.

---

Open lab-time

SQL Queries

• Find:
  • All customers over the age of 30.
  • The id of Kass Humphris.
  • The number of vehicles in the database. Hint: there’s a COUNT() function.
• Activity: Create an animals database

---

Dailies

• Submit today’s Codepen/repo/gist to the Dailies section (in Assessments) in Brightspace.