Here is the compilation of Top 51 Frequently Asked MySQL Interview Questions And Answers,I hope you will like that and it will be useful in the interviews
1) What is the difference between sql and Mysql?
SQL or Structured Query Language is a programming language designed for managing data held in a Relational Database Management System. Mysql is a open source, relational database management System.
2) What is MySQL database?
MySQL is a open source, relational database management System
it is a database system used on the web which runs on a server
it is very fast, reliable, and easy to use and ideal for both small and large applications
it uses standard SQL
it compiles on a number of platforms and is free to download and use
it is developed, distributed, and supported by Oracle Corporation
it is named after co-founder Monty Widenius’s daughter: My
3) How to set the root password if it is not set yet?
mysqladmin -u root -p password “newpassword”
4) How to set the root password if it has some old password?
mysqladmin -u root -p’oldpassword’ password newpass
5) In how many ways can you retrieve data in the result set of MySQL using PHP? What is the difference between mysql_fetch_object and mysql_fetch_array?
We can retrieve data in the result set of MySQL using PHP in four Ways
1.mysqli_fetch_row >> Get a result row as an enumerated array
2.mysqli_fetch_array >> Fetch a result row as associative and numeric array
3.mysqli_fetch_object >> Returns the current row of a result set as an object
4.mysqli_fetch_assoc >> Fetch a result row as an associative array
mysqli_fetch_object() is similar to mysqli_fetch_array(), with one difference –
an object is returned instead of an array, which implies that that we can only access the data by the field names, and not by their offsets (numbers are illegal property names).
6) What is SQLyog?
Answer
SQLyog program is the most popular GUI tool for admin. It is the most popular MySQL manager and admin tool. It combines the features of MySQL administrator, phpMyadmin and others MySQL front ends and MySQL GUI tools.
7) How to shutdown MySQL server Safely?
Answer
To shutdown MySQL server safely, type the following command.
mysqladmin -u root -p shutdown
Enter password:
You can also use the following commands to start/stop MySQL server.
# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start
8) What is the default port of MySQL Server? and how can we change it?
Answer
The default port of MySQL Server is 3306.
You can specify new port in my.cnf as port= and then restart the mysql service
9) How is MyISAM table stored?
Answer
MyISAM table is stored on disk in three formats.
-‘.frm’ file : storing the table definition
-‘.MYD’ (MYData): data file
-‘.MYI’ (MYIndex): index file
10) How is mysql database created and dropped?
CREATE DATABASE IF NOT EXISTS temp_database;
SHOW DATABASES;
DROP DATABASE IF EXISTS temp_database;
11) How to recover MySQL root password if it is lost
Answer
a)As Linux system root user stop the database process: /etc/init.d/mysql stop
(or: service mysql stop)
b) Start MySQL in safe mode and skip the use of the “grant tables”: /usr/bin/mysqld_safe –user=mysql –socket=/var/lib/mysql/mysql.sock –pid-file=/var/run/mysqld/mysqld.pid –datadir=/var/lib/mysql –skip-grant-tables –skip-networking &
c) Reset the MySQL root password: mysqladmin -u root flush-privileges password newpassword
d) Stop MySQL running in safe mode: kill `cat /var/run/mysqld/mysqld.pid`
e) start MySQL: /etc/init.d/mysql start
f) The new MySQL root password can now be used: mysql -u root -p
Respond with the password: newpassword
12) What are the various database engines with Mysql?
Answer
13) How to find the version of mysql?
Answer
We can use mysqladmin version to find that out
14) How the find the processlist of mysql?
Answer
mysqladmin processlist
+—-+——+———–+—-+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+—-+———+——+——-+——————+
| 50 | root | localhost | | Query | 0 | init | show processlist |
+—-+——+———–+—-+———+——+——-+——————+
15) How to take backup and restore the mysql database?
Take backup of a database
mysqldump database > backup-file-<dbname>.sql;Restore a database
mysql database < backup-file-<dbname>.sql;
16) How to take backup of several database or all databases in mysql?
Answer
Dump several databases with single command
mysqldump –databases database1 [database2 …] > backup_of_databases.sql
Dump all databases using –all-databases option
mysqldump –all-databases > backup_of_all_databases.sql
17) Is it possible to dump a table data in mysql?
Answer
yes
we write MySQL query results into a .txt or .CSV files through the select statement.
mysql> SELECT name,address continent FROM person_directory INTO OUTFILE ‘e:/person.txt’;
we might get the below error
ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement
Then, please check the variable
mysql> Select @@global.secure_file_priv;
+—————————+
| @@global.secure_file_priv |
+—————————+
| /var/lib/mysql-files/ |
+—————————+
1 row in set (0.00 sec)
Now try again creating .txt or .csv files into the directory above
mysql> SELECT name,address continent FROM person_directory INTO OUTFILE ‘/var/lib/mysql-files/person.txt’;
18) How to check for plugins in mysql?
Answer
Use the command show plugins;
mysql> show plugins;
+—————————-+———-+——————–+———+———+
| Name | Status | Type | Library | License |
+—————————-+———-+——————–+———+———+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+—————————-+———-+——————–+———+———+
19) How to get the indexes on the table in mysql?
Answer
mysql> show index in wpi_links;
+———–+————+————–+————–+————–+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+———–+————+————–+————–+————–+———–+————-+———-+——–+——+————+———+—————+
| wpi_links | 0 | PRIMARY | 1 | link_id | A | 0 | NULL | NULL | | BTREE | | |
| wpi_links | 1 | link_visible | 1 | link_visible | A | 0 | NULL | NULL | | BTREE | | |
+———–+————+————–+————–+————–+———–+————-+———-+——–+——+————+———+—————+
20) How to describe table in mysql?
Answer
SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]
[LIKE ‘pattern’ | WHERE expr]
Example
show columns from wpi_links;
21) How to list all the tables in mysql database?
Answer
SHOW [FULL] TABLES [{FROM | IN} db_name]
[LIKE ‘pattern’ | WHERE expr]
Examples
show tables
22) What is command to check table is exist?
Answer
CHECK TABLE table_name;
23) How many columns can we create for index?
Answer
16
24) What is ISAM?
Answer
ISAM stands for Indexed Sequential Access Method, a method for indexing data for fast retrieval.
25) What are Federated tables?
Answer
A Federated Table is a table which points to a table in an other MySQL database instance (Might be on same OR Different server).
26) What are the drivers in MySQL?
PHP Driver
JDBC Driver
ODBC Driver
C WRAPPER
PYTHON Driver
PERL Driver
RUBY Driver
CAP11PHP Driver
Ado.net5.mxjDrivers can be downloaded at the link Download Drivers
27) What are Heap tables?
HEAP tables are present in memory and they are used for high speed storage on temporary basis.
Check Important Points about Heap Tables?
•BLOB or TEXT fields are not allowed.
•Only comparison operators can be used like =, <,>, = >,=<.
•AUTO_INCREMENT is not supported by HEAP tables.
•Indexes should be NOT NULL.
28) How will you switch to a database, and start working on that?
use <database name>;
29) How many TRIGGERS are possible in MySQL?
There are only six triggers are allowed to use in MySQL database and they are.
Before Insert
After Insert
Before Update
After Update
Before Delete
After Delete
30) What is the difference between Primary Key and Unique key?
Answer
| Primary Key | A column in a table whose values uniquely identify the rows in the table. A primary key value cannot be NULL. |
| Unique Key | Unique Keys are used to uniquely identify each row in the table. There can be one and only one row for each unique key value. So NULL can be a unique key. There can be only one primary key for a table but there can be more than one unique for a table. |
31) What is the difference between char and varchar data types?
Set char to occupy n bytes and it will take n bytes even if u r storing a value of n-m bytes
Set varchar to occupy n bytes and it will take only the required space and will not use the n bytes
32) What Are Date and Time Data Types in MySQL?
MySQL supports the following date and time data types:
DATE – A date in the range of ‘1000-01-01’ and ‘9999-12-31’. Default DATE format is “YYYY-MM-DD”.
DATETIME – A date with the time of day in the range of ‘1000-01-01 00:00:00’ and ‘9999-12-31 23:59:59’. Default DATETIME format is “YYYY-MM-DD HH:MM:SS”.
TIMESTAMP – A timestamp. The range is ‘1970-01-01 00:00:00’ to partway through the year 2037. Default DATETIME format is “YYYY-MM-DD HH:MM:SS”.
TIME – A time. The range is ‘-838:59:59’ to ‘838:59:59’. Default TIME format is “HH:MM:SS”.
TIME – A time. The range is ‘-838:59:59’ to ‘838:59:59’. Default TIME format is “HH:MM:SS”.
YEAR – A year in 4 digits in the range of 1901 and 2155. Default YEAR format is “YYYY”.
33) How to Create a New User and grant privileges?
CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;GRANT ALL PRIVILEGES on *.* to newuser@’localhost’;
FLUSH PRIVILEGES;
34) How to grant specific permission to user?
GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@’localhost’35) How to revoke specific permission to user?REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;36) How to change user password in mysql?
SET PASSWORD FOR ‘username’@’localhost’ = <password>;
or
ALTER USER ‘username’@’localhost’ IDENTIFIED BY <password>;;
37) why do we use GROUP BY and ORDER BY function in mysql?
Group By is used for retrieving information about a group of data. It is generally used with some aggregate function like SUM, AVG etc. ORDER BY is used to sort the records using column name. It can sort column in both ascending and descending order.
38) What is JOIN in mysql? What are the different types of join?
When we have to fetch records from more than one table we can use JOIN keyword. The process is known as joining the tables. There are various types of join like INNER JOIN, LEFT JOIN, RIGHT JOIN, and OUTER JOIN.
39) Why is the basic difference between LEFT JOIN, RIGHT JOIN and INNER JOIN?
INNER Join compares two tables and only returns results where a match exists. Records from the 1st table are duplicated when they match multiple results in the 2nd. INNER joins tend to make result sets smaller, but because records can be duplicated this isn’t guaranteed.LEFT join means keep all records from the 1st table no matter what and insert NULL values when the 2nd table doesn’t match.RIGHT Join means the opposite: keep all records from the 2nd table no matter what and insert NULL values when the 1st table doesn’t match.
40) If we use SUM function in mysql, does it return sum of that row or for that column?
Sum function works on the column basis and will return the sum of that particular row only.
41) What do we use to remove duplicate records while fetching a data in mysql ?
We use DISTINCT keyword.42) What is the use of count function in mysql?count() is used for fetching the total number records in a table.
43) How do we delete a row in a table?
Take an exampleDELETE FROM person_details WHERE person_id=175785;In this case it will delete the record of the person with person_id 175785
44) How do we drop a table?
DROP table customers_data;It will drop the table customers_data
45) Explain about various database engine in mysql?
Answer
| Engines | Description |
| InnoDB | This is the default storage engine for MySQL 5.5 and higher. It provides transaction-safe (ACID compliant) tables, supports FOREIGN KEY referential-integrity constraints. It supports commit, rollback, and crash-recovery capabilities to protect data. It also supports row-level locking. It’s “consistent non-locking reads” increases performance when used in a multiuser environment. It stores data in clustered indexes which reduces I/O for queries based on primary keys. |
| MyISAM | This storage engine, manages non transactional tables, provides high-speed storage and retrieval, supports full text searching. |
| MEMORY | Provides in-memory tables, formerly known as HEAP. It sores all data in RAM for faster access than storing data on disks. Useful for quick looks up of reference and other identical data. |
| MERGE | Groups more than one similar MyISAM tables to be treated as a single table, can handle non transactional tables, included by default. |
| EXAMPLE | You can create tables with this engine, but can not store or fetch data. Purpose of this is to teach developers about how to write a new storage engine. |
| ARCHIVE | Used to store a large amount of data, does not support indexes. |
| CSV | Stores data in Comma Separated Value format in a text file. |
| BLACKHOLE | Accepts data to store but always returns empty. |
| FEDERATED | Stores data in a remote database. |
46) How will you get current date in MySQL?
mysql> SELECT CURRENT_DATE();
+—————-+
| CURRENT_DATE() |
+—————-+
| 2016-08-06 |
+—————-+
1 row in set (0.00 sec)
47) What does –i-am-a-dummy flag to do when starting MySQL?
Makes the MySQL engine refuse UPDATE and DELETE commands where the WHERE clause is not present.
48) What do DDL, DML, and DCL stand for?
DDL is the abbreviation for Data Definition Language dealing with database schemas as well as the description of how data resides in the database. An example is CREATE TABLE command. DML denotes Data Manipulation Language such as SELECT, INSERT etc. DCL stands for Data Control Language and includes commands like GRANT, REVOKE etc
49) What is meant by transaction and ACID properties?
Transaction is logical unit of work where either all or none of the steps should be performed. ACID is the abbreviation for Atomicity, Consistency, Isolation, and Durability that are properties of any transaction
50) How many types of logs are there in mysql?
General Log, Error Log, Binary Log and Slow Query Log
51) Can the database be renamed in MySQL?
No.