mysql sql语句面试经典50题

2024年1月26日发(作者：)

mysql sql语句面试经典50题

1. 查询所有列的数据：

```sql

SELECT * FROM table_name;

```

2. 条件查询：

```sql

SELECT * FROM table_name WHERE condition;

```

3. 排序查询：

```sql

SELECT * FROM table_name ORDER BY column_name ASC/DESC;

```

4. 去重查询：

```sql

SELECT DISTINCT column_name FROM table_name;

```

5. 统计行数：

```sql

SELECT COUNT(*) FROM table_name;

```

6. 条件统计：

```sql

SELECT COUNT(*) FROM table_name WHERE condition;

```

7. 分组统计：

```sql

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

```

8. 连接查询 - 内连接：

```sql

SELECT * FROM table1 INNER JOIN table2 ON _name = _name;

```

9. 连接查询 - 左连接：

```sql

SELECT * FROM table1 LEFT JOIN table2 ON _name = _name;

```

10. 连接查询 - 右连接：

```sql

SELECT * FROM table1 RIGHT JOIN table2 ON _name = _name;

```

11. 子查询：

```sql

SELECT column_name FROM table_name WHERE column_name = (SELECT column_name

FROM another_table WHERE condition);

```

12. 更新数据：

```sql

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

```

13. 插入数据：

```sql

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

```

14. 删除数据：

```sql

DELETE FROM table_name WHERE condition;

```

15. 模糊查询：

```sql

SELECT * FROM table_name WHERE column_name LIKE 'pattern';

```

16. 使用通配符的模糊查询：

```sql

SELECT * FROM table_name WHERE column_name LIKE '%pattern%';

```

17. 使用正则表达式的模糊查询：

```sql

SELECT * FROM table_name WHERE column_name REGEXP 'pattern';

```

18. 限制结果集的行数：

```sql

SELECT * FROM table_name LIMIT 10;

```

19. 跳过前N行：

```sql

SELECT * FROM table_name LIMIT offset, count;

```

20. 计算平均值：

```sql

SELECT AVG(column_name) FROM table_name;

```

21. 计算总和：

```sql

SELECT SUM(column_name) FROM table_name;

```

22. 计算最大值和最小值：

```sql

SELECT MAX(column_name), MIN(column_name) FROM table_name;

```

23. 使用CASE语句进行条件查询：

```sql

SELECT column_name, CASE WHEN condition THEN 'value1' ELSE 'value2' END FROM

table_name;

```

24. 使用GROUP BY和HAVING进行分组过滤：

```sql

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING

COUNT(*) > 1;

```

25. 多表更新：

```sql

UPDATE table1 SET column1 = value1 WHERE column_name IN (SELECT column_name

FROM table2 WHERE condition);

```

26. 多表删除：

```sql

DELETE FROM table1 WHERE column_name IN (SELECT column_name FROM table2 WHERE

condition);

```

27. 创建表：

```sql

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

...

);

```

28. 修改表结构 - 添加列：

```sql

ALTER TABLE table_name ADD column_name datatype;

```

29. 修改表结构 - 修改列数据类型：

```sql

ALTER TABLE table_name MODIFY column_name new_datatype;

```

30. 修改表结构 - 删除列：

```sql

ALTER TABLE table_name DROP COLUMN column_name;

```

31. 创建索引：

```sql

CREATE INDEX index_name ON table_name (column_name);

```

32. 删除索引：

```sql

DROP INDEX index_name ON table_name;

```

33. 使用LIMIT和OFFSET进行分页查询：

```sql

SELECT * FROM table_name LIMIT page_size OFFSET (page_number - 1) * page_size;

```

34. 使用IFNULL处理空值：

```sql

SELECT column1, IFNULL(column2, 'default_value') FROM table_name;

```

35. 使用COALESCE处理空值：

```sql

SELECT column1, COALESCE(column2, 'default_value') FROM table_name;

```

36. 计算日期差：

```sql

SELECT DATEDIFF(date1, date2) FROM table_name;

```

37. 获取当前日期和时间：

```sql

SELECT NOW();

```

38. 获取唯一标识符：

```sql

SELECT UUID();

```

39. 使用INNER JOIN和GROUP BY计算每个组的总数：

```sql

SELECT _name, COUNT(*) FROM table1 INNER JOIN table2 ON

_name = _name GROUP BY _name;

```

40. 使用CASE语句进行多条件查询：

```sql

SELECT column_name, CASE WHEN condition1 THEN 'value1' WHEN condition2 THEN

'value2' ELSE 'value3' END FROM table_name;

```

41. 使用CONCAT连接字符串：

```sql

SELECT CONCAT(column1, ' ', column2) AS concatenated_string FROM table_name;

```

42. 使用TRIM去除字符串两端的空格：

```sql

SELECT TRIM(column_name) FROM table_name;

```

43. 使用LEFT和RIGHT截取字符串：

```sql

SELECT LEFT(column_name, length) FROM table_name;

SELECT RIGHT(column_name, length) FROM table_name;

```

44. 使用IN进行多值匹配：

```sql

SELECT * FROM table_name WHERE column_name IN (value1, value2, value3);

```

45. 使用BETWEEN进行范围查询：

```sql

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

```

46. 使用COUNT和GROUP BY查找重复值：

```sql

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING

COUNT(*) > 1;

```

47. 使用DATE_FORMAT格式化日期：

```sql

SELECT DATE_FORMAT(column_name, '%Y-%m-%d') FROM table_name;

```

48. 使用SUM和GROUP BY进行累计求和：

```sql

SELECT column_name, SUM(column_name) OVER (ORDER BY column_name) AS

running_total FROM table_name;

```

49. 使用CONVERT进行数据类型转换：

```sql

SELECT column_name, CONVERT(column_name, new_datatype) FROM table_name;

```

50. 使用UPDATE和LIMIT进行分页更新：

```sql

UPDATE table_name SET column1 = value1 LIMIT page_size OFFSET (page_number - 1) *

page_size;

```