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

More Decks by Davey Shafik

Other Decks in Programming


  1. Databases Getting Started with Data

  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
  3. About These Slides

  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
  5. What is a Database?

  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)
  7. Types of Database

  8. SQL (Relational)

  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
  10. NoSQL (Document/Key-Value/Graph)

  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
  12. Non-Relational Document Stores / Key-Value Stores

  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
  14. Relational Graph Databases

  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
  16. Relational Concepts

  17. Relational Concepts • Schema • Tables • Indexes • Relationships

    • Stored Procedures • Triggers 17
  18. A Note about MySQL

  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
  20. Data Types

  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
  22. Exercise One Create a Users Table

  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
  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)
  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) );
  26. SQL Structured Query Language

  27. SQL • INSERT — Create Data • UPDATE — Update

    Existing Data • SELECT — Fetch Data • DELETE — Delete Data 27 Four Main Queries
  28. CRUD Also Known As:

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

  30. Conditions

  31. Conditions • Used with: • SELECT • UDPATE • DELETE

    • JOINs • Preceded by the WHERE, ON, or USING keyword 31
  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
  33. INSERT

  34. INSERT INSERT INTO table name ( list, of, columns )

    VALUES ( "list", "of", "values" ); 34
  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
  36. UPDATE

  37. UPDATE UPDATE table name SET column = "some", name =

    "value" WHERE some condition;
  38. WARNING: Don’t forget your conditions! Otherwise you update every row

    in the table!
  39. UPDATE UPDATE users SET username = "davey", email = "davey@engineyard.com"

    WHERE id = 1;
  40. SELECT

  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;
  42. SELECT SELECT  * FROM  users WHERE  username  =  "davey"  AND

     password  =  "$2y$10$Ol..." LIMIT  1;
  43. SELECT SELECT first_name, last_name, email FROM users ORDER BY first_name,

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

    last_name LIMIT 10, 20;
  45. DELETE

  46. DELETE DELETE FROM table WHERE column = "some" AND name

    = "value" OR other_column = "other value" ORDER BY some, columns LIMIT number;

  48. None
  49. DELETE DELETE FROM users WHERE id = 1;

  50. Constraints

  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
  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
  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) );
  54. Features

  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)
  56. Features: Users Table •ID should be auto increment •ID should

    be the Primary Key •ID should be unsigned 56
  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) );
  58. Indexes

  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

  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
  62. Foreign Keys

  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
  64. Exercise Two Profiles & Foreign Keys

  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
  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
  67. Exercise Two: Profiles Table Schema CREATE TABLE profiles ( id

  68. INSERT INSERT INTO profiles ( users_id, summary, bio, location )

    VALUES ( 1, "Community Engineer at Engine Yard", NULL, "Florida, USA" );
  69. Indexing Your Data

  70. None
  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
  72. Exercise Three Indexes

  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%";
  74. Exercise Three: Indexes 74 Users Users Key Type id PRIMARY

    KEY username UNIQUE username, password UNIQUE email UNIQUE
  75. JOINs Connecting Tables

  76. JOINs • Used to JOIN multiple tables • INNER JOIN

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

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

  79. LEFT OUTER JOIN For when one side or the other

    doesn’t match Users Users with Profiles Profiles
  80. RIGHT OUTER JOIN Users Profiles with Users Profiles


    ON ( profiles.user_id = users.id ) WHERE profiles.location LIKE '%Dallas%' ORDER BY users.first_name, users.last_name;

    JOIN profiles ON ( profiles.user_id = users.id ) WHERE users.id = 1;

    JOIN profiles ON ( profiles.user_id = users.id ) WHERE profiles.bio LIKE '%PHP%';
  84. Databases and PHP

  85. Connecting to Databases Using PDO

  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
  87. Connecting to MySQL <?php $pdo = new PDO( "mysql:dbname=db;host=localhost", "user",

    "pass" ); ?>
  88. Querying Data

  89. Executing Queries <?php $pdo = new PDO(...); $query = $pdo->prepare(

    "SELECT * FROM user WHERE id = :id" ); $conditions = array( ':id' => 1 ); $result = $query->execute($conditions); ?>
  90. Handling Results

  91. Handling Results <?php $result = $query->execute($conditions); if ($result) {  

     echo  "Results  Found:  "  .$query-­‐>rowCount(); while ($row = $query->fetch()) { echo "<a href='/edit/" .$row['id']. "'>" .$row['first_name']. ' ' . $row['last_name'] .'</a>'; } }
  92. Handling Results as Objects <?php $result = $query->execute($conditions); if ($result)

    { echo  "Results  Found:  "  .$query-­‐>rowCount(); while ($row = $query->fetchObject()) { echo "<a href='/edit/" .$row->id. "'>" .$row->first_name. ' ' . $row->last_name .'</a>'; } }
  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 "<a href='/edit/" .$row->id. "'>" .$row->getName(). '</a>'; } }
  94. Thank You Feedback: http://joind.in/8703 Twitter: @dshafik Email: davey@engineyard.com Slides: http://daveyshafik.com/slides