how to add a column after a particular column in mysql table

1. To add a new colmn to the existing table the ALTER command is used. Syntax:
               alter table < table name > add < column name > ;
    Example:
          To add a column called ldate to the main_tab table with a datatype of date, use the following SQL statement:
               mysql> alter table main_tab add ldate date;
            This statement will add the ldate  column to the end of the table.
2. To insert the new column after a specific column, such as name, use this statement:
   ALTER TABLE contacts ADD email VARCHAR(60) AFTER name;

mysql> alter table main_tab add ldate date after doj;
Query OK, 11 rows affected (0.12 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> desc main_tab;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| acc_no   | int(5)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20) | YES  |     | NULL    |                |
| gender   | varchar(6)  | YES  |     | NULL    |                |
| password | varchar(10) | YES  |     | NULL    |                |
| doj      | date        | YES  |     | NULL    |                |
| ldate    | date        | YES  |     | NULL    |                |
| amount   | float(10,2) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
 
   
3. If you want the new column to be first, use this statement:
   ALTER TABLE contacts ADD email VARCHAR(60) FIRST;
   
   
4. 
   //To drop a particular column in mysql table
   
   mysql> desc main_tab;
   +----------+-------------+------+-----+---------+----------------+
   | Field    | Type        | Null | Key | Default | Extra          |
   +----------+-------------+------+-----+---------+----------------+
   | acc_no   | int(5)      | NO   | PRI | NULL    | auto_increment |
   | name     | varchar(20) | YES  |     | NULL    |                |
   | gender   | varchar(6)  | YES  |     | NULL    |                |
   | password | varchar(10) | YES  |     | NULL    |                |
   | doj      | date        | YES  |     | NULL    |                |
   | amount   | float(10,2) | YES  |     | NULL    |                |
   | ldate    | date        | YES  |     | NULL    |                |
   +----------+-------------+------+-----+---------+----------------+
   7 rows in set (0.00 sec)
   
   mysql> alter table main_tab drop ldate;
   Query OK, 11 rows affected (0.10 sec)
   Records: 11  Duplicates: 0  Warnings: 0
   
   mysql> desc main_tab;
   +----------+-------------+------+-----+---------+----------------+
   | Field    | Type        | Null | Key | Default | Extra          |
   +----------+-------------+------+-----+---------+----------------+
   | acc_no   | int(5)      | NO   | PRI | NULL    | auto_increment |
   | name     | varchar(20) | YES  |     | NULL    |                |
   | gender   | varchar(6)  | YES  |     | NULL    |                |
   | password | varchar(10) | YES  |     | NULL    |                |
   | doj      | date        | YES  |     | NULL    |                |
   | amount   | float(10,2) | YES  |     | NULL    |                |
   +----------+-------------+------+-----+---------+----------------+
   6 rows in set (0.00 sec)
   
   
   how to change a column without deleting the contents in mysql table

1. starting with mysql
   
   mysql -u root -p
2. starting with mysql
   
   mysql -u root -p
   
   

sudo synaptic
then search for mysql, make sure you have the server selected, click apply.

command for creating a database create database ;
Example: create database bank;
command for list all the available databases
show databases;

command for selecting particular database
use ;
ex:
use bank;

http://www.tech-recipes.com/category/database/mysql/

A primary key uniquely identify a row in a table. One or more columns may be identified as the primary key. The values in a single column used as the primary key must be unique (like a person’s social security number). When more than one column is used, the combination of column values must be unique.

When creating the contacts table described in Create a basic MySQL table, the column contact_id can be made a primary key using PRIMARY KEY(contact_id) as with the following SQL command:
CREATE TABLE `test1` (
contact_id INT(10),
name VARCHAR(40),
birthdate DATE,
PRIMARY KEY (contact_id)
);
Additional columns can be identified as part of the primary key with a comma separated list in the PRIMARY KEY command, like PRIMARY KEY (contact_id, name).
A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself , or it can be an artificial field (one that has nothing to do with the actual record). A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key.
Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).
Below are examples for specifying a primary key when creating a table:
MySQL:
CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));
Oracle:
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
SQL Server:
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
Below are examples for specifying a primary key by altering a table:
MySQL:
ALTER TABLE Customer ADD PRIMARY KEY (SID);
Oracle:
ALTER TABLE Customer ADD PRIMARY KEY (SID);
SQL Server:
ALTER TABLE Customer ADD PRIMARY KEY (SID);
Note: Before using the ALTER TABLE command to add a primary key, you'll need to make sure that the field is defined as 'NOT NULL' -- in other words, NULL cannot be an accepted value for that field.

This slick MySQL syntax allows you to increment or decrement an existing number in a table without first having to read the value. This is a nice way to increment an access counter.

To increment the value ‘counter’ by one for the row in table ‘images’ where ‘image_id’ is ‘15′, use:
UPDATE images SET counter=counter+1 WHERE image_id=15

how to get system time and date in c
http://www.intechgrity.com/automatically-insert-current-date-and/
mysql> insert into main_tab values(1,1,'suni','f','cpgm',CURDATE(),1000.00);
Query OK, 1 row affected (0.00 sec)

mysql> select * from main_tab;
+----+--------+------+--------+----------+------------+---------+
| id | acc_no | name | gender | password | doj        | amount  |
+----+--------+------+--------+----------+------------+---------+
|  1 |      1 | suni | f      | cpgm   | 2010-11-19 | 1000.00 |
+----+--------+------+--------+----------+------------+---------+
1 row in set (0.00 sec)

mysql> insert into main_tab values(2,2,'prem','m','welcome',now(),1000.00);Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from main_tab;
+----+--------+------+--------+----------+------------+---------+
| id | acc_no | name | gender | password | doj        | amount  |
+----+--------+------+--------+----------+------------+---------+
|  1 |      1 | suni | f      | cpgm   | 2010-11-19 | 1000.00 |
|  2 |      2 | prem | m      | welcome   | 2010-11-19 | 1000.00 |
+----+--------+------+--------+----------+------------+---------+
2 rows in set (0.00 sec)

add new column in to an existing mysql table

Drop Column:

 alter table icecream drop column flavor ;

Drop Column is used to remove an entire column and all its data from a table.

Add Column:

alter table icecream add column flavor varchar (20) ; 

 mysql> desc deposit;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| acc_no | int(5)      | YES  |     | NULL    |       |
| amount | float(10,2) | YES  |     | NULL    |       |
| ddate  | date        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table deposit add column flag char(1);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc deposit;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| acc_no | int(5)      | YES  |     | NULL    |       |
| amount | float(10,2) | YES  |     | NULL    |       |
| ddate  | date        | YES  |     | NULL    |       |
| flag   | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc main_tab;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| acc_no   | int(5)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20) | YES  |     | NULL    |                |
| gender   | varchar(6)  | YES  |     | NULL    |                |
| password | varchar(10) | YES  |     | NULL    |                |
| doj      | date        | YES  |     | NULL    |                |
| amount   | float(10,2) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> alter table main_tab add column ldate date;
Query OK, 30 rows affected (0.07 sec)
Records: 30  Duplicates: 0  Warnings: 0

mysql> desc main_tab;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| acc_no   | int(5)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20) | YES  |     | NULL    |                |
| gender   | varchar(6)  | YES  |     | NULL    |                |
| password | varchar(10) | YES  |     | NULL    |                |
| doj      | date        | YES  |     | NULL    |                |
| amount   | float(10,2) | YES  |     | NULL    |                |
| ldate    | date        | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

how to change/modify particular column value in mysql table
mysql> select * from deposit;
+--------+---------+------------+------+
| acc_no | amount  | ddate      | flag |
+--------+---------+------------+------+
|      1 | 2000.00 | 2010-11-26 | d    |
|      1 | 2000.00 | 2010-11-26 | d    |
|      2 | 3000.00 | 2010-11-26 | d    |
|      3 | 2000.00 | 2010-11-26 | d    |
+--------+---------+------------+------+
4 rows in set (0.00 sec)

mysql> update deposit set amount=5000.00 where acc_no=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from deposit;
+--------+---------+------------+------+
| acc_no | amount  | ddate      | flag |
+--------+---------+------------+------+
|      1 | 2000.00 | 2010-11-26 | d    |
|      1 | 2000.00 | 2010-11-26 | d    |
|      2 | 5000.00 | 2010-11-26 | d    |
|      3 | 2000.00 | 2010-11-26 | d    |
+--------+---------+------------+------+
4 rows in set (0.00 sec)