Creating and Editing Query

This topic describes how to create and edit a SQL query in Data Compare for MySQL.

To create a query, do the following:

  1. On the Standard toolbar, click New SQL.

  2. In the Connect to Server dialog box, select a server connection and click Connect. For more information about how to create a server connection, see Connecting to a Database.

  3. In the new SQL document, start typing a query to a database.

  4. On the SQL toolbar, click Execute or press F5 to see the query results.

Coding Techniques Example

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

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

To create a query, do the following:

  1. On the SQL toolbar, click New SQL.

  2. Start typing a SELECT statement. Enter the first three letters “sel”. Data Compare for MySQL shows you the list of the relevant keywords. You can select the SELECT keyword from the list and press ENTER. The keyword appears in SQL editor.

    SELECT statement

  3. Start typing “f”. Data Compare for MySQL shows you a list of valid values from the sakila database. If you continue typing characters, the list is filtered to include only the values 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. Data Compare for MySQL also shows a quick info for every highlighted item in the list. It allows you to see the object structure immediately.

    SELECT statement

    The film table comes first on the list, so just press TAB or ENTER to insert it into the SQL document.

  4. To select the films categories that are stored in a separate table, type the JOIN keyword and press the space. Data Compare for MySQL shows you all available solutions to implement JOIN. Select the required statement and press ENTER.

    SELECT statement

  5. Continue typing the query. You can use the Quick Info feature while typing SQL code. The Quick Info feature is a tooltip with brief information about database objects, parameters, and variables. The tooltip appears when you place the mouse pointer on the required item in the SQL script.

    SELECT statement

    In case of database objects, the tooltip shows their location and types. You will find this option useful, while working with large scripts.

    SELECT statement

  6. Use CTRL + K, CTRL + D combination to format the query text.

  7. To add the WHERE condition, insert the WHERE f.release_year > 2004 statement into the query and press F5 to execute it.

    SELECT statement

As you can see that code completion feature provided by Data Compare for MySQL allows you to create complex queries in a few keystrokes.