Mysql Script: Unterschied zwischen den Versionen

Aus Xinux Wiki
Zur Navigation springen Zur Suche springen
Die Seite wurde neu angelegt: „= Dynamische Webseiten mit PHP und MySQL – Prinzip = == Einführung == * Dynamische Webseiten generieren Inhalte zur Laufzeit, abhängig von Benutzereingabe…“
 
Keine Bearbeitungszusammenfassung
 
(2 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 1: Zeile 1:
= Dynamische Webseiten mit PHP und MySQL – Prinzip =


== Einführung ==
* Dynamische Webseiten generieren Inhalte zur Laufzeit, abhängig von Benutzereingaben oder Datenbankinformationen.
* PHP als serverseitige Skriptsprache verarbeitet Anfragen und interagiert mit einer MySQL/MariaDB-Datenbank.
* Im Gegensatz zu statischen HTML-Seiten können Inhalte aktualisiert, gefiltert und personalisiert werden.


== Architektur einer dynamischen Webseite ==
== Datenbankverwaltung ==
* Ein typischer Aufbau besteht aus drei Hauptkomponenten:
Datenbank erstellen.
** '''Client''' – Webbrowser sendet Anfragen an den Server.
* create database my_db;
** '''Webserver''' – Nginx oder Apache verarbeitet PHP-Skripte.
** '''Datenbank''' – MySQL/MariaDB speichert und liefert Inhalte.


* Ablauf einer Anfrage:
Datenbank löschen.
** Nutzer ruft eine URL auf → Anfrage wird an den Webserver gesendet.
* drop database my_db;
** Webserver führt das PHP-Skript aus → PHP verarbeitet Eingaben.
** PHP stellt eine Verbindung zur MySQL-Datenbank her und holt Daten.
** PHP generiert HTML-Code basierend auf den Datenbankinhalten.
** Der HTML-Code wird an den Browser gesendet und angezeigt.


== PHP mit MySQL verbinden ==
In eine Datenbank wechseln.
* Verbindung zur Datenbank herstellen:
* use my_db;
<pre>
<?php
$servername = "localhost";
$username = "root";
$password = "passwort";
$dbname = "meinedatenbank";


$conn = new mysqli($servername, $username, $password, $dbname);
== Tabellenverwaltung ==
Tabelle mit mehreren Spalten erstellen.
* create table my_table (kw int(4), tests int(16), labor int(16), postiv int(16), prozent float);


if ($conn->connect_error) {
Beschreibung einer Tabelle anzeigen.
    die("Verbindung fehlgeschlagen: " . $conn->connect_error);
* desc my_table;
}
echo "Erfolgreich verbunden";
?>
</pre>


== Datenbankabfragen mit PHP ==
Tabelle löschen.
* Daten in die Datenbank einfügen:
* drop table my_table;
<pre>
<?php
$sql = "INSERT INTO nutzer (name, email) VALUES ('Max', 'max@example.com')";
if ($conn->query($sql) === TRUE) {
    echo "Neuer Datensatz erfolgreich hinzugefügt";
} else {
    echo "Fehler: " . $conn->error;
}
?>
</pre>


* Daten aus der Datenbank abrufen:
Alle Werte in einer Tabelle löschen.
<pre>
* truncate table my_table;
<?php
$sql = "SELECT id, name, email FROM nutzer";
$result = $conn->query($sql);


if ($result->num_rows > 0) {
== Daten einfügen und bearbeiten ==
    while($row = $result->fetch_assoc()) {
Werte in eine Tabelle einfügen.
        echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"] . "<br>";
* insert into my_table values(11,127457,114,7582,5.95);
    }
} else {
    echo "Keine Ergebnisse";
}
?>
</pre>


== Formulare und Benutzereingaben ==
Wert in einer Reihe ändern.
* PHP ermöglicht Interaktion durch Formulare.
* update my_table set labor=154 where kw=14;
<pre>
<form method="post" action="verarbeitung.php">
    Name: <input type="text" name="name"><br>
    Email: <input type="email" name="email"><br>
    <input type="submit" value="Absenden">
</form>
</pre>


* Verarbeiten der Eingaben in PHP:
Reihe aus der Tabelle löschen.
<pre>
* delete from my_table where kw=11;
<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $name = $_POST["name"];
    $email = $_POST["email"];
   
    $sql = "INSERT INTO nutzer (name, email) VALUES ('$name', '$email')";
    if ($conn->query($sql) === TRUE) {
        echo "Daten erfolgreich gespeichert";
    } else {
        echo "Fehler: " . $conn->error;
    }
}
?>
</pre>


== Sicherheitsaspekte ==
== Daten abfragen ==
* SQL-Injections verhindern:
Alle Werte einer Tabelle anzeigen.
<pre>
* select * from my_table;
<?php
$stmt = $conn->prepare("INSERT INTO nutzer (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
$stmt->execute();
?>
</pre>
* Cross-Site Scripting (XSS) vermeiden:
<pre>
<?php
echo htmlspecialchars($row["name"]);
?>
</pre>


== Erweiterte Abfragen und Performance-Optimierung ==
Bestimmte Spalten ausgeben, wenn eine Bedingung erfüllt ist.
* Indexierung für schnellere Abfragen:
* select kw, prozent from my_table where tests > 500000;
* create index idx_kw on my_table(kw);
* explain select * from my_table where kw=15;


* View für häufig genutzte Abfragen:
Sortierte Ausgabe der Ergebnisse.
* create view kw_statistik as select kw, sum(tests) as gesamt_tests from my_table group by kw;
* select * from my_table where tests > 500000 order by labor;


== Mehr Datensicherheit und Transaktionen ==
Letzten Wert aus der Tabelle ausgeben.
* Sperren und Transaktionen für Datenintegrität:
* select * from my_table order by kw desc limit 1;
* start transaction;
* update my_table set labor=200 where kw=15;
* commit; *(statt commit auch rollback möglich)*


== Fortgeschrittene Benutzerverwaltung ==
== CSV-Import und -Export ==
* Benutzer mit eingeschränkten Rechten:
Daten aus einer CSV-Datei in die Tabelle laden.
* create user 'statuser'@'localhost' identified by 'safe123';
* load data infile "/var/lib/mysql-files/my.csv" into table my_table fields terminated by ',' lines terminated by '\n' ignore 1 rows;
* grant select on my_db.* to 'statuser'@'localhost';


== Dynamische Berechnungen in SQL ==
Daten aus einer Tabelle als CSV-Datei speichern.
* SUM(), AVG() und andere Aggregatfunktionen:
* select * into outfile "/var/lib/mysql-files/backup.csv" fields terminated by ',' enclosed by '"' lines terminated by '\n' from my_table;
* select avg(prozent) from my_table where tests > 500000;


* CASE-Anweisungen für bedingte Berechnungen:
== Backup und Wiederherstellung ==
* select kw, case when prozent > 5 then 'hoch' else 'niedrig' end as risiko from my_table;
Backup einer Datenbank erstellen.
* mysqldump my_db > my_db.sql;


== Triggers und Events für Automatisierungen ==
Backup aller Datenbanken erstellen.
* Automatische Protokollierung bei INSERT:
* mysqldump --all-databases > all_dbs.sql;
 
Backup einer einzelnen Datenbank wiederherstellen.
* mysql my_db < my_db.sql;
 
Backup aller Datenbanken wiederherstellen.
* mysql < all_dbs.sql;
 
== Benutzerverwaltung ==
Neuen Benutzer anlegen.
* create user 'tuxman'@'localhost' identified by 'Suxer-88';
 
Benutzerrechte für eine Datenbank vergeben.
* grant all privileges on my_db.* to 'tuxman'@'localhost';
* flush privileges;
 
Benutzerrechte entziehen.
* revoke all privileges on my_db.* from 'tuxman'@'localhost';
* flush privileges;
 
Passwort eines Benutzers ändern.
* update user set password=password('suxer') where user='tuxman';
* flush privileges;
 
Liste aller Benutzer ausgeben.
* select user from mysql.user;
 
== Erweiterte SQL-Funktionen ==
Tabelle mit Primärschlüssel erstellen.
* create table my_table (kw int(4) primary key, tests int(16), labor int(16), postiv int(16), prozent float);
 
Neue Spalte zur Tabelle hinzufügen.
* alter table my_table add column test_p_1000 int(16);
 
Spalte aus einer Tabelle entfernen.
* alter table my_table drop column test_p_1000;
 
Berechnete Spalte hinzufügen.
* alter table my_table add column prozent float default (postiv / tests * 100);
 
Tabelle duplizieren.
* create table my_new_table like my_table;
 
Datentyp einer Spalte ändern.
* alter table my_table modify column prozent double;
 
Spaltennamen ändern.
* alter table my_table change prozent anteil float;
 
== Gruppierte Abfragen ==
Summenberechnung nach Gruppen.
* select sum(AnzahlFall), Altersgruppe from rki_csv where Landkreis = 'SK Zweibrücken' group by Altersgruppe;
 
== Automatisierung mit Triggern und Events ==
Automatische Protokollierung von Einträgen aktivieren.
* create trigger log_insert after insert on my_table for each row insert into log_table (aktion, zeit) values ('INSERT', now());
* create trigger log_insert after insert on my_table for each row insert into log_table (aktion, zeit) values ('INSERT', now());


* Zeitgesteuerte Events:
Regelmäßige Bereinigung alter Daten durch ein Event.
* create event cleanup_old_entries on schedule every 1 day do delete from my_table where kw < 10;
* create event cleanup_old_entries on schedule every 1 day do delete from my_table where kw < 10;


== JSON-Unterstützung in MySQL 5.7+ und MariaDB ==
== JSON-Unterstützung in MySQL 5.7+ und MariaDB ==
* Speichern von JSON-Daten:
JSON-Daten in einer Tabelle speichern.
* create table json_test (id int auto_increment primary key, daten json);
* create table json_test (id int auto_increment primary key, daten json);


* Abfragen innerhalb von JSON-Feldern:
Daten aus JSON-Feld extrahieren.
* select json_extract(daten, '$.name') from json_test;
* select json_extract(daten, '$.name') from json_test;


== Fazit ==
== Fazit ==
* Dynamische Webseiten ermöglichen interaktive und datenbankgestützte Anwendungen.
Dieses Skript fasst die wichtigsten MySQL-Befehle kompakt zusammen. Die vorgestellten Befehle ermöglichen effizientes Datenbankmanagement. Automatisierung und Optimierung können durch Trigger, Events und JSON-Unterstützung erweitert werden.
* PHP verarbeitet Benutzeranfragen und stellt Daten aus MySQL bereit.
* Sicherheitsmaßnahmen wie Prepared Statements und Validierungen sind essenziell.
* Durch Kombination mit CSS und JavaScript können erweiterte Funktionen umgesetzt werden.

Aktuelle Version vom 15. März 2025, 10:25 Uhr


Datenbankverwaltung

Datenbank erstellen.

  • create database my_db;

Datenbank löschen.

  • drop database my_db;

In eine Datenbank wechseln.

  • use my_db;

Tabellenverwaltung

Tabelle mit mehreren Spalten erstellen.

  • create table my_table (kw int(4), tests int(16), labor int(16), postiv int(16), prozent float);

Beschreibung einer Tabelle anzeigen.

  • desc my_table;

Tabelle löschen.

  • drop table my_table;

Alle Werte in einer Tabelle löschen.

  • truncate table my_table;

Daten einfügen und bearbeiten

Werte in eine Tabelle einfügen.

  • insert into my_table values(11,127457,114,7582,5.95);

Wert in einer Reihe ändern.

  • update my_table set labor=154 where kw=14;

Reihe aus der Tabelle löschen.

  • delete from my_table where kw=11;

Daten abfragen

Alle Werte einer Tabelle anzeigen.

  • select * from my_table;

Bestimmte Spalten ausgeben, wenn eine Bedingung erfüllt ist.

  • select kw, prozent from my_table where tests > 500000;

Sortierte Ausgabe der Ergebnisse.

  • select * from my_table where tests > 500000 order by labor;

Letzten Wert aus der Tabelle ausgeben.

  • select * from my_table order by kw desc limit 1;

CSV-Import und -Export

Daten aus einer CSV-Datei in die Tabelle laden.

  • load data infile "/var/lib/mysql-files/my.csv" into table my_table fields terminated by ',' lines terminated by '\n' ignore 1 rows;

Daten aus einer Tabelle als CSV-Datei speichern.

  • select * into outfile "/var/lib/mysql-files/backup.csv" fields terminated by ',' enclosed by '"' lines terminated by '\n' from my_table;

Backup und Wiederherstellung

Backup einer Datenbank erstellen.

  • mysqldump my_db > my_db.sql;

Backup aller Datenbanken erstellen.

  • mysqldump --all-databases > all_dbs.sql;

Backup einer einzelnen Datenbank wiederherstellen.

  • mysql my_db < my_db.sql;

Backup aller Datenbanken wiederherstellen.

  • mysql < all_dbs.sql;

Benutzerverwaltung

Neuen Benutzer anlegen.

  • create user 'tuxman'@'localhost' identified by 'Suxer-88';

Benutzerrechte für eine Datenbank vergeben.

  • grant all privileges on my_db.* to 'tuxman'@'localhost';
  • flush privileges;

Benutzerrechte entziehen.

  • revoke all privileges on my_db.* from 'tuxman'@'localhost';
  • flush privileges;

Passwort eines Benutzers ändern.

  • update user set password=password('suxer') where user='tuxman';
  • flush privileges;

Liste aller Benutzer ausgeben.

  • select user from mysql.user;

Erweiterte SQL-Funktionen

Tabelle mit Primärschlüssel erstellen.

  • create table my_table (kw int(4) primary key, tests int(16), labor int(16), postiv int(16), prozent float);

Neue Spalte zur Tabelle hinzufügen.

  • alter table my_table add column test_p_1000 int(16);

Spalte aus einer Tabelle entfernen.

  • alter table my_table drop column test_p_1000;

Berechnete Spalte hinzufügen.

  • alter table my_table add column prozent float default (postiv / tests * 100);

Tabelle duplizieren.

  • create table my_new_table like my_table;

Datentyp einer Spalte ändern.

  • alter table my_table modify column prozent double;

Spaltennamen ändern.

  • alter table my_table change prozent anteil float;

Gruppierte Abfragen

Summenberechnung nach Gruppen.

  • select sum(AnzahlFall), Altersgruppe from rki_csv where Landkreis = 'SK Zweibrücken' group by Altersgruppe;

Automatisierung mit Triggern und Events

Automatische Protokollierung von Einträgen aktivieren.

  • create trigger log_insert after insert on my_table for each row insert into log_table (aktion, zeit) values ('INSERT', now());

Regelmäßige Bereinigung alter Daten durch ein Event.

  • create event cleanup_old_entries on schedule every 1 day do delete from my_table where kw < 10;

JSON-Unterstützung in MySQL 5.7+ und MariaDB

JSON-Daten in einer Tabelle speichern.

  • create table json_test (id int auto_increment primary key, daten json);

Daten aus JSON-Feld extrahieren.

  • select json_extract(daten, '$.name') from json_test;

Fazit

Dieses Skript fasst die wichtigsten MySQL-Befehle kompakt zusammen. Die vorgestellten Befehle ermöglichen effizientes Datenbankmanagement. Automatisierung und Optimierung können durch Trigger, Events und JSON-Unterstützung erweitert werden.

Abgerufen von „https://wiki.ixheim.de/index.php?title=Mysql_Script&oldid=59926“