Lets Play With SQL

In My knowledge SQL or MySQL (Structured Query Language) is a tool for storing data in form of rows and columns. But when I started working deeply with SQL I realized that it is not only a query language ,we can use it as a power full programming tool for data manipulation.

We all know about basic SQL commands like ‘Create Table’,'Insert Into’, ‘Select’, ‘Delete’,'Drop’ etc. Here I am try to introduce some commands that not regularly use, but very use full.

Usually LIKE is using for pattern matching. But we can use ‘LIKE’ also for creating a new table structure as an existing table.

"CREATE TABLE new_table_name LIKE existing_table"

After creating table structure we can copy contents from other table using INSERT INTO command

"INSERT INTO new_table SELECT * FROM existing_table"

It copy all data from one table to another. If we need to copy only specific column we can specify column names

"INSERT INTO new_table (col1,col2,......)SELECT col1,col2,.... FROM existing_table"

we can also give specify copy data with in a limit.

"INSERT INTO new_table SELECT * FROM existing_table LIMIT 1000 OFFSET 1000"

It copies data from 1000th raw up to 2000th row.

We can rename a table using rename command;

"RENAME TABLE tanle_name TO new_name"

There may be some situations that we need to back up all the data from a table or back up entire data base. For this purpose we use MySQL dump command. These are very use full system commands for creating back up.

MySQL Dump Export command for backing up:

Dump Database:

"mysqldump --user='username' --password='password' db_name > filename.sql"

Only Database Structure:

"mysqldump --user='username' --password='password' --no-data db_name > filename.sql"

Database Data:

"mysqldump --user='username' --password='password' --no-create-info db_name > filename.sql"

Dump Severel Databases in to a single file:

"mysqldump --user='username' --password='password' --databases  db_name1,db_name2 > filename.sql"

Dump All databases in Sever:

"mysqldump --user='username' --password='password' --all-databases  > filename.sql"

Restore and Import Databases:

"mysql --user='username' --password='password'  db_name < filename.sql"

To Export A Table Structure:

"mysqldump --user='username' --password='password' db_name table_name >filename.sql"

To Export A Table data:

"mysqldump --no-create-info --compact --user='username' --password='password' db_name table_name > table_name.sql"

Import Table:

"mysql --user='username' --password='password'  db_name < table_name.sql"