MySQL Interview Questions & Answers

MySQL is an open source, multithreaded, multi-user SQL database management system. It is relational database management system .MySQL is the world’s most popular and widely used database system. It is very friendly to PHP web development applications.

MySQL is a free,veryfast  and easy to use. If that is what you are looking for, you should give it a try. MySQL also has a very practical set of features developed in very close cooperation with our users. You can find a performance comparison of MySQL to some other database managers on our benchmark page. See section 12.7 using Your Own Benchmarks. MySQL was originally developed to handle very large databases much faster than existing solutions and has been successfully used in highly demanding production environments for several years. Though under constant development, MySQL today offers a rich and very useful set of functions. The connectivity, speed, and security make MySQL highly suited for accessing databases on the Internet.

• It is Cross platform and Unicode support
• ACID
• Embedded database library
• Triggers and Cursors and updated views
• Online DDL
• SSL
• Multiple storage engines
• Multithreaded SQL server supporting various client programs and libraries
• Different backend
• Wide range of application programming interfaces and
• Administrative tools
• Native storage engines InnoDB, NyISAM, Memory (heap),CSV,NDB cluster, etc.

MySQL has the following technical specifications:

• Manageable and easy to use
• High performance
• Flexible structure and easy to modify
• Supports many operating systems and many programming languages like C/C++,Java, PHP, Ruby,etc.
• Replication and high availability
• Full Security and multiple storage management

• MySQL is freely available and open source software which is available at any time and has no cost involved.
• MySQL is portable
• It is focus on the web, cloud and Big data.
• There is variety of user interface that can be implemented
• GUI with command prompt.
• Administration is supported using MySQL Query Browser

• MySQL is not so efficient for large scale databases.
• It is not support XML and OLAP
• It is not as mature as other relational database management systems.
• It does not support COMMIT and STORED PROCEDURES functions version less than 5.0.
• Poor support for user defined functions and stored procedures
• Transactions are not handled very efficiently.

• Compound statements (Begin/end)
• Variable declarations (Declare) and assignment (Set)
• Condition declarations
• Handler declarations
• Flow control statements (if case, whilw, loop, repeat, leave, iterate)

There are only six Triggers allowed to use in MySQL database.

• Before Update
• After Update
• Before Insert
• After Insert
• Before Delete
• After Delete

InnoDB is a transaction safe storage engine developed by Inno Oy, now Oracle Corporation.

A REGEXP pattern match succeed if the pattern matches anymore in the value being tested.

A defined point in any transaction is known as savepoint. SAVEPOINT is a statement in MySQL which is used to set a named transaction save point with a name of identifier.

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.

MyISAM and InnoDB are two popular storage engines in MySQL. The key differences include:

MyISAM: It is the default storage engine in older versions of MySQL. It supports full-text searching and is suitable for read-intensive workloads. However, it lacks transactional support, foreign key constraints, and crash recovery mechanisms.

InnoDB: It is the default storage engine in recent versions of MySQL. It provides support for transactions, ACID (Atomicity, Consistency, Isolation, Durability) properties, and foreign key constraints. InnoDB is suitable for applications that require data integrity, concurrency control, and crash recovery.

UNION and UNION ALL are used to combine the result sets of two or more SELECT statements in MySQL. The main difference is:

UNION: It combines the result sets of SELECT statements, removing duplicate rows. It performs a distinct operation.

UNION ALL: It combines the result sets of SELECT statements, including all rows without removing duplicates. It does not perform a distinct operation, resulting in faster performance compared to UNION.

Indexing in MySQL improves query performance by creating data structures that allow for efficient data retrieval. Here’s how it works:

• An index is created on one or more columns of a table.
• The index stores a sorted copy of the indexed column(s) along with a pointer to the actual row.
• When a query is executed, MySQL can use the index to quickly locate the rows that match the query conditions, reducing the need for full table scans.
• Indexes are crucial for optimizing the performance of SELECT, JOIN, and WHERE clauses.

There are several techniques to optimize query performance in MySQL, including:

• Properly indexing tables to speed up data retrieval.
• Using EXPLAIN to analyze query execution plans and identify bottlenecks.
• Avoiding unnecessary columns in SELECT queries.
• Limiting the use of functions or calculations in WHERE clauses.
• Optimizing database schema design, including table normalization and denormalization.
• Caching query results or frequently accessed data using tools like Memcached or Redis.
• Tuning MySQL configuration parameters, such as buffer sizes and query cache settings, based on workload characteristics.

Data integrity ensures the accuracy, consistency, and reliability of data stored in a database. In MySQL, you can enforce data integrity by:

• Using primary key and unique constraints to ensure uniqueness of values in columns.
• Applying foreign key constraints to maintain referential integrity between related tables.
• Defining appropriate data types and column constraints to restrict the values that can be stored.
• Utilizing transactions to ensure atomicity and consistency during data modifications.
• Implementing triggers to enforce complex business rules or perform data validation.

It compressed the MyISAM tables, which reduces their disk usage.

Byusing,net start MySQL, net stop MySQL

By using /etc/init.d/mysql star

To login, we can make use of this command:

#[mysql dir]/mysql –h hostname –u<UserName> -p<password>

In MySQL,To take the database backup use the following syntax:

mysqldump –add-drop-table -u [username] -p[password] [database] > [backup_file]

A trigger is a named database object that is associated with the table, and it activates when a particular event occurs for the table. In MySQL, Create triggers statement used increate anew trigger.

Example of Syntax:

CRETE

[DEFINER={user| CURRENT_USER}]

TRIGGER trigger_name

trigger_time trigger_event

ON tbl_name FOR EACH ROW

trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

BLOB stands for binary large object. It is used to hold a variable amount of data.

There are four types of BLOB.

1. TINYBLOB
2. BLOB
3. MEDIUMBLOB
4. LONGBLOB

The differences among all these are the maximum length of values they can hold.

TEXT is case-insensitive BLOB. TEXT values are non-binary strings (character string). They have a character set and values are stored and compared based on the collation of the character set.

There are four types of TEXT.

1. TINYTEXT
2. TEXT
3. MEDIUMTEXT
4. LONGTEXT

MySQL connect:

• It opens a new connection to the database.
• Every time you need to open and close database connection, depending on the request.
• Opens page every time when it loaded.

MySQL_pconnect:

• In MySQL_pconnect, “p” stands for persistent connection so it opens the persistent connection.
• the database connection cannot be closed.
• it is more useful if your site has more traffic because there is no need to open and close connection frequently and every time when page is loaded.

Floating point numbers are stored in FLOAT with eight place accuracy and it has four bytes.

Floating point numbers are stored in DOUBLE with accuracy of 18 places and it has eight bytes.

CHAR_LENGTH is character count whereas the LENGTH is byte count. The numbers are same for Latin characters but they are different for Unicode and other encodings.

Every row of a table is identified uniquely by primary key. There is only one primary key for a table.

Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.

In MyISAM static all the fields will have fixed width. The Dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate the data types with various lengths.

MyISAM Static would be easier to restore in case of corruption.

• Install antivirus and configure the operating system’s firewall.
• Never use the MySQL Server as the UNIX root user.
• Change root username and password
• Restrict or disable remote access.

Following are the differences between CHAR and VARCHAR:

• CHAR and VARCHAR types differ in storage and retrieval
• CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255
• When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.

Storage engines are called table types and data is stored in files using various techniques.

Technique involves:

• Storage mechanism
• Locking levels
• Indexing
• Capabilities and functions

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

MySQL comes up with some default database that can be used as a base to create a new one. The command that is used to create a new database is as follows:

CREATE DATABASE SQL command

The command has to be written in MySQL terminal. This command will create a new database and then you can create new tables and include data in it.

We can login through this command:

# [mysql dir]/bin/mysql -h hostname -u <UserName> -p <password>

MySQL support 3 groups of data types as listed below:

String Data Types: Char, Nchar, Varchar, Nvarchar, Binary, Varbinary, Tinyblob, Tinytext, Blob, Text, Mediumblob, Mediumtext, Longblob, Longtext, Enum, Set.

Numeric Data Types:Bit, Tinyint, Boolean, Smallint, Mediumint, Integer, Bigint, Float, Double, Real, Decimal.

Date and Time Data Types: Date, Datetime, Timestamp, Time, Year.

-> prompt in the command of MySQL indicates that a single statement is being entered across multiple lines. From this prompt MySQL interprets that you haven’t finished entering the statements. It has no impact of enter which you might press to go to the next line. MySQL will execute the statement only when you will insert the semicolon in the end which it recognizes.

Each MyISAM table is stored on disk in three formats:

• The ‘.frm’ file stores the table definition
• The data file has a ‘.MYD’ (MYData) extension
• The index file has a ‘.MYI’ (MYIndex) extension

Following are the differences between mysql_fetch_array and mysql_fetch_object:

mysql_fetch_array() -Returns a result row as an associated array or a regular array from database.

mysql_fetch_object –  Returns a result row as object from database.

If you have an existing view, and you don’t want it anymore, you can delete it by using the “DROP VIEW viewName” statement.

You can create a new view based on one or more existing tables by using the

“CREATE VIEW viewName AS selectStatement” .

Total 5 types of tables are present:

• MyISAM
• Heap
• Merge
• INNO DB
• ISAM

Following objects are created using CREATE statement:

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

A defined point in any transaction is known as savepoint.

SAVEPOINT is a statement in MySQL which is used to set a named transaction save point with a name of identifier.

Port Number is 3306.

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.

Rollback is a way to terminate a transaction with all database changes not saving to the database server.

MyISAM : This is default. Based on Indexed Sequntial Access Method. The above SQL will create a MyISA table.

ISAM : same

HEAP : Fast data access, but will loose data if there is a crash. Cannot have BLOB, TEXT & AUTO INCRIMENT fields

BDB : Supports Transactions using COMMIT & ROLLBACK. Slower that others.

InoDB : same as BDB

It is easy to backing up data with phpMyadmin. Select the database you want to backup by clicking the database name in the left hand navigation bar. Then click the export button and make sure that all tables are highlighted that you want to backup. Then specify the option you want under export and save the output.

This is the basic MySQL Interview Questions asked in an interview. Find below the different types of functions used in MySQL.

MySQL String Functions: INSERT, CONCAT, FORMAT, INSERT, LENGTH, LEFT, FIELD, LTRIM, TRIM, UPPER etc.

MySQL Date Functions: ADDDATE, ADDTIME, DATE, MONTH, MINUTE etc.

MySQL Numeric Functions: AVG, DIV, EXP, MAX, MIN, POWER, TRUNCATE etc

MySQL Advanced Functions: BIN, CASE, CAST, DATABASE, IF, ISNULL, VERSION etc.