close

Вход

Забыли?

вход по аккаунту

Шадский Никита Геннадьевич. Разработка программно – методического обеспечения модуля «Язык SQL» дисциплины «Базы данных» для бакалавров направления подготовки 09.03.03 Прикладная информатика

код для вставки
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ
ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ
УЧРЕЖДЕНИЕ ВЫСШЕГО ОБРАЗОВАНИЯ
«ОРЛОВСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
имени И.С. ТУРГЕНЕВА»
ВЫПУСКНАЯ КВАЛИФИКАЦИОННАЯ РАБОТА БАКАЛАВРА
по направлению подготовки 44.03.01 Педагогическое образование
направленность (профиль): Информатика
Студента Шадского Никиты Геннадьевича шифр 140692
Факультет (институт): Физико - математический
РАЗРАБОТКА ПРОГРАММНО-МЕТОДИЧЕСКОГО ОБЕСПЕЧЕНИЯ
МОДУЛЯ «ЯЗЫК SQL» ДИСЦИПЛИНЫ «БАЗЫ ДАННЫХ» ДЛЯ
БАКАЛАВРОВ НАПРАВЛЕНИЯ ПОДГОТОВКИ «ПРИКЛАДНАЯ
ИНФОРМАТИКА»
Студент
Шадский Н.Г.
Руководитель
к.п.н., доц. Симанева Т. А.
Зав. кафедрой / РОП
к.ф.-м.н., доц. Дорофеева В. И.
Орёл 2018
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РОССИЙСКОЙ ФЕДЕРАЦИИ
ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ
УЧРЕЖДЕНИЕ ВЫСШЕГО ОБРАЗОВАНИЯ
«ОРЛОВСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ
имени И.С. ТУРГЕНЕВА»
Факультет физико-математический
Кафедра информатики
Направление подготовки 44.03.01 Педагогическое образование
Направленность (профиль) Информатика
УТВЕРЖДАЮ:
За
(подпись)
«10» ноября 2017г.
ЗАДАНИЕ
на выполнение выпускной квалификационной работы
студента Шадского Никиты Геннадьевича шифр 140692
1. Тема ВКР Разработка программно-методического обеспечения модуля
«Язык SQL» дисциплины «Базы данных» для бакалавров направления
подготовки «Прикладная информатика»
Утверждена приказом по университету от «10» ноября 2017 года №2-3229
2. Срок сдачи студентом законченной работы «20» июня 2018 года
3. Исходные данные к работе: специализированная литература, источники
сети Интернет.
4. Содержание ВКР
ВВЕДЕНИЕ
КЛАВ А 1. ОБЩИЕ ПОЛОЖЕНИЯ О РАЗРАБОТКЕ ПРОГРАММНО­
МЕТОДИЧЕСКОГО ОБЕСПЕЧЕНИЯ МОДУЛЯ «ЯЗЫК SQL»
ДИСЦИПЛИНЫ «БАЗЫ ДАННЫХ» ДЛЯ БАКАЛАВРОВ НАПРАВЛЕНИЯ
ПОДГОТОВКИ «ПРИКЛАДНАЯ ИНФОРМАТИКА»
1.1. Место дисциплины «Базы данных» в структуре основной
образовательной программы.
1.2. Цели и задачи изучения модуля «Язык SQL»
1.3. Компетенции обучающегося, формируемые в результате освоения
модуля «Язык SQL»
1.4. Логико-структурная схема преподавания модуля «Язык SQL»
ГЛАВА 2. ПРОГРАММНО-МЕТОДИЧЕСКОЕ ОБЕСПЕЧЕНИЕ МОДУЛЯ
«ЯЗЫК SQL» ДИСЦИПЛИНЫ «БАЗЫ ДАННЫХ» ДЛЯ БАКАЛАВРОВ
НАПРАВЛЕНИЯ ПОДГОТОВКИ «ПРИКЛАДНАЯ ИНФОРМАТИКА»
2.1. Содержание и тематическое планирование модуля «Язык SQL».
2.2. Аннотации лекционных и лабораторных работ модуля «Язык SQL».
2.3. Методическое обеспечение модуля «Язык SQL».
2.4. Примерные лекционные и лабораторные занятия по модулю «Язык
SQL» дисциплине «Базы данных» для бакалавров направления подготовки
«Прикладная информатика»
2.4.1. Лекция 1. Язык SQL
2.4.2. Лекция 2. Простые запросы к базе данных
2.4.3. Лекция 3. Сложные запросы к базе данных.
2.4.4. Лабораторная работа №1. Создание и заполнение базы данных.
2.4.5. Лабораторная работа №2. Создание простых запросов к базе
данных.
2.4.6. Лабораторная работа №3. Создание сложных запросов к базе
данных.
2.5. Фонд оценочных средств модуля «Язык SQL».
ЗАКЛЮЧЕНИЕ
СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ
ПРИЛОЖЕНИЯ
5. Перечень графического материала
Таблиц - 4. Графических иллюстраций (представлений) - 70
Дата выдачи задания «10» ноября 2017 г.
Научный руководитель ВКР
Симанева Т.А.
Задание принял к исполнению
Шадский Н.Г.
КАЛЕНДАРНЫЙ ПЛАН
Наименование этапов
ВКР
Срок выполнения
этапов
Примечание
Подбор и анализ
источников по теме
исследования
декабрь - январь 2018г.
Выполнено
Работа над введением
апрель 2018г.
Выполнено
Работа над главой 1
февраль-март 2018г.
Выполнено
Работа над главой 2
апрель-май 2018г.
Выполнено
Работа над заключением май 2018г.
Выполнено
Оформление ВКР
июнь 2018г.
Выполнено
Сдача ВКР
июнь 2018г.
Выполнено
Студент
Шадский Н.Г.
( подпись)
Научный руководитель ВЬСР
Симанева Т.А.
( подпись)
5
Аннотация
ВКР бакалавра на тему «Разработка программно-методического
обеспечения модуля «Язык
SQL» дисциплины
«Базы данных» для
бакалавров направления подготовки «Прикладная информатика»» содержит
124 страницы текста, таблиц - 4, графических иллюстраций (представлений)
-70, использованных источников -36 , приложений - 3.
Модуль «Язык SQL» является одной из главных составляющих
дисциплины «Базы данных», так как является универсальным средством
создания,
модификации
и
управления
данными.
Элегантность
и
независимость от специфики компьютерных технологий, а также его
поддержка лидерами промышленности в области технологии реляционных
баз данных, сделало SQL (и, вероятно, в течение обозримого будущего
оставит
его)
основным
стандартным
языком.
Как
пишет
школа
программирования Coding Dojo, составив топ самых востребованных языков
программирования в мире, в 2017 году этот список возглавляет SQL - «язык
структурированных
запросов».
Изучение
языка
SQL
способствует
формированию выпускников, ориентированных на определенную сферу их
профессиональной деятельности и конкурентоспособных на рынке труда, что
в первую очередь обусловливается успешным освоением образовательной
программы по данному направлению подготовки.
Ключевые слова: базы данных, язык SQL, типы данных языка SQL,
создание, изменение и удаление таблиц, формирование запросов к базе
данных, оператор SELECT,
внешние
объединения таблиц,
методика
преподавания языка SQL, практика SQL запросов.
Предмет исследования. Методическая система обучения модуля
«Язык SQL» дисциплины «Базы данных» для бакалавров направления
подготовки «Прикладная информатика».
6
Объект исследования. Процесс преподавания модуля «Язык SQL»
дисциплины «Базы данных» для бакалавров направления подготовки
«Прикладная информатика».
Цель работы. Разработка программно-методического обеспечения
модуля
«Язык
SQL»
дисциплины
«Базы
данных»
для
бакалавров
направления подготовки «Прикладная информатика», а именно разработка
курса
лекционных
занятий,
лабораторных
работ
и
методических
рекомендаций для изучения данного модуля.
Метод исследования. Для исследования применяются методы анализа,
обобщения, восхождение от абстрактного к конкретному, описание.
Результаты работы.
В выпускной квалификационной работе
разработано программно-методическое обеспечение модуля «Язык SQL», а
именно: разработана система лекционных занятий и лабораторных работ,
сформирован фонд оценочных средств модуля, разработаны методические
рекомендации к изучению модуля.
Работа
имеет
теоретическое
и практическое значение,
т.к.
разработанный комплекс программно-методического обеспечения модуля
«Язык SQL» может применяться преподавателями вуза для проведения
занятий по дисциплине «Базы данных».
7
СОДЕРЖАНИЕ
ВВЕДЕНИЕ................................................................................................................9
ГЛАВА 1. ОБЩИЕ ПОЛОЖЕНИЯ О РАЗРАБОТКЕ ПРОГРАММНО­
МЕТОДИЧЕСКОГО ОБЕСПЕЧЕНИЯ МОДУЛЯ «ЯЗЫК SQL»
ДИСЦИПЛИНЫ «БАЗЫ ДАННЫХ» ДЛЯ БАКАЛАВРОВ НАПРАВЛЕНИЯ
ПОДГОТОВКИ «ПРИКЛАДНАЯ ИНФОРМАТИКА».......................................12
1.1. Место дисциплины «Базы данных» в структуре основной
образовательной программы............................................................................... 12
1.2. Цели и задачи изучения модуля «Язык SQL»............................................12
1.3. Компетенции обучающегося, формируемые в результате освоения
модуля «Язык SQL»............................................................................................. 13
1.4. Логико-структурная схема преподавания модуля «Язык SQL»...............14
ГЛАВА 2. ПРОГРАММНО-МЕТОДИЧЕСКОЕ ОБЕСПЕЧЕНИЕ МОДУЛЯ
«ЯЗЫК SQL» ДИСЦИПЛИНЫ «БАЗЫ ДАННЫХ» ДЛЯ БАКАЛАВРОВ
НАПРАВЛЕНИЯ ПОДГОТОВКИ «ПРИКЛАДНАЯ ИНФОРМАТИКА»........16
2.1. Содержание и тематическое планирование модуля «Язык SQL»............ 16
2.2. Аннотации лекционных и лабораторных работ модуля «Язык SQL».... 17
2.3. Методическое обеспечение модуля «Язык SQL»...................................... 20
2.4. Примерные лекционные и лабораторные занятия по модулю «Язык
SQL» дисциплине «Базы данных» для бакалавров направления подготовки
«Прикладная информатика»...............................................................................21
2.4.1. Лекция 1. Язык SQL........................................................................... 21
2.4.2. Лекция 2. Простые запросы к базе данных.......................................30
2.4.3. Лекция 3. Сложные запросы к базе данных.......................................43
2.4.4. Лабораторная работа №1. Создание и заполнение базы данных. ... 64
2.4.5. Лабораторная работа №2. Создание простых запросов к базе
данных............................................................................................................. 65
2.4.6. Лабораторная работа №3. Создание сложных запросов к базе
данных............................................................................................................. 66
2.5. Фонд оценочных средств модуля «Язык SQL»..........................................68
ЗАКЛЮЧЕНИЕ.......................................................................................................70
СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ................................................ 72
8
ПРИЛОЖЕНИЯ
77
9
ВВЕДЕНИЕ
Актуальность работы.
ФГОС ВО направления подготовки 09.03.03 Прикладная информатика
в качестве области профессиональной деятельности выпускников включает
разработку
проектов
автоматизации
и
информатизации
прикладных
процессов и создание информационных систем в прикладных областях, а
также выполнение работ по созданию, модификации, внедрению и
сопровождению информационных систем и управление этими работами.
Объектами
профессиональной
программу
бакалавриата,
деятельности
данного
выпускников,
направления
подготовки
освоивших
являются
прикладные и информационные процессы, информационные технологии,
информационные системы [1]. Таким образом, дисциплина «Базы данных»
востребована с точки зрения практического применения знаний, умений и
навыков в учебной и будущей профессиональной деятельности студентов
направления «Прикладная информатика» и подготовки студентов к изучению
профильных дисциплин.
Модуль «Язык SQL» является одной из главных составляющих
дисциплины «Базы данных», так как является универсальным средством
создания,
модификации
и
управления
данными.
Элегантность
и
независимость от специфики компьютерных технологий, а также его
поддержка лидерами промышленности в области технологии реляционных
баз данных, сделало SQL (и, вероятно, в течение обозримого будущего
оставит
его)
основным
стандартным
языком.
Как
пишет
школа
программирования Coding Dojo, составив топ самых востребованных языков
программирования в мире, в 2017 году этот список возглавляет SQL - «язык
структурированных запросов» [2]. Изучение языка SQL способствует
формированию выпускников, ориентированных на определенную сферу их
профессиональной деятельности и конкурентоспособных на рынке труда, что
в первую очередь обусловливается успешным освоением образовательной
программы по данному направлению подготовки [3].
10
Поэтому тема моей дипломной работы является актуальной.
Объект и предмет исследования работы
Объектом исследования является процесс преподавания модуля «Язык
SQL» дисциплины «Базы данных» для бакалавров направления подготовки
«Прикладная информатика».
Предметом исследования является методическая система обучения
модуля
«Язык
SQL»
дисциплины
«Базы
данных»
для
бакалавров
направления подготовки «Прикладная информатика».
Цели и задачи работы
Целью
данной
выпускной
квалификационной
работы
является
разработка программно-методического обеспечения модуля «Язык SQL»
дисциплины «Базы данных» для бакалавров направления подготовки
«Прикладная информатика», а именно разработка курса лекционных занятий,
лабораторных работ и методических рекомендаций для изучения данного
модуля.
Для достижения поставленной цели в выпускной квалификационной
работе необходимо решить следующие задачи:
1.
Проанализировать учебно-методическую литературу и современное
состояние преподавания дисциплинарного модуля «Язык SQL» дисциплины
«Базы данных», в вузах;
2.Изучить
основные
программные
средства
использования
возможностей языка SQL;
3.Определить основные цели, задачи и особенности преподавания
модуля
«Язык
SQL»
дисциплины
«Базы
данных»
для
бакалавров
направления подготовки «Прикладная информатика»,
4.Разработать программно-методическое обеспечение модуля «Язык
SQL» дисциплины «Базы данных» для бакалавров направления подготовки
«Прикладная информатика»:
- разработать систему лекционных занятий;
- разработать систему лабораторных работ;
11
- разработать фонд оценочных средств для контроля знаний;
- разработать методические рекомендации.
Структура выпускной квалификационной работы.
Выпускная квалификационная работа состоит из введения, двух глав,
заключения, списка используемых источников, приложения.
Во
введении
рассматривается
актуальность
выбранной
темы,
формулируются цели, задачи исследования.
В первой главе рассматривается место дисциплины «Базы данных» в
структуре основной образовательной программы бакалавров направления
подготовки
«Прикладная
дисциплинарного
модуля
информатика»,
цели
«Язык
компетенции
SQL»,
и
задачи
изучения
обучающегося,
формируемые в результате освоения модуля. Так же рассматривается логико­
структурная схема изучения модуля.
Во второй главе приводится программно-методическое обеспечение
модуля «Язык SQL» дисциплины «Базы данных». В ней приводится
тематическое содержание и тематическое планирование модуля, аннотации
лекционных и лабораторных работ,
методические
рекомендации по
изучению модуля. Основной частью главы являются системы лекций и
лабораторных занятий по модулю, а также фонд оценочных средств освоения
модуля «Язык SQL».
В заключении формулируются полученные результаты, и подводится
итог проделанной работы.
В 3 приложениях приводятся дополнительные варианты заданий
лабораторных работ, фонд оценочных средств модуля, а также методические
рекомендации по изучению модуля.
12
ГЛАВА 1. ОБЩИЕ ПОЛОЖЕНИЯ О РАЗРАБОТКЕ ПРОГРАММНО­
МЕТОДИЧЕСКОГО ОБЕСПЕЧЕНИЯ МОДУЛЯ «ЯЗЫК SQL»
ДИСЦИПЛИНЫ «БАЗЫ ДАННЫХ» ДЛЯ БАКАЛАВРОВ
НАПРАВЛЕНИЯ ПОДГОТОВКИ «ПРИКЛАДНАЯ ИНФОРМАТИКА»
1.1. Место дисциплины «Базы данных» в структуре основной
образовательной программы.
Дисциплина «Базы данных» относится к дисциплинам базовой части
учебного плана направления подготовки 09.03.03 Прикладная информатика.
Освоение студентами данной дисциплины предполагает наличие у них
знаний в области программирования, а также информационных систем и
технологий.
В связи с этим изучению студентами дисциплины «Базы данных»
предшествует освоение ими таких дисциплин, как «Информатика и
программирование»,
«Основы
визуального
программирование»,
«Информационные системы и технологии».
Освоение студентами дисциплины «Базы данных» ориентировано на
формирование у них знаний и навыков в области моделей данных,
проектирования баз данных, построения и реализации баз данных,
манипулирования данными, изучение языка SQL.
Знания, полученные в курсе «Базы данных», будут использованы
студентами
при
информационных
изучении
систем»,
следующих
«Проектная
дисциплин:
деятельность»,
«Проектирование
а
также
для
подготовки выпускной квалификационной работы.
1.2. Цели и задачи изучения модуля «Язык SQL»
Изучение модуля «Язык SQL» в рамках дисциплины «Базы данных» в
высших учебных заведениях направлено на достижение следующих целей:
•
Освоение знаний, составляющих основу модуля «Язык SQL» в
рамках дисциплины «Базы данных».
13
•
Изучение понятий и синтаксиса языка SQL и его применение в
практической деятельности.
•
Изучения приемов практического применения языка SQL для
выборки данных.
•
Развитие
познавательных
интересов,
интеллектуальных
и
творческих способностей, развитие навыков контроля и самоконтроля,
сосредоточенности, воображения.
Задачи:
S
Овладение
основными
функциями
универсального
языка
манипулирования данными SQL.
S
Выработка практических навыков использования языка SQL.
S
Овладение навыков по созданию и заполнению таблиц на языке
S
Формирование навыков в построение запросов к базе данных.
S
Развитие навыков в применение агрегатных функций в операторе
SQL.
выбора.
S
Получение навыков в построение вложенных запросов.
S
Выработка
практических
навыков
работы
с
внешними
объединениями и операторами манипулирования данными.
S
Развитие навыков работы с компьютером.
S
Развитие умения студентов самостоятельно работать.
S
Формирование культуры мышления, способности к обобщению,
анализу, восприятию информации, постановке целей и выбору путей ее
достижения.
1.3.
Компетенции
обучающегося,
формируемые
в
результате
освоения модуля «Язык SQL»
Процесс изучения дисциплины «Базы данных», а также изучаемый в ее
рамках модуль «Язык SQL», направлен на формирование и развитие у
14
студентов
следующих
общепрофессиональных
и
профессиональных
компетенций:
способностью
использовать
нормативно-правовые
документы,
международные и отечественные стандарты в области информационных
систем и технологий (ОПК-1);
способностью
проектировать
ИС
в
соответствии
с
профилем
подготовки по видам обеспечения (ПК-3).
1.4. Логико-структурная схема преподавания модуля «Язык SQL»
Методика изучения модуля «Язык SQL» дисциплины «Базы данных»
подразумевает вначале знакомство с общим понятием языка SQL: его
представление, возможности, операции создания таблиц и модификаций их
содержимого. Также пойдет речь о SQL-сервере, как он принимает запросы
на языке SQL для общения с клиентами и их три основных типа.
Затем студенты знакомятся с основами языка SQL, описываются
основные типы данных языка: базовые, символьные и десятичные константы
и др. Рассматривается применением операторов формирования запросов к
базе данных.
Далее идет подробное знакомство с операциями модификации таблиц:
создание, изменение и удаление таблиц баз данных. Также сюда будут
входить описание строк и полей таблицы, первичного и вторичного ключа
таблицы. После рассматривается оператор добавления строк в таблицу и его
синтаксические формы: добавление строк перечислением значений всех ее
ячеек, добавление строк с использованием списка имен столбцов и
добавление строк по результатам запроса к базе данных.
В дальнейшем будет рассматриваться оператор SELECT, который
осуществляет формирования запросов к базе данных. Здесь рассматриваются
конструкции
различных форм выборки данных таблицы. Например,
описывается выборка данных из таблиц с применением агрегатных функций
в операторе выбора, рассматриваются вложенные запросы, внешние
15
(внутренние)
рассмотренных
объединения
конструкции
таблиц
базы
оператора
данных
выбора
и
т.д.
данных
Для
всех
приводятся
иллюстрирующие примеры.
На лабораторных работах закрепляются все приобретенные знания.
Лабораторные начинаются с создания и заполнения базы данных различных
вариантов предметной области, которые будут отражены в практических
упражнениях. После этого создаются и выполняются простые и сложные
запросы к созданной базе данных. При выполнении лабораторных работ
студенты
могут
пользоваться
лекциями,
а
также
использовать
дополнительную литературу. Лабораторные работы выполняются с помощью
средств СУБД MySQL.
Таким образом, для изучения модуля «Язык SQL» дисциплины «Базы
данных» невозможно обойтись без лабораторных работ. Они позволяют
развивать самостоятельность, вырабатывать навыки и умения практического
применения средств языка SQL при работе с базами данных.
В качестве итоговой формы контроля дисциплины «Базы данных»
выступает экзамен, который позволяет проверить степень освоения модуля
«Язык SQL», а также сформированность компетенций.
16
ГЛАВА 2. ПРОГРАММНО-МЕТОДИЧЕСКОЕ ОБЕСПЕЧЕНИЕ
МОДУЛЯ «ЯЗЫК SQL» ДИСЦИПЛИНЫ «БАЗЫ ДАННЫХ» ДЛЯ
БАКАЛАВРОВ НАПРАВЛЕНИЯ ПОДГОТОВКИ «ПРИКЛАДНАЯ
ИНФОРМАТИКА»
2.1.
Содержание и тематическое планирование модуля «Язык
SQL».
Изучение
модуля
«Язык
SQL»
дисциплины
«Базы
данных»
предполагает изучение следующих тем.
Язык SQL
Понятие языка SQL, а также история разработки языка SQL, операции
управления таблицами, группы SQL СУБД. Основа языка SQL, основные
операторы языка SQL. Типы данных языка SQL. Манипулирование
таблицами и строками таблиц, создание и описание таблицы, а также
описание первичного и вторичного ключа. Изменение и удаление таблицы,
добавление строк в таблицу [8].
Простые запросы к базе данных
Выборка данных из таблиц, оператор SELECT. Формирование запросов
к базе данных, конструкции GROUP BY и ORDER BY. Упорядочение строк
перечислением
полных
имен
столбцов.
Манипулирование
строками,
операторы DELETE и UPDATE. Применение агрегатных функций в
операторе выбора.
Сложные запросы к базе даны
Вложенные запросы, предикат EXISTS и NOT EXISTS. Внешние
объединения, определения INNER, LEFT, NULL, RIGHT и FULL. Операторы
манипулирования данными: операции удаления записей оператор DELETE,
добавления или ввода новых записей оператор INSERT
и операция
изменения (обновления записей) оператор UPDATE.
На модуль «Язык SQL» отводится 18 часов. Из них 6 часов лекций и 12
часов лабораторных работ.
17
Тематическое планирование модуля «Язык SQL» дисциплины «Базы
данных»
№
Наименование темы
Кол-во
часов
п/п
1
Лекция 1. «Язык SQL»
2
2
Лекция 2. «Простые запросы к базе данных»
2
3
Лекция 3. «Сложные запросы к базе данных»
2
4
Лабораторная работа №1. «Создание и заполнение
базы данных»
4
5
Лабораторная работа №2. «Создание простых запросов
к базе данных»
4
6
Лабораторная работа №3. «Создание сложных
запросов к базе данных»
4
Ито г:
18
Формой текущего контроля знаний студентов является контроль
правильности выполнения и оформления лабораторных работ.
2.2.
Аннотации лекционных и лабораторных работ модуля «Язык
SQL».
Лекция 1. Язык SQL.
Данная лекция является вводной в модуле «Язык SQL». Вводится
понятие языка SQL. Рассматриваются основы языка SQL,
а также типы
данных языка. Основной целью лекции является знакомство с таким
понятиям, как структурированный язык запросов. Основные составляющие
операторы языка SQL. Основные символьные и десятичные константы.
Операторы манипулирования таблицами и строками таблиц.
18
Лекцию удобнее провести в сочетании объяснения преподавателя с
использованием демонстрации (мультимедийная презентация). Практически
всё сказанное преподавателем необходимо записать в тетрадь.
Формы организации учебной деятельности: групповые, фронтальные.
Методы организации учебной деятельности: словесные, наглядные.
Лекция 2. Простые запросы к базе данных.
В данной лекции описывается формирование запросов к базе данных.
Рассматривается выборка данных из таблиц. Основной целью является
знакомство с оператором SELECT, а также конструкциями GROUP BY и
ORDER BY.
Лекцию удобнее провести в сочетании объяснения преподавателя с
использованием
демонстрации
(мультимедийная презентация).
с
большим
количеством
примеров
При рассмотрении примеров работы
оператора выборки данных можно использовать интерактивных средства
обучения (например, таких как вопрос-ответ).
Формы организации учебной деятельности: групповые, фронтальные.
Методы организации учебной деятельности: словесные, наглядные.
Лекция 3. Сложные запросы к базе данных.
При изучении данной темы основная цель -
познакомить со
вложенными запросами, которые проверяются в предикате внешнего запроса
(в предложении WHERE или HA VING) .
При
рассмотрении
примеров
также
можно
использовать
интерактивных средства обучения (например, таких как вопрос-ответ).
Практически всё сказанное преподавателем необходимо записать в тетрадь.
Формы организации учебной деятельности: групповые, фронтальные.
Методы организации учебной деятельности: словесные, наглядные.
Лабораторная работа №1. Создание и заполнение базы данных.
Цель
работы:
ознакомиться
с
возможностями
интерактивной
программы MySQL, создать с ее помощью базу данных, набор таблиц в ней и
заполнить таблицы данными для последующей работы.
19
Необходимые
теоретические
сведения:
для выполнения работы
необходимо иметь представление об использование основных операторов
языка SQL (лекции 1, 2).
Работа направлена на ознакомление с правилами работы в MySQL и
изучение набор команд языка SQL, связанных с созданием базы данных,
созданием и заполнением структуры таблиц базы данных, вставкой,
удалением и изменением записей таблиц.
Формы
групповые.
организации
Методы
учебной
организации
деятельности:
учебной
индивидуальные,
деятельности:
словесные,
практические, самостоятельная работа.
Лабораторная работа №2. Создание простых запросов к базе
данных.
Цель работы: используя базу данных, подготовленную в первой
лабораторной работе, разработать и реализовать серию запросов, связанных с
выборкой информации данных таблиц.
Необходимые
теоретические
сведения:
для выполнения работы
необходимо иметь представление о правилах и способах создания простых
запросов к базе данных (лекции 1, 2).
Работа направлена на формирование навыков и умений по работе с
изучением состава, правил и порядка использования ключевых фраз
оператора SELECT.
Формы
групповые.
организации
Методы
учебной
организации
деятельности:
учебной
индивидуальные,
деятельности:
словесные,
практические, самостоятельная работа.
Лабораторная работа №3. Сложные запросы к базе данных.
Цель работы: подготовить и реализовать вложенные запросы, а также
внутренние и внешние объединения.
Необходимые
теоретические
сведения:
для выполнения работы
необходимо иметь представление о запросах языка SQL, связанных с
манипулированием данных из нескольких таблиц (лекция 2, 3).
20
Формы
групповые.
организации
Методы
учебной
организации
деятельности:
учебной
индивидуальные,
деятельности:
словесные,
практические, самостоятельная работа.
2.3. Методическое обеспечение модуля «Язык SQL».
Для изложения модуля «Язык SQL» используются различные методы и
формы обучения. Система курса разбита на две части таким образом, что
одна часть - лекции, является теоретической, где содержатся основные
понятия и содержания тем «Язык SQL» и где рассматриваются теория языка
SQL,
другая часть, лабораторные работы - практическая,
помогающая
учащимся освоить теоретический материал на практике и лучше понять курс.
При изложении теоретического материала и для контроля можно
использовать организационные формы обучения, такие как лекция, лекция с
применением интерактивных форм. Т.к. курс по модулю «Язык SQL»
дисциплины «Базы данных» является новым для учащихся, необходимо,
чтобы материал излагался на доступном для понимания студента языке,
подкреплялся наглядными примерами. Объяснение материала необходимо
вести по принципу «от простого к сложному».
Учащиеся впервые знакомятся с программным комплексом модуля
«Язык
SQL»
дисциплины
«Базы
данных».
Основным
показателем
изученности модуля является практическое освоение материала. В связи с
этим подразумевается значительное использование компьютера на занятиях.
Таким
образом,
важно
большое
внимание
лабораторных работ. Необходимо привести
уделить
организации
подробные методические
указания к каждому заданию лабораторной работы.
Индивидуальная работа студентов за компьютером под руководством
преподавателя является основой для глубокого понимания данного курса.
Подробные методические рекомендации к содержанию модуля «Язык
SQL» приведены в Приложении 3.
21
2.4.
Примерные лекционные и лабораторные занятия по модулю
«Язык SQL» дисциплине «Базы данных» для бакалавров направления
подготовки «Прикладная информатика»
2.4.1. Лекция 1. Язык SQL
Цели:
Обучающая - рассказать об общих сведениях языка SQL, разъяснить
основные функции.
Развивающая - развить у обучающихся логическое мышление, память,
внимание;
способности четко
формулировать
свои мысли;
развитие
воображения студентов; развитие навыков конспектирования; развитие
интереса к предмету; развитие навыков работы на компьютере;
Воспитательная - развитие кругозора, вызвать желание освоить эти
знания; воспитание аккуратности при выполнении заданий, культуры труда,
бережного отношения к технике; воспитание информационной культуры
учащихся; воспитание у учащихся мотивации учебной деятельности.
План лекции:
1. Понятие языка SQL.
2. Основа языка SQL.
3. Типы данных языка SQL.
4. Манипулирование таблицами и строками таблиц.
Рекомендации преподавателю:
Практически все сказанное преподавателем необходимо записать в
тетрадь.
Содержание лекции:
1. Понятие языка SQL.
Язык SQL (Structured Query Language - структурированный язык
запросов) представляет собой стандартный высокоуровневый язык описания
данных и манипулирования ими в системах управления базами данных
(СУБД), построенных на основе реляционной модели данных.
22
Язык SQL был разработан фирмой IBM в конце 70-х годов. Первый
международный
стандарт
языка
был
принят
международной
стандартизирующей организацией ISO в 1989 г., а новый (более полный) - в
1992 г.. В настоящее время все производители реляционных СУБД
поддерживают с различной степенью соответствия стандарт SQL92 [9].
В языке SQL реализованы операции модификации содержимого строк
таблицы и пополнения таблицы новыми строками (что теоретически может
рассматриваться как операция объединения), а также операции управления
таблицами.
В
настоящее
время
наибольшее
распространение
получили
реляционные SQL СУБД двух групп:
1. мощные крупные коммерческие СУБД, ориентированные на хранение
огромных объемов информации (от гигабайт);
2. мобильные компактные свободно распространяемые (в том числе и в
исходных кодах) СУБД, использование которых оправдано и для БД
объемом всего лишь в десятки килобайт.
Наиболее известными СУБД первой группы являются:
• Sybase SQLServer фирмы Sybase, Inc.;
• Oracle фирмы Oracle Corporation;
• Ingres фирмы Computer Associates International;
• Informix фирмы Informix Corporation.
К наиболее популярным СУБД второй группы относятся:
• PostgreSQL организации PostgreSQL;
• microSQL фирмы Hughes Technologies Pty. Ltd.;
• mySQL фирмы T.C.X DataKonsult AB [10].
В данном учебном пособии практические упражнения, которые может
выполнить обучающийся после изучения основ языка SQL, реализуются
средствами СУБД MySQL.
SQL-сервер реализует собственно хранение данных и манипулирование
ими. Он принимает запросы на языке SQL от своих клиентов, выполняет их и
23
возвращает результаты (чаще всего в виде вновь построенных таблиц)
клиентам. Для общения с клиентами используется специальный протокол
(как правило, реализованный в виде протокола прикладного уровня стека
сетевых протоколов TCP/IP).
Клиентскую часть СУБД составляют клиенты трех основных типов.
• Интерактивные клиенты,
обеспечивающие пользователю-человеку
возможность общения с SQL-сервером непосредственно с помощью
языка SQL.
• И1111-клиенты,
обеспечивающие
интерфейс
прикладного
программирования (ИПП) прикладным программам, использующим
средства SQL-сервера. Такой ИПП может быть средством общения
прикладной программы с SQL-сервером на языке SQL или набором
стандартных
функций
доступа
к
реляционной
SQL
БД
без
формирования символьных строк запросов (например, стандартный
интерфейс ODBC).
• WWW-клиенты,
встраиваемые
в
World
Wide
Web-сервера
и
обеспечивающие доступ к информационным возможностям SQLсервера пользователям сети Internet по протоколу HTTP (протоколу
передачи гипертекстовых документов) [11].
Именно WWW-клиент СУБД MySQL используется в учебном пособии
для выполнения практических упражнений.
2. Основа языка SQL.
Программа на языке SQL представляет собой простую линейную
последовательность операторов языка SQL. Язык SQL в своем чистом виде
операторов управления порядком выполнения запросов к БД (типа циклов,
ветвлений, переходов) не имеет.
Операторы языка SQL строятся с применением:
• зарезервированных ключевых слов;
• идентификаторов (имен) таблиц и столбцов таблиц;
24
• логических, арифметических и строковых выражений, используемых
для формирования критериев поиска информации в БД и для
вычисления значений ячеек результирующих таблиц;
• идентификаторов (имен) операций и функций, используемых в
выражениях.
Все ключевые слова, имена функций и, как правило, имена таблиц и
столбцов представляются 7-мибитными символами кодировки ASCII (иначе
говоря - латинскими буквами) [12].
В языке SQL не делается различия между прописными (большими) и
строчными (маленькими) буквами, т.е., например, строки SELECT, Select,
select
представляют
собой
одно
и
то
же
ключевое
слово.
Для
конструирования имен таблиц и их столбцов допустимо использовать буквы,
цифры и знак _ (подчеркивание), но первым символом имени обязательно
должна быть буква. Запрещено использование ключевых слов и имен
функций в качестве идентификаторов таблиц и имен столбцов. Полный
список ключевых слов и имен функций (а он весьма обширен) можно найти в
документации на конкретную СУБД [13].
Оператор начинается с ключевого слова-глагола (например, CREATE создать, UPDATE - обновить, SELECT - выбрать и т.п.) и заканчивается
знаком; (точка с запятой). Оператор записывается в свободном формате и
может занимать несколько строк. Допустимыми разделителями лексических
единиц в операторе являются:
• один или несколько пробелов,
• один или несколько символов табуляции,
• один или несколько символов новая строка.
При описании операторов языка SQL в учебном пособии используются
следующие соглашения.
• Прописными
(большими)
буквами
(например,
WHERE) набраны зарезервированные слова.
SELECT,
FROM,
25
• Курсивом (например, имя табл, сложн_условие) набраны переменные
(нетерминальные символы), подлежащие замене в реальном операторе
конструкцией из терминальных символов (идентификаторов, знаков
операций, имен функций и т.п.).
• В
квадратные
скобки
([...]) заключается
необязательная
часть
оператора, которую можно опустить при создании реального оператора
(сами квадратные скобки в текст оператора не включаются).
• Вертикальная черта (|) означает возможность выбора (или) из двух или
нескольких вариантов синтаксической конструкции (сама вертикальная
черта в текст оператора не включается). Подчеркнутый вариант
(например, в [ ALL | DISTINCT }).
• Последовательность символов, ... обозначает возможность повторения
произвольное количество раз (в том числе и нулевое) предшествующей
запятой конструкции. Символ, включается реальный оператор в
качестве разделителя перед каждым повторением конструкции [14].
К сожалению, разработчики реальных СУБД неаккуратно обращаются
с требованиями стандартов языка SQL в части комментариев. Поэтому
комментарии при использовании в различных СУБД в текстах программ на
языке SQL могут помечаться следующими способами:
• от двойного минуса (--) до конца строки;
• от символа # до конца строки;
• между последовательностями /* и */ (стиль комментариев языка СИ)
3. Типы данных языка SQL.
Типы данных, используемые в языке SQL для хранения информации в
столбцах таблиц БД, весьма разнообразны. К сожалению, производители
конкретных
реляционных
СУБД
считают
своим
долгом
улучшить
множество типов данных, регламентируемых стандартом, реализуя свои
собственные версии и расширения.
Автором учебного пособия в качестве базовых предлагается считать
следующие типы данных:
26
• INT[(len)] - целое число длиной 4 байта, представляемое при выводе
максимально len цифрами;
• SMALLINT[(len)] - целое число длиной 2 байта, представляемое при
выводе максимально len цифрами;
• FLOAT[(len,dec)] - действительное число, представляемое при выводе
максимально len символами с dec цифрами после десятичной точки;
• CHAR(size) - строка символов фиксированной длины размером size
символов;
•
VARCHAR(size) - строка символов переменной длины максимальным
размером до size символов;
• BLOB (Binary Large OBject) - массив произвольных (двоичных) байтов
(максимальный размер зависит от реализации, обычно это 65535 байт);
этот тип данных может использоваться, например, для хранения
изображений;
• DATE - астрономическая дата;
• TIME - астрономическое время [15].
Символьные константы (типа CHAR и VARCHAR) записываются как
последовательности символов, заключенные в одиночные апострофы,
например brass (латунь).
Десятичные константы (типа FLOAT) могут записываться в научной
нотации как последовательности следующих компонент:
• знак числа;
• десятичное число с точкой;
• символ е;
• знак (+ или -) показателя степени;
• целое число, играющее роль показателя степени числа 10.
Например, десятичное число -0,123 может быть записано как -12.3е-2.
Отличие типов данных CHAR и VARCHAR заключается в том, что для
хранения в таблице строк символов типа CHAR используется точно size байт
(хотя содержание хранимых строк может быть значительно короче), в то
27
время как для строк типа VARCHAR незанятые символами строк (пустые)
байты в таблице не хранятся. Подчеркнем, что величины len и dec (в отличие
от size) не влияют на размер хранения данных в таблице, а только
форматируют вывод данных из таблицы.
4.Манипулирование таблицами и строками таблиц.
Для создания, изменения и удаления таблиц в SQL БД используются
операторы CREATE TABLE, ALTER TABLE и DROP TABLE.
Создание таблицы
Создание таблицы в БД реализуется оператором CREATE TABLE,
имеющим следующий синтаксис
CREATE TABLE им я_т абл (с_специф икация , ...);
где с пецификация
имеет разнообразный синтаксис.
Здесь же
рассматриваются наиболее часто используемые ее формы.
1. Описание столбца таблицы
им я_ст олбца т и п д а н н ы х [NULL1
где
имястолбца
-
имя
столбца таблицы,
а
типданных
-
спецификация одного из типов данных, рассмотренных в разделе Типы
данных языка SQL. Необязательное ключевое слово NULL означает, что
ячейкам данного столбца разрешено быть пустыми (т.е. не содержать какоголибо значения).
2. Описание столбца таблицы
им я_ст олбца
т ип данны х
NOT NULL
[DEFAULT по_умолч]
[PRIMARY KEY]
где конструкция NOT NULL запрещает иметь в таблице пустые ячейки
в данном столбце. Конструкция PRIMARY KEY указывает, что содержимое
столбца будет играть роль первичного ключа для создаваемой таблицы.
Конструкция DEFAULT по_умолч переопределяет имеющееся для столбцов
каждого типа данных значение по умолчанию (например, 0 для числовых
типов), используемое при добавлении в таблицу оператором INSERT INTO
строк, не содержащих значений в этом столбце.
28
3. Описание первичного ключа
PRIMARY KEY им я_клю ча (им я_ст олбца , ...)
Эта спецификация позволяет задать первичный ключ для таблицы в
виде композиции содержимого нескольких столбцов.
4. Описание вторичного ключа
KEY им я_клю ча (им я_ст олбца , ...)
Модификация таблицы
Модификация существующей таблицы в БД реализуется оператором
ALTER TABLE, имеющим следующий синтаксис
ALTER TABLE им я_т абл м _специф икация [м _сп ец и ф и кац и я ...]
где мспецификация имеет различные формы. Ниже рассматриваются
наиболее часто используемые.
1. Добавление нового столбца
ADD COLUMN с_специф икация
где с пецификация - описание добавляемого столбца в том виде, как
оно используется для создания таблицы оператором CREATE TABLE.
2. Удаление первичного ключа для таблицы
DROP PRIMARY KEY
3. Изменение/удаление значения по умолчанию
ALTER COLUMN им я_ст олбца SET по_ум олч
или
ALTER COLUMN им я_ст олбца DROP DEFAULT
Удаление таблицы
Удаление одной или сразу нескольких таблиц из БД реализуется
оператором DROP TABLE, имеющим следующий простой синтаксис
DROP TABLE имя_т абл, ...
Подчеркнем, что оператор DROP TABLE удаляет не только все
содержимое таблицы, но и само описание таблицы из БД. Если требуется
удалить только содержимое таблицы, то необходимо использовать оператор
DELETE FROM.
29
Добавление строк в таблицу
Для добавления строк в таблицу SQL базы данных используется
оператор INSERT INTO. Основные его синтаксические формы описываются
ниже.
1. Добавление строки перечислением значений всех ее ячеек
INSERT INTO имя_табл VALUES (знач, ...);
где знач - константное значение ячейки строки. Значения ячеек в
списке должны соответствовать порядку перечисления спецификаций
столбцов таблицы в операторе CREATE TABLE. Допустимо в качестве знач
указывать ключевое слово NULL, что означает отсутствие значения для
соответствующей ячейки строки.
Перед добавлением новой строки в
таблицу СУБД проверяет допустимость перечисленных значений, используя
описание столбцов таблицы из оператора CREATE TABLE.
2. Добавление строки с использованием списка имен столбцов
INSERT INTO имя_табл (имя_столбца, ...) VALUES (знач, ...);
Здесь списки имен столбцов и значений ячеек добавляемой строки
должны быть согласованы, хотя нет никаких требований к их порядку.
Допустимо опускать в списках информацию о некоторых ячейках строки,
при этом
о ячейки, соответствующие столбцам со спецификацией NULL в
операторе CREATE TABLE, будут пустыми;
о ячейки, соответствующие столбцам со спецификацией NOT
NULL в операторе CREATE TABLE, заполняются значениями по
умолчанию.
3. Добавление строк по результатам запроса к БД
INSERT INTO имя_табл [(имя_столбца, ...)] SELECT ...
Такой оператор дает возможность добавить в таблицу 0, 1 или сразу
несколько новых строк, полученных в результате запроса к базе данных,
реализуемого оператором SELECT[16].
30
2.4.2. Лекция 2. Простые запросы к базе данных
Цели:
Обучающая - рассказать об общих сведениях формирования запросов к
базе данных, разъяснить основные функции и методы работы.
Развивающая - развить у обучающихся логическое мышление, память,
внимание;
способности четко
формулировать
свои мысли;
развитие
воображения студентов; развитие навыков конспектирования; развитие
интереса к предмету; развитие навыков работы на компьютере;
Воспитательная - развитие кругозора, вызвать желание освоить эти
знания; воспитание аккуратности при выполнении заданий, культуры труда,
бережного отношения к технике; воспитание информационной культуры
учащихся; воспитание у учащихся мотивации учебной деятельности.
План лекции:
1. Формирование запросов к базе данных.
2. Выборка данных из таблиц.
3. Манипулирование строками таблиц.
4. Применение агрегатных функций в операторе выбора.
Рекомендации преподавателю:
Данную
лекцию
удобно
провести
используя
мультимедийные
технологии, т.к. лекция содержит большое количество визуальных примеров.
Практически все сказанное преподавателем необходимо записать в
тетрадь.
Содержание лекции:
1.Формирование запросов к базе данных.
Упорядочивание и группирование строк результирующей таблицы
Для
обеспечения
структурированности
в
расположении
строк
результирующей таблицы в операторе SELECT используются конструкции
GROUP BY и ORDER BY.
Упорядочение
строк
достигается
перечислением
полных
имен
столбцов, по которым в возрастающем (ASC) или убывающем (DESC)
31
порядке сортируются строки результирующей таблицы. При этом строки
упорядочиваются в первую очередь по столбцу, указанному первым в списке
ORDER BY. Затем, если среди значений ячеек первого столбца есть
повторяющиеся, производится упорядочение по второму столбцу и так далее.
• Оператор SELECT может обеспечить
вычисление
агрегативных
функций для групп строк результирующей таблицы. Для этого
используется список полных имен столбцов в конструкции GROUP BY.
Первое полное имя столбца в списке GROUP BY используется для
разбиения строк результирующей таблицы на первичные группы,
первичные группы разделяются на подгруппы вторым в списке полным
именем столбца и так далее.
• Оператор SELECT выводит значения агрегативных функций для самых
малых подгрупп [17].
Примечание. Конструкция HAVING сложн_условие, как необязательная
составная
часть
предложения
GROUP
BY,
позволяет
определять
дополнительный (к WHERE сложн_условие) критерий выборки строк в
группы.
Этот
дополнительный
постпроцессорной
обработки
критерий
к
таблице,
применяется
полученной
в
в
режиме
результате
использования критерия из конструкции WHERE.
2.Выборка данных из таблиц
Для
извлечения
данных,
содержащихся
в таблицах
SQL
БД,
используется оператор SELECT, имеющий в общем случае сложный и
многовариантный синтаксис. В данном учебном пособии рассматриваются
только несложные и наиболее часто используемые примеры конструкций
оператора SELECT.
Упрощенно оператор SELECT выглядит следующим образом:
SELECT [ALL | DISTINCT] в_вы раж ение , ...
FROM им я_т абл [син_т абл], ...
[WHERE слож н_условие]
[GROUP BY полн_и м я_ст олбца\ном _ст олбщ , ...]
32
[ORDER BY полн_им я_ст олбца\ном _ст олбца [ASC|DESC], ...]
[HAVING слож н_условие];
Результатом работы оператора является выводимая на стандартный
вывод (экран дисплея) вновь построенная таблица, для которой
• количество и смысл (семантика) столбцов определяется списком
элементов ввыражение;
• содержимое строк определяется содержимым исходных таблиц из
списка FROM и критерием выборки, задаваемым сложн_условие.
При описании синтаксиса оператора SELECT использованы следующие
обозначения:
• син_табл - необязательный синоним имени таблицы, используемый
для сокращения длины записи выражений и условий в операторе
SELECT.
• полнимястолбца - полное имя столбца в виде
[имя_т абл \син_т абл .]им я_ст олбца
Конкретизирующий таблицу префикс в имени столбца необходим
только для различения столбцов, имеющих одинаковое имя в разных
таблицах из списка FROM.
• номстолбца - номер столбца результирующей таблицы.
З.Манипулирование строками таблиц
Для удаления и изменения строк таблиц SQL БД применяются
операторы DELETE и UPDATE.
Удаление строк
Удаление строк таблицы реализуется оператором DELETE FROM,
имеющим следующий синтаксис
D E L E T E F R O M имя_табл [WHERE сложн_условие]
где сложн_условие имеет описанный выше синтаксис. В результате
выполнения оператора из таблицы удаляются все строки, удовлетворяющие
критерию сложн_условие. Если в операторе DELETE FROM конструкция
WHERE опущена, то удаляются все строки таблицы.
33
Модификация строк
Изменение содержимого строк таблицы реализуется оператором
UPDATE, имеющим следующий синтаксис
UPDATE им я_т абл SET и м я _ст ол бц а= вы раж ен и е,...
[WHERE слож н_условие]
где выражение - выражение (в простейшем случае - константа),
согласующееся по результату с типом данных столбца. В выражение
допустимо использование значений ячеек любых столбцов таблицы,
рассмотренных ранее операций и функций (но не агрегативных), а также
прежнего содержимого модифицируемой ячейки. Обновлению подлежат
столбцы строк, отвечающих критерию сложное условие. Если конструкция
WHERE в операторе отсутствует, то обновляются все строки таблицы
Приведем несколько примеров использования оператора SELECT.
• Вывести список всех групп (без повторений), где должны пройти
экзамены.
SELECT DISTINCT Группы FROM R3
Результат:
• Вывести список студентов, которые сдали экзамен по дисциплине
"Базы данных" на "отлично".
SELECT ФИО FROM R1
WHERE Дисциплина = "Базы данных" AND Оценка = 5
Результат:
ФИО
Петров Ф. И.
Крылова Т. С.
Владимирова В . А.
34
• Вывести список всех студентов, которым надо сдавать экзамены с
указанием названий дисциплин, по которым должны проводиться эти
экзамены.
SELECT ФИО,Дисциплина FROM R2,R3
WHERE R2T руппа = R3T руппа;
Здесь часть WHERE задает условия соединения отношений R2 и R3,
при отсутствии условий соединения в части WHERE результат будет
эквивалентен расширенному декартову произведению, и в этом случае
каждому студенту были бы приписаны все дисциплины из отношения R3, а
не те, которые должна сдавать его группа.
Результат:
•
ФИО
Днси11плина
Петров Ф. И.
Базы данных
Сидоров К. А.
Базы данных
Петров Ф. И.
Теория информации
Сидоров К. А.
Теория информации
Петров Ф. И.
Английский язык
Сидоров К. А.
Английский язык
ТрофимовП А.
Сети и телекоммуникации
Иванова Е. А.
Сети и телекоммуникации
Уткина Н_В .
Сети и телекоммуникации
Трофимов П. А.
Английский язык
Иванова Е. А.
Английский язык
Уткина Н. В .
Английский язык
Вывести список лентяев, имеющих несколько двоек.
SELECT DISTINCT R l^ T O FROM R1 a, R1 b
WHERE a. ФИО = Ь.ФИО AND
a. Дисциплина <> b. Дисциплина AND
a.Оценка <= 2 AND ^Оценка <= 2;
35
Здесь
мы
использовали
псевдонимы
для
именования
отношения R1 а и b, так как для записи условий поиска нам необходимо
работать сразу с двумя экземплярами данного отношения.
Результат:
Из этих примеров хорошо видно, что логика работы оператора выбора
(декартово произведение-селекция-проекция) не совпадает с порядком
описания
в
нем данных
потом список таблиц
соединения).
Дело
для
в
(сначала список полей
декартова
том,
для
произведения,
что SQL изначально
потом
проекции,
условие
разрабатывался
для
применения конечными пользователями, и его стремились сделать, возможно
ближе к языку естественному, а не к языку алгоритмическому. По этой
причине SQL на первых порах вызывает путаницу и раздражение у
начинающих его изучать профессиональных программистов, которые
привыкли разговаривать с машиной именно на алгоритмических языках[18].
Наличие
неопределенных
( Null )
значений
повышает
гибкость
обработки информации, хранящейся в БД. В наших примерах мы можем
предположить ситуацию, когда студент пришел на экзамен, но не сдавал
его по некоторой причине, в этом случае оценка по некоторой дисциплине
для данного студента имеет неопределенное значение. В данной ситуации
можно поставить вопрос: "Найти студентов, пришедших на экзамен, но не
сдававших его с указанием названия дисциплины". Оператор SELECT будет
выглядеть следующим образом:
SELECT ФИО, Дисциплина FROM R1
WHERE Оценка IS NULL
Результат:
ФИО
Дисциплина
Миронов А. В. Теория информации
36
4.Применение агрегатных функций в операторе выбора.
В SQL добавлены
дополнительные
функции,
которые
позволяют
вычислять обобщенные групповые значения. Для применения агрегатных
функций предполагается предварительная операция группировки. В чем
состоит
суть операции группировки?
множество кортежей
При
отношения разбивается
группировке
на
группы,
в
все
которых
собираются кортежи, имеющие одинаковые значения атрибутов, которые
заданы в списке группировки.
Например, сгруппируем отношение R1 по значению столбца Дисципли
на. Мы получим 4 группы, для которых можем вычислить некоторые
групповые значения, например количество кортежей в группе, максимальное
или минимальное значение столбца Оценка.
Это
делается
с
помощью агрегатных
функций. Агрегатные
функции вычисляют одиночное значение для всей группы таблицы. Список
этих функций представлен в табл. 1.
Таблица 1. Агрегатные функции
Функция
Результат
COUNT
Количество строк или непустых значений полей, которые выбрал запрос
SUM
Сумма всех выбранных значений данного поля
AVG
Среднеарифметическое значение всех выбранных значений данного поля
MIN
Наименьшее из всех выбранных значений данного поля
MAX
Наибольшее из всех выбранных значений данного поля
Агрегатные
функции используются
подобно
именам
полей
в
операторе SELECT, но с одним исключением:
они берут имя поля
как аргумент.
использоваться
С
функциями SUM и AVG могут
только
числовые поля. С функциями COUNT, MAX и MIN могут использоваться как
числовые, так и символьные поля. При использовании с символьными
полями MAX и MIN будут транслировать их в эквивалент ASCII кода и
обрабатывать
в
алфавитном
порядке.
Некоторые СУБД позволяют
37
использовать вложенные агрегаты, но это является отклонением от
стандарта ANSI со всеми вытекающими отсюда последствиями.
Например,
можно
экзамены по каждой
вычислить
дисциплине.
количество
Для этого
студентов,
надо
сдававших
выполнить запрос с
группировкой по полю "Дисциплина" и вывести в качестве результата
название дисциплины и количество строк в группе по данной дисциплине.
Применение символа * в качестве аргумента функции COUNT означает
подсчет всех строк в группе.
SELECT R1.Дисциплина, COUNT(*) FROM R1
GROUP BY R1.Дисциплина
Результат:
Если же мы хотим сосчитать количество сдавших экзамен по какойлибо дисциплине, то нам необходимо исключить неопределенные значения
из исходного отношения перед группировкой. В этом случае запрос будет
выглядеть следующим образом:
SELECT R1. Дисциплина, COUNT(*) FROM R1
WHERE R1.Оценка IS NOT NULL
GROUP BY R1.Дисциплина
Получим результат:
В этом случае строка со студентом
38
Миронов А. В. Теория информации Null
не попадет в набор кортежей перед группировкой, поэтому количество
кортежей в группе для дисциплины "Теория информации" будет на 1
меньше.
Можно применять агрегатные функции также и без операции предвари
тельной группировки, в этом случае все отношение рассматривается как
одна группа и для этой группы можно вычислить одно значение на группу.
Обратившись снова к базе данных "Сессия" (таблицы R1, R2, R3 ),
найдем количество успешно сданных экзаменов:
SELECT COUNT(*) FROM R1
WHERE Оценка > 2;
Это,
конечно,
отличается
от
выбора поля,
поскольку
всегда
возвращается одиночное значение, независимо от того, сколько строк
находится
в
таблице.
Аргументом агрегатных
функций могут
быть
отдельные столбцы таблиц. Но для того, чтобы вычислить, например,
количество различных значений некоторого столбца в группе, необходимо
применить ключевое
Вычислим количество
слово DISTINCT совместно
различных
оценок,
с
именем
полученных по каждой
дисциплине:
SELECT R1.Дисциплина, COUNT(DISTINCT RLO^ h^ )
FROM R1
WHERE R1.Оценка IS NOT NULL
GROUP BY R1.Дисциплина
Результат:
столбца.
39
В
результат
несколько агрегатных
можно
включить значение поля
функций,
а
в
условиях
группировки
группировки
и
можно
использовать несколько полей. При этом группы образуются по набору
заданных полей группировки. Операции с агрегатными функциями могут
быть применены к объединению множества исходных таблиц. Например,
поставим вопрос: определить для каждой группы и каждой дисциплины
количество успешно сдавших экзамен и средний балл по дисциплине [19].
SELECT R1.Оценка, R1.Дисциплина, COUNT(*), АУЩОценка)
FROM R1,R2
WHERE R l^ MQ = К2.ФИО AND
R l.Оценка IS NOT NULL AND
Rl. Оценка > 2
GROUP BY R l.Оценка Rl. Дисциплина
Результат:
Мы
не
можем
использовать агрегатные
функции в
предложении WHERE, потому что предикаты оцениваются в терминах
одиночной строки, а агрегатные функции - в терминах групп строк.
Предложение GROUP BY позволяет определять подмножество значений
в особом поле в терминах другого поля и применять функцию агрегата к
подмножеству.
функции в
Это дает возможность объединять поля и агрегатные
едином
предложении SELECT. Агрегатные
функции могут
применяться как в выражении вывода результатов строки SELECT, так и в
выражении условия обработки сформированных групп HA VING. В этом
случае каждая агрегатная функция вычисляется для каждой выделенной
группы. Значения, полученные при вычислении агрегатных функций, могут
40
быть использованы для вывода соответствующих результатов или для
условия отбора групп.
Построим запрос, который выводит группы, в которых по одной
дисциплине на экзаменах получено больше одной двойки:
SELECT R2.Группа FROM R1,R2
WHERE R l^ MO = R2^ MO AND
R1.Оценка = 2
GROUP BY Я2.Группа, R1.Дисциплина
HAVING count(*)> 1
В дальнейшем в качестве примера будем работать не с БД "Сессия", а
с БД "Банк",
состоящей
из
одной
таблицы F,
в
которой
хранится отношение F, содержащее информацию о счетах в филиалах
некоторого банка:
F = (N, ФИО, Филиал, Дата Открытия, Дата Закрытия, Остаток);
Q = (Филиал, Город);
поскольку на этой базе можно ярче проиллюстрировать работу с
агрегатными функциями и группировкой.
Например, предположим, что мы хотим найти суммарный остаток на
счетах в филиалах. Можно сделать раздельный запрос для каждого из них,
выбрав SUM(Остаток) из таблицы для каждого филиала. GROUP BY, однако,
позволит поместить их все в одну команду:
SELECT Филиал, SUM(Остаток) FROM F
GROUP BY Филиал;
GROUP BY применяет агрегатные функции независимо для каждой
группы, определяемой с помощью значения поля Филиал. Группа состоит из
строк с одинаковым значением поля Филиал, и функция SUM применяется
отдельно для каждой такой группы, то есть суммарный остаток на счетах
подсчитывается отдельно для каждого филиала. Значение поля, к которому
применяется GROUP BY, имеет, по определению, только одно значение на
группу вывода, как и результат работы агрегатной функции. Поэтому мы
41
можем совместить в одном запросе агрегат и поле. Вы можете также
использовать GROUP BY с несколькими полями.
Предположим, что мы хотели бы увидеть только те суммарные
значения остатков на счетах, которые превышают $5000. Чтобы увидеть
суммарные
остатки
свыше
предложение HA VING.
$5000,
необходимо
использовать
Предложение HA VING определяет
критерии,
используемые, чтобы удалять определенные группы из вывода, точно так же
как предложение WHERE делает это для индивидуальных строк.
Правильной командой будет следующая:
SELECT Филиал, 8иМ(Остаток) FROM F
GROUP BY Филиал
HAVING SUM(Остаток) > 5000;
Аргументы в предложении HA VING подчиняются тем же самым
правилам, что и в предложении SELECT, где используется GROUP BY. Они
должны иметь одно значение на группу вывода [20].
Следующая команда будет запрещена:
SELECT Филиал, SUM(Остаток) FROM F
GROUP BY Филиал
HAVING Дата Открытия = 27/12/1999;
Поле Дата
Открытия
предложении HAVING,
одно значение на
не
потому
группу
может
что
вывода.
оно
Чтобы
быть
может
использовано
иметь
избежать
больше
такой
в
чем
ситуации,
предложение HAVING должно ссылаться только на агрегаты и поля,
выбранные GROUP
BY.
Имеется
правильный
способ
сделать
вышеупомянутый запрос:
SELECT Филиал, 8ИМ(Остаток) FROM F
WHERE Дата Открытия = '27/12/1999' GROUP BY Филиал;
Смысл данного запроса следующий: найти сумму остатков по каждому
филиалу счетов, открытых 27 декабря 1999 года.
42
Как
и
говорилось
ранее, HAVING может
использовать
только
аргументы, которые имеют одно значение на группу вывода. Практически,
ссылки на агрегатные функции - наиболее общие, но и поля, выбранные с
помощью GROUP BY, также допустимы. Например, мы хотим увидеть
суммарные остатки на счетах филиалов в Санкт-Петербурге, Пскове и
Урюпинске:
SELECT Филиал, ЗиМ(Остаток) FROM F,Q
WHERE F. Филиал = Q. Филиал
GROUP BY Филиал
HAVING Город IN ("Санкт-Петербург", "Псков", "Урюпинск");
Поэтому в арифметических выражениях предикатов, входящих в
условие выборки раздела HAVING, прямо можно использовать только
спецификации столбцов, указанных в качестве столбцов группирования в
разделе GROUP BY. Остальные столбцы можно специфицировать только
внутри спецификаций агрегатных функций COUNT, SUM, AVG, MIN и MAX,
вычисляющих в данном случае некоторое агрегатное значение для всей
группы строк. Аналогично обстоит дело с подзапросами, входящими в
предикаты
условия
выборки
раздела HAVING:
если
в
подзапросе
используется характеристика текущей группы, то она может задаваться
только путем ссылки на столбцы группирования.
Результатом
выполнения
раздела HA VING является
сгруппированная таблица, содержащая только те группы строк, для которых
результат
вычисления условия
раздел HAVING присутствует
поиска есть TRUE.
в
табличном
В
частности,
выражении,
если
не
содержащем GROUP BY, то результатом его выполнения будет либо
пустая таблица,
либо
результат
выполнения
предыдущих
разделов табличного выражения, рассматриваемый как одна группа без
столбцов группирования.
43
2.4.3. Лекция 3. Сложные запросы к базе данных.
Цели:
Обучающая - рассказать об различных видах запросов в базе данных
разъяснить основные функции.
Развивающая - развить у обучающихся логическое мышление, память,
внимание;
способности четко
формулировать
свои мысли;
развитие
воображения студентов; развитие навыков конспектирования; развитие
интереса к предмету; развитие навыков работы на компьютере;
Воспитательная - развитие кругозора, вызвать желание освоить эти
знания; воспитание аккуратности при выполнении заданий, культуры труда,
бережного отношения к технике; воспитание информационной культуры
учащихся; воспитание у учащихся мотивации учебной деятельности.
План лекций:
1. Вложенные запросы.
2. Внешние объединения.
З.Операторы манипулирования данными.
Рекомендации преподавателю:
Данную
лекцию
удобно
провести,
используя
мультимедийные
технологии, т.к. лекция содержит большое количество визуальных примеров.
Практически все сказанное преподавателем необходимо записать в
тетрадь.
Содержание лекции:
1. Вложенные запросы.
Теперь рассмотрим БД "Сессия" и на ее примере использование
вложенных запросов.
С помощью SQL можно вкладывать запросы внутрь друг друга.
Обычно внутренний запрос генерирует значение, которое проверяется в
предикате
внешнего запроса
(в
предложении WHERE или HA VING),
определяющего, верно оно или нет. Совместно с подзапросом можно
44
использовать предикат
EXISTS,
который
возвращает
истину,
если вывод подзапроса не пуст.
В сочетании с другими возможностями оператора выбора, такими как
группировка, подзапрос представляет
собой
мощное
средство
для
достижения нужного результата. В части FROM оператора SELECT допустим
о применять синонимы к именам таблицы, если при формировании запроса
нам требуется более чем один экземпляр некоторого отношения. Синонимы
задаются с использованием ключевого слова AS, которое может быть вообще
опущено. Поэтому часть FROM может выглядеть следующим образом:
FROM R1 AS A, R1 AS B
или
FROM R1 A, R1 B;
оба выражения эквивалентны и рассматриваются как применения
оператора SELECT к двум экземплярам таблицы R1.
Например,
покажем,
как
выглядят
на SQL некоторые
запросы
к БД "Сессия":
Список тех, кто сдал все положенные экзамены.
SELECT ФИО FROM R1 as a
WHERE Оценка > 2
GROUP BY ФИО
HAVING COUNT(*) = (SELECT COUNT(*)
FROM R2,R3
WHERE R2T руппа=Я3 .Группа AND ФИО=а ФИО)
Здесь во встроенном запросе определяется общее число экзаменов,
которые должен сдавать каждый студент, обучающийся в группе, в которой
учится данный студент, и это число сравнивается с числом экзаменов,
которые сдал данный студент.
Список тех, кто должен был сдавать экзамен по БД, но пока еще не
сдавал.
SELECT ФИО FROM R2 a, R3
45
WHERE R2.rpynna=R3.Группа AND Дисциплина = "БД" AND NOT
EXISTS (SELECT ФИО
FROM R1
WHERE ФИО=а.ФИО AND Дисциплина = "БД")
Предикат EXISTS ( SubQuery) истинен, когда подзапрос SubQuery не
пуст, то есть содержит хотя бы один кортеж, в противном случае
предикат EXISTS ложен.
Предикат NOT EXISTS обратно - истинен только тогда, когда
подзапрос SubQuery пуст.
Обратите
внимание,
каким
образом NOT
EXISTS с
вложенным
запросом позволяет обойтись без операции разности отношений. Например,
формулировка запроса со словом "все" может быть выполнена как бы с
двойным отрицанием. Рассмотрим пример базы, которая моделирует
поставку отдельных деталей отдельными поставщиками, она представлена
одним отношением SP"Поставщики-детали" со схемой
SP
(Номер_поставщика,
номер_детали)
P
(номер_детали,
наименование)
Вот
каким
образом
формулируется
ответ
на запрос:
"Найти
поставщиков, которые поставляют все детали".
SELECT
DISTINCT
НОМЕР_ПОСТАВЩИКА
FROM
SP
SP1
WHERE NOT EXISTS
(SELECT номер_детали FROM P WHERE NOT EXISTS
(SELECT * FROM SP SP2
WHERE SP2.номер_поставщика=SP1.номер_поставщика AND
sp2.номер_детали = P.номер_детали));
Фактически
мы
переформулировали
этот запрос так:
"Найти
поставщиков таких, что не существует детали, которую бы они не
поставляли". Следует отметить, что этот запрос может быть реализован и
через агрегатные функции с подзапросом:
SELECT DISTINCT Номер_поставщика
46
FROM SP
GROUP BY Номер_поставщика
HAVING Count(DISTINCT номер_детали) =
(SELECT Count( номер_детали)
FROM P)
В стандарте SQL92 операторы сравнения расширены до многократных
сравнений с использованием ключевых слов ANY и ALL. Это расширение
используется при сравнении значения определенного столбца со столбцом
данных, возвращаемым вложенным запросом.
Ключевое слово ANY, поставленное в любом предикате сравнения,
означает, что предикат будет истинен, если хотя бы для одного значения из
подзапроса предикат
сравнения истинен. Ключевое
слово ALL требует,
чтобы предикат сравнения был бы истинен при сравнении со всеми строками
подзапроса.
Например, найдем студентов, которые сдали все экзамены на оценку не
ниже чем "хорошо". Работаем с той же базой "Сессия", но добавим к ней еще
одно отношение R4, которое характеризует сдачу лабораторных работ в
течение семестра:
R1 = (ФИО, Дисциплина, Оценка);
R2 = (ФИО, Группа);
R3 = (Группы, Дисциплина )
R4 = (ФИО, Дисциплина, Номерлабраб, Оценка);
SELECT R 1 ^ to FROM R1 WHERE 4 < = All (SELECT R1.Оценка
FROM R1 as R11
WHERE R 1 ^ to = R 11^ to)
Рассмотрим еще один пример:
Выбрать студентов, у которых оценка по экзамену не меньше, чем хотя
бы
одна
оценка по сданным
дисциплины:
SELECT R1^TO
им
лабораторным
работам поданной
47
FROM R1
WHERE R1.Оценка >= ANY (Select R4.Оценка
FROM R4
WHERE R1. Дисциплина = R4. Дисциплина AND R l^ MO = R4^ MO)
2.Внешние объединения.
Стандарт SQL2 расширил понятие условного объединения. В стандарте
SQL1
при
объединении отношений
использовались только условия,
задаваемые в части WHERE оператора SELECT, и в этом случае в
результирующее отношение попадали только
сцепленные
по заданным
условиям кортежи исходных отношений, для которых эти условия были
определены и истинны. Однако в действительности часто необходимо
объединять таблицы таким образом, чтобы в результат попали все строки из
первой таблицы, а вместо тех строк второй таблицы, для которых не
выполнено условие соединения, в результат попадали бы неопределенные
значения. Или наоборот, включаются все строки из правой (второй) таблицы,
а
отсутствующие
части
строк
из
первой
таблицы
дополняются
неопределенными значениями. Такие объединения были названы внешними
в противоположность объединениям, определенным стандартом SQL1,
которые стали называться внутренними.
В общем случае синтаксис части FROM в стандарте SQL2 выглядит
следующим образом:
FROM <список исходных таблиц>
< выражение естественного объединения >
< выражение объединения >
< выражение перекрестного объединения >
< выражение запроса на объединение >
<список исходных таблиц>::= <имя_таблицы_1> [ имя синонима
таблицы_1] [ ...] [,<имя_таблицы_л>[ <имя синонима таблицы_n> ] ]
<выражение естественного объединениям: =
48
<имя_таблицы_1 > NATURAL { INNER | FULL [OUTER] LEFT
[OUTER] | RIGHT [OUTER]} JOIN <имя таблицы 2>
<выражение
перекрестного
объединения>::
=
<имя_таблицы_1>
CROSS JOIN <имя_таблицы_2>
<выражение запроса на объединение>::=
<имя_таблицы_1> UNION JOIN <имя_таблицы_2>
<выражение объединения>::= <имя_таблицы_1> { INNER
FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER]}
JOIN {ON условие | [USING (список столбцов)]} <имя_таблицы_2>
В этих определениях INNER -означает внутреннее объединение, LEFT левое объединение, то есть в результат входят все строки таблицы 1, а части
результирующих кортежей, для которых не было соответствующих значений
в таблице 2, дополняются значениями NULL(неопределенно). Ключевое
слово RIGHT означает правое внешнее объединение, и в отличие от левого
объединения в этом случае в результирующее отношение включаются все
строки таблицы 2, а недостающие части из таблицы 1 дополняются
неопределенными значениями, Ключевое слово FULL определяет полное
внешнее объединение: и левое и правое. При полном внешнем объединении
выполняются
и
правое
и
левое
результирующее отношение включаются
внешние
все
объединения
строки
из
и
в
таблицы
1,
дополненные неопределенными значениями, и все строки из таблицы 2,
также дополненные неопределенными значениями.
Ключевое слово OUTER означает внешнее, но если заданы ключевые
слова FULL, LEFT, RIGHT, то объединение всегда считается внешним.
Рассмотрим примеры выполнения внешних объединений.
Снова
вернемся к БД "Сессия". Создадим отношение, в котором будут стоять все
оценки, полученные всеми студентами по всем экзаменам, которые они
должны были сдавать. Если студент не сдавал данного экзамена, то вместо
оценки у него будет стоять неопределенное значение. Для этого выполним
последовательно естественное внутреннее объединение таблиц R2 и R3
49
по атрибуту Группа, а полученное отношение соединим левым внешним
естественным
объединением
с таблицей R1,
используя
столбцы ФИО
и Дисциплина. При этом в стандарте разрешено использовать скобочную
структуру, так как результат объединения может быть одним из аргументов в
части FROM оператора SELECT.
SELECT R1.ФИО, R1.Дисциплина, R1.Оценка
FROM (R2 NATURAL INNER JO IN R3) LEFT JOIN R1 USING ( ФИО,
Дисциплина)
Результат:
ФИО
Дисциплина
Оценка
П етров Ф. И.
Базы дан н ы х
5
Сидоров К. А.
Базы дан н ы х
4
М иронов А. В.
Базы дан н ы х
2
М иронов А. В.
Теория инф орм ации Null
Степанова К. Е.
Теория инф орм ации 2
Крылова Т. С
Теория инф орм ации 5
ФИО
Дисциплина
Оценка
Владимиров В. А. Теория информации
Null
Петров Ф. И.
Английский язык
5
Сидоров К. А.
Английский язык
Null
Владимиров В. А. Английский язык
4
Трофимов П. А.
Сети и телекоммуникации 4
Иванова Е. А.
Сети и телекоммуникации 5
Иванова Е. А.
Английский язык
3
Уткина Н. В,
Английский язык
Null
Рассмотрим еще один пример, для этого возьмем БД "Библиотека". Она
состоит из трех отношений, имена атрибутов здесь набраны латинскими
буквами, что является необходимым в большинстве коммерческих СУБД.
BO O K S (ISBN, TITLE, AUTOR, COAUTOR, YEARIZD, PAGES)
READER (N U M READER, NAME READER, ADRESS,
H O O M P H O N E , W O R K P H O N E , BIRTH DAY)
50
EXEMPLARE
(INV,
ISBN,
YES_NO,
NUMREADER,
DATEIN,
DATEOUT)
Здесь таблица BOOKS описывает
все
книги,
присутствующие
в
библиотеке, она имеет следующие атрибуты:
• ISBN — уникальный шифр книги;
• TITLE — название книги;
• A UTOR — фамилия автора;
• COA UTOR — фамилия соавтора;
• YEARIZD — год издания;
• PAGES — число страниц.
Таблица READER хранит сведения обо всех читателях библиотеки, и
она содержит следующие атрибуты:
• NUMREADER - уникальный номер читательского билета;
• NAMEREADER - фамилию и инициалы читателя;
• ADRESS - адрес читателя;
• HOOM PHONE - номер домашнего телефона;
•
WORK PHONE - номер рабочего телефона;
• BIRTH_DAY- дату рождения читателя.
Таблица EXEMPLARE содержит сведения о текущем состоянии всех
экземпляров всех книг. Она включает в себя следующие столбцы:
• INV - уникальный инвентарный номер экземпляра книги;
• ISBN - шифр книги, который определяет, какая это книга, и ссылается
на сведения из первой таблицы;
• YES NO - признак наличия или отсутствия в библиотеке данного
экземпляра в текущий момент;
• NUM READER - номер читательского билета, если книга выдана
читателю, и Null в противном случае;
• DATE IN - если книга у читателя, то это дата, когда она выдана
читателю;
• DATE OUT - дата, когда читатель должен вернуть книгу в библиотеку.
51
Определим перечень книг у каждого читателя; если у читателя нет
книг, то номер экземпляра книги равен NULL. Для выполнения этого поиска
нам надо использовать левое внешнее объединение, то есть мы берем все
строки
из
таблицы READER и
таблицы EXEMPLARE,
если
во
соединяем
второй
со
строками
таблице
нет
соответствующим
из
строки
с
номером
читательского билета, то в строке результирующего отношения атрибут EXE
MPLARE.INV будет иметь неопределенное значение NULL:
SELECT READER.NAMEREADER, EXEMPLARE.INV
FROM READER LEFT JOIN EXEMPLARE
ON READER.NUM_READER=EXEMPLARE.NUM_READER
Операция внешнего объединения, как мы уже упоминали, может
использоваться для формирования источников в предложении FROM,
поэтому допустимым будет, например, следующий текст запроса:
SELECT *
FROM ( BOOKS LEFT JOIN EXEMPLARE)
LEFT JOIN (READER NATURAL JOIN EXEMPLARE)
USING (ISBN)
При этом для книг, ни один экземпляр которых не находится на руках у
читателей, значения номера читательского билета и дат взятия и возврата
книги будут неопределенными.
Перекрестное объединение в трактовке стандарта SQL2 соответствует
операции расширенного декартова произведения, то есть операции
соединения двух таблиц, при которой каждая строка первой таблицы
соединяется с каждой строкой второй таблицы.
Операция запроса на объединение эквивалентна операции теоретико­
множественного
объединения
в
алгебре.
При
этом
требование
эквивалентности схем исходных отношений сохраняется. Запрос на объедине
ние выполняется по следующей схеме:
SELECT - запрос
52
UNION
SELECT - запрос
UNION
SELECT - запрос
Все запросы, участвующие в операции объединения, не должны
содержать выражений, то есть вычисляемых полей.
Например, нужно вывести список читателей, которые держат на руках
книгу "Идиот" или книгу "Преступление и наказание". Вот как будет
выглядеть запрос:
SELECT READER.NAME_READER
FROM READER, EXEMPLARE,BOOKS
WHERE
EXEMPLARE.NUM_READER=
READER.NUM_READER
AND
EXEMPLARE.ISBN = BOOKS.ISBN AND
BOOKS.TITLE = "Идиот"
UNION
SELECT READER.NAME_READER
FROM READER, EXEMPLARE,BOOKS
WHERE
EXEMPLARE.NUM_READER=
READER.NUM_READER
AND
EXEMPLRE.ISBN = BOOKS.ISBN AND
BOOKS.TITLE = "Преступление и наказание"
По умолчанию при выполнении запроса на объединение дубликаты
кортежей всегда исключаются. Поэтому, если найдутся читатели, у которых
находятся
на
руках
обе
книги,
то
они
все
равно
в
результирующий список попадут только один раз.
Запрос на объединение может объединять любое число исходных
запросов.
Так, к предыдущему запросу можно добавить еще читателей, которые
держат на руках книгу "Замок":
53
UNION
SELECT READER.NAME_READER
FROM READER, EXEMPLARE, BOOKS
WHERE
EXEMPLARE.NUM_READER=
READER.NUM_READER
AND
EXEMPLARE.ISBN = BOOKS.ISBN AND
BOOKS.TITLE = "Замок"
В том случае, когда вам необходимо сохранить все строки из исходных
отношений,
необходимо
использовать ключевое
слово ALL в операции
объединения. В случае сохранения дубликатов кортежей схема выполнения
запроса на объединение будет выглядеть следующим образом:
SELECT - запрос
UNION ALL
SELECT - запрос
UNION ALL
SELECT - запрос
Однако тот же результат можно получить простым изменением
фразы WHERE первой
части исходного
запроса,
соединив локальные
условия логической операцией ИЛИ и исключив дубликаты кортежей.
SELECT DISTINCT READER.NAME_READER
FROM READER, EXEMPLARE, BOOKS
WHERE
EXEMPLARE.NUM_READER=
READER.NUM_READER
AND
EXEMPLARE.ISBN = BOOKS.ISBN AND
BOOKS.TITLE = "Идиот" OR
BOOKS.TITLE = "Преступление и наказание" OR
BOOKS.TITLE = "Замок"
Ни
один
из
исходных запросов
в операции UNION не
должен
содержать предложения упорядочения результата ORDER BY, однако
результат
объединения
может
быть
упорядочен,
для
этого
54
предложение ORDER BY с указанием
списка столбцов упорядочения
записывается после текста последнего исходного SELECT- запроса.
З.Операторы манипулирования данными.
В операции манипулирования
данными
входят
три
операции:
операция удаления записей - ей соответствует оператор DELETE, операция
добавления или ввода новых записей - ей соответствует оператор INSERT и
операция
изменения
(обновления
записей)
-
ей
соответствует
оператор UPDATE. Рассмотрим каждый из операторов подробнее.
Все операторы манипулирования данными позволяют изменить данные
только в одной таблице.
Оператор ввода данных INSERT имеет следующий синтаксис:
INSERT INTO имя_таблицы [(<список столбцов>) ]
VALUES (<список значений>)
Подобный синтаксис позволяет ввести только одну строку в таблицу.
Задание списка столбцов необязательно тогда, когда мы вводим строку с
заданием значений всех столбцов. Например, введем новую книгу в
таблицу BOOKS
INSERT INTO BOOKS (ISBN,TITLE,AUTOR,COAUTOR,YEARIZD,
PAGES)
VALUES
("5-88782-290-2",
"Аппаратные
средства
IBM
PC.
Энциклопедия", "Гук М. ","",2000,816)
В этой книге только один автор, нет соавторов, но мы в списке
столбцов задали столбец COAUTOR, поэтому мы должны были ввести
соответствующее значение
в разделе VALUES. Мы ввели пустую строку, потому что мы знаем
точно, что нет соавтора. Мы могли бы ввести неопределенное значение
NULL.
Так
как
мы
вводим
полную
строку,
то
мы
можем
не
задавать список столбцов, ограничиться только заданием перечня значений, в
этом случае оператор ввода будет выглядеть следующим образом:
55
INSERT INTO BOOKS VALUES ("5-88782-290-2", "Аппаратные средства
IBM PC. Энциклопедия","Гук М.","",2000,816).Результаты работы обоих
операторов одинаковые.
Наконец, мы можем ввести неполный перечень значений, то есть не
вводить соавтора, так как он отсутствует для данного издания. Но в этом
случае мы должны задать список вводимых столбцов, тогда оператор ввода
будет выглядеть следующим образом:
INSERT INTO BOOKS ( ISBN,TITLE,A UTOR,YEARIZD,PAGES)
VALUES ("5-88782-290-2",
"Аппаратные средства IBM PC. Энциклопедия",
"Гук М.",2000,816)
Столбцу COAUTOR будет присвоено в этом случае значение NULL.
Какие столбцы должны быть заданы при вводе данных? Это
определяется тем, как описаны эти столбцы при описании соответствующей
таблицы, и будет рассмотрено более подробно при описании языка Data
Definition Language (DDL). Здесь мы пока отметим, что если столбец
или атрибут имеет признак обязательный ( NOT NULL ) при описании
таблицы, то оператор INSERT должен обязательно содержать данные для
ввода в каждую строку данного столбца. Поэтому если в таблице все столбцы
обязательные, то каждая вводимая строка должна содержать полный
перечень вводимых значений, а указание имен столбцов в этом случае
необязательно.
В
противном
случае,
если
имеется
хотя
бы
один
необязательный столбец и вы не вводите в него значений, задание списка
имен столбцов - обязательно.
В набор значений могут быть включены специальные функции и
выражения. Ограничением здесь является то, что значения этих функций
должны быть определены на момент ввода данных. Поэтому, например, мы
можем сформировать оператор ввода данных в таблицу EXEMPLARE
следующим образом:
56
INSERT INTO EXEMPLARE (INV,ISBN, YES_NO,NUM_READER,DA TE_
IN, DATE OUT)
VALUES (1872, "5-88782-290-2",NO,
344,GetDate(),DateAdd(d,GetDate(),14))
И это означает, что мы выдали экземпляр книги с инвентарным
номером 1872 читателю с номером читательского билета 344, отметив, что
этот экземпляр не присутствует с этого момента в библиотеке, и определили
дату выдачи книги как текущую дату (функция GetDate() ), а дату возврата
задали двумя неделями позднее, использовав при этом функцию DateAdd (),
которая позволяет к одной дате добавить заданное количество интервалов
даты и тем самым получить новое значение типа "дата". Мы добавили 14
дней к текущей дате.
Оператор ввода данных позволяет ввести сразу множество строк, если
их можно выбрать из некоторой другой таблицы. Допустим, что у нас есть
таблица со студентами и в ней указаны основные данные о студентах: их
фамилии, адреса, домашние телефоны и даты рождения. Тогда мы можем
сделать всех студентов читателями нашей библиотеки одним оператором:
INSERT INTO READER (NAME READER, ADRESS, HOOMPHONE,
BIRTHDAY)
SELECT (NAME STUDENT, ADRESS, HOOM PHONE, BIRTH DAY)
FROM STUDENT
При
этом
номер
читательского
билета
может
назначаться
автоматически, поэтому мы не вводим значения этого столбца в таблицу.
Кроме того, мы предполагаем, что у студентов дневного отделения еще нет
работы и поэтому нет рабочего телефона, и мы его не вводим.
Оператор удаления данных позволяет удалить одну или несколько
строк из таблицы в соответствии с условиями, которые задаются для
удаляемых строк.
Синтаксис оператора DELETE следующий:
57
DELETE FROM имя_таблицы [WHERE условия_отбора]
Если условия отбора не задаются, то из таблицы удаляются все строки,
однако
это
не
означает,
что
удаляется
вся таблица.
Исходная таблица остается, но она остается пустой, незаполненной.
Например, если нам надо удалить результаты прошедшей сессии, то мы
можем удалить все строки из отношения R1 командой
DELETE FROM R1
Условия отбора в части WHERE имеют тот же вид, что и условия
фильтрации в операторе SELECT. Эти условия определяют, какие строки из
исходного отношения будут удалены. Например, если мы исключим студента
Миронова А. В., то мы должны написать следующую команду:
DELETE FROM R2
WHERE ФИО = 'Миронов А.В.'
В части WHERE может находиться встроенный запрос. Например, если
нам надо исключить неуспевающих студентов, то по закону о высшем
образовании неуспевающим считается студент, имеющий две и более
задолженности по последней сессии. Тогда нам в условиях отбора надо найти
студентов, имеющих либо две или более двоек, либо два и более несданных
экзамена из числа тех, которые студент сдавал. Для поиска таких горестудентов нам надо выбрать из отношения R1 все строки с оценкой 2 или с
неопределенным
значением,
потом
надо
сгруппировать
полученный
результат по атрибуту ФИО и, подсчитав количество строк в каждой группе,
которое соответствует количеству несданных экзаменов каждым студентом,
отобрать те группы, у которых количество строк не менее двух. Теперь
попробуем просто записать эту сложную конструкцию на SQL и убедимся,
что этот сложный запрос записывается достаточно компактно.
DELETE FROM R2 WHERE Я2.ФИО IN (SELECT R l^ T O FROM R1
WHERE Оценка = 2 OR Оценка IS NULL
GROUP BY R 1 ^ r o HAVING COUNT(*) >= 2
58
Однако при выполнении операции DELETE, включающей сложный
подзапрос, в подзапросе нельзя упоминать таблицу, из которой удаляются
строки, поэтому СУБД отвергнет такой красивый подзапрос, который
попытается удалить всех не только сдававших, но и не сдававших студентов,
которые имеют более двух задолженностей.
DELETE FROM R2 WHERE Я2.ФИО IN (SELECT Я1.ФИО
FROM (R2 NATURAL INNER JOIN R3 )
LEFT JOIN R1 USING ( ФИО, Дисциплина)
WHERE Оценка = 2 OR Оценка IS NULL
GROUP BY R 1 ^ r o
HAVING COUNT(*) >= 2
Все
операции
манипулирования данными связаны с понятием
целостности базы данных. В настоящий момент мне бы хотелось отметить
только то, что операции манипулирования данными не всегда выполнимы,
даже если синтаксически они написаны правильно. Действительно, если мы
бы захотели удалить какую-нибудь группу из отношения R3, то СУБД не
позволила бы нам это сделать, так как в отношениях R1 и R2 есть строки,
связанные с удаляемой строкой в отношении R3. Почему так делается, мы
узнаем позднее, а пока просто примем к сведению, что не все операторы
манипулирования выполнимы.
Операция обновления данных UPDATE требуется тогда, когда
происходят изменения во внешнем мире и их надо адекватно отразить в базе
данных, так как надо всегда помнить, что база данных отражает некоторую
предметную область. Например, в нашем учебном заведении произошло
счастливое событие, которое связано с тем, что госпожа Степанова К. Е.
пересдала экзамен по дисциплине "Базы данных" с двойки сразу на четверку.
В этом случае нам надо срочно выполнить соответствующую корректировку
таблицы R1. Операция обновления имеет следующий формат:
UPDATE имя_таблицы
SET имя_столбца = новое_значение [WHERE условие_отбора]
59
Часть WHERE является необязательной, так же как и в операторе
DELETE. Она играет здесь ту же роль, что и в операторе DELETE, позволяет
отобрать
строки,
к
которым
будет
применена
операция
модификации. Если условие отбора не задается, то операция модификации
будет применена ко всем строкам таблицы.
Для решения ранее поставленной задачи нам необходимо выполнить
следующую операцию
UPDATE R1
SET R1.Оценка = 4
WHERE ЯЕФИО = "Степанова К.Е." AND ЯЕДисциплина = "Базы
данных"
В каких случаях требуется провести изменение в нескольких строках?
Это не такая уж редкая задача. Например, если мы расширим нашу учебную
базу данных еще одним отношением, которое содержит перечень курсов, на
которых учатся наши студенты, то можно с помощью операции обновления
промоделировать операцию перевода групп на следующий курс. Пусть новое
отношение R4 имеет следующую схему:
R4 = <Группа, Курс>
В этом случае перевод на следующий курс можно выполнить
следующей операцией обновления:
UPDATE R4
SET R4.Kypc = R4.Kypc + 1
И результат будет выглядеть следующим образом:
60
Операция модификации, так же как и операция удаления, может
использовать сложные подзапросы. Расширим нашу базу еще одним
отношением, которое будет содержать перечень студентов, получающих
стипендию с указанием надбавки, которую они получают за отличную учебу.
Исходно там могут находиться все студенты с указанием неопределенного
размера стипендии. По мере анализа отношения R1 мы можем постепенно
заменять неопределенные значения на конкретные размеры стипендии.
Отношение R5 имеет вид:
Будем считать наличие трех пятерок по сессии признаком повышенной
стипендии, + 50% к основной, наличие двух пятерок из сданных экзаменов и
отсутствие двоек и троек на сданных экзаменах - признаком повышения
стипендии на 25%, наличие хотя бы одной двойки среди сданных экзаменов признаком снятия или отсутствия стипендии вообще, то есть - 100%
надбавки. При отсутствии троек на сданных экзаменах назначим обычную
стипендию с надбавкой 0%. Однако все эти изменения мы должны будем
сделать отдельными операциями обновления.
Назначение повышенной стипендии:
61
UPDATE R5
SET R5.CrnneHA^ = 50% WHERE R5.ФИО IN
(SELECT RE ФИО
FROM R1
WHERE R1. Оценка = 5
GROUP BY R l ^ O
HAVING COUNT(*) =3 )
Назначение стипендии с надбавкой 25%:
UPDATE R5
SET R5. Стипендия = 25% WHERE R 5 ^ T O
IN (SELECT R 1 ^ T O FROM R1
WHERE R 1 ^ T O NOT
IN (SELECT A. ФИО FROM R1 A
WHERE A. Оценка <=3 OR A. Оценка IS NULL)
GROUP BY R 1 ^ T O HAVING COUNT(*)>=2 )
Назначение обычной стипендии:
UPDATE R5
SET R5.Стипендия = 0%
WHERE R 5 ^ T O IN (SELECT R1.ФИO FROM R1
WHERE R1. Оценка >=4 AND R 1 ^ T O NOT IN (SELECT A. ФИО
FROM R1 A
WHERE A. Оценка <= 3 OR A. Оценка IS NULL) )
Снятие стипендии:
UPDATE R5
SET R5. Стипендия = -100% WHERE R 5 ^ T O IN
(SELECT R 1 ^ T O FROM R1
WHERE R1. Оценка <= 2 OR
R1.Oценка IS NULL)
Почему мы в первом запросе на обновление не использовали
дополнительную проверку на отсутствие двоек, троек и несданных
62
экзаменов, как мы сделали это при назначении следующих видов стипендии?
Просто мы учли особенности нашей предметной области: у нас в
соответствии с исходными данными только 3 экзамена. Но если мы можем
предположить, что число экзаменов может быть произвольным и изменяться
от семестра к семестру, то нам надо изменить наш запрос. Запрос - это
некоторый алгоритм решения конкретной задачи, которую мы формулируем
заранее на естественном языке. И оттого, что наша задача решается всего
одним оператором языка SQL, она не становится примитивной.
Мощность языка SQL и состоит в том, что он позволяет одним
предложением сформулировать ответы на достаточно сложные запросы, для
реализации которых на традиционных языках понадобилось бы писать
большую программу. Итак, подумаем, как нам надо изменить текст нашего
запроса на обновление для назначения повышенной стипендии при любом
количестве сданных экзаменов. Прежде всего, каждая группа может иметь
свое число экзаменов в сессию, это зависит от специальности и учебного
плана, по которому учится данная группа. Поэтому для каждого студента нам
надо знать, сколько экзаменов он должен был сдавать и сколько экзаменов он
сдал на пять, и в том случае, когда эти два числа равны, мы можем назначить
ему повышенную стипендию.
Будем решать нашу задачу по шагам. В конечном счете, нам все равно
надо знать, сколько экзаменов должен сдавать каждый конкретный студент,
поэтому сначала сосчитаем количество экзаменов, которые должна сдавать
группа, в которой учится этот студент.
Это мы делать умеем, для этого надо сделать запрос SELECT над
отношением R3, сгруппировав его по атрибуту Группа, и вывести для каждой
группы количество дисциплин, по которым должны сдаваться экзамены.
Если мы учтем, что в одной сессии по одной дисциплине не бывает более
одного экзамена, то можно просто подсчитывать количество строк в каждой
группе.
SELECT R3.Группа, число_экзаменов = COUNT(*)
63
FROM R3 GROUP BY R3.Группа
Однако нам нужен не этот запрос, нам нужен запрос, в котором мы
определяем для каждого студента количество экзаменов. Этот запрос мы
должны строить по схеме встроенного запроса:
SELECT COUNT(*)
FROM R3
WHERE R2T руппа = R3T руппа
GROUP BY R3.Группа
А почему мы здесь в части FROM не написали имя второго отношения
R2? Мы имя этого отношения укажем для связи с вышестоящим запросом,
когда
будем
формировать
запрос
полностью.
Теперь
попробуем
сформулировать полностью запрос. Нам надо объединить отношения R1 и R2
по атрибуту ФИО, нам надо знать группу, в которой учится каждый студент,
далее надо выбрать все строки с оценкой 5 и сгруппировать их по фамилии
студента, сосчитав количество строк в каждой группе, а выбирать будем те
группы, в которых число строк в группе равно числу строк во встроенном
запросе, рассмотренном ранее, при условии равенства количества строк в
группе результату подзапроса, который выводит только одно число.
SELECT R 1 ^ r o FROM R1,R2
WHERE R1. ФИО = Р2.ФИО AND R1.Оценка = 5
GROUP BY R 1 ^ r o HAVING COUNT(*) = (SELECT COUNT(*)
FROM R3 WHERE R2. Группа = R3. Группа
GROUP BY R3.Группа)
Ну а теперь нам осталась последняя простейшая операция: надо
заменить старый вложенный запрос, определявший отличников, получивших
три пятерки на сессии, на новый универсальный запрос:
UPDATE R5
SET R5.Стипендия = 50%
WHERE Я5.ФИО IN (SELECT R 1 ^ T O
FROM R1,R2
64
WHERE R1. ФИО = Я2.ФИО AND
R1. Оценка = 5 GROUP BY R l ^ O
HAVING COUNT(*) = (SELECT COUNT(*) FROM R3
WHERE R2.rpynna = ЯЭ.Группа GROUP BY R3Tpynna))
Вот какой сложный запрос мы построили. Это ведь практически один
оператор, а какую сложную задачу он решает. Действительно, мощность
языка SQL иногда удивляет даже профессионалов, кажется, невозможно
построить один запрос для решения конкретной задачи, но когда начинаешь
поэтапно его конструировать - все получается. Самое сложное - это сделать
переход от словесной формулировки задачи к представлению ее в терминах
нашего SQL, но этот процесс сродни процессу алгоритмизации при решении
задач традиционного программирования, а он всегда был самым трудным,
творческим и неформализуемым процессом. Недаром на заре развития
программирования американский специалист по программированию Дональд
Е. Кнут озаглавил свой многотомный капитальный труд по теории и
практике программирования "Искусство программирования для ЭВМ" ("The
art of computer programming").
2.4.4.
Лабораторная работа №1. Создание и заполнение базы
данных.
Цель: ознакомиться с возможностями интерактивной программы
MySQL, создать с ее помощью базу данных, набор таблиц в ней и заполнить
таблицы данными для последующей работы.
Содержание работы
1. Ознакомиться с правилами работы в MySQL.
2. Изучить набор команд языка SQL, связанный с созданием базы
данных, созданием и заполнение структуры таблиц, вставкой, модификацией
и удалением записей таблиц.
Задания.
65
1. Спроектировать базу данных по описанию предметной области
согласно условиям вашего варианта.
2. Реализовать спроектированную базу данных в СУБД MySQL,
заполнить таблицы созданной базы. Задать ключевые поля для созданных
таблиц. В каждой основной таблице базы данных должно быть не менее 5, а в
дочерних - не менее 10 записей.
Пример варианта предметной области
Предметная область: Библиотека.
Минимальный список характеристик:
•
Автор книги, название, год издания, цена, является ли
новым изданием, краткая аннотация;
•
номер читательского билета, ФИО, адрес и телефон
читателя, дата выдачи книги читателю и дата сдачи книги читателем.
Возможные варианты предметной области приведены в Приложении 1.
2.4.5.
Лабораторная работа №2. Создание простых запросов к базе
данных.
Цель: используя данные базы данных, подготовленной в первой
лабораторной работе, подготовить и реализовать серию запросов, связанных
с выборкой информации данных таблиц.
Содержание работы
Изучить основные запросы языка SQL, связанные с выборкой данных
из базы данных.
Задания.
1.Вывести все столбцы одной из таблиц вашей базы данных.
2. Вывести один столбец одной из таблиц вашей базы данных.
3. Вывести два или три столбца одной из таблицы вашей базы данных.
4.Отсортировать одну из таблиц по значению одного из полей в
порядке возрастания.
66
5.Отсортировать одну из таблиц по значению одного из полей в
порядке убывания.
6. Вывести информацию из таблицы вашей базы данных, где значение
одного из полей определяется по заданному значению или идентификатору.
7. Вывести информацию из таблицы вашей базы данных, где значения
нескольких
полей
определяются
по
заданному
значению
или
идентификатору.
8.
Вывести информацию из таблицы вашей базы данных, используя
операторы: =(равно), > (больше), < (меньше).
9. Вывести информацию из таблицы вашей базы данных, используя
операторы: >= (больше или равно), <= (меньше или равно), != (не равно).
10. Вывести информацию из таблицы вашей базы данных, используя
операторы: IS NOT NULL, IS NULL, BETWEEN (между), IN (значение
содержится).
11. Вывести информацию из таблицы вашей базы данных, используя
операторы: NOT IN (значение не содержится), LIKE (соответствие), NOT
LIKE (не соответствие).
12. Создать групповой запрос к вашей базе данных.
13. Создать групповой запрос с условием на группу к вашей базе
данных.
14. Создать запрос с использованием агрегированной функции к вашей
базе данных.
15. Создать групповой запрос с использованием агрегированной
функции к вашей базе данных.
2.4.6.
Лабораторная работа №3. Создание сложных запросов к базе
данных.
Цель: подготовить и реализовать вложенные запросы, внутренние и
внешние объединения.
Содержание работы
67
Изучить запросы языка SQL, связанные с манипулированием данных
из нескольких таблиц.
Задания.
1. Вывести информацию об одном параметре (поле) таблицы используя
запросы и подзапросы из нескольких таблиц вашей базы данных.
2. Вывести данные из двух таблиц и соединить их в одну, используя
запросы внутреннего объединения таблиц.
3.
Вывести информацию нескольких пользователей и темы вашей
базы данных, которые они создавали, используя запросы внешнего
объединения таблиц.
4. Вывести информацию всех пользователей и темы вашей базы
данных, которые они создавали, используя запросы внешнего объединения
таблиц.
5. Вывести информацию из трех таблиц нескольких пользователей,
чтобы вместо идентификаторов отображались имена и названия.
6. Вывести информацию из трех таблиц всех пользователей, чтобы
вместо идентификаторов отображались имена и названия темы.
7. Вывести информацию и узнать, количество пользователей, которые
создавали темы.
8. Вывести информацию и узнать, количество пользователей, которые
не создавали темы.
9. Поменяйте в своем запросе левостороннее объединение на
правостороннее для нескольких пользователей.
10. Поменяйте в своем запросе левостороннее объединение на
правостороннее для всех пользователей.
11. Добавьте запись в одну из таблиц вашей базы данных.
12. Изменить запись в одной из таблиц вашей базы данных.
13. Удалить запись в одной из таблиц вашей базы данных.
68
2.5. Фонд оценочных средств модуля «Язык SQL».
Модуль «Язык SQL» является частью дисциплины «Базы данных» для
направления подготовки Прикладная информатика. Промежуточная форма
аттестация по дисциплине - экзамен. Экзаменационный билет состоит из
двух теоретических вопросов и практической части, выполняемой на
компьютере.
Один
из
теоретических
вопросов
экзаменационного
билета
и
практическая часть относятся к модулю «Язык SQL»
Время, отводимое на экзамен - 1 час 30 минут. Из них на модуль
«Язык SQL» отводится 1 час.
Критерии и шкалы оценивания
Вид контроля
Промежуточная
аттестация
Форма
Оценочные
Критерии оценивания для промежуточной
Шкала
аттестации
средства
аттестации
оценивания
Комплект
Теоретическое содержание курса студентом
34-40
экзаменацио
освоено полностью, практические умения и
«отлично»
нных
навыки сформированы
билетов
Теоретическое содержание курса студентом
Экзамен
освоено
полностью,
но
отдельные
26-33
«хорошо»
практические умения и навыки сформированы
недостаточно
Теоретическое содержание курса студентом
освоено с незначительными
пробелами,
необходимые практические умения и навыки в
21-25
«удовлетвори
тельно»
основном сформированы
Теоретическое содержание курса студентом не
освоено, необходимые
практические умения,
и навыки не сформированы
0-20
«неудовлетво
рительно»
69
Пример экзаменационного билета для промежуточной аттестации
по дисциплине «Базы данных», включающий модуль «Язык SQL»
Утверждаю:
Зав. кафедрой
«
»______________ 20
г.
ФГБОУ ВО «Орловский государственный университет
имени И.С. Тургенева»
Физико-математический факультет
Кафедра информатики
Направление подготовки: 09.03.03 Прикладная информатика
Дисциплина «Базы данных»
Билет №1
1. <Вопрос, посвященный общей теории баз данных >
2. Понятие языка SQL.
3. Практическая часть.
3.1. Создать базу данных предметной области «Библиотека» со следующим
списком таблиц и их характеристик:
• Тематика (код тематики, наим.тематики)
• Издательство (код изд-ва, наим. изд-ва)
• Читатель (номер ЧБ, ФИО, адрес, год рождения)
• Книги (№ книги, наим.книги, авторы, код тематики, код изд-ва)
• Учет книг (номер ЧБ, № книги, дата выдачи, дата возврата, дата
факт.возврата).
3.2. Определить ключевые поля всех таблиц базы данных.
3.3. Выбереде читателей бравших указанную книгу.
3.4. Создайте групповой запрос с нахождением количества читателей
взявших книги каждой тематики.
3.5. Создайте запрос с нахождением читателей взявших более 3 книг.
Разработал:
Рассмотрены и одобрены на заседании кафедры
«__ »_________________ 20__г.
Протокол №
Полное
Приложении 2
содержание
фонда оценочных средств представлено в
70
ЗАКЛЮЧЕНИЕ
В данной выпускной квалификационной работе рассматривалась тема
«Разработка программно-методического обеспечения модуля «Язык SQL»
дисциплины «Базы данных» для бакалавров направления подготовки
«Прикладная информатика».
В ходе выполнения выпускной квалификационной работы были
решены все поставленные задачи:
1.
Проанализирована учебно-методическую литература и современное
состояние преподавания дисциплинарного модуля «Язык SQL» дисциплины
«Базы данных», в вузах.
2.
Изучены
основные
программные
средства
использования
возможностей языка SQL.
3. Определены основные цели, задачи и особенности преподавания
модуля
«Язык
SQL»
дисциплины
«Базы
данных»
для
бакалавров
направления подготовки «Прикладная информатика».
4.
Разработано программно-методическое обеспечение модуля «Язык
SQL» дисциплины «Базы данных» для бакалавров направления подготовки
«Прикладная информатика»:
- систему лекционных занятий;
- систему лабораторных работ;
- фонд оценочных средств для контроля знаний;
- методические рекомендации.
Таким образом, цель настоящей работы: разработка программно­
методического обеспечения модуля «Язык SQL» дисциплины «Базы данных»
для бакалавров направления подготовки «Прикладная информатика», а
именно разработка курса лекционных занятий, лабораторных работ, фонда
оценочных средств и методических рекомендаций для изучения данного
модуля была достигнута.
71
Материал данной работы может быть использован преподавателями
при
обучении
студентов
направления
информатика» дисциплине «Базы данных».
подготовки
«Прикладная
72
СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ
1.
Федеральный
государственный
образовательный
стандарт
высшего образования по направлению подготовки 09.03.03 Прикладная
информатика (уровень бакалавриата) (утвержден приказом Минобрнауки
России от 12. 03.2015 г. № 207) [Электронный ресурс] / Сайт Министерства
образования
и
науки
Российской
Федерации
/
-
URL:
http://минобрнауки.рф/документы /5442 (Дата обращения 01.02.2016).
2.
Шацков, В. В. Программирование приложений баз данных с
использованием СУБД MS SQL Server / В. В. Шацков. - СПб.: СанктПетербургский государственный архитектурно-строительный университет,
ЭБС
АСВ,
2015.
-
80
c.
[Электронный
ресурс]
URL:
http://www.iprbookshop.ru/63638.html (Дата обращения 01. 02.2018).
3.
Кара-Ушанов, В. Ю. SQL - язык реляционных баз данных / В. Ю.
Кара-Ушанов. - Екатеринбург: Уральский федеральный университет, ЭБС
АСВ,
2016.
-
156
c.
[Электронный
ресурс]
URL:
http://www.iprbookshop.ru/68419.html (Дата обращения 01.02.2018).
4.
Редько, В.Н. Базы данных и информационные системы / В.Н.
Редько, И. А. Бассараб. - М.: Знание, 2011. - 602 с.
5.
Федеральный
государственный
образовательный
стандарт
среднего (полного) общего образования (утвержден приказом Минобрнауки
России от 17 мая 2012 года № 413) [Электронный ресурс] / Сайт
Министерства образования и науки Российской Федерации / - URL:
http://минобрнауки.рф/документы/543(Дата обращения 01.02. 2016).
6.
Федеральный закон «Об образовании в Российской Федерации»
от 29 декабря 2012 г. № 273-ФЗ [Электронный ресурс] / Сайт Министерства
образования
и
науки
Российской
Федерации
/
-
URL:
Шр://минобрнауки.рф/документы/2974 (Дата обращения 01.02.2016).
7.
общего
Примерная основная образовательная программа основного
образования:
в
редакции
протокола
№3/15
от
28.12.2015
73
федерального учебно-методического объединения по общему образованию
[Электронный ресурс] / Сайт Министерства образования и науки Российской/
URL:http://минобрнауки.рф/projects/413/file/4587/POOP_OOO_reestr_2015_01.
doc (Дата обращения 01.02.2016).
8.
Дьяков, И. А. Базы данных. Язык SQL / И. А. Дьяков. - Тамбов:
Тамбовский государственный технический университет, ЭБС АСВ, 2012. - 81
c.
[Электронный ресурс] URL: http://www.iprbookshop.ru/64070.html (Дата
обращения 01. 02. 2018).
9.
Полякова, Л. Н. Основы SQL / Л. Н. Полякова. - М.: Интернет­
Университет Информационных Технологий (ИНТУИТ), 2016. - 273 с.
[Электронный ресурс] URL:
http://www.iprbookshop.ru/52210.html (Дата
обращения 01. 02. 2018).
10. Веймаер, Р. Сотел, Р. Освой самостоятельно Microsoft SQL Server
2000 за 21 день (+ CD-ROM) / Р. Веймаер, Р. Сотел. - М.: Вильямс, 2013. 549 с.
11. Кузнецов, С. Д. Введение в модель данных SQL / С. Д. Кузнецов.
- М.: Интернет-Университет Информационных Технологий (ИНТУИТ), 2016.
- 350 c. [Электронный ресурс] URL: http://www.iprbookshop.ru/73664.html
(Дата обращения 01. 02.2018).
12. Кара-Ушанов, В. Ю. SQL - язык реляционных баз данных / В. Ю.
Кара-Ушанов. - Екатеринбург: Уральский федеральный университет, ЭБС
АСВ,
2016.
-
156
c.
[Электронный
ресурс]
URL:
http://www.iprbookshop.ru/68419.html (Дата обращения 01.02.2018).
13. Фиайли, К. SQL / К. Фиайли. - Саратов.: Профобразование, 2017.
- 452 c. [Электронный ресурс] URL: http://www.iprbookshop.ru/63823.html
(Дата обращения 01. 02. 2018).
14. Пржиялковский, В. В. Введение в Oracle SQL / В. В.
Пржиялковский. - М.: Интернет-Университет Информационных Технологий
(ИНТУИТ),
2016.
-
336
c.
[Электронный
ресурс]
http://www.iprbookshop.ru/62808.html (Дата обращения 01.02.2018).
URL:
74
15. Тоу, Д. Настройка SQL / Д. Тоу. - СПб.: Питер, 2009. - 539 с.
16. Фейерштейн, С. Oracle PL / SQL для профессионалов / С.
Фейерштейн, Б. Прибыл. - СПб.: Питер, 2012. - 540 с.
17. Баженова, И. Ю. SQL и процедурно-ориентированные языки / И.
Ю. Баженова. - М.: Интернет-Университет Информационных Технологий
(ИНТУИТ),
2016.
-
166
с.
[Электронный
ресурс]
URL:
http://www.iprbookshop.ru/57532.html (Дата обращения 01.02. 2018).
18. Зудилова, Т.В. Создание запросов в Microsoft SQL Server 2008 /
Т. В. Зудилова, Г. Ю. Шмелева. - СПб.: Университет ИТМО, 2013.— 149 с.
[Электронный ресурс] URL: http://www.iprbookshop.ru/68136.html ( Дата
обращения 01. 02. 2018).
19. Дэвидсон, Л. Проектирование баз данных на SQL Server 2000 / Л.
Дэвидсон. - М.: Бином, 2009. - 631 с.
20. Аткинсон, Л. MySQL. Библиотека профессионала / Л. Аткинсон. М.: Вильямс, 2008. - 624 с.
21. Бурков, А. В. Проектирование информационных систем в
Microsoft SQL Server 2008 и Visual Studio 2008 / А. В. Бурков. - М.: Интернет­
Университет Информационных Технологий (ИНТУИТ), 2016. - 310 с.
[Электронный ресурс] URL:
http://www.iprbookshop.ru/52166.html (Дата
обращения 01. 02. 2018).
22. Гончарик, Н. Г. Цифровые мультимедийные технологии смысловые средства передачи информационного содержания / Н. Г.
Г ончарик // Проблемы создания информационных технологий: сб. науч. тр. 2012. - № 21. - С. 74-76.
23. Гохберг, Г.С. Информационные технологии: Учебник для студ.
учрежд. сред. проф. образования / Г.С. Гохберг, А.В. Зафиевский, А.А.
Короткин. - М.: ИЦ Академия, 2013. - 208 с.
24. Дейт, К. Дж. Введение в системы баз данных / К. Дж. Дейт. - М.:
Диалектика, 2012. - 360 с.
75
25. Култыгин, О. П. Администрирование баз данных. СУБД MS SQL
Server / О. П. Култыгин. - М.: Московский финансово-промышленный
университет «Синергия», 2012. - 232 c. [Электронный ресурс] URL:
http://www.iprbookshop.ru/17009.html (Дата обращения 01.02.2018).
26. Мельников, В. П. Информационные технологии: Учебник для
студентов высших учебных заведений / В.П. Мельников. - М.: ИЦ Академия,
2014. - 432 с.
27. Нанда, А. Oracle PL / SQL для администраторов баз данных / А.
Нанда, С. Фейерштейн. - М.: Символ- Плюс, 2008. - 496 c.
28. Нестеров, С. А. Интеллектуальный анализ данных средствами MS
SQL
Server 2008 /
С.
А.
Нестеров.
- М.:
Интернет-Университет
Информационных Технологий (ИНТУИТ), 2016. - 303 c.
[Электронный
ресурс] URL: http://www.iprbookshop.ru/62813.html (Дата обращения 01.02.
2018).
29. Озкарахан, Э. Машины баз данных и управление базами данных /
Э. Озкарахан. - М.: Мир, 2009. - 551 c.
30. Ткачев, О. А. Создание и манипулирование базами данных
средствами СУБД Мicrosoft SQL Server 2008 / О. А. Ткачев. - М.:
Московский городской педагогический университет, 2013.
[Электронный ресурс] URL:
-
152 c.
http://www.iprbookshop.ru/26613.html (Дата
обращения 01.02. 2018).
31. Туманов, В. Е. Основы проектирования реляционных баз данных
/ В. Е. Туманов. - М.: Бином, 2012. - 420 c.
32. Хаббард, Дж. Автоматизированное проектирование баз данных /
Дж. Хаббард.- М.: Мир, 2011. - 453 c.
33. Холин, А. Н. Ситуационные центры: перспективы цифровых
технологий. Площадка для апробации цифровых технологий / А. Н. Холин //
Науч. периодика: проблемы и решения. - 2011. - № 6. - С. 6-9.
76
34. Шаймарданов,
Р.
Б.
Моделирование
и
автоматизация
проектирования структур баз данных / Р. Б. Шаймарданов. - М.: Радио и
связь, 2008. -469 с.
35. Шнайдер,
Р.
Microsoft
SQL
Server
6.5.
Проектирование
высокопроизводительных баз данных / Р. Шнайдер. - М.: Лори, 2010. - 361 c.
36. Яргер, Р. Дж. MySQL и mSQL: Базы данных для небольших
предприятий и Интернета / Р. Яргер, Дж. Риз, Т. Кинг. - СПб.: Символ­
Плюс, 2013. - 560 c.
77
ПРИЛОЖЕНИЯ
78
Приложение 1
Варианты предметной области
Вариант 1
Предметная область: Библиотека.
Минимальный список характеристик:
• Автор книги, название, год издания, цена, является ли новым изданием,
краткая аннотация;
• номер читательского билета, ФИО, адрес и телефон читателя, дата
выдачи книги читателю и дата сдачи книги читателем.
Вариант 2
Предметная область: Оптовая база
Минимальный список характеристик:
• Код товара, название товара, количество на складе, стоимость единицы
товара, примечания - описание товара;
• номер и ФИО поставщика товара, срок поставки и количество товаров
в поставке.
Вариант 3
Предметная область: Поликлиника
Минимальный список характеристик:
• Номер, фамилия, имя, отчество, дата рождения пациента;
• ФИО,
должность
и
специализация
лечащего
врача,
диагноз,
поставленный данным врачом данному пациенту, необходимо ли
амбулаторное лечение, срок потери трудоспособности, состоит ли на
диспансерном учете, примечание.
Вариант 4
Предметная область: Авторемонтные мастерские
Минимальный список характеристик:
• Номер водительских прав,
автомобиля;
ФИО,
адрес
и телефон владельца
79
• номер, ФИО, адрес, телефон и квалификация механика;
• номер, марка, мощность и цвет автомобиля;
• номер, название, адрес и телефон ремонтной мастерской.
Вариант 5
Предметная область: Группа слушателей
Минимальный список характеристик:
• Наименование специальности, код группы, ФИО, дата рождения,
домашний адрес, телефон слушателя, примечания - автобиография
слушателя;
• код, название, количество часов и вид контроля предметов, код сессии
и оценки каждого слушателя каждому предмету в каждую сессию.
Вариант 6
Предметная область: Домоуправление
Минимальный список характеристик:
• Номер подъезда, номер квартиры, общая площадь, полезная площадь,
количество комнат,
• фамилия квартиросъемщика, дата прописки, количество членов семьи,
количество детей в семье, есть ли задолженность по квартплате,
примечание.
Вариант 7
Предметная область: Аэропорт
Минимальный список характеристик:
• Номер рейса, пункт назначения, дата рейса, тип самолета, время
вылета, время в пути, является ли маршрут международным,
• сведения о пассажире, примечание.
Вариант 8
Предметная область: Автотранспортное предприятие
Минимальный список характеристик:
80
• номерной знак автомобиля, марка автомобиля, его техническое
состояние,
местонахождение
автомобиля,
средняя
скорость,
грузоподъемность, расход топлива;
• табельный номер водителя, фамилия, имя, отчество, дата рождения,
стаж работы, оклад;
• дата выезда, дата прибытия, место назначения, расстояние, расход
горючего, масса груза.
Вариант 9
Предметная область: Отдел кадров
Минимальный список характеристик:
• Фамилия, имя, отчество, домашний адрес, телефон, дата рождения,
должность, дата зачисления, стаж работы, образование;
• фамилия, имя, отчество, и даты рождения членов семьи каждого
сотрудника, оклад;
• наименование подразделения, количество штатных единиц, фонд
заработной платы за месяц и за год.
Вариант 10
Предметная область: Сеть магазинов
Минимальный список характеристик:
• Номер, ФИО, адрес, телефон и капитал владельцев магазинов;
• номер, название, адрес и телефон магазина;
номер, ФИО, адрес, телефон поставщика, а также стоимость поставки
данного поставщика в данный магазин.
81
Приложение 2
ФОНД ОЦЕНОЧНЫХ СРЕДСТВ
по модулю «Язык SQL»
дисциплины «Базы данных»
Направление подготовки 09.03.03 Прикладная информатика
Направленность (профиль) Прикладная информатика в экономике
Орел 2018
1. Перечень оценочных средств и их соответствие планируемым результатам обучения по дисциплине
Форма
аттестации
Экзамен
Планируемые результаты обучения
Оценочные
(индикаторы достижения компетенций)
средства
Комплект
Знать:
экзаменационных - базовые нормативно-технические документы (отечественные и зарубежные стандарты) в области
билетов
информационных систем и технологий (З1(ОПК-1)-1);
- основные информационные ресурсы для использования в профессиональной деятельности
(З3(ОПК-1)-1);
Уметь:
- применять понятийно-терминологический аппарат из современных отечественных и зарубежных
стандартов в области информационных систем и технологий уметь применять стандарты для
оформления научных и технических разработок (У4(ОПК-1)-1);
- уметь работать с инструментальными средствами, поддерживающими создание программного
обеспечения для информационных систем (У 1(ПК-3) -I);
2. Критерии и шкалы оценивания
Вид контроля
Форма
аттестации
Оценочные
средства
Промежуточная
аттестация
Экзамен
Комплект
экзаменацион
ных билетов
Критерии оценивания для промежуточной аттестации
Шкала
оценивания
34-40
Теоретическое содержание курса студентом освоено полностью,
практические умения и навыки сформированы
«отлично»
Теоретическое содержание курса студентом освоено полностью,
26-33
но отдельные практические умения и навыки сформированы
«хорошо»
недостаточно
Теоретическое содержание курса студентом освоено с
21-25
незначительными
пробелами, необходимые практические «удовлетворительно»
умения и навыки в основном сформированы
0-20
Теоретическое содержание курса студентом не освоено,
необходимые практические умения и навыки не сформированы
«неудовлетворительно»
83
3. Типовые оценочные средства
3.1 Структура экзамена
Промежуточная форма аттестация по дисциплине «Базы данных» - экзамен.
Время и место проведения экзамена устанавливается в соответствии с расписанием экзаменационной сессии.
Время отводимое на экзамен - 1 час 30 минут. Из них на модуль «Язык SQL» отводится 1 час.
Экзаменационный билет состоит из двух теоретических вопросов и практической части, выполняемой на
компьютере. Один из теоретических вопросов экзаменационного билета и практическая часть относятся к модулю
«Язык SQL»_____________________________________________________________________________________________
№
Структура
билета
Разделы
дисциплины
1
Теоретический вопрос
№1
Общая теория база
данных
Проверяемые
результаты
обучения
З1(ОПК-1)-1
З3(ОПК-1)-1
У4(ОПК-1)-1
Критерии оценки
Максимальный
балл
14-15 баллов - полное раскрытие теоретического 15 баллов
вопроса, материал изложен в определенной
логической последовательности с демонстрацией
знаний в области базы данных;
10-13 баллов - раскрытие теоретического вопроса с
незначительными неточностями формулировок,
материал изложен в определенной логической
последовательности с демонстрацией знаний в
области базы данных;
6-9 баллов - поставленный вопрос раскрыт
недостаточно полно,
отсутствуют отдельные
определения, в целом материал изложен в
определенной логической последовательности с
частичной демонстрацией знаний в области базы
данных;
3-5 баллов -изложен краткий план ответа на вопрос
в определенной логической последовательности
(даны основные определения) с частичной
демонстрацией знаний в области базы данных;
0-2 баллов - ответ на вопрос не дан или приведены
лишь отдельные определения и положения, у
обучающегося отсутствует целостное представление
84
2
Теоретический вопрос
№2
Язык SQL
З1(ОПК-1)-1
З3(ОПК-1)-1
У4(ОПК-1)-1
У1(ПК-3) -I
3
Практическая часть
SQL - стандартный
язык запросов
З1(ОПК-1)-1
зз(о п к -1)-1
У4(ОПК-1)-1
У1(ПК-3) -I
об базах данных.
14-15 баллов - полное раскрытие теоретического 15 баллов
вопроса, материал изложен в определенной
логической последовательности с демонстрацией
знаний в области языка SQL;
10-13 баллов - раскрытие теоретического вопроса с
незначительными неточностями формулировок,
материал изложен в определенной логической
последовательности с демонстрацией знаний в
области языка SQL;
6-9 баллов - поставленный вопрос раскрыт
недостаточно полно,
отсутствуют отдельные
определения, в целом материал изложен в
определенной логической последовательности с
частичной демонстрацией знаний в области языка
SQL;
3-5 баллов -изложен краткий план ответа на вопрос
в определенной логической последовательности
(даны основные определения) с частичной
демонстрацией знаний в области языка SQL
0-2 баллов - ответ на вопрос не дан или приведены
лишь отдельные определения и положения, у
обучающегося отсутствует целостное представление
об языка SQL.
2*5=10 баллов
2 балла- ставится, когда студент демонстрирует
полное понимание и выполнение поставленной
задачи (сделано 100 % заданий).
1 балл - ставится, когда студент демонстрирует
частичное понимание и выполнение поставленной
задач без грубых ошибок (сделано более 50 %
задания);
0- баллов - ставится, когда студент демонстрирует
полное непонимание поставленной задачи (задача не
выполнена или выполнена менее 50% задания).
3.2 Перечень типовых теоретических вопросов для промежуточной
аттестации по модулю «Язык SQL»
1. Понятие языка SQL.
2. SQL-сервер.
3. Основа языка SQL.
4. Типы данных языка SQL.
5. Запросы SQL с использованием единственной таблицы. Исключение
дубликатов, выбор вычисляемых значений.
б.Запросы SQL с использованием единственной таблицы. Выборка с
использованием фразы WHERE. Использование в запросах операторов IN,
LIKE, BETWEEN AND, IS NULL.
7. Запросы SQL с использованием единственной таблицы. Выборка с
использованием
фразы
WHERE.
Выборка
с
упорядочиванием.
Использование в запросах операторов IS NULL.
8.Запросы SQL с использованием единственной таблицы. Специальные
SQL - функции. SQL функции без использования фразы GROUP BY.
9. SQL функции с использованием фразы GROUP BY. Использование
фразы HAVING.
10. Запросы в SQL. Общий синтаксис. Параметрический запрос в SQL.
11.Запросы действий в SQL. Запрос на добавление, запрос на обновление.
12.Запросы действий в SQL. Запрос на удаление, запрос на создание новой
таблицы.
13. Запросы SQL с использованием нескольких таблиц. Запросы,
использующие соединения (Декартово произведение таблиц, Естественное
соединение таблиц, Композиция таблиц)
14. Запросы SQL с использованием нескольких таблиц. Виды вложенных
подзапросов.
15. Запросы SQL с использованием нескольких таблиц. Простые
вложенные подзапросы.
16. Запросы SQL с использованием нескольких таблиц. Коррелированные
вложенные подзапросы.
17. Запросы SQL с использованием нескольких таблиц. Функции в
подзапросе. Запросы на объединение.
3.3 Перечень тем типовых практических задания для промежуточной
аттестации по модулю «Язык SQL»
1.
2.
3.
4.
5.
6.
Создание и удаление таблиц базы данных.
Ввод и редактирование данных в таблицах.
Выборка данных из одной или нескольких таблиц базы данных.
Сортировка таблиц базы данных.
Удаление дубликатов данных запросов.
Запросы с вычисляемыми полями. Переименование полей запроса.
86
7. Применение условных операторов для выборки данных таблиц базы
данных.
8. Применение операторов IS (NOT) NULL, BETWEEN AND,(NOT)
IN,(NOT) LIKE для выборки данных таблиц базы данных.
9. Группировка полей таблицы базы данных. Применение условий на
группу.
10. Работа с агрегатными функциями: AVG, COUNT, MIN, MAX и SUM.
11. Вложенные подзапросы.
12. Запросы действий DELETE, UPDATE.
3.4 Примерные варианты практических заданий для промежуточной
аттестации по модулю «Язык SQL»
Вариант 1.
1. Создать базу данных предметной области «Библиотека» со следующим
списком таблиц и их характеристик:
• Тематика (код тематики, наим.тематики)
• Издательство (код изд-ва, наим. изд-ва)
• Читатель (номер ЧБ, ФИО, адрес, год рождения)
• Книги (№ книги, наим.книги, авторы, код тематики, код изд-ва)
• Учет книг (номер ЧБ, № книги, дата выдачи, дата возврата, дата
факт.возврата).
2. Определить ключевые поля всех таблиц базы данных.
3. Выберете читателей, бравших указанную книгу.
4. Создайте групповой запрос с нахождением количества читателей взявших
книги каждой тематики.
5. Создайте запрос с нахождением читателей взявших более 3 книг.
Вариант 2.
1. Создать базу данных предметной области «Деканат» успеваемость
студентов со следующим списком таблиц и их характеристик:
• Студенты - (код, фамилия, имя, отчество, пол, дата рождения,
место рождения, группа студентов)
• Группы студентов - (название, курс, семестр)
• Дисциплины - (название, количество часов)
• Успеваемость - (оценка, вид контроля).
2. Определить ключевые поля всех таблиц базы данных.
3. Выберете студентов, 1 курса, 1999 года рождения.
4. Создайте групповой запрос с отображение количества студентов
получивших оценку «отлично» по всем дисциплинам за семестр.
5. Создайте запрос с нахождением студентов сдавших более 5 экзаменов.
87
Вариант 3.
1. Создать базу данных предметной области «Аптека» со следующим
списком таблиц и их характеристик:
• Лекарства (код тематики, наим.тематики, количество, цена,
сумма)
• Склад (код продукции, наименование продукции, единицы
измерения, дата прихода, количество расхода, места хранения)
• Производители (наименование производителя, адрес, номер
телефона, срок хранения)
• Поставщики (наименование поставщика, номер телефона, номер
заказа, цена доставки).
2. Определить ключевые поля всех таблиц базы данных.
3. Выберете лекарства, купившие указанную продукцию.
4. Создайте групповой запрос с нахождением количества заказов
доставленных поставщиком каждой тематики.
5. Создайте запрос с нахождением поставщиков принявших более 4 заказов.
88
3.5 Пример экзаменационного билета для промежуточной аттестации
по дисциплине «Базы данных», включающий модуль «Язык SQL»
Утверждаю:
Зав. кафедрой
«
»______________ 20
г.
ФГБОУ ВО «Орловский государственный университет
имени И.С. Тургенева»
Физико-математический факультет
Кафедра информатики
Направление подготовки: 09.03.03 Прикладная информатика
Дисциплина «Базы данных»
Билет №1
1. <Вопрос, посвященный общей теории баз данных >
2. Понятие языка SQL.
3. Практическая часть.
3.1. Создать базу данных предметной области «Библиотека» со следующим
списком таблиц и их характеристик:
• Тематика (код тематики, наим.тематики)
• Издательство (код изд-ва, наим. изд-ва)
• Читатель (номер ЧБ, ФИО, адрес, год рождения)
• Книги (№ книги, наим.книги, авторы, код тематики, код изд-ва)
• Учет книг (номер ЧБ, № книги, дата выдачи, дата возврата, дата
факт.возврата).
3.2. Определить ключевые поля всех таблиц базы данных.
3.3. Выбереде читателей бравших указанную книгу.
3.4. Создайте групповой запрос с нахождением количества читателей
взявших книги каждой тематики.
3.5. Создайте запрос с нахождением читателей взявших более 3 книг.
Разработал:
Рассмотрены и одобрены на заседании кафедры
« »_________________ 20 г.
Протокол №
89
Приложение 3
ОРЛОВСКИМ
ГОСУДАРСТВЕННЫЙ
УНИВЕРСИТЕТ
имени И. С. Тургенева
МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ
РОССИЙСКОЙ ФЕДЕРАЦИИ
ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ
БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ
УЧРЕЖДЕНИЕ ВЫСШЕГО ОБРАЗОВАНИЯ
"ОРЛОВСКИЙ ГОСУДАРСТВЕННЫЙ
УНИВЕРСИТЕТ ИМЕНИ И.С.ТУРГЕНЕВА"
ФИЗИКО-МАТЕМАТИЧЕСКИЙ ФАКУЛЬТЕТ
Кафедра «Информатики»
дисциплина «Базы данных»
Методические рекомендации по дисциплинарному модулю
«Язык SQL»
Направление подготовки: 09.03.03 Прикладная информатика
Профиль: прикладная информатика в экономике
Форма обучения: очная
Квалификация: бакалавр
Тип образовательной программы: академический бакалавриат
Орел 2018
90
Содержание
1 ЦЕЛИ И ЗАДАЧИ ОСВОЕНИЯ МОДУЛЯ___________________________________ 91
2 МЕТОДИЧЕСКИЕ УКАЗАНИЯ ДЛЯ ОБУЧАЮЩИХСЯ ПО ОСВОЕНИЮ
МОДУЛЯ
92
2.1 МЕТОДИЧЕСКИЕ УКАЗАНИЯ ДЛЯ ОБУЧАЮЩИХСЯ ПО ЛЕКЦИОННЫМ
ЗАНЯТИЯМ ПО МОДУЛЮ
92
2.2 МЕТОДИЧЕСКИЕ УКАЗАНИЯ ДЛЯ ОБУЧАЮЩИХСЯ ПО ЛАБОРАТОРНЫМ
ЗАНЯТИЯМ ПО МОДУЛЮ
93
2.3 МЕТОДИЧЕСКИЕ УКАЗАНИЯ ДЛЯ ОБУЧАЮЩИХСЯ ПО
ПРОМЕЖУТОЧНОЙ АТТЕСТАЦИИ ПО МОДУЛЮ
122
3 ПЕРЕЧЕНЬ ОСНОВНОЙ И ДОПОЛНИТЕЛЬНОЙ УЧЕБНОЙ ЛИТЕРАТУРЫ.
НЕОБХОДИМОЙ ДЛЯ ОСВОЕНИЯ МОДУЛЯ
123
3.1 ОСНОВНАЯ ЛИТЕРАТУРА
123
3.2 ДОПОЛНИТЕЛЬНАЯ ЛИТЕРАТУРА
123
91
1. Цели и задачи освоения модуля
Изучение модуля «Язык SQL» в рамках дисциплины «Базы данных» в
высших учебных заведениях направлено на достижение следующих целей:
•
Освоение знаний, составляющих основу модуля «Язык SQL» в
рамках дисциплины «Базы данных».
•
Изучение понятий и синтаксиса языка SQL и его применение в
практической деятельности.
•
Изучения приемов практического применения языка SQL для
выборки данных.
•
Развитие
познавательных
интересов,
интеллектуальных
и
творческих способностей, развитие навыков контроля и самоконтроля,
сосредоточенности, воображения.
Задачи:
S
Овладение
основными
функциями
универсального
языка
манипулирования данными SQL.
S
Выработка практических навыков использования языка SQL.
S
Овладение навыков по созданию и заполнению таблиц на языке
S
Формирование навыков в построение запросов к базе данных.
S
Развитие навыков в применение агрегатных функций в операторе
SQL.
выбора.
S
Получение навыков в построение вложенных запросов.
S
Выработка
практических
навыков
работы
с
внешними
объединениями и операторами манипулирования данными.
S
Развитие навыков работы с компьютером.
S
Развитие умения студентов самостоятельно работать.
S
Формирование культуры мышления, способности к обобщению,
анализу, восприятию информации, постановке целей и выбору путей ее
достижения.
92
2. Методические указания для обучающихся по освоению модуля
Процесс
изучения
дисциплины
предусматривает
активное
использование современных инновационных образовательных технологий.
Формы обучения: индивидуальные и групповые.
Методы обучения:
- работа с преподавателем,
- работа в коллективе обучающихся,
- самостоятельная работа.
При освоении дисциплины используются следующие виды активной и
интерактивной форм обучения для достижения запланированных результатов
обучения и формирования компетенций:
- совместное погружение в проблемное поле,
- обсуждение сложных и дискуссионных вопросов и проблем,
- работа в малых группах,
- разборы конкретных ситуаций, кейсов и т.д.
Процесс освоения дисциплины предусматривает следующие работы:
1. Контактная работа (аудиторная работа: лекционные и лабораторные
занятия)
2. Самостоятельная работа
3. Контрольные мероприятия (промежуточная аттестация)
2.1
Методические указания для обучающихся по лекционным
занятиям по модулю
Лекция является наиболее экономичным способом передачи учебной
информации, т.к. при этом обширный материал излагается концентрировано,
в логически выдержанной форме, с учетом характера профессиональной
деятельности обучаемых. Лекция закладывает основы научных знаний в
обобщенной форме. На лекционных занятиях преподаватель:
- знакомит слушателей с общей методикой работы над курсом,
93
- дает характеристику учебников и учебных пособий, знакомит
слушателей с обязательным списком литературы,
- рассказывает о требованиях к промежуточной аттестации,
- рассматривает основные теоретические положения курса,
- разъясняет вопросы, которые возникли у студентов в процессе
изучения курса.
Лекционное занятие преследует 5 основных дидактических целей:
- информационную (сообщение новых знаний);
- развивающую (систематизация и обобщение накопленных знаний);
- воспитывающую (формирование взглядов, убеждений, мировоззрения);
-стимулирующую (развитие познавательных и профессиональных
интересов);
- координирующую с другими видами занятий.
В процессе прослушивания лекций очень важно умение студентов
конспектировать наиболее значимые моменты теоретического материала.
Конспект помогает внимательнее слушать, лучше запоминать в процессе
записи, обеспечивает наличие опорных материалов при подготовке к
практическим занятиям и промежуточной аттестации.
В этой же тетради следует записывать неясные вопросы, требующие
уточнения на занятии. Рекомендуется в тетради отвести место для словаря,
куда в алфавитном порядке вписываются специальные термины и пояснения к
ним.
2.2
Методические указания для обучающихся по лабораторным
занятиям по модулю
Лабораторные занятия имеют целью углубление и закрепление
теоретических
знаний,
развитие
навыков
самостоятельного
экспериментирования.
В
ходе
лабораторного
занятия
студенты
под
руководством
преподавателя лично выполняют задания с целью проверки и подтверждения
94
отдельных теоретических положений учебной дисциплины, приобретают
практические навыки работы с заданной предметной областью дисциплины,
овладевают заданными инструментами и средствами.
Порядок проведения лабораторного занятия:
1. Вводная часть: - входной контроль подготовки студента; - вводный
инструктаж (знакомство студентов с содержанием предстоящей работы,
показ способов выполнения отдельных операций, предупреждение о
возможных ошибках).
2. Основная часть: - проведение студентом лабораторной работы; текущий
инструктаж,
повторный
показ
или
разъяснения
(в
случае
необходимости преподавателем исполнительских действий, являющихся
предметом инструктирования).
3. Заключительная часть: - оформление отчета о выполнении задания; заключительный инструктаж (подведение итогов выполнения учебных задач,
разбор допущенных ошибок и выявление их причин, сообщение результатов
работы каждого студента, объявление о том, что необходимо повторить к
следующему занятию).
Методическое обеспечение выполнения лабораторных работ
модуля «Язык SQL»
Создание базы данных и таблиц
Для освоения и работы с языком SQL будем использовать СУБД
MySQL.
Для
этого
прежде
всего
надо
запустить
сервер
MySQL.
mysql> _
Нам надо создать базу данных, которую мы назовем f o r u m . Для этого
в SQL существует оператор c r e a t e
d a ta b a s e . Создание базы данных
имеет следующий синтаксис:
create
database
имя б а з ы д а н н ы х ;
95
Максимальная длина имени БД составляет 64 знака и может включать
буквы, цифры, символ "_" и символ "$". Имя может начинаться с цифры, но
не должно полностью состоять из цифр. Любой запрос к БД заканчивается
точкой с запятой (этот символ называется разделителем - d e l i m i t e r ).
Получив запрос, сервер выполняет его и в случае успеха выдает сообщение
"Q u e r y
OK
..."
Итак, создадим БД f o r u m :
m ysql>
create
database
forum;
Нажимаем Enter и видим ответ "Q u e r y OK . . . ", означающий, что
БД была создана:
raysql> c re a te d atabase forum;
Query ОКг 1 row a ff e c t e d £0.05 se c )
iHysql>
Теперь в этой базе данных нам надо создать 3 таблицы: темы,
пользователи и сообщения. Но перед тем, как это делать, нам надо указать
серверу в какую именно БД мы создаем таблицы, т.е. надо выбрать БД для
работы. Для этого используется оператор u se . Синтаксис выбора БД для
работы следующий:
use
имя б а з ы данны х;
Итак, выберем для работы нашу БД f o r u m :
m ysql> use
forum;
Нажимаем Enter и видим ответ "D a t a b a s e c h a n g e d " - база данных
выбрана.
r*ysql> use forum ;
database changed
r*ysql> _
Выбирать БД необходимо в каждом сеансе работы с MySQL.
Для создания таблиц в SQL существует оператор c r e a t e t a b l e . Создание
базы данных имеет следующий синтаксис:
96
create
table
имя в т о р о г о
тип
имя т аб л и ц ы
столбца
тип,
...,
(имя п е р в о г о
столбца
имя п о с л е д н е г о
тип,
столбца
);
Требования к именам таблиц и столбцов такие же, как и для имен БД.
К
каждому
столбцу привязан
определенный тип
данных,
который
ограничивает характер информации, которую можно хранить в столбце
(например, предотвращает ввод букв в числовое поле). MySQL поддерживает
несколько типов данных: числовые, строковые, календарные и специальный
тип NULL, обозначающий отсутствие информации. Итак, создадим первую
таблицу - Темы:
create
table
tex t,id _ au th o r
topics
(id_topic
in t,
topic_nam e
in t);
Нажимаем Enter - таблица создана:
mysql> use forum;
Database changed
inysqls c r e a t e t a b l e t o p i c s ( i d _ t o p i c i n t , topic_name t e x t , id _a u th o r i n t ) ;
Query OK, О rows a f f e c t e d (0 .2 0 sec)
mysql> _
Итак, мы создали таблицу t o p i c s (темы) с тремя столбцами:
id_topic
i n t - id темы (целочисленное значение),
topic_nam e
id_author
t e x t - имя темы (строка),
i n t - id автора (целочисленное значение).
Аналогичным образом создадим оставшиеся две таблицы - u s e r s
(пользователи) и p o s t s (сообщения):
mysql> c r e a t e t a b l e u s e rs ( i d _ u s e r i n t ,
Query OK., 0 raws a f f e c t e d ( 0 .2 2 se c )
name t e x t ,
email t e x t , password t e x t ) ;
mysql> c r e a t e t a b l e p osts ( i d _ p o s t i n t ,
t);
Query OK, 0 rows a f f e c t e d (0 .0 9 se c )
message t e x t , id _a u t h o r i n t ,
i d _ t o p i c in
mysql> _____________________________________________________________________________________________________________
Итак, мы создали БД f o r u m и в ней три таблицы. Удержать в голове
названия всех таблиц базы данных и их столбцов просто невозможно.
Поэтому надо иметь возможность посмотреть, какие БД у нас существуют,
97
какие таблицы в них присутствуют, и какие столбцы эти таблицы содержат.
Для этого в SQL существует несколько операторов:
show d a t a b a s e s - показать все имеющиеся БД,
show
ta b le s -
показать
список
таблиц
текущей
БД
(предварительно ее надо выбрать с помощью оператора u s e ),
d e s c r i b e и м я _ т а б л и ц ы - показать описание столбцов указанной
таблицы.
Создание таблиц и наполнение их информацией
Теперь будем усовершенствовать таблицы для нашего форума.
Сначала разберем их. И начнем с таблицы u s e r s (пользователи). В ней у нас
4 столбца:
i d _ u s e r - целочисленные значения, значит будет тип int, ограничим его 10
символами - int (10).
name
- строковое значение varchar, ограничим его 20 символами -
varchar(20).
em ail
- строковое значение varchar, ограничим его 50 символами -
varchar(50).
p a s s w o r d - строковое значение varchar, ограничим его 15 символами -
varchar(15).
Все значения полей обязательны для заполнения, значит надо
добавить тип NOT NULL .
id_user
(10)
NOT NULL
name v a r c h a r ( 2 0 )
NOT NULL
em ail
in t
varchar(50)
NOT NULL
passw ord varchar(15)
NOT NULL
Первый столбец, из концептуальной модели нашей БД, является
первичным ключом (т.е. его значения уникальны, и они однозначно
идентифицируют запись). Следить за уникальностью самостоятельно можно,
но не рационально. Для этого в SQL есть специальный атрибут -
98
AUTO_INCREMENT, который при обращении к таблице на добавление
данных высчитывает максимальное значение этого столбца, полученное
значение увеличивает на 1 и заносит его в столбец. Таким образом, в этом
столбце автоматически генерируется уникальный номер, а следовательно тип
NOT NULL излишен. Итак, присвоим атрибут столбцу с первичным ключом:
id_user
in t
(10)
name v a r c h a r ( 2 0 )
em ail
AUTO_INCREMENT
NOT NULL
varchar(50)
NOT NULL
passw ord varchar(15)
NOT NULL
Теперь надо указать, что поле i d _ u s e r является первичным ключом.
Для этого в SQL используется ключевое слово PRIMARY KEY
(), в
скобочках указывается имя ключевого поля. Внесем изменения:
id_user
in t
(10)
name v a r c h a r ( 2 0 )
em ail
AUTO_INCREMENT
NOT NULL
varchar(50)
NOT NULL
passw ord varchar(15)
PRIMARY KEY
NOT NULL
(id_user)
Итак, таблица готова, и ее окончательный вариант выглядит так:
create
table
id_user
users
int
(
(10)
AUTO_INCREMENT,
name v a r c h a r ( 2 0 )
em ail
NOT NULL,
varchar(50)
NOT NULL,
passw ord v arch ar(1 5 )
PRIMARY KEY
(id
NOT NULL,
user)
Теперь разберемся со второй таблицей - t o p i c s (темы). Рассуждая
аналогично, имеем следующие поля:
id_topic
in t
topic_nam e
id
author
(10)
AUTO_INCREMENT
varchar(100)
in t
(10)
NOT NULL
NOT NULL
99
PRIMARY KEY
(id_topic)
Но в модели нашей БД поле i d _ a u t h o r является внешним ключом,
т.е. оно может иметь только те значения, которые есть в поле i d _ u s e r
таблицы u s e r s . Для того, чтобы указать это в SQL есть ключевое
слово FOREIGN KEY (), которое имеет следующий синтаксис:
FOREIGN KEY
(имя с т о л б ц а
которое
является
внешним ключом)
REFERENCES и м я _ т а б л и ц ы _ р о д и т е л я
(имя с т о л б ц а р о д и т е л я ) ;
Укажем, что i d _ a u t h o r - внешний ключ:
id_topic
in t
topic_nam e
id_author
(10)
AUTO_INCREMENT
varchar(100)
in t
(10)
NOT NULL
NOT NULL
PRIMARY KEY
(id_topic)
FOREIGN KEY
(id_author)
REFERENCES u s e r s
(id_user)
Таблица готова, и ее окончательный вариант выглядит так:
create
table
topics
id_topic
int
(
(10)
AUTO_INCREMENT,
topic_name v a rch ar(1 0 0 )
id_author in t
(10)
NOT NULL,
NOT NULL,
PRIMARY KEY ( i d _ t o p i c ) ,
FOREIGN KEY ( i d _ a u t h o r )
REFERENCES u s e r s
(id_user)
);
Осталась последняя таблица -
posts
(сообщения). Здесь все
аналогично, только два внешних ключа:
create
table
id_post
posts
int
(
(10)
AUTO_INCREMENT,
m e s s a g e t e x t NOT NULL,
id_author in t
(10)
NOT NULL,
100
id to p ic
int
(10 ) NOT NULL,
PRIMARY KEY ( i d p o s t ) ,
FOREIGN KEY ( i d a u t h o r ) REFERENCES u s e r s
id u ser),
FOREIGN KEY ( i d t o p i c )
REFERENCES t o p i c s
(id to p ic )
Обратите внимание, внешних ключей у таблицы может быть
несколько, а первичный ключ в MySQL может быть только один.
Таким образом, для созданию БД и ее таблиц выполняем следующие
операции. Запускаем сервер MySQL, создаем БД f o r u m (create database
forum;), выбираем ее для использования (use forum;) и создаем три наших
таблицы:
mysql> create database forum;
Query OKt 1 row affected CO. 00 sec)
mysql> use forum;
Database changed
mysql> create tab le users C
-> id_user int CIO) AUTD_I NCREMENT,
-> name varcharC^O) MOT MULL,
-> email varcharC^O) NOT NULL,
-> password varchar CIS) MOT MULL,
-> PRIMARY KEY Cid_user)
-> );
Query OK, О rows affected C°.И sec)
mysql> c r e a t e t a b 'e t o p ic s (
-> id _ t o p ic in t CIO) AUTO_INCREM E NT,
-> topic_nan:e v/archar СЮ0) NOT NULL,
- > id la u t h o r in t СЮ) MOT NULL f
-> PRIMARY KEY C id _ t o p ic ) ,
-> FOREIGN
KEY C id _a u th o r) REFERENCES
u se rs ( id - J is e r )
-> );
Query OK, О rows a ff e c t e d CO.03 se c )
mysql> c r e a t e t a b le p o sts C
-> id _ p o s t in t СЮ) AUTO_IMCREMEMTr
-> message t e x t MOT MULL,
-> i d_author i nt £10) NOT NULL f
-> id _ t o p ic in t СЮ) NOT NULL,
- > PRIMARY
KEY ( i d _ p o s t ) ,
-> FOREIGN
KEY C id _a u th o r) REFERENCES u se rs (id - M s e r ),
-> FOREIGN KEY ( id _ t o p ic ) REFERENCES t o p ic s Q d t o p i t )
-> ) ;
Query OK, 0 rows a ff e c t e d C °.0 4 se c )
mysql>
101
Обратите внимание, одну команду можно писать в несколько строк,
используя клавишу Enter (MySQL автоматически подставляет символ новой
строки ->), и только после разделителя (точки с запятой) нажатие клавиши
Enter приводит к выполнению запроса.
Помните, если вы сделали что-то не так, всегда можно удалить
таблицу или всю БД с помощью оператора DROP. Исправлять что-то в
командной строке крайне неудобно, поэтому иногда (особенно на начальном
этапе) проще писать запросы в каком-нибудь редакторе, например в
Блокноте, а затем копировать и вставлять их в черное окошко.
Итак, таблицы созданы, чтобы убедиться в этом вспомним о
команде s h o w t a b l e s :
И, наконец, посмотрим структуру нашей последней таблицы p o s t s :
iRysql> d e s c r ib e p a s t s ;
-I----------------j-------------1-------- 1--------1------------ 1--------------------- h
|
F ie ld
| Type
| Mull
| Key
| D e fa u lt
|
I
I
I
id _ p o s t
message
id _ a u th o r
id _ t o p ic
|
j
j
j
| NO
j NO
j NO
j NO
| PRI
j
j MUL
j MUL
| NULL
j NULL
j MULL
| MULL
| E x tra
|
■+-------------- 1------------- 1-------- 1------- 1------------ 1--------------------- h
in t ( 1 0 )
te x t
in t f lG )
i n t (10 )
|au to _in cre m e n t
|
rl---------------i-------------1-------- 1--------1------------ 1--------------------- h
4 rows in s e t
(0 .0 6 s e c )
ircysql> ______________________________________________________________________________________
Теперь становятся понятны значения всех полей структуры, кроме
поля DEFAULT . Это поле значений по умолчанию. Мы могли бы для какогонибудь столбца (или для всех) указать значение по умолчанию. Например,
если бы у нас было поле с названием "Женаты\ Замужем" и типом ENUM ('да',
'нет'), то было бы разумно сделать одно из значений значением по
умолчанию.
102
Синтаксис был бы следующий:
m arried
enum
( 'д а ',
'н е т ')
NOT NULL d e f a u l t ( ' д а ' )
Т.е. это ключевое слово пишется через пробел после указания типа
данных, а в скобках указывается значение по умолчанию.
Но вернемся к нашим таблицам. Теперь нам необходимо внести
данные в наши таблицы. На сайтах, вы обычно вводите информацию в какиенибудь html-формы, затем сценарий на каком-либо языке (php, java...)
извлекает эти данные из формы и заносит их в БД. Делает он это
посредством SQL-запроса на внесение данных в базу.
Для
этого
используется
оператор INSERT.
Синтаксис
можно
использовать двух видов. Первый вариант используется для внесения данных
во все поля таблицы:
INSERTINTO и м я _ т а б л и ц ы VALUES
( 'з н а ч е н и е _ п е р в о г о _
с т о л б ц а ',
'з н а ч е н и е _ в т о р о г о _ с т о л б ц а ',...,
'з н а ч е н и е _ п о с л е д н е г о _ с т о л б ц а ') ;
Давайте попробуем внести в нашу таблицу u s e r s
следующие
значения:
INSERT
INTO
's e r g e y @ m a il.r u ',
users
VALUES
( '1 ','s e r g e y ',
'1 1 1 1 ') ;
itiysql> INSERT INTO u sers VALUES (' 1
Query OK, 1 row a ffe c te d fO.OO se c)
s e r ge y ' , 1sergeyt&nai 1. r u T , '1 1 1 1 ');
inysql> _
Второй вариант используется для внесения данных в некоторые поля
таблицы:
INSERT
'и м я _ с т о л б ц а ')
'з н а ч е н и е
INTO
VALUES
второго
имя_таблицы
('и м я _ с т о л б ц а ',
('з н а ч е н и е _ п е р в о г о _ с т о л б ц а ',
с т о л б ц а ');
В нашей таблице u s e r s все поля обязательны для заполнения, но
наше первое поле имеет ключевое слово - AUTQ_INCREMENT (т.е. оно
103
заполняется автоматически), поэтому мы можем пропустить этот столбец:
INSERT
( 'v a le r a ',
INTO
users
(nam e,
'v a le r a @ m a il.r u ',
mysql> INSERT INTO u s e r s VALUES ( 11
Query OK, 1 row a f f e c t e d ( 0 . 0 0 se c )
em ail,
passw ord)
VALUES
'2222');
1s e r g e y ',
' se rg eyl& n ail. r u ',
*111 1 ' ) ;
mysql> INSERT INTO u s e r s (name, e m a i l , password) VALUES ( ' v a l e r a ' ,
r u ' , '2 2 2 2 ’) l
Query OK, 1 row a f f e c t e d ( 0 . 0 0 se c )
'v a le r a @ m a il.
mysql> _
Если бы у нас были поля с типом NULL , т.е. необязательные для
заполнения, мы бы тоже могли их проигнорировать. А вот если попытаться
оставить пустым поле со значением NOT NULL , то сервер выдаст сообщение
об ошибке и не выполнит запрос. Кроме того, при внесении данных сервер
проверяет связи между таблицами. Поэтому вам не удастся внести в поле,
являющееся внешним ключом, значение, отсутствующее в связанной
таблице. В этом вы убедитесь, внося данные в оставшиеся две таблицы.
Но прежде внесем информацию еще о нескольких пользователях.
Чтобы добавить сразу несколько строк, надо просто перечислять скобки со
значениями через запятую:
mysql>
->
->
->
INSERT INTO u s e r s (name, e m a i l , password) VALUES
( ' k a t y ' , ' katyl&gmail. ru ' , ' 3 3 3 3 r) ,
( ' s v e t a ' , ' sv eta @ ra m b le r. ru ' , ' 4 4 4 4 r) ,
( ' o l e g ' , ' o le g @ y a n d e x .ru ' , ' 55 55 5' )
-> ;
Query OK, 3 rows a f f e c t e d ( 0 . 0 0 s e c )
R e c o r d s : 3 D u p l i c a t e s : 0 W arn in g s: 0
mvsal>_____________________________________________________________________________
Теперь внесем данные во вторую таблицу - t o p i c s (темы). Все тоже
самое, но надо помнить, что значения в поле i d _ a u t h o r
присутствовать в таблице u s e r s (пользователи):
m(y5ql> INSERT INTO t o p ic s (topic^nam e , id _ a u th o r ) VALUES
-> ( ' Oi рыбалке', ' 1 ' ) ,
-> ('велосипед ы ', ' 2 Г) ,
-> ('ночные кл уб ы ', ' 1 ' ) ,
-> ( ' Oi рыбалке' , '4 ' ) ;
Query ОК, 4 rows a f f e c t e d (0 .1 6 s e c )
R e co rd s: 4 D u p lic a t e s : 0 W arnings: 0
mysql>
должны
104
Теперь давайте попробуем внести еще одну тему, но с i d _ a u t h o r ,
которого в таблице u s e r s нет (т.к. мы внесли в таблицу u s e r s только 5
пользователей, то id=6 не существует):
jnysql> INSERT INTO t o p i c s
-> ( ' ф у т б о л Г6Г) ;
(to p ic _n a m e
, i d _ a u t h o r ) VALUES
ERROR 1452 ( 2 3 0 0 0 ) : Cannot add or update a c h i l d row: a f o r e i g n key c o n s t r a i n t f
a i l s ( ’ forum ’ . ’ t o p i c s ’ , CONSTRAINT ’ t o p i c s _ i 6 f k _ l ’ FOREIGN KEY ( ’ i d _ a u t h o r ’ ) REF
ERENCE5 ’ u s e r s ’ ( ’ i d _ u s e r ’ ) )
mysql> _
Сервер выдает ошибку и говорит, что не может внести такую строку,
т.к. в поле, являющемся внешним ключом, стоит значение, отсутствующее в
связанной таблице u s e r s .
Теперь внесем несколько строк в таблицу p o s t s (сообщения), помня,
что в ней у нас 2 внешних ключа, т.е. i d _ a u t h o r и i d _ t o p i c , которые мы
будем вносить должны присутствовать в связанных с ними таблицах:
mysql> INSERT INTO1 p o sts (m essage, id _ a u t h o r , i d _ t оp i c ) VAL UE S
-> ( 1думаю, надо сделать та к 1, 11 1 , 11 1) ,
-> ( 'с о г л а с е н 1, ' 2' , ' 4 ' ) ,
-> ( ' а еще можно сделать т а к ', ' 3 ' , ' 1 ' ) ,
-> ( 'с о г л а с е н ', ' 2' , ' 1 Т) ;
Query 0К, 4 rows a f f e c t e d (0 ,0 7 s e c )
R e co rd s: 4 D u p lic a t e s : 0 W arning s: 0
mysql>
Итак, у нас есть 3 таблицы, в которых есть данные.
Выборка данных
Итак, в нашей БД f o r u m есть три таблицы: u s e r s (пользователи),
t o p i c s (темы) и p o s t s (сообщения). И мы хотим посмотреть, какие данные
в них содержатся. Для этого в SQL существует оператор SELECT. Синтаксис
его использования следующий:
SELECT ч т о _ в ы б р а т ь
FROM о т к у д а _ в ы б р а т ь ;
Вместо "ч т о _ в ы б р а т ь " мы должны указать либо имя столбца,
значения которого хотим увидеть, либо имена нескольких столбцов через
запятую, либо символ звездочки (*), означающий выбор всех столбцов
таблицы. Вместо "о т к у д а _ в ы б р а т ь " следует указать имя таблицы.
105
Давайте сначала посмотрим все столбцы из таблицы u s e r s :
SELECT * FROM u s e r s ;
y s q l> SELECT * FROM u s e r s ;
------------ 1----------- i--------id _ u s e r
|
name | email
password
------------ 1----------- i--------1 |
2 I
3 I
4 I
5
I
sergey
serg ey
vvaale
lera
ra
katy
k aty
sveta
s v e ta
o
oll eeg
g
|| sergey
sergey&mai 1. ru
|| vvaleraJjimai
a le r a
1. ru
I katy^g
katy®gjnai 1. ru
| [email protected]
s vet [email protected] ambler. r u
o
[email protected]
le g & v
ru
|
|
|
|
|
1111
2222
3333
4444
55555
rows in s e t (0 .1 0 se c )
ysqb
Вот и все наши данные, которые мы вносили в эту таблицу. Но
предположим, что мы хотим посмотреть только столбец i d _ u s e r . Для этого
в запросе мы укажем имя этого столбца:
SELECT i d
user
FROM u s e r s ;
raysql> SELECT ic L u s e r FROM u s e rs ;
-i---------+
ic L u s e r |
■h
-+
1 1
1
—
—
2
3
4
5 1
1
+
-+
5 rows 1Fl se t (0 .0 0 se c)
—
mysql>
А если мы захотим посмотреть, например, имена и e - m a i l наших
пользователей, то мы перечислим интересующие столбцы через запятую:
SELECT n a m e ,
mysql> SELECT name,
em ail
email FROM u s e r s ;
| name
| email
1
|
1
1
j
1
|
|
1
j
j
1
sergey
v a le ra
katy
sveta
ole g
se rg [email protected] m ail. ru
v a l e r a @ m a il. ru
k a [email protected] gm ail.ru
sv e ta @ ra m b le r. ru
[email protected]
5 rows i n s e t
FROM u s e r s ;
1
(0 .0 0 sec)
mysql> _
Аналогично, вы можете посмотреть, какие данные содержат и другие
наши таблицы. Давайте посмотрим, какие у нас существуют темы:
SELECT * FROM t o p i c s ;
106
jn y sq l> S E L E C T * FROM t o p i c s ;
rt------------ 1----------------1------------ h
| ic L to p ic
| ta p ic _ n a m e
| ic L a u th o r
1
1
|
1
-|-------------------------- ----------------------------------- --------------------------- _
1 |
2 I
3 j
4 j
о рыбалке
велосипеды
ночные клубы
о рыбалке
|
I
j
|
1 |
2\
1 j
4 |
rl------------ 1----------------1------------ h
4 row s in
set
£ 0 .0 0 s e c )
m y s q ls ____________________________________________________
Для вывода тем
слово
ORDER
по
алфавиту в
SQL
существует ключевое
BY после которого указывается имя столбца, по которому
будет происходить сортировка. Синтаксис следующий:
SELECT
имя с т о л б ц а
имя_столбца
FROM
имя_таблицы
ORDER
BY
сортировки;
По умолчанию сортировка идет по возрастанию, но это можно
изменить, добавив ключевое слово DESC.
Теперь данные отсортированы в порядке по убыванию.
Сортировку можно производить сразу по нескольким столбцам.
Например, следующий запрос отсортирует данные по столбцу t o p i c _ n a m e ,
и если в этом столбце будет несколько одинаковых строк, то в столбце
i d _ a u t h o r будет осуществлена сортировка по убыванию:
107
mysqls SELECT * FROM t o p ic s ORDER BY topic_name DESC, id _a u th o r DESC
id _to p ic
| topic_name
i d_author
о рыбалке
□ рыбалке
ночные клубы
велосипеды
4 rows in set ( 0. 00 sec)
mysqls
Сравните результат с результатом предыдущего запроса.
Очень часто бывает, что не нужна вся информация из таблицы.
Например, чтобы узнать, какие темы были созданы пользователем s v e t a
( i d = 4 ) . Для этого в SQL есть ключевое слово W H E R E , синтаксис у такого
запроса следующий:
SELECT и м я с т о л б ц а
FROM и м я т а б л и ц ы WHERE у с л о в и е ;
Для нашего примера условием является идентификатор пользователя,
т.е. нам нужны только те строки, в столбце i d _ a u t h o r которых стоит 4
(идентификатор пользователя sveta):
SELECT * FROM t o p i c s WHERE i d _ a u t h o r =4;
mysql> SELECT * FROM t o p i c s WHERE id _ a n th o r= 4 ;
| id _to p ic
| topic_name | id_authior
4 | о рыбалке
|
4
1 row in set CO-oo sec)
mysql>
Или мы хотим узнать, кто создал тему "в е л о с и п е д ы ":
(nysql> SELECT * FROM topics WHERE to p iс_палзе= велосипеды ;
1
1
1
-i--------------------------- ----------------------------- ----------------------------- _
| id_topi"c | topic_najne| icLauthor |
1
1
2 | велосипеда: |
1
2 \
-|--------------------------- ----------------------------- ----------------------------- _
i------------ 1------------- 1------------- 1~
1 raw in set
CO-00 sec)
inysql> ___________________________________________________________________
Конечно, было бы удобнее, чтобы вместо i d автора, выводилось его
имя, но имена хранятся в другой таблице. В дальнейшем можно будет узнать,
как выбирать данные из нескольких таблиц. А пока узнаем в таблице 2, какие
условия можно задавать, используя ключевое слово WHERE.
108
Таблице 2. Условия для ключевого слова WHERE.
Оператор
Описание
Отбираются значения равные указанному
Пример:
WHERE i d _ a u t h o r = 4 ;
SELECT * FROM t o p i c s
= (равно)
Результат:
m ysq l> S E L E C T * FROM t o p i c s WHERE id _ a u t h o r = 4
---------1------------------ 1-----------------1-
|
id _ t o p ic
| t o p ic _ n a m e | i c L a u t h o r
---------1------------------ 1
4
| о ры балке
1
1 row in set
CO-00
|
|
4
1­
|
1-----------------1­
sec)
Отбираются значения больше указанного
Пример:
SELECT * FROM t o p i c s WHERE i d _ a u t h o r > 2 ;
> (больше)
Результат:
Отбираются значения меньше указанного
Пример:
SELECT * FROM t o p i c s
<
(меньше)
WHERE i d _ a u t h o r < 3 ;
Результат:
s q l> S E L E C T * FROM t o p i c s WHERE id _ a u t h o r < 3
id _ t o p ic
| t o p ic _ n a m e
I id _ a u t h o r
□ рыбалке
ночные клубы
велосипеды
row s i n
set
>=
Отбираются
(больше
Пример:
Ш .СЮ s e c
значения
большие
и
равные
указанному
109
или равно) SELECT * FROM t o p i c s WHERE i d _ a u t h o r > = 2 ;
Результат:
m ysq b
S E L E C T * FROM t o p i c s WHERE id _ a u t h o r > = 2 ;
| id L t o p ic
| t o p ic _ n a m e
2
4
2
row s i n
| ic L a n t h o r
| в е л о си п е д а
I о рыбалке
set
CO .0 0 s e c )
invsqI>
Отбираются значения меньшие и равные указанному
Пример:
SELECT * FROM t o p i c s
<=
(меньше
или равно)
WHERE i d _ a u t h o r < = 3 ;
Результат:
тузр Ъ
S E L E C T * FROM t o p i c s WHERE id _a u t£ io r < ^ 3 ;
| ic L t o p ic
1
3
| t o p ic _ n a n s e
2
3 row s i n
| ic L a u t h o r
| о рыбалке
j ночные клубы
j в е л о си п е д а
set
( 0 .0 0
sec)
mysaliJ
Отбираются значения не равные указанному
Пример:
SELECT * FROM t o p i c s
!= (не
Результат:
равно)
m ysq l> S E L E C T * FROM t o p i c s
WHERE i d _ a u t h o r ! = 1 ;
WHERE l
Отбираются строки, имеющие значения в указанном поле
IS NOT
Пример:
SELECT
*
NULL
NULL ;
Результат:
FROM
to p ic s
WHERE
id _ au th o r
IS
NOT
110
Отбираются строки, не имеющие значения в указанном поле
Пример:
SELECT
*
FROM
to p ic s
WHERE
id _ au th o r
IS
NULL;
IS NULL Результат:
™ y s q l> S E L E C T * FROM t o p i c s WHERE i d _ a u t h o r
Em pty s e t £ 0 . 00 s e c )
IS
NULL
iH vsal>____________________________________________________________________________
Empty set - нет таких строк.
Отбираются значения, находящиеся между указанными
Пример:
SELECT
*
FROM t o p i c s
WHERE
id _ au th o r
BETWEEN 1
AND 3;
BETWEEN
(между)
Результат:
m ysq b
SELECT
| id _ t o p ic
2
IN
(значение
BETWEEN 1 AND 3 ;
ic L a u th o r
| о рыбалке
j ночные клубы
j велосипеды
set
C O .00 s e c )
Отбираются значения, соответствующие указанным
Пример:
SELECT
*
содержитс
4);
я)
FROM t o p i c s WHERE l d j i r t h o r
| t o p ic _ n a m e
1
3
3 row s i n
*
Результат:
FROM
to p ic s
WHERE
id _ au th o r
IN
(1,
111
ly s q b
SELECT
- FROM t o p i c s WHERE i d _ a u t h o r
------------ 1------------------ 1-------------h
IN
C l,
4 );
| id J b o p ic
| t o p ic _ n a m e
| ic L a u t h o r
|
1
1------------------- 1­
1-----| о ры балке--------|
1-|
В
j ночные клубы j
1 j
4
j о ры балке
|
4 |
3 row s i n
set
C 0 .0 0 s e c )
Отбираются значения, кроме указанных
Пример:
NOT IN
SELECT
(значение
4);
не
Результат:
*
FROM t o p i c s
WHERE i d _ a u t h o r
NOT
IN (1,
содержите
я)
Отбираются значения, соответствующие образцу
Пример:
SELECT
*
FROM
to p ic s
WHERE
to p ic
name
LIKE
'в е л % ';
LIKE
Результат:
(соответст
вие)
Возможные метасимволы оператора LIKE будут рассмотрены
ниже.
NOT
LIKE
Отбираются значения, не соответствующие образцу
(не Пример:
соответств SELECT * FROM t o p i c s
ие)
'в е л ! ';
WHERE
to p ic_ n am e
NOT
LIKE
112
Результат:
m ysq b
S E L E C T * FROM t o p i c s WHERE t o p ic _ n a in e MOT L IK E
| id L t o p ic
1
3
4
3 row s i n
| t o p ic _ n a m e
| id _ a tith o r
| о рыбалке
I ночные клубы
j о рыбалке
|
set
гЕ е л Я ’ ;
C O .00 s e c )
Метасимволы оператора LIKE
Поиск с использованием метасимволов может осуществляться только
в текстовых полях.
Самый распространенный метасимвол -
%. Он означает любые
символы. Например, если необходимо найти слова, начинающиеся с букв
"в е л ", то напишем LIKE ' в е л % ' , а если хотим найти слова, которые
содержат символы "к л у б ", то мы напишем LIKE ' % к л у б % ' . Например:
m ysq b
S E L E C T * FROM t o p i c s WHERE t o p ic _ n a m e L I K E
| id L t o p ic
| t o p ic _ n a m e
| id _ a u t h o r
|
|
3
| ночные клубы
|
1
1 row i n
set
1
'9ЁклубЯйг ;
CO-03 s e c )
m ysq b _
Еще один часто используемый метасимвол - _. В отличие от %,
который
обозначает
несколько
или
ни
одного
символа,
нижнее
подчеркивание обозначает ровно один символ. Например:
m ysq l>
SELECT
| id _ t o p ic
1
4
2
row s i n
- FROM t o p i c s
| t o p ic _ n a m e
WHERE t o p ic _ n a m e L IK E
| i d i_ a u t h o r
|
1
4
1
I
| о рыбалке
j о рыбалке
set
рыбЗйг ;
CO. 00 s e c )
m ysq b _
Обратите внимание на пробел между метасимволом и "р ы б ", если его
пропустить, то запрос не сработает, т.к. метасимвол обозначает ровно один
символ, а пробел - это тоже символ.
113
Вложенные запросы
При работе с таблицами базы данных возникло одно неудобство.
Когда необходимо было узнать, кто создал тему "в е л о с и п е д ы ", и делали
соответствующий запрос:
Вместо имени автора, получали его идентификатор. Это и понятно,
ведь делали запрос к одной таблице - Т ем ы , а имена авторов тем хранятся в
другой таблице - П о л ь з о в а т е л и . Поэтому, узнав идентификатор автора
темы, нам надо сделать еще один запрос - к таблице П о л ь з о в а т е л и , чтобы
узнать его имя:
В SQL предусмотрена возможность объединять такие запросы в один
путем превращения одного из них в подзапрос (вложенный запрос). Итак,
чтобы узнать, кто создал тему "в е л о с и п е д ы ", сделаем следующий запрос:
mysql> SELECT паше FROM users WHERE id_user IN
-> (SELECT icLauthor FROM topics WHERE topi c_najne=‘ велосипеды");
|+ -------------- + | name
[
Lj-------------| v a le r a |
Lj------------- -t1 raw i n s e t
( 0 .0 0 s e c )
mysq^ _
То есть, после ключевого слова WHERE, в условие мы записываем еще
один
запрос.
MySQL
сначала
обрабатывает
подзапрос,
возвращает
i d _ a u t h o r = 2 , и это значение передается в предложение WHERE внешнего
запроса.
В одном запросе может быть несколько подзапросов, синтаксис у
такого запроса следующий:
114
SELECT и м я _ с т о л б ц а
условия
FROM и м я _ т а б л и ц ы WHERE ч а с т ь
IN
(SELECT и м я _ с т о л б ц а
условия
FROM и м я _ т а б л и ц ы WHERE ч а с т ь
IN
(SELECT и м я _ с т о л б ц а
FROM и м я _ т а б л и ц ы WHERE
условие)
Обратите внимание, что подзапросы могут выбирать только один
столбец, значения которого они будут возвращать внешнему запросу.
Попытка выбрать несколько столбцов приведет к ошибке.
Давайте для закрепления составим еще один запрос, узнаем, какие
сообщения на форуме оставлял автор темы "в е л о с и п е д ы ":
m y s q ^ S E L E C T m e s sa g e FROM p o s t s WHERE i d _ a u t h o r IN
-> ( S E L E C T i c L a u t h o r FROM t o p i c s WHERE t o p i с_п азп е= 1вел осип еды 1) ;
н------------(| m e s sa g e
|
1 ----------h
| со гл а се н
| со гл а се н
|
|
н----------- +
2 ra w s i n
set
( 0 .1 6 s e c )
m vso l>
Теперь усложним задачу, узнаем, в каких темах оставлял сообщения
автор темы "в е л о с и п е д ы ":
m ysq l> S E L E C T t o p ic _ n a m e FROM t o p i c s WHERE i d _ t o p i c IN
-> ( S E L E C T i d _ t o p i c FROM p o s t s WHERE i d _ a u t h o r IN
-> ( S E L E C T
l'c L a u t h o r FROM t o p i c s WHERE t o p ic _ n a jn e = ' Е е л о с и п е д ь Г ) ) ;
1-------------- h
|t o p ic _ n a m e
|
■
+-------------- b
| о рыбалке
1 о рыбалке
■
+-------------- h
2
ra w s i n
set
( 0 .0 4
se c)
m ysql> __________________________________________________________________________________________________________
Давайте разберемся, как это работает.
•
Сначала MySQL выполнит самый глубокий запрос:
SELECT
to p ic
id _ au th o r
FROM
to p ic s
WHERE
nam e=’велоси п еды ’
•
Полученный результат ( i d _ a u t h o r = 2 ) передаст во внешний
запрос, который примет вид:
115
SELECT i d _ t o p i c
•
FROM p o s t s
WHERE i d _ a u t h o r
IN ( 2 ) ;
Полученный результат ( i d _ t o p i c : 4 , 1 ) передаст во внешний
запрос, который примет вид:
SELECT
to p ic_ n am e
FROM
to p ic s
WHERE
id _ to p ic
IN
(4,1);
•
И выдаст окончательный результат ( t o p i c _ n a m e : о рыбалке, о
рыбалке). Т.е. автор темы "в е л о с и п е д ы ” оставлял сообщения в теме "О
р ы б а л к е ", созданной Сергеем ( i d = 1 ) и в теме "О р ы б а л к е ", созданной
Светой ( i d = 4 ).
Вот собственно и все, что хотелось сказать о вложенных запросах.
Хотя, есть два момента, на которые стоит обратить внимание:
•
Не рекомендуется создавать запросы со степенью вложения
больше трех. Это приводит к увеличению времени выполнения и к
сложности восприятия кода.
•
Приведенный синтаксис вложенных запросов, скорее наиболее
употребительный, но вовсе не единственный. Например, могли бы вместо
запроса
SELECT n a m e
(SELECT
to p ic
FROM u s e r s
id _ au th o r
WHERE i d _ u s e r
FROM
IN
to p ic s
WHERE
n a m e = 'в е л о с и п е д ы ’ );
написать
SELECT n a m e
(SELECT
to p ic
FROM u s e r s
id _ au th o r
WHERE i d _ u s e r =
FROM
to p ic s
WHERE
name= ’в е л о с и п е д ы ’ ) ;
Т.е.
можно
использовать
ключевым словом WHERE.
любые
операторы,
используемые
с
116
Объединение таблиц (внутреннее объединение)
Предположим, необходимо узнать, какие темы, и какими авторами
были созданы. Для этого проще всего обратиться к таблице Темы ( t o p i c s ):
Но, что если необходимо, чтобы в ответе на запрос были не
идентификаторы авторов, а их имена? Вложенные запросы нам не помогут,
т.к. в конечном итоге они выдают данные из одной таблицы. А нам надо
получить
данные из двух таблиц ( Темы и П о л ь з о в а т е л и ) и объединить
их в одну. Запросы, которые позволяют это сделать, в SQL называются
Объединениями. Синтаксис самого простого объединения следующий:
SELECT
столбцов
имена
таблицы
столбцов
2
таблицы
FROM
имя
имя таб л и ц ы 2;
Давайте создадим простое объединение:
mysq;l> S E L E C T t o p i c _ n a m e , name FROM t o p i c s ,
+■
^—------------ +
1 t o p ic _ n a jn e
| папе
|
--------------------------1_------------- i| о рыбалке
se rg ey |
| велосипеды
se rg ey |
j ночные клубы j s e r g e y |
| о рыбалке
se rg ey |
j о рыбалке
v a le r a |
v a le r a j
| велосипеды
1 ночные клубы | v a l e r a |
| о рыбалке
v a le r a j
| о рыбалке
k a ty
1 велосипеды
k a ty
| ночные клубы | k a t y
1 о рыбалке
k a ty
| о рыбалке
sv e ta
| велосипеды
sv e ta
1 ночные клубы | s v e t a
| о рыбалке
sv e ta
j о рыбалке
o le g
| велосипеды
o le g
1 ночные клубы | o le g
| о рыбалке
o le g
-------------------------+------------- -h
20 row s i n s e t ( 0 .2 7 s e c )
itiysql>
u se rs;
1 ,и м ен а
таблицы
1,
117
П олучилось не совсем то, что ож идалось. Такое объединение научно
назы вается декартовы м произведением, когда каж дой строке первой таблицы
ставится в соответствие каж дая строка второй таблицы. В озмож но, бываю т
случаи, когда такое объединение полезно, но это явно не этот случай.
Ч тобы результирую щ ая таблица вы глядела так, как требовалось,
необходимо указать условие объединения. С вязы ваем наш и таблицы по
идентиф икатору автора, это и будет наш им условием. Т.е. укаж ем в запросе,
что необходимо вы водить только те строки, в которы х значения поля
i d _ a u t h o r таблицы t o p i c s
совпадаю т со значениям и поля i d _ u s e r
таблицы u s e r s :
mysql> SELECT topic^name, name FROM topics, users WHERE topics.id_author=users.i
d_user;
+■
-+------------- +
| name |
1 topic_name
-+------------- +
+■
| о рыбалке
| sergey |
j велосипеды j valera j
j ночные клубы j sergey j
| о рыбалке
j sveta
~+------------- +
4 rows in set (t>.01 sec)
mvsol>
Здесь будет понятнее:
-|-------------------------- __i---------------------| id _ a u th o r
1 to p ic _ n a m e
t o p i c_name
| о рыбалке
1
| велосипеды
г
j ночные клубы
1
| о рыбалке
1
4
н-------------------------- н ----------------------
о рыбалке
велосипеды
ночные клубы
о рыбалке
-+ --------------| паше
| s e rg e y
| v a le r a
| s e rg e y
| s v e ta
н ----------------
Т.е. в запросе создали следую щ ее условие: если в обеих таблицах есть
одинаковы е
идентиф икаторы ,
то
строки
с
этим
идентиф икатором
необходимо объединить в одну результирую щ ую строку.
О братите внимание н а две вещи:
•
Е сли в одной из объединяемы х таблиц есть строка с идентификатором ,
которого нет в другой объединяемой таблице, то в результирую щ ей
таблице строки с таким идентиф икатором не будет. В примере есть
пользователь O l e g
( i d = 5 ) , но он не создавал тем, поэтом у в
результате запроса его нет.
118
• При указании условия название столбца пишется после названия
таблицы, в которой этот столбец находится (через точку). Это сделано
во избежание путаницы, ведь столбцы в разных таблицах могут иметь
одинаковые названия, и MySQL может не понять, о каких конкретно
столбцах идет речь.
Вообще, корректный синтаксис объединения с условием выглядит
так:
SELECT и м я т а б л и ц ы
имя таб л и ц ы
1.и м я
столбца1
таблицы
1.и м я
столбца2
таблицы
имя таб л и ц ы 2 .и м я
столбца1
таблицы 2,
имя таб л и ц ы 2 .и м я
столбца2
таблицы 2
1,
1,
FROM
имя таб л и ц ы
1,
имя таб л и ц ы 2
WHERE
имя таб л и ц ы
1.и м я
столбца
по к о т о р о м у о б ъ е д и н я е м =
имя таб л и ц ы 2 .и м я
столбца
по к о т о р о м у о б ъ е д и н я е м ;
Если имя столбца уникально, то название таблицы можно опустить
(как делали в примере), но делать это не рекомендуется.
Как вы понимаете, объединения дают возможность выбирать любую
информацию из любых таблиц, причем объединяемых таблиц может быть и
три, и четыре, да и условие для объединения может быть не одно.
Для примера давайте создадим запрос, который покажет нам все
сообщения, к каким темам они относятся и авторов этих сообщений.
Конечно, вся эта информация хранится в таблице С о о б щ е н и я (p o s t s ):
mysql> SELECT message, id _a u th o r, id _ t o p ic FROM po sts;
L---------------------------1
1
1
| message
| id _a u th o r | id _ t o p ic |
rf-------------------------- i----------- 1-----------h
------------------------- ------------------------ _
|
|
|
|
дуиаю, надо сделать так
согласен
а еще можносделать так
согласен
|
|
|
|
1 |
2 |
3 |
2
\
1
4
1
1
|
|
|
|
1
_|---------------------------------------------------------- j------------------------- у----------------------- _
4 rows in se t СО. 00 sec)
mvsg1> ____________________________________
119
Но чтобы вместо идентификаторов отображались имена и названия,
придется сделать объединение трех таблиц:
m ysq l> S E L E C T
p a s t s . m e s s a g e , t o p i c s . t o p i c _ n a m e , u s e r s , name
->
FROM p o s t s , t o p i c s , u s e r s
->
WHERE p o s t s . l d _ a u t h o r = u s e r s . l d _ u s e r AND p o s t s . i d _ t o p i c = t a p i c s . i d _ t o p i c ;
i-----------------------------i--------------1--------- 1| m essa g e
| t o p ic _ n a m e | name
|
i-----------------------------i--------------1--------- 1|
|
I
|
дуиаю , н адо с д е л а т ь
со гл а се н
а еще можно с д е л а т ь
со гл а се н
так
так
| о>
| О'
|о
| О'
ры балке
ры балке
ры балке
ры балке
|
j
|
j
se rg e y
v a le r a
k a ty
v a le r a
|
|
|
1
_|----------------------------------------------------------------|----------------------------- 1|-------------------- _
4 row s i n
set
£ 0 .0 0 s e c ) _____________________________________________________________________________________________
Т.е. объединили таблицы С о о б щ е н и я и П о л ь з о в а т е л и условием
p o s t s . i d _ a u t h o r = u s e r s . i d _ u s e r , а таблицы С о о б щ е н и я
и
Темы -
условием p o s t s . i d _ t o p i c = t o p i c s . i d _ t o p i c
Объединения, которые рассматривались, называются Внутренними
объединениями. Такие объединения связывают строки одной таблицы со
строками другой таблицы (а может еще и третьей таблицы). Но бывают
ситуации, когда необходимо, чтобы в результат были включены строки, не
имеющие связанных. Например, когда создавали запрос, какие темы и
какими авторами были созданы, пользователь O l e g в результирующую
таблицу не попал, т.к. тем не создавал, а потому и связанной строки в
объединяемой таблице не имел.
Поэтому, если нам потребуется составить несколько иной запрос вывести всех пользователей и темы, которые они создавали, если таковые
имеются - то нам придется воспользоваться Внешним объединением,
позволяющим выводить все строки одной таблицы и имеющиеся связанные с
120
ними строки из другой таблицы. О таких объединениях мы и будем говорить
позже.
Объединение таблиц (внешнее объединение)
Итак, надо вывести всех пользователей и темы, которые они
создавали,
если
таковые
имеются.
Если
воспользуемся
внутренним
объединением, то получим в итоге следующее:
mysql> SELECT users.nam e, t o p ic s . t o p ic_name
-> FROM t o p ic s , users
-> WHERE u se rs .id _u se r= to p ic s . id _au th o r;
| name
1 topic_name
|
j
j
|
|
j
|
|
|
+
U--------- +
sergey
v a le ra
sergey
sveta
о рыбалке
велосипеда
ночные клубы j
о рыбалке
4 rows in se t (0.4Э sec)
mysql> _
То есть в результирующей таблице есть только те пользователи,
которые создавали темы. А надо, чтобы выводились все имена. Для этого
немного изменим запрос:
SELECT
FROM
ON
u se rs.n a m e ,
users
to p ic s.to p ic_ n a m e
LEFT OUTER J O I N t o p i c s
u se rs.id _ u se r= to p ic s.id _ a u th o r;
И получим желаемый результат - все пользователи и темы, ими
созданные. Если пользователь не создавал тему, но в соответствующем
столбце стоит значение NULL .
itiysql^ S E L E C T
u s e r s .n a m e , t o p i c s . t o p i c _ n a m e
->
FROM u s e r s L E F T OUTER JO IN t o p i c s
->
ON u s e r s . i d _ u s e r = t o p i c s . i d _ a u t h o r ;
H
----------- 1------------------- h
| name
| t o p ic _ n a m e
1
------------------- ----------------------------------
se rg ey
se rg ey
v a le r a
о рыбалке
ночные клубы
в е л о си п е д а
NULL
о рыбалке
NULL
katy
sveta
O'l eg
------------------—
| —
6 row s i n
set
( 0 .0 4
-+
1
1
-+
sec)
itiysgl^
Итак, добавили в наш запрос ключевое слово - LEFT OUTER J O I N ,
указав тем самым, что из таблицы слева надо взять все строки, и поменяли
121
ключевое слово WHERE на ON. Кроме ключевого слова LEFT
J O I N может быть использовано ключевое слово RIGHT
OUTER
OUTER
JO IN .
Тогда будут выбираться все строки из правой таблицы и имеющиеся
связанные с ними из левой таблицы. И наконец, возможно полное внешнее
объединение, которое извлечет все строки из обеих таблиц и свяжет между
собой те, которые могут быть связаны. Ключевое слово для полного
внешнего объединения - FULL OUTER J O I N .
Давайте заменим в нашем запросе левостороннее объединение на
правостороннее:
miysql> S E L E C T
u s e r s , name, t o p i c s . t o p i с_пазпе
->
FROM u s e r s RIGHT OUTER JO IN t o p i c s
->
ON u s e r s . i d _ u s e r = t o p i c s . i d _ a u t h o r ;
i---------- 1---------------- 1|
naane | t o p ic _ n a m e
1
■i---------------------- ----------------------------------- i-
|
j
j
j
se rg e y
v a le r a
se rg e y
sv e ta
| о ры балке
j в е л о си п е д а
j ночные клубы
j о ры балке
j
■
+---------- 1---------------- h
4 ro w s i n s e t
( 0 .0 0 s e c )
in v s a ls ______________________________________________________________________
Как видите, теперь есть все темы (все строки из правой таблицы), а вот
пользователи только те, которые темы создавали (т.е. из левой таблицы
выбираются только те строки, которые связаны с правой таблицей).
К сожалению, полное объединение СУБД MySQL не поддерживает.
Подведем итог. Синтаксис для внешнего объединения следующий:
SELECT
таблицы
2
FROM
ON
где
имя
. имя
таблицы
ТИП
имя
столбца,
имя
ТИП ОБЪЕДИНЕНИЯ
имя_таблицы_2
объединения;
ОБЪЕДИНЕНИЯ
RIGHT OUTER J O I N
.
столбца
имя_таблицы_1
условие
1
-
либо
LEFT
OUTER
JO IN,
либо
122
2.3
Методические указания для обучающихся по промежуточной
аттестации по модулю
В период подготовки к промежуточной аттестации студенты вновь
обращаются к пройденному учебному материалу. При этом они не только
закрепляют полученные знания, но и получают новые. Подготовка студента к
промежуточной аттестации включает в себя три этапа:
- самостоятельная работа в течение семестра;
-
непосредственная
подготовка
в
дни,
предшествующие
промежуточной аттестации по темам курса;
- подготовка к ответу на вопросы, содержащиеся в билетах.
Подготовка к промежуточной аттестации осуществляется на основании
списка вопросов по изучаемой дисциплине, конспектов лекций, учебников и
учебных пособий, научных статей, информации среды интернет.
Литература
для
подготовки
к
промежуточной
аттестации
рекомендуется преподавателем. Для полноты учебной информации и ее
сравнения лучше использовать не менее двух источников. Студент вправе
сам придерживаться любой из представленных в литературе точек зрения по
спорной проблеме (в том числе отличной от преподавателя), но при условии
достаточной научной аргументации.
Основным источником подготовки к промежуточной аттестации
является
конспект
лекций,
где
учебный
материал
дается
в
систематизированном виде, основные положения его детализируются,
подкрепляются современными фактами и информацией, которые в силу
новизны не вошли в опубликованные печатные источники. В ходе
подготовки к промежуточной аттестации студентам необходимо обращать
внимание не только на уровень запоминания, но и на степень понимания
излагаемых проблем.
Для подготовки к промежуточной аттестации преподаватель проводит
консультацию по возникающим вопросам.
123
Промежуточная аттестация проводится по вопросам, охватывающим
весь пройденный материал. По окончании ответа преподаватель может
задать студенту дополнительные и уточняющие вопросы.
Оценка качества подготовки обучающихся осуществляется в двух
основных направлениях: оценка уровня освоения дисциплин и оценка уровня
сформированности компетенций студентов. Предметом оценивания являются
знания, умения и практический опыт обучающихся.
Положительно будет оцениваться стремление студента изложить
различные точки зрения на рассматриваемую проблему, выразить свое
отношение к ней, применить теоретические знания по современным
проблемам.
3. Перечень основной и дополнительной учебной литературы,
необходимой для освоения модуля
3.1 Основная литература
1. Крис Фиайли SQL [Электронный ресурс]/ Крис Фиайли— Электрон.
текстовые данные.— Саратов: Профобразование, 2017.— 452 с.— Режим
доступа: http://www.iprbookshop.ru/63823.html.— ЭБС «IPRbooks»
2. Полякова Л.Н. Основы SQL [Электронный ресурс]/ Полякова Л.Н.—
Электрон. текстовые данные.— М.: Интернет-Университет Информационных
Технологий
(ИНТУИТ),
2016.—
273
с.—
Режим
доступа:
http://www.iprbookshop.ru/52210.html.— ЭБС «IPRbooks»
3.2 Дополнительная литература
1. Бурков А.В. Проектирование информационных систем в Microsoft
SQL Server 2008 и Visual Studio 2008 [Электронный ресурс]/ Бурков А.В.—
Электрон. текстовые данные.— М.: Интернет-Университет Информационных
Технологий
(ИНТУИТ),
2016.—
310
c.—
http://www.iprbookshop.ru/52166.html.— ЭБС «IPRbooks»
Режим
доступа:
124
2.
Баженова И.Ю.
SQL и процедурно-ориентированные языки
[Электронный ресурс]/ Баженова И.Ю.— Электрон. текстовые данные.— М.:
Интернет-Университет Информационных Технологий (ИНТУИТ), 2016.—
166 с.— Режим доступа:
http://www.iprbookshop.ru/57532.html.—
ЭБС
«IPRbooks»
3. Пржиялковский В.В. Введение в Oracle SQL [Электронный ресурс]/
Пржиялковский В.В.— Электрон. текстовые данные.— М.: Интернет­
Университет Информационных Технологий (ИНТУИТ), 2016.— 336 с.—
Режим доступа: http://www.iprbookshop.ru/62808.html.— ЭБС «IPRbooks»
Орловский государственный
университет имени И.С. Тургенева
ДНТИПЛАГИАТ
ТВОРИТЕ СОБСТВЕННЫМ УМОМ
СПРАВКА
о результатах проверки текстового д о к ум е н та
на н а л и ч и е з а и м с т в о в а н и й
Проверка выполнена в системе
Антиплагиат.ВУЗ
Автор работы
Шадский Никита Геннадьевич
Факультет, кафедра,
номер группы
физико-математический, кафедра информатики
Тип работы
Выпускная квалификационная работа
РАЗРАБОТКА ПРОГРАММНО-МЕТОДИЧЕСКОГО ОБЕСПЕЧЕНИЯ МОДУЛЯ _ЯЗЫК SQL
ДИСЦИПЛИНЫ БАЗЫ ДАННЫХ ДЛЯ БАКАЛАВРОВ НАПРАВЛЕНИЯ ПОДГОТОВКИ
„ПРИКЛАДНАЯ ИНФОРМАТИКА_
Название файла
диплом
Шадский. doc
Процент заимствования
7,32%
Процент цитирования
1,71%
Процент оригинальности
90,97%
Дата проверки
10:53:46 23 июля 2018г.
Модули поиска
Сводная коллекция ЭБС; Кольцо вузов; Модуль поиска "ФГБОУ ВО ОГУ им.
И.С.Тургенева"; Модуль поиска общеупотребительных выражений; Модуль поиска
перефразирований Интернет; Модуль поиска перефразирований eLIBRARY.RU;
Модуль поиска Интернет; Коллекция eLIBRARY.RU; Цитирование; Коллекция РГБ
Работу проверил
Черкасова Владлена Владиславовна
ФИО проверяющего
Дата подписи
££
Подпись проверяющего
Чтобы убедиться
в подлинности справки,
используйте QR-код, который
содержит ссылку на отчет.
Ответ на вопрос, является ли обнаруженное заимствование
корректным, система оставляет на усмотрение проверяющего.
Предоставленная информация не подлежит использованию
в коммерческих целях.
1/--страниц
Пожаловаться на содержимое документа