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.

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
    ",
    "davey@engineyard.com",
    "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 = "davey@engineyard.com"
    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  =  "davey@engineyard.com";
    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: davey@engineyard.com
    Slides:
    http://daveyshafik.com/slides
    

    View Slide