Chapter 22. Using The MySQL Query Browser

Table of Contents

22.1. Entering and Editing Queries Manually
22.2. Building Queries
22.3. Creating Views
22.4. Navigating Result Sets Within the Result Area
22.5. Exporting Result Sets
22.6. Handling BLOB and TEXT Columns
22.7. Editing Result Sets Within the Result Area
22.8. Comparing Result Sets
22.9. Managing Stored Procedures and Functions with MySQL Query Browser
22.10. Editing Queries From a Development Tool
22.11. Using the Regex Text Importer Tool

In this chapter we further describe the tools available in the MySQL Query Browser by using practical examples.

22.1. Entering and Editing Queries Manually

The query browser is most commonly used for executing queries and analyzing their results. The most direct way to create a query is to type it directly into the query area. As you type in your query, the SQL syntax portions of the query (SELECT, FROM, WHERE, and so on) are highlighted in blue.

As you enter your query, the query area will expand from an initial three lines in height to a maximum ten lines in height. For additional space, you can press the F11 key to maximize the query area. You can also choose the Maximize Query Area of the View menu to maximize the query area. When the query is maximized, line numbers are displayed for the query, and the query area can be resized by clicking and dragging the line that divides the query area from the result area. To restore the query area, press the F11 key again.

Once you have entered a query, click on the Execute button and your query results are displayed in the result area. You can also press Ctrl+ Enter to execute the query. If there is an error in your query, an error area appears at the bottom of the result area and displays the relevant error message and error number.

In addition to loading the query results into the current active result area, you can also create a new result area for the results of your query or split your current result area and load results into the new section.

To execute the query and load the results into a new result area click the down-arrow below the Execute button and choose the Execute in new Tab option or press Ctrl+Shift+Enter .

To split the active result area and display the query results click the down-arrow below the Execute button and choose the Split Tab and Execute option or press Ctrl+Alt+Enter .

You must set a default database before you can query specific tables (though you can still perform queries that are not database-specific). You can set the default database at the connection screen, or by right-clicking on a database in the database browser and choosing Make Default Schema, or by choosing the Change Default Schema option from the File menu.

22.2. Building Queries

One feature of the MySQL Query Browser is the ability to build queries. Use the database browser to select the columns and tables you wish to query and have the query created automatically based on your choices.

The first step to building a query is to choose a table to query. Click and drag a table to the query area to start a query. For example, by dragging the City table to the query area, SELECT * FROM City C is added to the query area. You can also double-click a table to start a new SELECT query.

Figure 22.1. The Table Tool

The table tool

When you select a table from the database browser and drag it over the query area, a table tool with some query composition actions is displayed. Drop the table you're dragging on the desired action and the query is modified accordingly. The following actions are possible:

  • SELECT replaces the current statement with a SELECT query containing the dragged table.

  • Add Table adds the dragged table to the list of tables in the current SELECT query

  • JOIN Table: If a SELECT query is already in the query box, with a table in it, the new dragged table is added and the appropriate WHERE clauses to perform a JOIN will be added

  • LEFT OUTER JOIN is the same as the previous, but does a LEFT OUTER JOIN instead of a JOIN

  • UPDATE replaces the current statement with an UPDATE statement containing the dragged table

  • INSERT replaces the current statement with an INSERT statement containing the dragged table

  • DELETE replaces the current statement with a DELETE statement containing the dragged table

Tables are joined based on identical column names for MyISAM tables and foreign key information for InnoDB tables.

Once a table is selected, you can choose specific columns to query; click the Select button from the query building buttons on the button bar. Your mouse pointer is changed to a Select pointer, which you can use to choose columns from the database browser. For example, by clicking on the Id, Name, and Country fields of the world sample database, the query SELECT C.Id, C.Name, C.Country FROM City C is built in the query area.

Once you have chosen the columns you wish to query, you can use the other query building buttons to complete your query with WHERE, GROUP BY, and ORDER BY clauses. When a new section of the query is added with the query building buttons, the cursor in the query area is placed in position for editing; if you click a field with the WHERE pointer, the cursor is in position for you to type in the details of the WHERE clause.

You can change between the different query building pointers by clicking on the query building buttons in the button bar, or by using a combination of Ctrl+Alt and the first letter of the pointer you wish to use (Ctrl+Alt+S for SELECT, Ctrl+Alt+W for WHERE, and so forth.)

If the query building buttons are not visible, they can be displayed through the Browser Options. See Section 23.5.1, “Display Options” for more information.

22.3. Creating Views

The simplest way to create a view using the MySQL Query Browser is to use the Create View button. Execute a query that represents the view you wish to create. Once the query is executed, click the Create View button, provide a name for the view, and the view is created.

Note

Creating views is not yet supported on all Operating systems.

You can also create a view by right-clicking on the database you wish to add a view to in the database browser and choosing the Create New View option. After you enter a name for the view, a view template will be displayed in the script editor.

22.4. Navigating Result Sets Within the Result Area

Once you have successfully executed a query you can then view and manipulate the result set within the result area.

You can navigate the result area using the arrow keys, tab key, and PageUp/PageDown keys. The Home and End keys can be used to move to the first and last column within a given row. Your current position within the result set is shown in the bottom-left corner of the application window. The First and Last buttons at the bottom of the result area can be used to move to the first and last rows of the result set.

NULL data will be indicated with a special NULL icon to differentiate NULL data from empty strings. BLOB fields will be empty and will have a special BLOB icon.

When navigating a particularly large result set, you can press the F12 key to maximize the result area. You can also choose the Maximize Tabsheets option of the View menu to maximize the result area. Pressing F12 again will restore the result area to its original size.

To search for a particular value within the result set, click the Search button. To see additional search options, click the Details >> button. The following options are available:

  • Case Sensitive: The search is performed in a case-sensitive manner. By default, searches are not case-sensitive.

  • Whole Words Only: The search does not allow partial matches. By default, partial matches are allowed (for example, Edm will match Edmonton).

  • Search From Top: The search begins with the first row in the result set.

  • Search From Cursor: The search begins from the currently selected row.

  • Search all Text / Columns: The search will involve all columns of the result set.

  • Search Only in Selected Text / Column: The search will only involve the currently selected column(s) of the result set.

  • Search Up: The search will scan the result set, starting at the designated start location and moving upwards.

  • Search Down: The search will scan the result set, starting at the designated start location and moving down.

To perform a search and replace operation on a result set, click the Replace tab in the search dialog window. The options for performing a replace operation are the same as for a search. To replace a single instance of a string, click the Replace button. To replace all instances of a string, click the Replace All button.

22.5. Exporting Result Sets

You can export any result set from MySQL Query Browser by right-clicking within the result set and choosing an option from the Export Resultset sub-menu. You can choose to export the result set in CSV, XML, HTML, Microsoft Excel XLS or PLIST formats.

22.6. Handling BLOB and TEXT Columns

The MySQL Query Browser provides functionality for dealing with BLOB and TEXT columns through a series of special icons.

Figure 22.2. The BLOB Handling Icons

The BLOB handling icons

These icons appear in any BLOB or TEXT columns in your result set. From left to right the following icons are available:

  • Open File: This icon looks like a file folder and is used to open a file for loading into the field.

  • View: This icon looks like a magnifying glass and is used to open the field viewer to view the contents of the field. The field viewer can be used to view TEXT fields and BLOB fields that contain images.

  • Edit: This icon looks like a pencil and opens the field viewer in edit mode, allowing you to make changes to the data and apply the changes to the result set.

  • Save: This icon looks like a floppy disk and is used to save the contents of a TEXT or BLOB field into a file.

  • Clear: This icon looks like an X within a black box and is used to clear the contents of a TEXT or BLOB field.

Only the View and Save icons are visible if you have not enabled edit mode. See Section 22.7, “Editing Result Sets Within the Result Area” for information on editing result sets.

The functionality represented by the icons is also available by right-clicking on the field. The blob handling icons can be displayed and hidden through the display options. See Section 23.5.1, “Display Options” for more information.

22.7. Editing Result Sets Within the Result Area

When a query is based on a single table and there is sufficient key information, the result set can be edited from within the result area. To edit a result set click the Edit button at the bottom of the window. If the Edit button is not active, your result set is not editable. (Under Linux this button is labeled Start Editing.)

Note

Queries derived from a single table lacking a primary key or from more than one table are not editable; they are read-only.

Once in edit mode you can insert, update, and delete rows from the result set. Navigate the fields using the Tab and arrow keys, and press Enter to edit the content of a field. You can also double-click a field to make it editable. When editing a field, use the tab key to move to the next editable field. All edited fields are highlighted in blue for easy identification.

To add rows to the result set, scroll to the blank row at the bottom of the result area and fill in the fields. All new rows are highlighted in green.

To delete a row right-click on the row and choose the Delete Row option. All deleted rows are highlighted in red.

Changes made to the result set are not applied immediately, but instead are cached until the Apply Changes button is pressed. You can abort your edits with the Discard Changes button. Exiting edit mode without choosing to apply or discard your changes prompts you to apply or discard your work.

22.8. Comparing Result Sets

You can compare result sets graphically with the MySQL Query Browser, allowing you to easily determine where rows have been inserted, updated, or deleted.

To compare two result sets, execute the first of the queries you wish to compare. Once the result set has loaded, right-click on the result set and choose the Split Tab Horizontally option. Load your second query into the new section of the result area and click the Compare button to compare the two result sets.

When you have activated the compare mode, both result sets will scroll in unison, both vertically and horizontally. Rows are matched for comparison, with blank rows added when one set has a row that the other set lacks.

If one result set has a row that the other result set does not have, that row is highlighted in green. The other result set has a blank row inserted that is highlighted in red. If both result sets have a matching row, but individual fields are different, those fields are highlighted in blue.

In order to successfully compare two result sets, you need two queries with matching column names and column order. For the MySQL Query Browser to match rows, primary keys must be defined in the tables used.

22.9. Managing Stored Procedures and Functions with MySQL Query Browser

When used with MySQL version 5 and higher, the MySQL Query Browser supports creating, editing, and viewing stored procedures and functions.

Stored procedures and stored functions are displayed in the database browser with a special icon to distinguish them from regular tables and views. Click the arrow to the left of a stored procedure or stored function to display the parameter list for that procedure or function.

Figure 22.3. Stored Procedure in Database Browser

Stored procedure in database
          browser

To edit a stored procedure or stored function, right-click on it in the database browser and choose the Edit Procedure or Edit Function option. This opens a new script editor tab with the selected procedure/function displayed. Once you have finished editing, click the Execute button above the script area or click the Execute option of the Script menu to update the procedure/function.

To create a new stored procedure or function, choose the Create Stored Procedure / Function option from the Script menu. Enter the desired procedure/function name and click either the Create PROCEDURE or Create FUNCTION button. A template similar to the following will be created:

DELIMITER \\

DROP PROCEDURE IF EXISTS `test`.`new_proc`\\
CREATE PROCEDURE `test`.`new_proc` ()
BEGIN

END\\

DELIMITER ;
  

After typing the procedure/function, click the Execute button above the script area or click the Execute option of the Script menu to create it.

To edit all stored procedures/functions at once, choose the Edit All Stored Procedures / Functions option of the Script menu. A new script editing tab will be created, containing all the stored procedures and stored functions for the current default database.

To remove an existing stored procedure or stored function, right-click on it in the database browser and choose the Drop Procedure or Drop Function option.

22.10. Editing Queries From a Development Tool

In order to help programmers optimize and troubleshoot their queries more efficiently, the MySQL Query Browser can copy queries from application code using your favorite integrated development environment (IDE).

Note

This functionality is only available for the Windows version of MySQL Query Browser.

The following PHP code will be used as an example:

$SQL = "SELECT Id, Name, Country FROM City" .
       "WHERE Name LIKE $cityname";

To copy the listing into the MySQL Query Browser, copy the block of code (including the assignment portion), right click within the query area of the MySQL Query Browser, and choose the Paste Clipboard Content as PHP Code option. The nonquery portions of the highlighted area will be stripped and the query will be pasted into the query area.

The dynamic elements of the query are converted into local parameters, visible in the parameter browser:

SELECT Id, Name, Country FROM City
WHERE Name LIKE :cityname

To set the value of a local parameter, highlight the value in the parameter browser and press F2. You can also double-click on the value to edit it. The value you assign will be used when the query is executed.

After editing the query, right-click within the query area and choose the Copy Query as PHP Code option. The surrounding PHP code will be re-inserted along with the modified query. This functionality allows you to edit queries quickly while programming.

22.11. Using the Regex Text Importer Tool

Caution

This feature is currently experimental, and so should be used with caution. Further information will be available in the near future.