Inserting and Retrieving Data

This walkthrough describes how to insert and retrieve data into/from a table and run a query using the SQL Editor.

Adding a Database Connection

To begin with, a connection to the MySQL server should be created.

To create a new 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.
  3. To connect to a server, click Connect.

Creating a Database and a Table

We will create a database and a table with the help of a CREATE statement in the SQL Editor.

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

  1. Open a new SQL document by selecting one of the following options:

    • On the Standard toolbar, click New SQL
    • On the File menu, click New > SQL
  2. In the SQL document that opens, type the following:

     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 execute the statement, click Execute or press F5.

Inserting Data into a Table

There are two ways to insert data into a table:

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

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 document that opens, type the following:

     INSERT INTO Dept (DeptNo, Dname, Loc) VALUES (10,'Accounting','New York')
    
  3. To execute the statement, click Execute or press F5.

If there were no mistakes in the statement, the Output window appears with the report that 1 record has been 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

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

Inserting Data Using the Grid-based Data Editor

To open Data Editor, execute a SELECT * FROM statement in a SQL document.

SELECT
  *
FROM customer
WHERE store_id > 1;

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

Data grid

To insert data into a grid, do the following:

  1. Click Append icon Append.

    -or-

    Right-click the row and then click Append on the shortcut menu.

  2. Click in the very first empty cell and type in the value.
  3. Click in the next cell or press TAB to add a new 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 rows
  • End or cancel editing a row

Working with data on the grid

Now, when there is a table populated with data, let’s see how it can be retrieved.

Retrieving Data from Tables

To retrieve data, we will use a SELECT statement. The most simple example of a SQL statement is selecting all data from a single table.

Note

Make sure that the connection to your server containing the database is active.

To retrieve data, do the following:

  1. On the Standard toolbar, click New SQL to open a new SQL document.
  2. In the SQL document that opens, type the SELECT * FROM statement.
  3. To run the query, click Execute on the SQL toolbar.

If everything is correct, the Data view of a document appears containing all data in the table.

Note

You can edit results of a query in these windows as described in the previous part of this topic.

You can execute any kind of queries using the SQL editor. Consider this:

SELECT count(*) Quantity, Job, Sum(Sal) Money FROM Demobase.Emp
WHERE Sal>800 GROUP BY Job HAVING count(*)>1 ORDER BY Money

The query returns all categories of employees (GROUP BY job) that have more than one member in the category (HAVING Count(*)>1). For every category the total salary is shown (SELECT … Sum(Sal) Money) together with the number of members in the category (SELECT Count(*) Quantity). Employees that have salary of 800 or less are excluded. The whole grid is ordered by salary amount (ORDER BY Money).