Creating a View with MySQL Query Browser

Exclusive offer: get 50% off this eBook here
Creating your MySQL Database: Practical Design Tips and Techniques

Creating your MySQL Database: Practical Design Tips and Techniques — Save 50%

A short guide for everyone on how to structure your data and set-up your MySQL database tables efficiently and easily.

$11.99    $6.00
by Djoni Darmawikarta | August 2008 | MySQL PHP

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.

Creating a View with MySQL Query Browser

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

Creating a View with MySQL Query Browser

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

Creating a View with MySQL Query Browser

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

Creating a View with MySQL Query Browser

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

Creating a View with MySQL Query Browser

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.

Creating a View with MySQL Query Browser

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.

Creating a View with MySQL Query Browser


Creating your MySQL Database: Practical Design Tips and Techniques A short guide for everyone on how to structure your data and set-up your MySQL database tables efficiently and easily.
Published: November 2006
eBook Price: $11.99
Book Price: $19.99
See more
Select your format and quantity:

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.

Creating a View with MySQL Query Browser

Enter the name of your view.

Creating a View with MySQL Query Browser

Write view definition.

Creating a View with MySQL Query Browser

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

Creating a View with MySQL Query Browser

Dropping a View

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

Creating a View with MySQL Query Browser

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

Creating a View with MySQL Query Browser

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.

Creating a View with MySQL Query Browser

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

Creating a View with MySQL Query Browser

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

Creating a View with MySQL Query Browser

Select the script you want to open.

Creating a View with MySQL Query Browser

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.

Creating a View with MySQL Query Browser

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.

Creating a View with MySQL Query Browser

Creating a View with MySQL Query Browser

Summary

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


Creating your MySQL Database: Practical Design Tips and Techniques A short guide for everyone on how to structure your data and set-up your MySQL database tables efficiently and easily.
Published: November 2006
eBook Price: $11.99
Book Price: $19.99
See more
Select your format and quantity:

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.

Books From Packt

Mastering phpMyAdmin 2.11 for Effective MySQL Management
Mastering phpMyAdmin 2.11 for Effective MySQL Management

BSelling Online with Drupal e-Commerce
Selling Online with Drupal e-Commerce

Learning Drupal 6 Module Development
Learning Drupal 6 Module Development

Building Websites with Joomla! 1.5
Building Websites with Joomla! 1.5

WordPress Complete
WordPress Complete

OpenCms 7 Development
OpenCms 7 Development

WordPress Theme Design
WordPress Theme Design

Drupal 5 Themes
Drupal 5 Themes

 


 

 

No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
f
f
q
U
E
n
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software