Языки запросов Запрос к БД формирует критерий

Зарегистрируйтесь, чтобы просмотреть полный документ!

Языки запросов Запрос к БД формирует: • критерий отбора интересующих пользователя данных; • способ преобразования отобранных данных к виду, удобному для восприятия. Существуют два стандарта языка запроса к БД: • язык запросов по образцу QBE – Query By Example; • язык структурированных запросов SQL – Structured Query Language. Языки запросов позволяют: • осуществлять выборку из БД; • создавать новые таблицы БД; • удалять, добавлять и изменять хранящиеся в таблицах данные. 1

Язык QBE Ориентирован на неквалифицированного пользователя. Предназначен для создания заранее не запланированных (ad hoc) запросов. Это язык визуального представления операции отбора или модификации данных. При формировании запроса на QBE пользователь должен указать: • • • источник данных запроса столбцы таблицы – результата запроса; условие отбора данных; требуется ли сортировка данных и, если да, то по каким полям; требуется ли группировка данных и, если да, то по каким полям; 2

Язык SQL Является языком профессионалов. Это командный язык. Позволяет формировать сложные и вложенные запросы. Имеет некоторые дополнительные возможности по сравнению с QBE. Официальный стандарт языка ANSI/ISO опубликован в 1986 году. Авторы: Американский институт национальных стандартов (American National Standarts Institute — ANSI); Международная организация по стандартам (International Standard Organization ISO). Изменения и расширения: в 1992, 1999, 2003, 2010 гг. 3

Набор команд языка SQL: • • • обработки данных, определения данных, управления доступом, управления транзакциями, команды программного SQL. 4

Команды обработки данных(DML): • Выборка (чтение) данных из БД – SELECT. • Добавление новых строк в БД – INSERT. • Удаление строк из БД – DELETE. • Обновление данных – UPDATE. Команды управления транзакциями: • COMMIT – завершение транзакции • ROLLBACK – отмена транзакции • START TRANSACTION 1 – начало транзакции 1 – есть другие варианты (My. SQL Postgre. SQL Server, не поддерживается Oracle) 5

Команды определения данных (DDL): • Создание в БД новой таблицы – CREATE TABLE или нового представления – CREATE VIEW. • Удаление таблицы из БД – DROP TABLE* или представления — DROP VIEW*. • Изменение структуры существующей таблицы – ALTER TABLE*. • Создание индекса — CREATE INDEX* и его удаление — DROP INDEX. Команды управления данными (DCL): • GRANT - предоставляет пользователю права доступа • REVOKE - отменяет права доступа пользователя * Курсивом выделены команды, входящие в большинство диалектов, но не в стандарт SQL 6

Команды программного SQL • DECLARE – определение курсора для запроса • OPEN – открытие курсора для чтения результата запроса • CLOSE – закрытие курсора для чтения результата запроса • FETCH – считывания строки из результата запроса • EXPLAIN* - описывает план доступа к данным для запроса • PREPARE* - подготавливает оператор SQL к динамическому выполнению (анализ синтаксиса, проверка параметров, выбор плана) • EXECUTE* - обеспечивает динамическое выполнение оператора SQL(подстановка параметров, выполнение плана, возврат рез-та в приложение) • DESCRIBE* - предназначена для описания подготовленного запроса * Команды, входящие в большинство диалектов, но не в стандарт SQL 7

Программный SQL позволяет: • использовать операторы интерактивного SQL в тексте программы на языке программирования высокого уровня; • наряду с операторами интерактивного SQL использовать новые специальные конструкции, дополняющие SQL и увеличивающие его возможности; • для передачи параметров в запрос использовать в тексте запроса переменные, объявленные в программе; • для возврата в программу результатов запроса использовать специальные конструкции, отсутствующие в интерактивном SQL; • осуществлять компиляцию запросов совместно с программой, обеспечивая впоследствии согласованную работу программы и СУБД. Заранее (на этапе компиляции) выполнять действия по анализу и оптимизации запросов, экономя время, затрачиваемое на этапе выполнения программы. 8

Варианты встраивания запросов на SQL в программу: • статический SQL (запрос определяется на стадии написания программы, в программу вставляется текст запроса), • динамический SQL (позволяет формировать запрос к базе данных во время работы программы, реагируя на те или иные произошедшие события), • метод, основанный на различных интерфейсах программирования приложений ( API ) 9

Схема компиляции программы с встроенными инструкциями статического SQL 10

Схема выполнения программы с встроенными инструкциями статического SQL 11

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

Схема выполнения программы со встроенными инструкциями динамического SQL 13

Интерфейсы программирования приложений (API) API представляют собой библиотеки функций, разработанные для связи прикладной программы с СУБД посредством выполнения SQL-запросов. Схема работы приложения совместно с SQL : • программа получает доступ к базе данных путем вызова одной или нескольких API-функций, подключающих программу к СУБД и к конкретной базе данных; • для пересылки инструкций SQL в СУБД программа формирует инструкцию в виде текстовой строки и затем передает эту строку в качестве параметра при вызове API-функции; • программа вызывает выполнение API-функции для проверки состояния переданной в СУБД инструкции и обработки ошибок; • если инструкция SQL представляет собой запрос на выборку, то, вызывая API-функции, программа записывает результаты запроса в свои переменные; обычно за один вызов возвращается одна строка или столбец данных; • свое обращение к базе данных программа заканчивает вызовом APIфункции, отключающей ее от СУБД. 14

Соглашения, используемые при описании синтаксиса SQL Соглашение Толкование ПРОПИСНЫЕ БУКВЫ Используются для написания ключевых и зарезервированных слов языка Курсив Текст, написанный курсивом, заменяет имена переменных и элементов приложения, которые должны быть заданы пользователем. Угловые скобки <> Текст внутри угловых скобок характеризует элемент, который должен быть задан пользователем Квадратные скобки[] Фигурные скобки {} Многоточие … Заключают в себе необязательные элементы. Если таких элементов несколько, они отделяются друг от друга знаком «|» . Из нескольких элементов при формировании команды можно выбрать один или ни одного. Заключают в себе несколько элементов, разделенных символом «|» , из которых при формировании запроса следует выбрать один. Показывает возможность многократного повторения элемента, после которого стоит. Если в описании синтаксиса вместе с многоточием указана запятая, ее следует вводить между элементами при их перечислении. 15

Синтаксис команды SELECT СУБД Access SELECT [ALL| DISTINCTROW| TOP <число> [, PERCENT]]<спецификация выбора столбцов> FROM <список таблиц и/или запросов> [WHERE<спецификация выбора строк>] [GROUP BY <спецификация группировки >] [HAVING <спецификация выбора групп >] [UNION [ALL]<инструкция выбора>] [ORDER BY<спецификация сортировки>] [IN <”имя БД-источника”>[<строка подключения>]] [WITH OWNERACCESS OPTION]; 16

Примеры простейших запросов. Опция FROM Вывести содержимое таблицы Dis_Gr базы данных (БД) Teach, имеющей схему Dis_Gr (Pr, FAK, DIS, GR). Запрос 1 SELECT Pr, Fak, Dis, Gr FROM Dis_Gr; Запрос 2 SELECT * FROM Dis_Gr; 17

Результат запроса 1 или 2 (таблица DIS_GR) ФИО Малышев С. В. Факультет ФКТИ Дисциплина БДи. БЗ Группа 5341 Матвеев Л. Б. ФКТИ ОП 2351 Сидоров С. С. ФКТИ ОП 3456 Иванов Н. П. ФКТИ ОСРВ 2351 Никитин Е. В. ФКТИ ТУ 2351 Соколова Н. Н. ФКТИ ТУ 2352 Сидоров С. С. ФКТИ ТУ 3456 Андреева А. И. ФКЭА ОП 5531 Сидоров С. С. ФКЭА ТУ 2345 Никитин Е. В. ЭФФ ОП 3421 Наумов С. А. ЭФФ ОП 3422 Васильев С. Л. ЭФФ ОП 4441 18

Содержимое таблицы Prep базы данных Teach ФИО Категория Нагрузка Контракт Андреева А. И. ассистент 880 05. 01. 1996 Васильев С. Л. профессор 580 17. 05. 1997 Иванов Н. П. профессор 620 20. 01. 1996 Малышев С. В. асс. к. н. 780 25. 03. 1998 Матвеев Л. Б. доцент 790 09. 01. 1996 Наумов С. А. асс. к. н. 760 12. 01. 2002 Никитин Е. В. доцент 650 02. 01. 1999 Сидоров С. С. профессор 95 01. 03. 1997 Соколова Н. Н. доцент 720 31. 08. 2001 Фомичева А. В. ассистент 770 01. 05. 2000 Фомичева Т. Г. доцент 750 01. 09. 1999 19

Опция WHERE Критерий отбора может содержать: • Элементарные логические условия, представляющие собой операции сравнения (>, >=, <, <=, =, <>), • Предикаты : • Between <н. г> AND <в. г>, • Like “<шаблон>”, • IN (<список>) • Сложные логические условия , в которых элементарные условия объединяются логическими операциями AND, OR и NOT, XOR (исключающее или), EQV (эквивалентность), и IMP (импликация). 20

Результаты выполнения операций XOR, EQV и IMP XOR да нет EQV да нет IMP да нет да да да нет нет да да 21

Примеры простейших запросов. Опция WHERE Какие дисциплины и в каких группах и ведет преподаватель Сидоров С. С. ? Запрос 3 SELECT Pr, Dis, Gr FROM Dis_Gr WHERE Pr = ”Сидоров С. С. ”; Кто кроме Сидорова С. С. ведет дисциплину ОП? Запрос 4 SELECT Pr FROM Dis_Gr WHERE Pr <> ”Сидоров С. С. ” AND Dis=”ОП”; 22

Пример запроса с параметром В каких группах преподаватель. ? и какие дисциплины ведет заданный Запрос 5 SELECT Dis_Gr. PR, Dis_Gr. DIS, Dis_Gr. GR FROM Dis_Gr WHERE Dis_Gr. PR = [ФИО преподавателя? ]; Результат запроса 3 и запроса 5 при значении параметра «Сидоров С. С. » ФИО Сидоров С. С. Дисциплина ОП ТУ ТУ Группа 3456 2345 3456 Результат запроса 5 при значении параметра «Никитин Е. В. » ФИО Дисциплина Никитин Е. В. ОП Группа 3421 Никитин Е. В. ТУ 3512 23

Пример запроса с параметром. Продолжение Поле может участвовать в условии отбора, но при этом не фигурировать в результате запроса. Например, не обязательно выводить фамилию и инициалы преподавателя, если они введены, как значения параметра: Запрос 6 SELECT Dis_Gr. DIS, Dis_Gr. GR FROM Dis_Gr WHERE Dis_Gr. PR = [ФИО преподавателя? ]; Результат запроса 6 при значении параметра «Никитин Е. В. » Дисциплина ОП ТУ Группа 3421 2351 24

Запрос с параметром, значение которого формируется в экранной форме Ссылка в условии отбора на значение такого параметра представляет собой конструкцию вида: [FORMS]![<имя формы>]![<имя элемента управления формы>] Пусть фамилия и инициалы интересующего нас преподавателя выбираются из списка с именем ФИО, расположенного на экранной форме «Преподаватель» . Запрос 7 SELECT Dis_Gr. DIS, Dis_Gr. GR FROM Dis_Gr WHERE Dis_Gr. PR = [Forms]![Преподаватель]![ФИО]; Ссылка из запроса на элемент управления в подчиненной форме представляет собой конструкцию: [FORMS] ! [<имя формы>] ! [<имя подчиненной формы>]. [FORM] ! [<имя элемента управления подчиненной формы>] 25

Извлечение информации из нескольких связанных таблиц Вывести список преподавателей с указанием их нагрузки, группы, в которой они работают и преподаваемой дисциплины. Запрос 8 SELECT Prep. FIO, Prep. NAGR, DIS_GR. DIS FROM Prep, DIS_GR WHERE Prep. FIO=Dis_Gr. PR; Если не указать условие объединения таблиц, то запрос выполнится, но будет использовано декартово произведение перечисленных после FROM таблиц 26

Результат запроса 8 ФИО Малышев С. В. Васильев С. Л. Сидоров С. С. Наумов С. А. Матвеев Л. Б. Андреева А. И. Никитин Е. В. Иванов Н. П. Сидоров С. С. Соколова Н. Н. Никитин Е. В. Нагрузка 780 580 95 760 790 880 650 620 95 95 720 650 Группа 5341 4441 3456 3422 2351 5531 3421 2351 2345 3456 2352 2351 Дисциплина БДи. БЗ ОП ОП ОП ОСРВ ТУ ТУ 27

Извлечение информации из нескольких связанных таблиц. Продолжение Вывести список преподавателей, которые ведут дисциплину ОП и имеют нагрузку не более 700 часов. Запрос 9 SELECT Prep. FIO, Dis_Gr. Dis, Prep. Nagr FROM Prep, Dis_Gr WHERE Prep. FIO= Dis_Gr. Pr AND (Dis_Gr. Dis=”ОП” AND Prep. Nagr <=700); Результат запроса 9 ФИО Никитин Е. В. Васильев С. Л. Сидоров С. С. Дисциплина ОП ОП ОП Нагрузка 650 580 95 28

Спецификация выбора столбцов Это список, в котором можно использовать: • поля таблиц; • текстовые константы; • арифметические выражения, в т. ч. содержащие функции. Синтаксис спецификации выбора столбцов : [ + | - ] { функция | (выражение) | литерал | имя_поля } [{ + | - | * | / | | ^ | MOD | & } { функция | (выражение) | литерал | имя_поля}] … Операндами выражения могут быть: • поля таблиц и запросов, указанных в списке предложения FROM; • литералы (числовые, буквенно-цифровые константы, константы типа «дата/время» ; • функции, встроенные в Access и/ или написанные пользователем. 29

Примеры операндов выражения AVG (Nagr) – функция, вычисляющая среднюю нагрузку преподавателей по данным таблицы Prep; Year(Contract) – функция, вычисляющая год заключения контракта по дате, содержащейся в поле Contract таблицы Prep; Year(#01. 04. 2011#) – возвращает значение 2011; Month(Date()) или Month(Now()) – функция, определяющая месяц по текущей дате. 30

Примеры выражений Пример 1. Если в таблице есть поля «Цена» и «Количество» товара, а результатом запроса должна быть его стоимость, следует задать выражение (Цена * Количество). Пример 2. Если таблица содержит фамилии, имена и отчества людей, помещенные в три поля: Фамилия, Имя и Отчество. Фамилия Иванов … Петров Имя Иван … Петр Отчество Иванович … Петрович Требуется вывести фамилию и инициалы. Следует задать выражение: [Фамилия] & “ ” & Left([Имя]; 1) & “. ” & Left([Отчество]; 1) & “. ” Строка таблицы «Иванович» будет преобразована в строку «Иванов И. И. » . 31

Пример использования в запросе текстовой константы и выражения Таблица TEST ФИО Оклад Премия Иванов И. И. 5000 2000 Петров П. П. 6000 1000 Сидоров С. С. 3000 1000 Михайлов М. М. 12000 Запрос SELECT ФИО, “Оклад+Премия”, Оклад+Премия FROM TEST; Результат запроса ФИО Иванов И. И. Петров П. П. Сидоров С. С. Михайлов М. М. Выражение 1 Выражение 2 Оклад+Премия 7000 Оклад+Премия 4000 Оклад+Премия 14000 32

Пример задания альтернативных имён столбцов Запрос SELECT ФИО AS Работник, “Оклад+Премия” AS Выплаты, Оклад+Премия AS [К выдаче] FROM TEST; Результат запроса Работник Иванов И. И. Петров П. П. Сидоров С. С. Михайлов М. М. Выплаты К выдаче Оклад+Премия 7000 Оклад+Премия 4000 Оклад+Премия 14000 33

Скачать презентацию Языки запросов Запрос к БД формирует критерий

Базы данных_Лекц9 -13.pptx

Количество слайдов: 33