SQL Query Examples and Explanations for the idc_evaluating Database

This document provides a series of SQL queries for the idc_evaluating database, each followed by a concise English description of its goal and usage.

SQL 1 : Retrieve all rows from ns_active_ip where province_code equals '110000'.

select
  *
from
  idc_evaluating.ns_active_ip
where
  province_code = '110000';

SQL 2 : Retrieve rows from ns_active_ip_udp whose dest_ip is one of the specified IP addresses (IP_1, IP_2, IP_3).

select
  *
from
  idc_evaluating.ns_active_ip_udp
where
  dest_ip in ('IP_1', 'IP_2', 'IP_3');

SQL 3 : Same as SQL 2 but includes five destination IP values (IP_1‑IP_5).

select
  *
from
  idc_evaluating.ns_active_ip_udp_record
where
  dest_ip in ('IP_1', 'IP_2', 'IP_3', 'IP_4', 'IP_5');

SQL 4 : Count active IP addresses in ns_active_ip for province code '110000' and house code '1024', naming the result column "活跃IP总数" (Active IP Total).

select
  count(*) as 活跃IP总数
from
  idc_evaluating.ns_active_ip
where
  province_code = '110000'
  and house_code = '1024';

SQL 5 : Delete all rows in ns_active_ip that match the same province and house codes.

delete
from
  idc_evaluating.ns_active_ip
where
  province_code = '110000'
  and house_code = '1024';

SQL 6 : Show the table structure of ns_active_ip_udp using either DESCRIBE or SHOW COLUMNS.

describe idc_evaluating.ns_active_ip_udp;

show columns
from idc_evaluating.ns_active_ip_udp;

SQL 7 : Count rows in ns_active_ip_udp where verify_id = '1024' and status = '0', naming the column "计数" (Count).

select
  count(*) as 计数
from
  idc_evaluating.ns_active_ip_udp
where
  verify_id = '1024'
  and status = '0';

SQL 8 : Retrieve all columns for a single verify_id value.

select
  *
from
  idc_evaluating.ns_active_ip_udp
where
  verify_id = '1024';

SQL 9 : Retrieve all columns for multiple verify_id values (1024, 2048).

select
  *
from
  idc_evaluating.ns_active_ip_udp
where
  verify_id in ('1024', '2048');

SQL 10 : Count rows in ns_active_ip_udp_record for a single verify_id, naming the result "攻击总数" (Attack Total).

select
  count(*) as 攻击总数
from
  idc_evaluating.ns_active_ip_udp_record naiur
where
  verify_id = '1024';

SQL 11 : Same as SQL 10 but for multiple verify_id values.

select
  count(*) as 攻击总数
from
  idc_evaluating.ns_active_ip_udp_record naiur
where
  verify_id in ('1024', '2048');

SQL 12 : Perform an inner join between ns_active_ip_udp (alias naiu) and ns_command_log (alias ncl) on request_id, returning distinct command_id, destination IP, attack count, and status for command IDs between '1024' and '2048'.

select
  distinct ncl.command_id as 指令ID,
  naiu.dest_ip as 目的IP,
  naiu.attacks_count as 攻击次数,
  naiu.status as 攻击状态
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
where
  ncl.command_id between '1024' and '2048';

SQL 13 : Calculate half of the total attack count for the same command‑ID range using SUM and DISTINCT.

select
  distinct SUM(naiu.attacks_count) / 2 as 攻击总数
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
where
  ncl.command_id between '1024' and '2048';

SQL 14 : Retrieve distinct command information, compute adjusted attack counts (90% of original, rounded, plus 1), and return additional fields such as command issuance time and calculated attack time for command IDs between '1024' and '2048'.

select
  distinct
    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 攻击状态,
    ROUND(
      case when naiu.attacks_count is not null then naiu.attacks_count * 0.9 else null end,
      0
    ) + 1 as 日志数据条数
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
where
  ncl.command_id between '1024' and '2048';

Each SQL block is accompanied by a step‑by‑step explanation of the keywords used (e.g., SELECT, DISTINCT, INNER JOIN, WHERE, CASE, ROUND, DATE_ADD) and the logical conditions applied, helping readers understand how to construct and interpret similar queries in a relational database environment.