In magento sometimes we need to add fields to the existing table or create a new table for our module, for this purpose we are required to write the installer script instead or performing the operation directly on database.

Lets see how we can do it :

1. Create a module with namespace and modulename.

2. Then Create a file config.xml in etc folder in our module.

2. Then Create a installer script file mysql4-install-0.1.0.php in Sql folder at the path Namespace/Modulename/Sql/namespace_modulename_setup/mysql4-install-0.1.0.php in our module.

Now, in our config.xml file at the path Namespace/Modulename/etc/config.xml write the below code in it :

<?xml version="1.0"?>

<config>

  <modules>

    <Namespace_Modulename>

      <version>0.1.0</version>

    </Namespace_Modulename>

  </modules>



  <global>

    <resources>

      <modulename_attribute>

        <setup>

          <module>Namespace_Modulename</module>

          <class>Mage_Catalog_Model_Resource_Setup</class>

        </setup>

        <connection>

          <use>core_setup</use>

        </connection>

      </modulename_attribute>

      <modulename_attribute_write>

        <connection>

          <use>core_write</use>

        </connection>

      </modulename_attribute_write>

      <modulename_attribute_read>

        <connection>

          <use>core_read</use>

        </connection>

      </modulename_attribute_read>

    </resources>

  </global>

</config>

In the above code we have defined our module with the version 0.1.0 and 

then in global we have called the installer script to run the Sql query from the installer.

Note : 

we have defined the version of our module here at the top with the code as :

  <modules>

    <Namespace_Modulename>

      <version>0.1.0</version>

    </Namespace_Modulename>

  </modules>

The version tag here <version>0.1.0</version> have the version of our module i.e., 0.1.0. So, there must be a installer script in Sql folder in our module with the same version that we already created with the file name as mysql4-install-0.1.0.php with the version.

Then in the global tag we defined our resource class file which should be in our model as we defined the model in module tag Namespace_Modulename.  

The resource class should be in our model with the definition as :

class Namespace_Modulename_Model_Resource_Setup extends Mage_Core_Model_Resource_Setup {

}

Now, in the Installer script in Sql in the file mysql4-install-0.1.0.php write the below code:

<?php



$installer = $this;

$installer->startSetup();

$installer->run("

  CREATE TABLE `{$installer->getTable('blog_post')}` (

   `blogpost_id` int(11) NOT NULL auto_increment,

   `title` text,

   `post` text,

   `date` datetime default NULL,

   `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,

   PRIMARY KEY (`blogpost_id`)

  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;



  INSERT INTO `{$installer->getTable('blog_post')}` VALUES (1,'My New Title','This is a blog post','2009-07-01 00:00:00','2009-07-02 23:12:30');

");

$installer->endSetup();

Now in the above script we defined a sql query to create a custom table 'blog_post' with the fields defined below and field `blogpost_id` as primary key.

Afterwards for example we inserted raw data in that table with the insert query. 

In this way we can write different queries according to the requirement of our module.

Now the thing is when we need to upgrade our module then the changes in database also to be done, for that purpose we wont going to edit the last script instead we write another script and change the version of the module.

here is one thing that we should know before going to upgrade our module that whenever we run a script its entry is done in the core_resource table in our database so that script wont going to be run again. So, to make our new script run we would give the file name newer version or we would delete the entry from the table to make our script run. 

The standard way is to upgrade the script version as mysql4-install-0.1.0.php to mysql4-install-0.1.0-0.1.1.php and our module version in config.xml as 

<modules>

    <Namespace_Modulename>

      <version>0.1.1</version>

    </Namespace_Modulename>

  </modules>

Clear the cache of the setup and then hit the url the script gets called at that time does the operation on the database accordingly.