Лекция 11. Запросы выборки данных (DRL). Запросы выборки: Основу запросов DRL составляет инструкция SELECT. SELECT [ALL|DISTINCT|DISTINCTROW|TOP] FROM. - презентация

1 Лекция 11. Запросы выборки данных (DRL). Запросы выборки: Основу запросов DRL составляет инструкция SELECT. SELECT [ALL|DISTINCT|DISTINCTROW|TOP] FROM [IN ] [ INNER|RIGHT|LEFT JOIN ON. =. ] [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY [ ]…]

2 Инструкция SELECT позволяет производить выборку и вычисления над данными из одной или нескольких таблиц. Результатом выполнения инструкции является ответная таблица. Простейшая ферма инструкции SELECT включает: фразы SELECT и FROM. Фраза SELECT определяет поля, подлежащие выводу в выходной набор, а фраза FROM определяет имена таблиц, включенных в запрос. Имена полей и таблиц отделяются запятыми, а предложение запроса заканчивается точкой с запятой. Если в запросе участвует несколько таблиц, то для исключения двусмысленности имена полей следует записывать в полной форме:. (например, Клиенты.Адрес) Для повышения эффективности вначале указываются меньшие по размеру таблицы. Если имена полей и таблиц включают пробелы, то их необходимо заключать в квадратные скобки. Список полей следует задавать в той последовательности, в которой они должны быть представлены в выходном наборе. Например: SELECT Фамилия, Имя, Отчество, [год рождения] FROM Клиенты; Для выбора всех полей применяется шаблон *. SELECT * FROM Клиенты;

3 Список полей или выражений может содержать имена полей, участвующих в запросе, выражения над столбцами, а также строковые константы, заключенные в кавычки. В выражениях (вычисляемых полях) могут принимать участие имена полей, знаки арифметических операций (+, -, *,/,^), множество встроенных функций, константы, круглые скобки и следующие операции: \ - возвращает целое от деления двух арифметических выражений (заранее округленных); MOD - возвращает остаток от деления двух арифметических выражений (заранее округленных); & - операция конкатенации; IF(условие, выражение1, выражение2) – если условие истинно, то возвращается результат выражения1, в противном случае - выражения2 (но в любом случае вычисляется как выражение 1, так и выражение2). Например: SELECT [Фамилия] & " " & [Имя] & " " & [Отчество] AS Полное_имя, "возраст", DateDiff(уууу, [Дата Рождения], Date()) AS Возраст FROM Клиенты;

4 Именам полей и таблиц можно назначать альтернативные имена (псевдонимы). Псевдонимы записываются через ключевое слово AS ( или AS ). В предыдущем примере псевдонимы использовались для задания имен вычисляемым полям. В противном случае имя вычисляемого поля имело бы вид: выражение1 и т. д. В большинстве случаев псевдонимы используются для сокращения набора длинных имен. Особенно это эффективно для замены длинных имен таблиц, поскольку в многотабличных запросах приходится включать имена таблиц в описание каждого поля. Псевдонимы также используются при создании рекурсивных запросов, когда приходится соединять записи из одной и той же таблицы. Тогда для различия копий таблиц им приходится назначать псевдонимы.

5 Если в запрос включены не все поля некоторой таблицы, то выходной набор может содержать одинаковые строки. Предикаты ALL, DISTINCT, DISTINCTROW (записываются сразу после оператора SELECT) служат для управления выводом повторяющихся строк. По умолчанию принимается предикат ALL, т. е. в ответную таблицу включаются все строки, в том числе и повторяющиеся. Предикат DISTINCT исключает записи, которые содержат повторяющиеся значения в выбранных полях. В выходной набор включаются только уникальные значения каждого из полей, находящихся в списке фразы SELECT. Если фраза SELECT содержит более одного поля, то для включения записи в результат выполнения запроса необходимо, чтобы совокупность значений во всех этих полях была уникальной. Предикат DISTINCTROW исключает полностью повторяющиеся записи и применяется для многотабличных запросов. Он игнорируется, если запрос содержит только одну таблицу или все поля всех таблиц. В Access может дополнительно применяться предикат ТОР, возвращающий определенное число записей, находящихся в начале или в конце диапазона, описанного с помощью фразы ORDER BY.

6 В одном запросе SQL позволяет обращаться к данным нескольких БД различных форматов. Такие запросы получили название гетерогенных. В разных СУБД синтаксис записи гетерогенных запросов несколько отличается. В Access для полного описания имени и типа таблицы применяется ключевое слово IN: имя таблицы IN "путь к файлу" "тип таблицы". Например: SELECT * FROM Клиенты IN "с:\dBase\Clients.dbf " dBASE ; В системах, ориентированных на доступ к данным через BDE (Borland Database Engine), для указания пути к таблице необходимо указать имя псевдонима базы (BDE Alias), заключенного в двоеточие с обеих сторон. Например: SELECT * FROM :BCDEMOS:Cients. Фраза WHERE не обязательна. Она задает условия, которым должны удовлетворять записи в результирующей таблице. Выражение является логическим. Его элементами могут быть имена полей, операции сравнения, >=, =, , арифметические операции, логические операторы (NOT, AND, OR, XOR), скобки, функции IN, BETWEEN,LIKE, IS(NOT) NULL и множество встроенных функций. Строки заключаются в кавычки, а константы типа Дата/Время - в символы #. =, =, , арифметические операции, логические операторы (NOT, AND, OR, XOR), скобки, функции IN, BETWEEN,LIKE, IS(NOT) NULL и множество встроенных функций. Строки заключаются в кавычки, а константы типа Дата/Время - в символы #.">

7 Функция IN проверяет на равенство любому значению из списка: поле1 IN ("Минск", "Москва", "Киев"). Функция BETWEEN задает диапазон значений. Границы диапазона разделяются оператором And: [поле2] BETWEEN 50 And 100 (эквивалентно выражению [поле2] =50). Функция LIKE проверяет на соответствие заданному шаблону символов. В качестве символов шаблона используются: * любое число произвольных символов; ? - один произвольный символ; # - одна произвольная цифра; [] - диапазон допустимых символов. К примеру, [А-Я], [3-9]. Если же необходимо исключить символы, то перед ними ставится !: [!А-Я]. Например, LIKE "А*" - любая строка, начинающаяся с А, LIKE "220###" задает условие для почтового индекса, начинающегося с 220, LIKE "####АА[А-Х]" - отбирает номера автомобилей серий ААА,..., ААХ.

8 Запрос может быть основан на нескольких таблицах. Простое включение полей из нескольких таблиц дает простой перебор всех их возможных значений. Для двух таблиц общее число записей будет равно произведению числа записей в первой и второй таблицах. Но так как реляционная база данных практически всегда состоит из таблиц, связанных между собой посредством совпадающих значений полей, участвующих в связи, то для правильного объединения данных необходимо включать в запрос явное определение соответствующих связей. Связь можно задать с помощью двух способов: с помощью оператора INNER|RIGHT|LEFT JOIN и с помощью дополнительного условия выборки во фразе WHERE. Причем в SQL объединение данных можно произвести даже по неравенству, т.е. поддерживаются операции сравнения -, ,, >=. Оператор INNER|RIGHT|LEFT JOIN не является обязательной частью инструкции SELECT и оформляется как часть фразы FROM: SELECT Товары.[Наименование товара], Заказы.Дата, Заказы.[Полная цена] FROM Товары INNER JOIN Заказы ОN Товары.Код_товара = Заказы.Код_товара,

9 Этот же запрос можно записать следующим образом (второй способ задания связи): SELECT Товар.[Наименование товара], Заказы.Дата, Заказы.[Полная цена] FROM Товары. Заказы WHERE Товары. Код _товара = Заказы.Код_товара; Для большинства многотабличных запросов набор записей формируется на основе совпадающих значений полей, участвующих в связи, т. е. с помощью естественного, иначе внутреннего, объединения (зквисоединение). Внутреннее соединение задается с помощью оператора INNER JOIN. Для двух таблиц, связанных отношением «один ко многим», из главной таблицы будут отобраны только те записи, которые имеют связанные записи в подчиненной таблице. Допустим, мы имеем две таблицы - «Клиенты» и «Заказы». С помощью внутреннего объединения получим сведения по клиентам, имеющим хотя бы один заказ. Если заказов у некоторых клиентов несколько, то сведения по ним повторятся столько раз, сколько было сделано заказов

10 Но предположим, что мы хотим получить информацию по всем клиентам и посмотреть, кто заказывал что-либо, а кто нет. Ответ на такой вопрос позволяют дать запросы с внешним объединением (RIGHT|LEFT [OUTER] JOIN). Тогда в запрос будут включены все записи из таблицы «Клиенты» и те записи из таблицы «Заказы», которые имеют связанные записи в главной таблице. Выбор LEFT либо RIGHT зависит от того, с какой стороны от слова JOIN находится та таблица, из которой необходимо отобрать все записи: SELECT Фамилии, Имя, [Дата заказа], Цена FROM Клиенты LEFT JOIN Заказы ОN Клиенты, код_клиента=Заказы.код_клиента;

11 Этот же запрос можно записать и с правым объединением: Клиенты RIGHT JOIN Заказы. При задании ссылочной целостности такой запрос не имеет большого смысла, так как результат ничем не будет отличаться от внутреннего соединения, поскольку записей в подчиненной таблице Заказы, не связанных с записями в главной таблице Клиенты, просто не может быть. Кроме соединения записей из нескольких таблиц, можно также провести рекурсивное соединений записей из одной и той же таблицы (используется, когда в таблице есть записи, которые ссылаются на другие записи этой же таблицы). Предположим, мы имеем таблицу о сотрудниках, где кроме полей «Фамилия», «Имя», «Должность» и т. д. есть поле 'Подчиняется", в котором записывается тот код сотрудника (первичный ключ данной таблицы, которому данный сотрудник подчиняется: SELECT Сотр.Должностъ, Сотр.Фамилия, Сотр.Имя, Сотр. 1.Должность AS Подчиняется FROM Сотрудннки AS Сотр. LEFT JOIN Сотрудники AS Сотр.1 ON Сотр.Подчиняется = Сотр.1.КодСотрудннка; (в этом запросе целесообразно выполнить внешнее левое объединение для отображения и тех сотрудников, которые не подчиняются никому другому).

12 Запросы с группировкой Иногда интерес представляет не каждая строка таблицы в отдельности, а итоговые значения по группам данных. Например, может понадобиться общая сумма продаж для клиентов, проживающих в определенном районе, или интересно знать средний объем продаж по месяцам, чтобы выяснить, тенденции сбыта. Получить ответы на такие вопросы можно с помощью итоговых запросов (запросов с группировкой). Фраза GROUP BY позволяет выделять группы в результирующем множестве записей. Группой являются записи с совпадающими значениями в полях, перечисленных во фразе GROUP BY.

13 GROUP BY. Оператор перегруппирует таблицу таким образом, чтобы в каждой группе все строки имели одно и то же значение поля. Фраза SELECT затем применяется уже к группам перекомпонованной таблицы. Каждое выражение во фразе SELECT должно принимать единственное значение для группы, т. е. оно может быть либо самим полем, либо арифметическим выражением, включающим это поле, либо константой либо агрегатной функцией, возвращающей единственное значение для группы, В запросах с группировкой необходимо тщательно следить за включением полей во фразу SELECT, так как в противном случае можно не получить желаемого результата. Если во фразу SELECT будет помещено хотя бы одно поле, которое не является единственным для группы, например ключевое поле подчиненной таблицы, то создание групп будет прервано, так как в результате каждая строка запроса будет уникальна.

14 Группирование записей само по себе ничего не дает. Обычно производятся вычисления для групп: Для этой цели имеется ряд групповых (иначе агрегатных) функции, производящих следующие действия над значениями заданного поля (аргумента функции) для каждой группы: SUM - вычисляет сумму: AVG - вычисляет среднее арифметическое; STDEV – вычисляет стандартное отклонение; VAR- вычисляет дисперсию; COUNT - вычисляет число записей, для которых значение заданного поля отлично от NULL. Для подсчета всех записей необходимо использовать операцию *. Например COUNT (*); MIN - возвращает минимальное значение; МАХ - возвращает максимальное значение; FIRST - возвращает первое значение; LAST - возвращает последнее значение.

15 Групповые функции могут также применяться сами по себе, без выполнения группировки. Тогда группой будут считаться все отобранные фразой WHERE записи. Например: SELECT COUNT (*) FROM Заказы; -произведет подсчет всех записей в таблице «Заказы»; SELECT SUM ([Отпускная цена] + [Транс издержки]) AS Полная_цена FROM Заказы WHERE Город = "Минск"; - подсчитает полную сумму цен по всем заказам, сделанным из Минска; SELECT Клиенты.Фамилия, SUM (Заказы.Цена) AS Стоимость FROM Клиенты INNER JOIN Заказы ON Клиенты.КодКлиента = Заказы.КодКлиента WHERE Клиенты.Город ="Минск" GROUP BY Клиенты.Фамилия; подсчитает полную сумму цен по всем заказам для каждого клиента, проживающего в Минске.

16 Фраза HAVING используется для дополнительной селекции записей во время определения групп. Она выполняет те же функции, что и WHERE, но уже в рамках выходного набора. Фраза HAVING устанавливает, какие записи, сгруппированные посредством GROUP BY, должны отображаться и участвовать в групповых операциях. Правила записи аналогичны правилам формирования во фразе WHERE: SELECT код_товара FROM Заказы GROUP BY код_товара HAVING COUNT (*) > 1; - отбирает коды товаров, заказываемых более чем одним покупателем. Фраза ORDER BY замыкает инструкцию SELECT и задает порядок сортировки результирующего множества. Каждая спецификация сортировки представляет собой пару вида: [ASC/DESC]. Необязательный модификатор ASC задает сортировку по возрастанию, DESC - по убыванию. Большинство СУБД требуют, чтобы поле, участвующее в сортировке, было перечислено во фразе SELECT: SELECT Товар.Цена FROM Товары ORDER BY Цена DESC;

17 Параметрические запросы До сих пор условие отбора мы записывали явно во фразе WHERE. Но во многих случаях оно становится известным только во время выполнения программы. SQL позволяет вводить условия отбора в виде параметров запроса. В этом случае такие запросы называются параметрическими или динамическими. Для увеличения эффективности выполнения таких запросов в некоторых СУБД можно вызвать операцию PREPARE, которая подготовит запрос к запуску, т. е. зарезервирует необходимую память и проведет его оптимизацию. Тогда сразу после задания параметра запрос будет готов к запуску. Для освобождения зарезервированной памяти в таком случае применяется операция UNPREPARE.

18 Для задания параметра в Access некоторый текст, являющийся именем параметра, необходимо заключить в квадратные скобки. Этот же текст будет выводится в виде приглашения в окне задания значения параметра. Имя параметра должно отличаться от названий полей таблиц, включенных в запрос. Дополнительно можно явно определить типы параметров с помощью инструкции PARAMETERS в виде [имя параметра] тип данных, [имя параметра] тип данных, и т. д., что позволяет контролировать значения параметров на соответствие типу еще при их вводе. В таком случае инструкция PARAMETERS располагается перед описанием запроса: PARAMETERS [Введите год:] INT; SELECT Клиенты.Фамилия FROM Клиенты INNER JOIN Заказы ON Клиенты.КодКлиента = Заказы.КодКлиента WHERE Year(Заказы.Дата) = [Введите год:];

19 Вложенные запросы. Инструкции SELECT могут многократно вкладываться друг в друга. Вложенная инструкция SELECT записывается как часть фразы WHERE и служит для отбора записей основного запроса. SQL выполняет вложенный подзапрос и затем сравнивает каждую строку основного запроса с результатом вложенного. Вложенные запросы записываются внутри скобок. Например: SELECT Фамилия, Имя FROM Клиенты WHERE Кредит < (SELECT AVG(Кредит) FROM Клиенты);

20 Если подчиненный запрос возвращает набор записей, то вместо операторов сравнения можно использовать функции ALL, SOME, ANY, EXISTS, IN, получившие по функции EXISTS название кванторов. Квантор существования EXISTS обычно записывается следующим образом: EXISTS (SELECT * FROM...). Перед ним можно поставить NOT для инверсии результата. Выражение EXISTS считается истинным тогда и только тогда, когда результат вычисления подзапроса является непустым множеством. В запросах данного типа необходимо явно определять связь между таблицами в виде условия отбора фразы WHERE вложенного запроса. В качестве примера выберем фамилии покупателей, которым был продан компьютер «Р II 350»: SELECT Фамилия FROM Клиенты WHERE EXISTS(SELECT * FROM Заказы WHERE Заказы.код_клиента = Клиенты.код_клиента AND [Наименование товара] = "Р II 350");

21 Кванторы SOME и ANY (синонимы) используются для отбора в главном запросе записей, которые удовлетворяют сравнению с записями, отобранными подчиненным запросом. Например: SELECT * FROM Товары WHERE Цена > ANY (SELECT Цена FROM Заказано WHERE Скидка >= 0.25); Этот запрос отбирает все товары, цена которых больше, чем цена любого товара (т. е, самого недорогого), проданного со скидкой 25 %.

22 Квантор ALL используется для отбора в главном запросе записей, которые удовлетворяют сравнению со всеми записями, отобранными подчиненным запросом: SELECT Марка FROM Товары WHERE Цена > ALL (SELECT Цена FROM Заказано WHERE Скидка >= 0.25); Этот запрос отбирает все товары, цена которых больше цены всех товаров (т. е. самого дорогого), проданных со скидкой 25 %. Квантор IN используется для отбора в главном запросе только тех записей, которые содержат значения, совпадающие с одним из отобранных подчиненным запросом: SELECT Марка FROM Товары WHERE Цена IN (SELECT Цена FROM Заказано WHERE Скидка >= 0.25); Этот запрос отбирает все товары, цена которых совпадает с ценой товаров, проданных со скидкой 25 %.

23 Выгрузка данных в файл Чтобы результат запроса был сохранен в файл, добавьте в команду SELECT выражение INTO OUTFILE Путь и имя файла [FIELDS...] [LINES...] В этой команде нужно указать полный путь к файлу, в который будут выгружены данные (этот файл должен быть новым, не существующим на момент выгрузки). При задании пути к файлу необходимо использовать прямую косую черту вместо принятой в Windows обратной косой черты. Указанный файл создается на компьютере, на котором работает сервер MySQL. Данные выгружаются в той кодировке, в которой они хранятся в базе данных. Команды SELECT … INTO OUTFILE и LOAD DATA можно использовать для резервного копирования таблиц или для переноса данных на другой сервер MySQL. Например, данные из таблицы Customers(Клиенты), сохраненные в файл с помощью команды: SELECT * FROM Customers INTO OUTFILEС:/data/Customers.txt; можно загрузить в таблицу Customers_сору (имеющую такую же структуру, что и таблица Customers) с помощью команды: LOAD DATA INFILE С:/data/Customers.txt INTO TABLE Customers_сору;