Базы данных | Access, SQL, Big Data

​Агрегирование данных с помощью обобщающих запросов. Обобщающие запросы позволяют легко агрегировать числовые данные. С помощью обобщающих запросов можно вычислить значения функций Sum(), Average(), Count(), Minimum(), Maximum(), а также провести другие агрегирующие вычисления над данными в результатах запроса. Также запросы позволяют вычислить одно значение, относящееся ко всем строкам в результатах запроса, или, при желании, сгруппировать вычисления. Например, можно определить итоговые значения по заказам для каждой строки в результате запроса.

TotalSales: Sum([Price]*[Quantity])

#Запросы #АгрегированиеДанных

​Использование построителя выражений. Построитель выражений представляет собой удобный инструмент создания выражений, применяемых не только в запросах, но и во многих других объектах Access. Чтобы открыть диалоговое окно Построитель выражений (Expression Builder), выберите команду Построить (Build) в контекстном меню поля бланка запроса, или команду Построитель (Builder), находящуюся на ленте. Окно Построитель выражений состоит из трех столбцов Элементы выражений (Expression Elements), Категории выражений (Expression Categories) и Значения выражений (Expression Values). В столбце Элементы выражений отображаются объекты базы данных. Например, с помощью узла Функции (Functions) можно получить доступ к пользовательским и к встроенным функциям. Выберем категорию функций Дата/Время (Date/Time) в столбце Категории выражений, после выбора все встроенные функции для работы со значениями даты и времени появятся в столбце Значения выражений. Если пользователь дважды щелкнет по конкретной функции (к примеру, по функции DatePart()), имя самой функции и имена ее параметров помещаются в текстовое поле в верхней части окна. Обратите внимание на то, что функция DatePart() имеет четыре параметра interval, data, firstdayofweek, firstweekofyear. Если известны значения, которые необходимо подставить в качестве этих параметров, то достаточно ввести вместо обозначений параметров требуемые значения. Чтобы получить подробную информацию об определенной функции, можно вызвать справку к этой функции. #ПостроительВыражений

​Создание вычисляемых полей. Одно из правил нормализации таблиц гласит, что результаты вычислений не должны храниться в базе данных. Для вывода результатов вычислений необходимые вычисления можно встраивать в запросы, а также отображать результаты вычислений в формах и отчетах, заложив запросы с вычислениями в основу формы или отчета. Можете так же добавлять к формам и отчетам элементы управления, с помощью которых будут выполняться необходимые вычисления. В определенных случаях это может способствовать повышению производительности. Это тему мы рассмотрим чуть позже. В столбцах результатов запросов могут быть представлены результаты вычисления любого допустимого выражения, в том числе значения, выражаемые пользовательскими функциями. Благодаря этому запросы становятся чрезвычайно мощным средством. Например, можно рассчитать стоимость товаров на основе полей Price (цена) и Quantity (количество), перемножив между собой, или вывести одним полем фамилию и первую букву имени клиента, написав следующие выражения:

Amount: [tblOrderDetails]![Price]*[tblOrderDetails]![Quantity]

Client: [tblClients]![LastName] & " " & Left([tblClients]![FirstName];1) & "." 

#Запросы #ВычисляемыеПоля

​​Нюансы применения многотабличных запросов. Работая с многотабличными запросами, необходимо учитывать некоторые нюансы их применения, которые касаются обновления данных, а также состава строк в результатах запроса. Важно учитывать, что некоторые поля в результатах многотабличного запроса обновить невозможно. Это касается полей на стороне "один" связи "один ко многим", на основании которых формируется соединение (если только не был установлен флажок Каскадное обновление связанных полей (Cascade Update Related Fields)). Кроме того, исключена возможность обновлять поле на стороне "многие" связи, по которому выполняется соединение, после обновления данных на стороне "один". Еще более важно то, что некоторые поля могут быть обновлены, но это приводит к весьма неожиданным последствиям. В частности, необходимо учитывать, что может вызвать обновление полей на стороне "один" связи "один ко многим". Безусловно, при этом фактически пользователь обновляет одну строку в исходной таблице на стороне "один" связи, но это может затронуть несколько строк на стороне "многие" той же связи. Например, показан результат на основе таблиц tblClients, tblOrders, tblOrderDetails и tblItems. В данном случае в определенной строке в выводе запроса значение Павел поля FirstName заменено на Андрей. На первый взгляд может показаться, что это изменение повлияет только на конкретный элемент, тем не менее после перехода к другой записи, изменились все строки, в которых встречался Иванов Павел на Иванова Андрея. Это изменение произошло в связи с тем, что данные о клиенте извлекаются из одной строки таблицы tblClients. #Запросы #МноготабличныеЗапросы

​​Создание запросов на основе нескольких таблиц. Если таблицы базы данных нормализованы должным образом, то, как правило, информация об отдельных сущностях представлена в разных таблицах, поэтому часто возникает необходимость собрать воедино эти данные с помощью запросов. К счастью, эта задача в Access решается очень легко. На скриншоте приведен запрос, в котором выполняется соединения таблиц tblClients, tblOrders, tblOrderDetails и tblItems, в результате чего происходит выборка данных из всех этих таблиц. Обратите внимание на то, что данные полей LastName, FirstName, Phone, Address извлекаются из таблицы tblClients, данные поля OrderDate - из таблицы tblOrders, данные поля Item - из таблицы tblItems, а данные поля Quantity - из таблицы tblOrderDetails. #Запросы #МноготабличныеЗапросы

Применение дат в критериях. Access предоставляет значительные возможности, связанные с добавлением календарных функций и выражений к критериям запроса. Использование этих критериев позволяет находить все строки, относящиеся к определенному месяцу, к конкретному дню недели или периоду между датами. Вот несколько примеров использования календарных функций: Date() - вернет все строки с текущей датой; Day([OrderDate])=1 - вернет все строки, в которых дата заказа относится к первому дню месяца; Mount([OrderDate])=1 - вернет все строки, в которых дата относится к январю; Year([OrderDate])=2020 - вернет все строки, в которых дата относится к 2020 году; Weekday([OrderDate])=1 - вернет все строки, в которых днем недели заказа является воскресение (при условии, что используется принятая нумерация дней недели с воскресения); Between #1/1/2020# And #31/12/2020# - вернет все строки с данными за 2020 год; DatePart("q",[OrderDate])=2 - вернет все строки с данными за второй квартал. Функция Weekday(Date,[FirstDayOfWeek]) действует с учетом применяемого языкового стандарта, а также в зависимости от того, как определен в системе первый день недели. В частности, при вызове функции Weekday() без необязательного аргумента FirstDayOfWeek в качестве первого дня недели по умолчанию принимает день который определен константой vbSunday (воскресение). При значении, равном 0, аргумент FirstDayOfWeek принимает по умолчанию значение, которое определено в системе. Могут быть также другие значения. #Запросы #Критерии #Функции

Уточнение запросов с помощью критериев. Одним из важных свойств запроса является возможность регламентировать вывод результатов с помощью критериев выборки. Программа Access позволяет комбинировать критерии, применяя различные операторы, действия которых распространяются на значения одного или нескольких полей. Доступные в Access операторы определения критериев и их назначение описаны ниже. = (Равно). Например, ="Москва" - выводит все строки, в которых в рассматриваемом поле находится значение "Москва". < (Меньше). Например, <100 - выводит все строки, в которых в рассматриваемом поле находится значение меньше 100. <= (Меньше или равно). Например, <=100 - выводит все строки, в которых в рассматриваемом поле находится значение, меньшее или равное 100. > (Больше). Например, >100 - выводит все строки, в которых в рассматриваемом поле находится значение больше 100. >= (Больше или равно). Например, >=100 - выводит все строки, в которых в рассматриваемом поле находится значение, большее или равное 100. <> (Не равно). Например, <>"Москва" - выводит все строки, в которых в рассматриваемом поле находится значение, отличное от "Москва". And (Оба условия должны быть истинными) - позволяет найти все строки, в которых оба условия истинные. Or (Истинным может быть одно из условий) - позволяет найти все строки, в которых одно из условий истинное. Like - обеспечивает сравнение строкового выражения. Например, Like"Моск*" - выполняет поиск всех строк, которые начинаются с подстроки "Моск". Between - выполняет поиск значений, относящихся к определенному диапазону. Например, Between 5 and 10 - выполняет поиск всех строк со значениями в поле от 5 до 10 включительно. In - определяет находится ли указанное значение в строке. Например, In("Москва";"Рязань";"Казань") - выводит все строки, в которых значение поля равно "Москва", "Рязань" или "Казань". Not - еще один вариант оператора проверки на неравенство. Например, Not"Москва" - выведет все строки со значениями, отличными от значения "Москва". Is Null - выполняет поиск значений Null. Выводит все строки, в которые не были введены данные. Is Not Null - выполняет поиск всех строк со значениями, отличными от Null. Выполняет поиск всех тех строк, в которые были введены данные. Звездочка (*) является подстановочным символом. В примере с конструкцией Like"Моск*" запрос выводит все строки, в которых заданное поле начинается с последовательности символов Моск, за которой могут следовать произвольные символы. #Запросы #Критерии #Операторы

​​Сортировка по значениям нескольких полей. Часто возникает необходимость отсортировать результаты запроса по значениям нескольких полей. В бланке запроса поля, по которым выполняется сортировка, должны быть расположены слева направо; сортировка выполняется в первую очередь по значениям крайнего левого поля, а затем – по значениям поля, следующего за ним слева направо, после этого – по значениям третьего и всех прочих полей, расположенных правее. Если же данные столбцов присутствовали в выводе запроса в другом порядке, то необходимо переместить столбцы в режиме таблицы вручную после выполнения запроса. #Запросы #Сортировка

​​Упорядочивание результатов запросов. После вызова на выполнение нового запроса обратите внимание на то, что результирующие строки появляются без упорядочения по какому-то принципу, но, вообще говоря, чаще всего требуется, чтобы они были расположены в определенном порядке. Для этого в бланке запроса предназначена строка Сортировка (Sort). Чтобы отсортировать результат запроса, выполните следующие действия: 1. В режиме конструктора щелкните в бланке запроса по ячейке Сортировка (Sort) того столбца, по значениям которого необходимо отсортировать строки. 2. Воспользуйтесь полем с раскрывающимся списком, чтобы выбрать способ сортировки По возрастанию (Ascending) или По убыванию (Descending). #Запросы #Сортировка

​​Добавление полей к запросу. После добавления таблицы или запроса, можно приступить к выбору полей, которые необходимо включить в запрос. Окно запроса состоит из двух разделов. Верхний раздел окна содержит таблицы и запросы, лежащие в основе создаваемого запроса. В нижнем разделе приведены поля, включаемые в результат запроса. Можно добавить поле в бланк запроса, приведенный в нижнем разделе окна, одним из следующих способов: ▪️ дважды щелкните по имени поля, которое необходимо добавить; ▪️ перетащите отдельное поле из таблицы, расположенной в верхнем разделе окна, в бланк запроса, находящийся в нижнем разделе; ▪️ чтобы выделить в таблице несколько полей, используйте клавишу <Shift>, позволяющую выбрать непрерывный ряд полей, а также клавишу <Ctrl>, с помощью которой можно отмечать отдельно расположенные поля. Перетащите выделенные поля в бланк запроса. ❗️ Чтобы включить все поля в результат запроса, дважды щелкните по звездочке (*), расположенной в списке полей. Безусловно, это очень удобно, но мы считаем такой шаг довольно опасным, поскольку вывод запроса после изменений структуры всей таблицы может неожиданно для пользователя стать совсем другим. Если выбрана звездочка (*), то в результат запроса включаются все поля таблицы, независимо от того, нужны они там или нет. Применение лишних полей может привести к существенному снижению производительности в локальной сети или приложении «клиент/сервер». #Запросы #ДобавлениеПолей

​​Добавление таблиц к запросу. Как было указано выше, если будет выбран Конструктор запросов (Query Design), откроется диалоговое окно Добавление таблицы (Show Table). В этом диалоговом окне можно выбрать таблицы или запросы с помощью которых подготавливаются данные для рассматриваемого запроса. В Access не имеет значения, применяются ли в качестве источника данных для запроса таблицы или другие запросы. Запросы и таблицы можно выбирать, дважды щелкая по именам таблиц или запросов, которые необходимо добавить, или щелкая по таблицы или запросу с последующим щелчком по кнопке Добавить (Add). Так же предусмотрена возможность выбирать несколько таблиц или запросов используя клавишу <Shift> для выбора непрерывного ряда таблиц или запросов, или клавишу <Ctrl> для выбора отдельно расположенных таблиц или запросов. После выбора необходимых таблиц или запросов щелкните по кнопке Добавить, а затем по кнопке Закрыть (Close) #Запросы #КонструкторЗапросов #ДобавлениеТаблиц

​​Основы запросов Access. Создать простой запрос несложно., поскольку компания Microsoft предоставила для этого дружественный интерфейс, в котором широко используются технология перетаскивания. В Access предусмотрены два способа создания нового запроса. Первый состоит в том, чтобы выбрать закладку Создать (Create), а затем щелкнуть по кнопке Мастер запросов (Query Wizard). Откроется окно Новый запрос (New Query). С помощью Мастера простых запросов (Simple Query Wizard) выполняются основные шаги созданию простого запроса. Другие программы-мастеры позволяют создать три более специализированных типа запросов: Перекрестный запрос (Crosstab Query Wizard), Повторяющиеся значения (Find Duplicates Query Wizard) и Записи без подчиненных (Find Unmatched Query Wizard). Второй способ: выбрать закладку Создать, а затем щелкнуть по кнопке Конструктор запросов (Query Design). В открывшемся диалоговом окне Добавление таблицы (Show Table) можно выбрать таблицы и запросы, которые должны быть включены в создаваемый запрос. #Запросы #ОсновыЗапросов #МастерЗапросов #КонструкторЗапросов

​​Свойства таблиц. Кроме свойств полей, предусмотрена возможность задавать свойства, которые относятся к таблице. Чтобы получить доступ к свойствам таблицы, щелкните по кнопке Страница свойств (Property Sheet). ▪️ Свойство Описание (Description) используется главным образом в целях создания документации. ▪️ С помощью свойства Режим по умолчанию (Default View) можно указать режим, в котором отображается таблица сразу после ее открытия. ▪️ Свойство Условие на значение (Validation Rule) определяется, какая проверка правильности должна проводиться га уровне строки, а не на уровне поля. Например, величина кредитного лимита может зависеть от доходов клиента. В этом случае данные, представленные в одном поле, зависят от значений в другом поле. ▪️ Свойство Сообщение об ошибке (Validation Text) определяет, какое сообщение об ошибке появится при обнаружении того, что пользователь нарушает правило проверки. ▪️ Свойство Фильтр (Filter) используется для указания на то, какое подмножество строк появляется в режиме таблицы, в форме или результатах запроса. ▪️ Свойство Порядок сортировки (Order By) позволяет определить применяемый по умолчанию порядок вывода строк. Как правило, свойства Фильтр и Порядок сортировки не применяются в качестве свойств таблицы. ▪️ С помощью свойства Имя подтаблицы (Subdatasheet Name) можно указать имя подтаблицы, используемой в качестве таблицы расшифровки. Если значение этого свойства задано равным Автоматически (Auto), то детализирующая таблица распознается автоматически на основании связей, которые определены в базе данных. ▪️ Свойства Подчиненные поля (Link Child Fields) и Основные поля (Link Master Fields) реализуются в целях определения полей, которые используются для связывания текущей таблицы с таблицей, указанной в свойстве Имя подтаблицы. ▪️ Свойство Высота подтаблицы (Subdatasheet Height) используется для определения максимальной высоты подтаблицы, а свойство Развернутая подтаблица (Subdatasheet Expanded) служит для указания на то, должна ли подтаблица автоматически отображаться в развернутом состоянии. ▪️ Свойство Ориентация (Orientation) определяет направление письма для текста, отображаемого в таблице. По умолчанию для диалекта русского/английского языка, применяется значение Слева направо (Left-to-Right). #Таблицы #СвойстваТаблиц #СтраницаСвойств.

​​Свойство Сжатие Юникод (Unicode Compression). Еще одним важным свойством является Сжатие Юникод (Unicode Compression). Данное свойство относится только к полям Короткий текст (Short Text) и Длинный текст (Long Text). Это свойство можно использовать для указания на то, должно ли применяться сжатие к данным в поле по принципу сжатия Юникода. До выхода Access 2000 данные хранились в виде набора символов в двухбайтовом формате (double-byte character set - DBCS), который был специально предназначен для хранения символьных данных на определенных языках, таких как китайский. В версии Access 2000 и последующих версиях все символьные данные хранятся в двухбайтовом представлении Юникода. Безусловно, этот формат требует большего объема памяти для каждого символа (2 байта, а не 1 байт), но свойство Сжатие Юникод позволяет применять сжатие к данным, если это возможно. Если используемый набор символов допускает сжатие и для свойства Сжатие Юникод установлено значение Да(Yes), то данные в столбце хранятся в сжатом формате. #Таблицы #СвойстваПолей #СжатиеЮникод #UnicodeCompression

​​Свойство Индексировано (Indexed). Индексы применяются для повышения производительности при поиске пользователем данных в поле. Безусловно, обычно лучшие результаты поиска достигаются при большем, чем при меньшем количестве индексов, но индексы обладают определенными недостатками. Как правило, следует предусматривать индексы для всех полей, которые регулярно используются в операциях поиска и сортировки, а также служат в качестве критериев для запросов. Недостатками индексов является то, что они занимают пространство жесткого диска и замедляют процесс редактирования, добавления и удаления данных. Безусловно, в большинстве случаев преимущества индексирования перевешивают недостатки, но не следует без разбора задавать индексы на каждом поле каждой таблицы. Создавайте индексы только для тех полей или комбинаций полей, с помощью которых пользователь выполняет поиск или сортировку. Не создавайте индексы для тех полей, которые содержат данные с большим количеством дубликатов, например, для полей, которые могут содержать только два разных значения. Наконец, никогда не индексируйте поля Логический (Yes/No). Для хранения данных этих полей применяется только 1 бит, кроме того, к ним относится приведенное выше правило, поскольку они могут иметь только одно из двух значений. По этим причинам применение индексов по отношению к полям Логический не дает никаких преимуществ. Для создания индексов отличных от индекса первичного ключа, которые определены на нескольких полях, необходимо использовать окно Индексы (Indexed). #Таблицы #СвойстваПолей #Индексировано #Indexed #Индексы

​​Свойство Пустые строки (Allow Zero Length). Свойство Пустые строки (Allow Zero Length) аналогично свойству Обязательное поле. Оно должно использоваться для определения того, разрешено ли пользователю вводить строку с нулевой длиной. Строка с нулевой длиной не аналогична значению Null (которое соответствует отсутствию данных). Строка с нулевой длинной указывает на то, что в данном поле находится пустое значение данных - "" . Например, сотрудник, не являющийся гражданином РФ, не имеет паспорта. Если предусмотрена возможность вводить строки с нулевой длинной, то лицо, на которое возложены обязанности по вводу данных, может указать, что серии и номера паспорта не существует, введя две кавычки - "". Сообщение об ошибке не будет выводиться. #Таблицы #СвойстваПолей #ПустыеСтроки #AllowZeroLength

​​Свойство Обязательное поле (Required). Свойство Обязательное поле (Required) является очень важным, поскольку оно определяет, является ли ввод значения в некоторое поле обязательным. В частности, это свойство может служить для работы с полями внешнего ключа, когда возникает необходимость обеспечить ввод данных в поле. Это свойство может так же применяться по отношению к любому полю, содержащему информацию, без которой нельзя обойтись. Например, невозможно ввести заказ, не указав клиента. #Таблицы #СвойстваПолей #ОбязательноеПоле #Required

​​Свойство Сообщение об ошибке (Validation Text). Свойство Сообщение об ошибке (Validation Text) позволяет задать сообщение об ошибке, передаваемое пользователю при нарушении им правила проверки. Свойство Сообщение об ошибке должно содержать текст. Применение выражений в этом свойстве не допускается. #Таблицы #СвойстваПолей #Подпись #СообщениеОбОшибке #ValidationText

​​Свойство Условие на значение (Validation Rule). Свойство Условие на значение (Validation Rule) дает возможность регламентировать то, что пользователь фактически может внести в это поле. Пользователь не может нарушить правила проверки, поскольку их строго предписывает ядро базы данных. Как и применительно к свойству Значение по умолчанию, свойство Условие на значение может содержать либо текст, либо допустимое выражение, но не предусмотрена возможность включать в него пользовательские функции. Не допускается также включать в свойство Условие на значение ссылки на формы, запросы или таблицы. Если задано свойство Условие на значение, а свойство Сообщение об ошибке (Validation Text) не заполнено, то Access автоматически отображает стандартное сообщение об ошибке при каждом нарушении пользователем правил проверки. #Таблицы #СвойстваПолей #Подпись #УсловиеНаЗначение #ValidationRule

​​Свойство Значение по умолчанию (Default Value). Очень важным является свойство Значение по умолчанию (Default Value), предназначенное для определения значения, которое Access помещает по умолчанию в поле при добавлении пользователем новых строк в таблицу. Значения, применяемые по умолчанию, которые могут представлять собой текст или выражения, позволяют сэкономить много времени для тех, кто занимается вводом данных. Но в Access эти значения никоим образом не используются для проверки правильности того, что введено пользователем в то или иное поле. Например, функция Date(), представляющая собой встроенную функцию VBA (Visual Basic for Applications), возвращает текущую дату. При использовании этой функции в качестве значения по умолчанию для поля, Access вводит текущую дату в поле при добавлении пользователем новой строки в таблицу. #Таблицы #СвойстваПолей #Подпись #ЗначениеПоУмолчанию #DefaultValue