26 июн. 2009 г.

SQL: Quick start

Скорее это памятка самому себе, ибо как только перестаёшь постоянно иметь дело с sql, так тут же начинаешь его забывать.
В качестве примера будем рассматривать некоторый офис, значения взяты с Wiki / SQL Join - Sample tables
Таблица «Отдел»:
CREATE TABLE departament (
id INTEGER NOT NULL,
name VARCHAR(32) NOT NULL,

CONSTRAINT departament_id_pk PRIMARY KEY (id),
CONSTRAINT departament_name_qk UNIQUE(name)
);
Данные о отделах:
INSERT INTO departament VALUES 
(31, 'Sales'), 
(33, 'Engineering'), 
(34, 'Clerical'), 
(35, 'Marketing');
Таблица «Сотрудник»:
CREATE TABLE employee (
id INTEGER NOT NULL,
lastname VARCHAR(32) NOT NULL, 
departament_id INTEGER NOT NULL,

CONSTRAINT employee_id_pk PRIMARY KEY (id),
CONSTRAINT employee_name_qk UNIQUE(lastname),
CONSTRAINT employee_departament_id FOREIGN KEY(departament_id)
REFERENCES departament(id)  ON DELETE CASCADE
);
Инфо о сотрудниках:
INSERT INTO employee VALUES 
(1, 'Rafferty', 31), 
(2, 'Jones', 33),
(3, 'Steinberg', 33), 
(4, 'Robinson', 34), 
(5, 'Smith', 34), 
(6, 'Jasper', NULL);
Таблица «Счета сотрудников»:
CREATE TABLE bill(
employee_id INTEGER NOT NULL, 
payments INTEGER, 
debet INTEGER, 

CONSTRAINT bill_employee_id FOREIGN KEY(employee_id)
REFERENCES employee(id) ON DELETE CASCADE
)
Собственно счета:
INSERT INTO bill VALUES 
(1, NULL, 50),  (1, 100, NULL),
(2, NULL, 5),  (2, NULL, 55),
(3, 50, NULL),  (3, 100, NULL),
(4, 50, NULL), (4, NULL,100);

Теперь собственно всякие запросы и результаты:
  • Cross Join он же Cartesian join - Декартовое (прямое) произведение.
    SELECT employee.lastname, departament.name 
    FROM employee 
    CROSS JOIN departament;
    эквивалентно:
    SELECT employee.lastname, departament.name 
    FROM employee, departament;
  • Inner Join - пересечение множеств основной (левой) таблицы и присоединяемой (права) таблицы, которые удовлетворяют условию.
    Замечание: Inner Join может быть заменён на WHERE условие при прямом произведении таблиц.
    Пример: получить список всех сотрудников и названиях их отделов, если сотрудник закреплён к отделу
    SELECT employee.lastname, departament.name FROM employee
    INNER JOIN departament 
    ON employee.departament_id = departament.id;
    Замечание: эквивалентно
    SELECT employee.lastname, departament.name FROM employee, departament 
    WHERE employee.departament_id = departament.id
    lastname  |    name     
    -----------+-------------
    Rafferty  | Sales
    Jones     | Engineering
    Steinberg | Engineering
    Robinson  | Clerical
    Smith     | Clerical
  • Left (outer) Join - т.н. левое дополнение, когда из основной (слева) таблицы будут получены все значения, а из присоединяемой таблицы (справа), только те, которые удовлетворяют условию.
    Пример: получить список всех сотрудников и названиях их отделов, NULL - если сотрудник не закреплён к отделу.
    SELECT employee.lastname, departament.name FROM employee
    LEFT OUTER JOIN departament 
    ON employee.departament_id = departament.id;
    lastname  |    name     
    -----------+-------------
    Rafferty  | Sales
    Jones     | Engineering
    Steinberg | Engineering
    Robinson  | Clerical
    Smith     | Clerical
    Jasper    | 
    
  • Right (outer) Join - т.н. правое дополнение, когда из присоединяемой (справа) таблицы будут получены все значения, а из основной таблицы (левой), только те, которые удовлетворяют условию.
    Пример: получить список всех отделов и сотрудников в этих отделах, NULL - если сотрудников в отделе нет, сотрудники, которые не закреплены к отделу не будут показаны.
    SELECT employee.lastname, departament.name FROM employee
    RIGHT OUTER JOIN departament 
    ON employee.departament_id = departament.id;
    Замечание: может быть представлен через Left (outer) Join
    SELECT employee.lastname, departament.name FROM departament
    LEFT OUTER JOIN employee 
    ON employee.departament_id = departament.id;
    lastname  |    name     
    -----------+-------------
    Rafferty  | Sales
    Steinberg | Engineering
    Jones     | Engineering
    Smith     | Clerical
    Robinson  | Clerical
    | Marketing
  • Full Join - объединение двух предыдущих методик.
    Пример: получить список всех отделов и сотрудников в этих отделах, NULL - если сотрудников в отделе нет, NULL - если сотрудник не закреплён к отделу.
    SELECT employee.lastname, departament.name FROM employee
    FULL JOIN departament 
    ON employee.departament_id = departament.id;
    lastname  |    name     
    -----------+-------------
    Rafferty  | Sales
    Jones     | Engineering
    Steinberg | Engineering
    Robinson  | Clerical
    Smith     | Clerical
    Jasper    | 
    | Marketing
  • Group by - объединение (агрегация) по колонке, часто используется в связке с функциями sum, min, max, average, count.
    Пример: какие сотрудники производили какие-либо операции
    SELECT employee.lastname FROM bill 
    INNER JOIN employee 
    ON employee_id = employee.id 
    GROUP BY employee.lastname;
    lastname  
    -----------
    Steinberg
    Jones
    Robinson
    Rafferty
  • Coalesce - выбор первого не NULL аргумента из списка
    Пример:Вычислить баланс сотрудника, однако при вычислении с использованием SUM если одно из значений будет NULL, то и вся сумма будет NULL.
    SELECT employee.lastname,
    SUM(COALESCE(bill.debet, 0)) - SUM(COALESCE(bill.payments, 0)) 
    AS sum
    FROM bill 
    INNER JOIN employee 
    ON employee_id = employee.id
    GROUP BY employee.lastname;
    lastname  | sum  
    -----------+------
    Steinberg | -150
    Jones     |   60
    Robinson  |   50
    Rafferty  |  -50

2 комментария:

Nodir Gulyamov комментирует...

Кстати тут один из friend-ов в ЖЖ опубликовал описание как это все работает изнутри, довольно позновательно:
Часть 1
Часть 2
Часть 3
Связанное обсуждение

Владимир Долженко комментирует...

@Nodir Gulyamod:
огромное спасибо, крайне познавательно.