Mysql Interview Questions for Experienced

This page all about Mysql interview questions for experienced candidates, In any application development mysql is database is mandatory. Mysql is the one the best database is being used, Now it became Mysqli.

Table of Contents

What is the technique involved for storage engine in MYSQL
What are the drivers available in MySQL?
How to make database connection MySQL through PDO
What are the different tables present in MySQL?
What is the difference between MyISAM and InnoDB storage engines in MySQL?
What is difference between TRUNCATE and DELETE in mysql
What is the difference between PRIMARY key and UNIQUE Key in Mysql
How to display 50 records from 20th row of the table.
What is the command to check table is exist
What is the difference between primary key and candidate key?
How can you show indexes defined for a table by Mysql Query?
What is the difference between ORDER BY and GROUP BY in MySQL
What is the different between NOW() and CURRENT_DATE()

What is the technique involved for storage engine in MYSQL

Storage mechanism
Locking levels
Indexing
Capabilities and functions

What are the drivers available in MySQL?

PHP Driver
JDBC Driver
ODBC Driver
C WRAPPER
PYTHON Driver
PERL Driver
RUBY Driver
CAP11PHP Driver
Ado.net5.mxj

How to make database connection MySQL through PDO

<?php
define('DB_HOST','localhost');
define('DB_USER','user');
define('DB_PASS','password');
define('DB_NAME','db');
try
{
$dbh = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,DB_USER, DB_PASS,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
}
catch (PDOException $e)
{
exit("Error: " . $e->getMessage());
}
?>

<?php

define('DB_HOST','localhost');

define('DB_USER','user');

define('DB_PASS','password');

define('DB_NAME','db');

try

{

$dbh = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,DB_USER, DB_PASS,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));

}

catch (PDOException $e)

{

exit("Error: " . $e->getMessage());

}

What are the different tables present in MySQL?

In basic MySQL support 5 types for table engine. But among them, MyISAM and INNO DB is the most usable table
Here INNO DB is the default table engine. Because of Its Support RDBMS.

MyISAM
INNO DB
Heap
Merge
ISAM

What is the difference between MyISAM and InnoDB storage engines in MySQL?

1 . InnoDB provides us row-level locking while MyISAM provides us table-level locking.
2 . InnoDB supports foreign key constraints wheres in MyISAM does not have foreign key constraints.
3 . InnoDB does not support full-text search wheres MyISAM supports us full-text search.

What is difference between TRUNCATE and DELETE in mysql

1. DELETE Auto increment ID will not be reset, But In TRUNCATE auto-increment ID will be Reset.
2. We can not use where clause in truncate but in delete, we can use conditions using where clause
3 . Truncate is much faster than Delete.

What is the difference between PRIMARY key and UNIQUE Key in Mysql

1.UNIQUE key can be a NULL value but PRIMARY key cannot take NULL values.
2. A Table can have multiple UNIQUE keys but cannot more than one PRIMARY key.

How to display 50 records from 20th row of the table.

SELECT * FROM TABLE_NAME
LIMIT 19,50;

1 2	SELECT * FROM TABLE_NAME LIMIT 19,50;

What is the command to check table is exist

CHECK TABLE table_name;

What is the difference between primary key and candidate key?

primary key a is a key which is a combination of unique and never be null.
a primary key must be only one per table.
A candidate key is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data.
Every primary key is a candidate key, but candidate key can not be the primary key.

How can you show indexes defined for a table by Mysql Query?

SHOW INDEX FROM TABLE_NAME ;

What is the difference between ORDER BY and GROUP BY in MySQL

When MySQL fetch query fetches data set from the database, in order to get a result in ascending or descending order you have to use the keyword ORDER BY. By default, records are sort in ascending.

SELECT column1, column2, … FROM TABLE_NAME ORDER BY column1 DESC;

1	SELECT column1, column2, … FROM TABLE_NAME ORDER BY column1 DESC;

GROUP BY used with aggregate functions to group the result-set by one or more columns.

SELECT column_name FROM TABLE_NAME WHERE condition GROUP BY column_name

1	SELECT column_name FROM TABLE_NAME WHERE condition GROUP BY column_name

What is the different between NOW() and CURRENT_DATE()

NOW () is used to show current year, month, date, hours, minutes and seconds.
CURRENT_DATE() shows current year,month and date only.