Using WinMySQL Professional

New MySQL® table

This menu, only enabled when connected to a database can also be invoked by the shortcut «Ctrl-T».

The main target of this feature is to give to MySQL® users a powerful tables creation tool, easy to use, with all the MySQL® specific enhancements such as advanced types (enum, set, blob ...) or advanced options that ODBC doesn't manage.

When the tables creation screen displays, all the components are disabled, excepted the «table name» located in the «table properties» group on top right. WinMySQL Professional waits for a table name before enabling the others components

How to create a table ?

In fact, you can create a MySQL® table with only a table name and one column definition. So, first, enter a table name and then, go to the column definition editor on the center of the screen. There, click on the «Add column» button to put the column definition in the column listbox, on the right of the screen. Then, the «Create table» button becomes active. Click on it, if you have sufficient privileges, the table will be created with one column (named «column0») of type varchar(25).

With the above synopsis, it is now obvious to understand how to create a more complex table before clicking on the «Create table» button :

• You may add all the columns that you want to the column listbox.

• You may change the column default name.

• You may change the column default type.

• You may apply column options

• You may apply table options

Column types

To change a column type, you have two possiblities :

• You can use the «Column types» combo box and select the required type in the list

• You can use the contextual menu (with a right mouse button click) to select the required type in the types menus.

When a column type is selected, WinMySQL Professional suggests a size format (when the type requires it). For intance, if you select a «varchar» type, the size suggested will be 25 ; if you select a «float» type, the size suggested will be 8.2 ; if you select a «date», there will be no suggestion because size directives aren't applicable on this format.

If the size format suggested doesn't match with your wish, you may modify it in the requested fields («num» and «dec» if enabled) using up and down arrow or replacing the values direcly.

If the selected column type is «enum» or «set», you must enter additional data (called «member» for the «set» type and «enumeration» for the «enum» type). To do, enter a value in the first box of «Enum or set values» group box and click on the «Add value» button to put it into the values listbox. You must do it for each value. You also can remove a value with the «Del value» button or clear the listbox with the «Clear set» button.

If you wish to load a set or an enumeration, click on the «Load set» button. The default enum or set files are named *.set and can be written with any plain text editor inserting a carriage return between each member. You also can write saving your enum list from WinMySQL Professional clicking with the «Save set» button.

Column type options

On several column types, you can apply options, for instance «binary» option for «varchar» type or «zerofill» option for numeric types. To apply options, you can select options combinations in the combo box located below the size format numbers, or you can either use contextual menus (right mouse click).

Column options

On each column, it is possible to apply several options. Because of the diversity of MySQL® releases, WinMySQL Professional only suggests the most common column options in the «Current column options» groupbox with intelligent checkboxes which can exclude themselves when the combination is impossible, and with a box to enter directly the portion of SQL statement to reference a column of another table in case or foreign key.

If you wish to apply very sharp options to the current column and you are very familar to MySQL® SQL statements, you may use the «Manual options» box in the «Current column options» groupbox. There, you can enter the portion of SQL statement. This box override all other options selected in the groupbox including the «Column reference definition».

Table options

On tables, MySQL® allows several options. WinMySQL Professional suggests the more common in the «Table options» combo box but may be incompatible with the oldest releases of MySQL®.

The target of «Table select statement» box is to add a trailing «select» clause in the generated SQL query. This clause is incompatible with oldest releases of MySQL®.

The target of the «Table advanced options » box is to add custom trailing clauses in the generated SQL query. This feature allows a compatibility with the future releases of MySQL® servers.

Columns management

We have seen above how to add columns in the columns listbox. It is possible to edit a column already added with a double click on the required column line in the columns listbox. This action recalls the column into the column definition editor. Then, you can change anything and choose if you want to add this column modified with the «Add column button» (in this case, you must have changed the column name) or if you want to update the edited column with the «Change column button».

At the opposite, if you want to delete a column already added, select it with one mouse click on the required column line in the columns listbox. Then click on the «Delete column» button.

You also may change the columns order, using the «Up» and «Down» buttons located on the right bottom of the columns listbox.

Table structure saving

If you want to reuse your table structures (as templates for instance), you can save it clicking on the «Save structure» button. It will write a *.mtt file on the disk, storing all the table definition with all the options and clauses. Only the table name is not saved. The *.mtt files are WinMySQL Professional proprietaries and then cannot be written in a plain text editor.

Table structure loading

You can reuse a previously saved table structure clicking on the «Load structure» button.

Checking the generated query

If you uses manual options with portions of SQL statements, you may want to see the generated query to check it and correct what can make troubles. To see the generated query, click on the «Show query» button.

You also may want to keep a copy in the clipboard or into a *.sql file of the generated query. To do it, click on the related button of the «Show query» window. If you want to manually change anything in the query before copying it, you can do it, but the query which will be really sent to the MySQL® server will still be the original generated query.

This feature is really useful when you want to create tables using the mysql command line client with telnet or the phpMyAdmin web client because you only have privileges on the localhost and then you can't use WinMySQL Professional directly online. Of course, in this case, you must have a personnal MySQL® server on which you have sufficient privileges to connect with your WinMySQL Professional station to go to the «Show query» feature !