Guida MySQL
Sequel MySQL
connect STANDARD (not SSH)
mysql -uroot -ppassword -hlocalhost
login
quit
logout
select user(), version(), current_date, 3/2;
show databases;
lista dei database
use database_name;
seleziona un database
show tables;
lista delle tabelle
show fields in table_name;
lista dei campi in una tabella
CREATE table table_name (field1 VARCHAR(25), field2 INT(15));
crea tabella con campo stringa e campo intero
DROP table table_name;
distrugge la tabella
INSERT table_name SET field1=value1, field2=value2;
inserisci un record
DELETE FROM table_name WHERE field LIKE value;
cancella un record
UPDATE table_name SET field1=value1 WHERE field2 LIKE value2;
aggiorna un record esistente
ALTER table table_name ADD column field3 float(4,3);
aggiunge un campo alla tabella
SELECT field1, field2 FROM table_name;
display dei campi di una tabella
select * from table_name;
display di tutti i campi di una tabella
select * from table_name where field1 = 'stringa';
select * from table_name where field1 > 20;
select * from table_name where field1 = 'stringa1' and field2 = 'stringa2';
display di campi di una tabella con condizione sui campi
select * from table_name where field1 like 'a%';
select * from table_name where field1 like 'id_';
wildcard: % per una stringa, _ per un singolo carattere
select * from table_name order by field1;
display di campi di una tabella seguendo l'ordine di un campo
es. ssh pccmstib02 -lxdaq
es. mysql -uCMSTIBwriter -pXXXXXXX -hcmstac00
es. mysql -uroby -pXXXXXXX -hdbsrv.pi.infn.it
----------------------------------------------------------------
importa dati in tabella da cvs
Workbanch 
tasto destro mouse: Start Command Line Client
pcpicc08
use roborso;
show tables;
show fields in tibapv;
select * from tibapv;
+----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| modid | int(9) | YES | | NULL | |
| apv | int(1) | YES | | NULL | |
| det | int(1) | YES | | NULL | |
| side | int(1) | YES | | NULL | |
| layer | int(1) | YES | | NULL | |
| l_cl | int(1) | YES | | NULL | |
| l_cr | int(1) | YES | | NULL | |
| l_mc | int(1) | YES | | NULL | |
| l_md | int(1) | YES | | NULL | |
| Tsil_65 | float(4,3) | YES | | NULL | |
| V250_65 | float(4,3) | YES | | NULL | |
| V125_65 | float(4,3) | YES | | NULL | |
| Ileak_65 | float(4,3) | YES | | NULL | |
| Thyb_65 | float(4,3) | YES | | NULL | |
| Tdcu_65 | float(4,3) | YES | | NULL | |
| Tsil_25 | float(4,3) | YES | | NULL | |
| V250_25 | float(4,3) | YES | | NULL | |
| V125_25 | float(4,3) | YES | | NULL | |
| Ileak_25 | float(4,3) | YES | | NULL | |
| Thyb_25 | float(4,3) | YES | | NULL | |
| Tdcu_25 | float(4,3) | YES | | NULL | |
+----------+------------+------+-----+---------+-------+
+-----------+------+------+------+-------+------+------+------+------+---------+---------+---------+----------+---------+---------+---------+---------+---------+----------+---------+---------+
| modid | apv | det | side | layer | l_cl | l_cr | l_mc | l_md | Tsil_65 | V250_65 | V125_65 | Ileak_65 | Thyb_65 | Tdcu_65 | Tsil_25 | V250_25 | V125_25 | Ileak_25 | Thyb_25 | Tdcu_25 |
+-----------+------+------+------+-------+------+------+------+------+---------+---------+---------+----------+---------+---------+---------+---------+---------+----------+---------+---------+
| 369175364 | 4 | 1 | 1 | 4 | 8 | 1 | 3 | 1 | 14.409 | 2.441 | 1.219 | 4.061 | 21.508 | 25.337 | 15.789 | 2.437 | 1.213 | 3.374 | 23.905 | 27.022 |
+-----------+------+------+------+-------+------+------+------+------+---------+---------+---------+----------+---------+---------+---------+---------+---------+----------+---------+---------+
-----------------------------------------------------------------------------
updatetable.c
-----------------------------------------------------------------------------
#include
#include
#include
int main(int argc, char **argv)
{
MYSQL *conn;
FILE *fp;
int nid;
float tsil, v250, v125, ileak, thyb, tdcu;
char str[200];
fp = fopen("dcu-reference-vpsp25.dat", "r");
conn = mysql_init(NULL);
mysql_real_connect(conn, "dbsrv.pi.infn.it", "roby", "password", "roborso", 0, NULL, 0);
while(!feof(fp))
{
fscanf(fp, "%d %f %f %f %f %f %f", &nid, &tsil, &v250, &v125, &ileak, &thyb, &tdcu);
// if(nid==369120285) {
printf("%d %f %f %f %f %f %f\n", nid, tsil, v250, v125, ileak, thyb, tdcu);
sprintf(str, "UPDATE tibapv SET Tsil_25=%f, V250_25=%f, V125_25=%f, Ileak_25=%f, Thyb_25=%f, Tdcu_25=%f WHERE modid like '%d'", tsil, v250, v125, ileak, thyb, tdcu, nid);
mysql_query(conn, str);
// }
}
mysql_close(conn);
fclose(fp);
}
----------------------------------------------------------------------------
scrivifile.c
----------------------------------------------------------------------------
/* scrivi dati su file */
#include
#include
#include
main()
{
FILE *fp;
MYSQL *conn;
MYSQL_RES *result;
MYSQL_ROW row;
int num_fields, i, nid;
nid = 0;
float tsil, v250, v125, ileak, thyb, tdcu;
char str[200];
tsil=1.1;
v250=2.2;
v125=3.3;
ileak=4.4;
thyb=5.5;
tdcu=6.6;
fp = fopen("dump.dat", "w");
conn = mysql_init(NULL);
mysql_real_connect(conn, "dbsrv.pi.infn.it", "roby", "password", "roborso", 0, NULL, 0);
mysql_query(conn, "SELECT * FROM tibapv");
result = mysql_store_result(conn);
num_fields = mysql_num_fields(result);
while ((row = mysql_fetch_row(result)))
{
for(i = 0; i < num_fields; i++)
{
// sprintf(str, "%s", row[i] ? row[i] : "NULL");
sprintf(str, "%s", row[i] ? row[i] : "0.000");
fprintf(fp, "%s ", str);
}
fprintf(fp, "\n");
}
mysql_free_result(result);
mysql_close(conn);
fclose(fp);
}
------------------------------------------------------------------------------------
crea N-ple: readasciiwriteroot-3.C
------------------------------------------------------------------------------------
{
TFile *f = new TFile("tibapv.root","RECREATE");
TTree *T = new TTree("ntuple","new");
Long64_t nlines = T->ReadFile("dump.dat","modid/L:apv/L:det/L:side/L:layer/L:l_cl/L:l_cr/L:l_mc/L:l_md/L:Tsil_65/F:V250_65/F:V125_65/F:Ileak_65/F:Thyb_65/F:Tdcu_65/F:Tsil_25/F:V250_25/F:V125_25/F:Ileak_25/F:Thyb_25/F:Tdcu_25/F");
printf(" found %lld points\n",nlines);
T->Write();
}
------------------------------------------------------------------------------------
root macro to make plots: tib_cooling.C
------------------------------------------------------------------------------------
//#include
{
TFile f("tibapv.root");
TNtuple *nt = (TNtuple*)f.Get("ntuple");
gStyle->SetOptStat(0);
// histo quantities:
int nbins = 40;
int histomax = 1000;
Double_t xmin = -10;
Double_t xmax = 100;
char titlex[40]; char titley[25]; char legenda[25];
sprintf(titlex,"DCUs THybrid (Celsius)");
sprintf(titley,"entries");
sprintf(legenda,"TK-CP: SS1");
TH1F *plot1 = new TH1F("plot1","",nbins,xmin,xmax);
TH1F *plot2 = new TH1F("plot2","",nbins,xmin,xmax);
TH1F *plot3 = new TH1F("plot3","",nbins,xmin,xmax);
plot1->SetMaximum(histomax);
plot2->SetMaximum(histomax);
plot3->SetMaximum(histomax);
TCanvas *c1 = new TCanvas("c1","",0,0,600,600);
c1->SetLogy(1);
plot1->SetLineColor(2);
plot2->SetLineColor(4);
plot3->SetLineColor(3);
plot1->SetLineWidth(4);
plot2->SetLineWidth(4);
plot3->SetLineWidth(4);
nt->Project("plot1","Thyb_65","");
nt->Project("plot2","Thyb_65","(side==2&&layer==3&&l_cl==2)||(side==2&&layer==3&&l_cl==5)");
nt->Project("plot3","Thyb_65","(side==2&&layer==3&&l_cl==1)||(side==2&&layer==3&&l_cl==6)");
plot1->Draw();
plot1->GetYaxis()->SetTitle(titley);
plot1->GetXaxis()->SetTitle(titlex);
plot2->Draw("sames");
plot3->Draw("sames");
TPaveText *pt = new TPaveText(0.6,0.75,0.9,0.9,"brNDC");
pt->SetBorderSize(1);
char plot1txt[40];
char plot2txt[40];
char plot3txt[40];
sprintf(plot1txt,"TIB %3.0f: %3.1f+-%3.1f",(plot1->GetEntries()),(plot1->GetMean()),(plot1->GetRMS()));
sprintf(plot2txt,"TIB 2.3.2 2.3.5 %3.0f: %3.1f+-%3.1f",(plot2->GetEntries()),(plot2->GetMean()),(plot2->GetRMS()));
sprintf(plot3txt,"TIB 2.3.1 2.3.6 %3.0f: %3.1f+-%3.1f",(plot3->GetEntries()),(plot3->GetMean()),(plot3->GetRMS()));
TText *tx0 = pt->AddText(legenda); tx0->SetTextColor(1);
TText *tx1 = pt->AddText(plot1txt);
TText *tx2 = pt->AddText(plot2txt);
TText *tx3 = pt->AddText(plot3txt);
tx1->SetTextColor(2);
tx2->SetTextColor(4);
tx3->SetTextColor(3);
pt->Draw("");
// c1->SaveAs("1014.png");
}
cat dump.dat | awk -F " " '{print $1 " " $16-$10 " " $17-$11 " " $18-$12 " " $19-$13 " " $20-$14 " " $21-$15}'