14 examples of ready-to-use SQL statements

SQL 1:

Query all rows from the "ns_active_ip" table in the "idc_evaluating" database where the province code is 110000.

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

SQL 2:

Query all rows from the "ns_active_ip_udp" table in the "idc_evaluating" database where the destination IP column value contains 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:

Query all rows from the "ns_active_ip_udp_record" table in the "idc_evaluating" database where the destination IP column value contains the specified IP addresses (IP_1, IP_2, IP_3, IP_4, 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:

Query the total number of active IP addresses that satisfy the condition from the "ns_active_ip" table in the "idc_evaluating" database, where the province code is 110000 and the facility code is 1024. Rename the result column header as "Total Active IP".

select
 count(*) as Total_Active_IP
from
 idc_evaluating.ns_active_ip
where
 province_code = '110000'
 and house_code = '1024';

SQL 5:

Delete all active IP address data from the "ns_active_ip" table in the "idc_evaluating" database that matches the province code 110000 and facility code 1024.

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

SQL 6:

Retrieve the table structure for the "ns_active_ip_udp" table in the "idc_evaluating" database.

describe idc_evaluating.ns_active_ip_udp;

or

show columns
from
idc_evaluating.ns_active_ip_udp;

SQL 7:

Query the count of rows that satisfy the conditions of the verify_id (task ID) and status fields from the "ns_active_ip_udp" table in the "idc_evaluating" database. Rename the result column header as "Count".

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

SQL 8:

Retrieve all rows from the "ns_active_ip_udp" table in the "idc_evaluating" database that satisfy the conditions of a single verify_id (task ID).

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

SQL 9:

Retrieve all rows from the "ns_active_ip_udp" table in the "idc_evaluating" database that satisfy the conditions of multiple verify_id (task ID).

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

SQL 10:

Query the count that satisfies the condition of a single verify_id (task ID) from the "ns_active_ip_udp_record" table in the "idc_evaluating" database. Rename the result column header as "Total Attacks".

select
 count(*) as Total_Attacks
from
 idc_evaluating.ns_active_ip_udp_record naiur
where
 verify_id = '1024';

SQL 11:

Query the count that satisfies the condition of multiple verify_id (task ID) from the "ns_active_ip_udp_record" table in the "idc_evaluating" database. Rename the result column header as "Total Attacks".

select
 count(*) as Total_Attacks
from
 idc_evaluating.ns_active_ip_udp_record naiur
where
 verify_id in ('1024', '2048');

SQL 12:

Retrieve data from two tables using an inner join and return unique values of Instruction ID, Destination IP, Number of Attacks, and Attack Status that satisfy specific conditions. These conditions include the Instruction ID being within a specified range and the request_id matching in both tables.

select
 distinct ncl.command_id as Cmd_id,
 naiu.dest_ip as Dest_IP,
 naiu.attacks_count as Count_Attacks,
 naiu.status as Attack_Status
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:

The purpose of this SQL statement is to get half the value of the total number of attacks within a specified command_id range as the total number of attacks.

select
 distinct SUM(naiu.attacks_count) / 2 as Total_Attacks
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:

The purpose of this SQL statement is to retrieve a series of records within a specific command_id range and calculate the number of attacks multiplied by 0.9, then round it to the nearest integer and add 1. Finally, return these processed records along with their Instruction ID, Issued Time, Destination IP, Number of Attacks, Attack Time, Attack Status, and Number of Log Data.

select
 distinct
    ncl.command_id as Cmd_id,
 naiu.create_time as Cmd_create_time,
 naiu.dest_ip as dest_ip,
 naiu.attacks_count as Count_Attacks,
 DATE_ADD(naiu.create_time, interval 10 minute) as Count_Attacks,
 naiu.status as Attack_Status,
 ROUND(
    case
      when naiu.attacks_count is not null then naiu.attacks_count * 0.9
      else null
    end,
    0
  ) + 1 as log_rows
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';