SQLyog is definitely a great and must have tool for any serious MySQL or MariaDB developer and database administrator. I would recommend going for either the SQLyog Enterprise Edition or the SQLyog Ultimate Edition so as to get the full benefits of using SQLyog to manage your MariaDB and MySQL databases.
Any DBA or developer for that matter would know how frustrating syntax errors can get or how typos can really mess up a seemingly productive day. Again any DBA or developer will know how the problem mentioned above cannot be solved easily when trying to work with with MySQL using the command line client.
With your boss breathing down your neck while the pressure of deadlines mounts, that cannot be a pretty picture. So it comes in as no surprise that a MySQL client such as SQLyog can come in handy and save the day.
This is the second in a series of tutorials showing how to do various tasks on a MySQL database such as creating tables, populating tables, moving tables and databases, duplicating and altering among others. Previously we dealt with how to backup a MySQL database using SQLyog. Today we tackle how to create tables in MySQL using SQLyog.
At this point I assume you have already gotten yourself a copy of SQLyog. Ideally you would want at the least; the Enterprise Edition, but for this purposes the SQLyog Community Edition should work just fine. I am also making the assumption that you have your SQLyog fired up and created a database on your MySQL database server.
You could also use the built in practice database that comes with the MySQL Server installation. I have chosen to use that one for this purpose. Just in case, the database is called “test”.
With your mouse, expand the list of databases on the left and click on the database you want to create the table in. Right-click on the table folder-like icon that drops down and select “create table” as illustrated below.
For purposes of this tutorial; we are going to create a simple table called students highlighting the most common data types used in a database table. The table we will create will have the student number as a unique number automatically given by the system. The first and last names of the student will be recorded in separate fields.
We will need to know whether the student is male or female. Which class they are officially assigned to (for this purpose, a student can only be assigned to one class). Finally we need to know when they were born.
Here goes. After following the last instructions given above you should have seen a New Table Wizard pop-up and populated as shown below.
Populating the fields in the table shown above in the wizard is quite self explanatory. The only thing that may not be so obvious is the fact that when you type in the date type. The wizard will help you decide what is because the field to type in the data type is actually a combo box featuring a list of the possible data types MySQL has to offer.
Once you have populated all the fields with the necessary information you can set up attributes for the fields by checking the corresponding check boxes to the right of each of the entries.
There is also an option on SQLyog to set up advanced properties. These properties can be accessed from the button at the bottom of the Create Table Wizard. This button gives you access to features such as setting the table type, the collation, character set among others as seen below. I will not make any changes one the advanced properties.
If at this stage everything is setup properly you may go ahead and click on the “Create Table” button. Once that is done you will be required to enter a table name. I entered “students” as my table name. If there were errors or omissions in the details you entered in the SQLyog Create Table Wizard you will be given a notification of the error or omission otherwise you will be given an option to create another table which you can agree to or pass.
Creating MySQL Table Indexes
Notice we did not create any indexes other than the primary key for the student Id. Now would be a good time to do it. We will create another index on the class_id field. If the creation of the table went as planned, you should find an entry for your new table added to the list of the databases. So in my case I created my table in MySQL’s “test” database and my entry looks like this. Hopefully yours is the same.
Creating Indexes on MySQL tables using SQLyog is a rather simple task as you will soon see. All you need to do is right-click on the table entry on the tree menu on the side.
From the context menu that appears you will select the menu item that reads “Manage Indexes” and a dialog will pop up that allows you to make the entries required to create the index.
Clicking on the “New” button will reveal a second dialog that will allow you to create an index by giving it a name. Selecting the field which you would like to create the index for. Additional options which SQLyog gives are those that allow you to state whether the index should be “Unique”, “Fulltext” or a “Primary Key”. You may leave those options unchecked depending on what you really want to do with your tables.
Once everything is done and the two dialogs closed, you would have completed the task of creating and setting up indexes on a MySQL table using SQLyog. The tasks that have been done in this tutorial come far much easier and less typo error prone as hand coding and executing the table creation code using the MySQL command line client. The code that you would have to execute to create the table like the one I have is looks like this if hand coded.
CREATE TABLE `students` (
`sudent_id` smallint(4) unsigned NOT NULL auto_increment,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`class_id` tinyint(2) unsigned NOT NULL,
`birth-date` date default NULL,
`gender` enum(‘Female’,'Male’) NOT NULL,
PRIMARY KEY (`sudent_id`),
KEY `classid` (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Next in this series we will be looking at how you can insert data into MySQL tables using SQLyog.
To learn more about Sobbayi Interactive’s services or to find out how to get your very own custom applications for your business developed by our super-talented team, email us today at firstname.lastname@example.org or get started here.