Inserting and Retrieving Data

This walkthrough describes how to insert data into a table and perform a query using the SQL Editor.

Adding a Database Connection

First of all a connection to the MySQL server should be created.

To create a database connection, do the following:

  1. On the Database menu, click New Connection icon New Connection.
  2. In the Database Connection Properties dialog box that opens, set up connection parameters and select a database you want to connect to.
  3. To proceed with the connection, click Connect. For more information about how to connect to a database, see Connecting to a Database.

Creating Database and Tables

To create a database and a table, do the following:

  1. On the Standard toolbar, click New SQL.

  2. In the SQL Editor that opens, type the following query:

    CREATE DATABASE demobase;
    CREATE TABLE demobase.dept (
        DeptNo INT(11),
        Dname VARCHAR(14) NOT NULL,
        Loc VARCHAR(13),
    PRIMARY KEY (DeptNo)
    );
    CREATE TABLE demobase.emp (
        EmpNo INT(11),
        EName VARCHAR(10),
        Job VARCHAR(9),
        MGR INT(11),
        HireDate DATETIME,
        Sal FLOAT,
        Commission FLOAT,
        DeptNo INT(11),
    PRIMARY KEY (EmpNo),
    UNIQUE INDEX index1 USING BTREE (EmpNo)
    );
    
  3. To run the query, click Execute icon Execute or press F5.

Inserting Data into a Table

There are two ways to insert data into a table:

  • Use the grid-based data SQL Editor
  • Use a query

Inserting Data Using a Query

To insert data into a table, do the following:

  1. On the Standard toolbar, click New SQL.

  2. In the SQL Editor that opens, type the following text:
    INSERT INTO Dept (DeptNo, Dname, Loc) VALUES (10,'Accounting','New York')
    
  3. To execute the query, click Execute icon Execute or press F5.

If there were no mistakes in the statement, the Output window appears with the report: Execute succeeded. 1 row affected.

The query used is a full form of the statement “INSERT INTO”.

To insert bigger blocks of data, use the following form:

INSERT INTO Dept VALUES (20,'Sales','Dallas'), (30,'Sales2','Chicago')

Note

Names of the fields are omitted and several rows are inserted with a simple query. After executing it, the following message appears: 2 rows affected.

Inserting Data with the Grid-based Data Editor

To open Data Editor, retrieve the data from the table by executing a SELECT * FROM statement in a SQL document. For example, we want to get a list of films that came out after 2004:

SELECT
  *
FROM film
WHERE release_year > 2004;

The list of films is displayed as a grid on the Data Viewer editor where you can view, edit, insert data, or switch to the card view if needed.

Data viewer in Schema Compare for MySQL

To insert data into a grid, do the following:

  1. Click Append button Append

    -or-

    right-click the grid and select Append on the shortcut menu.

  2. Click the empty cell and type a value.
  3. Click the next cell or press TAB to add the value. Do the same for all cells in the row.
  4. Repeat steps 1-3 to insert all the required rows into the table.

Tips & Tricks

Use the editor toolbar to work with the data on the grid:

  • Switch between the rows: first, previous, next, or last
  • Add or remove a row
  • End or cancel editing a row

Editor toolbar in Data Viewer