Запрос sql в ms access. Введение

SQL - Урок 4. Выборка данных - оператор SELECT

Итак, в нашей БД forum есть три таблицы: users (пользователи), topics (темы) и posts (сообщения). И мы хотим посмотреть, какие данные в них содержатся. Для этого в SQL существует оператор SELECT . Синтаксис его использования следующий:

SELECT что_выбрать FROM откуда_выбрать;

Вместо "что_выбрать" мы должны указать либо имя столбца, значения которого хотим увидеть, либо имена нескольких столбцов через запятую, либо символ звездочки (*), означающий выбор всех столбцов таблицы. Вместо "откуда_выбрать" следует указать имя таблицы.

Давайте сначала посмотрим все столбцы из таблицы users:

SELECT * FROM users;

Вот и все наши данные, которые мы вносили в эту таблицу. Но предположим, что мы хотим посмотреть только столбец id_user (например, в прошлом уроке, нам надо было для заполнения таблицы topics (темы) знать, какие id_user есть в таблице users). Для этого в запросе мы укажем имя этого столбца:

SELECT id_user FROM users;

Ну, а если мы захотим посмотреть, например, имена и e-mail наших пользователей, то мы перечислим интересующие столбцы через запятую:

SELECT name, email FROM users;

Аналогично, вы можете посмотреть, какие данные содержат и другие наши таблицы. Давайте посмотрим, какие у нас существуют темы:

SELECT * FROM topics;

Сейчас у нас всего 4 темы, а если их будет 100? Хотелось бы, чтобы они выводились, например, по алфавиту. Для этого в SQL существует ключевое слово ORDER BY после которого указывается имя столбца по которому будет происходить сортировка. Синтаксис следующий:

SELECT имя_столбца FROM имя_таблицы ORDER BY имя_столбца_сортировки;

По умолчанию сортировка идет по возрастанию, но это можно изменить, добавив ключевое слово DESC

Теперь наши данные отсортированы в порядке по убыванию.

Сортировку можно производить сразу по нескольким столбцам. Например, следующий запрос отсортирует данные по столбцу topic_name, и если в этом столбце будет несколько одинаковых строк, то в столбце id_author будет осуществлена сортировка по убыванию:

Сравните результат с результатом предыдущего запроса.

Очень часто нам не нужна вся информация из таблицы. Например, мы хотим узнать, какие темы были созданы пользователем sveta (id=4). Для этого в SQL есть ключевое слово WHERE , синтаксис у такого запроса следующий:

Для нашего примера условием является идентификатор пользователя, т.е. нам нужны только те строки, в столбце id_author которых стоит 4 (идентификатор пользователя sveta):

Или мы хотим узнать, кто создал тему "велосипеды":

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

Оператор	Описание
= (равно)	Отбираются значения равные указанному Пример: SELECT * FROM topics WHERE id_author=4; Результат:
> (больше)	Отбираются значения больше указанного Пример: SELECT * FROM topics WHERE id_author>2; Результат:
< (меньше)	Отбираются значения меньше указанного Пример: SELECT * FROM topics WHERE id_author Результат:
>= (больше или равно)	Отбираются значения большие и равные указанному Пример: SELECT * FROM topics WHERE id_author>=2; Результат:
<= (меньше или равно)	Отбираются значения меньшие и равные указанному Пример: SELECT * FROM topics WHERE id_author Результат:
!= (не равно)	Отбираются значения не равные указанному Пример: SELECT * FROM topics WHERE id_author!=1; Результат:
IS NOT NULL	Отбираются строки, имеющие значения в указанном поле Пример: SELECT * FROM topics WHERE id_author IS NOT NULL; Результат:
IS NULL	Отбираются строки, не имеющие значения в указанном поле Пример: SELECT * FROM topics WHERE id_author IS NULL; Результат: Empty set - нет таких строк.
BETWEEN (между)	Отбираются значения, находящиеся между указанными Пример: SELECT * FROM topics WHERE id_author BETWEEN 1 AND 3; Результат:
IN (значение содержится)	Отбираются значения, соответствующие указанным Пример: SELECT * FROM topics WHERE id_author IN (1, 4); Результат:
NOT IN (значение не содержится)	Отбираются значения, кроме указанных Пример: SELECT * FROM topics WHERE id_author NOT IN (1, 4); Результат:
LIKE (соответствие)	Отбираются значения, соответствующие образцу Пример: SELECT * FROM topics WHERE topic_name LIKE "вел%"; Результат: Возможные метасимволы оператора LIKE будут рассмотрены ниже.
NOT LIKE (не соответствие)	Отбираются значения, не соответствующие образцу Пример: SELECT * FROM topics WHERE topic_name NOT LIKE "вел%"; Результат:

Метасимволы оператора LIKE

Поиск с использованием метасимволов может осуществляться только в текстовых полях.

Самый распространенный метасимвол - % . Он означает любые символы. Например, если нам надо найти слова, начинающиеся с букв "вел", то мы напишем LIKE "вел%", а если мы хотим найти слова, которые содержат символы "клуб", то мы напишем LIKE "%клуб%". Например:

Еще один часто используемый метасимвол - _ . В отличие от %, который обозначает несколько или ни одного символа, нижнее подчеркивание обозначает ровно один символ. Например:

Обратите внимание на пробел между метасимволом и "рыб", если его пропустить, то запрос не сработает, т.к. метасимвол _ обозначает ровно один символ, а пробел - это тоже символ.

На сегодня достаточно. В следующем уроке мы научимся составлять запросы к двум и более таблицам. А пока попробуйте самостоятельно составить запросы к таблице posts (сообщения).

Данный урок посвящен SQL запросам к базе данных на VBA Access . Мы рассмотрим, как на VBA осуществляется запросы INSERT, UPDATE, DELETE к базе данных, а также научимся получать конкретное значение из запроса SELECT.

Те, кто программируют на VBA Access и работая при этом с базой данных SQL сервера, очень часто сталкиваются с такой простой и нужной задачей как посыл SQL запроса к базе данных, будь то INSERT, UPDATE или простой SQL запрос SELECT . А так как мы начинающие программисты мы тоже должны уметь это делать, поэтому сегодня займемся именно этим.

Мы уже затрагивали тему получения данных с SQL сервера, где как раз на VBA писали код для получения этих данных, например в статье про Выгрузку данных в текстовый файл из MSSql 2008 или также немного затрагивали в материале Выгрузка данных из Access в шаблон Word и Excel , но так или иначе там мы рассматривали это поверхностно, а сегодня предлагаю поговорить об этом чуть более подробней.

Примечание! Все примеры ниже рассмотрены с использованием ADP проекта Access 2003 и базы данных MSSql 2008. Если Вы не знаете что вообще такое ADP проект то это мы рассматривали в материале Как создать и настроить ADP проект Access

Исходные данные для примеров

Допустим, у нас есть таблица test_table, которая будет содержать номера и названия месяцев в году (запросы выполнены с использованием Management Studio )

CREATE TABLE .( NOT NULL, (50) NULL) ON GO

Как я уже сказал, мы будем использовать ADP проект, настроенный на работу с MS SQL 2008, в котором я создал тестовую форму и добавил кнопку start с подписью «Выполнить» , которая нам понадобится для тестирования нашего кода, т.е. весь код мы будем писать в обработчике события «Нажатие кнопки ».

Запросы к базе INSERT, UPDATE, DELETE на VBA

Чтобы долго не тянуть сразу приступим, допустим, нам нужно добавить строку в нашу тестовую таблицу (код прокомментирован )/

Private Sub start_Click() "Объявляем переменную для хранения строки запроса Dim sql_query As String "Записываем в нее нужный нам запрос sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "Июнь")" "Выполняем его DoCmd.RunSQL sql_query End Sub

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

Как видим, данные вставились.

Для того чтобы удалить одну строку пишем вот такой код.

Private Sub start_Click() "Объявляем переменную для хранения строки запроса Dim sql_query As String "Записываем в нее запрос на удаление sql_query = "DELETE test_table WHERE id = 6" "Выполняем его DoCmd.RunSQL sql_query End Sub

Если мы проверим, то увидим, что нужная строка удалилась.

Для обновления данных записываем в переменную sql_query запрос update, надеюсь, смысл понятен.

Запрос SELECT к базе на VBA

Здесь дела обстоят чуть интересней, чем с остальными конструкциями SQL.

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

Private Sub start_Click() "Объявляем переменные "Для набора записей из базы Dim RS As ADODB.Recordset "Строка запроса Dim sql_query As String "Строка для вывода итоговых данных в сообщении Dim str As String "Создаем новый объект для записей set RS = New ADODB.Recordset "Строка запроса sql_query = "SELECT id, name_mon FROM test_table" "Выполняем запрос с использованием текущих настроек подключения проекта RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Циклом перебираем записи While Not (RS.EOF) "Заполняем переменную для вывода сообщения str = str & RS.Fields("id") & "-" & RS.Fields("name_mon") & vbnewline "переход к следующей записи RS.MoveNext Wend "Вывод сообщения msgbox str End Sub

Здесь мы уже используем циклы VBA Access для того чтобы перебрать все значения в нашем наборе записей.

Но, достаточно часто бывает необходимо получить не все значения из набора записей, а всего лишь одно, например, название месяца по его коду. И для этого использовать цикл как-то накладно, поэтому мы можем просто написать запрос, который вернет всего одно значение и обращаться именно к нему, например, получим название месяца по коду 5

Private Sub start_Click() "Объявляем переменные "Для набора записей из базы Dim RS As ADODB.Recordset "Строка запроса Dim sql_query As String "Строка для вывода итогового значения Dim str As String "Создаем новый объект для записей set RS = New ADODB.Recordset "Строка запроса sql_query = "SELECT name_mon FROM test_table WHERE id = 5" "Выполняем запрос с использованием текущих настроек подключения проекта RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Получаем наше значение str = RS.Fields(0) msgbox str End Sub

Для универсальности здесь мы уже обратились не по имени ячейки, а по ее индексу, т.е. 0, а это самое первое значение в Recordset , в итоге мы получили значение «Май» .

Как видите, все достаточно просто. Если Вам достаточно часто требуется получать конкретное значение из базы (как в последнем примере ), то рекомендую вывести весь код в отдельную функцию (Как написать функцию на VBA Access 2003) с одним входящим параметром, например, код месяца (если рассматривать наш пример ) и просто, где необходимо вывести это значение, вызывать нужную нам функцию с нужным параметром и все, этим мы значительно уменьшим код VBA и улучшим восприятие нашей программы.

На сегодня это все. Удачи!

Описание учебного проекта "Магазин"

Схема связей таблиц

Описание таблиц

m_category - категории товаров

m_income - приход товаров

m_outcome - расход товаров

m_product - справочник, описание товаров

m_supplier - справочник ; информация о поставщиках

m_unit - справочник; единицы измерения

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

Microsoft Access 2003 или новей.

Запрос SQL в MS Access. Начало

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

Для перехода в режим редактирования полей таблицы, на верхней панели выберите режим Конструктора:

Для вывода результата запроса SQL, щелкните дважды на названии запроса на панели слева:

Для того чтобы перейти в режим редактирования запроса SQL , на верхней панели выберите режим SQL:

Запрос SQL. Примеры в MS Access. SELECT: 1-10

В запросе SQL оператор SELECT используется для осуществления выборки из таблиц базы данных.

Запрос SQL Q001. Пример запроса SQL для получения только нужных полей в нужной последовательности:

SELECT dt, product_id, amount

FROM m_income;

Запрос SQL Q002. В этом примере запроса SQL символ звездочки (*) использован для вывода всех столбцов таблицы m_product, иначе говоря, для получения всех полей отношения m_product:

SELECT *
FROM m_product;

Запрос SQL Q003. Инструкция DISTINCT используется для исключения повторяющихся записей и получения множества уникальных записей:

SELECT DISTINCT product_id

FROM m_income;

Запрос SQL Q004. Инструкция ORDER BY используется для сортировки (упорядочивания) записей по значениям определенного поля. Имя поля указывается за инструкцией ORDER BY:

SELECT *
FROM m_income

ORDER BY price;

Запрос SQL Q005. Инструкция ASC используется как дополнение к инструкции ORDER BY и служит для определения сортировки по возрастанию. Инструкция DESC используется как дополнение к инструкции ORDER BY и служит для определения сортировки по убыванию. В случае, когда ни ASC, ни DESC не указаны, подразумевается наличие ASC (default):

SELECT *
FROM m_income

ORDER BY dt DESC , price;

Запрос SQL Q006. Для отбора необходимых записей из таблицы пользуются различными логическими выражениями, которые выражают условие отбора. Логическое выражение приводится после инструкции WHERE. Пример получения из таблицы m_income всех записей , для которых значение amount больше 200:

SELECT *
FROM m_income

WHERE amount>200;

Запрос SQL Q007. Для выражения сложных условий пользуются логическими операциями AND (конъюнкция), OR (дизъюнкция) и NOT (логическое отрицание). Пример получения из таблицы m_outcome всех записей, для которых значение amount равно 20 и значение price больше или равно 10:

Price

FROM m_outcome
WHERE amount=20 AND price>=10;

Запрос SQL Q008. Для объединения данных двух или более таблиц пользуются инструкциями INNER JOIN, LEFT JOIN, RIGHT JOIN. В следующем примере извлекаются поля dt, product_id, amount, price из таблицы m_income и поле title из таблицы m_product. Запись таблицы m_income соединяется с записью таблицы m_product при равенстве значения m_income.product_id значению m_product.id:

ON m_income.product_id=m_product.id;

Запрос SQL Q009. В этом запросе SQL нужно обратить внимание на две вещи: 1) искомый текст заключен в одинарные кавычки ("); 2) дата приведена в формате #Месяц/День/Год#, что верно для MS Access. В других системах формат написания даты может быть другим. Пример вывода информации о поступлении молока 12-го июня 2011 года. Обратите внимание на формат даты #6/12/2011#:

SELECT dt, product_id, title, amount, price

FROM m_income INNER JOIN m_product

WHERE title="Молоко" And dt=#6/12/2011#;

Запрос SQL Q010. Инструкция BETWEEN используется для проверки принадлежности некоторому диапазону значений. Пример запроса SQL, выводящий информацию о товарах, поступивших между 1-м и 30-м июнем 2011 года:

SELECT *
FROM m_income INNER JOIN m_product

ON m_income.product_id=m_product.id
WHERE dt BETWEEN #6/1/2011# And #6/30/2011#;

Запрос SQL. Примеры в MS Access. SELECT: 11-20

Один запрос SQL можно вкладывать в другой. Подзапрос - есть не что иное , как запрос внутри запроса. Обычно, подзапрос используется в конструкции WHERE. Но возможны и другие способы использования подзапросов.

Запрос Q011. Выводится информация о товарах из таблицы m_product, коды которых есть и в таблице m_income:

SELECT *
FROM m_product

WHERE id IN (SELECT product_id FROM m_income);

Запрос Q012. Выводится список товаров из таблицы m_product, кодов которых нет в таблице m_outcome:

SELECT *
FROM m_product

WHERE id NOT IN (SELECT product_id FROM m_outcome);

Запрос Q013. В этом запросе SQL выводится уникальный список кодов и названий товаров, коды которых есть в таблице m_income, но которых нет в таблице m_outcome:

SELECT DISTINCT product_id, title

FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE product_id NOT IN (SELECT product_id FROM m_outcome);

Запрос Q014. Выводится из таблицы m_category уникальный список категорий, названия которых начинаются на букву М:

SELECT DISTINCT title

FROM m_product
WHERE title LIKE "М*";

Запрос Q015. Пример выполнения арифметических операций над полями в запросе и переименования полей в запросе (alias). В этом примере для каждой записи о расходе товара подсчитываются сумма расхода = количество*цена и размер прибыли, при предположении, что прибыль составляет 7 процентов от суммы продаж:

amount*price/100*7 AS profit
FROM m_outcome;

Запрос Q016. Проанализировав и упростив арифметические операции , можно увеличить скорость выполнения запроса:

SELECT dt, product_id, amount, price, amount*price AS outcome_sum,

outcome_sum*0.07 AS profit
FROM m_outcome;

Запрос Q017. При помощи инструкции INNER JOIN можно объединить данные нескольких таблиц. В следующем примере, в зависимости от значения ctgry_id, каждой записи таблицы m_income, сопоставляется название категории из таблицы m_category, к которой принадлежит товар:

SELECT c.title, b.title, dt, amount, price, amount*price AS income_sum

FROM (m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id)
INNER JOIN m_category AS c ON b.ctgry_id=c.id
ORDER BY c.title, b.title;

Запрос Q018. Такие функции как SUM - сумма, COUNT - количество, AVG – среднее арифметическое значение, MAX – максимальное значение, MIN – минимальное значение называются агрегатными функциями. Они принимают множество значений , и после их обработки возвращают единственное значение. Пример подсчета суммы произведения полей amount и price при помощи агрегатной функции SUM:

SELECT SUM(amount*price) AS Total_Sum

FROM m_income;

Запрос Q019. Пример использования нескольких агрегатных функций:

SELECT Sum(amount) AS Amount_Sum, AVG(amount) AS Amount_AVG,

MAX(amount) AS Amount_Max, Min(amount) AS Amount_Min,
Count(*) AS Total_Number
FROM m_income;

Запрос Q020. В этом примере подсчитана сумма всех товаров с кодом 1, оприходованных в июне 2011 года:

SELECT Sum(amount*price) AS income_sum

FROM m_income
WHERE product_id=1 AND dt BETWEEN #6/1/2011# AND #6/30/2011#;.

Запрос Q021. Следующий запрос SQL вычисляет на какую сумму было продано товаров, имеющих код 4 или 6:

SELECT Sum(amount*price) as outcome_sum

FROM m_outcome
WHERE product_id=4 OR product_id=6;

Запрос Q022. Вычисляется на какую сумму было продано 12 июня 2011 года товаров, имеющих код 4 или 6:

SELECT Sum(amount*price) AS outcome_sum

FROM m_outcome
WHERE (product_id=4 OR product_id=6) AND dt=#6/12/2011#;

Запрос Q023. Задача такова. Вычислить на какую общую сумму было оприходовано товаров категории "Хлебобулочные изделия".

Для решения этой задачи нужно оперировать тремя таблицами: m_income, m_product и m_category, потому что:

- количество и цена оприходованных товаров хранятся в таблице m_income;
- код категории каждого товара хранится в таблице m_product;
- название категории title хранится в таблице m_category.

Для решения данной задачи воспользуемся следующим алгоритмом:

- определение кода категории "Хлебобулочные изделия" из таблицы m_category посредством подзапроса;
- соединение таблиц m_income и m_product для определения категории каждого оприходованного товара;
- вычисление суммы прихода(= количество*цена) для товаров, код категории которых равен коду, определенному вышеуказанным подзапросом.
SELECT
FROM m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (SELECT id FROM m_category WHERE title="Хлебобулочные изделия");

Запрос Q024. Задачу вычисления общей суммы оприходованных товаров категории "Хлебобулочные изделия" решим следующим алгоритмом:
- каждой записи таблицы m_income, в зависимости от значения его product_id, из таблицы m_category, сопоставить название категории ;
- выделить записи, для которых категория равна "Хлебобулочные изделия";
- вычислить сумму прихода = количество*цена.

FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)

WHERE c.title="Хлебобулочные изделия";

Запрос Q025. В этом примере вычисляется сколько наименований товаров было израсходовано:

SELECT COUNT(product_id) AS product_cnt

FROM (SELECT DISTINCT product_id FROM m_outcome) AS t;

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

SELECT title, SUM(amount*price) AS outcome_sum

FROM m_product AS a INNER JOIN m_outcome AS b
ON a.id=b.product_id
GROUP BY title;

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

SELECT c.title, SUM(amount*price) AS outcome_sum,

ROUND(AVG(amount*price),2) AS outcome_sum_avg
FROM (m_product AS a INNER JOIN m_outcome AS b ON a.id=b.product_id)
INNER JOIN m_category AS c ON a.ctgry_id=c.id
GROUP BY c.title;

Запрос Q028. Вычисляется для каждого товара общее и среднее количество его поступлений и выводит информацию о товарах, общее количество поступления которых не менее 500:

SELECT product_id, SUM(amount) AS amount_sum,

Round(Avg(amount),2) AS amount_avg
FROM m_income
GROUP BY product_id
HAVING Sum(amount)>=500;

Запрос Q029. В этом запросе вычисляется для каждого товара сумма и среднее значение его поступлений, осуществленных во втором квартале 2011 года. Если общая сумма прихода товара не менее 1000, то отображается информация об этом товаре:

SELECT title, SUM(amount*price) AS income_sum

FROM m_income a INNER JOIN m_product b ON a.product_id=b.id
WHERE dt BETWEEN #4/1/2011# AND #6/30/2011#
GROUP BY title
HAVING SUM(amount*price)>=1000;

Запрос Q030. В некоторых случаях нужно сопоставлять каждой записи некоторой таблицы каждую запись другой таблицы ; что называется декартовым произведением. Таблица, образующаяся в результате такого соединения, называется таблицей Декарта. Например, если некоторая таблица А имеет 100 записей и таблица В имеет 15 записей, то их таблица Декарта будет состоять из 100*15=150 записей. Следующий запрос соединяет каждую запись таблицы m_income с каждой записью таблицы m_outcome:
FROM m_income, m_outcome;

Запрос Q031. Пример группирования записей по двум полям. Следующий запрос SQL вычисляет по каждому поставщику сумму и количество поступивщих от него товаров:

SUM(amount*price) AS income_sum

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

SELECT supplier_id, product_id, SUM(amount) AS amount_sum,

GROUP BY supplier_id, product_id;

Запрос Q033. В этом примере два вышеприведенных запроса (q031 и q032) использованы как подзапросы. Результаты этих запросов методом LEFT JOIN объединены в один отчет. Следующий запрос выводит отчет о количестве и сумме поступивщих и реализованных продуктов по каждому поставщику. Следует обратить внимание на то, что если какой-то товар уже поступил, но еще не реализован, то клетка outcome_sum для этой записи будет пустой. , что данный запрос служит только примером использования относительно сложных запросов в качестве подзапроса. Производительность данного запроса SQL при большом объеме данных сомнительна:

SELECT *
FROM

SUM(amount*price) AS income_sum

ON a.product_id=b.id GROUP BY supplier_id, product_id) AS a
LEFT JOIN
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b
ON a.product_id=b.id GROUP BY supplier_id, product_id) AS b
ON (a.product_id=b.product_id) AND (a.supplier_id=b.supplier_id);

Запрос Q034. В этом примере два вышеприведенных запроса (q031 и q032) использованы как подзапросы. Результаты этих запросов методом RIGTH JOIN объединены в один отчет. Следующий запрос выводит отчет о сумме платежей каждого клиента по использованным им платежным системам и сумме сделанных им инвестиций. Следующий запрос выводит отчет о количестве и сумме поступивщих и реализованных продуктов по каждому поставщику. Следует обратить внимание на то, что если какой-то товар уже реализован, но еще не поступил, то клетка income_sum для этой записи будет пустой. Наличие таких пустых клеток является показателем ошибки в учете продаж, так как до продажы сначала необходимо, чтобы соответствующий товар поступил:

SELECT *
FROM

(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS income_sum
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS a
RIGHT JOIN
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS b
ON (a.supplier_id=b.supplier_id) AND (a.product_id=b.product_id);

Запрос Q035. Выводится отчет о сумме доходов и расходов по продуктам. Для этого создается список продуктов по таблицам m_income и m_outcome, затем для каждого продукта из этого списка вычисляется сумма его приходов по таблице m_income и сумма его расходов по таблице m_outcome:

SELECT product_id, SUM(in_amount) AS income_amount,

SUM(out_amount) AS outcome_amount
FROM
(SELECT product_id, amount AS in_amount, 0 AS out_amount
FROM m_income
UNION ALL
SELECT product_id, 0 AS in_amount, amount AS out_amount
FROM m_outcome) AS t
GROUP BY product_id;

Запрос Q036. Функция EXISTS возвращает значение TRUE, если переданное ей множество содержит элементы. Функция EXISTS возвращает значение FALSE , если переданное ей множество пустое, то есть не содержит элементов. Следующий запрос выводит коды товаров, которые содержатся как в таблице m_income, так и в таблице m_outcome:

SELECT DISTINCT product_id

FROM m_income AS a
WHERE EXISTS(SELECT product_id FROM m_outcome AS b

Запрос Q037. Выводятся коды товаров, которые содержатся как в таблице m_income, так и в таблице m_outcome:

SELECT DISTINCT product_id

FROM m_income AS a
WHERE product_id IN (SELECT product_id FROM m_outcome)

Запрос Q038. Выводятся коды товаров, которые содержатся как в таблице m_income, но не содержатся в таблице m_outcome:

SELECT DISTINCT product_id

FROM m_income AS a
WHERE NOT EXISTS(SELECT product_id FROM m_outcome AS b
WHERE b.product_id=a.product_id);

Запрос Q039. Выводится список товаров, сумма продаж которых максимальная. Алгоритм таков. Для каждого товара вычисляется сумма его продаж. Затем, определяется максимум этих сумм. Затем, для каждого товара снова вычисляется сумма его продаж, и выводятся код и сумма продаж товаров , сумма продаж которых равна максимальной:

SELECT product_id, SUM(amount*price) AS amount_sum

FROM m_outcome
GROUP BY product_id
HAVING SUM(amount*price) = (SELECT MAX(s_amount)
FROM (SELECT SUM(amount*price) AS s_amount FROM m_outcome GROUP BY product_id));

Запрос Q040. Зарезервированное слово IIF (условный оператор) используется для оценки логического выражения и выполнения того или иного действия в зависимости от результата (TRUE или FALSE). В следующем примере поставка товара считается «малой», если количество меньше 500. В противном случае, то есть количество поступления больше или равно 500, поставка считается «большой»:

SELECT dt, product_id, amount,

IIF(amount FROM m_income;

Запрос SQL Q041. В случае, когда оператор IIF используется несколько раз, удобнее заменить его оператором SWITCH. Оператор SWITCH (оператор множественного выбора) используется для оценки логического выражения и выполнения того или иного действия в зависимости от результата. В следующем примере поставленная партия считается «малой», если количество товара в партии меньше 500. В противном случае, то есть если количество товара больше или равно 500, партия считается «большой»:

SELECT dt, product_id, amount,

SWITCH(amount =500,"большая") AS mark
FROM m_income;

Запрос Q042. В следующем запросе если количество товара в поступившей партии меньше 300, то партия считается «малой». В противном случае, то есть если условие amount SELECT dt, product_id, amount,
IIF(amount IIF(amount FROM m_income;

Запрос SQL Q043. В следующем запросе если количество товара в поступившей партии меньше 300, то партия считается «малой». В противном случае, то есть если условие amount SELECT dt, product_id, amount,
SWITCH(amount amount amount>=1000,"большая") AS mark
FROM m_income;

Запрос SQL Q044. В следующем запросе продажи разделяются на три группы: малые (до 150), средние (от150 до 300), большие (300 и более). Далее, для каждой группы вычисляется итоговая сумма:

SELECT Category, SUM(outcome_sum) AS Ctgry_Total

FROM (SELECT amount*price AS outcome_sum,
IIf(amount*price IIf(amount*price FROM m_outcome) AS t
GROUP BY Category;

Запрос SQL Q045. Функция DateAdd используется для прибавления дней, месяцев или лет к данной дате и получения новой даты. Следующий запрос:
1) к дате из поля dt прибавляет 30 дней и отображает новую дату в поле dt_plus_30d;
2) к дате из поля dt прибавляет 1 месяц и отображает новую дату в поле dt_plus_1m:

SELECT dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m

FROM m_income;

Запрос SQL Q046. Функция DateDiff предназначена для вычисления разницы между двумя датами в различных единицах (днях, месяцах или годах). Следующий запрос вычисляет разницу между датой в поле dt и текущей датой в днях , месяцах и годах:

SELECT dt, DateDiff("d",dt,Date()) AS last_day,

DateDiff("m",dt,Date()) AS last_months,
DateDiff("yyyy",dt,Date()) AS last_years
FROM m_income;

Запрос SQL Q047. Вычисляются количество дней со дня поступления товара (таблица m_income) до текущей даты с помощью функции DateDiff и сопоставляется срок годности (таблица m_product):

DateDiff("d",dt,Date()) AS last_days
FROM m_income AS a INNER JOIN m_product AS b
ON a.product_id=b.id;

Запрос SQL Q048. Вычисляются количество дней со дня поступления товара до текущей даты, затем проверяется превышает ли это количество срок годности:

SELECT a.id, product_id, dt, lifedays,

DateDiff("d",dt,Date()) AS last_days, IIf(last_days>lifedays,"Да","Нет") AS date_expire
FROM m_income a INNER JOIN m_product b
ON a.product_id=b.id;

Запрос SQL Q049. Вычисляются количество месяцев со дня поступления товара до текущей даты. В столбце month_last1 вычисляется абсолютное количество месяцев, в столбце month_last2 вычисляется количество полных месяцев:

SELECT dt, DateDiff("m",dt,Date()) AS month_last1,

DateDiff("m",dt,Date())-iif(day(dt)>day(date()),1,0) AS month_last2
FROM m_income;

Запрос SQL Q050. Выводится поквартальный отчет о количестве и сумме оприходованных товаров за 2011 год:

SELECT kvartal, SUM(outcome_sum) AS Total

FROM (SELECT amount*price AS outcome_sum, month(dt) AS m,
SWITCH(m =10,4) AS kvartal
FROM m_income WHERE year(dt)=2011) AS t
GROUP BY kvartal;

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

SELECT product_id, SUM(in_sum) AS income_sum, SUM(out_sum) AS outcome_sum

FROM (SELECT product_id, amount*price as in_sum, 0 as out_sum
from m_income
UNION ALL
SELECT product_id, 0 as in_sum, amount*price as out_sum
from m_outcome) AS t
GROUP BY product_id
HAVING SUM(in_sum)
Запрос Q052. Нумерацию строк, возвращаемых запросом, реализуют по-разному. Например, можно перенумеровать строки отчета, подготовленного в MS Access, средствами самого MS Access. Перенумеровать можно и с использованием языков программирования , например, VBA или PHP. Однако иногда это необходимо сделать в самом запросе SQL. Итак, следующий запрос пронумерует строки таблицы m_income в соответствии с порядком возрастания значений поля ID:

SELECT COUNT(*) as N, b.id, b.product_id, b.amount, b.price

FROM m_income a INNER JOIN m_income b ON a.id GROUP BY b.id, b.product_id, b.amount, b.price;

Запрос Q053. Выводится пятерка лидеров среди продуктов по сумме продаж. Вывод первых пяти записей осуществляется с помощью инструкции TOP:

SELECT TOP 5, product_id, sum(amount*price) AS summa

FROM m_outcome
GROUP BY product_id
ORDER BY sum(amount*price) DESC;

Запрос Q054. Выводится пятерка лидеров среди продуктов по сумме продаж, и нумерует строки в результате:

SELECT COUNT(*) AS N, b.product_id, b.summa

FROM

FROM m_outcome GROUP BY product_id) AS a
INNER JOIN
(SELECT product_id, sum(amount*price) AS summa,
summa*10000000+product_id AS id
FROM m_outcome GROUP BY product_id) AS b
ON a.id>=b.id
GROUP BY b.product_id, b.summa
HAVING COUNT(*)ORDER BY COUNT(*);

Запрос Q055. Следующий SQL-запрос показывает использование математических функций COS, SIN, TAN, SQRT, ^ и ABS в MS Access SQL:

SELECT (select count(*) from m_income) as N, 3.1415926 as pi, k,

2*pi*(k-1)/N as x, COS(x) as COS_, SIN(x) as SIN_, TAN(x) as TAN_,
SQR(x) as SQRT_, x^3 as "x^3", ABS(x) as ABS_
FROM (SELECT COUNT(*) AS k
FROM m_income AS a INNER JOIN m_income AS b ON a.idGROUP BY b.id) t;

Запрос SQL. Примеры в MS Access. UPDATE: 1-10

Запрос U001. Следующий SQL-запрос на изменение увеличивает на 10% цены на товары с кодом 3 в таблице m_income:

UPDATE m_income SET price = price*1.1

WHERE product_id=3;

Запрос U002. Следующий SQL-запрос на обновление увеличивает в таблице m_income на 22 единицы количество всех товаров , названия которых начинаются со слова "Масло":

UPDATE m_income SET amount = amount+22

WHERE product_id IN (SELECT id FROM m_product WHERE title LIKE "Масло*");

Запрос U003. Следующий SQL-запрос на изменение в таблице m_outcome снижает на 2 процента цены на все товары, производителем которых является ООО "Сладкое":

UPDATE m_outcome SET price = price*0.98

WHERE product_id IN
(SELECT a.id FROM m_product a INNER JOIN m_supplier b
ON a.supplier_id=b.id WHERE b.title="ООО "Сладкое"");.

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

SQL : ИЗВЛЕЧЕНИЕ ДАННЫХ - команда SELECT

Цель работы:

познакомиться с SQL-инструкциями;
научиться в Access создавать простейшие SQL-запросы с помощью команды SELECT;

· использование операторов IN, BETWEEN, LIKE, IS NULL.

Задание №1. Создать запрос на выборку в режиме SQL всех значений полей ИМЯ и ФАМИЛИЯ из таблицы СТУДЕНТЫ.

SELECT ИМЯ, ФАМИЛИЯ

FROM СТУДЕНТЫ;

Задание №2 . Создать запрос на выборку в режиме SQL всех столбцов таблицы СТУДЕНТЫ.

SELECT *

FROM СТУДЕНТЫ;

Задание №3. Создать запрос на выборку в режиме SQL названий городов, где проживают студенты, сведения о которых находятся в таблице ЛИЧНЫЕ ДАННЫЕ.

SELECT DISTINCT ГОРОД

FROM [ЛИЧНЫЕ ДАННЫЕ];

Задание №4. Создать запрос на выборку в режиме SQL, выполняющий выборку имен всех студентов с фамилией Иванов, сведения о которых находятся в таблице СТУДЕНТЫ.

SELECT ФАМИЛИЯ, ИМЯ

FROM СТУДЕНТЫ

WHERE ФАМИЛИЯ="Иванов";

Задание №5 . Создать запрос на выборку в режиме SQL для получения имен и фамилий студентов, обучающихся в группе УИТ-22 на бюджетной форме обучения.

SELECT ФАМИЛИЯ, ИМЯ

FROM СТУДЕНТЫ

WHERE ГРУППА="УИТ-22" AND БЮДЖЕТ=true;

Задание №6 . Создать запрос в режиме SQL. на выборку из таблицы СДАЧА ЭКЗАМЕНОВ сведении о студентах, имеющих оценки только 4 и 5.

SELECT *

FROM [ СДАЧА ЭКЗАМЕНОВ ]

WHERE ОЦЕНКА IN (4,5);

Задание №7. Coздать зanpoc и режиме SQL на выборку сведений о студентах, имеющих экзаменационную оценку 3 по предмету ИОСУ.

SELECT *

FROM [ СДАЧА ЭКЗАМЕНОВ ]

WHERE ПРЕДМЕТ =" ИОСУ " And ОЦЕНКА Not In (4,5);

Задание №8. Создать запрос в режиме SQL на выборку записей о предметах, часы которых находятся в пределах между 100 и 130.

SELECT *

FROM ПРЕДМЕТЫ

WHERE ЧАСЫ BETWEEN 100 AND 130;

Задание №9. Создать запрос в режиме SQL на выборку из таблицы СТУДЕНТЫ сведений о студентах, фамилии которых начинаются, например, на букву «С».

SELECT *

FROM СТУДЕНТЫ

WHERE ФАМИЛИЯ LIKE " С *";

Вывод: В ходе лабораторной работы познакомились с SQL-инструкциями, научились в Access создавать простейшие SQL-запросы с помощью команды SELECT используя операторы IN, BETWEEN, LIKE.

Примеры запросов SQL могут использованы для изучения и практикума написания запросов SQL в MS Access.

Один запрос SQL можно вкладывать в другой. Подзапрос - есть не что иное, как запрос внутри запроса. Обычно, подзапрос используется в конструкции WHERE. Но возможны и другие способы использования подзапросов.

Запрос Q011. Выводится информация о товарах из таблицы m_product, коды которых есть и в таблице m_income:

SELECT *
FROM m_product
WHERE id IN (SELECT product_id FROM m_income);

Запрос Q012. Выводится список товаров из таблицы m_product, кодов которых нет в таблице m_outcome:

SELECT *
FROM m_product
WHERE id NOT IN (SELECT product_id FROM m_outcome);

SELECT DISTINCT product_id, title
FROM m_income INNER JOIN m_product
ON m_income.product_id=m_product.id
WHERE product_id NOT IN (SELECT product_id FROM m_outcome);

Запрос Q014. Выводится из таблицы m_category уникальный список категорий, названия которых начинаются на букву М:

SELECT DISTINCT title
FROM m_product
WHERE title LIKE "М*";

amount*price/100*7 AS profit
FROM m_outcome;

Запрос Q016. Проанализировав и упростив арифметические операции, можно увеличить скорость выполнения запроса:

SELECT dt, product_id, amount, price, amount*price AS outcome_sum,
outcome_sum*0.07 AS profit
FROM m_outcome;

SELECT c.title, b.title, dt, amount, price, amount*price AS income_sum
FROM (m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id)
INNER JOIN m_category AS c ON b.ctgry_id=c.id
ORDER BY c.title, b.title;

Запрос Q018. Такие функции как SUM - сумма, COUNT - количество, AVG – среднее арифметическое значение, MAX – максимальное значение, MIN – минимальное значение называются агрегатными функциями. Они принимают множество значений, и после их обработки возвращают единственное значение. Пример подсчета суммы произведения полей amount и price при помощи агрегатной функции SUM.