Overview of User Interfaces

Saturday, 30 January 2010 09:56
Print
User Rating: / 0
PoorBest 

MySQL provides three main GUI client programs for use with MySQL Server. The manuals for these GUI tools are available online.

In addition, we will also explore the popular phpMyAdmin tool.

Establishing a Connection

Before using MySQL Administrator and the Query Browser, we must establish a connection to the correct MySQL server (and schema).

Make-Connection

The following information is required to make a connection:

You can store often-used connection parameters into predefined connection settings, as shown:

New-Connection

MySQL Query Browser

The main purpose of the MySQL Query Browser is to help you in composing and testing SQL commands. Additionally, the program offers some auxiliary functions: you can insert data into tables, alter data in tables, test regular expressions (for the SQL operator REGEX), read MySQL help texts, etc.

To become familiar with the MySQL Query Browser, input a simple SQL command. SQL keywords are shown automatically in color. Execute the command with the EXECUTE button.

The first window in Query Browser provides you the capability of writing your scripts and seeing the results in the window. You can manage your schemas, manage users, etc.

Query browser is divided into many sections for achieving these tasks.

  1. Query Area - This area allows for inputting your desired query.
  2. Result Area - This area shows the results after the query is executed.
  3. Query Toolbar - Basic buttons for executing queries.
  4. Advanced Toolbar - Contains transaction buttons like Start, Commit, Rollback, Query Building buttons like Select, From, Where, Group By and other operations.
  5. Object Browser - View databases, tables, views, bookmarks, and history.
  6. Information Browser - Part of the sidebar to look up syntax, built-in functions, parameters, etc
  7. Edit/Navigation Bar - Bottom bar to go into Edit mode and navigate the result set.

Keyboard Shortcuts

A few keyboard shortcuts make using the MySQL Query Browser more efficient and convenient:

Main Query Window

This window allows for input and execution of SQL Commands. One can type in a query, build it using buttons, or open a saved query from files as shown: QueryBrowser

Execute the query and see results:

QueryBrowser

Query Toolbar

This toolbar contains some query and navigation related buttons.

QueryBrowser

The simplest way to use Query Browser is to write in a query into the SQL Query Area and click the Execute button. The navigation buttons are a great help in browsing through the history of your queries. If the query succeeds, the results are available in the currently active result area.

Result Area

The following image shows the view of the Result Area from a query:

QueryBrowser

All query results are displayed in the result area. Within this area you can use multiple tabs. 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. The result sets of some queries do not display well in the Results Area.

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 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.

Advanced Toolbar

A view of the Advanced Toolbar to form a query:

QueryBrowser

This area contains a set of buttons for transaction control, query management, and query building.

The left panel of the Advanced Toolbar contains the transaction control buttons. The buttons allow you to start, commit, and roll back a transaction. 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. The next panel contains the query building buttons to build a query visually by clicking on the tables and columns that you wish to involve in your query.

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 most panel contains the Create View button, that allows for creating a view from the current query.

SQL Commands with Mouse Clicks

Using various arrow buttons of the Toolbar, you can assemble SQL commands with minimal typing effort. The following click sequences provide an example:

Note: If you do not see SELECT buttons, click on the menu option VIEW and select Maximize Query Edit or press F11.

From the mouse clicks (and minimal typing) above, the MySQL Query Browser forms the following command:

SELECT f.`title`, f.`rating`, f.`rental_rate` FROM film f
WHERE f.`film_id`=101
ORDER BY f.`title`

History and Bookmarks for SQL Commands

The MySQL Query Browser stores all executed SQL commands automatically in a History List, available in the sidebar to the right of the window. With drag and drop you can move the command into the input region and execute it with a double click.

QueryBrowser

You can give frequently used statements a Caption with Ctrl+B and store them in the bookmark list. The list of Bookmarks is available in the sidebar:

QueryBrowser

Using Scripts: Executing Several Commands

Open a SQL script input area with FILE | NEW SCRIPT TAB. There you can specify several SQL commands separated by semicolons. You can then execute these commands all at once (EXECUTE button) or step by step (SCRIPT menu).

These scripts represent several ordinary SQL commands, not stored procedures. The commands can be stored as a *.sql file using the FILE menu. The history and bookmark functions, however, are not available.

MySQL Help - Information Browser

The MySQL Query Browser can also be used for reading MySQL help. The help texts are accessible via the lists SYNTAX and FUNCTIONS in the sidebar. These are extracts from the online documentation.

QueryBrowser

The Information Browser provides access to all information that is not directly related to actual data within your database.

Object Browser

In the Object Browser, you can view the databases and their various components in a nested tree-style.

QueryBrowser

The Script Editor

This is the area where your current script is being edited.

The Script Editor displays within an individual tab and allows for execution of a script containing multiple statements.

QueryBrowser

The Script Debugging Buttons

The following buttons are available when using the Script Editor:

Changing Data in SELECT Results

With simple SELECT queries containing data from only one table (no JOINs) and without GROUP BY or aggregation functions, the results can be changed. To do so, click on the EDIT button at the bottom of the table. This button is available only for results that can be changed.

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.

To change a table field, you must activate it with a double-click. You simply add new records to the end of the table. To delete a record (a row), execute DELETE ROWS with the right mouse button. Changes take effect only after you click the button APPLY CHANGES.

QueryBrowser

Stored Procedures

The MySQL Query Browser makes available a number of elementary functions for input of stored procedures, which are functions composed of several SQL commands that are controlled directly by the MySQL server. Stored procedures are a characteristic of the MySQL server, while scripts belong to the MySQL Query Browser. We will discuss stored procedures in greater detail in a later lesson.

MySQL Administrator

While the MySQL Query Browser helps in assembling and trying out SQL commands, the MySQL Administrator helps with administrative tasks related to management of a MySQL server. The various operations one can perform using the Administrator are covered in a later lesson.

One common task you may perform with the Administrator is adding users to schemas. Or, you can view the health and general statistics of the server:

Admin-Health

MySQL Migration Toolkit

Organizations around the globe increasingly view MySQL as a viable, cost-efficient alternative to the expensive databases that have drained corporate budgets for years. Yet the cost and effort needed to move existing and generally large data stores can be daunting and discouraging.

The MySQL Migration Toolkit provides a well-planned wizard that can provide valuable migration assistance by accelerating and streamling the process while reducing problems and issues.

Presently, the MySQL Migration Toolkit is able to handle migrating the majority of schemas and data from Oracle, Microsoft Access, Microsoft SQL Server, Sybase and MaxDB. In addition, you can also migrate data from any database supporting a standard JDBC driver.

A Brief Overview of the Migration Process

According to MySQL documentation, migrating data from an external relational database management system to MySQL is outlined as an eight-step process as shown:

The Migration Process

The Migration View:

The MySQL Migration Toolkit Migration Plan

  1. Source/Target Selection: Specify the connection parameters for the source and target database servers.
  2. Object Selection: Select the objects (tables, views, stored procedures) that will be migrated.
  3. Object Mapping: Choose the method used for mapping and transforming the objects.
  4. Manual Editing: Manually edit the new objects to ensure a proper transformation.
  5. Schema Creation: The MySQL Migration Toolkit physically creates the transformed object on the target MySQL server.
  6. Data Mapping: Convey any changes that need to be made to the data as it is migrated.
  7. Bulk Transfer: The MySQL Migration Toolkit transfers the data from the source server to the target server.
  8. Summary (Final step): MySQL Migration Toolkit generates a summary report of the migration process for review.

phpMyAdmin

Although not a product offered by MySQL, phpMyAdmin is such a valuable administration tool that it certainly bears mentioning here. A web-based MySQL administration application written in PHP, phpMyAdmin is used by thousands of developers and is practically a staple among web hosting providers around the globe. It's not only very stable (it has been in development since 1998), but also feature-rich thanks to an enthusiastic development team and user community.

phpMyAdmin is a PHP-based tool to perform the administration of MySQL over the Web. It can be used to create/drop databases, create/drop/alter tables, delete/edit/add fields, execute a SQL statement, define keys, manage privileges, and export data into various formats.

phpMyAdmin offers a number of compelling features:

phpMyAdmin is released under the GNU General Public License. The official phpMyAdmin web site offers source downloads, news, mailing lists, a live demo, and more.

phpMyAdmin

User Interfaces MySQL Conclusion

In this lesson of the MySQL tutorial, we learned about various user interfaces that can be used for querying, managing, importing and exporting data in MySQL.

To continue to learn MySQL go to the top of this page and click on the next lesson in this MySQL Tutorial's Table of Contents.