Chapter 21. A Tour of the MySQL Query Browser

Table of Contents

21.1. The Main Query Window
21.2. The Query Toolbar
21.2.1. The Navigation Buttons
21.2.2. The Query Area
21.2.3. The Action Buttons
21.3. The Advanced Toolbar
21.4. The Result Area
21.5. The Object Browser
21.5.1. The Database Browser
21.5.2. The Bookmark Browser
21.5.3. The History Browser
21.6. The Information Browser
21.6.1. The Syntax Browser
21.6.2. The Function browser
21.6.3. The Parameter Browser
21.6.4. The Transaction Browser
21.7. The Script Editor
21.7.1. The Script Debugging Buttons
21.7.2. The Script Area

21.1. The Main Query Window

Once you successfully connect to a MySQL server you are presented with the main query window of the MySQL Query Browser. All of the application's functionality is available through this window.

Figure 21.1. The MySQL Query Browser Main Query Window

The MySQL Query Browser main query
          window

The main query window is divided up into several sections:

  • Query Toolbar: The query toolbar is where you create and execute your queries. It is composed of three navigation buttons (Go Back, Next, Refresh), the query area, two action buttons (Execute and Stop), and a status indicator.

  • Advanced Toolbar: The advanced toolbar contains three sets of buttons: the Transaction Buttons (Start, Commit, Rollback), the query management buttons (Explain, Compare), and the query building buttons (Select, From, Where, and so on.)

    The Advanced Toolbar is only visible when the Show advanced toolbars and Show composer buttons toolbar options in the Browser Options screen are checked. See Section 23.5, “The Browser Section”.

  • Result Area: All query results are displayed in the result area. You can have multiple tabs active at one time, allowing you to work on multiple queries. The result area can be split vertically and horizontally for performing comparisons, and queries in different parts of a split result area can be joined together for master-detail analysis.

  • Object Browser: The object browser is part of the sidebar and allows you to manage your databases, bookmarks, and history. You can choose which database and tables to query, add commonly used queries to a collection of bookmarks, and browse through previously issued queries in order to use them again.

    If there are many schemata on your MySQL server, the search field with the magnifying glass icon may be handy for filtering the schemata you are looking for. Typing in t or T, for example, will set the filter to schemata whose names begin with a t. The filter works in a case-insensitive fashion.

    You may also filter schemata using the wild card ? to stand in for any single character and * for zero to any number of characters. For example the string *boo?* will find schemata with the names books, coursebooks, and bookings.

  • Information Browser: The Information Browser is part of the sidebar and is used to look up built-in functions, and to manage queries that contain parameters. One example of this is when establishing a master-detail pair of queries. The data in the master query is available as parameters to the detail query.

Using the View menu, most of these sections can be displayed and hidden by checking and unchecking the elements you wish to view.

Each of these topics are covered in more detail in the sections that follow.

21.2. The Query Toolbar

All queries, whether generated automatically, graphically, or manually, appear in the Query Toolbar. The simplest way to use the MySQL Query Browser is to type a query into the query area and click the execute button. A statement terminator such as ; or \G is not required.

21.2.1. The Navigation Buttons

To the left of the query area are the navigation buttons. The navigation buttons allow you to browse through your query history so that you can review and re-execute a previously executed query. The query history is available through the object browser.

Clicking the Go Back button loads the previous query in your history, whereas the Go Next button loads the following query. Only queries that execute without errors are added to your history.

As you navigate with the Next and Go Back buttons, the queries you navigate through are not executed unless you explicitly execute them by clicking the Execute button. The Refresh button re-executes the last executed query, which may or may not be the current query in the query area.

21.2.2. The Query Area

The query area is where the text of all queries and statements are displayed. By default, the query area is three lines high and automatically expands to a maximum of ten lines in height. For queries longer than ten lines, the query area is scrollable.

For additional space, you can press the F11 key to maximize the query area. You can also do this by choosing Maximize Query Area from the View menu. When the query is maximized, line numbers are displayed and the query area can be resized by clicking and dragging the line that divides the query area from the result area. Under Windows, pressing the F11 key again reverts to the normal view. Under Linux choose the Normal View menu option from the View menu.

Figure 21.2. The Query Area Expanded to Ten Lines

The query area expanded to ten
            lines

The following commands are available by right clicking the query area: Cut (Ctrl+X), Copy (Ctrl+C), Paste (Ctrl+V), Clear (Ctrl+N), Open Query ... (Ctrl+O), and Save Query As ... (Ctrl+S). The Open and Save commands load the contents of an SQL file into the query area or save the contents of the query area into a text file.

21.2.3. The Action Buttons

To the right of the query area are the action buttons. The Execute button executes any queries in the query area, whereas the Stop button ceases execution.

If you click the down arrow below the Execute button, there are three potential execution options:

  • Execute (Ctrl+Enter): This executes the query and displays the results in the currently active result area.

  • Execute in new Tab (Ctrl+Shift+Enter ): This executes the query and displays the results in a newly created result area.

  • Split Tab and Execute (Ctrl+Alt+Enter ): This splits the result area horizontally and then displays the query results in the lower half of the result area.

To the right of the action buttons is the status indicator. The status indicator shows the standard MySQL logo when the MySQL Query Browser is idle, and displays an animated logo when the MySQL Query Browser is busy processing a query. Both icons can be seen in the following illustration:

Figure 21.3. The MySQL Query Browser Status Icons

The MySQL Query Browser status icons

21.3. The Advanced Toolbar

Below the query bar is the Advanced Toolbar. The Advanced Toolbar contains a set of buttons for transaction control, query management, and query building.

Note

The Advanced Toolbar is only available under Windows.

Figure 21.4. The Advanced Toolbar

The advanced toolbar

The left panel of the advanced toolbar contains the transaction control buttons. From left to right, the buttons allow you to start, commit, and roll back a transaction. As when using the command-line client, you can only use transactions with table handlers that support them (InnoDB for example). For more information on transactions, see The InnoDB Transaction Model and Locking.

The center panel provides buttons for query management. The Explain button can be used to get the EXPLAIN output for the current query from the MySQL server. When you use this feature, the results are shown below the result set window. To remove this window right click anywhere in the window and choose the Remove Resultset option.

The Compare button allows you to compare the results of two queries.

The next panel contains the query building buttons. You can use these buttons to build a query visually by clicking on the tables and columns that you wish to involve in your query, using specialized mouse pointers to indicate which part of the query the different fields and tables occupy.

The right panel contains the Create View button. More information on the Create View button can be found in the Creating Views section.

21.4. The Result Area

All query results are displayed in the result area. Within the result area you can use multiple tabs, and individual result areas can be split either vertically or horizontally. New tabs can be created either by choosing the Execute in new Tab option on the query bar, or by clicking the New Tab button at the top of the result area. In addition, you can also right click on the current result area and choose the Add new Tabsheet (Ctrl+T) option.

Individual tabs are closed by either right clicking within the result area and choosing the Remove Tab option, or by clicking the X icon on the tab you wish to close.

To split a result area, right click on it and choose either the Split Tab vertically or the Split Tab horizontally option. After splitting the result area you can then choose one half of the result area and view result sets within it. You can remove sections of the result area by right clicking on the section you wish to remove and clicking Remove Resultset.

When navigating a particularly large result set, you can press the F12 key to maximize the result area or, alternately, you can choose the Maximize Tabsheets option of the View menu. Under Windows, pressing F12 again will restore the result area to its original size. Under Linux, to revert to the normal view choose the Normal View menu option under the View menu.

The result sets of some queries do not display well in the Results Area; SHOW CREATE TABLE tblname, for example. For a better view of the results of such queries, right click the row you wish to view and choose the View Field in Popup Editor option from the pop-up menu.

The result area can be used to review and edit the results of a query, with editing permitted as long as the query is based on a single table and there is sufficient key information to uniquely identify rows. To edit the contents of the result area you must enable edit mode through the use of the Edit button at the bottom of the result area. Any edits you make are not immediately applied, but instead you need to click the Apply Changes button next to the Edit button. Clicking the Discard Changes button throws away any changes you have made to the data.

21.5. The Object Browser

Use the object browser to browse your server's databases, bookmarks, and query history.

21.5.1. The Database Browser

The database (schemata) browser is the primary screen of the object browser. You can use the database browser not only to select tables and fields to query, you can also edit tables, create new tables and databases, and drop tables and databases. The database browser can be used to set the default database; this is required before you can issue queries against tables.

Figure 21.5. The Database Browser

The database browser

The current default database will always be highlighted with bold text. To change the default database, right click on the desired database and choose the Make Default Schema option or simply double click on the desired database.

You can filter the databases shown by using the search bar at the top of the database browser. As you fill in the search bar, databases that do not match your search are hidden from view. You can clear the search bar by clicking the X button on the right side of the search bar. All databases will once again be displayed. You can specify what objects are filtered by clicking on the magnifying glass icon at the left side of the search bar and choosing the appropriate option. Options include Schemata, Schema Assets, Columns/Indices and Custom Selection.

To view a database's tables, stored procedures, and views, click the black arrow on the left of the database name. You can view a table's columns by clicking on the black arrow to the left of the table name. Columns that form part of a primary key have a small key icon to the left of their name. Otherwise they have a blue diamond icon.

When you click the black arrow to the left of a view, the columns that form the view are shown. When you click the black arrow to the left of a stored procedure or stored function, the arguments of the stored procedure or function are shown.

To create a new database, right click within the database browser and choose the Create New Schema option. To create a new table, right click the database you wish to add a table to and choose the Create New Table option. To create a new view, right click within the database you wish to add a view to and choose the Create New View option. To create a new stored procedure or stored function, right click within the database you wish to add to and choose the Create New Procedure / Function option. You can drop objects by right clicking on the object you wish to drop and choosing the appropriate option (Drop Schema, Drop Table, etc.).

You can edit an object by right clicking on it and choosing the appropriate edit option (Edit Table, Edit View, etc.). When you choose the Edit Table option, the MySQL Table Editor will be displayed with the selected table. For more information on editing tables, see The MySQL Table Editor. When the Edit View, Edit Function, or Edit Procedure options are chosen, the CREATE VIEW, CREATE PROCEDURE, or CREATE FUNCTION statement will be shown in a new tab using the Script Editor. It is possible to edit all server functions/procedures at once by choosing the Edit All Stored Procedures / Functions option from the Script menu.

If you need the CREATE statement for any object, right click on the object and choose the Copy SQL to Clipboard option. The appropriate CREATE statement will be copied to the clipboard and can be used to recreate the selected object.

Note

Only the object itself is created with the Copy SQL to Clipboard command, the statements required to populate the object are not created.

21.5.2. The Bookmark Browser

You can place your more commonly used queries in bookmarks so that you can quickly retrieve them and re-use them later. To add a query to your bookmarks, highlight and drag it from the query area into the bookmark browser.

Your bookmarks can be organized into folders and subfolders to help with management of your queries. To add a new subfolder right click on an existing folder and choose the Create Bookmark Folder option. You can remove bookmarks and folders by right clicking on them and choosing the Delete Items option. After you confirm that you wish to delete the item it will be removed from your bookmark list.

Warning

If you delete a folder all items and subfolders within the folder are also deleted.

The Bookmark Browser is one of a number of XML files use for internal purposes by the Query Browser. For a listing of these files see Appendix E, XML Files Used by MySQL Query Browser.

21.5.3. The History Browser

With the history browser you can browse through all the queries you have previously issued. To expand a given day's queries, double click on the day. To load a history item into the query area, double click on it or drag it to the query area.

You can remove history items by right clicking on them and choosing the Delete Selected History Entries option from the drop-down menu. You can also use the Clear History option to erase all history entries.

You can create bookmarks from history items by right clicking on a selected history item and choosing the Add History Item as Bookmark menu option.

21.6. The Information Browser

The Information Browser provides access to all information that is not directly related to actual data within your database. The parameter browser provides different local, global, and dynamic parameters that can help build your queries, whereas the syntax browser provides a convenient reference to MySQL query syntax. The Function browser is a quick reference to the various functions built into MySQL. The Transaction Browser lists all queries that make up a single transaction and serves as a history for a single transaction.

21.6.1. The Syntax Browser

The syntax browser provides a quick reference tool for determining proper syntax to use when constructing queries. By double clicking on the various types of syntax (SELECT, REPLACE, and so forth) you can bring up reference information using the in-line help system.

Reference information is displayed in a new tab within the result area.

To expand a given syntax category, double click on the category header.

21.6.2. The Function browser

The function browser provides a quick reference tool for usage of the built-in functions of the MySQL server. By double clicking on the various functions you can bring up reference information using the in-line help system.

To expand a given function category, double click on the category header.

Reference information is displayed in a new tab within the result area.

21.6.3. The Parameter Browser

The parameter browser contains all the local, global, and dynamic parameters that can be included in your query. Local parameters affect the query in the current query window only. Global parameters affect all queries. Dynamic parameters are generated automatically from existing queries.

Parameter categories can be displayed and hidden by clicking on the Global Params, Local Params and Dynamic Params headings.

To set the value of a parameter, click on the value and press the F2 key, or double click the value.

To add a new parameter, right click within the parameter browser and select the Add Parameter option.

To delete a parameter, right click on the parameter and choose the Delete Parameter option.

To change a Local parameter into a Global parameter, right click on the parameter and choose the Move Parameter to Global Parameters option.

21.6.4. The Transaction Browser

The Transaction Browser provides a list of all statements that have been executed in the current transaction. The Transaction browser will be displayed when you start a transaction using the transaction control buttons.

21.7. The Script Editor

The Script Editor provides an alternate interface for use when dealing with large SQL scripts that involve multiple SQL statements.

Figure 21.6. The Script Editor

The script editor

The Script Editor displays within an individual tab in the Result Area. When a Script Editor tab is active, the Advanced Toolbar buttons are replaced with script debugging buttons.

To open a script in the Script Editor, choose the Open Script ... option from the File menu. You can also press the Ctrl + O keys to open an SQL script.

To create a new Script Editor window without loading a script, choose the New Script Tab option from the File menu.

If you're experiencing problems with character sets (for example, Spanish accented characters, Russian, or Chinese characters aren't displayed properly), here's some advice:

Avoid ANSI encoding in your script files. Rather than using ANSI, save scripts in UTF-8 encoding. Take care when loading script files generated by other tools. Many tools save files in ANSI encoding, which in turn means you have to open them as ANSI in Query Browser (see the Files of type combo box in the file open dialog):

Figure 21.7. The File Open Dialog

The file open dialog

There are significant limitations to ANSI formatted files; the current system locale must be the same as the machine where the file originated. For example, you cannot store a script in ANSI on a French version of Windows and expect it to load correctly on a Spanish version of Windows. Because of this and many other limitations we strongly recommend always using Unicode for scripts. It doesn't matter whether you use UTF-8 or UTF-16, since both are Unicode transformation formats and can be read properly by any of the MySQL GUI tools. Using UTF, you could even write Spanish text with Chinese comments on a German version of Windows.

The MySQL Query Browser forum provides many contributions by users of non-Latin1 characters sets; see http://forums.mysql.com/list.php?108.

21.7.1. The Script Debugging Buttons

The following buttons are available when using the Script Editor:

  • Execute: Execute the script from the beginning and do not stop execution until the end of the script is reached.

  • Continue: Execute the script from the beginning (or current position) and stop for errors or break points.

  • Step: Execute the next statement, functions will be executed but not stepped into.

  • Pause: Stops script execution and highlights on the next statement to be executed.

  • Stop: Stop execution of the script.

21.7.2. The Script Area

The current script being edited in the Script Editor is displayed in the Script Area. The Script Area features line numbering and syntax highlighting to make it easier to work with scripts.

Script lines that are marked with a blue dot to the left of the line are eligible for use as breakpoints. To set a line as a breakpoint, click the blue dot. You can also set a breakpoint by positioning the cursor in the line you wish to break at and clicking the Toggle Breakpoint option in the Script menu. To remove all breakpoints, choose the Clear All Breakpoints option of the Script menu.

To execute a selection of statements, highlight the statements you wish to execute and choose the Execute Selection option of the Script menu.