tw4ld6 发表于 2024-8-4 15:34:28

14 个拿来就用的 SQL 语句实例!


    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">源自</span> |&nbsp;杰哥的IT之旅</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">作者 |&nbsp;JackTian</span></p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">SQL 1:</strong>从 idc_evaluating 数据库的 ns_active_ip 表中<span style="color: black;">查找</span>省份代码为 110000 的所有行数据。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;*</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">from</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;idc_evaluating.ns_active_ip</p><span style="color: black;">where</span>&nbsp;province_code&nbsp;=&nbsp;<span style="color: black;">110000</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">;</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">SQL 2:</strong>从 idc_evaluating 数据库的 ns_active_ip_udp 表中<span style="color: black;">查找</span>目的 IP 列值<span style="color: black;">包括</span>指定 IP <span style="color: black;">位置</span>(IP_1、IP_2、IP_3)的所有行数据。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;*</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">from</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;idc_evaluating.ns_active_ip_udp</p><span style="color: black;">where</span>&nbsp;dest_ip&nbsp;<span style="color: black;">in</span>&nbsp;(<span style="color: black;">IP_1</span>,&nbsp;<span style="color: black;">IP_2</span>,&nbsp;<span style="color: black;">IP_3</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">);</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">SQL 3:</strong>从 idc_evaluating 数据库的 ns_active_ip_udp_record 表中<span style="color: black;">查找</span>目的 IP 列值<span style="color: black;">包括</span>指定 IP <span style="color: black;">位置</span>(IP_1、IP_2、IP_3、IP_4、IP_5)的所有行数据。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;*</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">from</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">idc_evaluating.ns_active_ip_udp_record</p><span style="color: black;">where</span>&nbsp;dest_ip&nbsp;<span style="color: black;">in</span>&nbsp;(<span style="color: black;">IP_1</span>,&nbsp;<span style="color: black;">IP_2</span>,&nbsp;<span style="color: black;">IP_3</span>,&nbsp;<span style="color: black;">IP_4</span>,&nbsp;<span style="color: black;">IP_5</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">);</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">SQL 4:</strong>从 idc_evaluating 数据库的 ns_active_ip 表中<span style="color: black;">查找</span>省份代码为 110000 且机房代码为 1024 计算满足<span style="color: black;">要求</span>的所有活跃 IP <span style="color: black;">位置</span>数量,并将<span style="color: black;">查找</span>的结果表头命名为"活跃IP总数"。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;count(*)&nbsp;as&nbsp;活跃IP总数</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">from</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;idc_evaluating.ns_active_ip</p><span style="color: black;">where</span>&nbsp;province_code&nbsp;=&nbsp;<span style="color: black;">110000</span>&nbsp;and&nbsp;house_code&nbsp;=&nbsp;<span style="color: black;">1024</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">;</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">SQL 5:</strong>删除 idc_evaluating 数据库的 ns_active_ip 表中匹配省份代码为 110000 且机房代码为 1024 的所有活跃 IP <span style="color: black;">位置</span>数据。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">delete</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">from</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;idc_evaluating.ns_active_ip</p><span style="color: black;">where</span>&nbsp;province_code&nbsp;=&nbsp;<span style="color: black;">110000</span>&nbsp;and&nbsp;house_code&nbsp;=&nbsp;<span style="color: black;">1024</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">;</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">SQL 6:</strong><span style="color: black;">查找</span> idc_evaluating 数据库中的 ns_active_ip_udp 表结构。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">describe&nbsp;idc_evaluating.ns_active_ip_udp;</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">或</span><span style="color: black;">运用</span>以下语句:</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">show&nbsp;columns</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">from</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">idc_evaluating.ns_active_ip_udp;</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">执行以上任一语句后,将返回 ns_active_ip_udp 表的所有列名、数据类型、键信息以及其他属性,<span style="color: black;">经过</span>查看这些字段信息来<span style="color: black;">认识</span>表的结构。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">SQL 7:</strong>从 idc_evaluating 数据库的 ns_active_ip_udp 表中<span style="color: black;">查找</span>满足 verify_id(任务ID)和 status(状态)字段<span style="color: black;">要求</span>的行计数,并将<span style="color: black;">查找</span>的结果表头命名为"计数"。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;count(*)&nbsp;as&nbsp;计数</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">from</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;idc_evaluating.ns_active_ip_udp</p><span style="color: black;">where</span>&nbsp;verify_id&nbsp;=&nbsp;<span style="color: black;">1024</span>&nbsp;and&nbsp;status&nbsp;=&nbsp;<span style="color: black;">0</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">;</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">SQL 8:</strong>从 idc_evaluating 数据库的 ns_active_ip_udp 表中<span style="color: black;">查找</span>满足单个 verify_id(任务ID)所有字段的行数据。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;*</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">from</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;idc_evaluating.ns_active_ip_udp</p><span style="color: black;">where</span>&nbsp;verify_id&nbsp;=&nbsp;<span style="color: black;">1024</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">;</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">SQL 9:</strong>从 idc_evaluating 数据库的 ns_active_ip_udp 表中查询满足多个 verify_id(任务ID)所有字段的行数据。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;*</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">from</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;idc_evaluating.ns_active_ip_udp</p><span style="color: black;">where</span>&nbsp;verify_id&nbsp;<span style="color: black;">in</span>&nbsp;(<span style="color: black;">1024</span>,&nbsp;<span style="color: black;">2048</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">);</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">SQL 10:</strong>从 idc_evaluating 数据库的 ns_active_ip_udp_record 表中<span style="color: black;">查找</span>满足单个 verify_id(任务ID)字段<span style="color: black;">要求</span>的计数,并将<span style="color: black;">查找</span>的结果表头命名为"攻击总数"。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;count(*)&nbsp;as&nbsp;攻击总数</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">from</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;idc_evaluating.ns_active_ip_udp_record&nbsp;naiur</p><span style="color: black;">where</span>&nbsp;verify_id&nbsp;=&nbsp;<span style="color: black;">1024</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">;</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">SQL 11:</strong>从 idc_evaluating 数据库的 ns_active_ip_udp_record 表中<span style="color: black;">查找</span>满足多个 verify_id(任务ID)字段<span style="color: black;">要求</span>的计数,并将<span style="color: black;">查找</span>的结果表头命名为"攻击总数"。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;count(*)&nbsp;as&nbsp;攻击总数</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">from</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">idc_evaluating.ns_active_ip_udp_record&nbsp;naiur</p><span style="color: black;">where</span>&nbsp;verify_id&nbsp;<span style="color: black;">in</span>&nbsp;(<span style="color: black;">1024</span>,&nbsp;<span style="color: black;">2048</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">);</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">SQL 12:</strong><span style="color: black;">经过</span>内连接,从两个表中获取数据,并返回满足特定<span style="color: black;">要求</span>的<span style="color: black;">独一</span>指令 ID、目的IP、攻击次数和攻击状态。这些<span style="color: black;">要求</span><span style="color: black;">包含</span>指令 ID 在指定的范围内,并且这两个表中 request_id <span style="color: black;">必要</span>匹配。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;distinct&nbsp;ncl.command_id&nbsp;as&nbsp;指令ID,</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;naiu.dest_ip&nbsp;as&nbsp;目的IP,</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;naiu.attacks_count&nbsp;as&nbsp;攻击次数,</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;naiu.status&nbsp;as&nbsp;攻击状态</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">from</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;idc_evaluating.ns_active_ip_udp&nbsp;as&nbsp;naiu</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">inner&nbsp;join&nbsp;idc_evaluating.ns_command_log&nbsp;as&nbsp;ncl</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">on</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;naiu.request_id&nbsp;=&nbsp;ncl.request_id</p><span style="color: black;">where</span>&nbsp;ncl.command_id&nbsp;between&nbsp;<span style="color: black;">1024</span>&nbsp;and&nbsp;<span style="color: black;">2048</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">;</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">以下是对该 SQL 语句的<span style="color: black;">仔细</span>解释:</p>SELECT: 用于从数据库中<span style="color: black;">查找</span>数据的命令。DISTINCT: 该关键字用于返回<span style="color: black;">独一</span>的结果。在这个<span style="color: black;">状况</span>下,ncl.command_id的值在结果集中是<span style="color: black;">独一</span>的。ncl.command_id as 指令ID: <span style="color: black;">暗示</span><span style="color: black;">选取</span>ncl表中的command_id列,并将其命名为“指令ID”。naiu.dest_ip as 目的IP: <span style="color: black;">暗示</span><span style="color: black;">选取</span>naiu表中的dest_ip列,并将其命名为“目的IP”。naiu.attacks_count as 攻击次数: <span style="color: black;">暗示</span><span style="color: black;">选取</span>naiu表中的attacks_count列,并将其命名为“攻击次数”。naiu.status as 攻击状态: <span style="color: black;">暗示</span><span style="color: black;">选取</span>naiu表中的status列,并将其命名为“攻击状态”。FROM idc_evaluating.ns_active_ip_udp as naiu: <span style="color: black;">暗示</span>从名为idc_evaluating.ns_active_ip_udp的表中<span style="color: black;">选取</span>数据,并将该表临时命名为naiu。INNER JOIN idc_evaluating.ns_command_log as ncl ON naiu.request_id = ncl.request_id: 这是一个内连接操作,将idc_evaluating.ns_command_log表(临时命名为 ncl)与之前的naiu表连接起来。连接的<span style="color: black;">要求</span>是两个表中的request_id<span style="color: black;">必要</span>相等。WHERE ncl.command_id BETWEEN 1024 AND 2048: 这是一个<span style="color: black;">要求</span>子句,限制只<span style="color: black;">选取</span><span style="color: black;">哪些</span>ncl.command_id字段在1024和2048之间的数据记录。<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">SQL 13:</strong>该 SQL 语句的目的是获取在指定的command_id范围内的攻击次数的一半<span style="color: black;">做为</span>攻击总数。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">distinct&nbsp;SUM(naiu.attacks_count)&nbsp;/&nbsp;2&nbsp;as&nbsp;攻击总数</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">from</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;idc_evaluating.ns_active_ip_udp&nbsp;as&nbsp;naiu</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">inner&nbsp;join&nbsp;idc_evaluating.ns_command_log&nbsp;as&nbsp;ncl&nbsp;&nbsp;</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">on</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;naiu.request_id&nbsp;=&nbsp;ncl.request_id</p><span style="color: black;">where</span>&nbsp;ncl.command_id&nbsp;between&nbsp;<span style="color: black;">1024</span>&nbsp;and&nbsp;<span style="color: black;">2048</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">;</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">以下是对该 SQL 语句的<span style="color: black;">仔细</span>解释:</p>SELECT DISTINCT SUM(naiu.attacks_count) / 2 as 攻击总数: <span style="color: black;">这儿</span><span style="color: black;">运用</span>了SUM函数来计算naiu.attacks_count的总和,<span style="color: black;">而后</span>除以2,最后将结果命名为"攻击总数"。DISTINCT关键字<span style="color: black;">保证</span><span style="color: black;">每一个</span>攻击总数只计算一次。FROM idc_evaluating.ns_active_ip_udp AS naiu: 指定了主<span style="color: black;">查找</span>表,即idc_evaluating.ns_active_ip_udp,并给它一个别名naiu。INNER JOIN idc_evaluating.ns_command_log AS ncl ON naiu.request_id = ncl.request_id: <span style="color: black;">这儿</span><span style="color: black;">运用</span>了一个内连接(INNER JOIN),连接了idc_evaluating.ns_command_log表,并给它一个别名ncl。连接的<span style="color: black;">要求</span>是naiu.request_id字段和ncl.request_id字段<span style="color: black;">必要</span>相等。WHERE ncl.command_id BETWEEN 1024 AND 2048: 这是一个<span style="color: black;">要求</span>子句,限制了只<span style="color: black;">选取</span><span style="color: black;">哪些</span>ncl.command_id在1024和2048之间的数据记录。<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">SQL 14:</strong>该 SQL 语句的目的是获取一系列特定command_id范围内的记录,并计算这些记录的攻击次数乘以0.9,<span style="color: black;">而后</span>四舍五入到<span style="color: black;">近期</span>的整数并加1,最后返回这些处理过的记录以及它们的指令ID、下发时间、目的IP、攻击次数、攻击时间、攻击状态以及日志数据条数。</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">select</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;distinct&nbsp;&nbsp;&nbsp;&nbsp;</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;&nbsp;&nbsp;&nbsp;ncl.command_id&nbsp;as&nbsp;指令ID,</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">naiu.create_time&nbsp;as&nbsp;指令下发时间,</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;naiu.dest_ip&nbsp;as&nbsp;目的IP,</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;naiu.attacks_count&nbsp;as&nbsp;攻击次数,</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;DATE_ADD(naiu.create_time,&nbsp;interval&nbsp;10&nbsp;minute)&nbsp;as&nbsp;攻击时间,</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;naiu.status&nbsp;as&nbsp;攻击状态,</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">ROUND(</p>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: black;">case</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;when&nbsp;naiu.attacks_count&nbsp;is&nbsp;not&nbsp;null&nbsp;<span style="color: black;">then</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;naiu.attacks_count&nbsp;*&nbsp;0.9&nbsp;&nbsp;&nbsp;&nbsp;</p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: black;">else</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;null&nbsp;&nbsp;&nbsp;&nbsp;</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;&nbsp;&nbsp;&nbsp;end,</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;&nbsp;&nbsp;&nbsp;0</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;&nbsp;)&nbsp;+&nbsp;1&nbsp;as&nbsp;日志数据条数</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">from</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;idc_evaluating.ns_active_ip_udp&nbsp;as&nbsp;naiu</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">inner&nbsp;join&nbsp;idc_evaluating.ns_command_log&nbsp;as&nbsp;ncl</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">on</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">&nbsp;naiu.request_id&nbsp;=&nbsp;ncl.request_id</p><span style="color: black;">where</span>&nbsp;ncl.command_id&nbsp;between&nbsp;<span style="color: black;">1024</span>&nbsp;and&nbsp;<span style="color: black;">2048</span>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">;</p>
    <p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">以下是对该 SQL 语句的<span style="color: black;">仔细</span>解释:</p>SELECT DISTINCT: 用于从数据库中<span style="color: black;">选取</span>数据的命令,DISTINCT关键字<span style="color: black;">保证</span><span style="color: black;">每一个</span>指令的ID和其他列的值都是<span style="color: black;">独一</span>的。ncl.command_id as 指令ID, naiu.create_time as 指令下发时间, naiu.dest_ip as 目的IP, naiu.attacks_count as 攻击次数, DATE_ADD(naiu.create_time, INTERVAL 10 MINUTE) AS 攻击时间, naiu.status as 攻击状态: 这些是<span style="color: black;">查找</span>的列,它们来自两个表:idc_evaluating.ns_active_ip_udp(别名为naiu)和idc_evaluating.ns_command_log(别名为ncl)其中,DATE_ADD(naiu.create_time, INTERVAL 10 MINUTE) AS 攻击时间<span style="color: black;">暗示</span>将naiu.create_time的时间加上10分钟,<span style="color: black;">而后</span>命名为"攻击时间"。ROUND(CASE WHEN naiu.attacks_count IS NOT NULL THEN naiu.attacks_count * 0.9 ELSE NULL END, 0) + 1 AS 日志数据条数: 这部分代码计算了攻击次数乘以0.9,<span style="color: black;">而后</span>四舍五入到<span style="color: black;">近期</span>的整数,最后加1。<span style="color: black;">倘若</span>不存在攻击次数,则返回NULL。结果被命名为"日志数据条数"。FROM idc_evaluating.ns_active_ip_udp AS naiu INNER JOIN idc_evaluating.ns_command_log AS ncl ON naiu.request_id = ncl.request_id: 这部分代码执行了一个内连接(INNER JOIN),连接了idc_evaluating.ns_active_ip_udp表(别名为naiu)和idc_evaluating.ns_command_log表(别名为ncl)。连接的<span style="color: black;">要求</span>是两个表中的request_id<span style="color: black;">必要</span>相等。WHERE ncl.command_id BETWEEN 1024 AND 2048: 这是一个<span style="color: black;">要求</span>子句,限制了<span style="color: black;">查找</span>结果只<span style="color: black;">包含</span><span style="color: black;">哪些</span>command_id在1024和2048之间的数据记录。


4zhvml8 发表于 2024-10-6 05:59:53

你的见解真是独到,让我受益良多。
页: [1]
查看完整版本: 14 个拿来就用的 SQL 语句实例!