Отчёт по курсовой работе на тему «Лабораторный практикум по изучению языка структурированных запросов sql при использовании субд mysql по курсу «Базы данных»

Вид материала

Практикум

Содержание Псевдонимы полей Функции агрегирования Группировка данных Order by 2 Order by 2 desc Выборка данных из нескольких таблиц Соединение равенства Объединение запросов Работа с представлениями данных Создание представлений

Подобный материал:

• Методическое пособие по курсу «Базы данных и информационные системы» 2011, 489.34kb.
• Лекция №1: Стандарты языка sql, 1420.56kb.
• Курс 2778. Создание запросов на языке Microsoft sql server 2005 Transact-sql. Курс, 16.57kb.
• Сервер баз данных, 379.17kb.
• Методические рекомендации по использованию sql-ориентированных заданий, выполняемых, 151.55kb.
•  - это аббревиатура выражения Structured Query Language (язык структурированных запросов)., 3691.83kb.
• Программа дисциплины «Базы данных», 380.05kb.
• Программа дисциплины «Базы данных», 395.38kb.
• Курсовая работа, 52.16kb.
• Курс лекций "Базы данных и субд" Ульянов В. С. Лекция Язык sql. Создание таблиц и ограничений, 146.46kb.

Использование вычисляемых полей

Язык SQL позволяет создавать вычисляемые поля в тексте запроса. Для реализации этой функции в запросе просто приводится выражение, в котором используются арифметические и математические операторы, а также имена полей в качестве переменных. В результате выполнения запроса с вычисляемыми полями выборка будет содержать не только ту информацию, которая содержится в таблицах базы данных, но и дополнительную информацию, полученную в результате вычисления заданного выражения.

Кроме математических операций в SQL поддерживается ряд строковых функций, выполняющий такие операции, как конкатенация строк, выделение подстроки, поиск подстроки внутри строки и ряд других. В запросах SQL также могут применяться функции преобразования символьного типа в числовой, и наоборот, символьного типа в дату и т. п.

При создании вычисляемого поля можно использовать следующие арифметические операторы:

оператор сложения «+»; Q оператор вычитания «-»; Q оператор умножения «*»; Q оператор деления «/».

Приоритет перечисленных операторов соответствует общепринятому: умножение и деление, затем сложение и вычитание. Порядком выполнения операторов можно управлять с помощью круглых скобок.

Рассмотрим пример использования вычисляемых полей. Для этого на основании данных таблицы «Продажи» вычислим для каждого товара сумму денег, полученных за проданный товар (произведение цены на количество проданного товара), и сумму, на которую заказано товаров (произведение цены на количество заказанного товара), а также разность между ними:

SELECT [Код товара]. Цена. Заказано. Продано. Цена*Продано. Цена*3аказано. Цена*Заказано-Цена*Продано FROM Продажи

Данный запрос содержит три вычисляемых поля. Результат его выполнения приведен на рис.

Кроме арифметических операторов допускается использование ряда математических функций, например:

• ABS — вычисление абсолютного значения;
  
• ROUND — округление;
  
• SQR — извлечение квадратного корня;
  
• ЕХР — экспонента;
  
• LOG — натуральный логарифм;
  
• SIN, COS, TAN — тригонометрические функции.
  

Арифметические операторы и математические функции можно использовать как в списке полей после ключевого слова SELECT, так и в предложении, задающем условие выборки (WHERE).

Набор математических функций зависит от конкретной реализации языка SQL Синтаксис одинаковых функций в разных реализациях также может различаться (например, функция вычисления квадратного корня может обозначаться либо SQR, либо SQRT).

Псевдонимы полей

В запросах SQL можно изменять имена полей. Задаваемые при этом новые имена называются псевдонимами (aliases). Их удобно применять при задании в запросе вычисляемых полей. С помощью псевдонимов этим полям можно присваивать осмысленные имена. Псевдоним помещается после имени поля или после вычисляемого выражения через ключевое слово AS.

Переименование поля с помощью псевдонима действительно только в пределах конкретного запроса.

В качестве примера воспользуемся предыдущим запросом, задав в нем псевдонимы для вычисляемых полей:

SELECT [Код товара]. Цена. Заказано, Продано. Цена*Продано AS [Сумма продажи]. Цена*3аказано AS [Сумма заказа]. Цена*Заказано-Цена*Продано AS [Разность] FROM Продажи

Результаты выполнения данного запроса приведены на рис.

Способы задания псевдонимов различаются в разных реализациях SQL. Часто псевдоним задается просто указанием нового имени через пробел после имени поля или вычисляемого выражения, без дополнительных ключевых слов.

Функции агрегирования

Функциями агрегирования называются функции, которые позволяют определить количество записей в таблице или количество значений в столбце таблицы, находят минимальное, максимальное и среднее значение для столбца таблицы, а также вычисляют сумму данных для столбца. Таким образом, агрегирующие функции обеспечивают получение некоторой обобщенной информации.

В SQL определены следующие стандартные функции агрегирования:

• COUNT — выполняет подсчет записей в таблице или подсчет ненулевых значений в столбце таблицы;
  
• SUM — возвращает сумму содержащихся в столбце значений;
  
• MIN — возвращает минимальное значение в столбце; Q МАХ — возвращает максимальное значение в столбце; О AVG — вычисляет среднее значение для содержащихся в столбце значений.
  

В качестве примера рассмотрим таблицу «Продажи». Подсчитаем количество записей в поле «Продано», минимальное и максимальное количество проданных товаров, общую сумму проданных товаров и среднее значение проданных товаров. Для этого зададим следующий запрос:

SELECT СОиШПродано) AS [Всего записей]. МШ(Продано) AS mm. МАХ(Продано) AS max, ЗиМ(Продано) AS [Всего продано]. АУб(Продано) AS [Среднее количество продаж] FROM Продажи

Результат выполнения этого запроса показан на рис.

Со всеми функциями агрегирования можно использовать опцию DISTINCT. В этом случае выполняется обобщение информации только для различающихся строк.

Как правило, использование опции DISTINCT с агрегирующими функциями не имеет смысла, поскольку при подсчете обобщенных данных обычно следует учитывать все записи, а не только уникальные.

Группировка данных

Группировка данных — это объединение записей в соответствии со значениями некоторого заданного поля. Для группировки результатов выборки совместно с оператором SELECT используется предложение GROUP BY. Данное предложение должно следовать после предложения WHERE, но перед предложением ORDER BY. После ключевых слов GROUP BY указывается список полей, включенных в выборку с помощью оператора SELECT. Причем нужно обязательно указывать все отбираемые поля (за исключением полей, относящихся к агрегирующим функциям), хотя порядок их перечисления после предложения GROUP BY может не соответствовать порядку списка после слова SELECT.

Синтаксис оператора SELECT с предложением GROUP BY следующий:

SELECT fieldl. field2. ... , fieldN FROM tab!el {, table2. ... . tableN) WHERE условие

GROUP BY fieldl. field2. ... . fieldN ORDER BY fieldl {ASC | DESC}

Применение предложения GROUP BY без дополнительных функций дает такой же результат, как и применение предложения упорядочения ORDER BY.

Например, если выбрать из таблицы «Товары» два поля — «Наименование» и «Категория», а затем сгруппировать их с помощью запроса:

SELECT Наименование. Категория

FROM Товары

GROUP BY Категория. Наименование.

то результат выборки будет упорядочен по значению первого поля, указанного в предложении GROUP BY (рис.

Если в запросе выбрать только одно поле и выполнить для него группировку, то результирующая выборка не будет содержать дублирующих друг друга записей. Например, если выполнить запрос, аналогичный предыдущему (рис. 11.23), но выбрать только поле «Категория»:

SELECT Категория

FROM Товары

GROUP BY Категория.

то выборка будет содержать только три записи (рис. 11.24).

В этом случае группировка дает такой же результат, как применение оператора SELECT с опцией DISTINCT и предложением ORDER BY.

Поскольку применение одного предложения GROUP BY не дает никакого нового результата, то совместно с ним, как правило, используются функции агрегирования. В этом случае они применяются для вычисления итоговых значений по отдельным группам данных.

Например, чтобы подсчитать количество покупок товаров, сделанных каждым из клиентов, используется следующий запрос:

SELECT [Код клиента].

ЗиЖПродано) AS [Количество покупок]

FROM Продажи

GROUP BY [Код клиента]

Результат выполнения такого запроса приведен на рис. 11.25.

Результаты группировки можно упорядочить с помощью ключевого слова ORDER BY, а в операторе SELECT, содержащем предложение группировки, можно использовать предложение WHERE. Для иллюстрации этой возможности модифицируем предыдущий запрос следующим образом: выберем только тех клиентов, которые сделали за один раз более 10 покупок, и упорядочим результаты выборки в порядке возрастания:

SELECT [Код клиента].

5иМ(Продано) AS [Количество покупок]

FROM Продажи

WHERE Продано>10

GROUP BY [Код клиента]

ORDER BY 2 DESC

Результат выполнения данного запроса изображен на рис. 11.26.

Для задания ограничений на создаваемые группы совместно с ключевым словом GROUP BY может использоваться предложение HAVING. Оно должно следовать после GROUP BY, но до предложения ORDER BY (если оно присутствует в запросе).

В предыдущем примере в качестве условия было задано количество покупок за один раз. Если мы хотим установить ограничение на общее количество покупок, то необходимо применить предложение HAVING:

ПРИМЕЧАНИЕ ——————————————————————————————————————— В предложении HAVING не обязательно использовать только те поля, которые заданы в списке оператора SELECT.

Модифицируем рассмотренный выше пример (рис. 11.27) таким образом, чтобы ограничение было наложено не на количество купленных товаров, а на их стоимость:

SELECT [Код клиента].

51)М(Продано) AS [Количество покупок]

FROM Продажи

GROUP BY [Код клиента]

HAVING 5иМ(Продано*Цена)>25000

ORDER BY 2 DESC

Данный запрос отбирает клиентов, купивших товаров более чем на 25 000, и отображает количество сделанных ими покупок (рис. 11.28).

В предложении GROUP BY, в отличие от предложения ORDER BY, нельзя вместо имен выбранных полей использовать их порядковые номера в списке оператора SELECT.

Выборка данных из нескольких таблиц

Как правило, информация, хранящаяся в базе данных, содержится в нескольких связанных между собой таблицах. Язык SQL позволяет создавать запросы, извлекающие данные из нескольких таблиц. При этом выполняется операция соединения, состоящая в объединении нескольких таблиц с целью поиска в них запрошенных данных.

Существует несколько способов соединения таблиц. Наиболее часто встречаются следующие:

Q соединение равенства; О соединение неравенства; Q внешние соединения.

Для задания вида соединения используется предложение WHERE, в котором вид соединения указывается с помощью операторов сравнения или логических операторов.

Соединение равенства

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

Синтаксис оператора выборки для этого способа соединения таблиц будет следующим:

SELECT tablel.fieldl. table2.field2 {. ... . tableN.fieldN} FROM tablel. table2 {,.... tableN} WHERE tablel.common_fieldl - table2.common_fieldl {AND tablel.common_field2 - table2.common_field2}

При формировании запроса на выборку из нескольких таблиц в списке полей после слова SELECT перед именем поля обычно указывается имя таблицы, к которой это поле относится. Такое действие называется квалификацией полей запроса. Квалификация обязательна только для полей, имеющих одинаковые имена в разных таблицах, из которых производится выборка.

Рассмотрим пример выборки из двух таблиц с использованием соединения равенства. Выберем из таблицы «Клиенты» поля, содержащие сведения об именах клиентов, а из таблицы «Продажи» — поля, в которых содержатся сведения о покупках, сделанных клиентами. Для связывания таблиц воспользуемся общим для обеих таблиц полем «Код клиента»:

SELECT Клиенты.Фамилия. Клиенты.Имя.

Клиенты.Отчество. Продажи.Продано

FROM Клиенты. Продажи

WHERE Клиенты.[Код клиента]=Продажи.[Код клиента]

Результат выполнения данного запроса приведен на рис. 11.29.

При связывании таблиц можно использовать предложение группировки. Изменим рассмотренный выше запрос (см. рис. 11.29) таким образом, чтобы результаты были сгруппированы по полям «Фамилия», «Имя», «Отчество» и для каждого клиента выводилось суммарное количество покупок:

SELECT Клиенты.Фамилия. Клиенты.Имя, Клиенты Отчество,

Зимспродажи.Продано) AS [Количество покупок]

FROM Клиенты. Продажи

WHERE Клиенты.[Код клиента]=Продажи.[Код клиента]

GROUP BY Клиенты.Фамилия. Клиенты.Имя. Клиенты Отчество

Результаты, возвращаемые этим запросом, приведены на рис. 11.30.

Выборка из трех таблиц проводится аналогичным образом, только в предложении WHERE необходимо указать условие связи с третьей таблицей. Для примера дополним предыдущий запрос (см. рис. 11.30) таким образом, чтобы в выборку была включена информация о наименовании товара из таблицы «Товары»:

SELECT Клиенты.Фамилия, Клиенты.Имя, Клиенты.Отчество,

511М(Продажи.Продано) AS [Количество покупок].

Товары.Наименование

FROM Клиенты. Продажи. Товары

WHERE (Клиенты.[Код клиента]=Продажи.[Код клиента]) AND

(Продажи.[Код товара]=Товары.[Код товара])

GROUP BY Клиенты.Фамилия, Клиенты.Имя. Клиенты.Отчество.

Товары.Наименование

Результаты выполнения данного запроса показаны на рис. 11.31.

SELECT Клиенты Фамилия, Клиенты Имя, Клиенты Отчество, >11М(Продажи Продано) AS [Количество покупок], 'овары Наименование ^:ROM Клиенты, Продажи, Товары

WHERE (Клиенты [Код клиента]-Продажи [Код клиента]) AND Продажи [Код товара)-Товары [Код товара]] 3ROUP BY Клиенты Фамилия. Клиенты Имя, Клиенты Отчество, 'овары Наименование

Подзапросы

Подзапрос представляет собой запрос, помещенный внутри другого запроса. Подзапросы применяются для получения данных, которые затем используются другим запросом.

Запрос, содержащий подзапрос, называется сложным. В процессе его выполнения сначала выполняется подзапрос, а затем — основной запрос. При создании сложного запроса необходимо следовать следующему набору правил:

О подзапросы должны заключаться в круглые скобки;

О предложение ORDER BY может быть использовано только в основном запросе;

О подзапросы, возвращающие более одной записи, могут использоваться только

с многозначными операторами; О в основном запросе нельзя использовать оператор BETWEEN.

'ELECT Товары [Наименование], ЗиМ(Продажи [Продано!) AS (Всего продано) FROM Товары LEFT OUTER JOIN Продажи

)N Товары [Код товара]»Продажи [Код товара] GROUP BY Товары [Наименование]

Ниже приведен синтаксис оператора SELECT с подзапросом:

SELECT {* | ALL | DISTINCT fieldl. field2. . . fieldN}

FROM tablel {. tableZ. ... , tableN}

WHERE условие (SELECT fieldl{. fields. . . fieldN}

FROM tablel {. table2. .. . tableN}

WHERE условие)

Для иллюстрации технологии использования подзапроса воспользуемся следующим примером. Выберем из таблицы «Продажи» информацию о продажах товара с наименованием «Delphi 5»:

SELECT [Код клиента]. Заказано. Продано, Цена

FROM Продажи

WHERE [Код товара]-(5Е1ЕСТ [Код товара]

FROM Товары

WHERE Наименование-'Del phi 5')

Поскольку в таблице «Продажи» не содержится наименования товара, то с помощью подзапроса мы обращаемся к таблице «Товары» и определяем код товара заданного наименования. Затем, в основном запросе, выбираем интересующие нас поля из таблицы «Продажи», в которых код товара совпадает с тем, который получен в результате выполнения подзапроса. Результат, полученный при выполнении приведенного запроса, показан на рис. 11.33.

В подзапросе, так же как и в основном запросе, можно использовать подзапросы. Максимальный уровень вложенности подзапросов определяется конкретной реализацией SQL.

Объединение запросов

Язык SQL позволяет объединять несколько запросов с помощью специальных операторов. Запросы, включающие в себя несколько операторов SELECT, принято называть составными.

SELECT [Код клиента]. Заказано, Продано, Цена

FROM Продажи

WHERE (Код товара]=(SЕLЕСТ (Код товара]

FROM Товары

WHERE Наименование-'Delphi 5')

Составные запросы формируют один набор данных на основе результатов, полученных при выполнении каждого отдельного запроса, входящего в объединение. Во многих случаях составные запросы целесообразно использовать вместо простых запросов со сложным условием выборки. Это связано с тем, что разбиение сложного условия на несколько более простых запросов делает текст запроса более понятным. Как правило, проще написать составной запрос, чем аналогичный простой запрос со сложным условием отбора данных.

Для объединения запросов наиболее часто используются операторы UNION и UNION ALL (предусмотренные стандартом ANSI).

При объединении запросов, независимо от типа используемых операторов объединения, необходимо следовать следующим правилам:

каждый из запросов, входящих в объединение, должен возвращать одинаковое

количество полей (в том числе и вычисляемых); Q типы полей, возвращаемых в результате выполнения каждого запроса, должны совпадать.

Работа с представлениями данных

Представление (view) — это предопределенный запрос, который хранится в ба данных. Представление можно рассматривать как виртуальную таблицу, которая формируется из одной или нескольких реальных таблиц базы данных (и/или р нее созданных представлений). Работа с представлением после его создания полностью аналогична работе с таблицей. Представления обычно используются в двух случаях:

для объединения данных, хранящихся в нескольких таблицах (разбиение таблицы обычно производится при нормализации базы данных) с целью представления в удобном для просмотра и редактирования виде;

для разграничения доступа к информации — с помощью представлений можно разрешить пользователю доступ только к части информации, хранящейся в тг лице базы данных.