当前位置：首页 > news >正文

SQL语法大全指南

news 2025/8/19 9:49:06

SQL（Structured Query Language）是操作关系型数据库的标准语言，其关键字和语法元素是构建各类数据库操作的基础。本文将系统梳理SQL核心语法，按功能分类详解常用关键字、用法示例及数据库差异，助你全面掌握SQL操作逻辑。

一、数据查询（SELECT）：从数据库中提取信息的核心工具

数据查询是SQL最常用的功能，通过SELECT语句实现，配合各类关键字可完成过滤、分组、排序、连接等操作。

基础查询关键字

SELECT：指定要查询的列（*表示所有列）。
示例：SELECT id, name FROM users;（查询users表的id和name列）
FROM：指定查询的数据源（表或视图）。
示例：SELECT * FROM orders;（从orders表查询所有数据）
WHERE：对查询结果进行条件过滤（支持比较运算符、逻辑运算符）。
示例：SELECT * FROM products WHERE price > 100 AND stock > 0;（查询价格>100且有库存的商品）
ORDER BY：对结果排序（ASC升序，DESC降序，默认升序）。
示例：SELECT * FROM employees ORDER BY hire_date DESC;（按入职日期降序排列员工）
DISTINCT：去除结果集中的重复行。
示例：SELECT DISTINCT department FROM employees;（查询所有不重复的部门）

分组与聚合查询

GROUP BY：按指定列对结果分组，常与聚合函数配合。
示例：SELECT department, COUNT(*) AS emp_count FROM employees GROUP BY department;（按部门统计员工数量）
HAVING：对GROUP BY分组后的结果进行过滤（区别于WHERE，HAVING可使用聚合函数）。
示例：SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 5000;（筛选平均工资>5000的部门）
聚合函数：对分组数据计算（COUNT()计数、SUM()求和、AVG()平均值、MAX()最大值、MIN()最小值）。
示例：SELECT MAX(score) AS max_score FROM exams;（查询最高分数）

多表连接查询

INNER JOIN：返回两表中匹配条件的行（仅保留交集）。
示例：SELECT u.name, o.order_no FROM users u INNER JOIN orders o ON u.id = o.user_id;（查询用户及其订单号）
LEFT JOIN（左外连接）：返回左表所有行，右表匹配行（无匹配则为NULL）。
示例：SELECT u.name, o.order_no FROM users u LEFT JOIN orders o ON u.id = o.user_id;（查询所有用户，包括无订单的用户）
RIGHT JOIN（右外连接）：返回右表所有行，左表匹配行（无匹配则为NULL）。
示例：SELECT u.name, o.order_no FROM users u RIGHT JOIN orders o ON u.id = o.user_id;（查询所有订单，包括无对应用户的订单）
FULL JOIN（全外连接）：返回两表所有行（匹配与不匹配）。
注意：MySQL不支持，需用LEFT JOIN + UNION + RIGHT JOIN模拟；PostgreSQL、SQL Server支持。
CROSS JOIN：返回两表的笛卡尔积（慎用，结果集可能极大）。
示例：SELECT * FROM products CROSS JOIN categories;（返回商品与类别的所有组合）

结果限制与分页

LIMIT/OFFSET：限制返回行数（LIMIT）和偏移量（OFFSET），适用于MySQL、PostgreSQL。
示例：SELECT * FROM articles LIMIT 10 OFFSET 20;（从第21行开始取10条数据，即第3页）
TOP：限制返回行数，适用于SQL Server。
示例：SELECT TOP 5 * FROM products;（取前5条商品数据）
ROWNUM：行号伪列，适用于Oracle（需配合子查询实现分页）。
示例：SELECT * FROM (SELECT t.*, ROWNUM rn FROM articles t) WHERE rn BETWEEN 11 AND 20;（取第11-20行数据）

高级查询扩展

WITH (CTE)：定义公共表表达式（临时结果集），简化复杂查询。
示例：WITH high_salary AS (SELECT * FROM employees WHERE salary > 10000) SELECT * FROM high_salary WHERE department = '技术部';
窗口函数：在结果集的“窗口”内计算（不折叠行，区别于GROUP BY）。
示例：SELECT name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees;（按部门给员工工资排名）
UNION/UNION ALL：合并多个查询结果（UNION去重，UNION ALL不去重，后者性能更高）。
示例：SELECT name FROM students UNION ALL SELECT name FROM teachers;（合并学生和教师姓名）

二、数据操作（DML）：修改数据库中的数据

数据操作语言（DML）用于添加、修改、删除表中的数据，核心是INSERT、UPDATE、DELETE。

插入数据

INSERT INTO ... VALUES：插入单行或多行数据。
示例1（单行）：INSERT INTO users (name, age) VALUES ('张三', 25); 示例2（多行）：INSERT INTO users (name, age) VALUES ('李四', 30), ('王五', 28);
INSERT ... SELECT：将查询结果插入表中。
示例：INSERT INTO archive_orders SELECT * FROM orders WHERE create_time < '2023-01-01';（归档2023年前的订单）
DEFAULT：使用列的默认值插入。
示例：INSERT INTO users (name, reg_time) VALUES ('赵六', DEFAULT);（reg_time使用默认值，如当前时间）

修改数据

UPDATE：更新表中的数据，需配合SET指定修改内容，WHERE限制范围（无WHERE则更新全表，慎用）。
示例：UPDATE products SET price = price * 1.1 WHERE category = '电子产品';（电子产品涨价10%）
多表关联更新：MySQL支持JOIN语法，PostgreSQL支持FROM语法。
示例（MySQL）：UPDATE users u JOIN orders o ON u.id = o.user_id SET u.vip = 1 WHERE o.total_amount > 10000;（给订单总金额>10000的用户设为VIP）

删除数据

DELETE：删除表中的行，WHERE限制删除范围（无WHERE则删除全表数据，可回滚）。
示例：DELETE FROM logs WHERE create_time < '2023-01-01';（删除2023年前的日志）
TRUNCATE：快速清空表数据（保留表结构，多数数据库中为DDL操作，不可回滚，性能优于DELETE）。
示例：TRUNCATE TABLE temp_data;（清空临时数据表）
注意：PostgreSQL中TRUNCATE支持事务回滚，是例外。

特殊操作

MERGE (UPSERT)：按条件执行插入或更新（若存在则更新，不存在则插入）。
示例：MERGE INTO users u USING new_users n ON u.id = n.id WHEN MATCHED THEN UPDATE SET u.name = n.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (n.id, n.name);
INSERT ... ON DUPLICATE KEY UPDATE：MySQL特有，主键冲突时执行更新。
示例：INSERT INTO stats (date, count) VALUES ('2023-10-01', 1) ON DUPLICATE KEY UPDATE count = count + 1;（日期重复时累加计数）

三、数据定义（DDL）：创建和管理数据库对象

数据定义语言（DDL）用于创建、修改、删除数据库对象（表、索引、视图等），核心是CREATE、ALTER、DROP。

表操作

CREATE TABLE：创建表，需指定列名、数据类型及约束。
示例：CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT CHECK (age > 0), reg_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
ALTER TABLE：修改表结构（添加列、修改列、删除列等）。
示例1（添加列）：ALTER TABLE students ADD COLUMN gender CHAR(1); 示例2（修改列类型）：ALTER TABLE students ALTER COLUMN age TYPE SMALLINT;
DROP TABLE：删除表（需谨慎，数据会丢失）。
示例：DROP TABLE IF EXISTS temp_table;（仅当表存在时删除，避免报错）

约束管理

约束用于保证数据完整性，常见类型包括：

PRIMARY KEY：主键（唯一标识每行，非空且唯一）。
示例：ALTER TABLE users ADD PRIMARY KEY (id);
FOREIGN KEY：外键（关联另一表的主键，保证参照完整性）。
示例：ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);（orders表的user_id关联users表的id）
UNIQUE：唯一约束（列值不可重复，可空）。
示例：CREATE TABLE users (email VARCHAR(100) UNIQUE, ...);（邮箱不可重复）
CHECK：检查约束（限制列值范围）。
示例：CREATE TABLE products (price DECIMAL CHECK (price >= 0), ...);（价格不能为负）
DEFAULT：默认值约束（列的默认值）。
示例：CREATE TABLE orders (status VARCHAR(20) DEFAULT '待支付', ...);

索引与视图

CREATE INDEX：创建索引（提升查询效率，不影响数据本身）。
示例：CREATE INDEX idx_orders_user_id ON orders(user_id);（给orders表的user_id列建索引）
CREATE VIEW：创建视图（虚拟表，基于查询结果，简化复杂查询）。
示例：CREATE VIEW user_orders AS SELECT u.name, o.order_no FROM users u JOIN orders o ON u.id = o.user_id;

四、数据控制（DCL）：管理数据库权限

数据控制语言（DCL）用于分配和撤销用户对数据库对象的操作权限。

GRANT：授予权限（如查询、插入、修改等）。
示例：GRANT SELECT, INSERT ON users TO 'reader'@'localhost';（授予reader用户查询和插入users表的权限）
REVOKE：撤销已授予的权限。
示例：REVOKE INSERT ON users FROM 'reader'@'localhost';（撤销reader用户插入users表的权限）
CREATE USER：创建数据库用户。
示例：CREATE USER 'admin'@'%' IDENTIFIED BY 'SecurePwd123';（创建admin用户，允许远程登录）
ROLE：创建角色（权限集合，简化权限管理）。
示例：CREATE ROLE 'data_analyst'; GRANT SELECT ON ALL TABLES TO 'data_analyst'; GRANT 'data_analyst' TO 'user1';（创建分析师角色并授权）

五、事务控制与锁：保证数据一致性

事务是一组不可分割的操作，要么全执行，要么全不执行，用于保证数据一致性。

事务核心操作

BEGIN TRANSACTION/START TRANSACTION：开启事务。
示例：START TRANSACTION;
COMMIT：提交事务（永久保存修改）。
示例：COMMIT;
ROLLBACK：回滚事务（撤销未提交的修改）。
示例：ROLLBACK;
SAVEPOINT：设置事务保存点（可回滚到指定点，无需撤销全部操作）。
示例：SAVEPOINT sp1; ... ROLLBACK TO sp1;（回滚到sp1保存点）

事务隔离级别

数据库通过隔离级别控制事务间的相互影响，常见级别：

READ UNCOMMITTED：最低级别，允许读取未提交的数据（可能产生脏读）。
READ COMMITTED：默认级别，只能读取已提交的数据（避免脏读）。
REPEATABLE READ：保证同一事务中多次读取结果一致（避免不可重复读）。
SERIALIZABLE：最高级别，事务串行执行（避免幻读，性能较低）。
示例：SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;（设置隔离级别为可重复读）

锁机制

锁用于防止并发操作导致的数据不一致：

FOR UPDATE：行级排他锁（其他事务不能修改或加排他锁）。
示例：SELECT * FROM products WHERE id = 1 FOR UPDATE;（锁定id=1的商品行，准备修改）
FOR SHARE：行级共享锁（其他事务可读，不可修改）。
示例：SELECT * FROM products WHERE id = 1 FOR SHARE;（锁定行用于读取，允许其他事务共享锁）

六、条件与逻辑：构建查询条件的核心

SQL的条件与逻辑关键字用于过滤数据、处理分支逻辑，是查询的“判断系统”。

AND/OR/NOT：逻辑运算符（与/或/非）。
示例：SELECT * FROM products WHERE price > 100 AND stock > 0;
IN/NOT IN：判断值是否在列表或子查询中。
示例：SELECT * FROM users WHERE city IN ('北京', '上海', '广州');
BETWEEN ... AND ...：判断值是否在范围内（包含端点）。
示例：SELECT * FROM orders WHERE create_time BETWEEN '2023-10-01' AND '2023-10-31';（查询10月订单）
LIKE：模式匹配（%匹配任意字符，_匹配单个字符）。
示例：SELECT * FROM users WHERE name LIKE '张%';（查询姓张的用户）
IS NULL/IS NOT NULL：判断值是否为NULL（NULL不能用=或!=判断）。
示例：SELECT * FROM products WHERE description IS NULL;（查询无描述的商品）
CASE：条件分支（类似if-else）。
示例：SELECT name, CASE WHEN score >= 60 THEN '及格' ELSE '不及格' END AS result FROM exams;（判断考试结果）

七、函数与表达式：处理数据的工具集

SQL提供丰富的函数用于数据转换、计算和处理，按功能可分为以下几类。

类型转换函数

CAST()：标准类型转换。
示例：SELECT CAST('2023-10-01' AS DATE);（将字符串转为日期）
CONVERT()：SQL Server特有转换函数。
示例：SELECT CONVERT(DATE, '2023-10-01');

字符串函数

CONCAT()：拼接字符串。
示例：SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SUBSTRING()：截取子串。
示例：SELECT SUBSTRING('Hello World', 1, 5);（返回'Hello'）
LENGTH()/LEN()：计算字符串长度（LENGTH适用于MySQL/PostgreSQL，LEN适用于SQL Server）。
示例：SELECT LENGTH(name) AS name_len FROM users;

日期函数

CURRENT_DATE/CURRENT_TIMESTAMP：获取当前日期/时间戳。
示例：SELECT CURRENT_DATE;（返回当前日期，如2023-10-05）
DATEADD()：日期加减。
示例：SELECT DATEADD(DAY, 7, '2023-10-01');（返回2023-10-08，加7天）

窗口函数

窗口函数用于在结果集的“窗口”内计算，常见函数：

ROW_NUMBER()：为每行分配唯一行号。
示例：SELECT name, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM students;
RANK()/DENSE_RANK()：排名（RANK会跳号，DENSE_RANK不跳号）。
示例：SELECT name, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;

八、特殊用途与扩展：数据库特有功能

不同数据库在标准SQL基础上扩展了特有语法，用于满足特定需求。

自增列：自动生成唯一值（MySQL用AUTO_INCREMENT，SQL Server用IDENTITY，PostgreSQL用SERIAL）。
示例（MySQL）：CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, ...);
EXPLAIN：分析查询执行计划（优化查询性能）。
示例：EXPLAIN SELECT * FROM orders WHERE user_id = 1;（查看该查询的执行计划）
存储过程/函数：预编译的SQL块（复用逻辑，提升性能）。
示例（创建简单函数）：CREATE FUNCTION get_user_count() RETURNS INT RETURN (SELECT COUNT(*) FROM users);
DUAL：虚拟表（Oracle、MySQL用于无表查询）。
示例：SELECT CURRENT_TIMESTAMP FROM DUAL;（查询当前时间，无需实际表）