Ознакомление с SQL-инструкциями; создание простейших SQL-запросов в Access с помощью команды SELECT используя операторы IN, BETWEEN, LIKE. Язык запросов SQL Синтаксис sql запросов в 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);
Запрос 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 процентов от суммы продаж:
Price, amount*price AS outcome_sum,
amount*price/100*7 AS profit
FROM m_outcome;
Запрос Q016. Проанализировав и упростив арифметические операции, можно увеличить скорость выполнения запроса:
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
FROM m_income
WHERE product_id=1 AND dt BETWEEN #6/1/2011# AND #6/30/2011#;.
Запрос Q021. Следующий запрос SQL вычисляет на какую сумму было продано товаров, имеющих код 4 или 6:
SELECT
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 для определения категории каждого оприходованного товара;
- вычисление суммы прихода(= количество*цена) для товаров, код категории которых равен коду, определенному вышеуказанным подзапросом.
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, сопоставить название категории;
- выделить записи, для которых категория равна "Хлебобулочные изделия";
- вычислить сумму прихода = количество*цена.
SELECT Sum(amount*price) AS income_sum
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:
SELECT *FROM m_income, m_outcome;
Запрос Q031. Пример группирования записей по двум полям. Следующий запрос SQL вычисляет по каждому поставщику сумму и количество поступивщих от него товаров:
SUM(amount*price) AS income_sum
Запрос Q032. Пример группирования записей по двум полям. Следующий запрос вычисляет для каждого поставщика сумму и количество его продуктов, проданных нами:
SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_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<500,"малая","большая") AS mark
FROM m_income;
Запрос SQL Q041. В случае, когда оператор IIF используется несколько раз, удобнее заменить его оператором SWITCH. Оператор SWITCH (оператор множественного выбора) используется для оценки логического выражения и выполнения того или иного действия в зависимости от результата. В следующем примере поставленная партия считается «малой», если количество товара в партии меньше 500. В противном случае, то есть если количество товара больше или равно 500, партия считается «большой»:
SELECT dt, product_id, amount,
SWITCH(amount<500,"малая",amount>=500,"большая") AS mark
FROM m_income;
Запрос Q042. <300 не выполняется, то проверяется является ли количество товаров в партии меньше 500. Если размер партии меньше 500, то она считается «средней». В противном случае партия считается «большой»:
SELECT dt, product_id, amount,
IIF(amount<300,"малая",
IIF(amount<1000,"средняя","большая")) AS mark
FROM m_income;
Запрос SQL Q043. В следующем запросе если количество товара в поступившей партии меньше 300, то партия считается «малой». В противном случае, то есть если условие amount<300 не выполняется, то проверяется является ли количество товаров в партии меньше 500. Если размер партии меньше 500, то она считается «средней». В противном случае партия считается «большой»:
SELECT dt, product_id, amount,
SWITCH(amount<300,"малая",
amount<1000,"средняя",
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<150,"малая",
IIf(amount*price<300,"средняя","большая")) AS Category
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<4,1,m<7,2,m<10,3,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 Запрос Q052.
Нумерацию строк, возвращаемых запросом, реализуют по-разному. Например, можно перенумеровать строки отчета, подготовленного в MS Access, средствами самого MS Access. Перенумеровать можно и с использованием языков программирования, например, VBA или PHP. Однако иногда это необходимо сделать в самом запросе SQL. Итак, следующий запрос пронумерует строки таблицы m_income в соответствии с порядком возрастания значений поля ID: SELECT COUNT(*) as N, b.id, b.product_id, b.amount, b.price Запрос Q053.
Выводится пятерка лидеров среди продуктов по сумме продаж. Вывод первых пяти записей осуществляется с помощью инструкции TOP: SELECT TOP 5, product_id, sum(amount*price) AS summa Запрос Q054.
Выводится пятерка лидеров среди продуктов по сумме продаж, и нумерует строки в результате: SELECT COUNT(*) AS N, b.product_id, b.summa Запрос Q055.
Следующий SQL-запрос показывает использование математических функций COS, SIN, TAN, SQRT, ^ и ABS в MS Access SQL: SELECT (select count(*) from m_income) as N, 3.1415926 as pi, k, На самом деле любой запрос в MS Access реализуется с помощью языка SQL. Большинство запросов можно построить, пользуясь средствами, которые Access предоставляет в режиме конструктора, и в этом случае они будут храниться в виде инструкций SQL. Но некоторые типы запросов можно построить, только используя язык SQL. Синтаксис инструкции SELECT в MS Access.
Ядром языка SQL является инструкция SELECT. Она используется для отбора строк и столбцов из таблиц базы данных и содержит пять основных предложений. В общем случае ее синтаксис можно представить в следующем виде: FROM <список-таблиц> В MS Access реализованы важные средства расширения языка: инструкция TRANSFORM, позволяющая строить перекрестные запросы; предложение IN, с помощью которого задается связь с удаленной базой данных или указываются имена столбцов перекрестного запроса, и др. Синтаксис: [[{имя-таблицы | имя-запроса-на-выборку | псевдоним}!]].][имя-поля] Имя-таблицы, имя-запроса-на-выборку
и псевдоним,
уточняющий имя поля, обязательно должны присутствовать в предложении FROM запроса. Если таблица или запрос имеет псевдоним, необходимо использовать именно его, а не реальное имя таблицы или запроса. Первую часть имени (включая точку) можно опустить, если поле имеется только в одной из таблиц предложения FROM. При определении списка полей использование символа "*" вместо имени поля указывает, что нужно отобразить все столбцы данной таблицы. Если в качестве списка полей использован символ "*", то отбираются все столбцы всех таблиц, указанных в предложении FROM. Пример 10.5.
Следующее выражение задает ноле из таблицы [Товары], имя которого Наименование: Товары. Наименование Предложение FROM.
Задает таблицы или запросы, служащие источниками данных для создаваемого запроса. Синтаксис: FROM {имя-таблицы [[AS
] псевдоним] I имя-запроса-на-выборку [ псевдоним] I <таблица-объединения>] ... [IN
сспецификация источника>] где <таблица-объединения>: {имя-таблицы [[AS
] псевдоним] I имя-запроса-на-выборку [[AS
] псевдоним] I (<таблица-объединения>)} {INNER I LEFT I RIGHT] JOIN {имя-таблицы [[AS
] псевдоним] I имя-запроса-на-выборку [[AS
] псевдоним] I (<таблица-объединения>)} ON
<условие объединения> Для каждой таблицы и запроса можно определить альтернативное имя. Оно используется как псевдоним вместо полного имени таблицы при задании имен столбцов в списке полей, условии объединения и предложении WHERE. Пример 10.6.
Следующий запрос отображает все поля таблицы Товары: Пример 10.7.
Следующий запрос отображает товары, цена которых больше 100 долл, и гарантийный срок которых 36 мес: FROM Товары WHERE Товары.Цена > 100 AND Товары.[Гарантийный срок] = 36; Большие возможности языка SQL во многом основаны на его способности объединять информацию из нескольких таблиц или запросов. Для задания типа объединения таблиц в логический набор записей, из которого будет выбираться необходимая информация, в предложении FROM используется операция JOIN. Операция INNER JOIN используется для получения всех строк из обеих логических таблиц, удовлетворяющих условию объединения. Операция LEFT JOIN возвращает все строки из первой логической таблицы, объединенные с теми строками из второй, для которых выполняется условие объединения. Аналогично, операция RIGHT JOIN возвращает все строки из второй логической таблицы, объединенные с теми строками из первой таблицы, для которых выполняется условие объединения. <Условие объединения> – выражение, в котором поля первой таблицы сравниваются с полями второй таблицы. В бланке запроса QBE в условии объединения используется только оператор равно (=). Запрос на основе объединения таблиц по неравенству (<, >, <>, <= или >=) можно создать только в режиме SQL. Пример 10.8.
Следующий запрос отображает сведения о складах фирмы Citii i nk: SELECT Склады.Номерсклада, Склады.Адрес, Склады.Телефон FROM Фирмы INNER JOIN Склады ON Фирмы.Кодфирмы = Склады.Кодфирмы WHERE Фирмы.Название = "Citilink"; Пример 10.9.
Запрос отображает список товаров, которые еще не продавались, с указанием наименования товаров и номеров складов, на которых они хранятся: SELECT Товары.Наименование, Хранение.Номерсклада LEFT JOIN Продажи ON Хранение.ID = Продажи.ID WHERE ((Продажи.Количество) Is Null); Пример 10.10.
Следующая операция возвратит все строки из таблиц "Товары" и "Хранение", для которых совпадают значения в поле КодТовара: Товары INNER JOIN Хранение ON Товары.КодТовара = Хранение.КодТовара Операция LEFT JOIN возвратит все строки из таблицы, полученной в результате предыдущей операции INNER JOIN, объединенные с теми строками из таблицы "Продажи", для которых выполняется условие объединения. Тогда в с троке товара, который не продавался, в поле [Количество проданного товара] будет значение Null, что будет являться условием отбора для искомого списка. Предложение GROUP BY.
В инструкции SELECT задает столбцы, используемые для формирования групп из выбранных строк. Синтаксис: GROUP BY имя-столбца, ... Оператор GROUP BY разделяет рассматриваемую таблицу па такие группы, что внутри любой из этих групп все строки содержат одинаковые значения в указанном столбце. Пример 10.11.
Следующий запрос отображает общее количество проданного товара каждого наименования: SELECT Товары.Наименование, Sum(Продажи.Количество) FROM (Товары INNER JOIN Хранение ON Товары.КодТовара = Хранение.КодТовара) INNER JOIN Продажи ON Хранение.Ю=Продажи.ID GROUP BY Товары.Наименование; Предложение HAVING.
Это специальная форма фразы WHERE. Она относится пе к отдельным строкам, а к группам: предикат во фразе HAVING всегда ссылается (посредством специальных библиотечных функций, таких как, например, SET) на свойства групп, а не строк, и на основе этого предиката группы целиком либо выбираются, либо отбрасываются. Синтаксис: HAVING "условие отбора> В случае отсутствия предложения GROUP BY условие отбора применяется ко всей логической таблице, определенной инструкцией SELECT. Пример 10.12.
Следующий запрос отображает общее количество проданного товара по дням с 10 по 20 декабря 2003 г.: SELECT Продажи.Дата, Эит(Продажи.Количество) FROM Продажи GROUP BY Продажи.Дата HAVING ((Продажи.Дата) Between #10.12.03# AND Предложение ORDER BY.
Задает порядок расположения строк, возвращаемых инструкцией SELECT. Синтаксис: ORDER BY {имя-столбца I номер-столбца },... Оператор ORDER BY определяет сортировку результата выборки в порядке возрастания (ASC) или убывания (DESC) значений атрибута. В предложении ORDER BY можно указать несколько столбцов. Список сортируется сначала по значениям столбца, имя которого указано первым. Пример 10.13.
Следующий запрос отображает список товаров, упорядоченных по цене: SELECT Товары.Наименование, Товары.Цена FROM Товары ORDER BY Товары.Цена DESC; Инструкция SELECT.
Выполняет операции выбора и объединения для создания логической таблицы (набора записей) на базе других таблиц или запросов. Выше был рассмотрен синтаксис инструкции SELECT в общем виде. Рассмотрим его более подробно: SELECT ] <список-полей> Предикаты ALL, DISTINCT, DISTINCTROW, TOP n
или TOP n
PERCENT уточняют окончательный набор записей запроса. По умолчанию действует предикат ALL, при котором в набор записей включаются все строки, удовлетворяющие условиям отбора, в том числе дубликаты. Предикат DISTINCT требует, чтобы запрос возвратил только строки, отличающиеся от всех остальных. Если инструкция SELECT содержит предикат DISTINCTROW, то в набор записей включаются только те строки, в которых конкатенация первичных ключей из всех таблиц, участвующих в формировании возвращаемых столбцов, является уникальной. В зависимости от того, какие столбцы представлены в наборе записей, иногда можно увидеть повторяющиеся строки, но даже в этом случае каждая строка запроса образована из уникальной (DISTINCT) комбинации строк (ROWS) базовых таблиц. Чтобы результирующий набор содержал только первые п
или первые п
процентов записей, используйте предикат ТОР п
или TOP п
PERCENT. Параметр п
должен быть целым числом, не превышающим 100, если используется ключевое слово PERCENT. Пример 10.14.
Следующий запрос отображает список 10 самых дорогих товаров: SELECT ТОР 10 Товары.Наименование, Товары.Цена FROM Товары ORDER BY Товары.Цена DESC; Подчиненный запрос (вложенная выборка).
Язык SQL позволяет использовать в условии отбора результаты другой выборки. Уровней вложенности может быть несколько. Пример 10.15.
Следующий запрос отображает список процессоров, цена которых ниже средней цены процессоров: SELECT Товары.Наименование, Товары.Цена FROM Товары WHERE ((Товары.Наименование) Like "проц*") AND ((Товары.L(eHa)<(SELECT Ауд(Товары.Цена) AS СредняяЦена FROM Товары WHERE ((Товары.Наименование) Like "проц*"))); Запрос должен заканчиваться символом "точка с запятой".
Приложение СУБД MS Access – это полноценный помощник для создания и ведения баз данных, заключенных в таблицы и массивы. Если база имеет слишком большой объем, быстро найти необходимые значения довольно сложно. Именно поэтому в Access существует такая функция, как запросы. Рассмотрим, что это такое, как работает, какие имеет особенности. Чтобы разобраться, как создавать запросы в Access, нужно знать основные положения работы с СУБД. Существует два способа выполнить данную процедуру: Первый способ дает возможность создать любой из всех доступных запросов в ручном режиме, но с небольшой оговоркой, заключающейся в том, что пользователь имеет опыт работы с приложением Access. Также он должен разбираться хотя бы в основных его задачах. Что касается второго способа, то его нужно рассмотреть более подробно. Знающий человек за несколько кликов мышью выбирает те компоненты, которые потребуются пользователю для выполнения запроса, а затем быстро формирует реестр, в соответствии с собранными ключевыми значениями. Если это первое знакомство с СУБД, и пользователь не представляет, как создавать запросы в Access, то выбирается программа Мастер. В данном режиме можно ознакомиться и разобраться со следующими типами запросов: Данный выбор осуществляется уже на первом этапе работы с Мастером. А в дальнейшем, следуя четким указаниям, даже начинающий пользователь легко создаст запрос. Познакомимся с его разновидностями. Этот инструмент работы с таблицами собирает нужные данные из указанных пользователем полей. Уже по названию видно, что это самый популярный тип запросов для новичков. Его удобство заключается в том, что такая процедура открывается в новой вкладке. Поэтому ответ на вопрос, как создать запрос в Access 2010, становится очевидным уже после открытия первого меню Мастера. Этот тип выборки более сложный. Чтобы разобраться, как создать перекрестный запрос в Access с помощью «Мастера» в данном режиме, нужно кликнуть по этой функции в первом окне. На экране появится таблица, в которой можно выбрать до трех столбцов, расположенных в оригинале. Одно из оставшихся не выбранных полей может быть использовано в качестве заголовков таблицы запроса. На третьем этапе процедуры (пересечение) выбирается еще одно значение с вариативностью функцию (среднее значение, сумма, первый, последний). На фото показано, что перекрестный запрос создан, и что по заданным параметрам совершены необходимые действия. Как понятно из названия, основное предназначение данного запроса – выборка всех одинаковых строк в таблице по указанным параметрам. Выглядит это так: Кроме того, доступен выбор дополнительных полей, чтобы подобрать соответствие сразу в нескольких строках. Чтобы выбрать повторяющиеся записи, нужно раскрыть список запросов и создать там новую папку. Далее в окошке «Новый запрос» выбрать строку «Поиск повторяющихся записей». Далее нужно следовать указаниям Мастера. Это последний тип запросов, доступный в режиме «Мастер – Записи без подчиненных». В этом случае ведется выборка только тех значений, которые не задействованы ни в одном поле таблиц и запросов, но которые уже созданы. Данный тип актуален только в случаях, когда баз данных несколько. Все эти четыре типа запросов являются базовой точкой для работы со сложными элементами, но позволяют легко разобраться, как создать запрос в базе данных Access. Разберемся, зачем нужно выполнять описанные выше действия. Задача всех простых и сложных запросов в СУБД Access заключается в следующем: Этот тип работы с базами данных является сложным, так как требует участия нескольких таблиц. Необходимо, чтобы во всех таблицах были общие ключевые поля. В противном случае совершить операцию не получится. Повторим, как создать запрос на выборку в Access. Сначала нужно создать простой запрос с выбором нужных полей. Уже здесь можно редактировать данные, чтобы привести их в желаемый вид. К слову, внесенные изменения перенесутся и в исходные таблицы, так что этот момент нужно учитывать. В открывшемся окне конструктора заполняется окно «Добавление таблиц». Здесь нужно добавить те таблицы или запросы, из которых нужно вытаскивать исходные значения. После добавления можно приступить к заполнению условий запроса. Для этого нам нужна строка «Поле». В ней нужно подобрать те значения из таблиц, которые будут отображаться при запросе. Чтобы завершить операцию, нужно нажать на кнопку «Выполнить». Это еще одна разновидность сложной процедуры, которая потребует от пользователя определенных навыков работы с базами данных. Одним из главных направлений такого действия является подготовка к созданию отчетов с объемными данными, а также получение сводных результатов. Как создавать запросы в Access 2007 с помощью конструктора, будет рассмотрено ниже. Начинать данную процедуру по выборке данных нужно с создания простого запроса, чтобы выбрать нужные поля. Далее через режим Конструктора обязательно нужно заполнить поле «Условие отбора» и, уже исходя из внесенного значения, будет осуществляться отбор. Таким образом, на вопрос о том, как создать запрос с параметром в Access, ответ простой - внести исходные параметры для выборки. Чтобы работать с Конструктором необходимо пользоваться Мастером запросов. Там создается первичные данные для фильтрации, которые служат основой дальнейшей работы. Продолжаем усложнять ситуацию. Еще труднее для понимания является информация о том, как создавать запросы в Access, если присутствует несколько таблиц с данными. Перекрестный запрос уже рассматривался выше, как один из вариантов работы с Мастером. Однако, и в режиме «Конструктора» можно создавать подобный запрос. Для этого необходимо нажать «Конструктор запросов» - «Перекрестный». Открывается меню добавления исходных таблиц, а также возможность заполнения выборочных полей. Единственное, на что следует обратить внимание, – пункты «групповая операция» и «перекрестная таблица». Их нужно заполнять правильно, иначе процедура не будет выполнена корректно. Перекрестные запросы – это наиболее простой способ поиска и выборки информации из нескольких источников данных, плюс с возможностью формирования диаграмм и графиков. Более того, при использовании данной процедуры быстрее выполняется поиск, даже с несколькими вариантами развития. Конечно, присутствуют и «подводные камни», которые могут помешать в работе. Например, при создании запроса на сортировку базы данных по значению столбцов система выдает ошибку. То есть доступна только сортировка по стандартным пунктам – «возрастание и убывание». Подводя итоги, нужно сказать, что решить, как создавать запросы в Access – с помощью Мастера или Конструктора, должен сам пользователь. Хотя, для большинства людей, которые используют СУБД MS Access, больше подойдет первый вариант. Ведь Мастер сам сделает всю работу, оставив для пользователя только несколько кликов мышью, при выборе условий запроса. Чтобы использовать расширенные настройки, явно необходим опыт работы с базами данных на уровне профессионала. Если в работе задействованы большие базы, лучше всего обратиться к специалистам, дабы избежать нарушения работы СУБД и возможных потерь данных. Есть один момент, который доступен лишь программистам. Так как основным языком СУБД является SQL, то нужный запрос можно написать в виде программного кода. Чтобы работать в данном режиме, достаточно нажать на строку уже созданного запроса, и в открывшемся контекстном меню выбрать «Режим SQL». В простейшем случае запрос реализует выбор из одной таблицы нужных полей, записей, соответствующих заданным условиям отбора, и просмотр результатов выполнения запроса. Рассмотрим запросы на выборку в Access на примере получения информации из таблицы ТОВАР базы данных Поставка товаров. Задача 1
. Пусть необходимо выбрать ряд характеристик товара по его наименованию. В результате выполненных действий в окне конструктора запросов (рис. 4.1) в верхней панели появится схема данных запроса, которая включает выбранные для данного запроса таблицы. В данном случае одну таблицу ТОВАР. Таблица представлена списком полей. Первая строка в списке полей таблицы, отмеченная звездочкой (*), обозначает все множество полей таблицы. Нижняя панель является бланком запроса, который нужно заполнить. Кроме того, на ленте появляется и автоматически активизируется новая вкладка (Query Tools | Design) (на рис. 4.3 представлена на часть этой вкладки), на которой цветом выделен тип созданного запроса - Выборка
(Select). Таким образом, по умолчанию всегда создается запрос на выборку. Команды этой вкладки представляют инструментарий для выполнения необходимых действий при создании запроса. Эта вкладка открывается, когда в режиме конструктора создается новый запрос или редактируется существующий. ЗАМЕЧАНИЕ
Задача 2.
Пусть надо выбрать товары, цена которых не более 1000 руб., и НДС не более 10%, а также выбрать товары, цена которых более 2500 руб. Результат должен содержать наименование товара (НАИМ_ТОВ), его цену (ЦЕНА) и НДС (СТАВКА_НДС). Задача 3
. Пусть надо выбрать все накладные за заданный период. Результат должен содержать номер накладной (НОМ_НАК), код склада (КОД_СК), дату отгрузки (ДАТА_ОТГР) и общую стоимость отгруженного товара (СУММА_НАКЛ). Для закрепления смотрим видеоурок:
В MS Access можно создавать базы данных, таблицы, формы и другие отчеты. Эта статья поможет пользователю запускать SQL-запросы в MS Access. Можно выполнять те же запросы, которые используются в SQL для выборки данных из базы данных. Эта статья предназначена для пользователей, которые только что начали изучать MS Access и хотят выполнять SQL запросы в MS Access. Единственное условие, которое необходимо перед тем как приступить – это наличие доступа к базе данных, использующейся в организации. Информация о статье Эту страницу просматривали 4443 раз. Была ли эта статья полезной? Каждый веб-разработчик должен знать SQL, чтобы писать запросы к базам данных. И, хотя, phpMyAdmin никто не отменял, зачастую необходимо испачкать руки, чтобы написать низкоуровневый SQL.
Именно поэтому мы подготовили краткий экскурс по основам SQL. Начнем же! Для создания таблиц предназначена инструкция CREATE TABLE . В качестве аргументов должно быть задано название столбцов, а также их типы данных. Создадим простую таблицу по имени month
. Она состоит из 3 колонок: Вот как будет выглядеть соответствующий SQL запрос: CREATE TABLE months (id int,
name varchar(10),
days int);
Также при создании таблиц целесообразно добавить первичный ключ для одной из колонок. Это позволит держать записи уникальными и ускорит запросы на выборку. Пусть в нашем случае уникальным будет название месяца (столбец name
) CREATE TABLE months (id int,
name varchar(10),
days int,
PRIMARY KEY (name));
Теперь давайте заполнять нашу таблицу months
полезной информацией. Добавление записей в таблицу производится через инструкцию INSERT . Есть два способа записи этой инструкции. Первый способ не указать имена столбцов, куда будут вставлены данные, а указать только значения. Этот способ записи прост, но небезопасен, поскольку нет гарантии, что по мере расширения проекта и редактировании таблицы, столбцы будут располагаться в том же порядке, что и ранее. Безопасный (и в тоже время более громоздкий) способ записи инструкции INSERT требует указания как значений, так и порядка следования столбцов: Здесь первое значение в списке VALUES
соответствует первому указанному имени столбца и т.д. Инструкция SELECT - наш лучший друг, когда мы хотим получить данные из базы данных. Она используется очень часто, так что отнеситесь к этому разделу очень внимательно. Самый простое использование инструкции SELECT - запрос, который возвращает все столбцы и строки из таблицы (например, таблицы по имени characters
): SELECT * FROM "characters"
Символ звездочка (*) означает, что мы хотим получить данные из всех столбцов. Так базы данных SQL обычно состоят из более чем одной таблицы, то требуется обязательно указывать ключевое слово FROM , следом за которым через пробел должно следовать название таблицы. Иногда мы не хотим получить данные не из всех столбцов в таблице. Для этого, вместо звездочки (*) мы должны через запятую записать имена желаемых столбцов. SELECT id, name FROM month
Кроме того, во многих случаях мы хотим, чтобы полученные результаты были отсортированы в определенном порядке. В SQL мы делаем это с помощью ORDER BY . Он может принимать опциональный модификатор – ASC (по-умолчанию) сортирующий по возрастанию или DESC , сортирующий по убыванию: SELECT id, name FROM month ORDER BY name DESC
При использовании ORDER BY убедитесь, что оно будет последним в инструкции SELECT . В противном случае будет выдано сообщение об ошибке. Вы узнали, как выбрать из базы данных с помощью SQL запроса строго определенные столбцы, но что если нам нужно получить еще и определенные строки? На помощь здесь приходит условие WHERE , позволяющее нам фильтровать данные в зависимости от условия. В этом запросе мы выбираем только те месяцы из таблицы month
, в которых больше 30 дней с помощью оператора больше (>). SELECT id, name FROM month WHERE days > 30
Ранее мы использовали фильтрацию данных с использованием одного критерия. Для более сложной фильтрации данных можно использовать операторы AND и OR и операторов сравнения (=,<,>,<=,>=,<>). Здесь мы имеем таблицу, содержащую четыре самых продаваемых альбомов всех времен. Давайте выберем те из них, которые классифицируются как рок и у которых менее 50 миллионов проданных копий. Это можно легко сделать путем размещения оператора AND между этими двумя условиями. WHERE также поддерживает несколько специальных команд, позволяя быстро проверять наиболее часто используемые запросы. Вот они: Например, если мы хотим выбрать альбомы с поп
и соул
музыкой, мы можем использовать IN("value1","value2") . SELECT * FROM albums WHERE genre IN ("pop","soul");
Если мы хотим получить все альбомы, изданные между 1975 и 1985годами, мы должны записать: SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;
SQL напичкан с функциями, которые делают разные полезные вещи. Вот некоторые из наиболее часто используемых: Чтобы получить самый последний год в нашей таблице мы должны записать такой SQL запрос: SELECT MAX(released) FROM albums;
В предыдущем пункте мы научились делать простые расчеты с данными. Если мы хотим использовать результат от этих расчетов, нам не обойтись без вложенных запросов. Допустим, мы хотим вывести artist
, album
и release year
для старейшего альбома в таблице. Мы знаем, как получить эти конкретные столбцы: SELECT artist, album, released FROM albums;
Мы также знаем, как получить самый ранний год: SELECT MIN(released) FROM album;
Все, что нужно сейчас, - это объединить два запроса с помощью WHERE: SELECT artist,album,released
FROM albums
WHERE released = (SELECT MIN(released) FROM albums);
В более сложных базах данных существует несколько таблиц, связанных друг с другом. Например, ниже представлены две таблицы о видеоиграх (video_games
) и разработчиков видеоигр (game_developers
). В таблице video_games
есть колонка разработчик (developer_id
), но в ней содержится целое число, а не имя разработчика. Это число представляет собой идентификатор (id
) соответствующего разработчика из таблицы разработчиков игр (game_developers
), связывая логически два списка, что позволяет нам использовать информацию, хранящуюся в них обоих одновременно. Если мы хотим создать запрос, который возвращает все, что нужно знать об играх, мы можем использовать INNER JOIN для связи колонок из обеих таблиц. SELECT video_games.name,
video_games.genre,
game_developers.name,
game_developers.country
FROM video_games
INNER JOIN game_developers
ON video_games.developer_id = game_developers.id;
Это самый простой и наиболее распространенный тип JOIN . Есть несколько других вариантов, но они применимы к менее частым случаям. Если вы посмотрите на предыдущий пример, то вы заметите, что существуют две колонки называемые name
. Это сбивает с толку, так что давайте установим псевдоним одного из повторяющихся столбцов, например, name
из таблицы game_developers
будет называться developer
. Мы также можем сократить запрос задав псевдонимы имен таблиц: video_games
назовем games
, game_developers
- devs
: SELECT games.name,
games.genre,
devs.name AS developer,
devs.country
FROM video_games AS games
INNER JOIN game_developers AS devs
ON games.developer_id = devs.id;
Часто мы должны изменить данные в некоторых строках. В SQL это делается с помощью инструкции UPDATE . Инструкция UPDATE состоит из: Ниже приведена таблица tv_series
с сериалами с их рейтингом. Однако, в таблицу закралась маленькая ошибка: хотя сериал Игра престолов
и описывается как комедия, он на самом деле ей не является. Давайте исправим это! Удаление строки таблицы с помощью SQL - это очень простой процесс. Все, что вам нужно, - это выбрать таблицу и строку, которую нужно удалить. Давайте удалим из предыдущего примера последнюю строку в таблице tv_series
. Делается это с помощью инструкции >DELETE DELETE FROM tv_series WHERE id = 4
Если мы хотим, чтобы удалить все строки, но оставить саму таблицу, то воспользуйтесь командой TRUNCATE: TRUNCATE TABLE table_name;
В случае, когда мы на самом деле хотим, чтобы удалить и данные, и саму таблицу, то нам пригодится команда DROP: DROP TABLE table_name;
Будьте очень осторожны с этими командами. Их нельзя отменить!/p> На этом мы завершаем наш учебник по SQL! Мы многое о чем не рассказали, но то, что вы уже знаете, должно быть достаточно, чтобы дать вам несколько практических навыков в вашей веб-карьере. Запрос SQL - это запрос, создаваемый при помощи инструкций SQL. Язык SQL (Structured Query Language) используется при создании запросов, а также для обновления и управления реляционными базами данных, такими как базы данных Microsoft Access. Когда пользователь создает запрос в режиме конструктора запроса, Microsoft Access автоматически создает эквивалентную инструкцию SQL. Есть ряд запросов, которые можно сделать только в режиме SQL. Опытным программистам часто проще сразу писать выражение на SQL, чем формировать запрос. Вид запроса в конструкторе: При сложных расчетах для получения результата приходится последовательно делать несколько запросов. Ясно, что эти действия должны производиться автоматически без участия пользователя. Для этого используются макросы, состоящие из нескольких последовательно выполняемых команд. Вычисления в запросах, возможности создания и редактирования формул. Для полей из указанных в схеме запроса таблиц можно указать любые вычисления. Чтобы произвести вычисления, в запрос необходимо добавить дополнительные вычисляемые поля, значения которых рассчитываются на базе значений других полей запроса. Итоговые запросы, группировка, итоговые функции. Итоговый запрос создается с помощью режима - Сводный запрос. Можно использовать три таблицы, включая связующую таблицу. При этом можно с любом месте запроса вызвать контекстное меню (правая кнопка мыши) и выбрать признак «групповые операции». В бланке запроса появится новая строка Группировка. Итоговые функции: в поле, по которому мы хотим посчитать итоги, выбрать из списка функцию «Sum» (сумма), чтобы просуммировать все значения выбранных полей. Функция «Подсчет» посчитает количество значений поля. информация редактирование microsoft Запрос - это обращение к СУБД для выполнения каких-либо операций с данными: выбора части данных из общего объема, добавление вычислимых полей, массовое изменение данных и т. д. В запросе можно: Типы запросов: Запросы используются в качестве источников записей для форм и отчетов. В большинстве своем и в формах, и в отчетах перед выдачей нужно отобрать часть данных по каким-либо условиям и отсортировать данные. Это делается с помощью запросов. Запрос может храниться отдельно или быть привязан к форме или отчету. В Microsoft Access есть несколько типов запросов.
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)
FROM m_income a INNER JOIN m_income b ON a.id <= b.id
GROUP BY b.id, b.product_id, b.amount, b.price;
FROM m_outcome
GROUP BY product_id
ORDER BY sum(amount*price) DESC;
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(*)<=5
ORDER BY COUNT(*);
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.id<=b.id
GROUP BY b.id) t;Создание запросов в Microsoft Access
Легкий путь для новичков
Простой запрос
Перекрестный запрос
Повторяющиеся записи
Записи без подчиненных
Функции запросов в MS Access
Запрос на выборку
Запрос с параметрами
Расширенный перекрестный запрос
Конструирование запросов на выборку с условиями отбора
Окно запроса в режиме таблицы аналогично окну просмотра таблицы базы данных. Через некоторые таблицы запроса может производиться изменение данных базовой таблицы, лежащей в основе запроса. Запрос, просматриваемый в режиме таблицы, в отличие от таблицы базы данных Access 2010, не имеет столбца Щелкнуть для добавления
(Click to Add), предназначенного для изменения структуры таблицы. В этом режиме на вкладке ленты Главная
(Home) доступны те же кнопки, что и при открытии таблицы базы данных.Шаги
Что вам понадобится
1. Создание таблицы
Дата и время
Тип данных
Описание
DATE
Значения даты
DATETIME
Значения даты и времени с точностью до минты
TIME
Значения времени
2. Вставка строк
3. Извлечение данных из таблиц
4. Фильтрация данных
5. Расширенная фильтрация данных. Операторы AND и OR
SELECT *
FROM albums
WHERE genre = "рок" AND sales_in_millions <= 50
ORDER BY released
6. In/Between/Like
7. Функции
8. Подзапросы
9. Объединение таблиц
10. Алиасы
11. Обновление данных
Данные таблицы tv_series
UPDATE tv_series SET genre = "драма" WHERE id = 2;
12. Удаление данных
Будьте осторожными при написании инструкции DELETE и убедитесь, что условие WHERE присутствует, иначе все строки таблицы будут удалены!
13. Удаление таблицы