MySQL tutorial and commands part 2

Here are some more mysql commands from which is very useful for anyone working with mysql database.
this are very useful for application developer which is going to use mysql database for there applications.


Increasing no of connections for mysql
---------------------------------------------
You can increase this value in main config file (e.g., /etc/my.cnf) using this syntax:

[mysqld]
set-variable=max_connections=250



Myisamchk command
---------------------------
if you run "'myisamchk ORDERS.MYI" it will check whether ORDERS table is corrupted or not. if corrupted it will say

MyISAM-table 'ORDERS.MYI' is corrupted Fix it using switch "-r" or "-o"

to fix it you can run "'myisamchk -r ORDERS.MYI"


UNIX_TIMESTAMP function
-------------------------------------
SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00'); -> 875996580
give date and will return no of seconds , it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC



Diff between 2 dates
------------------------

mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001','1997-12-30 01:01:01.000002');

      -> '46:58:57.999999'

Returns Time to seconds
------------------------------
Returns the time argument, converted to seconds.

mysql> SELECT TIME_TO_SEC('22:23:00');

        -> 80580

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
--------------------------------------------------------
If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD. The server interprets date as a value in the current time zone and converts it to an internal value in UTC. Clients can set their time zone


TAKING THE BACKUP OF A TABLE
-----------------------------------------------

CREATE TABLE ORDER_TEMP SELECT * FROM ORDER;



Running mysql query from unix command prompt
----------------------------------------------

mysql -u root -h <hostname>  <database name >-e "UPDATE ORDERT SET TYPE ='PARTIAL' WHERE TYPE='FULL'

-h for host and –e for expression.


Showing list of databases
---------------------------

mysql> show databases;

hope this  will be useful.

to read further please see next set of mysql commands tutorial

MySQL tutorial and commands Part 1
MySQL tutorial and commands part 3

Difference between truncate and delete in SQL

Basic and Advanced SELECT command example

How to manage transaction in MySQL

MySql Tutorial : mysqldump utility in mysql