Query Constructor :    SmartMySQL has an advanced feature called Query Constructor which is an advance version of Query Builder which is more helpful who has basic knowledge in SQL and speed up to nine times your SQL Query development time with simple drag and drop the tables into SQL Query editor. You just need to add filter conditions.  Demo

• Type UPDATE/U/u then drag table generates UPDATE command.
• Type DELETE/D/d then drag table generates DELETE command.
• Type INSERT/I/i then drag table generates INSERT command.

SmartMYSQL provides you with a powerful GUI  Query Builder. The Query Builder is a ‘special TAB in the SQL editor area of the program. When the program opens it will display one such tab. You can add more from the ‘file’ and the ‘powertools’ menu.

The Query Builder can be selected by clicking on the “+” icon in the query editor line to the right of all open tabs and select the option “New Query Builder”,

or from the ‘File’ menu,

The Query Builder TAB is divided into 3 sections:

• a ‘canvas’ (a drawing area)
• a Field listing area
• A Query Panel area

You can move the ‘divider’ between those three areas to fit the needs of the individual queries that you are building.

Also note that there is one setting in ‘settings/preferences’ that affects the behavior of the Query Builder: you can choose to use `backquotes` to enclose table and column names or not. You need to use `backquotes` in some special cases: for instance if the name of a table or column is a word that has a special meaning in MySQL (a ‘reserved word’), like for instance the words ‘select’, ‘table’, ‘begin’ etc. Also if one or more names starts with a number or contains special characters you may need depending on the MySQL version. Using `backquotes` is SAFE, but some users think that they make queries hard to read.

To start building a query use the mouse to drag the tables that shall be used for this particular query from the Object Browser into the canvas of the Query Builder. You can arrange them and resize them as you want. If some lines occur between two of the tables it is because there are Foreign Keys defined between those tables. The line indicates a SQL JOIN. It is reasonable to think the columns ‘related’ by defining a Foreign Key should be JOINed. So, SmartMYsql will propose you that. You can right click on the line and define the properties of the JOIN or modify it if you want.

To define JOINs that are not defined ‘implicit’ in the database schema itself as Foreign Keys, you just right click on the line and select  modify from that option choose new join conditions. So JOIN is possible too with table types that do not support Foreign Keys.

Note that also VIEWs are supported by the Query Builder. You operate them completely identically. In the following the term ‘table’ refers to both tables and VIEWs!

Table Alias: When table is added to the canvas more than once, a table alias is generated for second and following instances and the alias is used by Query Builder in queries.

Once the tables are dragged into the canvas and the JOINs properly set up start selecting the columns that shall be included with the RESULT of the query.

• Clicking on checkbox present in front of every column in canvas. The column will be added at the end/right of the GRID.
• Now the Query Tab will look like the image below. Note that a query is already generated and displayed in the Query panel area. This is a simply query only doing SELECTs on the columns that you have chosen to display in the GRID JOINing the columns with the JOIN type that you selected.

You can copy the generated query from the Query Builder editor area to a ‘common’ or ‘normal’ Query Tab from where you can execute and do anything else you would do with a query that you had written yourself. You can also copy to the clipboard. The icons just below the GRID are for those copy operations.

In the image above you also see that changing a JOIN type or deleting line representing a JOIN from the canvas is done by right-clicking on the line itself, and selecting from the context menu.

Note that LEFT and RIGHT JOINs have a different meaning depending on the direction of the JOIN. LEFT JOINing table an on table b returns the same as RIGHT JOINing table b on table a (and vice versa). With INNER JOINs the result is the same. The JOIN direction is decided by the program from recording in which direction the line in the canvas was drawn. If you are not very familiar with JOINs it will probably be a good idea always to use a fixed procedure: for instance, consider always having ‘child’ tables to the left of ‘parent’ tables and always draw in the canvas from left to right. Lines drawn by the program itself (due to the existence of Foreign Keys) will always be considered as being drawn from ‘child’ to ‘parent’.

Also, note that the SmartMYsql Query Builder updates the displayed query ‘in real time’ – that is whenever a column is added or removed from the GRID or any kind of parameter is entered the GRID, the query is updated at the very moment that the cursor leaves that cell of the GRID where you added or changed something.

Finally, note that you can INSERT or DELETE a complete column in the GRID by click on the checkbox in front of each column.

Query Constructor: SmartMySQL has another feature called Query constructor which is an advance version of Query Builder which is more helpful who has basic knowledge in SQL and speed up unto nine times your SQL Query development time with simple drag and drop the tables into SQL Query editor. You just need to add filter conditions

 

 

To generate a usable query the example shown on the previous page will need to have ‘something’ added. At least you would probably SORT and FILTER the data shown in the result set using the SQL ‘ORDER BY’ and ‘WHERE’ clauses.

Sorting: To define the sort order of data you enter a number in the ‘sort order’ row of the GRID. The lowest number will be the sort criteria given the highest priority (the first column in the ORDER BY clause). The ‘next-lowest number will be the sort criteria given 2nd priority and so on. For every column that shall be sorted you also enter if sorting on that column shall be done Ascending or Descending.

Filtering: Filtering data is done by entering criteria in the ‘criteria’ rows of the GRID. You can use all operators recognized by MySQL like “>” (greater than) for numbers and time-type variables, “LIKE ‘something'” for strings, “IS (NOT) NULL” for all types of data, etc. If you add more criteria, criteria in the same row of the GRID will be logical ‘AND”ed’ while criteria added in different rows of the same column of the GRID, the resulting criteria for each row will be logical ‘OR’ed’.

The example below illustrates all this:

In this example, we first sort descending on ‘release_year’. For those data in the result where ‘name’ is the same the 4th sort order takes effect: they will be sorted (ascending) on ‘film_id’. And if there should be some data having the same values for both ‘release_year’ and ‘film_id’ the result will be sorted alphabetically on ‘film’.

In this example, ‘release_year’ is a YEAR type. So if we only want to display data related to ‘film’ whose ‘release_year’ in the year of 2009 and before we can use the criteria ‘<=2009-01-26’.

 

When generating a query you can use any function that MySQL supports. An example:

There are two examples of the use of functions here.

The first (and simplest) example is that we no longer have selected just the ‘special_features’ column of the ‘film’ table, but a function like ‘substr( `special_features`,1,10). This will return a string up to 10 characters starting with the first character of the special_features, but if the `special_features` is longer than 10 characters only the first 10 will be returned. This may make the result look better, and it will be easier to use the result in text and copy it to some kind of document. You will probably recognize the customer from the first 10 characters of his name!

To change from the name of the column itself to a function on that name you just click twice in the cell of the GRID where it displays and edits it. You may even write functions inside other functions and use expressions too like ‘truncate(mycolumn,0)+1’. This will (on) return the ‘lowest’ INTEGER number ‘higher’ than the actual value DECIMALS.

The second example is a little more complicated but not less important. Instead of filtering `release_year` on the ‘year of 2009 and before’ we want to filter on the ‘year 2009 and only that year’. We could of course use a ‘>’ and a ‘<‘ type of condition and AND those logically, but using the year() function on the `release_year` column would be much more readable. To filter on year() of that column without displaying the year() alone in the result we add a column more showing ‘year(release_year)’ and UNCHECK the ‘SHOW’ checkbox for that column. Compare the SQL above with that on the previous page.

This is the method you use whenever you want to sort (ORDER BY …), filter (WHERE …) on a column (or a function/expression on a column) without returning the value used for sorting or filtering in the result itself. Actually, you can also do the same with the GROUP BY (see next page) so it might look like:

“SELECT a,b,c WHERE x {criteria} ORDER by length(y) ASC GROUP by z;” (in the example y is a string type)

Here all the columns a,b,c,x,y,z are added to the GRID, y is edited using the length() function but only the SHOW checkboxes for the a,b and c columns are checked, so only those will display in the result.

However GROUPING like this (GROUP BY a column  not included in the SELECT clause) is considered ‘bad practice’ and experts disagree whether such construction is in accordance with SQL standards. With MySQL the situation is that recent versions support this, older versions don’t (like most other database servers also don’t).

Here we will discuss the use of the GROUP BY clause and the use of aggregate functions like COUNT(), MAX(), AVG() (average) etc. Using such functions in combination with criteria can be a problem for a lot of users. SQL defines a special construction (the HAVING clause) for handling criteria on those. Using this clause correctly – especially in combination with the GROUP by clause, ‘common’ functions and complex JOINs is ‘too hairy’ for many users – and many simply stayed away from that. That is a shame because these sorts of queries can be very powerful – and in a single SQL statement, you may be able to get a result directly that would otherwise take a lot of additional work or application code to retrieve!

With the SmartMYsql Query Builder, it becomes very easy to handle this. The program decides which criteria will have to go into a WHERE clause and which must go into a HAVING clause. You won’t have to think about different rules between ‘common’ and ‘aggregate’ functions – no matter if the column is used for sorting, filtering or used with a GROUP BY.

On the `actor_id` column we use the aggregate function COUNT() and criteria at the same time. You see that these criteria go into the HAVING clause and not the WHERE clause. The criteria on year(release_year) is (correctly) in the WHERE clause, however.

In this example, you also see a GROUP BY clause on `substr(special_features….)`. That means that all rows having the same value for substr(special_features ….) will be GROUP’ed into a single line in the result. By using the MIN() aggregate function of the `release_year` we will ensure that the ‘lowest’ value for `release_year` will be displayed. As `release_year` is a DATE ‘lowest’ means ‘first’ in terms of TIME. In plain words, this query will return a single line displaying the date of the first order to be delivered for every customer who has an order registered in the system.

We will study one more advanced example:

There are three things you should notice here:

First: you should note that in this example there is one column (ìtem_id) on which there simultaneously is used ‘GROUP BY, criteria, and aggregate function (COUNT()). No problem! Note that when using GROUP BY on columns where an aggregate functions is also used, SmartMYSQL correctly does the GROUP BY on ‘the column itself’ and not on the ‘aggregated column’.

Second: you should study the criteria entered this example. There are quite a lot of them! First note that SmartMYsql ‘keeps track of’ which criteria shall be used with WHERE and HAVING respectively. The AND/OR rule of entering horizontally/vertically in the GRID as described before is applied to each of those two categories independently. So the two criteria like ” ‘sam%’ ” and ” = ‘ACADEMY DINOSAUR’ ” can safely be entered on the same line even if you do not want them logically ‘AND’ed’ if you are sure that they belong to the WHERE and HAVING clauses respectively. If you are not sure, then you may just as well enter each of them on their own separate line of the GRID. The resulting query will be the same.

Third: SmartMYsql Query Builder lets you specify an ALIAS (a ‘nickname’) for a column (or for a function/expression defined on a column). When executing the query the ALIAS will be displayed as the column header for the column in the result.

Lastly, also  note that all over this chapter of the manual we have restricted ourselves to using examples with only two tables. This was done in order not to make an example graphics unnecessarily complicated, but rather to keep them as simple as possible. The principles are the same no matter if there are used 2 or 10 tables for the query. You can of course use as many tables as you like. The structure of JOINs handled by SmartMYsql can be a ‘chain-of-JOINs’ or a ‘tree-of-JOINs’ and every combination of the two. It is further a restriction that only tables from the same database can be JOINED.