以下是需要掌握的 SQL 查询基础语句：
SELECT：选择数据表中的数据SELECT column_name1, column_name2 FROM table_name; FROM：指定数据表SELECT column_name FROM table_name; WHERE：筛选符合条件的数据SELECT column_name FROM table_name WHERE column_name = 'value'; AND：用于连接 WHERE 子句中的多个条件SELECT column_name FROM table_name WHERE column_name1 = 'value1' AND column_name2 = 'value2'; OR：用于连接 WHERE 子句中的多个条件，其中至少一个条件必须成立SELECT column_name FROM table_name WHERE column_name1 = 'value1' OR column_name2 = 'value2'; IN：筛选符合指定值中任意一个的数据SELECT column_name FROM table_name WHERE column_name IN ('value1', 'value2', 'value3'); NOT IN：筛选不符合指定值中任意一个的数据SELECT column_name FROM table_name WHERE column_name NOT IN ('value1', 'value2', 'value3'); LIKE：筛选符合指定模式的数据SELECT column_name FROM table_name WHERE column_name LIKE 'pattern'; NOT LIKE：筛选不符合指定模式的数据SELECT column_name FROM table_name WHERE column_name NOT LIKE 'pattern'; BETWEEN：筛选在指定范围内的数据SELECT column_name FROM table_name WHERE column_name BETWEEN 'value1' AND 'value2'; NOT BETWEEN：筛选不在指定范围内的数据SELECT column_name FROM table_name WHERE column_name NOT BETWEEN 'value1' AND 'value2'; ORDER BY：按指定列进行排序SELECT column_name FROM table_name ORDER BY column_name ASC; DESC：按指定列进行降序排序SELECT column_name FROM table_name ORDER BY column_name DESC; ASC：按指定列进行升序排序SELECT column_name FROM table_name ORDER BY column_name ASC; GROUP BY：按指定列进行分组SELECT column_name1, SUM(column_name2) FROM table_name GROUP BY column_name1; HAVING：筛选分组后符合指定条件的数据SELECT column_name1, SUM(column_name2) FROM table_name GROUP BY column_name1 HAVING SUM(column_name2) > 100; JOIN：连接多个数据表SELECT table1.column_name1, table2.column_name2 FROM table1 JOIN table2 ON table1.column_name1 = table2.column_name1; LEFT JOIN：连接左侧数据表，并包括右侧数据表中与左侧数据表中没有匹配项的行SELECT table1.column_name1, table2.column_name2 FROM table1 LEFT JOIN table2 ON table1.column_name1 = table2.column_name1; RIGHT JOIN：右联接SELECT * FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name; FULL OUTER JOIN：全外联接SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name; UNION：合并多个查询结果SELECT column_name FROM table1 UNION SELECT column_name FROM table2; EXISTS：检查子查询结果是否存在SELECT column_name FROM table1 WHERE EXISTS (SELECT column_name FROM table2 WHERE column_name = 'value'); NOT EXISTS：检查子查询结果是否不存在SELECT column_name FROM table1 WHERE NOT EXISTS (SELECT column_name FROM table2 WHERE column_name = 'value'); IN：在一系列值中匹配SELECT column_name FROM table_name WHERE column_name IN ('value1', 'value2', 'value3'); NOT IN：不在一系列值中匹配SELECT column_name FROM table_name WHERE column_name NOT IN ('value1', 'value2', 'value3'); BETWEEN：在一定范围内匹配SELECT column_name FROM table_name WHERE column_name BETWEEN 'value1' AND 'value2'; LIKE：根据通配符匹配SELECT column_name FROM table_name WHERE column_name LIKE '%value%'; NOT LIKE：根据通配符不匹配SELECT column_name FROM table_name WHERE column_name NOT LIKE '%value%'; AVG：计算平均值SELECT AVG(column_name) FROM table_name; COUNT：计算数据行数SELECT COUNT(*) FROM table_name; MAX：计算最大值SELECT MAX(column_name) FROM table_name; MIN：计算最小值SELECT MIN(column_name) FROM table_name; SUM：计算总和SELECT SUM(column_name) FROM table_name; DISTINCT ON：基于指定列的唯一值去重SELECT DISTINCT ON (column_name) column_name, column_name2, column_name3 FROM table_name; CASE：根据条件返回不同的结果SELECT column_name, CASE WHEN column_name = 'value1' THEN 'result1' WHEN column_name = 'value2' THEN 'result2' ELSE 'result3' END AS new_column_name FROM table_name; COALESCE：返回第一个非空值SELECT COALESCE(column_name1, column_name2, column_name3) AS new_column_name FROM table_name; ROW_NUMBER：按照指定列进行分组并排序SELECT column_name, ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name2 DESC) FROM table_name; RANK：按照指定列进行分组并排序，相同值的行具有相同的排名，跳过下一个排名SELECT column_name, RANK() OVER (PARTITION BY column_name ORDER BY column_name2 DESC) FROM table_name; DENSE_RANK：按照指定列进行分组并排序，相同值的行具有相同的排名，不跳过下一个排名SELECT column_name, DENSE_RANK() OVER (PARTITION BY column_name ORDER BY column_name2 DESC) FROM table_name; NTILE：将分组分成指定数量的桶SELECT column_name, NTILE(4) OVER (ORDER BY column_name2 DESC) FROM table_name; LAG：返回当前行之前的指定偏移量的行的列值SELECT column_name, LAG(column_name2, 1) OVER (ORDER BY column_name2) FROM table_name; LEAD：返回当前行之后的指定偏移量的行的列值SELECT column_name, LEAD(column_name2, 1) OVER (ORDER BY column_name2) FROM table_name; FIRST_VALUE：返回分组中第一个行的列值SELECT column_name, FIRST_VALUE(column_name2) OVER (PARTITION BY column_name ORDER BY column_name2) FROM table_name; LAST_VALUE：返回分组中最后一个行的列值SELECT column_name, LAST_VALUE(column_name2) OVER (PARTITION BY column_name ORDER BY column_name2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM table_name; PERCENT_RANK：计算每行的百分位排名SELECT column_name, PERCENT_RANK() OVER (ORDER BY column_name2 DESC) FROM table_name; CONCAT：连接多个字符串值为一个字符串SELECT CONCAT(column_name1, ' ', column_name2) FROM table_name;
