Контрольная по пройденному материалу (с 04.10.07) состоится 08.11.07 в компании «НИКС» в 17.30 на Звездном бульваре 19. Схему проезда можно посмотреть на сайте http://www. *****/dealers/howtofindus. html.
Просьба не опаздывать, обязательно взять с собой паспорт и студенческий билет.
Сбор в холле предприятия (на входе) с 17.20, за вами придут.
Задания (с прайсом и матрицами) следует обязательно прислать до 08.11.07 на e-mail *****@***ru с заголовком "Парадигмы: задача N". Итоговая оценка будет складываться из домашних заданий и контрольной.
Лекция 4.
1. Синтаксис языка SQL. Операторы DDL и DML.
2. Оператор SELECT, его синтаксис. Использование WHERE, ORDER BY, TOP, DISTINCT и т. д.
3. Запросы с параметром.
4. Реализация запросов в QBE
Практика:
Написание простейших запросов в SQL и QBE:
§ выборка данных по дате, контрагенту, артикулу,
§ сортировка данных по различным полям,
§ выборка уникальных контрагентов, выборка нескольких первых контрагентов
§ выбор городов с максимальным оборотом и тд.
Дом. задание:
Только на основе полученных на семинаре знаний, используя структуру накладных, получить данные по накладной с максимальной суммой оплаты.
Лекция 4.
SQL - язык работы с базами данных.
Типы запросов:
DDL (Data Definition Language - Язык Определения Данных). Состоит из команд, которые создают, меняют, удаляют объекты (таблицы, индексы и так далее) в базе данных. То есть влияют на структуру БД.
DML (Data Manipulation Language - Язык Манипулирования Данными). Набор команд, которые управляют содержимым БД. Добавляют строки, удаляют, меняют, проводят выборку.
Что такое запрос:
Запрос – последовательность инструкций на SQL.
Запросы используются для просмотра, изменения и анализа данных различными способами. Запросы также можно использовать в качестве источников записей для форм, отчетов и страниц доступа к данным.
Запросы на выборку:
Для начала рассмотрим синтаксис оператора на выборку для одной таблицы.
Синтаксис SELECT к одной таблице:
SELECT [top, distinct] Поле1, Поле2, ….
FROM Таблица1
WHERE Условие
[ORDER BY ПолеN asc (desc)];
SELECT – ключевое слово, которое сообщает БД о том, что выполняемый запрос является запросом на выборку.
Поле1, Поле2 – поля, которые необходимо вывести на экран. Если нужно вывести все поля, то следует писать
SELECT * FROM…
Порядок вывода атрибутов определяет пользователь, то есть столбцы из таблицы выводятся в таком же порядке, как указано после ключевого слова SELECT.
FROM – также ключевое слово, которое сообщает БД о том, из какой (-их) таблицы следует произвести выборку. Таблица1 - имя таблицы, из которой необходимо провести выборку.
WHERE – ключевое слово, которое говорит о том, что вывести нужно не все строки, а только удовлетворяющие Условию.
Условие – прописанное условие выбора записей.
Такой запрос не обязательно будет упорядочивать данные на выходе. Та же самая команда, выполненная с теми же самыми данными, но в разное время, не обязательно выведет тот же самый порядок. Упорядочивание записей можно проводить самостоятельно при помощи SQL, используя команду ORDER BY. ПолеN – поле, по которому следует упорядочить, соответственно asc – сортировка по возрастанию, desc – по убыванию.
Кроме того, если у пользователя возникает необходимость посмотреть только несколько первых записей, то для таких целей служит команда top.
Например:
SELECT top 5 [percent] контрагент, сумма_RUR
FROM Накладные
Top 5 – первые 5 записей, Top 5 Percent – первые 5 процентов записей.
Если возникает необходимость выбора уникальных записей, в таком случае используется предикат distinct. Например, когда возникает необходимость выбора уникальных покупателей, запрос будет выглядеть так:
SELECT distinct контрагент
FROM Накладные
Запись условия:
Скажем несколько слов по поводу записи условий в предложении WHERE.
Для записи условий используются как реляционные, так и булевы операторы. Реляционные: >, <, =, <>. Булевы: AND, OR, NOT. Также используются операторы: Like, In, Between.
Условие при помощи булевых и реляционных операторов можно сформулировать, например, так:
WHERE (поле1>Значение1 or поле1 < значение2) AND поле2 <> значение3.
Как использовать операторы like, In, Between.
Оператор IN:
Оператор IN определяет набор значений, в которое данное значение может или не может быть включено. Например, условие
SELECT * FROM Накладные
WHERE контрагент in (‘Иванов’, ‘Петров’)
выберет все накладные, по которым закупались Иванов или Петров.
Оператор BETWEEN:
Оператор BETWEEN похож на оператор IN. В отличие от определения по номерам из набора, как это делает IN, BETWEEN определяет диапазон. Синтаксис такой: ключевое слово BETWEEN с начальным значением, ключевое AND и конечное значение. В отличие от IN, BETWEEN чувствителен к порядку, и первое значение в предложении должно быть первым по алфавитному или числовому порядку.
SELECT *
FROM Номенклатура
WHERE масса BETWEEN 0.10 AND 0.12
Причем, крайние значения также входят в набор.
Упражнение 1:
В наших накладных присутствуют покупатели: Иванов, Андреев, Петров, Сидоров. Что выдаст запрос:
SELECT *
FROM Накладные
WHERE Контрагент BETWEEN 'А' AND 'С';
Оператор LIKE:
LIKE применим только к текстовым полям. Оператор используется для поиска подстрок, например в тех случаях, когда точное написание искомого слова неизвестно, или требуется определить записи отвечающие группе условий.
Синтаксис таков:
SELECT *
FROM Номенклатура
WHERE Наименование like “*карта*”
С помощью такого запроса можно получить товары, наименование которых содержит подстроку «карта»: видеокарта, звуковая карта и тд.
Имеются два типа групповых символов используемых с LIKE (в Access):
· символ “?” замещает любой одиночный символ;
· символ “*” замещает последовательность любого числа символов (включая отсутствие). Например '*p*t' будет соответствовать словам 'put', 'posit', или 'opt', но не 'spite'.
Реализация в QBE (конструктор запросов):
Поля, которые требуется выводить, указываются галками в строке «Вывод на экран». Условие прописывается соответственно в строке «условие отбора». Реализация «AND» осуществляется при прописывании условий в одной строке для каждого из условий, реализация «OR» осуществляется при прописывании условий в разных строках для каждого из условий (что отражается в QBE так: Условие отбора … или …)
Сортировка указывается при выставлении значения «по возрастанию» или по убыванию в строке «Сортировка». Вывод определенного числа строк проводится с помощью «Свойств запроса», в меню «Вывод всех полей» указывается нужное количество выводимых полей. В том же меню проходит выбор уникальных записей, при выборе в меню «Уникальные значения» соответствующей строки. «Уникальные строки» в отличие от «Уникальные значения» выводит уникальные строки, при соединении двух таблиц. В SQL реализуется при помощи предиката distinctrow.
Упражнение: Понять разницу между distinct и distinctrow.
Запросы с параметрами:
Под запросом с параметром понимаются запросы, в которых в условии прописывается параметр, задаваемый пользователем каждый раз заново при запуске запроса.
Например:
PARAMETERS [Имя контрагента] Text;
SELECT *
FROM Накладные
WHERE Контрагент = [Имя контрагента];
В случае использования QBE параметр прописывается точно также как обычное условие в строках «условие отбора».
Запросы с Группировкой полей:
Что такое запросы с группировкой? В случае, если нам необходимо посчитать некоторую агрегатную величину по каким-либо полям, используются запросы с группировкой.
Пример запроса:
SELECT Контрагент, SUM (Сумма_RUR)
FROM Накладные
GROUP BY Контрагент
Такой запрос сгруппирует поля из накладной по контрагенту и просуммирует значения покупок из накладных.
Вид запроса в QBE:
Группировка в QBE реализуется при нажатой кнопке со знаком “суммы” 
. На рисунке показан запрос, в котором проводится отбор по контрагенту, задаваемому параметром, осуществляется группировка по этому полю, а также проводится отбор по дате. В данном случае сортировка – лишняя, так как в результате выполнения запроса выведется одна строка.
Упражнение: Почему результатом будет одна строка?
Реализация связи таблиц в QBE:
На следующем рисунке показана реализация запроса по выбору товаров и подсчету оборота по каждому из них для покупок, осуществленных после определенной даты.
Линия, соединяющая поля ндок в таблицах Накладные и Накладные_данные, говорит о том, что в данном запросе осуществлена связь по полю ндок. То есть, товары выберутся как раз из тех документов, которые были выписаны позже даты в условии.
Кроме того, не следует забывать, что в качестве используемых данных необязательно использовать только таблицы, также в качестве источника для данных можно использовать промежуточный запрос.
Реализацию связи таблиц с помощью инструкций SQL изучим на следующих лекциях.
Упражнения:
1. Создать БД продаж с таблицами (таблицы + схема данных):
Накладные
Накладные_данные
Контрагенты
Номенклатура
Города
и тд по необходимости
2. Реализовать запросы:
- Выбрать город, в котором больше всего контрагентов
- Выбрать город с максимальным оборотом по отпуску и приему товара
- По каждому городу вывести оборот в штуках, позициях, деньгах, кг, объеме по отпуску и приему товара отдельно
- По каждому товару вывести оборот в штуках, позициях, деньгах, кг, объеме по отпуску и приему товара отдельно
- Вывести 5 контрагентов с максимальным средним оборотом в уе в день.
