[ Send this article ]
Your message has been sent.
How would you like to send this article:
[ Save this article ]
Save this article to your account for easy access.
This article has been saved to your account.
Go to my account
This article has been emailed to your Kindle.
Send this article
Complete the form below to send this article, Building Queries Visually in MySQL Query Browser, to a friend (or to yourself). We will never share your details (or your friend's) with anyone. For more information, read our Privacy Policy.
by
Djoni Darmawikarta | April 2008 |
MySQL PHP
MySQL Query Browser, one of the open source MySQL GUI tools from MySQL AB, is used for building MySQL database queries visually. In MySQL Query Browser, you build database queries using just your mouse—click, drag and drop!
MySQL Query Browser has plenty of visual query building functions and features. This article Djoni Darmawikarta shows two examples, building Join and Master-detail queries. These examples will demonstrate some of these functions and features.
MySQL Query Browser, one of the open source MySQL GUI tools from MySQL AB, is used for building MySQL database queries visually. In MySQL Query Browser, you build database queries using just your mouse—click, drag and drop!
MySQL Query Browser has plenty of visual query building functions and features. This article shows two examples, building Join and Master-detail queries. These examples will demonstrate some of these functions and features.
Join Query
A pop-up query toolbar will appear when you drag a table or column from the Object Browser’s Schemata tab to the Query Area. You drop the table or column on the pop-up query toolbar’s button to build your query.
The following example demonstrates the use of the pop-up query toolbar to build a join query that involves three tables and two types of join (equi and left outer).
Drag and drop the product table from the Schemata to Add Table(s) button.
A SELECT query on the product table is written in the Query Area.
Drag and drop the item table from Schemata to the JOIN Table(s) button on the Pop-up Query Toolbar.
The two tables are joined on the foreign-key, product_code.
If no foreign-key relationship exists, the drag and drop won’t have any effect.
Drag and drop the order table from Schemata to the LEFT OUTER JOIN button on the Pop-up Query Toolbar.
Maximize query area by pressing F11. You get a larger query area, and your lines are sequentially numbered (for easier identification). Move the FROM clause to its next line, by putting your cursor just before the FROM word and press Enter.
Similarly, move the ON clause to its next line.
Now, you can see all lines completely, and that the item table is left join to the order table on their foreign-key relationship column, the order_number column.
As of now our query is SELECT *, i.e. selecting all columns from all tables. Let’s now select the columns we’d like to show at the query’s output. For example, drag and drop the order_number from the item table, product_name from the product table, and then quantity from the item table. (If necessary, expand the table folders to see their columns).
The sequence of the selecting the columns is reflected in the SELECT clause (from left to right).
Note that you can’t select column from the left join of the order table (if you try, nothing will happen)
Next, add an additional condition. Drag and drop the amount column on the WHERE button in the Pop-up Query Toolbar.
The column is added, with an AND, in the WHERE clause of the query. Type in its condition value, for example, > 1000.
To finalize our query, drag and drop product_name on the ORDER button, and then, order_number (from item table, not order table) on the GROUP button. You’ll see that the GROUP BY and ORDER clauses are ordered correctly, i.e. the GROUP BY clause first before the ORDER BY, regardless of your drag & drop sequence.
To test your query, click the Execute button.
Your query should run without any error, and display its output in the query area (below the query).
Master-detail Query
If you have data related in a master-detail fashion, you’d likely need to build a master-detail query for it. An example of master-detail data is order and its order item (item, for short), where an order always has one or more item—the order is the master; its item, the detail. You build a master-detail query to answer the question: Which detail belongs to this master? Let’s build a master-detail query on an order-item data visually in MySQL Query Browser.
Query the order as the master part of our master-detail query, by dragging and dropping the order table from the Schemata tab to the Result Area (Resultset 1 tab).
The query output (order data) is displayed on the Resultset 1 tab.
Split the result tab horizontally by right-clicking anywhere in the result tab, and select the Split Tab Horizontally option.
Two things happen as a result of splitting the result tab:
- The result tab now has two result set areas.
- The columns of the master table are listed under the Dynamic Params folder in the Params tab of the Information Browser.
Query the item as the detail part of our master-detail query by doing the following two steps:
- Drag and drop the item table into the Query Area.
- Load your cursor with the WHERE clause by clicking the WHERE button in the Advanced Toolbar.
- Expand the item table’s folder if necessary to see the order_number column, and click the order_number. WHERE clause is added to the query in the Query Area.
Do the following two steps:
- Add an = sign.
- Drag the order_number parameter and drop it in the Query Area.
Your query’s WHERE clause is now complete, and the query is ready to execute.
Execute the query by clicking the Execute button.
Note
- The master’s column you use as a parameter in the WHERE clause should be of the same data type and length as that the detail’s; otherwise, you’d get an unexpected result.
- Your master-detail query can have more than one parameter.
- The master-detail query can only be an equijoin query, i.e. the WHERE clause can only use = (equality operator).
The item’s result set is based on the master’s selected data. In the initial display, the items shown are for order number 1001.
To further test our master-detail query, click another order number of the master result set. For example, click order_number 1004 of the order result set, and the detail result set will show all items for order_number 1004.
Summary
We have just learned about the MySQL Query Browser and how it is used to create complex queries visually and in an easy manner.
About the Author :
Djoni Darmawikarta built his career in IBM Asia Pacific and Canada as a software engineer, international consultant, instructor and project manager, for a total of 17 years. He's currently a technical specialist in the Data Warehousing and Business Intelligence team of a Toronto-based insurance company. Outside of his office works, Djoni writes IT articles and books.
Books From Packt
Post new comment