Girl Develop It! PHP/MySQL Session 2

Session 2- Intro MySQL

Transcript

1. Girl Develop It! PHP/ MySQL class Session 2: MySQL
2. What is MySQL? An engine for SQL SQL: Structured Query Language Relational Database System
3. Relational Databases Tables Columns- structure of table Data types, column names Rows- records in tables
4. What does the relational mean? You can have tables related to each other through the use of keys and indexes key- a column of a table that contains a unique value for each row primary: for this table (can NOT repeat) foreign: relates to another record in another table (can repeat) index- a column that where the values in the rows are kept in order and in an index for faster searching
5. Getting started Using LAMP/WAMP/MAMP, find out how to get to your PHPMyAdmin page Take a peek around
6. Structured Query Language (SQL) How you give the data base commands Keywords are generally capitalized, but depending on the engine/frontend you use, you don’t have to. Generally better safe than sorry. Several types of queries: CREATE, INSERT, SELECT, ALTER, DROP Once you get the basic patterns down, you can add in conditions/options
7. CREATE statements Create a database or a table Syntax: CREATE DATABASE {itemName}; For Tables: CREATE TABLE tableName( fieldName fieldType [options], field2Name fieldType [options], ); See example2-1.sql for database/table creation
8. INSERT Statements These insert records into tables Syntax: INSERT INTO tableName VALUES(col1Value, col2Value, col3Value...); Can also add to only certain columns for a row, ignoring the rest See example 2-2.sql for examples
9. SELECT Statements Select row(s) from a table Syntax: SELECT * from TableName WHERE col1=val1 AND col2 = val2 [OPTIONS] See example2-3.sql
10. WHERE clause Where a column equals a value Can use AND, OR logic, = or != Can use IS NULL or IS NOT NULL See example 2-4.sql
11. Selecting only certain columns Replace the * after SELECT with the names of the columns you would like to select Example: SELECT username, password from User... See example2-5.sql for examples
12. LIMIT option SELECT ...WHERE... LIMIT x, y; Selects y records, starting with x See example 2-6.sql for examples
13. ORDER BY option SELECT...WHERE...ORDER BY fieldName ASC/DESC Orders the results by the fieldName column either ASCending or DESCending See Example2-7.sql for examples
14. Joins Joins are select statements that select rows from more than one table, based on a foreign key relationship between the rows of data selected. Three types: left, right, inner To see exactly how these works, see example2-8.sql
15. Homework Design two tables that match something in real life and would logically form a join. Write a few queries that would make sense for these tables, including a join select.