Itarvin - 必知必会常用的掌握的 SQL 查询语句

必知必会常用的掌握的 SQL 查询语句

作者 itarvin 阅读 2892 2023-12-05 11:40:28

SELECT：选择数据表中的数据

SELECT column_name1, column_name2 FROM table_name1;

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) & gt 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;

保留分类下的前十条

DELETE FROM article WHERE id not IN (
    SELECT id FROM (
        SELECT t.*, ROW_NUMBER() OVER(PARTITION BY catid ORDER BY id asc) AS row_num
        FROM article t
    ) subquery
    WHERE row_num <= 10
)

本文于 2024-03-01 12:03 由作者进行过修改

本文链接：https://itarvin.com/detail-225.aspx

必知必会常用的掌握的 SQL 查询语句

注册