SQL! Fun, Delight, Only A Mild Amount Of Terror

I love SQL and have noticed that a number of junior devs aren't well versed in it and over-rely on the ORM. This tutorial is my attempt to share the love. It takes about 2 hours.

Transcript

1. SQL! Fun, Delight, Only A Mild Amount Of Terror Alleviate the terror by downloading the slides at jmkoni.com/sql
2. What is SQL?
3. Structured Query Language
4. person has one pet pet address has many used in all relational databases
5. Create - put data into tables Read - query data out of tables Update - change data already in tables Delete - remove data from a table What can SQL do?
6. A few quick notes on SQL syntax...
7. Every statement must end with a semi-colon If you open up a quotation mark, you must close with the same type of quotation mark (ex: "hi", 'hi', but "hi' won't work) If you get stuck and it keeps showing you ...> instead of sqlite> in the prompt, then you have most likely either A. messed up the quotes B. forgotten your semi-colon SQL doesn't care if you return after each line or if you indent. The indentations in these slides are just to show how you would do it if you are writing up SQL in a professional setting.
8. Let's create a few tables!
9. $ sqlite3 testdata.db Create your database!
10. CREATE TABLE person ( id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT, email TEXT );
11. CREATE TABLE pet ( id INTEGER PRIMARY KEY, name TEXT, owner_id INTEGER, species TEXT, birth_date DATE, FOREIGN KEY(owner_id) REFERENCES person(id) );
12. CREATE TABLE address ( id INTEGER PRIMARY KEY, person_id INTEGER, street_address TEXT, city TEXT, state TEXT, zip_code TEXT, FOREIGN KEY(person_id) REFERENCES person(id) );
13. sqlite> .exit Let's go ahead and exit
14. What are primary & foreign keys? CREATE TABLE pet ( id INTEGER PRIMARY KEY, .. owner_id INTEGER, .. FOREIGN KEY(owner_id) REFERENCES person(id) );
15. Primary keys Unique identifier for a row within a table Cannot be null Only one primary key per table Usually automatically generated
16. Foreign keys Field in one table that identifies a row in another Generally references the primary key in another table Adding the foreign key constraint prevents invalid data
17. Time to put some data in the tables! jmkoni.com/pyladiessql
18. $ sqlite3 testdata.db < pyladiessqltestdata.sql After you download, load the data in the database *should return nothing*
19. Let's do a couple of simple selects!
20. $ sqlite3 testdata.db sqlite> .mode columns sqlite> .headers on Open up the DB and also... let's make our output look a bit nicer
21. sqlite> SELECT * FROM person LIMIT 1; id first_name last_name email ---------- ---------- ---------- ----------------------- 1 Rylee Wynn Ryl.WYN2659@yopmail.com sqlite> SELECT name, species FROM pet LIMIT 1; name species ---------- ---------- Amani rabbit
22. WHERE clause
23. filter your records! sqlite> SELECT city FROM address WHERE state = 'CA'; city ------------- Moreno Valley Bakersfield San Francisco Chula Vista Fremont Oxnard
24. Also use comparison operators! sqlite> SELECT name, birth_date FROM pet WHERE birth_date >= '2015-01-10'; name birth_date ---------- ------------------- Kaylee 2015-04-07 10:00:00 Kennedi 2015-12-03 10:00:00 Janet 2015-11-23 10:00:00 Keyon 2015-10-30 10:00:00 Pierce 2015-09-15 10:00:00 Draven 2015-12-04 10:00:00 Drew 2015-02-28 10:00:00 Chana 2015-09-11 10:00:00
25. Other available operators = <> (or !=) > < >= <= BETWEEN LIKE IN
26. You can also combine with AND & OR
27. sqlite> SELECT city FROM address WHERE state = 'CA' OR state = 'MA'; city ------------- Moreno Valley Bakersfield San Francisco Springfield Worcester Chula Vista Fremont Oxnard
28. sqlite> SELECT city FROM address WHERE city LIKE '%field%' AND (state = 'CA' OR state = 'MA'); city ----------- Bakersfield Springfield put it all together...
29. What about putting everything in order?
30. sqlite> SELECT name FROM pet ORDER BY name DESC LIMIT 5; name ---------- Zoey Zion Zayden Yareli Xzavier Easy!
31. INSERTing new records
32. sqlite> INSERT INTO pet (name, species, birth_date) VALUES ('Fluffypants', 'rabbit', '2016-05-10 10:00:00'); sqlite> SELECT * FROM pet WHERE name = 'Fluffypants'; id name owner_id species birth_date ---------- ----------- ---------- ---------- ------------------- 195 Fluffypants rabbit 2016-05-10 10:00:00
33. UPDATE your records
34. sqlite> UPDATE pet SET birth_date = '2016-05-11 10:00:00' WHERE name = 'Fluffypants'; sqlite> SELECT * FROM pet WHERE name = 'Fluffypants'; id name owner_id species birth_date ---------- ----------- ---------- ---------- ------------------- 195 Fluffypants rabbit 2016-05-11 10:00:00
35. DELETE all that old crap
36. sqlite> DELETE FROM pet WHERE name = 'Fluffypants'; sqlite> SELECT * FROM pet WHERE name = 'Fluffypants'; (nothing)
37. Time to learn about joins
38. Four basic types of joins: INNER LEFT RIGHT FULL OUTER
39. INNER JOIN Table B Table A Results of an INNER JOIN
40. sqlite> SELECT COUNT(person.id) FROM person; 100 sqlite> SELECT COUNT(DISTINCT(person.id)) FROM person INNER JOIN pet ON pet.owner_id = person.id; 87
41. sqlite> SELECT person.first_name, person.last_name, pet.name, pet.species FROM person INNER JOIN pet ON pet.owner_id = person.id LIMIT 5; first_name last_name name species ---------- ---------- ---------- ---------- Rylee Wynn Amani rabbit Rylee Wynn Brayan snake Korbin Stuart April cat Rebecca Reilly Kaylynn snake Mariano Dawson Valentino ferret
42. LEFT JOIN Table B Table A Results of an LEFT JOIN
43. sqlite> SELECT COUNT(person.id) FROM person; 100 sqlite> SELECT COUNT(DISTINCT(person.id)) FROM person LEFT JOIN pet ON pet.owner_id = person.id; 100
44. sqlite> SELECT person.first_name, person.last_name, pet.name, pet.species FROM person LEFT JOIN pet ON pet.owner_id = person.id LIMIT 10; first_name last_name name species ---------- ---------- ---------- ---------- Korbin Stuart April cat Rebecca Reilly Kaylynn snake Mariano Dawson Kaylee hamster Mariano Dawson Valentino ferret Mariano Dawson Zayden cat Andres Horne Jaylin Mcdonald Franco cat
45. RIGHT JOIN Table B Table A Results of a RIGHT JOIN
46. FULL OUTER JOIN Table B Table A Results of a FULL JOIN
47. Bad news: RIGHT & FULL OUTER joins aren't available in sqlite3
48. SQL Functions
49. We've already used one (COUNT)...
50. but there are so many more! NUMERICAL AVG() COUNT() ROUND() SUM() AGGREGATE GROUP BY HAVING TEXT UCASE() or UPPER() LCASE() or LOWER() FORMAT() PICK ONE FIRST() LAST() MAX() MIN() DATE NOW() or DATE('now')
51. Let's do a few examples
52. sqlite> SELECT name, species, MIN(birth_date) AS birth_date FROM pet; name species birth_date ---------- ---------- ------------------- Adrian rabbit 2000-01-07 10:00:00 What is the oldest pet?
53. sqlite> SELECT person.first_name, person.last_name, address.city, COUNT(pet.id) AS NumPets FROM person INNER JOIN address ON address.person_id = person.id LEFT JOIN pet ON pet.owner_id = person.id GROUP BY person.id; first_name last_name city NumPets ---------- ---------- ---------- ---------- Rylee Wynn Stamford 2 Korbin Stuart Hialeah 1 Rebecca Reilly Sioux Fall 1 Mariano Dawson Wichita 3 Andres Horne Naperville 0 How many pets does each person have?
54. sqlite> SELECT person.first_name, person.last_name, COUNT(pet.id) FROM person INNER JOIN pet ON pet.owner_id = person.id GROUP BY person.id HAVING COUNT(pet.id) = 2 ORDER BY person.last_name LIMIT 5; first_name last_name TotalPets ---------- ---------- ---------- Kendyl Adkins 2 Katherine Aguirre 2 Ismael Alvarez 2 Camden Bailey 2 Clay Barton 2 Select all people with 2 pets (limit 5)
55. Any questions?