SQLyog Help File

Overview of SQLyog

SQLyog is a easy to use, lightweight and compact graphical tool to manage your MySQL database anywhere in the world. With SQLyog you can -

• Very fast retrieval of data.
• Can handle multiple queries of more then 100000 records each. Very efficient in memory.
• Drop databases and tables.
• Create tables in a jiffy with a very easy to use Table Maker.
• Execute multiple queries on your MySQL server as a batch.
• Quickly insert / delete / update data from tables.
• Save your frequently used SQL scripts with only a mouseclick.
• Export data from a query into clipboard.
• Flush logs, hosts, privileges etc. with a mouseclick.
• Diagnose your tables with Table Diagnosis.
• Export data in CSV, XML and HTML format.
• Export data structure and the data itself.
• Import data from CSV files.

This document is the most updated help for version 1.71. If you have a more recent version then download the recent help from the Webyog website.

About this manual

This manual is available only in HTML format. We are planning to come out with other format in future. Till then this document contains all the information you want to know about SQLyog. If you have any queries, questions or suggestion about SQLyog please mail them to support@webyog.com

Pricing

SQLyog is a freeware software. You can you use it without any restrictions.

Downloading and using SQLyog

Download the self-extraction zip file from the webyog site. Unzip all its content -

• SQLyog.exe ( the main software )
• libmysql.dll ( library needed for the software )
• sqlyog.html ( the help file )
• helpimages directory ( contains images for this help file )

to a directory say c:\SQLyog and run the exe.

This release does not have any setup files or such because we have tried to make the software as simple as possible with no overheads. If you don't want to use the software just delete the directory into which you had copied the files.

What's New in SQLyog 1.71

There have been lot of changes from the previous version i.e. 0.9. Here is the list of some of the major changes implemented in 1.71.

With so many changes its a must update from 0.9.

Getting started with SQLyog

After you have unzipped the file. Run SQLyog.exe. The first thing you will get is Fig. 1. Fill in all the details about the server you want to connect i.e. host
address , user name, password and the port number. If you don't give any details SQLyog will try to connect to localhost with the default user and port number 3306. You must connect to database successfully before you can perform any activities with the software.

After you have connected to an instance of MySQL, you can make additional connections by selecting the New Connection option from the File menu.

Hardware / Software Requirement

Here are the list of software and hardware under which SQLyog has been found to work perfectly -

• Windows 95/98/Me/XP/2000
• 64 MB RAM ( it even runs in 32 MB RAM, but then executing queries with big resultset will have memory problems )
• Pentium Processor ( gives optimum performance in 166 MHz also )

The software has been tested in machines having memory ranging from 64 MB to 256 MB and processors ranging from 166 MHz to 1.4 GHz with OS
Win 95/98/XP/2000.

As of now the SQLyog runs only under Windows. We are planning to come with a OS independent version.

Using SQLyog Windows

After you have established a connection you will get a window like Fig. 2. The name in the image signifies the name with each window will be referred to in this document.

The following table lists the icons in the SQLyog toolbar.

	Inserts a new template in the SQL window of the current active connection.
	New instance of a connection
	Open a SQL script file (.SQL) in the SQL window of the current active connection.
	Save the SQL script into a .SQL file.
	Cut the selected text.
	Copy the selected text into the clipboard.
	Paste from clipboard.
	Execute the query or queries from the SQL window in the active connection. Clicking it will give you three options. You can select to execute the current query which you are writing, or selected query(s) or all the queries in one batch.
	Refresh the Object Browser for the current active connection.
	Can change the database for the current connection.

Title Window

The title window shows the database name and the user name to current connection. If the query has been saved then it shows the complete path of the SQL file.

Object Browser Window

The window gives you the complete detail of the server you are connected to in a tree format. In the top you will see the user@servername and all the database.

Starting from 1.71, the object browser wont retrieve the whole data about a server in one instance. It will first show only the databases and when you expand it, it will retrieve the table names only. When you expand the table name it will retrieve data about the table. This reduces time as SQLyog has to retrieve only necessary data when required. It is also efficient on memory.

Remember, retrieval of data takes place only for the first time. After that a cache is maintained. To get the latest information you have to refresh the Object Browser.

Expanding database node will show the tables in the database and expanding the table name node will show the columns and indexes available for the table.

You can refresh the object browser with the latest detail about the server whenever you want by right clicking on the window and selecting Refresh Object Browser or by pressing F9. When you refresh the object browser SQLyog tries to get you back to the last selected item. The precision of it depends upon the changes done in the server before and after Refreshing.

The popup menu also has other options - Drop Database, Drop Table, Insert Statement For The Table, New Table In The Database. More about them later.

SQL Window

It is here where you write your SQL commands which you want execute. Write any SQL query and press F5 to get the result of the query. There are various ways to enter SQL in the window -

• Type SQL commands directly in the window.
• Open a saved SQL script file. The file will be opened for editing.
• Insert a template from Edit / Insert Templates option.
• Open a SQL script from the personal folder. More about personal folder later.

The SQL window provides you with various commands to help you write SQL commands, including the standard editing commands : Undo, Cut, Copy, Paste and Select All. You can also force case, insert and remove comment marks.

If you are executing multiple queries then separate the individual queries by a (;). Otherwise the software will take it as a single query and you might get an error even if there is no error with the SQL. Each query would create a new tab for its resultset.

From 1.71, you can execute queries in SQLyog in three ways.

1. To execute a particular query in a batch, just place your cursor in the query which you want to execute i.e. before the semicolon ending of the query and press F5. Doing this will result in SQLyog to execute that particular query only.
2. To execute a selected query in set of query, select those query and press Ctrl+F5. Be sure of selecting the whole of query. Leaving a part of query unselected will result in undefined result.
3. To execute the whole batch of queries which you have entered in the SQL window, press Shift+F5. This will result in execution of all the queries one by one.

SQL window supports syntax highlighting for better usability. The different color signifies different element of SQL -

Fig.3 ( example of single query). Fig. 4 ( example of multiple query ).

Note : If you are executing only one query then you don't have to end it with a (;). You can just execute them. Putting a (;) wont have any effect. The application uses it as a separator between queries.

ResultMessage Window

This window shows you the resultset or messages returned due to the execution of the query. The window has two tabs - Results and Messages.

Selecting Results tab will show you the resultset returned by the query. If you have executed multiple queries which returns resultset then you will see multiple Records tab in the window with the resultsets of the respective queries attached to it. Therefore, the first tab will hold the result of the first query and second for the second query and so on. To view the results just select the respective tab.

Selecting the Messages will show you the messages or results of all the queries you have executed. This includes the error for the query (if any), number of rows returned by the resultset or number of rows modified by INSERT / DELETE / UPDATE etc. statements. For multiple queries, individual messages will be shown with each message terminated by a newline. Click Fig. 5 for a sample.

Note : SQLyog does not stop executing query if there is an error in one query within multiple queries. It will execute all the queries and then will show you the results. So maybe sometimes you wont get the resultset in the tab in which you expected. The tabs contains resultset of only the successful queries.

SQLyog Status Bar

The status bar has two parts. It displays -

• General information about activity in the window, including descriptions of selected menu commands
• Number of current active connections by the current user.

Query Window Status Bar

The status bar has four parts. It displays -

• Status of the current operation.
• Amount of time in milliseconds taken to execute each query. When you select one of the Records tab it will show the time taken to execute that query and not the sum of time taken for all queries.
• Number of rows returned for the query.
• Current position of the insert cursor in the SQL window.

SQLyog Keyboard Shortcuts

Keeping in mind about the preference of developers for keyboard, SQLyog incorporates many keyboard shortcuts for fast working with the application. Following is the complete list of all keyboard shortcuts available in SQLyog.

You can get the above list by selecting Help / Keyboard Shortcuts in SQLyog.

Comments in SQLyog

SQLyog supports three types of comments in the query.

• -- to the end of line. So if any line that's starts with -- and has a space after it will be taken as a comment.
  Note : -- without a space will give an error in the query because MySQL takes it as valid line. You must put a space after -- to make it a comment.
• # to end of line. Anything that's follows the character will be taken as comment.
• /* */. Anything, whether single or multiple line /* and */ will be taken as a comment.

Some of the valid comments are -

• -- this is a comment.
• --is not a comment.
• /* is a comment */
• /*
  is a comment
  */
• sql query #is a comment
• sql query # is a comment

Something more about SQL window

This section describes some task that can be performed in the SQL window.

Moving and Copying Text

• Select the text to be copied or moved. Click Edit/Select All to select all the text in the window.
• Click Edit/Cut to move or Edit/Copy to copy text. Alternatively, you can use the keyboard shortcut also.
• Go to the point where you want to insert the text.
• Click Edit/Paste.
• Alternatively, you can select the text to be moved and drag it to the new location.
  Note : You can perform drag and drop operation within a query window or between multiple query window.

Undoing Mistakes

• Press Ctrl+Z or select Edit/Undo
  Note : Each time you do undo the previous action is rolled back.

To Remove All the Text in the Window

• Press Ctrl+A or select Edit/Select All
• Press Del.

Change the Case of Text in Window

• Select the text for which you want to change the text.
• Press Ctrl+Shift+U, to make the text Uppercase ( alternatively you can select Edit/Advanced/Make Selection Uppercase )
  or
  Press Ctrl+Shift+L, to make the text Lowercase ( alternatively you can select Edit/Advanced/Make Selection Lowercase )

Note : The feature will work only with characters. Any special character wont be affected.

Adding and Removing Comments

• Select the text to comment out or for which the comment marks are to be removed.
• Press Ctrl+Shift+C to comment the text.
  or
• Press Ctrl+Shift+R, to remove the comment.

Note : Even if you don't select the whole text in a line, the line will be commented out. So you can only comment or uncomment the whole line with this option. To comment multiple line, select multiple lines.

Using Templates in SQLyog

Templates tells you the basic skeleton of the SQL statement. Looking into it you can form your own statements.

• Select Edit/Insert Templates or Ctrl+Shift+T to invoke the dialog box.
• Select the option for which you want the statement.

Something more about Object Browser

With addition to view the database details, the object browser can be used to perform some other tasks also. To get a list of tasks which can be performed select any item and right click on it. The various options in the popup menu are -

• Refresh Object Browser - It refreshes the window with the latest details of the current connection.
  Note: You must refresh the object browser after any DROP / CREATE statement.
• Drop Database - You can drop a database by selecting the option. You have to confirm before the DROP operation will be performed.
  Note: This option will be disabled if you right click on a non-database item.
• Drop Table - You can drop a table by selecting the option. You have to confirm before the DROP operation will be performed.
  Note: This option will be disabled if you right click on a non-table item.
• Insert Statement For The Table - Selecting this option will insert a SQL INSERT statement for the selected table with all the columns name placed inside into it. You just have to type the values.
  Note: This option will be disabled if you right click on a non-table node.
• New Table In The Database - Click this option to create a table in the database. With the Table Maker you can create tables in jiffy. It has a easy to use grid type view. More about the Table Maker later on.
  Note: This option will be disabled if you right click on a non-database node.

Table Maker

To create a new table click on New Table In The Database in the Object Browser popup menu.

Fill in the columns as appropriate. Each row represents one column in the table.

Right click on any row and you get a context menu. Through this you can set a column as Primary Key.

To delete or insert one row in between right click and select option as required.

After giving all the details select Create Table button from the toolbar. In the Give Name dialog box give a name for the table and select the type of table you want to create. Before clicking on the option, make sure that you don't have any empty column in between because SQLyog starts traversing for columns from the top column and traverses till it encounters a empty column. So leaving a row blank will have undefined behavior.

If any error occurs while creating the table, the error will be shown in a messagebox.

Click here to get a view of Table Maker Fig. 6.

Blob Window

If any field in your resultset has a BLOB value column then SQLyog will only show [BLOB] in the column. Double clicking on it will result in a dialog box coming up with the value of that column. SQLyog supports binary data as well as BLOB field. It can show image files of JPEG/GIF/PNG type. For other binary data it will show some garbage value in the BLOB field dialog box.

Click here for a sample of variable length field, Fig. 7.

Click here for a sample of image data field, Fig. 8.

Personal Folder

You can save your most commonly used SQL scripts in the Personal Folder of SQLyog. It relives you from the task of saving and opening of a SQL file in your local storage. With the click of mouse you have an access to your most important scripts.

SQLyog stores all your personal folder files in the directory in which you unzipped the executable. It stores it in the (.mys) extension so you should never fiddle with it.

Saving SQL in Personal Folder

After you have written your SQL scripts in the SQL window you can add them into the personal folder by following the given steps -

• Right Click on the SQL window and select Insert Into Personal Folder from the context menu.
  or
  Select Edit/Insert Into Personal Folder
  or
  Press Ctrl+Shift+F
• You will get a dialog box like Fig. 9 . Give a name for the SQL script by which you want to refer it.
  Note : It can contain only characters which are allowed by Windows for a file name because SQLyog creates (.mys) based on this name only. If the key already exists or you have inserted any invalid character you will get an error.
• Now your file is added in the personal folder.

Inserting From Personal Folder

To insert a file from the Personal Folder follow the steps -

• Right Click on the SQL window and select Insert From Personal Folder from the context menu, and then select the keyname from the dropdown menu of which you want to insert the SQL script
  or
  Select Edit/Insert From Personal Folder and follow the steps given above
  or
  Press Ctrl+Shift+P to bring up the popup menu.
  
• The SQL command(s) stored in the will be included in the SQL window.

Managing Your Personal Folder

You can manage your Personal Folder by selecting Edit/Manage Personal Folder or selecting Manage Personal Folder from the context menu of the SQL window.

When you select the option you get a dialog box like Fig. 10, check the keys which you want to delete and press Delete. The files will be deleted and the Personal Folder Menu will be updated accordingly.

Note : Once you delete the file you cant get it back because SQLyog doesn't have any temporary deletion facility. So be sure that you want to delete the script.

Exporting Data

SQLyog provides the facility to export your resultset in XML, HTML and CSV format. To export any resultset, select the respective Results tab and select Transform/Export Data and select the format in which you want to save it. You will be prompted to give the file path and the file name. Give the details and your file will be saved.

You cant save multiple resultsets in one go. To save multiple resultsets select the respective Results tab and save them accordingly.

Note : If you have selected Save in CSV, SQLyog saves the data where the columns are separated by (,) and lines are separated by ( \r\n ).

Exporting Data In Clipboard

With SQLyog you can copy the whole of result set or selected rows in clipboard in CSV format. To copy all the result in the memory just right click on the Result Window and select Copy Data To Clipboard option from the context menu.

For copying only selected rows into Clipboard, select the particular rows by clicking on the rows by pressing SHIFT and then select Copy Selected Data To Clipboard.

Exporting Tables

You can export the whole of table with all its structures and data by selecting Import/Export/Export Tables option. When you select the option, you get a dialog box like Fig. 11.

Select the database for which you want to export the data. Selection of a database will show you its respective Tables, select the tables for which you want to export the data. Check the various options, select the file and press Export.

Importing From CSV File

SQLyog provides with the facility to import data into your table from a CSV file. To import a file select Transform/Import From Textfile and you will get a dialog box like Fig. 12.

Select the database, tablename and the columns and file all the required information. You should provide atleast how fields and lines are separated. By default they are (,) and ( \r\n ). If you have a file with different format then you can change it with updated details and SQLyog will import data in that format.

Note : This option will only work for MySQL versions > 3.22.6 because SQLyog uses the LOAD LOCAL INFILE command to import data. For MySQL versions less then 3.22.6 you will get a error.

Flush Manager

To flush Host / Logs / Privileges / Tables / Tables With Read Lock / Status, select the appropriate option from the Flush dialog box in Tools menu.

Table Diagnostics

To run a quick check on your database and tables, you can use SQLyog Table Diagnostics option. Fig. 13 shows the Table Diagnostics dialog box.

In the dialog box select the database and the table which you want to diagnose and select the type of diagnosis you want to do on the table. After the diagnosis you will get the report in Results listview.

FAQ

Check out the latest FAQ page at SQLyog FAQ Page.