ЛР_SQL. Использование selectsql для организации выборки данных

• Название: Использование selectsql для организации выборки данных
• Дата: 12.10.2022
• Размер: 60.5 Kb.
• Имя файла: ЛР_SQL.doc
• Тип: Лабораторная работа; #730673

___________________________________________________________________________

Лабораторная работа № 3

Тема: "Использование SELECT-SQL для организации

выборки данных"
Цель лабораторной работы: привитие практических навыков в использовании команд SELECT-SQL для выполнения выборок данных по различным критериям.
 Задание на лабораторную работу:

1. 
   Создать форму с элементами управления, с помощью которых можно продемонстрировать возможности работы команды SELECT-SQL.
   
2. 
   С данными, хранящимися в персональной БД, выполнить все действия, приведенные в примерах методических указаний и реализовать по возможности информационные запросы пользователей.
   

 Теоретические сведения
Вывод данных из таблицы по какому-либо условию может являться примером простейшего запроса, который в этом случае называется выборкой данных. В качестве исходных данных для примеров приведена БД Office, в состав которой входят таблицы Clients (данные по клиентам), Sales (данные по продажам), Price (данные по программным продуктам), Invoices (данные о выписанных счетах), Inv_details (данные о содержании счетов)

 Пример 1: Отбор списка товаров, цена которых менее 100 (поле price в таблице Price), и вывод его в окно просмотра.

SELECT * ;

FROM Price ;

WHERE price < 100
 Пример 2: Для выполнения проекции данных (когда необходимо выбрать только часть полей таблицы) в команде SELECT-SQL ограничивается число выводимых в результате полей. Вывести в окно просмотра только поля с описанием товара (description) и его цены (price):

SELECT description , price ;

FROM Price
 Пример 3: Для получения данных из нескольких таблиц, необходимо выполнить операцию объединения (join), в которой описываются условия связи между данными в этих таблицах. Операция объединения сравнивает данные в указанных полях таблиц и при их совпадении отбирает данные в результат запроса. В теме "Создание представлений" вопрос о типах объединений обсуждался подробно. Так, например, для получения данных о количестве проданной продукции из таблицы Sales и наименовании продукции из таблицы Price можно использовать два варианта команды SELECT-SQL:

а) SELECT Sales.quantity , Price.description ;

FROM Office!Price INNER JOIN Office!Sales ;

On Price.kod_id = Sales.product_id
b) SELECT Sales.quantity , Price.description ;

FROM Price , Sales ;

WHERE Price.kod_id = Sales.product_id
При объединении нескольких таблиц необходимо соблюдать правило, согласно которому условия равенства полей объединяемых таблиц должны указываться в обратной последовательности указания связанных таблиц. Например, для вывода списка клиентов и выписанных для них счетов необходимо объединить данные из трех таблиц:

SELECT Clients.name , Inv_details.product_descr ;

FROM Clients INNER JOIN ;

Invoices INNER JOIN ;

Inv_details ;

On Invoices.kod_id = Inv_details.kod_id ;

On Clients.kod_id = Invoises.client_id
В условиях объединения INNER JOIN последней указывается таблица Inv_details. С нее надо начать в опции ON запись условий равенства полей этой таблицы и таблицы, указанной слева от ключевого слова JOIN. Следующее условие записывается для таблиц Invoices и Clients.
 Пример 4: В списке колонок можно указывать не только поля, но и выражения, в том числе с использованием специальных функций агрегирования, а также константы. При этом порядок указания колонок в команде запроса будет соответствовать их расположению в результате запроса. На это необходимо обращать внимание для наиболее удобного расположения данных.

В отличии от предыдущего примера необходимо получить по каждому продукту суммарные сведения и в начало каждой строки записать дату выполнения запроса. Для указания признака суммирования ( в данном случае смена значения кода продукта) используем опцию GROUP BY. Результат выполнения запроса запомним в курсоре curList:

SELECT DATE() , Price.description , SUM(Sales.quantity) ;

FROM Office!Price INNER JOIN Office!Sales ;

ON Price.kod_id = Sales.product_id ;

GROUP BY Sales.product_id ;

INTO CURSOR curList
Не совсем удобных имен колонок в курсоре curList можно избежать, если в списке колонок использовать опцию AS. Необходимо учитывать, что в именах колонок не допускаются пробелы:

SELECT DATE() AS Когда_составили , ;

Price.description AS Что_продали , ;

SUM(Sales.quantity) AS Сколько ;

FROM Office!Price INNER JOIN Office!Sales ;

ON Price.kod_id = Sales.product_id ;

GROUP BY Sales.product_id ;

INTO CURSOR curList

BROWSE LAST
 Пример 5: Основное назначение опции GROUP BY – управление функциями агрегирования. В то же время, если использовать эту опцию без функций агрегирования, она повлияет на результат так же, как опция DISTINCT. Каждое значение будет представлено в результате только один раз. Например, следующая команда выведет все объекты выписки счетов:

SELECT product_descr FROM Inv_details
Команда с опцией GROUP BY направит в результат только неповторяющиеся значения:

SELECT product_descr FROM Inv_details GROUP BY 1
Что аналогично следующей команде:

SELECT DISTINCT product_descr FROM Inv_details
 Пример 6: Опция WHERE позволяет описать условия отбора записей. Каждое условие должно быть связано с предыдущим с помощью логической связки AND или OR. Для логического объединения условий должны использоваться скобки. Например, выполнить отбор данных по условию:

a) SELECT Price.description , Price.price ;

FROM Office!Price ;

WHERE Price.kod_id >= 30000 ;

AND Price.kod_id < 40000
b) SELECT Price.description , Price.price ;

FROM Office!Price ;

WHERE (Price.kod_id >= 30000 ;

AND Price.kod_id < 40000) ;

OR (Price.kod_id >= 10000 ;

AND Price.kod_id < 20000)
Помимо логического оператора для объединения условий AND, можно воспользоваться оператором BETWEEN для указания включающего интервала значений. Предыдущий пример можно переписать следующим образом:

SELECT Price.description , Price.price ;

FROM Office!Price ;

WHERE Price.kod_id BETWEEN 30000 AND 39999 ;

OR Price.kod_id BETWEEN 10000 AND 19999
Для указания в условиях отбора значений, которые не входят в какой-либо интервал, можно использовать отрицание NOT. Например:

SELECT Price.description , Price.price ;

FROM Office!Price ;

WHERE Price.kod_id NOT BETWEEN 30000 AND 39999
Даты в условиях отбора следует указывать в фигурных скобках. Например, вывести список клиентов, которые приобрели продукты в промежуток времени от 1 января до 30 апреля 1999 года:

SELECT Clients.name ;

FROM Clients , Sales ;

WHERE Sales.kod_clients = Clients.client_id ;

AND Sales.sale_date BETWEEN {01.01.99} AND {30.04.99}
Если в условиях отбора данных необходимо указать список значений, которым должны соответствовать данные, помещаемые в результат, то следует использовать оператор IN. Вывести на экран список клиентов, которые делали покупки по понедельникам в марте 1999 года, можно следующим образом:

SELECT Clients.name ;

FROM Clients , Sales ;

WHERE Sales.kod_clients = Clients.client_id ;

AND Sales.sale_date ;

IN ({01.03.99} , {08.03.99} , {15.03.99} , {22.03.99} , {29.03.99} )
 Пример 7: Наиболее важная область использования оператора IN – вложенные подзапросы. Подзапрос позволяет сформировать список значений, которым должны соответствовать данные в результате, если эти значения неизвестны заранее. Например, для того, чтобы вывести список продуктов, которые единовременно приобретались в количестве, не превышающем одну партию, можно написать такую команду:

SELECT description ;

FROM Price WHERE kod_id IN ;

( SELECT product_id FROM Sales WHERE quantity > 1)
Такое использование команды SELECT удобно и оправдано в некоторых случаях. Однако, следует учесть, что использование подзапросов резко ухудшает производительность приложения и в данном примере целесообразнее использовать следующую команду:

SELECT Price.description ;

FROM Price , Sales ;

WHERE Price.kod_id = Sales.product_id ;

AND Sales.quantity > 1
 Пример 8: При сравнении строковых выражений необходимо использовать установки, которые регулируют этот процесс. Например, следующая команда выведет всех клиентов, имена которых начинаются на "АО":

SELECT name FROM Clients WHERE name = "АО"
Однако, если была использована установка SET ANSI ON, то в результат не попадет ни одна запись.

Иногда для указания условий отбора данных невозможно обойтись только условиями сравнения. В этом случае используется оператор LIKE. Например, вывести список клиентов, имена которых начинаются с буквы "А", а третья буква – "р":

SELECT * FROM Clients WHERE name LIKE "A_p%"
 Пример 9: Опция ORDER BY используется для получения результата, данные в котором должны быть расположены в определенном порядке. Например, для получения списка выписанных счетов, записи в котором должны располагаться по наименованиям программных продуктов, а для одинаковых программных продуктов по убыванию суммы покупки, можно выполнить следующую команду:

SELECT inv_num , prodect_descr , price*quantity ;

FROM Invoices , Inv_details ;

WHERE Invoices.kod_id = Inv_details.kod_id ;

ORDER BY 2 , 3 DESC
Цифры в опции ORDER BY – это номера колонок в результате, которые удобно использовать вместо указания их имен. Если для получения колонки используется выражение, то можно указать только ее номер.

Опция HAVING используется вместе с опцией GROUP BY для формирования условий отбора записей в группы, в ней можно использовать локальные псевдонимы и функции агрегирования. Например, вывести список групп программных продуктов с указанием во второй колонке количества программных продуктов, входящих в каждую группу:

SELECT part_descr , COUNT(*) ;

FROM Price , Partition ;

WHERE kod_p = INT(kod_id/1000) ;

GROUP BY kod_p
Теперь поместим в результат только те группы, число программных продуктов в которых превышает 10. Это условие для каждой группы можно описать в опции HAVING:

SELECT part_descr , COUNT(*) ;

FROM Price , Partition ;

WHERE kod_p = INT(kod_id/1000) ;

GROUP BY kod_p ;

HAVING COUNT(*) > 10
 Пример 10: Опция UNION используется для объединения результатов одной команды SELECT с результатами другой команды (внешнее объединение). Например, необходимо получить список продаж программных продуктов, в который должны войти и не проданные программы.

SELECT Price.description , Sales.invoice_number , Sales.quantity ;

FROM Price , Sales ;

WHERE Price.kod_ce = Sales.kod_id ;

UNION ;

SELECT Price.description , 0 , 0 ;

FROM Price ;

WHERE Price.kod_ce NOT IN ;

(SELECT DICTINCT Sales.kod_id FROM Sales)
В первой части запроса (до опции UNION) отбираются все записи, соответствующие условиям, т.е. в запрос попадут программные продукты, которые проданы (коды продуктов есть и в таблице Sales). Во второй части запроса отбираются продукты, которые не проданы, их коды есть в таблице Price, но нет в таблице Sales. Важным условием является соответствие структур выходных данных в объединяемых частях, иначе будет зафиксирована ошибка. Для этого в список колонок второй части запроса включены нули, соответственно, для символьных данных использовать " ", для даты - { / / }.
 Пример 11: В качестве примера использования вложенного подзапроса составим список клиентов, сделавших покупки после 1 июля 1999 года. В примере для обозначения таблиц используются локальные псевдонимы. Результат запроса помещается в курсор curDate.

SELECT Name FROM Clients A ;

WHERE EXIST (SELECT * FROM Sales B ;

WHERE sale_date > {01.07.99} ;

AND A.client_id = B.kod_clients) ;

INTO CURSOR curDate
 Вопросы для защиты лабораторной работы:

1. 
   Какие способы получения выборок данных известны? Выделить наиболее эффективные способы с точки зрения быстродействия и экономии дискового пространства.
   
2. 
   С помощью каких средств языка SQL можно выполнить выборку данных из двух и более таблиц.
   
3. 
   Какие условия объединения таблиц можно использовать в SQL-команде?
   
4. 
   Какие функции агрегирования данных можно использовать в конструкциях SQL?
   
5. 
   Какие особенности формирования подзапроса в SQL-запросе известны?
   
6. 
   Изложить суть метода Рашмора. Использование данного метода для организации выборок данных.