Friday, June 10, 2011

Mysql Commands

Here you get some basic commands of mysql which use by many developers in their regular tasks:

1. Show all exisitng databases :
mysql> show databases;

2. Create Database :
mysql> create database database_name;

example:
mysql> create database production;

After Creating database in mysql you cannot directly use it, So for put it into use you have following commnad.

3. Use Database :
mysql> use database_name;

After use you can do operations on that database and you do not need to pass databse name in query or any operation:

4. For show existing tables in Database:
mysql> show tables;

5. Create Table :
mysql> create table table_name(field_name datatyape(size));

example:
mysql> create table mytest(id integer(11)primary key,code varchar(5),name varchar(20));

6. Description of tables (Check columns in table) :
mysql> desc table_name;

7. Remove table from database :
mysql> drop table table_name;

example:
mysql> drop table mytest;

8. Remove whole database or Drop database :
mysql> drop database database_name;

example:
mysql> drop database production;

What to do for restore database from file ?

For take backup and do restore you can do it directly giving commands without opening mysql prompt also:

9. Restore Database:
mysql - u user -p password database_name < backfile_name.sql

How to take backup of mysql database ?
10. Backup Databse:

- Take backup whole both tables and data:
mysqldump -u user -p password database_name > backupfile_name.sql

- For take backup only structure:
mysqldump -u user -p password --no-data database_name > backupfile_name.sql

- For take backup only data:
mysqldump -u user -p password --no-create-info database_name > backupfile_name.sql


Please give more inputs if you want regarding these commands.