libreoffice base запрос с параметром
Сортировка и создание запросов в LibreOffice Base
Следующее, чему следует научиться после создания базы данных, — работа с данными в ней. Сегодня рассмотрим, как в LibreOffice Base выполняются сортировка и создание запросов к базе данных, а также узнаем, как их печатать.
В качестве «испытуемой» будем использовать базу данных клиентов, которую мы создали на прошлом уроке. Откроем её в LibreOffice Base и выберем пункт Таблицы в окне База данных :
Сортировка представляет собой процесс упорядочения записей в таблице. Она может выполнятся по возрастанию или по убыванию значений ключа сортировки. Ключ сортировки — это поле, по значению которого производится сортировка. Ключей может быть несколько, и тогда они называются, соответственно, первичным, вторичным и так далее.
Для примера отсортируем дисконтные карты проценту предоставляемых ими скидок. Самую большую выведем сверху — это будет сортировка по убыванию. Откроем таблицу Дисконтные карты, двойным щелчком по ней (или выбором в контекстном меню кнопки Открыть) в окне Таблицы и выделим столбец Размер скидки — для этого щёлкнем по его «шапке». Затем выберем на панели инструментов кнопку По убыванию ( ) и нажмём её. Получим таблицу, отсортированную по размеру скидки клиента:
Чтобы использовать несколько ключей сортировки нужно вызвать окно, определяющее их порядок. Оно вызывается кнопкой Сортировать ( ) и позволяет выбрать последовательность и порядок их использования. Например, так:
Задание нескольких ключей позволяет определить, как будут упорядочены записи, у которых совпадает ключ сортировки, и унифицировать работу с данными.
Нажимаем кнопку Дальше и в следующем окне определяем порядок сортировки данных в итоговой таблице. Допустим, в начале списка нам хочется видеть клиентов с большим количеством баллов. Тогда сортировка будет такой:
Теперь зададим условие. У нас оно одно, но в общем случае их может быть несколько и для них можно определить, вместе они должны выполняться или для выборки будет достаточно только одного из них:
Также можно использовать псевдоним, если поля из разных таблиц имеют одно и то же имя.
В конце остаётся только определить название запроса и выбрать действие с ним:
Нажимаем кнопку Готово и получаем таблицу с запросом:
Другой путь — создание запроса в режиме дизайна. Создадим в нём запрос с тем же условиями, что и выше. Нажимаем кнопку Создать запрос в режиме дизайна. и в открывшемся окне добавления выбираем нужные нам таблицы и нажимаем кнопку Добавить (два раза, так как таблиц две):
В верхней части экрана при этом появятся выбранные таблицы, а в нижней мы будем формировать запрос. Сначала в строке Поле последовательно укажем все нужные нам поля. Затем, в строчке Псевдоним под ними впишем альтернативные названия. Строки Таблица и Видимый заполняются автоматический, нам остаётся только указать параметры сортировки и условие. В нашем случае готовая таблица запроса будет выглядеть так:
Очевидно, что создание запроса в режиме мастера состоит из большего числа шагов и имеет ряд не очевидных нюансов. Поэтому для создания запросов мы рекомендуем пользоваться режимом дизайна.
Создавать запрос в режиме SQL мы не будем, так как это наиболее сложный способ, который предполагает, что пользователь умеет писать запросы на этом языке. Однако, любой созданный ранее запрос можно увидеть в виде команд SQL, если в контекстном меню запросы выбрать пункт Редактировать в режиме SQL.
На этом уроке мы постарались рассмотреть основные приёмы, необходимые для создания запросов и выполнения сортировки в базах данных LibreOffice Base. Однако, если у вас остались вопросы по данной теме, вы можете задать их в комментариях или на нашем форуме. Мы постараемся оперативно на них ответить 🙂
Конструктор запросов
Представление конструктора запросов позволяет создавать и редактировать запросы базы данных.
Доступ к этой команде
Большинство баз данных использует запросы для фильтрации или сортировки таблиц для отображения записей на локальном компьютере. Представления выполняют то же самое, но на сервере. Если база данных расположена на сервере, который поддерживает представления, можно использовать их для фильтрации записей на сервере и уменьшения времени отображения.
Расположение элементов в окне «Дизайнер запросов» сохраняется вместе с созданным запросом, но не может быть сохранено с созданным представлением.
Конструктор
The lower pane of the Design View is where you define the query. To define a query, specify the database field names to include and the criteria for displaying the fields. To rearrange the columns in the lower pane of the Design View, drag a column header to a new location, or select the column and press Command Ctrl +arrow key.
Чтобы проверить запрос, дважды щёлкните его имя в документе базы данных. Результат запроса отобразится в таблице, аналогичной представлению источника данных. Примечание. Таблица отображается только временно.
Клавиши в построителе запросов
Добавить таблицу или запрос
Обзор
When you open the query design for the first time, you see a dialog in which you must first select the table or query that will be the basis for your new query.
Дважды щёлкните поля, чтобы добавить их к запросу. Перетаскивание для определения связей.
При разработке запроса изменить выбранные таблицы невозможно.
Удалить таблицы
Переместить таблицу и изменить размер таблицы
Расположение таблиц и их размер можно менять в соответствии с личными предпочтениями. Чтобы переместить таблицу, перетащите её верхнюю границу в нужное положение. Чтобы увеличить или уменьшить размер отображаемой таблицы, поместите указатель мыши на границу или угол таблицы и перетаскивайте её до достижения нужного размера.
Связи таблиц
При существовании связей между именем поля в одной таблице и именем поля в другой таблице эти связи можно использовать в запросе.
If, for example, you have a spreadsheet for articles identified by an article number, and a spreadsheet for customers in which you record all articles that a customer orders using the corresponding article numbers, then there is a relationship between the two «article number» data fields. If you now want to create a query that returns all articles that a customer has ordered, you must retrieve data from two spreadsheets. To do this, you must inform LibreOffice about the relationship which exists between the data in the two spreadsheets.
To do this, click a field name in a table (for example, the field name «Item-Number» from the Customer table), hold down the mouse button and then drag the field name to the field name of the other table («Item-Number» from the Item table). When you release the mouse button, a line connecting the two fields between the two table windows appears. The corresponding condition that the content of the two field names must be identical is entered in the resulting SQL query.
Создание запроса на основе нескольких связанных листов возможно только при использовании LibreOffice в качестве интерфейса для реляционной базы данных.
Получить доступ к таблицам из других баз данных запроса невозможно. Запрос, задействующий несколько таблиц, может быть создан только в пределах одной базы данных.
Specifying the relation type
Удаление связей
Чтобы удалить связь между двумя таблицами, щёлкните соединительную линию и нажмите клавишу DELETE.
Defining the query
Выберите условия для определения запроса. Каждый столбец таблицы конструктора содержит поле данных для запроса. Условия в одной строке связаны с логическим И.
Specifying field names
First, select all field names from the tables that you want to add to the query. You can do this either by drag-and-drop or by double-clicking a field name in the table window. With the drag-and-drop method, use the mouse to drag a field name from the table window into the lower area of the query design window. As you do this, you can decide which column in the query design window will receive the selected field. A field name can also be selected by double-clicking. It will then be added to the next free column in the query design window.
Удаление имён полей
Saving the query
Use the Save icon on the Standard toolbar to save the query. You will see a dialog that asks you to enter a name for the query. If the database supports schemas, you can also enter a schema name.
Схема
Введите имя схемы, назначенной запросу или таблице.
Имя запроса или таблицы
Введите имя запроса или таблицы.
Фильтрация данных
To filter data for the query, set the desired criteria in the lower area of the query design window. The following options are available:
Enter the name of the data field that is referred to in the Query. All settings made in the filter option rows refer to this field. If you activate a cell here with a mouse click you’ll see an arrow button, which enables you to select a field. The «Table name.*» option selects all data fields with the effect that the specified criteria will be applied to all table fields.
Псевдоним
Specifies an alias. This alias will be listed in the query instead of the field name. This makes it possible to use user-defined column labels. For example, if the data field is named PtNo and, instead of that name, you would like to have PartNum appear in the query, enter PartNum as the alias.
In a SQL statement, aliases are defined as follows:
SELECT column AS alias FROM table.
SELECT «PtNo» AS «PartNum» FROM «Parts»
Таблица
The corresponding database table of the selected data field is listed here. If you activate this cell with a mouse click, an arrow will appear which enables you to select a different table for the current query.
Сортировка
If you click on this cell, you can choose a sort option: ascending, descending and unsorted. Text fields will be sorted alphabetically and numerical fields numerically. For most databases, administrators can set the sorting options at the database level.
Видимый
Условия
Specifies a first criteria by which the content of the data field is to be filtered.
Here you can enter one additional filter criterion for each line. Multiple criteria in a single column will be interpreted as boolean OR.
You can also use the context menu of the line headers in the lower area of the query design window to insert a filter based on a function:
Функции
The functions which are available here depend on those provided by the database engine.
If you are working with the embedded HSQL database, the list box in the Function row offers you the following options:
Конструктор запросов
Представление конструктора запросов позволяет создавать и редактировать запросы базы данных.
 | Большинство баз данных использует запросы для фильтрации или сортировки таблиц для отображения записей на локальном компьютере. Представления выполняют то же самое, но на сервере. Если база данных расположена на сервере, который поддерживает представления, можно использовать их для фильтрации записей на сервере и уменьшения времени отображения. |
| Выбор команды Создать представление на вкладке Таблицы в документе базы данных приводит к открытию окна Конструктор представлений, похожего на описанное здесь окно Дизайнер запросов. |
Расположение элементов в окне «Дизайнер запросов» сохраняется вместе с созданным запросом, но не может быть сохранено с созданным представлением.
Содержание
Конструктор
Чтобы создать запрос, щелкните значок Запросы в документе базы данных, а затем щелкните Создать запрос в конструкторе.
Нижняя панель Конструктора запросов позволяет вам задать необходимый запрос. Чтобы задать запрос, укажите включаемые в него имена полей баз данных и критерии отображения полей. Чтобы переставить столбцы в нижней панели Конструктора запросов, перетяните заголовок столбца в новое местоположение либо выберите столбец и нажмите Ctrl+клавиша со стрелкой.
В верхней части окна конструктора отображаются значки панели Конструктор запросов и панели Конструктор.
Чтобы проверить запрос, дважды щелкните его имя в документе базы данных. Результат запроса отобразится в таблице, аналогичной представлению источника данных. Примечание. Таблица отображается только временно.
Клавиши в построителе запросов
| Клавиши | Функция |
|---|---|
| F4 | Предварительный просмотр |
| F5 | Выполнить запрос |
| F7 | Добавить таблицу или запрос |
Обзор
Дважды щелкните поля, чтобы добавить их к запросу. Перетаскивание для определения связей.
| При разработке запроса изменить выбранные таблицы невозможно. |
Удалить таблицы
Чтобы удалить таблицу из конструктора, щелкните верхнюю границу окна таблицы для отображения контекстного меню. Для удаления таблицы из конструктора можно воспользоваться командой Удалить. Кроме того, можно нажать клавишу DELETE.
Переместить таблицу и изменить размер таблицы
Расположение таблиц и их размер можно менять в соответствии с личными предпочтениями. Чтобы переместить таблицу, перетащите ее верхнюю границу в нужное положение. Чтобы увеличить или уменьшить размер отображаемой таблицы, поместите указатель мыши на границу или угол таблицы и перетаскивайте ее до достижения нужного размера.
Связи таблиц
При существовании связей между именем поля в одной таблице и именем поля в другой таблице эти связи можно использовать в запросе.
Если, к примеру, имеется электронная таблица для статей под номерами и электронная таблица для клиентов, в которую записываются все статьи, заказанные клиентом с использованием соответствующих номеров статей, то существует связь между двумя полями данных «номер статьи». Теперь, чтобы создать запрос всех заказанных клиентом статей, необходимо получить данные из двух электронных таблиц. Для этого требуется указать в LibreOffice, какая связь существует между данными этих электронных таблиц.
Щелкните имя поля в таблице (например, имя поля «Номер элемента» из таблицы клиентов), удерживая кнопку мыши, и перетащите имя поля на имя поля другой таблицы («Номер элемента» из таблицы элементов). Если отпустить кнопку мыши, появляется линия, соединяющая эти два поля в двух окнах. Соответствующее условие (содержимое имен полей должно совпадать) вводится в итоговый запрос SQL.
Создание запроса на основе нескольких связанных листов возможно только при использовании LibreOffice в качестве интерфейса для реляционной базы данных.
| Получить доступ к таблицам из других баз данных запроса невозможно. Запрос, задействующий несколько таблиц, может быть создан только в пределах одной базы данных. |
Указание типа ссылки
В качестве альтернативы можно нажимать клавишу TAB, пока не будет выбрана строка, а затем нажать Shift+F10 и выбрать команду Правка. Некоторые базы данных поддерживают только подмножество возможных типов объединений.
Удаление связей
Чтобы удалить связь между двумя таблицами, щелкните соединительную линию и нажмите клавишу DELETE.
Также можно удалить соответствующие записи из списка Включенные поля в диалоговом окне Связи. Или выделите соединительный вектор с помощью клавиши TAB, вызовите контекстное меню, нажав клавиши SHIFT+F10, и выберите команду Удалить.
Определить запрос
Выберите условия для определения запроса. Каждый столбец таблицы конструктора содержит поле данных для запроса. Условия в одной строке связаны с логическим И.
Указать имя поля
Сначала выберите все имена полей из таблиц, которые требуется добавить к запросу. Для этого перетащите или дважды щелкните имя поля в окне таблицы. При перетаскивании перетащите имя поля из окна таблицы в нижнюю область конструктора запросов. После этого можно определить столбцы для добавления в поле. Чтобы выбрать имя поля, дважды щелкните его мышью. Оно будет добавлено в следующий пустой столбец.
Удаление имен полей
Чтобы удалить имя поля из запроса, щелкните заголовок столбца в поле и в контекстном меню столбца выберите команду Удалить.
Сохранить запрос
Чтобы сохранить запрос, используйте значок Сохранить на стандартной панели. Отобразится диалоговое окно для ввода имени запроса. Если база данных поддерживает схемы, можно также указать схему.
Схема
Введите имя схемы, назначенной запросу или таблице.
Имя запроса или таблицы
Введите имя запроса или таблицы.
Фильтрация данных
Чтобы выполнить фильтрацию данных для запроса, задайте нужные параметры в нижней области конструктора. Доступны следующие строки:
Введите имя поля данных, ссылка на которое содержится в запросе. Все параметры, заданные в нижних строках, зависят от этого поля. Если выделить ячейку кнопкой мыши, появится кнопка со стрелкой, позволяющая выбрать поле. Параметр «Имя таблицы.*» определяет все поля данных и условия, доступные для всех полей таблицы.
Псевдоним
Задание псевдонима. Этот псевдоним будет указан в запросе вместо имени поля. Это позволяет применять подписи столбцов, определенные пользователем. Например, если поле данных имеет имя PtNo, можно ввести псевдоним PartNum, который будет отображаться вместо имени.
В инструкциях SQL псевдонимы определяются следующим образом:
SELECT column AS alias FROM table.
SELECT «PtNo» AS «PartNum» FROM «Parts»
Таблица
Здесь указана таблица базы данных, соответствующая выбранному полю данных. Если щелкнуть эту ячейку, появится стрелка, позволяющая выбрать другую таблицу текущего запроса.
Сортировка
Видимый
Чтобы сделать поле видимым в запросе, пометьте его свойство Видимый. Если поле данных используется только для формулировки условия, показывать его не обязательно.
Условия
Задание условий фильтрации содержимого поля данных.
Здесь можно ввести дополнительное условия для фильтрации каждой строки. Несколько условий в одном столбце будут соединены с помощью оператора «ИЛИ».
Также можно использовать контекстное меню заголовков строк в нижней области конструктора, чтобы добавить дополнительную строку для функций.
Функции
Доступность функций для запуска зависит от базы данных.
При работе с БД HSQL в поле со списком в строке Функция можно использовать следующие варианты:
| Параметр | SQL | Результат |
|---|---|---|
| Без функции | Ни одна функция не выполняется. | |
| Среднее | AVG | Вычисление среднего геометрического значения поля. |
| Считать | COUNT | Определение количества записей в таблице. Пустые поля могут учитываться (a) или нет (b). a) COUNT(*): Назначение звездочки в качестве аргумента, учитывающего все записи таблицы. b) COUNT(столбец): Назначение имени поля в качестве аргумента, учитывающего только те поля, в которых данное имя поля содержит значение. Нулевые значения (пустые поля) не учитываются. |
| Максимальное значение | МАКС | Определение наибольшего значения поля. |
| Минимальное значение | МИН | Определение наименьшего значения поля. |
| Сумма | СУММ | Вычисление суммы значений связанных полей. |
| Группировать | ГРУППИРОВАТЬ ПО | Группировка данных запроса согласно выбранному имени поля. Выполнение функций происходит в соответствии с указанными группами. В SQL этот параметр соответствует разделу GROUP BY. Если условие добавлено, его запись появляется в SQL HAVING. |
Также можно вводить вызовы функций непосредственно в инструкцию SQL. Используется следующий синтаксис:
SELECT FUNCTION(column) FROM table.
Например, следующий вызов функции в SQL для расчета суммы:
SELECT SUM(«Price») FROM «Article».
За исключение функции Группа, вышеуказанные функции имеют общее название «Агрегатные функции». Это функции, которые выполняют расчет данных для создания сводных данных на основании полученных результатов. Могут быть также доступны дополнительные функции, не указанные в поле со списком. Это зависит от конкретной используемой системы базы данных и состояния драйвера Base.
Чтобы использовать другие функции, не указанные в поле со списком, их необходимо указать в Поле.
Вызовам функций можно назначать псевдонимы. Чтобы не отображать запрос в заголовке столбца, введите нужное имя в поле Псевдоним.
Соответствующая функция в инструкции SQL:
SELECT FUNCTION() AS alias FROM table
SELECT COUNT(*) AS count FROM «Item»
| При запуске этой функции дополнительные столбцы могут быть вставлены в запрос только с помощью получения этих столбцов как функции «Группировать». |
В следующем примере выполняется запуск запроса по двум таблицам: таблица «Элемент» с полем «Номер элемента» и таблица «Поставщики» с полем «Имя поставщика». Кроме того, в обеих таблицах есть общее поле «Номер поставщика».
Чтобы создать запрос, который содержит всех поставщиков, поставляющих более трех элементов, необходимо выполнить следующие шаги.
Если таблица «Элемент» содержит поля «Стоимость» (для цены статьи) и «Номер поставщика», можно получить предоставляемую поставщиком среднюю стоимость элемента с помощью следующего запроса:
Доступны следующие символы и команды контекстного меню:
Функции
Отображение или скрытие строки для выбора функций.
Имя таблицы
Отображение или скрытие строки имени таблицы.
Псевдоним
Отображение или скрытие строки псевдонима.
Отдельные значения
Применение к запросу только отдельных значений. Запрос применяется к записям, содержащим данные, которые появляются в выбранных полях несколько раз. Если команда Отдельные значения активна, в запросе будет отображаться только одна запись (DISTINCT). В противном случае будут отображаться все записи, удовлетворяющие условиям запроса (ALL).
Например, если в базе данных адресов несколько раз встречается фамилия «Smith», можно воспользоваться командой Отдельные значения и указать в запросе, чтобы эта фамилия встречалась только один раз.
В SQL эта команда соответствует предикату DISTINCT.
Предел
Allows you to maximize the number of records with which query returns.
If there is added a Limit, you will get at most as many rows as the number you specify. Otherwise, you will see all records corresponding to the query criteria.
Формулирование условий фильтрации
При формулировании условий фильтрации могут использоваться различные операторы и команды. Помимо реляционных операторов, существуют команды SQL, запрашивающие содержимое полей базы данных. При использовании этих команд в синтаксисе LibreOffice система автоматически преобразует их в соответствующий синтаксис SQL. Кроме того, можно непосредственно ввести команду SQL. В следующих таблицах представлен обзор операторов и команд: