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 lumn_name = lumn_name;
```
9. 连接查询 - 左连接：
```sql
SELECT * FROM table1 LEFT JOIN table2 lumn_name = lumn_name;
```
10. 连接查询 - 右连接：
```sql
SELECT * FROM table1 RIGHT JOIN table2 lumn_name = lumn_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
lumn_name, COUNT(*) FROM table1 INNER JOIN table2 lumn_name = lumn_name GROUP lumn_name;
```
40. 使用CASE语句进行多条件查询：
```sql
SELECT column_name, CASE WHEN condition1 THEN 'value1' WHEN condition2 THEN 'value2' ELSE 'value3' END FROM table_name;
```
mysql group by order by
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;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论