10 Essential MySQL Interview Questions for Database Administrators

As a database administrator, you need to be proficient in MySQL, one of the most popular open source database management systems. Whether you're a beginner or a seasoned professional, you should be prepared to answer some basic MySQL interview questions. In this article, we’ll cover some of the most common questions and provide examples to help you prepare for your next interview.

What is MySQL?

MySQL is an open source relational database management system (RDBMS) that uses SQL (Structured Query Language) to manage and operate data. It was first released in 1995 and is currently owned by Oracle Corporation. MySQL is widely used in web applications, especially those built using PHP, and is known for its speed, reliability, and ease of use.

What different data types does MySQL support?

MySQL supports multiple data types, including −

• Numeric data type − INT, BIGINT, FLOAT, DOUBLE, DECIMAL.

• Date and Time Data Types - DATE, TIME, DATETIME, TIMESTAMP.

• String data type − CHAR, VARCHAR, TEXT, BLOB.

• Other data types - BOOLEAN, ENUM, SET.

Here are some examples−

• INT − Represents an integer (e.g. 5, 10, 100).

• VARCHAR − Represents a variable-length string (e.g. 'hello', 'world').

• TEXT − Represents large text values ​​(e.g. blog posts, articles).

• DATE − Represents a date (e.g. '2023-04-03').

• TIMESTAMP − Represents date and time (e.g. '2023-04-03 14:30:00').

What is a Primary Key in MySQL?

A primary key is a unique identifier for a row in a MySQL table. It is used to ensure that each row in a table is uniquely identifiable and can be accessed quickly. A primary key can consist of one or more columns, but it must be unique and cannot be null.

This is an example −

CREATE TABLE users (
   id INT PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   email VARCHAR(255) NOT NULL UNIQUE
);

In this example, the 'id' column is the primary key of the 'users' table. It ensures that each user can be uniquely identified by their ID.

What is a Foreign Key in MySQL?

A foreign key is a column or group of columns in a MySQL table that refers to the primary key of another table. It is used to establish a relationship between two tables and ensure data consistency between them. Foreign key constraints can be added to a table to enforce referential integrity.

This is an example −

CREATE TABLE orders (
   id INT PRIMARY KEY,
   user_id INT,
   product_id INT,
   FOREIGN KEY (user_id) REFERENCES users(id),
   FOREIGN KEY (product_id) REFERENCES products(id)
);

In this example, the 'user_id' and 'product_id' columns are foreign keys that refer to the 'id' column in the 'users' and 'products' tables, respectively. This ensures that orders are associated with valid users and products.

What are views in MySQL?

In MySQL, a view is a virtual table based on the results of a SELECT statement. Views are often used to simplify complex queries and provide an abstract view of the underlying data.

This is an example −

CREATE VIEW user_orders AS
SELECT u.name, o.product_name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;

In this example, we create a view called 'user_orders' that returns the user name, product name, and order date for all orders. This view is based on a JOIN operation between the 'users' and 'orders' tables.

What is the difference between INNER JOIN and LEFT JOIN in MySQL?

INNER JOIN and LEFT JOIN are both types of JOIN operations used to combine rows from two or more tables. The main difference between the two is that INNER JOIN returns only rows that have matching values ​​in both tables, while LEFT JOIN returns all rows from the left table (the first table listed in the JOIN statement) as well as from the right table of matching lines.

This is an example −

Suppose we have two tables, 'users' and 'orders'. The 'users' table contains information about users, while the 'orders' table contains information about orders placed by these users.

To get a list of all users and their corresponding orders, we can use LEFT JOIN −

SELECT u.name, o.product_name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

This query would return all users, regardless of whether they have any orders, and their corresponding orders, if any.

To get the list of users who have placed orders, we can use INNER JOIN −

SELECT u.name, o.product_name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

This query would only return users who have placed orders, and their corresponding orders.

What is an index in MySQL?

Indexing is the process of creating an index on one or more columns of a MySQL table to improve query performance. An index is a data structure that allows a database to quickly find rows based on the values ​​in the indexed columns.

Here's an Example

Suppose we have a table called 'products' that contains information about products, including a column called 'product_name'. To improve the performance of searching for products by name, we can create an index on the 'product_name' column −

CREATE INDEX idx_products_product_name ON products(product_name);

This will create an index on the 'product_name' column of the 'products' table, allowing the database to quickly look up products by name.

在MySQL中，CHAR和VARCHAR数据类型有什么区别？

CHAR和VARCHAR都是MySQL中用于存储字符数据的字符串数据类型。两者之间的主要区别在于，CHAR是固定长度的数据类型，而VARCHAR是可变长度的数据类型。

Here's an Example

如果我们将一个列定义为CHAR(10)，无论该列中存储的数据的长度如何，它始终会占用10个字节的存储空间。这意味着，如果我们在该列中存储字符串'hello'，它将会被填充空格以占用10个字节的存储空间。

如果我们将一列定义为VARCHAR(10)，它将只占用必要的存储空间来存储数据。这意味着如果我们在该列中存储字符串'hello'，它将只占用5个字节的存储空间。

一般来说，对于长度固定的列（例如邮政编码），使用CHAR是一个好的实践，而对于长度可变的列（例如产品名称），使用VARCHAR。

什么是MySQL中的子查询？

子查询是在MySQL中嵌套在另一个查询中的查询。子查询可以用于检索将在主查询中使用的数据，或者根据条件过滤数据。

Here's an Example

假设我们有两个表，'users'和'orders'。'users'表包含有关用户的信息，而'orders'表包含有关这些用户下的订单的信息。

要获取所有已下订单的用户列表，我们可以使用子查询 -

SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);

这个查询首先执行子查询，该子查询从'orders'表中返回一个用户ID列表。然后，主查询使用IN运算符过滤'users'表，并返回下单的用户的姓名。

MySQL中的数据库事务是什么？

MySQL中的数据库事务是一系列作为单个工作单元执行的SQL语句。事务用于确保一组SQL语句以原子、一致、隔离和持久（ACID）的方式执行。

Here's an Example

假设我们有一个名为'accounts'的表，其中包含有关银行账户的信息，包括一个名为'balance'的列。要将资金从一个账户转移到另一个账户，我们需要在一个事务中更新两个账户的余额 -

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

此交易将从ID为1的账户中扣除100美元，并将100美元添加到ID为2的账户中，确保该交易以ACID方式执行。

The above is the detailed content of 10 Essential MySQL Interview Questions for Database Administrators. For more information, please follow other related articles on the PHP Chinese website!