Банки и базы данных

Банки и базы данных

Тема 7. Языковые средства СУБД

назад | оглавление | вперёд

Языковые средства СУБД представляют собой:

Язык описания данных (Data Definition Language, DDL)
Язык манипулирования данными (Data Manipulation Language, DML)

DDL позволяет:

Определить структуру данных
Определить связи между данными
Определить ограничения на данные.

DME позволяет описать алгоритмы доступа и обработки данных.
Реализации языковых средств СУБД делятся на:

Закрытые системы: со свои собственным языком и не работающих со стандартными языками (dBase, FoxPro)
Открытые системы: нет своего языка, а пользующие универсальные языки программирования.
Комбинированные системы: симбиоз открытых систем и закрытых.
Базовый язык описывает структуры и алгоритмы обработки данных
Расширение языка работает с данными.

Способы реализации базового языка:

Новый транслятор (Pascal -> Delphi)
Препроцессор (программа, реализующая команды работы с данными в последовательности команд базового языка)
Call-интерфейс (библиотека, нужная для работы с данными)
Реализация call-интерфейса через много подпрограмм (dBase: dbopen(), dbclose()…)
Реализация call-интерфейса через одну процедуру, где команда передается через параметры

SQL (Structured Query Language).

Реляционно-ориентированный язык, позволяющий минимумом команд реализовать около 30 операций по работе с данными, позволяющий как формулировать запросы, так и писать прикладные программы.

Команды делятся на группы:

Команды определения данных
1. создание БД
2. создание таблиц
3. задание полей таблиц
4. создание индексов
5. удаление таблиц, индексов, БД.
Запросы на выборку данных
Команды модификации данных
1. добавление данных
2. удаление данных
3. изменение данных
Команды управления данными
1. привелегии
2. паралельный доступ
3. транзакции

Кроме команд, существует множество операторов:

Арифметические вычисления
сравнения
создание виртуальных таблиц
запоминание результатов запросов/вычислений в таблицах БД
группировка данных по группам

Типы данных в SQL:

integer	целые числа
decimal(p,q)	с фиксированной точкой, p- всего знаков, q – после запятой
float	с плавающей точкой
char(n)	строка длиной n
archer	строка переменной длины
date	дата
time	время
datetime	дата/время
money	денежный тип
logical	логический
doubleprecesion	двойная точность

Cуществует 3 уровня языка SQL

Минимальный SQL
- INSERT
- SELECT
- UPDATE
Базовый SQL
- ALTER TABLE
- CREATE TABLE
- CREATE VIEW
- Более рсширеный синтаксис SELECT
Расширеный SQL (используется в “навороченных” СУБД Oracle, DB2…)

Запросы в SQL.

Запросы бывают:

Простые (данные берутся только из одной таблицы)
Многотабличные (данные из нескольких таблиц)
Вложенные (запрос внутри запроса)

SELECT [[ALL]/DISTINCT] {*/поле1, поле2…}
FROM {базовая таблица1/представление 1}, {базовая таблица2/представление 2}
[WHERE условие]
Результатом команды SELECT есть таблица (представление).
ALL                     все значения в столбцах
DISTINCT          только уникальные значения
FROM                задает таблицы, откуда запрашивается
WHERE             условия на выборку

Пример 1:

Список штукатуров
SELECT Фамилия
FROM Сотрудник
WHERE Должность=’штукатур’

Пример 2:

Данные о всех студентах АД-76
SELECT *
FROM Студенты
WHERE Группа=’АД-76

Составное условие
WHERE [NOT] условие1 [AND/OR] [NOT] условие2…
условия:

значение {=/<>/<=/</>=/>} {константа/выражение}
значение1 [NOT] BEETWEEN значение2 AND значение3
значение [NOT] IN {список констант/значений}
значение IS [NOT] NULL

Пример 3:

Сотрудники с окладом 800..1000 р.
SELECT Фамилия
FROM Сотрудники
WHERE Оклад BEETWEEN 800 AND 1000

Пример 4:

Сотрудники 2, 5 и 6 отделов
SELECT Фамилия
FROM Сотрудники
WHERE Отдел IN 2,5,6

Пример 5:

Студенты АД-76, не сдававшие экзамен
SELECT Фамилия
FROM Студент
WHERE Группа=’АД-76’ AND Оценка IS NULL
ORDER BY           Упорядочить выводимые поля
ASC                       по возрастанию
DESC                    по убыванию

Пример 6:

Студенты АД-76, упорядоченные по алфавиту
SELECT Фамилия
FROM Студент
WHERE Группа=’АД-76’
ORDER BY Фамилия ASC

Многотабличный запрос

Сотрудники:

Код сотрудника	Фамилия	Код отдела
1	Иванов	1
2	Петров	3

Отделы

Код отдела	Название
1	Снабжение

Пример 7:

Список сотрудников из отдела “Снабжения”
SELECT Фамилия
FROM Сотрудники, Отдел
WHERE Название=’Снабжение’

AND Сотрудники.КодОтдела=Отдел.КодОтдела

Алгоритм выполнения запроса:

Строится декартово произведение таблиц
Из результата произведения выбираются нужные строки
Из строк выбираются нужные столбцы

Пример 8:

КодСотрудника	ФИО	КодОтдела	КодНачальника

Необходимо выбрать ФИО и Фамилию начальника
SELECT А.Фамилия, В.Фамилия
FROM Сотрудник.А, Сотрудник.В
WHERE В.КодСотрудник=А.КодНачальника

Вложеные запросы.

Подзапрос помещается в WHERE главного запроса

Пример 9:

Расписание

КодСотрудника

День

Время

Должности работающих в понедельник
SELECT Должность
FROM Сотрудник
WHERE КодСотрудника IN (

SELECT КодСотрудника
FROM Расписание
WHERE День=’Понедельник’
)

Подзапросы бывают двух видов:

Некорелированные (результат подзапроса не зависит от главного запроса)
Корелированные (связи нет)

Пример 10:

Списки людей, работающих в тех отделах, которые работают по понедельникам

Расписание

КодОтдела

День

Время

SELECT Должность
FROM Сотрудники
WHERE КодОтдела IN (

SELECT КодОтдела
FROM Расписание
WHERE День=’Понедельник’ AND
Сотрудник.КодОтдела=
Расписание. КодОтдела=
)

Операции группировки

Встроенные в SQL функции

SUM	Суммирование
COUNT	Количество элементов в группе
AVG	Среднее значение в группе
MAX	Максимальной значение в группе
MIN	Минимальной значение в группе

Пример 11.

Максимальный и минимальный оклад
SELECT MAX(Оклад), MIN(Оклад)
FROM Сотрудники

Группировка:
SELECT . . .
. . .
. . .
GROUP BY {поле1, поле2} [HAVING условие]
HAVING- условие, накладываемое на группу

Пример 12.

Максимальный и минимальный оклад в отделе
SELECT MAX(Оклад), MIN(Оклад)
FROM Сотрудники
GROUP BY КодОтдела

Пример 13.

Максимальный и минимальный оклад в отделах, в которых более 1 сотрудника
SELECT MAX(Оклад), MIN(Оклад)
FROM Сотрудники
GROUP BY КодОтдела
HAVING COUNT(*)>1

Пример 14.

Сотрудники с окладом выше среднего по предприятию
SELECT Фамилия
FROM Сотрудники
WHERE Оклад> (

SELECT AVG(Оклад)
FROM Cотрудники
)

Команды модификации данных.

Модификация - ввод новых данных в таблицы, изменение данных, удаление данных из таблицы.
Удаление данных
DELETE
FROM {таблица/представление}
[WHERE условие]

Удаляется из указанной таблицы все записи, удовлетворяющие условию.

DELETE
FROM Сотрудники
WHERE Фамилия=’Иванов’

Добавление данных

INSERT
INTO {таблица/представление} (столбец [, столбец..])
VALUES ({константа/переменная}[,{константа/переменная}…])

Добавляет строку с указанными значениями.
INSERT
INTO Сотрудники (Фамилия, Имя, Отчество)
VALUES ('Иванов', 'Иван', 'Иванович')

второй вариант
INSERT
INTO {таблица/представление} (столбец [, столбец..])

подзапрос

Пример:

INSERT
INTO Начальник (фамилия, имя, отчество)
(
SELECT фамилия, имя, отчество
FROM Сотрудники
WHERE Должность= 'Инженер'
)

Изменение данных
UPDATE {таблица/представление}
SET столбец=значение [,столбец=значение]…
WHERE условие

устанавливает значение столбцов в тех кортежах, которые удовлетворяют условию.

UPDATE {таблица/представление}
SET столбец=значение [,столбец=значение]…
FROM {базовая таблица/представление} [псевдоним]
[,{базовая таблица/представление} [псевдоним]…]

Пример:

UPDATE Поставка
SET Цена=0
WHERE КодПоставщика IN (

SELECT КодПоставщика
FROM Поставщики
WHERE Город=’Москва’

)

Использование операций реляционой алгебры в SQL.

UNION- объединение.
SELECT *
FROM Сотрудники UNION Начальники

INTERSECT – Пересечение
SELECT *
FROM Сотрудники INTERSECT Начальники

EXCEPT – разность
SELECT *
FROM Сотрудники EXCEPT Начальники

JOIN- Соединение

Естественное соединение
От результата остаются только строки с совпадающими общими строками.
“Сотрудники JOIN должность” равносильно
SELECT *
FROM Сотрудники, Должность
WHERE Сотрудники.КодДолжности=Должность.КодДолжности.
Тета-соединение
Соединение с тета-операцией

Команды определения данных.

Это команды, позволяющие создавать/удалять таблицы, индексы, представления.

Таблицы.

CREATE TABLE базовая_таблица (столбец тип_данных [NOT NULL] ….)
Создать таблицу с указанным набором столбцов. NOT NULL показывает, что столбец не может быть пустым.
CREATE TABLE Сотрудник (Фамилия CHAR(50) NOT NULL)
DROP TABLE базовая_таблица
Удалить таблицу

Индексы

Индексы позволяют выполнять более быстрый поиск по таблице.
Индекс представляет собой один или несколько столбцов таблицы, по которым наиболее часто ведётся поиск, упорядоченные по значению первичного ключа.
CREATE [UNIQUE] INDEX имя_индекса
ON Базовая_Таблица (столбец [[ASC]/DESC]…)

- Создать индекс на основе таблицы и указанных столбцов таблицы.

DROP INDEX имя_индекса
Удалить индекс
CREATE INDEX Тема
ON КнижныйФонд (Тема ASC)

Представления

Представление- это временная таблица в БД, отажающая необходимые в данный момент пользователю данные. Необходимы представления для того, чтобы:

пользователь мог видеть только те данные, что необходимы (на какие есть права)
обеспечетить независимость работы различных приложений с доними и теми же таблицами.

CREATE VIEW ИмяПредставления (столбец…)
AS подзапрос [WITH CHECK OPTIONS]
- Создать представление на основе указанных таблиц.
DROP VIEW ИмяПредставления

Удалить представление.

Пример:

CREATE VIEW Назначение (ФИО, Должность)
AS SELECT ФИО, Должность, КодДолжности
FROM Сотрудник UNION Должность.

Привилегии пользователей при работе с данными

GRANT привелегии ON объект TO пользователь
REVOKE привелегии ON объект TO пользователь

Установить/снять привелегии.

назад | оглавление | вперёд