Essential SQL Queries for the IDC_Evaluating Database – From Simple Selects to Complex Joins

This article provides a collection of practical SQL statements for the idc_evaluating database, covering basic data retrieval, conditional filtering, aggregation, table description, and advanced joins with calculations.

SQL 1 – Select rows by province code

Retrieve all rows from ns_active_ip where the province code equals '110000'.

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

SQL 2 – Select rows by destination IP list

Fetch rows from ns_active_ip_udp whose dest_ip is one of the specified IPs.

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

SQL 3 – Select rows by a larger IP list

Similar to SQL 2 but includes five IP addresses.

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 IPs for a specific province and house

Count the number of active IPs where province code is '110000' and house code is '1024', naming the result column "活跃IP总数".

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

SQL 5 – Delete active IPs for a specific province and house

Remove all rows matching the same criteria as SQL 4.

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

SQL 6 – Show table structure

Display column definitions for 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 by verify_id and status

Count rows where verify_id = '1024' and status = '0', naming the column "计数".

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

SQL 8 – Retrieve all fields for a single verify_id

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

SQL 9 – Retrieve all fields for multiple verify_id values

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

SQL 10 – Count attacks for a single verify_id

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

SQL 11 – Count attacks 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 – Inner join to fetch command ID, destination IP, attack count and status

Join ns_active_ip_udp (alias naiu) with ns_command_log (alias ncl) on request_id, filtering command_id between '1024' and '2048'. Return distinct command ID, destination IP, attack count, and status.

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';

SELECT

: selects data from the database. DISTINCT: ensures each command_id appears only once. ncl.command_id as 指令ID: aliases the column. naiu.dest_ip as 目的IP: selects destination IP. naiu.attacks_count as 攻击次数: selects attack count. naiu.status as 攻击状态: selects attack status. FROM ... AS naiu: defines alias for the first table. INNER JOIN ... AS ncl ON naiu.request_id = ncl.request_id: joins the two tables on matching request IDs. WHERE ncl.command_id BETWEEN '1024' AND '2048': filters command IDs within the range.

SQL 13 – Sum of attack counts divided by two for a command ID range

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';

SELECT DISTINCT SUM(...)/2 as 攻击总数

: calculates half the total attacks, ensuring uniqueness. FROM ... AS naiu: source table for attacks. INNER JOIN ... AS ncl ON naiu.request_id = ncl.request_id: joins command log. WHERE ncl.command_id BETWEEN '1024' AND '2048': limits to the specified command ID range.

SQL 14 – Complex join with calculated log entry count

Retrieve distinct command information, compute attack time (add 10 minutes), and calculate a log‑data count by multiplying attacks_count by 0.9, rounding, and adding 1.

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';

SELECT DISTINCT

: ensures unique rows.

Column list includes command ID, dispatch time, destination IP, attack count, calculated attack time, status, and a rounded log‑data count. DATE_ADD(..., interval 10 minute): adds ten minutes to the dispatch time. ROUND(CASE WHEN ... THEN ... END, 0) + 1: multiplies attack count by 0.9, rounds to the nearest integer, then adds one.

Inner join on request_id links the two tables.

Filter limits command_id to the range 1024‑2048.

These examples demonstrate how to perform common data‑retrieval tasks, manipulate result sets, and combine tables in MySQL‑compatible SQL for the idc_evaluating schema.