MySQL Interview Questions
What is MySQL?
MySQL is an open-source relational database management system (RDBMS) based on SQL (Structured Query Language). It is widely used for managing and organizing data in web applications. MySQL supports various platforms and is known for its reliability, performance, and ease of use.
What are the different types of MySQL storage engines?
MySQL supports various storage engines, each with its own characteristics and features. Here are some of the most commonly used MySQL storage engines:
- InnoDB: The default and most widely used storage engine in MySQL. It supports transactions, foreign keys, and row-level locking, making it suitable for general-purpose applications.
- MyISAM: A historical storage engine that provides high performance for read-intensive operations. It doesn't support transactions or foreign keys but offers full-text search capabilities and table-level locking.
- MEMORY (HEAP): Stores data in memory, offering extremely fast read and write operations. However, it does not provide data durability as data is lost when the server restarts.
- CSV: Stores data in comma-separated values (CSV) format, making it useful for exporting and importing data between MySQL and other applications.
- Archive: Designed for storing and retrieving large volumes of data efficiently. It achieves high compression ratios and supports INSERT and SELECT operations but does not support indexes or transactions.
- NDB (MySQL Cluster): A high-availability storage engine designed for MySQL Cluster. It provides data distribution and replication across multiple nodes for scalability and fault tolerance.
- Merge: Allows combining multiple MyISAM tables that have identical structure into a single virtual table for query optimization and data management.
- Blackhole: Accepts but does not store data, making it useful for testing or replication scenarios where data is not needed on the target server.
- Federated: Allows accessing data from a remote MySQL server as if it were a local table, enabling distributed database architectures.
- TokuDB: A storage engine known for its high-performance data compression and scalability, especially for write-heavy workloads.
What are ACID properties in MySQL?
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable transaction processing: Atomicity guarantees all or none of a transaction is completed, Consistency ensures data integrity, Isolation prevents concurrent transaction conflicts, and Durability ensures data persistence after transactions.
What is a primary key in MySQL?
A primary key is a unique identifier for each record in a MySQL table. It must contain unique values and cannot contain NULL values. The primary key ensures that each row in the table is uniquely identifiable, aiding in efficient data retrieval and organization.
What is a foreign key in MySQL?
A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. It establishes a relationship between two tables, enforcing referential integrity by ensuring that the value in the foreign key column corresponds to a value in the primary key column of the related table.
How do you create a database in MySQL?
To create a database in MySQL, you use the 'CREATE DATABASE' statement. For example: 'CREATE DATABASE mydatabase;'. This command creates a new database named "mydatabase" in the MySQL server, which can then be used to create tables and store data.
CREATE DATABASE mydatabase;
How do you create a table in MySQL?
To create a table in MySQL, you use the CREATE TABLE statement followed by the table name and a list of column definitions.
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
PRIMARY KEY (column_name) -- Optional primary key constraint
);
What is normalization in MySQL?
Normalization is the process of organizing data to minimize redundancy and improve data integrity. It involves dividing large tables into smaller, related tables and defining relationships between them. Normalization typically involves multiple steps, called normal forms, each with specific rules.
What is denormalization in MySQL?
Denormalization is the process of combining normalized tables to improve read performance at the expense of write performance and data integrity. It involves adding redundant data to one or more tables, reducing the need for complex joins but potentially increasing storage requirements.
How do you insert data into a MySQL table?
To insert data into a MySQL table, you use the INSERT INTO statement followed by the table name and the values you want to insert.
INSERT INTO users (username, email) VALUES ('Baibhav_Kr', '[email protected]');
How do you update data in a MySQL table?
To update data in a MySQL table, you use the UPDATE statement along with the table name and the new values you want to set.
UPDATE users SET email = '[email protected]' WHERE username = 'baibhav_kr';
How do you delete data from a MySQL table?
To delete data from a MySQL table, you use the DELETE FROM statement along with an optional WHERE clause to specify which rows to delete.
DELETE FROM users WHERE username = 'baibhav_kr';
What is a MySQL JOIN?
A JOIN in MySQL is a query that combines rows from two or more tables based on a related column. Types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. Joins are used to retrieve related data from multiple tables efficiently.
What is an INNER JOIN in MySQL?
An INNER JOIN returns rows when there is at least one match in both tables being joined. It only includes records that have matching values in both tables. For
SELECT users.username, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;
What is a LEFT JOIN in MySQL?
A LEFT JOIN returns all rows from the left table and matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.
SELECT users.username, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
What is a RIGHT JOIN in MySQL?
A RIGHT JOIN returns all rows from the right table and matched rows from the left table. If no match is found, NULL values are returned for columns from the left table.
SELECT users.username, orders.order_id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
What is a FULL JOIN in MySQL?
A FULL JOIN returns all rows when there is a match in either left or right table. It combines the results of both LEFT and RIGHT JOINs. MySQL does not directly support FULL JOIN, but it can be emulated using a UNION of LEFT JOIN and RIGHT JOIN.
What is a MySQL VIEW?
A VIEW in MySQL is a virtual table based on the result set of a SELECT query. It allows you to simplify complex queries, improve security by restricting data access, and present data in a specific format. Views do not store data themselves but provide a way to query data.
How do you create a VIEW in MySQL?
To create a view in MySQL, you use the CREATE VIEW statement followed by the view name and the SELECT query that defines the view.
CREATE VIEW user_orders AS
SELECT users.username, orders.order_id
FROM users
JOIN orders ON users.id = orders.user_id;
How do you delete a VIEW in MySQL?
To delete a view in MySQL, you use the DROP VIEW statement followed by the name of the view you want to delete.
DROP VIEW user_orders;
What is an INDEX in MySQL?
An INDEX in MySQL is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage and slower writes. Indexes are created on columns to enhance search performance and are essential for optimizing query performance.
CREATE INDEX idx_username ON users (username);
How do you create an INDEX in MySQL?
You create an INDEX using the 'CREATE INDEX' statement.
CREATE INDEX idx_username ON users (username);
How do you delete an INDEX in MySQL?
To delete an index in MySQL, you use the DROP INDEX statement followed by the name of the index and the table from which you want to remove the index.
DROP INDEX idx_username ON users;
What is a UNIQUE INDEX in MySQL?
A UNIQUE INDEX ensures that all values in the indexed column are distinct. It enforces uniqueness for the column, preventing duplicate values. You create it using:
CREATE UNIQUE INDEX idx_email ON users (email);
What is a PRIMARY KEY INDEX in MySQL?
A PRIMARY KEY INDEX is a special type of UNIQUE INDEX that uniquely identifies each row in a table. It enforces entity integrity by ensuring that no two rows have the same primary key value. It is created automatically when you define a primary key.
What is a composite key in MySQL?
A composite key consists of two or more columns used together as a unique identifier for a table's records. It is useful when a single column is not sufficient to ensure uniqueness.
PRIMARY KEY (order_id, product_id);
What is the difference between CHAR and VARCHAR in MySQL?
In MySQL, CHAR and VARCHAR are used to store string data, but they have different characteristics and use cases.
| Feature | CHAR | VARCHAR |
|---|---|---|
| Definition | Fixed-length character string. | Variable-length character string. |
| Storage | Allocates fixed space based on the defined length. | Allocates space based on the actual data length. |
| Padding | Pads with spaces to the defined length. | Does not pad; stores exactly the given data length. |
| Performance | Faster for fixed-size data due to fixed-length storage. | Can be slower for varying lengths due to dynamic storage. |
| Memory Usage | Wastes space if the stored data is shorter than the defined length. | More efficient as it uses only required space. |
| Use Case | Best for storing data with a known fixed length (e.g., country codes, fixed-length IDs). | Best for storing data with varying lengths (e.g., names, addresses). |
| Maximum Length | Up to 255 characters. | Up to 65,535 characters, depending on the row size and character set. |
| Example | CHAR(10) always uses 10 bytes regardless of the string length. | VARCHAR(10) uses 1-10 bytes plus 1-2 bytes for length information. |
What is a MySQL transaction?
A transaction in MySQL is a sequence of one or more SQL operations executed as a single unit. It ensures data consistency and integrity by allowing operations to be rolled back if an error occurs. Transactions use commands like 'BEGIN', 'COMMIT', and 'ROLLBACK'.
How do you start and end a transaction in MySQL?
To start and end a transaction in MySQL, you use the BEGIN, COMMIT, and ROLLBACK statements.
- Start a Transaction: You begin a transaction by using the BEGIN statement. This indicates the start of a series of SQL statements that should be treated as a single unit of work.
BEGIN;
- Commit a Transaction: If you want to make the changes permanent and save them to the database, you use the COMMIT statement. This commits all the changes made within the transaction to the database.
COMMIT;
- Rollback a Transaction: If, for any reason, you want to discard the changes made within the transaction and revert the database to its previous state, you use the ROLLBACK statement.
ROLLBACK;
Simple example of a transaction:
BEGIN; -- Start the transaction
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT; -- Commit the transaction
What is MySQL replication?
MySQL replication involves copying data from one MySQL server (master) to another (slave). It is used for load balancing, backup, and data distribution. Replication can be configured in various modes, such as asynchronous, semi-synchronous, and synchronous.
What are the different types of MySQL replication?
The main types of MySQL replication are asynchronous, semi-synchronous, and synchronous. Asynchronous replication does not require the slave to acknowledge receipt of data. Semi-synchronous requires acknowledgment from at least one slave, and synchronous ensures all slaves have the data before committing.
CHANGE MASTER TO MASTER_HOST='slave_host', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
START SLAVE;
How do you backup a MySQL database?
You can backup a MySQL database using the 'mysqldump' command-line utility.
mysqldump -u username -p database_name > backup.sql
How do you restore a MySQL database?
To restore a MySQL database, use the 'mysql' command-line utility with the backup file.
mysql -u username -p database_name < backup.sql
What is MySQL Workbench?
MySQL Workbench is a graphical tool for database architects, developers, and DBAs. It provides data modeling, SQL development, server administration, backup, and other tools to manage MySQL databases efficiently. It offers an intuitive interface and advanced features for database design and management.
What is the difference between DELETE and TRUNCATE in MySQL?
In MySQL, both DELETE and TRUNCATE commands are used to remove records from a table, but they differ significantly in terms of their operation, performance, and usage.
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Operation | Removes rows one at a time based on a condition. | Removes all rows from a table without logging individual row deletions. |
| Syntax | DELETE FROM table_name WHERE condition; | TRUNCATE TABLE table_name; |
| Condition | Can specify a condition to delete specific rows. | Cannot specify a condition; always removes all rows. |
| Performance | Slower for large tables due to row-by-row deletion. | Faster for large tables as it deallocates entire data pages. |
| Logging | Fully logged, which means it writes an entry for each deleted row. | Minimal logging, only the page deallocation is logged. |
| Rollback | Can be rolled back if used within a transaction. | Cannot be rolled back as it commits implicitly. |
| Triggers | Invokes triggers if they are defined on the table. | Does not invoke triggers. |
| AUTO_INCREMENT Reset | Does not reset the AUTO_INCREMENT counter. | Resets the AUTO_INCREMENT counter to zero. |
| Foreign Key Constraints | Checks foreign key constraints and can fail if constraints are violated. | Checks foreign key constraints and can fail if constraints are violated. |
| Usage | Used when you need to delete specific rows or when you need to log deletions. | Used when you need to quickly remove all rows from a table. |
What are MySQL stored procedures?
Stored procedures are precompiled SQL code that you can save and reuse. They encapsulate complex SQL statements for data manipulation, improving code reusability, security, and performance. Procedures are executed with the 'CALL' statement.
How do you create a stored procedure in MySQL?
You create a stored procedure using the 'CREATE PROCEDURE' statement.
CREATE PROCEDURE GetUser(IN userId INT)
BEGIN
SELECT * FROM users WHERE id = userId;
END;
What are MySQL triggers?
Triggers are database objects that automatically execute specified SQL code in response to certain events on a table, such as INSERT, UPDATE, or DELETE. They enforce business rules and data integrity without requiring manual intervention.
How do you create a trigger in MySQL?
To create a trigger in MySQL, you use the CREATE TRIGGER statement followed by the trigger name, trigger timing (BEFORE or AFTER), trigger event (INSERT, UPDATE, DELETE), and the SQL statements to be executed when the trigger is fired.
CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name
FOR EACH ROW
BEGIN
-- SQL statements
END;
What are MySQL user-defined functions (UDFs)?
User-defined functions are custom functions created to perform specific operations that are not available through built-in MySQL functions. UDFs are written in a programming language like C or C++ and registered with MySQL.
What is the difference between stored procedures and functions in MySQL?
In MySQL, both stored procedures and functions are used to encapsulate SQL statements for reuse and improved database management. However, they serve different purposes and have distinct characteristics.
| Feature | Stored Procedures | Functions |
|---|---|---|
| Purpose | Encapsulate a series of SQL statements for complex operations. | Perform a specific calculation or task and return a single value. |
| Return Value | Can return zero or more values using OUT parameters. | Must return a single value. |
| Syntax to Call | Called using the CALL statement. | Called as part of an SQL expression. |
| Use Case | Suitable for operations like data modification, business logic. | Ideal for computations, transformations, and returning scalar values. |
| Modifies Database State | Can perform transactions and modify database state. | Cannot modify the database state, only read data. |
| Parameter Types | Supports IN, OUT, and INOUT parameters. | Only supports IN parameters. |
| Allowed in SQL Statements | Cannot be called directly in SELECT, WHERE, or other SQL statements. | Can be called directly within SQL statements like SELECT, WHERE. |
| Transaction Control | Can include transaction control statements (COMMIT, ROLLBACK). | Cannot include transaction control statements. |
| Example Usage | Complex business logic, batch processing. | Mathematical calculations, string manipulations. |
How do you optimize a MySQL query?
To optimize a MySQL query, you can use indexing, avoid SELECT *, write efficient WHERE clauses, use LIMIT for large datasets, and analyze execution plans with 'EXPLAIN'. Proper normalization and avoiding unnecessary joins also help improve query performance.
What is the 'EXPLAIN' statement in MySQL?
The 'EXPLAIN' statement provides information about how MySQL executes a query, including details on table access methods, join operations, and index usage. It helps identify performance bottlenecks and optimize queries.
EXPLAIN SELECT * FROM users WHERE id = 1;
What is MySQL clustering?
MySQL clustering is a method of distributing a MySQL database across multiple nodes to ensure high availability, fault tolerance, and scalability. The MySQL NDB Cluster is a popular clustering solution that provides real-time access to data and automatic failover.
What is MySQL partitioning?
Partitioning divides a table into smaller, more manageable pieces, called partitions, while keeping them as a single logical table. It improves query performance and maintenance for large datasets. MySQL supports various partitioning types, including range, list, hash, and key.
How do you enable slow query logging in MySQL?
Slow query logging helps identify queries that take a long time to execute. Enable it by setting 'slow_query_log' to 'ON' in the MySQL configuration file ('my.cnf') and specifying the 'slow_query_log_file' path. You can also set the 'long_query_time' to define the threshold.
What are MySQL data types?
MySQL supports various data types categorized into numeric (INT, FLOAT, DECIMAL), string (VARCHAR, CHAR, TEXT), date and time (DATE, DATETIME, TIMESTAMP), and binary (BLOB, BINARY). Choosing the appropriate data type is crucial for data integrity and performance.
How do you handle NULL values in MySQL?
NULL values represent missing or unknown data. You handle them using SQL functions like 'IS NULL', 'IS NOT NULL', 'IFNULL()', and 'COALESCE()'. For example, to select rows where a column is NULL:
SELECT * FROM users WHERE email IS NULL;
What is MySQL's AUTO_INCREMENT?
AUTO_INCREMENT is an attribute that automatically generates a unique number for a column, typically used for primary keys. Each time a new row is inserted, the column value increments by 1. It simplifies creating unique identifiers for rows.
What is MySQL error handling?
MySQL error handling involves capturing and managing errors using SQL conditions and handlers within stored procedures or functions. Use the 'DECLARE' statement to define error conditions and 'DECLARE HANDLER' to specify actions when errors occur, improving reliability and robustness.
How do you manage user privileges in MySQL?
User privileges control access to MySQL databases and operations. Use 'GRANT' to assign privileges and 'REVOKE' to remove them. The 'SHOW GRANTS' statement displays current privileges. Proper privilege management enhances security by restricting unauthorized actions.
What is the difference between a clustered and a non-clustered index in MySQL?
In MySQL, understanding the difference between clustered and non-clustered indexes is crucial for database optimization and performance tuning.
| Feature | Clustered Index | Non-Clustered Index |
|---|---|---|
| Definition | Determines the physical order of data in a table. | Separate structure from the actual data, stores a pointer to the data. |
| Primary Key | InnoDB engine automatically creates it on the primary key. | Can be created on any column(s) of the table. |
| Quantity Per Table | Only one clustered index per table. | Multiple non-clustered indexes can exist per table. |
| Performance | Efficient for range queries due to physical storage order. | Requires additional lookup to fetch the actual data. |
| Storage | Index and data rows are stored together in the same structure. | Index is stored separately from data rows, points to row locations. |
| Use Case | Optimal for frequently searched columns and range queries. | Useful for quick search on columns not covered by clustered index. |
| Data Insertion/Update | Can be slower due to maintaining physical order of rows. | Generally faster for inserts and updates as it doesn't alter data order. |
How do you perform database migration in MySQL?
Database migration involves transferring data from one database to another. Use tools like 'mysqldump' for exporting and importing data, or 'MySQL Workbench' for migration tasks. Plan carefully to ensure data integrity and minimal downtime during the migration process.