ПБД (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
    )
)

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