Contents

Creating and editing a query

This topic explains how to create and edit an SQL query in dbForge Studio for MySQL.

To create a query:

1. Create a server connection. For more information about how to create a server connection see Connecting to a Database.

2. On the Start page, click SQL Development and then click SQL Editor. Alternatively, click New SQL on the Standard toolbar. An empty SQL document opens.

3. Start type a query to a database.

4. Click Execute or press F5 to see the query results.

Coding Techniques Example

The dbForge Studio SQL editor provides many features that make it easier for you to write and manage your code. Let’s explore some Intellisense features that assist you while coding.

We are going to create a sample query. In this example we will use the sakila database. Let’s display all the films stored in the sakila database, that were released after 2004.

1. Open the SQL Editor.

2. Start typing the SELECT statement. Enter first three letters sel. dbForge Studio shows you the list of the relevant keywords and snippets. You can select the SELECT keyword from the list and press ENTER. The keyword appears in the SQL editor. You can also select the Sel snippet from the list and press the TAB button. In this case the SELECT * FROM statement appears in the SQL editor.

If you start type the same letters sel a little faster, before the list appears (by default the list has 200 ms delay), a little tooltip appears next to the letters. It signals that you can press the TAB button to complete the statement automatically by inserting a snippet.

Regardless of the method you choose, the SQL editor displays the SELECT * FROM statement.

3. Now, lets type a table name from which we want to retrieve the data. Start type f. dbForge Studio shows you a list of valid members from the sakila database. If you continue typing characters, the list is filtered to include only the members that begin with those characters. After selecting an item, you can insert it into your code by pressing TAB (or ENTER) or by typing a space. dbForge Studio also shows a quick info for every highlighted item in the list. It allows you to see the object structure immediately.

The film table comes first on the list, so just press TAB or ENTER to insert it into the SQL editor. dbForge Studio assigns the alias for the film table automatically.

4. We need to select films categories that is stored in a separate tables. In a similar way, either type or insert the JOIN keyword and press SPACE. dbForge Studio shows you all available solutions to implement JOIN. Select required statement and press ENTER.

Now, type one more JOIN keyword to join the category table. Again, dbForge Studio shows you the list of available solutions to choose.

5. Use CTRL + K + D combination to format the query text. Now we can execute the query to see results. To do this, click the Execute button on the Debug panel, or press F5.

6. There is no need to retrieve all the columns from the film table, so we can leave only required ones. Place the cursor after the asterisk character and press TAB.

dbForge Studio shows the columns list that are available in the film table.

Let’s select and remove all but f.film_id and f.title.

7. Next, add a category name. For this, place the cursor after f.title and click CTRL + space.

A list of valid members appears. Select a checkbox next to the name column. It will be added to the SELECT statement automatically. We can rename it by typing c.name AS category_name.

8. The last step is to add the WHERE condition. Add the WHERE f.release_year > 2004 statement at the end of the query and press F5 to execute it.

We got what we wanted.

As you can see that Intellisense feature provided by dbForge Studio allows you to create complex queries in a few keystrokes.

Want to Find out More?

• Previous
• Next