$ createdb mydb To create a new database
$ /usr/local/pgsql/bin/createdb mydb
$ dropdb mydb delete database
$ psql mydb PostgreSQL interactive terminal program
mydb=> SELECT version();
mydb=> SELECT current_date;
mydb=> SELECT 2 + 2;
mydb=> \h help on the syntax of various SQL commands
mydb=> \q get out of psql
$ man psql documentation
RDBMS relational database management system
Relation is essentially a mathematical term for table.
table is a collection of rows
-- comment
/* multiline comment
* with nesting: /* nested block comment */
*/
CREATE TABLE weather (
city varchar(80),
temp_lo int,
temp_hi int,
prcp real,
date date
);
int | smallint | real | double precision | char(N) | varchar(N) | date | time | timestamp | interval
DROP TABLE tablename;
INSERT INTO weather VALUES
('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
COPY weather FROM '/home/user/weather.txt';
SELECT * FROM weather;
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;
SELECT * FROM weather ORDER BY city;
SELECT * FROM weather ORDER BY city, temp_lo;
SELECT DISTINCT city FROM weather;
SELECT DISTINCT city FROM weather ORDER BY city;
SELECT * FROM weather, cities WHERE city = name;
SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;
SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.location FROM weather, cities WHERE cities.name = weather.city;
SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
SELECT * FROM weather w, cities c WHERE w.city = c.name;
SELECT max(temp_lo) FROM weather;
SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
SELECT city, max(temp_lo) FROM weather GROUP BY city;
SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40;
SELECT city, max(temp_lo) FROM weather WHERE city LIKE 'S%'(1) GROUP BY city HAVING max(temp_lo) < 40;
UPDATE weather SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 WHERE date > '1994-11-28';
DELETE FROM weather WHERE city = 'Hayward';
DELETE FROM tablename;
------------------------------
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities WHERE city = name;
SELECT * FROM myview;
------------------------------
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 );
------------------------------
TRANSACTION
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- etc etc
COMMIT;
------------------------------
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;
--------------------------
SELECT depname, empno, salary, avg(salary)
OVER (PARTITION BY depname) FROM empsalary;
--------------------------
SELECT depname, empno, salary, rank()
OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
--------------------------
SELECT salary, sum(salary) OVER () FROM empsalary;
--------------------------
SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
--------------------------
SELECT depname, empno, salary, enroll_date
FROM
(SELECT depname, empno, salary, enroll_date,
rank() OVER (PARTITION BY depname
ORDER BY salary DESC, empno) AS pos
FROM empsalary ) AS ss
WHERE pos < 3;
--------------------------
SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
--------------------------
CREATE TABLE cities (
name text,
population real,
altitude int);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
SELECT name, altitude FROM ONLY cities
WHERE altitude > 500;
--------------------------
CREATE FUNCTION dept(text) RETURNS dept
AS $$ SELECT * FROM dept WHERE name = $1 $$
LANGUAGE SQL;
--------------------------
scalar subquery is a SELECT query that returns exactly one row with one column or NULL if there is no row.
--------------------------
CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)
RETURNS text
AS
$$
SELECT CASE
WHEN $3 THEN UPPER($1 || ' ' || $2)
ELSE LOWER($1 || ' ' || $2)
END;
$$
LANGUAGE SQL IMMUTABLE STRICT;
SELECT concat_lower_or_upper('Hello', 'World', true);
-- HELLO WORLD
--------------------------
SELECT concat_lower_or_upper(a := 'Hello', b := 'World');
--------------------------
CREATE TABLE products (
product_no integer,
name text,
price numeric DEFAULT 9.99);
--------------------------
CREATE TABLE products ( product_no integer
DEFAULT nextval('products_product_no_seq') );
--------------------------
CREATE TABLE products ( product_no SERIAL);
--------------------------
CREATE TABLE products ( product_no integer, name text,
price numeric CHECK (price > 0) );
--------------------------
CREATE TABLE products
(
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
--------------------------
CREATE TABLE products (
product_no integer,
name text,
price numeric,
CHECK (price > 0),
discounted_price numeric,
CHECK (discounted_price > 0),
CONSTRAINT valid_discount
CHECK (price > discounted_price)
);
--------------------------
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric );
--------------------------
No comments:
Post a Comment