ПБД (9) - Лекция №5 - SQL

Языки запросов

• SQL;
• объектное расширение SQL;
• OQL;
• Datalog;
• XQuery.

SQL

Умеет:

• создание схемы данных;
• работа с данными;
• работа с транзакциями;
• авторизация и права доступа;
• определение системы целостности, триггеры;
• определение представлений;
• определение физических структур для хранения данных.

Типы данных:

• integer;
• float;
• boolean;
• char(10);
• varchar(255);
• clob;
• blob;
• domain.

Синтаксис

Создание таблицы

CREATE TABLE таблица
(
    поле1 тип ограничение,
    поле2 тип ограничение,
    поле3 тип ограничение
);

Ограничения

Могут иметь имена:

CONSTRAINT имя;

Например:

ALTER TABLE таблица
ADD CONSTRAINT имя CHECK(условие);

Виды ограничений:

• уровня атрибутов:
  • NOT NULL;
  • DEFAULT;
  • UNIQUE;
  • CHECK();
• уровня кортежей:
  • PRIMARY KEY();
  • FOREIGN KEY() REFERENCES table();
  • DEFERRABLE INITIALLY DEFERRED;
  • CHECK();
• общего вида - проверяется при любом изменении БД:
  • ASSERTION;
  • триггеры.

Например:

CREATE ASSERTION имя
CHECK((SELECT COUNT(*) FROM таблица1) > (SELECT COUNT(*) FROM таблица2);

Пример триггера:

AFTER UPDATE [of поле] ON таблица REFERENCING
    OLD ROW AS имя1
    NEW ROW AS имя2
FOR EACH ROW [WHEN(условие)] {тело SQL};

Удаление таблицы

DROP TABLE таблица;

Создание индекса

CREATE [UNIQUE] INDEX имя
ON таблица();

Соединение таблиц

• таблица1 $$\times$$ таблица2;
• join;
• natural join;
• cross join;
• outer join:
  • left outer join;
  • right outer join;
  • full outer join.

Примеры

Film(name, year, len, type, stud);

Actor(inn, fio, edu);

Stud(sname, addr);

FA(inn, name, year);

Создание таблиц:

CREATE TABLE Film
(
     name varchar(50),
     year integer CHECK(year>1850 AND year <=2012), -- потому что конец света
     len integer NOT NULL DEFAULT 120,
     type char(2),
     stud integer REFERENCES Stud(sid) ON UPDATE CASCADE ON DELETE SET NULL,
     
     PRIMARY KEY(name, year)
);

CREATE TABLE Stud
(
     sid integer PRIMARY KEY UNIQUE,
     sname varchar(50) NOT NULL,
     addr varchar(300)
);

CREATE TABLE Actor
(
    inn char(10) PRIMARY KEY,
    fio varchar(200),
    edu varchar(50),
    CHECK(edu IN('среднее', 'высшее', 'Щукинское'))
);

CREATE TABLE FA
(
    act char(10) NOT NULL REFERENCES Actor(inn),
    fname varchar(50) NOT NULL,
    fyear integer NOT NULL,
    
    FOREIGN KEY(fname, fyear) REFERENCES Film(name, year),
    PRIMARY KEY(act, fname, fyear)
);

Извлечение данных:

SELECT * | поля | выражения | агрегация
FROM таблица | вложенный запрос
WHERE условие [AND | OR | NOT условие];

Примеры:

-- выбрать фильмы, снятые в 60-е года
SELECT *
FROM Film
WHERE year > 1950 AND year < 1960;

-- выбрать что-то ещё
SELECT name, year, len/60 AS hour
FROM Film
WHERE (name like '%s' or name like '_a')
  AND EXISTS(SELECT * FA WHERE fname = name)
ORDER BY name, year DESC;

-- фильмы студий Лос-Анджелеса
SELECT name, year, sname
FROM Film join Stud ON stud = sid
WHERE addr = 'Los Angeles';

-- актёры фильма "The Matrix"
SELECT fio
FROM Actors join FA ON inn = act
WHERE fname = 'The Matrix';

-- актёры, которые не снимались ни в одном фильме
SELECT fio
FROM Actor
WHERE inn NOT IN(SELECT act FROM FA);

-- актёры, которые снялись хотя бы в одном фильме
SELECT fio
FROM Actor
WHERE inn IN(SELECT act FROM FA);

-- актёр, игравший во всех фильмах
SELECT *
FROM Actors
WHERE NOT exists
(
    (
        SELECT название, год
        FROM Фильм
    )
    except
    (
        SELECT название, год
        FROM ФА
        WHERE ФА.inn = Актёр.inn
    )
)

продолжение...