Mysql Interview Questions for Experienced

Mysql Interview Questions for Experienced




What are 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());
}
?>

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 Its Support RDBMS.

  • MyISAM
  • INNO DB
  • Heap
  • Merge
  • ISAM

What is 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 support 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 difference between PRIMARY key and UNIQUE Key in Mysql

1.UNIQUE key can be a NULL value but PRIMARY key can not  take NULL values.
2. A Table can have multiple UNIQUE key 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;

What is 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.
primary key must be only one per table.
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 primary key.

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

SHOW INDEX FROM TABLE_NAME ;

What is difference between ORDER BY and GROUP BY in MySQL

When mysql fetch query fetch data set from database, in order to get 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;

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

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.