Transcript An introduction to SQL

An introduction to
SQL
1/21/2014 – See chapter 2.3 and 6.1
PostgreSQL http://www.postgresql.org/docs/9.3/static/index.html
Acknowledgement – Thanks to Dr. Mayfield for the lab activity.
SQL Query language
• Divided into “clauses” each of which does a particular piece of
work.
• Two kinds of languages
• DDL – Data Definition Language – Manipulates the database
schema
• DML – Data Manipulation Language – Manipulates the database
data
DDL (2.3)
• CREATE – Makes new database objects
• CREATE TABLE …
• CREATE USER …
• CREATE DATABASE …
• ALTER – Changes database objects
• ALTER TABLE …
DML – 6.1
• SELECT – Read only query to examine data from the database.
May be used in conjunction with update statements.
• UPDATE – Update existing data
• INSERT – Insert new rows
• DELETE – Delete existing rows
Important clauses
• SELECT – Chooses the individual fields (columns) from the
database (not necessarily a particular table)
• FROM – Chooses which table(s) we are working with
• WHERE – Provides criteria for the selection
PostgreSQL
• Relational DBMS use SQL but each has their own dialects and
supported features.
• PostgreSQL is:
• “PostgreSQL is a powerful, open source object-relational
database system. It has more than 15 years of active
development and a proven architecture that has earned it a
strong reputation for reliability, data integrity, and
correctness.” (www.postgresql.org/about)
Our server
• db.cs.jmu.edu
• Accessible from JMU networks only
• Hardware
• 2*12 Core Opteron 2.2 GHz
• 48 GB RAM (plus 48 GB swap)
• 6*275 GB Disks (1.5 TB free)
• Software
• Linux 3.8 (Ubuntu 12.04.4)
• PostgreSQL 9.1
Additional server
• For those interested in exploring MySQL it is available on:
• daisy.cs.jmu.edu
• See me if you want an account.
Logging in
• From the command line:
• psql -h db.cs.jmu.edu -U username
• To change your password:
• \ password
• Your db account is separate from your JMU account!
• Do it now
Create a table using DDL
CREATE TABLE movie
(
id integer,
title text,
year integer,
genres text,
mpaa text,
budget text
);
White space does not matter but style dictates indentation for
readability.
Case does not matter for the keywords like CREATE and TABLE but
convention has us capitalize them.
Do it now!
Loading the db
• Choices – You can hand enter the data ….
• OR – You can load it from a file.
• Today we will load from a file.
Load the database
• Download
• http://w3.cs.jmu.edu/mayfiecs/cs474/movies.csv.gz
• About 1.4 MB
• Uncompress
• gunzip movies.csv.gz
• Now 3.8 MB
• With psql
• \copy movie FROM movies.csv WITH CSV HEADER;
• ANALYZE VERBOSE movie;
• 63,508 rows
• NOTE: \ is an escape which says to do this outside of psql
Basic psql commands
General
• \? help on psql commands
• \h: help on SQL syntax
• \i execute commands from file
• \q: quit psql (or Ctrl-D)
Browsing
• \d: list tables, views, etc
• \d NAME: describe table, NAME,/etc
Important
• Ctrl-C cancels the current query
• Tab completion is your friend!
Your first DML query
SELECT title
FROM movie
WHERE year = 2012;
-- what you want from the db
-- the table to operate on
-- the criterion
Use up/down arrows and page up/down
g or G: go to first / last line
/ or ?: search forward / backward
Press ‘h’ for help
Press ‘q’ to quit
Explore
• Write another query that includes more than one field in the
select and a different criterion.
• You can use AND and OR to combine criteria.
• Multiple fields are separated by commas in the SELECT
statement.
Homework
• Download any data set from:
• http://archive.ics.uci.edu/ml/
• Create the table
• Import the data into your database
• Write several queries exploring the data set.
• See HW03 in Canvas