Saturday, October 7, 2017

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)

Further Learning
MySQL Fundamentals by Pinal Dave
MySQL Fundamentals Part 2 by Pinal Dave
High Performance MySQL: Optimization, Backups, and Replication

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