Polecenie kończymy ; a nie ENTEREM
W POLECENIACH SQL – poprzedzają komentarz
\dt, \dp, \z – wyświetlanie tabel
\d NAZWA TABELI – STRUKTURA TABELI
\h <polecenie> - pomoc na temat polecenia SQL
CREATE TABLE
Typy danych
int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp, interval + geometryczne (np. point)
przykład
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);
CREATE TABLE cities (
name varchar(80),
location point
);
2 DROP TABLE tablename;
3 INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
Wskazanie pól
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);
TYPY DANYCH
TYP LOGICZNY I DOKŁADNE TYPY NUMERYCZNE
| Nazwa SQL | Alternatywna nazwa PosgreSQL | Opis |
|---|---|---|
| boolean | Bool | Wartość prawdy: TRUE, ‘t’, ‘true’, ‘y’, ‘yes’, ‘1’ |
| smallint | Int2 | Dwubajtowa liczba calkowita ze znakiem (od -32768 do 32767) |
| integer, int | Int4 | Czterobajtowa liczba całkowita ze znakiem (od –2147483648 do 2147483647) |
| bit | 0 lub 1 | |
| bit varying | varbit | Sekwencja bitów |
PRZYBLIŻONE TYPY NUMERYCZNE
| numeric(precyzja, skala) | Zapisuje liczbę z określoną dokładnością | |
|---|---|---|
| decimal(precyzja, skala) | ||
| float(precyzja) | float4, float8 | Prezycja<7 to float4, precyzja={8…15} to float8 |
| Real | float4 | Zalecane użycie float(precyzja) |
| couble precision | float8 | Tak samo jak float (15) |
| money | Jak decimal(7,2) |
TYPY DATY I CZASU
| timestamp | datatime | Zapisuje czas od 4713 r. p.n.e. do 1465001 r. n.e. z koładnością do 1 mikrosekundy |
|---|---|---|
| timestamp with timezone | Zapisuje czas od 1903 do 2037 r. n.e. z dokładnością do 1 mikrosekundy | |
| interval | Interval, timespan | |
| date | Daty od 4713 t. P.n.e. do 32767 r. n. e. z dokładnością do 1 dnia | |
| time | Zapisuje czas dnia od godziny 0 do 23:59:59:99 z dokładnością do 1 us |
TYPY ZNAKOWE
| char | Pojedynczy znak | |
|---|---|---|
| char(n) | Zapisuje dokładnie n znaków, jeśli w rzeczywistości jest mniej znaków, zostaną one dopełnione spacjami (zalecany dla krótkich ciągów znakowych o znanej długości) | |
| char varying (n) | varchar(n) | Zmienna liczba znaków, maksymalnie do n znaków |
| text | Specyficzny dla Postgresa, podobnie jak varchar, nie potrzeba określać n |
LAB 2
Definicja tabeli – wartości domyślne (typ danych timestamp now())
CREATE TABLE products (
product_no integer,
name text,
price numeric DEFAULT 9.99
);
SPRAWDZANIE WARTOŚCI – CHECK Constraints
2.1 postać
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0)
);
NADAWANIE WŁASNEJ NAZWY (DLA POTRZEB KOMUNIKATU O BŁĘDZIE)
CREATE TABLE products (
product_no integer,
name text,
price numeric CONSTRAINT positive_price CHECK (price > 0)
);
OPERACJE NA KILKU ATRYBUTACH – PORÓWNANIE
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0),
discounted_price numeric CHECK (discounted_price > 0),
CHECK (price > discounted_price)
);
Inne ograniczenia
3.1 NOT NULL
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric
);
Kilka ograniczeń
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric NOT NULL CHECK (price > 0)
);
4 UNIKALNOŚĆ WAROŚCI W POLACH (TABELI) – UWAGA NULL NIE JEST PORÓWNYWANY!
4.1 UNIKALNOŚĆ WARTOŚCI W KOLUMNIE
CREATE TABLE products (
product_no integer UNIQUE,
name text,
price numeric
);
4.2 unikalność wartości w obfrębie wszystkich pól tabeli
CREATE TABLE products (
product_no integer,
name text,
price numeric,
UNIQUE (product_no)
);
4.3. UNKILANOŚĆ WARTOŚCI W WYBRANYCH KOLUMNACH
CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c)
);
4.4. WŁASNE KOMUNIKATY
CREATE TABLE products (
product_no integer CONSTRAINT must_be_different UNIQUE,
name text,
price numeric
);
5 DEKLARACJA KLUCZY TABELI (GŁÓWNY, OBCE)
5.1 KLUCZ – GŁÓWNY – UNKALNY I BEZ NULL
DEKLARACJE RÓWNOWAŻNE:
CREATE TABLE products (
product_no integer UNIQUE NOT NULL,
name text,
price numeric
);
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
5.2 KLUCZ ZŁOŻONY
CREATE TABLE example (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);
5.3 KLUCZ OBCY – TYP DANYCH MUSI BYĆ ZGODNY Z KLUCZEM GŁÓWNYM!
TABELA GŁÓWNA
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
TABELA ZWIĄZANA
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);
5.4 BEZ WSKAZANIA DOMYŚLNIE ZWIĄZANY BĘDZIE ATRYBUT KLUCZ GŁÓWNY
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products,
quantity integer
);
5.5. KILKA KLUCZY OBCYCH
CREATE TABLE t1 (
a integer PRIMARY KEY,
b integer,
c integer,
FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);
PRZYKŁAD
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text,
...
);
CREATE TABLE order_items (
product_no integer REFERENCES products,
order_id integer REFERENCES orders,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
5.6 WIĘZY INTEGRALNOŚCI - KASKADOWE USUWANIE REKORDÓW
ON DELETE (także ON UPDATE) RESTRICT – BLOKADA USUWANIA ZWIĄZANYCH REKORDÓW
ON DELETE (także ON UPDATE) CASCADE – KASKADOWE USUWANIE ZWIĄZANYCH REKORDÓW
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text,
...
);
CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
6 MODYFIKACJA STRUKTURY TABELI
ALTER TABLE products ADD COLUMN description text;
lub
ALTER TABLE products ADD COLUMN description text CHECK (description <> '');
ALTER TABLE products DROP COLUMN description;
lub
ALTER TABLE products DROP COLUMN description CASCADE;
ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
LAB 3
Mamy tabele:
products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);
W tabeli products mamy 5 produktów
W tabeli orders mamy 4 zamówienia
1 CROSS JOIN
SELECT * FROM products CROSS JOIN orders;
2 INNER JOIN (ON warunek !!! lub słowo USING)
SELECT * FROM products INNER JOIN orders ON products.product_no = orders.product_no;
albo
SELECT * FROM products INNER JOIN orders USING (product_no);
NATURAL JOIN – skrót od USING łączący te same kolumny w tabelach
SELECT * FROM products NATURAL INNER JOIN orders;
LEFT JOIN (= INNER JOIN + niepołączone krotki z pierwszej tabeli!)
SELECT * FROM products LEFT JOIN orders ON products.product_no = orders.product_no;
Albo
SELECT * FROM products LEFT JOIN orders USING (product_no);
5 RIGHT JOIN – (INNER JOIN + krotki z niepołączone kolejnej tabeli!)
SELECT * FROM products RIGHT JOIN orders ON products.product_no = orders.product_no;
6 FULL JOIN (inner join+ + krotki z niepołączone pierwszej tabeli+ + krotki z niepołączone kolejnej tabeli
)
SELECT * FROM products FULL JOIN orders ON products.product_no = orders.product_no;
7 aliasy
FROM nazwa_tabeli AS nazwaaliasu, nazwa_tabeli AS nazwaaliasu
Aliasy i join
SELECT * FROM nazwa tabeli nazwa aliasu JOIN nazwa tabeli nazwa aliasu ON
Albo
SELECT * FROM nazwa tabeli AS nazwa aliasu CROSS JOIN nazwa tabeli AS nazwa aliasu