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 Slide

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

    View Slide

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

    View Slide

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

    View Slide

  5. View Slide

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

    View Slide

  7. 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 Slide

  8. 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 Slide

  9. 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 Slide

  10. 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 Slide

  11. 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 Slide

  12. 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 Slide

  13. 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 Slide

  14. 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 Slide

  15. 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 Slide

  16. 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 Slide

  17. 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 Slide

  18. 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 Slide

  19. 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 Slide

  20. Database
    Customers
    Orders
    Addresses
    Items

    View Slide

  21. 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 Slide

  22. 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 Slide

  23. View Slide

  24. 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 Slide

  25. 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 Slide

  26. 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 Slide

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

    View Slide

  28. 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 Slide

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

    View Slide

  30. /
    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 Slide

  31. 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 Slide

  32. Questions?
    David Baumgold // @singingwolfboy

    View Slide