Building Queries Visually in MySQL Query Browser

Exclusive offer: get 50% off this eBook here
Creating your MySQL Database: Practical Design Tips and Techniques

Creating your MySQL Database: Practical Design Tips and Techniques — Save 50%

A short guide for everyone on how to structure your data and set-up your MySQL database tables efficiently and easily.

$11.99    $6.00
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.

Building Queries Visually in MySQL Query Browser

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.

Building Queries Visually in MySQL Query Browser

A SELECT query on the product table is written in the Query Area.

Building Queries Visually in MySQL Query Browser

Drag and drop the item table from Schemata to the JOIN Table(s) button on the Pop-up Query Toolbar.

Building Queries Visually in MySQL Query Browser

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.

Building Queries Visually in MySQL Query Browser

Drag and drop the order table from Schemata to the LEFT OUTER JOIN button on the Pop-up Query Toolbar.

Building Queries Visually in MySQL Query Browser

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.

Building Queries Visually in MySQL Query Browser

Building Queries Visually in MySQL Query Browser

Similarly, move the ON clause to its next line.

Building Queries Visually in MySQL Query Browser

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.

Building Queries Visually in MySQL Query Browser

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).

Building Queries Visually in MySQL Query Browser

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)

Building Queries Visually in MySQL Query Browser

Next, add an additional condition. Drag and drop the amount column on the WHERE button in the Pop-up Query Toolbar.

Building Queries Visually in MySQL Query Browser

The column is added, with an AND, in the WHERE clause of the query. Type in its condition value, for example, > 1000.

Building Queries Visually in MySQL Query Browser

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.

Building Queries Visually in MySQL Query Browser

To test your query, click the Execute button.

Building Queries Visually in MySQL Query Browser

Your query should run without any error, and display its output in the query area (below the query).

Building Queries Visually in MySQL Query Browser

 

Creating your MySQL Database: Practical Design Tips and Techniques A short guide for everyone on how to structure your data and set-up your MySQL database tables efficiently and easily.
Published: November 2006
eBook Price: $11.99
Book Price: $19.99
See more
Select your format and quantity:

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).

Building Queries Visually in MySQL Query Browser

The query output (order data) is displayed on the Resultset 1 tab.

Building Queries Visually in MySQL Query Browser

Split the result tab horizontally by right-clicking anywhere in the result tab, and select the Split Tab Horizontally option.

Building Queries Visually in MySQL Query Browser

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.

Building Queries Visually in MySQL Query 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.
    Building Queries Visually in MySQL Query Browser
  • Load your cursor with the WHERE clause by clicking the WHERE button in the Advanced Toolbar.
    Building Queries Visually in MySQL Query Browser
  • 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.
    Building Queries Visually in MySQL Query Browser

Do the following two steps:

  • Add an = sign.
    Building Queries Visually in MySQL Query Browser
  • Drag the order_number parameter and drop it in the Query Area.
    Building Queries Visually in MySQL Query Browser

Your query’s WHERE clause is now complete, and the query is ready to execute.

Building Queries Visually in MySQL Query Browser

Execute the query by clicking the Execute button.

Building Queries Visually in MySQL Query Browser

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.

Building Queries Visually in MySQL Query Browser

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.

Building Queries Visually in MySQL Query Browser

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.

Creating your MySQL Database: Practical Design Tips and Techniques A short guide for everyone on how to structure your data and set-up your MySQL database tables efficiently and easily.
Published: November 2006
eBook Price: $11.99
Book Price: $19.99
See more
Select your format and quantity:

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

Mastering phpMyAdmin 2.11 for Effective MySQL Management
Mastering phpMyAdmin 2.11 for Effective MySQL Management

BSelling Online with Drupal e-Commerce
Selling Online with Drupal e-Commerce

Learning Drupal 6 Module Development
Learning Drupal 6 Module Development

Building Websites with Joomla! 1.5
Building Websites with Joomla! 1.5

WordPress Complete
WordPress Complete

OpenCms 7 Development
OpenCms 7 Development

WordPress Theme Design
WordPress Theme Design

Drupal 5 Themes
Drupal 5 Themes

 


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software