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

SQL Crash Course

SQL Crash Course

Databases are powerful, and to a beginner, can seem quite daunting. What is SQL, and why should I use it? This presentation provides a brief crash-course in what SQL databases are and how to write SQL queries. It also touches on the usage of object-relational mappers in Python. (Presented at the Boston Python meetup group.)

David Baumgold

October 29, 2013
Tweet

More Decks by David Baumgold

Other Decks in Programming

Transcript

  1. a
    crash course
    in
    SQL Databases
    David Baumgold // @singingwolfboy

    View full-size slide

  2. Computers aren’t very good at
    remembering stuff…
    …unless you use a
    database.

    View full-size slide

  3. two basic types
    SQL NoSQL
    • More powerful
    • Better consistency
    • More mature
    • Transaction support
    • Simpler
    • Faster
    • Evolving quickly
    • More scalable

    View full-size slide

  4. two basic types
    SQL NoSQL
    • More powerful
    • Better consistency
    • More mature
    • Transaction support
    • Simpler
    • Faster
    • Evolving quickly
    • More scalable

    View full-size slide

  5. Structured Query Language
    It’s how you talk to a SQL database!
    Jeez,!
    dude has!
    anger!
    management!
    issues

    View full-size slide

  6. It’s actually not that hard
    SELECT u.id, u.username
    FROM users AS u
    WHERE u.active;
    That’s a real SQL query!
    It’s almost like English!
    !
    Sort of like another language we all know…

    View full-size slide

  7. Tabular data
    id username! first_name last_name active
    1 singingwolfboy David Baumgold TRUE
    2 nedbat Ned Batchelder TRUE
    3 gvanrossum Guido Van Rossum FALSE
    4 divagoddess Cher NULL FALSE
    5 pitydafool Mr. T TRUE

    View full-size slide

  8. Unordered data
    id username! first_name last_name active
    4 divagoddess Cher NULL FALSE
    2 nedbat Ned Batchelder TRUE
    5 pitydafool Mr. T TRUE
    1 singingwolfboy David Baumgold TRUE
    3 gvanrossum Guido Van Rossum FALSE
    SAME

    View full-size slide

  9. Basic Data Query
    SELECT u.id, u.username
    FROM users AS u
    WHERE u.active;
    SELECT:
    what columns do you want?
    FROM:
    what tables do you
    want to use?
    WHERE:
    how do you want to filter the contents of the table?
    “SFW” query: very common

    View full-size slide

  10. Shorter
    SELECT *
    FROM users
    WHERE active;
    SELECT * means
    give me EVERY column
    AS is optional;
    it’s just an alias
    If your column names are unambiguous,
    you don’t need to specify which table they’re from
    WHERE is optional too,
    if you want EVERY row in the table

    View full-size slide

  11. WHERE clause
    SELECT *
    FROM users
    WHERE age >= 18;
    SELECT *
    FROM users
    WHERE last_login < '2013-09-29';
    It’s just a comparison!
    !
    Booleans, dates, strings,
    many different types
    SELECT *
    FROM users
    WHERE last_name IS NOT NULL;
    NULL means a lack of data,
    but the database will happily
    return it unless you specify otherwise

    View full-size slide

  12. Chaining
    SELECT *
    FROM users
    WHERE job='knight'
    AND quest='seek_grail'
    AND fav_color='blue';
    Use AND for multiple
    conditions that must
    all apply
    SELECT *
    FROM users
    WHERE job='enchanter'
    OR first_name='Tim';
    Use OR for multiple
    conditions where any
    may apply

    View full-size slide

  13. ORDER BY clause
    • Databases are built for
    speed
    • Data might be organized
    weirdly on disk
    • By default, queries will
    return data as soon as
    possible, even if it’s out of
    order
    id name
    5 Sue
    18 Zach
    3 Ariel
    11 James
    7 Mark
    19 Eve
    27 Charlie

    View full-size slide

  14. ORDER BY clause
    SELECT id, name
    FROM users
    ORDER BY id;
    id name
    3 Ariel
    5 Sue
    7 Mark
    11 James
    18 Zach
    19 Eve
    27 Charlie

    View full-size slide

  15. ORDER BY clause
    SELECT id, name
    FROM users
    ORDER BY name;
    id name
    3 Ariel
    27 Charlie
    19 Eve
    11 James
    7 Mark
    5 Sue
    18 Zach

    View full-size slide

  16. ORDER BY clause
    SELECT id, name
    FROM users
    ORDER BY name desc;
    id name
    18 Zach
    5 Sue
    7 Mark
    11 James
    19 Eve
    27 Charlie
    3 Ariel

    View full-size slide

  17. JOIN
    Hoo-boy.!
    This is where it gets!
    a bit complicated.
    But it’s also the most powerful part of SQL,
    so hang in there!

    View full-size slide

  18. OnlineStore.com
    id name order1_item1 order1_item2 order2_item1 order2_item2
    1 Frank wrench screw toaster mongoose

    Let’s build a database!
    • Customers
    • Items
    • Orders

    View full-size slide

  19. Database
    Customers
    Orders
    Addresses
    Items

    View full-size slide

  20. id name
    1 Frank
    2 Joanne
    Customer
    id street cust_id
    1 1 main street 1
    2 11 cambridge 1
    3 98 myrtle ave 2
    4 NERD center 2
    Address
    Foreign Key
    SELECT c.name, a.street
    FROM customers AS c, address AS a
    WHERE a.cust_id = c.id;
    One-To-Many Table
    Primary Key

    View full-size slide

  21. id name
    1 Frank
    2 Joanne
    Customer id name price
    1 hammer 6.00
    2 nail 0.50
    3 toaster 4.50
    4 mongoose 129.99
    Item
    id date cust_id
    1 2013-10-23 1
    2 2013-09-01 1
    Order
    order_id item_id quantity
    1 1 1
    1 2 12
    1 3 1
    2 2 5
    2 4 1
    Order_Item
    Many-To-Many Table

    View full-size slide

  22. Check this out!
    SELECT i.name
    FROM customer AS c,
    order AS o,
    item AS i,
    order_item AS oi
    WHERE o.cust_id = c.id
    AND oi.order_id = o.id
    AND oi.item_id = i.id
    AND c.name = 'Santa'
    AND o.date >= '2012-12-01'
    AND o.date <= '2012-12-31';
    What items did people named Santa
    buy in December of last year?

    View full-size slide

  23. Check this out!
    SELECT SUM(i.price)
    FROM customer AS c,
    order AS o,
    item AS i,
    order_item AS oi
    WHERE o.cust_id = c.id
    AND oi.order_id = o.id
    AND oi.item_id = i.id
    AND c.name = 'Santa'
    AND o.date >= '2012-12-01'
    AND o.date <= '2012-12-31';
    How much did he spend?

    View full-size slide

  24. Check this out!
    SELECT c.name
    FROM customer AS c,
    order AS o,
    item AS i,
    order_item AS oi
    WHERE o.cust_id = c.id
    AND oi.order_id = o.id
    AND oi.item_id = i.id
    AND i.name = 'mongoose';
    Who bought the mongoose?

    View full-size slide

  25. Slice and Dice Your Data
    and answer questions
    you haven’t even
    thought of yet

    View full-size slide

  26. Python!
    Ships with the sqlite3 module,
    which gives you a full SQL database
    right inside your Python console!
    >>> import sqlite3
    >>> db = sqlite3.connect("example.db")
    >>> db.execute("SELECT * FROM users;")

    View full-size slide

  27. Python and ORM
    ORM = Object Relational Mapper
    Organize your
    data in
    SQL tables
    Use your data
    as Python objects

    View full-size slide

  28. /
    Two good Python ORMs
    >>> from sqlalchemy import Column, Integer, String
    >>> class User(Base):
    ... __tablename__ = 'users'
    ... id = Column(Integer, primary_key=True)
    ... name = Column(String)
    ... password = Column(String)
    ...
    >>> u = User("Joe Average", "123456")
    >>> u.save()
    >>> u.id
    1
    (Django ORM is very similar)

    View full-size slide

  29. ORM queries
    SELECT i.name
    FROM customer AS c, order AS o,
    item AS i, order_item AS oi
    WHERE o.cust_id = c.id
    AND oi.order_id = o.id
    AND oi.item_id = i.id
    AND c.name = 'Santa'
    AND o.date >= '2012-12-01'
    AND o.date <= '2012-12-31';
    from datetime import date
    q = (session.query(Item.name).join(Customer).join(Order)
    .filter(Customer.name == "Santa")
    .filter(Order.date >= date(2012, 12, 1))
    .filter(Order.date <= date(2012, 12, 31))
    )

    View full-size slide

  30. Questions?
    David Baumgold // @singingwolfboy

    View full-size slide