Table of Contents
In this chapter we further describe the tools available in the MySQL Query Browser by using practical examples.
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 of the 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 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.
button and your query results are displayed in the result area. You can also pressIn 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 Ctrl+Shift+Enter .
button and choose the option or pressTo split the active result area and display the query results click the down-arrow below the Ctrl+Alt+Enter .
button and choose the option or pressYou 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
, or by choosing the option from the menu.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.
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
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.
The simplest way to create a view using the MySQL Query Browser is to use the
button. Execute a query that represents the view you wish to create. Once the query is executed, click the button, provide a name for the view, and the view is created.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
option. After you enter a name for the view, a view template will be displayed in the script editor.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
and 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 option of the 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
button. To see additional search options, click the 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
button. To replace all instances of
a string, click the button.
You can export any result set from MySQL Query Browser by
right-clicking within the result set and choosing an option from
the CSV
,
XML
, HTML
, Microsoft Excel
XLS
or PLIST
formats.
The MySQL Query Browser provides functionality for dealing with
BLOB
and TEXT
columns
through a series of special 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.
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 Start Editing.)
button at the bottom of the window. If the button is not active, your result set is not editable. (Under Linux this button is labeledQueries 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
option. All deleted rows are highlighted in red.Changes made to the result set are not applied immediately, but instead are cached until the
button is pressed. You can abort your edits with the button. Exiting edit mode without choosing to apply or discard your changes prompts you to apply or discard your work.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
option. Load your second query into the new section of the result area and click the 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.
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.
To edit a stored procedure or stored function, right-click on it in the database browser and choose the
or option. This opens a new script editor tab with the selected procedure/function displayed. Once you have finished editing, click the button above the script area or click the option of the menu to update the procedure/function.To create a new stored procedure or function, choose the
option from the menu. Enter the desired procedure/function name and click either the or 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
button above the script area or click the option of the menu to create it.To edit all stored procedures/functions at once, choose the
option of the 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
or option.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).
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
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
option. The surrounding PHP code will be re-inserted along with the modified query. This functionality allows you to edit queries quickly while programming.