49 Top MySQL Interview Questions With Answers

49 Top MySQL Interview Questions With Answers

Today we’re having a huge collection of MySQL interview questions with answers to help both the freshers and experienced people searching for job. These questions are literally important regarding interview process. You might be asked one of the in the future or similar to one of them. Go through them and submit your own questions asked in the interview.

Top 49 MySQL Interview Questions With Answers

Following are the MySQL interview questions with answers:

What Is MySQL?

MySQL is a freely available open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). SQL is the most popular language for adding, accessing, and managing content in a database. It is most noted for its quick processing, proven reliability, ease, and flexibility of use. (Source)

Why MySQL Is Used?

Some of the top reasons are given below.

  • Scalability and Flexibility
  • High Performance
  • High Availability
  • Web and Data Warehouse Strengths
  • Robust Transactional Support
  • Comprehensive Application Development
  • Strong Data Protection
  • Management Ease
  • Lowest Total Cost of Ownership
  • Open Source Freedom and 24 x 7 Support

What Are The Technical Features Of MySQL?

You can have a complete list of technical features of MySQL from here.

What is the default port for MySQL Server?

The default port for the MySQL server is 3306.

What are Heap Tables?

Tables that are present in the memory are called HEAP tables. When creating a HEAP table in MySql, user needs to specify the TYPE as HEAP. These tables are now more commonly known as memory tables. These memory tables never have values with data types like “BLOB” or “TEXT.” They use indexes which make them faster.

What is the Difference Between Float And Double?

They both represent floating point numbers. A FLOAT is for single-precision, while a DOUBLE is for double-precision numbers.

MySQL uses four bytes for single-precision values and eight bytes for double-precision values. (Source)

CHAR_LENGTH And LENGTH?

CHAR_LENGTH, as the name suggests, returns the number of characters/character count. The LENGTH returns the number of bytes/bytes count. To count the Latin characters, both lengths are the same. To count Unicode and other encodings, the lengths are different.

What are the advantages of MySQL over Oracle? 

  • Scalability
  • High Availability
  • DR capabilities
  • Performance
  • Performance tuning capabilities

What Is BLOB?

A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.

How Many Triggers Are Allowed In MySQL Table?

Six triggers are allowed in the Mysql table:

  • BEFORE INSERT
  • AFTER INSERT
  • BEFORE UPDATE
  • AFTER UPDATE
  • BEFORE DELETE
  • AFTER DELETE

What is TEXT?

It’s a case-insensitive BLOB. There are four types of TEXT:

  • TINYTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

What is the Difference Between BLOB And Text?

BLOB is used for storing binary data, while Text is used to store large strings.

How does a user see MySQL timestamps?

In a readable format: YYYY-MM-DD HH:MM:SS.

How would you get the current date in Mysql?

By using the command SELECT CURRENT_DATE();

How can one concatenate strings in MySQL?

By using CONCAT (string1, string2, string3)

How do you control the max size of a HEAP table?

Using MySQL config variable called max_heap_table_size.

How would you enter Characters as HEX Numbers?

Enter HEX numbers with single quotes and a prefix of (X).

You need to show all the indexes defined in a table, say ‘user’ of the database, say ‘Mysql.’ How will you achieve this?

Using mysql> show index from user.

How will you export tables as an XML file in MySQL?

Using command mysql -u USER_NAME –xml -e ‘SELECT * FROM table_name’ > table_name.xml

How will you get the current date in MySQL?

Using mysql> SELECT CURRENT_DATE();

You want to see only certain rows from a result set from the beginning or end of a result set. How will you do it?

Using Command mysql> SELECT * FROM name LIMIT 1; (To Show 1 Record) (Source)

Login In MySQL With Unix Shell?

# [mysql dir]/bin/mysql -h hostname -u root -p pass

Create a database on the MySQL server with unix shell.

mysql> create database databasename;

How will you list or view all databases from the MySQL server?

mysql> show databases;

How to use a database?

mysql> use databasename;

See all the tables from a database of MySQL server.

mysql> show tables;

How to see the table’s field formats or description of the table.

mysql> describe tablename;

Delete a database from the MySQL server.

mysql> drop database databasename;

How will you get the Sum of column?

mysql> SELECT SUM(*) FROM [table “” not found /]
;

How to delete a table?

mysql> drop table tablename;

Show all data from a table.

mysql> SELECT * FROM tablename;

How to return the columns and column information pertaining to the designated table?

mysql> show columns from tablename;

Show certain selected rows with the value “xyz.”

mysql> SELECT * FROM tablename WHERE fieldname = “xyz”;

How will you show unique records?

mysql> SELECT DISTINCT columnname FROM tablename;

Show selected records sorted in ascending or descending order?

mysql> SELECT col1,col2 FROM tablename ORDER BY col2 DESC;
mysql> SELECT col1,col2 FROM tablename ORDER BY col2 ASC;

How to Return total number of rows?

mysql> SELECT COUNT(*) FROM tablename;

How to Change a user’s password from unix shell?

[mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

How to Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set a new password. Exit MySQL and restart the MySQL server.

/etc/init.d/mysql stop 

# mysqld_safe –skip-grant-tables &

# mysql -u root

mysql> use MySQL;

mysql> update user set password=PASSWORD(“newrootpassword”) where User=’root’;

mysql> flush privileges;

mysql> quit

# /etc/init.d/mysql stop

# /etc/init.d/mysql start (Source)

Restore the database from backup.

[mysql dir]/bin/mysql -u username -password databasename < /tmp/databasename.sql.

How to dump a table from a database?

[mysql dir]/bin/mysqldump -c -u username -password databasename tablename > /tmp/databasename.tablename.sql

How to dump one database for backup?

[mysql dir]/bin/mysqldump -u username -password –databases databasename >/tmp/databasename.sql

How to dump all databases for backup. Is backup file sql commands to recreate all DB’s?

[mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

How to Load a CSV file into a table?

mysql> LOAD DATA INFILE’/tmp/name.csv’ replace INTO TABLE [table “” not found /]
FIELDS TERMINATED BY, ” LINES TERMINATED BY ‘n’ (field1,field2,field3);

How to Update database permissions?

mysql> flush privileges;

List the objects that can be created using CREATE statement?

The below objects can be created using CREATE statement:

  • DATABASE
  • EVENT
  • FUNCTION
  • INDEX
  • PROCEDURE
  • TABLE
  • TRIGGER
  • USER
  • VIEW

How many columns can be used for creating Index?

A maximum of 16 indexed columns can be created for any standard table.

What is InnoDB?

InnoDB is a storage engine for MySQL. MySQL 5.5 and later use it by default. It provides standard ACID-compliant transaction features and foreign key support (Declarative Referential Integrity). (Source: Wikipedia)

What are the drivers in MySQL?

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

What is SQLyog?

The SQLyog program is Software that provides a GUI tool for MySQL.

FAQs

What is the full form of MySQL?

The full form of MySQL is 'Structured Query Language.' MySQL is a freely available open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL).

What are the 4 types of MySQL?

The four types of MySQL are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.

Why Is MySQL Used?

Some of the top reasons are given below. Scalability and Flexibility High Performance High Availability Web and Data Warehouse Strengths Robust Transactional Support Comprehensive Application Development Strong Data Protection Management Ease Lowest Total Cost of Ownership Open Source Freedom and 24 x 7 Support

Final Takeaway

So this was all about MySQL interview questions and answers. If you’ve faced any interviews, submit your questions below in the comment section. For any doubt or query, feel free to leave a comment.

Scroll to Top