[TOC]
SQL
Structured query language
Database has several tables for different purposes.
basic query
select food from diet where species = "orangutan";
select * from tablename limit 10 offset 2;
select name, birthdate from animals where species != 'gorilla';
insert into tablename values ( val1, val2, … );
select name from animals, diet where animals.species=diet.species and diet.food='fish';
select count (species),species from animals group by species order by count(species) DESC;
select animals group by species having num=1; # after aggregation
select ordernames.name, count(*) as num
from animals, taxonomy, ordernames
where animals.species = taxonomy.name and taxonomy.t_order = ordernames.t_order
group by ordernames.name
order by num desc;
update posts set content='cheese' where content like '%spam%';
delete from posts where content='cheese';
Create/drop database/table, primary key, references
create database name;
create table fish(id serial primary key, name text);
create table postal_places(postal_code text,country text, name text, primary key(postal_code, country));
create table sales(sku text references products, sale_date date, count integer); # use references for foreign key
drop database name;
drop table name;
delete from tablename;
insert into tablename values ('This is text!');
\c name # connect to database
join
select animals.name, animals.species, diet.food
from animals join diet
on animals.species = diet.speices
where food = 'fish';
-- show item with 0 count
select products.name, products.sku, count(sales.sku) as num
from products left join sales
on products.sku = sales.sku
group by products.sku;
-- self join
select a.id, b.id, a.building, a.room
from residences as a, residences as b
where a.building = b.building
and a.room = b.room
and a.id > b.id
order by a.building, a.room;
subQuery
select name, weight from players, (select avg(weight) as av from players) as subq where weight< av;
create view name as select ...
Python DB-API
use PosreSQL
always use 2 command windows
- for python
python forum.py
http://localhost:8000/ # you can open it on a browser
http://localhost:8000/ # you can open it on a browser
- for psql
psql forum # load forum database, equal to" \i forum.sql"
select 2+2 as a, 4+4 as b;
select * from posts;
select * from posts \watch # update very 2 seconds, so you can see what's added
\d posts # get columns and type
\dt # list all tables
\H #switch b/w plain text vs HTML
loop hole
something fun: http://xkcd.com/
sql injection attack:
'); delete from posts; —
script injeciton attack
DB-API using sqlite3
import sqlite3
with sqlite3.connect("chinook.db") as conn:
cursor = conn.cursor()
rows = cursor.execute('select * from artist limit 10;').fetchall()
conn.commit() # required if insert new data
for row in rows: print row
normalized design, which makes it easier to write effective code using a database.
A Simple Guide to Five Normal Forms in Relational Database Theory
A Simple Guide to Five Normal Forms in Relational Database Theory
normalized table
- Every row has the same number of columns,
- There’s a unique key. Everything in a row says something about the key.
- Facts that don’t relate to the key belong in different tables,
- Tables should’t imply relationships that don’t exist.
