introduction to postgresql
TRANSCRIPT
• Introduction to databases
• Introduction to PostgreSQL
• CRUD
• Create
• Read/query
• Update
• Destroy
• Joins
Overview
What’s a database?
• A database stores data!
• It is a collection of information that is organized so that it can be easily accessed, managed, and updated
Why do I care?• If you’re building a web application, you’re
going to have data to store
• What data do these websites store?
• Facebook (users, profiles, groups, events, businesses)
• Twitter (users, tweets)
• CNN (articles, comments)
What’s PostgreSQL?• PostgreSQL (“post-gress-Q-L”) is an open
source object-relational database system
• Open source = free!
• Object - some object-oriented paradigms are natively supported, such as objects, classes, and inheritance
• Relational - database is structured to recognize relations among stored items of information
Let’s make a database
$ createdb testdb
$ psql testdb
=> SELECT version();
Note: to exit psql just type “\q”
Let’s make a table
=> CREATE TABLE spaceship ( nickname varchar(35), crew_size int, commission_date date );
CRUD
• CRUD (create, read, update, delete) are the four basic functions you perform on a database
Create
• Examples:
• Creating a new account on Facebook
• Posting a picture on Instagram
• Creating a new tweet on Twitter
Read
• Examples:
• Browsing your Facebook timeline
• Looking at pictures on Instagram
• Reading the latest article on CNN
Update
• Examples:
• Changing your password
• Editing a comment
• Changing your relationship status on Facebook
Destroy
• Examples:
• Deleting a picture on Facebook (well, maybe…)
• Deleting a tweet on Twitter
• Unfriending somebody on Facebook
Create
• SQL uses the INSERT statement to create a new row
=> INSERT INTO spaceship VALUES (‘falcon’, 15, ‘2040-04-16’);
• Add a few more records to your spaceship table
Note: you must enter values in the same order you used during creation of the table
Read
• To read data from your database, one or more tables must be queried
• The SELECT statement is used to perform queries
=> SELECT * FROM spaceship;
Read
• The * is a shorthand for “all columns”
• The same result could be achieved with:
=> SELECT nickname, crew_size, commission_date FROM spaceship;
Read
• Queries can be ordered by adding ORDER BY [column] to the end of your query
=> SELECT * FROM spaceship ORDER BY nickname;
=> SELECT * FROM spaceship ORDER BY commission_date;
Read• Queries can be “qualified” by adding a
WHERE clause that specifies which rows are wanted
=> SELECT * FROM spaceship WHERE commission_date > ‘2030-04-16’;
=> SELECT * FROM spaceship WHERE nickname = ‘falcon’;
=> SELECT * FROM spaceship WHERE crew_size > 5;
Update• Rows can be updated using the UPDATE
command
• Update row(s) matching WHERE clause
=> UPDATE spaceship SET nickname = ‘eagle’ WHERE nickname = ‘falcon’;
• Careful, default updates all rows!
=> UPDATE spaceship SET crew_size = 20;
Destroy• Rows can be destroyed using the DELETE
command
• Delete row(s) matching WHERE clause
=> DELETE from spaceship WHERE nickname = ‘falcon’;
• Careful, default deletes all rows!
=> DELETE from spaceship;
Congratulations!
• Now you know how to CRUD a PostgreSQL database!
Digging Deeper…
• Joins between tables
• Transactions
But First…
• Install sample database that we can run some joins/queries on
• https://code.google.com/p/northwindextended/downloads/detail?name=northwind.postgre.sql
But First…
$ createdb northwind
$ psql northwind < northwind.postgre.sql
$ psql northwind
But First…
• Lets do a little exploration:
=> \dt
=> \d+ orders
=> \d+ customers
Joins - Inner Join• SQL inner join is the most common type of
join
• Used to combine rows from two or more tables, based on a common field between them
Joins - Inner Join• Which company placed each order?
• Return the OrderID, CustomerID, and CompanyName for each order
• Using an Inner Join we can join the orders table with the customers table, using the CustomerID
=> SELECT “OrderID”, orders.”CustomerID’, “CompanyName” FROM orders INNER JOIN customers ON orders.”CustomerID”=customers.”CustomerID”;
Joins - Inner Join• What products were ordered, and what were
their prices?
• Return the ProductID, UnitPrice, and ProductName for each item of a given order
• Using an Inner Join we can join the order_details table with the products table
=> SELECT order_details.”ProductID”, ”UnitPrice”, “ProductName” FROM order_details INNER JOIN products ON order_details.”ProductID”=products.”ProductID” WHERE “OrderID” = 10248;
Joins - Left Outer Join• The LEFT OUTER JOIN keyword returns all
rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
Joins - Left Outer Join• “Let me see a list of all of our customers, and
any orders they have made”
• Need to return the CompanyName and associated OrderID (if any)
• Using a Left Outer Join we can join the orders table with the customers table
=> SELECT customers."CompanyName", orders.”OrderID" FROM customers LEFT OUTER JOIN orders ON customers."CustomerID"=orders."CustomerID" ORDER BY customers."CompanyName";
Joins - Right Outer Join
• The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.
Joins - Full Outer Join• The FULL OUTER JOIN keyword returns all
rows from the left table (table1) and from the right table (table2).The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.
Transactions• “A transaction is a unit of work that is performed against a
database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.”
• “You use transactions when the set of database operations you are making needs to be atomic. That is - they all need to succeed or fail. Nothing in between.” http://stackoverflow.com/questions/9317866/when-to-use-transactions-in-sql-server
• Examples:
• Money transfer
• Order fulfillment
• Can you think of any others?
Joel Brewer
@jahbrewskibrewerdigital.com