Hands-free Building of Queries with Query Builder

Contents

• Drawing simple, complex, and any queries you need
• Adjusting queries to your needs
• Making maximum use of query results
• Summary

Creating queries and managing query results have been greatly improved and visualized thanks to contemporary GUIs of many modern tools. However, it happens that one can be easily lost in the variety of windows and a great deal of options, striving to quickly do the task.

Let's review one of possible ways to avoid difficulties, while building queries, and gain your point, while managing received data. This article describes the features of Query Builder - a professional tool for visual and quick creating and managing queries. It is integrated into dbForge Studio for MySQL or supplied as part of dbForge Fusion for MySQL.

Drawing simple, complex, and any queries you need

Visual creating queries on the diagram

Effortless creating of queries is usually reached by various means. The core principle is to use a diagram where tables, participating in the query, are presented as shapes. So you can visually join tables, select columns, and do other tasks.

Query Builder uses a well-thought diagram where you can drag-and-drop tables from Database Explorer, a special window, which allows you to see and manage database schemas. You can select multiple tables at once, pressing the Ctrl key. On the diagram a table turns into a shape, which accurately shows the list of table's columns. You will see which columns have foreign and primary keys, as they are marked with special "key" icons.

Joining tables becomes simple, because you can quickly find the required tables and columns to join and then drag-and-drop the column of one table to the corresponding column of the other table. Moreover, joins are automatically created, if tables with foreign keys and their referenced tables are moved to the diagram. To change the join type, choose the join and select the required type (Left Outer or Right Outer) from the right-click menu.

The table shapes are very convenient both to view and to manage. When your query includes many tables, you will appreciate the following options to get better view:

• Resizing shapes (you can either manually resize shapes to various directions, dragging the resize markers at the shape sides, or fit the same size for multiple shapes using menu options - Make Same Width or Make Same Height.)
• Aligning shapes either in a column or in a row
• Placing one shape over another (When some shapes overlap each other, you can use the Bring to Front option from the right-click menu to place one shape over another, or the Send to Back option to do the opposite action.)
• Controlling spaces between shapes (You can proportionally increase and decrease horizontal and vertical spacing between the shapes, making it equal or just remove.)
• Zooming in and out the diagram
• Customizing the diagram ( You can set the diagram size in pixels, change the background color, or disable the diagram completely.)

Creating complex queries with subqueries

Query Builder is aimed to simplify usual inconveniences and errors when including multiple subqueries into a main query. You can create and edit subqueries in separate diagram windows, which are the tabs of the main diagram. To access multiple subqueries as well as the main query, you should only switch between tabs of the diagram window. To create a subquery, right-click on the diagram of the main query window and select the Create SubQuery option from the menu. The new diagram window opens, where you can create your subquery, the same way as you created the main query.

Query Builder offers quick navigation through the query elements in the Document Outline window. It shows all query elements as a tree structure, very useful for analysis and navigation. You can see all the query clauses, as well as all the subqueries. When you double-click a query clause in the tree, the corresponding tab in the editor opens where you can see and manage its parameters. In the From node you can see the tables, participating in the query, so when you double-click any of them, the corresponding table shape is highlighted on the diagram. If you select a subquery node, the diagram window for the subquery will open.

When dealing with large amounts of data, the execution of the query can take some time. So you have to wait for some time until the process is finished. Query Builder offers asynchronous execution, which fits these cases the best way. It doesn't prevent you from doing other tasks when data is being retrieved. If required, you can execute only subqueries as well as the root query.

Adjusting queries to your needs

Selecting columns, setting conditions of JOIN, WHERE, and HAVING clauses can be rather tricky. To avoid difficulties, Query Builder offers a special tabbed editor, placed under the diagram. It consists of 6 tabs (Selection, Joins, Where, Group By, Having, Order By), each entitled for managing query clauses. You can refine your query both on the diagram or in the editor. No complicated operations and multiple-step procedures are required.

To include table columns into your query, choose them in the table shapes on the diagram or switch to the Selection tab of the tabbed editor and select from the drop-down list. This tab helps you accurately select columns, change their order, enter aliases, easily apply functions, filtering and sorting options. When filtering, sorting, or functions are applied, special icons appear next to the corresponding columns in the table shapes. So while refining your query, you don't lose the control, but see a detailed picture of the query parameters step-by-step. Moreover, you can enter any custom statements on the Selection tab to create a desired query.

When tables are joined, you can see and edit their conditions on the Joins tab. It will be easy to manage joins as they are presented as a tree in the tabbed editor. The join can have either a separate condition or a group of conditions, united with a logical operator. The groups of conditions are presented as the nodes of the tree. Each node accurately shows the type of the join and names of the tables, which are joined. When you expand a node, you can see join conditions. You can edit all join parameters (types of joins, conditions, or group of conditions, logical operators, etc.) in several clicks. One - to click the required parameter, others - to select the options from the appeared menu or a drop-down list, or enter the value manually. You can add or delete joins or their parameters even in one click. Use the button to add and the button to delete. For more flexibility, use favorite commands Ctrl+C and Ctrl+V to copy and paste conditions.

To manage WHERE and HAVING clauses, use the corresponding tabs of the editor. They also present conditions as the compact tree. You can create either a separate condition or groups of conditions, which are presented as tree nodes. To quickly do this, click the logical operator, marked with red, and select the required option from the appeared menu. Use the same menu to change the type of group logical operators. To navigate between conditions and groups, use the up and down arrow keys. Use the left and right arrow keys to navigate between criteria operators and operands. To move conditions from one group to another, place the mouse pointer over any condition element and, holding the left mouse key, move the condition to the required place.

Now you will be able to easily create queries with conditions of any complexity and get clear-eyed understanding what you should do.

When you need to group retrieved data by some fields, specify them on the Group By tab. To add or delete fields to the list, use the arrow buttons in the middle of the tab. To change the columns order, use up and down arrow buttons. To sort data by fields, select them on the Order By tab just the same way. You see, the logic is the same, so you will intuitively understand what to do next.

You can create and edit your queries using two different views - Design and Text. It is very convenient to switch between views, as they are presented as tabs at the bottom of the Query document. Design view is a visual way of creating and managing queries using the advanced diagram and the tabbed editor, Text view is for managing query code. When you switch from Design view to Text one, all changes made in the query are applied to the query code in the SQL editor. When you make changes in the query code, you can see these changes in visual mode, when returning to Design view.

Making maximum use of query results

After your query is executed, you can see the retrieved data in the Data window and, in case any errors occur during the execution, see the list of errors in the Error List window. These windows are placed under the editor, so you can control your query on all stages - from creation to execution. As you can execute separate subqueries, as well as the main query, you can open the Data window for each subquery.

Query Builder has concentrated all functionality that you would like to have at your fingertips while managing query results.

The Data window offers a set of various modes for convenient viewing retrieved data. They are the following:

• Grid View
• Card View
• Paginal Mode
• Auto-search Mode
• Data Observer Mode
• Cached Updates Mode

By default, Grid View is selected, so you can see the query results in the grid. To optimize data overview, you can change columns' width using the Best Fit option from the right-click menu. For better understanding and analysis, group, sort, and filter your data. If you retrieved data from one table only, you can edit data right in the grid and apply changes. Use Append and Delete options from the right-click menu to add records to the tables or delete them.

Card View is another good way of data presentation. It shows each data row as a card where the row number is placed as the card caption. Using this mode, you get accurately organized data, which is convenient for analysis and great-looking when printed.

Paginal Mode will suit you when retrieved data contains thousands or even more records. You can set a number of rows on the page to display the data in parts.

Auto-search Mode allows you to instantly find data in the columns. Click any field in the required column and type the first characters the required field contains. If you made a mistake when typing, press Backspace and type the string again. You will be automatically moved to the found field.

Data Observer Mode allows you to quickly browse data of tables or views just by clicking them in Database Explorer. It's no magic, just turn on this mode and see the results. It looks like "hands-free query creating, isn't it?

Cached Updates Mode - use it to control updates of any separate database object. When you turn on this mode for some object and begin changing its data, updates are stored locally on the client side, until you click the Apply Changes button. If you do not want to apply the changes, click the Cancel Changes button. When you close the Data window in Cached Updates Mode, you will cancel all changes made since the last Apply command.

When dealing with BLOB fields, open LOB Viewer window, which automatically detects the type of the content in this field and represents it as an image, text, rich text, or hex dump.

Choose Data export option to export retrieved data to 10 popular file formats (CSV, DBF, HTML, MS Access, MS Excel, ODBC, PDF, RTF, Text, and XML) with additional settings. Query Builder offers a convenient wizard to make export accurate and customizable. For example, while exporting data to MS Excel, you can choose separate colors for the text and background in table's header and rows, change border width and font settings, and preview the results.

Print your data just from the grid using the Print option from the right-click menu. To print the diagram with the query elements, select the required section on the diagram and in the top menu click File→Print.

The list of Query Builder's features can be continued. You can choose them to empower creating and managing queries. Query Builder has become popular since it's been implemented as part of dbForge Studio for MySQL and dbForge Fusion for MySQL.

Summary

Whether you are a newbie in building queries or a professional with solid expertise, the industry offers a wide choice of full-featured IDEs, highly-tailored powerful tools, or a variety of add-ins to meet your needs. You can decide in favor of any.

Query Builder is a reliable tool, which will assist you where you lack skills, automate your routine tasks, and provide the desired opportunities to make maximum use of query results. Using Query Builder, you will appreciate building queries, as you will get a clear understanding what to do and how to do, so that your tedious query creating will become easy and almost hands-free.