Ознакомление с SQL-инструкциями; создание простейших SQL-запросов в Access с помощью команды SELECT используя операторы IN, BETWEEN, LIKE. Как составлять SQL-запросы - подробные примеры Объединение результатов запроса: оператор UNION
СУБД Access
Microsoft Access является СУБД реляционного типа, в которой разумно сбалансированы все средства и возможности, типичных для современных систем управления базами данных. Реляционная база упрощает поиск, анализ, поддержку и защиту данных, поскольку они сохраняются в одном месте. Access в переводе с английского означает "доступ". MS Access одна из самых мощных, гибких и простых в использовании СУБД. В ней можно создавать большинство приложений, не написав ни единой строки программы, но если нужно создать нечто очень сложное, то на этот случай MS Access предоставляет мощный язык программирования - Visual Basic Application.
Популярность СУБД Microsoft Access обусловлена следующими причинами:
Доступность в изучении и понятность позволяют Access являться одной из лучших систем быстрого создания приложений управления базами данных;
Возможность использования OLE технологии;
Интегрированность с пакетом Microsoft Office;
Полная поддержка Web-технологий;
Визуальная технология позволяет постоянно видеть результаты своих действий и корректировать их;
Наличие большого набора "мастеров" по разработке объектов.
Основными видами объектов, с которыми работает программа, являются: таблица, запрос, форма, отчет, страница, макрос, модуль.
Таблица - это объект, который используется для хранения данных. Каждая таблица включает информацию об объекте определенного типа. Таблица содержит поля (столбцы), в которых хранятся различного рода данные, и записи (строки). Для каждой таблицы должен быть определен первичный ключ (одно поле, имеющее для каждой записи уникальное значение или несколько полей, совокупное значение которых для каждой записи уникально), который является однозначным идентификатором каждой записи таблицы.
Для увеличения скорости доступа к данным отдельные поля таблицы (или их совокупность) могут быть объявлены индексами. Индекс - средство, ускоряющее поиск и сортировку в таблице за счет использования ключевых значений, которое позволяет обеспечить уникальность строк таблицы. Первичный ключ таблицы индексируется автоматически. Не допускается создание индексов для полей с некоторыми типами данных.
Запрос - это объект, который позволяет пользователю получить нужные данные из одной или нескольких таблиц. С помощью запросов можно также создавать новые таблицы, используя данные одной или нескольких таблиц, которые уже существуют. Наиболее распространенный тип запросов - запрос на выборку. Запрос на выборку отбирает данные из одной или более таблиц по заданным условиям, а затем отображает их в нужном порядке.
Форма - это объект, предназначенный в основном для ввода данных, отображения их на экране или управления работой приложения.
Отчет - объект, предназначенный для создания документа, который впоследствии может быть распечатан или включен в документ другого приложения.
визуальная разработка программирование база
Страница - используется для доступа к данным текущей базы данных Access.
Макрос - объект, представляющий собой структурированное описание одного или нескольких действий, которые должен выполнить Access в ответ на определенное событие.
Модуль - объект, содержащий программы на Microsoft Visual Basic, которые позволяют разбить процесс на более мелкие действия и обнаружить те ошибки, которые нельзя было бы найти с использованием макросов.
Запуск СУБД осуществляется Пуск - Программы - Мicrosoft Access. Выполнить команду Файл - Создать.
Интерфейс работы с объектами базы данных унифицирован. По каждому из них предусмотрены стандартные режимы работы: Создать (создание структуры объектов); Конструктор (изменение структуры объектов); Открыть (Просмотр, Запуск - предназначен для работы с объектами базы данных).
Язык запросов SQL
SQL (Structured Query Language - структурированный язык запросов) с 1986г. является стандартным языком реляционных баз данных. В частности, он используется в приложениях Access и Excel.
SQL - информационно-логический язык, предназначенный для описания хранимых данных, извлечения хранимых данных и модификации данных. Изначально, SQL был основным способом работы пользователя с базой данных и представлял собой небольшую совокупность команд (операторов) допускающих создание таблиц, добавление в таблицы новых записей, извлечение записей из таблиц, удаление записей и изменение структур таблиц. В связи с усложнением язык SQL стал более прикладным языком программирования, а пользователи получили возможность использовать визуальные построители запросов.
Язык SQL представляет собой совокупность операторов:
операторы определения данных (Data Definition Language, DDL);
операторы манипуляции данными (Data Manipulation Language, DML);
операторы определения доступа к данным (Data Control Language, DCL);
операторы управления транзакциями (Transaction Control Language, TCL).
Запросы в MS Access сохраняются и реализуются с помощью языка SQL. Хотя большинство запросов можно создать графическими средствами (запросы по образцу), однако хранятся они в виде инструкций SQL. В ряде случаев (например, в подчиненных запросах) можно использовать только язык SQL.
SQL относится к непроцедурным языкам. Он просто декларирует, что нужно сделать, а исполнение возлагается на СУБД (система управления базами данных).
В SQL используется трехзначная логика. Наряду с традиционными логическими значениями TRUE и FALSE используется NULL (НЕИЗВЕСТНО или ОТСУТСТВИЕ ДАННЫХ).
Операции осуществляются над целыми наборами данных, а не над отдельными элементами, как в других языках программирования.
Запрос на языке SQL состоит из инструкций. Каждая инструкция может содержать несколько предложений.
SQL - один из самых распространенных языков программирования, для создания и управления базой данных, а также для проведения разнообразных действий с самими данными.
Как показывает практика, он довольно простой в освоении и максимально использует стандартную лексику английского языка. Как и любой другой язык программирования, SQL имеет собственную логику и синтаксис, набор основных команд и правила их использования.
Классификация команд языка SQL
Все стандартные можно рассматривать исходя из их назначения. Как основу внегласной классификации можно взять такие наборы, как:
Команды для построения запросов.
Команды встроенных процедур и функций.
Команды триггеров и системных таблиц.
Наборы комбинаций для работы с датой и строковыми переменными.
Команды для работы с данными и таблицами.
Данную классификацию можно продолжать до бесконечности, но основные наборы команды языка SQL будут построены именно исходя из этих типов.
Рассматривая классификацию языка, нельзя не упомянуть о том, что он является универсальным, о чем говорит сфера его использования. Этот язык программирования и его разновидности задействуются не только в стандартной среде, но и в других программах, которые, так или иначе, вы использовали.
Сферу использования SQL можно рассматривать с точки зрения офисного программного обеспечения, а именно MicrosoftAccess. Этот язык, а точнее, его разновидность — MySQL, позволяет администрировать базы данных в сети Internet. Даже среда разработки Oracle использует в основе своих запросов команды SQL.
Использование SQL в MicrosoftAccess
Одним из самых простых примеров использования языка для программирования баз данных считается пакет программного обеспечения MicrosoftOffice. Изучение этого программного продукта предусмотрено школьным курсом информатики, а в одиннадцатом классе рассматривается система управления базой данных MicrosoftAccess.
Именно при изучении этого приложения ученики знакомятся с языком разработки баз данных и получают базовое понимание всего в него входящего. SQL команды Access довольно примитивны, конечно же, если рассматривать их на профессиональном уровне. Выполнение таких команд очень простое, а создаются они в приспособленном редакторе кода.
Рассмотрим конкретный пример:
SELECT Pe_SurName
WHERE Pe_Name = "Мэри";
Исходя из синтаксиса команды можно понять, что она вернет пользователю фамилию человека, в данном случае женщины по имени Мэри, которая хранится в таблице базы данных Contacts.
Хоть и использование SQL в Access ограничено, иногда такие простые запросы очень сильно могут упростить выполнение поставленного задания.
Использование команд SQL в Oracle
Oracle - это, наверное, единственный серьезный конкурент Microsoft SQL Server. Именно данная среда разработки и управления постоянно приводит к совершенствованию функций программного продукта компании Microsoft, так как конкуренция - это двигатель прогресса. Несмотря на постоянное соперничество, команды SQL Oracle повторяют SQL. Стоит отметить, что хоть Oracle и считается практически полной копией SQL, логика этой системы и языка в целом считается проще.
Система Oracle при использовании определенного набора команд не имеет такой сложной структуры. Если рассматривать возможности данных сред разработки баз данных, Oracle не имеет сложной структуры вложенных запросов.
Такая разница позволяет во много раз ускорить работу с данными, но, в противовес, ведет к нерациональному использованию памяти, в некоторых отдельных случаях. Структура Oracle в основном построена на временных таблицах и их использовании. Как пример: команды SQL в данной системе строятся по аналогии со стандартами самого языка SQL, хотя незначительно и отличаются от него.
SELECTCONCAT(CONCAT(CONCAT(‘Сотрудник ‘, sname), CONCAT(SUBSTR(fname, 0, 1), SUBSTR(otch, 0, 1))), CONCAT(‘принятнаработу ‘, acceptdate)) FROM employees WHERE acceptdate > to_date(‘01.01.80′,’dd.mm.yyyy’);
Данный запрос вернет данные о сотрудниках, которые приняты на работу в определенный промежуток времени. Хоть структура запроса отличается, от выполнение команд SQL в этих системах похоже, за исключением мелких деталей.
Использование SQL в сети Internet
С появлением всемирной паутины, то есть интернета, сфера использования языка SQL расширяется. Как известно, в сети хранится масса информации, но она не хаотично расположена, а размещена на сайтах и серверах по определенным критериям.
За хранение информации в Интернете, как и в других местах, отвечают непосредственно базы данных, а сайты являются системами управления. Как правило, сайты и их программный код организованы на разных языках программирования, но в основе баз данных лежит одна из разновидностей SQL, а именно язык создания баз данных, ориентированный под веб-интерфейсы MySQL.
Синтаксис и основной набор команд этого языка полностью копируют привычный всем SQL, но с некоторыми своими дополнениями, которые и дают ему отличие от Microsoft tSQL Server.
Команды SQL полностью похожи не только по синтаксису, но и по стандартному набору служебных слов. Разница состоит только в вызове и структурировании запроса. Для примера можно рассмотреть запрос для создания новой таблицы, именно она является первым, чему учат детей в школах на информатике:
$link = mysqli_connect("localhost", "root", "", "tester");
if (!$link) die("Error");
$query = "create table users(
login VARCHAR(20),
password VARCHAR(20)
if (mysqli_query($link, $query)) echo "Таблица создана.";
elseecho "Таблица не создана: ".mysqli_error();
mysqli_close($link);
В результате выполнения такого запроса можно получить новую таблицу "Юзеры", в которой будет два поля: логин и пароль.
Синтаксис изменен под Вэб, но в основу положены команды MicrosoftSQLServer.
Построение запросов MicrosoftSQLServer
Выборка из таблиц определенного набора данных одна из основных задач SQL. Для таких операций предусмотрена команда select в SQL. Именно о ней пойдет речь ниже.
Правила построение команды очень просты, а сама команда select в SQL строится следующим образом. К примеру, есть таблица, в которой имеются данные о сотруднике, которая, к примеру, имеет имя Person. Поставим задачу, что из таблицы нужно выбрать данные о сотрудниках, дата рождения которых - в промежутке от первого января до первого марта текущего года включительно. Для такой выборки необходимо выполнить команду SQL, в которой будет не только стандартная конструкция, но и условие выбора:
Select * from Person
Where P_BerthDay >= ‘01/01/2016’ and P_BerthDay<= ‘03/01/2016’
Выполнение такой команды вернет все данные о сотрудниках, день рождения которых находится в том периоде, который был задан вами. Иногда может стоять задача вывести только фамилию, имя и отчество сотрудника. Для этого запрос нужно построить чуть иначе, например, таким образом:
SelectP_Name - имя
P_SurName - фамилия
P_Patronimic - отчество
Where P_BerthDay >= ‘01/01/2016’ and P_BerthDay<= ‘03/01/2016’
Однако это всего лишь выбор чего-либо. Он, по сути своей, не влияет ни на что, а лишь предоставляет информацию. Но если вы решили заняться языком SQL всерьез, вам придется научится вносить изменения в базы данных, так как их построение без этого попросту невозможно. Как это делается, будет рассмотрено чуть ниже.
Основные команды SQL для изменения данных
Синтаксис языка построен не только для выполнения запросов, но и для манипуляций с данными. В основном задачей программиста баз данных является написание скриптов для выборок и отчетов, но иногда необходимо вносить правки в таблицы. Список команд SQL для таких действий невелик и состоит из трех основных команд:
Insert (пер. Вставить).
Update (пер. Обновление).
Delete (пер. Удалить).
Назначение этих команд легко определить, для этого достаточно будет всего лишь перевести их название. Эти команды просты в использовании и имеют не сложную схему построения, но стоит упомянуть, что некоторые из них, при неправильном использовании, могут нанести непоправимый вред базе.
Как правило, перед использованием такие MSSQL команды нужно продумать, и учесть все возможные последствия их выполнения.
Выучив данные команды, вы сможете полноценно начать работу с таблицами баз данных, тем самым видоизменять ее и вносить какие-то новые перменные или же удалять старые.
Команда Insert
Для вставки данных в таблицу используется самая безопасная команда — Insert. Неправильно вставленные данные всегда можно удалить и внести в базу данных заново.
Команда Insert предназначена для вставки в таблицу новых данных и позволяет добавить как полный набор, так и выборочно.
Для примера рассмотрим команду вставки в ранее описанную таблицу Person. Для того чтобы внести данные в таблицу необходимо выполнить команду SQL, которая позволит вставить все данные в таблицу или заполнить ее выборочно.
Insert into person
Select ‘Григорьев’,’Виталий’,’Петрович’,’01/01/1988’
Команды такого плана автоматически заполняют все ячейки таблицы с указанными данными. Бывают ситуации, когда у сотрудника нет отчества, скажем, он по обмену приехал работать из Германии. В таком случае нужно выполнить команду вставки данных, которая занесет в таблицу только то, что необходимо. Синтаксис такой команды будет следующим:
Insertintoperson(P_Name, P_SurName ,P_BerthDay)
Values (‘Дэвид’, ‘Гук’,’02/11/1986’)
Такая команда заполнит только указанные ячейки, а все остальные будут иметь значение null.
Команда для изменения данных
Для изменения данных как всей строки, так и некоторых ячеек используется команда Update SQL. Выполнять такую команду нужно только с определенным условием, а именно точно указывать в какую строку по номеру необходимо внести изменения.
Команда Update SQL имеет несложный синтаксис. Для правильного использования необходимо указать, какие данные, в какой колонке и в какой записи стоит изменить. Далее составить скрипт и выполнить его. Рассмотрим пример. Нужно изменить дату рождения Дэвида Гука, который внесен в таблицу сотрудников под номером 5.
Set P_BerthDay = ’02/10/1986’ where P_ID = 5
Условие (в данном скрипте) не даст изменить дату рождения во всех записях таблицы, а обновит только нужные.
Именно этой командой программисты пользуются чаще всего, так как она позволяет изменять данные в таблице не нанося существенный вред всей информации.
Команды для использования встроенных процедур и функций
С помощью языка SQL можно не только строить запросы, но и создавать встроенные механизмы для работы с данными. Как правило, бывают моменты, когда нужно использовать в теле одного запроса выборку, написанную ранее.
Если судить логически, то нужно скопировать текст выборки и вставить в нужное место, но можно обойтись и более простым решением. Рассмотрим пример, когда на рабочем интерфейсе выведена кнопка для печати отчета, скажем в Excel. Эта операция будет выполняться по мере необходимости. Для таких целей служат встроенные хранимые процедуры. Команды в данном случае, заключаются в процедуру и вызываются с помощью команды SQLExec.
Предположим, что была создана процедура для вывода даты рождения сотрудников с ранее описанной таблицы Person. В таком случае нет необходимости писать весь запрос. Для получения необходимой информации достаточно выполнить команду Exec [имя процедуры] и передать необходимые для выборки параметры. Как пример можно рассмотреть механизм создания процедуры такого характера:
CREATEPROCEDUREPrintPerson
@DB smalldatetime
@DE smalldatetime
SELECT * from Person
FROM HumanResources.vEmployeeDepartmentHistory
WHERE P_BerthDay >= @DB and P_BerthDay <= @DE
ANDEndDateISNULL;
Данная процедура вернет все сведения о сотрудниках, день рождения которых будет находиться в заданном временном периоде.
Организация целостности данных. Триггеры
Некоторые MS SQL-команды, можно даже сказать, конструкции, позволяют не только организовать манипуляции с данными, но и обеспечить их целостность. Для таких целей в языке предназначены системные конструкции, которые создает сам программист. Это так называемые триггеры, которые смогут обеспечить контроль данных.
В этом случае для организации проверки условий используются стандартные команды SQL-запросов. В триггерах можно создавать массу условий и ограничений для работы с данными, которые помогут управлять не только доступом к информации, но и запретить удаление, изменение или вставку данных.
Типы команд SQL, которые можно использовать в триггере, не ограничены. Рассмотрим на примере.
Если описывать механизм создания триггера, то типы команд SQL здесь такие же, как при создании процедуры. Сам алгоритм будет описан ниже.
Первым делом нужно описать служебную команду для создания триггеров:
Указываем, для какой операции с данными (в нашем случае это операция изменения данных).
Следующим шагом будет указание таблиц и переменных:
declare @ID int. @Date smalldatetime @nID int. @nDatesmalldatetime
DEclare cursor C1 for select P_ID, P_BerthDay from Inserted
DEclare cursor C2 for select P_ID, P_BerthDay from deleted
Задаем шаги выбора данных. После, в теле курсоров прописываем условие и реакцию на него:
if @ID = @nID and @nDate = "01/01/2016"
sMasseges "Выполнить операцию невозможно. Дата не подходит"
Стоит упомянуть о том, что триггер можно не только создать, но и отключить на время. Такую манипуляцию может провести только программист, выполнив команды SQL SERVER:
altertablePERSONdisabletriggerall - для отключения всех триггеров, созданных для данной таблицы, и, соответственно, altertablePERSONenabletriggerall - для их включения.
Эти основные команды SQL используются чаще всего, но их комбинации могут быть самыми разнообразными. SQL - очень гибкий язык программирования и дает разработчику максимум возможностей.
Вывод
Из всего вышесказанного можно сделать единственный вывод: знание языка SQL просто необходимо тем, кто собирается всерьез заняться программированием. Он лежит в основе всех выполняемых операций как в интернете, так и в домашних базах данных. Именно поэтому будущий программист обязательно должен знать множество команд данного языка, так как лишь с их помощью можно, так сказать, общаться с компьютером.
Конечно, недостатки есть, как и во всем в этом мире, но они настолько незначительны, что просто меркнут перед достоинствами. Среди всех языков программирования SQL практически единственная в своем роде, ведь она является универсальной, и знания по написанию скриптов и кодов лежат в основе практически всех сайтов.
Главным достоинством SQL безоговорчно можно считать его простоту, ведь, как-никак, именно он внесен в школьную программу. С ним может справиться даже начинающий программист, толком не разбирающийся в языках.
Язык структурированных запросов (SQL) является стандартным средством для работы с базами данных. Может использоваться как для интерактивной работы с БД, так и включаться в языки программирования. В CУБД Access SQL позволяет:
· создавать, модифицировать или удалять таблицы в базе данных Access;
· создавать или удалять ключи, индексы для таблиц в базе данных Access;
· вставлять, удалять или модифицировать записи таблиц;
· получать сводную информацию о данных в таблицах (число записей, суммы, средние, минимальные, максимальные значения и др.);
· осуществлять поиск и извлекать данные из одной или более таблиц по запросу.
Рассмотрим основные операторы языка SQL для поиска и извлечения информации из базы данных. Для этого используется оператор SELECT
SELECT список имен полей 1
FROM список имен таблиц
SELECT, FROM, WHERE, ORDER BY, GROUP BY - ключевые слова;
список имен полей 1 - набор имен полей, которые выбираются из одной или нескольких таблиц, указанных в списке имен таблиц, вычисляемых полей, агрегированных функций (например, count, sum, min, max, avg и т.д.). Для выбора всех полей из списка таблиц вместо перечисления их имен можно использовать символ «*». Если имя поля таблицы содержит пробел, т.е. состоит из нескольких слов, то это имя должно заключаться в квадратные скобки;
список имен таблиц - набор имен таблиц, из которых производится выбор информации.
Имена полей и таблиц нечувствительны к регистру клавиатуры и указываются в списках через запятую.
Примеры:
SELECT * FROM Книга - выбор всех полей всех книг из таблицы «Книга» базы данных по библиографии.
SELECT Название, [Год издания] FROM Книга - выбор полей заголовков (Название ) и года издания (Год издания ) всех книг из таблицы «Книга» . Имя поля года издания указано в квадратных скобках (имеет пробел).
Таблица «Издательство» Таблица «Книга» Таблица «Автор»
Рис. 2.1. Структура и схема БД по библиографии
Для поиска информации, удовлетворяющей некоторому условию, в операторе SELECT используется раздел WHERE, который имеет следующий синтаксис:
SELECT список имен полей FROM список имен таблиц WHERE условие,
где условие определяет критерий поиска информации.
В условии используются имена полей, операции сравнения (<, <=, =, >, >=, <>) и специальные операции сравнения IN, LIKE, BETWEЕN. Эти операции могут объединяться с помощью логических операций (And, Or) и задавать сложные условия поиска информации.
Примеры:
SELECT Название, [Год издания] FROM Книга
WHERE [Год издания] > 1991 определяет выбор названий книг, год выпуска которых позже 1991.
WHERE Фамилия = ”Иванов” определяет выбор полей имен и фамилий авторов из таблицы «Автор », фамилии которых совпадают с фамилией Иванов. Здесь символ “ используется для задания текстовых констант.
Операция IN сравнивает содержимое поля со списком заданных значений, определяющих критерий поиска информации.
Примеры:
SELECT Название, [Год издания] FROM Книга
WHERE [Год издания] IN (1995, 1996) определяет выбор названий и года издания книг, опубликованных в 1995 или в 1996 гг.
SELECT Фамилия, Имя, Город FROM Автор
WHERE Город IN (“Омск”, “Москва”, “Киев”) определяет выбор авторов, живущих в городах Омск, Москва или Киев.
Операция LIKE сравнивает содержимое поля со значением образца. Для записи образца используются строковые константы, символы шаблона и списки диапазона символов.
Символами шаблона являются: *, ?, #. Символ * соответствует цепочке символов, ? - соответствует одному символу, # - соответствует одной цифре.
Примеры:
Р* - возможные результаты поиска рука, Роза.
КИ? - возможные результаты поиска кий, кит.
12345# - возможные результаты поиска 123455, 123457.
Список диапазона заключается в квадратные скобки, первый и последний символы диапазона отделяются дефисом (-). Диапазон задается в возрастающем порядке.
Примеры:
SELECT Фамилия, Имя, Отчество FROM Автор
WHERE Фамилия LIKE "И*" определяет выбор авторов, фамилии которых начинаются с буквы И.
SELECT Название FROM Книга
WHERE Название LIKE “*база данных*” AND [Год издания] = 1996 определяет выбор названий книг, выпущенных в 1996 году и в названии которых присутствует фраза «база данных».
Операция BETWEEN проверяет принадлежность значения поля диапазону значений и является включающим значением (выбираются записи, содержащие поле со значением, равным границе диапазона). Границы значений объединяются операцией AND.
Примеры:
SELECT Название, [Год издания] FROM Книга
WHERE [Год издания] Between #01.01.1994# And #30.06.1994# определяет выбор книг, изданных в первой половине 1994 года. Здесь символ # используется для задания констант типа дата.
SELECT [Название издат], адрес, телефон FROM Издательство
WHERE [Название издат] Between "Нау" And "Фин" определяет выбор информации об издательских фирмах, начальные буквы названий которых, расположенные в алфавитном порядке, находятся в диапазоне между "Нау" и "Фин".
Для определения порядка , в котором представляются результаты поиска записей, используется раздел ORDER BY , синтаксис которого следующий:
SELECT список имен полей FROM список имен таблиц ORDER BY имя поля 1 [ASC | DESC][, имя поля 2[ASC | DESC]] ,
где имя поля1, имя поля2 – поля (из списка имен полей), по которым производится упорядочение. Опция ASC устанавливает порядок сортировкипо возрастанию (данный порядок установлен по умолчанию), DESC устанавливает порядок сортировки по убыванию. Упорядочение может вестись по нескольким полям (например, по фамилии, а затем по имени)
Пример:
SELECT Название FROM Книга
WHERE Название LIKE “*база данных*” AND [Год издания] = 1996
ORDER BY Название определяет выбор названий книг, выпущенных в 1996 году и в названии которых присутствует фраза «база данных», и упорядочивает названия в алфавитном порядке.
Для группировки записей используется раздел GROUP BY , синтаксис которого следующий:
SELECT список имен полей FROM список имен таблиц GROUP BY имя поля 1 [, имя поля 2],
где имя поля1, имя поля2 – поля (из списка имен полей), по которым производится группировка записей. Порядок следования полей в этом предложении определяет уровни их группировки. Группировку можно производить для выражений (например, вычисляемых полей), тогда в списке полей этому выражению должно быть присвоено какое-нибудь имя. Для агрегированных функций (count, sum, min, max, avg и т.д.) группировка обязательна.
Пример:
SELECT Фамилия, [Год издания], Sum (Цена) AS [Стоимость ]
GROUPBY Фамилия, [Год издания]
ORDER BY Фамилия, [Год издания] определяет суммарную цену (Стоимость ) книг года издания для каждого автора.
При работе с несколькими таблицами , каждое из рассмотренных условий выбора может быть применено для любой из таблиц. В общем случае при формировании запроса с использованием нескольких таблиц в разделе WHERE указывается связь между таблицами. Синтаксис запроса для нескольких таблиц следующий:
SELECT список имен полей FROM список имен таблиц WHERE список связей,
где список связей определяет, как таблицы в списке имен таблиц связаны между собой. Например, имя_таблицы1.имя_поля1 = имя_таблицы2.имя_поля2.
Примеры:
SELECT Книга.Название, Издательство. [Название издат]
FROM Книга, Издательство
WHERE Книга. [Код издат]= Издательство. [Код издательства] And
Издательство. [Название издат]=”Наука”
Этот запрос определяет выбор названий книг (Название ) из таблицы «Книга » и названий издательств (Название издат ) из таблицы издательств (Издательство ). Раздел WHERE осуществляет следующее:
· определяет связь между таблицами (выбирается название книги и номер издательства «Код издат » из таблицы «Книга» , по номеру издательства «Код издательства » в таблице издательств «Издательство» находится название издательства);
· задает условие для выбора книг, изданных только в издательстве «Наука».
Один запрос 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 – это полноценный помощник для создания и ведения баз данных, заключенных в таблицы и массивы. Если база имеет слишком большой объем, быстро найти необходимые значения довольно сложно. Именно поэтому в 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 раз. Была ли эта статья полезной?
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) доступны те же кнопки, что и при открытии таблицы базы данных.Шаги
Что вам понадобится
