"20" questions available for MySQL.

Q. What are the different Storage Engines present in MySQL table Database, which one is default?

MySQL has following types of Storage Engine :

  1. MyISAM, The default storage engine till the release of mysql version 5.5. Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type, an .frm file stores the table format, the data file has an .MYD(MYData) extension, the index file has an .MYI (MYIndex) extension.
  2. InnoDB, becomes the default storage engine with the release of MySQL 5.5, InnoDB is a transaction-safe storage engine which obeys all the ACID property and has commit, rollback, and crash-recovery capabilities to protect user data.
  3. Merge, the MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one. “Identical” means that all tables have identical column and index information. You cannot merge MyISAM tables in which the columns are listed in a different order, do not have exactly the same columns, or have the indexes in different order.
  4. Heap/Memory, the MEMORY or HEAP storage engine creates special-purpose tables with contents that are stored in memory. Because the data is vulnerable to crashes, hardware issues, or power outages, only use these tables as temporary work areas or read-only caches for data pulled from other tables.
  5. NDB,also known as NDBCLUSTER — This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.
  6. CSV, The CSV storage engine stores data in text files using comma-separated values format. You can use the CSV engine to easily exchange data between other software and applications that can import and export in CSV format.

Q. How you can find a record with 2nd highest value from a mysql table database ?

Mysql gives a number of ways to find a record with second highest value. Some of few are:

  1. By using aggregate function (Complex way), you can use the mysql max() function using in the below example.
    # To find only 2nd highest value
    SELECT max(`field_name`) FROM `table_name`
    #To find all the attributes that poses 2nd highest value
    SELECT * FROM `table_name` WHERE `field_name` = (
        SELECT max( `field_name` ) FROM `table_name` WHERE `field_name` < 
               (SELECT max( `field_name` ) FROM `table_name` ) ) ;
  2. Without using aggregate function (Simple way) , you can first arrange the records in descending order and then you can limit the records , see the below example.
    SELECT * FROM `table_name` ORDER BY  `field_name` DESC LIMIT 1 , 1 ;
    # LIMIT 1,1 represents the offset value and the limit value respectively. #

Q. How to backup a MySQL database ?

Use the following command to backup/dump a mysql database.

mysqldump -u [user-name] -p[password] [database_name] > dumpfilename.sql

#the following example will backup the database "test"
mysqldump -u root -p test > test.sql

#Backup multiple databases
mysqldump -u root -p test1 test2 test3 > dumptest.sql  //the above example will backup the test1 test2 test3 database to dumptest.sql

#Backup all the databases
mysqldump -u root -p --all-databases > all-database.sql

#Backup a database table
mysqldump -u [username] -p[password] [db] [table] \  > dumpfile.sql

Q. How to connect to a database present in the remote server ?

You need hostname , user-name , password of the remote server database to connect. See the following example.

#To connect to a remote db server
mysql -h [hostname] -u [username] -p[password] // this will connect you to remote server 

mysql -h -u root -ptempaassword

#To list all database;
mysql > show databases; //to list all the database present

#To use a database;
mysql > use db-name;  // to use desired database

Q. Differentiate between FLOAT and DOUBLE ?

FLOAT stores floating point numbers with accuracy up to eight places and has four bytes while DOUBLE stores floating point numbers with accuracy upto 18 places and has eight bytes.

Start a discussion...or just leave a comment.