sql必备50条语句 sql最常用的50条语句

SQL必备50条语句

SQL是数据库管理的核心工具，掌握常用语句能显著提高工作效率。以下是SQL最常用的50条语句，按功能分类整理：

一、基础查询语句

全表查询
SELECT * FROM table_name;
条件查询
SELECT * FROM table_name WHERE condition;-- 示例：SELECT * FROM employees WHERE salary > 50000;
多条件查询
SELECT * FROM table_name WHERE condition1 AND/OR condition2;
模糊查询
SELECT * FROM table_name WHERE column_name LIKE '%pattern%';
排序查询
SELECT * FROM table_name ORDER BY column_name ASC/DESC;-- 示例：SELECT * FROM employees ORDER BY salary DESC;
限制返回行数
SELECT * FROM table_name LIMIT number;-- 示例：SELECT * FROM employees LIMIT 10;
分页查询
SELECT * FROM table_name LIMIT offset, number;-- 或使用标准SQL语法：SELECT * FROM table_name OFFSET offset ROWS FETCH NEXT number ROWS ONLY;
去重查询
SELECT DISTINCT column_name FROM table_name;
列选择查询
SELECT column1, column2 FROM table_name;
计算列查询
SELECT column1, column2 * 1.1 AS new_column FROM table_name;

INNER JOIN（内连接）
SELECT a.column1, b.column2 FROM table1 a INNER JOIN table2 b ON a.key = b.key;-- 示例：SELECT employees.name, departments.dept_name FROM employees INNER JOIN departments ON employees.dept_id = departments.dept_id;
LEFT JOIN（左连接）
SELECT a.column1, b.column2 FROM table1 a LEFT JOIN table2 b ON a.key = b.key;-- 示例：SELECT employees.name, departments.dept_name FROM employees LEFT JOIN departments ON employees.dept_id = departments.dept_id;
RIGHT JOIN（右连接）
SELECT a.column1, b.column2 FROM table1 a RIGHT JOIN table2 b ON a.key = b.key;
FULL OUTER JOIN（全外连接）
SELECT a.column1, b.column2 FROM table1 a FULL OUTER JOIN table2 b ON a.key = b.key;
自连接
SELECT a.column1, b.column1 FROM table_name a, table_name b WHERE a.key = b.parent_key;
交叉连接（笛卡尔积）
SELECT * FROM table1 CROSS JOIN table2;
多表连接
SELECT a.col1, b.col2, c.col3 FROM table1 a JOIN table2 b ON a.key = b.key JOIN table3 c ON b.key = c.key;
非等值连接
SELECT a.col1, b.col2 FROM table1 a JOIN table2 b ON a.col1 > b.col2;
自然连接
SELECT * FROM table1 NATURAL JOIN table2;
USING简化连接
SELECT * FROM table1 JOIN table2 USING (key_column);

插入数据
INSERT INTO table_name (column1, column2) VALUES (value1, value2);-- 示例：INSERT INTO employees (name, salary, dept_id) VALUES ('John Doe', 60000, 3);
批量插入
INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4);
从查询插入
INSERT INTO table_name (column1, column2) SELECT col1, col2 FROM source_table WHERE condition;
更新数据
UPDATE table_name SET column1 = value1 WHERE condition;-- 示例：UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 3;
多列更新
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
基于查询更新
UPDATE table1 SET column1 = (SELECT column2 FROM table2 WHERE ...) WHERE condition;
删除数据
DELETE FROM table_name WHERE condition;-- 示例：DELETE FROM employees WHERE salary < 30000;
清空表数据
TRUNCATE TABLE table_name;
合并数据（UPSERT）
-- MySQL语法INSERT INTO table_name (column1, column2) VALUES (value1, value2) ON DUPLICATE KEY UPDATE column1 = value1;-- PostgreSQL语法INSERT INTO table_name (column1, column2) VALUES (value1, value2) ON CONFLICT (key_column) DO UPDATE SET column1 = value1;

分组查询
SELECT column1, COUNT(*) FROM table_name GROUP BY column1;-- 示例：SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id;
分组后过滤
SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > number;-- 示例：SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id HAVING avg_salary > 50000;
简单子查询
SELECT * FROM table1 WHERE column1 IN (SELECT column2 FROM table2 WHERE condition);-- 示例：SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
相关子查询
SELECT * FROM table1 a WHERE EXISTS (SELECT 1 FROM table2 b WHERE a.key = b.key);
派生表查询
SELECT a.col1 FROM (SELECT col1 FROM table1 WHERE condition) a;
公用表表达式（CTE）
WITH cte_name AS ( SELECT * FROM table_name WHERE condition)SELECT * FROM cte_name;
递归CTE
WITH RECURSIVE cte_name AS ( -- 基础查询 SELECT * FROM table_name WHERE condition UNION ALL -- 递归部分 SELECT t.* FROM table_name t JOIN cte_name c ON t.parent_id = c.id)SELECT * FROM cte_name;
窗口函数
SELECT column1, ROW_NUMBER() OVER (PARTITION BY column2 ORDER BY column3) AS row_numFROM table_name;
常用窗口函数
SELECT column1, RANK() OVER (ORDER BY column2 DESC) AS rank, DENSE_RANK() OVER (ORDER BY column2 DESC) AS