SQL ステートメントの生成

www.altova.com このトピックを印刷 前のページ 1つ上のレベル 次のページ

ホーム >  SQL との作業 >

SQL ステートメントの生成

DatabaseSpy 内で、you can automatically generate SQL statements based on existing objects displayed in the オンラインブラウザー. You can either drag a データベースオブジェクト from the オンラインブラウザー into an SQL エディターウィンドウ, or right-click a データベースオブジェクト in the オンラインブラウザー and use コンテキストメニュー. The objects from which you can generate SQL statements (or extract information such as name and path) are as follows:

 

Tables table
Columns column
Keys PK
Indexes index
Triggers trigger
Views view
Procedures procedure
Functions function

 

The following table shows which SQL statements can be generated (or information be extracted into the SQL エディター) for each データベースオブジェクト type.

 

Select

table

column

-

-

-

view

-

-

Name

table

column

PK

index

trigger

view

procedure

function

Path

table

column

PK

index

trigger

view

procedure

function

Create

table

-

-

index

trigger

view

procedure

function

Drop

table

column

PK

index

trigger

view

procedure

function

Add

-

column

PK

-

-

-

-

-

Alter

-

column

PK

index

trigger

view

procedure

function

Rename

table

column

-

-

-

view

-

-

Insert

table

column

-

-

-

view

-

-

Update

table

column

-

-

-

view

-

-

データの削除

table

-

-

-

-

view

-

-

実行

-

-

-

-

-

-

procedure

-

 

メモ:The syntax of the statements may vary depending on the database kind you are using.

 

Select

Creates a SELECT statement that retrieves data from (i) all columns of the source table, (ii) the selected column(s) of the parent table, or (iii) all columns that are included in a view. Basically, this is the same procedure as when selecting データの取得 from コンテキストメニュー, however the query is not automatically executed in this case.

Name

Returns the name of the selected データベースオブジェクト. You can also select several objects. The names are printed in individual lines, separated by commas.

Path

Returns the full path of the selected データベースオブジェクト (for example, Database.Schema.Table.Column). You can also select several objects. The paths are printed in individual lines, separated by commas.

Create

Generates a CREATE statement based on the selected データベースオブジェクト. Depending on the selected データベースオブジェクト, you have to edit this statement as follows:

Edit the index name as well as the properties, and enter the appropriate column to create a new index for the parent column of the selected index.
Edit the trigger name as well as the table that invokes the trigger, and enter the action that is to be triggered.
Edit procedure name and parameters, and adapt the body accordingly to create a new stored procedure.
Edit the function name as well as the body of the function. Define additional or remove parameters, if required.

Drop

Creates a DROP or ALTER statement, 個々のly, that deletes the selected データベースオブジェクト from the database.

Add

Generates an ALTER statement that (i) adds a new column to the parent table of the selected column and uses the name and definition of the selected column as デフォルトの値, or (ii) adds a constraint and uses the properties of the selected constraint as default. The デフォルトの値 have to be edited.

Alter

Generates an ALTER statement that allows you to edit the properties of the selected データベースオブジェクト.

Rename

Allows you to rename the selected table, column, or view. Change this parameter to the desired new table name.

Please note: This command is not available for Microsoft Access databases.

Insert

Allows you to insert data into the selected table, column, or view. デフォルトでは、 no デフォルトの値 specified. You must edit the statement and specify the values that are to be inserted into the table. When inserting data into individual columns, or if the primary key is not included in the view, you must also specify a value for the primary key unless an IDENTITY statement has been used for auto-generation of a primary key when the table was created.

Update

Creates an UPDATE statement that updates (i) all columns of the selected table, (ii) the selected column, or (iii) the columns that are included in the selected view. No default is specified for the new values. Note that this command will update all rows with the same values if no WHERE statement is added to select a specific row.

データの削除

Generates a DELETE statement that deletes data (i) from the selected table and adds a WHERE clause using the table's primary key to specify the row to be deleted, or (ii) from the parent table of the selected view. Note that this command will delete all rows of the table if no WHERE statement is added.

実行

Declares the parameters needed for execution and creates an EXEC statement that executes the stored procedure with the required parameters.

 

Generating SQL statements with ドラッグアンドドロップ

When you drag a table or column from the オンラインブラウザー and drop it into an SQL エディターウィンドウ, a SELECT statement is generated by default. The kind of statement that should be generated for any データベースオブジェクト is configurable in the オンラインブラウザー options. 例えば、 you can configure that dropping a table into the SQL エディター should generate an UPDATE statement, not a SELECT.

 

 

To generate SQL statements using the default configured statement type:

ドラッグアンドドロップ one or several データベースオブジェクトs from the オンラインブラウザー into an existing SQL エディターウィンドウ. An SQL statement appears in the SQL エディター. Note that one statement per table will be generated if you select objects from different tables.

 

You can also right-click a データベースオブジェクト and drag it into an open SQL エディターウィンドウ. In this case, when you release the mouse ボタン, a コンテキストメニュー opens from which you can choose the desired statement type to be generated (例えば、 SELECT, INSERT, UPDATE, etc.).

 

 

To generate SQL statements of specific type:

1.Right-click one or several データベースオブジェクトs in the オンラインブラウザー and use drag right to drop the object(s) into an existing SQL window.
2.Select from コンテキストメニュー the type of SQL statement you want to generate.

 

You can define several additional options that determine how 生成された SQL statement appears in the SQL エディター. In the example below, the Append semi-colons to statement end has been activated in the SQL Generation options. This causes the semi-colon character to appear at the end of the statement. Also, the Enable SQL Formatting for the SQL エディター has been deactivated in the SQL Formatting options. This causes the FROM keyword to appear on the same line as the SELECT keyword (if SQL formatting had been enabled, a line break would have been automatically inserted before the FROM keyword).

br-gensql1

Generated SELECT statement

 

新規の SQL エディターウィンドウ内でステートメントを生成する方法

If you want your SQL statements to be generated in a new SQL エディターウィンドウ, do the following:

 

1.オンラインブラウザー内のデータベースオブジェクトを右クリックし、 「新規の SQL エディター内で表示」 をコンテキストメニューから選択します。
2.From the サブメニュー, select the appropriate statement you want to use (例えば、 SELECT, INSERT, etc).

(C) 2019 Altova GmbH