如何掌握并应用SQL中的常用查询语句？

SQL 常用查询语句详解

1. 基础查询语句

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

语法:SELECT column1, column2, ... FROM table_name;

示例:

    SELECT name, age FROM students;

FROM: 指定要查询的表。

语法:FROM table_name

示例:

    FROM students;

2. 条件查询

WHERE: 用于添加查询条件。

语法:WHERE condition

示例:

    SELECT * FROM employees WHERE department = 'HR';

AND / OR: 用于组合多个条件。

语法:condition1 AND/OR condition2

示例:

    SELECT * FROM employees WHERE department = 'HR' AND salary > 50000;

3. 排序与限制

ORDER BY: 对结果进行排序。

语法:ORDER BY column_name [ASC|DESC]

示例:

    SELECT * FROM employees ORDER BY salary DESC;

LIMIT: 限制返回的记录数。

语法:LIMIT number

示例:

    SELECT * FROM employees LIMIT 10;

4. 聚合函数

COUNT(): 计算行数。

语法:COUNT(*)

示例:

    SELECT COUNT(*) FROM employees;

SUM(): 计算数值列的总和。

语法:SUM(column_name)

示例:

    SELECT SUM(salary) FROM employees;

AVG(): 计算平均值。

语法:AVG(column_name)

示例:

    SELECT AVG(salary) FROM employees;

MAX() / MIN(): 获取最大值或最小值。

语法:MAX(column_name) / MIN(column_name)

示例:

    SELECT MAX(salary), MIN(salary) FROM employees;

5. 分组查询

GROUP BY: 根据一个或多个列对结果进行分组。

语法:GROUP BY column1, column2, ...

示例:

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

HAVING: 过滤分组后的结果。

语法:HAVING condition

示例:

    SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000;

6. 连接查询

INNER JOIN: 内连接，只返回两个表中匹配的记录。

语法:INNER JOIN table2 ON table1.column = table2.column

示例:

    SELECT e.name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.id;

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

语法:LEFT JOIN table2 ON table1.column = table2.column

示例:

    SELECT e.name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id;

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

语法:RIGHT JOIN table2 ON table1.column = table2.column

示例:

    SELECT e.name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.id;

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

语法:FULL JOIN table2 ON table1.column = table2.column

示例:

    SELECT e.name, d.department_name FROM employees e FULL JOIN departments d ON e.department_id = d.id;

7. 子查询

子查询: 嵌套在其他SQL语句中的查询。

语法:SELECT column1, column2 FROM (SELECT ... ) AS subquery

示例:

    SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

8. 联合查询

UNION: 合并多个查询结果集，去除重复记录。

语法:SELECT ... UNION SELECT ...

示例:

    SELECT name FROM employees UNION SELECT name FROM former_employees;

UNION ALL: 合并多个查询结果集，保留所有记录，包括重复记录。

语法:SELECT ... UNION ALL SELECT ...

示例:

    SELECT name FROM employees UNION ALL SELECT name FROM former_employees;

相关问题与解答

问题1: 如何在SQL中查询某个特定列中的唯一值？

解答: 可以使用DISTINCT 关键字来查询唯一值，要查询员工表中所有唯一的部门名称，可以使用以下语句：

SELECT DISTINCT department FROM employees;

问题2: 如果我想查询每个部门的最高工资，应该如何编写SQL语句？

解答: 你可以使用GROUP BY 结合聚合函数MAX() 来实现，以下是一个例子：

SELECT department_id, MAX(salary) AS max_salary FROM employees GROUP BY department_id;