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.
Note
The Database Explorer is not available in the dbForge Data Compare for MySQL, bForge Schema Compare for MySQL.
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:
In the displayed document, type the following text:
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.
The editor is displayed where you can view the data the table contains, if any.
Now, when there is a table populated with data, let’s see how it can be retrieved.
The most simple example of an SQL statement is selecting all data from a single table.
or -
drag the table from the Database Explorer onto the document area to make this string appear automatically.
Alternatively, you can right click the table and select Select All Rows.
If everything is correct, either the Data window or the Data view of a document should appear 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).
Quantity | Job | Money |
---|---|---|
3 | Salesman | 4100 |
2 | Manager | 5825 |
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.