Запросы к базе данных Определение Назначение

Запросы к базе данных • Определение • Назначение • Типы, виды • Создание Система КЗТО «КАКОВ ЗАПРОС, ТАКОВ ОТВЕТ» 1

ОПРЕДЕЛЕНИЕ ЗАПРОС (query) – это средство ЗАПРОС выбора необходимой информации из базы данных. Вопрос, сформированный по отношению к базе данных, и есть запрос. 2

ОПРЕДЕЛЕНИЕ ЗАПРОС – объект БД, который позволяет проводить основные операции по обработке данных – сортировку, фильтрацию, объединение данных из разных источников – и сохранять результаты с некоторым именем, чтобы в дальнейшем применять эти операции по мере необходимости. 3

ОПРЕДЕЛЕНИЕ ЗАПРОС – это обращение к БД для поиска или изменения в базе данных информации, соответствующей заданным критериям, некоторый набор данных, полученный в результате обращения к БД 4

ОПРЕДЕЛЕНИЕ • Результат запроса – новая таблица, Результат запроса которая существует до закрытия запроса. • Структура запроса определяется Структура запроса полями, выбранными из одной или нескольких взаимосвязанных таблиц БД. Записи формируются путем объединения записей таблиц, участвующих в запросе. 5

Типы запросов (2 типа): I • QBE - запрос по образцу – QBE - запрос по образцу средство для отыскания необходимой информации в базе данных. Формируется путем заполнения бланка запроса в окне Конструктора запросов. 6

Типы запросов (2 типа): II • SQL – запросы – это запросы, которые составляются (программистами) из последовательности SQL – инструкций. Эти инструкции задают, что надо сделать с входным набором данных для генерации выходного набора. 7

Все запросы Access строит на основе SQL – запросов, чтобы посмотреть их, необходимо перейти в Режим – SQL Режим SQL. 8

ВИДЫ ЗАПРОСОВ • запрос-выбор (или простой запрос); • запрос-действие; • параметрический запрос; • перекрестный запрос; • запрос SQL. 9

10

СОЗДАНИЕ 1 11

Простой запрос - создает простой запрос из Простой запрос определенных полей. Перекрестный запрос - создает запрос, данные Перекрестный запрос в котором имеют компактный формат, подобный формату сводных таблиц в Excel. Повторяющиеся записи — создает запрос, Повторяющиеся записи выбирающий повторяющие записи из таблицы или простого запроса. Записи без подчиненных - создает запрос, Записи без подчиненных выбирающий из таблицы записи, не связанные с записями из другой таблицы. 12

13

14

15

16

Студенты не записавшиеся на факультатив 17

С помощью КОНСТРУКТОРА можно создать следующие виды запросов: • Простой • По условию • Параметрические • Итоговые • С вычисляемыми полями • SQL - запрос 18

СОЗДАНИЕ 2 19

Окно Конструктора состоит из двух частей – верхней и нижней. • В верхней части окна размещается схема данных запроса, которая содержит список таблиц – источников и отражает связь между ними. • В нижней части окна находится Бланк построения запроса QBE (Query by Example), в котором каждая строка выполняет определенную функцию: 20

Схема данных запроса Бланк 21

Бланк · Поле – указывает имена полей, которые участвуют в запросе · Имя таблицы – имя таблицы, с которой выбрано это поле · Сортировка – указывает тип сортировки · Вывод на экран – устанавливает флажок просмотра поля на экране · Условия отбора - задаются критерии поиска · Или – задаются дополнительные критерии отбора 22

Простой запрос 23

Запрос на выборку условия 24

Используются квадратные скобки Параметрический запрос 25

Итоговый запрос 26

Итоговые запросы значительно отличаются от обычных. В них поля делятся на 2 типа: -поля, по которым осуществляется группировка данных; -поля, для которых проводятся вычисления. Для составления итогового запроса, находясь в режиме конструктора, следует нажать кнопку Итоги 27

Строка Групповая операция в бланке QBE Агрегатные функции: SUМ - вычисляет сумму всех значений заданного поля (для числовых или денежных полей), отобранных запросом; AVG - вычисляет среднее значение в тех записях AVG определенного поля, которые отобраны запросом ( для числовых или денежных полей); MIN - выбирает минимальное значение в MIN записях определенного поля, отобранных запросом; 28

Агрегатные функции: MAX — выбирает максимальное значение в записях определенного поля, отобранных запросом; COUNT — вычисляет количество записей, COUNT отобранных запросом в определенном поле, в которых значения данного поля отличны от нуля; FIRST - определяет первое значение в указанном FIRST поле записей; LAST -. определяет последнее значение в LAST указанном поле записей. 29

Запрос с вычисляемыми полями 30

Вычисляемые поля Можно задать вычисления над любыми полями таблицы и сделать вычисляемое значение новым полем в запросе. Для этого в строке Поле бланка QBE вводится формула для вычисления, причем имена полей заключаются в квадратные скобки. Например: =[ Оклад]*0. 15 31

Вычисляемые поля - Операторы: • + Сложение, - Минус, * Умножение, / Деление, ^ Возведение в степень; • Соединение частей текста при помощи знака &, например: =[ Фамилия] & “ “&[Имя] Стоимость: - название нового поля 32

Результаты выполнения запроса или данные таблиц можно представить в виде диаграмм и графиков 33

Обновление Перекрестный Выбор Объединение Создание таблицы К серверу Добавление Управление Удаление 34

четыре типа запросов-действий: Создание таблицы, обновление, удаление, добавление Создание таблицы. Этот запрос позволяет сохранить Создание таблицы набор данных, вышедших в ответ на запрос, в виде таблицы. Обновление. Используется для того, чтобы обновить Обновление значения некоторых полей для всех записей, вышедших в ответ на запрос. Удаление. Используется для удаления всех записей, Удаление вышедших в ответ на запрос. Добавление. Используется для добавления данных в Добавление имеющуюся таблицу. ACCESS отмечает запросы-действия специальными значками. 35

Создание таблицы 36

Запросы на создание таблицы Для сохранения результатов выборки в реально существующей таблице используются запросы на создание таблицы. Это ускоряет доступ к данным, полученным по запросу. Сохранение в виде отдельной таблицы полезно и в том случае, если какие-либо данные нужно хранить в течение длительного времени. Например, сводную информацию по годам необходимо хранить в течение 5 лет. После создания данные в новой таблице не связаны с данными источника БД. Ваша задача следить по необходимости за обновлением данных в таблице – вновь запускать запрос 37

Запрос добавление 38

Запросы на добавление При создании запроса на добавление проверяйте результат выборки, так как запрос на добавление данных отменить нельзя. Типы данных полей таблиц источника и приемника должны быть совместимыми. Существует функция Val(Текстовое поле) – преобразует текстовое поле в числовое. 39

Запрос удаление 40

41

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

Запрос обновление 43

Значки 44

Виды критериев Для создания запроса с несколькими критериями пользуются различными операторами. • Логическая операция ИЛИ - OR ИЛИ 4 or 5 • Логическая операция И - AND >2 and <5 45

Виды критериев • Оператор Between (диапазон) Between between 10 and 20 • Оператор In позволяет задавать используемый для сравнения список значений. Например: in (“первый”, ”второй”, ”третий”) 46

Виды критериев • Оператор Like полезен для поиска образцов в текстовых полях, с шаблонами и без них: * — обозначает любое количество ( включая нулевой) символов; ? — любой одиночный символ; # — указывает что в данной позиции должна быть цифра. Например: для выбора фамилии, начинающейся с буквы П и с окончанием “ов” можно записать like П*ов 47

Виды критериев • Функций для даты и времени Day(дата) — возвращает значение дня месяца в диапазоне от 1 до 31 Month(дата) — возвращает значение месяца года в диапазоне от 1 до 12 Year(дата) — возвращает значение года в диапазоне от 100 до 9999 48

Виды критериев • Сортировка данных в запросе • Операторы для даты и времени Можно ввести дату и время, при этом значения должны быть заключены между символами #. Например: #10 мая 1998# >#31. 12. 96# 49

Аномалии запросов • Аномалия удаления - удаляются записи, которые на прямую не связаны с удаляемым значением. • Аномалия вставки – возникает когда при вставки новой записи появляются пустые неопределяемые значения или появляются дублирующиеся записи. 50

Основы SQL: запросы к базе данных Что такое база данных SQL? Структурированный язык запросов (Structured Query Language) – стандарт коммуникации с базой данных, который поддержан ANSI. Самая последняя версия – SQL-99, хотя новый стандарт SQL-200 n уже находится в разработке. Большинство баз данных твердо придерживается стандарта ANSI-92. • Разработан для реляционных систем управления база данных (РСУБД) 51

SQL основан на реляционной алгебре и реляционных исчислений (исчисление кортежей) и не имеет операторов цикла. Операторы языка делятся на группы: 1 - операторы определения данных (Data Definition Language, DDL) • CREATE создает объект БД (саму базу, таблицу, представление, пользователя, индекс и т. д. ) • ALTER изменяет объект • DROP удаляет объект 52

2 – операторы манипулирования данных (операции реляционной алгебры) DML Есть четыре основных типа запросов данных в SQL, которые относятся к так называемому языку манипулирования данными (Data Manipulation Language или DML): • SELECT – выбрать строки из таблиц; • INSERT – добавить строки в таблицу; • UPDATE – изменить строки в таблице; • DELETE – удалить строки в таблице; 53

3 - операторы определения доступа к данным (Data Control Language, DCL) • GRANT предоставляет пользователю (группе) разрешения на определенные операции с объектом; • REVOKE отзывает ранее выданные разрешения; • DENY задает запрет, имеющий приоритет над разрешением; 54

4 - операторы управления транзакциями (Transaction Control Language, TCL) • COMMIT применяет транзакцию; • ROLLBACK откатывает все изменения, сделанные в контексте текущей транзакции; • SAVEPOINT делит транзакцию на более мелкие участки; 55

SQL представлен множеством инструкций, каждая из которых предписывает СУБД выполнить определенное действие: создать таблицу, извлечь данные, … У каждого объекта в БД есть уникальное имя и обращение к объекту идет по имени. <Имя_таблицы>. <Имя_поля> 56

Типы данных SQL q. Символьные типы данных - содержат буквы, цифры и специальные символы. • CHAR или CHAR(n) -символьные строки фиксированной длины. CHAR без параметра соответствует CHAR(1). Для хранения таких данных всегда отводится n байт вне зависимости от реальной длины. • VARCHAR(n) - символьная строка переменной длины. Для хранения отводится число байт, соответствующее реальной длине строки. 57

Типы данных SQL q. Целые типы данных - поддерживают только целые числа (арифметические операции и агрегирующие функции. • INTEGER или INT- целое, для хранения которого отводится, как правило, 4 байта. Интервал значений от - 2147483647 до + 2147483648 • SMALLINT - короткое целое (2 байта), интервал значений от - 32767 до +32768 58

Типы данных SQL q Вещественные типы данных - описывают числа с дробной частью. FLOAT и SMALLFLOAT - числа с плавающей точкой (для хранения отводится обычно 8 и 4 байта соответсвенно). • DECIMAL(p) - тип данных аналогичный FLOAT с числом значащих цифр p. • DECIMAL(p, n) - аналогично предыдущему, p - общее количество десятичных цифр, n - количество цифр после десятичной запятой. 59

Типы данных SQL q Денежные типы данных - описывают, естественно, денежные величины. Если в ваша система такого типа данных не поддерживает, то используйте DECIMAL(p, n). MONEY(p, n) - все аналогично типу DECIMAL(p, n). Вводится только потому, что некоторые СУБД предусматривают для него специальные методы форматирования. 60

Типы данных SQL q Дата и время - используются для хранения даты, времени и их комбинаций. • DATE - тип данных для хранения даты. • TIME - тип данных для хранения времени. • INTERVAL - тип данных для хранения временного интервала. • DATETIME - тип данных для хранения моментов времени (год + месяц + день + часы + минуты + секунды + доли секунд). 61

Типы данных SQL q Двоичные типы данных - позволяют хранить данные любого объема в двоичном коде (оцифрованные изображения, исполняемые файлы и т. д. ). Определения этих типов наиболее сильно различаются от системы к системе, часто используются ключевые слова: • BINARY • BYTE • BLOB 62

Типы данных SQL q Последовательные типы данных - используются для представления возрастающих числовых последовательностей. • SERIAL - тип данных на основе INTEGER, позволяющий сформировать уникальное значение (например, для первичного ключа). При добавлении записи СУБД автоматически присваивает полю данного типа значение, получаемое из возрастающей последовательности целых чисел. 63

Типы данных SQL q для всех типов данных имеется общее значение NULL - "не определено". Это значение имеет каждый элемент столбца до тех пор, пока в него не будут введены данные. При создании таблицы можно явно указать СУБД могут ли элементы того или иного столбца иметь значения NULL (это не допустимо, например, для столбца, являющего первичным ключом). 64

При описании команд предполагается, что: • текст, набранный строчными буквами (например, CREATE TABLE) является обязательным • текст, набранный прописными буквами и заключенный в угловые скобки (например, <имя_базы_данных>) обозначает переменную, вводимую пользователем • в квадратные скобки (например, [NOT NULL]) заключается необязательная часть команды • взаимоисключающие элементы команды разделяются вертикальной чертой (например, [UNIQUE | PRIMARY KEY]). 65

DDL: Операторы создания схемы базы данных. 66

DDL: Операторы создания таблицы 67

CREATE TABLE Друзья ([Код] integer, [Фамилия] text, [Имя] text, [Дата рождения] date, [Телефон] text, [Заметки] Memo, CONSTRAINT [Индекс1] PRIMARY KEY ([Код])); 68

DDL: Операторы удаление таблицы DROP TABLE Друзья; 69

DDL: Операторы модификация таблицы 70

ALTER TABLE Друзья ADD Адрес CHAR(50), Фото BINARY ; ALTER TABLE Друзья DROP Фото ; 71

DML: Команды модификации данных INSERT INTO table_name (column 1, column 2, column 3) VALUES (‘data 1’, ‘data 2’, ‘data 3’); 72

DML: Команды модификации данных DELETE FROM table_name WHERE column 1 = ‘data 1’; 73

DML: Команды модификации данных UPDATE table_name SET column 1 = ‘data 1’, column 2 = ‘data 2’ WHERE column 3 = ‘data 3’; 74

Запрос SELECT для выборки нужных данных Для извлечения записей из таблиц в SQL определен оператор SELECT. С помощью этой команды осуществляется не только операция реляционной алгебры "выборка" (горизонтальное подмножество), но и предварительное соединение (join) двух и более таблиц. Это наиболее сложное и мощное средство SQL, полный синтаксис оператора SELECT имеет вид: 75

Запрос SELECT для выборки нужных данных SELECT [ALL | DISTINCT] <список_выбора> FROM <имя_таблицы>, . . . [ WHERE <условие> ] [ GROUP BY <имя_столбца>, . . . ] [ HAVING <условие> ] [ORDER BY <имя_столбца> [ASC | DESC], . . . ] 76

Для того, чтобы получить все строки данных для специфических столбцов, используется запрос такого вида: SELECT column 1, column 2 FROM table_name; Также, можно получить все столбцы из таблицы, используя подстановочный знак «*» : SELECT * FROM table_name; 77

Следующий запрос возвратит все столбцы со всех строк, где «column 1» содержит значение « 3» : SELECT * FROM table_name WHERE column 1=3; 78

SELECT * FROM table_name WHERE ((Age >= 18) AND (Last. Name BETWEEN ‘Иванов’ AND ‘Сидоров’)) OR Company LIKE ‘Motorola’; Что в переводе на русский язык означает: выбрать все столбцы из таблицы table_name, где значение столбца age больше или равно 18, а также значение столбца Last. Name находится в алфавитном промежутке от Иванов до Сидоров включительно, или же значением столбца Company является Motorola. 79

AS псевдоним - Имя, которое станет заголовком столбца вместо исходного названия столбца в таблице 80

81

82

Предложение ORDER BY является необязательным. Однако оно необходимо для отображения данных в порядке сортировки. По умолчанию используется порядок сортировки по возрастанию (от "A" до "Я" и от 0 до 9). 83

84

85

86