Register Login

MySQL Interview Questions and Answers

Updated Apr 11, 2019

What is MySQL?

MySQL is an open source relational database management system that is based on SQL. It is very popular among users as it runs easily on multiple platforms like Windows, UNIX, and LINUX.

It is widely used for developing web applications and for online publishing. It works on a client-server model and is a part of the LAMP enterprise stack.

How to reset the MySQL root password?

The root password of MySQL can be set using the following steps:

  • Log in as Administrator.
  • Stop the MySQL Server from the Administrative tools in the Control Panel.
  • Make a text file and put an SQL statement to reset the root password. Replace the existing password with the new password.
  • Save the file and start MySQL again from the command line.
  • MySQL has to be started by using the --init-file option, where the contents of this file will be the address of the altered password file.
  • After the Server is running, delete the altered password file.

How to check if MySQL is running?

The following commands can be executed to check whether MySQL is running or not:

  • C:> "C:Program FilesMySQLMySQL Server 5.7binmysql" test
  • C:> "C:Program FilesMySQLMySQL Server 5.7binmysqlshow" -u root MySQL

In case the root password has been reset or a new user has been created, the appropriate -u and –p options have to be used along with the commands provide above.

Difference between Having and Where clause in MySQL?

The differences between Where and Having a clause in MySQL are:

  • Where is used for fetching the required rows from a table based on a condition, but Having is used to filter grouped
  • Where is used before the Group By clause but the Having clause is used after the Group By command.

How to open MySQL command line?   

The MySQL command line can be opened through the following command:

To start the mysqld in the command line type C:> "C:Program FilesMySQLMySQL Server 8.0binmysqld"

Here, the path of the MySQL can vary according to the file location of the system.

What port does MySQL use?   

The TCP/IP port that runs MySQL is by default port number 3306.

What is InnoDB in MySQL?

InnoDB in MySQL is the default storage engine it uses. It provides ACID compliant tables, supports foreign keys, rollback, commit and crash recovery in a case the database crashes and loses its data.

The engine helps in the reduction of the number of I/O queries that are executed using primary keys. It maintains the performance of the MySQL database.

What is varchar in MySQL?     

The varchar is a data type in MySQL that does not have a fixed length. They are variable length strings that can be assigned a value for 0 to 65,535.

Varchar values are stored in MySQL as a 1 byte or 2-byte length prefix along with the actual data. This length prefix defines the number of bytes within the value.

What is a blob in MySQL?

The BLOB (Binary Large Object) in MySQL is capable of storing different amounts of data. They are of different types namely TINYBLOB, MEDIUMBLOB, LONGBLOB, and BLOB.

These types are similar but different in the amount of data they can store. They have 4 text types namely, TINYTEXT, MEDIUMTEXT, LONGTEXT, and TEXT.

What is MySQL pid file?

The pid file contains the pathname of the file where the server writes its Process ID. This file is created in the data directory by the server unless a different path name to another directory is provided.

What is collation in MySQL?

In MySQL, a collation is a sequence order in a specific character set. It is basically a set of rules that is used to sort and compare character strings. Every collation is associated with a character set.

What is MySQL delimiter?

The delimiter in MySQL is used to define functions, stored procedures and make triggers. The semicolon is the default delimiter. This is helpful for the MySQL client to understand where the procedures and functions end and the procedures can be executed as a unit.

You can alter the delimiters to develop new procedures. In case of multiple statements different delimiters like // and $$.

What is an index in MySQL?

Index in MySQL is a data structure that is used to enhance the speed of the operations in a table. It is actually a kind of table where there exists a primary key and a pointer to each record in the original table.

They can be created using one or more columns for increasing the efficiency of accessing the records and making lookups easier.

What is natural join in MySQL?

A natural join is used to carry out the same tasks that are performed through Inner Join and Left Join, where the Using and On clauses are used.

In Natural Join, columns with the similar names of the associated tables will be shown only once in the query results. But the associate tables need to have columns with the same name.

What is unsigned in MySQL?

The unsigned data type is used to provide only non-negative numbers for a column but no negative numbers. It can be applied when a large numeric range is required in a column.

All the integers can have the unsigned data type as an optional attribute. For example, if a column that is INT, and is made Unsigned, the size of its range remains the same but the endpoints alter from 0 and 4294967295.

What are prepared statements in MySQL?

A prepared statement is used to enhance the speed and efficiency of SQL statements by executing the same statements repeatedly. It makes use of the client-server binary protocol.

Three SQL statements are required to be executed along with the prepared statement:

  • Prepare – This prepares the statement before running it.
  • Execute – The statement is executed by this command.
  • Deallocate Prepare – This releases the statement.

What is auto_increment in MySQL?

The AUTO_INCREMENT keyword is used to perform auto increment in MySQL. This works on the numeric data types that get incremented each time a new record is added to the database table.

Its starting value is 1 and it will get increased by 1 every time a record is inserted. This feature is very helpful for ensuring the uniqueness of the primary key. An unsigned constraint is placed along with the auto-incrementing primary key.

What are flush privileges in MySQL?

If the GRANT tables are modified directly through the statements like Insert, Delete, and Update, the changes will have no effect if the server is not instructed to restart or reload all the tables. In this case, the flush privileges are used.

This can be done by executing a mysqladmin flush privileges statement.

How to delete a column in MySQL?

The columns can be deleted in MySQL using the DROP statement. The following steps will be used:

  • After the Alter Table clause, mention the table where the column that has to be deleted exists.
  • After the Drop Column clause, provide the name of that column.

What is MySQL workbench?

The MySQL Workbench is a tool that is used for designing physical data models and altering the databases through advanced techniques like reverse engineering. It supports all objects like views, stored procedures, tables that are a part of any database.