SQL中有哪些常用的查询语句？

SQL常用查询语句详解

1. 基本查询语句

SELECT: 用于从数据库中检索数据。

SELECT column1, column2 FROM table_name;

示例：SELECT first_name, last_name FROM employees;

2. 条件查询

WHERE: 用于指定查询条件。

SELECT * FROM table_name WHERE condition;

示例：SELECT * FROM employees WHERE department = 'Sales';

3. 排序查询

ORDER BY: 用于对结果集进行排序。

SELECT * FROM table_name ORDER BY column1 [ASC|DESC];

示例：SELECT * FROM employees ORDER BY salary DESC;

4. 限制查询结果

LIMIT: 用于限制返回的记录数。

SELECT * FROM table_name LIMIT number;

SELECT * FROM table_name LIMIT number OFFSET number;

示例：SELECT * FROM employees LIMIT 10;

5. 聚合函数

COUNT(): 统计行数或列值不为NULL的行数。

SELECT COUNT(*) FROM table_name;

示例：SELECT COUNT(*) FROM employees;

SUM(): 计算列的总和。

SELECT SUM(column_name) FROM table_name;

示例：SELECT SUM(salary) FROM employees;

AVG(): 计算列的平均值。

SELECT AVG(column_name) FROM table_name;

示例：SELECT AVG(salary) FROM employees;

MAX(): 查找列的最大值。

SELECT MAX(column_name) FROM table_name;

示例：SELECT MAX(salary) FROM employees;

MIN(): 查找列的最小值。

SELECT MIN(column_name) FROM table_name;

示例：SELECT MIN(salary) FROM employees;

6. 分组查询

GROUP BY: 根据一个或多个列分组，并使用聚合函数。

SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;

示例：SELECT department, COUNT(*) FROM employees GROUP BY department;

7. HAVING子句

HAVING: 用于过滤分组后的结果。

SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 HAVING condition;

示例：SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;

8. 连接查询

INNER JOIN: 返回两个表中匹配的记录。

SELECT columns FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;

示例：SELECT employees.first_name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;

LEFT JOIN (LEFT OUTER JOIN): 返回左表所有记录及右表匹配的记录。

SELECT columns FROM table1 LEFT JOIN table2 ON table1.common_field = table2.common_field;

示例：SELECT employees.first_name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;

RIGHT JOIN (RIGHT OUTER JOIN): 返回右表所有记录及左表匹配的记录。

SELECT columns FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field;

示例：SELECT employees.first_name, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;

FULL OUTER JOIN: 返回两个表中所有的记录，当没有匹配时返回NULL。

SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.common_field = table2.common_field;

示例：SELECT employees.first_name, departments.department_name FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.department_id;

CROSS JOIN: 返回两个表的笛卡尔积。

SELECT columns FROM table1 CROSS JOIN table2;

示例：SELECT employees.first_name, departments.department_name FROM employees CROSS JOIN departments;

9. UNION操作符

UNION: 合并两个或多个SELECT语句的结果集，去除重复的记录。

SELECT column1 FROM table1 UNION SELECT column1 FROM table2;

示例：SELECT first_name FROM employees UNION SELECT first_name FROM managers;

UNION ALL: 合并两个或多个SELECT语句的结果集，包括重复的记录。

SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2;

示例：SELECT first_name FROM employees UNION ALL SELECT first_name FROM managers;

10. 子查询

子查询: 嵌套在另一个SQL查询中的查询。

SELECT column1 FROM table1 WHERE column2 = (SELECT column3 FROM table2);

示例：SELECT first_name FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

相关问题与解答

Q1: 如何在SQL中删除重复的记录？

A1: 你可以使用带有CTE（公用表表达式）的DELETE语句来删除重复记录，以下是一个示例：

WITH CTE AS (
    SELECT column1, column2, row_number() OVER (PARTITION BY column1, column2 ORDER BY primary_key) as row_num
    FROM your_table
)
DELETE FROM your_table
WHERE primary_key IN (SELECT primary_key FROM CTE WHERE row_num > 1);

在这个例子中，我们首先通过CTE为每一组重复的记录分配一个行号，然后删除行号大于1的记录。

Q2: 如何优化SQL查询以提高性能？

A2: SQL查询优化可以通过多种方式实现，包括但不限于以下几点：

1、索引: 确保对经常用于WHERE子句、JOIN条件、ORDER BY子句中的列建立索引。

2、选择适当的字段: 只选择需要的字段，避免使用SELECT *。

3、减少数据量: 使用WHERE子句过滤掉不需要的数据，减少处理的数据量。

4、避免复杂的子查询: 如果可能，将子查询转换为JOIN操作。

5、使用EXPLAIN分析查询计划: 检查查询的执行计划，找出瓶颈并进行优化。