Mysql Kompakt: Unterschied zwischen den Versionen
Zur Navigation springen
Zur Suche springen
(22 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt) | |||
Zeile 1: | Zeile 1: | ||
=Datenbank erstellen= | =Datenbank erstellen= | ||
*create database my_db; | *create database my_db; | ||
+ | =Datenbank löschen= | ||
+ | *drop database my_db; | ||
+ | |||
=In Datenbank wechseln= | =In Datenbank wechseln= | ||
*use my_db; | *use my_db; | ||
Zeile 51: | Zeile 54: | ||
*alter table my_table add column test_p_1000 int(16); | *alter table my_table add column test_p_1000 int(16); | ||
=Spalte entfernen= | =Spalte entfernen= | ||
− | *alter table my_table drop column test_p_1000 | + | *alter table my_table drop column test_p_1000; |
+ | |||
=Select mit Bedingung= | =Select mit Bedingung= | ||
*select * from my_table where tests > 500000; | *select * from my_table where tests > 500000; | ||
Zeile 62: | Zeile 66: | ||
=Backup einer Datenbank= | =Backup einer Datenbank= | ||
*mysqldump my_db > my_db.sql | *mysqldump my_db > my_db.sql | ||
+ | =Backup aller Datenbanken= | ||
+ | *mysqldump --all-database > my_db.sql | ||
+ | |||
=Restore einer Datenbank= | =Restore einer Datenbank= | ||
*mysql my_db < my_db.sql | *mysql my_db < my_db.sql | ||
+ | =Restore einer aller Datenbanken= | ||
+ | *mysql < /tmp/my_db.sql | ||
+ | |||
=Reihe löschen= | =Reihe löschen= | ||
*delete from my_table where kw=11; | *delete from my_table where kw=11; | ||
− | = | + | =Letzter Wert anzeigen= |
*select * from my_table order by KW desc limit 1; | *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; | ||
+ | =Benutzer Rechte entziehen= | ||
+ | *revoke all privileges on covid19.* from 'root'@'%' ; | ||
+ | *flush privileges; | ||
+ | =Passwort ändern= | ||
+ | *UPDATE user SET Password=PASSWORD('suxer') WHERE user='xinux'; | ||
+ | *flush privileges; | ||
+ | |||
+ | =Alle Benutzer anzeigen= | ||
+ | *SELECT user FROM mysql.user; | ||
+ | |||
+ | =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) | ||
+ | =Spalte mit Werte generieren= | ||
+ | *alter table my_table add column prozent float default( postiv / tests * 100); | ||
+ | =Duplizieren einer Tabelle= | ||
+ | *create table my_new_table like my_table; | ||
+ | =Select Into= | ||
+ | *select * into my_table from rki_csv where AnzahlGenesen=1 and Landkreis = "SK Zweibrücken"; | ||
+ | =Datentyp einer Spalte ändern= | ||
+ | *alter table rki_all modify column Datenstand timestamp; | ||
+ | =Name einer Spalte ändern= | ||
+ | *alter table risk_kreis change Quarantaene NF_14 int(11) ; | ||
+ | |||
+ | =Gruppieren= | ||
+ | *select sum(AnzahlFall) , Altersgruppe from rki_csv where Landkreis = 'SK Zweibrücken' group by Altersgruppe ; | ||
+ | =Keine Ahnung wie das heisst?= | ||
+ | *select sum(a1.count),sum(a2.count) from age a1 , age a2 where a1.indx <= "4" and a2.indx >= 5 and a2.indx <= 14 ; | ||
+ | =Spalte hinzufügen= | ||
+ | *alter table impfen add AstraZeneca int(16) after Moderna; | ||
+ | *alter table impfen add RS int(16) first; | ||
+ | =Specials= | ||
+ | *select user from my_auth where user='erwin' and length(password) > 5; | ||
+ | *select user from my_auth where user='erwin' and length(password) = 6 ; | ||
+ | *select user from my_auth where user='erwin' and substring(password,1,1) < 5 ; |
Aktuelle Version vom 12. März 2023, 13:54 Uhr
Datenbank erstellen
- create database my_db;
Datenbank löschen
- drop database my_db;
In Datenbank wechseln
- use my_db;
Tabelle erstellen
- create table my_table (kw int(4), tests int(16), labor int(16) , postiv int(16), prozent float);
Beschreibung der Tabelle anzeigen
- desc my_table;
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
- select * from my_table;
Alle Werte in einer Tabelle löschen
- truncate table my_table;
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
- drop table my_table;
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
Backup aller Datenbanken
- mysqldump --all-database > my_db.sql
Restore einer Datenbank
- mysql my_db < my_db.sql
Restore einer aller Datenbanken
- mysql < /tmp/my_db.sql
Reihe löschen
- delete from my_table where kw=11;
Letzter 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;
Benutzer Rechte entziehen
- revoke all privileges on covid19.* from 'root'@'%' ;
- flush privileges;
Passwort ändern
- UPDATE user SET Password=PASSWORD('suxer') WHERE user='xinux';
- flush privileges;
Alle Benutzer anzeigen
- SELECT user FROM mysql.user;
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)
Spalte mit Werte generieren
- alter table my_table add column prozent float default( postiv / tests * 100);
Duplizieren einer Tabelle
- create table my_new_table like my_table;
Select Into
- select * into my_table from rki_csv where AnzahlGenesen=1 and Landkreis = "SK Zweibrücken";
Datentyp einer Spalte ändern
- alter table rki_all modify column Datenstand timestamp;
Name einer Spalte ändern
- alter table risk_kreis change Quarantaene NF_14 int(11) ;
Gruppieren
- select sum(AnzahlFall) , Altersgruppe from rki_csv where Landkreis = 'SK Zweibrücken' group by Altersgruppe ;
Keine Ahnung wie das heisst?
- select sum(a1.count),sum(a2.count) from age a1 , age a2 where a1.indx <= "4" and a2.indx >= 5 and a2.indx <= 14 ;
Spalte hinzufügen
- alter table impfen add AstraZeneca int(16) after Moderna;
- alter table impfen add RS int(16) first;
Specials
- select user from my_auth where user='erwin' and length(password) > 5;
- select user from my_auth where user='erwin' and length(password) = 6 ;
- select user from my_auth where user='erwin' and substring(password,1,1) < 5 ;