MySQL Query Browser, one of the open source MySQL GUI tools from MySQL AB, is used for building MySQL database queries visually. In MySQL Query Browser, you build database queries using just your mouse—click, drag and drop!

When you’re creating a query for non-technical users and you don’t want them to see the complexity of the query, you can create a view from the query. Your users can then use the view as a table; for example, they can double-click the view, which will effectively execute its complex query behind the scene.

In MySQL Query Browser, you have two ways to create a view, which Djoni Darmawikarta will demonstrate in this article:

• From an existing query
• Writing from scratch manually

Please refer to an earlier article by the author to learn how to build queries visually.

Creating a View from an Existing Query

To create a view from a query, you must have executed the query successfully. To be more precise, the view is created from the latest successfully executed query, not necessarily from the query currently in the Query Area. To further clarify, the following three examples are cases where the view is not created from the current query:

• Your current query fails, and immediately after you create a view from the query. The view created is not from the failed query. If the failed query is the first query in your MySQL Query Browser session, you can’t create any view.
• You have just moved forward or backward the query in the Query Area without executing it, and then your current query is not the latest successfully executed.
• You open a saved query that you have never executed successfully in your active Resultset.

Additionally, if you’re changing your Resultset, the view created is from the latest successfully executed query that uses the currently active Resultset to display its output.

To make sure your view is from the query you want, select the query, confirm it as written in the Query Area, execute the query, and then, immediately create its view.

You create a view from an existing query by selecting Query | Create View from Select from the Menu bar.

Type in the name you want to give to the view, and then click Create View. MySQL Query Browser creates the view.

When successfully created, you can see the view in the Schemata.

You can modify a view by editing it: Right-click the view and select Edit View.

You can edit the CREATE view statement by right-clicking it and select Edit View.

The CREATE view statement opens in its Script tab. When you finish editing, you can execute the modified view. If successful, the existing view is replaced with the modified one.

To replace the view you’re editing with the modified view, change the name of the view before you execute it. If you want to keep the view you’re editing, remove the DROP VIEW statement.

How best to collect, name, group, and structure your data Design your data with future growth in mind Practical examples from initial ideas to final designs For more information please visit http://www.PacktPub.com/creating-mysql-tips/book

Writing View from Scratch Manually

When you right-click a table, MySQL Query Browser provides you with a skeleton CREATE view statement, in which you can write in your view definition.

Enter the name of your view.

Write view definition.

When you finish writing your view definition, click Execute to create the view.

Dropping a View

To drop a view just right-click the view and select Drop View.

Click OK to confirm that you really want to delete the view; the view will no longer be in the Schemata.

Saving the View as Script

You can save a CREATE view statement as a script file outside of MySQL Query Browser. If you later want to create or re-create the view, you can re-call the script into MySQL Query Browser and execute it.

To save a CREATE view statement, you must have it in a Script tab. Right-click anywhere in the tab and select Save Script As.

Type in the name you want to give to the script file, and then click Save.

To recall a view script, select File | Open Script.

Select the script you want to open.

MySQL Query Browser opens the script in a new Script tab. You can edit the script if you need to, and then to create the view just click the Execute button.

When you’re closing the Script tab (by clicking the red cross sign on its tab header) and you made any change to the script, you’ll be prompted to save the script.

Summary

We have just learned about the MySQL Query Browser and how it is used to create views visually and in an easy manner.

How best to collect, name, group, and structure your data Design your data with future growth in mind Practical examples from initial ideas to final designs For more information please visit http://www.PacktPub.com/creating-mysql-tips/book

About the Author

Djoni Darmawikarta built his career in IBM Asia Pacific and Canada as a software engineer, international consultant, instructor and project manager, for a total of 17 years. He's currently a technical specialist in the Data Warehousing and Business Intelligence team of a Toronto-based insurance company. Outside of his office works, Djoni writes IT articles and books.