¶ Introduzione
Un database, nel senso più generale, è una raccolta organizzata di dati. Più specificamente, un database è un sistema elettronico che consente di accedere, manipolare e aggiornare facilmente i dati. In altre parole, un database viene utilizzato da un'organizzazione come mezzo elettronico per memorizzare, gestire e recuperare le informazioni. Il database è una delle pietre miliari dell'IT aziendale e la sua capacità di organizzare, elaborare e gestire le informazioni in modo strutturato e controllato è la chiave di molti aspetti della moderna efficienza aziendale.
¶ Concetti fondamentali dei database
Qui definiremo solo brevemente ogni termine con degli esempi.
¶ Tabelle
La tabella è l'unità di base per la memorizzazione dei dati in un database relazionale. Le tabelle sono composte da colonne e righe. Le colonne sono gli attributi o le qualità che vogliamo esprimere, mentre le righe contengono i dati veri e propri, con uno (o nessun) elemento per riga. Pensate al layout di un foglio di calcolo; è molto simile all'organizzazione logica di una tabella relazionale.
Un semplice esempio delle tabelle del database di una banca commerciale è riportato di seguito.
¶ Relazioni
Le relazioni sono il motivo per cui i database relazionali funzionano così bene. Se si vuole imparare un solo concetto sui database, questo è quello da apprendere. Come dice il nome, le relazioni sono il cuore dei database relazionali. Nei database relazionali, una relazione esiste tra due tabelle quando una di esse ha una chiave esterna che fa riferimento alla chiave primaria dell'altra tabella. (Per saperne di più sulle chiavi esterne e primarie si veda il paragrafo successivo).
Nel diagramma seguente si possono vedere esempi di relazioni. Ad esempio, il campo (colonna) AccountTypeID della tabella AccountTypes fa riferimento alla colonna AccountTypeID della tabella Customer.
¶ Row
Una riga, detta anche record, rappresenta un insieme di dati relativi a un elemento specifico. Ogni record di una tabella ha esattamente la stessa struttura, ma ovviamente dati diversi. Pensate alle righe di un foglio Excel: il concetto di riga in un database è molto simile. Ogni riga di una tabella è costituita da elementi di dati distinti, con uno (o zero) elementi per ogni colonna della tabella. Le righe sono anche chiamate tuple, anche se questo termine non è molto comune.
Di seguito è riportato un esempio di record o riga:
¶ Colonna
Una colonna è un insieme specifico di valori in una tabella dello stesso tipo. Definisce un attributo specifico della tabella o dei dati. Per esempio, possiamo creare una colonna chiamata CUSTOMER_SURNAME in una tabella. Si tratta di una colonna autoesplicativa il cui scopo è memorizzare i cognomi dei clienti, un valore per ogni riga. Ancora una volta, si pensi alle righe di un foglio di calcolo Excel e si avrà un'idea abbastanza precisa di come funzionano le colonne in una tabella relazionale.
¶ Chiave primaria
Una chiave primaria è una colonna speciale o una combinazione di colonne che identifica in modo univoco ogni record (riga) della tabella. La colonna della chiave primaria deve essere unica per ogni riga e non deve contenere valori nulli. Ad esempio, per identificarsi in vari database appartenenti a diversi dipartimenti governativi degli Stati Uniti, viene assegnato e utilizzato un identificatore unico, il numero di previdenza sociale.
La chiave primaria, insieme al concetto di chiave esterna strettamente correlato, è il modo principale in cui vengono definite le relazioni. Le chiavi primarie possono anche essere una combinazione di colonne. Ad esempio, per molte aziende il mese di calendario è un periodo finanziario a breve termine. Pertanto, per identificare in modo univoco qualsiasi periodo, è possibile combinare la colonna del mese e quella dell'anno, ad esempio maggio 2011, per formare una chiave primaria che identifichi in modo univoco ogni singolo periodo finanziario.
¶ Chiave esterna
Non si può parlare dello yin delle chiavi primarie senza lo yang delle chiavi esterne. Le due cose vanno di pari passo. Una chiave primaria definisce in modo univoco un record, mentre una chiave esterna viene utilizzata per fare riferimento allo stesso record da un'altra tabella.
Nel database di una banca commerciale, supponiamo di avere una colonna CUSTOMER_ID come chiave primaria nella tabella CUSTOMER_MASTER. Supponiamo che la stessa tabella contenga anche altre informazioni rilevanti sui clienti in altre colonne, come CUSTOMER_SURNAME, CUSTOMER_FIRSTNAME, CUSTOMER_SOCIAL_SEC_NUMBER, CUSTOMER, CUSTOMER_GENDER e così via.
Abbiamo anche un'altra tabella chiamata LOANS_MASTER per tenere traccia dei prestiti concessi ai clienti della stessa banca. Ora abbiamo bisogno di una sola colonna in questa tabella per identificare il cliente che ha ricevuto un particolare prestito. Possiamo chiamare questa colonna CUSTOMERID, che farà riferimento alla colonna CUSTOMER_ID della tabella CUSTOMER_MASTER. Non è necessario memorizzare tutte le altre informazioni sul cliente (nome, sesso, numero di previdenza sociale, ecc.) nella tabella dei prestiti. Questa è l'eleganza del modello relazionale.
¶ SQL
SQL, Structured Query Language (SQL) è il linguaggio di fatto utilizzato per la gestione e la manipolazione dei dati nei database relazionali. SQL può essere utilizzato per interrogare, inserire, aggiornare e modificare i dati. Tutti i principali database relazionali supportano SQL, il che rende la vita molto più facile agli amministratori di database (DBA), che devono gestire database su diverse piattaforme. La competenza in SQL è di solito una delle prime cose che ogni DBA deve imparare all'inizio della sua carriera. Si noti che alcuni pronunciano SQL come una sola parola, "sequel".
Il linguaggio
SQLè diverso daSQLServer, una piattaforma di database relazionale di Microsoft. Può confondere i principianti a causa dell'uso del termine genericoSQL.
La maggior parte delle piattaformeRDBMScommerciali ha le proprie implementazioniSQLpersonalizzate, ma queste tendono a essere pienamente compatibili con lo standardSQL.
Generalmente, un'istruzione SQL inizia dicendo cosa fare (per esempio, SELECT), poi dice a quale oggetto farlo (per esempio, usando la clausola FROM). Può anche avere una condizione aggiunta alla fine (per esempio, con una clausola WHERE).
SELECT * FROM nome_tabella WHERE nome_colonna = 'criteri';
UPDATE nome_tabella SET nome_colonna_1='valore_1' WHERE nome_colonna_2='valore_2';
SELECTRetrieves data from the databaseINSERTInserts new data into the databaseUPDATEUpdates existing data in the databaseDELETEDeletes existing data from the database
¶ Various Syntax in SQL
¶ Sintassi varie in SQL
¶ SQL SELECT Statement
SELECT column1, column2....columnN FROM table_name;
¶ Clausola SQL DISTINCT
``SQL
SELECT DISTINCT column1, column2....columnN FROM table_name;
### SQL Clausola WHERE
``SQL
SELECT column1, column2....columnN FROM table_name WHERE CONDITION;
¶ Clausola SQL AND/OR
SELECT column1, column2....columnN FROM table_name WHERE CONDITION-1 {AND|OR} CONDIZIONE-2;
¶ SQL IN Clause
SELECT column1, column2....columnN FROM table_name WHERE column_name IN (val-1, val-2,...val-N);
¶ SQL BETWEEN Clause
SELECT column1, column2....columnN FROM table_name WHERE column_name BETWEEN val-1 AND val-2;
¶ Clausola SQL LIKE
SELECT column1, column2....columnN FROM table_name WHERE column_name LIKE { PATTERN };
¶ Clausola SQL ORDER BY
SELECT column1, column2....columnN FROM table_name WHERE CONDITION ORDER BY column_name {ASC|DESC};
¶ SQL GROUP BY Clause
SELECT SUM(nome_colonna) FROM nome_tabella WHERE CONDITION GROUP BY nome_colonna;
¶ Clausola SQL COUNT
SELECT COUNT(nome_colonna) FROM nome_tabella WHERE CONDITION;
¶ Clausola SQL HAVING
SELECT SUM(nome_colonna) FROM nome_tabella WHERE CONDITION GROUP BY nome_colonna HAVING (condizione della funzione aritmica);
¶ SQL INSERT INTO StatementI
INSERT INTO table_name ( column1, column2....columnN) VALUES ( value1, value2....valueN);
### SQL UPDATE Statement
```SQL
UPDATE table_name SET column1 = value1, column2 = value2....columnN=valueN \[ WHERE CONDITION \];
¶ SQL DELETE Statement
DELETE FROM table_name WHERE {CONDITION};
¶ Operatori aritmetici SQL
Supponiamo che la variabile a contenga 10 e la variabile b contenga 20, allora:
| Operatore | Descrizione | Esempio |
|---|---|---|
+(Addizione) |
Aggiunge i valori su entrambi i lati dell'operatore. | a + b darà 30 |
-(Sottrazione) |
Sottrae l'operando di destra dall'operando di sinistra. | a - b darà -10 |
*(Moltiplicazione) |
Moltiplica i valori su entrambi i lati dell'operatore. | a * b darà 200 |
/(Divisione) |
Divide l'operando di sinistra per quello di destra. | b / a darà 2 |
%(Modulo) |
Divide l'operando di sinistra per l'operando di destra e restituisce il resto. | b % a darà 0 |
¶ Operatori di confronto SQL
Supponiamo che la variabile a contenga 10 e la variabile b contenga 20, allora:
| Operatore | Descrizione | Esempio |
|---|---|---|
= |
Verifica se i valori di due operandi sono uguali o no, se sì allora la condizione diventa vera. | (a = b) non è vero. |
!= |
Verifica se i valori di due operandi sono uguali o no, se i valori non sono uguali allora la condizione diventa vera. | (a != b) è vero. |
<> |
Verifica se i valori di due operandi sono uguali o no, se i valori non sono uguali allora la condizione diventa vera. | (a <> b) è vero. |
> |
Verifica se il valore dell'operando di sinistra è maggiore del valore dell'operando di destra, se sì, la condizione diventa vera. | (a > b) non è vero. |
< |
Verifica se il valore dell'operando di sinistra è minore del valore dell'operando di destra, se sì la condizione diventa vera. | (a < b) è vero. |
>= |
Verifica se il valore dell'operando di sinistra è maggiore o uguale al valore dell'operando di destra, se sì allora la condizione diventa vera. | (a >= b) non è vero. |
<= |
Verifica se il valore dell'operando di sinistra è minore o uguale al valore dell'operando di destra, se sì allora la condizione diventa vera. | (a <= b) è vero. |
!< |
Verifica se il valore dell'operando di sinistra non è inferiore al valore dell'operando di destra, se sì la condizione diventa vera. | (a !< b) è falso. |
!> |
Verifica se il valore dell'operando di sinistra non è maggiore del valore dell'operando di destra, se sì allora la condizione diventa vera. | (a !> b) è vero. |
¶ Operatori logici SQL
Ecco un elenco di tutti gli operatori logici disponibili in SQL.
| Operatore | Descrizione |
|---|---|
ALL |
Si usa per confrontare un valore con tutti i valori di un altro insieme di valori. |
AND |
Consente l'esistenza di più condizioni nella clausola WHERE di un'istruzione SQL. |
ANY |
Si usa per confrontare un valore con qualsiasi valore applicabile nell'elenco in base alla condizione. |
BETWEEN |
Si usa per cercare i valori che si trovano all'interno di un insieme di valori, dato il valore minimo e il valore massimo. |
EXISTS |
Si usa per cercare la presenza di una riga in una tabella specificata che soddisfi un determinato criterio. |
IN |
Si usa per confrontare un valore con un elenco di valori letterali specificati. |
LIKE |
Si usa per confrontare un valore con valori simili usando gli operatori jolly. |
NOT |
Inverte il significato dell'operatore logico con cui è utilizzato. Ad esempio: NOT EXISTS, NOT BETWEEN, NOT IN, ecc. È un operatore di negazione. |
OR |
Si usa per combinare più condizioni nella clausola WHERE di un'istruzione SQL. |
IS NULL |
Si usa per confrontare un valore con un valore NULL. |
UNIQUE |
Esamina ogni riga di una tabella specificata per verificarne l'unicità (assenza di duplicati). |
¶ Utilizzare il database con Sirius-ES PLC
L'hmi fornisce i comandi per connettersi e gestire i database con query SQL. Le operazioni sul database possono essere effettuate con javascript. Il sistema può gestire un database SqLite locale. Per utilizzare un database remoto, è necessario installare SirLinux4 come sistema operativo. Qt fornisce driver per diversi database (non tutti).
I database supportati sono MySql e PostGreSQL.
Ecco alcuni esempi di connessione e gestione dei dati.
¶ Aprire la connessione al database
Il server del database deve essere raggiungibile dalla rete; il PLC deve essere connesso e l'indirizzo IP deve essere valido.
var db;
function openConnection()
{
//create a database
db = QSqlDatabase.addDatabase("QMYSQL");
if (db.isValid())
{
db.setHostName("DB_IP_ADDRESS");
db.setPort("DB_PORT");
db.setDatabaseName("DB_NAME");
db.setUserName("DB_USERNAME");
db.setPassword("DB_PASSWORD");
if (db.open())
{
return true;
// or do something with the connection
}
else
{
return false;
//managing connection error or do somethig
}
}
}
¶ Chiudere la connessione al database
Se l'operazione è stata eseguita, la connessione può essere chiusa per poterla rilasciare.
var db;
function closeConnecion()
{
if (typeof db != "undefined")
{
if (db.isOpen())
db.close();
}
}
¶ Gestione degli errori del database
Le operazioni del database possono dare luogo a errori, come l'operazione di apertura o un SQL errato.
Per gestirli, gli errori possono essere recuperati e utilizzati.
var db;
//opening `database connection
//do some database operation
function getDatabaseError()
{
var error = db.lastError();
var text = error.text();
return text;
}
function showError()
{
var database = db.open();
var errormessage = getDatabaseError();
guiInterface("gui::messagebox",database, errorMessage, messageBoxOk);
}
¶ Ottenere l'elenco delle tabelle
Per mostrare il contenuto del database, l'elenco delle tabelle deve essere recuperato con un comando.
var db;
//opening `database connection
var tables = db.tables();
if (typeof tables == "object")
{
for (var i=0; i < tables.length ;i++)
{
var tableName = `tables\[i\];
//do something
}
}
//closing `database connection
¶ Ottenere le colonne della tabella
Ottenere le colonne della tabella deve essere usato per mostrare i dati o fare qualcos'altro.
var db;
//opening `database connection
//column count
var record = db.record("TABLE_NAME");
var record_size = record.count();
for (var field_cnt = 0; field_cnt < record_size ;field_cnt++)
{
var field_name = record.fieldName(field_cnt);
//do something with the column name, like populate a tableView header
}
//closing database connection
¶ Operazioni CRUD
Sono supportate tutte le CRUD (Create, Read, Update, Delete).
Per eseguire le operazioni CRUD, è necessario utilizzare un oggetto QSqlQuery per eseguire la query.
¶ Select
Le Select sono utilizzate per leggere i dati.
var db;
var query;
//opening database connection
query = new QSqlQuery();
query.exec("SELECT * FROM TABLE_NAME);
var rowCount = query.size();
var columnCount = db.record("TABLE_NAME").count();
//getting data
for(var i=0; i<rowCount; i++)
{
//selecting current row
query.seek(i);
for(var j=0; j<columnCount; j++)
{
var cellValue = query.value(j).toString();
//do something with the value
}
}
//closing database connection
¶ Insert, Update, Delete
Le operazioni di scrittura sono diverse da quelle di lettura.
var db;
var query;
//opening database connection
//INSERT, UPDATE or DELETE
var sql = "UPDATE TABLE_NAME SET COLUMN_NAME = 'VALUE' WHERE COLUMN_NAME_2 = VALUE_2";
query = new QSqlQuery();
query.prepare(sql);
query.exec();
//closing database connection