ПБД (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);
продолжение...