Инструкция SELECT

Инструкция SELECT

Формирование запросов из базы данных

Общий вид команды SELECT

SELECT [DISTINCT][<псевдоним>]<выражение>[,…]

FROM <БД>[<псевдоним>][,…]

[INTO <получатель>][TO FILE][ADDITIVE][TO PRINTER]

[NO CONSOLE][NO WAIT][PLAIN]

[WHERE][<условие связи>][AND <условие связи>…]

[AND <условие отбора>][…]

[ORDER BY <колонка>[,…]]

[HAVING <условие отбора>]

[GROUP BY <колонка>…]

SELECT допускает включение в себя других внутренних команд SELECT

Строчки [1..2]

Указание результатов выборки и источников данных. Перед FROM перечисляются отбираемые выражения, а после – имена таблиц, из которых берутся данные. Выражение может быть полем записи, константой, функцией от переменных, полей и т. д. Если выражение является именем поля, то оно может быть составным. Псевдонимом может быть любое имя, которое вы присваиваете в команде SELECT. Это задаваемое временное имя указывается в опции <псевдоним>, после слова FROM. Никаких последствий за пределами SELECT такое назначение не имеет. Если необходимо построить выборку из всех полей таблицы, вместо их перечисления можно указать «*». В результате выполненной выборки получается совокупность колонок, заголовками которых могут быть имена полей. Если имена совпадают, то такие колонки получают совпадающие имена, к которым присоединяется одна из букв по алфавиту (TAB_A, TAB_B). Аналогичным образом даются имена колонкам, полученным в результате вычисления выражений. Их имена состоят из слова EXP и последовательности чисел (EXP_1, EXP_2). Исключения – выражения, использующие собственные функции SQL (MIN, MAX…). Имена колонок в этом случае будут включать имена функций. Если не устраивают имена, формируемые по назначению, можно назначить свои, указав их после слова AS в следующем виде:

<выражение> AS <новое имя колонки>

Включение [DISTINCT] исключает возможность вывода одинаковых строк выборки.

Строчки [3..4]

указание объекта, куда пересылается выборка. <получатель> может быть таблица, массив, текстовый файл, экран, принтер. Кроме того, информация может быть переслана в так называемый курсор. Курсор – временный набор данных, который может быть временным файлом и имеет режим только чтения. Данные курсора могут быть напечатаны, из них может быть образовано меню, они могут использоваться командой BROWSE, курсор может быть обработан другой командой SELECT. К колонкам курсора обращаются по имени этих колонок. Получатель может быть одним из следующих типов

ARRAY <имя массива>

CURSOR <имя курсора>

TABLE<имя>

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

NO CONSOLE – выборка не выдается на экран

PLAIN – заголовки колонок не выдаются
NO WAIT – не делается пауза при заполнении экрана

Строчки [5..6]

Критерии отбора данных.

Условие связи применяется в том случае, если выборка осуществляется из более, чем одной таблицы и указывает критерий, которому должны отвечать поля из разных таблиц. Разрешается использовать знаки отношений >, <, >=, <=. Допускается задание нескольких критериев, соединенных AND. Условие отбора строится аналогично, но из выражений только для одной таблицы, и допускается использование логических операторов OR, NOT.<Условия> могут включать следующие операторы.

LIKE – позволяет построить условие сравнения по шаблону.

Формат: <выражение> LIKE <шаблон>

BETWEEN – проверяет, находится ли выражение в заданном диапазоне.

Формат: <выражение> BETWEEN <нижнее значение> AND <верхнее значение>

IN – проверяет, находится ли выражение, стоящее слева от IN, среди перечисленных справа от IN.

Все эти операторы можно комбинировать с помощью связок OR, AND, NOT и [].

Строчки [7..9]

Группировка, сортировка

GROUP BY <колонка> - задаются колонки, по которым производится группировка выходных данных. Все записи, для которых значения колонок совпадают, отображаются в выборке единственной строкой.

HAVING <условие отбора> - задает критерий отбора данных в каждую сформированную в процессе выборки группу.

ORDER BY <колонка> - опция задает упорядочивание по заданной колонке. По умолчанию – сортировка по возрастанию, но может быть задана и по убыванию.

Выборка всех полей из базы STUD

SELECT * FROM STUD

Вывод min, max, среднего значения поля стипендия

SELECT MAX(STIP), MIN(STIP), AVG(STIP) FROM STUD

Вывод фамилий студентов, получающих стипендию от 500 до 1000 рублей.

SELECT FAM FROM STUD WHERE STIP BETWEEN 500 AND 1000

Вывод фамилий всех студентов, кроме тех, кто учится на «ФБ» и «ФГО»

SELECT FAM, FAK FROM STUD WHERE FAK NOT IN (‘ФБ’, ‘ФГО’)

Вывод названий всех факультетов из базы STUD

SELECT DISTINCT FAK FROM STUD

Вывод фамилий студентов, которые являются семейными

SELECT FAM FROM STUD WHERE SEM_POL=”S”

SEM_POL – признак семейного положения

Вывод фамилий студентов и номеров курса по алфавиту с другими именами колонок

SELECT FAM AS FAMILIA, CURS AS NUMBER_CURS;

FROM STUD ORDER BY FAM

Вывод фамилий студентов и номеров курса из таблицы STUD и из таблицы TEACHER для записей, у которых совпадают номера курсов.

SELECT S. FAM, S. KURS, T. KURS, T. FIO;

FROM STUD S, TEACHER T;

WHERE S. KURS=T. KURS

Соотнесенные подзапросы

Когда используются подзапросы, в SQL имеется возможность обратиться к внутреннему запросу таблицы в предложении внешнего запроса FROM с помощью соотнесенного подзапроса. При этом подзапрос выполняется неоднократно по одному разу для каждой записи таблицы основного запроса. Строка внешнего запроса, для которой внутренний запрос каждый раз будет выполняться – текущая строка. Процедуры, выполняемые соотнесенным подзапросом:

1.  выбор строки из таблицы во внешнем запросе

2.  сохранение значения текущей строки псевдонима, имя, которое определено в предложении FROM внешнего запроса.

3.  выполнение подзапроса. При этом везде, где найден псевдоним из внешнего запроса, используется значение из текущей строки – внешняя ссылка.

4.  оценка предиката внешнего запроса на основе результата подзапроса

Эта последовательность повторяется до тех пор, пока все строки не будут проверены.

Псевдонимы также часто необходимы для того, чтобы дать возможность ссылаться к той же самой таблице во внутреннем и внешнем запросах. Часто соотнесенный подзапрос используют на основе той же самой таблицы, что и основной запрос.

Найти все оценки по дисциплине со значениями выше средней по этой же дисциплине

SELECT * FROM USP FIRST;

WHERE OCENKA (SELECT AVG(OCENKA));

FROM USP SECOND;

WHERE SECOND. PNUM=FIRST. PNUM

Соотнесенные подзапросы очень близки к объединениям. Обе конструкции включают проверку каждой записи одной таблицы с каждой записью другой или с псевдонимом из той же таблицы. При этом большинство операций у них похожи.

Изменение существующих данных

Эта команда содержит ключевое слово UPDATE, где указывается имя используемой таблицы и предложение SET, которое определяет вносимое изменение для требуемого поля таблицы. При помощи команды UPDATE можно модифицировать данные из нескольких полей, а также использовать подзапросы. При этом нельзя в предложении FROM любого подзапроса модифицировать таблицу, к которой ссылается основная команда.

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

VIEW – некоторое подобие таблицы, содержание которой выбирается с помощью выполнения запроса. Причем при изменении значения в этих таблицах автоматически меняется их представление.

Представление создается командой CREATE VIEW, после которого указывается его имя, а далее следует запрос, формирующий тело представления. Поля представления могут иметь свои имена, полученные из имен полей основной таблицы. Представления могут также использовать подзапросы, в том числе и соотнесенные.

Вывести все оценки по дисциплине, которые больше средней оценки по этой дисциплине.

CREATE VIEW AVG OC AS;

SELECT * FROM USP FIRST;

WHERE OCENKA > (SELECT AVG (OCENKA)

FROM USP SECOND;

WHERE SECOND. PNUM=FIRST. PNUM)

__________________

Вывести фамилии, шифр, предмет, который студент сдал.

SELECT STUD. FAM, STUD. SNUM, USP. PNUM, USP. SNUM;

FROM STUD INNER JOIN USP;

ON STUD. SNUM=USP. SNUM

CREATE VIEW <имя>

(<имя поля>, <тип>[(<размер>,…)[,<имя поля>]])

/FROM ARRAY <массив>

INSERT INTO <имя файла>[(<поле1>[,<поле2>])]

VALUES <выражение1>[,<выражение2>]

Добавление записей в конец существующего файла, используя выражения, перечисленные после слова VALUES.

Если опущены имена полей, выражения будут записываться в последовательные поля базы данных в соответствии с ее структурой.

INSERT INTO <имя> FROM ARRAY <имя>

Индексы

Индекс – указатель на данные в таблице.

CREATE INDEX имя ON имя таблицы

Типы индексов:

простые – индекс, создаваемый по данным одного столбца таблицы

CREATE INDEX имя ON имя таблицы (имя столбца)

уникальные – используются не только для ускорения поиска данных, но и для обеспечения их целостности. Наличие уникального индекса позволяет ввести в таблицу дубликаты записи. Его можно создать только по тому столбцу таблицы, данные которого уникальны.

CREATE UNIQUE INDEX имя ON имя таблицы (имя столбца)

составные – индексы, составленные по значениям нескольких столбцов таблицы. При создании составного индекса следует учитывать вопросы производительности базы данных. Поскольку порядок столбцов в условии индекса может сильно влиять на скорость извлечения данных. Общее правило для повышения производительности – более ограничивающее значение должно идти первым. Но первым должен указываться столбец, наличие которого всегда предполагается в условиях выбора.

CREATE INDEX имя ON имя таблицы (имя столбца1, имя столбца2)

Использование составного индекса оказывается наиболее эффективным в том случае, когда соответствующие индексу столбцы часто одновременно используются в условиях запросов в выражении WHERE. Не рекомендуется создание индексов для таблиц с ключами очень большой длины, так как скорость работы такой таблицы снижается из-за больших объемов ввода/вывода.

FoxPro

Индексы предназначаются для создания первичных ключей, используемых для определения отношений между таблицами и условий целостности данных. В этом случае индексы должны быть уникальными.

Например:

Если для создания уникального индекса используется простой, значения поля должны быть неповторяющимися. Когда для создания уникального индекса используется составной, значения в каждом из полей, входящих в индекс, могут повторяться, но индексное выражение должно быть уникальным.

Regular – обычный. Создается индекс, в котором для каждой записи таблицы хранится значение индексного выражение. Если несколько записей имеют одинаковые значения индексного выражения, то каждое значение хранится отдельно и содержит ссылку на связанную с ним запись.

Unique – создается индекс, в котором хранятся только неповторяющиеся значения индексного выражения. Если 2 или более записей содержат одинаковые значения индексного выражения, то будет храниться только одно значение и ссылка на первую из записей с одинаковым значением индексного выражения

Candidate – создается уникальный индекс, который не содержит полей с пустыми значениями

Primary – создается уникальный индекс, который используется для связывания таблиц и определения условий целостности данных. Поля, входящие в первичный ключ, не должны допускать ввода пустых значений.

Cascade – при изменении значений полей первичного ключа или ключа кандидата в родительской таблице, автоматически осуществляется каскадное изменение всех соответствующих значений в дочерней таблице.

Restrict – не позволяет изменять значения полей первичного ключа или ключа кандидата в родительской таблице, если в дочерней таблице имеется хотя бы одна запись, содержащая ссылку на неизменяемую запись.

Ignore – позволяет изменить значение полей первичного ключа или ключа кандидата в родительской таблице независимо от существования связанных записей в дочерней таблице. Целостность данных при этом не поддерживается.

Для определения целостности данных используется построитель целостности данных, который содержит перечень всех установленных отношений между таблицами. Таблица содержит имена родительской, дочерней таблицы, индексов и связей.

Файловые структуры для хранения информации в базах данных

Классификация

Файлы:

прямого доступа последовательного доступа индексные

плотный индекс (индексно-прямые) В – деревья Индексно-последовательные

инвертированные списки взаимосвязанные файлы

·  с однонаправленными цепочками

·  с двунаправленными цепочками

Прямого доступа на устройствах прямого доступа. Имеют фиксированную длину записи, и обеспечивают более быстрый доступ. Адрес м. б. вычислен по номеру записи. Однако поиск по номеру неэффективен. Лучше искать по ключам, поэтому есть ф-я преобразования ключа в номер.(например – хеширование(рассказать))

С переменной длиной записи – последовательного доступа. Тогда конец записи отмечается специальным маркером, либо в начале каждой записи дана ее длина.

Плотный индекс: данные в области данных одинаковой длины. В индексной области все записи упорядочены по значению ключа.

Неплотный индекс – ключ указывает не на запись, а на страницу записей. Внутри страниц записи упорядочены.

Б деревья – построение индекса по индексу

Инвертированные списки: ведется поиск по вторичным ключам. Они м. б. одинаковыми. Инвертированный список – двухуровневая индексная структура. На первом уровне расположены значения вторичных ключей. На втором – блок указателей на записи с таким значением ключа. На третьем – собственно данные.

Деревья – ежику понятно.(LPTR, DATA, RPTR)

Файлы с однонаправленными и двунаправленными цепочками

Для моделирования отношений «один ко многим» и «многие ко многим» на файловых структурах используется принцип организации цепочек записей внутри файла и ссылки на номера записей для нескольких взаимосвязанных файлов.

Моделирование «один ко многим» с использованием однонаправленных цепочек

В этом случае связываются 2 файла F1 и F2. Предполагается, что одна запись в файле F1 связана с несколькими записями в файле F2. При этом F1 называется основным, а F2 – зависимым или подчиненным. Структура основного файла может быть условно представлена в следующем виде.

 

В подчиненном файле также к каждой записи добавляется специальный указатель. В нем хранится номер записи, которая является следующей в цепочке записей подчиненного файла. То есть каждая запись подчиненного файла делится на 2 области:

1. область указателя

2. область содержимого записи

 

Файл F1

Номер записи	Ключ и остальная запись	Указатель
1	Иванов	1
2	Петров	3
3	Сидоров	2
4	Андреев

Файл F2

Номер записи	Указатель на следующую запись	Содержимое записи
1	4	КПД-100
2	-	ВКСС-200
3	6	КПД-200
4	5	БЖД-300

Первая запись в файле F1 связана с цепочкой записей файла F2, которая начинается с записи №1. Следующая запись - №4, а последняя - №5. Таким образом Иванов ведет КПД в группе 100 и БЖД в группе 300.

Часто бывает необходимо просматривать цепочку подчиненных записей в двух направлениях – в прямом и обратном. В этом случае применяют двойные указатели. В основном файле первый указатель равен номеру первой записи в цепочке записей подчиненного файла, а второй – номеру последней записи. В подчиненном файле1 указатель равен номеру следующей записи в цепочке, а другой – номеру предыдущей записи в цепочке. Для первой и последней записи цепочки один из указателей пуст.

F1

Номер записи	Ключ и остальная запись	Указатель на первую запись	Указатель на последнюю запись
1	Иванов	1	5
2	Петров	3	6
3	Сидоров	2	2
4	Андреев

F2

Номер записи	Указатель на предыдущую запись	Указатель на следующую запись	Содержимое записи
1	-	4	КПД-100
2	-	-	ВКСС-200
3	-	6	КПД-200
4	1	5	БЖД-300
5	4	-
6	3	-

Один файл подчиненный или основной может быть связан с несколькими другими файлами. При этом для каждой связи моделируются свои указатели. Связь двух основных файлов F1 и F2 с одним связующим файлом F3 может быть промоделирована следующим образом:

F1, F2

Ключ

Содержимое записи

Указатель на файл

F3

Цепочки для файла F1

Содержимое записи

Цепочки для файла F2

Многоаспектный поиск

Особенности многоаспектного поиска. Здесь аргумент поиска содержит несколько атрибутов записей, не являющихся первичными ключами. В общем случае требуется найти все записи с определенными значениями, указанных в запросе атрибутов. Большой класс методов основан на принципе использования инверсных массивов. Различия между основным и инверсным массивами:

Каждая запись основного массива соответствует определенному объекту и содержит перечень признаков, характеризующих этот объект. Каждая запись инверсного массива соответствует определенному признаку и содержит перечень объектов, характеризующихся этим признаком. Количество инверсных массивов обычно равно числу признаков, по которым ведется поиск. Массивы могут быть упорядочены по значению соответствующего признака, и тогда в них возможен ускоренный поиск. В результате поиска по каждому инверсному массиву определяется множество объектов. Объекты каждого множества характеризуются определенным значением одного признака.

Пример: найти все детали, имеющие определенный цвет и вес

Аргумент поиска содержит 2 признака, поэтому надо создать 2 инверсных массива:

Прямой массив

Детали	Значения признаков (цвет, вес)
1	Ц1, В1
2	Ц1, В2
3	Ц2, В3
4	Ц2, В1

Инверсный массив 1 Инверсный массив 2

Цвет	Деталь
Ц1	1,2,11,15
Ц2	3,4,20

Цвет	Деталь
В1	1,4,16,11
В2	2,9,12
В3	3,10,19

Находится результат операции пересечения двух множеств. В целях экономии памяти в инверсных массивах могут храниться не сами записи, а указатели на адреса хранения этих записей

Объектно ориентированная модель

В этой модели при представлении данных имеется возможность иденитифицировать отдельные записи базы данных. Структура о-о БД может быть представлена как дерево, узлами которого являются объекты.

Свойства объектов описываются некоторыми стандартными типами или пользовательскими классами.

Логически структура похожа на структуру иерархической БД.

Основное отличие между ними состоит в методах уринзирования (?) данными.

Логическая структура ООБД: Рисунок какой-нибудь на тему:

База
Свойство	Тип	Значение

Суть в том, что поле внутри записи может быть классом, и содержать внутри сложную структуру.

Хранилище данных

В хранилище могут помещаться результаты транзакционных данных, также могут подвергаться конвертированию, чтобы обеспечить совместимость данных, полученных из других источников. Для обеспечения процесса отсечения и извлечения данных используются термины: Расслоение, Расщепление.

Хранимые данные можно модифицировать методами многомерного моделирования с использованием звездообразной схемы, состоящей из таблицы фактов, окруженной таблицами измерений.

Рисунок

Отношения между таблицей фактов и измерений должны быть простыми, чтобы все было понятно. М. б. таблица развертывания измерений