SQL

DICAS:

Structured Query Language (SQL:2023 ou ISO/IEC 9075:2023)
Os comandos não são case sensitive
Os nomes são case sensitive
Não esqueça do ; nos comandos
Em caso de erro tente interpretar a mensagem de erro

Sintaxe

Data Definition Language (DDL): DATABASE, TABLE, CONSTRAINTS, VIEW, INDEX
- CREATE
- DELETE (DROP)
- UPDATE
- RENAME
Data Manipulation Language (DML)
- INSERT
- UPDATE
- DELETE
Data Query Language (DQL):
- SELECT
  - FROM (JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN)
  - WHERE
  - GROUP BY
  - HAVING
  - ORDER BY (ASC, DESC)
Data Control Language (DCL):
- GRANT
- REVOKE
Transaction Control Language (TCL):
- START (BEGIN)
- TRANSACTION
- COMMIT
- ROLLBACK
Operators
- Comparison: >, >=, <, <=,= ,!= , <>
- Logical: AND, OR, NOT, ALL, ANY, SOME, BETWEEN, IN, LIKE, EXISTS, REGEXP, IS NULL, CASE
- Arithmetic: +, -, *, /, %
- Bitwise: &, |, ^
- Compound: +=, -=, *=, /=, %=
Functions
- Aggregate: MAX, MIN, AVG, SUM, COUNT
- Numeric: ROUND, CEILING, CEIL, FLOOR, ABS, RAND
- String: LENGTH, UPPER, LOWER, LTRIM, RTRIM, TRIM, LEFT, RIGHT, SUBSTRING, LOCATE, REPLACE, CONCAT, CONCAT,
- Dates and Time: NOW, CURDATE, CURTIME, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DAYNAME, MONTHNAME, EXTRACT, DATE_FORMAT, TIME_FORMAT, DATE_ADD, DATE_SUB, DATEDIFF, TIME_TO_SEC
- IF, IFNULL, COALESCE
Stored Procedures
Triggers
Views
Indice
Constraint: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, CREATE INDEX

Dataset

name	address	transmitted	received	time
portal.ifrn.edu.br	10.0.0.100	4	4	2023-02-16 13:41:49
portal.ifrn.edu.br	10.0.0.100	8	4	2023-02-16 13:41:49
portal.ifrn.edu.br	10.0.0.100	4	3	2023-02-16 13:41:49
…	…	…	…	…
www.ifpb.edu.br	200.10.10.12	5	5	2023-02-18 13:41:49

Questões

Quantos pacotes foram recebidos?
Qual é a média de pacotes perdidos?
Qual é a porcentagem de pacotes recebidos do IFPB?
Qual é a porcentagem de pacotes perdidos do IFPB no dia 05/03/2024 entre 09:00 até 18:00?

Veja Normalização de Dados

DDL

Database

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

mysql> CREATE DATABASE monitor_db;
Query OK, 1 row affected (0.00 sec)
 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| monitor_db         |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> CREATE DATABASE monitor_db;
Query OK, 1 row affected (0.00 sec)
 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| monitor_db         |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> DROP DATABASE monitor_db;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

mysql> DROP DATABASE monitor_db;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

Table

mysql> USE monitor_db;
Database changed
 
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| monitor_db |
+------------+
1 row in set (0.00 sec)

mysql> USE monitor_db;
Database changed
 
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| monitor_db |
+------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> CREATE TABLE host (
    ->   id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   name varchar(100) NOT NULL,
    ->   address varchar(100) NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW TABLES;
+----------------------+
| Tables_in_monitor_db |
+----------------------+
| host                 |
+----------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE host (
    ->   id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   name varchar(100) NOT NULL,
    ->   address varchar(100) NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW TABLES;
+----------------------+
| Tables_in_monitor_db |
+----------------------+
| host                 |
+----------------------+
1 row in set (0.00 sec)

erd diagram

mysql> DESCRIBE host;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(100) | NO   |     | NULL    |                |
| address | varchar(100) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> DESCRIBE host;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(100) | NO   |     | NULL    |                |
| address | varchar(100) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> DROP TABLE host;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW TABLES;
+----------------------+
| Tables_in_monitor_db |
+----------------------+
| host                 |
+----------------------+
1 row in set (0.01 sec)

mysql> DROP TABLE host;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW TABLES;
+----------------------+
| Tables_in_monitor_db |
+----------------------+
| host                 |
+----------------------+
1 row in set (0.01 sec)

mysql> RENAME TABLE host TO hosts;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW TABLES;
+----------------------+
| Tables_in_monitor_db |
+----------------------+
| hosts                |
+----------------------+
1 row in set (0.01 sec)

mysql> RENAME TABLE host TO hosts;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW TABLES;
+----------------------+
| Tables_in_monitor_db |
+----------------------+
| hosts                |
+----------------------+
1 row in set (0.01 sec)

mysql> DESCRIBE hosts;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(100) | NO   |     | NULL    |                |
| address | varchar(100) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
 
mysql> ALTER TABLE hosts ADD COLUMN mask varchar(100);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> DESCRIBE hosts;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(100) | NO   |     | NULL    |                |
| address | varchar(100) | NO   |     | NULL    |                |
| mask    | varchar(100) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> DESCRIBE hosts;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(100) | NO   |     | NULL    |                |
| address | varchar(100) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
 
mysql> ALTER TABLE hosts ADD COLUMN mask varchar(100);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> DESCRIBE hosts;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(100) | NO   |     | NULL    |                |
| address | varchar(100) | NO   |     | NULL    |                |
| mask    | varchar(100) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE hosts DROP COLUMN mask;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> DESCRIBE hosts;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(100) | NO   |     | NULL    |                |
| address | varchar(100) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE hosts DROP COLUMN mask;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> DESCRIBE hosts;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(100) | NO   |     | NULL    |                |
| address | varchar(100) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

DML & DQL

INSERT

mysql> INSERT INTO hosts
    -> VALUES (1, 'www.ifpb.edu.br', '200.10.10.10');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO hosts
    -> VALUES (1, 'www.ifpb.edu.br', '200.10.10.10');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO hosts
    ->   (name, address)
    -> VALUES
    ->   ('www.ifrn.edu.br', '200.10.10.11'),
    ->   ('www.ifpb.edu.br', '200.10.10.12');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO hosts
    ->   (name, address)
    -> VALUES
    ->   ('www.ifrn.edu.br', '200.10.10.11'),
    ->   ('www.ifpb.edu.br', '200.10.10.12');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

SELECT

mysql> SELECT * FROM hosts;
+----+-----------------+--------------+
| id | name            | address      |
+----+-----------------+--------------+
|  1 | www.ifpb.edu.br | 200.10.10.10 |
|  2 | www.ifrn.edu.br | 200.10.10.11 |
|  3 | www.ifpb.edu.br | 200.10.10.12 |
+----+-----------------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM hosts;
+----+-----------------+--------------+
| id | name            | address      |
+----+-----------------+--------------+
|  1 | www.ifpb.edu.br | 200.10.10.10 |
|  2 | www.ifrn.edu.br | 200.10.10.11 |
|  3 | www.ifpb.edu.br | 200.10.10.12 |
+----+-----------------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT name, address FROM hosts;
+-----------------+--------------+
| name            | address      |
+-----------------+--------------+
| www.ifpb.edu.br | 200.10.10.10 |
| www.ifrn.edu.br | 200.10.10.11 |
| www.ifpb.edu.br | 200.10.10.12 |
+-----------------+--------------+
3 rows in set (0.00 sec)

mysql> SELECT name, address FROM hosts;
+-----------------+--------------+
| name            | address      |
+-----------------+--------------+
| www.ifpb.edu.br | 200.10.10.10 |
| www.ifrn.edu.br | 200.10.10.11 |
| www.ifpb.edu.br | 200.10.10.12 |
+-----------------+--------------+
3 rows in set (0.00 sec)

ORDER BY

mysql> SELECT
    ->   name, address
    -> FROM
    ->   hosts
    -> ORDER BY
    ->   name;
+-----------------+--------------+
| name            | address      |
+-----------------+--------------+
| www.ifpb.edu.br | 200.10.10.10 |
| www.ifpb.edu.br | 200.10.10.12 |
| www.ifrn.edu.br | 200.10.10.11 |
+-----------------+--------------+
3 rows in set (0.01 sec)

mysql> SELECT
    ->   name, address
    -> FROM
    ->   hosts
    -> ORDER BY
    ->   name;
+-----------------+--------------+
| name            | address      |
+-----------------+--------------+
| www.ifpb.edu.br | 200.10.10.10 |
| www.ifpb.edu.br | 200.10.10.12 |
| www.ifrn.edu.br | 200.10.10.11 |
+-----------------+--------------+
3 rows in set (0.01 sec)

WHERE

mysql> SELECT
    ->   name, address
    -> FROM
    ->   hosts
    -> WHERE
    ->   id = 1;
+-----------------+--------------+
| name            | address      |
+-----------------+--------------+
| www.ifpb.edu.br | 200.10.10.10 |
+-----------------+--------------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   name, address
    -> FROM
    ->   hosts
    -> WHERE
    ->   id = 1;
+-----------------+--------------+
| name            | address      |
+-----------------+--------------+
| www.ifpb.edu.br | 200.10.10.10 |
+-----------------+--------------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   name, address
    -> FROM
    ->   hosts
    -> WHERE
    ->   name LIKE '%ifpb%'
    ->   AND address LIKE '200.%.%.%';
+-----------------+--------------+
| name            | address      |
+-----------------+--------------+
| www.ifpb.edu.br | 200.10.10.10 |
| www.ifpb.edu.br | 200.10.10.12 |
+-----------------+--------------+
2 rows in set (0.00 sec)

mysql> SELECT
    ->   name, address
    -> FROM
    ->   hosts
    -> WHERE
    ->   name LIKE '%ifpb%'
    ->   AND address LIKE '200.%.%.%';
+-----------------+--------------+
| name            | address      |
+-----------------+--------------+
| www.ifpb.edu.br | 200.10.10.10 |
| www.ifpb.edu.br | 200.10.10.12 |
+-----------------+--------------+
2 rows in set (0.00 sec)

LIMIT

mysql> SELECT
    ->   *
    -> FROM
    ->   hosts
    -> LIMIT
    ->   2;
+----+-----------------+--------------+
| id | name            | address      |
+----+-----------------+--------------+
|  1 | www.ifpb.edu.br | 200.10.10.10 |
|  2 | www.ifrn.edu.br | 200.10.10.11 |
+----+-----------------+--------------+
2 rows in set (0.00 sec)

mysql> SELECT
    ->   *
    -> FROM
    ->   hosts
    -> LIMIT
    ->   2;
+----+-----------------+--------------+
| id | name            | address      |
+----+-----------------+--------------+
|  1 | www.ifpb.edu.br | 200.10.10.10 |
|  2 | www.ifrn.edu.br | 200.10.10.11 |
+----+-----------------+--------------+
2 rows in set (0.00 sec)

OFFSET

mysql> SELECT
    ->   *
    -> FROM
    ->   hosts
    -> LIMIT
    ->   2
    -> OFFSET
    ->   1;
+----+-----------------+--------------+
| id | name            | address      |
+----+-----------------+--------------+
|  2 | www.ifrn.edu.br | 200.10.10.11 |
|  3 | www.ifpb.edu.br | 200.10.10.12 |
+----+-----------------+--------------+
2 rows in set (0.00 sec)

mysql> SELECT
    ->   *
    -> FROM
    ->   hosts
    -> LIMIT
    ->   2
    -> OFFSET
    ->   1;
+----+-----------------+--------------+
| id | name            | address      |
+----+-----------------+--------------+
|  2 | www.ifrn.edu.br | 200.10.10.11 |
|  3 | www.ifpb.edu.br | 200.10.10.12 |
+----+-----------------+--------------+
2 rows in set (0.00 sec)

COUNT()

mysql> SELECT
    ->   COUNT(*) AS total
    -> FROM
    ->   hosts;
+-------+
| total |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   COUNT(*) AS total
    -> FROM
    ->   hosts;
+-------+
| total |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

UPDATE

mysql> UPDATE
    ->   hosts
    -> SET
    ->   address = '10.0.0.10'
    -> WHERE
    ->   id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> SELECT * FROM hosts
    WHERE id = 2;
+----+-----------------+-----------+
| id | name            | address   |
+----+-----------------+-----------+
|  2 | www.ifrn.edu.br | 10.0.0.10 |
+----+-----------------+-----------+
1 row in set (0.00 sec)

mysql> UPDATE
    ->   hosts
    -> SET
    ->   address = '10.0.0.10'
    -> WHERE
    ->   id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> SELECT * FROM hosts
    WHERE id = 2;
+----+-----------------+-----------+
| id | name            | address   |
+----+-----------------+-----------+
|  2 | www.ifrn.edu.br | 10.0.0.10 |
+----+-----------------+-----------+
1 row in set (0.00 sec)

mysql> UPDATE
    ->   hosts
    -> SET
    ->   name = 'portal.ifrn.edu.br',
    ->   address = '10.0.0.100'
    -> WHERE
    ->   id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> SELECT
    ->   name, address
    -> FROM
    ->   hosts
    -> WHERE
    ->   id = 2;
+----+--------------------+------------+
| id | name               | address    |
+----+--------------------+------------+
|  2 | portal.ifrn.edu.br | 10.0.0.100 |
+----+--------------------+------------+

mysql> UPDATE
    ->   hosts
    -> SET
    ->   name = 'portal.ifrn.edu.br',
    ->   address = '10.0.0.100'
    -> WHERE
    ->   id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> SELECT
    ->   name, address
    -> FROM
    ->   hosts
    -> WHERE
    ->   id = 2;
+----+--------------------+------------+
| id | name               | address    |
+----+--------------------+------------+
|  2 | portal.ifrn.edu.br | 10.0.0.100 |
+----+--------------------+------------+

DELETE

mysql> SELECT * FROM hosts;
+----+-----------------+--------------+
| id | name            | address      |
+----+-----------------+--------------+
|  1 | www.ifpb.edu.br | 200.10.10.10 |
+----+-----------------+--------------+
 
mysql> DELETE FROM
    ->   hosts
    -> WHERE
    ->   id = 1;
Query OK, 1 row affected (0.01 sec)
 
mysql> SELECT
    ->   name, address
    -> FROM
    ->   hosts
    -> WHERE
    ->   id = 1;
Empty set (0.00 sec)

mysql> SELECT * FROM hosts;
+----+-----------------+--------------+
| id | name            | address      |
+----+-----------------+--------------+
|  1 | www.ifpb.edu.br | 200.10.10.10 |
+----+-----------------+--------------+
 
mysql> DELETE FROM
    ->   hosts
    -> WHERE
    ->   id = 1;
Query OK, 1 row affected (0.01 sec)
 
mysql> SELECT
    ->   name, address
    -> FROM
    ->   hosts
    -> WHERE
    ->   id = 1;
Empty set (0.00 sec)

DICA: Muito cuidado com o DELETE e UPDATE sem o WHERE!

Database Relationships

1:1

Modelo Lógico

erd diagram

Modelo Físico

erd diagram

mysql> CREATE TABLE pings (
    ->   id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> DESCRIBE pings;
+------------+----------+------+-----+-------------------+-------------------+
| Field      | Type     | Null | Key | Default           | Extra             |
+------------+----------+------+-----+-------------------+-------------------+
| id         | int      | NO   | PRI | NULL              | auto_increment    |
| created_at | datetime | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-----------+----------+------+-----+-------------------+-------------------+
2 rows in set (0.01 sec)
 
mysql> CREATE TABLE stats (
    ->   id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   transmitted int NOT NULL,
    ->   received int NOT NULL,
    ->   time float NOT NULL,
    ->   ping_id int NOT NULL REFERENCES pings (id) ON DELETE CASCADE ON UPDATE CASCADE
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> DESCRIBE stats;
+-------------+-------+------+-----+---------+----------------+
| Field       | Type  | Null | Key | Default | Extra          |
+-------------+-------+------+-----+---------+----------------+
| id          | int   | NO   | PRI | NULL    | auto_increment |
| transmitted | int   | NO   |     | NULL    |                |
| received    | int   | NO   |     | NULL    |                |
| time        | float | NO   |     | NULL    |                |
| ping_id     | int   | NO   |     | NULL    |                |
+-------------+-------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> CREATE TABLE pings (
    ->   id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> DESCRIBE pings;
+------------+----------+------+-----+-------------------+-------------------+
| Field      | Type     | Null | Key | Default           | Extra             |
+------------+----------+------+-----+-------------------+-------------------+
| id         | int      | NO   | PRI | NULL              | auto_increment    |
| created_at | datetime | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-----------+----------+------+-----+-------------------+-------------------+
2 rows in set (0.01 sec)
 
mysql> CREATE TABLE stats (
    ->   id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   transmitted int NOT NULL,
    ->   received int NOT NULL,
    ->   time float NOT NULL,
    ->   ping_id int NOT NULL REFERENCES pings (id) ON DELETE CASCADE ON UPDATE CASCADE
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> DESCRIBE stats;
+-------------+-------+------+-----+---------+----------------+
| Field       | Type  | Null | Key | Default | Extra          |
+-------------+-------+------+-----+---------+----------------+
| id          | int   | NO   | PRI | NULL    | auto_increment |
| transmitted | int   | NO   |     | NULL    |                |
| received    | int   | NO   |     | NULL    |                |
| time        | float | NO   |     | NULL    |                |
| ping_id     | int   | NO   |     | NULL    |                |
+-------------+-------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

1:N

Modelo Lógico

erd diagram

Modelo Físico

erd diagram

mysql> CREATE TABLE icmps (
    ->   id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   seq int NOT NULL,
    ->   ttl int NOT NULL,
    ->   time float NOT NULL,
    ->   ping_id int NOT NULL REFERENCES pings (id) ON DELETE CASCADE ON UPDATE CASCADE
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> DESCRIBE icmps;
+---------+-------+------+-----+---------+----------------+
| Field   | Type  | Null | Key | Default | Extra          |
+---------+-------+------+-----+---------+----------------+
| id      | int   | NO   | PRI | NULL    | auto_increment |
| seq     | int   | NO   |     | NULL    |                |
| ttl     | int   | NO   |     | NULL    |                |
| time    | float | NO   |     | NULL    |                |
| ping_id | int   | NO   |     | NULL    |                |
+---------+-------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
 
mysql> CREATE TABLE users (
    ->   id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   name varchar(100) NOT NULL,
    ->   email varchar(100) UNIQUE,
    ->   password varchar(30) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> DESCRIBE users;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(100) | NO   |     | NULL    |                |
| password | varchar(30)  | NO   |     | NULL    |                |
| email    | varchar(100) | YES  | UNI | NULL    |                |
+----------+--------------+------+-----+---------+----------------+4 rows in set (0.00 sec)
 
mysql> ALTER TABLE pings ADD COLUMN host_id int NOT NULL REFERENCES hosts (id) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE pings ADD COLUMN user_id int NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> DESCRIBE pings;
+------------+----------+------+-----+-------------------+-------------------+
| Field      | Type     | Null | Key | Default           | Extra             |
+------------+----------+------+-----+-------------------+-------------------+
| id         | int      | NO   | PRI | NULL              | auto_increment    |
| host_id    | int      | NO   |     | NULL              |                   |
| user_id    | int      | NO   |     | NULL              |                   |
| created_at | datetime | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-----------+----------+------+-----+-------------------+-------------------+
4 rows in set (0.01 sec)
 
mysql> INSERT INTO
    ->   users (name, email, password)
    -> VALUES
    ->   ('Alice', 'alice@email.com', 'secret'),
    ->   ('Bob', 'bob@email.com', 'secret');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO
    ->   pings (host_id, user_id)
    -> VALUES
    ->   (1, 1),
    ->   (2, 1),
    ->   (1, 1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO
    ->   icmps (seq, ttl, time, ping_id)
    -> VALUES
    ->   (1, 54, 74.7, 1),
    ->   (2, 54, 74.0, 1),
    ->   (3, 54, 72.5, 1),
    ->   (1, 54, 43.5, 2),
    ->   (2, 54, 44.6, 2),
    ->   (1, 54, 70.0, 3),
    ->   (2, 54, 80.0, 3),
    ->   (3, 54, 100.0, 3);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO
    ->   stats (transmitted, received, time, ping_id)
    -> VALUES
    ->   (4, 3, 221.2, 1),
    ->   (2, 2, 88.1, 2)
    ->   (3, 3, 250.0, 1);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT
    ->   *
    -> FROM
    ->   pings
    -> INNER JOIN
    ->   stats;
+----+---------+---------+---------------------+----+-------------+----------+-------+---------+
| id | host_id | user_id | created_at          | id | transmitted | received | time  | ping_id |
+----+---------+---------+---------------------+----+-------------+----------+-------+---------+
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  1 |           4 |        3 | 221.2 |       1 |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  1 |           4 |        3 | 221.2 |       1 |
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  1 |           4 |        3 | 221.2 |       1 |
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  2 |           2 |        2 |  88.1 |       2 |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  2 |           2 |        2 |  88.1 |       2 |
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  2 |           2 |        2 |  88.1 |       2 |
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  3 |           3 |        3 |   250 |       1 |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  3 |           3 |        3 |   250 |       1 |
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  3 |           3 |        3 |   250 |       1 |
+----+---------+---------+---------------------+----+-------------+----------+-------+---------+
9 rows in set (0.00 sec)
 
mysql> SELECT
    ->   *
    -> FROM
    ->   pings p
    -> INNER JOIN
    ->   stats s
    -> ON
    ->   s.ping_id = p.id;
+----+---------+---------+---------------------+----+-------------+----------+-------+---------+
| id | host_id | user_id | created_at          | id | transmitted | received | time  | ping_id |
+----+---------+---------+---------------------+----+-------------+----------+-------+---------+
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  1 |           4 |        3 | 221.2 |       1 |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  2 |           2 |        2 |  88.1 |       2 |
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  3 |           3 |        3 |   250 |       1 |
+----+---------+---------+---------------------+----+-------------+----------+-------+---------+
3 rows in set (0.00 sec)
 
mysql> SELECT
    ->   *
    -> FROM
    ->   pings p
    -> INNER JOIN
    ->   icmps i
    -> ON
    ->   i.ping_id = p.id;
+----+---------+---------+---------------------+----+-----+-----+------+---------+
| id | host_id | user_id | created_at          | id | seq | ttl | time | ping_id |
+----+---------+---------+---------------------+----+-----+-----+------+---------+
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  1 |   1 |  54 | 74.7 |       1 |
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  2 |   2 |  54 |   74 |       1 |
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  3 |   3 |  54 | 72.5 |       1 |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  4 |   1 |  54 | 43.5 |       2 |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  5 |   2 |  54 | 44.6 |       2 |
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  6 |   1 |  54 |   70 |       3 |
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  7 |   2 |  54 |   80 |       3 |
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  8 |   3 |  54 |  100 |       3 |
+----+---------+---------+---------------------+----+-----+-----+------+---------+
8 rows in set (0.00 sec)
 
mysql> SELECT
    ->   *
    -> FROM
    ->   pings p
    -> INNER JOIN
    ->   users u
    -> ON
    ->   p.user_id = u.id;
+----+---------+---------+---------------------+----+-------+----------+-----------------+
| id | host_id | user_id | created_at          | id | name  | password | email           |
+----+---------+---------+---------------------+----+-------+----------+-----------------+
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  1 | Alice | secret   | alice@email.com |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  1 | Alice | secret   | alice@email.com |
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  2 | Bob   | secret   | bob@email.com   |
+----+---------+---------+---------------------+----+-------+----------+-----------------+
3 rows in set (0.00 sec)
 
mysql> SELECT
    ->   *
    -> FROM
    ->   pings p
    -> INNER JOIN
    ->   hosts h
    -> ON
    ->   p.host_id = h.id;
+----+---------+---------+---------------------+----+-----------------+--------------+
| id | host_id | user_id | created_at          | id | name            | address      |
+----+---------+---------+---------------------+----+-----------------+--------------+
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  1 | www.ifpb.edu.br | 200.10.10.10 |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  2 | www.ifrn.edu.br | 200.10.10.11 |
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  1 | www.ifpb.edu.br | 200.10.10.10 |
+----+---------+---------+---------------------+----+-----------------+--------------+
3 rows in set (0.00 sec)
 
mysql> SELECT
    ->   h.name,
    ->   p.created_at,
    ->   s.transmitted,
    ->   s.received
    -> FROM
    ->   hosts h
    -> INNER JOIN
    ->   pings p ON p.host_id = h.id
    -> INNER JOIN
    ->   stats s ON s.ping_id = p.id
    -> ORDER BY
    ->   h.name;
+-----------------+---------------------+-------------+----------+
| name            | created_at          | transmitted | received |
+-----------------+---------------------+-------------+----------+
| www.ifpb.edu.br | 2024-03-05 00:54:50 |           4 |        3 |
| www.ifpb.edu.br | 2024-03-05 00:54:50 |           3 |        3 |
| www.ifrn.edu.br | 2024-03-05 00:54:50 |           2 |        2 |
+-----------------+---------------------+-------------+----------+
3 rows in set (0.00 sec)

mysql> CREATE TABLE icmps (
    ->   id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   seq int NOT NULL,
    ->   ttl int NOT NULL,
    ->   time float NOT NULL,
    ->   ping_id int NOT NULL REFERENCES pings (id) ON DELETE CASCADE ON UPDATE CASCADE
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> DESCRIBE icmps;
+---------+-------+------+-----+---------+----------------+
| Field   | Type  | Null | Key | Default | Extra          |
+---------+-------+------+-----+---------+----------------+
| id      | int   | NO   | PRI | NULL    | auto_increment |
| seq     | int   | NO   |     | NULL    |                |
| ttl     | int   | NO   |     | NULL    |                |
| time    | float | NO   |     | NULL    |                |
| ping_id | int   | NO   |     | NULL    |                |
+---------+-------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
 
mysql> CREATE TABLE users (
    ->   id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   name varchar(100) NOT NULL,
    ->   email varchar(100) UNIQUE,
    ->   password varchar(30) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> DESCRIBE users;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(100) | NO   |     | NULL    |                |
| password | varchar(30)  | NO   |     | NULL    |                |
| email    | varchar(100) | YES  | UNI | NULL    |                |
+----------+--------------+------+-----+---------+----------------+4 rows in set (0.00 sec)
 
mysql> ALTER TABLE pings ADD COLUMN host_id int NOT NULL REFERENCES hosts (id) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE pings ADD COLUMN user_id int NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> DESCRIBE pings;
+------------+----------+------+-----+-------------------+-------------------+
| Field      | Type     | Null | Key | Default           | Extra             |
+------------+----------+------+-----+-------------------+-------------------+
| id         | int      | NO   | PRI | NULL              | auto_increment    |
| host_id    | int      | NO   |     | NULL              |                   |
| user_id    | int      | NO   |     | NULL              |                   |
| created_at | datetime | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-----------+----------+------+-----+-------------------+-------------------+
4 rows in set (0.01 sec)
 
mysql> INSERT INTO
    ->   users (name, email, password)
    -> VALUES
    ->   ('Alice', 'alice@email.com', 'secret'),
    ->   ('Bob', 'bob@email.com', 'secret');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO
    ->   pings (host_id, user_id)
    -> VALUES
    ->   (1, 1),
    ->   (2, 1),
    ->   (1, 1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO
    ->   icmps (seq, ttl, time, ping_id)
    -> VALUES
    ->   (1, 54, 74.7, 1),
    ->   (2, 54, 74.0, 1),
    ->   (3, 54, 72.5, 1),
    ->   (1, 54, 43.5, 2),
    ->   (2, 54, 44.6, 2),
    ->   (1, 54, 70.0, 3),
    ->   (2, 54, 80.0, 3),
    ->   (3, 54, 100.0, 3);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO
    ->   stats (transmitted, received, time, ping_id)
    -> VALUES
    ->   (4, 3, 221.2, 1),
    ->   (2, 2, 88.1, 2)
    ->   (3, 3, 250.0, 1);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT
    ->   *
    -> FROM
    ->   pings
    -> INNER JOIN
    ->   stats;
+----+---------+---------+---------------------+----+-------------+----------+-------+---------+
| id | host_id | user_id | created_at          | id | transmitted | received | time  | ping_id |
+----+---------+---------+---------------------+----+-------------+----------+-------+---------+
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  1 |           4 |        3 | 221.2 |       1 |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  1 |           4 |        3 | 221.2 |       1 |
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  1 |           4 |        3 | 221.2 |       1 |
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  2 |           2 |        2 |  88.1 |       2 |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  2 |           2 |        2 |  88.1 |       2 |
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  2 |           2 |        2 |  88.1 |       2 |
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  3 |           3 |        3 |   250 |       1 |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  3 |           3 |        3 |   250 |       1 |
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  3 |           3 |        3 |   250 |       1 |
+----+---------+---------+---------------------+----+-------------+----------+-------+---------+
9 rows in set (0.00 sec)
 
mysql> SELECT
    ->   *
    -> FROM
    ->   pings p
    -> INNER JOIN
    ->   stats s
    -> ON
    ->   s.ping_id = p.id;
+----+---------+---------+---------------------+----+-------------+----------+-------+---------+
| id | host_id | user_id | created_at          | id | transmitted | received | time  | ping_id |
+----+---------+---------+---------------------+----+-------------+----------+-------+---------+
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  1 |           4 |        3 | 221.2 |       1 |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  2 |           2 |        2 |  88.1 |       2 |
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  3 |           3 |        3 |   250 |       1 |
+----+---------+---------+---------------------+----+-------------+----------+-------+---------+
3 rows in set (0.00 sec)
 
mysql> SELECT
    ->   *
    -> FROM
    ->   pings p
    -> INNER JOIN
    ->   icmps i
    -> ON
    ->   i.ping_id = p.id;
+----+---------+---------+---------------------+----+-----+-----+------+---------+
| id | host_id | user_id | created_at          | id | seq | ttl | time | ping_id |
+----+---------+---------+---------------------+----+-----+-----+------+---------+
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  1 |   1 |  54 | 74.7 |       1 |
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  2 |   2 |  54 |   74 |       1 |
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  3 |   3 |  54 | 72.5 |       1 |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  4 |   1 |  54 | 43.5 |       2 |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  5 |   2 |  54 | 44.6 |       2 |
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  6 |   1 |  54 |   70 |       3 |
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  7 |   2 |  54 |   80 |       3 |
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  8 |   3 |  54 |  100 |       3 |
+----+---------+---------+---------------------+----+-----+-----+------+---------+
8 rows in set (0.00 sec)
 
mysql> SELECT
    ->   *
    -> FROM
    ->   pings p
    -> INNER JOIN
    ->   users u
    -> ON
    ->   p.user_id = u.id;
+----+---------+---------+---------------------+----+-------+----------+-----------------+
| id | host_id | user_id | created_at          | id | name  | password | email           |
+----+---------+---------+---------------------+----+-------+----------+-----------------+
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  1 | Alice | secret   | alice@email.com |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  1 | Alice | secret   | alice@email.com |
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  2 | Bob   | secret   | bob@email.com   |
+----+---------+---------+---------------------+----+-------+----------+-----------------+
3 rows in set (0.00 sec)
 
mysql> SELECT
    ->   *
    -> FROM
    ->   pings p
    -> INNER JOIN
    ->   hosts h
    -> ON
    ->   p.host_id = h.id;
+----+---------+---------+---------------------+----+-----------------+--------------+
| id | host_id | user_id | created_at          | id | name            | address      |
+----+---------+---------+---------------------+----+-----------------+--------------+
|  1 |       1 |       1 | 2024-03-05 00:54:50 |  1 | www.ifpb.edu.br | 200.10.10.10 |
|  2 |       2 |       1 | 2024-03-05 00:54:50 |  2 | www.ifrn.edu.br | 200.10.10.11 |
|  3 |       1 |       2 | 2024-03-05 01:49:36 |  1 | www.ifpb.edu.br | 200.10.10.10 |
+----+---------+---------+---------------------+----+-----------------+--------------+
3 rows in set (0.00 sec)
 
mysql> SELECT
    ->   h.name,
    ->   p.created_at,
    ->   s.transmitted,
    ->   s.received
    -> FROM
    ->   hosts h
    -> INNER JOIN
    ->   pings p ON p.host_id = h.id
    -> INNER JOIN
    ->   stats s ON s.ping_id = p.id
    -> ORDER BY
    ->   h.name;
+-----------------+---------------------+-------------+----------+
| name            | created_at          | transmitted | received |
+-----------------+---------------------+-------------+----------+
| www.ifpb.edu.br | 2024-03-05 00:54:50 |           4 |        3 |
| www.ifpb.edu.br | 2024-03-05 00:54:50 |           3 |        3 |
| www.ifrn.edu.br | 2024-03-05 00:54:50 |           2 |        2 |
+-----------------+---------------------+-------------+----------+
3 rows in set (0.00 sec)

N:M

Modelo Lógico

erd diagram

Modelo Físico

erd diagram

mysql> CREATE TABLE tags (
    ->   id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   name varchar(100) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> DESCRIBE tags;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
 
mysql> CREATE TABLE tag_hosts (
    ->   id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   host_id int NOT NULL REFERENCES hosts (id) ON DELETE CASCADE ON UPDATE CASCADE,
    ->   tag_id int NOT NULL REFERENCES tags (id) ON DELETE CASCADE ON UPDATE CASCADE
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> DESCRIBE tag_hosts;
+---------+------+------+-----+---------+----------------+
| Field   | Type | Null | Key | Default | Extra          |
+---------+------+------+-----+---------+----------------+
| id      | int  | NO   | PRI | NULL    | auto_increment |
| host_id | int  | NO   |     | NULL    |                |
| tag_id  | int  | NO   |     | NULL    |                |
+---------+------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
 
mysql> INSERT INTO
    ->   tags (name)
    -> VALUES
    ->   ('Portal'),
    ->   ('Educacional');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO
    ->   tag_hosts (tag_id, host_id)
    -> VALUES
    ->   (1, 1),
    ->   (1, 2),
    ->   (2, 1),
    ->   (2, 2);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> SELECT
    ->   h.name,
    ->   t.name
    -> FROM
    ->   hosts h
    -> INNER JOIN
    ->   tag_hosts th ON th.host_id = h.id
    -> INNER JOIN
    ->   tags t ON t.id = th.tag_id
    -> ORDER BY
    ->   h.name;
+-----------------+-------------+
| name            | name        |
+-----------------+-------------+
| www.ifpb.edu.br | Portal      |
| www.ifpb.edu.br | Educacional |
| www.ifrn.edu.br | Portal      |
| www.ifrn.edu.br | Educacional |
+-----------------+-------------+
4 rows in set (0.00 sec)

mysql> CREATE TABLE tags (
    ->   id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   name varchar(100) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> DESCRIBE tags;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
 
mysql> CREATE TABLE tag_hosts (
    ->   id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ->   host_id int NOT NULL REFERENCES hosts (id) ON DELETE CASCADE ON UPDATE CASCADE,
    ->   tag_id int NOT NULL REFERENCES tags (id) ON DELETE CASCADE ON UPDATE CASCADE
    -> );
Query OK, 0 rows affected (0.02 sec)
 
mysql> DESCRIBE tag_hosts;
+---------+------+------+-----+---------+----------------+
| Field   | Type | Null | Key | Default | Extra          |
+---------+------+------+-----+---------+----------------+
| id      | int  | NO   | PRI | NULL    | auto_increment |
| host_id | int  | NO   |     | NULL    |                |
| tag_id  | int  | NO   |     | NULL    |                |
+---------+------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
 
mysql> INSERT INTO
    ->   tags (name)
    -> VALUES
    ->   ('Portal'),
    ->   ('Educacional');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO
    ->   tag_hosts (tag_id, host_id)
    -> VALUES
    ->   (1, 1),
    ->   (1, 2),
    ->   (2, 1),
    ->   (2, 2);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> SELECT
    ->   h.name,
    ->   t.name
    -> FROM
    ->   hosts h
    -> INNER JOIN
    ->   tag_hosts th ON th.host_id = h.id
    -> INNER JOIN
    ->   tags t ON t.id = th.tag_id
    -> ORDER BY
    ->   h.name;
+-----------------+-------------+
| name            | name        |
+-----------------+-------------+
| www.ifpb.edu.br | Portal      |
| www.ifpb.edu.br | Educacional |
| www.ifrn.edu.br | Portal      |
| www.ifrn.edu.br | Educacional |
+-----------------+-------------+
4 rows in set (0.00 sec)

Relacionamentos

Modelo Lógico

erd diagram

Modelo Físico

erd diagram

Questões do Dataset

Quantos pacotes foram recebidos? 8

mysql> SELECT
    ->   sum(s.transmitted) AS transmitted,
    ->   sum(s.received) AS received,
    ->   sum(s.transmitted - s.received) AS losted
    -> FROM
    ->   hosts h
    -> INNER JOIN
    ->   pings p ON h.id = p.host_id
    -> INNER JOIN
    ->   stats s ON p.id = s.ping_id;
+-------------+----------+--------+
| transmitted | received | losted |
+-------------+----------+--------+
|           9 |        8 |      1 |
+-------------+----------+--------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   sum(s.transmitted) AS transmitted,
    ->   sum(s.received) AS received,
    ->   sum(s.transmitted - s.received) AS losted
    -> FROM
    ->   hosts h
    -> INNER JOIN
    ->   pings p ON h.id = p.host_id
    -> INNER JOIN
    ->   stats s ON p.id = s.ping_id;
+-------------+----------+--------+
| transmitted | received | losted |
+-------------+----------+--------+
|           9 |        8 |      1 |
+-------------+----------+--------+
1 row in set (0.00 sec)

Qual é a média de pacotes perdidos? 0,3

mysql> SELECT
    ->   avg(s.transmitted) AS transmited,
    ->   avg(s.received) AS received,
    ->   avg(s.transmitted - s.received) AS losted
    -> FROM
    ->   hosts h
    -> INNER JOIN
    ->   pings p ON h.id = p.host_id
    -> INNER JOIN
    ->   stats s ON p.id = s.ping_id;
+------------+----------+--------+
| transmited | received | losted |
+------------+----------+--------+
|     3.0000 |   2.6667 | 0.3333 |
+------------+----------+--------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   avg(s.transmitted) AS transmited,
    ->   avg(s.received) AS received,
    ->   avg(s.transmitted - s.received) AS losted
    -> FROM
    ->   hosts h
    -> INNER JOIN
    ->   pings p ON h.id = p.host_id
    -> INNER JOIN
    ->   stats s ON p.id = s.ping_id;
+------------+----------+--------+
| transmited | received | losted |
+------------+----------+--------+
|     3.0000 |   2.6667 | 0.3333 |
+------------+----------+--------+
1 row in set (0.00 sec)

Qual é a porcentagem de pacotes recebidos do IFPB? 85,7%

mysql> SELECT
    ->   sum(s.transmitted) AS transmitted,
    ->   sum(s.received) AS received,
    ->   round(sum(s.received)/sum(s.transmitted), 3) AS losted
    -> FROM
    ->   hosts h
    -> INNER JOIN
    ->   pings p ON h.id = p.host_id
    -> INNER JOIN
    ->   stats s ON p.id = s.ping_id
    -> WHERE
    ->   h.name LIKE '%ifpb%';
+-------------+----------+--------+
| transmitted | received | losted |
+-------------+----------+--------+
|           7 |        6 |  0.857 |
+-------------+----------+--------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   sum(s.transmitted) AS transmitted,
    ->   sum(s.received) AS received,
    ->   round(sum(s.received)/sum(s.transmitted), 3) AS losted
    -> FROM
    ->   hosts h
    -> INNER JOIN
    ->   pings p ON h.id = p.host_id
    -> INNER JOIN
    ->   stats s ON p.id = s.ping_id
    -> WHERE
    ->   h.name LIKE '%ifpb%';
+-------------+----------+--------+
| transmitted | received | losted |
+-------------+----------+--------+
|           7 |        6 |  0.857 |
+-------------+----------+--------+
1 row in set (0.00 sec)

Qual é a porcentagem de pacotes perdidos do IFPB no dia 05/03/2024 entre 00:00 até 06:00? 14,3%

mysql> SELECT
    ->   1 - round(sum(s.received)/sum(s.transmitted), 3) AS losted
    -> FROM
    ->   hosts h
    -> INNER JOIN
    ->   pings p ON h.id = p.host_id
    -> INNER JOIN
    ->   stats s ON p.id = s.ping_id
    -> WHERE
    ->   h.name LIKE '%ifpb%' AND
    ->   p.created_at BETWEEN '2024-03-05 00:00:00' AND '2024-03-05 06:00:00';
+--------+
| losted |
+--------+
|  0.143 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   1 - round(sum(s.received)/sum(s.transmitted), 3) AS losted
    -> FROM
    ->   hosts h
    -> INNER JOIN
    ->   pings p ON h.id = p.host_id
    -> INNER JOIN
    ->   stats s ON p.id = s.ping_id
    -> WHERE
    ->   h.name LIKE '%ifpb%' AND
    ->   p.created_at BETWEEN '2024-03-05 00:00:00' AND '2024-03-05 06:00:00';
+--------+
| losted |
+--------+
|  0.143 |
+--------+
1 row in set (0.00 sec)

p.created_at > '2024-03-05 00:00:00' AND p.created_at < '2024-03-05 06:00:00'

p.created_at > '2024-03-05 00:00:00' AND p.created_at < '2024-03-05 06:00:00'

Backup

$ docker compose exec mysql mysqldump -u root -p monitor_db > database.sql

$ docker compose exec mysql mysqldump -u root -p monitor_db > database.sql

/codes/database/mysql-server/database.sql

-- MySQL dump 10.13  Distrib 8.3.0, for Linux (x86_64)
--
-- Host: localhost    Database: monitor_db
-- ------------------------------------------------------
-- Server version	8.3.0
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
--
-- Table structure for table `hosts`
--
 
DROP TABLE IF EXISTS `hosts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `hosts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `address` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `hosts`
--
 
LOCK TABLES `hosts` WRITE;
/*!40000 ALTER TABLE `hosts` DISABLE KEYS */;
INSERT INTO `hosts` VALUES (1,'www.ifpb.edu.br','200.10.10.10'),(2,'www.ifrn.edu.br','200.10.10.11'),(3,'www.ifpb.edu.br','200.10.10.12');
/*!40000 ALTER TABLE `hosts` ENABLE KEYS */;
UNLOCK TABLES;
 
--
-- Table structure for table `icmps`
--
 
DROP TABLE IF EXISTS `icmps`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `icmps` (
  `id` int NOT NULL AUTO_INCREMENT,
  `seq` int NOT NULL,
  `ttl` int NOT NULL,
  `time` float NOT NULL,
  `ping_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `icmps`
--
 
LOCK TABLES `icmps` WRITE;
/*!40000 ALTER TABLE `icmps` DISABLE KEYS */;
INSERT INTO `icmps` VALUES (1,1,54,74.7,1),(2,2,54,74,1),(3,3,54,72.5,1),(4,1,54,43.5,2),(5,2,54,44.6,2),(6,1,54,70,3),(7,2,54,80,3),(8,3,54,100,3);
/*!40000 ALTER TABLE `icmps` ENABLE KEYS */;
UNLOCK TABLES;
 
--
-- Table structure for table `pings`
--
 
DROP TABLE IF EXISTS `pings`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `host_id` int NOT NULL,
  `user_id` int NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `pings`
--
 
LOCK TABLES `pings` WRITE;
/*!40000 ALTER TABLE `pings` DISABLE KEYS */;
INSERT INTO `pings` VALUES (1,1,1,'2024-03-05 00:54:50'),(2,2,1,'2024-03-05 00:54:50'),(3,1,2,'2024-03-05 01:49:36');
/*!40000 ALTER TABLE `pings` ENABLE KEYS */;
UNLOCK TABLES;
 
--
-- Table structure for table `stats`
--
 
DROP TABLE IF EXISTS `stats`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `stats` (
  `id` int NOT NULL AUTO_INCREMENT,
  `transmitted` int NOT NULL,
  `received` int NOT NULL,
  `time` float NOT NULL,
  `ping_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `stats`
--
 
LOCK TABLES `stats` WRITE;
/*!40000 ALTER TABLE `stats` DISABLE KEYS */;
INSERT INTO `stats` VALUES (1,4,3,221.2,1),(2,2,2,88.1,2),(3,3,3,250,1);
/*!40000 ALTER TABLE `stats` ENABLE KEYS */;
UNLOCK TABLES;
 
--
-- Table structure for table `tag_hosts`
--
 
DROP TABLE IF EXISTS `tag_hosts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tag_hosts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `host_id` int NOT NULL,
  `tag_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `tag_hosts`
--
 
LOCK TABLES `tag_hosts` WRITE;
/*!40000 ALTER TABLE `tag_hosts` DISABLE KEYS */;
INSERT INTO `tag_hosts` VALUES (1,1,1),(2,2,1),(3,1,2),(4,2,2);
/*!40000 ALTER TABLE `tag_hosts` ENABLE KEYS */;
UNLOCK TABLES;
 
--
-- Table structure for table `tags`
--
 
DROP TABLE IF EXISTS `tags`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tags` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `tags`
--
 
LOCK TABLES `tags` WRITE;
/*!40000 ALTER TABLE `tags` DISABLE KEYS */;
INSERT INTO `tags` VALUES (1,'Portal'),(2,'Educacional');
/*!40000 ALTER TABLE `tags` ENABLE KEYS */;
UNLOCK TABLES;
 
--
-- Table structure for table `users`
--
 
DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `password` varchar(30) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `users`
--
 
LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,'Alice','secret','alice@email.com'),(2,'Bob','secret','bob@email.com');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
-- Dump completed on 2024-03-05  2:01:26

/codes/database/mysql-server/database.sql

-- MySQL dump 10.13  Distrib 8.3.0, for Linux (x86_64)
--
-- Host: localhost    Database: monitor_db
-- ------------------------------------------------------
-- Server version	8.3.0
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
--
-- Table structure for table `hosts`
--
 
DROP TABLE IF EXISTS `hosts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `hosts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `address` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `hosts`
--
 
LOCK TABLES `hosts` WRITE;
/*!40000 ALTER TABLE `hosts` DISABLE KEYS */;
INSERT INTO `hosts` VALUES (1,'www.ifpb.edu.br','200.10.10.10'),(2,'www.ifrn.edu.br','200.10.10.11'),(3,'www.ifpb.edu.br','200.10.10.12');
/*!40000 ALTER TABLE `hosts` ENABLE KEYS */;
UNLOCK TABLES;
 
--
-- Table structure for table `icmps`
--
 
DROP TABLE IF EXISTS `icmps`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `icmps` (
  `id` int NOT NULL AUTO_INCREMENT,
  `seq` int NOT NULL,
  `ttl` int NOT NULL,
  `time` float NOT NULL,
  `ping_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `icmps`
--
 
LOCK TABLES `icmps` WRITE;
/*!40000 ALTER TABLE `icmps` DISABLE KEYS */;
INSERT INTO `icmps` VALUES (1,1,54,74.7,1),(2,2,54,74,1),(3,3,54,72.5,1),(4,1,54,43.5,2),(5,2,54,44.6,2),(6,1,54,70,3),(7,2,54,80,3),(8,3,54,100,3);
/*!40000 ALTER TABLE `icmps` ENABLE KEYS */;
UNLOCK TABLES;
 
--
-- Table structure for table `pings`
--
 
DROP TABLE IF EXISTS `pings`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `host_id` int NOT NULL,
  `user_id` int NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `pings`
--
 
LOCK TABLES `pings` WRITE;
/*!40000 ALTER TABLE `pings` DISABLE KEYS */;
INSERT INTO `pings` VALUES (1,1,1,'2024-03-05 00:54:50'),(2,2,1,'2024-03-05 00:54:50'),(3,1,2,'2024-03-05 01:49:36');
/*!40000 ALTER TABLE `pings` ENABLE KEYS */;
UNLOCK TABLES;
 
--
-- Table structure for table `stats`
--
 
DROP TABLE IF EXISTS `stats`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `stats` (
  `id` int NOT NULL AUTO_INCREMENT,
  `transmitted` int NOT NULL,
  `received` int NOT NULL,
  `time` float NOT NULL,
  `ping_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `stats`
--
 
LOCK TABLES `stats` WRITE;
/*!40000 ALTER TABLE `stats` DISABLE KEYS */;
INSERT INTO `stats` VALUES (1,4,3,221.2,1),(2,2,2,88.1,2),(3,3,3,250,1);
/*!40000 ALTER TABLE `stats` ENABLE KEYS */;
UNLOCK TABLES;
 
--
-- Table structure for table `tag_hosts`
--
 
DROP TABLE IF EXISTS `tag_hosts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tag_hosts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `host_id` int NOT NULL,
  `tag_id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `tag_hosts`
--
 
LOCK TABLES `tag_hosts` WRITE;
/*!40000 ALTER TABLE `tag_hosts` DISABLE KEYS */;
INSERT INTO `tag_hosts` VALUES (1,1,1),(2,2,1),(3,1,2),(4,2,2);
/*!40000 ALTER TABLE `tag_hosts` ENABLE KEYS */;
UNLOCK TABLES;
 
--
-- Table structure for table `tags`
--
 
DROP TABLE IF EXISTS `tags`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `tags` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `tags`
--
 
LOCK TABLES `tags` WRITE;
/*!40000 ALTER TABLE `tags` DISABLE KEYS */;
INSERT INTO `tags` VALUES (1,'Portal'),(2,'Educacional');
/*!40000 ALTER TABLE `tags` ENABLE KEYS */;
UNLOCK TABLES;
 
--
-- Table structure for table `users`
--
 
DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `password` varchar(30) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `users`
--
 
LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,'Alice','secret','alice@email.com'),(2,'Bob','secret','bob@email.com');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
-- Dump completed on 2024-03-05  2:01:26

mysql> select table_name as "Table Name", table_rows  AS "Row Count" FROM information_schema.tables WHERE table_schema = 'monitor_db';
+------------+-----------+
| Table Name | Row Count |
+------------+-----------+
| hosts      |         3 |
| icmps      |         8 |
| pings      |         3 |
| stats      |         3 |
| tag_hosts  |         4 |
| tags       |         2 |
| users      |         2 |
+------------+-----------+

mysql> select table_name as "Table Name", table_rows  AS "Row Count" FROM information_schema.tables WHERE table_schema = 'monitor_db';
+------------+-----------+
| Table Name | Row Count |
+------------+-----------+
| hosts      |         3 |
| icmps      |         8 |
| pings      |         3 |
| stats      |         3 |
| tag_hosts  |         4 |
| tags       |         2 |
| users      |         2 |
+------------+-----------+

Referências

SQL Roadmap
SQL Complete Guide
enochtangg/quick-SQL-cheatsheet
SQL Tutorial: W3Schools e TutorialsPoint
SQL cheat sheet: Websitesetup, ZeroTurnaround, cse.unl.edu, hofmannsven, sqltutorial
SQlite Online
What is the best way to learn SQL?
Databases
- SQlite: SQL, Datatypes
- MySQL: Datatypes, Functions, Operators
- Oracle
Normalização
- Description of the database normalization basics
- Database normalization
Dataset
- Portal
- Samples
Ferramentas
- DBeaver

Editar esta página