Skip navigation links
9.3 Creating Views »
Section Navigation      [Toggle]

9.2. Building Queries

One feature of the MySQL Query Browser is the ability to build queries. Use the database browser to select the columns and tables you wish to query and have the query created automatically based on your choices.

The first step to building a query is to choose a table to query. Click and drag a table to the query area to start a query. For example, by dragging the City table to the query area, SELECT * FROM City C is added to the query area. You can also double-click a table to start a new SELECT query.

Figure 9.1. The Table Tool

The table tool

When you select a table from the database browser and drag it over the query area, a table tool with some query composition actions is displayed. Drop the table you're dragging on the desired action and the query is modified accordingly. The following actions are possible:

  • SELECT replaces the current statement with a SELECT query containing the dragged table.

  • Add Table adds the dragged table to the list of tables in the current SELECT query

  • JOIN Table: If a SELECT query is already in the query box, with a table in it, the new dragged table is added and the appropriate WHERE clauses to perform a JOIN will be added

  • LEFT OUTER JOIN is the same as the previous, but does a LEFT OUTER JOIN instead of a JOIN

  • UPDATE replaces the current statement with an UPDATE statement containing the dragged table

  • INSERT replaces the current statement with an INSERT statement containing the dragged table

  • DELETE replaces the current statement with a DELETE statement containing the dragged table

Tables are joined based on identical column names for MyISAM tables and foreign key information for InnoDB tables.

Once a table is selected, you can choose specific columns to query; click the Select button from the query building buttons on the button bar. Your mouse pointer is changed to a Select pointer, which you can use to choose columns from the database browser. For example, by clicking on the Id, Name, and Country fields of the world sample database, the query SELECT C.Id, C.Name, C.Country FROM City C is built in the query area.

Once you have chosen the columns you wish to query, you can use the other query building buttons to complete your query with WHERE, GROUP BY, and ORDER BY clauses. When a new section of the query is added with the query building buttons, the cursor in the query area is placed in position for editing; if you click a field with the WHERE pointer, the cursor is in position for you to type in the details of the WHERE clause.

You can change between the different query building pointers by clicking on the query building buttons in the button bar, or by using a combination of Ctrl+Alt and the first letter of the pointer you wish to use (Ctrl+Alt+S for SELECT, Ctrl+Alt+W for WHERE, and so forth.)

If the query building buttons are not visible, they can be displayed through the Browser Options. See Section 11.5.1, “Display Options” for more information.