以下是一些程序员需要掌握的 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_n