クエリーデザイン

クエリーのデザインビュー では、データベースクエリーを作成および編集できます。

「クエリーデザイン」ウィンドウのレイアウトは、作成したクエリーとともに保存されますが、作成したビューとともには保存できません。

デザインビュー

クエリーを作成するには、データベースドキュメント内で クエリー アイコンをクリックしてから、デザイン表示でクエリーを作成 をクリックします。

「デザインビュー」の下部のパネルでは、クエリーの定義 を行います。クエリーを定義するには、データベースからクエリーに含める フィールド名 を指定し、フィールドを表示する 条件 を指定します。「デザインビュー」の下部のパネルで、列の配列を変えるには、列のヘッダーを移動先にドラッグするか、列を選択してから + 矢印キーを押します。

クエリーデザインの表示ウィンドウの最上段には、クエリーデザイン バーおよび デザイン バーのアイコンが表示されます。

クエリーをテストしたい場合は、データベースドキュメントのクエリー名をダブルクリックします。クエリーの結果は、データソースの表示に似たテーブルに表示されます。注: 表示されるテーブルは一時的なものです。

クエリーデザイン表示のキー

キー	関数
F4	プレビュー
F5	クエリーの実行
F7	テーブルまたはクエリーの追加

ブラウズ

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.

フィールドをダブルクリックすると、フィールドがクエリーに追加されます。ドラッグ＆ドロップすると、リレーションを定義できます。

テーブルの解除

テーブルを作成画面から解除するには、テーブルウィンドウの上部枠をクリックして、コンテキストメニューを呼び出します。削除 コマンドを使って作成画面からテーブルを解除します。または、Deleteキーを押します。

テーブルの移動とテーブルサイズの変更

テーブルの並べ替えおよびサイズの変更を実施できます。テーブルを移動するには、マウスで上の枠をつかみ、目的の場所に引っ張ります。表示を拡大したり、縮小させるには、枠や角を引っ張ります。

テーブル間の対応関係

あるテーブルのフィールドと別のテーブルのフィールドとの間にデータの対応関係が存在する場合、その対応関係をクエリーに使用することができます。

たとえば、商品管理用の表計算ドキュメントを用意して、個々の商品は商品番号で区別するものとし、これとは別にカスタマ管理用の表計算ドキュメントを用意して、個々の商品番号を使ってカスタマから注文されたすべての商品を記録するような場合、この両者の「商品番号」データフィールドには対応関係が生じます。そして、1 人の顧客が注文したすべての商品を一覧表示するクエリーを新規作成するには、この 2 つの表計算ドキュメントの双方からデータを抽出する必要があります。こうした処理を実施するには、2 つの表計算ドキュメントの間にどのような対応関係が存在するかを、LibreOffice に指定する必要があります。

テーブルの中のデータフィールド（例えば子テーブルの「登録番号」のデータフィールド）をクリックして、マウスのボタンを押したままの状態で別のテーブル（例えば親テーブルの「登録番号」）に引きます。ここでマウスのボタンを離すと、これら２つのフィールドをつなぐ線が表示されます。結果として生成される SQL クエリーでは、これらの項目がお互いに同じ値である必要があるという条件を指定します。

複数の関連シートを使ったクエリーの作成は、リレーショナルデータベースのインタフェースとして LibreOffice を使用する場合のみ可能となります。

リンク形式の指定

リンクされた 2 つのフィールドを結ぶ接続線をダブルクリックするか、メニュー 挿入 → 新しいリレーション を選択すると、ダイアログ リレーション が開き、リンクの種類が指定できます。

または、行が選択されるまで Tab キーを押し、Shift + F10 キーを押して、コンテキストメニューを表示し、そこで 編集 コマンドを選択します。データベースによっては、選択可能な結合タイプのサブセットにしか対応していない場合があります。

リンクを解除する

２つのテーブルの間に存在するリンクを解除する場合、マウスでそのリンク線をクリックしてDeleteキーを押します。

別の方法としては、ダイアログ リレーション の 使用フィールド から該当項目を削除することもできます。また、Tab キーを押して目的の接続線を強調表示させて (Shift) + (F10) キーを押すとコンテキストメニューが表示されるので、コマンド 削除 を選択します。

クエリーの定義

クエリーを定義する条件を選択します。 作成されるテーブルの列はそれぞれクエリーのデータフィールドがあります。行に定義されている条件は論理的「AND」でリンクします。

データフィールドの指定

まず、クエリーに追加するテーブルで、すべてのフィールド名を選択します。この操作は、テーブルウィンドウの各フィールド名をドラッグ＆ドロップまたはダブルクリックすることで行えます。ドラッグ＆ドロップで行う場合は、テーブルウィンドウにあるフィールド名をマウスでドラッグして、クエリーデザイン画面の下側にあるエリアにドロップします。この方法では、どの列にどのフィールドを追加するかを逐次指定できます。ダブルクリックでフィールド名を選択します。この方法では、一番左側の空欄の列に追加されてゆきます。

データフィールドの解除

クエリーからデータフィールドを解除する場合、マウスで該当するフィールドに対応した列の頭をクリックして列のインデックスで 解除 コマンドを実行します。

クエリーの保存

標準バーの 保存 アイコンをクリックしてクエリーを保存します。クエリーの名前を入力するダイアログが表示されます。データベースでスキーマがサポートされている場合には、スキーマを入力することもできます。

スキーマ

クエリービューとテーブルビューに割り当てるスキーマの名前を入力します。

クエリービューとテーブルビューの名前

クエリービューとテーブルビューの名前を入力します。

データのフィルタリング

クエリーでデータのフィルタリングを行うには、データビューの下面でフィルタリングの条件を設定します。設定できる条件行には、下記のものがあります。

フィールド

クエリーで参照するデータフィールド名を指定します。ここより下の行の設定は、すべてこのフィールドを対象とします。 マウスクリックでセルをアクティブにすると、矢印に変化したマウスポインタを使ってフィールドを選択できます。「テーブル名.*」形式のオプションは、すべてのデータフィールドを示すもので、これを選択すると、設定する条件はテーブル中の全フィールドを対象とするようになります。

エイリアス

エイリアスを指定します。このエイリアスは、クエリーの実行結果で、フィールド名の代わりにリストされます。これによって、ユーザーが定義した列ラベルを使用することができるようになります。 たとえば、テーブル中の実際のデータフィールド名が PtNo であり、クエリーの実行結果ではこの名前の代わりに PartNum と表示させる場合には、エイリアスとして PartNum と入力します。

SQL ステートメントではエイリアスは次のように定義されます。

SELECT column AS alias FROM table.

たとえば、

SELECT "ItemNr" AS Item Number FROM "Item"

テーブル

ここには、選択したデータフィールドに対応するデータベーステーブルが表示されます。 マウスクリックでセルをアクティブにすると、矢印に変化したマウスポインタを使って、現在のクエリーで使用する他のテーブルを選択できます。

並べ替え

セルをクリックすると、並べ替え順序のオプションを昇順、降順、および並べ替えなしから選択できます。テキストフィールドはアルファベット順にソートされ、数値フィールドは数値の順にソートされます。ほとんどのデータベースの場合、管理者が並べ替え順序のオプションを設定できます。

表示

データフィールドの 表示 プロパティをオンにすると、クエリーでそのフィールドが表示されます。. 条件を記述するためだけにデータフィールドを使用する場合には、データフィールドを表示させる必要はありません。

条件

データフィールドの内容をフィルターする 条件 を指定します。

あるいは

各行毎にここで更なるフィルタリング条件を定義することができます。一つの列に対して複数の条件が存在する場合、これらは論理的「OR」で連結されます。

更に、クエリーのデザイン画面の下部にある行頭のコンテキストメニューで、関数を用いた行を一つ挿入できます。

関数

データベース製品によってさまざまな関数を実行できます。

HSQL データベースであれば、機能行のリストボックスに次のようなオプションがあります。

オプション	SQL	結果
機能なし		これにより機能の実行はありません。
平均	AVG	フィールドの算術平均を計算します。
数量	COUNT	テーブル内のレコード数をカウントします。空白フィールドは (a) または (b) の方式でカウントされます。 a）COUNT(*):アスタリスク（*）の引数を入力すると、テーブル内の全てのデータレコードが数えられます。 b）COUNT(column):データフィールドを引数として入力すると、指定されている値のデータフィールドがある全てのデータレコードが数えられます。Null 値（空のフィールド）はこの場合数えられません。
最高	MAX	フィールドでの最高値を算出します。
最低	MIN	フィールドでの最低値を算出します。
合計	SUM	付属する全てのフィールドの値の合計を算出します。
グループ化	グループ単位	選択したフィールド名により、クエリーのデータをグループ化します。この関数の実行結果は、指定したグループに応じて変化します。このオプションは、SQL の GROUP BY 句に該当します。追加した条件は、SQL の HAVING 句に追加されます。

関数呼び出しは、SQL ステートメント内に直接指定することもできます。構文は下記の形式で指定します。

SELECT FUNCTION(column) FROM table.

たとえば次のように SQL 文の中で合計算出の関数を使用できます。

SELECT SUM("Price") FROM "Item".

グループ機能を除いて、上記機能はいわゆる集計機能です。これらの機能は、データを計算し、その結果から集計を生成する機能です。リストボックスにない追加機能も可能です。これは使われているデータベースシステムや Base のドライバーの最新ステータスによります。

リストボックスにないほかの機能を使うためには、フィールドにそれを入力しなければなりません。

関数呼び出しでエイリアスを使うこともできます。クエリーが列の先頭に表示されない場合は、エイリアス の行に任意の名前を入力できます。

SQL ステートメントでは対象となる関数を次のように呼び出します。

SELECT FUNCTION() AS エイリアス FROM table

例:

SELECT COUNT(*) AS 数量 FROM "Item"

例：

次の例では、２つのテーブルを対象とするクエリーを実行します。Item_No フィールドを持つ Item テーブルと Supplier_Name フィールドを持つSuppliers テーブルがあります。また、両方のテーブルには Supplier_No というフィールドがあります。

すべての suppliers を対象として 3 つ以上の Item が含まれるクエリーを作成するには次のステップが必要です。

1. テーブル Item と suppliers をクエリーのデザインに挿入します。

2. テーブル間にリレーションがまだなければ、両方のテーブルの Supplier_No フィールドをつなぎます。

3. テーブル Item からフィールド Item_No をダブルクリックします。コンテキストメニューで 関数 の行を表示させて関数 Count を選択します。

4. 条件として「>3」を入力し、可視フィールドをオフにします。

5. テーブル Suppliers からフィールド Supplier_Name をダブルクリックして、関数グループを選択します。

6. クエリーを実行します。

テーブル Item のフィールド price（各商品の単価）とフィールド Supplier_No （Item の supplier）があるときは、supplier が提供する Item の平均価格は次のようにして求められます。

1. テーブル Item をクエリーのデザインに挿入します。

2. フィールド Price と フィールド Supplier_No をダブルクリックします。

3. 関数 の行をオンにしてフィールド Price で関数 Average を選択します。

4. エイリアスの行に Average と入力することもできます。

5. フィールド Supplier_No でグループ化を選択します。

6. クエリーを実行します。

以下に示すコンテキストメニューコマンドとアイコンがあります。

関数

関数選択行の表示、非表示を切り替えます。

テーブル名

テーブル名の行の表示、非表示が切り替わります。

エイリアス名

エイリアス行の表示 / 非表示を切り替えます。

固有値

クエリーに固有値のみが適用されます。 選択したフィールドに複数回登場するデータが含まれているレコードに適用されます。固有値 コマンドが有効な場合、クエリーでは 1 レコードだけが表示されます (DISTINCT)。それ以外の場合、クエリー条件に該当するすべてのレコード行がそのまま表示されます (ALL)。

たとえば、「Smith」という名前が複数登録されているアドレス帳データベースを扱う場合に、コマンド 「固有値」 を使うことで、「Smith」という名前を 1 度だけ抽出するようにできます。

複数のフィールドを抽出するクエリーでは、それらのフィールドデータの組み合わせが一意になり、その結果が固有のレコードになる必要があります。たとえばアドレス帳に、「Smith in Chicago」(シカゴ在住の Smith 氏) に該当する人物は 1 人だけ登録されているのに、「Smith in London」(ロンドン在住の Smith 氏) は 2 人分登録されているものとします。この場合のクエリーでは「last name」と「city」の 2 つのフィールドを使用しますが、その際にコマンド 「固有値」 をオンにすることで、「Smith in Chicago」と「Smith in London」に該当する結果をそれぞれ 1 回ずつ出力するようにできます。

このコマンドに該当する 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 の構文でこれらのコマンドを使用した場合は、LibreOffice が自動的に対応する SQL 構文に変換します。また SQL コマンドを直接入力することもできます。下記の一覧は、演算子とコマンドを簡単にまとめたものです。

演算子	意味	次のことが起きると条件が満たされる...
=	等しい	... 指定値とフィールド値は一致する。 = 演算子はクエリーフィールドでは表示されません; 演算子なしの値を入力すると = 演算子が使用されます。
<>	等しくない	... 指定値とフィールド値は一致しない。
>	大きい	... 指定値よりもフィールド値は大きい。
<	小さい	... 指定値よりもフィールド値は小さい。
>=	大きいか同等	... 指定値とフィールド値は同等か、その方が大きい。
<=	小さいか同等	... 指定値とフィールド値は同等か、その方が小さい。

LibreOffice コマンド	SQL コマンド	意味	次のことが起きると条件が満たされる...
IS EMPTY	IS NULL	null	... The value of the field is empty. For Yes/No fields with three states, this command automatically queries the undetermined state (neither Yes nor No).
IS NOT EMPTY	IS NOT NULL	空でない	... データフィールドは空ではない。
LIKE （任意の数の文字のワイルドカード文字は* 1文字のワイルドカード文字は ? ）	LIKE (任意の数の文字に対応する % プレースホルダー) 一文字のワイルドカード文字 _ ）	検索パターンと一致する	... 指定値がフィールド値に含まれている。ワイルドカード文字「*」はxの値が開始時点「x*」なのか、終了時点「*x」なのか、それともフィールド値の範囲内「*x*」なのかを示します。ワイルドカード文字として、SQL クエリーで SQL 文字である %を使用できます。この場合、LibreOffice の上層部のファイルシステムにワイルドカード文字「*」が存在するものとします。 ここのワイルドカード文字「*」および「%」は複数の任意の文字です。LibreOffice の疑問符「?」や SQL クエリーの下線「_」は、任意の一文字を表します。
NOT LIKE	NOT LIKE	検索パターンと一致しない	... 指定値がフィールド値に含まれていない。
BETWEEN x AND y	BETWEEN x AND y	区間 [x,y] に存在する	... データフィールドの値はx値とy値の間の値である。
NOT BETWEEN x AND y	NOT BETWEEN x AND y	区間[x,y]には存在しない	... データフィールドの値はx値とy値の間の値ではない。
IN (a; b; c...) 全ての値リストで区切りに使っているセミコロンに注意してください。	IN (a, b, c...)	a, b, c..を含む	... 指定値a, b, c,...のどれかがフィールド値に含まれている。多くの任意の値を対象にすることができ、クエリーの結果は論理「OR」で求められます。a, b, c... といった値は数値でも文字でもかまいません
NOT IN (a; b; c...)	NOT IN (a, b, c...)	a, b, c..を含まない	... 指定値a, b, c,...のどれかがフィールド値に含まれていない。
= TRUE	= TRUE	「True」値	...「True」値がフィールド値に含まれている。
= FALSE	= FALSE	「False」値	...「False」値がフィールド値に含まれている。

例）

='Ms.'	"Ms."という値が含まれているデータフィールドを返します。
<'2001-01-10'	returns dates that occurred before January 10, 2001
LIKE 'g?ve'	フィールド中に「give」や「gave」などのデータを含むフィールド名を返します。
LIKE ''S*'	「Sun」という値が含まれているデータフィールドを返します。
BETWEEN 10 AND 20	値が10と20の間のデータフィールドを引き渡します。（この場合、対象となるのはテキストフィールドと数値フィールドの両方です。）
IN (1; 3; 5; 7)	1、3、5、7の値が含まれるデータフィールドを返します。たとえば、item number のような登録番号を含むデータフィールドを対象とする場合、特定の登録番号の結果を返します。
NOT IN ('Muller')	「Muller」という値が含まれていないデータフィールドを返します。

Like Escape Sequence：{escape 'escape-character'}

例）select * from Item where Item Name like 'The *%' {escape '*'}

この例では、「The *」で始まる項目名全ての内容が得られます。尚、ここでは一般的にワイルドカード文字として扱われている記号である「*」、「?」、「 _」、または「%」およびピリオド等も検索できます。

Outer Join Escape Sequence：{oj outer-join}

例）select Item.* from {oj Item LEFT OUTER JOIN orders ON Item.No=orders.ANR}

テキストフィールドのクエリー

テキストフィールドは、値を引用符号でくくる必要があります。大文字と小文字の区別はしません。

日付フィールドのクエリー

Date fields are represented as #Date# to clearly identify them as dates. Date, time and date/time constants (literals) used in conditions can be of either the SQL Escape Syntax type, or default SQL2 syntax.

Date Type Element	SQL Escape syntax #1 - may be obsolete	SQL Escape syntax #2	SQL2 syntax
日付	{D'YYYY-MM-DD'}	{d 'YYYY-MM-DD'}	'YYYY-MM-DD'
時刻	{D'HH:MM:SS'}	{t 'HH:MI:SS[.SS]'}	'HH:MI:SS[.SS]'
日付と時刻	{D'YYYY-MM-DD HH:MM:SS'}	{ts 'YYYY-MM-DD HH:MI:SS[.SS]'}	'YYYY-MM-DD HH:MI:SS[.SS]'

例：select {d '1999-12-31'} from world.years

Example: select * from mytable where years='1999-12-31'

All date expressions (literals) must be enclosed with single quotation marks. (Consult the reference for the particular database and connector you are using for more details.)

はい/いいえフィールドからのクエリー実行

YES/NO フィールドをクエリーするには、dBASE テーブル用の次の構文を使用します。

ステータス	クエリー条件	例
はい	dBASE テーブルの場合:所定の値以外	=1 では「はい」または「ON」のステータスを示す（黒い印）のはい/いいえフィールドの全てのデータレコードが返されます。
いいえ	.	=0 では「いいえ」または「OFF」のステータスを示す（黒い印）のはい/いいえフィールドの全てのデータレコードが返されます。
Null	IS NULL	「IS NULL」では「はい」でも「いいえ」でもないステータスを示す（灰色の印）はい/いいえフィールドのすべてのレコードが返されます。

パラメータークエリー

Parameter queries allow the user to input values at run-time. These values are used within the criteria for selecting the records to be displayed. Each such value has a parameter name associated with it, which is used to prompt the user when the query is run.

Parameter names are preceded by a colon in both the Design and SQL views of a query. This can be used wherever a value can appear. If the same value is to appear more than once in the query, the same parameter name is used.

In the simplest case, where the user enters a value which is matched for equality, the parameter name with its preceding colon is simply entered in the Criterion row. In SQL mode this should be typed as WHERE "Field" = :Parameter_name

Parameter queries may be used as the data source for subforms, to allow the user to restrict the displayed records.

パラメーター入力

The Parameter Input dialog asks the user to enter the parameter values. Enter a value for each query parameter and confirm by clicking OK or typing Enter.

The values entered by the user may consist of any characters which are allowable for the SQL for the relevant criterion; this may depend on the underlying database system.

SQL モジュール

SQL とは「Structured Query Language」の略字であり、リレーショナルデータベースへのクエリーを実行と管理のためにの命令を表現するものです。

LibreOffice で行うクエリー処理の大半は、直接 SQL コードを入力する必要がないため、特に SQL に関する知識が無くても利用できます。クエリーデザインで作成したクエリーは、LibreOffice により自動的に対応する SQL 構文に変換されます。作成したクエリーがどのような SQL コマンドに変換されるかは、デザインビューの切り替えオン/オフ ボタンをクリックして、SQL ビューを表示することで確認できます。

SQL コードで直接クエリーを作成することもできます。その場合、特別な構文は対象のデータベースシステムに依存するということを忘れないようにします。

手で SQL コードを作成する場合、クエリーのデザイン の画像の上層部でサポートされていない SQL 特有のクエリーを作成できます。クエリーはネイティブ SQL モジュールで実行する必要があります。

SQL ビューでアイコン SQL コマンドを直接実行 をクリックすると、LibreOffice が自動作成する以外のクエリーも入力できます。