Skip to content

Instantly share code, notes, and snippets.

@mdang

mdang/SQLREF.md

Created May 26, 2015 17:07
Show Gist options
  • Save mdang/02fba2a0ab0724972187 to your computer and use it in GitHub Desktop.
Save mdang/02fba2a0ab0724972187 to your computer and use it in GitHub Desktop.
Download ZIP
SQL Reference
Raw
SQLREF.md

SQL

Admin Notes

  • Notifying instructors if late

Homework Review

10am, 30 minutes

Class Review

10:30, 30 minutes

About SQL

11:00, 40 minutes

  • SQL stands for Structured Query Language. SQL is just a way of talking to relational databases.

Types of SQL DBs

  • There are many different instances of SQL databases, each with tiny differences amongst themselves. The differences are so minute we won't go over them here, but if you'd like to look them up, go ahead.
    • PostgreSQL
    • MySQL
    • SQLite
    • Proprietary software like Oracle or Microsoft SQL Server

SQL Data Types

  • There are lots and lots of SQL data types, many more than in Ruby. But there are a couple that we use most of the time:

SQL Syntax

EXERCISE: Using your ERDs/Database models from last night's homework, go through the fields and decide what data type they should be. Use the PG docs to help.

BREAK: 10 minutes

Installing Postgres

11:50, 15 minutes

$ brew update
$ brew doctor
$ brew install postgresql
$ initdb /usr/local/var/postgres -E utf8
$ mkdir -p ~/Library/LaunchAgents
$ cp /usr/local/Cellar/postgresql/<<<YOUR_PSQL_VERSION_NUMBER>>>/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
$ launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ gem install pg

If something goes wrong during the Homebrew installation, you can use http://postgresapp.com/ instead

Using Databases and Tables

12:05, 30 minutes

Creating a database

  • First, let's open up psql, which is a REPL for Postgres, a type of SQL database we have installed on our computers
  • If we type \l, we'll be able to see all the databases Postgres has already. Postgres has already created a couple databases with metadata that it uses internally, which we can see here.
  • To create our own database, we can type CREATE DATABASE wdi;

Creating a table

  • To do anything with our database, first we need to connect to it. We do that by typing \c wdi.
  • If we type \d, we'll see there are no tables
  • We can create a table by using the following syntax CREATE TABLE students ( id SERIAL PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, age INT NOT NULL, ss_num INT NOT NULL UNIQUE );
    • The format on each of these lines for creating a field is field_name TYPE_OF_FIELD CONSTRAINT (CONSTRAINT 2) (CONSTRAINT 3)
    • Constraints are the rules we apply to fields on the database to make sure our database stays clean and in a valid state.
    • Some of the most popular constraints are PRIMARY KEY, NOT NULL, and UNIQUE. There are others that you should check out.

EXERCISE: Create a database named after the app you modeled for last night's homework, then create at least 2 of the tables with the appropriate fields and constraints

LUNCH: 1 Hour

CRUD Statements

1:35, 40 minutes

Creating records

  • INSERT INTO wdi (first_name, last_name, age) VALUES ('Sean', 'Shannon', 26)

Reading records

  • SELECT * FROM wdi
  • The WHERE clause
    • To use the where clause, we need a field to compare, an operator specifying the type of comparison to be made, and a value that represents our criterion
    • SELECT * FROM wdi WHERE last_name = 'Shannon'
    • SELECT * FROM wdi WHERE age >= 25

Updating records

  • UPDATE students SET ss_num = 987654321 WHERE age > 25;

Deleting records

  • DELETE FROM students WHERE age > 25;

Joins

2:15, 30 minutes

Indexing

2:45, 20 minutes

  • A database index is a data structure that orders itself by some criterion, and then maps the ordered data in the index to the unordered data in tables
  • Dewey Decimal System analogy
  • Database indices should be created for any field of data you will frequently query, or search for. If the field is unordered and you attempt to search through it, it is similar to looking through a library without any idea of where the book you want is
  • Creating too many database indices can be harmful because these indexes take up space in memory, and so creating so many indices that they don't fit in memory will make your application's performance suffer

CREATE INDEX index_name ON table_name ( column1, column2.....);

BREAK: 10 minutes

Accessing PG from Scripts

3:15, 40 minutes

  • For now we can simply

      require "pg"
  conn = PG.connect(:hostaddr => "127.0.0.1", :port => 5432, :dbname => "wdi")
  res = conn.exec("SELECT * FROM students")
  res.each do |item|
    puts item
  end

  • The PG object allows us to access and interface with the Postgres database. It is the API (or bridge) from our app to the database.

  • Here, we're saving the result of the PG connection to a variable named conn, then we're using the exec method of that object to send a SQL query to the database. Finally, we're looping through the results of the query and puts-ing them.

EXERCISE: Write a simple program that will take input from the user and insert their information as a row in the students table we created using the PG object to communicate with the database.

SQL injection

  • SQL injection is one of the most dangerous and most prominent types of attacks on the web. It is also one of the easiest to stop.

  • SQL injection occurs when user input is not "sanitized" first, meaning it is possible for the user to type characters that are not properly escaped and can control the actual SQL itself

  • SQL injection allows malicious users to gain access to your database which has potentially private and valuable information about your users and your application, and then the hacker can even lock you out of the database

  • http://xkcd.com/327/

  • To prevent this, we can use prepared statements, like so:

      conn.prepare('insert_student_statement', 'INSERT INTO students (first_name, last_name, age, ss_num) values ($1, $2, $3, $4)')
  conn.exec_prepared('insert_student_statement', [ 'Mikael', 'Davis', 25, 649204729 ])

  • Prepared statements sanitize our data by escaping it, which means it will prevent any quotation marks from being interpreted as control characters, and instead will interpret them literally as characters in a string. This prevents the hacker from being able to influence our program.

LAB EXERCISE

3:55, Remainder of class

Using SQL and prepared statements, create a Ruby class for Student and add class methods that would implement the basic CRUD functionality to interact with a database by calling a Ruby method on the class

BONUS: Try thinking of additional helpful SQL commands that you can implement to act on multiple records at a time, and implement those.

DOUBLE BONUS: Try making an abstract class that can be extended by any subclass that wants to use these SQL methods. You'll have to update your SQL statements!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment