Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Introduction to Databases

Introduction to Databases

In this talk we will cover everything you need to get going with databases.

You learn about different types of databases, and when they should be used.

You will also learn how to CRUD — Create, Retrieve, Update and Delete data, database schema, and when to use indexes and joins.

Davey Shafik

June 29, 2013
Tweet

More Decks by Davey Shafik

Other Decks in Programming

Transcript

  1. Databases
    Getting Started with Data

    View Slide

  2. •Community Engineer at Engine Yard
    •Author of Zend PHP 5 Certification
    Study Guide, Sitepoints PHP
    Anthology: 101 Essential Tips, Tricks
    & Hacks & PHP Master: Write
    Cutting Edge Code
    •A contributor to Zend Framework,
    phpdoc, FRAPI and PHP internals
    •@dshafik
    Davey Shafik

    View Slide

  3. About These Slides

    View Slide

  4. About These Slides
    • Two slides per “slide”
    • Title Slide (for when I’m talking)
    • Details slide (for later)
    • Nobody likes it when you can read the slide just as
    well as the speaker can
    • I like slides that are useful
    4

    View Slide

  5. What is a Database?

    View Slide

  6. What is a Database?
    A database is an organized collection of data.
    The data is typically organized to model relevant
    aspects of reality, in a way that supports
    processes requiring this information.


    Source: Wikipedia
    (Emphasis Mine)

    View Slide

  7. Types of Database

    View Slide

  8. SQL (Relational)

    View Slide

  9. SQL (Relational)
    • Highly Structured Data
    • Using Tables, Columns and Rows
    • One or more relationships exist between datas
    • Constraints
    • Primary Keys (a unique row identifier)
    • Unique Keys (one or more columns that must have unique
    values, either individually, or as a group)
    • Foreign Keys (a column value that must be derived from a
    column value in another table)
    • Indexes
    • A lookup for one, or multiple columns aggregate data
    9

    View Slide

  10. NoSQL
    (Document/Key-Value/Graph)

    View Slide

  11. NoSQL (Document/Key-Value/Graph)
    • Sometimes called “Not Only SQL” because some
    NoSQL DBs have a SQL-like query language
    • Not always non-relational
    • Always unstructured
    • Intended to provide higher scalability and higher
    availability
    • Looser consistency models
    11

    View Slide

  12. Non-Relational
    Document Stores / Key-Value Stores

    View Slide

  13. NoSQL (Document/Key-Value/Graph)
    • NoSQL is non-relational
    • Document Stores
    • Centers around the concept of a document, and it’s related meta-data
    • Collections of documents
    • Hierarchies of documents
    • Examples: Couchbase Server, CouchDB, MongoDB, Amazon SimpleDB,
    Oracle NoSQL DB
    • Key-Value Stores
    • Data stored and accessible directly by a unique key
    • Examples: Memcache, MongoDB, Couchbase Server, Cassandra, Riak,
    Amazon DynamoDB, Redis, Oracle NoSQL DB
    13

    View Slide

  14. Relational
    Graph Databases

    View Slide

  15. NoSQL (Document/Key-Value/Graph)
    • NoSQL is relational (say what?!)
    • Graph Databases
    • All data is related to N other data
    • Relationships are in the data, not indexes
    • Examples: OQGraph for MySQL
    • Example Implementation: Facebook’s Graph API
    15

    View Slide

  16. Relational Concepts

    View Slide

  17. Relational Concepts
    • Schema
    • Tables
    • Indexes
    • Relationships
    • Stored Procedures
    • Triggers
    17

    View Slide

  18. A Note about MySQL

    View Slide

  19. A Note on MySQL
    • MySQL supports multiple drivers (called engines) for
    it’s tables.
    • These engine provide different features.
    • The two most common are InnoDB (default since
    MySQL 5.5) and MyISAM (previously the default).
    • InnoDB has far more features, and is recommended
    for almost all situations
    • We will assume InnoDB for all MySQL examples
    19

    View Slide

  20. Data Types

    View Slide

  21. Name What Notes
    int exact whole numbers Signed or unsigned
    decimal exact decimal numbers (fixed length) Signed or unsigned
    float
    approximate decimal number (variable
    length)
    Signed or unsigned
    char strings (fixed length) Max size: 255 bytes
    varchar strings (variable length) Max size: 255 bytes
    text strings (variable length) Max size: 255 bytes - 4GB
    blob binary strings (variable length) Max size: 255 Bytes - 4GB
    date dates (no time) Any date is valid
    datetime dates (with time) Any date/time is valid
    timestamp timestamp
    UNIX timestamp, must be >
    1/1/1970
    NULL Null values
    Does not equal anything, even
    NULL

    View Slide

  22. Exercise One
    Create a Users Table

    View Slide

  23. Exercise One: Users Table
    • Unique Identifier
    • Username
    • Password
    • Email Address
    • Name or First Name/Last Name
    23
    Consider:
    • Column Names
    • Column Types
    • Column Lengths

    View Slide

  24. Exercise One: Users Table
    24
    Users
    Users
    id int
    username varchar(20)
    password varchar(60)
    email varchar(150)
    first_name varchar(45)
    last_name varchar(55)

    View Slide

  25. Exercise One: Users Table (Schema)
    CREATE TABLE users (
    id INT,
    username VARCHAR(20),
    password VARCHAR(60),
    email VARCHAR(150),
    first_name VARCHAR(45),
    last_name VARCHAR(55)
    );

    View Slide

  26. SQL
    Structured Query Language

    View Slide

  27. SQL
    • INSERT — Create Data
    • UPDATE — Update Existing Data
    • SELECT — Fetch Data
    • DELETE — Delete Data
    27
    Four Main Queries

    View Slide

  28. CRUD
    Also Known As:

    View Slide

  29. CRUD
    29
    Create INSERT
    R etrieve SELECT
    Update UPDATE
    Delete DELETE

    View Slide

  30. Conditions

    View Slide

  31. Conditions
    • Used with:
    • SELECT
    • UDPATE
    • DELETE
    • JOINs
    • Preceded by the WHERE, ON, or USING keyword
    31

    View Slide

  32. Operators
    32
    Operator
    = Equality
    <>, != Inequality
    < Less Than
    <= Less Than or Equal To
    > Greater Than
    >= Greater Than or Equal To
    IS NULL NULL Equality
    IS NOT NULL NULL Inequality
    AND Boolean AND
    OR Boolean OR
    BETWEEN Range Equality

    View Slide

  33. INSERT

    View Slide

  34. INSERT
    INSERT INTO table name (
    list,
    of,
    columns
    ) VALUES (
    "list",
    "of",
    "values"
    );
    34

    View Slide

  35. INSERT
    INSERT INTO users (
    id,
    username,
    password,
    email,
    first_name,
    last_name
    ) VALUES (
    1,
    "dshafik",
    "$2y$10$Ol/KS4/Bhs5ENUh7OpIDL.Gs1SIWDG.rPaBkPAjjQ2UTITI60YDmG
    ",
    "[email protected]",
    "Davey",
    "Shafik"
    );
    35

    View Slide

  36. UPDATE

    View Slide

  37. UPDATE
    UPDATE
    table name
    SET
    column = "some",
    name = "value"
    WHERE
    some condition;

    View Slide

  38. WARNING:
    Don’t forget your conditions!
    Otherwise you update every row in the table!

    View Slide

  39. UPDATE
    UPDATE
    users
    SET
    username = "davey",
    email = "[email protected]"
    WHERE
    id = 1;

    View Slide

  40. SELECT

    View Slide

  41. SELECT
    SELECT
    list,
    of,
    columns
    FROM
    table
    WHERE
    column = "some"
    AND name = "value"
    OR other_column = "other value"
    ORDER BY some, columns
    LIMIT start, end;

    View Slide

  42. SELECT
    SELECT
     *
    FROM
     users
    WHERE
     username  =  "davey"
     AND  password  =  "$2y$10$Ol..."
    LIMIT  1;

    View Slide

  43. SELECT
    SELECT
    first_name, last_name, email
    FROM
    users
    ORDER BY first_name, last_name
    LIMIT 0, 10;

    View Slide

  44. SELECT
    SELECT
    first_name, last_name, email
    FROM
    users
    ORDER BY first_name, last_name
    LIMIT 10, 20;

    View Slide

  45. DELETE

    View Slide

  46. DELETE
    DELETE
    FROM
    table
    WHERE
    column = "some"
    AND name = "value"
    OR other_column = "other value"
    ORDER BY some, columns
    LIMIT number;

    View Slide

  47. DELETE
    DELETE FROM users;

    View Slide

  48. View Slide

  49. DELETE
    DELETE FROM
    users
    WHERE
    id = 1;

    View Slide

  50. Constraints

    View Slide

  51. Constraints: Users Table
    • IDs should be unique
    • Usernames should be unique
    • Passwords should not be unique
    • Email Address should be unique
    • First Name should not be unique
    • Last Name should not be unique
    • All column should not be NULL
    51

    View Slide

  52. Constraints: Users Table
    52
    Users
    Users Constraints
    id int unique, not null
    username varchar(20) unique, not null
    password varchar(60) not null
    email varchar(150) unique, not null
    first_name varchar(45) not null
    last_name varchar(55) not null

    View Slide

  53. Constraints: Users Table Schema
    CREATE TABLE users (
    id INT NOT NULL,
    username VARCHAR(20) NOT NULL,
    password VARCHAR(60) NOT NULL,
    email VARCHAR(150) NOT NULL,
    first_name VARCHAR(45) NOT NULL,
    last_name VARCHAR(55) NOT NULL,
    UNIQUE INDEX id_UNIQUE (id),
    UNIQUE INDEX username_UNIQUE (username),
    UNIQUE INDEX email_UNIQUE (email)
    );

    View Slide

  54. Features

    View Slide

  55. Name What Notes
    Auto Increment
    (auto_increment)
    Automatically
    inserts the (last
    row)+1 value
    when inserting
    • Column must be
    set as PRIMARY
    KEY
    • One Per Table
    Signed/Unsigned
    Sets Numeric columns
    to signed (may be
    positive or negative)
    or unsigned (must be
    positive)
    Unsigned numbers
    start at 0 and allow
    for much larger
    numbers.
    Zero Fill (zerofill)
    Left Pads numeric
    values to the column
    size
    Only applied on
    retrieval (i.e. it’s
    not stored this
    way)

    View Slide

  56. Features: Users Table
    •ID should be auto increment
    •ID should be the Primary Key
    •ID should be unsigned
    56

    View Slide

  57. Features: Users Table Schema
    CREATE TABLE users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    password VARCHAR(60) NOT NULL,
    email VARCHAR(150) NOT NULL,
    first_name VARCHAR(45) NOT NULL,
    last_name VARCHAR(55) NOT NULL,
    UNIQUE INDEX username_UNIQUE (username),
    UNIQUE INDEX email_UNIQUE (email),
    PRIMARY KEY (id)
    );

    View Slide

  58. Indexes

    View Slide

  59. Indexes
    59
    Name Constraints Notes
    Index None May have NULL values
    Unique Unique May have NULL values
    Primary Key Unique
    Must NOT have NULL values. May
    auto_increment. There can only be
    one.
    Foreign Key
    Must match data
    in linked table
    May have NULL values

    View Slide

  60. INDEX, UNIQUE,
    & PRIMARY KEY

    View Slide

  61. INDEX, UNIQUE, & PRIMARY KEY
    • Can be added during table creation:
    • CREATE TABLE foo (
    column_name TYPE,
    INDEX name (column, list),
    UNIQUE name (column, list),
    PRIMARY KEY (column)
    );
    • Index/Unique can have multiple columns
    • Can be added after table creation:
    • CREATE INDEX name ON table_name (column, list);
    ALTER TABLE table name ADD INDEX (column, list);
    CREATE UNIQUE INDEX name ON table (column, list);
    ALTER TABLE table name ADD UNIQUE (column, list);
    ALTER TABLE table name ADD PRIMARY KEY (column);
    • Must be added with caution!
    61

    View Slide

  62. Foreign Keys

    View Slide

  63. Foreign Keys
    • Used to create inter-table relationships
    • Value must be in the foreign table or NULL
    • Can update when the foreign table updates
    • Can delete when the foreign table deletes
    • Can be set to NULL when the foreign table deletes
    63

    View Slide

  64. Exercise Two
    Profiles & Foreign Keys

    View Slide

  65. Exercise Two: Profiles Table
    • Unique Identifier
    • Short Introductory Summary
    • Full Biography
    • Location
    65
    Consider:
    • Must link to the Users table
    • One Profile per User

    View Slide

  66. Exercise Two: Profiles Table
    Profiles
    Profiles Constraints
    id int UNSIGNED
    primary key, auto_increment,
    not null
    users_id int UNSIGNED
    unique, foreign key -> users.id,
    not null
    summary varchar(200) none
    bio TEXT none
    location varchar(100) none

    View Slide

  67. Exercise Two: Profiles Table Schema
    CREATE TABLE profiles (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    users_id INT UNSIGNED NOT NULL,
    summary VARCHAR(200) NOT NULL,
    bio TEXT NULL,
    location VARCHAR(150) NULL,
    PRIMARY KEY (id),
    UNIQUE INDEX users_id_UNIQUE (users_id),
    CONSTRAINT
    FOREIGN KEY (users_id) REFERENCES users (id)
    ON DELETE CASCADE
    );

    View Slide

  68. INSERT
    INSERT INTO profiles (
    users_id,
    summary,
    bio,
    location
    ) VALUES (
    1,
    "Community Engineer at Engine Yard",
    NULL,
    "Florida, USA"
    );

    View Slide

  69. Indexing Your Data

    View Slide

  70. View Slide

  71. Indexing Your Data
    • Indexes make writes slower, and reads (much faster).
    The more indexes, the slower your writes.
    • The creation of indexes should be determined by the
    SELECT queries being run upon the data. Nothing else.
    • For example, if you run a query that SELECTs using two WHERE
    criteria with an AND condition, that is probably a good
    combination index.
    • MySQL can only use one index [per table] at a time and
    will (generally) pick the best option based on the query
    • Indexes cannot be used with LIKE if starting with a
    wildcard (e.g. %foo%)
    71

    View Slide

  72. Exercise Three
    Indexes

    View Slide

  73. Exercise Three: Indexes
    • Add Indexes to the users table
    • Remember we already have a PRIMARY KEY and UNIQUE indexes
    • Example queries we will perform against it:
    73
    SELECT  *  FROM
     users
    WHERE
     username  =  "davey"
     AND  password  =  "$2y$10$Ol...";
    SELECT  *  FROM  users  
    WHERE  email  =  "[email protected]";
    SELECT  *  FROM  users  
       WHERE  first_name  LIKE  "%Dave%";

    View Slide

  74. Exercise Three: Indexes
    74
    Users
    Users
    Key Type
    id PRIMARY KEY
    username UNIQUE
    username, password UNIQUE
    email UNIQUE

    View Slide

  75. JOINs
    Connecting Tables

    View Slide

  76. JOINs
    • Used to JOIN multiple tables
    • INNER JOIN
    • LEFT or RIGHT OUTER JOIN
    76

    View Slide

  77. INNER JOIN
    Get the intersection of two tables
    Users Profiles
    Users with Profiles

    View Slide

  78. OUTER JOIN
    For when one side or the other doesn’t match

    View Slide

  79. LEFT OUTER JOIN
    For when one side or the other doesn’t match
    Users
    Users with Profiles
    Profiles

    View Slide

  80. RIGHT OUTER JOIN
    Users
    Profiles with Users
    Profiles

    View Slide

  81. SELECT... INNER JOIN
    SELECT * FROM
    users
    INNER JOIN profiles
    ON (
    profiles.user_id = users.id
    )
    WHERE
    profiles.location LIKE '%Dallas%'
    ORDER BY
    users.first_name,
    users.last_name;

    View Slide

  82. SELECT... LEFT OUTER JOIN
    SELECT * FROM
    users
    LEFT OUTER JOIN profiles
    ON (
    profiles.user_id = users.id
    )
    WHERE
    users.id = 1;

    View Slide

  83. SELECT... RIGHT OUTER JOIN
    SELECT * FROM
    users
    RIGHT OUTER JOIN profiles
    ON (
    profiles.user_id = users.id
    )
    WHERE
    profiles.bio LIKE '%PHP%';

    View Slide

  84. Databases and PHP

    View Slide

  85. Connecting to Databases
    Using PDO

    View Slide

  86. Connecting to Databases
    • PDO
    • MySQL
    • PostgreSQL
    • MSSQL
    • Oracle
    • SQLite
    • ODBC and DB2
    • Firebird
    • DSN — Data Source Name
    • Driver Name
    • Hostname & Port
    • or
    • Unix Socket
    • Username 86

    View Slide

  87. Connecting to MySQL
    $pdo = new PDO(
    "mysql:dbname=db;host=localhost",
    "user",
    "pass"
    );
    ?>

    View Slide

  88. Querying Data

    View Slide

  89. Executing Queries
    $pdo = new PDO(...);
    $query = $pdo->prepare(
    "SELECT * FROM user WHERE id = :id"
    );
    $conditions = array(
    ':id' => 1
    );
    $result = $query->execute($conditions);
    ?>

    View Slide

  90. Handling Results

    View Slide

  91. Handling Results
    $result = $query->execute($conditions);
    if ($result) {
       echo  "Results  Found:  "  .$query-­‐>rowCount();
    while ($row = $query->fetch()) {
    echo ""
    .$row['first_name']. ' '
    . $row['last_name'] .'';
    }
    }

    View Slide

  92. Handling Results as Objects
    $result = $query->execute($conditions);
    if ($result) {
    echo  "Results  Found:  "  .$query-­‐>rowCount();
    while ($row = $query->fetchObject()) {
    echo ""
    .$row->first_name. ' '
    . $row->last_name .'';
    }
    }

    View Slide

  93. Handling Results as Custom Objects
    class User {
    function getName() {
    return $this->first_name
    . ' ' . $this->last_name;
    }
    }
    if ($result) {
    echo  "Results  Found:  "  .$query-­‐>rowCount();
    while ($row = $query->fetchObject("User")) {
    echo ""
    .$row->getName().
    '';
    }
    }

    View Slide

  94. Thank You
    Feedback:
    http://joind.in/8703
    Twitter: @dshafik
    Email: [email protected]
    Slides:
    http://daveyshafik.com/slides

    View Slide