Creating and Editing a Query

This topic explains how to create and edit an SQL query in Schema Compare for MySQL

To create a query:

  1. Create a server connection.
    • In the Database menu, click New Connection.
    • Enter the connection properties in the Database Connection Properties dialog box.
    • Click OK.
  2. Click New SQL in the File menu. An empty SQL document opens.

  3. Start type a query to a database.

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

Coding Techniques Example

The Schema 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 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. Schema 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 the SQL editor.

    SELECT

  3. Now, let’s type a table name from which we want to retrieve the data. Start type “f”. Schema Compare for MySQL 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. Schema 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 FROM

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

  4. We need to select films categories that is stored in a separate tables. In a similar way, type the JOIN keyword and press the space. Schema Compare for MySQL shows you all available solutions to implement JOIN. Select required statement and press ENTER.

    JOIN

  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.

    Quick info

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

    Quick info

  6. Use CTRL + K, CTRL + D combination to format the query text. Now we can execute the query to see results. To do so, click the Execute button, or press F5.

    Warning

  7. 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.

    Result

We got what we wanted.

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