Suppose you have a dump of more than one databases in a single dump file, then you can restore one or all the databases.

mysql -u root -p --one-database DBNAME < DUMPFILE.SQL

You should take care of a few things when you take the dump.

• Create a database by that name in mysql if it does not exist.
• Switch to the database you want to restore, this will help restore the database properly.
• You need to be outside mysql environment to run this command.

For more specific restoration of databases from a dump file you can try mydumpsplitter. 

Points To Remember

1. You can take dump of Mysql using mysqldump in windows, linux or ubuntu operating systems.
2. You should reach to the location of mysql/bin in a windows platform to use mysqldump, you can use from any location in ubuntu and linux.

Taking dump of whole database

When you want to take the dump of the whole file, you can simply do it with the following command

mysqldump -u db_username -p database_name > path/dump.sql

Here the database_name is your database name and path is absolute path where you want to create dump.

Taking dump of a table

When you want to take the dump of a single table you can do this by the following command.

mysqldump -u db_username -p database_name tablename > path/dump.sql

Taking dump of a remote database

When you want to take a dump of a remote user you can do this by the following command.

mysqldump -u <db_username> -h <db_host> -p database_name > path/dump.sql

Taking dump of a remote database table

When you want to take the dump of a single table from a remote database you can use the following command

mysqldump -u <db_username> -h <db_host> -p database_name table_name > path/dump.s

How to restore the database from a dump file.

Points To Remember

• We can use subqueries in databases like Mysql and avoid multiple queries.
• Syntax - INSERT INTO table1 SELECT * FROM table2;
• We do not write VALUES in the query. 

Example : Insert Values from one table to another.

Suppose we have two table person and person_old with columns

• id int primary key
• name varchar
• age number
• contact varchar
• description text

And we want to copy all the details of the table person_old to table person. Then we must write our query as. This will insert all the rows of the person_old table to the new table person.

insert into person select * from person_old;

Also if we want to add only a few columns of the table person_old to the table person.Suppose we want to add only name, age and contact to the table person from table person_old where age is less than 25, then we can query as follows.

insert into person(name,age,contact) select name,age,contact from person_old  where age < 25;

Why this question is important ?

This is a most frequently asked question in an interview these days. This question tests the thinking of a candidate and how much the candidate knows about the database. This question  cannot be done without the use of a sub query and most of the people are not comfortable writing the sub-queries. So if you are not very comfortable with database queries you might get tricked in this question.

Example 

Suppose we have a table with id's from 1 to 1000 and we need to find the second largest id from this table.
Let's say we have table

create table test(id int not null primary_key, name varchar(50));

So we can get the second largest value of id in the following ways.

• SELECT max(id) FROM test WHERE id!=(SELECT MAX(id) FROM test GROUP BY id) GROUP BY id;

• SELECT MIN(id) FROM test (SELECT id FROM test ORDER BY id DESC LIMIT 2) t ;

In Query 1 : SELECT max(id) FROM test WHERE id!=(SELECT MAX(id) FROM test GROUP BY id) GROUP BY id;
We first find the largest id from the table "test" by the sub query "(SELECT MAX(id) FROM test GROUP BY id)", in our case this will give 1000 .Now we select the max "id" from table test where id is not equal to this id, thus it will give 999 as the max id.

In Query 2 : SELECT MIN(id) FROM test (SELECT id FROM test ORDER BY id DESC LIMIT 2) t ;
We first find the number of records in decreasing order that we have to find, suppose we have to find the second largest value then we will find 2 records, if we need to find the third largest value then we need to find the three largest records.SELECT id FROM test ORDER BY id DESC LIMIT 2, gives us 2 records for id = 1000 and 999. Now we select the id with the minimum value, i.e id =999, which is minimum between 1000 and 999.

Using Query 2, we can find out any largest value from  given table. We just need to change the LIMIT N where 'N' is the n-largest value in the table e.g for fourth largest value it will be LIMIT 4 and for 10th largest value it will be LIMIT 10.

Points To Remember 

• Download Mysql Java Connector by clicking on the link.
• Put the above downloaded jar file in the lib folder of your project.

Code : Mysql JDBC connection in Java

import java.sql.Connection;
import java.sql.DriverManager;


public class JavaMysqlConnection {

 
    private String host = "localhost:3306"; // For running on localhost
    private String db = "databaseName";
    private String userName = "userName";
    private String password = "password";
     
    public Connection getConnection() {
       try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn =  DriverManager.getConnection("jdbc:mysql://"+ host +"/"+db,userName, password);
            return conn;
       }catch(Exception e){
             e.printStackTrace();
       }
       return null;
    }
}

   

public class Test{

      public static void main(String[] argv) {
         Connection conn =  new JavaMysqlConnection().getConnection();
         if(conn == null)
             System.out.println("Connection was not established");
         else
             System.out.println("Connection is successfully established");
     }
}

This is a very generic code that you can use in both your standalone projects and web based projects. Whenever you need to get a connection to database, you just need to create an object of class JavaMysqlConnection and call its getConnection() method.

Syntax : SHOW CREATE TABLE tablename

When To Use ?

• You already have a table in the database and you want to view how this table was created.
• You want to check all the datatypes, default value, nullability of the columns.
• You want to see all the constraints and keys that are applicable to this table.
• This command can be used to recreate the table as it is with all constraints and keys set. 

Example

Suppose we have two tables Institute and User  and we want to check the schema of the tables or in simple terms how these tables were created, then we will use this command SHOW CREATE TABLE user, where user is the table name whose schema we want to check.
The above command will give the following output.

user  | CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `city` varchar(255) DEFAULT NULL,
  `date_created` datetime NOT NULL,
  `dob` datetime DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `firstName` varchar(30) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `is_enabled` tinyint(1) NOT NULL,
  `is_expired` tinyint(1) DEFAULT NULL,
  `lastName` varchar(30) DEFAULT NULL,
  `last_updated` datetime NOT NULL,
  `mobile` bigint(20) DEFAULT NULL,
  `pwd` varchar(255) NOT NULL,
  `token` varchar(255) DEFAULT NULL,
  `user_role` int(11) NOT NULL,
  `institute_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_52d8b7773f334f29885182c5fad` (`email`),
  KEY `FK_d87d9b2cd99f4f999b750d83637` (`institute_id`),
  CONSTRAINT `FK_d87d9b2cd99f4f999b750d83637` FOREIGN KEY (`institute_id`) REFERENCES `institute` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |