MySQL Interview Questions

## 1. How to create and delete a database?
Answer: 

-- Create database  
CREATE DATABASE mydb;  
-- Delete database  
DROP DATABASE mydb;

Data Types and Constraints

## 2. What data types does MySQL support?
Answer:
MySQL supports various data types, including:

  • Basic types: TINYINT, SMALLINT, INT, BIGINT
  • Binary types: CHAR, VARCHAR, TEXT
  • Fixed-length types: FIXED_LENGTH, ENUM
  • Long types: LONGTEXT, BLOB
  • Constraint types: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY

Querying and Optimization

## 3. How to optimize query performance?
Answer:

  • Use indexes: Add indexes to frequently queried columns
  • Avoid full table scans: Limit the query scope using WHERE clauses
  • Use LIMIT: Restrict the number of returned results
  • Avoid SELECT *: Select only required fields
  • Pagination queries: Optimize large data volume queries using LIMIT and OFFSET

Transactions and Locks

## 4. What is a transaction? How to ensure ACID properties?
Answer:
A transaction is the smallest unit of database operations, ensuring that multiple operations either all complete or all roll back.

  • Atomicity: All operations must complete or roll back together
  • Consistency: Data state remains consistent before and after the transaction execution
  • Isolation: Transactions executed simultaneously are independent of each other
  • Durability: Changes made by a completed transaction are permanently saved

Indexes and Queries

## 5. What is an index? In what scenarios should indexes be used?
Answer:
An index is a structure in the database used to accelerate data retrieval by storing data in an ordered manner.
Scenarios suitable for using indexes:

  • Columns in query conditions (e.g., WHERE clauses)
  • Frequently queried columns
  • To avoid full table scans

Storage and Backup

## 6. How to back up a database?
Answer:

  • Use the mysqldump command: 
    mysqldump -u username -p dbname > backup.sql
  • Use physical backups:
    • Use tools like mysqldump or percona-xtrabackup
    • For InnoDB tables, you can export data using INSERT INTO ... SELECT

Security and Permissions

## 7. How to set user permissions?
Answer: 

-- Create user  
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';  
-- Grant privileges  
GRANT ALL PRIVILEGES ON mydb.* TO 'newuser'@'localhost';  
-- Refresh privileges  
FLUSH PRIVILEGES;

Issues and Solutions

## 8. What is a deadlock? How to avoid deadlocks?
Answer:
A deadlock occurs when multiple transactions wait for each other to release resources, preventing further execution.
Measures to avoid deadlocks:

  • Avoid holding locks for extended periods within transactions
  • Use explicit commits (COMMIT) to release locks
  • Adopt optimistic locking mechanisms (e.g., using version numbers)

Advanced Features

## 9. What is a stored procedure? How to write one?
Answer:
A stored procedure is precompiled SQL code that can be executed repeatedly.
Steps to write a stored procedure:

  1. Create the stored procedure: 
    CREATE PROCEDURE proc_name (IN param1 INT)  
BEGIN  
  SELECT * FROM table WHERE id = param1;  
END;
  2. Call the stored procedure: 
    CALL proc_name(10);

Common Issues

## 10. What is a trigger? How to create one?
Answer:
A trigger is an SQL statement automatically executed by the database at specific events (such as INSERT or UPDATE).
Syntax for creating a trigger: 

CREATE TRIGGER trigger_name BEFORE INSERT ON table  
FOR EACH ROW  
BEGIN  
  INSERT INTO log_table (event, id) VALUES ('INSERT', NEW.id);  
END;