Datenbank erstellen
In Datenbank wechseln
Tabelle erstellen
- create table my_table (kw int(4), tests int(16), labor int(16) , postiv int(16), prozent float);
Beschreibung der Tabelle anzeigen
Werte in Tabelle einfügen
- insert into my_table values(11,127457,114,7582,5.95);
- insert into my_table values(12,348619,152,23820,6.83);
- insert into my_table values(13,361515,151,31414,8.69);
- insert into my_table values(14,408348,200,36885,9.3);
Wert in einer Reihe ändern
- update my_table set labor=154 where kw=14;
Alle Werte einer Tabelle anzeigen
Alle Werte in einer Tabelle löschen
Tabelle aus CSV Datei füllen
- Beispiel CSV Datei
- cat /var/lib/mysql-files/my.csv
kw,tests,labor,postiv,prozent
11,127457,114,7582,5.95
12,348619,152,23820,6.83
13,361515,151,31414,8.69
14,408348,154,36885,9.03
15,380197,164,30791,8.10
16,331902,168,22082,6.65
17,363890,178,18083,4.97
18,326788,175,12608,3.86
19,403875,182,10755,2.66
20,432666,183,7233,1.67
21,353467,179,5218,1.48
22,405269,178,4310,1.06
23,340986,176,3208,0.94
24,326645,172,2816,0.86
25,387484,175,5309,1.37
26,467004,180,6374,1.36
27,505518,150,3080,0.61
28,509298,177,2989,0.59
29,537334,173,3480,0.65
30,569868,176,4462,0.78
31,573802,161,5551,0.97
- load data infile "/var/lib/mysql-files/my.csv" into table my_table fields terminated by ',' lines terminated by '\n' ignore 1 rows;
Tabelle löschen
Spalte hinzufügen
- alter table my_table add column test_p_1000 int(16);
Spalte entfernen
- alter table my_table drop column test_p_1000;
Select mit Bedingung
- select * from my_table where tests > 500000;
Ausgabe nur gewisser Werte
- select kw,prozent from my_table where tests > 500000;
Order by
- select * from my_table where tests > 500000 order by labor;
Sichern in CSV Datei
- select * into outfile "/var/lib/mysql-files/backup.csv" fields terminated by ',' enclosed by '"' lines terminated by '\n' from my_table;
Backup einer Datenbank
- mysqldump my_db > my_db.sql
Restore einer Datenbank
Reihe löschen
- delete from my_table where kw=11;
Letzer Wert anzeigen
- select * from my_table order by KW desc limit 1;
Benutzer angelegen
- create user 'tuxman'@'localhost' identified by 'Suxer-88';
Benutzer Rechte gewähren
- grant all privileges on my_db.* to 'tuxman'@'localhost' ;
- flush privileges;
Tabelle mit Primären Schlüssel
- create table my_table (kw int(4) primary key , tests int(16), labor int(16) , postiv int(16), prozent float)