This walkthrough describes how to insert data into a table and perform a query using the SQL Editor.
First of all a connection to the MySQL server should be created.
Set up connection parameters in the dialog window that appears.
In the displayed document, type the following text:
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) );
There are two ways to insert data into a table. First, you can do it using a query. The second way is to use the grid-based data editor. Here both ways are described.
Here goes content of the Emp table:
Content of the Dept table should be like this:
Open SQL editor by clicking the Create New SQL button on the Standard toolbar.
INSERT INTO Dept (DeptNo, Dname, Loc) VALUES (10,'Accounting','New York')
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 that names of the fields are omitted and several rows are inserted with the simple query. After executing it you must receive message “2 rows affected”.
Right-click it and choose Retrieve Data from shortcut menu.
The editor is displayed where you can view the data the table contains, if any.
In the following cell, type: President.
Now, when there is a table populated with data, let’s see how it can be retrieved.
In Database Explorer make sure that connection to your server containing the database (FirmStaff) is active.
In the document, type the string
SELECT * FROM Demobase.Emp
drag the table from Database Explorer onto the document area to make this string appear automatically.
If everything is correct, either the Data window or the Data view of a document should appear containing all data in the table.
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).
Now when you’ve learned how to retrieve data from a single table, it is time to find out how to perform SELECT on several tables.