СОЗДАНИЕ ЗАПРОСОВ НА ВЫБОРКУ И ИЗМЕНЕНИЕ ДАННЫХ В БД

CREATING QUERIES FOR SAMPLING AND CHANGING DATA IN THE DATABASE

Roza Gaynanova

lecturer of the Department of General Educational Disciplines Kazan national research Technological University

Russia, Kazan

АННОТАЦИЯ

В статье рассматриваются примеры создания запросов на выборку и изменение  данных на языке SQL. В качестве примера рассматривается разработка запросов для базы данных «Отдел заказов».

ABSTRACT

The article discusses examples of creating queries for sampling and modifying data in SQL. As an example, consider the development of queries for the database "Department of Orders".

Ключевые слова: база данных, SQL Server, язык SQL,  операторы Select, Update, Delete, Insert.

Keywords: database, SQL Server, SQL language, Select, Update, Delete, Insert statements.

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

При разработке информационной системы приходится решать две основные задачи: задачу разработки баз данных, предназначенной для хранения информации; задачу разработки графического интерфейса пользователя клиентских приложений. В качестве примера студентам предлагается работа с базой данных на SQL сервере «Отдел заказов». БД «Отдел заказов» содержит следующие таблицы: «Клиенты», «Каталог товаров», «Заказы», «Закупки». Таблица «Клиенты» содержит данные о клиентах: Номер_клиента, ФИО_клиента, Телефон, Адрес. Таблица «Каталог товаров» содержит сведения о имеющихся товарах: Код_товара, Наименование, Описание, Цена. Таблица «Заказы» содержит данные о заказанных клиентами товарах: Номер_заказа, Номер_клиента, Код_товара, Цена_товара, Дата_заказа, Количество, Сумма_оплаты (вычисляемое поле Цена_товара* Количество), Оплачено. Таблица «Закупки» содержит сведения о закупленных товарах: Номер_закупки, Код_товара, Цена_товара, Количество, Дата_закупки, Добавлено.

Для выборки данных из БД используется оператор Select. Оператор Select позволяет получать существующие данные из базы данных. Большинство операторов Select описывают четыре главных свойства результирующего набора:

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

Пример 1. Создать запрос для вычисления общего количества и общей суммы заказов по каждому наименованию товара. В запрос включить поля: Наименование, Количество, Сумма_оплаты. При создании запроса используется операнд группировки GROUP BY и функция, суммирующая значения поля Количество и суммирующая значения поля Сумма_оплаты. Данные выбираются из таблиц Каталог товаров и Заказы. В данном запросе Sum(Заказы.Количество) и Sum(Заказы.Сумма_оплаты) вычисляемые поля, они получают названия «Общее_количество» и «Сумма_заказов», указываются после ключевого слова AS.

SELECT [Каталог товаров].Наименование, Sum(Заказы.Количество) AS Общее_количество, Sum(Заказы.Сумма_оплаты) AS Сумма_заказов

FROM Заказы INNER JOIN [Каталог товаров]

ON Заказы.Код_товара = [Каталог товаров].[Код товара]

GROUP BY [Каталог товаров].Наименование

Результат выполнения запроса (рисунок 1):

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

Для изменения данных используются операторы Update, Delete, Insert.

Оператор Update способен изменять данные в одной записи, группе записей или во всех записях таблицы. Синтаксис оператора UPDATE выглядит следующим образом:

UPDATE {таблица}

SET поле1=значение1[, поле2=значение2]

[WHERE {условие}]

SET задает поля, которые следует изменить, и их новые значения. Во всех записях, которые соответствуют условию поиска, заданному конструкцией WHERE, значения заданных полей обновляются значениями, заданными в конструкции SET. Если конструкция WHERE не задана, обновляются все записи.

Пример 2. Создать запрос, который уменьшает количество товаров в таблице «Каталог товаров», если заказанный товар оплачен.

UPDATE [Каталог товаров]

SET [Количество] = [Каталог товаров].[Количество]-[Заказы].[Количество]

FROM [Каталог товаров] INNER JOIN

Заказы ON [Каталог товаров].[Код товара] = Заказы.Код_товара

WHERE Заказы.Оплачено = 1

После успешного выполнения запроса появляется сообщение об обработанном количестве строк .

Оператор Delete удаляет из таблицы одну или несколько записей. Любая таблица, из которой с помощью оператора Delete удалены все записи, все равно остается в базе данных. Для быстрого удаления всех данных из таблицы можно использовать оператор Truncate Table.

Пример 3. Заказы не оплаченные в течении 10 дней из таблицы Заказы удалить.

DELETE FROM Заказы

WHERE (DATEDIFF(dd, Заказы.Дата_заказа, Getdate())>10) and (Оплачено=0)

Функция Getdate() возвращает текущую дату. Функция DateDiff возвращает количество дней прошедших от даты заказа до текущей даты.

Оператор Insert добавляет в таблицу одну или несколько записей. В операторе Insert может использоваться подзапрос Select:

INSERT INTO {таблица} [(список полей)] SELECT ...

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

Пример 4. Создадим таблицу «Сведения для закупки», куда будем записывать сведения о товарах, количество которых близко нулю (например, не более трех). Эти сведения будут необходимы для подачи заявки на закупку товаров. Таблица будет состоять из следующих полей (таблица 1):

Таблица 1.

Структура таблицы «Сведения для закупки»

Создадим запрос, который переписывает в эту таблицу сведения о товарах из таблицы «Каталог товаров», количество которых не более 3.

INSERT INTO [Сведения для закупки]

SELECT [Код товара], Наименование, Количество

FROM [Каталог товаров]

WHERE [Каталог товаров].Количество<=3

После успешного выполнения запроса появляется сообщение о количестве обработанных строк (строк обработано: 4) Посмотреть содержимое таблицы «Сведения для закупки» можно выполнив команду «Выбрать первые 1000 строк» (рисунок 2).

Рисунок 2. Содержимое таблицы «Сведения для закупки»

Чтобы каждый раз при оформлении заявки, не создавать этот запрос, создадим хранимую процедуру, которая удаляет все записи из таблицы «Сведения для закупки» и переписывает сведения о товарах, которые пора закупать.

Хранимая процедура – это сохраненный набор инструкций языка SQL, выполняемый как единое целое. Хранимая процедура является специальной программой из совместно откомпилированных команд SQL, сохраняемой в базе данных SQL Server и выполняемой по вызову клиента.

Для создания хранимой процедуры в окне обозревателя объектов выберем базу данных «Отдел заказов», откроем узел «Программирование», щелкнем правой кнопкой узел «Хранимые процедуры». В контекстном меню выберем команду «Создать хранимую процедуру» (рисунок 3). В открывшемся окне запроса после слов CREATE PROCEDURE нужно ввести название процедуры «Сведения_для_заявки», между словами BEGIN и END ввести операторы TRUNCATE TABLE и INSERT (рисунок 4).

Рисунок 3. Выбор команды «Создать хранимую процедуру»

Рисунок 4. Редактирование хранимой процедуры

В хранимую процедуру можно передавать параметры. В инструкции создания хранимой процедуры за ключевыми словами CREATE PROCEDURE следует предлагаемое по умолчанию имя процедуры и определения ее параметров и переменных. Параметры в хранимой процедуре, как и переменные, задаются вместе с типами данных, возможно присвоение значений по умолчанию. Имя параметра должно начинаться с символа @. Для параметров допускаются те же типы данных, что и для столбцов таблиц. Если у процедуры несколько параметров, их определения разделяются запятыми.

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

Передаваемые параметры обозначим: @Наименование, @МинЦена и @МахЦена. Типы этих параметров должны совпадать с типами полей «Наименование» и «Цена» таблицы «Каталог товаров». Параметр @Наименование должен иметь тип nchar(30), параметры @МинЦена и @МахЦена - тип money.

Будут выбираться поля Наименование, Цена и Количество из таблицы «Каталог товаров». Условие выборки указывается с помощью операнда WHERE: WHERE (SUBSTRING(Наименование,1,Len(@Наименование))= @Наименование) and (Цена BETWEEN @МинЦена AND @МахЦена)

Здесь функция SUBSTRING извлекает подстроку из исходной строки. Тип извлеченной строки будет совпадать с типом исходной строки. Синтаксис этой функции: SUBSTRING (строковое_значение, начальная_позиция,длина)

Подстрока, извлекаемая из элемента строковое_значение, начинается с символа, порядковый номер которого указан в элементе начальная_позиция. Длина извлекаемой строки указывается в элементе длина. Функция Len возвращает длину строки.

Из поля Наименование таблицы «Каталог товаров», начиная с первой позиции, выделяется подстрока с длиной равной длине передаваемого параметра @Наименование и сравнивается со строкой @Наименование. Цена товара должна находиться в диапазоне @МинЦена и @МахЦена.

PROCEDURE ПоискТовара @Наименование nchar(10), @МинЦена money, @МахЦена money

AS

BEGIN

SELECT Наименование, Цена, Количество

FROM [Каталог товаров]

WHERE (SUBSTRING(Наименование,1,Len(@Наименование))= @Наимен) and (Цена BETWEEN @МинЦена AND @МахЦена)

END

Допустим, покупатель хочет заказать принтер, цена принтера должна быть в переделах от 12000 до 17000 рублей. Для выполнения хранимой процедуры создадим запрос:

EXEC          ПоискТовара

@Наименование = 'Принтер',

@МинЦена = 12000,

@МахЦена = 17000

Результат выполнения (рисунок 5)

Рисунок 5. Результат выполнения хранимой процедуры «Поиск товара»

В результате изучения курса «Методы и средства проектирования информационных систем» студенты знакомятся методами проектирования информационных систем, методами разработки запросов для работы с базами данных на SQL сервере, предназначенной для хранения информации. Знакомятся с основами языка запросов SQL. Приобретают навыки создания таблиц, запросов, представлений, хранимых процедур и функций, возвращающих табличное значение, в среде SQL Server Management Studio 2012. Приобретают навыки использования функций языка SQL при создании запросов. Это способствует развитию научно-исследовательских и творческих способностей студентов.

Список литературы:

1. Аллен Тейлор. SQL для чайников, 8-е издание.: Пер. с англ. – М.: ООО “И.Д. Вильямс”, 2014. – 416 с.
2. Станек Уильям Р. Microsoft SQL Server 2012. Справочник администратора. /СПб.: БХВ-Петербург, 2013. – 576с.