3 min read

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

 


Subscribe to the weekly Packt Hub newsletter. We'll send you the results of our AI Now Survey, featuring data and insights from across the tech landscape.

* indicates required

LEAVE A REPLY

Please enter your comment!
Please enter your name here