Конспект урока по информатике "Сложные запросы на выборку"

11 класс

Тема урока «Cложные запросы на выборку к многотабличной базе данных».

Тип урока: урок изучения нового материала с элементами практики.

Цели:

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

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

Воспитательные: формирование общекультурных навыков работы с информационными системами, формирование информационной   культуры.

Методы:

объяснительно-иллюстрационный при объяснении нового материала;

репродуктивный, при выполнении заданий;

частично-поисковый, при выполнении практической работы;

Программно-дидактическое обеспечение: проектор, СУБД MS Access, презентация на тему «Реализация запросов к многотабличным базам данных», инструкции по выполнению практической работы.

Ход урока.

I. Организационный момент. Постановка целей урока.

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

2. Как организовать вычисления  различных показателей (суммы, максимального, минимального, среднего значения и т.п. ) в  сформированных по разным признакам группах  данных.

II. Изложение нового материала.

1.Как уже вам известно, ЗАПРОС –  это команда на выполнение определенного вида манипулирования данными. Существуют самые разные виды запросов: запросы на выборку, на удаление или обновление данных.

Многотабличные БД позволяют в одном запросе задействовать  данные из любых связанных таблиц.   Рассмотрим наиболее распространенный вид запроса - запрос на выборку данных. Организовать выполнение такой команды можно несколькими способами.

·               записать команду на универсальном языке SQL (Structured Query Language), на котором формулируются запросы во многих СУБД и, в том числе, в Libre Office .

·               для пользователей в Libre Office существует также возможность не составлять запросы непосредственно на этом языке, а реализовывать их при помощи более высокоуровневого средства – КОНСТРУКТОРА запросов. Однако, и в этом случае,  пользователь должен первоначально правильно сформулировать условия выбора на языке математической логики.

Общий вид команды запроса на выборку имеет следующий формат:

Условие выбора в команде это логическое выражение, которое должно быть истинным для отбираемых записей БД.

На языке SQL условие выбора строится на языке математической логики, по правилам описания логических выражений, уже знакомым нам, например,  по языку программирования Паскаль.

Сложные логические выражения строятся из простых, объединенных логическими операциями конъюнкция - И (AND), дизъюнкция - ИЛИ (OR) и отрицание НЕ (NOT). Имена полей выступают в качестве переменных, которые при помощи операций отношений (<, >, >= ,<=, <>) сравниваются с соответствующими им по типу константами или значениями других полей. Эти константы могут быть числового, текстового, логического типов, датами в разных форматах.

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

 

Построение запросов в КОНСТРУКТОРЕ запросов.

В Конструкторе запросов используется своеобразный табличный способ представления условий отбора.

Для получения запроса часто не требуется извлекать данные из всех таблиц БД. При формировании запроса сначала строится подсхема (выбираются те таблицы, которые в нее войдут).

 В Конструкторе реализация запроса осуществляется в форме таблицы и выполняется в следующем порядке:

·         В первой строке указываются поля, участвующие в запросе.

·         Вторая строка содержит имя таблицы, из которой извлекается соответствующе поле.

·         В третьей строке выбирается, если это необходимо, порядок    сортировки .

·         Флажки в четвертой строке отмечают признак вывода данного поля на экран  при выполнении запроса.

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

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

Рассмотрим примеры использования шаблонов и способы записи условий

Смысл символа	Шаблон	Результат поиска
? – любой один символ	За?ор	Забор Затор
* - любая группа символов	Д*нь	День Добрый день Длинная тень
# - любая цифра	#-й	5-й 9-й
[ ] – один символ из указанного набора	Иванов[аы]	Иванова Ивановы

Примеры условных выражений:

Not Москва	Не Москва
In (Саратов, Томск, Уфа)	Текст «Саратов», «Томск» или «Уфа»
< Р	Текст, начинающийся с букв «А» - «П»
>= Р	Текст, начинающийся с букв «Р» - «Я»
Date()	Текущая дата
>=01.01.06	Дата позднее 01.01.06
Beetween 01.01.2011 and 31.12.2011	Дата 2011 года
*.02.*	Дата февраля
Like «Р*»	Текст, начинающийся с буквы Р

3. Итоговые запросы.

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

III. Выполнение практической работы «Создание запросов к многотабличной базе данных».

Цель работы:

ð  отработка приемов реализации сложных запросов на выборку к многотабличной БД;

ð  обучение реализации запросов на удаление записей.

Программные средства: Libre Office

Реализовать в КОНСТРУКТОРЕ запросов следующие запросы к БД «Приемная комиссия».

IV. Итоги урока.

Оценить работу  и подвести итоги выполнения практической работы.

Домашнее задание.

1. Знать основные параметры команды запроса на выборку и правила записи сложных логических условий отбора данных                                                                                                                                                                                                                                   

• 1 слайд

  Запросы на выборку
  в многотабличных базах данных
  

• 2 слайд

  
  
  Запросы на выборку
  в многотабличных базах данных
  ЗАПРОС – это команда на выполнение определенного вида манипулирования данными. Виды запросов: запросы на выборку, на удаление или обновление данных и др.
  

• 3 слайд

  Условие отбора - это логическое выражение, которое должно быть истинным для отбираемых записей БД
  Сложные логические выражения строятся из простых, объединенных логическими операциями:
  конъюнкция - И (AND),
  дизъюнкция - ИЛИ (OR)
  отрицание - НЕ (NOT).
  Имена полей выступают в качестве переменных, которые при помощи операций отношений (<,>, >= ,<=, <>) сравниваются с соответствующими им по типу константами или значениями других полей. Эти константы могут быть числового, текстового, логического типов, датами в разных форматах.
  Пример условного выражения:
  (КЛАСС = «11а» OR КЛАСС = «10а») AND
  ПРЕДМЕТ = «История» AND ГОД = 5
  Правила выполнения операций конъюнкция (AND) и дизъюнкция (OR) при реализации запросов на выборку:
  Операция OR объединяет в одну выборку записи, удовлетворяющие хотя бы одному из условий.
  Операция AND работает иначе: сначала отбираются все записи, удовлетворяющие первому условию, затем из отобранных выбираются те, которые удовлетворяют второму условию (и т.д., если еще имеются условия)
  

• 4 слайд

  1 шаг
  результат
  2 шаг
  3 шаг
  

• 5 слайд

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

• 6 слайд

  Схема БД «Школа»
  УЧЕНИКИ
  КЛАССЫ
  УСПЕВАЕМОСТЬ
  
  1
  1
  М
  М
  Номер
  Кл. руководитель
  Число учащихся
  Класс
  Фамилия
  Имя
  Пол
  Адрес
  Предмет
  Четв. оценки
  Год. оценки
  Подчиненная таблица
  
  Основная таблица
  Подчиненная таблица
  
  Основная таблица
  

• 7 слайд

  УСПЕВАЕМОСТЬ
  КЛАССЫ
  УЧЕНИКИ
  1
  М
  1
  М
  Одной записи из таблицы «Классы» соответствует много записей из таблицы «Ученики»
  Одной записи из таблицы «Ученики» соответствует много записей из таблицы «Успеваемость»
  

• 8 слайд

  Связь таблиц «Классы» и «Ученики» через ключевое поле класс
  Связь таблиц «Ученики» и «Успеваемость» через ключевые поля класс и номер_уч
  

• 9 слайд

  Запросы на выборку
  в многотабличных базах данных
  Создать запрос на выборку в MS Access можно несколькими способами:
  - записать команду на универсальном языке SQL (Structured Query Language), на котором формулируются запросы во многих СУБД и, в том числе, в MS Access
  - реализовывать запрос при помощи более высокоуровневого средства – КОНСТРУКТОРА запросов.
  
  

• 10 слайд

  Общий вид команды запроса на выборку имеет следующий формат:
  
  выбрать <СПИСОК ПОЛЕЙ> для <УСЛОВИЯ ВЫБОРА>
  сортировать < КЛЮЧИ > по <порядок сортировки >
  
  На языке SQL это выглядит как:
  SELECT <СПИСОК ПОЛЕЙ>
  FROM < ИМЯ ТАБЛИЦ >
  WHERE <УСЛОВИЯ ВЫБОРА>
  ORDER BY <КЛЮЧИ СОРТИРОВКИ>
  Запросы на выборку
  в многотабличных базах данных
  

• 11 слайд

  Запрос на языке SQL
  Вывести все годовые отметки по всем предметам для ученика Пронина Ивана, отсортировав результаты по предметам по возрастанию.
  Общий вид запроса
  выбрать УСПЕВАЕМОСТЬ . Предмет, УСПЕВАЕМОСТЬ . Год
  для УЧЕНИКИ . Фамилия = “Пронин” И УЧЕНИКИ . Имя =”Иван”
  сортировать УСПЕВАЕМОСТЬ . Предмет по возрастанию.
  На языке SQL это выглядит как:
  SELECT [Успеваемость].[Предмет], [Успеваемость].[Год]
  FROM Ученики INNER JOIN Успеваемость ON
  ([Ученики].[Класс]=[Успеваемость].[Класс]) AND
  ([Ученики].[Номер_уч]=[Успеваемость].[Номер_уч])
  WHERE ((([Ученики].[Фамилия])="Пронин") And
  (([Ученики].[Имя])="Иван")))
  ORDER BY [Успеваемость].[Предмет];
  
  
  

• 12 слайд

  При формировании запроса к многотабличной БД сначала строится подсхема ( т.е. выбираются таблицы, которые в нее войдут)
  В Конструкторе реализация запроса осуществляется в форме таблицы и выполняется в следующем порядке:
  в первой строке указываются поля, участвующие в запросе.
  вторая строка содержит имя таблицы, из которой извлекается соответствующе поле.
  в третьей строке выбирается, если это необходимо, порядок сортировки .
  Флажки в четвертой строке отмечают признак вывода данного поля на экран при выполнении
  
  Конструктор запросов
  

• 13 слайд

  В строках «Условие отбора» и «или» в ячейках таблицы конструк-тора записываются условия, накладываемые на значения соот-ветствующих полей. Условия, стоящие в одной строке, должны вы-полняться одновременно, т. е. они соединяются между собой опе-рацией И; условия в разных строках соединяются операцией ИЛИ.
  
  

• 14 слайд

  Конструктор запросов
  Пример 1. Требуется получить список всех учеников 10-11 классов, у которых среди годовых отметок по информатике только «4» и «5».
  команда запроса в обобщенном виде:
  
  Выбрать Ученики.Класс, Ученики.Фамилия, Успеваемость.Предмет, Успеваемость.Год
  для Ученики.Класс = “1??” И Успеваемость.Предмет =” Информатика“ И Успеваемость.Год >=4
  сортировать Ученики.Класс по возрастанию, Ученики.Фамилия по возрастанию
  
  

• 15 слайд

  Примеры использования масок и шаблонов
  Смысл символа
  
  Шаблон
  
  Результат поиска
  
  ? – любой один символ
  
  За?ор
  
  Забор
  Затор
  
  * - любая группа символов
  
  Д*нь
  
  Добрый день
  Длинная тень
  День
  # - любая цифра
  
  #-й
  
  5-й
  9-й
  
  [ ] – один символ из ука-занного набора
  
  Иванов[аы]
  
  Иванова
  Ивановы
  
  

• 16 слайд

  Примеры условных выражений
  Not Москва
  
  Не Москва
  
  In (Саратов, Томск, Уфа)
  
  Текст «Саратов», «Томск» или «Уфа»
  
  < Р
  
  Текст, начинающийся с букв «А» - «П»
  
  > = Р
  
  Текст, начинающийся с букв «Р» - «Я»
  
  Date()
  
  Текущая дата
  
  > =01.01.06
  
  Дата позднее 01.01.06
  
  Beetween 01.01.2011 and 31.12.2011
  
  Дата 2011 года
  
  *.02.*
  
  Дата февраля
  
  Like «Р*»
  
  Текст, начинаю-щийся с буквы Р
  
  

• 17 слайд

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

• 18 слайд

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

• 19 слайд

  Пример 2.
  Создать запрос, который будет выводить список учеников, которые повысили свои отметки по истории или по информатике на конец года по сравнению со 2-ой четвертью.
  
  Особенностью данного запроса является то , что значение
  одного поля (Успеваемость.Год) сравнивается со значением
  другого поля (Успеваемость.2_чт).
  Порядок реализации запроса :
  => открыть окно Конструктора запросов и добавить в него таблицу УЧЕНИКИ и УСПЕВАЕМОСТЬ;
  => в столбцы бланка запроса поместить поля Класс, Номер_уч, Фамилия, Предмет, 2_чт, Год;
  => для поля Предмет в строке Условие отбора ввести условие «История», а для поля Год условие > [2_чт]. В строке ниже (строка «или») для поля Предмет ввести условие «Информатика», а для поля Год условие > [2_чт]. В случае сравнения значения полей друг с другом, необходимо значение поля записывать в квадратных скобках.
  
  

• 20 слайд

  Итоговые запросы
  Запросы, выполняющие вычисления в группах записей, называются итоговыми запросами.
  Такими вычислениями могут быть:
  суммирование значений некоторого числового поля (Sum),
  нахождение наибольшего или наименьшего значения (Max, Min)
  вычисление среднего (Avg),
  подсчет количества каких-либо конкретных значений (Count) и др.
  

• 21 слайд

  Итоговые запросы
  Пример 3.
  Подсчитать количество отличников по информатике для каждого класса школы по итогам года.
  Для выполнения этого задания в Конструкторе запросов требуется:
  в строке Групповая операция выбрать операцию Count (считать) сгруппировать записи из таблицы УСПЕВАЕМОСТЬ по классам
   Выполнить команду Вид —> Групповая операция для добавления в бланк запроса строки Групповая операция
  отобрать записи, относящиеся к предмету «информатика»,
  среди них выбрать лишь те, в которых годовая оценка — «5»
  в таблице Конструктора запросов в сроке Условия отбора в столбце Предмет поставить «информатика», а в столбце Год -5
  подсчитать число таких записей
   во втором столбце Год
  
  

• 22 слайд

  Домашнее задание .
  
  Знать основные параметры команды запроса на выборку и правила записи сложных логических условий отбора данных
  2. Оформить в табличном виде запросы на выборку к БД «Приемная комиссия»