Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Sql Mysql-alter Mysql Alter Column Datatype

Related Tutorials


 
 

Share on Google+Share on Google+

Mysql Alter Column Datatype

Advertisement
Mysql Alter Column Datatype is used to modify the table and change the datatype of field.

Mysql Alter Column Datatype

     

Mysql Alter Column Datatype is used to modify the table and change the datatype of field.

Understand with Example

The Tutorial illustrate an example from 'Mysql Alter Column  Datatype'. To understand this example we create a table 'employees' with empid as Primary Key.

Query to create table:

CREATE TABLE `employees` (               
            ->Empid int(10),  
            ->Empname varchar(60),    
             ->date date
            ->PRIMARY KEY(empid)  
           ->);

Query to insert data into Table named employees:

The Query insert into add the records or rows into the table 'employees'.

mysql>insert into employees values(01,'Girish','2008-12-22');
Query OK, 1 row affected (0.02 sec)
mysql>insert into employee1 values(02,'Komal','2008-12-23');
Query OK, 1 row affected (0.02 sec)

Query to view data of  Table named employees:

To view the detail of table 'employee1' we use select query that return the detail of records.

mysql> select * from employee;

Output:-

+-------+---------+------------+
| Empid | Empname | date       |
+-------+---------+------------+
|     1 | Girish  | 2008-12-22 |
|     2 | Komal   | 2008-12-23 |
+-------+---------+------------+
2 rows in set (0.00 sec)

Data type of Table named employees before altering column data type:

The describe employee describe the field name ,data type ,null ,key etc in table employees

+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| empid   | int(11)     | NO   | PRI | NULL    | auto_increment |
| Empname | varchar(60) | NO   |     |         |                |
| date    | date        | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

Query to alter column data type of the Table named employees:

The Alter Query redefine the table 'employee' and change the data type of column  'empid'.

mysql> alter table employees
    -> change empid empid varchar(100);
Query OK, 2 rows affected (0.13 sec)
Records: 2  Duplicates: 0  Warnings: 0

Data type of Table named employees after altering column data type:

The describe employee describe the field name ,data type ,null ,key etc in table employees.

mysql> describe employees;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| empid   | varchar(100) | NO   | PRI |         |       |
| empname | varchar(130) | YES  |     | NULL    |       |
| date    | date         | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
Advertisement

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: January 13, 2009

Related Tutorials

Discuss: Mysql Alter Column Datatype  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:3
suhas
February 16, 2012
alter table

i m changing varchar to date type then already inserted data m/d/y will change into the y/d/m/ any solution thanks in welcome
Pradesh dalai
April 29, 2012
how to know employe details

i am a employee having my emp id also but i cant able to know my emploee details through this emplyoee id, kindly help me please howb to know
bluefiber
October 12, 2012
Use 'modify' instead of 'change'

The method you describe works but is not the most efficient as it requires you to enter the column name twice. Your method then be as follows: mysql> alter table employees -> modify empid varchar(100);
DMCA.com