Тема 1. Разработка БД информационной системы «Учебный процесс».
Необходимые знания.
База данных – именованная и организованная совокупность данных, отражающая состояние объектов и их отношений в рассматриваемой предметной области, являющаяся программно управляемой.
Предметная область – часть реального мира, которая должна быть адекватно в полном информационном объеме представлена в БД.
Концептуальная схема должна содержать:
· Объекты и их атрибуты;
· Связи между объектами;
· Ограничения, накладываемые на данные;
· Семантическую информацию о данных;
· Обеспечение безопасности и поддержки целостности БД.
Реляционная модель - совокупность взаимосвязанных двумерных таблиц, называемых отношениями.
· Предметная область рассматривается как некоторая совокупность реальных объектов (сущностей) и разного рода связей между ними.
· Каждый объект обладает определенным набором свойств (атрибутов).
· Описание предметной области может включать не только физические объекты, но и сведения о процессах и абстракциях
В реляционной модели определены следующие понятия:
· Объект – сущность – отношение - таблица,
· Экземпляр объекта - кортеж – запись – строка,
· Атрибут – поле – столбец,
· Элемент данных – ячейка,
· Домен – множество допустимых значений данного атрибута. Домены могут отличаться для каждого из атрибутов, но несколько атрибутов могут определяться на одном и том же домене;
· Кардинальное число – количество кортежей
· Степень отношения – количество атрибутов
· Первичный ключ – поле, значения которого однозначно идентифицируют запись и данные в этом поле не могут повторяться.
· Составной первичный ключ – это первичный ключ, состоящий более чем из одного поля
· Внешний ключ – это поле подчиненной таблицы, чьи значения совпадают с имеющимися значениями первичного ключа другой таблицы, которая является главной в этой связи.
· Схема данных – это группа связанных таблиц.
Свойства отношения:
· Отношение имеет уникальное имя
· Каждый атрибут имеет уникальное имя, его значения берутся из одного и того же домена.
· Каждый компонент записи является простым, атомарным значением, не состоящим из группы значений. (Нельзя заменить значение атрибута другим отношением, тогда это была бы сетевая или иерархическая модель).
· Последовательность атрибутов несущественна.
· Последовательность записей несущественна
Все записи должны быть различны.
Одним из важнейших понятий, имеющих практическое значение, является понятие целостности базы данных.
Целостность – это правильность данных в любой момент времени.
Но СУБД не может контролировать правильность каждого отдельного значения, вводимого в базу данных (хотя каждое значение можно проверить на правдоподобность). Например, невозможно проверить при вводе данных оценка 5 или 4 на самом деле была поставлена студенту, но можно запретить ввод какого-либо другого значения поля «оценка», кроме значений 2,3,4,5.
Выделяют три группы правил целостности:
Целостность по сущностям.
1. Отсутствие записей дубликатов (наличие первичного ключа это гарантирует)
2. Отсутствие полей с множественными атрибутами (обеспечивается нормализацией)
3. Не допускается, чтобы первичный ключ, принимал неопределенное значение.
Целостность по ссылкам.
Для любой записи с конкретным значением внешнего ключа должна обязательно существовать запись связанной таблицы с соответствующим значением первичного.
Целостность, определяемая пользователем.
Для любой конкретной базы данных существует ряд дополнительных специфических правил, которые относятся к ней одной и определяются разработчиком. Чаще всего контролируется:
· уникальность тех или иных атрибутов,
· диапазон значений (экзаменационная оценка от 2 до 5)
· принадлежность набору значений (пол "М" или "Ж").
Одним из методов проектирования баз данных является метод нормализации, предложенный Кодом. Этот метод мы и будем использовать.
Нормализация – это процедура устранения нежелательных функциональных зависимостей, аномалий, пошаговый обратимый процесс замены данной схемы другой схемой, в которой таблицы имеют более простую и регулярную структуру, при этом их количество может увеличиться.
Нормализация основана на концепции нормальных форм, которых всего шесть. Рассмотрим первые три:
· 1НФ – все значения полей таблицы должны быть атомарными, а все записи уникальными. (Любая реляционная таблица по определению уже в 1НФ);
· 2НФ – таблица находится в 1НФ и любое не ключевое поле однозначно идентифицируется полным набором ключевых полей. (2 НФ применима только для таблицы с составным ключом);
· 3НФ - таблица находится во 2НФ и все ее не ключевые поля зависят только от первичного ключа.
На практике, если таблица находиться в 3НФ, то дальнейшую нормализацию можно и не проводить. Таблица в 1НФ подлежит нормализации.
Реляционная БД - совокупность взаимосвязанных двумерных таблиц.
Связи бывают 1:1 (один к одному), 1:М (Один ко многим) и М:N (Много ко многим).
ПРАКТИЧЕСКОЕ ЗАДАНИЕ.
Проектирование базы данных.
Мы будем проектировать методом нормализации базу данных для информационной системы «Учебный процесс на факультете». Создаваемая информационная система должна обеспечивать потребности администрации факультета в оперативной, всеобъемлющей и достоверной информации о ходе учебного процесса, текущей и итоговой успеваемости студентов, формировать списки студентов для перевода на следующий курс или для отчисления в случае неуспеваемости, а также позволять студентам получать необходимую им информацию о преподавателях, дисциплинах и личной успеваемости.
Исследуйте предметную область.
Первым этапом проектирования базы данных, как известно, является изучение предметной области. В качестве учебного примера рассмотрим предметную область, которая вам должна быть хорошо известна,– учебный процесс.
Несмотря на то, что у Вас уже есть некоторое представление об этой предметной области, прочитайте внимательно ее описание, предложенное ниже.
Список преподавателей кафедры
Название кафедры_____________
Код кафедры____________
Телефон________________
Заведующий_______________________
| Табельный номер | ФИО | Ученая степень | Ученое звание | Должность |
План проведения занятий в группе
Группа №________ Семестр____________(текущий)
| Название предмета | Код предмета | ФИО преподавателя | Табельный номер преподавателя | Вид занятия | Часы |
Экзаменационная ведомость
Название предмета_______________ Группа №_______ Преподаватель _____________
Вид сдачи ___________________ Дата_____________________
| № п\п | ФИО студента | Оценка | Подпись преподавателя |
2. Разработайте реляционную модель рассмотренной предметной области. Для этого необходимо:
1. На основании представленных документов и описания предметной области, выделить основные информационные объекты.
2. Для каждого объекта определить составляющие его структуру атрибуты, их типы и необходимые ограничения. Результаты оформить в виде таблицы (См. табл. 1. «Объекты и атрибуты».).
3. Для каждого объекта выделить ключевые атрибуты.
4. Определить связи между объектами и типы связей.
5. Построить в тетради реляционную модель.
6.Нормализовать таблицы и построить схему данных.
3. Средствами СУБД ACCESS физически реализуйте разработанную модель - создайте базу данных «Учебный процесс». Для этого рекомендуется:
· создать сначала главные таблицы, определив типы полей, а также все возможные и необходимые свойства полей и, конечно, поле первичного ключа в каждой таблице;
· создать подчиненные таблицы, определив типы полей, а также все возможные и необходимые свойства полей, не забывая замечательное свойство «Подстановка» для полей внешних ключей;
· создать схему данных, обеспечивая целостность данных при определении логических связей.
· осуществить загрузку данных (для успешного выполнения в дальнейшем запросов в каждой таблице должно быть не менее 10 записей, а в таблице, где будут храниться сведение по успеваемости, - не менее 20 записей)
Таблица 1. Объекты и атрибуты.
| Объект | Атрибут | Тип | Первичный ключ |
| Кафедра | Код кафедры | Счетчик | Код кафедры |
| Название | Текст (30 симв.) | ||
| Комната | Текст (4 симв.) | ||
| Телефон | Текст (13 симв.) | ||
| Заведующий | Текст (20 симв.) | ||
| Группа | Далее заполните самостоятельно… | … |
4. Выполните проверки поддержания целостности в созданной БД:
1. проверку целостности при изменении значений связанных полей в таблицах:
• откройте таблицу ГРУППА и измените значение ключевого поля Номер группы в одной первой записи;
• убедитесь, что во всех записях подчиненной таблицы СТУДЕНТ изменилось значение в поле Номер группы для студентов этой группы;
• если этого не произошло, перейдите в окно Схема данныхи измените соответствующую связь, установив флажок Каскадное обновление связанных полей;
• проверьте наличие такого флажка в других подчиненных таблицах.
2. проверку целостности при добавлении записи в подчиненную таблицу;
3. проверку целостности при удалении записи в главной таблице;
4. используя Мастер анализа таблиц (Table Analyzer Wizard), выполните анализ таблицы ПРЕПОДАВАТЕЛИ с целью выделения повторяющихся данных в таблицу-справочник;
5. Внесите соответствующие изменения в разработанную вами в тетради схему данных.
5. Проверьте полученные знания, выполнив тестовые задания. (См. стр. Тестовые задания. Тема 1).
Необходимые знания.
Типы запросов:
Запрос на выборку – выбирает данные из всех указанных в бланке запроса таблиц, как без условия, так и по написанному условию. Следите за тем, чтобы все указанные в запросе таблицы были связаны! При задании условия отбора в бланке запроса в строке Условие отбора можно указывать как конкретное значение, так и параметр. Параметр позволяет сделать запрос более универсальным, т.к. открывающееся при запуске такого запроса диалоговое окно позволит ввести любое значение, участвующее в условии отбора по этому полю. Такой запрос называется запросом с параметром или параметрическим. Имя параметра запроса задается непосредственно в строке условие отбора вместо конкретного значения текстом в квадратных скобках. Параметров в одном запросе может быть несколько. Примеры запросов на выборку простого и параметрического представлены на рис. 1 и 2.
В запросе над полями могут выполняться вычисления. При создании запроса с вычислениями формула записывается в пустое поле строки Поле также как оператор присваивания – в левой части указывается имя поля для будущего результата вычислений, затем ставится двоеточие, справа от него записывается само выражение, которое может состоять из имен полей в квадратных скобках, встроенных функций, констант, параметров, знаков арифметических операций. Например, см. рис. 3.
В запросе на выборку можно также применять групповые операции для полей, где имеются повторяющиеся значения. Такой запрос, как правило, содержит поле, по которому нужно группировать и поле, к данным которого применяется та или иная статистическая функция. Для создания такого запроса следует предварительно щелкнуть по кнопке ∑ на панели инструментов. Пример см. рис.4.
К запросам действия относятся запросы на обновление, удаление, добавление данных и запросы на создание таблицы.
Запрос на выборку является основой всех других видов запросов.
Прежде чем создавать и выполнять запрос-действие, создайте аналогичный запрос на выборку, убедитесь в правильности полученного результата и только после этого можно менять тип запроса на нужный и запускать его на выполнение. Тип запроса меняется в режиме конструктора через выбор из открывающегося списка типов запросов.
Запрос на обновление изменяет значения в выбранном поле на новое как для всех записей, тогда условие задавать не нужно, так и для выбранных по условию, которое можно задавать как для изменяемого поля, так и для любого другого см. рис.5.
При использовании в запросе на обновление таблиц со связью 1:М, можно обновить только данные полей таблицы со стороны «М». При попытке изменить поле внешнего ключа возможно нарушение целостности базы данных.
Будьте очень внимательны при выполнении запросов на обновление. Если запрос выполнен, отменить его уже нельзя, а вернуть данным прежние значения часто весьма проблематично.
Запрос на удаление удаляет записи из одной или нескольких связанных таблиц. Если при создании связи, установлен флажок Каскадное удаление связанных записей, то удаление записей из главной таблицы повлечет за собой удаление записей из подчиненной таблицы по этой связи. Если же флажок не установлен, то при попытке удаления записей из главной таблицы возможно нарушение целостности базы данных и такое удаление не выполнится.
Будьте еще более внимательны при выполнении запросов на удаление! Помните, что если запрос на удаление выполнен, то отменить его результат уже нельзя!
Запрос на удаление без заданного условия отбора может удалить все записи из указанных таблиц!
Запрос на добавление выполняет добавление записей в таблицу базы данных из таблицы результата запроса, следовательно, поля добавляемых записей должны соответствовать полям таблицы, куда производится добавление. В запросе может быть меньше полей, чем в таблице-адресате, если на пропущенные поля не наложено требование обязательности заполнения. Ключевые поля таблицы-адресата обязательно указывать в бланке запроса. Запрос на добавление выполняет добавление записей в таблицу как текущей базы данных, тогда в диалоговом окне Добавление указывается только имя таблицы-адресата, так и в таблицу другой базы данных, тогда необходимо кроме имени таблицы еще указывать имя файла базы данных, где находится таблица-адресат. Пример см. рис.6.
Запрос на создание таблицы обычно используется, когда нужно сохранить результат какого-то запроса в виде таблицы. Запросом на создание таблицы можно создать таблицу, как в текущей, так и в любой другой базе данных, в последнем случае нужно кроме имени создаваемой таблицы указать также имя файла этой базы данных.
ПРАКТИЧЕСКОЕ ЗАДАНИЕ.
Создание запросов средствами СУБД ACCESS
1. Создайте следующие запросы на выборку:
1. Получить сведения об оценках всех студентов по всем дисциплинам с указанием фамилии студента и названия дисциплины.
2. Изменить созданный запрос, упорядочив по алфавиту фамилии студентов.
3. Получить сведения об оценках двух конкретных студентов по всем предметам.
4. Получить сведения об оценках любого студента по любому предмету (запрос с параметром).
5. Узнать, кто из преподавателей ведет занятия по математике в 423 группе.
6.Изменить запрос, сделав его параметрическим, так, чтобы можно было получать сведения для любой группы и для любого предмета.
Необходимые знания.
Форма - это объект MS ACCESS, облегчающий восприятие информации, используемый для ввода, просмотра и корректировки данных с элементами автоматизации, это пользовательский интерфейс;
Источником данных для формы могут быть таблицы и запросы.
Разделы формы.
· Сведения в форме могут быть разбиты на разделы. Все формы содержат область данных, но форма также может включать разделы заголовка формы, верхнего колонтитула, нижнего колонтитула и примечания формы. Каждый раздел формы имеет свое предназначение и печатается в определенном порядке.
· В режиме конструктора разделы представлены в виде полос, и каждый содержащийся в отчете раздел представлен один раз. В напечатанном отчете разделы могут повторяться неоднократно. Способ отображения сведений в каждом разделе определяется расположением элементов управления, таких как надписи и поля.
Способы создания форм:
Конструктор
Мастер форм
Автоформа:
· в столбец,
· ленточная,
· табличная;
· Диаграмма;
· Сводная таблица
Последовательность действий при создании формы:
· Открыть окно БД
· Выбрать объект Формы
· Выбрать Создать
· В окне Новая форма выбрать способ создания
· Выбрать источник данных
· Нажать кнопку ОК
Структура формы в режиме конструктора
· В заголовке формы отображаются сведения, которые остаются неизменными для каждой записи, такие как название формы. Заголовок формы выводится сверху на экране в режиме формы и сверху на первой странице при печати формы.
· В верхнем колонтитуле отображаются сведения, такие как название формы и заголовки столбцов, которые печатаются сверху на каждой печатной странице. Верхние колонтитулы выводятся только в печатных формах.
· В области данных отображаются записи. Допускается вывод только одной записи на экране или странице или вывод стольких записей, сколько уместится на экране или странице. Здесь же можно размещать формулы, если требуется выполнить вычисления по данным каждой записи.
· В верхнем колонтитуле отображаются сведения, такие как дата или номера страниц, которые печатаются снизу на каждой печатной странице. Нижние колонтитулы выводятся только в печатных формах.
· В примечании формы отображаются элементы, которые остаются неизменными для каждой записи, такие как кнопки или инструкции по работе с формой. Здесь же можно размещать формулы, если требуется выполнить подведение итогов. Примечание формы выводится снизу на экране в режиме формы и снизу на первой странице при печати формы.
Пользователь имеет возможность скрыть раздел или изменить его размеры, добавить рисунок или задать цвет фона раздела. Можно также изменять свойства, определяющие, как будет печататься содержимое раздела.
Подчиненные формы.
Многотабличные формы – могут состоять из основной части и одной или нескольких подчиненных, включаемых форм. Удобно создавать с помощью мастера.
Можно явно включить подчиненную форму, а можно включать ее кнопкой. В первом случае в окне Создание форм установите флажок Подчиненные формы, о втором – Связанные формы.
В любую форму можно добавить подчиненную форму при этом можно использовать ранее созданную форму или создать ее по ходу дела.
Для встраивания подчиненной формы в существующую в режиме конструктора нужно:
· нажать на панели элементов кнопку «Подчиненная форма / отчет» при этом отключить кнопку Мастера Элементов на этой же панели;
· установить курсор на место размещения создаваемого объекта в форме и протащить до нужного размера;
· свободный элемент нужно связать с формой, которая будет подчиненной.
Если нужной подчиненной формы нет, то можно ее создать, используя мастер, который должен быть включен, т.е. сначала нажать кнопку Мастер, затем Подчиненная форма/Отчет на панели элементов.
В форме можно выполнять вычисления.
· Если нужно вычислить что-то для каждой записи, то формула должна размещаться в области данных, если нужно вычислить итоговое значение, то формула должна размещаться в области примечаний, например: = AVR([Цена]) – средняя цена товаров..
· Для создания формулы используйте кнопку Поле, затем в элемент управления Свободный введите нужное выражение на вкладке Данные/Данные, можно использовать Построитель выражений, например, для вычисления стоимости работ по проекту в день следует записать формулу = [Стоимость_проекта]/[Срок_выполнения].
· Элементы управления из области примечаний не отображаются при просмотре подчиненной формы, если в ее свойстве Режим по умолчанию установлено значение таблица.
Ограничение доступа к данным через форму.
Для защиты данных от изменения используется свойство Блокировкадля любого поля формы.
Установить курсор в рамке поля, затем выберите в контекстном меню, Свойства, Данные, Блокировка, Да. Поле будет доступно только для чтения.
Чтобы все записи были доступны только для чтения, нужно в свойствах всей формы на вкладке Данные в списках Разрешить добавление, Разрешить удаление, Разрешить изменение, выбрать Нет.
Тоже можно сделать, выбрав свойство Тип набора записей \ Статический набор.
Свойство «Блокирование записей» - нужно в сетевом варианте.
Свойство Ввод данных \ Да – разрешает только ввод новых данных, просмотр невозможен, при открытии формы видна только пустая запись.
Кнопочная форма.
Содержит одну или несколько элементов Кнопка, выбор которой вызывает определенное действие над объектом БД, например запуск запроса, открытие формы и т.д. С кнопкой может быть связан запуск макроса.
Создается с помощью мастера создания.
Чаще всего используется для создания главного меню.
Может располагаться в любой форме.
Отчет - это объект MS ACCESS, предназначенный в основном для печати, - это форматированное представление данных.
Отчет может быть достаточно сложной структуры, обеспечивающей вывод взаимосвязанных данных из разных таблиц, их группировку, а так же выполняющий вычисления данных.
Перед созданием отчета необходимо определить:
· Цель отчета (для кого и зачем);
· Данные, которые будут включены в отчет, и из какой таблицы (запроса) они будут получены;
· Форму отчета;
· Нужно ли выполнять сортировку и группировку данных и по каким полям;
· Нужно ли выполнять вычисления и по каким формулам
Средства ACCESS позволяют создать макет отчета, по которому может быть получен выходной печатный документ.
В макете определяются состав и содержание отчета, размещения в нем данных, выводимых из полей таблиц, запросов БД, и вычисляемых реквизитов, поля, по которым нужно выполнять группировку.
Для каждого уровня группировки определяются заголовки и примечания, вычисляемые итоговые значения.
Оформляются заголовки и подписи реквизитов, и определяется порядок вывода данных
При создании отчетов все очень похоже на создание форм
СТРУКТУРА ОТЧЕТА:
· Заголовок отчета - Выводится только в начале
· Верхний колонтитул - Выводится вверху каждой страницы
· Область данных - Содержит записи таблиц и запросов
· Нижний колонтитул - Выводится внизу каждой страницы
· Примечание отчета - Выводится только в конце отчета
При группировке в окно конструктора отчетов могут быть добавлены разделы «Заголовок группы» и «Примечание группы».
В заголовке группы, размещаются поля, по которым осуществляется группировка.
В примечание группы – выражения для подведения итогов по группе.
Возможно до 10 уровней группировки.
Элементы отчета, которые можно использовать кроме полей, размещены на панели элементов. Работать с элементами и их свойствами также как и в конструкторе форм.
Макрос представляет набор макрокоманд, который создается для автоматизации часто выполняемых задач. Группа макросов позволяет выполнить несколько задач одновременно. Для создания простого макроса необходимо выполнить следующее:
· В главном окне БД выбрать объект Макрос,
· Выбрать команду Создать,
· В открывшемся окне из списка макрокоманд выбрать последовательно необходимые макрокоманды,
· Для каждой макрокоманды необходимо задать ее свойства,
· В завершении создания макроса ему необходимо присвоить имя.
ПРАКТИЧЕСКОЕ ЗАДАНИЕ.
Создание форм, отчетов и макросов средствами СУБД ACCESS
Создайте следующие формы.
1. В таблицу КА ФЕДРЫ добавьте два новых поля Нагрузка (количество часов) и ЧислоПреподавателей и заполните их. На основании измененной таблицы создайте однотабличную форму, в которой среди прочих данных по кафедрам вычислялась и выводилась бы средняя нагрузка преподавателей по каждой кафедре. Вычислите суммарную нагрузку преподавателей всех кафедр.
2. Используя Мастер создания форм, создайте на основании связанных таблиц КА ФЕДРЫ и ДИСЦИПЛИНЫ две многотабличные формы подчиненную и связанную.
3. Перейдите в режим конструктора и внесите свои коррективы в созданную связанную форму, например, сделайте подпись к кнопке, озаглавьте форму и измените фон.
4. Создайте в многотабличной форме КАФЕДРЫ-ДИСЦИПЛИНЫ вычисляемое поле для расчета количества дисциплин на каждой кафедре, используя формулу = [Имя подчиненной формы].[Form] ! [Имя вычисляемого элемента в подчиненной форме]
5.. Создайте подчиненную форму СПИСОК ГРУППЫ. Реализуйте в ней возможность вычисления количества студентов в каждой группе.
Создайте следующие отчеты.
1. Отчет со списками студентов по группам, в котором должно быть:
• в заголовке отчета должна выводиться текущая дата;
• вывод названия отчета на каждой странице
• в заголовке группы - номер группы;
• список студентов упорядочен по алфавиту;
• вывод нумерации страниц;
• в каждой группе вычислить и отобразить в отчете количество студентов.
2. На основании соответствующего запроса создать отчет «Приказ на отчисление» с указанием ФИО и группы отчисляемого студента.
Создайте следующие макросы.
1.Макрос, который откроет форму для оформления нового студента, развернет ее на весь экран и установит на новую запись.
2.Макрос, который автоматически выполнит всю процедуру отчисления студентов:
· Запуск запроса на добавление в таблицу «Архив» сведений об отчисляемых студентах
· Запуск запроса на удаления из БД сведений об отчисляемых студентах.
3.Создать форму-меню с кнопками:
♦ «Список кафедр факультета» (должен выдаваться список всех кафедр с полным названием и ФИО заведующего);
♦ «Зачисление» (должна обеспечиваться возможность ввода данных о новом студенте через соответствующую форму);
♦ «Отчисление» (должна открыться форма, отражающая список отчисляемых студентов для проверки, и содержащая кнопку, запускающую процедуру отчисления).
Форма-меню должна открываться в момент открытия файла БД.
4. Проверьте полученные знания, выполнив тестовые задания. (См. стр. Тестовые задания. Тема 3).
Задание 4. Создание БД средствами СУБД SQL-server 2008 R2
Какие методы и средства предлагает СУБД SQL-сервер для создания базы данных и ее таблиц, как можно удалить таблицу или всю базу данных - об этом Вы узнаете, изучив эту тему и выполнив соответствующие практические рекомендации.
НЕОБХОДИМЫЕ СВЕДЕНИЯ
База данных SQL Server 2008 R2 представляет собой набор файлов данных и файлов журнала транзакций. Файлы данных хранят данные и объекты: таблицы, индексы, представления, хранимые процедуры и триггеры. Файлы журнала транзакций хранят сведения журнала транзакций. Каждая БД должна иметь хотя бы один файл журнала транзакций и один файл данных.
Файлы данных бывают основные с расширением .MDF и дополнительные с расширением .NDF. Основной файл содержит системные таблицы и системные объекты и необязательно пользовательские таблицы и объекты, т.к. последние могут помещаться и в дополнительные файлы с целью повышения производительности. Задача повышения производительности перед нами не стоит, поэтому мы не будем создавать дополнительные файлы, а все данные будем хранить в основном файле.
Необходимые знания.
Структура языка SQL:
· DDL – язык определения данных,
· DQL – язык запросов,
· DML – язык манипулирования данными,
· DCL – язык управления данными,
· команды администрирования данных,
· команды управления транзакциями
В этой теме мы займемся изучением только языка запросов DQL.
Язык DQL имеет всего один оператор SELECT, но обладающий огромными возможностями. Этот единственный оператор поиска реализует все операции реляционной алгебры. Однако писать запросы на языке SQL (грамотные запросы) сначала совсем не просто. Надо учиться, так же как надо учиться решать математические задачки или составлять алгоритмы для решения непростых комбинаторных задач. Один и тот же запрос может быть реализован несколькими способами, и, будучи все правильными, они, тем не менее, могут существенно отличаться по времени исполнения, и это особенно важно для больших баз данных.
Синтаксис оператора SELECT имеет следующий вид:
SELECT [ALL | DISTINCT] «писок полей>|*)
FROM <Список таблиц>
[WHERE <Предикат-условие выборки или соединения>]
[GROUP BY <Список полей результата>]
[HAVING <Предикат-условие для группы>]
[ORDER BY <Список полей, по которым упорядочить вывод>]
Символ *. (звездочка) означает, что в результирующий набор включаются все столбцы из исходных таблиц запроса.
В разделе FROM задается перечень исходных отношений (таблиц) запроса.
В разделе WHERE задаются условия отбора строк результата или условия соединения кортежей исходных таблиц, подобно операции условного соединения в реляционной алгебре.
В разделе GROUP BY задается список полей группировки.
В разделе HAVING задаются предикаты-условия, накладываемые на каждую группу.
В части ORDER BY задается список полей упорядочения результата, то есть список полей, который определяет порядок сортировки в результирующем отношении. Например, если первым полем списка будет указана Фамилия, а вторым Номер группы, то в результирующем отношении сначала будут собраны в алфавитном порядке студенты, и если найдутся однофамильцы, то они будут расположены в порядке возрастания номеров групп.
Оператор SELECT предназначен для выборки информации из таблиц БД и представления нужным образом результата.
Для выборки необходимо указать как минимум две вещи:
· что выбрать,
· откуда выбрать.
В общем виде оператор выглядит следующем образом.
SELECT [ALL | DISTINCT] {* | [имя столбца [AS новое имя]]} [,…n]
FROM имя таблицы [[AS] псевдоним] [,…n]
[WHERE <условие поиска>] [GROUP BY имя столбца [,…n] [HAVING <условие для группы>]
[ORDER BY имя столбца [,…n]]
На первый взгляд это выглядит ужасно, но если разобраться, здесь нет ничего сложного. Попробуйте исключить все, что записано в квадратных скобках как необязательное. Что получим?
SELECT *
FROM имя таблицы
Мы написали запрос, позволяющий вывести для всех полей (это знак *) все записи из таблицы, имя которой указано после слова FROM.
Например:
SELECT *
FROM Преподаватели;
Если нужны не все поля, то следует вместо знака * указать список имен этих полей.
Например:
1. Вывести фамилии всех студентов и номера их зачеток
SELECT ФИО, N_зачетки
FROM Студент;
Итак, запомним -
SELECT что вывести (какие столбцы)
FROM откуда вывести (из каких таблиц)
Если Вы хотите упорядочить выводимые данные, используйте сортировку, которая задается предложением ORDER BY,гдеуказывается по значениям какого столбца следует сортировать и как сортировать. ПредложениеORDER BY должно быть последним в операторе SELECT.
Сортировать можно по нескольким столбцам.
По умолчанию сортировка выполняется по возрастанию значений (ASC).
Для сортировки по убыванию необходимо в конце ORDER BY предложения записать ключевое слово DESCENDING сокращенно DESC
Например:
1. Вывести по алфавиту фамилии всех студентов с указанием номеров их зачеток
SELECT ФИО, N_зачетки
FROM Студент
ORDER BY ФИО;
2. Вывести список студентов по убыванию значений поля «год рождения»
SELECT ФИО, N_зачетки, год_рождения
FROMСтудент
ORDER BY ФИО, год_рождения DESC ;
Если Вы хотите вывести не просто все имеющиеся записи, а вывести с применением некоторого условия, то применяйте предложение WHERE.
Существует 5 основных типов условий поиска в предложении WHERE:
· сравнение,
· попадание в заданный диапазон,
· принадлежность множеству,
· соответствие строкового значения заданному шаблону,
· проверка на значение NULL.
Рассмотрим эти условия.
Сравнение -
WHERE логическое выражение
Например:
Вывести все сведения их таблицы «Ведомость» для записей, где значения в поле Оценка >3.
SELECT *
FROM Ведомость
WHERE Оценка>3;
Логическое выражение может быть сложным, тогда следует вспомнить, что
· логическое выражение вычисляется слева направо,
· сначала вычисляется то, что в скобках,
· логические операции выполняются в следующей последовательности сначала NOT затем AND потом OR.
Диапазон в предложении WHERE задается оператором BETWEEN,в котором указываются минимальное значение диапазона AND максимальное значение диапазона (граничные значения включаются).
Конструкция NOT BETWEEN – для поиска значений вне диапазона
Например:
Из таблицы Товар вывести данные о товаре ценой от 500 до 1500
SELECT Наименование, Цена
FROM Товар
WHERE Цена BETWEEN 500 AND 1500;
Принадлежность множеству
Оператор IN(список заданных значений)проверяет соответствие списку заданных значений, NOT IN – наоборот несоответствие.
Например:
Вывести из таблицы Студенты данные о студентах, родившихся в 1990 и 1991 годах
SELECT *
FROM Студенты
WHERE Год_рождения IN(1990,1991);
Соответствие строкового значения заданному шаблону -
используется операторLIKE ”шаблон”.
В шаблоне используются следующие символы:
% -заменяет любое количество любых символов;
_-заменяет один символ;
[]-предлагает набор символов;
[^]-предлагает все символы кроме указанных.
Например:
Найти студентов, у которых в номере телефона:
Третья цифра 8
WHERE Студент.Телефон LIKE ”_ _8%”
Вторая цифра 5 или 9
WHERE Студент.Телефон LIKE ”_[59]%”
Вторая цифра 5,6 или 7
WHERE Студент.Телефон LIKE ”_[5-7]%”
Встречается 23
WHERE Студент.Телефон LIKE ”%23% ”
Проверка на значение NULL – т.е. поиск записей, в которых значение некоторого поля в данный момент отсутствует
Например:
Найти студентов без телефона:
SELECT ФИО
FROM Студент
WHERE Телефон IS NULL;
Найти студентов с телефоном:
SELECT ФИО, Телефон
FROM Студент
WHERE Телефон IS NOT NULL;
С помощью SELECT можно осуществлять вычисления по данным в соответствии с формулой (выражением), где используются обращения к данным заданных полей, константы, встроенные функции языка и знаки арифметических операций.