SQL Query 面试题与答案

问题 46. Explain the concept of a natural join.

A natural join is a type of JOIN that automatically matches columns with the same name in the joined tables. It eliminates duplicate columns in the result set.

Example:

SELECT * FROM table1 NATURAL JOIN table2;

问题 47. What is the purpose of the SQL LIKE operator?

The LIKE operator is used to search for a specified pattern in a column. It is often used with wildcard characters (% and _).

Example:

SELECT * FROM table WHERE column LIKE 'pattern%';

问题 48. Write a SQL query to find the top N records from a table.

SELECT * FROM table ORDER BY column DESC LIMIT N;

问题 49. Explain the concept of a CTE (Common Table Expression).

A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It improves the readability and maintainability of complex queries.

Example:

WITH cte_name AS (SELECT * FROM table) SELECT * FROM cte_name;

问题 50. What is the purpose of the SQL TRIGGER?

A TRIGGER is a set of instructions that are automatically executed (or 'triggered') in response to specific events, such as INSERTs, UPDATEs, or DELETEs, on a particular table.

Example:

CREATE TRIGGER trigger_name BEFORE INSERT ON table FOR EACH ROW BEGIN -- trigger logic END;