数据库 SQL 查询语句详解
SQL(Structured Query Language)是一种用于管理和操作关系型数据库的编程语言,它包括数据定义语言(DDL)、数据操纵语言(DML)、数据控制语言(DCL)和事务控制语言(TCL),本文将详细解析 SQL 查询语句,包括其基本结构、常用函数和高级查询技巧。

一、基本结构
1、SELECT 语句:用于从数据库中选择数据。
SELECT column1, column2, ... FROM table_name;
2、WHERE 子句:用于过滤数据。
SELECT column1, column2, ... FROM table_name WHERE condition;
3、ORDER BY 子句:用于对结果集进行排序。
SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC|DESC];
4、GROUP BY 子句:用于对数据进行分组。
SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;
5、HAVING 子句:用于过滤分组后的数据。
SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 HAVING condition;
6、JOIN 子句:用于连接多个表。

SELECT columns FROM table1 JOIN table2 ON table1.common_field = table2.common_field;
二、常用函数
1、聚合函数:如 COUNT(), SUM(), AVG(), MAX(), MIN()。
SELECT COUNT(*) FROM employees; SELECT AVG(salary) FROM employees;
2、字符串函数:如 CONCAT(), LENGTH(), SUBSTRING(), UPPER(), LOWER()。
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees; SELECT LENGTH(email) FROM customers;
3、日期函数:如 NOW(), DATE(), YEAR(), MONTH(), DAY()。
SELECT NOW() AS current_time; SELECT YEAR(birthdate) FROM people;
三、高级查询技巧
1、子查询:在一个查询中嵌套另一个查询。
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location='New York');
2、联合查询:使用 UNION 或 UNION ALL 合并两个查询的结果。

SELECT name FROM customers UNION SELECT name FROM suppliers;
3、窗口函数:如 ROW_NUMBER(), RANK(), DENSE_RANK()。
SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;
4、CTE(公用表表达式):用于简化复杂查询。
WITH HighSalaries AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) > 50000 ) SELECT e.department_id, e.employee_id, e.salary FROM employees e JOIN HighSalaries hs ON e.department_id = hs.department_id;
四、示例表格及查询
假设我们有以下两个表:
Employees:
employee_id | first_name | last_name | department_id | salary | |
1 | John | Doe | 1 | 60000 | |
2 | Jane | Smith | 2 | 65000 | |
3 | Alice | Johnson | 1 | 70000 | |
4 | Bob | Brown | 2 | 55000 |
Departments:
department_id | department_name | location | |
1 | Sales | New York | |
2 | Engineering | San Francisco |
查询每个部门的平均薪资:
SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id;
查询薪资高于部门平均薪资的员工:
SELECT e.* FROM employees e JOIN ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) dept ON e.department_id = dept.department_id WHERE e.salary > dept.avg_salary;
相关问题与解答
问题1:如何在 SQL 中删除重复行?
解答:可以使用DISTINCT
关键字来删除重复行,或者使用ROW_NUMBER()
窗口函数结合子查询来删除重复行。
WITH RankedRows AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY (SELECT NULL)) AS row_num FROM your_table ) DELETE FROM RankedRows WHERE row_num > 1;
问题2:如何在 SQL 中更新表中的数据?
解答:可以使用UPDATE
语句结合SET
子句和WHERE
子句来更新表中的数据。
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 1;
来源互联网整合,作者:小编,如若转载,请注明出处:https://www.aiboce.com/ask/78743.html