sql.yml

--- 
sql: |-
  Select
  
    SELECT first_name, last_name, state, active
    FROM users
    
    +------------+-----------+-------+--------+
    | first_name | last_name | state | active |
    +------------+-----------+-------+--------+
    | Homer      | Simpson   | WI    |      1 | 
    | Marge      | Simpson   | MI    |      1 | 
    | Barney     | Gumble    | IL    |      0 | 
    | Bart       | Simpson   | WI    |      1 | 
    +------------+-----------+-------+--------+
    
    Filtering with WHERE
    
    SELECT first_name, last_name, state, active
    FROM users
    WHERE active = 1;
    
    +------------+-----------+-------+--------+
    | first_name | last_name | state | active |
    +------------+-----------+-------+--------+
    | Homer      | Simpson   | WI    |      1 | 
    | Marge      | Simpson   | MI    |      1 | 
    | Bart       | Simpson   | WI    |      1 | 
    +------------+-----------+-------+--------+
    
    Ordering records
    
    SELECT first_name, last_name, state
    FROM users
    ORDER BY state asc
    
    +------------+-----------+-------+
    | first_name | last_name | state |
    +------------+-----------+-------+
    | Barney     | Gumble    | IL    | 
    | Marge      | Simpson   | MI    | 
    | Homer      | Simpson   | WI    | 
    | Bart       | Simpson   | WI    | 
    +------------+-----------+-------+
    
    Counts
    
    select count(*) as NumberOfRecords from users;
    
    +-----------------+
    | NumberOfRecords |
    +-----------------+
    |               4 | 
    +-----------------+
                  
    Grouping
    
    SELECT count(*) as people, state 
    FROM users
    GROUP BY state;
    
    +--------+-------+
    | people | state |
    +--------+-------+
    |      1 | IL    | 
    |      1 | MI    | 
    |      2 | WI    | 
    +--------+-------+
  
  Insert
  
    INSERT INTO users
    (first_name, last_name, active, state)
    VALUES ('Lisa', 'Simpson', 1, 'WI');
      
    Note that names and values have to match up.  
    
  Update
  
    UPDATE users SET state = "WI" WHERE users.id = 1;
    
  Delete
  
    DELETE from users 
    WHERE state = 'WI';
  
    Delete everyone.
  
      DELETE from users;
    
        -or- (faster)
         
      TRUNCATE users;  
  
  
  Joins
  
     chapters
     +----+------------------+---------+
     | id | title            | book_id |
     +----+------------------+---------+
     |  1 | Ninjas Are Cool  |       1 | 
     |  2 | Pirates Are Cool |       2 | 
     |  3 | Silence Is Key   |       1 | 
     +----+------------------+---------+
  
     Books
     +----+-----------------+
     | id | title           |
     +----+-----------------+
     |  1 | The Ninja Book  | 
     |  2 | The Pirate Book | 
     |  3 | The Web Book    | 
     +----+-----------------+   
  
  
     Explicit Joins
     ---------------
          
     Get all books and chapters
     
     SELECT books.title AS book, chapters.title AS chapter
     FROM books 
     LEFT JOIN chapters
     ON books.id = chapters.book_id;
     
     +-----------------+------------------+
     | book            | chapter          |
     +-----------------+------------------+
     | The Ninja Book  | Ninjas Are Cool  | 
     | The Ninja Book  | Silence Is Key   | 
     | The Pirate Book | Pirates Are Cool | 
     | The Web Book    | NULL             | 
     +-----------------+------------------+
     
     ** The LEFT JOIN keyword returns all the rows from the left table (books), even if there are no matches in the right table (chapters)
        This is also called OUTER JOIN
        
     Get all books with no chapters
     
      SELECT books.title AS book, chapters.book_id
      FROM books 
      LEFT OUTER JOIN chapters
      ON books.id = chapters.book_id
      WHERE chapters.book_id IS NULL;
              
      +--------------+---------+
      | book         | book_id |
      +--------------+---------+
      | The Web Book |    NULL | 
      +--------------+---------+
      
      Get only books with chapters.
      SELECT books.title AS book, chapters.book_id
      FROM books 
      INNER JOIN chapters
      ON books.id = chapters.book_id;
      
      +-----------------+---------+
      | book            | book_id |
      +-----------------+---------+
      | The Ninja Book  |       1 | 
      | The Pirate Book |       2 | 
      | The Ninja Book  |       1 | 
      +-----------------+---------+
      
      * Inner joins are joins in which only rows with explicit matches
        are returned. If a record in the left table (books) has no
        associations in the right table(chapters), it is not returned.
  
    Removing duplicates
  
      Joins have duplication though. Filter that down with the DISTINCT
      keyword.
      
      SELECT distinct books.title AS book, chapters.book_id
      FROM books 
      INNER JOIN chapters
      ON books.id = chapters.book_id;
      
      +-----------------+---------+
      | book            | book_id |
      +-----------------+---------+
      | The Ninja Book  |       1 | 
      | The Pirate Book |       2 | 
      +-----------------+---------+
     
      * IN order for distinct to work, the WHOLE ROW must be a duplicate.
      
    Implicit joins:
    
    Many people do an inner join implicitly in the WHERE condition:
    
     SELECT distinct books.title AS book, chapters.book_id
     FROM books, chapters
     WHERE books.id = chapters.book_id;
      
     +-----------------+---------+
     | book            | book_id |
     +-----------------+---------+
     | The Ninja Book  |       1 | 
     | The Pirate Book |       2 | 
     +-----------------+---------+
     
     This works, but is NOT recommended on many databases, especially for
     large datasets. The WHERE conditions are USUALLY run on the entire set
     of returned rows. n*rows.
     
    Join Conditions
    
    Conditions can be applied in joins as well instead of on the WHERE clause. This can reduce query times.
    
    Assume our books table had a 'published' column.
    And you wanted the number of chapters from books that were
    published.
    
    +----+-----------------+-----------+
    | id | title           | published |
    +----+-----------------+-----------+
    |  1 | The Ninja Book  |         1 | 
    |  2 | The Pirate Book |         0 | 
    |  3 | The Web Book    |         0 | 
    +----+-----------------+-----------+
    
    SELECT books.title, count(chapters.book_id) as number_of_chapters
    FROM books
    INNER JOIN chapters
      ON books.id = chapters.book_id
      AND books.published = 1
    GROUP BY books.title;
    
    +----------------+--------------------+
    | title          | number_of_chapters |
    +----------------+--------------------+
    | The Ninja Book |                  2 | 
    +----------------+--------------------
          
  DON'T FEAR THE SQL! IT IS POWERFUL!