ПРАКТИЧЕСКОЕ ЗАНЯТИЕ № 4
«ИНДЕКСИРОВАНИЕ И СОРТИРОВКА ТАБЛИЦ»
Цель занятия: Научиться создавать индексы для таблиц базы данных, организовывать простую и сложную сортировку записей таблицы.
ИНФОРМАЦИОННАЯ ЧАСТЬ
Индексирование полей и записей в базе данных Access
При помощи индексов ускоряется сортировка и поиск записей. Индексы таблиц Microsoft Access используются так же, как и предметные указатели в книгах: при поиске данных выполняется их поиск в индексе. Индексы можно создавать по одному или нескольким полям. Составные индексы позволяют пользователю различать записи, в которых первые поля могут иметь одинаковые значения.
Выбор полей для индексирования
В основном, требуется индексировать поля, в которых часто осуществляется поиск, поля сортировки или поля, объединенные с полями из других таблиц в запросах. Однако индексы могут замедлить выполнение некоторых запросов на изменение, например, запросов на добавление, при выполнении которых требуется обновление индексов многих полей.
Поля первичного ключа таблиц индексируются автоматически, а поля с типом данных «Поле объекта OLE» индексировать нельзя. Для остальных полей индексирование используется, если выполняются следующие условия.
Поле имеет тип данных «Текстовый», «Числовой», «Денежный» или «Дата/время».
Предполагается выполнение поиска значений в поле.
Предполагается выполнение сортировки значений в поле.
Предполагается выполнение сортировки большого числа различных значений в поле. Если поле содержит много одинаковых значений, то применение индекса незначительно ускорит выполнение запросов.
Составные индексы
Если предполагается частое выполнение одновременной сортировки или поиска в нескольких полях, можно создать для этих полей составной индекс. Например, если в одном и том же запросе часто задаются условия для полей «Имя» и «Фамилия», то для этих двух полей имеет смысл создать составной индекс.
При сортировке таблицы по составному индексу Microsoft Access сначала выполняет сортировку по первому полю, определенному для данного индекса. Если в первом поле содержатся записи с повторяющимися значениями, то выполняется сортировка по второму полю, определенному для данного индекса, и так далее. В составной индекс можно включить до 10 полей.
Создание индекса
Перед созданием индекса необходимо решить, следует ли создать индекс для одного поля или составной индекс. Индекс для одного поля создается с помощью установки свойства Индексированное поле. В следующей таблице приведены возможные параметры свойства Индексированное поле.
Таблица 1- Параметры свойства "Индексированное поле"
Параметр свойства
|
Значение
|
Нет
|
Не создавать индекс для этого поля (или удалить существующий индекс)
|
Да (Допускаются совпадения)
|
Создать индекс для этого поля
|
Да (Совпадения не допускаются)
|
Создать уникальный индекс для этого поля
|
При создании уникального индекса невозможно ввести новое значение в определенном поле, если такое значение уже существует в том же поле другой записи. В Access уникальный индекс автоматически создается для первичных ключей, однако может понадобиться, чтобы создание значений, совпадающих со значениями в других полях, было невозможным. Например, можно создать уникальный индекс для поля, в котором содержатся серийные номера, чтобы двум продуктам не мог быть присвоен один и тот же серийный номер.
Создание индекса для одного поля
В области переходов щелкните правой кнопкой мыши название таблицы, в которой необходимо создать индекс, затем в контекстном меню выберите Конструктор.
Щелкните Имя поля для поля, которое следует индексировать.
В разделе Свойства поля откройте вкладку Общие.
В свойстве Индексированное поле щелкните значение Да (Допускаются совпадения), если следует разрешить повторяющиеся значения, или значение Да (Совпадения не допускаются), чтобы создать уникальный индекс.
Чтобы сохранить изменения, щелкните Сохранить на панели быстрого доступа или нажмите сочетание клавиш CTRL+S.
Создание составного индекса
Чтобы создать составной индекс, необходимо включить в него строку для каждого поля в индексе и поместить индекс только в самой первой строке. Все строки обрабатываются как часть одного индекса до тех пор, пока не будет обнаружена строка с другим названием индекса. Чтобы вставить строку, щелкните правой кнопкой мыши место, куда следует вставить строку, затем в контекстном меню щелкните команду Вставить строки.
В области переходов щелкните правой кнопкой мыши название таблицы, в которой необходимо создать индекс, затем в контекстном меню выберите Конструктор.
На вкладке Конструктор в группе Показать или скрыть щелкните Индексы.
Появится окно «Индексы». Измените размеры этого окна, чтобы отображались пустые строки и свойства индекса.
В первой пустой строке столбца Индекс введите имя индекса. Для индекса можно использовать либо имя одного из индексируемых полей, либо другое подходящее имя.
В столбце Имя поля щелкните стрелку, затем щелкните первое поле, которое следует использовать в индексе.
Следующую строку столбца Индекс оставьте пустой, затем в столбце Имя поля укажите второе индексируемое поле. Повторите этот шаг для всех полей, которые необходимо включить в индекс.
Чтобы изменить порядок сортировки значений полей, в столбце Порядок сортировки окна «Индексы» щелкните По возрастанию или По убыванию.
В окне Свойства индекса окна Индексы установите свойства индекса для строки в столбце Имя индекса, содержащем индекс. Установите свойства в соответствии со следующей таблицей.
Таблица 2-Свойства индексов
Надпись
|
Значение
|
Первичный
|
Если Да, то индекс является первичным ключом.
|
Уникальный
|
Если Да, то каждое индексируемое значение должно быть уникальным.
|
Пропуск пустых полей
|
Если Да, то записи с пустыми значениями в индексируемых полях будут исключены из индекса.
|
Чтобы сохранить изменения, щелкните Сохранить на панели быстрого доступа.
Сортировка записей
Существует два вида сортировки, которые можно выполнить: простая сортировка и сложная сортировка.
Простая сортировка. При сортировке в режиме формы, в режиме таблицы выполняется простая сортировка, то есть все записи поля сортируются по возрастанию или по убыванию (но не в том и другом порядке сортировки одновременно).
Сложная сортировка. Если нужно провести сортировку записей в режиме конструктора запроса, в окне расширенного фильтра, в режиме конструктора отчета, в режиме конструктора страницы, в режиме сводной диаграммы или сводной таблицы, можно выполнить сложную сортировку. Это означает, что по некоторым полям допускается сортировка по возрастанию, а по другим полям сортировка по убыванию.
Сортировка таблицы, запроса или формы
Укажите поля для сортировки. Чтобы выполнить сортировку по двум или более полям, укажите, какие из полей будут использоваться в качестве внутренних и внешних полей сортировки.
Щелкните правой кнопкой мыши столбец или элемент управления, соответствующий внутреннему полю, и выберите одну из команд сортировки. Команды зависят от типа данных, содержащихся в выбранном поле.
Примечание. При сортировке данных по полю с логическим типом данных значения «Да», «Истина» или «Включено» считаются «выбранными», а значения «Нет», «Ложь» или «Отключено» — «снятыми». По умолчанию этот тип поля отображается в виде флажка, но пользователь может настроить отображение поля в виде текстового поля или поля со списком. При смене вида отображения поля на текстовое поле или поля со списком сортировка происходит по признакам «выбрано» или «снято».
 Числовой, Денежный, Счетчик
 Текстовый, Поле MEMO, Гиперссылка
 Логический
 Дата/время
Повторите предыдущий шаг для каждого поля сортировки, включая последнее внешнее поле сортировки.
Записи переупорядочиваются в соответствии с порядком сортировки.
Если текстовое поле содержит значения Null и пустые строки, при сортировке по возрастанию сначала отображаются записи со значением Null, потом записи с пустыми строками, а затем записи с непустыми значениями.
Если значение в поле начинается со специального знака , такого как дефис, скобки или другого символа , при сортировке по возрастанию соблюдаются следующие правила:
Значения, начинающиеся с пробела, отображаются перед алфавитно-цифровыми значениями.
Значения в скобках отображаются после значений, начинающихся с пробелов, но перед алфавитно-цифровыми значениями.
Значения, начинающиеся со знака «минус» (-), отображаются перед значениями со знаком «плюс» (+).
Для всех других знаков порядок сортировки определяется на основе кодов ASCII этих знаков. Например, для знака доллара ($) используется код 36, а для знака равенства (=) — 61, поэтому значения, начинающиеся с $, отображаются перед значениями, начинающимися с =.
Для переопределения этого порядка можно проигнорировать первый знак для всех значений в этом поле. Этот метод удобно использовать, если значения в поле всегда начинаются с одного специального знака, например знака «минус (-)», или с одинакового количества специальных знаков — то есть заранее известно, сколько знаков игнорировать. Если количество знаков, которое необходимо игнорировать, изменяется, можно определить специальный (пользовательский) порядок сортировки.
Имейте в виду, что нельзя удалить порядок сортировки только в одном поле. Чтобы отменить сортировку во всех полях сортировки, на вкладке Главная в группе Сортировка и фильтр нажмите кнопку Очистить все сортировки, а затем примените необходимый порядок сортировки.
Дополнительные сценарии сортировки
Если применяются команды сортировки, для определения порядка записей используется тип данных для поля и полные значения каждого поля. Однако иногда требуется отсортировать текстовые значения, такие как понедельник, вторник и т.д. не в алфавитном, а в специальном порядке, или отсортировать текстовое поле, содержащее IP-адреса. Если требуются особые способы сортировки, для которых не предусмотрены команды сортировки, см. следующие разделы:
Если новая форма или отчет основываются на таблице или запросе, порядок сортировки которых был сохранен вместе с ними, то он наследуется и новой формой или отчетом.
В одном или нескольких полях результатов запроса или расширенного фильтра может быть отсортировано до 255 знаков.
Если бланк запроса или фильтра содержит знак «звездочка» из списка полей, то определить в нем порядок сортировки можно, только добавив в него поля, которые необходимо отсортировать.
Для сортировки значений дат и времени от более ранних к более поздним используйте порядок сортировки по возрастанию. Для сортировки от более поздних значений к более ранним используйте сортировку по убыванию.
Числа, хранящиеся в текстовых полях, сортируются как строки знаков, а не как числовые значения. Поэтому для выполнения их сортировки в числовом порядке все текстовые строки должны иметь одинаковую длину. Например, результатом сортировки по возрастанию текстовых строк «1», «2», «11» и «22» будет «1», «11», «2», «22». В начало строк с меньшим количеством знаков следует добавить незначащие нули, например: «01», «02», «11», «22». Еще одним решением данной проблемы для полей, не содержащих значения Null, будет использование функции Val для сортировки числовых значений строк. Например, если столбец «Возраст» является текстовым полем, содержащим числовые значения, то для расположения записей этого столбца в должном порядке можно указать в ячейке Поле функцию Val([Возраст]), а в ячейке Сортировка указать нужный порядок сортировки. Если числовые значения или значения дат хранятся только в текстовом поле, рекомендуется изменить тип данных этого поля таблицы на числовой, денежный или даты/времени. После выполнения сортировки по этому полю числа или даты будут располагаться в надлежащем порядке без ввода дополнительных нулей.
ИСПОЛНИТЕЛЬНАЯ ЧАСТЬ
Отсортируйте таблицу «Студент» по убыванию Даты рождения. Скопируйте полученную таблицу в документ MS Word.
Отсортируйте таблицу «Студент» по возрастанию Номера группы. Скопируйте полученную таблицу в документ MS Word.
-
Добавьте в таблицу «Экзамен» следующие записи:
Таблица 3-Экзамен
|
№ студ. билета
|
Код предмета
|
Семестр
|
Допуск
|
Оценка
|
Дата сдачи
|
А23
|
8
|
2
|
Да
|
5
|
22.07.2007
|
С32
|
1
|
1
|
Нет
|
|
|
Ю17
|
8
|
3
|
Нет
|
|
|
А23
|
2
|
3
|
Нет
|
|
|
А23
|
1
|
3
|
Да
|
4
|
22.07.2007
|
А23
|
3
|
1
|
Да
|
5
|
21.09.2007
|
Отсортируйте в таблице «Экзамен» записи по возрастанию Кода предмета и семестра. Скопируйте полученную таблицу в документ MS Word.
Переиндексируйте таблицу «Экзамен» (Конструктор- Показать или скрыть-Индексы) следующим образом, чтобы сортировка и поиск записей сначала осуществлялись по полю Семестр (по возрастанию), затем по Коду предмета (по возрастанию), и в последнюю очередь по Номеру студенческого билета (по убыванию).
Д
Рисунок 1 Переиндексирование таблицы Экзамен
ля этого откройте таблицу «Экзамен» в режиме конструктора, выберите команду Индексы. Измените порядок следования полей в столбце Имя поля и порядок сортировки для них (рис.1). Откройте таблицу «Экзамен» в режиме просмотра записей, скопируйте таблицу в MS Word.
В конструкторе таблиц для таблицы «Зачет» отмените Первичный ключ таблицы.
Добавьте следующие строки в таблицу «Зачет»:
Таблица 4-Зачет
|
№ студ. билета
|
Код предмета
|
Семестр
|
Допуск
|
Оценка
|
Дата сдачи
|
С32
|
11
|
2
|
Да
|
4
|
|
Ш8
|
11
|
3
|
Да
|
4
|
|
Ш8
|
4
|
2
|
Да
|
3
|
|
С32
|
4
|
1
|
Да
|
5
|
|
С32
|
1
|
2
|
Да
|
5
|
|
Ш8
|
4
|
1
|
Да
|
5
|
|
П15
|
4
|
1
|
Да
|
5
|
|
П15
|
11
|
2
|
Да
|
5
|
|
П15
|
1
|
3
|
Да
|
5
|
|
Ш8
|
11
|
2
|
Да
|
3
|
|
Ш8
|
1
|
3
|
Да
|
3
|
|
Ш8
|
1
|
2
|
Да
|
3
|
|
Создайте для таблицы «Зачет» новый индекс- ОЦЕНКА, в котором Оценки отсортированы по убыванию, а № студенческого билета по возрастанию. Для этого откройте таблицу в режиме конструктора, выберите команду меню Индексы. Удалите все строки, которые там есть. Название индекса укажите в первом столбце. В столбце Имя поля выберите поля индексирования, в третьем столбце измените порядок сортировки (рис.2).
П олученную таблицу скопируйте в документ MS Word.
П
Рисунок 2 Создание индекса Оценка для таблицы Зачет
ереиндексируйте таблицу «Зачет» таким образом, чтобы Оценка была отсортирована по убыванию, Код предмета по возрастанию, Семестр- по убыванию. Индекс назовите – Семестр. Результат скопируйте в MS Word.
Отмените для таблицы «РодителиСтудентов» ключевое поле. Создайте для таблицы новый индекс- Родители, в котором КодРодителя отсортированы по убыванию, а № студенческого билета по возрастанию. Результат скопируйте в текстовый документ.
П ри помощи расширенного фильтра создайте сложную сортировку записей в таблице «Преподаватели», в которой производится сортировка записей по полю Стаж работы по убыванию, а по полю Категория- по возрастанию.
Д
Рисунок 3 Выбор расширенного фильтра
ля этого откройте таблицу в режиме Таблицы, выберите команду меню Дополнительно(Параметры расширенного фильтра)-Расширенный фильтр (рис.3).
Появится конструктор. Перенесите в нижнюю часть конструктора из таблицы поля СтажРаботы и Категория, установите для них порядок сортировки (рис.4). Затем нажмите на панели инструментов кнопку Дополнительно (Параметры расширенного фильтра)- Сохранить как запрос  и сохраните данный расширенный фильтр в виде запроса Стаж_Категория. Результат запроса при этом будет находиться в объектах Запросы. Скопируйте результат в MS Word.
При помощи расширенного фильтра задайте сортировку таблицы «Студент» по Группе- по убыванию, по Дате рождения- по возрастанию. Результат скопируйте в MS Word. Запрос назовите «ПоГруппе»
П
Рисунок 4 Конструктор расширенного фильтра
ри помощи расширенного фильтра задайте сложную сортировку таблицы «Предмет» по полям КодСпециальности по убыванию, КоличествоЧасов по возрастании. Сохраните его как запрос «СпецЧасы».
КОНТРОЛЬНЫЕ ВОПРОСЫ
Что такое индекс и для чего применяется индексирование полей?
Какие поля таблицы индексируются автоматически?
Какие типы данных нельзя индексировать?
В каком порядке выполняется сортировка записей в сложных индексах?
Сколько существует видов сортировки, и в чем их отличие?
Каким образом сортируются записи типа Дата\время?
Как сортируются числа, хранящиеся в текстовых полях?
Каким образом сортируются пустые записи?
|
