|
29 Dec 2005
>cd C:\Program Files\PostgreSQL\8.0\bin
>initdb /data/hw0
//Success. You can now start the database server using:
//Created a c:/data/hw0 folder
//I must use an non-administrator account to do this, weird.
>pg_ctl start -D \data/hw0
//postmaster starting
>createdb mydb
//CREATE DATABASE
//dropdb mydb = command to remove a database
>psql mydb
//Welcome to psql 8.0.5, the PostgreSQL interactive terminal.
mydb=# SELECT version();
//version
//------------------------------------------------------------------------------------------
//PostgreSQL 8.0.5 on i686-pc-mingw32, compiled by GCC
gcc.exe (GCC) 3.4.2 (mingw-special)
//(1 row)
mydb=# SELECT current_date;
// date
//------------
//2005-12-29
//(1 row)
mydb=# SELECT 2+2;
//?column?
//----------
// 4
//(1 row)
mydb=# CREATE TABLE weather (
mydb(# city varchar(80),
mydb(# temp_lo int, -- low temperature
mydb(# temp_hi int, -- high temperature
mydb(# prcp real, -- precipitation
mydb(# date date
mydb(# );
//CREATE TABLE
CREATE TABLE
mydb=# CREATE TABLE cities (
mydb(# name varchar(80),
mydb(# location point
mydb(# );
//CREATE TABLE
//DROP TABLE tablename; = delete a table
mydb=# INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
//INSERT 17235 1, another way is the following 2 lines
//INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
//VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
mydb=# INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
//INSERT 17236 1, the second entry is point type, user-defined type
mydb=# COPY weather FROM '/home/user/weather.txt';
//Copy data from a text file
mydb=# SELECT * FROM weather;
// city | temp_lo | temp_hi | prcp | date
//---------------+---------+---------+------+------------
//San Francisco | 46 | 50 | 0.25 | 1994-11-27
//(1 row)
mydb=# SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
// city | temp_avg | date
//---------------+----------+------------
//San Francisco | 48 | 1994-11-27
//(1 row)
mydb=# INSERT INTO weather VALUEs ('Hayward', 37, 54, 0 ,'1991-11-29');
mydb-# WHERE city = 'San Francisco' AND prcp > 0.0;
// city | temp_lo | temp_hi | prcp | date
//---------------+---------+---------+------+------------
// San Francisco | 46 | 50 | 0.25 | 1994-11-27
//(1 row)
mydb=# SELECT * FROM weather
mydb-# ORDER BY city;
// city | temp_lo | temp_hi | prcp | date
//---------------+---------+---------+------+------------
// Hayward | 37 | 54 | 0 | 1991-11-29
// San Francisco | 46 | 50 | 0.25 | 1994-11-27
//(2 rows)
mydb=# INSERT INTO weather VALUEs ('Hayward', 37, 54, 0 ,'1991-11-29');
mydb=# SELECT DISTINCT city
mydb-# FROM weather;
// city
//---------------
// Hayward
// San Francisco
//(2 rows)
mydb=# SELECT *
mydb-# FROM weather, cities
mydb-# WHERE city = name;
// city | temp_lo | temp_hi | prcp | date | name | location
//---------------+---------+---------+------+------------+---------------+-----------
// San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
//(1 row)
// alternatively
//SELECT weather.city, weather.temp_lo, weather.temp_hi,
// weather.prcp, weather.date, cities.location
//FROM weather, cities
//WHERE cities.name = weather.city;
mydb=# SELECT *
mydb-# FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
// city | temp_lo | temp_hi | prcp | date | name | location
//---------------+---------+---------+------+------------+---------------+-----------
// Hayward | 37 | 54 | 0 | 1991-11-29 | |
// Hayward | 37 | 54 | 0 | 1991-11-29 | |
// San Francisco | 46 | 50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
//(3 rows)
mydb=# SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
mydb-# W2.city, W2.temp_lo AS low, W2.temp_hi AS high
mydb-# FROM weather W1, weather W2
mydb-# WHERE W1.temp_lo < W2.temp_lo
mydb-# AND W1.temp_hi > W2.temp_hi;
// city | low | high | city | low | high
//---------+-----+------+---------------+-----+------
// Hayward | 37 | 54 | San Francisco | 46 | 50
// Hayward | 37 | 54 | San Francisco | 46 | 50
//(2 rows)
mydb=# SELECT max(temp_lo) FROM weather;
// max
//-----
// 46
//(1 row)
//other compute command = count, sum, avg (average), max (maximum) and min
(minimum)
mydb=# SELECT city FROM weather
mydb-# WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
// city
//---------------
// San Francisco
//(1 row)
mydb=# SELECT city, max(temp_lo)
mydb-# FROM weather
mydb-# GROUP BY city;
// city | max
//---------------+-----
// Hayward | 37
// San Francisco | 46
//(2 rows)
mydb=# SELECT city, max(temp_lo)
mydb-# FROM weather
mydb-# GROUP BY city
mydb-# HAVING max(temp_lo) < 40;
// city | max
//---------+-----
// Hayward | 37
//(1 row)
mydb=# SELECT city, max(temp_lo)
mydb-# FROM weather
mydb-# WHERE city LIKE 'S%'
mydb-# GROUP BY city
mydb-# HAVING max(temp_lo) > 40;
// city | max
//---------------+-----
// San Francisco | 46
//(1 row)
mydb=# UPDATE weather
mydb-# SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
mydb-# WHERE date < '1994-11-28';
//UPDATE 3, this updates, and change the existing data
mydb-# DELETE FROM weather WHERE city = 'Hayward';
//delete a column from the table, this changes the table
//DELETE FROM tablename;= make the table empty
mydb=# CREATE VIEW myview AS
mydb-# SELECT city, temp_lo, temp_hi, prcp, date, location
mydb-# FROM weather, cities
mydb-# WHERE city = name;
//CREATE VIEW
mydb=# SELECT * FROM myview;
// city | temp_lo | temp_hi | prcp | date | location
//---------------+---------+---------+------+------------+-----------
// San Francisco | 44 | 48 | 0.25 | 1994-11-27 | (-194,53)
//(1 row)
//hide away the table structure
//Foreign Keys
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
//ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
//DETAIL: Key (city)=(Berkeley) is not present in table "cities".
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
COMMIT;
//Use ROLLBACK instead of COMMIT if you decide not to proceed with the
transaction
//Savepoint
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;
//Inheritants
CREATE TABLE cities (
name text,
population real,
altitude int -- (in ft)
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
SELECT name, altitude
FROM cities
WHERE altitude > 500;
//select from all inherited structured of cities
SELECT name, altitude
FROM ONLY cities
WHERE altitude > 500;
//select only from pure city structures
mydb=# \h
\\help
mydb=# \q
//quit
drop table T;
create table T(a int, b int, c int);
copy T from '/home/ff/cs186/gendata/T.data' USING DELIMITERS ',';
13,13,13
10,11,12
10,14,12
11,14,12
14,10,10
1. Starting Postgres with Cygwin
http://www.postgresql.org/docs/faqs.FAQ_CYGWIN.html
2. PostgreSQL win32 Download
http://www.postgresql.org/ftp/binary/v8.0.5/win32/
3. XP Apache/PHP/mySQL guide
http://www.mjmwired.net/resources/mjm-apache-windows.html
4. Berkeley OCF Class recommended mySQL installation Guide
http://www.linuxguruz.com/z.php?id=31
5. Berkeley Database Class CS 186
http://inst.eecs.berkeley.edu/~cs186/fa05/homework.html
|
