Примерная тематика НИРС по теме
Использование баз данных в социальной работе.
Занятие № 14
Тема: «СУБД.Создание запросов с использованием SQL».
2. Форма организации учебного процесса: практическое.
3. Значение темы (актуальность изучаемой проблемы): изучение принципов создания запросов с использованием SQL поможет будущему социальному работнику понимать принципы работы баз данных и основанных на них информационных систем, а также позволит разрабатывать базы данных и запросы для проведения научных исследований в своей учебной и профессиональной деятельности.
4. Цели обучения:
- общая:
обучающийся должен понимать сущность и значение информации в развитии современного информационного общества, сознавать опасности и угрозы, возникающие в этом процессе, соблюдать основные требования информационной безопасности, в том числе защиты государственной тайны (ОК-11);
владеть основными методами, способами и средствами получения, хранения, переработки информации, иметь навыки работы с компьютером как средством управления информацией (ОК-12);
- учебная:
знать способы хранения информации в медицинских и биологических системах, основные подходы к формализации и структуризации различных типов медицинских данных, используемых для формирования решений в ходе лечебно-диагностического процесса;
уметь использовать базы данных по социальной работе, использовать языки программирования;
владеть базовыми технологиями преобразования информации.
5. План изучения темы:
5.1 Контроль исходного уровня знаний:
-тест на сайте дистанционного обучения cdo.krasgmu.ru.
Основные понятия и положения темы
Access SQL. Основные понятия, лексика и синтаксис
Для извлечения данных из базы данных используется язык SQL (Structured Query Language — язык структурированных запросов). SQL — это язык программирования, который очень напоминает английский, но предназначен для программ управления базами данных. Каждый выполняемый запрос на самом деле основан на SQL.
Понимание принципов работы SQL помогает создавать улучшенные запросы и упрощает исправление запросов, которые возвращают неправильные результаты.
Что такое SQL?
SQL — это язык программирования, предназначенный для работы с наборами фактов и отношениями между ними. В программах управления реляционными базами данных, таких как Microsoft Office Access, язык SQL используется для работы с данными. В отличие от многих языков программирования SQL удобочитаем и понятен даже новичкам. Как и многие языки программирования, SQL является международным стандартом, признанным такими комитетами по стандартизации, как ISO(на английском языке) и ANSI(на английском языке).
“SQL удобочитаем и понятен даже новичкам.”
На языке SQL описываются наборы данных, помогающие получить ответы на вопросы. При использовании SQL необходимо применять правильный синтаксис. Синтаксис — это набор правил, позволяющих правильно сочетать элементы языка. Синтаксис SQL основан на синтаксисе английского языка и включает много таких же элементов, как и синтаксис языка Visual Basic для приложений (VBA) (VBA (Visual Basic for Applications). Версия макроязыка программирования Microsoft Visual Basic, используемая для программирования приложений для Microsoft Windows и поставляемая с некоторыми программами корпорации Майкрософт.).
Например, простая инструкция SQL, извлекающая список фамилий контактов с именем Мария, может выглядеть следующим образом:
SELECT Фамилия
FROM Контакты
WHERE Имя = 'Мария';
Язык SQL используется не только для выполнения операций над данными, но ещё и для создания и изменения структуры объектов базы данных, например таблиц. Та часть SQL, которая используется для создания и изменения объектов базы данных, называется языком описания данных DDL. Язык DDL не рассматривается в этом разделе. Дополнительные сведения см. в статье Создание и изменение таблиц и индексов с использованием запросов определения данных.
Инструкции SELECT
Инструкция SELECT служит для описания набора данных на языке SQL. Она содержит полное описание набора данных, которые необходимо получить из базы данных, включая следующее:
· таблицы, в которых содержатся данные;
· связи между данными из разных источников;
· поля или вычисления, на основе которых отбираются данные;
· условия отбора, которым должны соответствовать данные, включаемые в результат запроса;
· необходимость и способ сортировки.
Предложения SQL
Инструкция SQL состоит из нескольких частей, называемых предложениями. Каждое предложение в инструкции SQL имеет свое назначение. Некоторые предложения являются обязательными. В приведенной ниже таблице указаны предложения SQL, используемые чаще всего.
| Предложение SQL | Описание | Обязательное? |
| SELECT | Определяет поля, которые содержат нужные данные. | Да |
| FROM | Определяет таблицы, которые содержат поля, указанные в предложении SELECT. | Да |
| WHERE | Определяет условия отбора полей, которым должны соответствовать все записи, включаемые в результаты. | Нет |
| ORDER BY | Определяет порядок сортировки результатов. | Нет |
| GROUP BY | В инструкции SQL, которая содержит статистические функции, определяет поля, для которых в предложении SELECT не вычисляется сводное значение. | Только при наличии таких полей |
| HAVING | В инструкции SQL, которая содержит статистические функции, определяет условия, применяемые к полям, для которых в предложении SELECT вычисляется сводное значение. | Нет |
Термины SQL
Каждое предложение SQL состоит из терминов, которые можно сравнить с частями речи. В приведенной ниже таблице указаны типы терминов SQL.
| Термин SQL | Сопоставимая часть речи | Определение | Пример |
| идентификатор | существительное | Имя, используемое для идентификации объекта базы данных, например имя поля. | Клиенты.[НомерТелефона] |
| оператор | глагол или наречие | Ключевое слово, которое представляет действие или изменяет его. | AS |
| константа | существительное | Значение, которое не изменяется, например число или NULL. | |
| выражение | прилагательное | Сочетание идентификаторов, операторов, констант и функций, предназначенное для вычисления одного значения. | >= Товары.[Цена] |
Основные предложения SQL: SELECT, FROM и WHERE
Общий формат инструкции SQL:
SELECT поле_1
FROM таблица_1
WHERE условие_1
Access игнорирует разрывы строк в инструкции SQL. Несмотря на это, каждое предложение рекомендуется начинать с новой строки, чтобы инструкцию SQL было удобно читать как тому, кто ее написал, так и всем остальным.
Каждая инструкция SELECT заканчивается точкой с запятой (;). Точка с запятой может стоять как в конце последнего предложения, так и на отдельной строке в конце инструкции SQL.
Пример в Access
В приведенном ниже примере показано, как в Access может выглядеть инструкция SQL для простого запроса на выборку.
Предложение SELECT
Предложение FROM
Предложение WHERE
Эту инструкцию SQL следует читать так: «Выбрать данные из полей "Адрес электронной почты“ и "Компания“ таблицы "Контакты“, а именно те записи, в которых поле "Город“ имеет значение "Сиэтл“».
Разберем пример по предложениям, чтобы понять, как работает синтаксис SQL.
Предложение SELECT
SELECT [Адрес электронной почты], Организация
Это предложение SELECT. Оно содержит оператор (SELECT), за которым следуют два идентификатора ([Адрес электронной почты] и Организация).
Если идентификатор содержит пробелы или специальные знаки (например, Адрес электронной почты), он должен быть заключен в прямоугольные скобки.
В предложении SELECT не нужно указывать таблицы, в которых содержатся поля, и нельзя задать условия отбора, которым должны соответствовать данные, включаемые в результаты.
В инструкции SELECT предложение SELECT всегда стоит перед предложением FROM.
Предложение FROM
FROM Контакты
Это предложение FROM. Оно содержит оператор (FROM), за которым следует идентификатор (Контакты).
В предложении FROM не указываются поля для выборки.
Предложение WHERE
WHERE Город="Ростов"
Это предложение WHERE. Оно содержит оператор (WHERE), за которым следует выражение (Город="Ростов").
В отличие от предложений SELECT и FROM предложение WHERE является необязательным элементом инструкции SELECT.
С помощью предложений SELECT, FROM и WHERE можно выполнять множество действий.
Сортировка результатов: предложение ORDER BY
Как и в Microsoft Office Excel, в Access можно сортировать результаты запроса в таблице. Используя предложение ORDER BY, в запросе также можно указать способ сортировки результатов при выполнении запроса. Если используется предложение ORDER BY, оно должно находиться в конце инструкции SQL.
Предложение ORDER BY содержит список полей, для которых нужно выполнить сортировку, в том же порядке, в котором будут применена сортировка.
Предположим, например, что результаты сначала нужно отсортировать по убыванию значения поля Организация, а затем, если присутствуют записи с одинаковым значением поля Организация, отсортировать их по возрастанию значения поля Адрес электронной почты. Предложение ORDER BY будет выглядеть следующим образом:
ORDER BY Организация DESC, [Адрес электронной почты]
По умолчанию в Access выполняется сортировка по возрастанию (A-Z, А-Я, от наименьшего к наибольшему). Чтобы вместо нее выполнить сортировку значений по убыванию, необходимо указать ключевое слово DESC.
Работа со сводными данными: предложения GROUP BY и HAVING
Иногда возникает необходимость работы со сводными данными, такими как итоговые продажи за месяц или самые дорогие товары на складе. Для этого в предложении SELECT к полю следует применить статистическую функцию (Статистические функции. Функции, такие как Sum, Count, Avg или Var, используемые для расчетов итоговых значений.). Например, если в результате выполнения запроса нужно получить количество адресов электронной почты каждой компании, предложение SELECT может выглядеть следующим образом:
SELECT COUNT([Адрес электронной почты]), Организация
Возможность употребления той или иной статистической функции зависит от типа данных в поле или используемого выражения.
Задание полей, которые не используются в статистической функции: предложение GROUP BY
При использовании статистических функций обычно необходимо создать предложение GROUP BY. В предложении GROUP BY указываются все поля, к которым не применяется статистическая функция. Если статистические функции применяются ко всем полям в запросе, предложение GROUP BY создавать не нужно.
Предложение GROUP BY должно следовать сразу же за предложением WHERE или FROM, если предложение WHERE отсутствует. В предложении GROUP BY поля указываются в том же порядке, что и в предложении SELECT.
Продолжим предыдущий пример. Пусть в предложении SELECT статистическая функция применяется только к полю [Адрес электронной почты], тогда предложение GROUP BY будет выглядеть следующим образом:
GROUP BY Организация
Ограничение статистических значений с помощью условий группировки: предложение HAVING
Если необходимо указать условия для ограничения результатов, но поле, к которому их требуется применить, используется в статистической функции, предложение WHERE использовать нельзя. Вместо него следует использовать предложение HAVING. Предложение HAVING работает так же, как и WHERE, но используется для статистических данных.
Предположим, например, что к первому полю в предложении SELECT применяется функция AVG (которая вычисляет среднее значение):
SELECT COUNT([Адрес электронной почты]), Организация
Чтобы ограничить результаты запроса на основе значения функции COUNT, к этому полю нельзя применить условие отбора в предложении WHERE. Вместо него условие следует поместить в предложение HAVING. Например, если нужно, чтобы запрос возвращал строки только в том случае, если у компании имеется несколько адресов электронной почты, можно использовать следующее предложение HAVING:
HAVING COUNT([Адрес электронной почты])>1
Запрос может включать и предложение WHERE, и предложение HAVING, при этом условия отбора для полей, которые не используются в статистических функциях, указываются в предложении WHERE, а условия для полей, которые используются в статистических функциях, — в предложении HAVING.
Объединение результатов запроса: оператор UNION
Оператор UNION используется для одновременного просмотра всех данных, возвращаемых несколькими сходными запросами на выборку, в виде объединенного набора.
Оператор UNION позволяет объединить две инструкции SELECT в одну. Объединяемые инструкции SELECT должны иметь одинаковое число и порядок выходных полей с такими же или совместимыми типами данных. При выполнении запроса данные из каждого набора соответствующих полей объединяются в одно выходное поле, поэтому выходные данные запроса имеют столько же полей, сколько и каждая инструкция SELECT по отдельности.
В запросах на объединение числовой и текстовый типы данных являются совместимыми.
Используя оператор UNION, можно указать, должны ли в результаты запроса включаться повторяющиеся строки, если таковые имеются. Для этого следует использовать ключевое слово ALL.
Запрос на объединение двух инструкций SELECT имеет следующий базовый синтаксис:
SELECT поле_1
FROM таблица_1
UNION [ALL]
SELECT поле_a
FROM таблица_a
;
Предположим, например, что есть две таблицы, которые называются Продукты (товары) и Services (услуги). Обе таблицы содержат поля с названием товара или услуги, ценой и сведениями о гарантии, а также поле, в котором указывается эксклюзивность предлагаемого товара или услуги. Несмотря на то, что в таблицах Продукты и Services предусмотрены разные типы гарантий, основная информация одна и та же (предоставляется ли на отдельные продукты или услуги гарантия качества). Для объединения четырех полей из двух таблиц можно использовать следующий запрос на объединение:
SELECT имя, цена, доступна_гарантия, эксклюзивное_предложение
FROM Продукты
UNION ALL
SELECT имя, цена, наличие_гарантии, эксклюзивное_предложение
FROM SУслуги
;
5.3. Самостоятельная работа по теме:
Студент выполняет задание в соответствии с руководством «Создание запросов на языке SQL», расположенным на сайте дистанционного обучения cdo.krasgmu.ru.
5.4 Итоговый контроль знаний:
- ответы на вопросы по теме занятия;
- решение ситуационных задач;
- тестовых заданий по теме.
Вопросы по теме занятия:
1. Что такое SQL?
2. Для чего используется SQL?
3. Для чего служит инструкция SELECT?
4. Какие предложения SQL вы знаете? Для чего они предназначены?
Тестовые задания по теме занятия:
1. С ПОМОЩЬЮ ВАЛИДНОГО DELETE ВЫРАЖЕНИЯ МОЖЕТ БЫТЬ УДАЛЕНО СТРОК ИЗ ТАБЛИЦЫ (ВЫБЕРИТЕ НАИБОЛЕЕ ТОЧНЫЙ ВАРИАНТ ОТВЕТА)…
1) 0
2) 1
3) 0 и более
4) 1 и более
5) все строки
2. ПРЕДЛОЖЕНИЕ, КОТОРОЕ ОПРЕДЕЛЯЕТ ПОЛЯ, КОТОРЫЕ СОДЕРЖАТ НУЖНЫЕ ДАННЫЕ – ЭТО
1) SELECT
2) WHERE
3) ORDER BY
4) FROM
3. ПРЕДЛОЖЕНИЕ, КОТОРОЕ ОПРЕДЕЛЯЕТ ТАБЛИЦЫ, КОТОРЫЕ СОДЕРЖАТ ПОЛЯ, УКАЗАННЫЕ В ПРЕДЛОЖЕНИИ SELECT.– ЭТО
1) SELECT
2) WHERE
3) ORDER BY
4) FROM
4. ПРЕДЛОЖЕНИЕ, КОТОРОЕ ОПРЕДЕЛЯЕТ УСЛОВИЯ ОТБОРА ПОЛЕЙ, КОТОРЫМ ДОЛЖНЫ СООТВЕТСТВОВАТЬ ВСЕ ЗАПИСИ, ВКЛЮЧАЕМЫЕ В РЕЗУЛЬТАТЫ.– ЭТО
1) SELECT
2) WHERE
3) ORDER BY
4) FROM
5. ПРЕДЛОЖЕНИЕ, КОТОРОЕ ОПРЕДЕЛЯЕТ ПОРЯДОК СОРТИРОВКИ РЕЗУЛЬТАТОВ.– ЭТО
1) SELECT
2) WHERE
3) ORDER BY
4) FROM
Ситуационные задачи по теме с эталонами ответов.
Задача № 1
Вы – администратор базы данных в службе медико-социальной экспертизы. В базе данных имеется более 10 000 записей о пациентах. Вы получили задание составить список всех людей, нуждающихся в санаторно-курортном лечении.
1) Какими средствами вы воспользуетесь?
2) Какую инструкцию вы будете использовать?
Задача № 2
Вы – администратор базы данных в фонде социального страхования. В базе данных имеется более 1 млн. записей о пациентах.
1) Какую инструкцию Вы будете использовать, чтобы выбрать всех людей по фамилии «Иванов»?
2) Какую инструкцию Вы будете использовать, чтобы «почистить» базу данных: исключить сведения об умерших людях?
6. Домашнее задание для уяснения темы занятия: учебно-методические разработки следующего занятия.