MySQL interview questions and answers

Home >> MySQL interview questions and answers >>

  MySQL interview questions and answers part - 5

This article will give you an overview of MySQL questions and answers that may be helpful for you in an interview or online test.

Question: What is MySQL data directory? How to determine the location of the data directory.
MySQL data directory is most important location in which all MySQL databases are stored. The default data directory is located in "data" folder inside mysql, inside "data" directory you can see the same folder name as your database name.

 Example:
 // For Example let me show the data folder path in WAMP server.
 wamp\bin\mysql\mysql\data

Question: Where MyISAM table will be stored and also give their formats of storage
Each MyISAM table is stored on disk in three formats:

  • The .frm file stores the table definition.
  • The data file has a .MYD (MYData) extension.
  • The index file has a .MYI (MYIndex) extension.

.frm, .MYD and .MYI files will be stored inside mysql/data folder. Let me explain it through an example.

Let's assume you have a database named as phpcodehub. Now you can see "phpcodehub" folder inside MySQL "data" directory, it means that inside "data" directory you can see the same folder name as your database name, like below example.

 Example:
 // phpcodehub database inside MySQL data directory.
 wamp\bin\mysql\mysql\data\phpcodehub

Let's assume you have an table named as "test_table_1" inside phpcodehub database and it's storage engine is MyISAM. Now you can see .frm, .MYD and .MYI files inside your "phpcodehub" folder with prefixed by table name, like below example.

 Example:
 // Let me take an example of WAMP server to show the complete path of .frm, .MYD and .MYI files
 wamp\bin\mysql\mysql\data\phpcodehub

 // test_table_1 files inside phpcodehub folder in MySQL data directory.
 test_table_1.frm
 test_table_1.MYD
 test_table_1.MYI

Note : 

  • An interesting feature of MyISAM is that one can copy/paste a database folder to another machine and then use the database (without a dump).

Question: Where InnoDB table will be stored?
InnoDB stores its tables and indexes in a tablespace. By default, when InnoDB is initialized, it creates 3 important files in the MySQL data directory – ibdata1, ib_logfile0 and ib_logfile1.

  • The ibdata1 is the data file in which system and user data will be stored.
  • The ib_logfile0 and ib_logfile1 are the redo log files.

By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because each table that is created when this setting is in effect has its own tablespace.

InnoDB table files willl be stored inside mysql/data folder. Let me explain it through an example.

Let's assume you have a database named as phpcodehub. Now you can see "phpcodehub" folder inside MySQL "data" directory, it means that inside "data" directory you can see the same folder name as your database name, like below example.

 Example:
 // phpcodehub database inside MySQL data directory.
 wamp\bin\mysql\mysql\data\phpcodehub

Let's assume you have an table named as "test_table_2" inside phpcodehub database and it's storage engine is InnoDB. Now you can see .frm and .ibd files inside your "phpcodehub" folder with prefixed by table name, like below example.

 Example:
 // Let me take an example of WAMP server to show the complete path of .frm and .ibd files  
 wamp\bin\mysql\mysql\data\phpcodehub

 // test_table_2 files inside phpcodehub folder in MySQL data directory.
 test_table_2.frm
 test_table_2.ibd

Note:

  • The .frm file stores the table definition.
  • The .ibd file type is primarily associated with 'MySQL - InnoDB'. InnoDB tables and indexes can be stored in their own file (a feature called "multiple tablespaces" since in this way each table uses its own tablespace). If you have multiple tablespaces enabled, InnoDB will store the newly created table in a *.ibd file in the database directory where also belongs the table.
  • A tablespace is essentially a black box that InnoDB manages allby itself tablespace which is essentially a virtual filesystem spanning one or more files on disk. InnoDB uses tablespace for many purpose It keeps its undo log, insert buffer, doublewrite buffer, and other internal structure in the table space.
  • redo log is Oracle terminology, transaction log is InnoDB terminology.

Question: How to create and close a mysql connection?
Here I will give you examples of MySQL and MySQLi extension, but the mysql extension is deprecated and will be removed in the future : use mysqli or PDO.

MySQLi have two ways to use it.

  • MySQLi (Object Oriented)
  • MySQLi (Procedural)

MySQLi (Procedural) is similer to MySQL and I have also used MySQL and MySQLi (Procedural) in below examples.

Example:

$servername = 'database-server-name';
$username = 'database-username';
$password = 'database-password';

// Create connection using MySQL : 
$link = mysql_connect($servername, $username, $password);
if (!$link) {
    die('Connection failed: ' . mysql_error());
}
echo 'Connected successfully';

// Create connection using MySQLi : 
$link = mysqli_connect($servername, $username, $password);
if (!$link) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

// Close connection
MySQL : mysql_close($link);
MySQLi : mysqli_close($link);

Question: How to select a database?
I have used MySQL and MySQLi (Procedural) to select a database.

Example:

$servername = 'database-server-name';
$username = 'database-username';
$password = 'database-password';
$database_name = 'database-name';
$link = 'database-connection-object';

// Select a database using MySQL : 
mysql_select_db($database_name, $link);

// Select a database using MySQLi : 
Type 1 : mysqli_select_db($link, $database_name);
Type 2 : mysqli_connect($servername, $username, $password, $database_name);

Question: How to get last inserted record id?
I have used MySQL and MySQLi (Procedural) to return last inserted record id.

Example:

// Using MySQL : 
$insertSQL = 'INSERT SQL QUERY';
$result = mysql_query($insertSQL);
// Print auto-generated id
echo "New record has id: " . mysql_insert_id();

// Using MySQLi :
$insertSQL = 'INSERT SQL QUERY';
$link = 'database-connection-object';
$result = mysqli_query($link, $insertSQL);
// Print auto-generated id
echo "New record has id: " . mysqli_insert_id($con);

Question: How to execute an sql query? How to fetch its result?
I have used MySQL and MySQLi (Procedural) to execute an sql query and fetch its result.

Example:

// Using MySQL : 
$select_sql = "SELECT username FROM user WHERE user_id = 2 ";
$result = mysql_query($select_sql);
if(mysql_num_rows($result) > 0) {
   //Output data of each row
   while($row = mysql_fetch_row($result)) {
        echo "user name : " . $row[0];
   }
} else {
    echo "0 results";
}

// Using MySQLi :
$link = 'database-connection-object';
$select_sql = "SELECT username FROM user WHERE user_id = 2 ";
$result = mysqli_query($link, $select_sql);
if(mysqli_num_rows($result) > 0) {
   //Output data of each row
   while($row = mysqli_fetch_row($result)) {
        echo "user name : " . $row[0];
   }
} else {
    echo "0 results";
}

Question: How we can retrieve the data in the result set of MySQL using PHP?
We can retrieve the data in the result set of MySQL using PHP in four ways.

1. mysql_fetch_row
2. mysql_fetch_array
3. mysql_fetch_object
4. mysql_fetch_assoc

Let's me explain all four with example.

1. mysql_fetch_row()
Fetch a result row as an numeric array.

Example :

$select_sql = "SELECT username FROM user WHERE user_id = 2";
$result = mysql_query($select_sql);
$row = mysql_fetch_row($result);
echo $row['0'];

2. mysql_fetch_array()
Fetch a result row as an associative array, a numeric array and also it fetches by both associative & numeric array.

Example :

$select_sql = "SELECT username FROM user WHERE user_id = 2";
$result = mysql_query($select_sql);
$row = mysql_fetch_array($result);
 
// numeric array
echo $row['0'];

// associative array
echo $row['username'];

3. mysql_fetch_object()
Fetch a result row as an object.

Example :

$select_sql = "SELECT username FROM user WHERE user_id = 2";
$result = mysql_query($select_sql);
$row = mysql_fetch_object($result);
echo $row->username;

4. mysql_fetch_assoc()
Fetch a result row as an associative array.

Example :

$select_sql = "SELECT username FROM user WHERE user_id = 2";
$result = mysql_query($select_sql);
$row = mysql_fetch_assoc($result);
echo $row['username'];

 

Comments

very good explanation of MySQL data directory.

It's really very good explanation of MySQL data directory.

  1 year ago      Amit Kumar      

Write your comment now