MySQL provides three main GUI client programs for use with MySQL Server. The manuals for these GUI tools are available online.
- EMS SQL Manager for MySQL is a high performance tool for MySQL database administration and development.
- SQL Maestro for MySQL Overview: is the premier MySQL admin tool for MySQL database management, control and development.
- SQLyog MySQL GUI is the most powerful MySQL manager and admin tool, combining the features of MySQL Query Browser, Administrator, phpMyAdmin and other MySQL Front Ends and MySQL GUI tools in a single intuitive interface.
- MySQL Query Browser: This graphical tool is used for creating, executing, and optimizing queries on MySQL databases.
- MySQL Administrator: This tool is used for administering MySQL servers, databases, tables, and user accounts.
- MySQL Migration Toolkit: This tool helps you migrate schemas and data from other relational database management systems for use with MySQL.
- Other MySQL GUI Tools
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).
The following information is required to make a connection:
- STORED CONNECTION: Under Windows, you can give a preconfigured connection name, containing often used connection parameters, saving typing every time.
- On Linux - After giving all additional parameters, you choose from a list box the entry SAVE THIS CONNECTION. Then a dialog appears in which you can give the connection a name.
- SERVER HOST: Network host name or IP of the computer on which the MySQL server is running. If it is a local computer, use localhost.
- PORT: By default, the connection takes place via the TCP/IP protocol, where the MySQL default port 3306 is used. Use another port number if MySQL server is configured to use one.
- USERNAME AND PASSWORD: MySQL user name and the associated password. The password is not stored with connection data for reasons of security and must be supplied each time.
- DEFAULT SCHEMA (Query Browser): Name of the database on which you'll execute SQL commands. You may leave the field empty and choose a database later with FILE | CHANGE DEFAULT SCHEMA.
- DETAILS: In rare cases it is necessary to input optional connection parameters in this field. If the MySQL server is configured without TCP/IP for security reasons, under Windows enter the name of the Named Pipe; under Linux, enter the file name of the Socket File.
You can store often-used connection parameters into predefined connection settings, as shown:
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.
- Query Area - This area allows for inputting your desired query.
- Result Area - This area shows the results after the query is executed.
- Query Toolbar - Basic buttons for executing queries.
- Advanced Toolbar - Contains transaction buttons like Start, Commit, Rollback, Query Building buttons like Select, From, Where, Group By and other operations.
- Object Browser - View databases, tables, views, bookmarks, and history.
- Information Browser - Part of the sidebar to look up syntax, built-in functions, parameters, etc
- Edit/Navigation Bar - Bottom bar to go into Edit mode and navigate the result set.
A few keyboard shortcuts make using the MySQL Query Browser more efficient and convenient:
- Ctrl+Return executes the current SQL command (like EXECUTE).
- Ctrl+Shift+Return executes the command but shows the result in a new dialog sheet. This has the advantage that the results of previous commands remain available.
- F11 changes the arrangement of the individual windows and enlarges the input region for SQL commands. This makes it easier to enter long SQL commands. If you press F11 again, the standard layout is restored.
- F12 closes the individual windows except for the current result region. This makes it easier to read extensive results. Pressing F12 again restores the original window layout.
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:
Execute the query and see results:
This toolbar contains some query and navigation related buttons.
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.
The following image shows the view of the Result Area from a query:
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.
A view of the Advanced Toolbar to form a query:
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:
- Click on the button SELECT.
- Expand table film to see its columns.
- Click on the columns title, rating, and rental_rate of the film table.
- Click on the button WHERE.
- Click on the column film_id of the film table.
- Type in =101 (for the WHERE condition) from the keyboard.
- Click on the button ORDER.
- Click on the column title of the film table.
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
ORDER BY f.`title`
- f is an alias for the flim table. MySQL Query Browser does not use the optional AS.
- Execute: Click on the EXECUTE button or hit Ctrl+Enter to run the SQL statement and see its results.
- Save: Select FILE | SAVE AS from the menu to save the query to a file.
- Exporting results: With FILE | EXPORT RESULTSET you can export the most recent result of SELECT commands in the formats CSV, HTML, XML, and Excel.
- View BLOBs: The content of BLOB columns are not displayed by default. You can view BLOB data in a separate editor window, even dump the blob to a file.
- Explain Query: The button EXPLAIN gives internal, execution-related information about the planned execution of the most recent SQL command by MySQL. The meaning of various aspects of the Explain Plan is discussed in another lesson.
- Updating Data: We can execute UPDATE, INSERT, DELETE, and other data altering commands.
- Transactions: With InnoDB tables, we can execute transaction-oriented commands via buttons for START TRANSACTION | COMMIT | ROLLBACK.
- Refresh: In a multi-session, multi-user situation, the displayed result set may be refreshed to get the latest matching rows.
- Multiple Result Regions: With FILE | NEW QUERY TAB you open an additional SQL input field and result region. In this way you can test several SQL commands in parallel.
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.
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:
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.
The Information Browser provides access to all information that is not directly related to actual data within your database.
- The Syntax Browser - A convenient reference to MySQL query syntax, it is your quick help in finding the correct syntax for building up the queries.
- The Function Browser - The Function Browser is a quick reference to the various functions built into MySQL.
- The Parameter Browser - The Parameter Browser provides different local, global, and dynamic parameters that can help build your queries.
- Local parameters affect the query in the current query window only.
- Global parameters affect all queries.
- Dynamic parameters are generated automatically from existing queries.
- The Transaction Browser - A single transaction could be made up of many queries. The Transaction Browser lists all queries that make up a single transaction and serves as a history for a single transaction.
In the Object Browser, you can view the databases and their various components in a nested tree-style.
- Database Browser - The Database/schemata Browser is the primary screen of the Object Browser. You can use the Database Browser to select tables and fields, edit tables, create new tables and databases, and drop tables and databases. The Database Browser can be used to set the default database, which is required before you can issue queries against tables.
- Result 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. The Bookmark Browser is one of a number of XML files use for internal purposes by the Query Browser.
- The History Browser - With the History Browser you can browse through all the queries you have previously issued. 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.
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.
The Script Debugging Buttons
The following buttons are available when using the Script Editor:
- Execute: Execute the script from the beginning to the end of the script.
- Continue: Execute the script from the current position or beginning and stop for errors or break points.
- Step: Continue executing the next statement; functions will be executed but not stepped into.
- Pause: Pauses script execution and highlights on the next statement to be executed.
- Stop: Terminate execution of the script.
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.
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.
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:
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:
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 browser based, allowing you to easily manage remote MySQL databases from anywhere you have access to the web. SSL is also transparently supported, allowing for encrypted administration if your server offers this feature.
Administrators can exercise complete control over user privileges, passwords, and resource usage, as well as create, delete, and even copy user accounts.
Real-time interfaces are available for viewing uptime information, query and server traffic statistics, server variables, and running processes.
Developers from around the world have translated phpMyAdmin's interface into 50 languages, including English, Chinese (traditional and simplified), Arabic, French, Spanish, Hebrew, German, and Japanese.
phpMyAdmin offers a highly optimized point-and-click interface that greatly reduces the possibility of user-initiated errors.
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.
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.