Thursday, October 7, 2010

MySQL tutorial and commands part 3

Hi Guys , here are some more mysql commands which is useful in day 2 day work.

Method for converting current time stamp to date:
--------------------------------------------------------------
select from_unixtime(left(1201159475416, 10));
this  method is used to convert the timestamp to the date time format in mysql ,left() method will return 10 char from the specified string  if we store time stamp value in millisecond.


mysql> select from_unixtime(left(1210916129820  , 10))
    -> ;
+------------------------------------------+
| from_unixtime(left(1210916129820  , 10)) |
+------------------------------------------+
| 2008-05-16 01:35:29                      |
+------------------------------------------+
1 row in set (0.00 sec)



Viewing mysql command history
-------------------------------------
There is one hidden file called .mysql-history ,on which all commands are stored which we typed in  MySQL console. its generally resides in home directory.



Taking backup of MyISAM database in mysql
------------------------------------------------------
There are multiple way to take backup of  MyISAM tables in mysql e.g. using mysqldumb.
One way to take the backup of a database to copy the files .MYD, .MYI and .frm, this way you can write scripts which can copy database from one server to other , merge databases etc.


To remove a column from table
------------------------------------
alter table ice cream drop column flavor ;


Changing  size of a coloum and datatype of a colom in mysql
--------------------------------------------------------------------
alter table people modify name VARCHAR(35) ;
alter table ORDERS modify CLIENT  varchar(255) default NULL;


Displaying index from a mysql table
------------------------------------------
mysql> SHOW INDEX FROM   database.ORDERS;



Getting mysql server version
----------------------------------
you can use method called version()

mysql> select version();
+-----------+
| version() |
+-----------+
| 3.23.58 |
+-----------+
1 row in set (0.02 sec)

to read further please see next set of mysql commands tutorial

1 comment :

deepak said...

MySQL is my favorite database and I love to learn more and more MySQL commands. I have read your all MySQL command tutorials and I found them very good. write some more mysql command tutorial please.

Post a Comment